|
其實這里知識不難，只是好久不接觸突然被問的話有時還真的一時答不上，自己寫一遍勝過盲掃。當(dāng)然，也有些常讀常新的地方會記錄下來。
對SQL語言進(jìn)行劃分
DDL --- Data Definition Language --- 定義 --- 增刪改數(shù)據(jù)庫和表的結(jié)構(gòu)
create alter drop comment(注釋) truncate（刪除）
DML --- Data Manipulation Language --- 操作 --- 對記錄增刪改
insert update delete
DQL --- Data Quary Language --- 查詢 --- 對記錄進(jìn)行查詢
select from where group by having order by limit
DCL --- Data Control Language --- 控制 --- 訪問權(quán)限和安全規(guī)則
commit rollback set transaction grant revoke
要是要突然問的話有時還真的一時回答不上，記住單詞勝過記縮寫
DBMS的分類
- 關(guān)系型
- NosQL
- 鍵值型 --- Redis
- 文檔型 --- MongoDB
- 搜索引擎 --- Elasticsearch
- 列存儲（列族數(shù)據(jù)庫）
- 圖形數(shù)據(jù)庫
-
列存儲數(shù)據(jù)庫說是“可以降低系統(tǒng)的I/O,但功能相對有限”，不過我看到了一段有意思的話覺得很有道理
列存儲常見于分布式文件系統(tǒng)，如Hbase
-
圖（這種數(shù)據(jù)結(jié)構(gòu)）存儲了實體（對象）之間的關(guān)系。以最典型的人與人社交關(guān)系為例，其數(shù)據(jù)模型主要是以節(jié)點和邊來實現(xiàn)。特點在于可以有效解決復(fù)雜的關(guān)系問題。
DDL
-
創(chuàng)建表時可以在create table tableName (...)后面追加參數(shù)，可以追加的有
-
engine = InnoDB
-
CHARACTER SET = utf8 COLLATE = utf8_general_ci
其中CHARACTER SET是指定字符編碼，COLLATE是指定排序規(guī)則。且utf8_general_ci是對大小寫不敏感，對大小寫敏感是utf8_bin
在定義varchar（）類型字段的時候也可以后綴CHARACTER SET = xxx COLLATE = yyy
-
在創(chuàng)建索引的時候我們可以選擇不同類型的索引（UNIQUE INDEX --- 唯一索引 或 NORMAL INDEX --- 普通索引）以及不同的索引方式（BTREE 或 HASH），如
CREATE TABLE XXX(
...
UNNIQUE INDEX indexName (字段名) USING BTREE
...
)
索引分為單列索引和組合索引，組合即一個索引可以包含多個列
-
約束
- 主鍵，可以是一個字段也可以是多個字段
- 外鍵
- UNIQUE （唯一性）約束，
- NOT NULL 約束
- DEFAULT 約束，在插入記錄時如果該字段為空，那么就會設(shè)置為默認(rèn)值。（而不是“不變量”的意思，它仍然可變）
- CHECK 約束 （MySQL8.0.16后版本支持）
-
理論上設(shè)計數(shù)據(jù)表的“三少一多”原則
-
表個數(shù)少
-
表中字段少
-
聯(lián)合主鍵字段少
-
主鍵和外鍵多
表的設(shè)計核心就是簡單可復(fù)用，主鍵是一張表的代表，因此主鍵外鍵越多，說明表之間的利用率最高。
但這個原則不是絕對的，因為有時我們需要犧牲數(shù)據(jù)的冗余度來換取處理數(shù)據(jù)的效率，畢竟join總是會造成復(fù)雜。
另外在大型項目中，大量的更新以及高并發(fā)的情況下，外鍵會造成額外的開銷，也容易造成死鎖。因此在業(yè)務(wù)量較大時，可以采用在業(yè)務(wù)層實現(xiàn)，取消外鍵來提高效率。因此在實際生產(chǎn)中為了方便維護(hù)基本不使用外鍵。
另外也不推薦使用自增長主鍵，不利于維護(hù)。例如銀行一般使用唯一表示字段uuid+日期+渠道流水(unique index)來保證數(shù)據(jù)唯一性。
-
修改字段類型不要忘記COLUMN
ALTER TABLE tableName MODIFY COLUMN newName type;
修改字段名
ALTER TABLE tableName CHANGE oldName newName type;
修改大表字段需要謹(jǐn)慎 容易引發(fā)表結(jié)構(gòu)寫鎖。（0623）
DQL隨記
-
發(fā)現(xiàn)一個有趣的新用法 --- 在查詢時插入臨時列
使用單引號可以將單引號中內(nèi)容作為定值，然后在查詢中臨時加入該列（臨時意味著并不改變表的結(jié)構(gòu)，僅在返回時臨時插入）
-
Order by 后有多個列時，會先按照第一個列進(jìn)行排序，如果第一列的值相同，則會根據(jù)第二列進(jìn)行排序，以此類推。
-
select 語句的關(guān)鍵詞順序（很基礎(chǔ)的還是默一遍吧）
SELECT...(DISTINCT)...FROM...WHERE...GROUP BY...HAVING...ORDER BY...LIMIT
-
select 語句執(zhí)行順序 （0624）
先找表再分組再排序；分組前WHERE分組后HAVING；排序前SELECT+DISTINCT，排序后LIMIT
FROM
WHERE
GROUP BY
HAVING
SELECT+DISTINCT
ORDER BY
LIMIT
-
少見的運算符號
-
REGEXP --- 正則表達(dá)式（RegExp）--- SELECT REGEXP
-
<=> --- SELECT a<=>b FROM ... --- 當(dāng)a，b都為NULL時返回1，否則返回0
<=>與=不同的是，當(dāng)a，b都為null時
a<=>b 返回 1
a=b 返回 null
- 通配符 % 是匹配 大于等于0個任意數(shù)量的字符
SQL常用函數(shù)
算術(shù)函數(shù)
- ABS() --- 取絕對值
- MOD(a,b) --- 取余，a%b的余數(shù)
- ROUND(a,b) --- 四舍五入保留位數(shù)，保留b位小數(shù)
字符串函數(shù)
-
CONCAT("a",b) --- 拼接字符串，a必須有單引號或者雙引號，后面的可以沒有，在MySQL中可以連接多個字符串
-
LENGTH(“a”) --- 返回字符串a(chǎn)的長度，中文占三個字符
CHAR_LENGTH() --- 返回字符串的長度，中文也只算一個字符
-
LOWER(),UPPER()
-
REPALCE("要被替換的字符串","要替換的部分","替換內(nèi)容")
SELECT REPLACE("CIVILAZATION","IVILAZATION",LOWER("IVILAZATION"));
返回
+------------------------------------------------------------+
| REPLACE("CIVILAZATION","IVILAZATION",LOWER("IVILAZATION")) |
+------------------------------------------------------------+
| Civilazation |
+------------------------------------------------------------+
1 row in set (0.00 sec)
-
SUBSTRING ("要被截取的字符串",開始截取的位置，截取長度)
截取位置的第一位從1開始
每個中文漢字也只占一個長度
時間函數(shù)
轉(zhuǎn)換函數(shù)
-
CAST(),目標(biāo)類型可以是以下類型之一：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 字符集名)
用于轉(zhuǎn)換字符集
-
COALESCE()，返回參數(shù)中第一個非空返回值
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(*) 只統(tǒng)計行數(shù)，無論某行中某些字段是否為null
COUNT（字段）時會忽略字段值為NULL的行，只統(tǒng)計字段值不為NULL的行的總數(shù)
-
AVG()，MAX()，MIN() 也會忽略為NULL的數(shù)據(jù)行
- MAX()，MIN() 也可以對字符串類型進(jìn)行統(tǒng)計，按照英文或漢語字母順序從前到后，越向后越大
ANY ALL
- any是
只要滿足任何一個子查詢的返回值&&滿足比較條件就返回結(jié)果。some是any的別名
- all是
只有滿足所有子查詢的返回值&&滿足比較條件才返回結(jié)果
EXISTS IN
-
EXISTS 和IN 很相似，使用IN時要提起寫出字段，因此適合在知道去哪個字段里查詢時使用
-
in是先執(zhí)行子查詢并得到一個結(jié)果集，再將結(jié)果集帶入外層謂詞條件。子查詢只進(jìn)行一次。
EXISTS是先取一條主查詢中的數(shù)據(jù)，再將數(shù)據(jù)帶入并執(zhí)行一次子查詢。主查詢有多少數(shù)據(jù)子查詢就會進(jìn)行多少次。
-
因為in和exists的查詢機(jī)制，為了效率，外表數(shù)據(jù)量大使用in，外表數(shù)據(jù)量小使用exists
即小表驅(qū)動大表
#現(xiàn)有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在先執(zhí)行子查詢時可能會過濾掉有null值得那一行
當(dāng)一句Where id not in('A123456789','B123456789')執(zhí)行時其實就是跟 Where id<>'A123456789' and id<>'B123456789' 是一樣的。而用and就是要所有條件都是True才會是True，只要有一個條件不成立就會是False。因此當(dāng)比對的欄位有NULL時(例 : Where id<>'A123456789' and id<>NULL)，而NULL是不能比較的，因此id<>NULL會判斷是False。一但有一條件是回傳False，那整個Where條件式就是False了，因此這一筆資料就會被認(rèn)為是不符合的。
具體案例可見此處
NULL對NOT IN的影響本質(zhì)上是對SELECT子查詢的影響，因此在select查詢時也要注意
隨記2
|
