MySQL多表查询

建表和数据准备

# 建表
create table department(id int, name varchar(20));
create table employee(id int primary key auto_increment, name varchar(20), sex enum("male", "female") not null default "male", age int, dep_id int);

# 写入数据
insert into department values (200, "技术"), (201, "销售"), (202, "运营");

insert into employee (name, sex, age, dep_id) values 
("haha", "male", 24, 201),
("hax", "male", 24, 201),
("sax", "male", 24, 202),
("sexy", "male", 24, 200),
("proxy", "male", 24, 201);

外连接语法

　　select 字段列表 from 表1 inner | left | right join 表2 on 表1.字段=表2.字段

交叉连接

　　直接将两张表拼接在一起，不使用任何匹配条件，生成笛卡尔积；

　　select * from department, employee;

内连接：inner join

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

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

　　select employee.id employee.name employee.age from employee, department where employee.dep_id=department.id;

　　这两条SQL语句的效果是等同的；

左外连接：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；但可以用下面的方式实现全外连接，也就是用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;

子查询

　　子查询是将一个查询语句嵌套在另一个查询语句中，内层查询语句的结果可以作为外层查询语句的查询条件；在子查询中可以包含：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);

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

# 查看不足1人的部门名称
select name from department where id not in (select distinct dep_id from employee);

带比较运算符的子查询

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

# 查询 大于 部门内平均年龄的员工名、年龄
select t1.name, t1.age from employee t1 
inner join 
(select dep_id, avg(age) avg_age from employee 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时，外层查询语句不进行查询；

# 存在id为200的部门，True；可以查询出结果
select * from employee where exists (select id from department where id=200);
# 不存在id为204的部门，False；
select * from employee where exists (select id from department where id=204);
# 查询结果为empty