数据库——多表查询

1、多表连接关系：

主要语法：

SELECT 字段列表
        FROM 表1 INNER|LEFT|RIGHT JOIN 表2
        ON 表1.字段 = 表2.字段;

交叉连接：生成笛卡尔积

部门和员工两张表，每个员工都对应了所有的部门。

select * from employee,department;

总行数为两张表的行数的乘积。

内连接 inner join ：找两张表共有的部分

相当于利用条件从笛卡尔积结果中筛选出了正确的结果。

select * from employee inner join department 
on employee.dep_id = department.id ;

左连接: left join 以左边为准

在内连接的基础上保留左表的记录

select * from employee left join department 
on employee.dep_id = department.id ;

右连接：right join 保留右表的记录

select * from employee right join department 
on employee.dep_id = department.id ;

全外连接：union 间接实现

在内连接的基础上，保留左右两表没有对应关系的记录，MySQL只能间接实现

select * from employee left join department
 on employee.dep_id = department.id
union
select * from employee right join department
 on employee.dep_id = department.id ;

示例

#示例1：以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门
select employee.name,department.name 
	from employee inner join department
    on employee.dep_id = department.id
    where age > 25;

2、完整的执行顺序

执行from 语句 先找到两张表，找笛卡儿积
执行 on 过滤，制取两张表有对应关系的
添加外部行，是内还是左，还是右

执行where过滤
执行group by
执行having
执行select
执行distinct
执行order by
执行limit

3、子查询

子查询是将一个查询语句嵌套在另一个查询语句中。

• 子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
• 还可以包含比较运算符：= 、 !=、> 、<等

带IN关键字的子查询

#查询平均年龄在25岁以上的部门名

select id,name from department
    where id in 
        (select dep_id from employee group by dep_id having avg(age) > 25);

#查看不足1人的部门名
select name from department
    where id in 
        (select dep_id from employee group by dep_id having count(id) <=1);

带比较运算符的子查询

#查询大于所有人平均年龄的员工名与年龄
mysql> select name,age from emp
 where age > (select avg(age) from emp);

#查询大于部门内平均年龄的员工名、年龄
select t1.name,t1.age from emp t1
inner join 
(select dep_id,avg(age) avg_age from emp group by dep_id) t2
on t1.dep_id = t2.dep_id
where t1.age > t2.avg_age;

1、查询每个部门最新入职的那位员工

分组，每个部门，
时间最大的就是最新的

select * from employee inner join 
(select post,max(hire_date) as max_date from employee group by post) as t2
on employee.post = t2.post
where employee.hire_date = t2.max_date

带EXISTS关键字的子查询

EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录，而是返回一个真假值，True或False。
当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询

 select * from employee  where exists
   (select id from department where id=200);