Windows下配置PHP支持mysql
在php.ini文件中找到extension=php_mysql.dll
和extension=php_mysqli.dll，如果之前有分号，表明被注释，去掉分号即可；如果没有这两句需要手动输入
unset()释放变量
可变变量
PHP中变量名可以是变量。
$catlogue = "city";
$$catlogue = "Zhengzhou";
第二句相当于$city = "Zhengzhou";
在输出可变变量时需要注意(使用大括号表明$cityname解释出来的值也要作为一个变量进行解释)
$Reno = 360000;
$Pasadena = 138000;
$cityname = "Reno";
echo "The size of $cityname is ${$cityname}";
$cityname = "Pasadena";
echo "The size of $cityname is ${$cityname}";
输出结果如下：
The size of Reno is 360000
The size of Pasadena is 138000
如果没有大括号，会输出
The size of Reno is $Reno
输出变量值可以使用以下3个句子
echo
print_r
var_dump //同时输出变量类型和变量值
定义常量
define("constantname","constantvalue");
PHP8种数据类型
Integer: 整数
Floating-point number: 浮点数
String: 字符串
Boolean: 布尔值
Array: 数组
Object: 对象
Resource: A reference that identifies a connection
NULL: A value that represents no value
PHP内置SQLite
MySQL查询语句
CREATE
DROP
ALTER
SHOW
INSERT
LOAD
SELECT
UPDATE
DELETE
use databasename
show databasename
quit
创建和维护MySQL用户账户
备份数据
更新MySQL
每个MySQL账户都有以下属性：
1.账户名（可以有16字符长，其中可以有空格和-符号，但不能出现通配符；账户名可以为空，这样可以允许主机上的所有有正确密码的用户访问数据库）
2.主机 —— 用户可以从它访问数据库的主机（可以包含通配符，例如George@%可以允许George在任何主机上访问数据库；主机名也可为空，作用和%一样）
3.密码（可为空）
4.一系列权限
一个主机名和一个账户名确定一个唯一的用户（accountname@hostname）。
所有的用户信息都存储在mysql数据库中，这个数据库在安装MySQL时自动创建。要添加或改变用户信息，必须有一个有权限操作mysql数据库的账户。
mysql数据库中包含以下表：
user
db
host
tables_priv
columns_priv
CREATE USER accountname@hostname IDENTIFIED BY 'password'
SET PASSWORD FOR username@hostname = PASSWORD('password')
取消密码：SET PASSWORD FOR username@hostname = PASSWORD('')
查看用户权限：SHOW GRANTS ON accountname@hostname
GRANT privilege (columns) ON tablename
TO accountname@hostname IDENTIFIED BY 'password'
GRANT select (firstName,lastName), update,
insert (birthdate) ...
GRANT select ON ProductCatalog.* TO phpuser@localhost
IDENTIFIED BY 'A41!14a!'
REVOKE privilege (columns) ON tablename
FROM accountname@hostname
REVOKE all ON . FROM accountname@hostname
DROP USER accountname@hostname, accountname@hostname, ...
mysqldump --user=accountname --password=password
databasename >path\backupfilename
mysql -u accountname -p < path/backupfilename
建表流程
1.Name your database.
2.Identify the objects.
3.Define and name a table for each object.
4.Identify the attributes for each object.
5.Define and name columns for each separate attribute that you identify in Step 4.
6.Identify the primary key.
7.Define the defaults.
8.Identify columns that require data.(不允许为空)
设计良好的数据库将每个数据只存储在一个地方。
建立表关系
常用MySQL数据类型
CHAR(length)
VARCHAR(length)
TEXT
INT(length)
INT(length)
UNSIGNED
BIGINT
DECIMAL(length,dec)
DATE
TIME
DATETIME
ENUM ("val1","val2"...)
SERIAL AUTO_INCREMENT（SERIAL是BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE的一个别名。）
一个表格必须有一个字段或者字段组合作为主键。不能有两个记录有相同的主键。如果多字段构成主键，在定义时PRIMARY KEY (columnname1,columnname2)
CREATE TABLE tablename (
columnname datatype definition1 definition2 ...,
columnname datatype definition1 definition2 ...,
...,
PRIMARY KEY(columnname) )
CREATE TABLE IF NOT EXISTS tablename
CREATE TABLE tablename LIKE oldtablename
新表只复制旧表的结构，不复制数据
SHOW TABLES
EXPLAIN tablename
查看表结构
DROP TABLE tablename
使用ALTER语句改变数据库结构。基本结构为ALTER TABLE tablename，后面可以跟
ADD columnname definition
ALTER columnname SET DEFAULT value
ALTER columnname DROP DEFAULT
CHANGE columnname newcolumnname definition
DROP columnname
MODIFY columnname definition
RENAME newtablename
例子：
ALTER TABLE Customer RENAME NewCustomer
ALTER TABLE Customer MODIFY lastName VARCHAR(50)
增###
INSERT INTO tablename (columnname, columnname,...,columnname)
VALUES (value, value,...,value)
从服务器所在目录的文件导入数据
LOAD DATA INFILE "path/datafilename" INTO TABLE tablename
FIELDS TERMINATED BY 'character'
FIELDS ENCLOSED BY 'character'
LINES TERMINATED BY 'character'
例子：
LOAD DATA INFILE "customer.dat" INTO TABLE Customer
FIELDS TERMINATED BY ','
从本地文件导入数据
LOAD DATA LOCAL INFILE "path/datafilename"
INTO TABLE tablename
查###
SELECT * FROM tablename
SELECT COUNT(*) FROM tablename
SELECT columnname,columnname,columnname,... FROM tablename
SELECT price,price*1.08 AS priceWithTax FROM Inventory
SELECT MAX(price) FROM Inventory
SELECT * FROM Customers ORDER BY DESC lastName
SELECT * FROM Inventory GROUP BY Category
SELECT lastName,firstName FROM Customer
WHERE lastName LIKE "B%"
AND city = "Indianapolis"
AND (phone LIKE "%8%" OR fax LIKE "%8%")
//返回前三条在德州的客户记录
SELECT * FROM Customer WHERE state="TX" LIMIT 3
//重复的Category只返回一次
SELECT DISTINCT Category FROM Product
UNION###
SELECT lastName,firstName FROM Member UNION ALL
SELECT lastName,firstName FROM OldMember
(SELECT lastName FROM Member UNION ALL
SELECT lastName FROM OldMember) ORDER BY lastName
JOIN##
INNER JOIN####
SELECT columnnamelist FROM table1,table2
WHERE table1.col2 = table2.col2
OUTER JOIN####
SELECT columnnamelist FROM table1 LEFT JOIN table2
ON table1.col1=table2.col2
SELECT columnnamelist FROM table1 RIGHT JOIN table2
ON table1.col1=table2.col2
更新表###
UPDATE tablename SET column=value,column=value,...
WHERE clause
删除记录###
DELETE FROM tablename WHERE clause
删除一列###
ALTER TABLE tablename DROP columnname
删除表###
DROP TABLE tablename
删除数据库###
DROP DATABASE databasename