「前言」文章內容大致是對MySQL表內容的基本操作，即增刪查改。

「歸屬專欄」MySQL

「主頁鏈接」個人主頁

「筆者」楓葉先生(fy)

一、MySQL表內容的增刪查改

• 表內容的增刪查改簡稱CRUD：Create（新增），Retrieve（查找），Update（修改），Delete（刪除）
• DML【data manipulation language】數據操縱語言，用來對數據進行操作代表指令：insert，delete、update
• DML中又單獨分了一個DQL【Data Query Language】，數據查詢語言，代表指令：select

1.1 Create

create是用于新增數據，新增數據的SQL語法如下：

INSERT [INTO] table_name [(column [, column] ...)] VALUES (value_list) [, (value_list)] ...

• 大寫的表示關鍵字，[] 是可選項，可以選擇不寫
• table_name是表的名字
• column列，用于指定每個value_list中的值應該插入到表中的哪一列
• value_lis的值與column一一對應

語法到下面再一一解釋

1.1.1 單行數據+全列插入

先創建一個學生表，表當中包含自增長的主鍵id、學號、姓名和QQ號。

mysql> create table if not exists student(-> id int unsigned primary key auto_increment,-> stu_id int unsigned not null unique comment '學號',-> name varchar(20) not null,-> qq varchar(20)-> );

面使用insert語句向學生表中插入記錄，每次向表中插入一條記錄，并且插入記錄時不指定column列，表示按照表中默認的列順序進行全列插入，因此插入的每條記錄中的列值需要按表列順序依次列出（全列插入）

-- 全列插入
mysql> insert into student values (1, 10001, '張三', 222222);
mysql> insert into student values (2, 10002, '李四', 222223);

注意：value_list數量必須和定義表的列的數量及順序一致

注：into也可以省略，不過為了符號插入語義，一般都寫出來。

1.1.2 多行數據+指定列插入

插入的時候，也可以不用指定id(這時候就需要明確插入數據到那些列了，即指定列插入)，對于表中的ID來說，mysql會使用默認的值進行自增

insert into student (stu_id, name, qq) values (10003, '王五', 222224);

insert語句也可以一次向表中插入多條記錄，插入的多條記錄之間使用逗號隔開，并且插入記錄時可以只指定某些列進行插入。

mysql> insert into student (stu_id, name, qq) values (10004, '趙六', null), (10005, '田七', null);

注意：不允許為空一列，必須插入值，否則報錯。

1.1.3 插入否則更新

向表中插入記錄時，如果待插入記錄中的主鍵或唯一鍵已經存在，那么就會因為主鍵沖突或唯一鍵沖突導致插入失敗。

主鍵沖突

唯一鍵沖突

這時可以選擇性的進行同步更新操作，語法：

INSERT ... ON DUPLICATE UPDATE column1=value1 [, column2=value2] ...;

注：

• 大寫的表示關鍵字，[ ]中代表的是可選項
• ON DUPLICATE KEY 當發生重復key的時候，就執行后面的語句
• UPDATE后面的column=value，表示當插入記錄出現沖突時需要更新的列值

規則：

• 如果表中沒有沖突數據，則直接插入數據
• 如果表中有沖突數據，則將表中的數據進行更新

例如，插入的值主鍵發生沖突，則將表中沖突的列進行更新

mysql> insert into student (id, stu_id, name) values (1, 10001, '孫悟空')-> on duplicate key update stu_id = 10011, name = '孫悟空';
Query OK, 2 rows affected (0.00 sec)

執行插入否則更新的語句，可以通過受影響的數據行數來判斷本次數據的插入情況：

• 0 rows affected：表中有沖突數據，但沖突數據的值和指定更新的值相同
• 1 row affected：表中沒有沖突數據，數據直接被插入
• 2 rows affected：表中有沖突數據，并且數據已經被更新

也可以通過 MySQL 函數獲取受到影響的數據行數

SELECT ROW_COUNT();

1.1.4 數據替換

• 如果表中沒有沖突數據，則直接插入數據
• 如果表中有沖突數據，則先將表中的沖突數據刪除，然后再插入數據

語法：只需要在插入數據時將SQL語句中的INSERT改為REPLACE即可，其他相同

例如：主鍵或者唯一鍵如果沖突，則刪除后再插入

mysql> replace into student (stu_id, name) values (10002, '唐三藏');
Query OK, 2 rows affected (0.00 sec)

執行該語句后，也可以通過受影響的數據行數來判斷本次數據的插入情況：

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

1.2 Retrieve

查找數據的SQL語法如下：

SELECT
[DISTINCT] {* | {column [, column] ...}
[FROM table_name]
[WHERE ...]
[ORDER BY column [ASC | DESC], ...]
LIMIT ...

說明：

• 大寫的表示關鍵字，[ ]中代表的是可選項
• { }中的 | 代表可以選擇左側的語句或右側的語句

接下來創建表結構，用于測試

CREATE TABLE exam_result (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL COMMENT '同學姓名',
chinese float DEFAULT 0.0 COMMENT '語文成績',
math float DEFAULT 0.0 COMMENT '數學成績',
english float DEFAULT 0.0 COMMENT '英語成績'
);

插入測試數據

INSERT INTO exam_result (name, chinese, math, english) VALUES
('唐三藏', 67, 98, 56),
('孫悟空', 87, 78, 77),
('豬悟能', 88, 98, 90),
('曹孟德', 82, 84, 67),
('劉玄德', 55, 85, 45),
('孫權', 70, 73, 78),
('宋公明', 75, 65, 30);

1.2.1 SELECT列

全列查詢

查詢數據時直接用*代替column列表，表示進行全列查詢，這時將會顯示被篩選出來的記錄的所有列信息

mysql> select * from exam_result;

注意：通常情況下不建議使用*進行全列查詢，查詢的列越多，意味著需要傳輸的數據量越大，可能會影響到索引的使用

指定列查詢

在查詢數據時也可以只對指定的列進行查詢，這時將需要查詢的列在column列表列出即可

注意：指定列的順序不需要按定義表的順序來

mysql> select id, name from student;

查詢字段為表達式

select不僅能夠用來查詢數據，還可以用來計算某些表達式或執行某些函數

在查詢數據時，column列表中除了能羅列表中存在的列名外，也可以將表達式羅列到column列表中

mysql> select id, name, math, 10+20 from exam_result;

如果將表達式添加到column列表，那么每當一條記錄被篩選出來時就會執行這個表達式，然后將表達式的計算結果作為這條記錄的一個列值進行顯示

注意：存儲的數據依舊沒有發生改變

column列表中的表達式中也可以包含多個表中已有的字段

為查詢結果指定別名

語法：

SELECT column [AS] alias_name [...] FROM table_name;

說明：

• 大寫的表示關鍵字，[ ]中代表的是可選項

查詢結果列名太長了，可以對列名進行重命名

進行對結果重命名

mysql> select id, name, math+chinese+english as 總分 from exam_result;

as也可以省略

mysql> select id, name, math+chinese+english 總分 from exam_result;

對查詢結果去重

如果想要對查詢結果進行去重操作，可以在SQL中的select后面帶上distinct關鍵字

mysql> select distinct math from exam_result;

1.2.2 SELECT查詢加WHERE條件

• 如果在查詢數據時沒有指定where子句，那么會直接將表中某一列所有的記錄都顯示出來
• 如果在查詢數據時指定了where子句，那么在查詢數據時會先根據where子句篩選出符合條件的記錄，查詢結果只會顯示符合條件的記錄

where子句中可以指明一個或多個篩選條件，各個篩選條件之間用邏輯運算符AND或OR進行關聯，下面給出了where子句中常用的比較運算符和邏輯運算符。

比較運算符

邏輯運算符

注意：MySQL里面比較相等使用的是一個=，不使用兩個等號，與C/C++里面的不一樣

測試案例

英語不及格的同學及英語成績 ( < 60 )

在where子句中指明篩選條件為英語成績小于60

mysql> select id, name, english from exam_result where english<60;

語文成績在 [80, 90] 分的同學及語文成績

在where子句中指明篩選條件為語文成績大于等于80并且小于等于90，使用and進行并列條件

mysql> select id, name, chinese from exam_result where chinese >= 80 and chinese <= 90;

此外，這里也可以使用BETWEEN a0 AND a1來指明語文成績的的所在區間

mysql> mysql> select id, name, chinese from exam_result where chinese between 80 and 90;

數學成績是 58 或者 59 或者 98 或者 99 分的同學及數學成績

在where子句中使用 or 進行條件連接

mysql> select id, name, math from exam_result where math = 58 or math = 59 or math = 98 or math = 99;

此外，也可以通過IN (option, ...)的方式來判斷數學成績是否符合篩選要求

mysql> select id, name, math from exam_result where math in (58, 59, 98, 99);

姓孫的同學

通過模糊匹配來判斷當前同學是否姓孫（需要用到%來匹配任意多個字符），使用到關鍵字like

mysql> select id, name from exam_result where name like '孫%';

孫某同學

通過模糊匹配來判斷當前同學是否為孫某（需要用到_來嚴格匹配任意單個字符），使用到關鍵字like

mysql> select id, name from exam_result where name like '孫_';

語文成績好于英語成績的同學

按照條件使用where子句查詢即可

mysql> select id, name, chinese, english from exam_result where chinese > english;

總分在 200 分以下的同學

在select的column列表中添加表達式查詢，查詢的表達式為語文、數學和英語成績之和，為了方便觀察可以將表達式對應的列指定別名為“總分”，在where子句中指明篩選條件為三科成績之和小于200

mysql> select id, name, chinese+english+math as 總分 from exam_result where chinese+english+math < 200;

需要注意的是，在where子句中不能使用select中指定的別名：

• 查詢數據時是先根據where子句篩選出符合條件的記錄。
• 然后再將符合條件的記錄作為數據源來再依次執行select語句。

也就說說語句的執行順序是where子句先執行，在執行select語句

所以在where子句中不能使用別名select的別名，如果在where子句中使用別名，那么在查詢數據時就會產生報錯

語文成績 > 80 并且不姓孫的同學

where子句要使用AND與NOT的關鍵字和通過模糊匹配like

mysql> select id, name, chinese from exam_result where chinese > 80 and name not like '孫%';

孫某同學，否則要求總成績 > 200 并且 語文成績 < 數學成績 并且 英語成績 > 80

mysql> select id, name, chinese+english+math as 總分 from exam_result where name like '孫_' or (-> chinese+math+english >  200 and chinese < math and english > 80);

NULL的查詢

使用的測試表是上面的student表

查詢qq號已知的同學姓名

mysql> select name, qq from student where qq is not null;

查詢QQ號未知的同學

mysql> select name, qq from student where qq is null;

需要注意的是，在與NULL值作比較的時候應該使用<=>運算符，使用=運算符無法得到正確的查詢結果，不過都不怎么使用<=>運算符，判斷為空或者不為空常使用is null或is not null

=運算符是NULL不安全的，使用=運算符將任何值與NULL作比較，得到的結果都是NULL

<=>運算符是NULL安全的，使用<=>運算符將NULL和NULL作比較得到的結果為TRUE(1)，將非NULL值與NULL作比較得到的結果為FALSE(0)

1.2.3 對查詢結果排序

排序語法：

-- ASC 為升序（從小到大）
-- DESC 為降序（從大到小）
-- 默認為 ASC
SELECT ... FROM table_name [WHERE ...] ORDER BY column [ASC|DESC], [...];

說明：

• 大寫的表示關鍵字，[ ]中代表的是可選項

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

查詢所有同學及數學成績，按數學成績升序顯示

下面使用的測試表是上面的exam_result表

mysql> select name, math from exam_result order by math;

查詢所有同學及 qq 號，按 qq 號排序按升序顯示

使用的測試表是上面的student表

mysql> select name, qq from student order by qq asc;

注意： NULL值視為比任何值都小，因此排升序時出現在最上面。

查詢所有同學及 qq 號，按 qq 號排序按降序顯示

mysql> select name, qq from student order by qq desc;

注意： NULL值視為比任何值都小，因此降序時出現在最下面。

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

mysql> select name, math, english, chinese from exam_result order by math desc, english asc, chinese asc;

這里說明一下，首先排序的是數學，數學成績進行降序排序的，只有滿足了數學降序，然后才到英語升序排序

比如，當兩條記錄的數學成績相同時就會按照英語成績進行排序，如果這兩條記錄的英語成績也相同就會繼續按照語文成績進行排序，以此類推

查詢同學及總分，由高到低

mysql> select name, math+english+chinese from exam_result order by math+english+chinese desc;

需要注意的是，在order by子句中可以使用select中指定的別名：

• 查詢數據時是先根據where子句篩選出符合條件的記錄（如果有where子句）
• 然后再將符合條件的記錄作為數據源來依次執行select語句
• 最后再通過order by子句對select語句的執行結果進行排序

也就是說，order by子句的執行是在select語句之后的，所以在order by子句中可以使用別名

mysql> select name, math+english+chinese as 總分 from exam_result order by 總分 desc;

查詢姓孫的同學或者姓曹的同學數學成績，結果按數學成績由高到低顯示

mysql> select name, math from exam_result where name like '孫%' or name like '曹%' order by math desc;

1.2.4 篩選分頁結果

語法如下：

-- 起始下標為 0-- 從 s 開始，篩選 n 條結果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n；-- 從 0 開始，篩選 n 條結果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;-- 從 s 開始，篩選 n 條結果，比第二種用法更明確，建議使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

說明：

• 大寫的表示關鍵字，[ ]中代表的是可選項
• 查詢SQL中各語句的執行順序為：where、select、order by、limit
• limit子句在篩選記錄時，不加限制，記錄的下標從0開始

注意：對未知表進行查詢時，最好加一條 LIMIT 1，避免因為表中數據過大，查詢全表數據導致數據庫卡死

按 id 進行分頁，每頁 3 條記錄，分別顯示 第 1、2、3 頁

mysql> select id, name, math, chinese, english from exam_result-> order by id limit 3 offset 0;

從第3條記錄開始，向后篩選出3條記錄

mysql> select id, name, math, chinese, english from exam_result order by id limit 3 offset 3;

從第6條記錄開始，向后篩選出3條記錄（如果結果不足 3 個，不會有影響）

select id, name, math, chinese, english from exam_result order by id limit 3 offset 6;

1.3 Update

修改表中數據語法如下：

UPDATE table_name SET column = expr [, column = expr ...]
[WHERE ...] [ORDER BY ...] [LIMIT ...]

說明：

• 大寫的表示關鍵字，[ ]中代表的是可選項
• column=expr，表示將記錄中列名為column的值修改為expr
• 在修改數據之前需要先找到待修改的記錄，update語句中的where、order by和limit就是用來定位數據的，不進行條件限制，表中一列數據都會被修改
• 所以要慎用該命令

將孫悟空同學的數學成績變更為 80 分

先查看原數據，再進行修改，最后再查詢是否已修改

mysql> update exam_result set math = 80 where name = '孫悟空';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

將曹孟德同學的數學成績變更為 60 分，語文成績變更為 70 分

也是如此，先查看原數據，再進行修改，最后再查詢是否已修改

mysql> update exam_result set math = 60, chinese = 70 where name = '曹孟德';

將總成績倒數前三的 3 位同學的數學成績加上 30 分

先查看原數據

mysql> select name, math, math+chinese+english as 總分 from exam_result order by 總分 asc limit 3;

再進行修改，在update語句中指明要將篩選出來的記錄的數學成績加上30分，最后再查詢是否已修改

mysql> update exam_result set math = math+30 order by math+chinese+english asc limit 3;

需要注意的是，MySQL中不支持+=這種復合賦值運算符，此外，這里在查看更新后的數據時不能查看總成績倒數前三的3位同學，因為之前總成績倒數前三的3位同學，數學成績加上30分后可能就不再是倒數前三了

mysql> select name, math, math+chinese+english as 總分 from exam_result where name in('宋公明', '劉玄德', '曹孟德');

將所有同學的語文成績更新為原來的 2 倍

查看原始數據

在update語句中指明要將篩選出來的記錄的語文成績變為原來的2倍，并在修改后再次查看數據確保數據成功被修改

mysql> update exam_result set chinese = chinese*2;

注意：更新全表的語句慎用，沒有條件限制，則會更新全表

1.4 Delete

1.4.1 刪除數據

刪除數據語法：

DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]

說明：

• 大寫的表示關鍵字，[ ]中代表的是可選項
• 在刪除數據之前需要先找到待刪除的記錄，delete語句中的where、order by和limit就是用來定位數據的

刪除孫悟空同學的考試成

先查看原數據，再刪除數據，再查看數據是否存在

刪除整張表數據

創建測試表

CREATE TABLE for_delete (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);

插入測試數據

INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C');

在delete語句中只指明要刪除數據的表名，而不通過where、order by和limit指明篩選條件，這時將會刪除整張表的數據。

mysql> delete from for_delete;

再向表中插入一些數據，在插入數據時不指明自增長字段的值，這時會發現插入數據對應的自增長id值是在之前的基礎上繼續增長的

查看創建表時的相關信息時可以看到，有一個AUTO_INCREMENT=n的字段，當通過delete語句刪除整表數據時，不會重置AUTO_INCREMENT=n字段，因此刪除整表數據后插入數據對應的自增長id值會在原來的基礎上繼續增長。

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

1.4.2 截斷表

截斷表語法如下：

TRUNCATE [TABLE] table_name

說明：

• 大寫的表示關鍵字，[ ]中代表的是可選項
• truncate只能對整表操作，不能像delete一樣針對部分數據操作
• truncate實際上不對數據操作，所以比delete更快
• 但是truncate在刪除數據的時候，并不經過真正的事物，所以無法回滾
• truncate會重置AUTO_INCREMENT=n字段

創建一張測試表

CREATE TABLE for_truncate (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)-- 插入測試數據
INSERT INTO for_truncate (name) VALUES ('A'), ('B'), ('C');
);

在truncate語句中只指明要刪除數據的表名，這時便會刪除整張表的數據，但由于truncate實際不對數據操作，因此執行truncate語句后看到影響行數為0

mysql> truncate table for_truncate;
Query OK, 0 rows affected (0.01 sec)

再向表中插入一些數據，在插入數據時不指明自增長字段的值，這時會發現插入數據對應的自增長id值是重新從1開始增長的

注意： 截斷表操作要慎用

1.5 插入查詢結果

語法：

INSERT INTO table_name [(column [, column ...])] SELECT ...

說明：

• 大寫的表示關鍵字，[ ]中代表的是可選項
• 該SQL的作用是將篩選出來的記錄插入到指定的表當中

刪除表中的的重復復記錄，重復的數據只能有一份

-- 創建原數據表
CREATE TABLE duplicate_table (id int, name varchar(20));
-- 插入測試數據
INSERT INTO duplicate_table VALUES
(100, 'aaa'),
(100, 'aaa'),
(200, 'bbb'),
(200, 'bbb'),
(200, 'bbb'),
(300, 'ccc');

思路：

• 創建一張臨時表，該表的結構與測試表的結構相同。
• 以去重的方式查詢測試表中的數據，并將查詢結果插入到臨時表中。
• 將測試表重命名為其他名字，再將臨時表重命名為測試表的名字，實現原子去重操作。

-- 創建一張空表 no_duplicate_table，結構和 duplicate_table 一樣
mysql> create table no_duplicate_table like duplicate_table;-- 將 duplicate_table 的去重數據插入到 no_duplicate_table
mysql> insert into no_duplicate_table select distinct * from duplicate_table;-- 通過重命名表，實現原子的去重操作
mysql> rename table duplicate_table to old_duplicate_table, -> no_duplicate_table to duplicate_table;

臨時表的結構與測試表相同，因此在創建臨時表的時候可以借助like進行創建

通過插入查詢語句將去重查詢后的結果插入到臨時表中，由于臨時表和測試表的結構相同，并且select進行的是全列查詢，因此在插入時不用在表名后指明column列表

將測試表重命名為其他名字（相當于對去重前的數據進行備份），將臨時表重命名為測試表的名字，這時便完成了表中數據的去重操作

1.6 聚合函數

聚合函數對一組值執行計算并返回單一的值，常用的聚合函數如下：

函數	說明
COUNT([DISTINCT] expr)	返回查詢到的數據的數量
SUM([DISTINCT] expr)	返回查詢到的數據的總和，不是數字沒有意義
AVG([DISTINCT] expr)	返回查詢到的數據的平均值，不是數字沒有意義
MAX([DISTINCT] expr)	返回查詢到的數據的最大值，不是數字沒有意義
MIN([DISTINCT] expr)	返回查詢到的數據的最小值，不是數字沒有意義

注意：聚合函數可以在select語句中使用，此時select每處理一條記錄時都會將對應的參數傳遞給這些聚合函數

統計班級共有多少同學

這里用之前的student表來進行演示

使用*做統計，不受 NULL 影響，將*作為參數傳遞給count函數，這時便能統計出表中的記錄條數

mysql> select count(*) from student;

也可以使用表達式做統計，使用count函數，并將表達式作為參數傳遞給count函數，這時也可以統計出表中的記錄條數

mysql> select count(1) from student;

這種寫法相當于在查詢表中數據時，自行新增了一列列名為特定表達式的列，我們就是在用count函數統計該列中有多少個數據，等價于統計表中有多少條記錄

統計班級收集的 qq 號有多少

使用count函數統計qq列中數據的個數，NULL不會計入結果

mysql> select count(qq) from student;

統計本次考試的數學成績分數個數

這里用之前的exam_result表來進行演示

先使用count函數統計math列中數據的個數（包含重復的）

使用count函數時（包括其他聚合函數），在傳遞的參數之前加上distinct，這時便能統計出表中數學成績去重后的個數。

mysql> select count(distinct math) from exam_result;

統計數學成績總分

可以使用sum函數統計math列中數據的總和

mysql> select sum(math) from exam_result;

統計不及格的數學成績總分

使用where子句中指明篩選條件為數學成績小于60分

mysql> select sum(math) from exam_result where math < 60;

注意：如果沒有結果，返回 NULL

統計平均總分

可以使用avg函數計算總分的平均值

mysql> select avg(chinese+math+english) 平均總分 from exam_result;

返回英語最高分

可以使用max函數查詢英語成績最高分

mysql> select max(english) as maxEnglish from exam_result;

返回 > 70 分以上的數學最低分

使用where子句中指明篩選條件為英語成績大于70分，在select語句中使用min函數查詢英語成績最低分

mysql> select min(english) as minEnglish from exam_result where english > 70;

1.7 group by子句的使用（分組查詢）

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

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

說明：

• SQL中大寫的表示關鍵字，[ ]中代表的是可選項
• 查詢SQL中各語句的執行順序為：where、group by、select、order by、limit
• group by后面的列名，表示按照指定列進行分組查詢

準備工作，創建一個雇員信息表（來自oracle 9i的經典測試表）
雇員信息表中包含三張表，分別是員工表（emp）、部門表（dept）和工資等級表（salgrade）

員工表（emp）中包含如下字段：

• 雇員編號（empno）
• 雇員姓名（ename）
• 雇員職位（job）
• 雇員領導編號（mgr）
• 雇傭時間（hiredate）
• 工資月薪（sal）
• 獎金（comm）
• 部門編號（deptno）

部門表（dept）中包含如下字段：

• 部門編號（deptno）
• 部門名稱（dname）
• 部門所在地點（loc）

工資等級表（salgrade）中包含如下字段：

• 等級（grade）
• 此等級最低工資（losal）
• 此等級最高工資（hisal）

雇員信息表SQL代碼

已上傳至下載，主頁的資源頁面即可找到

然后上傳文件，在MySQL中使用source命令依次執行文件中的SQL

source SQL文件路徑
-- 例如
mysql> source /home/fy/mysql/scott_data.sql

使用該數據庫

部門表（dept）的表結構和表中的內容如下：

員工表（emp）的表結構和表中的內容如下：

工資等級表（salgrade）的表結構和表中的內容如下：

顯示每個部門的平均工資和最高工資

在group by子句中指明按照部門號進行分組，在select語句中使用avg函數和max函數，分別查詢每個部門的平均工資和最高工資

mysql> select deptno, avg(sal) as 平均工資, max(sal) as 最高工資 from emp group by deptno;

注意：是先執行分組語句，然后各自在組內做聚合查詢得到每個組的平均工資和最高工資

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

在group by子句中指明依次按照部門號和崗位進行分組，在select語句中使用avg函數和min函數，分別查詢每個部門的每種崗位的平均工資和最低工資

mysql> select deptno, job, avg(sal) 平均工資, min(sal) 最低工資 from emp group by deptno, job;

注意：group by子句中可以指明按照多個字段進行分組，各個字段之間使用逗號隔開，分組優先級與書寫順序相同，比如，當兩條記錄的部門號相同時，將會繼續按照崗位進行分組。

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

這里要使用到HAVING條件，語法如下：

SELECT ... FROM table_name [WHERE ...] [GROUP BY ...] [HAVING ...] ...;

說明：

• 大寫的表示關鍵字，[ ]中代表的是可選項
• SQL中各語句的執行順序為：where、group by、select、having、order by、limit
• having子句中可以指明一個或多個篩選條件
• having經常和group by搭配使用，作用是對分組進行篩選，作用有些像where

先統計每個部門的平均工資,在group by子句中指明按照部門號進行分組

mysql> select deptno, avg(sal) 平均工資 from emp group by deptno;

然后通過having子句篩選出平均工資低于2000的部門

mysql> select deptno, avg(sal) 平均工資 from emp group by deptno having 平均工資 < 2000;

having子句和where子句的區別

• where子句放在表名后面，而having子句必須搭配group by子句使用，放在group by子句的后面
• where子句是對整表的數據進行篩選，having子句是對分組后的數據進行篩選
• where子句中不能使用聚合函數和別名，而having子句中可以使用聚合函數和別名

總結一下，SQL中各語句的執行順序

1. 根據where子句篩選出符合條件的記錄
2. 根據group by子句對數據進行分組
3. 將分組后的數據依次執行select語句
4. 根據having子句對分組后的數據進行進一步篩選
5. 根據order by子句對數據進行排序
6. 根據limit子句篩選若干條記錄進行顯示

--------------------- END ----------------------

「 作者 」 楓葉先生
「 更新 」 2023.8.18
「 聲明 」 余之才疏學淺，故所撰文疏漏難免，或有謬誤或不準確之處，敬請讀者批評指正。