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