【DB學習筆記】SQL(Structured Query Language，結構化查詢語言) ─ (2)

一、約束

二、JOIN查詢-多表查詢

三、子查詢

四、engine(儲存引擎)

五、transaction(事務)

六、index(索引)

七、view(視圖)

八、Database Normalization(資料庫正規化)

九、SQL Injection

延續上一篇文章，本文將繼續整理SQL命令和相關概念。為了方便對照，我將上一篇的示範表格貼過來這篇：

-- 部門資料表
CREATE TABLE dept(
    deptno INT PRIMARY KEY,
    dname VARCHAR(10),
    loc VARCHAR(10)
);
INSERT INTO dept (deptno,dname,loc) VALUES 
(10,'ACCOUNTING','NEW YORK'),
(20,'RESEARCH','DALLAS'),
(30,'SALES','CHICAGO '),
(40,'OPERATIONS','BOSTON');

-- 員工資料表
CREATE TABLE emp(
    empno INT PRIMARY KEY,
    ename VARCHAR(10),
    job VARCHAR(10),
    mgr INT,
    hiredate DATE,
    sal INT,
    comm INT,
    deptno INT,
    FOREIGN KEY(deptno) REFERENCES dept(deptno)
);
INSERT INTO emp VALUES 
(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20),
(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30),
(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30),
(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20),
(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30),
(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30),
(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10),
(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20),
(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10),
(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30),
(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20),
(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30),
(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20),
(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

-- 薪資級距表
CREATE TABLE salgrade(
    grade INT PRIMARY KEY,
    losal INT,
    hisal INT
);
INSERT INTO salgrade (grade,losal,hisal) VALUES 
(1,700,1200),
(2,1201,1400),
(3,1401,2000),
(4,2001,3000),
(5,3001,9999);

一、約束

在創建表的時候，約束能保證表的完整性和有效性。

• NOT NULL：非空約束表示該欄位值不可為空

CREATE TABLE t_vip(
    id INT,
    name VARCHAR(255) NOT NULL
);

INSERT INTO t_vip(id) VALUES(3); -- Error Code: 1364. Field 'name' doesn't have a default value

• UNIQUE：唯一性約束表示欄位值不可重複

CREATE TABLE t_vip(
    id INT,
    name VARCHAR(255) UNIQUE
);

INSERT INTO t_vip VALUES(1, 'Jack');
INSERT INTO t_vip VALUES(2, 'Jack'); -- name為Jack已重複，Error Code: 1062. Duplicate entry 'Jack' for key 'name'
INSERT INTO t_vip(id) VALUES(3); -- name可以為null但不可重複

-- 多個欄位聯合唯一
CREATE TABLE t_vip(
    id INT,
    name VARCHAR(255),
    UNIQUE(id, name) -- 表示id和name排列組合後的結果不可重複
);

-- 在mysql中，NOT NULL和UNIQUE一起使用，會默認該欄位為PK(主鍵)
CREATE TABLE t_vip(
    id INT,
    name VARCHAR(255) NOT NULL UNIQUE
);

-- 可以使用DESC觀察name欄位是否被自動設為PK
DESC t_vip;

• 主鍵約束：受主鍵約束的欄位，其值代表每一列紀錄的唯一識別。

-- 單一主鍵
CREATE TABLE t_vip(
    id INT PRIMARY KEY,
    name VARCHAR(255)
);

-- 複合主鍵
CREATE TABLE t_vip(
    id INT,
    name VARCHAR(255),
    PRIMARY KEY(id,name) -- 視為一個主鍵
);

-- 不可有多重主鍵，Error Code: 1068. Multiple primary key defined
CREATE TABLE t_vip(
    id INT PRIMARY KEY,
    name VARCHAR(255) PRIMARY KEY
);

-- 主鍵值自動生成
CREATE TABLE t_vip(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255)
);

INSERT INTO t_vip(name) VALUES('Jack'); -- id自動生成
INSERT INTO t_vip(name) VALUES('Jack'); -- id自動生成
INSERT INTO t_vip(name) VALUES('Jack'); -- id自動生成

-- 可以使用DESC觀察
DESC t_vip;

• 外鍵約束：受外鍵約束的欄位，其值關聯至其他表的主鍵值，用於確保資料間的關聯性和一致性

-- 假設沒有外鍵約束，雖然可以在學生表中寫下班級id，但無法保證不會寫到一個根本不存在的班級id，導致關聯出錯
CREATE TABLE class(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255)
);

CREATE TABLE student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    cid INT,
    FOREIGN KEY(cid) REFERENCES class(id) -- 注意：引用的欄位不一定要是PK，但須unique
);

INSERT INTO class(name) VALUES('classA'); -- 創建一個新班級資料，其id為1
INSERT INTO student(name,cid) VALUES('Jack',2); -- 創建一個新學生資料，其cid為2，根本不存在
-- ↑ Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`learning`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `class` (`id`))


-- 被關連到的表為父表，因此class為父表、student為子表
-- 創建表的順序？ → 先創建父表、再創建子表
-- 刪除表的順序？ → 先刪除子表、再刪除父表
-- 插入資料的順序？ → 先插入父表、再插入子表
-- 刪除資料的順序？ → 先刪除子表、再刪除父表

二、JOIN查詢-多表查詢

• INNER JOIN 只會返回兩個資料表中符合連接條件的資料，即只返回兩個表中共同存在的資料，而不會包含沒有匹配的資料。

-- 查詢每位員工所在的部門名稱，並顯示員工名和部門名
SELECT e.ename, d.dname
FROM emp e -- 取別名，等同於emp AS e
INNER JOIN dept d -- INNER可以省略
ON e.deptno = d.deptno; -- 條件是等價關係，所以被稱為等價連接

-- 查詢每位員工的薪資等級，並顯示員工名、薪資、薪資等級
SELECT e.ename, e.sal, s.grade
FROM emp e
JOIN salgrade s
ON e.sal BETWEEN s.losal AND s.hisal; -- 條件不是等價關係，所以被稱為非等價連接

-- 查詢每位員工的主管是誰，並顯示兩者的名子 ─ self join
SELECT a.ename, b.ename AS 'leader'
FROM emp a
JOIN emp b
ON a.mgr = b.empno;

• OUTER JOIN：允許從多個資料表中合併資料，並保留缺少匹配的資料，也可以看作某張表會被當成主表，這張主表會和其他表匹配資料，即使匹配到的資料為空，位於主表中的該筆記錄仍會留下。

-- 查詢每位員工所在的部門名稱，並顯示員工名和部門名
SELECT e.ename, d.dname
FROM emp e 
RIGHT OUTER JOIN dept d -- OUTER可以省略。RIGHT表示將JOIN右側表作為主表
ON e.deptno = d.deptno;

-- 任何RIGHT OUTER JOIN都有LEFT OUTER JOIN的寫法，反之亦然
SELECT e.ename,d.dname
FROM dept d
LEFT OUTER JOIN emp e -- OUTER可以省略。LEFT表示將JOIN左側表作為主表
ON e.deptno = d.deptno;

• 笛卡爾乘積

-- 兩張表join時，若沒有加入任何限制條件，其結果會是兩張表的總列數乘積，稱為笛卡爾乘積
-- 原因是表在連接的時候是表與表間的一條條紀錄互相比對
SELECT ename, dname FROM emp, dept; -- 沒有寫下條件

-- 避免笛卡爾乘積：加入條件篩選
SELECT ename, dname FROM emp, dept WHERE emp.deptno = dept.deptno;

-- 思考：加入條件後，匹配次數有變少嗎？ → 沒有，以範例來說，雖然結果有14筆，但比對次數依然是56次(14*4)
-- 換句話說，減少表的連接次數也是會提高效率的

• UNION

/* 對於表連接來說，總匹配次數符合笛卡爾乘積，而UNION可以使用拼接的方法來減少匹配次數，
在合適的應用場景下，UNION的效率會比JOIN來得高 */
-- A 連接 B 連接 C，假設A、B、C各10條紀錄，JOIN 匹配次數如下：
-- 10 * 10 * 10 = 1000次
-- 而UNION 匹配次數如下：
-- A 連接 B： 10 * 10 = 100次
-- A 連接 C： 10 * 10 = 100次
-- 100 + 100 = 200次

-- UNION等同於把乘法運算變成加法運算
-- 注意： 1. 欲合併的結果其行數需相同，在mysql中，只要行數相同、欄位名不同也是可以合併的
SELECT ename, job
FROM emp
WHERE job = 'MANAGER'
UNION
SELECT ename, sal
FROM emp
WHERE job = 'SALESMAN'

• 其他例題

-- 多表連接
SELECT ...
FROM a
JOIN b
ON a和b的連接條件
JOIN c
ON a和c的連接條件
RIGHT JOIN d
ON a和d的連接條件

-- 找出每位員工的名子、薪資、部門名稱、薪資級距
SELECT e.ename, e.sal, d.dname, s.grade
FROM emp e
JOIN dept d
ON e.deptno = d.deptno
JOIN salgrade s
ON e.sal BETWEEN s.losal AND s.hisal;

-- 找出每位員工的名子、薪資、部門名稱、薪資級距、上司名子
SELECT e.ename, e.sal, d.dname, s.grade, l.ename
FROM emp e 
JOIN dept d
ON e.deptno = d.deptno
JOIN salgrade s
ON e.sal BETWEEN s.losal AND s.hisal
LEFT JOIN emp l
ON e.mgr = l.empno;

三、子查詢

• 子查詢接在SELECT後面

-- 找出每個員工的名子、部門編號和部門名稱
SELECT e.ename, e.deptno,
        (SELECT d.dname
	 FROM dept d
	 WHERE e.deptno = d.deptno
	 ) AS dname
FROM emp e;

-- 注意：當子查詢返回的結果超過1筆紀錄，會報錯 Error Code: 1242. Subquery returns more than 1 row
SELECT e.ename, e.deptno,
	(SELECT d.dname
	 FROM dept d
	 ) AS dname
FROM emp e;

• 子查詢接在WHERE後面

-- 找出除最低薪資以外的所有員工的名子和薪資
SELECT ename,sal FROM emp WHERE sal > (SELECT MIN(sal) FROM emp);

• 子查詢接在FROM後面

-- 找出每個職位的平均薪資屬於哪個薪資級距
SELECT t.*, s.grade
FROM (SELECT job,AVG(sal) AS avgsal FROM emp GROUP BY job) t
JOIN salgrade s
ON t.avgsal BETWEEN s.losal AND s.hisal;

四、engine(儲存引擎)

在MySQL中，儲存引擎是用來決定表如何儲存和管理資料的一個重要設定。且不同的儲存引擎支援不同的功能，在性能、佔用資源、並發性等方面表現可能會有所不同，選用儲存引擎取決於使用者的需求和資料表需要什麼特性。

-- 查看emp表的狀態
SHOW CREATE TABLE emp;
-- 結果如下，請特別留意最後一列的內容：
/* 'CREATE TABLE `emp` (
  `empno` int(11) NOT NULL,
  `ename` varchar(10) DEFAULT NULL,
  `job` varchar(10) DEFAULT NULL,
  `mgr` int(11) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `sal` int(11) DEFAULT NULL,
  `comm` int(11) DEFAULT NULL,
  `deptno` int(11) DEFAULT NULL,
  PRIMARY KEY (`empno`),
  KEY `deptno` (`deptno`),
  CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci'
*/

-- 建表時可以在「)」小括號右側指定ENGINE(儲存引擎)、DEFAULT CHARSET(默認編碼方式)，例如：
CREATE TABLE class(
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- MySQL中查看有哪些儲存引擎
SHOW ENGINES; -- 我使用的是MySQL 8.0.15，共支持9個儲存引擎

• InnoDB儲存引擎：MySQL默認的儲存引擎。由於支持事務以保證安全，效率相較於其他儲存引擎來說不高，也不能壓縮、不能轉唯讀，所以無法有效節省空間。
• MyISAM儲存引擎：使用三個文件表示一個表。分別是格式文件(mytable.frm)─儲存表結構的定義、資料文件(mytable.MVD)─儲存表中的列的內容、索引文件(mytable.MVI)─儲存表中的索引。可被轉換為壓縮的、唯讀表，以利節省空間。
• MEMORY儲存引擎：表的資料和索引都存在記憶體中，不能存TEXT和BLOB類型，以前被稱為HEAP引擎，查詢效率是最高的，但安全性不佳。

五、transaction(事務)

當一個事務代表一個完整的業務邏輯時，意味著這個事務不是全部成功完成，就是全部失敗回滾，不會出現部分成功和部分失敗的情況。這有助於保持資料庫的一致性和完整性。

舉例來說，考慮一個銀行轉帳的業務操作，包含兩個步驟：

1. 從一個帳戶扣除金額。
2. 將該金額轉入另一個帳戶。

在這個例子中，這兩個步驟構成了一個完整的業務邏輯。如果其中一個步驟出現錯誤，整個事務應該回滾，以保持資料庫的一致性。例如：第二步驟中轉帳過程出現錯誤，則整個事務應該回滾，回滾應包含第一步驟中從某帳戶扣除金額，從而確保資料庫中的帳戶餘額保持一致。只有當兩個步驟都成功完成時，整個事務才會提交，並且資料庫中的帳戶餘額得到更新，這個過程就是一個完整的業務邏輯。

只有DML(Data Manipulation Language，資料操作語言)與事務是有關係的，因為它們用於管理資料、會對資料進行增刪改：

• INSERT：將資料插入表中。
• UPDATE：更新表中現有的資料。
• DELETE：從表中刪除記錄。

在InnoDB中提供一組用來記錄事務性活動的日誌文件(redo log)。在執行事務時，每一條DML操作都會被記錄到「事務性活動的日誌文件」中。事務執行期間，我們可以選擇提交事務或回滾事務，提交事務使用commit;，它會將變動資料永久儲存到資料庫表中，代表事務的成功結束；而回滾事務使用rollback;，它會撤銷之前所有的DML操作並清空事務性活動的日誌文件，表示事務的失敗結束，留意每次的回滾只能回滾到上一次的提交點。如下例：

-- 提交事務：
START TRANSACTION; -- 方法一
BEGIN; -- 方法二

-- 在此進行一系列的DML操作
UPDATE dept SET loc = 'LONDON' WHERE deptno = 10;
INSERT INTO dept (deptno, dname, loc) VALUES (50, 'MARKETING', 'PARIS');
COMMIT; -- 將所有DML操作永久保存到資料庫中



-- 回滾事務：
START TRANSACTION; -- 方法一
BEGIN; -- 方法二

-- 在此進行一系列的DML操作
UPDATE dept SET loc = 'SYDNEY' WHERE deptno = 20;
DELETE FROM dept WHERE deptno = 30;
ROLLBACK; -- 撤銷所有DML操作，恢復到事務開始前的狀態

1. 事務的四個特性─ACID

A：原子性(Atomicity)確保事務是不可再分的最小工作單元。

C：一致性(Consistency)要求在同一個事務中的所有操作必須全部成功或全部失敗，以確保資料的一致性。

I：隔離性(Isolation)確保不同事務之間是隔離的，就像教室A和教室B之間有一道牆。當事務A在操作一張表時，另一個事務B也在操作同一張表，它們彼此不會互相干擾。

D：持久性(Durability)是事務結束後的保證，一旦事務提交，相關的資料將被永久保存在硬碟上，不會因為其他因素丟失。

2. 事務的4個隔離級別

-- 查看隔離級別
SELECT @@GLOBAL.transaction_isolation; -- GLOBAL
SELECT @@SESSION.transaction_isolation; -- SESSION

-- 設定隔離級別
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; --其他值：READ COMMITTED、REPEATABLE READ、SERIALIZABLE

-- 創建user表
CREATE TABLE user (
    name VARCHAR(50)
);

-- 以下驗證都是使用root帳號登入，並以兩個cmd視窗代表A、B兩個不同地方的連線

• read uncommitted：最低級別。事務A可以讀取到事務B尚未提交的資料，即dirty read現象，只存在於理論上的說法，實際上不可能使用這樣的級別。 → 還沒提交就能讀到。

驗證read uncommitted

(1) 在第一個cmd視窗中，開啟一個transaction

(2) 搜尋user table，結果為empty

(3) 在第二個cmd視窗中，開啟另一個transaction

(4) 搜尋user table，結果為empty

(5) 插入一筆name為Jack的資料─注意這邊沒有commit

(6) 在第一個cmd視窗中，搜尋user table，出現一筆name為Jack的資料

(7) 搜尋現在的狀態是read uncommitted

• read committed：事務A只能讀取到事務B已經提交的資料，該級別解決了dirty read現象。但有個問題是不可重複讀取資料，指的是在開啟事務之後，假設第一次讀取資料時的紀錄共3筆，而第二次讀取到了4筆，稱為「不可重複讀取」，因此也代表每次讀到的資料都是貼近真實的、存在的數據。 → 提交後才能讀到。

驗證read committed

(1) 在第一個cmd視窗中，開啟一個transaction

(2) 搜尋user table，結果為empty

(3) 在第二個cmd視窗中，開啟另一個transaction

(4) 插入一筆name為Jack的資料─注意這邊沒有commit

(5) 在第一個cmd視窗中，搜尋user table，結果為empty

(6) 在第二個cmd視窗中，提交commit;

(7) 在第一個cmd視窗中，搜尋user table，出現一筆name為Jack的資料

(8) 搜尋現在的狀態是read committed

• repeatable read：事務A開啟之後，不管過了多久，只要事務沒有結束，每一次在事務A中讀取到的資料都是一致的。即使事務B在這期間將資料做了增刪改並且提交了，事務A讀取到的資料還是沒有發生改變，這就是可重複讀取，而這可能會導致每一次讀取到的資料都不夠真實。例如：銀行從下午三點執行一項轉帳事務直到下午五點，這期間若有人來做存款、提款，表示開啟其他事務，而轉帳事務使用repeatable read可以確保我從執行開始至結束讀取到的資料都是相同的、不受其他事務干擾的。 → 提交後也無法讀到，只能讀取到開啟事務當下的資料。

驗證repeatable read

(1) 在第一個cmd視窗中，開啟一個transaction

(2) 搜尋user table，結果為empty

(3) 在第二個cmd視窗中，開啟另一個transaction

(4) 插入一筆name為Jack的資料

(5) 提交commit;

(6) 在第一個cmd視窗中，搜尋user table，結果為empty

(7) 在第二個cmd視窗中，再開啟另一個transaction

(8) 插入一筆name為Allison的資料

(9) 提交commit;

(10) 在第一個cmd視窗中，搜尋user table，結果為empty

(11) 插入一筆name為Wen的資料

(12) 提交commit;

(13) 搜尋user table，出現三筆資料

(14) 在第二個cmd視窗中，搜尋現在的狀態是repeatable read

• serializable：最高級別，表示事務排隊。雖然解決了前面三個級別的所有問題，但是效率最低、無法併發。 → 直到我操作的事務結束為止，無法發起其他事務。

驗證serializable

(1) 在第一個cmd視窗中，開啟一個transaction

(2) 搜尋user table，結果為empty

(3) 在第二個cmd視窗中，開啟另一個transaction

(4) 搜尋user table，結果會被卡住，因為第一個視窗中的transaction尚未結束

(5) 在第一個cmd視窗中，插入一筆name為Jack的資料

(6) 提交commit;

(7) 在第二個cmd視窗中，等第一個視窗中的transaction commit後，結果才會出來(9.82 sec)

(8) 提交commit;

(9) 搜尋現在的狀態是serializable

💡 MySQL :: MySQL 8.0 Reference Manual :: 13.3.7 SET TRANSACTION Statement
💡 MySQL :: MySQL 8.0 Reference Manual :: 5.1.9 Using System Variables-global&session

六、index(索引)

索引是為了提高查詢效率而在資料表的欄位上添加的，就像一本書的目錄，可以縮小掃描範圍，一張表的一個欄位或多個欄位聯合起來都可以添加索引。MySQL的查詢方式主要有全表掃描和根據索引檢索，我們可以想像眼前有一本字典，想要查詢某個字時，可以從頭一頁頁看過或是根據部首、筆劃數、注音等不同的索引快速搜索，而索引在MySQL是使用B-Tree資料結構遵循左小右大原則存放。

-- 舉例：
SELECT * FROM user WHERE id = 101; -- 因為id是PK會自動創建索引，所以mysql會根據索引物件idIndex來搜尋
-- 1. 通過idIndex找到101後，直接鎖定物理儲存編號0x6666
-- 2. 透過0x6666找到name為Ken的這筆紀錄，不需要去表格中一一掃過每個值(全表掃描)

-- 注意：表中的欄位順序是不會變動的，但索引會重新排列

索引的運作概念

💡 MySQL :: MySQL 8.0 Reference Manual :: 8.3.1 How MySQL Uses Indexes

在任何資料庫中，主鍵欄位上會自動添加索引物件；另外，MySQL中，若一個欄位有unique約束，也會自動創建索引物件。每張表的記錄在硬碟存儲上都有一個硬碟的物理存儲編號，在MySQL中，索引是一個單獨的物件，不同的存儲引擎以不同的形式存在，如：MyISAM存儲引擎索引存儲在.MYI文件中、InnoDB存儲引擎索引存儲在名為tablespace的邏輯名稱中、MEMORY存儲引擎索引則存儲在記憶體中。而無論存儲在何處，索引在MySQL中都是以樹的形式存在。

針對欄位添加索引的考慮條件如下：

(1) 資料量龐大(通常需測試當前硬體環境以區別資料量是否龐大)。

(2) 該欄位經常出現在WHERE條件中，需要被頻繁掃描。

(3) 該欄位有較少的DML操作(INSERT、DELETE、UPDATE)，避免索引頻繁排序。

(4) 避免過多索引，因索引需維護而可能降低系統效能。

(5) 優先考慮通過主鍵或unique約束的欄位進行查詢，以提高效率。

另外，唯一性不夠的欄位不太適合作為索引。索引的主要目的是加快查詢操作，如果一個欄位的唯一性不高，即使加上索引，查詢的效率也不會有太大的提升。還有，索引也會佔用額外的存儲空間，如果對於唯一性不高的欄位添加索引，會增加資料庫的儲存負擔。

若某個欄位的值範圍較小，即該欄位的值具有較少的不同取值，這樣也可以減少索引的大小，提高查詢效率。某些情況添加索引可能會對性能產生負面影響，並且不會帶來太多的好處，可以考慮使用其他更適合的索引或優化查詢方式來提高性能。

1. 索引的類型

• 單一索引：針對單個欄位創建的索引，用於加速該欄位的查詢操作。
• 複合索引：將多個欄位結合在一起創建的索引，用於加速多個欄位的查詢操作，可以優化涉及這些欄位的查詢效率。
• 主鍵索引：特殊的唯一性索引，用於標識每一行記錄的唯一性，且不允許空值，通常用於快速查找特定記錄和建立表間關聯。
• 唯一性索引：確保欄位中的值是唯一的，用於防止重複數據的插入，但可以包含空值。

2. 創建和刪除索引

CREATE INDEX emp_ename_index ON emp(ename); -- 創建索引
DROP INDEX emp_ename_index ON emp; --刪除索引

-- 如何查看sql語句是否用了索引搜尋
-- (1)
EXPLAIN SELECT * FROM emp WHERE ename = 'KING'; 
/* # id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'emp', NULL, 'ref', 'emp_ename_index', 'emp_ename_index', '43', 'const', '1', '100.00', NULL */
-- 可以看到type是ref、rows有1筆 → 表示索引搜尋

-- (2)
EXPLAIN SELECT * FROM emp WHERE sal = 5000;
/* # id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'emp', NULL, 'ALL', NULL, NULL, NULL, NULL, '14', '10.00', 'Using where' */
-- 可以看到type是ALL、rows有14筆 → 表示全表搜尋

3. 索引失效

-- 第一種情況
EXPLAIN SELECT * FROM emp WHERE ename LIKE '%T';
/* # id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'emp', NULL, 'ALL', NULL, NULL, NULL, NULL, '14', '11.11', 'Using where' */

/* ename欄位即使添加了索引，也不會使用索引，原因是因為模糊匹配中使用了「%」符號開頭！
在進行模糊查詢時，儘量避免使用「%」開頭，這是一種優化的策略。 */

-- 第二種情況
EXPLAIN SELECT * FROM emp WHERE ename = 'KING' OR job = 'MANAGER';
/* # id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'emp', NULL, 'ALL', 'emp_ename_index', NULL, NULL, NULL, '14', '16.43', 'Using where' */

/* 使用「OR」時會導致索引失效，若使用「OR」，則要求兩側的條件欄位都必須有索引才會使用索引，
若其中一側的欄位沒有索引，則另一側的索引也不會被使用。這就是為什麼不建議使用「OR」的原因。 */

-- 第三種情況
CREATE INDEX emp_job_sal_index ON emp(job, sal);
EXPLAIN SELECT * FROM emp WHERE job = 'MANAGER';
/* # id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'emp', NULL, 'ref', 'emp_job_sal_index', 'emp_job_sal_index', '43', 'const', '3', '100.00', NULL */

EXPLAIN SELECT * FROM emp WHERE sal = 800;
/* # id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'emp', NULL, 'ALL', NULL, NULL, NULL, NULL, '14', '10.00', 'Using where' */

/* 使用複合索引時，若沒有使用複合索引的左側欄位進行查詢，索引將會失效。
複合索引是指將兩個或更多欄位聯合起來建立一個索引，使得這些欄位的組合成為索引鍵，稱為複合索引。*/

-- 第四種情況
CREATE INDEX emp_sal_index ON emp(ename);
EXPLAIN SELECT * FROM emp WHERE sal + 200 = 800;
/* # id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'emp', NULL, 'ALL', NULL, NULL, NULL, NULL, '14', '100.00', 'Using where' */

/* 在WHERE中，欄位參與了數學運算會導致索引失效。 */

-- 第五種情況
EXPLAIN SELECT * FROM emp WHERE LOWER(ename) = 'smith';
/* # id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'emp', NULL, 'ALL', NULL, NULL, NULL, NULL, '14', '100.00', 'Using where' */

/* 在WHERE中，使用函式會導致索引失效 */

七、view(視圖)

視圖就是站在不同角度看資料。

CREATE TABLE dept2 AS SELECT * FROM dept; -- 複製dept表，原理是將查詢結果當作欲新建的表
CREATE VIEW dept2_view AS SELECT * FROM dept2; -- 創建視圖，後面必須是DQL語句(如：SELECT * FROM dept2)
DROP VIEW dept2_view; -- 刪除視圖

-- 注意：我們可以對視圖做增刪改查，但原表也會連帶改變
-- 增
INSERT INTO dept2_view(deptno, dname, loc) VALUES (50, 'MARKETING', 'TAIPEI'); -- view插入新資料
-- 刪
DELETE FROM dept2_view WHERE deptno = 50;
-- 改
UPDATE dept2_view SET dname = 'DEVELOPMENT' WHERE deptno = 50;
-- 查
SELECT * FROM dept2_view;

SELECT * FROM dept2; -- 檢查會發現原表也一同變更

-- 創建一個emp_dept_view
CREATE VIEW emp_dept_view AS
SELECT e.ename, e.sal, d.dname
FROM emp e
JOIN dept d 
ON e.deptno = d.deptno;

-- 改
UPDATE emp_dept_view
SET sal = 1000
WHERE dname = 'ACCOUNTING';

總結來說，視圖物件可以用來儲存一條複雜的SQL語句，並在不同位置上重複使用，使得開發和維護更加簡單方便。視圖可以像使用表格一樣進行增刪改查等操作，且不會消失，因為它儲存在硬碟上。

八、Database Normalization(資料庫正規化)

Database Normalization是一種資料庫設計的方法，旨在最小化資料重複和資料異常，從而提高資料庫的效率和靈活性。它將資料組織成多個相互關聯的表，以減少冗餘資料並確保資料的完整性。

資料庫正規化遵循一系列規則，通常分為三個階段(1NF、2NF、3NF，Normal Form)，每個階段都有特定的要求。簡單來說，資料庫正規化的目標是將一個大表拆分成多個較小的表，並使用關聯(如主鍵和外鍵)來保持資料之間的一致性和相互關聯。

透過正確的資料庫正規化，可以確保資料的一致性、減少冗餘、提高查詢效率、簡化資料更新和維護，同時還能避免數據異常情況，使資料庫更具可靠性和可擴展性。

我們使用以下表格說明正規化的過程：

原表

1. 第一正規化(1NF)： 在第一正規化中，資料表中的每個欄位只包含原子性的值，不允許多值、重複值或者是由多個值組成的集合。它要求資料表中的每個欄位都是單一值的，不可再分，並且必須有主鍵。透過1NF的要求，能夠消除欄位中的重複數據，確保每個欄位都包含唯一的數據。

1NF

2. 第二正規化(2NF)： 在第二正規化中，資料表必須首先符合1NF，且每個非主鍵欄位完全依賴於主鍵，而不是依賴於其他非主鍵欄位。如果有一個非主鍵欄位只依賴於部分主鍵，則需將這些部分主鍵與相關的非主鍵欄位拆分成一個新的資料表，上表中，我們可以看到員工和負責專案ID作為複合主鍵，且非主鍵欄位如名字、負責專案名稱，分別依賴於員工ID、負責專案ID，這就是部分依賴。因此可以拆分為三張表來描述1NF表中，員工與負責專案的多對多關係，透過2NF的要求，能夠避免資料冗餘、空間浪費。

2NF

3. 第三正規化(3NF)： 在第三正規化中，資料表必須首先符合2NF，且每個非主鍵欄位之間不能存在遞移性依賴關係。也就是說，如果有一個非主鍵欄位依賴於另一個非主鍵欄位，則需要將這些相關的非主鍵欄位拆分成一個新的資料表，上表中可以看到，薪水依賴於職等、職等又依賴於員工ID。透過3NF的要求，避免資料冗餘、空間浪費、資料更新異常，使資料表更加穩定和靈活。

3NF

資料庫正規化是一個重要的資料庫設計原則，能夠幫助我們優化資料結構，減少數據冗餘和異常，提高資料庫的效率和可靠性。每個階段的要求都有助於消除設計上的缺陷，使資料庫能夠更好地應對不同的查詢和操作需求。在實際開發中，我們需要在資料冗餘和效率之間取得平衡。有時候，表的結構看起來可能存在一些冗餘，但卻能減少表與表之間的連接，降低撰寫複雜SQL語句的工作量。因此，資料正規化雖然是一個良好的理論，但我們仍需根據實際情況來考慮最適合的資料庫設計策略。

💡 Q:為何減少表與表間的連接能夠增加效率？A:可以參考上面提到的笛卡爾乘積。

九、SQL Injection

SQL Injection是一種常見的資安攻擊方式，當應用程式在處理使用者輸入的資料時，如果未對輸入的數據進行適當的驗證和處理，攻擊者可以通過在輸入中插入惡意的SQL語句來竊取、修改或刪除資料庫中的數據，或者對資料庫進行其他非法操作。SQL注入攻擊利用了應用程式的漏洞，使攻擊者能夠執行未經授權的SQL查詢，進而對資料庫和應用程式造成嚴重影響。

以下是一個簡單的SQL Injection例子，假設有一個網站上的登入頁面，使用者需要輸入名稱和密碼來進行登入，而後台的查詢語句可能會類似於以下形式：

SELECT * FROM users WHERE username = 'input_username' AND password = 'input_password';

正常情況下，’input_username’和’input_password’是由使用者輸入的值。然而，如果應用程式沒有對輸入的值進行適當的處理和驗證，攻擊者可以利用SQL注入漏洞進行攻擊。例如，攻擊者可以在使用者名稱欄位輸入以下內容：

「' OR '1'='1' -- 」
-- 注意「」裡，最後還有一個空白格

這樣在實際執行查詢時，SQL語句變成：

SELECT * FROM users WHERE username = '' OR '1'='1' -- ' AND password = 'input_password';

由於’1'=’1'永遠為真，上述查詢將返回所有使用者記錄，而不僅僅是匹配的使用者記錄。攻擊者可能以此方式繞過登入認證，並取得所有使用者資料的訪問權限。

這是一個簡單的例子，實際的SQL注入攻擊可能更複雜和隱蔽，因此開發者應該對使用者輸入的數據進行適當的驗證和處理，並使用參數化查詢來防止SQL注入攻擊。

💡 一次看懂 SQL Injection 的攻擊原理 | by Jayden Lin | 程式猿吃香蕉 | Medium | 程式猿吃香蕉
💡 參數化查詢 — 維基百科，自由的百科全書 (wikipedia.org)

小心得：不可思議的~我又完成了新的一篇學習筆記 ʕ〃•Ⱉ•〃ʔ，我想DB和SQL的部分就告一段落了~~
【DB學習筆記】SQL(Structured Query Language，結構化查詢語言) ─ (1) | by Allison | Jul, 2023 | Medium