DML、DQL、流程函数、子查询、多表查询案例

课程资料
链接：https://pan.baidu.com/s/1Mwz9zKPCroiPSZGbZ82ung
提取码：yyds

DML

注意事项:
• 插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
• 字符串和日期型数据应该包含在引号中。
• 插入的数据大小，应该在字段的规定范围内。

修改数据

C. 将所有的员工入职日期修改为 2008-01-01

 update employee set entrydate = '2008-01-01';

DQL

基本语法

SELECT
	字段列表
FROM
	表名列表
WHERE
	条件列表
GROUP BY
	分组字段列表
HAVING
	分组后条件列表
ORDER BY
	排序字段列表
LIMIT
	分页参数

我们在讲解这部分内容的时候，会将上面的完整语法进行拆分，分为以下几个部分：
- 基本查询（不带任何条件）
- 条件查询（WHERE）
- 聚合函数（count、max、min、avg、sum）
- 分组查询（group by）
- 排序查询（order by）
- 分页查询（limit）

条件查询

1). 语法

 SELECT 字段列表 FROM 表名 WHERE 条件列表 ;

2). 条件
常用的比较运算符如下:

常用的逻辑运算符如下:

案例:

I. 查询年龄等于18 或 20 或 40 的员工信息

select * from emp where age = 18 or age = 20 or age =40;
select * from emp where age in(18,20,40);

J. 查询姓名为两个字的员工信息 _ %

select * from emp where name like '__';

分组查询

1). 语法

SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后过滤条件 ];

2). where与having区别
执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组
之后对结果进行过滤。
判断条件不同：where不能对聚合函数进行判断，而having可以。

D. 统计各个工作地址上班的男性及女性员工的数量

select workaddress, gender, count(*) '数量' from emp group by gender , workaddress;

流程函数

流程函数也是很常用的一类函数，可以在SQL语句中实现条件筛选，从而提高语句的效率。

演示如下：
A. if

select if(false, 'Ok', 'Error');

B. ifnull

select ifnull('Ok','Default');
select ifnull('','Default');
select ifnull(null,'Default');

C. case when then else end

需求: 查询emp表的员工姓名和工作地址 (北京/上海 ----> 一线城市 , 其他 ----> 二线城市)

select
	name,
( case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else'二线城市' end ) as '工作地址'
from 
	emp;

案例:

create table score(
	id int comment 'ID',
	name varchar(20) comment '姓名',
	math int comment '数学',
	english int comment '英语',
	chinese int comment '语文'
) comment '学员成绩表';

insert into score(id, name, math, english, chinese) VALUES 
(1, 'Tom', 67, 88, 95),
(2, 'Rose' , 23, 66, 90),(3, 'Jack', 56, 98, 76);

具体的SQL语句如下:

select
	id,
	name,
(case when math >= 85 then '优秀' when math >=60 then '及格' else '不及格' end ) '数学',
(case when english >= 85 then '优秀' when english >=60 then '及格' else '不及格' end ) '英语',
(case when chinese >= 85 then '优秀' when chinese >=60 then '及格' else '不及格' end ) '语文'

from score;

MySQL的常见函数我们学习完了，那接下来，我们就来分析一下，在前面讲到的两个函数的案例场景，
思考一下需要用到什么样的函数来实现?
1). 数据库中，存储的是入职日期，如 2000-01-01，如何快速计算出入职天数呢？ -------->
答案: datediff
2). 数据库中，存储的是学生的分数值，如98、75，如何快速判定分数的等级呢？ ---------->
答案: case … when …

子查询

列子查询

子查询 返回的结果是一列（可以是多行），这种子查询称为列子查询。
常用的操作符：IN 、NOT IN 、 ANY 、SOME 、 ALL

案例:
A. 查询 “销售部” 和 “市场部” 的所有员工信息
分解为以下两步:
①. 查询 “销售部” 和 “市场部” 的部门ID

select id from dept where name = '销售部' or name = '市场部';

②. 根据部门ID, 查询员工信息

select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');

B. 查询比 财务部 所有人工资都高的员工信息
分解为以下两步:
①. 查询所有 财务部 人员工资

select id from dept where name = '财务部';
select salary from emp where dept_id = (select id from dept where name = '财务部');

②. 比 财务部 所有人工资都高的员工信息

select * from emp where salary > all ( select salary from emp where dept_id =
(select id from dept where name = '财务部') );

C. 查询比研发部其中任意一人工资高的员工信息
分解为以下两步:
①. 查询研发部所有人工资

select salary from emp where dept_id = (select id from dept where name = '研发部');

②. 比研发部其中任意一人工资高的员工信息

select * from emp where salary > any ( select salary from emp where dept_id =
(select id from dept where name = '研发部') );

表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询。
常用的操作符：IN
案例:
A. 查询与 “鹿杖客” , “宋远桥” 的职位和薪资相同的员工信息
分解为两步执行:
①. 查询 “鹿杖客” , “宋远桥” 的职位和薪资

select job, salary from emp where name = '鹿杖客' or name = '宋远桥';

②. 查询与 “鹿杖客” , “宋远桥” 的职位和薪资相同的员工信息

select * from emp where (job,salary) in ( select job, salary from emp where name =
'鹿杖客' or name = '宋远桥' );

B. 查询入职日期是 “2006-01-01” 之后的员工信息 , 及其部门信息
分解为两步执行:
①. 入职日期是 “2006-01-01” 之后的员工信息

select * from emp where entrydate > '2006-01-01';

②. 查询这部分员工, 对应的部门信息;

多表查询案例

数据环境准备:

create table salgrade(
	grade int,
	losal int,
	hisal int
) comment '薪资等级表';
insert into salgrade values (1,0,3000);
insert into salgrade values (2,3001,5000);
insert into salgrade values (3,5001,8000);
insert into salgrade values (4,8001,10000);
insert into salgrade values (5,10001,15000);
insert into salgrade values (6,15001,20000);
insert into salgrade values (7,20001,25000);
insert into salgrade values (8,25001,30000);

在这个案例中，我们主要运用上面所讲解的多表查询的语法，完成以下的12个需求即可，而这里主要涉
及到的表就三张：emp员工表、dept部门表、salgrade薪资等级表 。
1). 查询员工的姓名、年龄、职位、部门信息 （隐式内连接）
表: emp , dept
连接条件: emp.dept_id = dept.id

select e.name , e.age , e.job , d.name from emp e , dept d where e.dept_id = d.id;

2). 查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显式内连接）
表: emp , dept
连接条件: emp.dept_id = dept.id

select e.name , e.age , e.job , d.name from emp e inner join dept d on e.dept_id =
d.id where e.age < 30;

3). 查询拥有员工的部门ID、部门名称
表: emp , dept
连接条件: emp.dept_id = dept.id

select distinct d.id , d.name from emp e , dept d where e.dept_id = d.id;

4). 查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出
来(外连接)
表: emp , dept
连接条件: emp.dept_id = dept.id

select e.*, d.name from emp e left join dept d on e.dept_id = d.id where e.age >40 ;

5). 查询所有员工的工资等级
表: emp , salgrade
连接条件 : emp.salary >= salgrade.losal and emp.salary <= salgrade.hisal

-- 方式一
select e.* , s.grade , s.losal, s.hisal from emp e , salgrade s where e.salary >=
s.losal and e.salary <= s.hisal;
-- 方式二
select e.* , s.grade , s.losal, s.hisal from emp e , salgrade s where e.salary
between s.losal and s.hisal;

6). 查询 “研发部” 所有员工的信息及 工资等级
表: emp , salgrade , dept
连接条件 : emp.salary between salgrade.losal and salgrade.hisal ,
emp.dept_id = dept.id
查询条件 : dept.name = ‘研发部’

select e.* , s.grade from emp e , dept d , salgrade s where e.dept_id = d.id and (
e.salary between s.losal and s.hisal ) and d.name = '研发部';

7). 查询 “研发部” 员工的平均工资
表: emp , dept
连接条件 : emp.dept_id = dept.id

select avg(e.salary) from emp e, dept d where e.dept_id = d.id and d.name = '研发部';

8). 查询工资比 “灭绝” 高的员工信息。
①. 查询 “灭绝” 的薪资

select salary from emp where name = '灭绝';

②. 查询比她工资高的员工数据

select * from emp where salary > ( select salary from emp where name = '灭绝' );

9). 查询比平均薪资高的员工信息
①. 查询员工的平均薪资

select avg(salary) from emp;

②. 查询比平均薪资高的员工信息

select * from emp where salary > ( select avg(salary) from emp );

10). 查询低于本部门平均工资的员工信息
①. 查询指定部门平均薪资

select avg(e1.salary) from emp e1 where e1.dept_id = 1;
select avg(e1.salary) from emp e1 where e1.dept_id = 2;

②. 查询低于本部门平均工资的员工信息

select * from emp e2 where e2.salary < ( select avg(e1.salary) from emp e1 where
e1.dept_id = e2.dept_id );

11). 查询所有的部门信息, 并统计部门的员工人数

select d.id, d.name , ( select count(*) from emp e where e.dept_id = d.id ) '人数'
from dept d;

12). 查询所有学生的选课情况, 展示出学生名称, 学号, 课程名称
表: student , course , student_course
连接条件: student.id = student_course.studentid , course.id =
student_course.courseid

select s.name , s.no , c.name from student s , student_course sc , course c where
s.id = sc.studentid and sc.courseid = c.id ;

备注: 以上需求的实现方式可能会很多, SQL写法也有很多，只要能满足我们的需求，查询出符合条
件的记录即可。