第二章 `MySQL`数据库的增删改查

第一节 `DML`语句

1. 什么是`DML`

DML为Data Manipulation Language，表示数据操作语言。主要体现于对表数据的增删改操作。因此DML仅包括INSERT、UPDATE和DELEETE语句。

2. INSERT语句

-- 需要注意，VALUES后的字段值必须与表名后的字段名一一对应
INSERT INTO 表名(字段名1, 字段名2, ..., 字段名n) VALUES(字段值1, 字段值2, ..., 字段值n);
-- 需要注意，VALUES后的字段值必须与创建表时的字段顺序保持一一对应
INSERT INTO 表名 VALUES(字段值1, 字段值2, ..., 字段值n);
-- 一次性插入多条数据
INSERT INTO 表名(字段名1, 字段名2, ..., 字段名n) VALUES(字段值1, 字段值2, ..., 字段值n),(字段值1, 字段值2, ..., 字段值n), ... , (字段值1, 字段值2, ..., 字段值n);
INSERT INTO 表名 VALUES(字段值1, 字段值2, ..., 字段值n), (字段值1, 字段值2, ..., 字段值n), ..., (字段值1, 字段值2, ..., 字段值n);

示例：向课程表中插入数据

INSERT INTO course (`number`, name, score, `time`) VALUES (1, 'Java', 8, 80);
INSERT INTO course VALUE (2, 'Golang', 6, 60);
INSERT INTO course (`number`, score, name, `time`) VALUES (3, 5, 'Python', 50);
INSERT INTO course (`number`, name, score, `time`) VALUES (4, 'C/Cpp', 3, 30),(5, 'Spring', 4, 40);
INSERT INTO course VALUE (6, 'SpringMVC', 4 ,40),(7, 'SpringBoot', 5, 50);

3. UPDATE语句

UPDATE 表名 SET 字段名1=字段值1[,字段名2=字段值2, ..., 字段名n=字段值n] [WHERE 修改条件];

3.1 WHERE条件子句

3.1 WHERE条件子句
在Java中，条件的表示通常都是使用关系运算符来表示，在SQL语句中也是一样，使用 >, <, >=, <=, != 来表示。不同的是，除此之外，SQL中还可以使用SQL专用的关键字来表示条件。这些将在后面的DQL语句中详细讲解。
在Java中，条件之间的衔接通常都是使用逻辑运算符来表示，在SQL语句中也是一样，但通常使用AND来表示逻辑与(&&)，使用OR来表示逻辑或(||)
示例：

WHERE time > 20 AND time < 60; 
-- 等价于
WHERE time > 20 && time < 60;

3.2 UPDATE语句使用

将C/Cpp的学分更改为2，学时更改为20

UPDATE course SET score=2, `time`=20 WHERE name='C/Cpp';

4. DELETE语句

DELETE FROM 表名 [WHERE 删除条件];

示例：删除课程表中课程编号为1的数据

DELETE FROM course WHERE 'number'=1;

5. TRUNCATE语句

-- 清空表中的数据
TRUNCATE [TABLE] 表名;

示例：清空表course中的数据

TRUNCATE course;

6. DELETE与TRUNCATE区别

DELETE语句根据条件删除表中数据，而TRUNCATE语句则是将表中数据全部清空；如果DELETE语句要删除表中所有数据，那么在效率上要低于TRUNCATE语句。
如果表中有自增长列，TRUNCATE语句会重置自增长的计数器，但DELETE语句不会。
TRUNCATE语句执行后，数据无法恢复，而DELETE语句执行后，可以使用事务回滚进行恢复。

第二节 `DQL`语句

1. 什么是`DQL`

DQL全称是Data Query Language，表示数据查询语言。体现在数据的查询操作上，因此，DQL仅包括SELECT语句。

2. SELECT语句

SELECT ALL/DISTINCT * | 字段名1 AS 别名1[,字段名1 AS 别名1, ..., 字段名n AS 别名n] FROM 表名 WHERE 查询条件

注：ALL表示查询所有满足条件的记录，可以省略；DISTINCT表示去掉查询结果中重复的记录AS可以给数据列、数据表取一个别名

示例：从课程表中查询课程编号小于5的课程名称，从课程表中查询Java课程的学分(score)和学时(time)，从课程表中查询Java课程的学分和学时并重命名

SELECT name FROM course WHERE `number` < 5;
SELECT score, `time` FROM course name='Java';
SELECT score AS '学分', `time` AS '学时' FROM course name='Java';
-- AS可以省略
SELECT score '学分', `time` '学时' FROM course name='Python';
-- 给表起别名
SELECT c.name, c.score, c.time FROM course c WHERE c.name='Java';
-- 给表起别名的同时给字段重命名
SELECT c.name '课程名称', c.score '学分', c.time '学时' FROM course c WHERE c.name='Python';

3. 比较操作符

操作符	语法	说明
IS NULL	字段名 IS NULL	如果字段的值为NULL，则条件满足
IS NOT NULL	字段名 IS NOT NULL	如果字段的值不为NULL，则条件满足
BETWEEN ... AND ...	字段名 BETWEEN 最小值 AND 最大值	如果字段的值在最小值与最大值之间（能够取到最小值和最大值），则条件满足
LIKE	字段名 LIKE '%匹配内容%'	如果字段值包含有匹配内容，则条件满足
IN	字段名 IN(值1，值2，...，值n)	如果字段值在值1,值2, ...，值n中，则条件满足

示例：从课程表查询课程名为NULL的课程信息

SELECT * FROM course WHERE name IS NULL;

示例：从课程表查询课程名不为NULL的课程信息

SELECT * FROM coures WHERE name IS NOT NULL;

示例：从课程表查询学分在2~4之间的课程信息

SELECT * FROM course WHERE score BETWEEN 2 AND 4;
-- 等价于
SELECT * FROM course WHERE score >= 2 ANDE score <= 4;

示例：从课程表查询课程名包含"V"的课程信息

SELECT * FROM course WHERE name LIKE '%V%';

示例：从课程表查询课程名以"J"开头的课程信息

SELECT * FROM course WHERE name LIKE 'J%';

示例：从课程表查询课程名以"p"结尾的课程信息

SELECT * FROM course WHERE name LIKE '%p';

示例：从课程表查询课程名只有三个字符的课程信息

SELECT * FROM course WHERE name LIKE '___';

示例：从课程表查询课程编号为1,3,5的课程信息

SELECT * FROM course WHERE `number` IN (1, 3, 5);

4. 分组

数据表准备：新建学生表student，包含字段学号（no），类型为长整数，长度为20，是主键，自增长，非空；姓名（name），类型为字符串，长度为20，非空；性别（sex），类型为字符串，长度为2，默认值为"男"；年龄（age），类型为整数，长度为3，默认值为0；成绩（score），类型为浮点数，长度为5，小数点后面保留2位有效数字

CREATE TABLE IF NOT EXISTS student(
    `no` BIGINT(20) AUTO_INCREMENT NOT NULL PRIMARY KEY COMMENT '学号',
    name VARCHAR(20) NOT NULL COMMENT '姓名',
    sex VARCHAR(2) DEFAULT '男' COMMENT '性别',
    age VARCHAR(3) INT(3) DEFAULT 0 COMMENT '年龄',
    score DOUBLE(5, 2) COMMENT '成绩'
)ENGINE=InnoDB CHARSET=UTF8 COMMENT '学生表'

插入测试数据：

INSERT INTO student(no, name, sex, age, score) VALUES (DEFAULT, '枫阿雨', '男', 19, 89);
INSERT INTO student(no, name, sex, age, score) VALUES (DEFAULT, '雨阿枫', '男' 19, 90);
INSERT INTO student(no, name, sex, age, score) VALUES (DEFAULT, '阿枫雨', '男', 19, 62);
INSERT INTO student(no, name, sex, age, score) VALUES (DEFAULT, '枫雨阿', '男', 22, 75);
INSERT INTO student(no, name, sex, age, score) VALUES (DEFAULT, '雨枫阿', '女', 18, 59);
INSERT INTO student(no, name, sex, age, score) VALUES (DEFAULT, '阿雨枫', '其他', 27, 88);
INSERT INTO student(no, name, sex, age, score) VALUES (DEFAULT, '亚烟雨', '男', 19, 88);
INSERT INTO student(no, name, sex, age, score) VALUES (DEFAULT, '烟亚雨', '女', 28, 81);
INSERT INTO student(no, name, sex, age, score) VALUES (DEFAULT, '雨亚烟', '其他', 32, 62);

4.1 分组查询

SELECT ALL/DISTINCT * | 字段名1 AS 别名1[,字段名1 AS 别名1, ..., 字段名n AS 别名n] FROM 表名 WHERE 查询条件 GROUP BY 字段名1, 字段名2, ...,字段名n;

注：分组查询所得结果只会展示组内的第一条数据

示例：从学生表查询成绩在80分以上的学生信息并按性别分组

SELECT * FROM student WHERE score > 80 GROUP BY sex;

示例：从学生表查询成绩在60~80之间的学生信息并按性别和年龄分组

SELECT * FROM student WHERE score >= 60 AND score <= 80 GROUP BY sex, age;

4.2 聚合函数

COUNT()：统计满足条件的数据总条数

示例：从学生表查询成绩在80分以上的学生人数
```
SELECT COUNT(*) total FROM student WHERE score > 80;
```
SUM()：只能用于数值类型的字段或表达式，九三该满足条件的字段值的总和

示例：从学生表查询不及格的学生人数和总成绩
```
SELECT COUNT(*) totalCount, SUM(score) totalScore FROM student WHERE score < 60;  
```
AVG()：只能用于数值类型的字段或者表达式，计算该满足条件的字段值的平均值

示例：从学生表查询男生、女生、其他类型的学生的平均成绩
```
SELECT sex, AVG(score) avgScore FROM student GROUP BY sex; 
```
MAX()：只能用于数值类型的字段或者表达式，计算该满足条件的字段值的最大值

示例：从学生表查询学生的最大年龄
```
SELECT MAX(age) FROM student;
```
MIN()：只能用于数值类型的字段或者表达式，计算该满足条件的字段值的最小值

示例：从学生表查询学生的最低分
```
SELECT MIN(score) FROM student;
```

4.3 分组查询结果筛选

分组后如果还需要满足其他条件，则需要使用HAVING子句来完成。

SELECT ALL/DISTINCT * | 字段名1 AS 别名1[,字段名1 AS 别名1, ..., 字段名n AS 别名n] FROM 表名 WHERE 查询条件 GROUP BY 字段名1, 字段名2, ...,字段名n HAVING 筛选条件;

示例：从学生表查询年龄在18~22之间的学生信息并按性别分组，找出组内平均分在75分以上的组

SELECT * FROM student WHERE age BETWEEN 18 AND 22 GROUP BY sex HAVING AVG(score) > 75;

5. 排序

SELECT ALL/DISTINCT * | 字段名1 AS 别名1[,字段名1 AS 别名1, ..., 字段名n AS 别名n] FROM 表名 WHERE 查询条件 ORDER BY 字段名1 ASC|DESC，字段名2 ASC|DESC,..., 字段名n ASC|DESC;
-- DESC : 降序排序
-- ASC : 升序排序

注：ORDER BY必须位于WHERE 条件之后。

示例：从学生表查询年龄在18~30岁之间的学生信息并按成绩从高到低排列，如果成绩相同，则按年龄从小到大排列

SELECT * FROM student WHERE age BETWEEN 18 AND 30 ORDER BY score DESC, age ASC;

6. 分页

SELECT ALL/DISTINCT * | 字段名1 AS 别名1[,字段名1 AS 别名1, ..., 字段名n AS 别名n] FROM 表名 WHERE 查询条件 LIMIT 偏移量, 查询条数

LIMIT的第一个参数表示偏移量，也就是跳过的行数。
LIMIT的第二个参数表示查询返回的最大行数，可能没有给定的数量那么多行。

示例：从学生表分页查询成绩及格的学生信息，每页显示3条，查询第2页学生信息

SELECT * FROM student WHERE score >= 60 LIMIT 3, 3;

注：如果一个查询中包含分组、排序和分页，那么它们之间必须按照分组->排序->分页的先后顺序排列。

一	二	三	四	五	六	日
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30

第二章 MySQL数据库的增删改查

第一节 DML语句

1. 什么是DML