MySql基础-多表查询

多表查询

多表查询分类
（1）连接查询

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

（2）子查询

1、连接查询

1.1 内连接

• 隐式内连接

select字段列表from 表1，表2 where 条件...;

-.显式内连接

select 字段列表 from 表1  inner join 表2  on  连接条件...;

内连接查询的是两张表交集的部分
例：

-- 内连接展示
-- 1、查询每一个员工的姓名，及关联的部门名称（隐式内连接实现）
-- 表结构 emp,dept
-- 连接条件：emp.dept_id=dept_id
select emp.name,dept.name from emp,dept where emp.dept_id=dept.id;

-- 为表起了一个别名实现查询，注意在去了别名之后就不可以再用以前的名字了
select e.name,d.name from emp e,dept d where e.dept_id=d.id;

-- 2、查询每一个员工的姓名，及关联部门的名称（隐式内连接实现）--- inner join...on...
-- 表结构 emp,dept
-- 连接条件：emp.dept_id=dept_id
select e.name,d.name from emp e inner join dept d on e.dept_id = d.id;

1.2 外连接

• 左外连接
  相当于查询表1（左表）的所有数据包含表1和表2交集部分的数据

select 字段列表 from 表1 left outer join 表2 on条件...;

• 右外连接
  相当于查询表2（右表）的所有数据包含表1和表2交集部分的数据

select  字段列表  from 表1 right outer join 表2  on 条件...

注意：outer可省略

例：

-- 外连接展示
-- 1、查询emp表中的所有数据，和对应的部门信息（左外连接）
-- 表结构 emp,dept
-- 连接条件：emp.dept_id=dept_id
select e.*,d.name from emp e left outer join dept d on e.dept_id=d.id;
-- 可省略upper效果一样
select e.*,d.name from emp e left join dept d on e.dept_id=d.id;

-- 2、查询dept表中的所有信息及对应的员工姓名（右外连接）
-- 表结构 emp,dept
-- 连接条件：emp.dept_id=dept_id
select d.*,e.name from emp e right join dept d on e.dept_id=d.id;

1.3 自连接

在这里插入代码片自连接查询可以是内连接查询，也可以是外连接查询。

select 字段列表 from 表A 别名A join 表A 别名B on 条件…

例：

-- 自连接(必须起别名）
-- 1、查询员工及其所属领导的名字
-- 表结构：emp
select a.name,b.name from emp a,emp b where a.managerid=b.id;

-- 2、查询所有员工emp及其领导的名字emp,如果员工没有领导，也需要查询出来
-- 表结构：emp a,emp b
select a.name '员工',b.name '领导' from emp a left join emp b on a.managerid=b.id;

2、联合查询-union, union all
对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。
select 字段列表from 表a 
union[all]
select字段列表from 表b......;
对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
union all会将全部的数据直接合并在一起，union会对合并之后的数据进行去重。
例：
-- union all,union
-- 1、将工资低于5000的员工和年龄大于50岁的员工全部查询出来
select * from emp where salary<10000
union all
select * from emp where age>40;
-- 相当于
select * from emp where salary<10000||age>40;

2、子查询

• 概念：SQL语句中嵌套select语句，称为嵌套查询，又称子查询

select * from t1 where column1 = (select column1 from t2);

子查询外部的语句可以是insert/update / delete / select的任何一个。

• 根据子查询结果不同，分为:
  标量子查询(子查询结果为单个值)
  列子查询(子查询结果为一列)
  行子查询(子查询结果为一行)
  表子查询(子查询结果为多行多列)
• 根据子查询位置，分为：where之后，from后面，select后面

2.1 标量子查询

子查询返回的结果是单个值(数字、字符串、日期等)，最简单的形式，这种子查询成为标量子查询。
常用的操作符: =， <>，>，>=，<，<=
例：

-- 标量子查询
-- 1、查询”查询部“的所有员工信息
--   a、查询”销售部“部门ID
--   b、根据销售部ID,查询员工信息
--   c、将两者通过子查询方式连接起来
select * from emp where dept_id=(select id from dept where name='销售部');

-- 2、查询在”小昭“入职前的员工信息
select * from emp where entrydate > (select entrydate from emp where name='冷柠');

2.2 列子查询

子查询返回的结果是一列(可以是多行)，这种子查询称为列子查询。
常用的操作符:in、not in、any、some、all

操作符	描述
in	在权定的集合范围之内，多选一
not in	不在指定的集合范围之内
any	子查询返回列表中，有任意一个满足即可
some	与any等同，使用some的地方都可
all	以使用any 子查询返回列表的所有值都必须满足

例：

-- 列子查询（关键词in)
-- 1、查询”销售部“和”市场部“的所有员工信息
select * from emp where dept_id in(select id from dept where name='财务部' or name='销售部');

-- 2、查询比财务部员工工资都高的员工信息(关键词all)
--   a.查询所有财务部的员工
select id from emp where dept_id=(select id from dept where name='财务部');
--   b.比财务部所有员工工资都高的员工信息
select * from emp where salary > all(select salary from emp where dept_id=(select id from dept where name='财务部'));

-- 3、查询比研发部任意一人工资高的员工信息(关键词any,some)
-- a、查询研发部所有人工资
select salary from emp where dept_id=(select id from dept where name='研发部');
-- b、比研发部任意一人工资高的员工信息（some与any概念相同，可以等价使用）
select * from emp where salary>any(select salary from emp where dept_id=(select id from dept where name='研发部'));
select * from emp where salary>some(select salary from emp where dept_id=(select id from dept where name='研发部'));

2.3 行子查询

子查询返回的结果是一行（可以是多列），这种子查询为行子查询。（这些代表的都是=后面嵌套的select语句返回的值显示形式）
常用的操作符：=、<>、in、not in
例：

-- 行子查询
-- 1、查询与”张无忌“的薪资及直属领导相同的员工信息
select * from emp where (salary,managerid)=(select salary,managerid from emp where name='蓝儿');

2.4 表子查询

子查询返回的结果是多行队列，这种子查询称为表子查询。
常用操作符：in
例：

-- 表子查询
-- 1、查询与"常明","紫儿"的职位和薪资相同的员工信息
select * from emp where (job,salary) in (select job,salary from emp where name ='常明' or name = '紫儿');

-- 2、查询入职信息是‘2006-01-01’之后的员工信息及其部门信息
select e.*,d.* from (select * from emp where entrydate>'2006-01-01') e left join dept d on e.dept_id=d.id;

3、多表查询相关综合案例

在遇到与多个表的信息都相关的查询题时，先用from连接多个表所有的共同信息再通过where按题目要求去去重，从而得到查询结果。 注意在在自查询时要起别名，平常多表查询时也要注意起别名，还要注意子查询。

-- ------------>多表查询<------------
create table salgrade(
    grade int,
    losal int,
    hisal int
)comment '薪资等级表';

insert into salgrade values (1,0,3000),(2,3001,5000),(3,5001,8000),(4,8001,10000),
                            (5,10001,15000),(6,15001,20000),(7,20001,25000),(8,25001,30000);

-- 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、部门名称(关键字distinct重复去重）
select distinct d.id,d.name from emp e,dept d where e.dept_id=d.id;

-- 4、查询所有年龄大于40岁的员工，及其所属部门名称；如果员工没有分配部门，也需要显示出来
select e.*,d.name from emp e left join dept d on e.dept_id=d.id where age>40;

-- 5、查询所有员工的工资等级
-- 表：emp,salgrade
select e.*,s.grade from emp e,salgrade s where e.salary>=s.losal and e.salary<=s.hisal;

select e.*,s.grade 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,
     salgrade s,
     dept d
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 *
from emp
where salary > (select salary from emp where name = '常明');

-- 9、查询比平均薪资高的员工信息
select * from emp where salary>(select avg(salary) from emp);

-- 10、查询本部门低于平均工资的员工信息
-- a、查询指定部门的平均薪资:1
select avg(e1.salary) from emp e1 where e1.dept_id=1;
-- b、查询低于本部门平均工资的员工信息
select *
from emp e2
where e2.salary < (select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id);

-- 11、查询所有的部门信息，并统计部门的员工人数
select d.*,(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,
     stedent_course sc,
     course c
where s.id = sc.studentid
  and sc.courseid = c.id;