MySQL 初級語法筆記

TEMPORARY 關鍵字是用來建立暫時的資料表，當你的連結階段結束後會自動刪除。 CREATE TEMPORARY TABLE table_name;

IF NOT EXISTS 只有在資料表不存在時才會建立。 CREATE TABLE IF NOT EXISTS table_name;

LIKE old_table_name 會依照 old_table_name 的構造來建立新的資料表。

欄位定義 (column definition) 可用的選項：

• NOT NULL 或 NULL 來定義欄位不能包括 NULL (NOT NULL) 或可以包括 NULL (NULL)。
• 預設值是可以包括 NULL。
• DEFAULT 可用來宣告欄位的預設值。
• AUTO_INCREMENT 會自動產生連續的數字。產生的數字會大於資料表內的最大數值。每個資料表只能有一個 AUTO_INCREMENT 欄位。他必須是索引。
• 任何 PRIMARY KEY 的欄位會自動建立索引。
• PRIMARY KEY 可用來宣告 PRIMARY KEY 的欄位。
• REFERENCES 可用來宣告欄位是 FOREIGN KEY，只能用在 InnoDB 資料表。

下面的關鍵字可用來宣告欄位的資料：

• PRIMARY KEY 來設定多欄位的 PRIMARY KEY，跟著是欄位的名稱。
• INDEX 和 KEY 是一樣的關鍵字，用來設定索引。
• UNIQUE 表示欄位是獨特的，一樣是索引。
• FULLTEXT 可用來建立 FULLTEXT 索引，可用於 TEXT, CHAR, VARCHAR 類型。FULLTEXT 只能用在 MyISAM 資料表。
• FOREIGN KEY 可用來宣告 FOREIGN KEY。

下面的選項可以不用，通常是用在最佳化：

• AUTO_INCREMENT = # 設定開始的 AUTO_INCREMENT 數值。
• AVG_ROW_LENGTH = # 可用來估計你要的欄位長度。
• CHECKSUM = 1 開啟資料表的核對值。可用來修復損壞的資料表。只能用在 MyISAM。
• COMMENT = "字串" 用來輸入資料表的註解。
• MAX_ROWS = # 設定資料表允許的欄位值 (最大)。
• MIN_ROWS = # 設定資料表允許的欄位值 (最小)。
• PACK_KEYS = {0 | 1 | DEFAULT} MySQL 的預設是會壓縮 CHAR, VARCHAR, 和 TEXT 的字串。如要關閉選 0。
• DELAY_KEY_WRITE = {0 | 1} 延後 key 的更新，直到關閉資料庫。只能用在 MyISAM。
• ROW_FORMAT = {default | dynamic | fixed | compress} 用來設定欄位的儲存方式。只能用在 MyISAM。
• RAID_TYPE = {1 | STRIPED | RAID0} RAID_CHUNKS = # RAID_CHUNKSIZE = # RAID 的設定。
• UNION = (table_name, [table_name...]) 用於 MERGE 資料表。允許你設定 MERGE 使用的資料表。
• INSERT_METHOD = {NO | FIRST | LAST} 用於 MERGE 資料表。設定要插入資料到哪個資料表。
• DATA_DIRECTORY = "絕對路徑" 儲存資料的目錄。
• INDEX_DIRECTORY = "絕對路徑" 儲存索引的目錄。

欄位和資料類型

數字類型

INT 和 FLOAT 範例：salary decimal(10, 2) - 寬度 10 和 2 個小數點。 可以用 UNSIGNED 和 ZEROFILL 關鍵字。 UNSIGNED 只能包括零或整數。ZEROFILL 會顯示數字前的零數。

整數和變化

• INT 可以縮寫成 INT，大小是 4 bytes。
• TINYINT 是 1 byte，跟 BIT 和 BOOL 相同。
• SMALLINT 是 2 bytes。
• MEDIUMINT 是 3 bytes。
• BIGINT 是 8 bytes。
• FLOAT 是單精密度的浮點數。
• DOUBLE 是雙精密度的浮點數，跟 REAL 和 DOUBLE PRECISION 相同。

字串和文字類型

• CHAR 可用來儲存固定長度的字串，如果不設定長度，預設是 CHAR(1)。最大值是 255 個字元。固定長度有時候會比 VARCHAR 來的快。如果跟著的是 BINARY 關鍵字，比較字串的時候必須注意大小寫。
• VARCHAR 可用來儲存動態的字串，如果預先不知道字串的長度，範圍是 0 到 255。
• TEXT 可儲存較長的字串，最大到 65535 個字元或 bytes。
• BLOB 代表 binary large object，用來儲存 binary 資料，最大到 65535 個字元或 bytes。比較字串時需要注意大小寫。
• TINYTEXT 或 TINYBLOB 可儲存小於 255 個字元或 bytes 的字串。
• MEDIUMTEXT 或 MEDIUMBLOB 可儲存到 16777215 個字元或 bytes 的字串。
• LONGTEXT 或 LONGBLOB 可儲存到 4294,967,295 個字元或 bytes 的字串。
• ENUM 可用來儲存自訂的值。範例：gender enum('m', 'f') 表示儲存的直是 m 和 f。也可儲存 NULL，所以可能的直是 m, f, NULL, 或 error。
• SET 跟 ENUM 類似不過可以儲存其它的 ENUM 值。

日期和時間類型

• date 可用來儲存日期，以 YYYY-MM-DD 顯示。
• time 可用來儲存時間，以 HH:MM:SS 顯示。
• datetime 包含了日期和時間，以 YYYY-MM-DD HH:MM:SS 顯示。
• timestamp 儲存插入欄位或改變的時間。版本 4.0 之前可以用 timestamp(10) 來改變顯示的寬度。
• year 可用來儲存年份，可以用 year(2) 或 year(4) 來設定顯示格式，預設是 year(4)。year(2) 代表從 1970 到 2069 的年份。

建立索引

任何宣告為 PRIMARY KEY, KEY, UNIQUE, 或 INDEX 的欄位都會自動建立索引。 要將索引建立到欄位用：

CREATE INDEX name ON employee(name);

這會建立一個叫 name 的索引到 employee 資料表裡的 name 欄位。

刪除資料庫，資料表，和索引

刪除整個資料庫，包括裡面的資料：

DROP DATABASE employee;

可以在 employee 的前面加上 IF EXISTS。

刪除一個資料表：

DROP TABLE assignment;

完整格式：

DROP [TEMPORARY] TABLE [IF EXISTS] table_name [, table_name...]

刪除索引：

DROP INDEX index_name ON employee;

改變資料表結構

如果要改變資料表的結構，建立 name 的索引在 employee 裡面的 name 欄位：

ALTER TABLE employee ADD INDEX name (name);

完整格式：

ALTER [IGNORE] TABLE table_name alter_spec [, alter_spec ...]
alter_spec:
ADD [COLUMN] create_definition [FIRST | AFTER col_name]
or ADD [COLUMN] (create_definition, create_definition,...)
or ADD INDEX [index_name] (index_col_name,...)
or ADD PRIMARY KEY (index_col_name,...)
or ADD UNIQUE [index_name] (index_col_name,...)
or ADD FULLTEXT [index_name] (index_col_name,...)
or ADD [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name,...)
[reference_definition]
or ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
or CHANGE [COLUMN] old_col_name create_definition
[FIRST | AFTER column_name]
or MODIFY [COLUMN] create_definition [FIRST | AFTER col_name]
or DROP [COLUMN] col_name
or DROP PRIMARY KEY
or DROP INDEX index_name
or DISABLE KEYS
or ENABLE KEYS
or RENAME [TO] new_table_name
or ORDER BY col_name
or table_options

因為 ALTER 非常有彈性所以有很多的查詢子句。 CHANGE 和 MODIFY 相同，可以讓你改變欄位的定義或位置。 DROP COLUMN 會刪除資料表裡的欄位。 DROP PRIMARY KEY 和 DROP INDEX 會刪除相連的欄位索引。 DISABLE KEYS 會告訴 MySQL 停止更新索引，只能用在 MyISAM。 ENABLE KEYS 會繼續更新索引。 RENAME 可以讓你改變資料表的名稱。 ORDER BY 會重新排序欄位。

使用 INSERT

INSERT 可以讓你輸入欄位到資料表裡。 完整格式：

INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] table_name [(col_name,...)]
VALUES ((expression | DEFAULT),...),(...),...
[ON DUPLICATE KEY UPDATE col_name=expression, ...]
or
INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] table_name [(col_name,...)]
SELECT ...
or
INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] table_name
SET col_name=(expression | DEFAULT), ...
[ON DUPLICATE KEY UPDATE col_name=expression, ...]

範例：

INSERT INTO department VALUES
(42, 'Finance'),
(128, 'Research and Development'),
(NULL, 'Human Resources');

INTO 可以不用。 INSERT INTO department SELECT ... 可以從其它的資料表裡取得資料，存到 department 這個資料表。 INSERT INTO department SET name = 'Asset Management'; 這會輸入一個欄位的資料，只提供 name 的欄位。其他沒有資料的欄位會是 NULL。

其它查詢子句：

• LOW PRIORITY 或 DELAYED 會等其他用戶讀取完資料後再輸入資料。LOW PRIORITY 會阻止要輸入資料的用戶，所以在跑完查詢後需要等待一段時間。DELAYED 不會阻止用戶，但是輸入資料要等沒人使用資料表後才會完成。範例：INSERT DELAYED INTO ...
• IGNORE 會忽略輸入跟欄位的 PRIMARY KEY 或 UNIQUE 值的衝突所產生的錯誤訊息。
• DEFAULT 會用預設值建立欄位。範例：INSERT INTO department VALUES(DEFAULT)
• ON DUPLICATE KEY UPDATE 會解決跟 PRIMARY KEY 或 UNIQUE 的衝突。範例：INSERT INTO warning (employeeID) VALUES(6651) ON DUPLICATE KEY UPDATE count = count+1;

使用 REPLACE

使用方法跟 INSERT 相同。REPLACE 可以解決有衝突的輸入。如果有衝突欄位會被更新。

使用 DELETE

用來刪除欄位。 範例：DELETE FROM department; 會刪除 department 裡面所有的欄位。 如果要選擇哪個欄位，用 WHERE。 範例：DELETE FROM department WHERE name='Asset Management'; 如果有開啟 --safe-updates 或 --i-am-a-dummy 選項可以避免一次刪除全部的欄位。所以必須選擇要刪除的欄位。

完整格式：

DELETE [LOW_PRIORITY] [QUICK] FROM table_name
    [WHERE where_definition]
    [ORDER BY ...]
    [LIMIT rows]
or
DELETE [LOW_PRIORITY] [QUICK] table_name[.*] [, table_name[.*] ...]
    FROM table-references
    [WHERE where_definition]
or
DELETE [LOW_PRIORITY] [QUICK]
    FROM table_name[.*] [, table_name[.*] ...]
    USING table-references
    [WHERE where_definition]

後兩個格式可以用來刪除其他資料表裡的欄位。

範例：

DELETE employee, employeeSkills
FROM employee, employeeSkills, department
WHERE employee.employeeID = employeeSkills.employeeID
AND employee.departmentID = department.departmentID
AND department.name='Finance';

上面的會刪除全部的有在 Finance department 裡面工作的 employees，然後刪除他們的 employeeSkills。 FROM 裡面的欄位不會被刪除，他們只是用來搜尋。只有在 employee 和 employeeSkills 裡面的欄位會被刪除。 AND 是包含的條件。employee.employeeID 表示 employee 資料表裡的 employeeID 欄位。 第三個格式跟第二個差不多，但是刪除 FROM 裡面的資料表，用 USING 來表示被搜尋的資料表。

額外的查詢子句：

• LOW_PRIORITY 跟 INSERT 裡的意思相同。
• QUICK 可用來加快 DELETE 的速度。
• ORDER BY 是用來刪除欄位的順序。
• LIMIT 可用來限制被刪除的欄位數量。

使用 TRUNCATE

可用來刪除全部的欄位。

TRUNCATE TABLE employee;

速度比 DELETE 還快。

使用 UPDATE

可以用來更新欄位。

UPDATE employee
SET job='DBA'
WHERE employeeID='6651';

完整格式：

UPDATE [LOW_PRIORITY] [IGNORE] table_name
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]
[ORDER BY ...]
[LIMIT rows]
or
UPDATE [LOW_PRIORITY] [IGNORE] table_name [, table_name ...]
SET col_name=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]

WHERE 可以用來選擇要更新哪個欄位。 第二個格式可以用來更新多個資料表。 LOW_PRIORITY 和 IGNORE 跟在 INSERT 裡的相同，ORDER BY 和 LIMIT 跟在 DELETE 的相同。

用 LOAD DATA INFILE 上傳資料檔

可以讓你用檔案來輸入資料到一個資料表。

資料檔範例：

42	Finance
128	Research and Development
NULL	Human Resources
NULL	Marketing

載入資料檔：

LOAD DATA LOCAL INFILE 'department_infile.txt'
INTO TABLE department;

LOAD DATA INFILE 需要 FILE 的權限。

完整格式：

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'fileName.txt'
[REPLACE | IGNORE]
INTO TABLE table_name
[FIELDS
[TERMINATED BY '\t']
[[OPTIONALLY] ENCLOSED BY '']
[ESCAPED BY '\\']
]
[LINES TERMINATED BY '\n']
[IGNORE number LINES]
[(col_name, ...)]

額外的查詢子句：

• LOW_PRIORITY 跟 INSERT 裡的類似，會等用戶讀取完資料表後在繼續。
• CONCURRENT 允許其他用戶讀資料表裡的資料。
• LOCAL 表示資料檔在用戶的電腦裡，如果不用 MySQL 會在主機內讀取資料檔。
• REPLACE 會覆蓋舊的欄位，IGNORE 會留舊的欄位，如果有衝突。
• FIELDS 和 LINES 可以用來規定資料表的格式，預設的格式是每行一個欄位，欄位資料用 tabs 來分開，要或不要用單引號，和用 / 來跳脫字元。
• IGNORE number LINES 會忽略檔案內行數，number 表示行數。
• 最後一個子句可以用來設定只輸入資料到某些欄位。