MySQL表的增删查改-EW帮帮网

MySQL表的增删查改

1. 增

1.1 INSERT

案例表：

create table students(
    id int primary key auto_increment,
    stunum int not null unique comment '学号',
	name varchar(10) not null
	);

1.1.1 普通插入

INSERT 用于在数据库中插入一条数据。在插入数据时，如果是全列插入，则不需要指定数据字段的列；如果是指定列插入，则需要指定数据字段的列（如有默认值而没有自定义数据所有字段的情况）。

#全列插入
insert into students values (1,2024001,'李四');

#指定列插入，学号自增
insert into students (stunum,name) values (2024002,'王五');

1.1.2 插入时更新（ON DUPLICATE KEY UPDATE）

在插入数据时，可能会有旧数据已经存在，由于主键和唯一键对应的值已经存在导致插入失败。而 ON DUPLICATE KEY UPDATE 可以实现类似于对原数据进行更新的操作。

冲突：

插入一个学生赵六，它的 id 和学号被王五占用了：

#主键冲突
insert into students (id,stunum,name) values (1,2024003,'赵六');
ERROR 1062 (23000): Duplicate entry '1' for key 'students.PRIMARY'

#唯一键冲突
insert into students (stunum,name) values (2024002,'赵六');
ERROR 1062 (23000): Duplicate entry '2024002' for key 'students.stunum'

ON DUPLICATE KEY UPDATE：

将李四的 id 和王五的名字信息替换：

mysql> select * from students;
+----+---------+--------+
| id | stunum  | name   |
+----+---------+--------+
|  1 | 2024001 | 李四   |
|  2 | 2024002 | 王五   |
+----+---------+--------+

insert into students (id,stunum,name) values (3,2024001,'李四') on duplicate key update id=3;
Query OK, 2 rows affected (0.01 sec)
insert into students (id,stunum,name) values (2,2024001,'王五') on duplicate key update name='王五';
Query OK, 2 rows affected (0.01 sec)

mysql> select * from students;
+----+---------+--------+
| id | stunum  | name   |
+----+---------+--------+
|  2 | 2024002 | 王五   |
|  3 | 2024001 | 李四   |
+----+---------+--------+

ON DUPLICATE KEY UPDATE 适用于需要高效处理“存在更新，不存在插入”，和在需保留非冲突字段、避免自增 ID 增长或简化并发控制时的场景。通过观察执行插入语句后 Query 返回的语句，可以判断语句的执行情况：

Query	含义
0 row affected	表中有冲突数据，且冲突数据和 update 的值相等。
1 row affected	表中没有冲突数据，数据插入成功。
2 row affected	表中有冲突数据，且数据已经被更新。

1.1.3 替换（REPLACE）

REPLACE 可以替换数据，它的执行逻辑是：如果主键或唯一键没有冲突就直接插入；如果有冲突，就删除后再插入。注意，由于 REPLACE 在发生冲突时是将原数据删除再插入新的数据，此时如果字段设置了自增长（auto_increment），那么数据自增长部分仍然会 $+ 1$ 。

示例：

mysql> select * from students;
+----+---------+--------+
| id | stunum  | name   |
+----+---------+--------+
|  2 | 2024002 | 王五   |
|  3 | 2024001 | 李四   |
+----+---------+--------+

replace students (stunum,name) values (2024001,'赵六');
Query OK, 2 rows affected (0.01 sec)

mysql> select * from students;
+----+---------+--------+
| id | stunum  | name   |
+----+---------+--------+
|  2 | 2024002 | 王五   |
|  4 | 2024001 | 赵六   |
+----+---------+--------+
2 rows in set (0.00 sec)

可以看到，赵六的学号和李四发生冲突，李四数据被替换，id 也进行了自增。

REPLACE 的语句的执行情况，也可以通过观察执行插入语句后 Query 返回的语句判断：

Query	含义
1 row affected	表中没有冲突数据，数据插入成功。
2 row affected	表中有冲突数据，原数据删除后插入。

2. 查

2.1 SELECT

2.1.1 全列查询

通常情况下不会使用 * 进行全列查询，因为 * 是将所有的数据遍历一遍，查询的列越多，意味着需要传输的数据量越大，还可能会影响到索引的使用。

select * from students;
+----+---------+--------+
| id | stunum  | name   |
+----+---------+--------+
|  2 | 2024002 | 王五   |
|  4 | 2024001 | 赵六   |
+----+---------+--------+

2.1.2 指定列查询

指定列时，顺序不一定要按照定义表的顺序显示：

select name,stunum from students;
+--------+---------+
| name   | stunum  |
+--------+---------+
| 王五   | 2024002 |
| 赵六   | 2024001 |
+--------+---------+

2.1.3 表达式计算

SELECT 可以进行表达式计算，并将表达式结果以表格的形式给出。设计一个学生成绩单的表：

create table scores(
	name varchar(10),
	chinese tinyint unsigned,
	math tinyint unsigned,
	english tinyint unsigned);

insert into scores values('张三',95,80,90),('李四',85,100,80),('赵六',99,88,77);

SELECT 可以将表中各字段的和相加，并允许使用 as 为这个临时字段起别名（可以使用汉字）：

select name, chinese, math, english, chinese+math+english as total from scores;
+--------+---------+------+---------+-------+
| name   | chinese | math | english | total |
+--------+---------+------+---------+-------+
| 张三   |      95 |   80 |      90 |   265 |
| 李四   |      85 |  100 |      80 |   265 |
| 赵六   |      99 |   88 |      77 |   264 |
+--------+---------+------+---------+-------+

表达式即可以包含字段做加减乘除运算，也可以不包含字段做例如 $1 + 1$ 这样的简单运算：

select 1+1, 1-1, 1*2, 2/2;
+-----+-----+-----+--------+
| 1+1 | 1-1 | 1*2 | 2/2    |
+-----+-----+-----+--------+
|   2 |   0 |   2 | 1.0000 |
+-----+-----+-----+--------+

2.1.4 查询结果去重

DISTINCT 关键字可用于在查询结果中去重。去重时，要指明去重的是哪些字段：

select * from scores;
+-----------+---------+------+---------+
| name      | chinese | math | english |
+-----------+---------+------+---------+
| 张三      |      95 |   80 |      90 |
| 李四      |      85 |  100 |      80 |
| 赵六      |      99 |   88 |      77 |
| 张小三    |      95 |   81 |      91 |
| 李小四    |      90 |  100 |      88 |
| 赵小六    |      80 |   87 |      91 |
+-----------+---------+------+---------+

select distinct chinese from scores;
+---------+
| chinese |
+---------+
|      95 |
|      85 |
|      99 |
|      90 |
|      80 |
+---------+

要注意的是，DISTINCT 直接组合使用只能实现去重 A 重复且 B 重复的情况：

 #可以看到查询结果是6条，说明没有实现A和B的去重效果
 select distinct chinese, math from scores;
+---------+------+
| chinese | math |
+---------+------+
|      95 |   80 |
|      85 |  100 |
|      99 |   88 |
|      95 |   81 |
|      90 |  100 |
|      80 |   87 |
+---------+------+

如果在去重的同时，需要显示其他字段的信息，需要使用 GROUP BY 和聚合函数 GROUP_CONCAT 来实现：

select group_concat(name separator ', ') AS name, chinese
from scores
group by chinese;

+-------------------+---------+
| name              | chinese |
+-------------------+---------+
| 赵小六            |      80 |
| 李四              |      85 |
| 李小四            |      90 |
| 张三, 张小三      |      95 |
| 赵六              |      99 |
+-------------------+---------+

2.2 WHERE

WHERE 是 MySQL 查询中一个非常重要的关键字，它能够精确的控制需要实现的数据范围或条件。需要注意的是，WHERE 子句不能使用别名。

elect name, chinese, math, english, chinese+math+english as total from scores where total>270;
ERROR 1054 (42S22): Unknown column 'total' in 'where clause'
select name, chinese, math, english, chinese+math+english as total from scores where chinese+math+english>270;
+-----------+---------+------+---------+-------+
| name      | chinese | math | english | total |
+-----------+---------+------+---------+-------+
| 李小四    |      90 |  100 |      88 |   278 |
+-----------+---------+------+---------+-------+

2.2.1 运算符

比较运算符：

比较运算符	含义
>,>=	大于，大于等于。
<,<=	小于，小于等于。
=	等于，但 NULL 不安全。
<=>	等于，但 NULL 安全。
!=,<>	不等于。
BETWEEN $a_0$ AND $a_1$	范围匹配， $a_0,a_1]$ 如果 $a_0 <= \rm{value} <= a_1$ 返回 `TRUE(1)` 。
IN (option,…)	如果是 option 中的任意一个，返回 `TRUE(1)` 。
IS NULL	匹配是 `NULL` 的数据。
IS NOT NULL	匹配非 `NULL` 的数据。
LIKE	模糊匹配。`%` 表示任意多个（包括 0 个）任意字符；`_` 表示匹配任意一个字符。

NULL 安全是指能不能对 NULL 进行比较。

select null=null;
+-----------+
| null=null |
+-----------+
|      NULL |
+-----------+

select null<=>null;
+-------------+
| null<=>null |
+-------------+
|           1 |
+-------------+

逻辑运算符：

逻辑运算符	含义
AND	多个条件都必须为 `TRUE(1)`，结果才是 `TRUE(1)`。
OR	任意一个条件为 `TRUE(1)`，结果才是 `TRUE(1)`。
NOT	条件为 `TRUE(1)` 的，结果变为 `FALSE(0)` 。

2.2.2 排序（ORDER BY）

ORDER BY 用于给查询结果进行排序，没有 ORDER BY 子句的查询，返回结果的顺序都是未定义的，实践中不要依赖这个顺序。由于 ORDER BY 是对结果进行排序，它的子句中可以使用别名。

使用 ORDER BY 排序时，默认缺省是升序，使用 DESC 可以改为降序。默认 NULL 最小，空字符次之。

 select name, chinese, math, english, chinese+math+english as total from scores order by total;
+-----------+---------+------+---------+-------+
| name      | chinese | math | english | total |
+-----------+---------+------+---------+-------+
| 赵小六    |      80 |   87 |      91 |   258 |
| 赵六      |      99 |   88 |      77 |   264 |
| 张三      |      95 |   80 |      90 |   265 |
| 李四      |      85 |  100 |      80 |   265 |
| 张小三    |      95 |   81 |      91 |   267 |
| 李小四    |      90 |  100 |      88 |   278 |
+-----------+---------+------+---------+-------+

select name, chinese, math, english, chinese+math+english as total from scores order by total desc;
+-----------+---------+------+---------+-------+
| name      | chinese | math | english | total |
+-----------+---------+------+---------+-------+
| 李小四    |      90 |  100 |      88 |   278 |
| 张小三    |      95 |   81 |      91 |   267 |
| 张三      |      95 |   80 |      90 |   265 |
| 李四      |      85 |  100 |      80 |   265 |
| 赵六      |      99 |   88 |      77 |   264 |
| 赵小六    |      80 |   87 |      91 |   258 |
+-----------+---------+------+---------+-------+

2.2.3 查询结果分页（LIMIT）

如果表的内容很庞大，直接显示所有的查询结果可能会导致数据库卡死。对未知表时进行查询时，最好使用 LIMIT 来显示显示数据的行数。MySQL 默认数据的下标是从 0 开始的，LIMIT n,m 表示从第 $\rm n$ 条数据开始，查询查询 $\rm m$ 条数据。

 select name, chinese, math, english, chinese+math+english as total from scores order by total limit 0,3;
+-----------+---------+------+---------+-------+
| name      | chinese | math | english | total |
+-----------+---------+------+---------+-------+
| 赵小六    |      80 |   87 |      91 |   258 |
| 赵六      |      99 |   88 |      77 |   264 |
| 张三      |      95 |   80 |      90 |   265 |
+-----------+---------+------+---------+-------+

select name, chinese, math, english, chinese+math+english as total from scores order by total limit 3,6;
+-----------+---------+------+---------+-------+
| name      | chinese | math | english | total |
+-----------+---------+------+---------+-------+
| 李四      |      85 |  100 |      80 |   265 |
| 张小三    |      95 |   81 |      91 |   267 |
| 李小四    |      90 |  100 |      88 |   278 |
+-----------+---------+------+---------+-------+

也可以使用 OFFSET 设置每次查询的偏移量来达到分页显示的效果。

select name, chinese, math, english, chinese+math+english as total from scores order by total limit 3 offset 0;
+-----------+---------+------+---------+-------+
| name      | chinese | math | english | total |
+-----------+---------+------+---------+-------+
| 赵小六    |      80 |   87 |      91 |   258 |
| 赵六      |      99 |   88 |      77 |   264 |
| 张三      |      95 |   80 |      90 |   265 |
+-----------+---------+------+---------+-------+
3 rows in set (0.00 sec)

select name, chinese, math, english, chinese+math+english as total from scores order by total limit 3 offset 3;
+-----------+---------+------+---------+-------+
| name      | chinese | math | english | total |
+-----------+---------+------+---------+-------+
| 李四      |      85 |  100 |      80 |   265 |
| 张小三    |      95 |   81 |      91 |   267 |
| 李小四    |      90 |  100 |      88 |   278 |
+-----------+---------+------+---------+-------+

3. 改

3.1 UPDATE

UPDATE 用于对原有数据进行更新。注意，在操作时，需要至少指明操作的是哪几条数据，否则更新操作将会为该字段的所有数据都做相同的更新。

mysql> select * from scores;
+-----------+---------+------+---------+
| name      | chinese | math | english |
+-----------+---------+------+---------+
| 张三      |      95 |   80 |      90 |
| 李四      |      85 |  100 |      80 |
| 赵六      |      99 |   88 |      77 |
| 张小三    |      95 |   81 |      91 |
| 李小四    |      90 |  100 |      88 |
| 赵小六    |      80 |   87 |      91 |
+-----------+---------+------+---------+
6 rows in set (0.00 sec)

mysql> update scores set chinese = 59 where name = '张三';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update scores set chinese = 59, math = 59 where name = '李四';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update scores set chinese = 59, math = 59, english = 59 where name like '赵%';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from scores;
+-----------+---------+------+---------+
| name      | chinese | math | english |
+-----------+---------+------+---------+
| 张三      |      59 |   80 |      90 |
| 李四      |      59 |   59 |      80 |
| 赵六      |      59 |   59 |      59 |
| 张小三    |      95 |   81 |      91 |
| 李小四    |      90 |  100 |      88 |
| 赵小六    |      59 |   59 |      59 |
+-----------+---------+------+---------+
6 rows in set (0.00 sec)

将所有语文成绩 $< 60$ 的同学语文成绩 $+ 1$ ：

注意 MySQL 不能使用 += 这样的语法。

 update scores set chinese = chinese + 1 where chinese <60;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> select * from scores;
+-----------+---------+------+---------+
| name      | chinese | math | english |
+-----------+---------+------+---------+
| 张三      |      60 |   80 |      90 |
| 李四      |      60 |   59 |      80 |
| 赵六      |      60 |   59 |      59 |
| 张小三    |      95 |   81 |      91 |
| 李小四    |      90 |  100 |      88 |
| 赵小六    |      60 |   59 |      59 |
+-----------+---------+------+---------+

将总分倒数前三的同学数学成绩加 30 分：

使用 LIMIT 可以显示 UPDATE 生效的数据范围。

mysql> select name, chinese, math, english, chinese+math+english as total from scores order by total;
+-----------+---------+------+---------+-------+
| name      | chinese | math | english | total |
+-----------+---------+------+---------+-------+
| 赵六      |      60 |   59 |      59 |   178 |
| 赵小六    |      60 |   59 |      59 |   178 |
| 李四      |      60 |   59 |      80 |   199 |
| 张三      |      60 |   80 |      90 |   230 |
| 张小三    |      95 |   81 |      91 |   267 |
| 李小四    |      90 |  100 |      88 |   278 |
+-----------+---------+------+---------+-------+
6 rows in set (0.00 sec)

mysql> update scores set math=math+30 order by chinese+math+english limit 3;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select name, chinese, math, english, chinese+math+english as total from scores order by total;
+-----------+---------+------+---------+-------+
| name      | chinese | math | english | total |
+-----------+---------+------+---------+-------+
| 赵六      |      60 |   89 |      59 |   208 |
| 赵小六    |      60 |   89 |      59 |   208 |
| 李四      |      60 |   89 |      80 |   229 |
| 张三      |      60 |   80 |      90 |   230 |
| 张小三    |      95 |   81 |      91 |   267 |
| 李小四    |      90 |  100 |      88 |   278 |
+-----------+---------+------+---------+-------+
6 rows in set (0.00 sec)

4. 删

4.1 DELETE

DELETE 用于删除表中的某些数据。在删除某条数据时，需要配合 WHERE 来定位数据，否则 DELETE 会删除所有字段的所有数据。

create table test(
	id int primary key auto_increment,
	name varchar(10));
Query OK, 0 rows affected (0.03 sec)

insert into test values(1,'张三'),(2,'李四'),(3,'王五');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

select * from test;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 李四   |
|  3 | 王五   |
+----+--------+

删除表中某项数据：

mysql> delete from test where name='张三';
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+----+--------+
| id | name   |
+----+--------+
|  2 | 李四   |
|  3 | 王五   |
+----+--------+
2 rows in set (0.00 sec)

删除表中所有的数据：

mysql> delete from test;
Query OK, 2 rows affected (0.01 sec)

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

注意，由于删除表中所有数据是对数据进行删除，auto_increment 的自增值依然在累计：

mysql> insert into test(name) values('赵六');
Query OK, 1 row affected (0.01 sec)

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

4.2截断表（TRUNCATE）

TRUNCATE 也能做到类似于删除的效果。但与 DELETE 不同的是，TRUNCATE 是对表进行操作，不能指定部分数据进行操作，而且使用 TRUNCATE 会重置 auto_increment 自增值。

注意，TRUNCATE 使用时不需要写 FROM ：

mysql> show create table test\G;
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(10) 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> truncate test;
Query OK, 0 rows affected (0.04 sec)

mysql> insert into test(name) values('张三'),('李四'),('王五');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 李四   |
|  3 | 王五   |
+----+--------+
3 rows in set (0.00 sec)

5. 聚合函数

聚合函数是 MySQL 提供的用于对表中的数据做基本的计数、求和、求平均值、求最大值、求最小值等操作。

聚合函数	说明
COUNT([DISTINCT] expr)	返回查询到的数据的数量。
SUM([DISTINCT] expr)	返回查询到的数据之和（不是数字无意义）。
AVG([DISTINCT] expr)	返回查询到的数据的平均值（不是数字无意义）。
MAX([DISTINCT] expr)	返回查询到的数据的最大值（不是数字无意义）。
MIN([DISTINCT] expr)	返回查询到的数据的最小值（不是数字无意义）。

COUNT 对于 NULL 不会计入结果。

示例：

mysql> select * from scores;
+-----------+---------+------+---------+
| name      | chinese | math | english |
+-----------+---------+------+---------+
| 张三      |      60 |   80 |      90 |
| 李四      |      60 |   89 |      80 |
| 赵六      |      60 |   89 |      59 |
| 张小三    |      95 |   81 |      91 |
| 李小四    |      90 |  100 |      88 |
| 赵小六    |      60 |   89 |      59 |
+-----------+---------+------+---------+
6 rows in set (0.00 sec)

统计班级中有多少学生：

mysql> select COUNT(*) from scores;
+----------+
| COUNT(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

统计班中所有人语文成绩的平均值：

mysql> select AVG(chinese) from scores;
+--------------+
| AVG(chinese) |
+--------------+
|      70.8333 |
+--------------+
1 row in set (0.00 sec)

#也可以用表达式来算平均值
mysql> select SUM(chinese)/COUNT(*) from scores;
+-----------------------+
| SUM(chinese)/COUNT(*) |
+-----------------------+
|               70.8333 |
+-----------------------+
1 row in set (0.00 sec)

统计班中不同的语文成绩数：

表中的 [DISTINCT] 表示聚合函数是可以对重复数据忽略的。

mysql> select COUNT(DISTINCT chinese) from scores;
+-------------------------+
| COUNT(DISTINCT chinese) |
+-------------------------+
|                       3 |
+-------------------------+
1 row in set (0.00 sec)

6. 分组（GROUP BY）

GROUP BY 可用于对表中某些字段进行分组，再让组内的数据按照某种方式进行比较或筛选。GROUP BY 在某些情况下，会和 having 搭配使用来对结果进行筛选和过滤。

create table stuinfo(
    gender enum('男','女','其他'),
    age tinyint unsigned comment '年龄',
	height tinyint unsigned comment '身高'
	)
	
insert into stuinfo values
('男',14,160),('男',15,165),('男',15,170),('男',16,175),('男',16,180),('男',13,155),
('女',14,155),('女',14,160),('女',14,165),('女',15,160),('女',15,175),('女',16,170),
('其他',17,175),('其他',16,165);

mysql> select * from stuinfo;
+--------+------+--------+
| gender | age  | height |
+--------+------+--------+
| 男     |   14 |    160 |
| 男     |   15 |    165 |
| 男     |   15 |    170 |
| 男     |   16 |    175 |
| 男     |   16 |    180 |
| 男     |   13 |    155 |
| 女     |   14 |    155 |
| 女     |   14 |    160 |
| 女     |   14 |    165 |
| 女     |   15 |    160 |
| 女     |   15 |    175 |
| 女     |   16 |    170 |
| 其他   |   17 |    175 |
| 其他   |   16 |    165 |
+--------+------+--------+
14 rows in set (0.00 sec)

显示每种性别的平均身高和最低身高：

select gender,AVG(height),MIN(height) from stuinfo group by gender;
+--------+-------------+-------------+
| gender | AVG(height) | MIN(height) |
+--------+-------------+-------------+
| 男     |    167.5000 |         155 |
| 女     |    164.1667 |         155 |
| 其他   |    170.0000 |         165 |
+--------+-------------+-------------+
3 rows in set (0.00 sec)

显示平均身高低于165的性别及其的平均身高：

这里要使用到 having 关键字。

select gender,AVG(height) as avgheight from stuinfo group by gender having avgheight<165;
+--------+-----------+
| gender | avgheight |
+--------+-----------+
| 女     |  164.1667 |
+--------+-----------+
1 row in set (0.00 sec)

显示每种性别每个年龄的平均身高：

GROUP BY 可以对分组再分组。

select gender, age, AVG(height) as avgheight from stuinfo group by gender, age;
+--------+------+-----------+
| gender | age  | avgheight |
+--------+------+-----------+
| 男     |   14 |  160.0000 |
| 男     |   15 |  167.5000 |
| 男     |   16 |  177.5000 |
| 男     |   13 |  155.0000 |
| 女     |   14 |  160.0000 |
| 女     |   15 |  167.5000 |
| 女     |   16 |  170.0000 |
| 其他   |   17 |  175.0000 |
| 其他   |   16 |  165.0000 |
+--------+------+-----------+
9 rows in set (0.00 sec)

MySQL表的增删查改