一、Create

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

values 左側括號內是列屬性，右側括號內是列屬性對應的內容，必須在類型和數值上一一對應。

若忽略 values 左側括號內的內容，則稱為全列插入，否則成為按列插入。

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

1、單行數據 + 全列插入

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

2、多行數據 + 指定列插入

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

3、插入是否更新

由于 主鍵  /  唯一鍵 對應的值已經存在而導致插入失敗。

可以選擇性的進行同步更新操作：

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

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

4、替換

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

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

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

二、Retrieve

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

1、SELECT 列

（1）全列查詢

1. 查詢的列越多，意味著需要傳輸的數據量越大。
2. 可能會影響到索引的使用。

通常情況下不建議使用 * 進行全列查詢。

（2）指定列查詢

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

（3）查詢字段為表達式

• a. 表達式不包含字段

• b. 表達式包含多個字段

（4）為查詢結果指定別名

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

（5）結果去重

2、WHERE 條件

（1）比較運算符

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

用 = 判斷是否等于 NULL 這樣做是不安全的，因為 NULL 和 0 本身表示的含義不同，NULL 表示空，0 表示數字 0。

（2）邏輯運算符

?練習

a. 練習一 —— 英語不及格的同學及英語成績

b. 練習二 —— 語文成績在 [80, 90] 分的同學及語文成績

c. 練習三 —— 數學成績是 58 / 59 / 98 / 99 分的同學及數學成績

d. 練習四 —— 姓孫的同學及孫某同學

e. 練習五 —— 語文成績好于英語成績的同學

f. 練習六 —— 總分在 200 分以下的同學

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

g. 練習七 —— 語文成績 > 80 并且不姓孫的同學

h. 練習八 —— 孫某同學，否則要求總成績>200 并且 語文成績<數學成績 并且 英語成績>80 

練習九 —— NULL 的查詢

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

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

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

（3）結果排序

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

默認為 ASC。

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

?練習

a. 練習一 —— 同學及數學成績，按數學成績升序顯示

b. 練習二 —— 同學名字，按名字排序顯示

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

c. 練習三 —— 查詢同學各門成績，依次按 數學降序，英語升序，語文升序的方式顯示

 d. 練習四 —— 查詢同學及總分，由高到低

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

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

order by 中可以使用表達式。

 e. 練習五 —— 查詢姓孫的同學或者姓曹的同學數學成績，結果按數學成績由高到低顯示

• 結合 where 子句 和 order by 子句

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

（4）篩選分頁結果

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

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

• 從 s 開始，篩選 n 條結果

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

1：開始位置（下標從 0 開始）。

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

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

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

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

1：開始位置（下標從 0 開始）。

注意 ：起始下標為 0。

建議：對未知表進行查詢時，最好加一條 limit 1 ，避免因為表中數據過大，查詢全表數據導致數據庫卡死。 按 id 進行分頁，每頁 3 條記錄，分別顯示 第 1 、 2 、 3 頁。 

只有數據準備好了，才要顯示，limit 的本質功能是 “顯示”。

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

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

三、Update

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

對查詢到的結果進行列值更新。

?練習

（1）練習一 —— 將孫悟空同學的數學成績變更為 80 分

• 數據更新

（2）練習二 —— 將曹孟德同學的數學成績變更為 60 分，語文成績變更為 70 分

• 一次更新多個列

（3）練習三 —— 將總成績倒數前三的 3 位同學的數學成績加上 30 分 

• 更新值為原值基礎上變更

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

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

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

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

四、Delete

1、刪除數據

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

?練習

a. 練習一 —— 刪除孫悟空同學的考試成績

b. 練習二 —— 刪除整張表數據

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

2、截斷表

truncate [table] table_name;

• 截斷整表數據，注意影響行數是 0，所以實際上沒有對數據真正操作

• 再插入一條數據，自增 id 再重新增長

delete 和 truncate 的區(qū)別：

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

注意 ：這個操作慎用。

1. 只能對整表操作，不能像 delete 一樣針對部分數據操作。
2. 實際上 MySQL 不對數據操作，所以比 delete 更快，但是 truncate 在刪除數據時，并不經過真正的事物，所以無法回滾。
3. 會重置 auto_increment 項。 

五、插入查詢結果

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

?練習

（1）練習一 —— 刪除表中的的重復復記錄，重復的數據只能有一份

錯誤思路：

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

• 將 duplicate_table 的去重數據插入到 no_duplicate_table

• 通過重命名表，實現原子的去重操作

為什么最后是通過 rename 方式進行的？

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

六、聚合函數

?練習

（1）練習一 —— 統(tǒng)計班級共有多少同學

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

2）練習二 —— 統(tǒng)計本次考試的數學成績分數個數

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

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

注意：

distinct 要寫在括號內，因為我們是要對 math 去重，而不是對 count() 的結果去重。

NULL 不會計入結果。

（3）練習三 —— 統(tǒng)計數學成績總分

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

• 數學的平均成績

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

（5）練習五 —— 返回英語最高分

（6）練習六 —— 返回 > 70 分以上的數學最低分

七、group by 子句的使用

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

在 select 中使用 group by 子句可以對指定列進行分組查詢：

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

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

• emp 員工表

• dept 部門表

• salgrade 工資等級表

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

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

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

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

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

（2）having 和 group by 配合使用，對 group by 結果進行過濾（對聚合的結果進行判斷）

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

having 經常和 group by 搭配使用，作用是對分組進行篩選，作用有些像 where。 

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

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

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

補充：不要單純的認為，只有在磁盤上將表結構導入到 MySQL，真實存在的表才叫表。 

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

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

5、補充

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

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

總結

以上為個人經驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論