一、Create

insert [into] table_name [(column [, column] ...)] values (value_list) [, (value_list)] ...
value_list: value, [, value] ...

values 左側(cè)括號(hào)內(nèi)是列屬性，右側(cè)括號(hào)內(nèi)是列屬性對(duì)應(yīng)的內(nèi)容，必須在類型和數(shù)值上一一對(duì)應(yīng)。

若忽略 values 左側(cè)括號(hào)內(nèi)的內(nèi)容，則稱為全列插入，否則成為按列插入。

創(chuàng)建一張學(xué)生表：

1、單行數(shù)據(jù) + 全列插入

• 插入兩條記錄，value_list 數(shù)量必須和定義表的列的數(shù)量及順序一致。
• 注意，這里在插入時(shí)，也可以不用指定 id（當(dāng)然，那時(shí)候就需要明確插入數(shù)據(jù)到哪些列了），那么 MySQL 會(huì)使用默認(rèn)的值進(jìn)行自增。

2、多行數(shù)據(jù) + 指定列插入

插入兩條記錄，value_list 數(shù)量必須和指定列數(shù)量及順序一致

3、插入是否更新

由于 主鍵  /  唯一鍵 對(duì)應(yīng)的值已經(jīng)存在而導(dǎo)致插入失敗。

可以選擇性的進(jìn)行同步更新操作：

insert ... on duplicate key update column=value [, column = value] ...

• 0 row affected：表中有沖突數(shù)據(jù)，但沖突數(shù)據(jù)的值和 update 的值相等。
• 1 row affected：表中沒有沖突數(shù)據(jù)，數(shù)據(jù)被插入。
• 2 row affected：表中有沖突數(shù)據(jù)，并且數(shù)據(jù)已經(jīng)被更新。

4、替換

• 主鍵 / 唯一鍵沒有沖突，則直接插入。
• 主鍵 / 唯一鍵如果沖突，則刪除后再插入。

• 1 row affected：表中沒有沖突數(shù)據(jù)，數(shù)據(jù)被插入。
• 2 row affected：表中有沖突數(shù)據(jù)，刪除后重新插入。

如果表中無重復(fù)，那直接是插入，若重復(fù)則替換。

二、Retrieve

select [distinct] {* | {column [, column] ...} [from table_name] [where ...] [order by column [asc | desc], ...] limit ... 

1、SELECT 列

（1）全列查詢

1. 查詢的列越多，意味著需要傳輸?shù)臄?shù)據(jù)量越大。
2. 可能會(huì)影響到索引的使用。

通常情況下不建議使用 * 進(jìn)行全列查詢。

（2）指定列查詢

指定列的順序不需要按定義表的順序來。

（3）查詢字段為表達(dá)式

• a. 表達(dá)式不包含字段

• b. 表達(dá)式包含多個(gè)字段

（4）為查詢結(jié)果指定別名

select column [as] alias_name [...] from table_name;

（5）結(jié)果去重

2、WHERE 條件

（1）比較運(yùn)算符

注意：在 MySQL 中， 用 = 來判斷兩個(gè) 字符串或數(shù)字 是否相等，與 C/C++ 不同。

用 = 判斷是否等于 NULL 這樣做是不安全的，因?yàn)?NULL 和 0 本身表示的含義不同，NULL 表示空，0 表示數(shù)字 0。

（2）邏輯運(yùn)算符

?練習(xí)

a. 練習(xí)一 —— 英語不及格的同學(xué)及英語成績

b. 練習(xí)二 —— 語文成績?cè)?nbsp;[80, 90] 分的同學(xué)及語文成績

c. 練習(xí)三 —— 數(shù)學(xué)成績是 58 / 59 / 98 / 99 分的同學(xué)及數(shù)學(xué)成績

d. 練習(xí)四 —— 姓孫的同學(xué)及孫某同學(xué)

e. 練習(xí)五 —— 語文成績好于英語成績的同學(xué)

f. 練習(xí)六 —— 總分在 200 分以下的同學(xué)

where 條件中使用表達(dá)式，別名不能用在 where 條件中。

g. 練習(xí)七 —— 語文成績 > 80 并且不姓孫的同學(xué)

h. 練習(xí)八 —— 孫某同學(xué)，否則要求總成績>200 并且 語文成績<數(shù)學(xué)成績 并且 英語成績>80 

練習(xí)九 —— NULL 的查詢

• 分別查詢姓名為空、為空字符串、不為空的

• NULL 和 NULL 的比較，= 和 <=> 的區(qū)別

• SELECT NULL <=> NULL, NULL <=> 1, NULL <=> 0

（3）結(jié)果排序

• ASC 為升序（從小到大）
• DESC 為降序（從大到?。?/li>

默認(rèn)為 ASC。

注意：沒有 ORDER BY 子句的查詢，返回的順序是未定義的，永遠(yuǎn)不要依賴這個(gè)順序。  

?練習(xí)

a. 練習(xí)一 —— 同學(xué)及數(shù)學(xué)成績，按數(shù)學(xué)成績升序顯示

b. 練習(xí)二 —— 同學(xué)名字，按名字排序顯示

• NULL 視為比任何值都小，升序出現(xiàn)在最上面。
• NULL 視為比任何值都小，降序出現(xiàn)在最下面。

c. 練習(xí)三 —— 查詢同學(xué)各門成績，依次按 數(shù)學(xué)降序，英語升序，語文升序的方式顯示

 d. 練習(xí)四 —— 查詢同學(xué)及總分，由高到低

為什么在這里又能夠使用別名了呢？

能否使用別名完全取決于當(dāng)前 sql 子句的執(zhí)行順序。 

order by 中可以使用表達(dá)式。

 e. 練習(xí)五 —— 查詢姓孫的同學(xué)或者姓曹的同學(xué)數(shù)學(xué)成績，結(jié)果按數(shù)學(xué)成績由高到低顯示

• 結(jié)合 where 子句 和 order by 子句

order by 子句的執(zhí)行順序晚于 where 子句。  

（4）篩選分頁結(jié)果

select ... from table_name [where ...] [order by ...] limit n;

3：從表開始()開始連續(xù)讀取 3 行。

• 從 s 開始，篩選 n 條結(jié)果

select ... from  table_name [where ...] [order by ...] limit s, n;

1：開始位置（下標(biāo)從 0 開始）。

3：步長，從指定位置開始，連續(xù)讀取 3 條記錄。

• 從 s 開始，篩選 n 條結(jié)果，比第二種用法更明確，建議使用

select ... from  table_name [where ...] [order by ...] limit n offset s;
mit n offset s;

4：步長，從指定位置開始，連續(xù)讀取 4 條記錄。

1：開始位置（下標(biāo)從 0 開始）。

注意 ：起始下標(biāo)為 0。

建議：對(duì)未知表進(jìn)行查詢時(shí)，最好加一條 limit 1 ，避免因?yàn)楸碇袛?shù)據(jù)過大，查詢?nèi)頂?shù)據(jù)導(dǎo)致數(shù)據(jù)庫卡死。 按 id 進(jìn)行分頁，每頁 3 條記錄，分別顯示 第 1 、 2 、 3 頁。 

只有數(shù)據(jù)準(zhǔn)備好了，才要顯示，limit 的本質(zhì)功能是 “顯示”。

得先有數(shù)據(jù)，才能 “顯示”，“顯示” 時(shí)，limit 只是告訴 MySQL，顯示時(shí)只顯示從哪里開始，從開始位置顯示幾行。

limit 不是條件篩選，本質(zhì)就是把數(shù)據(jù)準(zhǔn)備好，排好序，然后再 limit，執(zhí)行階段更靠后。

三、Update

update table_name set column=expr [, column = expr ...] [where ...] [order by ...] [limit ...]

對(duì)查詢到的結(jié)果進(jìn)行列值更新。

?練習(xí)

（1）練習(xí)一 —— 將孫悟空同學(xué)的數(shù)學(xué)成績變更為 80 分

• 數(shù)據(jù)更新

（2）練習(xí)二 —— 將曹孟德同學(xué)的數(shù)學(xué)成績變更為 60 分，語文成績變更為 70 分

• 一次更新多個(gè)列

（3）練習(xí)三 —— 將總成績倒數(shù)前三的 3 位同學(xué)的數(shù)學(xué)成績加上 30 分 

• 更新值為原值基礎(chǔ)上變更

注意：別名可以在 order by 中使用  

（4）練習(xí)四 —— 將所有同學(xué)的語文成績更新為原來的 2 倍

• 沒有 where 子句，則更新全表。

注意 ：更新全表的語句慎用。 

四、Delete

1、刪除數(shù)據(jù)

delete from table_name [where ...] [order by ...] [limit ...];

?練習(xí)

a. 練習(xí)一 —— 刪除孫悟空同學(xué)的考試成績

b. 練習(xí)二 —— 刪除整張表數(shù)據(jù)

注意 ：刪除整表操作要慎用。

2、截?cái)啾?/h3>

truncate [table] table_name;

• 截?cái)嗾頂?shù)據(jù)，注意影響行數(shù)是 0，所以實(shí)際上沒有對(duì)數(shù)據(jù)真正操作

• 再插入一條數(shù)據(jù)，自增 id 再重新增長

delete 和 truncate 的區(qū)別：

• 都可以清空表中的數(shù)據(jù)。
• delete from 是傳統(tǒng)的刪除，不會(huì)對(duì)計(jì)數(shù)器進(jìn)行清空或重新置位，而 truncate 清空表會(huì)重置 auto_increment 項(xiàng)。
• truncate 是直接將表中數(shù)據(jù)清空，它不走事務(wù)，而 delete from 以及之前學(xué)的 sql 最終在運(yùn)行時(shí)都要以事務(wù)的方式被包裝，然后再讓 MySQL 去運(yùn)行。

注意 ：這個(gè)操作慎用。

1. 只能對(duì)整表操作，不能像 delete 一樣針對(duì)部分?jǐn)?shù)據(jù)操作。
2. 實(shí)際上 MySQL 不對(duì)數(shù)據(jù)操作，所以比 delete 更快，但是 truncate 在刪除數(shù)據(jù)時(shí)，并不經(jīng)過真正的事物，所以無法回滾。
3. 會(huì)重置 auto_increment 項(xiàng)。 

五、插入查詢結(jié)果

insert into table_name [(column [, column ...])] select ...;  

?練習(xí)

（1）練習(xí)一 —— 刪除表中的的重復(fù)復(fù)記錄，重復(fù)的數(shù)據(jù)只能有一份

錯(cuò)誤思路：

• 創(chuàng)建一張空表（no_duplicate_table，結(jié)構(gòu)和 duplicate_table 一樣）

• 將 duplicate_table 的去重?cái)?shù)據(jù)插入到 no_duplicate_table

• 通過重命名表，實(shí)現(xiàn)原子的去重操作

為什么最后是通過 rename 方式進(jìn)行的？

就是單純的想等一切都就緒了，然后統(tǒng)一放入、更新、生效等。

六、聚合函數(shù)

?練習(xí)

（1）練習(xí)一 —— 統(tǒng)計(jì)班級(jí)共有多少同學(xué)

• 使用 * 做統(tǒng)計(jì)，不受 NULL 影響
• 使用表達(dá)式做統(tǒng)計(jì)

2）練習(xí)二 —— 統(tǒng)計(jì)本次考試的數(shù)學(xué)成績分?jǐn)?shù)個(gè)數(shù)

• count(math) 統(tǒng)計(jì)的是全部成績 

• count(distinct math) 統(tǒng)計(jì)的是去重成績數(shù)量

注意：

distinct 要寫在括號(hào)內(nèi)，因?yàn)槲覀兪且獙?duì) math 去重，而不是對(duì) count() 的結(jié)果去重。

NULL 不會(huì)計(jì)入結(jié)果。

（3）練習(xí)三 —— 統(tǒng)計(jì)數(shù)學(xué)成績總分

• 不及格 < 60 的總分，沒有結(jié)果，返回 NULL 

• 數(shù)學(xué)的平均成績

（4）練習(xí)四 —— 統(tǒng)計(jì)平均總分

（5）練習(xí)五 —— 返回英語最高分

（6）練習(xí)六 —— 返回 > 70 分以上的數(shù)學(xué)最低分

七、group by 子句的使用

分組的目的：為了進(jìn)行分組后，方便進(jìn)行聚合統(tǒng)計(jì)。

在 select 中使用 group by 子句可以對(duì)指定列進(jìn)行分組查詢：

select column1, column2, .. from table group by column;

1、準(zhǔn)備工作，創(chuàng)建一個(gè)雇員信息表（來自 Oracle 9i 的經(jīng)典測(cè)試表）

• emp 員工表

• dept 部門表

• salgrade 工資等級(jí)表

2、如何顯示每個(gè)部門的平均工資和最高工資

• 指定列名，實(shí)際分組是用該列不同的行數(shù)來進(jìn)行分組的。
• 分組的 deptno，組內(nèi)一定是相同的。說明可以被聚合壓縮。
• 分組就是把一組按照條件拆分成了多個(gè)組，進(jìn)行各自組內(nèi)的統(tǒng)計(jì)。
• 分組(“分表”)，就是把一張表按照條件在邏輯上拆成了多個(gè)子表，然后分別對(duì)各自的子表進(jìn)行聚合統(tǒng)計(jì)。

3、顯示每個(gè)部門的每種崗位的平均工資和最低工資

4、顯示平均工資低于 2000 的部門和它的平均工資

（1）統(tǒng)計(jì)出每一個(gè)部門的平均工資（結(jié)果先聚合出來）

（2）having 和 group by 配合使用，對(duì) group by 結(jié)果進(jìn)行過濾（對(duì)聚合的結(jié)果進(jìn)行判斷）

having 和 group by 的語義是一樣的，having 相當(dāng)于是對(duì)分組聚合統(tǒng)計(jì)后的數(shù)據(jù)，進(jìn)行條件篩選。

having 經(jīng)常和 group by 搭配使用，作用是對(duì)分組進(jìn)行篩選，作用有些像 where。 

having VS where 的區(qū)別與執(zhí)行順序是什么？

都能夠做條件篩選，這是它們的共性。

但它們是完全不同的條件篩選，它們的條件篩選的階段是不同的。

補(bǔ)充：不要單純的認(rèn)為，只有在磁盤上將表結(jié)構(gòu)導(dǎo)入到 MySQL，真實(shí)存在的表才叫表。 

中間篩選出來的，包括最終結(jié)果，全都是邏輯上的表。（MySQL 一切皆表）

只要我們能夠處理好單表的 CURD，所有的 sql 場(chǎng)景就都能用統(tǒng)一的方式進(jìn)行。

5、補(bǔ)充

SQL 查詢中各個(gè)關(guān)鍵字的執(zhí)行先后順序：

from > on > join > where > group by > with > having > select > distinct > order by > limit

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論