MySQL--多表查询

目录

• 多表查询
  • 建表和数据准备
  • 子查询
    • in 关键字子查询
    • 带有比较运算符的子查询
    • exists关键字子查询
  • 多表连接查询
    • 笛卡尔积查询
    • 内连接查询
    • 左外连接查询
    • 右外连接查询
    • 全外连接查询

多表查询

建表和数据准备

# 建表
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,'销售'),
    (203,'运营');

insert into employee(name,sex,age,dep_id) values
    ('nick','male',18,200),
    ('jason','female',48,201),
    ('sean','male',38,201),
    ('tank','female',28,202),
    ('oscar','male',18,200),
    ('mac','female',18,204);

子查询

当我们一次性查不到想要数据时就需要使用子查询

1. 子查询是将一个查询语句嵌套在另一个查询语句中
2. 内层查询语句的查询结果，可以为外层查询语句提供查询条件
3. 子查询中可以包含in 、not in 、any、all、exists、not exists等关键字
4. 还可以包含比较运算符. = 、 !=、> 、<等

in 关键字子查询

当内层查询结果会有多个结果时, 不能使用 = 必须是in ,另外子查询必须只能包含一列数据

# 需求：
# 1. 查询出平均年龄大于25岁的部门名
select id,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 in(
    select id from department where name='技术'
);

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

带有比较运算符的子查询

比较运算符就是=、!=、>、>=、<、<=、

# 查询大于所有人平均年龄的员工名与年龄
mysql> 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时，外层查询语句不进行查询。

# department表中存在dept_id=203，Ture
select * from employee where exists(
    select id from department where id=200
);
+----+------------+--------+------+--------+
| id | name       | sex    | age  | dep_id |
+----+------------+--------+------+--------+
|  1 | nick       | male   |   18 |    200 |
|  2 | jason      | female |   48 |    201 |
|  3 | sean       | male   |   38 |    201 |
|  4 | tank       | female |   28 |    202 |
|  5 | oscar      | male   |   18 |    200 |
|  6 | mac        | female |   18 |    204 |
+----+------------+--------+------+--------+

# department表中存在dept_id=205，False
select * from employee where exists(
    select id from department where id=204
);
Empty set (0.00 sec)

多表连接查询

• 语法：

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

笛卡尔积查询

• 语法：

  select * from 表1，表2……

笛卡尔积查询会出现大量的错误数据，并且会产生重复的字段

如：select * from employee,department;

内连接查询

本质上就是笛卡尔积查询

• 语法：

  select * from table1 inner join table2 on 条件

  inner 可以省略不写

左外连接查询

也就是以左边的表为基准，无论能否匹配成功，都要显示完整，右边的仅展示匹配上的记录

本质就是：在内连接的基础上增加左边有右边没有的结果

select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id;
+----+------------+--------------+
| id | name       | depart_name  |
+----+------------+--------------+
|  1 | nick       | 技术          |
|  5 | oscar      | 技术         |
|  2 | jason      | 人力资源      |
|  3 | sean       | 人力资源      |
|  4 | tank       | 销售         |
|  6 | mac        | NULL         |
+----+------------+--------------+

右外连接查询

也就是以右边的表为基准, 无论是否能够匹配都要完整显示 ，左边的仅展示匹配上的记录

本质就是：在内连接的基础上增加右边有左边没有的结果

select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id;
+------+-----------+--------------+
| id   | name      | depart_name  |
+------+-----------+--------------+
|    1 | nick      | 技术          |
|    2 | jason     | 人力资源      |
|    3 | sean      | 人力资源      |
|    4 | tank      | 销售         |
|    5 | oscar     | 技术         |
| NULL | NULL      | 运营          |
+------+-----------+--------------+

全外连接查询

无论是否匹配成功 两边表的数据都要全部显示

本质就是：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果

注意：MySQL不支持全外连接

但我们可以将，左外连接查询的结果和右外连接查询的结果做一个合并，达到全外连接的效果

• 语法：

  select * from emp union select * from emp;

  union将自动去除重复的记录， 而union all 不去重复

  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
;
+------+------------+--------+------+--------+------+--------------+
| id   | name       | sex    | age  | dep_id | id   | name         |
+------+------------+--------+------+--------+------+--------------+
|    1 | nick       | male   |   18 |    200 |  200 | 技术          |
|    5 | oscar      | male   |   18 |    200 |  200 | 技术          |
|    2 | jason      | female |   48 |    201 |  201 | 人力资源      |
|    3 | sean       | male   |   38 |    201 |  201 | 人力资源      |
|    4 | tank       | female |   28 |    202 |  202 | 销售         |
|    6 | mac        | female |   18 |    204 | NULL | NULL         |
| NULL | NULL       | NULL   | NULL |   NULL |  203 | 运营          |
+------+------------+--------+------+--------+------+--------------+