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}";
輸出結(jié)果如下：
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種數(shù)據(jù)類型
Integer: 整數(shù)
Floating-point number: 浮點數(shù)
String: 字符串
Boolean: 布爾值
Array: 數(shù)組
Object: 對象
Resource: A reference that identifies a connection
NULL: A value that represents no value

PHP內(nèi)置SQLite

MySQL查詢語句
CREATE
DROP
ALTER
SHOW
INSERT
LOAD
SELECT
UPDATE
DELETE

use databasename
show databasename
quit

創(chuàng)建和維護MySQL用戶賬戶
備份數(shù)據(jù)
更新MySQL

每個MySQL賬戶都有以下屬性：
1.賬戶名（可以有16字符長，其中可以有空格和-符號，但不能出現(xiàn)通配符；賬戶名可以為空，這樣可以允許主機上的所有有正確密碼的用戶訪問數(shù)據(jù)庫）
2.主機 —— 用戶可以從它訪問數(shù)據(jù)庫的主機（可以包含通配符，例如George@%可以允許George在任何主機上訪問數(shù)據(jù)庫；主機名也可為空，作用和%一樣）
3.密碼（可為空）
4.一系列權(quán)限
一個主機名和一個賬戶名確定一個唯一的用戶（accountname@hostname）。

所有的用戶信息都存儲在mysql數(shù)據(jù)庫中，這個數(shù)據(jù)庫在安裝MySQL時自動創(chuàng)建。要添加或改變用戶信息，必須有一個有權(quán)限操作mysql數(shù)據(jù)庫的賬戶。
mysql數(shù)據(jù)庫中包含以下表：
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('')

查看用戶權(quán)限：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.(不允許為空)
設(shè)計良好的數(shù)據(jù)庫將每個數(shù)據(jù)只存儲在一個地方。

建立表關(guān)系

常用MySQL數(shù)據(jù)類型
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的一個別名。）

一個表格必須有一個字段或者字段組合作為主鍵。不能有兩個記錄有相同的主鍵。如果多字段構(gòu)成主鍵，在定義時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
新表只復(fù)制舊表的結(jié)構(gòu)，不復(fù)制數(shù)據(jù)

SHOW TABLES

EXPLAIN tablename
查看表結(jié)構(gòu)

DROP TABLE tablename

使用ALTER語句改變數(shù)據(jù)庫結(jié)構(gòu)?；窘Y(jié)構(gòu)為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)
從服務(wù)器所在目錄的文件導(dǎo)入數(shù)據(jù)
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 ','
從本地文件導(dǎo)入數(shù)據(jù)
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
//重復(fù)的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

刪除數(shù)據(jù)庫###

DROP DATABASE databasename