MySQL之多表查询

多表查询

内连接

隐式内连接

查询每一个员工的姓名 ， 及关联的部门的名称(隐式内连接实现) 表结构: 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;

显式内连接

查询每一个员工的姓名 ， 及关联的部门的名称(隐式内连接实现) 表结构: 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;
select e.name, d.name from emp e join dept d on e.deptid = d.id;

注意：显式内连接inner可以省略

外连接

左外连接

SELECT 字段列表 FROM 表1 LEFT [OUTER]JOIN 表2 0N 条件 ...;

相当于查询左表的所有数据和 表1表2交集部分的数据

右外连接

SELECT 字段列表 FROM 表1 RIGHT [ OUTER]JOIN 表2 0N 条件 ...;

相当于查询右表的所有数据和 表1表2交集部分的数据

外连接演示

1. 查询emp表的所有数据， 和对应的部门信息(左外连接) 表结构: emp，dept 连接条件:emp.dept_id = dept.id

   select e.*, d.name from emp e leftouterjoin 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 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;

注意：自连接查询，可以是内连接查询，也可以是外连接查询。

查询员工 及其 所属领导的名字 表结构: emp--

select a.name , b.name from emp a , emp b where a.managerid = b.id;

查询所有员工 emp 及其领导的名宁 emp ， 如果员工没有领导， 也需要查询出来

表结构: emp a，emp b

select a.name '员工', b.name '领导' from emp a left join emp b on a,managerid = b.id;

联合查询

案例：将薪资低于5000的员工和年龄大于 50 岁的员工全部查询出来

union all

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

union all查询结果不去重，单纯拼接在一起

union

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

union查询结果去重，并拼接在一起

注意：对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。

子查询

概念：

SQL语句中嵌套SELECT语句，称为嵌套查询又称子查询

形式

SELECT *FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );

子查询外部的语句可以是

INSERT / UPDATE / DELETE / SELECT 的任何一个

根据子查询结果不同，分为：

1. 标量子查询(子查询结果为单个值)

2. 列子查询(子查询结果为一列)

3. 行子查询(子查询结果为一行)

4. 表子查询(子查询结果为多行多列)

根据子查询位置，分为:

WHERE之后、FROM之后、SELECT之后。

 

案例

根据销售部部门ID， 查询员工信息

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 >(seLect entrydate from emp where name = 方东白);

列子查询

子查询返回的结果是一列(可以是多行)，这种子查询称为列子查询

常用的操作符:IN 、NOT IN、ANY 、SOME、ALL

操作符	描述
IN	在指定的集合范围之内多选一
NOT IN	不在指定的集合范围之内
ANY	子查询返回列表中，有任何一个满足即可
SOME	与ANY等同，使用SOME的地方都可以使用ANY
ALL	子查询返回列表的所有值都必须满足

案例

查询比 财务部 所有人工资都高的员工信息(ALL)

select * from emp where salary > all (select salary from emp where dept_id = (select id from dept where name = '财务部'));

查询比 研发部 所有人工资都高的员工信息(SOME/ANY)

select * from emp where salary > any ( select salary from emp where dept-id = (select id from dept where name = '研发部') );

行子查询

案例

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

表子查询

案例

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

查询“鹿杖客””宋远桥”的职位和薪资

select job，salary from emp where name =鹿杜客' or name =宋远桥';

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

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';

查询这部分员工，对应的部门信息。

select e.*, d.* from(select * from emp where entrydate > '2006-01-01')
e left join dept d on e.dept_id = d.id ;