MySQL
DQL 多表数据查询
连接查询、子查询与联合查询
测试数据
create table dep(
id int primary key auto_increment,
name varchar(16),
notes varchar(64)
);
create table emp(
id int primary key auto_increment,
name varchar(16) not null,
gender enum('male', 'female') not null,
age int not null,
salary float(10, 2),
post varchar(16),
join_date date,
leader_id int,
dep_id int
);
-- leader_id 表内数据关联;
-- dep_id 第二张表的数据关联,但是没有设置外键
-- prompt: 根据表结构生成数据用于练习多表联查场景,leader_id emp 表内自洽; dep_id 依赖 dep 表;笛卡尔积
select * from emp, dep;默认情况下使用上诉语句是不会出现任何错误而且数据也都够被查询出来;
假设 emp 和 dep 中各存在 5 条数据;那么就会被查询出 25 条数据;这种情况就被称之为笛卡尔积。
这是一种错误的情况,查询出来的数据没有导致数据膨胀并且没有丝毫关联;
连接查询 - 内连接
查询两张表的交集部分
select <字段> from <表 a> [inner] join <表 b> on <条件>
-- 查询, inner 是可选的,默认就是 inner
select * from emp join dep on emp.dep_id = dep.id;连接查询 - 外连接
查询左表或者右表的全部数据,包括交集部分
select <字段> from <表 a> [left | right] [outer] join on <条件>;-- 查询所有员工信息和部门信息
select emp.name, dep.name from emp left join dep on emp.dep_id = dep.id;
-- 查询所有员工数据以及对应的部分数据, 可以通过 as 给表执行别名
select e.name, d.name from emp as e right join dep as d on e.dep_id = d.id;连接查询 - 自连接
自己连接自己,将数据看成两张表,不要当做一张表,同时也可以使用左右外连接和内连接。
select <字段> from <表 a> as a join <表 a> as b on <条件>;-- 查询员工和对应的领导名字
select a.name as '员工', a.post as '职位', b.name as '领导' from emp as a join emp as b on a.leader_id = b.id;联合查询
把多次的查询结果合并到一起。union、union all
-- 查询年龄大于 45 以及 薪资大于 1.5w 的员工
select * from emp where age >= 45
union all -- 拼接两次的查询结果 all 会把相同的数据也查出来
select * from emp where salary >= 15000;
-- 在多表的使用场下要求查出来的字段个数要相同,不然会出现错误
-- 假设 a 表 4 个字段; b 表 3 个字段;
-- a 表的 select 后最多只能查 3 个字段;不能无脑写个 *子查询
嵌套查询; 可以在 select、from、where 之后;
- 标量子查询:子查询的结果是一个值,操作运算符是比较运算符
-- 查询技术所有员工信息
-- 1. 查询技术部 id
-- 2. 查询 id 为技术部的所有员工信息
-- 将 dep 查询的数据作为子查询条件
select * from emp where dep_id = (select id from dep where name = '技术部');
-- 查询薪资比黄忠高的员工信息
-- 1. 查询黄忠的薪资
-- 2. 查询比黄忠高的薪资
select * from emp where salary > (select salary from emp where name = '黄忠');- 列子查询:子查询的结果是一列,操作符包含
in 、not int、any(任一满足)、some(任一满足)、all(所有满足)
-- 查询人事部和财务部的员工
-- 1. 查询人事部和财务部的 id
-- 2. 查询两个 id 的所有的员工
select * from emp where dep_id in (select id from dep where name = '人事部' or name = '财务部');
-- 比市场部所有人入职都晚的员工信息
-- 查询市场部的 id
-- 查询市场部的所有的人的入职日期
-- 对比数据
select * from emp
where join_date > all(select join_date from emp where dep_id = (select id from dep where name = '市场部'));
-- 也可以不适用 all 而是通过 max 获取最大的入职日期直接进行对比,就变成了标量子查询
select * from emp
where join_date > (select max(join_date) from emp where dep_id = (select id from dep where name = '市场部'));- 行子查询:子查询的结果是一行, 操作符包含
=、!=、in、 not in
-- 查询和关羽薪资相同并且领导也想同的员工信息
-- 1. 查询关羽的薪资和领导
-- 2. 查询其他员工信息
select * from emp where
-- 把括号内的字段看做一个元素
(salary, leader_id) = (select salary, leader_id from emp where name = '关羽');- 表子查询:子查询的结果是多行多列,操作符包含
in、not in
如果将行子查询的例子在进行扩充即是表的子查询
-- 查询和关羽、赵云薪资相同并且领导也想同的员工信息
select * from emp where
(salary, leader_id) in (select salary, leader_id from emp where name = '关羽' or name = '赵云');
-- 查询工资为 8000 的员工信息以及部门信息
-- 1. 查询 8000 的员工信息
-- 2. 查询这部分员工的部门信息
-- 工资为 8k 的信息作为临时表和部门表进行关联
select e.*, dep.name from (select * from emp where salary = 8000) as e join dep on dep.id = e.dep_id;
-- 其实也可以直接连表查
select emp.*, dep.name from emp join dep on emp.dep_id = dep.id where emp.salary = 8000;习题
增加职位表数据
create table job_grade(
id int primary key auto_increment,
grade varchar(16),
min_salary int,
max_salary int
);
-- 通过工资水平划分等级,在 emp 表中没有字段强关联
insert into job_grade(grade, min_salary, max_salary) values
('p1', 0, 4999),
('p2', 5000, 9999),
('p3', 10000, 14999),
('p4', 15000, 19999),
('p5', 20000, 29999),
('p6', 30000, 39999);查询所有员工的职位等级
-- 由于 emp 和 job_grade 之间没有关联数据
-- 需要考虑关联的条件是 emp 的 salary 必须在 job_grade 的最大和最小工资之间
select
emp.name,
emp.salary,
job.grade
from
emp
left join job_grade as job on emp.salary >= job.min_salary
and emp.salary <= job.max_salary;查询人事部所有员工信息,以及他们的职级
-- 需要三张表的连接
select
emp.name,
emp.salary,
job.grade
from
emp
left join job_grade as job on emp.salary between job.min_salary and job.max_salary
left join dep on emp.dep_id = dep.id
where
dep.name = '人事部';查询薪资低于每个部分平均工资的员工
-- 1. 查询每个部门员工的平均工资
select avg(salary) from emp where dep_id = 3;
-- 2. 查询低于这个平均工资的人, 把第一步当做子查询,但是 id 要动态获取
select
a.name,
a.salary,
-- 将每个部分的平均工资显示出来
(select avg(salary) from emp where dep_id = a.dep_id) as 'avg_salary'
from emp as a where salary < (select avg(salary) from emp where dep_id = a.dep_id);查询每个部门的员工数量
-- 通过连表,再根据部门进行分组,通过 count 统计
select dep.name, count(emp.id) from emp left join dep on emp.dep_id = dep.id group by dep.id;