# SQL

# SQL 基礎

SQL : Structured query language 結構查詢語言

常見有 Oracle / MSSQL / MySQL / PostgreSQL / SQLite (前二為付費系統)

- 除 SQLite 外, 其餘為 DataBase server, server 又可稱 connection pool, 而一個 server 可以有好幾個 database, 而一個 database 則可以有好幾個資料表 (table)
- SQLite 則為檔案型, 一個檔案就是一個 database, 一樣可以有好幾個 table, 但同一時間可以連線的數量少 (編輯保護的關係)

**R**elational **D**ata**b**ase **M**anagement **S**ystem 關連式資料庫 RDBMS

## [SQL 注入 (injection)](https://developer.mozilla.org/zh-TW/docs/Glossary/SQL_Injection)

是一種常見的資料庫攻擊技術，其目的是通過在應用程式中注入惡意的SQL語句，以繞過應用程式的安全機制，從而對資料庫進行非法操作或者取得機敏資料。

## DDL / DML / DQL

分別為三種語言, 為構築 SQL 的基本功能

### DDL (**D**ata **D**efinition **L**anguage 資料定義語言)

- 定義 SQL 的結構與組件
- 主要為 CREATE、ALTER 和 DROP 等, 用於創建, 修改, 刪除等
- 對象為資料表

### DML (**D**ata **M**anipulation **L**anguage 資料操作語言)

- 操作 SQL 數據
- 主要為 INSERT、UPDATE 和 DELETE 等, 用於增、刪、改數據。
- 對象為數據

### DQL (**D**ata **Q**uery **L**anguage 資料查詢語言)

- 查詢 SQL 數據
- 主要為 SELECT 用於查詢數據

# **SQL 語法**

SQL 語法是建立於 SQL 92 (但各家廠商各有客制

需了解Ｃ新增、Ｒ查詢、Ｕ更新、Ｄ刪除與交叉查詢

create database 資料庫名字;
//建立資料庫
drop database 資料庫名字;
//刪除資料庫, 直接掰掰, 無法救回

可寫一行, 也可分段寫, 結尾的分號要寫以免多行時出現意外

大小寫不限制, 但在 lunix 中有區分大小寫, 建議使用一種種類後就不要混用

## 資料型態

1. 字串型態（String Types)
- CHAR(n) : 固定長度字串, 若未滿長度則會補上空白, 超過可能會截斷或出錯 (無法寫入)
- VARCHAR(n) : 變動長度字串, 多少字就存多少字, 會有額外 1 byte 紀錄長度 (此byte不算在長度內)
- TEXT : 長字串, 用於存長文章

可變長度的字串且希望節省儲存空間時, 用`VARCHAR`
而長度固定且相對較小，或注重查詢效能方面，可以考慮使用`CHAR`

2. 數字
- decimal : 十進位數字
- integer : 整數
3. 日期時間
- date : 日期，格式為'YYYY-MM-DD'。
- datetime : 日期和時間的組合，格式為'YYYY-MM-DD HH:MM:SS'。
4. blob = binary large object : 二進制大型物件, 圖像、音頻或影片
5. 其他 (可藉由 GPT 查詢, 總類繁複)

## 基礎操作 (base on SQLite)

### 資料庫

CREATE DATABASE some_db; //創立資料庫 (some_db 為自定義的資料庫名稱

DROP DATABASE some_db; //刪除資料庫, 直接掰掰, 沒救援手段

可只寫一行, 也可以分段寫, 最後的分號要寫, 以免多行時出意外

### 資料表

新增資料表

CREATE TABLE some_table ( //some_table 為自定義的表單名稱, 下放輸入各欄位資訊
		id INT NOT NULL AUTO_INCREMENT,  //id 此欄為整數, 不可為空, 自動生成序號
		name VARCHAR(100) NOT NULL,      //名字 此欄最多100字, 不可為空
		gender CHAR(1),                  //性別 此欄固定皆為一個字
		description TEXT,                //敘述 此欄為長串文字
		PRIMARY KEY(id)                  //此資料表的主 key 為 id
)

刪除資料表

DROP TABLE some_table; //建議直接用圖形介面點選就好

追加欄位

ALTER TABLE some_table
ADD COLUMN some_item VARCHAR(5); //新增一個為 some_item 的欄位, 並記錄最多五字元的項目

刪除欄位

ALTER TABLE some_table
DROP COLUMN some_item;  //刪除 some_item 的欄位, 

## 資料操作 (base on SQLite)

### 新增資料 (C)

INSERT INTO some_table
(name, age, gender, description)
VALUES
('名字', 20, 'F', "文字要加引號, 數字不用, 有需要符號用反斜線跳脫"); //一個項目對一個值

省略欄位

INSERT INTO some_table
VALUES
('名字', 20, 'F', "文字要加引號, 數字不用, 有需要符號用反斜線跳脫"); //同一個蘿菠一個坑

寫入部分欄位

INSERT INTO some_table
(description, age, name)
VALUES
("文字要加引號, 數字不用, 有需要符號用反斜線跳脫", 20, '名字'); //可不依順序寫, 但項目同依序填入

### 查詢資料 \(R)

SELECT *          //星號代表全部欄位, 此處可填入欄位名稱
FROM some_table

過濾資料

追加條件就可以用 AND 追加

SELECT *
FROM some_table
WHERE age = 20; //在 some_table 內找尋全部欄位, age = 20 的挑出來
WHERE age = 20 AND gender = 'F' // 同上條件, 但必須是女性

查詢空值

SELECT *
FROM some_table
WHERE age IS NULL; //some_table 內 age 為空值

查詢關鍵字

將 = 或 is 改成 like

% 代表字元, %我, 代表我前面有其他字, 我%代表我後面有字, %我%, 代表前後有字

SELECT *
FROM some_table
WHERE name LIKE '%英%'; //名字裡有 英 的人

網站的模糊查詢

網站的模糊是以不一樣的方式運作, 一般的查詢系統與模糊比對的查詢對比如下 :

- 一般：User -> 關鍵字 -> App -> DB -> result
- 模糊：User -> 關鍵字 -> App -> (DB索引) -> DB -> result

通常業界使用 ElasticSearch 來當 DB 索引 (簡單說就是 DB 的 DB

區間

SELECT *
FROM some_table
WHERE age BETWEEN 10 AND 25; //也可用以下
WHERE age >= 10 AND age <=25 

注意 “跟” ,如列出成績 “A” 跟 “B”, 但實際上不會有人同時有 A 跟 B 所以要用 or

SELECT *
FROM some_table
WHERE grade = "A" or grade = "B"; // 也可用以下
WHERE grade IN ('A', 'B');

不是 (SQL 內 <> 為不等於的意思 )

SELECT *
FROM some_table
WHERE grade <> "A" AND grade <> "B"; // 也可用以下
WHERE grade NOT IN ('A', 'B');

### 更新資料 (U)

UPDATE some_table   //哪個表
SET age = 10        //要改什麼
WHERE id = 25       //條件(目標)
// 如果此處沒有 WHERE 則是全部資料的 age 改成 10

多筆更新

UPDATE some_table
SET age = 10, gender = "M", grade = "C"  //要改什麼用逗號隔開
WHERE id = 25      

也可使用運算符號子

UPDATE some_table 
SET age += age      // age 加一歲
WHERE id = 25      

### 刪除資料 (D)

DELETE FROM some_table
WHERE grade = 'C';        //將成績Ｃ的通通刪掉

## 進階操作

### 計數 (計算總數)

SELECT COUNT(*)
FROM some_table
WHERE grade = 'A'   //計算成績為 A 的總人數

### 加總、平均

SELECT SUM(age)     // 用 SUM 計算有成績且成績Ａ的總年齡
FROM some_table
WHERE grade = 'A' AND age IS NOT NULL;

SELECT AVG(age)     // 用 AVG 計算有成績且成績Ａ的平均年齡
FROM some_table
WHERE grade = 'A' AND age IS NOT NULL;

### 最大最小值

SELECT MAX(age)     //最小則用 MIN
FROM some_table

### 分組

SELECT grade, SUM(age)  // 顯示成績, 並依成績分組後的年齡加總
FROM some_table
GROUP BY grade;  //依成績分組

### 挑掉重複

SELECT DISTINCT grade   // 顯示出總共有哪些成績
FROM some_table;

### 排序

SELECT *
FROM some_table
ORDER BY birthday ASC;  // 依生日排序

ASC 可以不寫, 本身就是以升冪排序為預設 ; DESC 則為降冪排序

### 數量限制

SELECT *
FROM some_table
ORDER BY grade 
LIMIT 5;           //依成績排序, 並顯示前五名

# ER 圖 (Entity-Relationship Diagram)

實體關係圖是用來描述系統中**重要的個體**及**其間的關係**。

實體 : 是指用以描述真實世界的物件。

在實務需求上我們可以將 實體 轉換成各種資料表

關係 : 是指用來表示 一個實體 與 另一個實體 關聯的方式。

如 : 一對一關係 、一對多關係 、多對多關係。

超簡易繪製流程：

1. 掌握業務流程和規則
2. 從中抽出「實體」
3. 檢核抽出的「實體」是否有不足或多餘的
4. 最後設定實體的「關聯」

若多對多的資料關聯會由第三方表格紀錄 (join table)

## 鍵 KEY

### 主鍵 Primary Key / PK

- 資料表中 (table) 某一個欄位
- 不可為空值 (null)
- 獨一無二的值

### 外部鍵 Foreign Key / FK

- 資料表中 (table) 某一個欄位
- 可以參照另一個資料表主鍵
- 確認該資料與其他資料表的內容可以對起來
- 可以為空值, 也不需要獨一無二

外部鍵有其限制 :

1. 參照完整性 : 確保建立時, 此外部鍵存在於另一個資料表中, 如 :

訂單中, 外部鍵為購買顧客 ID, 需確保此 ID 在顧客的資料表內是真實存在的

2. 一致性 : 即表語表之間的數據一致性, 當在父表中更新或刪除時, 外鍵約束可以定義相應的操作, 於子表做相同資料的更新與刪除
3. 數據完整性 : 即防止無效或不一致的數據進入數據庫。
4. 約束條件 : 可以定義一些約束條件, 如級聯操作 (CASCADE)、禁止操作 (RESTRICT) 等, 以規定父表的紀錄產生變化時, 子表相關紀錄的處理方式

## 多表參照查詢

### 交叉查詢及操作

SELECT *
FROM score_table
WHERE student =       // 從 score_table 找出 class_table 內為李某的 data
(                     // c 表的李某 id 會匯出至 s 表內查詢, 若 student 的資料對到則會顯示出來
SELECT studentid
FROM class_table
WHERE name = '李某'
);

也可多項目一起查詢

SELECT name, scroe
FROM score_table
WHERE student IN
(
SELECT studentid
FROM class_table
WHERE name IN ('李某','陳某')
);

### 交集 (join)

有分 inner / left / right (但SQLite 沒支援right)
Join 預設為 inner

SELECT *
FROM t1
JOIN t2  // 列出 t1 與 t2 兩兩相比的表, 總數為 t1數 * t2數

預設 inner 的狀況

SELECT *
FROM t1
JOIN t2 
WHERE t1.username = t2.name //請依 t1 的 username 為底, 對照 t2 的 name, 一樣請列出
//此處 where 用 on 也可以

使用 left

SELECT *
FROM t1
LEFT JOIN t2 
ON t1.username = t2.name
//此時 t1 表的內容會全數留下, 但在 t2 表內名字不符的會被屏棄, t1 內同名的會加入 t2 的資訊
//而 t1 沒有的相符名子的, t2 新增進來的欄位則以 null 填入

Join table 多表資料混合

SELECT b.*, h.name          // 顯示戰鬥歷史全部的欄目與英雄表內的名字欄目
FROM battle_histories as b  // 將戰鬥歷史的 table 以 b 代稱
JOIN heroes as h            // 將英雄的 table 以 h 代稱
ON b.hero_id = h.id         

可用 as 給予別名, 甚至不寫 as 也可以

SELECT b.*, h.name as 英雄 , m.name as 怪物 // 可改變最後顯示出來上方欄目的名稱
FROM battle_histories b
JOIN heroes h, monsters m
ON b.hero_id = h.id and b.monster_id = m.id  // 三張表交叉查詢