MySQL多表查询

MySQL多表查询

多表查询分类：

• 连接查询
  • 内连接：相当于查询A，B两张表的交集
  • 外连接：
    • 左外连接：查询A表的所有数据，以及A，B两表交集的数据
    • 右外连接：查询B表的所有数据，以及A，B两表交集的数据
    • 自连接：当表与自身的连接查询，自连接必须使用表别名
• 子查询

内连接

• 隐式内连接
  • select 字段列表 from 表1，表2 where 条件；
• 显式内连接
  • select 字段列表 from 表1 [inner] join 表2 on 连接条件；

-- 隐式内连接
mysql> select e.name,d.name from emp e,dept d where e.dept_id = d.id;

-- 显式内连接
mysql> select e.name,d.name from emp e inner join dept d on e.dept_id = d.id;
mysql> select e.name,d.name from emp e join dept d on e.dept_id = d.id;

外连接

• 左外连接
  • select 字段列表 from 表1 left [outer] join 表2 on 连接条件；

-- 查询emp表的所有数据，和相对应的部门信息
mysql> select e.*,d.name from emp e left join dept d on e.dept_id = d.id; 

• 右外连接
  • select 字段列表 from 表1 right [outer] join 表2 on 连接条件；

-- 查询dept表的所有数据，和对应的员工信息
mysql> select d.*,e.* from emp e right join dept d on e.dept_id = d.id;

自连接

• 自连接：当表与自身的连接查询，自连接必须使用表别名

-- 查询员工姓名，以及直属领导的名字
mysql> select a.name,b.name from emp a,emp b where a.manageid = b.id;

-- 查询emp表员工姓名，以及直属领导名字，如果员工没有领导也需查询出来
mysql> select a.name '员工',b.name '领导' from emp a left join emp b on a.manageid = b.id;

联合查询

• union all 和 union 区别：不加all会将合并查询结果去重
• select 字段列表 from biao1...... union [all] select 字段列表 from biao1......;
  • 注意：多张表联合查询字段列数必须相同

-- 查询薪资低于9000的员工，和年龄大于30岁的员工全部查询出来
mysql> select * from emp e where e.salary<9000  union select * from emp e where e.age > 30; 

子查询（嵌套查询）

标量子查询

• select * from 表1 where column1 = (select column1 from 表2)
  • 子查询外部的语句可以是insert/update/delete/select的任何一个
  • 子查询位置分为：where之后，from之后，select之后

-- 查询研发部所有员工信息
	a: 查询研发部id
	select id from dept where name='研发部';
	b: 根据研发部id，查询员工信息
	select * from emp where dept_id=2;
	
mysql> select * from emp where dept_id=(select id from dept where name='研发部');

-- 查询在张三入职之后的员工
	a: 查询张三的入职时间
	select entrydate from emp where name='张三';
	b: 和张三的入职时间进行比较，查询在张三入职之后的员工
	select * from emp where entrydate>'2022-01-15';
	
mysql> select * from emp where entrydate>(select entrydate from emp where name='张三');

列子查询

• 子查询返回的结果是一列（可以是多行），这种子查询成为列子查询
• 常用操作符：
  • IN：在指定集合范围之内，多选一
  • NOT IN：不在指定集合范围之内
  • ALL：子查询返回列表的所有值都必须满足
  • ANY：子查询返回列表中，有任意一个满足即可
  • SOME：与ANY等同，能使用ANY的地方都可以用SOME

-- 查询 研发部 和 市场部 所有员工信息
mysql> select * from emp where dept_Id IN ( select id from dept where  name='研发部' or name='市场部' );

-- 查询比市场部所有员工工资都高的员工信息
mysql> select * from emp where salary> ALL (select salary from emp where dept_id=(select id from dept where name='市场部'));

-- 查询比研发部其中任意一人工资高的员工
mysql> select * from emp where salary > SOME (select salary from emp where dept_id=(select id from dept where name='研发部'));

行子查询

• 子查询返回的一行（可以是多列）

-- 查询与张三的薪资及直属领导相同的员工信息
	a：查询张三的薪资和直属领导id
	mysql> select salary,manageid from emp where name='张三';

	b：查询与张三的薪资及直属领导相同的员工信息
	mysql> select * from emp where (salary,manageid) = (8000,2);
	
mysql> select * from emp where (salary,manageid) = (select salary,manageid from emp where name='张三');

表子查询

• 子查询返回结果为多行多列

-- 查询与张三和李四职位和薪资相同的员工
mysql> select * from emp where (job,salary) in (select job,salary from emp where name='张三' or name='李四');

-- 查询入职日期是2020-09-30之后的员工信息，及其部门信息
mysql> select e.*,d.* from ( select * from emp where entrydate > '2020-09-30' ) e left join dept d on e.dept_id = d.id;

练习：

-- 查询每个员工的姓名、职位、所属部门名称和薪资。
mysql> select e.name,e.job,d.name,e.salary from emp e inner join dept d on e.dept_id = d.id;

-- 查询所有员工的基本信息，包括姓名、职位、所属部门名称（即使某些员工没有分配部门也会显示）。
mysql> select e.name,e.job,d.name,e.salary from emp e left join dept d on e.dept_id = d.id; 

-- 查询所有部门及其员工信息（即使某些部门没有员工也会显示）。
mysql> select e.name,e.job,d.name from emp e right join dept d on e.dept_id = d.id;



-- 查询每个部门的平均薪资，并列出薪资高于部门平均薪资的员工姓名和薪资
	a:分别查询每个部门的平均薪资
	select e.dept_id,avg(e.salary) as 'avg_salary' from emp e
inner join dept d on e.dept_id = d.id group by e.dept_id;
	b:根据每个部门所有员工薪资和平均薪资比较，列出大于平均薪资的员工姓名和薪资
	
mysql> select d.name,e.name,e.salary from emp e inner join dept d on e.dept_id = d.id inner join (select e.dept_id,avg(e.salary) as 'avg_salary' from emp e
inner join dept d on e.dept_id = d.id group by e.dept_id) as dept_avg on e.dept_id = dept_avg.dept_id where e.salary > dept_avg.avg_salary;