基本語法

SELECT ...
FROM table1
CROSS JOIN table2;

結果

#89 SELECT e.ename, e.deptno, d.dname FROM dept d, emp e WHERE d.deptno = e.deptno;

- 使用表格全名
舉例:
```sql
SELECT emp.empno, emp.ename, emp.mgr, emp.sal, emp.deptno,
       dept.deptno, dept.dname
FROM emp
JOIN dept ON emp.deptno = dept.deptno;

#別名

👉 把 emp（員工）
👉 與 dept（部門） 👉 用 deptno 連接 👉 顯示員工資訊＋部門名稱

• 列出員工姓名KING所在的部門資訊

SELECT e.empno, e.ename, e.mgr, e.sal, e.deptno, d.deptno, d.dname
FROM emp e
JOIN dept d ON e.deptno = d.deptno
WHERE e.ename = 'KING';

自然連結(Natural Join)

• 連結條件(Join Condition)

1. 使用表格內之同名欄位作為連結條件

SELECT e.ename, e.job, e.sal, e.deptno, d.dname,d.loc
FROM emp e NATURAL JOIN dept d;

SELECT e.ename, e.job, e.sal, e.deptno, d.dname,d.loc
FROM emp e JOIN dept d ON e.deptno = d.deptno; 

#92
SELECT empno, ename, mgr, sal, deptno, dname
FROM emp
NATURAL JOIN dept
WHERE deptno = 10;

#89
SELECT e.empno, e.ename, e.mgr, e.sal, e.deptno, d.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno AND e.deptno = 10;

#USING
SELECT e.empno, e.ename, e.mgr, e.sal, deptno, d.dname
FROM emp e
JOIN dept d USING(deptno)
WHERE deptno = 10;

不相等連結(Non-Equijoins)

• 將要連結的表格名稱列在FROM 子句中，並加入JOIN 運算子及ON來設定連結條件
• 連結條件的運算子(Operators)
• >= 或<=
• BETWEEN AND 若符合條件的資料超過一筆以上時，因向量積關係，資料可能會不正確。

SELECT e.empno, e.ename, e.sal, s.grade
FROM emp e JOIN salgrade s ON (e.sal BETWEEN s.losal AND s.hisal);

• 列出所有員工薪資的等級

SELECT e.empno, e.ename, e.sal, s.grade
FROM emp e
JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal;

多表格連結

三個表格兩個join SELECT …
FROM t1 JOIN t2 ON t1.columnA = t2.columnA
JOIN t3 ON t2.columnB = t3.columnB
JOIN t4 ON t3.columnC = t4.columnC
… {color=“gray_bg”}

#92
SELECT c.name, c.custid, o.ordid, i.itemid
FROM customer c JOIN ord o ON c.custid = o.custid
JOIN item i ON o.ordid = i.ordid;

#89 不建議
SELECT  c.name, c.custid, o.ordid, i.itemid
FROM    
customer c,  ord o, item i
WHERE   
c.custid = o.custid AND o.ordid = i.ordid ;

• 多個資料表的連結(計算員工業績)

SELECT e.empno, e.ename, SUM(o.total) AS total
FROM emp e
JOIN customer c ON e.empno = c.repid -- 這個客戶是哪個員工負責
JOIN ord o ON c.custid = o.custid  -- 每個客戶都會多個訂單
GROUP BY e.empno, e.ename; -- 依照員工來做分組 ->因為要算他的業績
 -- 因為SELECT 有ename 所以group by也要有

員工表格找到員工→員工有客戶→客戶有訂單→加總訂單$ ename → empno = repid → custid = custid

外部連結(Outer Join)

語法

• 指定一資料表，當連結條件符合時則顯示，若與第二個資料表都不符合時則以null value顯示
• OUTER可以省略不寫
• 外部連結種類
• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN SELECT alias.column, alias.column,…
  FROM table1 [AS] alias1
  {[LEFT|RIGHT|FULL [OUTER]] JOIN }
  table2 [AS] alias2
  ON alias1.column = alias2.column
  WHERE …; {color=“gray_bg”}

-- 沒有部門的員工
SELECT eno, ename, e.dno, dname
FROM emp e  LEFT JOIN dept d ON (e.dno = d.dno); -- 以左邊的表格為依據，如果找不到資料，就將左邊表格加入，並顯示空值

-- 沒有員工的部門
SELECT eno, ename, e.dno, dname
FROM emp e RIGHT JOIN dept d ON (e.dno = d.dno); --右邊找不到匹配，我也加入右邊表格的資訊

# 沒有部門的員工及沒有員工的部門
SELECT eno, ename, e.dno, dname
FROM emp e FULL JOIN dept d ON (e.dno = d.dno); -- 全部的資料都顯示，不管條件有沒有匹配成功

找「沒有任何員工的部門」

SELECT e.ename, e.deptno, d.dname, d.loc
FROM emp e
RIGHT OUTER JOIN dept d ON e.deptno = d.deptno
WHERE e.empno IS NULL; --  找「員工不存在」的資料

--有員工 → e.empno 一定有值
--沒員工 → e.empno = NULL

自我連結(Self Joins)

• 同一個表格裡，資料和資料的關係
• 使用同一表格做連結運算
• 使用表格別名來建立二個相同資料表的連結運算

語法

SELECT …
FROM Table1 A1 JOIN Table1 A2 ON A1.Col1 = A2.Col2
… {color=“gray_bg”}

• 員工和主管，當找不到主管時，就不會被記入，就會少一筆

e1 -> 員工表格  e2 -> 主管表格
SELECT e1.empno, e1.ename, e1.mgr, e2.ename AS manager_name
FROM emp e1
JOIN emp e2 ON e1.mgr = e2.empno
ORDER BY e1.mgr;

子查詢(Sub-Queries)

概念

• 利用子查詢來解決另類查詢問題
• 請找出薪資比MARY高的所有員工資料
• 主查詢中含有另一個查詢(SELECT)
• 查詢時先執行子查詢,並將結果傳回主查詢
• 子查詢可以出現的位置
• WHERE子句
• HAVING 子句
• FROM子句
• SELECT子句
• 子查詢是巢狀查詢:查詢中有查詢
• 內部查詢(inner query)的結果會傳給外部查詢(outer query)
• 內部查詢結果當成外部查詢運算式中的運算元
• 子查詢的種類(依子查詢執行方式分類):
• 自主子查詢(Self-contained sub-queries): 與主查詢沒有相關性
• 相關子查詢(Correlated sub-queries): 需依賴外部查詢的值
• 子查詢的型式(依子查詢結果分類):
• 純量(scalar)
• 多值(multi-valued)
• 多欄位(multiple-column)
• 表格值(table-valued)

語法

單筆記錄子查詢(Single Row Subqueries)

回傳值只有單欄位單筆紀錄 sal → 2975

SELECT empno, ename, sal
FROM emp
WHERE sal > (
SELECT sal
FROM emp
WHERE empno = 7566
);

子查詢(Sub-Queries)－WHERE 子句

列出和JAMES同部門的員工

SELECT empno, ename, deptno
FROM emp
WHERE deptno = (
    SELECT deptno
    FROM emp
    WHERE ename = 'JAMES' -- 找到JAMES的員工編號
);

多個子查詢在SELECT 中

SELECT empno, ename, sal, job
FROM emp
WHERE job = (
    SELECT job
    FROM emp
    WHERE empno = 7499
)
AND sal > (
    SELECT sal
    FROM emp
    WHERE empno = 7934
);

列出薪資>全公司平均薪資的員工

SELECT empno, ename, sal
FROM emp
WHERE sal > (
    SELECT AVG(sal)
    FROM emp
);

HAVING 👉 找出：

✅ 最低薪水「比部門20還高」的部門

SELECT deptno, MIN(sal)
FROM emp
GROUP BY deptno
HAVING MIN(sal) > (
    SELECT MIN(sal)
    FROM emp
    WHERE deptno = 20
);

子查詢出現在SELECT子句中

SELECT ename, job, deptno, sal,
       CONCAT(ROUND(sal * 100 / (SELECT SUM(sal) FROM emp), 2), '%') AS "佔全公司薪資比率"
FROM emp
WHERE deptno = 30;

子查詢出現在FROM子句中: 衍生資料表(Derived Table) → 臨時產生的表格，只存在記憶體中，不在硬碟，僅能用在本次指令

SELECT e.ename, e.job, e.sal,
       CONCAT(ROUND(e.sal * 100 / b.total, 2), '%') AS "佔全公司薪資比率"
FROM emp e
CROSS JOIN (SELECT SUM(sal) AS total FROM emp) b
WHERE e.deptno = 30;

多筆記錄子查詢(Multiple Row Subqueries)

WHERE {color=“yellow_bg”} 多筆資料用() → 找出單筆資料用 = ** {color=“yellow_bg”} ** →找出多筆資料用 IN 列出公司所有主管

SELECT empno, ename
FROM emp
WHERE empno IN (
    SELECT mgr
    FROM emp
);

「沒有當主管的員工」—解決方法:排除子查詢空值資料

SELECT empno, ename
FROM emp
WHERE empno NOT IN (
    SELECT mgr -- EMPNO 沒有在MGR裡面
    FROM emp
    WHERE mgr IS NOT NULL
);

ANY or ALL

列出薪水比任一個clerk低的員工資料

SELECT empno, ename, sal
FROM emp
WHERE sal < ANY (
    SELECT sal
    FROM emp
    WHERE job = 'CLERK'
);

列出薪水比所有salesman高的員工資料

SELECT empno, ename, job, sal
FROM emp
WHERE sal > ALL (
    SELECT sal
    FROM emp
    WHERE job = 'salesman'
);

列出部門與薪水都與Martin 相同之員工

SELECT empno, ename, deptno, sal
FROM emp
WHERE (deptno, sal) = (
    SELECT deptno, sal
    FROM emp
    WHERE ename = 'MARTIN'
);

相關子查詢(Correlated Subqueries)

• 相關子查詢必須依賴外部查詢所使用的資料表的資料來執行子查詢
• 依賴於外部查詢，不能單獨執行
• 比自主子查詢難測試
• 執行次數依主查詢的資料筆數而定
• 一筆資料執行一次子查詢
• 可能傳回一個值或多個值

撰寫相關子查詢

內部查詢如何接收外部查詢資料表的資料
外部查詢如何接受內部查詢執行的結果(scalar or multi-valued)

SELECT custid, ordid, orderdate
FROM ord AS outerorders
WHERE orderdate = (
    SELECT MAX(orderdate)
    FROM ord AS innerorders
    WHERE innerorders.custid = outerorders.custid
)
ORDER BY custid;

查詢每個客戶最近跟公司下訂單的日期資料

SELECT ordid, custid, orderdate
FROM ord AS O1
WHERE orderdate = (
    SELECT MAX(orderdate) -- 最大就是最近的
    FROM ord AS O2
    WHERE O2.custid = O1.custid
)
ORDER BY custid, orderdate;

解釋

每筆訂單 → 找同客戶最大日期 → 留下最新訂單

查詢各部門薪資最高的員工資料

SELECT ename, sal, deptno
FROM emp oe
WHERE sal = (
    SELECT MAX(sal)
    FROM emp ie
    WHERE ie.deptno = oe.deptno
);

使用EXISTS－存在性測試

• 子查詢可配合使用EXISTS來做資料存在性測試(existence test)
• 只傳回真(True)或假(False)
  • 不會將查詢結果的資料傳回給主查詢
• 若子查詢有得到任一筆資料則傳回真(True),若子查詢沒有任何一筆資料則傳回假(False) WHERE [NOT] EXISTS (subquery) {color=“gray_bg”}
• EXISTS 不需任何欄位或運算式
• 因為不傳回資料,所以一般在子查詢中的SELECT 資料項都使用星號asterisk (*)

SELECT  custid, name
FROM    
customer AS c
WHERE 
EXISTS ( SELECT * 
FROM ord AS o
WHERE c.custid = o.custid);

SELECT  custid, name
FROM    
customer AS c
WHERE 
NOT EXISTS ( SELECT * 
FROM ord AS o
WHERE c.custid = o.custid);

列出下過訂單的客戶資料

SELECT custid, name
FROM customer AS c
WHERE EXISTS (
    SELECT *
    FROM ord AS o
    WHERE c.custid = o.custid
);

列出從未下過訂單的客戶資料

SELECT  custid, name
FROM    
customer AS c
WHERE NOT EXISTS ( SELECT *           
FROM ord AS o
WHERE c.custid=o.custid);

-->Empty set (0.00 sec) 表示所有的顧客都下過訂單

資料處理語言(DML) CRUD

資料處理語言(Data Manipulation Language)

• 做新改刪之前要先 SET SQL_SAFE_UPDATES= 0;

INSERT INTO命令

INSERT INTO table [(column,…)]
VALUES(value,…); {color=“gray_bg”}

• 欄位與值的數量需相同，位置對應且型態要相容
• 可以用指定欄位或不指定欄位的方式來新增資料
  • 不指定欄位時，資料表中所有的欄位均要給值
  • 使用DESC 指令可以得知欄位值的順序

#指定欄位
INSERT INTO dept(deptno, dname, loc)
VALUES(50,'MIS','NEW YORK');
#不指定欄位
INSERT INTO dept
VALUES(50,'MIS','NEW YORK');

#不指定欄位還是要按造欄位順序放入資料
#可以透過DESC的指令來查詢欄位順序

DESC dept;

新增資料－Null 值

• 新增空值到資料表的方法
• 未列舉的欄位,新增資料時將值填入預設值(default value)，若該欄位沒有設定預設值
  則填入空值(NULL)

INSERT INTO dept(deptno, dname)
VALUES (60, 'MIS');

result->
Query OK, 1 row affected

#新增loc = 空值 不要'null'
INSERT INTO dept(deptno, dname, loc)
VALUES (70, 'HR', NULL);

新增資料－日期資料

• 新增日期資料的方法
• 可以使用NOW()或CURRENT_DATE()…等函數
• 用字串輸入，請參照資料型態章節

INSERT INTO EMP(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES(8001,'JAMES','ANALYST',7839,CURRENT_DATE()
,2500,NULL,20);

INSERT INTO EMP(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES(8002,'JOHN','SALESMAN',7839,'2013-03-25',1400,500,30);

INSERT INTO多筆記錄

新增多筆資料 用( ), 區分新的一筆資料

INSERT INTO table_name (column_list)
VALUES (value_list_1),
(value_list_2),
…
(value_list_n); {color=“gray_bg”}

INSERT INTO EMP
VALUES
(8001,'JAMES','ANALYST',7839, CURRENT_DATE(),2500,NULL,20),
(8002,'JOHN','SALESMAN',7839, CURRENT_DATE(),1400,500,30);

INSERT敘述常發生的錯誤

SELECT * FROM bonus;

### UPDATE
### 修改資料表中的資料 UPDATE命令
UPDATE table<br>SET column=value \[,column=value\]... —\>多筆資料多個欄位<br>WHERE conditions; {color="gray_bg"}
- 欄位與值的型態應相同
- 一次可以修改多個欄位，用逗號隔開
- 若遺漏了WHERE子句則會更改資料表中所有的rows
- MySQL 為避免不小心更改到過多的資料，使用SQL_SAFE_UPDATES系統參數來保護資料(每次連線將其值設為1)，若要更改或刪除資料，請先執行下列設定:<br><span color="gray_bg">SETSQL_SAFE_UPDATES= 0;</span>
```sql
UPDATE emp
SET    
deptno = 20
WHERE  empno = 7782;

修改一個欄位資料

UPDATE emp
SET sal = 1000
WHERE empno = 7900;

同時更新兩個欄位資料

UPDATE emp
SET job    = 'SALESMAN',
deptno = 30
WHERE empno = 7369;

使用子查詢來更新資料

• 使用子查詢
• 與子查詢合併使用時，二者間資料表不可以是同一個 {color=“yellow_bg”}
• 當取值使用

-- 我要把部門編號改成 HR的部門編號 ，但我不知道HR的部門編號，先找HR的部門編號，再改。
UPDATE emp
SET deptno = (SELECT deptno
FROM dept 
WHERE dname='HR')
WHERE empno = 7900;

• 當條件使用

-- 把所有SALES這個部門的人加薪500
UPDATE emp
SET sal = sal + 500
WHERE deptno = (SELECT deptno
FROM dept 
WHERE dname='SALES');

DELETEFROM命令

DELETE FROM table
[WHERE conditions]; {color=“gray_bg”}

• 刪除資料表中的資料
• 若遺漏了WHERE子句時，則刪除資料表中所有的資料

DELETE FROM dept
WHERE deptno = 70;

• 指定刪除的對象

DELETE FROM dept
WHERE dname = 'Finance';

• 省略WHERE子句刪除表格中全部的資料

DELETE FROM dept;

• 使用子查詢結果作為刪除資料的對像(條件)

DELETE FROM emp
WHERE deptno = (SELECT deptno
FROM dept
WHERE dname LIKE '%Public%');

新增修改刪除常見錯誤

result-> ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (zb105.emp, CONSTRAINT EMP_DEPTNO_FK FOREIGN KEY (DEPTNO) REFERENCES dept (DEPTNO))

#### 新增資料－違反NOT NULL
```sql
INSERT INTO emp(empno, ename, job, deptno )->   
VALUES(9001,'SANDY','CLERK',NULL);

-
ERROR 1048 (23000): Column 'DEPTNO' cannot be null

-

UPDATE EMP
SET DEPTNO = NULL
WHERE EMPNO = 8001;

新增資料－違反外來鍵

新增資料時的錯誤
 違反外來鍵(Foreign Key)的錯誤

INSERT INTO FKT
Values (4,’ROW8’)

在PKT TABLE中並沒有4 這筆資料,所以無法新增

修改資料時的錯誤
 違反整合資料檢查條件(Integrity Constraint Error)

UPDATE FKT
SET FK = 3
WHERE C2 = ’ROW1’

UPDATE FKT
SET FK = 4
WHERE C2 = ’ROW5’

在PKT TABLE中並沒有4 這筆資料,所以無法更新

刪除資料－違反外來鍵

• 刪除資料的錯誤
  違反整合資料檢查條件(Integrity Constraint Error) 我的PK table : PK
  —————-
  1
  2
  3 → 刪掉這個
  
  但是我連到的FK FK
  —————- 1 A
  1 A
  2 B
  2 C
  3 D → FK會出錯 找不到PK
  3 E → FK會出錯 找不到PK
  3 F → FK會出錯 找不到PK
  

在FKT TABLE中有3筆資料參考著PKT TABLE中PK=3 這筆資料,
故刪除PKT TABLE中PK=3 時會產生錯誤訊息,如下:

DELETE FROM pkt
WHERE pk = 3;

-
ERROR 1217 (23000): Cannot delete or update a parent row: a 
foreign key constraint fails

DML交易控制(DML Transaction Control)

交易中只能有一個USER控制，USER2要等前一個UAER交易結束，所以寫交易要考慮其他USER

資料庫交易 (Database Transactions)

簡介

● 交易 (Database Transactions) 改變資料庫中現有資料 ● 依不同類型的指令所產生的交易： DML 交易 → 資料操作: 更改資料內容 (沒有動欄位)
DDL 交易 → 資料定義: 更改資料結構，EX: 建立新資料表，新增修改欄位，刪除表格等等
DCL 交易 → 資料控制: 管理權限，誰可以用我的資料 ● MySQL 預設的交易處理方式： DDL & DCL 交易：Auto commit（自動確認） → 直接生效，不能Rollbake

更改結構或是權限的時候，直接確定，不能回頭

DML 交易：Auto Commit（自動確認）→ User Commit（使用者確認或放棄） ##你可以決定要不要真的改動或是取消變更，可以反悔

交易控制 (Transactions Control)目的

主要目的: 維持資料庫中資料的

• 一致性（Consistency）
• 正確性（Correctness）

何謂交易(Database Transactions)

• 是多個 DML statements 所構成的邏輯工作單位
• 交易(Transaction) 提供了一個資料處理的邏輯單元(Logic Unit)
• 在該邏輯單元中:
• 如果全部執行成功,則會確定交易期間所修改的所有資料正式成為資料庫的內容(Commit)
• 如果有發生錯誤,則必須取消或回復該交易期間內所有的資料修改(Rollback)
• 交易的結果: 成功或失敗 →交易 = 要嘛全部成功，要嘛全部取消（不能一半）

交易 ACID 特性

所有資料庫交易都必須符合 ACID，確保資料完整性 ACID = 全做或不做、資料正確、互不干擾、結果永久

A（Atomic）單元性

• 又稱：不可分割性
• 交易必須「全部成功或全部失敗」
• 只要有一部分失敗 👉 整個交易回復（Rollback）

C（Consistency）一致性

• 交易前後，資料庫都要維持正確狀態
• 必須符合所有資料完整性限制（Constraint）

I（Isolation）隔離性

• 多個交易同時執行時
  👉 彼此不會互相干擾
• 每個交易感覺像「自己單獨執行」

D（Durability）持久性

• 一旦交易成功（Commit）
  👉 資料永久保存
• 即使系統當機也不會消失

AUTOCOMMIT 自動確認

• MySQL只有InnoDB Type才支援交易處理
• MySQL 預設的DML交易控制是自動確認 (AUTOCOMMIT=1)
• 每次連線時,MySQL會將此變數設定成1(True)
• 每一個DML指令執行完畢後把它作出的修改立刻提交給資料庫並使之永久化
• 查詢交易控制現況: SELECT @@AUTOCOMMIT
• 可以將交易控制改變成使用者確認 (AUTOCOMMIT=0)
• 設定指令為: SET AUTOCOMMIT = 0
• 在此狀態下,DML指令執行結果不會提交給資料庫
• 只有當執行COMMIT指令時,之前的修改才會永久寫入資料庫
• 若執行ROLLBACK指令,則會放棄所有DML對資料之修改

#AUTOCOMMIT = 1
-- 開啟自動提交（通常預設就是1）
SET AUTOCOMMIT = 1;

-- 查詢目前狀態
SELECT @@AUTOCOMMIT;

-- DML 操作（會自動提交）
INSERT INTO table_name VALUES (...);

UPDATE table_name
SET column = value
WHERE condition;

DELETE FROM table_name
WHERE condition;

#AUTOCOMMIT = 0
-- 關閉自動提交
SET AUTOCOMMIT = 0;

-- 開始交易（可寫可不寫，但建議寫）
START TRANSACTION;

-- DML 操作（暫時不會寫入）
INSERT INTO table_name VALUES (...);

UPDATE table_name
SET column = value
WHERE condition;

DELETE FROM table_name
WHERE condition;

#ROLLBACK

SET AUTOCOMMIT = 0;

START TRANSACTION;

UPDATE emp SET sal = 5000 WHERE empno = 7369;

ROLLBACK;  -- 或 COMMIT;

SET AUTOCOMMIT = 1;

COMMIT 確認交易

• 將資料儲存到資料庫中
• 釋放交易過程中的 Locking
• 刪除設定的儲存點(savepoint)
• 其他連線(sessions)的使用者將可以看到變更後的資料
• 無法再復原(Rollback)資料
• 此交易立即結束

START TRANSACTION;
INSERT INTO ...;
UPDATE ...;
DELETE FROM ...;
COMMIT;

ROLLBACK 放棄交易

• 取消交易中資料的變更
• 釋放交易過程中的 Locking
• 刪除設定的儲存點(savepoint)
• 其他連線(sessions)的使用者立即可對資料做操作(DML)
• 此交易立即結束

START TRANSACTION;
INSERT INTO ...;
UPDATE ...;
DELETE FROM ...;
ROLLBACK;

SAVEPOINT 設定交易儲存點

• 利用交易儲存點來設定交易Rollback的範圍
• 可透過交易儲存點將交易區分成數個段落
• 使用「SAVEPOINT name」在目前的交易中設定交易儲存點
• 若取同名,則先前的交易儲存點會被取消
• 使用「ROLLBACK TO name」回到指定的交易儲存點並將交易儲存點之後所產生的暫存資料遺棄
• 若省略name不寫,會Rollback整個交易
• 交易儲存點是邏輯上的觀念,無法顯示所設定的交易儲存點
• 使用「RELEASE SAVEPOINT name」移除交易儲存點
• ROLLBACK TO SAVEPOINT 可以將交易退回到指定儲存點位置,但不會結束交易

交易儲存點 (SAVEPOINT)

• 在交易中,可以有很多個statement,用邏輯的交易階段控制有助於交易的進行,儲存點即是實作的方法。
• 儲存點的名稱有分大小寫

START TRANSACTION; /* 交易控制開始 */
dml_statement;
...
SAVEPOINT TX1; /* 邏輯交易階段T1 */
dml_statement;
...
SAVEPOINT TX2; /* 邏輯交易階段T2 */
dml_statement;
...
SAVEPOINT TX3; /* 邏輯交易階段T3 */
dml_statement;
...
ROLLBACK TO TX3;
ROLLBACK TO TX2;
ROLLBACK TO TX1;
ROLLBACK; /* 交易控制結束 */

START TRANSACTION;

INSERT INTO ...;

SAVEPOINT A;

UPDATE ...;

SAVEPOINT B;

DELETE FROM ...;

-- 回到 B
ROLLBACK TO B;

-- 回到 A
ROLLBACK TO A;

-- 或全部取消
ROLLBACK;

資料定義語言（DDL / Data Definition Language）

儲存引擎 (Storage Engine)

• 儲存引擎(Storage Engine)是MySQL用來儲存資料的技術
• 不同的儲存引擎會有不同的資料儲存方式與運作特色
• MySQL提供多種儲存引擎,主要有三種

MyISAM:

MySQL早期預設的儲存引擎,支援的功能不像一般的資料庫那麼多(例如沒支援transaction);不過因為比較簡單,所以運作的效率相對也
比較好。

• 會建立以表格名稱為檔案名稱的三個檔案,附檔名分別為frm、MYD和MYI
• 以名為employee的表格為例
• employee.frm:儲存表格的結構與設定
• employee.MYD:儲存表格記錄資料
• employee.MYI:儲存表格索引資料
• MyISAM具有可攜性(portable)的特色,很容易複製一個資料庫到另外一台電腦
  的MySQL伺服器中
• 使用MyISAM時,MySQL並不會限制一個資料庫中可以包含的表格數量;但一個表格會在檔案系統中建立三個檔案,若超過作業系統對檔案數量或容量的限制,就不能再建立任何新的表格
• 使用MyISAM儲存引擎可以避免違反檔案大小的限制

InnoDB:

MySQL目前預設的儲存引擎,所提供的功能已和大型的商用資料庫一樣,像是交易(transaction)、記錄鎖定(row-level locking) 與外來鍵
(foreign keys)

• InnoDB有兩種儲存類型: 共用表空間和獨立表空間,預設為獨立表空間
• 可以用下列指令查看: show variables like ‘innodb_file_per_table’;
• 獨立表空間
• 創建資料庫時會建立一個以此資料庫名稱為名稱的資料夾
• 每個表格建立以表格名稱為檔案名稱的ibd檔,並將此檔案儲存於所屬資料庫之資料夾中
• 共用表空間
• 將所有表格的資料皆置於下面三個檔案「ibdata1」、「ib_logfile0」和「ib_logfile1」中
• 因為所有表格會使用同一個儲存空間,所以不同資料庫的表格資料也會儲存在一起
• InnoDB限制在這個共用的儲存空間中不能超過兩百萬個表格
• 使用此種儲存類型可以避免違反檔案數量的限制

MEMORY:

把資料儲存在紀憶體中,運作的效率是最快的;但只要MySQL伺服器關閉後,儲存的資料就會全部消失

• 和InnoDB一樣,在檔案系統中儲存的檔案只有frm檔
• MEMORY將所有表格的資料皆置於記憶體中
• 不適合儲存大量資料的表格,會耗用太多記憶體

字元集 (Character Set) 與字元序 (Collation)

• 在資料的存儲上,MySQL提供了不同的字元集支援。而在資料的對比操作上,則提供了不同的字元序支援

字元集 (Character Set)

• 字元集:定義了字元以及字元的編碼
• 為了要處理不同語言的文字,MySQL使用一套編碼來處理一種語言的文字,此一編碼謂之「Character Set」
• 例如MySQL有big5、latin1、ascii、utf8、utf16、…等,預設的字元集是utf8
• 不同的字元集會影響字串的儲存空間
• 使用「SHOW CHARSET」可顯示MySQL所有支援的字元集
• MySQL的字元集可設定在整個資料庫,或單一表格上,甚或某一特定的字串欄位;同理Collation

字元序 (Collation)

• 字元序:定義了字元的比較規則
• 在一個字元集中,所有字元的大小排序規則謂之
• 若以英文字母而言,大小順序是依照編碼的大小來決定,MySQL稱為「binary collation」
• 若大寫和小寫字母被當成是一樣的大小,然後再依照編碼來決定,MySQL稱為「case-insensitive collation」
• 以繁體中文而言,是沒有區分大小寫的
• 每個 Character set 都有數種 Collation 可搭配
• 使用「SHOW COLLATION」可顯示所有支援的 Collation
• 每一種字元集都有預設的Collation
• MySQL目前預設的字元集是「utf8mb4」,預設的Collation是「utf8mb4_0900_ai_ci」
• ci:case insensitive
• cs:case sensitive
• 是否區分大小寫的collation設定會影響排序的結果

#註:沒有指定 ENGINE 時,其預定的類型由參數 default-storage-engine 控制

### 使用現有資料表來建立新的資料表
- 使用子查詢建立新的資料表
CREATE TABLE TableName<br>AS<br>SELECT ColumnName,...<br>FROM TableName<br>WHERE ... {color="gray_bg"}
- 新創建之表格欄位名稱與子查詢中欄位同名
- 亦可在子查詢中使用欄位別名,此別名會成為新建資料表之欄位名稱
- 子查詢的資料會新增到新建的資料表中,若不需要資料則可在子查詢中設定 WHERE 1= 0
- 只有不為空值的檢查條件會被保留在新的資料表中,其餘的Constraint均不存在
```sql
CREATE TABLE EMP10
AS
SELECT EMPNO, ENAME, JOB, SAL
FROM EMP
WHERE DEPTNO = 10;
Query OK, 3 rows affected (0.26 sec)
Records: 3 Duplicates: 0 Warnings: 0

使用LIKE子句建立資料表

CREATE TABLE TableName LIKE Source_TableName; {color=“gray_bg”}

• 使用既存的資料表結構建立新資料表
• 此種方法不會有資料的新增,只有結構定義部份
• 無法指定欄位名稱,和來源資料表欄位同名
• 要新增資料請再使用Insert into with Sub-Query方法

CREATE TABLE emp1 LIKE emp;

衍生欄位 (Generated Column)

col_name data_type [GENERATED ALWAYS] AS (expression)
[VIRTUAL | STORED] [UNIQUE [KEY]] [COMMENT comment]
[[NOT] NULL] [[PRIMARY] KEY] {color=“gray_bg”}

• MySQL 5.7.6開始支持衍生欄位這個特性
• 所谓衍生欄位,就是資料表中這一欄位是由其他欄位計算而得
• 例如: 直角三角形的斜邊長度可由另外兩個邊的邊長計算出來
• 例如: 訂單項目中可由商品單價及購買數量計算出此項目小計金額
• 衍生欄位儲存方式有兩種 :
• VIRTUAL: 衍生欄位的值不會儲存,每次查詢時即時計算,此為預設方式
• STORED: 衍生欄位的值永久儲存,每次新增或修改時會計算出其值並儲存

CREATE TABLE triangle (
sidea DOUBLE,
sideb DOUBLE,
sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb));

#不用新增sidec 就會生出sidec
INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8);

衍生欄位使用規則

• 支援 MySQL 內建的各種函數,包括MIN,MAX,SUBSTRING 等函數
• 不支援 CONNECTION_ID(),NOW() 這一類函數,因為這些函數的返回值會隨時變化
• 不支援預存程序和預存函數
• 不支援子查詢
• 可以針對普通列的資料進行計算,也可以使用其他衍生欄位的資料進行計算
• SQL_MODE 的設置可能會影響計算的結果,使用時需要注意
• CREATE TABLE … LIKE … 生成的新表會複製衍生欄位的定義
• 可以使用衍生欄位作為外鍵
• 可以使用衍生欄位創建索引。

enum 資料型態

• MySQL 中的 ENUM 資料型態是一個字串物件,它僅允許儲存有限的字串值,而這些允許儲存的有限值是在創建表格時宣告
• ENUM 值在內部儲存為整數 (1、2、3 等),這比儲存字串更節省空間
• MySQL 根據 ENUM 值的索引號(而不是實際的字串)對其進行排序
• ENUM 欄位最多可包含 65,535 個不同的元素。如果列舉值數量不超過255 個,則佔用1 個位元組;如果超過 255 個,則佔用2 個位元組
• ENUM 欄位可以接受各種資料類型的值,包括整數、浮點數、小數和字串
• 如果想要修改列舉值/成員,必須使用 ALTER TABLE 指令重建整個表格來完成
• MySQL 中的 ENUM 適合具有有限且不變的值集的欄位
• ENUM 是一個字串物件,其值是從建立欄位時所定義的允許值清單中挑選的 column_name ENUM(‘value1’, ‘value2’, …, ‘valueN’); {color=“gray_bg”}

CREATE TABLE tickets (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
priority ENUM('Low', 'Medium', 'High') NOT NULL);

• 新增資料時,其值必須從建立欄位時所定義的允許值清單中挑選,也可以用索引值指定
• 若此欄位設定 NOT NULL,且新增資料時沒有指定其值, 則使用第一個列舉值為其預設值

INSERT INTO tickets(title, priority)
VALUES('Scan virus for computer A', 'High');

INSERT INTO tickets(title, priority)
VALUES('Upgrade Windows OS for all computers', 1);

INSERT INTO tickets(title)
VALUES('Refresh the computer of Ms. Lily');

• 查詢資料時,可比對其字串值或索引值。排序則依據索引值

SELECT * FROM tickets
WHERE priority = 'High'; -- priority = 1

#排序
SELECT * FROM tickets
ORDER BY priority DESC;

資料檢查條件 (Database Constraints)

• 用來檢查資料表的資料(data validation)

欄位預設值 (Default)

• 設定欄位預設值
• expr 需是常值(Literial),不可以是函數或運算式
• MySQL資料庫中,所有欄位皆有預設值,如果不設定其預設值為空值(NULL) ColumnName type [NOT NULL]|[NULL] DEFAULT expr {color=“gray_bg”}

CREATE TABLE EMP
( ...,
BRITHDATE DATE,
SEX CHAR(1) DEFAULT 'M',
...
);

不允許空值 (NOT NULL)

• 不為空值檢查(一定要輸入資料)
• NOT NULL: 該column所有的rows均要輸入資料 ColumnName type [NOT NULL]|[NULL] {color=“gray_bg”}

CREATE TABLE EMP
( EMPNO SMALLINT NOT NULL,
ENAME VARCHAR(10) NOT NULL,
JOB VARCHAR(9),
MGR SMALLINT,
HIREDATE DATE,
SAL INT,
COMM INT,
DEPTNO SMALLINT
);

主鍵 (Primary key)

• 主鍵(PK):
• 一個資料表中只能有一個主鍵
• 適用一個欄位構成的主鍵 ColumnName type PRIMARY KEY {color=“gray_bg”}

CREATE TABLE DEPT122
(
DEPTNO SMALLINT(4) PRIMARY KEY, #在欄位階層設定
DNAME VARCHAR(14),
LOC VARCHAR(13)
);

• 複合主鍵(由多個欄位構成)
• CONSTRAINT const_name: 為主鍵命名 CREATE TABLE table
  ( …,
  ColumnName type NOT NULL,
  …,
  [CONSTRAINT const_name] PRIMARY KEY(column,…)
  ); {color=“gray_bg”}

CREATE TABLE ITEM12
( ORDID INT NOT NULL,
ITEMID SMALLINT NOT NULL,
.. ..
CONSTRAINT PK_ITEM12_ORDID_ITEMID PRIMARY KEY(ORDID,ITEMID)
); #在資料表階層 因為有多個欄位都是pk 寫在最後面

多個pk 要合起來一起看，所以要看多個pk的組合為唯一

PK_ITEM12_ORDID_ITEMID
種類_表格_欄位1_欄位2

AUTO_INCREMENT 選項

• 欄位資料自動編號
• 欄位的資料型態必需是數值型態
• 預設起始值是1
• 需與Primary key或Unique key配合使用
• 一個表格只能設一個
• LAST_INSERT_ID() : 取得最近一次自動產生的編號

#起始值1
CREATE TABLE ORD2
( ORDID INT AUTO_INCREMENT PRIMARY KEY,
ORD_DATE DATE,
...
);

• 欄位自動編號(設定起始號碼)
• start 設定起始號碼 CREATE TABLE TableName
  (ColumnName type AUTO_INCREMENT,
  …,
  ) AUTO_INCREMENT = start ; {color=“gray_bg”}

CREATE TABLE ORD2
( ORDID INT AUTO_INCREMENT PRIMARY KEY,
ORD_DATE DATE,
...,
) AUTO_INCREMENT = 101 ;

AUTO_INCREMENT 選項

• 新增資料列(INSERT)時,對於AI之欄位若不給值、給定NULL或給定0,則系統自動編號
• 新增資料列(INSERT)時,對於AI之欄位若設定重複之值,設出現系統錯誤
• 新增資料列(INSERT)時,對於AI之欄位若設定值大於所有已存在之值(例:20),則下次新增時,則從此值加1(例: 21)開始
• 若在交易中新增資料列(INSERT)時,當取消交易(ROLLBACK),AI值不會回復
• 修改資料(UPDATE)時,對於AI之欄位若設定重複之值,設出現系統錯誤
• 修改資料(UPDATE)時,對於AI之欄位若設定值大於所有已存在之值(例:20),則下次新增時,則從此值加1(例: 21)開始
• 刪除資料時(DELETE)時,刪除資料列之AI欄位值,不會回收再使用
• 對於每個連線, LAST_INSERT_ID() 之值,不受其他連線之影響

唯一鍵 (Unique key)

ON UPDATE CASCADE

CREATE TABLE emp (
eno char(3) PRIMARY KEY,
ename varchar(30),
dno INT,
FOREIGN KEY(dno) REFERENCES dept(dno)
ON UPDATE CASCADE
);

UPDATE dept SET dno = 300
WHERE dno = 200;

ON UPDATE SET NULL

CREATE TABLE emp (
eno char(3) PRIMARY KEY,
ename varchar(30),
dno INT,
FOREIGN KEY(dno) REFERENCES dept(dno)
ON UPDATE SET NULL
);

UPDATE dept SET dno = 300
WHERE dno = 200;

ON DELETE RESTRICT

CREATE TABLE emp (
eno char(3)PRIMARY KEY,
ename varchar(30),
dno INT,
FOREIGN KEY(dno) REFERENCES dept(dno)
ON DELETE RESTRICT
);

DELETE FROM dept
WHERE dno = 200;

ON DELETE CASCADE

CREATE TABLE emp (
eno char(3) PRIMARY KEY,
ename varchar(30),
dno INT,
FOREIGN KEY(dno) REFERENCES dept(dno)
ON DELETE CASCADE
);

DELETE FROM dept
WHERE dno = 200;

ON DELETE SET NULL

CREATE TABLE emp (
eno char(3) PRIMARY KEY,
ename varchar(30),
dno INT,
FOREIGN KEY(dno) REFERENCES dept(dno)
ON DELETE SET NULL
);

DELETE FROM dept
WHERE dno = 200;

資料檢測 (Check) - (MySQL 8.0.16以後版本支援)

單一欄位限制

無法新增出現錯誤訊息

INSERT INTO parts(part_no, description,cost,price)
VALUES('A-001','Cooler',0,-100);

Error Code: 3819. Check constraint 'parts_chk_2' is
violated.

多欄位限制

SHOW CREATE TABLE parts;

check(布林值)

CREATE TABLE parts
( part_no VARCHAR(18) PRIMARY KEY,
description VARCHAR(40),
cost DECIMAL(10,2 ) NOT NULL CHECK (cost >= 0),
price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
CONSTRAINT parts_chk_price_gt_cost CHECK(price >= cost)
);

無法新增出現錯誤訊息

INSERT INTO parts(part_no, description,cost,price)
VALUES('A-001','Cooler',200,100);

Error Code: 3819. Check constraint 'parts_chk_price_gt_cost' is
violated.

• 可在建立資料表時指定或於資料表建立後加入
• 欄位階層(Column Level)
• 資料表階層(Table Level)

修改結構

資料表的維護 (Managing Tables)

• 資料表可以修改其結構
• 新增、修改、刪除欄位
• 新增、刪除資料檢查條件
• 修改資料表名稱或欄位名與註解
• Rename
• Comment
• 可以刪除資料表的結構或資料
• 刪除資料
• DELETE , TRUNCATE
• 刪除資料及結構
• DROP TABLE

新增一個新欄位 (Adding a Column)

• 加入新欄位
• Column_Definition 欄位的定義
• FIRST 新增在第一個欄位
• AFTER column 新增在指定欄位後
• 預設為新增在最後一個欄位之後 ALTER TABLE TableName
  ADD [COLUMN] Column_Definition [FIRST|AFTER column];
  OR
  ALTER TABLE TableName
  ADD [COLUMN] (Column_Definition,…); {color=“gray_bg”}

ALTER TABLE EMP10
ADD COLUMN MGR SMALLINT;

ALTER TABLE EMP10
ADD COLUMN HIREDATE DATE AFTER JOB;

--新增為第一個欄位
ALTER TABLE emp10
ADD PHONE varchar(12) DEFAULT '02-66316710' FIRST;

新增多個新欄位 (Adding Columns)

ALTER TABLE EMP10
ADD COLUMN(COMM INT, DEPTNO SMALLINT, EMAIL VARCHAR(200));

修改欄位定義 (Modifying a column)

• 修改欄位定義
• 修改欄位預設值
• 修改欄位資料型態
• 修改欄位順序 ALTER TABLE TableName
  ALTER [COLUMN] column {SET DEFAULT literal|DROP DEFAULT}; {color=“gray_bg”} ALTER TABLE TableName
  MODIFY [COLUMN] column_definition [FIRST|AFTER column]; {color=“gray_bg”}
• 修改欄位預設值 (取消預設值設定)

ALTER TABLE EMP10
ALTER PHONE DROP DEFAULT;

• 修改欄位資料型態/順序

ALTER TABLE EMP10
MODIFY COLUMN MGR INT AFTER JOB;

• 修改欄位資料型態/ NOT NULL

ALTER TABLE EMP10
MODIFY COLUMN ENAME VARCHAR(20) NOT NULL;

更改欄位名稱 (Rename the Name of Column)

• 更改欄位名稱/定義
• old_column 舊欄位名稱
• New_column_definition 新欄位定義 ALTER TABLE TableName
  CHANGE [COLUMN] old_column New_column_definition; {color=“gray_bg”}
• 更改欄位名稱

ALTER TABLE EMP10
CHANGE COLUMN SAL SALARY SMALLINT;

刪除欄位 (Dropping a column)

• 欄位有無資料皆可刪除,資料表最少留一個欄位 ALTER TABLE TableName
  DROP [COLUMN] ColumnName; {color=“gray_bg”}

ALTER TABLE dept1
DROP COLUMN loc;

新增資料檢查條件 (Adding a Constraint)

• 加入資料檢查條件
• const_name 限制條件名稱
• constraint_type 限制條件種類
• NOT NULL constraint 請用MODIFY句子 ALTER TABLE TableName
  ADD [CONSTRAINT const_name] constraint_type; {color=“gray_bg”}
• 加入資料檢查條件

/* 新增主鍵 */
ALTER TABLE EMP10
ADD CONSTRAINT PK_EMP10_EMPNO PRIMARY KEY(EMPNO);
/* 新增外來鍵 */
ALTER TABLE EMP10
ADD CONSTRAINT FK_EMP10_MGR FOREIGN KEY(MGR)

REFERENCES EMP10(EMPNO);

/* 新增唯一鍵 */
ALTER TABLE EMP10
ADD CONSTRAINT UK_EMP10_EMAIL UNIQUE(EMAIL);

刪除資料檢查條件 (Drop a Constraint)

ALTER TABLE table
DROP [PRIMARY KEY|FOREIGN KEY ConstraintName | {color=“gray_bg”} INDEX ConstraintName]; {color=“gray_bg”}

/* 刪除外來鍵 */
ALTER TABLE EMP10
DROP FOREIGN KEY FK_EMP10_MGR;
/* 刪除主鍵 */
ALTER TABLE EMP10
DROP PRIMARY KEY;

/* 刪除唯一鍵 */
ALTER TABLE EMP10
DROP INDEX UK_EMP10_EMAIL;

• 刪除UNIQUE key

#1. 新增 UNIQUE 約束
ALTER TABLE EMP10
ADD CONSTRAINT EMP10_ENAME_UK UNIQUE(ENAME);

#結果：

#ENAME 欄位 不能重複
#系統建立一個唯一索引 EMP10_ENAME_UK

#2. 嘗試更新為重複值

UPDATE EMP10
SET ENAME='CLARK'
WHERE EMPNO=7934;

#錯誤:
#ERROR 1062: Duplicate entry 'CLARK'
#表中已經有 ENAME = 'CLARK'
#UNIQUE 限制不允許重複

#3. 刪除 UNIQUE 約束
ALTER TABLE EMP10
DROP INDEX EMP10_ENAME_UK;

#結果：

#移除唯一限制
#ENAME 可以重複

#4. 再次更新

UPDATE EMP10
SET ENAME='CLARK'
WHERE EMPNO=7934;

#結果：

#成功更新
#因為已沒有 UNIQUE 限制

資料表改名 (Managing the Name of Table)

• 資料表重新命名 ALTER TABLE TableName
  RENAME [TO] new_table; {color=“gray_bg”}
• 將 EMP10 改名為 EMP10A

ALTER TABLE EMP10
RENAME TO EMP10A;

更改資料表儲存引擎類型

• 更改儲存引擎類型 ALTER TABLE TableName
  ENGINE = type; {color=“gray_bg”}

ALTER TABLE EMP10A
ENGINE = INNODB;