# SQL語法簡介
SQL（Structured Query Language）是「結構化查詢語言」的簡稱，是用於管理和操作資料庫的標準語言。SQL 由 ANSI（American National Standards Institute）制定，主要標準版本如下：
- SQL 89：1989 年發布，基本查詢功能。
- SQL 92：1992 年發布，加入更多功能與標準化。
- SQL 99：1999 年發布，加入更多進階特性，如遞迴查詢。
## SQL指令分類
SQL 指令依功能大致分為四類：
|類別 | 簡稱 | 功能說明 |
| -------- | -------- | -------- |
|資料定義語言| DDL |定義資料庫結構，例如建立和修改資料表。|
|資料操作語言| DML |管理資料，例如新增、更新、刪除和查詢資料。|
|資料控制語言| DCL |管理使用者權限，例如設定資料庫的存取權限。|
|交易控制語言| TCL |管理交易的提交和回復，例如提交或取消交易。|
1. 資料定義語言 DDL(Data Definition Language)
DDL 用於定義資料庫的結構，常用指令包括:
- CREATE：建立新的資料庫或資料表。
- ALTER：修改資料表的結構。
- DROP：刪除資料表或索引。
- TRUNCATE：快速清空資料表的所有資料。
2. 資料操作語言 DML(Data Manipulation Language)
DML 用於對資料進行增刪改查操作，常用指令包括
- INSERT：新增資料。
- UPDATE：更新資料。
- DELETE：刪除資料。
- SELECT：查詢資料。
3. 資料控制語言 DCL(Data Control Language)
DCL 用於管理資料庫的安全性和權限，常用指令包括：
- GRANT：授予權限。
- REVOKE：收回權限。
4. 交易控制語言 TCL(Transaction Control Language)
TCL 用於管理交易的處理，常用指令包括：
- COMMIT：提交交易，使更改永久保存。
- ROLLBACK：取消交易，使更改回復到提交前的狀態。
---
以下使用 SQLite 進行教學說明
---
## 安裝 SQLite 管理工具
SQLite 是一個輕量型的關聯式資料庫管理系統，它以 C 語言編寫而成，並且是一個開源軟體。它的特點是軟體相當小，只有幾百 KB，且在運行時所需的記憶體量也很小，這使它非常適合於嵌入式系統和移動設備中使用，配合很少的設定和管理，非常適合作為網頁後端資料庫的學習。若要配合高流量的 web 應用程式，通常建議改用 MySQL 或 PostgresSQL。
基本資料庫的構成有【資料庫、資料表、索引檔】，而資料表中又是由【欄位、記錄】所組成，SQLite 支援大部分的 ANSI SQL 語法，但又有其獨有的語法，例如沒有 TRUNCATE TABLE 命令，但有 INSERT OR IGNORE INTO 命令。
### DB Browser for SQLite
SQLite 的管理工具推薦使用 DB Browser for SQLite ，請先至 [官網](http://sqlitebrowser.org/) 下載程式，安裝版或免安裝版皆可：https://sqlitebrowser.org/dl/#windows
以免安裝版為例：解壓縮後請執行主程式：DB Browser for SQLite.exe
## 建立資料庫
建立一個資料庫檔：【sample.db】 在自己的專案目錄下
## 建立資料表
在 SQL 中，CREATE TABLE 指令用來建立資料表。可以指定欄位名稱、資料類型以及條件約束，來定義資料表的結構。
**基本語法：**
```sql!
CREATE TABLE [IF NOT EXISTS] 資料表名稱 (
欄位名稱 資料形態 條件約束,
欄位名稱 資料形態 條件約束,
...
);
```
**常見的資料類型(datatype)：**
- INTEGER：整數型別，可以儲存 1、-10、100、0 等等整數值。
- TEXT：文字型別，可以儲存文字、數字、符號等等資料，例如 "Hello World"、"123"、"$%#" 等等。
- REAL：浮點數型別，可以儲存小數，例如 3.14159、2.5、-10.0 等等。
- BLOB：二進位型別，可以儲存任意二進位資料，例如影像、音效、PDF 檔案、壓縮檔案等等。
- NULL：空值型別，表示該欄位沒有值。
**常見的條件約束(constraints)有：**
- PRIMARY KEY：設定該欄位為主鍵，保證該欄位的值是唯一的，且不可為 NULL。
- NOT NULL：設定該欄位的值不可為 NULL，也就是必須有值。
- UNIQUE：設定該欄位的值必須是唯一的，也就是不能有重複的值。
- CHECK：設定該欄位的值必須符合特定條件，例如 CHECK (salary > 0)。
- FOREIGN KEY：設定該欄位為外鍵，關聯到其他資料表中的欄位。
- DEFAULT：為欄位設定預設值。
**範例：資料表關聯並設定條件約束**
```sql=
-- 訂單 資料表
CREATE TABLE IF NOT EXISTS 訂單 (
訂單編號 TEXT PRIMARY KEY, -- 主鍵，唯一且不可為 NULL
員工編號 TEXT NOT NULL, -- 必須有值
客戶編號 TEXT NOT NULL, -- 必須有值
打單日期 TEXT NOT NULL, -- 必須有值
出貨日期 TEXT, -- 可為 NULL
交貨方式 TEXT DEFAULT '自取', -- 預設值為 '自取'
FOREIGN KEY (客戶編號) REFERENCES 客戶(客戶編號) -- 外鍵，參照 客戶 資料表
);
-- 訂單詳細資料 資料表
CREATE TABLE IF NOT EXISTS 訂單詳細資料 (
訂單編號 TEXT NOT NULL, -- 外鍵參照 訂單 資料表
產品編號 TEXT NOT NULL, -- 外鍵參照 產品 資料表
數量 INTEGER CHECK(數量 > 0), -- 必須大於 0
單價 REAL CHECK(單價 > 0), -- 必須大於 0
折扣 REAL CHECK(折扣 >= 0 AND 折扣 <= 1), -- 折扣在 0 到 1 之間
PRIMARY KEY (訂單編號, 產品編號), -- 複合主鍵，保證唯一性
FOREIGN KEY (訂單編號) REFERENCES 訂單(訂單編號),
FOREIGN KEY (產品編號) REFERENCES 產品(產品編號)
);
-- 產品 資料表
CREATE TABLE IF NOT EXISTS 產品 (
產品編號 TEXT PRIMARY KEY, -- 主鍵，唯一且不可為 NULL
產品名稱 TEXT NOT NULL, -- 必須有值
單位 TEXT DEFAULT '個', -- 預設值為 '個'
單價 REAL CHECK(單價 > 0), -- 必須大於 0
庫存數量 INTEGER CHECK(庫存數量 >= 0), -- 必須為非負數
停止出貨 BOOLEAN DEFAULT 0 -- 預設為 0 (未停止)
);
```
:::info
在 CREATE TABLE 指令中，條件約束可以有兩種不同的寫法：一種是直接加在欄位定義後，另一種是放在所有欄位定義後的條件約束區塊中。
- 單一欄位約束：如果表格中的主鍵(PRIMARY KEY)、唯一鍵(UNIQUE)、或外鍵(FOREIGN KEY)僅涉及單一欄位，直接加在欄位定義後比較好，因為簡單且直觀。
- 多欄位約束：如果表格中有多個條件約束或約束涉及多個欄位（例如複合主鍵或複合唯一鍵），則應該放在所有欄位定義後的條件約束區塊中，這樣有助於代碼的清晰性與維護性。
:::
**實作**
請複製下列敘述後執行，並以 DB Browser for SQLite 觀察其變化
```sql=
CREATE TABLE if not exists "customer" (
"cid" TEXT PRIMARY KEY,
"cname" TEXT NOT NULL,
"ctel" TEXT NOT NULL,
"cadd" TEXT
);
CREATE TABLE if not exists "product" (
"pid" TEXT PRIMARY KEY,
"pname" TEXT NOT NULL,
"punit" INTEGER NOT NULL,
"pqty" INTEGER NOT NULL
);
CREATE TABLE if not exists "trade"(
"tid" INTEGER PRIMARY KEY AUTOINCREMENT,
"tdate" date NOT NULL,
"cid" TEXT NOT NULL,
"pid" TEXT NOT NULL,
"tqty" INTEGER NOT NULL,
"tunit" INTEGER NOT NULL,
"tsum" INTEGER NOT NULL
);
CREATE TABLE if not exists "temp" (
"t_a" TEXT PRIMARY KEY,
"t_b" TEXT NOT NULL,
"t_c" INTEGER NOT NULL,
"t_d" INTEGER NOT NULL
);
```
執行完上述的 create table 指令之後，應該可以看到資料庫中多了 4 個資料表，其中 sqlite_sequence 資料表是自動產生的，是因為 trade.tid 設定了自動增量(autoincrement)屬性的關係。
資料庫設定自動增量(autoincrement)的欄位，後續的增刪改處理都可忽略之，就當做此欄位不存在，因為 sqlite 系統會自動維護。
## 移除資料表
**語法**
```sql!
-- 移除整個資料表
DROP TABLE table_name;
```
:::info
SQL 語法的註解是 2 個 dash --
:::
## 新增記錄
在 SQL 中，INSERT 指令用於將新記錄新增到資料表中。可以選擇新增指定欄位的值，或者透過查詢結果將多筆資料同時新增。
### 對資料表新增指定欄位的值
如果想將特定的值新增到資料表中，可以使用以下語法：
```sql=
INSERT INTO customer (cid, cname, ctel, cadd)
VALUES ('c001', 'Peter', '0932-123456', 'Taichung City Park road');
-- 這個操作將會將一位名為 Peter 的客戶新增到 customer 表中。
```
:::info
資料庫設定自動增量(autoincrement)的欄位，後續的增刪改處理都可忽略之，就當做此欄位不存在，因為資料庫會自動維護。
:::
### 進階：插入查詢結果
可以透過查詢結果將資料直接新增到資料表中。這樣可以批量插入資料：
```sql=
INSERT INTO customer (cid, cname, ctel, cadd)
SELECT 'c004', 'Alice', '0922-123456', 'Taichung City east road';
-- 這個操作將會新增一位名為 Alice 的客戶。
```
### 進階：子查詢插入
子查詢是一種在 INSERT 語句中使用 SELECT 來插入資料的方式，可以更靈活地選擇要插入的記錄。例如：
```sql=
INSERT INTO trade (tdate, cid, pid, tqty, tunit, tsum)
SELECT '2023/11/28', cid, pid, tqty, tunit, tsum
FROM (SELECT 'c005' AS cid, 'p001' AS pid, 15 AS tqty, 30 AS tunit, 450 AS tsum) AS subquery;
-- 此操作將根據子查詢的結果新增一筆交易記錄。
```
請複製下列敘述後執行，並以 DB Browser for SQLite 觀察其變化：
```sql=
-- 新增客戶記錄
INSERT INTO customer (cid, cname, ctel, cadd) VALUES ('c001', 'Peter', '0932-123456', 'Taichung City Park road');
INSERT INTO customer VALUES ('c002', 'Janet', '0933-224778', 'Taichung City sanming road');
INSERT INTO customer VALUES ('c003', 'Tom', '0957-444666', 'Taichung City university road');
-- 新增產品記錄
INSERT INTO product (pid, pname, punit, pqty) VALUES ('p001', 'Notebook', 20, 48);
INSERT INTO product VALUES ('p002', 'Pencil', 5, 96);
INSERT INTO product VALUES ('p003', 'Book', 80, 120);
-- 新增交易記錄
INSERT INTO trade (tdate, cid, pid, tqty, tunit, tsum) VALUES ('2023/11/24', 'c001', 'p002', 5, 17, 85);
INSERT INTO trade (tdate, cid, pid, tqty, tunit, tsum) VALUES ('2023/11/25', 'c002', 'p001', 10, 30, 300);
INSERT INTO trade (tdate, cid, pid, tqty, tunit, tsum) VALUES ('2023/11/26', 'c001', 'p003', 5, 150, 750);
INSERT INTO trade (tdate, cid, pid, tqty, tunit, tsum) VALUES ('2023/11/27', 'c002', 'p003', 10, 140, 1400);
```
## 修改記錄
在 SQL 中，UPDATE 指令用於修改資料表中的記錄。可以選擇修改全部記錄或符合特定條件的記錄。執行修改操作前，請確認條件正確，以免造成資料錯誤。
### 修改全部記錄
如果想修改資料表中所有記錄的指定欄位值，可以使用以下語法：
```sql=
UPDATE customer
SET ctel = '0912-555555'; -- 這個操作將會修改所有客戶的電話號碼，請謹慎使用。
```
### 修改部分記錄
如果只想修改符合特定條件的記錄，則需要在 UPDATE 語句中添加 WHERE 子句，例如：
```sql=
UPDATE customer
SET ctel = '0912-556688'
WHERE cname = 'Tom'; -- 這個操作將會將 Tom 的電話號碼修改為 0912-556688。
```
### 進階修改
假設我們需要根據其他表的資料來修改客戶的電話號碼，這時可以使用子查詢來完成此操作。
首先，假設有一個 new_contacts 資料表，存放新的聯絡電話資料。以下是使用子查詢更新客戶資料的範例：
```sql=
UPDATE customer
SET ctel = (SELECT new_ctel FROM new_contacts WHERE new_contacts.cname = customer.cname)
WHERE cname IN (SELECT cname FROM new_contacts); -- 這個操作將會根據 new_contacts 表的資料更新 customer 表的電話號碼。
```
## 刪除記錄
在 SQL 中，DELETE 指令用於刪除資料表中的記錄。可以選擇刪除全部記錄或符合特定條件的記錄。刪除操作是不可逆的，因此在執行刪除之前，請確認刪除的條件是否正確。
### 刪除全部記錄
如果想刪除資料表中的所有記錄，可以使用以下語法：
```sql=
DELETE FROM customer; -- 這個操作將會刪除資料表中的所有資料，請謹慎使用。
```
### 刪除部分記錄
如果只想刪除符合特定條件的記錄，則需要在 DELETE 語句中添加 WHERE 子句，例如：
> DELETE FROM table_name WHERE condition;
```sql=
DELETE FROM customer WHERE cname = 'Tom';
```
### 進階刪除
假設我們需要刪除所有與 Tom 相關的交易記錄，然後再刪除 Tom 的客戶資料。可以使用子查詢來完成此操作。
首先，刪除所有與 Tom 相關的交易記錄：
```sql=
DELETE FROM trade
WHERE cid IN (SELECT cid FROM customer WHERE cname = 'Tom');
```
接著，再刪除 Tom 的客戶資料：
```sql=
DELETE FROM customer WHERE cname = 'Tom';
```
## 查詢記錄
在 SQL 中，SELECT 指令是用來查詢資料表的主要指令。可以根據需求指定欄位、加入篩選條件、進行子查詢，甚至連結多個資料表進行更複雜的查詢。
### 基本查詢語法
#### 查詢所有欄位
若要查詢資料表中的所有欄位，可以使用萬用字元 *，例如：
```sql=
SELECT * FROM table_name;
```
#### 查詢指定欄位
僅選取特定欄位進行查詢，可透過列出欄位名稱的方式：
```sql=
SELECT column1, column2, column3, ...
FROM table_name;
```
### 條件查詢
WHERE 子句允許我們對查詢結果進行篩選，以取得符合條件的記錄，例如：
```sql=
SELECT * FROM customer WHERE cname ='John';
SELECT * FROM customer WHERE cname LIKE 'J%'; -- 查詢 customer 表中所有 cname 以 "J" 開頭的客戶記錄
```
### 限制顯示的筆數 (LIMIT)
LIMIT 子句用來指定查詢結果最多顯示的筆數，這在查詢大量資料時特別有用。例如，只想顯示前 10 筆記錄時，可以加上 LIMIT 10，還可以搭配 OFFSET 指定從哪一筆開始顯示。
```sql=
SELECT * FROM customer LIMIT 10; -- 顯示前 10 筆記錄
SELECT * FROM customer LIMIT 10 OFFSET 5; -- 跳過前 5 筆，顯示第 6 到第 15 筆記錄
```
### 排序查詢結果 (ORDER BY)
ORDER BY 子句用來按照指定的欄位對查詢結果進行排序，可以是遞增 (ASC) 或遞減 (DESC) 排序。預設為遞增排序。例如，根據 cname 欄位進行遞增排序或根據 cid 欄位進行遞減排序：
```sql=
SELECT * FROM customer ORDER BY cname; -- 遞增 (ASC) 排序
SELECT * FROM customer ORDER BY cid DESC; -- 遞減 (DESC) 排序
```
ORDER BY 也可以搭配多個欄位進行排序，例如先依 cadd 遞增，再依 cname 遞減排序：
```sql=
SELECT * FROM customer ORDER BY cadd ASC, cname DESC;
```
### 進階查詢
#### 子查詢
透過子查詢可以在主查詢中嵌入另一個查詢。例如，從 table_name 中查詢欄位值在另一個查詢結果中的記錄：
```sql=
SELECT column1, column2
FROM table_name
WHERE column_name IN (SELECT column_name FROM another_table_name WHERE condition);
```
#### JOIN 查詢
JOIN 用於將多個資料表的記錄關聯在一起進行查詢。例如，將 trade 和 customer 表格中擁有相同 cid 的記錄進行連結查詢：
```sql=
SELECT trade.*, customer.cname
FROM trade
INNER JOIN customer ON trade.cid = customer.cid;
```
### 查詢範例
可以在 DB Browser for SQLite 中執行以下查詢指令，觀察查詢結果：
```sql!
-- 查詢 customer 表中的所有記錄
SELECT * FROM customer;
-- 查詢 customer 表中的 cid, cname, ctel 欄位
SELECT cid, cname, ctel FROM customer;
-- 查詢 cname 為 "Janet" 的客戶記錄
SELECT * FROM customer WHERE cname = 'Janet';
-- 查詢地址包含 "City" 的客戶記錄
SELECT * FROM customer WHERE cadd LIKE '%City%';
-- 查詢 trade 表中的所有記錄
SELECT * FROM trade;
-- INNER JOIN 查詢範例：查詢 trade 和 customer 表之間 cid 關聯的記錄
SELECT * FROM trade INNER JOIN customer ON trade.cid = customer.cid;
-- 查詢 trade 表的所有欄位及 customer 表中的 cname 欄位
SELECT trade.*, customer.cname
FROM trade
INNER JOIN customer ON trade.cid = customer.cid;
-- 多表連接查詢：查詢 trade 表、customer 表、product 表之間的關聯
SELECT t.tid, t.tdate, t.cid, c.cname, t.pid, p.pname, t.tqty, t.tunit, t.tsum
FROM trade AS t
INNER JOIN customer AS c ON t.cid = c.cid
INNER JOIN product AS p ON t.pid = p.pid;
```
## 分組（GROUP BY）與聚合函數
分組（GROUP BY）會將查詢結果按指定欄位分成不同群組，並在每個分類群組上使用聚合函數 (Aggregate Functions)進行統計分析，例如計算每個群組內的記錄數量、總和、平均值、最大值和最小值等。
常見的聚合函數如下：
- COUNT()：計算數量
- SUM()：計算總和
- AVG()：計算平均值
- MAX()：取得最大值
- MIN()：取得最小值
**範例**
假設我們有一個 sales 資料表，儲存銷售紀錄，包含以下欄位：
- product_id：產品編號
- quantity：銷售數量
- price：每件產品的售價
- sale_date：銷售日期
資料表內容如下
|product_id |quantity |price |sale_date|
| -------- | -------- | --- | -------- |
|P001 |5 |100 |2024-10-01|
|P002 |10 |200 |2024-10-01|
|P001 |7 |100 |2024-10-02|
|P003 |3 |150 |2024-10-02|
|P002 |8 |200 |2024-10-03|
**範例1：計算每個產品的銷售次數**
以下查詢會依 product_id 分組，並計算每種產品出現的次數：
```sql=
SELECT product_id, COUNT(*) AS sale_count
FROM sales
GROUP BY product_id;
```
查詢結果
|product_id |sale_count|
| -------- | -------- |
|P001 |2|
|P002 |2|
|P003 |1|
**範例2：計算每個產品的總銷售數量和平均售價**
以下查詢會依 product_id 分組，並對每組的 quantity 欄位計算總和 (SUM)，同時計算每組 price 欄位的平均值 (AVG)：
```sql=
SELECT product_id, SUM(quantity) AS total_quantity, AVG(price) AS average_price
FROM sales
GROUP BY product_id;
```
查詢結果
|product_id |total_quantity |average_price|
| -------- | -------- | -------- |
|P001 |12 |100|
|P002 |18 |200|
|P003 |3 |150|
**範例3：依照銷售日期分組，找出每日最高銷售數量**
此查詢會依 sale_date 分組，並找出每天銷售數量的最大值 (MAX)：
```sql=
SELECT sale_date, MAX(quantity) AS max_quantity
FROM sales
GROUP BY sale_date;
```
查詢結果
|sale_date |max_quantity|
| -------- | -------- |
|2024-10-01 |10|
|2024-10-02 |7|
|2024-10-03 |8|
## 索引（Index）建立
索引是一種用於加速資料庫查詢的資料結構，適用於在大量資料中執行頻繁查詢的欄位。例如，若要在含有數千筆記錄的資料表中查找特定客戶，則可以在「客戶姓名」欄位上建立索引，這樣系統會利用索引中的結構來加快搜尋速度，而不必遍歷整張資料表。
**優缺點：**
- 優點：索引可以顯著提升查詢速度，尤其是對篩選條件（如 WHERE 子句）和排序（如 ORDER BY 子句）有幫助。
- 缺點：過多索引會佔用額外的儲存空間，並在插入、刪除、更新等操作時增加維護成本。因此，在需要頻繁查詢的欄位上建立索引較為合適。
**語法：**
> CREATE INDEX index_name ON table_name (column_name);
**範例：**
假設我們有一個「客戶」資料表，若經常需要依據「客戶姓名」查詢資料，可以建立該欄位的索引來加速查詢：
```sql=
CREATE INDEX idx_customer_cname ON customer (cname);
```
此指令會在 customer 資料表的 cname 欄位上建立一個名為 idx_customer_cname 的索引，後續下達如下的查詢指令時，資料庫將會透過索引迅速找到符合條件的記錄，而不必進行全表搜尋。
```sql=
SELECT * FROM customer WHERE cname = 'Peter';
```
## 視圖（View）建立
視圖（View）是一種虛擬資料表，透過 CREATE VIEW 指令建立。視圖的主要功能是將一組查詢結果保存為一個資料表的表示形式，這樣可以簡化重複性查詢或封裝複雜的查詢邏輯。視圖不會儲存實際的資料，而是保存查詢定義，因此每次查詢視圖時，系統會動態執行其查詢語句來取得最新結果。
**使用視圖的優勢：**
- 重複使用查詢邏輯：視圖可以將複雜的查詢邏輯封裝起來，使得每次查詢更簡潔。
- 提高安全性：可以限制使用者僅能查詢視圖的內容，而無法存取底層資料表。
- 簡化報表產生：可以將多個資料表的聯結或計算結果定義成視圖，使得報表查詢更為便捷。
**語法：**
```sql=
CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;
```
**範例：**
假設我們有一個「訂單」資料表 (orders)，裡面包含了所有訂單的詳細資訊。若我們經常需要查詢狀態為「已完成」的訂單，可以建立一個視圖來保存此查詢結果：
```sql=
CREATE VIEW completed_orders AS
SELECT order_id, customer_id, order_date
FROM orders
WHERE status = 'Completed';
```
此視圖命名為 completed_orders，其中包含了 order_id、customer_id 和 order_date 欄位，並且僅顯示狀態為「已完成」的訂單。查詢該視圖就等同於查詢此條件的訂單，例如：
```sql=
SELECT * FROM completed_orders;
```
## 修改資料表 (ALTER TABLE)
ALTER TABLE 指令用於修改已存在的資料表結構，可以新增、修改或刪除欄位，甚至調整欄位的資料型態或新增索引。此指令在資料表結構變更需求時非常實用，但需謹慎使用，特別是在資料量龐大的情況下，以避免操作不當造成資料損壞或性能問題。
### 新增欄位
在 customer 資料表中新增一個名為 email 的欄位，資料型態為 VARCHAR(50)
```sql!
ALTER TABLE customer ADD email VARCHAR(50);
```
### 修改欄位屬性
將 ctel 欄位的資料型態從原本設定調整為 VARCHAR(15)，以便儲存較長的電話號碼
```sql!
ALTER TABLE customer MODIFY ctel VARCHAR(15);
```
### 刪除欄位
若決定不再需要 cadd 欄位，可以將其刪除，刪除後無法復原該欄位及其資料
```sql!
ALTER TABLE customer DROP COLUMN cadd;
```
### 增加欄位時一併增加索引
當我們已經在使用 ALTER TABLE 指令進行表結構調整（例如新增或修改欄位）時，為了簡化指令操作，可以使用 ADD INDEX 在同一指令中完成索引的新增。
```sql!
ALTER TABLE customer
ADD COLUMN email VARCHAR(50),
ADD INDEX idx_customer_email (email);
```
> 若只想要建立索引，而無需對資料表進行結構上的變更時，則可改用 CREATE INDEX 命令。
## CTE (Common Table Expression)
CTE（Common Table Expression）是 SQL:1999 標準的一部分，並且被大多數現代資料庫所支援。它的主要用途是建立臨時結果集，從而簡化複雜查詢並提高可讀性。CTE 的特色包括：
1. 多次引用：同一查詢中可多次使用 CTE，並可以串接多個 CTE，實現更靈活的查詢邏輯。
2. 遞迴查詢：CTE 支援遞迴查詢，特別適用於處理層級結構或樹狀結構的資料。
3. 資料操作：CTE 可以在 SELECT、INSERT、UPDATE、DELETE 和 CREATE TABLE 等 SQL 語句中使用，但在 SQLite 中僅支援查詢操作。
### 在 Python sqlite3 中使用 CTE
Python 的 sqlite3 模組內建支援 CTE，能夠應對複雜查詢和遞迴運算的需求。使用 CTE 時，有以下要求：
1. 版本要求：需使用 SQLite 版本 3.8.3 或以上。
2. 限制：在 SQLite 中，CTE 僅支援查詢操作，不能用於 INSERT、UPDATE 或 DELETE。
### ROW_NUMBER() 函數
ROW_NUMBER() 是一個窗口函數，用於為查詢結果集中的每一行賦予唯一的序號。該函數常與 CTE 結合使用，用於排序、分組和分頁等操作。需要注意的是：
1. 版本要求：SQLite 3.25.0 或以上版本支援 ROW_NUMBER() 函數。
2. 限制：在 SQLite 中，ROW_NUMBER() 只能用於查詢，無法用於 INSERT、UPDATE 或 DELETE。
### 範例程式碼
以下範例程式碼展示了如何使用 CTE 和 ROW_NUMBER() 函數。這段程式碼根據資料表順序產生記錄號：
```python=
import sqlite3
# 建立 SQLite 內存資料庫
conn = sqlite3.connect(":memory:")
cursor = conn.cursor()
# 建立範例資料表
cursor.executescript("""
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER);
INSERT INTO users (name, age) VALUES
('Alice', 30), ('Bob', 25), ('Charlie', 35);
""")
# 使用 CTE 和 ROW_NUMBER()
query = """
WITH RankedUsers AS (
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS 記錄號, -- 根據記錄順序生成記錄號
-- ROW_NUMBER() OVER (ORDER BY age DESC) AS 行號, -- 根據年齡排序生成行號
name, age
FROM users
)
SELECT * FROM RankedUsers where age > 28
"""
cursor.execute(query)
print(cursor.fetchall()) # 輸出: [(1, 'Charlie', 35), (2, 'Alice', 30)]
conn.close()
```
## SQLite 專用命令 (進階)
### 1. VACUUM
在 SQLite 資料庫中，當記錄被刪除或更新時，資料庫並不會立即釋放記錄所佔用的記憶體或磁碟空間，而是保留該空間以供未來使用。隨著操作的累積，資料庫會出現碎片化現象，使得儲存結構不連續，進而降低資料庫的效能。VACUUM 指令可用於清理這些空閒的磁碟空間，壓縮資料庫的體積，減少碎片化，並提升 SQL 查詢的執行效率。
**使用時機：**
- 碎片化嚴重時：若資料庫頻繁進行刪除和更新操作，建議定期執行 VACUUM。
- 提升效能：在資料庫讀取速度下降時，透過 VACUUM 可以重新整理資料表，提高查詢性能。
- 資料庫縮減：當刪除了大量記錄後執行 VACUUM，可以縮減資料庫佔用的實體空間。
**注意事項：**
- 執行時間較長：VACUUM 操作可能會佔用大量系統資源，適合在系統使用量低的時段進行。
- 僅對 SQLite 資料庫有效：在其他資料庫系統（例如 MySQL、PostgreSQL）中，VACUUM 的功能可能需其他命令來達成。
**語法：**
```sql!
VACUUM;
```
執行上述指令後，SQLite 將會重新組織並壓縮資料庫文件，釋放未使用的空間並減少碎片化，以提高資料庫的整體效能。
### 2. PRAGMA
在 SQLite 中，PRAGMA 指令提供了一種用來讀取或修改資料庫設定的方式。通過 PRAGMA 指令，我們可以控制 SQLite 的行為，像是調整資料庫的鎖定模式、記錄日誌的行為，或者檢視資料庫的內部狀態。PRAGMA 指令通常用於調整效能、維護資料庫和診斷問題。
PRAGMA 指令的效果通常是即時的，但部分指令可能需要在資料庫重新打開後才能生效，PRAGMA 指令可以顯著影響資料庫的行為和效能，因此在修改前應先測試，確保符合應用需求。
**常見的 PRAGMA 指令：**
#### foreign_keys
- 預設值：OFF
- 說明：允許在插入或刪除記錄時進行外鍵檢查，以維持資料的的參照完整性，外鍵約束在 SQLite 中預設是關閉的，需要明確啟用（PRAGMA foreign_keys = 1;）才能生效。
```sql!
PRAGMA foreign_keys = 1;
```
#### cache_size
- 預設值：-2000（頁面數）
- 說明：設定快取的大小以優化效能。SQLite 的快取大小以頁面數來設定，負值代表以 KB 為單位的快取大小，-2000 表示約 2 MB 的快取。實際值可能因版本不同而有所調整。
```sql!
PRAGMA cache_size = 1000; -- 可以增大快取容量，在大量資料讀取或寫入時提升效能。
```
#### journal_mode
預設值：DELETE
說明：設定日誌模式，以控制資料庫的恢復行為，在 DELETE 模式下，SQLite 每次交易完成後會刪除日誌檔。其他可用值有 WAL、TRUNCATE、PERSIST 等，WAL（Write-Ahead Logging）模式通常用於需要較高效能的情境。
```sql!
PRAGMA journal_mode = WAL; -- 將日誌模式設為 WAL（Write-Ahead Logging）可以在多使用者訪問下提供更佳的效能。
```
#### page_size
預設值：4096（Bytes）
說明：設定資料庫的頁面大小。SQLite 的預設頁面大小為 4096 Bytes，這個值要在資料庫建立前確定，建立之後無法更改。
```sql!
PRAGMA page_size = 4096; -- 調整頁面大小有助於根據特定的應用需求進行空間和效能優化。
```
#### encoding
預設值：UTF-8
說明：設定或檢查資料庫的編碼格式。這在確保資料的正確性以及跨系統的資料相容性時，特別重要。這個值要在資料庫建立前確定，一旦資料庫建立後，編碼格式無法更改，因此建議在建立資料庫時確定適當的編碼。
SQLite 僅支援 UTF-8、UTF-16le 和 UTF-16be 三種編碼，不支援其他編碼類型。
```sql!
PRAGMA encoding = "UTF-8";
```
上述預設值會根據 SQLite 的版本或設定選項而有所不同，使用前可確認當前資料庫版本的實際預設值。如有需要，可透過 PRAGMA 指令檢查目前設定的值，例如：
```sql!
PRAGMA foreign_keys;
PRAGMA cache_size;
PRAGMA journal_mode;
PRAGMA page_size;
PRAGMA encoding;
```
#### table_info(資料表)
```sql!
PRAGMA table_info(orders);
```
| cid | name | type | notnull | dflt_value | pk |
| ---- | ------- | ---- | ------- | ---------- | -- |
| 0 | cid | TEXT | 0 | | 1 |
| 1 | cname | TEXT | 1 | | 0 |
| 2 | ctel | TEXT | 1 | | 0 |
| 3 | cadd | TEXT | 0 | | 0 |
#### foreign_key_list(資料表)
```sql!
PRAGMA foreign_key_list(訂單詳細資料);
```
| id | seq | table | from | to | on_update | on_delete | match |
| --- | --- | ----- | ------- | ------ | --------- | --------- | ----- |
| 0 | 0 | 產品 | 產品編號 | 產品編號 | NO ACTION | NO ACTION | NONE |
| 1 | 0 | 訂單 | 訂單編號 | 訂單編號 | NO ACTION | NO ACTION | NONE |
## 參考資料
- [SQLite教學](https://www.1ju.org/sqlite/index)
Sign in with Wallet
Connect another wallet