Mysql 多表查询

交叉连接  不适用任何匹配条件。生成笛卡尔积

select * from employee,department;
select * from employee CROSS JOIN department
select * from employee INNER JOIN department

 CROSS JOIN 与 INNER JOIN 的表现是一样的，在不指定 ON 条件得到的结果都是笛卡尔积。 

内连接   只连接匹配的行  inner join

select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id; 

找两张表共有的部分，相当于利用条件从笛卡尔积结果中筛选出了正确的结果

外链接之左连接 优先显示左表全部记录 left join

select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id;

 

外链接之右连接   优先显示右表全部记录   right join

select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id;

全外连接    显示左右两个表全部记录

 　　全外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
    注意：mysql不支持全外连接 full JOIN
    强调：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：子查询是将一个查询语句嵌套在另一个查询语句中。
# 2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
# 3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
# 4：还可以包含比较运算符：= 、 !=、> 、<等

# 查询平均年龄在25岁以上的部门名
select id,name from department where id in (select dep_id from employee group by dep_id having avg(age) > 25);

# 查看技术部员工姓名
select name from employee where dep_id in (select id from department where name='技术');

# 查看不足1人的部门名(子查询得到的是有人的部门id)
select name from department where id not in (select distinct dep_id from employee);

# 查询大于所有人平均年龄的员工名与年龄
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; 

# 带EXISTS关键字的子查询
# EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。
# 而是返回一个真假值。True或False
# 当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询
select * from employee where exists (select id from department where id=200)