【MySQL】数据库表的CURD(二) - 详解
【MySQL】数据库表的CURD(二)
个人主页：大白的编程日记
专栏：MySQL笔记
前言
哈喽，各位小伙伴大家好!上期我们讲了数据库表的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注意：这个操作慎用
- 只能对整表操作，不能像 DELETE 一样针对部分数据操作；
- 实际上 MySQL 不对数据操作，所以比 DELETE 更快，但是 TRUNCATE 在删除数据的时候，并不经过真正的事物，所以无法回滚
- 会重置 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 >70 >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(二)。大家自己好好消化！今天就分享到这！感谢各位的耐心垂阅！咱们下期见！拜拜~
浙公网安备 33010602011771号