Learning Objectives
- 用 CREATE DATABASE、USE 建立並選擇資料庫，用 CREATE TABLE 建立課程的五張表格
- 依資料的性質選擇資料型別，說明 DECIMAL 與 FLOAT 的差別
- 使用 NOT NULL、DEFAULT、AUTO_INCREMENT、PRIMARY KEY、UNIQUE、CHECK、FOREIGN KEY 限制資料
- 用 SHOW CREATE TABLE 看出外鍵自動建立的索引與約束名稱
- 用 CREATE INDEX、DROP INDEX、ALTER TABLE 調整索引與欄位，用 DROP TABLE、DROP DATABASE 刪除
本課要建立的資料庫
從這一課起，大部分範例都使用同一個 employee 資料庫，記錄一家顧問公司的部門、員工、技能、客戶與派工。
這一課從空白開始，把它建出來：
| 表格 | 欄位 | 說明 |
|---|---|---|
department |
departmentID、name |
部門 |
employee |
employeeID、name、job、departmentID |
員工；departmentID 指向所屬部門 |
employeeSkills |
employeeID、skill |
員工技能；一位員工可以有多項技能 |
client |
clientID、name、address、contactPerson、contactNumber |
客戶 |
assignment |
clientID、employeeID、workdate、hours |
派工：哪位員工哪一天替哪個客戶工作幾小時 |
先認識幾個關聯式資料庫的名詞：
- 欄位（column）：表格的直欄，例如
name。同一個欄位的資料型別相同 - 資料列（row）：表格的橫列，一筆紀錄，例如一位員工
- 主鍵（primary key）：用來唯一識別每一列的欄位，值不可重複，也不可以是 NULL
- 外鍵（foreign key）：欄位的值必須存在於另一張表格的主鍵（或唯一鍵）裡，例如員工的
departmentID一定要是存在的部門
SQL 的敘述依用途分成幾類，本課寫的 CREATE、ALTER、DROP 屬於定義結構的 DDL（Data Definition Language）；
下一課的 INSERT、UPDATE、DELETE 是處理資料的 DML；第 06 課的 SELECT 是查詢；交易控制與權限設定則在後面的課程介紹。
大小寫、註解與保留字
SQL 關鍵字不分大小寫：create table 和 CREATE TABLE 相同。本軌的範例把關鍵字寫成大寫，方便和表格、欄位名稱區分。
資料庫與表格名稱是否分大小寫，取決於作業系統：MySQL 把每個資料庫存成一個資料夾，Linux 的檔名分大小寫，
所以在 Ubuntu 上 employeeSkills 和 employeeskills 是兩張不同的表格；Windows 的 MySQL 預設把名稱一律轉成小寫儲存。
欄位名稱在任何系統上都不分大小寫。寫程式時，名稱的大小寫和建立時保持一致，換到哪一台伺服器都不會出錯。
三種註解寫法：
SELECT 1 + 1; # 井字號到行尾是註解
SELECT 1 + 1; -- 兩個減號加一個空白，到行尾是註解
SELECT 1 /* 這段是註解，可以寫在中間，也可以跨很多行 */ + 1;
名稱和保留字相同時（例如 interval、order），要用反引號（鍵盤左上角的 `）括起來，否則會出現語法錯誤：
CREATE TABLE interval (begin INT, end INT); -- ERROR 1064
CREATE TABLE `interval` (`begin` INT, `end` INT); -- 可以
比較好的做法是一開始就避開保留字。
建立資料庫與第一張表格
CREATE DATABASE [IF NOT EXISTS] 資料庫名稱;
SHOW DATABASES; -- 列出伺服器上的資料庫
USE 資料庫名稱; -- 之後的敘述都在這個資料庫裡執行
一台 MySQL 伺服器可以有很多個資料庫，建立表格之前要先 USE，否則會出現 ERROR 1046 (3D000): No database selected。
SHOW DATABASES 會看到 information_schema、mysql、performance_schema、sys 四個系統資料庫，不要修改它們。
MySQL 8 新建的資料庫預設使用 utf8mb4 字元集，中文、日文與 emoji 都能儲存。
CREATE TABLE 的寫法：
CREATE TABLE [IF NOT EXISTS] 表格名稱 (
欄位名稱 資料型別 [欄位屬性],
欄位名稱 資料型別 [欄位屬性],
...
[表格層級的約束，例如 PRIMARY KEY (...)、FOREIGN KEY (...)]
) [ENGINE=InnoDB];
欄位之間用逗號隔開，最後一個欄位或約束後面不加逗號。
關於 ENGINE=：原課程的舊版投影片寫成 TYPE=InnoDB，這是 MySQL 4 時代的寫法，MySQL 5.5 已經移除，
在 MySQL 8 執行會出現 ERROR 1064 語法錯誤，請一律寫 ENGINE=。從 MySQL 5.5 起預設的儲存引擎就是 InnoDB，
所以 ENGINE=InnoDB 也可以省略；本課保留它，提醒你外鍵與交易都需要 InnoDB。
建立 employee 資料庫與前兩張表格
這個範例從一台空的伺服器開始。注意兩件事：USE 之後右邊才出現資料庫的表格；employee 表格的 departmentID 宣告成外鍵，所以 department 必須先建立。
常用的資料型別
每個欄位都要宣告資料型別。型別決定能存什麼、佔多少空間，也決定比較與排序的方式：
| 型別 | 用途 | 說明 |
|---|---|---|
INT |
整數 | 範圍 -2147483648～2147483647；更大的數字用 BIGINT |
DECIMAL(M,D) |
精確的小數，例如金額、學分 | M 是總位數（最多 65），D 是小數位數（最多 30）；DECIMAL(5,2) 可存 -999.99～999.99 |
FLOAT、DOUBLE |
近似的浮點數，例如量測值 | 以二進位儲存，0.1 這類數字無法精確表示；FLOAT 約 7 位有效數字，DOUBLE 約 15 位 |
VARCHAR(N) |
長度不固定的文字 | N 是最多幾個字元；utf8mb4 下單一欄位最多 16383 個字元 |
CHAR(N) |
長度固定的文字，例如代碼 | N 最多 255；不足的部分以空白補齊，取出時再把尾端空白去掉 |
TEXT |
長文字 | 最多 65535 位元組；更長用 MEDIUMTEXT、LONGTEXT |
DATE |
日期 | 格式 'YYYY-MM-DD'，例如 '2026-09-14' |
DATETIME |
日期與時間 | 格式 'YYYY-MM-DD hh:mm:ss' |
ENUM('a','b',...) |
只能是清單中的一個值 | 例如 ENUM('basic','advanced') |
BLOB |
二進位資料 | 圖片、檔案等；實務上檔案多半存在檔案系統，資料庫只存路徑 |
舊教材的兩個說法已經過時：VARCHAR 最多 255 個字元是 MySQL 5.0.3 以前的限制；
INT(11) 括號裡的數字是「顯示寬度」，不影響能存的範圍，MySQL 8.0.17 起已不建議使用，寫了會出現警告 1681。
欄位屬性與約束：
| 寫法 | 作用 |
|---|---|
NOT NULL |
不可以是 NULL（NULL 表示「沒有值」，和空字串 ''、數字 0 都不同） |
DEFAULT 值 |
新增資料時沒有給這個欄位，就用預設值；DATETIME 可以用 DEFAULT CURRENT_TIMESTAMP |
AUTO_INCREMENT |
自動編號，每張表格只能有一個，而且必須是索引（通常就是主鍵） |
PRIMARY KEY |
主鍵，自帶 NOT NULL 與不可重複 |
UNIQUE |
值不可重複（NULL 例外，可以有多個 NULL） |
CHECK (條件) |
每一列都必須滿足條件。MySQL 8.0.16 起才真的檢查，更早的版本會接受這個寫法但完全忽略 |
資料型別與約束的實際效果
這個範例在另一個資料庫 school（執行前已經建立並 USE）裡建一張課程表格，用到本節介紹的大部分型別與約束。範例先用 INSERT 放兩筆資料進去觀察結果，INSERT 的完整寫法是下一課的主題。注意最後一個 SELECT：hours 看起來是 0.1，和 0.1 比較卻不相等。
FLOAT 的精度問題
電腦用二進位儲存浮點數，就像十進位寫不完 1/3 一樣，二進位也寫不完 0.1。
FLOAT 只有 4 個位元組，存進去的 0.1 實際上是 0.10000000149011612；顯示時 MySQL 會四捨五入成 0.1，看起來沒有問題，
但拿來比較或加總就會出現誤差：
| 運算 | FLOAT | DOUBLE | DECIMAL(10,2) |
|---|---|---|---|
| 0.1 + 0.2 | 0.30000000447034836 | 0.30000000000000004 | 0.30 |
（以上是在 MySQL 8.0.44 對三種型別的欄位實際執行 SUM() 的結果。）
所以：
- 金額、學分、成績這類需要精確的數字，用
DECIMAL - 量測值（溫度、心率、工作時數）可以用
FLOAT或DOUBLE，但不要用=比較，改用範圍，例如WHERE hours BETWEEN 8.49 AND 8.51
課程原本的 assignment.hours 用 float，本課照原課程保留；範例中的 8.5 剛好可以用二進位精確表示，所以比較時不會出問題。
想一想：資料型別與 CHECK
下面的敘述執行後，SELECT 顯示什麼？
CREATE TABLE price (amount DECIMAL(5,2));
INSERT INTO price VALUES (123.456);
SELECT amount FROM price;
下面的查詢傳回什麼？
CREATE TABLE m (x FLOAT);
INSERT INTO m VALUES (0.1);
SELECT COUNT(*) FROM m WHERE x = 0.1;
在 MySQL 8.0.44 執行下面兩個敘述，結果是什麼？
CREATE TABLE grade (score INT, CHECK (score BETWEEN 0 AND 100));
INSERT INTO grade VALUES (120);
複合主鍵與外鍵
複合主鍵：主鍵可以由多個欄位組成，表示「這幾個欄位的組合」不可重複。
employeeSkills 的主鍵是 (employeeID, skill)：同一位員工可以有很多項技能，同一項技能也可以屬於很多位員工，
但同一位員工不會重複記錄同一項技能。複合主鍵要寫在表格層級：
PRIMARY KEY (employeeID, skill)
外鍵讓 MySQL 幫你守住表格之間的關係：
FOREIGN KEY (欄位) REFERENCES 另一張表格(欄位)
- 被參照的欄位必須有索引，通常就是對方的主鍵；兩邊的資料型別要相同
- 只有 InnoDB 支援外鍵；MyISAM 會接受這個寫法但不檢查
- 被參照的表格要先建立，所以建立順序是
department→employee→employeeSkills、client→assignment - 外鍵欄位如果還沒有可用的索引，MySQL 會自動建立一個索引，名稱與欄位相同
- 沒有替外鍵命名時，MySQL 自動取名為「表格名稱_ibfk_編號」，例如
employee_ibfk_1。之後刪除外鍵或看錯誤訊息時都會用到這個名稱
注意一個舊寫法的陷阱：下一課會用到課程原本的 warning 表格，它把 REFERENCES 直接寫在欄位後面：
employeeID INT PRIMARY KEY NOT NULL REFERENCES employee(employeeID)
MySQL 8.0 會解析但忽略這種寫在欄位定義裡的 REFERENCES，不會建立外鍵，也不會出現任何警告。
要建立外鍵，一律寫成獨立的 FOREIGN KEY (...) REFERENCES ...。
建立其餘三張表格，看看 MySQL 自動加了什麼
執行前，employee 資料庫裡已經有 department 與 employee。最後兩個 SHOW CREATE TABLE 以 \G 結尾，結果改成直向顯示，方便閱讀長的 CREATE TABLE 敘述。請比較自己寫的 CREATE TABLE 與 MySQL 顯示的版本：多了哪些 KEY 與 CONSTRAINT？
索引
索引（index）就像書末的索引：不必從第一頁翻到最後一頁，直接查到某個詞在哪幾頁。
沒有索引時，WHERE name = 'Ben Smith' 要逐列檢查整張表格；有了 name 的索引，MySQL 可以直接找到符合的列。
- 主鍵與
UNIQUE會自動建立索引；外鍵欄位也會自動建立索引（前一個範例看到的） - 其他常拿來搜尋、排序或連接的欄位，可以自己建立索引
- 索引一個或多個欄位都可以，例如
(lastName, firstName) - 索引不是越多越好：每次新增、修改資料都要同時更新索引，也會佔用磁碟空間
- 索引建立之後，查詢時要不要用、怎麼用，由 MySQL 自己決定，寫查詢的方式不必改變
CREATE [UNIQUE] INDEX 索引名稱 ON 表格(欄位, ...); -- 建立索引
DROP INDEX 索引名稱 ON 表格; -- 刪除索引
ALTER TABLE 表格 ADD INDEX 索引名稱 (欄位, ...); -- 另一種建立方式
SHOW INDEX FROM 表格; -- 列出表格的索引
刪除的規則：
- 刪除主鍵或唯一鍵時，對應的索引一起刪除；刪除表格時，所有索引都一起刪除
- 刪除外鍵約束（
ALTER TABLE 表格 DROP FOREIGN KEY 約束名稱）時，索引會留下來，要另外DROP INDEX - 外鍵還在時，不能刪除它需要的索引，會出現
ERROR 1553
建立與刪除索引
這個範例使用已經有資料的 employee 資料庫。SHOW INDEX 的 Cardinality 是 MySQL 估計的「不同值的個數」，是統計值而不是精確計數，直譯器的數字為示意。
修改與刪除表格
表格建好後，用 ALTER TABLE 修改結構，資料會保留：
| 寫法 | 作用 |
|---|---|
ADD COLUMN 欄位 型別 [AFTER 欄位] |
新增欄位，預設加在最後，AFTER 指定放在哪個欄位後面，FIRST 放在最前面；已有的資料列在新欄位填入預設值或 NULL |
MODIFY 欄位 新型別 |
改變型別或屬性，名稱不變 |
CHANGE 舊名稱 新名稱 型別 |
改名稱，同時要寫出完整的型別 |
RENAME COLUMN 舊名稱 TO 新名稱 |
只改名稱（MySQL 8.0 起） |
DROP COLUMN 欄位 |
刪除欄位與其中的資料 |
ADD INDEX、DROP INDEX、ADD CONSTRAINT、DROP FOREIGN KEY |
增刪索引與約束 |
MODIFY 把型別改小時，現有的資料如果放不下，MySQL 會拒絕整個修改（例如 ERROR 1265 Data truncated），資料不會被截斷。
刪除表格與資料庫：
DROP TABLE [IF EXISTS] 表格; -- 刪除表格的結構、資料與索引
DROP DATABASE [IF EXISTS] 資料庫; -- 刪除資料庫與裡面所有的表格
這兩個敘述沒有復原的方法（DDL 不能用交易回復），執行前先確認備份。 被其他表格的外鍵參照的表格不能直接刪除，要先刪除參照它的表格（或那個外鍵），也就是和建立相反的順序。
修改 client 表格，再試著刪除
前四個 ALTER TABLE 依序新增、修改、改名、刪除欄位，觀察右邊 client 表格的欄位變化。最後兩個敘述示範刪除：employee 被其他表格參照，刪不掉；DROP DATABASE 則把整個資料庫連同五張表格一起刪除。
想一想：主鍵、外鍵與索引
employeeSkills 的主鍵是 (employeeID, skill)，原本有 2 位員工會 Java。執行下面的敘述，結果是什麼？
INSERT INTO employeeSkills VALUES (9842, 'Java');
SELECT COUNT(*) FROM employeeSkills WHERE skill = 'Java';
課程原本的 warning 表格把 REFERENCES 寫在欄位定義裡。在 MySQL 8.0 執行下面的敘述，最後的 SELECT 顯示什麼？（員工 99999 不存在）
CREATE TABLE warning (
employeeID INT NOT NULL PRIMARY KEY REFERENCES employee(employeeID),
count INT DEFAULT 1
);
INSERT INTO warning (employeeID) VALUES (99999);
SELECT COUNT(*) FROM warning;
assignment 的主鍵是 (clientID, employeeID, workdate)，兩個外鍵分別是 clientID 與 employeeID。建立表格時，MySQL 為外鍵另外自動建立了哪些索引？
動手寫：建立 project 表格
公司要記錄替客戶執行的專案。請在 employee 資料庫建立 project 表格，再用 DESCRIBE project; 檢查結果：
| 欄位 | 規格 |
|---|---|
projectID |
整數、不可為 NULL、自動編號、主鍵 |
clientID |
整數、不可為 NULL，外鍵參照 client(clientID) |
title |
最多 100 個字元的文字、不可為 NULL |
budget |
精確的金額，共 10 位數、其中 2 位小數；不可為 NULL，預設 0 |
startDate |
日期 |
重點整理
| 敘述 | 作用 |
|---|---|
CREATE DATABASE、USE、SHOW DATABASES |
建立、選擇、列出資料庫 |
CREATE TABLE、SHOW TABLES、DESCRIBE |
建立、列出表格，查看欄位 |
SHOW CREATE TABLE 表格\G |
看 MySQL 實際儲存的表格定義，包含自動建立的索引與約束名稱 |
CREATE INDEX、DROP INDEX、SHOW INDEX |
建立、刪除、列出索引 |
ALTER TABLE ... ADD／MODIFY／CHANGE／DROP COLUMN |
修改表格結構 |
DROP TABLE、DROP DATABASE |
刪除，無法復原 |
- 精確的數字用
DECIMAL，FLOAT不要用=比較 CHECK在 MySQL 8.0.16 起才會檢查- 外鍵只寫在表格層級的
FOREIGN KEY ... REFERENCES ...；寫在欄位後面的 REFERENCES 會被忽略 - 外鍵會自動建立索引，約束名稱是「表格_ibfk_編號」
- 建立表格的順序：先被參照的表格；刪除的順序相反
- 舊寫法
TYPE=InnoDB已移除，改用ENGINE=InnoDB
下一課把資料放進這些表格，並且修改、刪除資料。