MySQL基础-多表查询

多表查询-多表关系

多表查询-概述

例如执行下行sql语句就会出现笛卡尔积：

sql">select *from emp,dept;
--消除笛卡尔积
select * from emp,dept where emp.dept_id = dept.id;

多表查询-查询分类

多表查询-连接查询-内连接

sql">--内连接演示
--1.查询每一个员工的姓名,及关联的部门的名称(隐式内连接实现)
--表结构：emp,dept
--连接条件：emp.dept_id = dept.id
select emp.name.dept.name from emp,dept where emp.dept_id = dept.id;
--起别名简化sql编写
select e.name,d.name from emp e,dept d where e.dept_id = d.id;
--2.查询每一个员工的姓名,及关联的部门的名称(显式内连接实现)
--关键字：INNER JOIN .. . ON ...(INNER可以省略)
--表结构：emp,dept
--连接条件：emp.dept_id = dept.id
select emp.name,dept.name from emp inner join dept on emp.dept_id = dept.id;
--起别名
select e.name,d.name from emp e inner join dept d on e.dept_id = d.id;

多表查询-连接查询-外连接

sql">--外连接演示
--1.查询emp表的所有数据,和对应的部门信息(左外连接),会完全包含左表中的全部数据.
--表结构：emp,dept
--连接条件：emp.dept_id = dept.id
select emp.* ,dept.name from emp left (outer) join dept on emp.dept_id = dept.id;
--起别名
select e.*,d.name from emp e left outer  join dept d on e.dept_id = d.id;
--2.查询dept表的所有数据,和对应的员工信息(右外连接),会完全包含右表中的全部数据.
select dept.* ,emp.* from emp right (outer) join dept on emp.dept_id = dept.id;
--起别名
select d.* ,e.* from emp e right (outer) join dept d on e.dept_id = d.id;
--左外连接和右外连接可以相互转换，只需要将表的顺序换一下
select dept.* ,emp.* from dept left (outer) join emp on emp.dept_id = dept.id;

多表查询-连接查询-自连接

要进行操作的数据表信息如下：

sql">--自连接
--1. 查询员工及其所属领导的名字(这里面的manager_id就是领导的id,通过自连接--相当于二次查询一张表--查询即可获取领导的名字)
--表结构：emp a,emp b
select a.name,b.name from emp a, emp b where a.managerid = b.id; 
--2. 查询所有员工及其领导的名字,如果员工没有领导,也要查询出来
--表结构：emp a,emp b
select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid  = b.id;

多表查询-联合查询

sql">--union all,union
--1.将薪资低于5000的员工，和年龄大于50岁的员工全部查询出来
select * from emp where salary < 5000
union 
select * from emp where age > 50;

注意：

多表查询-子查询

标量子查询

sql">--1.查询"销售部"所有员工信息
--(1)查出"销售部"部门id
select id from dept where name = '销售部';
--(2)根据销售部部门id的查询员工信息
select * from emp where dept_id = 4;
--结合起来
select * from emp where dept_id = (select id from dept where name = '销售部');

--2.查询在"方东白"入职之后的员工信息
--(1).查询"方东白"的入职日期
select entrydate from emp where name = '方东白';
--(2).查询指定入职日期之后入职的员工信息
select *from emp where entrydate > '2009-02-12';
--结合起来
select *from emp where entrydate > (select entrydate from emp where name = '方东白');

sql">--1.查询"销售部"和"市场部"的所有员工信息
--(1).查询"销售部"和"市场部"的部门ID
select id from dept where name = '销售部' or name = '市场部';
--(2).根据部门id,查询员工信息
select * from dept where dept_id in (2,4);
--结合
select * from dept where dept_id in (select id from dept where name = '销售部' or name = '市场部');--2.查询比财务部所有人工资都高的员工信息
--(1).查询所有财务部人员工资
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 = '财务部');
--(2).查询比财务部所有人工资都高的员工信息
select * from emp where salary > all(select salary from emp where dept_id = (select id from dept where name = '财务部'));--3. 查询比研发部其中任意一人工资高的员工信息
--(1).查询所有研发部人员工资
select id from dept where name = '研发部';
select salary from emp where dept_id = 2;
--结合
select salary from emp where dept_id = (select id from dept where name = '研发部');
--(2).查询比研发部其中任意一人工资高的员工信息
select * from emp where salary > any(select salary from emp where dept_id = (select id from dept where name = '研发部'));

sql">--1.查询与"张无忌"的薪资及直属领导相同的员工信息
--(1). 查询"张无忌"的薪资及直属领导
select salary,managerid from emp where name = '张无忌';
--(2). 查询与"张无忌"的薪资及直属领导相同的员工信息
select * from emp where (salary,managerid) = (select salary,managerid from emp where name = '张无忌');

sql">--1.查询与"鹿杖客"，"宋远桥"的职位和薪资相同的员工信息
--(1). 查询与"鹿杖客"，"宋远桥"的职位和薪资
select job,salary from emp where name = '鹿杖客' or name = '宋远桥';
--(2). 查询与"鹿杖客"，"宋远桥"的职位和薪资相同的员工信息
select * from emp where (job,salary) in (select job,salary from emp where name = '鹿杖客' or name = '宋远桥');

--2.查询入职日期是"2006-01-01" 之后的员工信息,及其部门信息
--(1).查询入职日期是"2006-01-01" 之后的员工信息
select * from emp where entrydate > '2006-01-01';
--(2).查询这部分员工，对应的部门信息
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.select e.name,e.age,e.job,d.name from emp e,dept d where e.dept_id = d.id;
2.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.select distinct d.id,d.name from emp e,dept d where e.dept_id = d.id;
4.select e.*,d.name from emp e left join dept d on e.dept_id = d.id where e.age > 40;
5.select e.*,s.grade from emp e,salgrade s where e.salary >= s.losal and e.salary <= s.hisal;
6.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.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.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.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;