SQL--多表查询

SQL--多表查询

多表关系

一对多(多对一)

多对多

通过中间表维护两张表.

一对一

多表查询概述

多表查询--笛卡尔积

# 多表查询,笛卡尔积
SELECT * from emp,dept;

消除笛卡尔积

# 消除笛卡尔积
SELECT * from emp,dept where emp.dept_id=dept.id;

分类

1. 链接查询

2. 子查询

内连接

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

隐式内连接

# -内连接演示
# --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.查询每一个员工的姓名，及关联的部门的名称（显式内连接实现）
select * from emp e inner join dept d on e.dept_id=d.id;
select * from emp e join dept d on e.dept_id=d.id;

外连接

左外连接完全包含左表的数据.

# 外连接演示
# --1.查询mp表的所有数据，和对应的部门信息（左外连接）
# 表结构 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;
select e.*,d.name from emp e left join dept d on e.dept_id=d.id;

右外连接完全包含右表的数据.

右外连接可以改成左外连接

# --2.查询dept表的所有数据，和对应的员工信息（右外连接）
select d.*,e.* from emp e right outer join dept d on e.dept_id=d.id;
select d.*,e.* from dept d left outer join emp e on e.dept_id=d.id;

自连接

自连接--内连接

# 自连接
# --1.查询员工及其所属领寻的名字
# 将一张表看成两张表,必须起别名
# 表结构 emp
select a.name,b.name from emp a,emp b where a.managerid=b.id;

自连接--左外连接

# --2.查询所有员工emp及其领导的名字emp，如果员工没有领导，也需要查询出来
# 自连接--左外连接
select a.name,b.name from emp a left outer join  emp b on  a.managerid=b.id;

联合查询

对于union查询,就是把多次查询的结果合并起来,形成一个新的查询结果.

多张表的列数必须保持一致,字段类型也需要保持一致.

将两个结果的结果集直接查询出来

1.将薪资低于5000的员工，和年龄大于50岁的员工全部查询出来.

select * from emp where salary<5000
union all
select * from emp where age>50;

去重就是把all去掉

select * from emp where salary<5000
union 
select * from emp where age>50;

子查询

SQL语句嵌套select语句.

子查询的分类.

标量子查询

子查询的结果是一个单个值

1.查询"销售部"的所有员工信息

# a 查询"销售部" 部门ID,查询员工信息
select id from dept where name='销售部';
# 可以发现为4
# b.根据销售部门ID,查询员工信息
select *from emp where  dept_id='4';
select *from emp where  dept_id=(select id from dept where name ='销售部');

2.查询在”方东白”入职之后的员工信息

# a.查询方东白的入职日期
select entrydate from emp where name='方东白';

# b.查询指定入职日期之后入职的员工信息
select * from emp where entrydate>'2009-02-12';
select * from emp where entrydate>(select entrydate from emp where name='方东白');

列子查询

1.查询"销售部”和"市场部"的所有员工信息

# a.查询销售部和市场部所有的部门id
select id from dept where name='销售部' or name = '市场部';
# b.根据部门id 查询员工信息
select * from emp where dept_id in (2,4);
select * from emp where dept_id in (select id from dept where name='销售部' or name = '市场部');

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

# a 查询所有 财务部 人员工资
select id from dept where name='财务部';
select salary from emp where dept_id='3';
select salary 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.查询比研发部其中任意一人工资高的员工信息

# a 查询研发部所有人工资
select  id from dept where name='研发部';
select  salary from emp where dept_id='1';
select  salary from emp where dept_id=(select  id from dept where name='研发部');
# b 比研发部其中任一人工资高的员工信息
select *
from emp
where salary > any (select salary from emp where dept_id = (select id from dept where name = '研发部'));
# some 和 any 相同
select *
from emp
where salary > some (select salary from emp where dept_id = (select id from dept where name = '研发部'));

行子查询

1. 查询与“张无忌”的薪资及直属领导相同的员工信息

# a 查询"张无忌的薪资和直属领导
select salary,managerid from emp where name='张无忌';
# b 查询与"张无忌"的薪资及直属领导相同的员工信息
select * from emp where salary='12500' and managerid='1';
select * from emp where (salary,managerid)=(12500,1);
select * from emp where (salary,managerid)=(select salary,managerid from emp where name='张无忌');

表子查询

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

# 查询与”鹿杖客”，“宋远桥”的职位和薪资相同的员工信息
select * from emp where (job,salary) in (select job,salary from emp where name='鹿杖客' or name='宋远桥');

2.查询入职日期是"2006-01-01”之后的员工信息，及其部门信息

# 查询入职日期 "2006-01-01之后入职的员工信息";
select * from emp where entrydate > '2006-01-01';

# b 查询这部分员工,对应的部门信息
select e.*,d.*
from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id=d.id;

多表查询案例

根据需求，完成SQL语句的编写
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岁的员工，及其归属的部门名称：如果员工没有分配部门，也需要展示出来

# 外连接
# 表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 where e.age>40;

5.查询所有员工的工资等级。

# emp表 salgrade表
# 连接条件 emp>=salgrade and emp.salary<=salgrade.hisal
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 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.id and d.name='研发部';

8.查询工资比"灭绝"高的员工信息

# 先查询灭绝的工资
select emp.salary from emp where emp.name='灭绝';
# 然后查询其他员工的信息
select * from emp e where e.salary>(8500);
select * from emp e where e.salary>(select emp.salary from emp where emp.name='灭绝');

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

# 先查询平均薪资
select avg(emp.salary) from emp;
# 然后查询大于平均薪资的员工信息
select * from emp where salary>(10673.5294);
select * from emp where salary>(select avg(emp.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 count(*) from emp where dept_id=1;
select d.id,d.name ,(select count(*) from emp e where e.dept_id=d.id) '人数' from dept d;

12.查询所有学生的选课情况，展示出学生名称，学号，课程名称

# 表: student,coursse ,student_course
# 连接条件 student.id =student_course.student,course.id =student_course.courseid

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

上面编写的SQL语句不是唯一的

多表查询总结