mysql 基本查询（CURD）+ 进阶操作

虾..

770人浏览 · 2026-03-02 14:34:45

虾.. · 2026-03-02 14:34:45 发布

表的基本查询分为增删查（CURD）

create(创建),update(更新),retrieve(读取),delete(删除)

1.Create（创建）

语法：

INSERT [INTO] table_name
[(column [, column] ...)]
VALUES (value_list) [, (value_list)] ...
value_list: value, [, value] ...

案例：

--创建一张学生表
CREATE TABLE students (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
sn INT NOT NULL UNIQUE COMMENT '学号',
name VARCHAR(20) NOT NULL,
qq VARCHAR(20)
);

6.1 单行数据+全列插入

--插入两条记录，value_list 数量必须和定义表的列的数量及顺序一致
--注意，这里在插入的时候，也可以不用指定id(当然，那时候就需要明确插入数据到那些列了)，那么mysql会使用默认的值进行自增。

mysql> INSERT INTO students VALUES (100, 10000, '唐三藏', NULL);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO students VALUES (101, 10001, '孙悟空', '11111');
Query OK, 1 row affected (0.01 sec)

查看插入结果

mysql> select*from students;
+-----+-------+-----------+-------+
| id  | sn    | name      | qq    |
+-----+-------+-----------+-------+
| 100 | 10000 | 唐三藏    | NULL  |
| 101 | 10001 | 孙悟空    | 11111 |
+-----+-------+-----------+-------+
2 rows in set (0.00 sec)

6.2 多行数据+指定列插入

插入两条记录，value_list 数量必须和指定列数量及顺序一致。

只插入id，sn，name三个字段，因为name默认值为空，会自动设置为null。

mysql> insert into students(id,sn,name) values(102,20001,'曹孟德'),(103,20002,'李白');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

查询结果：

mysql> select*from students;
+-----+-------+-----------+-------+
| id  | sn    | name      | qq    |
+-----+-------+-----------+-------+
| 100 | 10000 | 唐三藏    | NULL  |
| 101 | 10001 | 孙悟空    | 11111 |
| 102 | 20001 | 曹孟德    | NULL  |
| 103 | 20002 | 李白      | NULL  |
+-----+-------+-----------+-------+
4 rows in set (0.00 sec)

6.3 插入否则更新

如果由于主键或者唯一键对应的值已经存在而导致插入失败。

//主键冲突
mysql> INSERT INTO students (id, sn, name) VALUES (100, 10010, '唐大师');
ERROR 1062 (23000): Duplicate entry '100' for key 'students.PRIMARY'
//唯一键冲突
mysql> INSERT INTO students (sn, name) VALUES (20001, '曹阿瞒');
ERROR 1062 (23000): Duplicate entry '20001' for key 'students.sn'

可以选择性的进行同步更新算法：

INSERT ... ON DUPLICATE KEY UPDATE
column = value [, column = value] ...

插入时在后续加上on duplicate和要更新的字段

下面的代码就是插入时如果没有冲突就直接插入，如果主键冲突了，就将表里面主键冲突的条目的sn修改为10000，name修改为'唐僧'

mysql> insert into students(id,sn,name) values(100,10000,'唐僧') on duplicate key update sn=10000,name='唐僧';
Query OK, 2 rows affected (0.01 sec)

修改后结果：

mysql> select*from students;
+-----+-------+-----------+-------+
| id  | sn    | name      | qq    |
+-----+-------+-----------+-------+
| 100 | 10000 | 唐僧      | NULL  |
| 101 | 10001 | 孙悟空    | 11111 |
| 102 | 20001 | 曹孟德    | NULL  |
| 103 | 20002 | 李白      | NULL  |
+-----+-------+-----------+-------+
4 rows in set (0.00 sec)

6.4 替换

主键或者唯一键没有冲突，则直接插入；
主键或者唯一键如果冲突，则删除后再插入

用法和insert差不多

替换sn=20001的数据

mysql> replace into students(sn,name) values(20001,'曹阿瞒');
Query OK, 2 rows affected (0.01 sec)

替换结果：

mysql> select*from students;
+-----+-------+-----------+-------+
| id  | sn    | name      | qq    |
+-----+-------+-----------+-------+
| 100 | 10000 | 唐僧      | NULL  |
| 101 | 10001 | 孙悟空    | 11111 |
| 103 | 20002 | 李白      | NULL  |
| 105 | 20001 | 曹阿瞒    | NULL  |
+-----+-------+-----------+-------+
4 rows in set (0.00 sec)

2.Retrieve（读取）

语法：

SELECT
[DISTINCT] {* | {column [, column] ...}
[FROM table_name]
[WHERE ...]
[ORDER BY column [ASC | DESC], ...]
LIMIT ...

案例：

//创建表结构
CREATE TABLE exam_result (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL COMMENT '同学姓名',
chinese float DEFAULT 0.0 COMMENT '语文成绩',
math float DEFAULT 0.0 COMMENT '数学成绩',
english float DEFAULT 0.0 COMMENT '英语成绩'
);
//插入测试数据
INSERT INTO exam_result (name, chinese, math, english) VALUES
('唐三藏', 67, 98, 56),
('孙悟空', 87, 78, 77),
('猪悟能', 88, 98, 90),
('曹孟德', 82, 84, 67),
('刘玄德', 55, 85, 45),
('孙权', 70, 73, 78),
('宋公明', 75, 65, 30);
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0

2.1 select列

2.1.1 全列查询

通常情况下不建议使用 * 进行全列查询
1. 查询的列越多，意味着需要传输的数据量越大；
2. 可能会影响到索引的使用。（索引待后面博客讲解）

mysql> select*from exam_result;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | 唐三藏    |      67 |   98 |      56 |
|  2 | 孙悟空    |      87 |   78 |      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)

2.1.2 指定列查询

指定列的查询不需要按定义表的顺序来

mysql> select id,name,english from exam_result;
+----+-----------+---------+
| id | name      | english |
+----+-----------+---------+
|  1 | 唐三藏    |      56 |
|  2 | 孙悟空    |      77 |
|  3 | 猪悟能    |      90 |
|  4 | 曹孟德    |      67 |
|  5 | 刘玄德    |      45 |
|  6 | 孙权      |      78 |
|  7 | 宋公明    |      30 |
+----+-----------+---------+
7 rows in set (0.00 sec)

2.1.3 查询指定字段为表达式

表达式不含字段

mysql> select id,name,10 from exam_result;
+----+-----------+----+
| id | name      | 10 |
+----+-----------+----+
|  1 | 唐三藏    | 10 |
|  2 | 孙悟空    | 10 |
|  3 | 猪悟能    | 10 |
|  4 | 曹孟德    | 10 |
|  5 | 刘玄德    | 10 |
|  6 | 孙权      | 10 |
|  7 | 宋公明    | 10 |
+----+-----------+----+
7 rows in set (0.00 sec)

表达式包含一个字段

mysql> select id,name,english+10 from exam_result;
+----+-----------+------------+
| id | name      | english+10 |
+----+-----------+------------+
|  1 | 唐三藏    |         66 |
|  2 | 孙悟空    |         87 |
|  3 | 猪悟能    |        100 |
|  4 | 曹孟德    |         77 |
|  5 | 刘玄德    |         55 |
|  6 | 孙权      |         88 |
|  7 | 宋公明    |         40 |
+----+-----------+------------+
7 rows in set (0.00 sec)

表达式包含多个字段

mysql> select id,name,chinese+english+math from exam_result;
+----+-----------+----------------------+
| id | name      | chinese+english+math |
+----+-----------+----------------------+
|  1 | 唐三藏    |                  221 |
|  2 | 孙悟空    |                  242 |
|  3 | 猪悟能    |                  276 |
|  4 | 曹孟德    |                  233 |
|  5 | 刘玄德    |                  185 |
|  6 | 孙权      |                  221 |
|  7 | 宋公明    |                  170 |
+----+-----------+----------------------+
7 rows in set (0.00 sec)

2.1.4 为查询结果指定别名

语法：

SELECT column [AS] alias_name [...] FROM table_name;

示例：

将上面查询的表达式chinese+english+math取别名为总分。

mysql> select id,name,chinese+english+math 总分 from exam_result;
+----+-----------+--------+
| id | name      | 总分   |
+----+-----------+--------+
|  1 | 唐三藏    |    221 |
|  2 | 孙悟空    |    242 |
|  3 | 猪悟能    |    276 |
|  4 | 曹孟德    |    233 |
|  5 | 刘玄德    |    185 |
|  6 | 孙权      |    221 |
|  7 | 宋公明    |    170 |
+----+-----------+--------+
7 rows in set (0.00 sec)

2.1.5 结果去重

没去重可以看到有2个98分重复了

mysql> select math from exam_result;
+------+
| math |
+------+
|   98 |
|   78 |
|   98 |
|   84 |
|   85 |
|   73 |
|   65 |
+------+

去重结果：

加上distinct关键字

mysql> select distinct math from exam_result;
+------+
| math |
+------+
|   98 |
|   78 |
|   84 |
|   85 |
|   73 |
|   65 |
+------+
6 rows in set (0.00 sec)

2.2 where条件

2.2.1 英语成绩小于60的同学及英语成绩

mysql> select english from exam_result where english<60;
+---------+
| english |
+---------+
|      56 |
|      45 |
|      30 |
+---------+
3 rows in set (0.00 sec)

2.2.2 语文成绩在[80,90]分的同学及语文成绩

方法1：使用and，查找语文成绩小于80和成绩小于90的mysql> select chinese from exam_result

mysql> select chinese from exam_result where chinese>=80 and chinese<=90;
+---------+
| chinese |
+---------+
|      87 |
|      88 |
|      82 |
+---------+
3 rows in set (0.00 sec)

方法2：使用between...and...，查找语文成绩成绩在80分到90分这个区间的

mysql> select chinese from exam_result where chinese between 80 and 90;
+---------+
| chinese |
+---------+
|      87 |
|      88 |
|      82 |
+---------+
3 rows in set (0.00 sec)

2.2.3 数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩

使用in条件

mysql> select name,math from exam_result where math in(58,59,98,99);
+-----------+------+
| name      | math |
+-----------+------+
| 唐三藏    |   98 |
| 猪悟能    |   98 |
+-----------+------+
2 rows in set (0.00 sec)

2.2.4 性孙的同学及孙某同学

%匹配多个（包括0个）任意字符

mysql> select name from exam_result where name like '孙%';
+-----------+
| name      |
+-----------+
| 孙悟空    |
| 孙权      |
+-----------+
2 rows in set (0.00 sec)

2.2.5 语文成绩好于英语成绩的同学

使用where中的>来比较字段

mysql> select name,chinese,english from exam_result where chinese>english;
+-----------+---------+---------+
| name      | chinese | english |
+-----------+---------+---------+
| 唐三藏    |      67 |      56 |
| 孙悟空    |      87 |      77 |
| 曹孟德    |      82 |      67 |
| 刘玄德    |      55 |      45 |
| 宋公明    |      75 |      30 |
+-----------+---------+---------+
5 rows in set (0.00 sec)

2.2.6 总分在200分以下的同学

where条件中使用表达式

mysql> select name,chinese+english+math 总分 from exam_result where 总分<200;
ERROR 1054 (42S22): Unknown column '总分' in 'where clause'

上面这样写是明显不可以的，因为mysql执行顺序是from先确定表，然后where条件匹配，最后才是select确定列（如果取了别名，显示出来的是别名），这个顺序下，where是不认识别名的，所以where条件中是不可以使用别名的。

mysql> select name,chinese+english+math 总分 from exam_result where chinese+english+math<200;
+-----------+--------+
| name      | 总分   |
+-----------+--------+
| 刘玄德    |    185 |
| 宋公明    |    170 |
+-----------+--------+
2 rows in set (0.00 sec)

2.2.6 语文成绩>80并且不性孙的同学

and和not的使用

mysql> select name,chinese from exam_result where chinese>80 and name not like '孙%';
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 猪悟能    |      88 |
| 曹孟德    |      82 |
+-----------+---------+
2 rows in set (0.00 sec)

2.2.7 孙某同学，否则要求总成绩>200并且语文成绩<数学成绩并且英语成绩>80

综合查询

mysql> select name,chinese,math,english,chinese+math+english 总分 from exam_result where name like '孙_' or(chinese+math+english>200 and chinese<math and english>80);
+-----------+---------+------+---------+--------+
| name      | chinese | math | english | 总分   |
+-----------+---------+------+---------+--------+
| 猪悟能    |      88 |   98 |      90 |    276 |
| 孙权      |      70 |   73 |      78 |    221 |
+-----------+---------+------+---------+--------+
2 rows in set (0.00 sec)

2.2.7 NULL的查询

查询students表

mysql> select*from students;
+-----+-------+-----------+-------+
| id  | sn    | name      | qq    |
+-----+-------+-----------+-------+
| 100 | 10000 | 唐僧      | NULL  |
| 101 | 10001 | 孙悟空    | 11111 |
| 103 | 20002 | 李白      | NULL  |
| 105 | 20001 | 曹阿瞒    | NULL  |
+-----+-------+-----------+-------+
4 rows in set (0.00 sec)

可以看到qq字段中有很多条数据为NULL

查询qq号不为NULL的同学

mysql> select *from students where qq is not NULL;
+-----+-------+-----------+-------+
| id  | sn    | name      | qq    |
+-----+-------+-----------+-------+
| 101 | 10001 | 孙悟空    | 11111 |
+-----+-------+-----------+-------+
1 row in set (0.00 sec)

NULL和NULL的比较，=和<=>的区别

mysql> select NULL=NULL,NULL=1,NULL=0;
+-----------+--------+--------+
| NULL=NULL | NULL=1 | NULL=0 |
+-----------+--------+--------+
|      NULL |   NULL |   NULL |
+-----------+--------+--------+
1 row in set (0.00 sec)

有关于NULL的比较，使用=是无法比较的，都会显示NULL,需要使用<=>来进行有关于NULL的比较。

mysql> select NULL<=>NULL,NULL<=>1,NULL<=>0;
+-------------+----------+----------+
| NULL<=>NULL | NULL<=>1 | NULL<=>0 |
+-------------+----------+----------+
|           1 |        0 |        0 |
+-------------+----------+----------+
1 row in set (0.00 sec)

2.3 结果排序

语法：
ASC 为升序（从小到大）
DESC 为降序（从大到小）
默认为 ASC
SELECT ... FROM table_name [WHERE ...]
ORDER BY column [ASC|DESC], [...];

注意：没有 ORDER BY 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序。

2.3.1 同学及数学成绩，按数学成绩升序排序

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

2.3.2 同学及qq号，按qq号排序显示

NULL视为比任何值都小升序排在最上面

mysql> select name,qq from students order by qq;
+-----------+-------+
| name      | qq    |
+-----------+-------+
| 唐僧      | NULL  |
| 李白      | NULL  |
| 曹阿瞒    | NULL  |
| 孙悟空    | 11111 |
+-----------+-------+
4 rows in set (0.00 sec)

降序排在最下面

mysql> select name,qq from students order by qq desc;
+-----------+-------+
| name      | qq    |
+-----------+-------+
| 孙悟空    | 11111 |
| 唐僧      | NULL  |
| 李白      | NULL  |
| 曹阿瞒    | NULL  |
+-----------+-------+
4 rows in set (0.00 sec)

2.3.3 查询同学各门成绩，依次按数学成绩降序，英语升序，语文升序的方式显示

多字段排序，排序的优先级随书写顺序

mysql> select name,math,english,chinese from exam_result order by math desc,english,chinese;
+-----------+------+---------+---------+
| name      | math | english | chinese |
+-----------+------+---------+---------+
| 唐三藏    |   98 |      56 |      67 |
| 猪悟能    |   98 |      90 |      88 |
| 刘玄德    |   85 |      45 |      55 |
| 曹孟德    |   84 |      67 |      82 |
| 孙悟空    |   78 |      77 |      87 |
| 孙权      |   73 |      78 |      70 |
| 宋公明    |   65 |      30 |      75 |
+-----------+------+---------+---------+
7 rows in set (0.00 sec)

2.3.4 查询同学们总分，由高到低

order by中可以使用表达式

mysql> select name,math+english+chinese from exam_result order by math+english+chinese desc;
+-----------+----------------------+
| name      | math+english+chinese |
+-----------+----------------------+
| 猪悟能    |                  276 |
| 孙悟空    |                  242 |
| 曹孟德    |                  233 |
| 唐三藏    |                  221 |
| 孙权      |                  221 |
| 刘玄德    |                  185 |
| 宋公明    |                  170 |
+-----------+----------------------+
7 rows in set (0.02 sec)

2.3.5 查询姓孙的同学或者姓曹的同学数学成绩，结果按数学成绩由高到低显示

where子句和order by子句

mysql> select name,math from exam_result where name like '孙%' or '曹%' order by math desc;
+-----------+------+
| name      | math |
+-----------+------+
| 孙悟空    |   78 |
| 孙权      |   73 |
+-----------+------+
2 rows in set, 1 warning (0.00 sec)

可以发现，order by后面是可以使用别名的

select name,math from exam_result where name like '孙%' or '曹%' order by math desc;

在MySQL中，执行顺序是第一布：select确定查询的表，第二步：where确定条件，第三步：select确定显示字段，第四步：最后才是对字段进行排序，order by排序是在select后面的。

2.4 筛选分页结果

起始下标为 0
从 0 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
从 s 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;
从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

建议：对未知表进行查询时，最好加一条 LIMIT 1，避免因为表中数据过大，查询全表数据导致数据库卡死，按 id 进行分页，每页 3 条记录，分别显示第 1、2、3 页

limit确定一页显示的行数，offset确定从第几条开始显示

第一页：

mysql> select name,math,english,chinese from exam_result order by id limit 3 offset 0;
+-----------+------+---------+---------+
| name      | math | english | chinese |
+-----------+------+---------+---------+
| 唐三藏    |   98 |      56 |      67 |
| 孙悟空    |   78 |      77 |      87 |
| 猪悟能    |   98 |      90 |      88 |
+-----------+------+---------+---------+
3 rows in set (0.00 sec)

第二页：

mysql> select name,math,english,chinese from exam_result order by id limit 3 offset 3;
+-----------+------+---------+---------+
| name      | math | english | chinese |
+-----------+------+---------+---------+
| 曹孟德    |   84 |      67 |      82 |
| 刘玄德    |   85 |      45 |      55 |
| 孙权      |   73 |      78 |      70 |
+-----------+------+---------+---------+
3 rows in set (0.00 sec)

第三页：

mysql> select name,math,english,chinese from exam_result order by id limit 3 offset 6;
+-----------+------+---------+---------+
| name      | math | english | chinese |
+-----------+------+---------+---------+
| 宋公明    |   65 |      30 |      75 |
+-----------+------+---------+---------+
1 row in set (0.00 sec)

结果如果不足3个，是不会有影响的。

3. Update更新

语法

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

3.1 将孙悟空的数学成绩更新为60分

//查看原数据
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.06 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)

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

//更新前数据
mysql> select name,math,chinese+math+english 总分 from exam_result order by 总分 limit 3;
+-----------+------+--------+
| name      | math | 总分   |
+-----------+------+--------+
| 宋公明    |   65 |    170 |
| 刘玄德    |   85 |    185 |
| 唐三藏    |   68 |    191 |
+-----------+------+--------+
3 rows in set (0.00 sec)
//更新数据
mysql> update exam_result set math=math+30 order by chinese+english+math limit 3;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0
//更新后数据
mysql> 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语句就是更新全表

//更新前数据
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.01 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)

4. delete 删除数据

4.1 删除整表（慎用）

//准备测试表
CREATE TABLE for_delete (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);
Query OK, 0 rows affected (0.11 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> delete from for_delete;
Query OK, 3 rows affected (0.00 sec)
//查看表
mysql> select*from for_delete;
Empty set (0.00 sec)

这个时候在插入数据，自增id会在之前的最大id上面++。

//插入数据
mysql> INSERT INTO for_delete (name) VALUES ('D');
Query OK, 1 row affected (0.00 sec)
//查看数据
mysql> select*from for_delete;
+----+------+
| id | name |
+----+------+
|  4 | D    |
+----+------+
1 row 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 NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

4.2 截断表

语法：

TRUNCATE [TABLE] table_name

1.注意截断表只能对整表进行操作。

2.截断表的操作是比delete的操作快的，truncate在删除数据的时候，不会经过真正的事务，所以无法回滚。

3.会重置auto_increment值

现在for_delete的下一个自增值为5。

mysql> show create table for_delete\G
*************************** 1. row ***************************
       Table: for_delete
Create Table: CREATE TABLE `for_delete` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

表中还有一条数据

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

使用tuncate截断操作删除表数据

mysql> truncate table for_delete;
Query OK, 0 rows affected (0.03 sec)

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

插入数据的自增id重新从1开始了

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

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

5. 进阶操作

5.1 插入查询结果

语法：

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

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

1.创建表并插入数据

//创建表
mysql> CREATE TABLE duplicate_table (id int, name varchar(20));
Query OK, 0 rows affected (0.04 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

2.创建一张空表，结构和第一张表一样

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

3.将表1的数据去重插入到表2

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

4.将两个表的名字重命名

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

mysql> alter table duplicate_table_2 rename to duplicate_table;
Query OK, 0 rows affected (0.02 sec)

5.查看表内数据

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

5.2 聚合函数

测试表1数据

mysql> select*from students;
+-----+-------+-----------+-------+
| id  | sn    | name      | qq    |
+-----+-------+-----------+-------+
| 100 | 10000 | 唐僧      | NULL  |
| 101 | 10001 | 孙悟空    | 11111 |
| 103 | 20002 | 李白      | NULL  |
| 105 | 20001 | 曹阿瞒    | NULL  |
+-----+-------+-----------+-------+
4 rows in set (0.00 sec)

测试表2数据

5.2.1 统计笔记有多少学生

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

mysql> select count(*) from students;
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.02 sec)

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

NULL不会计入结果

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

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

count(math)统计本次考试的数学成绩分数个数

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

count(distinct math) 统计的是去重之后的数量

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

5.2.4 统计数学成绩总分

1.统计所有学生的数学成绩总分

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

2.统计数学成绩小于60的同学的数学成绩总分，没有结果的话，会返回NULL

mysql> select sum(math) from exam_result where math<60;
+-----------+
| sum(math) |
+-----------+
|      NULL |
+-----------+
1 row in set (0.00 sec)

5.2.5 统计平均总分

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

5.2.6 返回英语最高分

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

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

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

5.3 分组查询（GROUP BY）

结合GROUP BY对数据按指定列分组，再用聚合函数统计每组数据，用HAVING过滤分组结果（类似WHERE但作用于分组）。\n\n案例：按部门统计工资\n假设有员工表emp（含deptno部门号、sal工资）：

1. 按部门分组，统计每个部门的平均工资和最高工资
SELECT deptno AS 部门号, AVG(sal) AS 平均工资, MAX(sal) AS 最高工资 
FROM emp 
GROUP BY deptno;

2. 筛选平均工资<2000的部门（HAVING过滤分组结果）
SELECT deptno AS 部门号, AVG(sal) AS 平均工资 
FROM emp 
GROUP BY deptno 
HAVING 平均工资 < 2000;

执行顺序：FROM → WHERE（过滤行）→ GROUP BY（分组）→ HAVING（过滤分组）→ SELECT（查询列）→ ORDER BY（排序）→ LIMIT（分页）。