【MySQL】数据库表的CURD(二) - 详解

【MySQL】数据库表的CURD(二)

个人主页：大白的编程日记

专栏：MySQL笔记

---

文章目录

• 【MySQL】数据库表的CURD(二)
• • 前言
• 一. Update
• 二.Delete
• 三. 截断表
• 四. 插入查询结果
• 五. 聚合函数
• 六.group by子句的使用
• • 后言

前言

哈喽，各位小伙伴大家好!上期我们讲了数据库表的CURD(一)。今天我们来讲一下数据库表的CURD(二)。话不多说，我们进入正题！向大厂冲锋

一. Update

语法：

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: 1Warnings: 0
-- 查看更新后数据
SELECT name, math FROM exam_result WHERE name = '孙悟空';
+---+---+---++
name |math
+---+---+---++
| 孙悟空 | 80 |
+---+---+---+
1 row in set (0.00 sec)

将曹孟德同学的数学成绩变更为 60 分, 语文成绩变更为 70 分

-- 一次更新多个列
| nam
-- 查看原数据
SELECT name, math, chinese FROM exam_result WHERE name = '曹孟德';
+-----------+--------+-------+
e | 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 总分 LIMIT 3;
+--------+-----+------+
| name   | math| 总分 |
+--------+-----+------+
| 宋公明 |  65 |  170 |
| 刘玄德 |  85 |  185 |
| 曹孟德 |  60 |  197 |
+--------+-----+------+
3 rows in set (0.00 sec)
+-----------+--------+--------+
3 rows in set (0.00 sec)
-- 数据更新，不支持 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 总分 LIMIT 3;
+-----------+--------+
| 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, 7 rows 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 = '孙悟空';
+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------1 row in set (0.00 sec)

-- 删除数据  
DELETE FROM exam_result WHERE name = '孙悟空';
Query OK, 1 row affected (0.17 sec)

-- 查看删除结果  
SELECT * FROM exam_result WHERE name = '孙悟空';
Empty set (0.00 sec)

删除整张表数据

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

–准备测试表

CREATE TABLE for_delete (
id INT PRIMARY KEY AUTO_INCREMENT, nameVARCHAR(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: 3 Duplicates: 0Warnings: 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, 3 rows 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=n 项

SHOW CREATE TABLE for_delete\G
********** 1. row ***
Table: for_delete
Create Table: CREATE TABLE `for_delete` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`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, nameVARCHAR(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 +-+--+ 1D 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

思路：

-- 创建一张空表 noDuplicate_table，结构和 duplicate_table 一样  
CREATE TABLE noDuplicate_table LIKE duplicate_table;
Query OK, 0 rows affected (0.00 sec)

-- 将 duplicate_table 的去重数据插入到 noDuplicate_table  
INSERT INTO no_duplicate_table SELECT DISTINCT * FROM duplicate_table;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0Warnings: 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）统计的是全部成绩  
SELECTCOUNT（math）FROMexam_result;+ COUNT（math）|+ + 61+ 1rowin set(0.00sec)
--COUNT(DISTINCTmath）统计的是去重成绩数量SELECTCOUNT(DISTINCTmath）FROMexam_result;+ COUNT(DISTINCTmath)|+ 51  
1rowin set(0.00sec)

统计数学成绩总分

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子句的使用

在select中使用group by子句可以对指定列进行分组查询

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

案例：

• 准备工作，创建一个雇员信息表（来自oracle 9i的经典测试表）

○ EMP员工表
○ DEPT部门表
o SALGRADE工资等级表

• 如何显示每个部门的平均工资和最高工资

select deptno,avg(sal),max(sal) from EMP group by deptno;

• 显示每个部门的每种岗位的平均工资和最低工资

select avg(sal),min(sal),job, deptno from EMP group by deptno, job;

显示平均工资低于2000的部门和它的平均工资

统计各个部门的平均工资

select avg(sal) from EMP group by deptno

○ having和group by配合使用，对group by结果进行过滤

select avg(sal) as myavg from EMP group by deptno having myavg<2000; --having经常和group by搭配使用，作用是对分组进行筛选，作用有些像where。

后言

这就是数据库表的CURD(二)。大家自己好好消化！今天就分享到这！感谢各位的耐心垂阅！咱们下期见！拜拜~