SQL基礎隨記1 SQL分類常用函式 ALL ANY EXISTS IN 約束

其實這里知識不難，只是好久不接觸突然被問的話有時還真的一時答不上，自己寫一遍勝過盲掃，當然，也有些常讀常新的地方會記錄下來，

對SQL語言進行劃分

DDL --- Data Definition Language --- 定義 --- 增刪改資料庫和表的結構

create  alter  drop		comment(注釋)  truncate（洗掉）

DML --- Data Manipulation Language --- 操作 --- 對記錄增刪改

insert  update  delete

DQL --- Data Quary Language --- 查詢 --- 對記錄進行查詢

select  from  where  group by  having  order by limit

DCL --- Data Control Language --- 控制 --- 訪問權限和安全規則

commit	rollback	set transaction		grant	revoke

要是要突然問的話有時還真的一時回答不上，記住單詞勝過記縮寫

DBMS的分類

關系型
- 行存盤 --- MySQL
NosQL
- 鍵值型 --- Redis
- 檔案型 --- MongoDB
- 搜索引擎 --- Elasticsearch
- 列存盤（列族資料庫）
- 圖形資料庫

列存盤資料庫說是“可以降低系統的I/O,但功能相對有限”，不過我看到了一段有意思的話覺得很有道理

列存盤常見于分布式檔案系統，如Hbase
圖（這種資料結構）存盤了物體（物件）之間的關系，以最典型的人與人社交關系為例，其資料模型主要是以節點和邊來實作，特點在于可以有效解決復雜的關系問題，

DDL

創建表時可以在create table tableName (...)后面追加引數，可以追加的有
- engine = InnoDB
- ```
CHARACTER SET = utf8 COLLATE = utf8_general_ci
```
  其中CHARACTER SET是指定字符編碼，COLLATE是指定排序規則，且utf8_general_ci是對大小寫不敏感，對大小寫敏感是utf8_bin
  
  在定義varchar（）型別欄位的時候也可以后綴CHARACTER SET = xxx COLLATE = yyy
在創建索引的時候我們可以選擇不同型別的索引（UNIQUE INDEX --- 唯一索引或 NORMAL INDEX --- 普通索引）以及不同的索引方式（BTREE 或 HASH），如
```
CREATE TABLE XXX(
...
UNNIQUE INDEX indexName (欄位名) USING BTREE
...
)
```
索引分為單列索引和組合索引，組合即一個索引可以包含多個列
約束
- 主鍵，可以是一個欄位也可以是多個欄位
- 外鍵
- UNIQUE （唯一性）約束，
- NOT NULL 約束
- DEFAULT 約束，在插入記錄時如果該欄位為空，那么就會設定為默認值，（而不是“不變數”的意思，它仍然可變）
- CHECK 約束（MySQL8.0.16后版本支持）
理論上設計資料表的“三少一多”原則
- 表個數少
- 表中欄位少
- 聯合主鍵欄位少
- 主鍵和外鍵多
  
  表的設計核心就是簡單可復用，主鍵是一張表的代表，因此主鍵外鍵越多，說明表之間的利用率最高，
  
  但這個原則不是絕對的，因為有時我們需要犧牲資料的冗余度來換取處理資料的效率，畢竟join總是會造成復雜，
  
  另外在大型專案中，大量的更新以及高并發的情況下，外鍵會造成額外的開銷，也容易造成死鎖，因此在業務量較大時，可以采用在業務層實作，取消外鍵來提高效率，因此在實際生產中為了方便維護基本不使用外鍵，
  
  另外也不推薦使用自增長主鍵，不利于維護，例如銀行一般使用唯一表示欄位uuid+日期+渠道流水(unique index)來保證資料唯一性，
- 修改欄位型別不要忘記COLUMN
```
ALTER TABLE tableName MODIFY COLUMN newName type;
```
  修改欄位名
```
ALTER TABLE tableName CHANGE oldName newName type;
```
  修改大表欄位需要謹慎容易引發表結構寫鎖，（0623）

DQL隨記

發現一個有趣的新用法 --- 在查詢時插入臨時列

使用單引號可以將單引號中內容作為定值，然后在查詢中臨時加入該列（臨時意味著并不改變表的結構，僅在回傳時臨時插入）
Order by 后有多個列時，會先按照第一個列進行排序，如果第一列的值相同，則會根據第二列進行排序，以此類推，

select 陳述句的關鍵詞順序（很基礎的還是默一遍吧）

SELECT...(DISTINCT)...FROM...WHERE...GROUP BY...HAVING...ORDER BY...LIMIT

select 陳述句執行順序（0624）

先找表再分組再排序；分組前WHERE分組后HAVING；排序前SELECT+DISTINCT，排序后LIMIT
```
FROM
	WHERE
GROUP BY
	HAVING
	SELECT+DISTINCT
ORDER BY
	LIMIT
```

BETWEEN AND 的邊界值
- 對于數值，包含左右邊界
- 對于data型別，查詢時不帶分秒，包含左右邊界
- 對于datatime型別，查詢時不帶分秒，只包含左邊界，
  
  因為只查詢yyyy-MM-DD時，默認將時分秒設定為00:00:00，即在右邊界的日期一開始的時候就會停止查詢，遂不包含右邊界，

少見的運算子號
- REGEXP --- 正則運算式（RegExp）--- SELECT REGEXP
- <=> --- SELECT a<=>b FROM ... --- 當a，b都為NULL時回傳1，否則回傳0
```
<=>與=不同的是，當a，b都為null時
a<=>b 回傳 1
a=b 回傳 null
```

通配符 % 是匹配大于等于0個任意數量的字符

SQL常用函式

算術函式

ABS() --- 取絕對值
MOD(a,b) --- 取余，a%b的余數
ROUND(a,b) --- 四舍五入保留位數，保留b位小數

字串函式

CONCAT("a",b) --- 拼接字串，a必須有單引號或者雙引號，后面的可以沒有，在MySQL中可以連接多個字串
LENGTH(“a”) --- 回傳字串a的長度，中文占三個字符

CHAR_LENGTH() --- 回傳字串的長度，中文也只算一個字符
LOWER(),UPPER()

REPALCE("要被替換的字串","要替換的部分","替換內容")

SELECT REPLACE("CIVILAZATION","IVILAZATION",LOWER("IVILAZATION"));

回傳

+------------------------------------------------------------+
| REPLACE("CIVILAZATION","IVILAZATION",LOWER("IVILAZATION")) |
+------------------------------------------------------------+
| Civilazation                                               |
+------------------------------------------------------------+
1 row in set (0.00 sec)

SUBSTRING ("要被截取的字串",開始截取的位置，截取長度)

截取位置的第一位從1開始

每個中文漢字也只占一個長度

時間函式

當前時間

CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP --- （時間戳）具體從年到秒的時間

EXTRACT(xxx FROM （符合格式的時間或者函式）)

mysql> SELECT EXTRACT(YEAR FROM CURRENT_TIMESTAMP);
+--------------------------------------+
| EXTRACT(YEAR FROM CURRENT_TIMESTAMP) |
+--------------------------------------+
|                                 2020 |
+--------------------------------------+
1 row in set (0.00 sec)

上面的xxx可以用下列函式的名稱替換

? YEAR()、MONTH()、DAY()、HOUR()、MINUTE()、SECOND()
DATE()

轉換函式

CAST(),目標型別可以是以下型別之一：BINARY，CHAR，DATE，DATETIME，TIME，DECIMAL，SIGNED，UNSIGNED，

mysql> SELECT CONCAT('Your num is ',CAST(7 as char));
+----------------------------------------+
| CONCAT('Your num is ',CAST(7 as char)) |
+----------------------------------------+
| Your num is 7                          |
+----------------------------------------+
1 row in set (0.00 sec)

CONVERT(欄位名/字串 as 字符集名)

用于轉換字符集

COALESCE()，回傳引數中第一個非慷訓傳值

mysql> SELECT COALESCE(NULL, NULL, NULL, 'Google', NULL, 'baidu');
+-----------------------------------------------------+
| COALESCE(NULL, NULL, NULL, 'Google', NULL, 'baidu') |
+-----------------------------------------------------+
| Google                                              |
+-----------------------------------------------------+
1 row in set (0.00 sec)

忽略NULL

COUNT(*) 只統計行數，無論某行中某些欄位是否為null

COUNT（欄位）時會忽略欄位值為NULL的行，只統計欄位值不為NULL的行的總數
AVG()，MAX()，MIN() 也會忽略為NULL的資料行
- MAX()，MIN() 也可以對字串型別進行統計，按照英文或漢語字母順序從前到后，越向后越大

ANY ALL

any是只要滿足任何一個子查詢的回傳值&&滿足比較條件就回傳結果，some是any的別名
all是只有滿足所有子查詢的回傳值&&滿足比較條件才回傳結果

EXISTS IN

EXISTS 和IN 很相似，使用IN時要提起寫出欄位，因此適合在知道去哪個欄位里查詢時使用
in是先執行子查詢并得到一個結果集，再將結果集帶入外層謂詞條件，子查詢只進行一次，

EXISTS是先取一條主查詢中的資料，再將資料帶入并執行一次子查詢，主查詢有多少資料子查詢就會進行多少次，
因為in和exists的查詢機制，為了效率，外表資料量大使用in，外表資料量小使用exists

即小表驅動大表
```
#現有A，B兩表，
SELECT * FROM A WHERE columnA IN (SELECT columnA FROM B);
SELECT * FROM A WHERE columnA EXISTS (SELECT columnA FROM B WHERE B.columnA=A.columnA);
```
假如A表大于B表，使用IN較好，因為IN先使用的是較小的表B

假如A表小于B表，使用EXISTS較好，因為EXISTS先使用的是較小的表A
NOT EXISTS 與 NOT IN 之間最好使用 NOT EXIST 或者是 NOT IN ( ... IS NOT NULL)

因為NULL值是無法被比較的，IN在先執行子查詢時可能會過濾掉有null值得那一行

當一句Where id not in('A123456789','B123456789')執行時其實就是跟 Where id<>'A123456789' and id<>'B123456789' 是一樣的，而用and就是要所有條件都是True才會是True，只要有一個條件不成立就會是False，因此當比對的欄位有NULL時(例 : Where id<>'A123456789' and id<>NULL)，而NULL是不能比較的，因此id<>NULL會判斷是False，一但有一條件是回傳False，那整個Where條件式就是False了，因此這一筆資料就會被認為是不符合的，

具體案例可見此處

NULL對NOT IN的影響本質上是對SELECT子查詢的影響，因此在select查詢時也要注意