# SQL
## Concept
資料查詢語言(**DQL**, Data Query Language):
**SELECT**
資料操作語言(**DML**, Data Manipulation Language):
**INSERT/ UPDATE/ DELETE**
資料定義語言(**DDL**, Data Definition Language):
DDL不可以rollback
**CREATE/ ALTER/ DROP/ RENAME/ TRUNCATE**(截斷:用於清空table裡的資料)
資料控制語言(**DCL**, Data Control Language):
**GRANT**(授予權限)/ **REVOKE**(移除權限)
## DDL
### 修改欄位
ref: https://www.1keydata.com/tw/sql/sql-alter-table.html
## CRUD (DQL,DML)
### 查詢資料select
```sql
SELECT 欄位1,欄位2 FROM 表格 WHERE 條件;
```
* AND運算子，左右**兩邊都必須滿足**才會被查到
* OR運算子，左右**有一邊滿足**就會被查到
```sql
SELECT 欄位1,欄位2 FROM 表格 WHERE 條件1 [AND/OR] 條件2;
```
* 也可讓**括號內的條件先判斷**做更複雜的查詢
```sql
SELECT 欄位1,欄位2 FROM 表格 WHERE 條件1 AND (條件2 OR 條件3);
```
**ORDER BY**排序
```sql
SELECT * FROM 表格 [WHERE 條件] ORDER BY 欄位1 [ASC/DESC],欄位2 [ASC/DESC];
--語法糖:也可以寫成ORDER BY 1,2，意思相同
```
**DISTINCT** 排除重複
```sql
SELECT DISTINCT 欄位1,欄位2 FROM 表格;
```
**(NOT) IN** 拿**特定值**(以外)資料
```sql
SELECT * FROM 表格 WHERE 欄位 (NOT) IN ('xx','yy');
```
**(NOT) BETWEEN** 拿**特定區間**(以外)資料
```sql
SELECT * FROM 表格 WHERE 欄位 (NOT) BETWEEN 1 AND 100;
```
**(NOT) LIKE** 搜尋**相似條件**(以外)資料
```sql
SELECT * FROM 表格 WHERE 欄位 (NOT) LIKE '%大%';
```
* **Wildcard萬用字元**: 與**LIKE**搭配使用，可用於替代空出來的模糊字元以用於搜尋
**%**: 代替0~n個任意字元
**_**: 代替1個字元
**[字元]**: 代替1個指定字元
**[!字元]** 或 **[^字元]**: 代替1個指定排除字元
**AS**把欄位或表格**重新命名**
```sql
SELECT 欄位1 AS 新名1, 欄位2 AS 新名2 FROM 表格 AS 新表格名;
```
**SubQuery子查詢**
將括弧內查詢子句視為一個暫時的table再做查詢
```sql
SELECT count(*) FROM
( SELECT * FROM tableA JOIN tableB...略) AS resultTable
```
**資料庫查詢分頁**
```sql
SELECT ROW_NUMBER() OVER
(ORDER BY 欄位1 ASC) AS row_num, --放在最前面作為索引
* FROM table_a WHERE 1=1
ORDER BY 欄位1 ASC
OFFSET 0 * 5 rows fetch next 5 ROWS ONLY;
```
### 新增資料insert
```sql
INSERT INTO 表格 VALUES (欄位1值,欄位2值); --每個值都要依序寫
INSERT INTO 表格 (欄位1,欄位2) VALUES (欄位1值,欄位2值); --可以只寫特定幾個欄位
```
一次新增多筆 INSERT INTO VALUES
```sql
INSERT INTO 表格
VALUES (欄位1值,欄位2值),
(欄位1值,欄位2值),
(欄位1值,欄位2值); --每個值都要依序寫
```
從其他表取得資料新增 INSERT INTO SELECT
```sql
INSERT INTO 表格 (欄位1,欄位2)
SELECT 其他表格欄位1,其他表格欄位2 FROM 其他表格;
```
將查到的資料建成一個新表格 SELECT INTO(MySQL不支援)
```sql
SELECT 欄位1,欄位2
INTO 新表格 FROM 現有表格;
SELECT 欄位1,欄位2
INTO 新表格 FROM 現有表格 WHERE 0=1; --只複製表格結構
```
### 更新資料update
```sql
UPDATE 表格 SET 欄位1=欄位1值,欄位2=欄位2值 WHERE 條件;
UPDATE table1 alias
SET (欄位1, 欄位2 ) = (
SELECT 欄位1, 欄位2 FROM table2
WHERE table2.column_name = alias.column_name)
[WHERE column_name = VALUE]
```
### 刪除資料delete
```sql
DELETE FROM 表格 WHERE 條件;
```
### 清除資料表truncate
```sql
TRUNCATE TABLE XXXtable;
```
## JOIN
JOIN基本語法:
```sql
SELECT a.*, b.* from table_a a
join table_b b on a.join欄位 = b.join欄位;
```
不同種類的JOIN:
**(INNER) JOIN**: 內部連結，只有**左右表都符合**的結果才會出現
**LEFT JOIN**: 左外部連結，只要**左表**(寫在前join的表)有的就會出現在結果
**RIGHT JOIN**: 右外部連結，只要**右表**(寫在後被join的表)有的就會出現在結果
**FULL JOIN**: 全部外部連結，**無論有無符合條件**結果均會出現
**CROSS JOIN**: 交叉連結，兩表結合時，**不指定條件**，直接將兩者的所有可能排列出來
```sql
SELECT a.*, b.* from table_a a
cross join table_b b
```
**NATURAL JOIN**: 自然連結，有NATURAL JOIN、NATURAL LEFT JOIN、NATURAL RIGHT JOIN，簡單的來說就是會將兩表**同名欄位連結在一起的INNER JOIN**
**UNION**: 將兩個(以上) SQL 查詢的結果合併起來(**聯集**)，兩結果必須有同樣的欄位/型別/順序，結果資料列只會返回不同值如DISTINCT
```sql
SELECT 欄位1,欄位2... FROM table_a
UNION
SELECT 欄位1,欄位2... FROM table_b;
```
**UNION ALL**: 與UNION接近，只差在**不論是否重複皆返回**
**INTERSECT**: **交集**，為前語句與後語句有**重疊**的部分
```sql
SELECT 欄位1,欄位2... FROM table_a
INTERSECT
SELECT 欄位1,欄位2... FROM table_b;
```
**EXCEPT**(ORACLE使用**MINUS**): **差集**，為(前語句結果) - (後語句結果)部分
```sql
SELECT 欄位1,欄位2... FROM table_a
EXCEPT
SELECT 欄位1,欄位2... FROM table_b;
```
## Constraint
Constraint是用於限制欄位那些資料可以被存入資料表中，主要是建立資料表時的指定條件或是之後可以ALTER TABLE修改:
### NOT NULL
限制欄位不能接受空值，使用方式為直接在欄位後加NOT NULL:
```sql
CREATE TABLE XXX (
ID INT NOT NULL,
NAME VARCHAR(50) NOT NULL)
```
### UNIQUE
限制欄位必須唯一，使用方式為直接在欄位後加UNIQUE:
```sql
CREATE TABLE XXX (
ID INT NOT NULL UNIQUE,
NAME VARCHAR(50) NOT NULL
)
```
或最後寫UNIQUE(欄位)
```sql
CREATE TABLE XXX (
ID INT NOT NULL,
NAME VARCHAR(50) NOT NULL,
UNIQUE(ID)
);
```
如果要綁定**複合唯一鍵**(只要不是全欄位重複皆可)可以寫成:
CONSTRAINT 唯一鍵名稱 UNIQUE(欄位1,欄位2)
```sql
CREATE TABLE XXX (
ID INT NOT NULL,
NAME VARCHAR(50) NOT NULL,
CONSTRAINT U_XXX_ID UNIQUE (ID,NAME)
);
```
### PRIMARY KEY
限制欄位為**主鍵**，使用方式與UNIQUE接近，直接在欄位後加PRIMARY KEY
```sql
CREATE TABLE XXX (
ID INT NOT NULL PRIMARY KEY,
NAME VARCHAR(50) NOT NULL
)
```
或最後寫PRIMARY KEY(欄位)
```sql
CREATE TABLE XXX (
ID INT NOT NULL,
NAME VARCHAR(50) NOT NULL,
PRIMARY KEY(ID)
);
```
綁定**複合主鍵**時，可以CONSTRAINT 主鍵名稱 PRIMARY KEY(欄位1,欄位2)
```sql
CREATE TABLE XXX (
ID INT NOT NULL,
NAME VARCHAR(50) NOT NULL,
CONSTRAINT PK_XXX_ID PRIMARY KEY (ID,NAME)
);
```
### FOREIGN KEY
限制欄位為外來鍵
```sql
CREATE TABLE XXX (
ID INT NOT NULL,
NAME VARCHAR(50) NOT NULL,
PRIMARY KEY (ID),
FOREIGN KEY (C_ID) REFERENCES CUSTOMERS(C_ID)
);
```
或是CONSTRAINT 外來鍵名 KEY(外來鍵欄位1) REFERENCES 參考table(參考欄位1)
```sql
CREATE TABLE XXX (
ID INT NOT NULL PRIMARY KEY,
NAME VARCHAR(50) NOT NULL,
CONSTRAINT FK_XXX_ID FOREIGN KEY (C_ID) REFERENCES CUSTOMERS(C_ID)
);
```
### CHECK(MySQL使用無效)
用來限制欄位中可以放入的值，寫法為CHECK(欄位名 限制條件)
```sql
CREATE TABLE XXX (
ID INT NOT NULL,
NAME VARCHAR(50) NOT NULL CHECK (NAME!='XXX'),
PRIMARY KEY (ID),
);
```
多欄位檢查時可以這樣寫:
```sql
CREATE TABLE XXX (
ID INT NOT NULL PRIMARY KEY,
NAME VARCHAR(50) NOT NULL,
CPRIMARY KEY (ID),
CONSTRAINT CHK_XXX CHECK (NAME!='XXX')
);
```
### DEFAULT
用來指定如INSERT時沒有填入該欄位值，則會使用該預設值，直接在欄位後方DEFAULT '預設值' 即可
```sql
CREATE TABLE XXX (
ID INT NOT NULL,
NAME VARCHAR(50) DEFAULT 'UNKNOWN',
PRIMARY KEY (ID),
);
```
## 常用函式(ORACLE)
```sql
NVL(欄位,如果欄位為null時的值)
DECODE(欄位,如欄位為值1,轉換值1,如欄位為值2,轉換值2,...,預設值)
REGEXP_LIKE(欄位,'正則表示式字串')
TO_DATE('待轉換字串','轉換格式字串')
TO_CHAR(待轉換日期,轉換格式字串)
TO_CHAR(SYSDATE,'yyyy-MM-dd') --快速轉化現在日期為指定格式字串
TO_DATE(TO_CHAR(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd') --現在日期轉化特定格式日期
TO_DATE('2020-05-30', 'YYYY-MM-dd') --指定日期字串轉換成DATE
to_date('2020-01-01 13:14:20', 'yyyy-MM-dd HH24:mi:ss'); --指定日期+時間轉換
REPLACE( 欄位, '欲替代字串', '轉換字串')
LENGTH(欄位) --計算欄位字串長度
TRUNC（date）--直接截到剩日期 yyyy-MM-dd
TRUNC（date[,fmt]） --用來截掉日期，fmt 日期格式，該日期將由指定的元素格式所截去。
TRUNC（number[,decimals]） --用來截掉數字，類似ROUND，不過此函示為無條件捨去
SELECT 序列名稱.nextval FROM DUAL; --查詢序列的下一個號碼，呼叫後會自己加上去
```
### 對每組colA取MIN/MAX (就不會只有取到整張tableA的MIN/MAX)
```sql
select MIN(colPK), colA from tableA group by colA
```
### 欄位colA去除重複，並依照date_column排序取最新
```sql
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY colA ORDER BY date_column DESC) AS rn
FROM your_table
) ranked
WHERE rn = 1;
```
### 分頁
```sql
SELECT ROW_NUMBER() OVER (ORDER BY date_column ASC) AS row_num,*
FROM your_table WHERE 1=1
ORDER BY date_column ASC
OFFSET 0 * 5 rows fetch next 5 ROWS ONLY; --
```
### 將table暫存於後續使用
```sql
WITH
tempTable AS (
select ...
),
tempTable2 AS (
select ...
)
select ... --後續的select子句，可以從tempTable tempTable2取值;
```
### 階層 (ORACLE)
接在語法最後，可以用來查出某資料轄下的所有資料
```sql
START WITH ParentNo= 'ROOT' --假設上面的欄位ParentNo名稱為ROOT
CONNECT BY PRIOR PartNo = ParentNo --PartNo連結ParentNo串出階層樹
```
## SQL調校
1. 確認清除整張表資料時**TRUNCATE**替代DELETE
2. 速度: count **(欄位)** < count **(*)** < count **(1)**
3. 儘量用 **union** 代替 **or**，如:
```sql
select * from 表 where id = 1 or id = 3;
```
可改為
```sql
select * from 表 where id = 1
UNION
select * from 表 where id = 3;
```
4. 如果有預設判斷值盡量不使用**IS NULL**， 如果要判斷IS NOT NULL可以用**where LENGTH(欄位) > 0**
5. where 條件中表示式、函式操作移動到**等號右側**(**不要直接對欄位運算**)
```sql
--會全表掃描
SELECT * FROM T WHERE score/10 = 9
--走索引
SELECT * FROM T WHERE score = 10*9
```
6. 盡量不使用 **<>** 或 **!=**，或**NOT**等否定查詢
7. in / exists ，如**可使用exist則不用join**
如兩表大小差不多: 兩者皆可
子查詢表**大**: 用**exist**
子查詢表**小**: 用**in**
*注意:exist不用加欄位，主要是在括弧裡串原表的id及條件，in則可直接用欄位條件
```sql
-- 不走索引
SELECT * FROM 表 WHERE category IN ('A','B');
-- 走索引
SELECT t1.* FROM 表 t1 WHERE exists
(SELECT * FROM 表 t2 where t2.category IN ('A','B') and t1.id = t2.id);
--如只是要另一個表的交集，但沒有需要其欄位值可以用以下
SELECT t1.* FROM 表 t1 WHERE exists
(SELECT 1 FROM 表 t2 where t2.category IN ('A','B') and t1.id = t2.id);
```
8. 用>=取代>，<=取代<，可以直接讓DB從該"="的地方開始找
9. 資料重複項多: distinct效率高
資料唯一項多: group by效率高。
```sql
select distinct 欄位a,欄位b,欄位c from 表
```
可改為
```sql
select 欄位a,欄位b,欄位c from 表 group by 欄位a,欄位b,欄位c
```
10. 沒有要排除重複時，可以用**UNION ALL**取代UNION
11. 子查詢盡量不要直接使用IN，可改為用一個**自定的表**取代
```sql
SELECT * FROM 表1 WHERE 欄位a IN (SELECT 欄位a FROM 表2)
```
可改為
```sql
SELECT * FROM 表1, (SELECT colA FROM 表2) as tempTable
WHERE 表2.欄位a = 表1.欄位a;
```
12. **避免使用型別不同的值**來比較，假設salary欄位為浮點數，但比較時用整數
```sql
--應使用60000.00
select * from employee where salary>60000
```
13. 需增加**虛擬欄位**時(ex. '10' as catory )，務必不要在join table或去除重複時用，可等join完再補，否則會拖垮速度
14. 能直接insert就不要update，如要update就一次update完(where條件指定好)
15. 能**insert into select**就不要cursor繞迴圈(PL/SQL)
Sign in with Wallet
Connect another wallet