本章主要学习 DML 里的增删改操作。

1. 插入数据

1.1 实际问题

在数据库 atguigudb 中创建一个新表 emp1 ，用来插入新数据。

CREATE TABLE IF NOT EXISTS emp1(
emp_id INT,
last_name VARCHAR(25),
hire_date DATE,
salary DOUBLE(10, 2)
);
1
2
3
4
5
6

1.2 方式1：VALUES的方式添加

这种方式就是一条一条添加。也分为三种方式：

1.方式一：按照字段声明的先后顺序添加

按表的字段顺序依次写对应的数据，否则，添加失败，会报错：

INSERT INTO emp1
VALUES(1, 'Tom', '2022-7-27', 3200);
1
2

其中，第 2 行代码的最后一个字段 salary 的数据类型明明是 DOUBLE ，但却写成了整数。但其实是没有关系的，因为 MySQL 采用隐式转换自动地把整型转换成了字段要求的浮点型。

可以看到，这种方式有个弊端：就是当表的字段很多时，我们需要去查看表格的字段顺序，并逐一按字段顺序添加数据，是一件非常麻烦的事。于是，方式二就诞生了。

2.方式二：指明要添加的字段和顺序 (推荐)

INSERT INTO emp1(emp_id, hire_date, last_name, salary)
VALUES(2, '2020-9-19', 'Mike', 4000);

SELECT *
FROM emp1;
1
2
3
4
5

查询结果：

这种方式可以不需要严格按照表中字段的先后顺序添加数据，按照你声明的顺序添加。

除此之外，该方式还可以自主决定哪些字段要添加，哪些字段不添加。不添加的字段就为 NULL 。

【例子】要往表 emp1 中添加员工编号 emp_id 、姓名 last_name ，其他两个字段不添加。

INSERT INTO emp1(emp_id, last_name)
VALUES(3, 'Amy');

SELECT *
FROM emp1;
1
2
3
4
5

查询结果：

3.一条语句同时添加多个数据 (推荐)

只需要在 VALUES 后面的每条数据之间用逗号分隔即可。

INSERT INTO emp1
VALUES
(4, 'Dick', '2018-2-25', 5800),
(5, '谢自牧', '2008-7-13', 25800),
(6, '卢本伟', '2013-3-14', 3500);

SELECT *
FROM emp1;
1
2
3
4
5
6
7
8

查询结果：

1.3 方式2：将查询结果插入到表中

这种方式相当于是利用 SELECT 查询语句，批量地插入数据。其中，查询结果的字段必须要与插入表的字段一一对应，否则添加失败。

【例子】把员工表 employees 中部门号 department_id 为 60 和 70 的员工插入到表 emp1 中。

INSERT INTO emp1(last_name, emp_id, salary, hire_date)
SELECT last_name, employee_id, salary, hire_date
FROM employees
WHERE department_id IN(60, 70);

SELECT *
FROM emp1;
1
2
3
4
5
6
7

查询结果：

【精度丢失问题】

字段的数据类型长度不一致，有可能导致精度丢失的问题。

DESC emp1;
1

DESC employees;
1

本例是 employees --> emp1 ，是从小精度插入到大精度，这个是不会造成精度丢失问题的。但是如果是从大精度插入到小精度，就有可能造成精度丢失的问题。就好像从大别墅搬到小房子里，就有可能有一些家具装不下，需要丢弃一样。

2. 更新数据

更新数据是对已有的数据进行修改。使用 UPDATE 、 SET 和 WHERE 关键字。格式如下：

UPDATE 要修改的表的名称
SET 要修改的字段 = 新数据
WHERE 要修改的行
1
2
3

【注意】

使用 UPDATE 更新表时，一般都会使用 WHERE 限定要修改的行。如果不加 WHERE ，就会更新整个字段的所有行。

【例子1】把表 emp1 中员工编号为 3 的员工的入职日期修改为 2014-9-1 ，工资 salary 修改为 8000 元。

UPDATE emp1
SET hire_date = '2014-9-1',
salary = 8000
WHERE emp_id = 3;

SELECT *
FROM emp1;
1
2
3
4
5
6
7

查询结果：

【例子2】姓名中包含字符 ‘a’ 的员工提薪 20% 。

UPDATE emp1
SET salary = salary * 1.2
WHERE last_name LIKE '%a%';
1
2
3

【注意】

更新数据是有可能失败的，原因多种多样，可能是由于约束影响的。等到讲约束这一章就明白了。

3. 删除数据

删除数据使用关键字 DELETE … FROM … WHERE …。如果不加限定条件 WHERE，那么就会删除整张表的所有数据。

DELETE FROM 表名
WHERE 要删除的行
1
2

【例子】删除表 emp1 中员工编号 emp_id 为 1 的员工数据。

DELETE FROM emp1
WHERE emp_id = 1;

SELECT *
FROM emp1;
1
2
3
4
5

查询结果：

【注意】

删除数据也可能会由于约束条件删除失败。

3.1 小结

DML 操作默认都是自动提交数据 commit 。如果希望执行完后，不自动提交 commit，则需要使用：

SET autocommit = FALSE;
1

4. MySQL8新特性：计算列

计算列是在创建表时，定义字段时，可以指明该字段的数据是通过其他字段计算来的。这类似于 Excel 中的表格函数的作用。

例如，字段 a = 10，字段 b = 20，定义字段 c = a + b，那么字段 c 就会自动计算为 30 。并且当字段 a 和 b 发生变化时，字段 c 也会自动同步发生变化。

① 首先创建一张新表，字段 c 是计算列的格式：

CREATE TABLE test1(
a INT,
b INT,
c INT GENERATED ALWAYS AS (a + b) VIRTUAL
);
1
2
3
4
5

② 插入数据，字段 a = 10，字段 b = 20，字段 c 就会自动计算出数据：

INSERT INTO test1(a, b)
VALUES (10, 20);

SELECT *
FROM test1;
1
2
3
4
5

查询结果：

③ 修改字段 a 的值为 100 ，字段 c 的值也会自动发生变化：

UPDATE test1
SET a = 100;

SELECT *
FROM test1;
1
2
3
4
5

查询结果：

5. 综合案例

1、创建数据库 test01_library 。

CREATE DATABASE IF NOT EXISTS test01_library CHARACTER SET 'utf8';

SHOW DATABASES;
1
2
3

查询结果：

2、创建表 books，表结构如下：

CREATE TABLE IF NOT EXISTS books(
book_id INT,
book_name VARCHAR(50),
book_authors VARCHAR(100),
price FLOAT,
pubdate YEAR,
note VARCHAR(100),
num INT
);

DESC books;
1
2
3
4
5
6
7
8
9
10
11

查询结果：

3、向 books 表中插入记录：

​ 1）不指定字段名称，插入第一条记录

INSERT INTO books
VALUES(1, 'Tal of AAA', 'Dickes', 23, '1995', 'novel', 11);

SELECT *
FROM books;
1
2
3
4
5

查询结果：

​ 2）指定所有字段名称，插入第二记录

INSERT INTO books(book_id, book_name, book_authors, price, pubdate, note, num)
VALUES(2, 'EmmaT', 'Jane Iura', 35, '1993', 'joke', 22);

SELECT *
FROM books;
1
2
3
4
5

查询结果：

​ 3）同时插入多条记录（剩下的所有记录）

INSERT INTO books
VALUES
(3, 'Story if Jane', 'Jane Tim', 40, '2001', 'novel', 0),
(4, 'Lovey Day', 'George Byron', 20, '2005', 'novel', 30),
(5, 'Old Land', 'Honore Blade', 30, '2010', 'law', 0),
(6, 'The Battle', 'Upton Sara', 30, '1999', 'medicine', 40),
(7, 'Rose Hood', 'Richard Haggard', 28, '2008', 'cartoon', 28);

SELECT *
FROM books;
1
2
3
4
5
6
7
8
9
10

查询结果：

4、将小说类型 (novel) 的书的价格都增加5。

UPDATE books
SET price = price + 5
WHERE note = 'novel';

SELECT *
FROM books;
1
2
3
4
5
6

查询结果：

5、将名称为 EmmaT 的书的价格改为40，并将说明改为 drama 。

UPDATE books
SET price = 40, note = 'drama'
WHERE book_name = 'EmmaT';
1
2
3

查询结果：

6、删除库存为 0 的记录。

DELETE FROM books
WHERE num = 0;
1
2

查询结果：

7、统计书名中包含 a 字母的书。

SELECT COUNT(*)
FROM books
WHERE book_name LIKE '%a%';
1
2
3

查询结果：

8、统计书名中包含a字母的书的数量和库存总量。

SELECT COUNT(*), SUM(num)
FROM books
WHERE book_name LIKE '%a%';
1
2
3

查询结果：

9、找出 “novel” 类型的书，按照价格降序排列。

SELECT *
FROM books
WHERE note = 'novel'
ORDER BY price DESC;
1
2
3
4

查询结果：

10、查询图书信息，按照库存量降序排列，如果库存量相同的按照note升序排列。

SELECT *
FROM books
ORDER BY num DESC, note ASC;
1
2
3

查询结果：

11、按照note分类统计书的数量。

SELECT note, COUNT(*)
FROM books
GROUP BY note;
1
2
3

查询结果：

12、按照note分类统计书的库存量，显示库存量超过30本的。

SELECT note, SUM(num)
FROM books
GROUP BY note
HAVING SUM(num) > 30;
1
2
3
4

查询结果：

13、查询所有图书，每页显示5本，显示第二页。

SELECT *
FROM books
LIMIT 5, 5;
1
2
3

查询结果：

14、按照note分类统计书的库存量，显示库存量最多的。

# 方式一：显示分页
SELECT note, SUM(num) sum_num
FROM books
GROUP BY note
ORDER BY sum_num DESC
LIMIT 0, 1;

# 方式二：子查询
SELECT note, MAX(sum_num)
FROM (
        SELECT note, SUM(num) sum_num
        FROM books
        GROUP BY note) t_num;
1
2
3
4
5
6
7
8
9
10
11
12
13

查询结果：

15、查询书名达到 9 个字符的书，不包括里面的空格。

SELECT book_name
FROM books
WHERE CHAR_LENGTH(REPLACE(book_name, ' ', '')) >= 9;
1
2
3

查询结果：

这题的去除字符串中间的空格我不会，没想到是用字符串函数 REPLACE(str, a, b) 来实现把字符串中的空格 ' ' 替换成 '' ，从而去掉了所有字符串中的空格。

16、查询书名和类型，其中 note 值为 novel 显示小说，law 显示法律，medicine 显示医药，cartoon 显示卡通，joke 显示笑话，drama 显示戏剧。

SELECT book_name, note,
CASE note WHEN 'novel' THEN '小说'
          WHEN 'law' THEN '法律'
          WHEN 'medicine' THEN '医药'
          WHEN 'cartoon' THEN '卡通'
          WHEN 'drama' THEN '戏剧'
          ELSE '笑话'
          END AS '类别'
FROM books;	
1
2
3
4
5
6
7
8
9

查询结果：

17、查询书名、库存，其中num值超过30本的，显示滞销，大于0并低于10的，显示畅销，为0的显示需要无货。

SELECT book_name, num, 
CASE WHEN num > 30 THEN '滞销'
	 WHEN num > 0 AND num < 10 THEN '畅销'
	 WHEN num = 0 THEN '无货'
	 ELSE '正常'
	 END AS sell_status
FROM books;
1
2
3
4
5
6
7

查询结果：

18、统计每一种note的库存量，并合计总量。

SELECT IFNULL(note, '库存合计总量') AS note, SUM(num)
FROM books
GROUP BY note WITH ROLLUP;
1
2
3

查询结果：

19、统计每一种note的数量，并合计总量。

SELECT IFNULL(note, '合计总量') AS note, COUNT(*)
FROM books
GROUP BY note WITH ROLLUP;
1
2
3

查询结果：

20、统计库存量前三名的图书。

SELECT book_name, num
FROM books
ORDER BY num DESC
LIMIT 0, 3;
1
2
3
4

查询结果：

21、找出最早出版的一本书。

SELECT *
FROM books
ORDER BY pubdate ASC
LIMIT 0, 1;
1
2
3
4

查询结果：

22、找出novel中价格最高的一本书。

SELECT *
FROM books
WHERE note = 'novel'
ORDER BY price DESC
LIMIT 0, 1;
1
2
3
4
5

查询结果：

23、找出书名中字数最多的一本书，不含空格。

SELECT *
FROM books
ORDER BY CHAR_LENGTH(REPLACE(book_name, ' ', '')) DESC
LIMIT 0, 1;
1
2
3
4

查询结果：