【MySQL】第九弹---掌握SQL关键操作：更新、删除、插入与聚合分析的秘诀

✨个人主页： 熬夜学编程的小林

💗系列专栏： 【C语言详解】 【数据结构详解】【C++详解】【Linux系统编程】【MySQL】

目录

1 Update

2 Delete

2.1 删除数据

2.2 截断表

3 插入查询结果

4 聚合函数

5 group by子句的使用

1 Update

语法：

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

对查询到的结果进行列值更新

案例：

• 将孙悟空同学的数学成绩变更为 80 分

# 查看原数据
mysql> select name,math from exam_result where name = '孙悟空';
+-----------+------+
| name      | math |
+-----------+------+
| 孙悟空    |   78 |
+-----------+------+
1 row in set (0.00 sec)

# 更新孙悟空数学成绩
mysql> update exam_result set math = 80 where name = '孙悟空';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 查看最新数据
mysql> select name,math from exam_result where name = '孙悟空';
+-----------+------+
| name      | math |
+-----------+------+
| 孙悟空    |   80 |
+-----------+------+
1 row in set (0.00 sec)

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

# 查看原始数据
mysql> select name,math,chinese from exam_result where name = '曹孟德';
+-----------+------+---------+
| name      | math | chinese |
+-----------+------+---------+
| 曹孟德    |   84 |      82 |
+-----------+------+---------+
1 row in set (0.00 sec)

# 更新数学和语文成绩
mysql> update exam_result set math = 60,chinese = 70 where name = '曹孟德';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 查看新数据
mysql> select name,math,chinese from exam_result where name = '曹
孟德';
+-----------+------+---------+
| name      | math | chinese |
+-----------+------+---------+
| 曹孟德    |   60 |      70 |
+-----------+------+---------+
1 row in set (0.00 sec)

• 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分

# 查看倒数三名信息
mysql> select name,chinese+math+english total from exam_result order by total limit 3;
+-----------+-------+
| name      | total |
+-----------+-------+
| 宋公明    |   170 |
| 刘玄德    |   185 |
| 曹孟德    |   197 |
+-----------+-------+
3 rows in set (0.00 sec)

# 修改数学成绩
mysql> update exam_result set math=math+30 order by chinese+math+english limit 3;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

# 修改成绩厚的后三名成绩发生了变化
mysql> select name,chinese+math+english total from exam_result ordaer by total limit 3;
+-----------+-------+
| name      | total |
+-----------+-------+
| 宋公明    |   200 |
| 刘玄德    |   215 |
| 唐三藏    |   221 |
+-----------+-------+
3 rows in set (0.00 sec)

mysql> 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)

注意：mysql不支持+=的语法。

• 将所有同学的语文成绩更新为原来的 2 倍

注意：更新全表的语句慎用！

# 查看原始信息
mysql> 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)

# 更新语文成绩
mysql> update exam_result set chinese = chinese * 2;
Query OK, 7 rows affected (0.00 sec)
Rows matched: 7  Changed: 7  Warnings: 0

# 查看修改后的成绩
mysql> 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)

2 Delete

2.1 删除数据

语法：

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

案例：

• 删除孙悟空同学的考试成绩

# 查看name = 孙悟空的考试成绩
mysql> select * from exam_result where name = '孙悟空';
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  2 | 孙悟空    |     174 |   80 |      77 |
+----+-----------+---------+------+---------+
1 row in set (0.00 sec)

# 删除孙悟空的考试成绩
mysql> delete from exam_result where name = '孙悟空';
Query OK, 1 row affected (0.01 sec)

# 查看删除后孙悟空的信息
mysql> select * from exam_result where name = '孙悟空';
Empty set (0.00 sec)

• 删除整张表数据

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

创建表

mysql> create table for_delete (
    id int primary key auto_increment,
    name varchar(20)
    );

插入数据

mysql> insert into for_delete (name) values ('A'), ('B'), ('C');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from for_delete;
+----+------+
| id | name |
+----+------+
|  1 | A    |
|  2 | B    |
|  3 | C    |
+----+------+
3 rows in set (0.00 sec)

删除表数据

# 删除整个表的数据
mysql> delete from for_delete;
Query OK, 3 rows affected (0.00 sec)

# 查看表的数据
mysql> select * from for_delete;
Empty set (0.01 sec)

再插入一条数据

mysql> insert into for_delete (name) values('D');
Query OK, 1 row affected (0.01 sec)

# 插入的数据，id从4开始
mysql> select * from for_delete;
+----+------+
| id | name |
+----+------+
|  4 | D    |
+----+------+
1 row in set (0.00 sec)

# 查看表创建结构，auto_increment = 5
mysql> 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)

使用delele删除整个表的数据，不会重置auto_increment的值。

2.2 截断表

语法： 

TRUNCATE [TABLE] table_name

注意：这个操作慎用

• 1. 只能对整表操作，不能像 DELETE 一样针对部分数据操作；
• 2. 实际上 MySQL 不对数据操作，所以比 DELETE 更快，但是TRUNCATE在删除数据的时候，并不经过真正的事物，所以无法回滚
• 3. 会重置 AUTO_INCREMENT 项

创建表

mysql> create table for_truncate(
    -> id int primary key auto_increment,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> desc for_truncate;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

插入数据

mysql> insert into for_truncate (name) values('A'),('B'),('C');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from for_truncate;
+----+------+
| id | name |
+----+------+
|  1 | A    |
|  2 | B    |
|  3 | C    |
+----+------+
3 rows in set (0.00 sec)

截断表

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

mysql> select * from for_truncate;
Empty set (0.00 sec)

再插入一条数据

mysql> insert into for_truncate (name) values('D');
Query OK, 1 row affected (0.01 sec)

mysql> select * from for_truncate;
+----+------+
| id | name |
+----+------+
|  1 | D    |
+----+------+
1 row in set (0.00 sec)

mysql> 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)

3 插入查询结果

语法：

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

案例：

• 删除表中的的重复记录，重复的数据只能有一份

思路

1、创建一个原表结构相同的表

2、将原表去重的数据插入到新表

3、将原表改为其他名字(备份原表数据)

4、将新表名改为原表名

创建一个重复数据的表

mysql> create table duplicate_table (
    -> id int,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> 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

mysql> select * from duplicate_table;
+------+------+
| id   | name |
+------+------+
|  100 | aaa  |
|  100 | aaa  |
|  200 | bbb  |
|  200 | bbb  |
|  200 | bbb  |
|  300 | ccc  |
+------+------+
6 rows in set (0.00 sec)

创建一个原表结构相同的表

mysql> create table no_duplicate_table like duplicate_table;
Query OK, 0 rows affected (0.03 sec)

mysql> desc no_duplicate_table;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc duplicate_table;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

将原表去重的数据插入到新表

mysql> insert into no_duplicate_table select distinct * from dupliccate_table;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from no_duplicate_table;
+------+------+
| id   | name |
+------+------+
|  100 | aaa  |
|  200 | bbb  |
|  300 | ccc  |
+------+------+
3 rows in set (0.00 sec)

将原表改为其他名字(备份原表数据)

mysql> rename table duplicate_table to tmp_table;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+--------------------+
| Tables_in_test5_db |
+--------------------+
| exam_result        |
| for_delete         |
| for_truncate       |
| no_duplicate_table |
| student            |
| tmp_table          |
+--------------------+
6 rows in set (0.00 sec)

将新表名改为原表名

mysql> rename table no_duplicate_table to duplicate_table;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from duplicate_table;
+------+------+
| id   | name |
+------+------+
|  100 | aaa  |
|  200 | bbb  |
|  300 | ccc  |
+------+------+
3 rows in set (0.00 sec)

4 聚合函数

案例：

• 统计班级共有多少同学

# 使用 * 统计，不受NULL限制
mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

# 查看原表数据
mysql> select * from student;
+----+-----+--------+--------+
| id | sn  | name   | qq     |
+----+-----+--------+--------+
|  1 | 123 | 张三   | 123456 |
|  2 | 124 | 李四   | 156954 |
|  3 | 125 | 王五   | 523124 |
|  4 | 126 | 孙权   | NULL   |
|  5 | 127 | 妲己   | NULL   |
+----+-----+--------+--------+
5 rows in set (0.00 sec)

# 使用表达式统计
mysql> select count(1) from student;
+----------+
| count(1) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

• 统计班级收集的 qq 号有多少

# NULL 不会计入结果
mysql> select count(qq) from student;
+-----------+
| count(qq) |
+-----------+
|         3 |
+-----------+
1 row in set (0.00 sec)

mysql> select * from student;
+----+-----+--------+--------+
| id | sn  | name   | qq     |
+----+-----+--------+--------+
|  1 | 123 | 张三   | 123456 |
|  2 | 124 | 李四   | 156954 |
|  3 | 125 | 王五   | 523124 |
|  4 | 126 | 孙权   | NULL   |
|  5 | 127 | 妲己   | NULL   |
+----+-----+--------+--------+
5 rows in set (0.00 sec)

• 统计本次考试的数学成绩分数个数

mysql> select count(math) from exam_result;
+-------------+
| count(math) |
+-------------+
|           6 |
+-------------+
1 row in set (0.00 sec)

mysql> select math from exam_result; # 直接查询会有重复的成绩，需要去重
+------+
| math |
+------+
|   98 |
|   98 |
|   90 |
|  115 |
|   73 |
|   95 |
+------+
6 rows in set (0.00 sec)
# count(distinct math)统计的是去重成绩数量
mysql> select count(distinct math) from exam_result;
+----------------------+
| count(distinct math) |
+----------------------+
|                    5 |
+----------------------+
1 row in set (0.00 sec)

• 统计数学成绩总分

mysql> select sum(math) from exam_result;
+-----------+
| sum(math) |
+-----------+
|       569 |
+-----------+
1 row in set (0.00 sec)

# 不及格 < 60 的总分，没有结果，返回 NULL
mysql> select sum(math) from exam_result where math < 60;
+-----------+
| sum(math) |
+-----------+
|      NULL |
+-----------+
1 row in set (0.00 sec)

•  统计平均总分

mysql> select avg(chinese+math+english) 平均总分 from exam_result; 
+--------------+
| 平均总分     |
+--------------+
|        297.5 |
+--------------+
1 row in set (0.00 sec)

• 返回英语最高分

mysql> select max(english) 英语最高分 from exam_result;
+-----------------+
| 英语最高分      |
+-----------------+
|              90 |
+-----------------+
1 row in set (0.00 sec)

• 返回 > 70 分以上的数学最低分

mysql> select min(math) from exam_result where math > 70;
+-----------+
| min(math) |
+-----------+
|        73 |
+-----------+
1 row in set (0.00 sec)

5 group by子句的使用

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

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

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

• EMP员工表
• DEPT部门表
• SALGRADE工资等级表

# 将表的数据导入
source /root/mysql/scott_data.sql

# 查看当前数据库
mysql> select database();
+------------+
| database() |
+------------+
| scott      |
+------------+
1 row in set (0.00 sec)

# 查看表
mysql> show tables;
+-----------------+
| Tables_in_scott |
+-----------------+
| dept            |
| emp             |
| salgrade        |
+-----------------+
3 rows in set (0.00 sec)

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

mysql> select deptno,avg(sal),max(sal) from emp group by deptno;
+--------+-------------+----------+
| deptno | avg(sal)    | max(sal) |
+--------+-------------+----------+
|     10 | 2916.666667 |  5000.00 |
|     20 | 2175.000000 |  3000.00 |
|     30 | 1566.666667 |  2850.00 |
+--------+-------------+----------+
3 rows in set (0.00 sec)

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

mysql> select deptno,job,avg(sal),max(sal) from emp group by deptno,job;
+--------+-----------+-------------+----------+
| deptno | job       | avg(sal)    | max(sal) |
+--------+-----------+-------------+----------+
|     10 | CLERK     | 1300.000000 |  1300.00 |
|     10 | MANAGER   | 2450.000000 |  2450.00 |
|     10 | PRESIDENT | 5000.000000 |  5000.00 |
|     20 | ANALYST   | 3000.000000 |  3000.00 |
|     20 | CLERK     |  950.000000 |  1100.00 |
|     20 | MANAGER   | 2975.000000 |  2975.00 |
|     30 | CLERK     |  950.000000 |   950.00 |
|     30 | MANAGER   | 2850.000000 |  2850.00 |
|     30 | SALESMAN  | 1400.000000 |  1600.00 |
+--------+-----------+-------------+----------+
9 rows in set (0.00 sec)

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

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

mysql> select deptno,avg(sal) myavg from emp group by deptno having myavg < 2000;
+--------+-------------+
| deptno | myavg       |
+--------+-------------+
|     30 | 1566.666667 |
+--------+-------------+
1 row in set (0.00 sec)

执行顺序分析

mysql> select deptno,job,avg(sal) myavg from emp where ename != 'SMITH' group by deptno,job having myavg < 2000;
+--------+----------+-------------+
| deptno | job      | myavg       |
+--------+----------+-------------+
|     10 | CLERK    | 1300.000000 |
|     20 | CLERK    | 1100.000000 |
|     30 | CLERK    |  950.000000 |
|     30 | SALESMAN | 1400.000000 |
+--------+----------+-------------+
4 rows in set (0.00 sec)