【MySQL】基本查询(下)

tsx81429 · 2025-2-19 06:09:58

接待拜访：雾里看山-CSDN博客
本篇主题：【MySQL】基本查询(下)
发布时间：2025.2.18
隶属专栏：MySQL

Update

语法

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

复制代码

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

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

mysql> select name, math from exam_result;
+-----------+------+
| name | math |
+-----------+------+
| 唐三藏 | 98 |
| 孙悟空 | 78 |
| 猪悟能 | 98 |
| 曹孟德 | 84 |
| 刘玄德 | 85 |
| 孙权 | 73 |
| 宋公明 | 65 |
+-----------+------+
7 rows 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;
+-----------+------+
| name | math |
+-----------+------+
| 唐三藏 | 98 |
| 孙悟空 | 80 |
| 猪悟能 | 98 |
| 曹孟德 | 84 |
| 刘玄德 | 85 |
| 孙权 | 73 |
| 宋公明 | 65 |
+-----------+------+
7 rows in set (0.00 sec)

复制代码

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

mysql> select * from exam_result;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孙悟空 | 87 | 80 | 77 |
| 3 | 猪悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 刘玄德 | 55 | 85 | 45 |
| 6 | 孙权 | 70 | 73 | 78 |
| 7 | 宋公明 | 75 | 65 | 30 |
+----+-----------+---------+------+---------+
7 rows in set (0.00 sec)
mysql> update exam_result set math=60, chinese=70 where name='曹孟德';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from exam_result;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孙悟空 | 87 | 80 | 77 |
| 3 | 猪悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 70 | 60 | 67 |
| 5 | 刘玄德 | 55 | 85 | 45 |
| 6 | 孙权 | 70 | 73 | 78 |
| 7 | 宋公明 | 75 | 65 | 30 |
+----+-----------+---------+------+---------+
7 rows in set (0.00 sec)

复制代码

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

mysql> select name, math+chinese+english total from exam_result order by total asc 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.02 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select name, math+chinese+english total from exam_result;
+-----------+-------+
| name | total |
+-----------+-------+
| 唐三藏 | 221 |
| 孙悟空 | 244 |
| 猪悟能 | 276 |
| 曹孟德 | 227 |
| 刘玄德 | 215 |
| 孙权 | 221 |
| 宋公明 | 200 |
+-----------+-------+
7 rows in set (0.00 sec)

复制代码

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

复制代码

Delete

删除数据

语法

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

复制代码

案例

删除孙悟空同学的测验成绩

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.00 sec)
mysql> select * from exam_result;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 134 | 98 | 56 |
| 3 | 猪悟能 | 176 | 98 | 90 |
| 4 | 曹孟德 | 140 | 90 | 67 |
| 5 | 刘玄德 | 110 | 115 | 45 |
| 6 | 孙权 | 140 | 73 | 78 |
| 7 | 宋公明 | 150 | 95 | 30 |
+----+-----------+---------+------+---------+
6 rows in set (0.00 sec)

复制代码

删除倒数第一名

mysql> select name, chinese+math+english total from exam_result order by total asc limit 1;
+-----------+-------+
| name | total |
+-----------+-------+
| 刘玄德 | 270 |
+-----------+-------+
1 row in set (0.00 sec)
mysql> delete from exam_result order by chinese+math+english asc limit 1;
Query OK, 1 row affected (0.01 sec)
mysql> select * from exam_result;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 134 | 98 | 56 |
| 3 | 猪悟能 | 176 | 98 | 90 |
| 4 | 曹孟德 | 140 | 90 | 67 |
| 6 | 孙权 | 140 | 73 | 78 |
| 7 | 宋公明 | 150 | 95 | 30 |
+----+-----------+---------+------+---------+
5 rows in set (0.00 sec)

复制代码

删除整张表数据

注意：删除整表操纵要慎用！
创建表结构插入数据并查看。

mysql> CREATE TABLE for_delete (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(20)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C');
Query OK, 3 rows affected (0.01 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> 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=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

复制代码

删除数据

mysql> delete from for_delete;
Query OK, 3 rows affected (0.01 sec)
mysql> select * from for_delete;
Empty set (0.00 sec)

复制代码

插入数据并查看表结构

mysql> insert into for_delete (name) values ('D');
Query OK, 1 row affected (0.00 sec)
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)
mysql> select * from for_delete;
+----+------+
| id | name |
+----+------+
| 4 | D |
+----+------+
1 row in set (0.00 sec)

复制代码

截断表

语法

TRUNCATE [TABLE] table_name

复制代码

注意：这个操纵慎用

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

案例

创建表结构并插入数据

mysql> CREATE TABLE for_truncate (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(20)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO for_truncate (name) VALUES ('A'), ('B'), ('C');
Query OK, 3 rows affected (0.00 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)

复制代码

截断整表数据，注意影响行数是 0，所以实际上没有对数据真正操纵

mysql> truncate for_truncate;
Query OK, 0 rows affected (0.03 sec)
mysql> select * from for_truncate;
Empty set (0.00 sec)

复制代码

再插入一条数据，自增 id 在重新增长

mysql> insert into for_truncate (name) values ('D');
Query OK, 1 row affected (0.01 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)
mysql> select * from for_truncate;
+----+------+
| id | name |
+----+------+
| 1 | D |
+----+------+
1 row in set (0.00 sec)

复制代码

插入查询结果

语法

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

复制代码

案例

删除表中的的重复复记录，重复的数据只能有一份
创建原数据表并插入数据

mysql> CREATE TABLE duplicate_table (id int, name varchar(20));
Query OK, 0 rows affected (0.01 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.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
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)

复制代码

创建一张空表 no_duplicate_table，结构和 duplicate_table 一样

mysql> create table no_duplicate_table like duplicate_table;
Query OK, 0 rows affected (0.02 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)

复制代码

将 duplicate_table 的去重数据插入到 no_duplicate_table

mysql> insert into no_duplicate_table select distinct * from duplicate_table;
Query OK, 3 rows affected (0.03 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 old_duplicate_table;
Query OK, 0 rows affected (0.02 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.01 sec)

复制代码

为什么末了是通过rename的方式进行的？
就是单纯的等统统都就绪了，然后统一放入、更新、见效等。确保操纵是原子的。
聚合函数

函数介绍

函数说明COUNT([DISTINCT] expr)返回查询到的数据的数量SUM([DISTINCT] expr)返回查询到的数据的总和，不是数字没故意义AVG([DISTINCT] expr)返回查询到的数据的均匀值，不是数字没故意义MAX([DISTINCT] expr)返回查询到的数据的最大值，不是数字没故意义MIN([DISTINCT] expr)返回查询到的数据的最小值，不是数字没故意义案例

统计班级共有多少同学

使用 * 做统计，不受 NULL 影响

mysql> select count(*) from exam_result;
+----------+
| count(*) |
+----------+
| 5 |
+----------+
1 row in set (0.03 sec)

复制代码

使用表达式做统计

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

复制代码

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

NULL 不管帐入结果

mysql> select count(qq) from students;
+-----------+
| count(qq) |
+-----------+
| 9 |
+-----------+
1 row in set (0.00 sec)

复制代码

统计本次测验的数学成绩分数个数

COUNT(math) 统计的是全部成绩

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

复制代码

COUNT(DISTINCT math) 统计的是去重成绩数量

mysql> select count(distinct math) from exam_result;
+----------------------+
| count(distinct math) |
+----------------------+
| 4 |
+----------------------+
1 row in set (0.02 sec)

复制代码

统计数学成绩总分

如果没有结果，则返回 NULL

mysql> select sum(math) from exam_result;
+-----------+
| sum(math) |
+-----------+
| 454 |
+-----------+
1 row in set (0.00 sec)
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;
+--------------+
| 平均总分 |
+--------------+
| 303 |
+--------------+
1 row in set (0.00 sec)

复制代码

返回英语最高分

mysql> select max(english) from exam_result;
+--------------+
| max(english) |
+--------------+
| 90 |
+--------------+
1 row in set (0.01 sec)

复制代码

返回 > 70 分以上的英语最低分

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

复制代码

group by子句的使用

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

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

复制代码

分组的目标是为了进行分组以后，方便进行聚合统计
having和where

where：对具体的任意列进行条件筛选
having：对分组聚合之后的结果进行条件筛选
案例

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

EMP员工表
DEPT部分表
SALGRADE工资等级表

具体代码可私我

怎样体现每个部分的均匀工资和最高工资

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

复制代码

体现均匀工资低于2000的部分和它的均匀工资

统计各个部分的均匀工资

mysql> select avg(sal) from emp group by deptno;
+-------------+
| avg(sal) |
+-------------+
| 2916.666667 |
| 2175.000000 |
| 1566.666667 |
+-------------+
3 rows in set (0.00 sec)

复制代码

having和group by共同使用，对group by结果进行过滤

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

复制代码

结语

不要单纯的认为，只有磁盘上的表结构导入到MySQL，真实存在的表才叫表。中心筛选出来的，包括最闭幕果，都可以看成逻辑上的表。
MySQL统统皆表
将来我们只要能够处置惩罚好单表的CURD，所有sql场景，我们全部都能用统一的方式进行。
⚠️ 写在末了：以上内容是我在学习以后得一些总结和概括，如有错误或者需要补充的地方接待各位大佬评论或者私信我交流！！！

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

		自动登录	找回密码
密码			立即注册

【MySQL】基本查询(下)

本帖子中包含更多资源

0 个回复

快速回复

楼主热帖

标签云

【MySQL】 基本查询(下)

本帖子中包含更多资源

0 个回复

快速回复

楼主热帖

标签云

【MySQL】基本查询(下)