MySQL 之 多表连接查询及子查询

1、前期准备

#建表
create table department(
id int,
name varchar(20) 
);

create table employee(
id int,
name varchar(20),
sex enum('male','female'),
age int,
dep_id int
);

#插入数据
insert into department values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');

insert into employee(id,name,sex,age,dep_id) values
(1,'cai','male',18,200),
(2,'liu','female',48,201),
(3,'ling','male',38,201),
(4,'liangliang','female',28,202),
(5,'acai','male',18,200),
(6,'bao','female',18,204)
;

2、连表

(1)、 交叉连接：不使用任何匹配条件。生成笛卡尔积

select * from employee,department;

(2)、 内连接：只连接匹配的行（所有不在条件匹配内的数据，都会被剔出连表）

# 方式一 ：
	select * from employee,department where dep_id = department.id;
# 方式二 ：
	select * from employee inner join department on dep_id = department.id;

(3)、 外连接

左外连接：优先显示左表全部记录（本质就是在内连接的基础上增加左边有右边没有的结果）

右外连接：优先显示右表全部记录（本质就是在内连接的基础上增加右边有左边没有的结果）

全外连接：显示左右两个表全部记录（mysql 不支持全外连接，但可以间接实现全外连接）

# 左外连接 left join
	select * from employee left join department on dep_id = department.id;
# 右外连接 right join
	select * from employee right join department on dep_id = department.id;
# 全外连接 full join （mysql中不支持）
	# 在mysql中实现全外连接的方法
	select * from employee left join department on dep_id = department.id
	union
	select * from employee right join department on dep_id = department.id;

3、符合条件查询连接

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

# 示例2：以内连接的方式查询employee和department表，并且以age字段的升序方式显示。
    select * from employee inner join department on dep_id = department.id order by age;

4、子查询

(1)、 带 in 关键字的子查询

# 查询平均年龄在25岁以上的部门名
    select name from department where id in (select dep_id from employee group by dep_id having avg(age)>25);
    
# 查看不足1人的部门名(子查询得到的是有人的部门id)
     # 分步完成
    	先从employee中查有多少个部门有人
            select distinct dep_id from employee;
        从department表中把不在上述部门中的那些项找出来
            select * from department where id not in (200,201,202,204);
     # 组合结果
            select * from department where id not in (select distinct dep_id from employee);

(2)、 带比较运算符的子查询

比较运算符： = != > < >= <= <>

# 查看技术部员工姓名
    # 分步完成 
    	先查询技术部的id
          	select id from department where name = '技术';
     	根据技术部id查询employee表 找到技术部id对应的人名
            select * from employee where dep_id = 200;
     # 组合结果
            select name from employee where dep_id = (select id from department where name = '技术');

# 查询大于所有人平均年龄的员工名与年龄
    # 分步完成   
        所有人的平均年龄
            select avg(age) from employee;   # 28
        查大于上述平均年龄的人
            select name,age from employee where age>28;
    # 组合结果
            select name,age from employee where age>(select avg(age) from employee);
        
# 查询大于部门内平均年龄的员工名、年龄
    # 分步完成    
        查询各部门平均年龄
            select dep_id,avg(age) from employee group by dep_id;
        查大于部门平均年龄的人
            select * from employee where dep_id = 200 and age>18
            select * from employee where dep_id = 201 and age>43
            select * from employee where dep_id = 202 and age>28
            select * from employee where dep_id = 204 and age>18
    # 组合结果
            select * from employee inner join (select dep_id,avg(age) as avg_age from employee group by dep_id) as t on employee.dep_id = t.dep_id;

            select * from employee inner join (select dep_id,avg(age) as avg_age  from employee group by dep_id) as t on employee.dep_id = t.dep_id where age>avg_age;

(3)、 带 exists 关键字的查询

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

# 内层查询返回True，外层查询会进行
select name from employee1 where exists(select * from department where id=200);
# 内层查询返回False，外层查询不会进行
select name from employee1 where exists(select * from department where id=205);