其實(shí)這里知識(shí)不難，只是好久不接觸突然被問(wèn)的話有時(shí)還真的一時(shí)答不上，自己寫一遍勝過(guò)盲掃。當(dāng)然，也有些常讀常新的地方會(huì)記錄下來(lái)。

對(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 --- 控制 --- 訪問(wèn)權(quán)限和安全規(guī)則

commit	rollback	set transaction		grant	revoke

要是要突然問(wèn)的話有時(shí)還真的一時(shí)回答不上，記住單詞勝過(guò)記縮寫

DBMS的分類

• 關(guān)系型
  • 行存儲(chǔ) --- MySQL
• NosQL
  • 鍵值型 --- Redis
  • 文檔型 --- MongoDB
  • 搜索引擎 --- Elasticsearch
  • 列存儲(chǔ)（列族數(shù)據(jù)庫(kù)）
  • 圖形數(shù)據(jù)庫(kù)

• 列存儲(chǔ)數(shù)據(jù)庫(kù)說(shuō)是“可以降低系統(tǒng)的I/O,但功能相對(duì)有限”，不過(guò)我看到了一段有意思的話覺(jué)得很有道理

  

  列存儲(chǔ)常見于分布式文件系統(tǒng)，如Hbase

• 圖（這種數(shù)據(jù)結(jié)構(gòu)）存儲(chǔ)了實(shí)體（對(duì)象）之間的關(guān)系。以最典型的人與人社交關(guān)系為例，其數(shù)據(jù)模型主要是以節(jié)點(diǎn)和邊來(lái)實(shí)現(xiàn)。特點(diǎn)在于可以有效解決復(fù)雜的關(guān)系問(wè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ù)用，主鍵是一張表的代表，因此主鍵外鍵越多，說(shuō)明表之間的利用率最高。
     

    但這個(gè)原則不是絕對(duì)的，因?yàn)?strong>有時(shí)我們需要犧牲數(shù)據(jù)的冗余度來(lái)?yè)Q取處理數(shù)據(jù)的效率，畢竟join總是會(huì)造成復(fù)雜。

    另外在大型項(xiàng)目中，大量的更新以及高并發(fā)的情況下，外鍵會(huì)造成額外的開銷，也容易造成死鎖。因此在業(yè)務(wù)量較大時(shí)，可以采用在業(yè)務(wù)層實(shí)現(xiàn)，取消外鍵來(lái)提高效率。因此在實(shí)際生產(chǎn)中為了方便維護(hù)基本不使用外鍵。

    另外也不推薦使用自增長(zhǎng)主鍵，不利于維護(hù)。例如銀行一般使用唯一表示字段uuid+日期+渠道流水(unique index)來(lái)保證數(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)，后面的可以沒(méi)有，在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ù)

• 當(dāng)前時(shí)間

  • CURRENT_DATE

  • CURRENT_TIME

  • CURRENT_TIMESTAMP --- （時(shí)間戳）具體從年到秒的時(shí)間

  • EXTRACT(xxx FROM （符合格式的時(shí)間或者函數(shù)）)

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

• 上面的xxx可以用下列函數(shù)的名稱替換

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

• DATE()

轉(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ù)，無(wú)論某行中某些字段是否為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值是無(wú)法被比較的，IN在先執(zhí)行子查詢時(shí)可能會(huì)過(guò)濾掉有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í)也要注意

  

• MySQL不支持全外連接 （0625）

隨記2

• https://www.cnblogs.com/G-Aurora/p/13196872.html