MySQL-表的增删改查操作（DML）

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

前言

之前我们学习了对数据库和表的DDL操作，今天我们来学习对表的增删改查操作（DML）。

---

以下是本篇文章正文内容，下面案例可供参考

一、表的增删改查

表的增删改查我们也称为CRUD : Create(创建), Retrieve(读取)，Update(更新)，Delete（删除）。

二、使用步骤

1.insert

语法：
INSERT [INTO] table_name
[(column [, column] ...)]
VALUES (value_list) [, (value_list)] ...
value_list: value, [, value] …

单行数据 + 全列插入

mysql> CREATE TABLE students (
-> id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> sn INT NOT NULL UNIQUE COMMENT ‘学号’,
-> name VARCHAR(20) NOT NULL,
-> qq VARCHAR(20)
-> );

insert into students values(1,1001,'张三','1050');

多行数据 + 指定列插入

mysql> insert into students (sn,name,qq) values (1002,'李四','1051'),
(1003,'王五','1052'),
(1004,'赵六','1053');

插入否则更新

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

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

如果我们现在想插入insert into students values(1,1005,'李华','1054');，会由于主键id冲突导致插入失败，那么如果我们就是想插入并更新怎么办呢？

insert into students values(1,1005,'李华','1054') on duplicate key update sn=1005,name='李华',qq='1054';

替换

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

目前表中数据有

现在我想将sn为1002的李华改为刘备，就可以使用
replace into students (sn,name) values (1002,'刘备');

这时候会发现id为2的李华被删除了，取而代之的是id为6的刘备，至于为什么是6，那是因为我们的主键id设置了AUTO_INCREMENT，因为replace是“主键 或者 唯一键 如果冲突，则删除后再插入.”

2.select

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

以下面这张表为例

mysql> 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 ‘英语成绩’
-> );
Query OK, 0 rows affected (0.04 sec)

++++++++++++++++++++++++++++++++++++++++++++++++++++++++
mysql> 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);

全列查询

SELECT * FROM exam_result;

指定列查询

NULL 的查询

查询NULL，我们不用=，我们用is或者<=>。
示例表结构

mysql> create table students(
-> id int unsigned primary key auto_increment,
-> sn int unsigned unique key,
-> name varchar(30),
-> qq varchar(30)
-> );
Query OK, 0 rows affected (0.07 sec)
++++++++++++++++++++++++++++++++++++++++++++++++++
mysql> insert into students (sn,name,qq) values (1001,‘唐僧’,NULL),(1002,'.孙悟空,‘1234’),(1003,‘孙权’,null),(1004,‘曹操’,null);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

查询字段为表达式

如果你觉得显示表达式不够美观，还可以为它取别名

结果去重

WHERE 条件

英语不及格的同学及英语成绩 ( < 60 )

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

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

姓孙的同学 及 孙某同学

模糊搜索 like

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

总分在 200 分以下的同学

这里需要注意的是，由于sql语句的执行顺序，别名不能在where处使用，sql语句执行顺序如下：

1. from exam_result
2. where chinese+english+math < 200
3. select name chinese ,english, math, chinese+english+math as total

因为执行顺序原因，where不能使用别名。

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

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

结果排序

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

同学及数学成绩，按数学成绩升序显示

查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示（多字段排序）

查询同学及总分，由高到低

为什么这里使用order by 是可以用别名的？ 还是排序的原因，sql语句执行顺序如下：

1. from exam_result
2. select name,chinese,math,english,english+math+chinese as total
3. order by total desc

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

筛选分页结果

语法：

– 起始下标为 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;

比如说我们只想查看整个表的前两行数据。
select * from exam_result limit 2 offset 0; or select * from exam_result limit 0,2;

这种分页是可以配合where以及order来使用的。

3.Update

语法：

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

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

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

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

update exam_result set math=math+30 order by english+math+chinese asc limit 3;

将所有同学的语文成绩更新为原来的 2 倍

Delete

语法：

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

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

删除整张表数据

delete from exam_result;

只会删除表的内容，不会删除整个表。

截断表

语法：

TRUNCATE [TABLE] table_name

它与delete有一定区别

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

插入查询结果

如果我们要对一个表的内容进行去重应该怎么办？

先创建两个表：

1. CREATE TABLE duplicate_table (id int, name varchar(20));

2. INSERT INTO duplicate_table VALUES(100, 'aaa'),(100, 'aaa'), (200, 'bbb'), (200, 'bbb'), (200, 'bbb'), (300, 'ccc');

3. CREATE TABLE no_duplicate_table LIKE duplicate_table;

4. insert into no_duplicate_table select distinct * from duplicate_table;
   

聚合函数

统计班级共有多少同学

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

它会忽略NULL。

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

统计数学成绩总分

统计平均总分

返回英语最高分

为什么这样会报错呢？ 这是因为聚合函数的问题，这里的name无法聚合。

返回 > 80 分以上的数学最低分

group by子句的使用

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

现在有这么三个表

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

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

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

这里的having就像是group的where，将组也给筛选。

---