【MySQL】8. 基本查询(update/delete/聚合/分组)

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 【MySQL】8. 基本查询(update/delete/聚合/分组)

表的删改

3. Update

语法:

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

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

3.1 将孙悟空同学的数学成绩变更为 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)

3.2 将曹孟德同学的数学成绩变更为 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.3 将总成绩倒数前三的 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)

-- 数据更新,不支持 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)

3.4 将所有同学的语文成绩更新为原来的 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)

4. Delete

4.1 删除数据

语法:

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

案例:

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

-- 查看原数据
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.17 sec)

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

4.1.2 删除整张表数据

注意:删除整表操作要慎用!

-- 准备测试表
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: 3 Duplicates: 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, 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)

知识拓展:
在这里插入图片描述

4.2 截断表

语法:

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)

5. 插入查询结果

语法:

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

思路:
在这里插入图片描述
为啥最后通过rename方式进行呢? -- 就是等一切数据就绪再进行统一放入/更新/生效
不先进行rename再插入呢? -- MySQL当中rename的效率是非常高效的,插入是十分低效的而且也不安全(万一在插入的过程中断点丢失数据是非常危险的)

-- 查看最终结果
SELECT * FROM duplicate_table;
+----+-----+
| id | name|
+----+-----+
| 100| aaa |
| 200| bbb |
| 300| ccc |
+----+-----+
3 rows in set (0.00 sec)

6. 聚合函数

+-------------------------+------------------------------------+ 
|            函数          |                  说明               |
+-------------------------+------------------------------------+ 
| COUNT([DISTINCT] expr)  | 返回查询到的数据的 数量                |
| SUM([DISTINCT] expr)    | 返回查询到的数据的 总和,不是数字没有意义  |
| AVG([DISTINCT] expr)    | 返回查询到的数据的 平均值,不是数字没有意义|
| MAX([DISTINCT] expr)    | 返回查询到的数据的 最大值,不是数字没有意义|
| MIN([DISTINCT] expr)    | 返回查询到的数据的 最小值,不是数字没有意义|
+-------------------------+------------------------------------+

案例:

6.1 统计班级共有多少同学

-- 使用 * 做统计,不受 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)

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

-- NULL 不会计入结果
SELECT COUNT(qq) FROM students;
+-----------+
| COUNT(qq) |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

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

-- 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(DISTINCT math) |
+----------------------+
|                    5 |
+----------------------+
1 row in set (0.00 sec)

6.4 统计数学成绩总分

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)

6.4 统计平均总分

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

6.5 返回英语最高分

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

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

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

7. group by子句的使用

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

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

案例:
准备工作,创建一个雇员信息表(来自oracle 9i的经典测试表)
这里我将雇员信息表放在gitee上,点击这里即可!!!

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

下载到本地上之后导入到云服务的MySQL数据目录下:

-rw-r--r-- 1 root  root    3878 Mar 25  2022 scott_data.sql

[root@iZ0jl69kyvg0h181cozuf5Z tt]# pwd
/var/lib/mysql/tt
这里tt是我自己建的数据库(自己选择路径)

将数据库还原出来:

mysql> source /var/lib/mysql/tt/scott_data.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 1 row affected (0.01 sec)

对应的/var/lib/mysql下同样出现对应的数据库内容文件

[root@iZ0jl69kyvg0h181cozuf5Z mysql]# pwd
/var/lib/mysql
[root@iZ0jl69kyvg0h181cozuf5Z mysql]# ll
drwxr-x--- 2 mysql mysql     4096 Mar 21 09:30 scott

7.1 显示每个部门的平均工资和最高工资

在这里插入图片描述
在这里插入图片描述

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

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

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

统计各个部门的平均工资

select avg(sal) from EMP group by deptno

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

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

--having经常和group by搭配使用,作用是对分组进行筛选,作用有些像where

7.4 SMITH员工不参与计算,显示平均工资低于2000的岗位和它的平均工资

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

在这里插入图片描述

切屏小技巧:
在这里插入图片描述

练习题

在进行练习之前,请务必熟悉两篇博客当中的内容并且动手实践!!!

1. SQL195 查找最晚入职员工的所有信息

在这里插入图片描述

2. SQL196 查找入职员工时间排名倒数第三的员工所有信息

在这里插入图片描述

3. SQL201 查找薪水记录超过15条的员工号emp_no以及其对应的记录次数t

4. 182. 查找重复的电子邮箱

5. 595. 大国

6. 177. 第N高薪

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
12天前
|
SQL 前端开发 关系型数据库
SpringBoot使用mysql查询昨天、今天、过去一周、过去半年、过去一年数据
SpringBoot使用mysql查询昨天、今天、过去一周、过去半年、过去一年数据
43 9
|
13天前
|
缓存 监控 关系型数据库
如何优化MySQL查询速度?
如何优化MySQL查询速度?【10月更文挑战第31天】
41 3
|
17天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
85 1
|
18天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第26天】数据库作为现代应用系统的核心组件,其性能优化至关重要。本文主要探讨MySQL的索引策略与查询性能调优。通过合理创建索引(如B-Tree、复合索引)和优化查询语句(如使用EXPLAIN、优化分页查询),可以显著提升数据库的响应速度和稳定性。实践中还需定期审查慢查询日志,持续优化性能。
47 0
|
1月前
|
存储 SQL 关系型数据库
Mysql学习笔记(二):数据库命令行代码总结
这篇文章是关于MySQL数据库命令行操作的总结,包括登录、退出、查看时间与版本、数据库和数据表的基本操作(如创建、删除、查看)、数据的增删改查等。它还涉及了如何通过SQL语句进行条件查询、模糊查询、范围查询和限制查询,以及如何进行表结构的修改。这些内容对于初学者来说非常实用,是学习MySQL数据库管理的基础。
128 6
|
8天前
|
SQL 关系型数据库 MySQL
12 PHP配置数据库MySQL
路老师分享了PHP操作MySQL数据库的方法,包括安装并连接MySQL服务器、选择数据库、执行SQL语句(如插入、更新、删除和查询),以及将结果集返回到数组。通过具体示例代码,详细介绍了每一步的操作流程,帮助读者快速入门PHP与MySQL的交互。
24 1
|
10天前
|
SQL 关系型数据库 MySQL
go语言数据库中mysql驱动安装
【11月更文挑战第2天】
26 4
|
1月前
|
存储 关系型数据库 MySQL
Mysql(4)—数据库索引
数据库索引是用于提高数据检索效率的数据结构,类似于书籍中的索引。它允许用户快速找到数据,而无需扫描整个表。MySQL中的索引可以显著提升查询速度,使数据库操作更加高效。索引的发展经历了从无索引、简单索引到B-树、哈希索引、位图索引、全文索引等多个阶段。
61 3
Mysql(4)—数据库索引
|
19天前
|
关系型数据库 MySQL Linux
在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。
本文介绍了在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。同时,文章还对比了编译源码安装与使用 RPM 包安装的优缺点,帮助读者根据需求选择最合适的方法。通过具体案例,展示了编译源码安装的灵活性和定制性。
61 2
|
22天前
|
存储 关系型数据库 MySQL
MySQL vs. PostgreSQL:选择适合你的开源数据库
在众多开源数据库中,MySQL和PostgreSQL无疑是最受欢迎的两个。它们都有着强大的功能、广泛的社区支持和丰富的生态系统。然而,它们在设计理念、性能特点、功能特性等方面存在着显著的差异。本文将从这三个方面对MySQL和PostgreSQL进行比较,以帮助您选择更适合您需求的开源数据库。
89 4