MySQL多表查询

连表查询

  

 

上面两张表通过笛卡尔积得到一个全量拼接的大表；

笛卡尔积：

select * from employee,department;

内连接(inner join)

 双方能够互相匹配的项才会被显示出来；

select * from 表1 inner join 表2 on 条件

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

表的重命名：

select t1.name,t2.name from employee as t1 inner join department as t2
        on t1.dep_id = t2.id；

外连接

左外连接

左外连接(left join) 只完整的显示左表中的所有内容,以及右表中与左表匹配的项；

select * from 表1 left join 表2 on 条件

例如：
select * from employee left join department
		on employee.dep_id = department.id；

右外连接

右外连接(right join) 只完整的显示右表中的所有内容,以及左表中与右表匹配的项；

select * from 表1 right join 表2 on 条件

例如：
select * from employee right join department
        on employee.dep_id = department.id

全外连接

全外连接 永远显示左表和右表中所有的项，关键字(union)

例如：

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：还可以包含比较运算符：= 、 !=、> 、<等

# 查"技术"部的所有员工的名字
# select name from employee where dep_id = (select id from department where name = '技术');

# 查"技术"和"销售"部的所有员工的名字
# select name from employee where dep_id in (select id from department where name in ('技术','销售'));

练习：

1.找出年龄大于25岁的员工以及员工所在的部门
    select e.name,d.name from employee e inner join department d
    on e.dep_id = d.id where e.age>25
2.以内连接的方式查询employee和department表，并且以age字段的升序方式显示
    select * from employee e inner join department d
    on e.dep_id = d.id  order by e.age

1. 查询平均年龄在25岁以上的部门名
    select dep_id from employee group by dep_id having avg(age)>25
    select name from department where id in (select dep_id from employee group by dep_id having avg(age)>25);
2.查看技术部员工姓名
    select name from employee where dep_id = (select id from department where name = '技术');

3.查看不足1人的部门名(子查询得到的是有人的部门id)
    在员工表中不存在的一个部门id,在department表里
    在department表里的id字段中找到一个在员工表的dep_id中不存在的项

    select name from department where id not in (select dep_id from employee group by dep_id);
    把员工表里所有的人所在的dep_id都查出来

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

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