第二章 MySQL
数据库的增删改查
第一节 DML
语句
1. 什么是DML
DML
为Data Manipulation Language,表示数据操作语言。主要体现于对表数据的增删改操作。因此DML
仅包括INSERT
、UPDATE
和DELEETE
语句。
2. INSERT语句
|
|
示例:向课程表中插入数据
|
|
3. UPDATE语句
|
|
3.1 WHERE条件子句
3.1 WHERE
条件子句
在Java
中,条件的表示通常都是使用关系运算符来表示,在SQL
语句中也是一样,使用 >, <, >=, <=, != 来表示。不同的是,除此之外,SQL
中还可以使用SQL
专用的关键字来表示条件。这些将在后面的DQL
语句中详细讲解。
在Java
中,条件之间的衔接通常都是使用逻辑运算符来表示,在SQL
语句中也是一样,但通常使用AND
来表示逻辑与(&&
),使用OR
来表示逻辑或(||
)
示例:
|
|
3.2 UPDATE语句使用
将C/Cpp
的学分更改为2,学时更改为20
|
|
4. DELETE语句
|
|
示例:删除课程表中课程编号为1的数据
|
|
5. TRUNCATE语句
|
|
示例:清空表course中的数据
|
|
6. DELETE与TRUNCATE区别
- DELETE语句根据条件删除表中数据,而TRUNCATE语句则是将表中数据全部清空;如果DELETE语句要删除表中所有数据,那么在效率上要低于TRUNCATE语句。
- 如果表中有自增长列,TRUNCATE语句会重置自增长的计数器,但DELETE语句不会。
- TRUNCATE语句执行后,数据无法恢复,而DELETE语句执行后,可以使用事务回滚进行恢复。
第二节 DQL
语句
1. 什么是DQL
DQL
全称是Data Query Language,表示数据查询语言。体现在数据的查询操作上,因此,DQL
仅包括SELECT
语句。
2. SELECT语句
|
|
注:
ALL
表示查询所有满足条件的记录,可以省略;DISTINCT
表示去掉查询结果中重复的记录AS可以给数据列、数据表取一个别名
示例:从课程表中查询课程编号小于5的课程名称,从课程表中查询Java课程的学分(score)和学时(time),从课程表中查询Java课程的学分和学时并重命名
|
|
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的课程信息
|
|
示例:从课程表查询课程名不为NULL的课程信息
|
|
示例:从课程表查询学分在2~4之间的课程信息
|
|
示例:从课程表查询课程名包含"V"的课程信息
|
|
示例:从课程表查询课程名以"J"开头的课程信息
|
|
示例:从课程表查询课程名以"p"结尾的课程信息
|
|
示例:从课程表查询课程名只有三个字符的课程信息
|
|
示例:从课程表查询课程编号为1,3,5的课程信息
|
|
4. 分组
数据表准备:新建学生表student,包含字段学号(no),类型为长整数,长度为20,是主键,自增长,非空;姓名(name),类型为字符串,长度为20,非空;性别(sex),类型为字符串,长度为2,默认值为"男";年龄(age),类型为整数,长度为3,默认值为0;成绩(score),类型为浮点数,长度为5,小数点后面保留2位有效数字
|
|
插入测试数据:
|
|
4.1 分组查询
|
|
注:分组查询所得结果只会展示组内的第一条数据
示例:从学生表查询成绩在80分以上的学生信息并按性别分组
|
|
示例:从学生表查询成绩在60~80之间的学生信息并按性别和年龄分组
|
|
4.2 聚合函数
-
COUNT():统计满足条件的数据总条数
示例:从学生表查询成绩在80分以上的学生人数
1
SELECT COUNT(*) total FROM student WHERE score > 80;
-
SUM():只能用于数值类型的字段或表达式,九三该满足条件的字段值的总和
示例:从学生表查询不及格的学生人数和总成绩
1
SELECT COUNT(*) totalCount, SUM(score) totalScore FROM student WHERE score < 60;
-
AVG():只能用于数值类型的字段或者表达式,计算该满足条件的字段值的平均值
示例:从学生表查询男生、女生、其他类型的学生的平均成绩
1
SELECT sex, AVG(score) avgScore FROM student GROUP BY sex;
-
MAX():只能用于数值类型的字段或者表达式,计算该满足条件的字段值的最大值
示例:从学生表查询学生的最大年龄
1
SELECT MAX(age) FROM student;
-
MIN():只能用于数值类型的字段或者表达式,计算该满足条件的字段值的最小值
示例:从学生表查询学生的最低分
1
SELECT MIN(score) FROM student;
4.3 分组查询结果筛选
分组后如果还需要满足其他条件,则需要使用HAVING子句来完成。
|
|
示例:从学生表查询年龄在18~22之间的学生信息并按性别分组,找出组内平均分在75分以上的组
|
|
5. 排序
|
|
注:
ORDER BY
必须位于WHERE
条件之后。
示例:从学生表查询年龄在18~30岁之间的学生信息并按成绩从高到低排列,如果成绩相同,则按年龄从小到大排列
|
|
6. 分页
|
|
LIMIT的第一个参数表示偏移量,也就是跳过的行数。 LIMIT的第二个参数表示查询返回的最大行数,可能没有给定的数量那么多行。
示例:从学生表分页查询成绩及格的学生信息,每页显示3条,查询第2页学生信息
|
|
注:如果一个查询中包含分组、排序和分页,那么它们之间必须按照分组->排序->分页的先后顺序排列。
第三节 综合练习
1. 创建员工表 emp
,包含字段员工编号(no),类型为整数,长度为20,是主键,自增长,非空;姓名(name),类型为字符串,长度为20,非空;性别(sex),类型为字符串,长度为2,默认值为"男";年龄(age),类型为整数,长度为3,非空;所属部门(dept),类型为字符串,长度为20,非空;薪资(salary),类型为浮点数,长度为10,小数点后面保留2位有效数字,非空。
|
|
2. 向员工表插入如下数据:
姓名 | 性别 | 年龄 | 部门 | 薪资 |
---|---|---|---|---|
枫阿雨 | 男 | 19 | 研发部 | 27000 |
雨阿枫 | 男 | 24 | 研发部 | 22000 |
阿枫雨 | 女 | 23 | 财务部 | 16000 |
枫雨阿 | 女 | 26 | 财务部 | 17000 |
阿雨枫 | 男 | 28 | 研发部 | 25000 |
亚烟雨 | 女 | 24 | 研发部 | 23000 |
烟亚雨 | 女 | 24 | 测试部 | 12000 |
雨亚烟 | 女 | 26 | 测试部 | 14000 |
|
|
3. 烟亚雨 因工作出色而被提升为测试主管,薪资调整为16000
|
|
4. 研发部 阿雨枫 离职
|
|
5. 从员工表中查询出平均年龄小于25的部门
|
|
6. 从员工表中统计研发部的最高薪资、最低薪资、平均薪资和总薪资
|
|
7. 从员工表中统计各个部门的员工数量
|
|
8. 从员工表中查询薪资在14000以上的员工信息并按薪资从高到低排列
|
|
9. 从员工表中分页查询员工信息,每页显示5条员工信息,按薪资从高到低排列,查询第2页员工信息
|
|