---

📝Update

【MySQL 數據庫】MySQL基本查詢（第一節）

---

語法：

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

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

案例：

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

• 更新值為具體值
• 查看原數據

SELECT name, math FROM exam_result WHERE name = '孫悟空';| name | math | 
| 孫悟空 | 78|
1 row in set (0.00 sec) 

• 數據更新

UPDATE exam_result SET math = 80 WHERE name = '孫悟空'; Query OK, 1 row affected (0.04 sec)
Rows matched:1 Changed:1 warnings:0

• 查看更新后數據

SELECT name,math FROM exam_result WHERE name="孫悟空';| name | math |
|孫悟空 | 80|
1 row in set(0.00 sec)

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

• 一次更新多個列
• 查看原數據

SELECT name,math,chinese FROM exam_result WHERE name="曹孟德';

| name | math | chinese|
| 曹孟德 | 84| 82|
1 row in set (0.00 sec)

• 數據更新

UPDATE exam_result SET math =60,chinese=70 WHERE name='曹孟德';

Query ok,1 row affected (0.14 sec) Rows matched:1 Changed:1 warnings:0

• 查看更新后數據

SELECT name,math,chinese FROM exam_result WHERE name="曹孟德';

| name | math | chinese|
| 曹孟德 | 60| 70|
1 row in set (0.00 sec)

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

• 更新值為原值基礎上變更
• 別名可以在ORDER BY中使用
• 查看原數據

SELECT name, math,chinese+math+english總分FROM exam_result
ORDER BY總分LIMIT3;

| name | math| 總分 |
| 宋公明 | 65 | 170 |
| 劉玄德 | 85 | 185 |
| 曹孟德 | 60| 197 |
3 rows in set (0.00sec)

• 數據更新，不支持math+=30這種語法

UPDATE exam_result SET math = math+30
ORDER BY chinese + math+english LIMIT 3;

• 查看更新后數據
• 思考：這里還可以按總分升序排序取前3個么?

SELECT name,math,chinese +math+english總分FROM exam_result WHERE name IN('宋公明','劉玄德','曹孟德');

| name | math| 總分 |
| 曹孟德 | 90 | 227 |
| 劉玄德 | 115 | 215 |
| 宋公明 | 95 | 200 |
3 rows in set (0.00 sec)

• 按總成績排序后查詢結果

SELECT name,math,chinese+math+english總分FROM exam_result
ORDER BY總分LIMIT3;

| name | math| 總分 |
| 宋公明 | 95 | 200 |
| 劉玄德 | 115 | 215 |
| 唐三藏 | 98 | 221 |
3 rows in set (0.00 sec)

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

• 注意：更新全表的語句慎用！
• 沒有WHERE子句，則更新全表
• 查看原數據

SELECT* FROM exam_result;

| id | name | chinese| math | english |
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孫悟空 | 87 | 80 | 77 |
| 3 | 豬悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 70 | 90 | 67 |
| 5 | 劉玄德 | 55 | 115 | 45 |
| 6 | 孫權 | 70| 73 | 78 |
| 7 | 宋公明 | 75| 95| 30 |
7 rows in set(0.00 sec)

• 數據更新

UPDATE exam_result SET chinese = chinese*2;

Query OK,7rows affected (0.00 sec)
Rows matched:7 changed:7 warnings:0

• 查看更新后數據

SELECT* FROM exam_result;

| id | name | chinese | math | english |
| 1 | 唐三藏 | 134 | 98 | 56 |
| 2 | 孫悟空 | 174 | 80 | 77 |
| 3 | 豬悟能 | 176 | 98 | 90 |
| 4 | 曹孟德 | 140 | 90 | 67 |
| 5 | 劉玄德 | 110 | 115 | 45 |
| 6 | 孫權 | 140 | 73 | 78 |
| 7 | 宋公明 | 150 | 95| 30 |
7 rows in set (0.00 sec)

🌠 Delete

🌉 刪除數據

語法：

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

案例：

🌉 刪除孫悟空同學的考試成績

• 查看原數據

SELECT*FROM exam_result WHERE name="孫悟空';
| id | name | chinese | math | english |
| 2 | 孫悟空 | 174 | 80 | 77 |
1 row in set(0.00 sec)

• 刪除數據

DELETE FROM exam_result WHERE name='孫悟空';
Query OK,1 row affected (0.17sec)

• 查看刪除結果

SELECT*FROM exam_result WHERE name="孫悟空';
Empty set (0.00 sec)

🌉 刪除整張表數據

• 注意：刪除整表操作要慎用！
• 準備測試表

CREATE TABLE for_delete ( id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR (20);Query OK,0 rows affected (0.16 sec)
);

• 插入測試數據

INSERT INTO for_delete (name) VALUES ('A'),('B'),("c");Query OK,3 rows affected (1.05 sec) Records:3Duplicates:0 warnings:0

• 查看測試數據

SELECT* FROM for_delete;| id | name |
| 1 | A |
| 2 | B |
| 3 | C |
3 rows in set (0.00 sec)

• 刪除整表數據

DELETE FROM for_delete;Query OK,3rows affected (0.00 sec)

• 查看刪除結果

SELECT * FROM for_delete;Empty set (0.00 sec)

• 再插入一條數據，自增id在原值上增長

INSERT INTO for_delete (name) VALUES ("'D");
Query OK,1 row affected (0.00 sec)

• 查看數據

SELECT* FROM for_delete;| id | name |
| 4 | D |
1 row in set(0.00 sec)

• 查看表結構，會有AUTO_INCREMENT項

SHOW CREATE TABLE for_delete\G*************************** 1. row ***************************
Table: for_delete
create Table: CREATE TABLE for_delete'(
idint(11)NOT NULL AUTO_NCREMENT, "name' varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

🌉 截斷表

語法：

TRUNCATE [TABLE] table_name

注意：
這個操作慎用

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

案例：

• 準備測試表

CREATE TABLE for_truncate ( 
id INT PRIMARY KEY AUTO_INCREMENT, 
name VARCHAR(20) 
); 
Query OK, 0 rows affected (0.16 sec)

• 插入測試數據

INSERT INTO for_truncate (name) VALUES ('A'), ('B'), ('C'); Query OK, 3 rows affected (1.05 sec)
Records: 3 Duplicates: 0 Warnings: 0

• 查看測試數據

SELECT * FROM for_truncate; 
| id | name | 
| 1 | A | 
| 2 | B | 
| 3 | C | 
3 rows in set (0.00 sec)

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

TRUNCATE for_truncate; 
Query OK, 0 rows affected (0.10 sec)

• 查看刪除結果

SELECT * FROM for_truncate; 
Empty set (0.00 sec)

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

INSERT INTO for_truncate (name) VALUES ('D'); 
Query OK, 1 row affected (0.00 sec)

• 查看數據

SELECT * FROM for_truncate; 

| id | name | 
| 1 | D | 
1 row in set (0.00 sec)

• 查看表結構，會有 AUTO_INCREMENT=2 項

SHOW CREATE TABLE for_truncate\G 

*************************** 1. row ***************************
Table: for_truncate
Create Table: CREATE TABLE `for_truncate` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

🌠 插入查詢結果

語法：

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

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

• 創建原數據表

CREATE TABLE duplicate_table (id int, name varchar(20)); 
Query OK, 0 rows affected (0.01 sec)

• 插入測試數據

INSERT INTO duplicate_table VALUES 
(100, 'aaa'), 
(100, 'aaa'), 
(200, 'bbb'), 
(200, 'bbb'), 
(200, 'bbb'), 
(300, 'ccc'); 
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0

思路：

• 創建一張空表 no_duplicate_table，結構和 duplicate_table 一樣

CREATE TABLE no_duplicate_table LIKE duplicate_table; 

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

INSERT INTO no_duplicate_table SELECT DISTINCT * FROM duplicate_table;Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

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

RENAME TABLE duplicate_table TO old_duplicate_table, 
no_duplicate_table TO duplicate_table; 

Query OK, 0 rows affected (0.00 sec)

• 查看最終結果

SELECT * FROM duplicate_table; | id | name | 
| 100 | aaa | 
| 200 | bbb | 
| 300 | ccc | 
3 rows in set (0.00 sec)

🌠聚合函數

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

案例：

🌉統計班級共有多少同學

• 使用 * 做統計，不受 NULL 影響

SELECT COUNT(*) FROM students; | COUNT(*) | 
| 4 |
1 row in set (0.00 sec)

• 使用表達式做統計

SELECT COUNT(1) FROM students;| COUNT(1) | 
| 4 |
1 row in set (0.00 sec)

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

• NULL 不會計入結果

SELECT COUNT(qq) FROM students; | COUNT(qq) | 
| 1 |
1 row in set (0.00 sec)

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

• COUNT(math) 統計的是全部成績

SELECT COUNT(math) FROM exam_result; | COUNT(math) | 
| 6 |
1 row in set (0.00 sec)

• COUNT(DISTINCT math) 統計的是去重成績數量

SELECT COUNT(DISTINCT math) FROM exam_result; | COUNT(math) | 
| 6 |
1 row in set (0.00 sec)

🌉 統計數學成績總分

SELECT SUM(math) FROM exam_result; | SUM(math) | 
| 569 |
1 row in set (0.00 sec)

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

SELECT SUM(math) FROM exam_result WHERE math < 60;| SUM(math) | 
| NULL | 
1 row in set (0.00 sec)

🌉統計平均總分

SELECT AVG(chinese + math + english) 平均總分 FROM exam_result; 
| 平均總分 |
| 297.5 |

🌉返回英語最高分

SELECT MAX(english) FROM exam_result; | MAX(english) | 
| 90 |
1 row in set (0.00 sec)

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

SELECT MIN(math) FROM exam_result WHERE math > 70; | MIN(math) | 
| 73 |
1 row in set (0.00 sec)

🌠group by子句的使用

1. 語法結構：

   SELECT 分組列1, 分組列2, 聚合函數(統計列)
   FROM 表名
   [WHERE 篩選條件]  -- 分組前篩選行
   GROUP BY 分組列1, 分組列2  -- 按列分組（與SELECT中分組列順序一致）
   [HAVING 分組篩選條件]  -- 分組后篩選組
   [ORDER BY 排序列];  -- 對分組結果排序
   

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

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

2. 核心規則：
   • SELECT 后出現的非聚合函數列，必須全部出現在 GROUP BY 子句中（否則會報錯，因為無法確定非分組列的取值）。
   • GROUP BY 可以按多個列分組（先按第一列分組，同一組內再按第二列細分）。

2、經典案例解析（基于 EMP 員工表）
假設 EMP 表結構如下（簡化版）：

empno	ename	job	sal	deptno
1001	張三	經理	5000	10
1002	李四	職員	2000	10
1003	王五	職員	1800	10
1004	趙六	經理	4500	20
1005	錢七	職員	2200	20

案例 1：按單個列分組（部門）
需求：顯示每個部門的平均工資、最高工資和員工人數。

SELECT deptno AS 部門編號,AVG(sal) AS 平均工資,MAX(sal) AS 最高工資,COUNT(*) AS 員工人數  -- 統計每個部門的人數
FROM EMP
GROUP BY deptno;  -- 按部門編號分組

結果：

部門編號	平均工資	最高工資	員工人數
10	2933.33	5000	3
20	3350.00	4500	2

案例 2：按多個列分組（部門+崗位）
需求：顯示每個部門中，每種崗位的平均工資和最低工資。

SELECT deptno AS 部門編號,job AS 崗位,AVG(sal) AS 平均工資,MIN(sal) AS 最低工資
FROM EMP
GROUP BY deptno, job;  -- 先按部門分組，同部門內再按崗位分組

結果：

部門編號	崗位	平均工資	最低工資
10	經理	5000.00	5000
10	職員	1900.00	1800
20	經理	4500.00	4500
20	職員	2200.00	2200

案例 3：GROUP BY + HAVING 篩選分組結果
HAVING 用于對分組后的結果進行篩選（類似 WHERE，但 WHERE 是分組前篩選行，HAVING 是分組后篩選組）。

需求：顯示平均工資低于 3000 的部門及其平均工資。

SELECT deptno AS 部門編號,AVG(sal) AS 平均工資
FROM EMP
GROUP BY deptno
HAVING 平均工資 < 3000;  -- 篩選分組后的平均工資

結果：

部門編號	平均工資
10	2933.33

案例 4：GROUP BY + WHERE + HAVING 組合使用
需求：排除工資低于 1500 的員工后，統計每個部門的平均工資，且只顯示平均工資高于 2500 的部門。

SELECT deptno AS 部門編號,AVG(sal) AS 平均工資
FROM EMP
WHERE sal >= 1500  -- 先排除工資<1500的員工（本案例中無此類員工，僅作示例）
GROUP BY deptno
HAVING 平均工資 > 2500;  -- 再篩選平均工資>2500的部門

結果：

部門編號	平均工資
10	2933.33
20	3350.00

三、常見錯誤與注意事項

1. SELECT 中的非聚合列必須在 GROUP BY 中
   錯誤示例：

   SELECT deptno, job, AVG(sal) FROM EMP GROUP BY deptno;  -- 錯誤！job未在GROUP BY中
   

   原因：分組后每個部門包含多個崗位，job 列的值不唯一，無法確定顯示哪一個。

2. HAVING 可以使用別名，WHERE 不能
   正確：HAVING 平均工資 < 3000（平均工資 是 AVG(sal) 的別名）
   錯誤：WHERE 平均工資 < 3000（WHERE 不支持聚合函數別名）

3. GROUP BY 的分組順序不影響結果，但建議與 SELECT 中分組列順序一致
   例如 GROUP BY deptno, job 與 GROUP BY job, deptno 分組邏輯不同，結果也不同。

---

🚩總結

點擊刷題----->牛客刷題SQL篇

面試題：SQL查詢中各個關鍵字的執行先后順序 from > on> join > where > group by > with > having > select > distinct > order by > limit