第二章 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;
注:如果一个查询中包含分组、排序和分页,那么它们之间必须按照分组->排序->分页的先后顺序排列。