【MySQL】 基本查询(下)
接待拜访：雾里看山-CSDN博客本篇主题：【MySQL】 基本查询(下)
发布时间：2025.2.18
隶属专栏：MySQL
https://i-blog.csdnimg.cn/direct/5875cfcc16044a1f87500b9776ae29e4.gif#pic_center
Update
语法
UPDATE table_name SET column = expr [, column = expr ...]
对查询到的结果进行列值更新
案例
[*]将孙悟空同学的数学成绩变动为 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: 1Changed: 1Warnings: 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=70where name='曹孟德';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1Changed: 1Warnings: 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: 3Changed: 3Warnings: 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: 7Changed: 7Warnings: 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
案例
[*]删除孙悟空同学的测验成绩
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: 3Duplicates: 0Warnings: 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_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: 3Duplicates: 0Warnings: 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: 6Duplicates: 0Warnings: 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: 3Duplicates: 0Warnings: 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( expr)返回查询到的数据的 数量SUM( expr)返回查询到的数据的 总和，不是数字没故意义AVG( expr)返回查询到的数据的 均匀值，不是数字没故意义MAX( expr)返回查询到的数据的 最大值，不是数字没故意义MIN( 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企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]