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

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 , from employee inner join department on dep_id = department.id where age>25;
select ename, dname from employee e inner join department d on dep_id = d.id where age>25;
select , 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);。