10.10 多表连接查询

10.101 内连接

把两张表有对应关系的记录连接成一张虚拟表

select * from emp,dep；                                  #连接两张表的笛卡尔积
select * from emp,dep where emp.dep_id = dep.id;            # 不推荐用where连接表
select * from emp inner join dep on emp.dep_id = dep.id;    #推荐
+----+-----------+--------+------+--------+------+--------------+
| id | name      | sex    | age  | dep_id | id   | name         |
+----+-----------+--------+------+--------+------+--------------+
|  1 | egon      | male   |   18 |    200 |  200 | 技术         |
|  2 | alex      | female |   48 |    201 |  201 | 人力资源     |
|  3 | wupeiqi   | male   |   38 |    201 |  201 | 人力资源     |
|  4 | yuanhao   | female |   28 |    202 |  202 | 销售         |
|  5 | liwenzhou | male   |   18 |    200 |  200 | 技术         |
+----+-----------+--------+------+--------+------+--------------+

#应用：
select * from emp,dep where emp.dep_id = dep.id and dep.name = "技术"; 
select * from emp inner join dep on emp.dep_id = dep.id where dep.name = "技术";
+----+-----------+------+------+--------+------+--------+
| id | name      | sex  | age  | dep_id | id   | name   |
+----+-----------+------+------+--------+------+--------+
|  1 | egon      | male |   18 |    200 |  200 | 技术   |
|  5 | liwenzhou | male |   18 |    200 |  200 | 技术   |
+----+-----------+------+------+--------+------+--------+

10.102 左连接

在内连接的基础上，保留左边没有对应关系的记录

select * from emp left join dep on emp.dep_id = dep.id;
+----+------------+--------+------+--------+------+--------------+
| id | name       | sex    | age  | dep_id | id   | name         |
+----+------------+--------+------+--------+------+--------------+
|  1 | egon       | male   |   18 |    200 |  200 | 技术         |
|  5 | liwenzhou  | male   |   18 |    200 |  200 | 技术         |
|  2 | alex       | female |   48 |    201 |  201 | 人力资源     |
|  3 | wupeiqi    | male   |   38 |    201 |  201 | 人力资源     |
|  4 | yuanhao    | female |   28 |    202 |  202 | 销售         |
|  6 | jingliyang | female |   18 |    204 | NULL | NULL         |
+----+------------+--------+------+--------+------+--------------+

10.103 右连接

在内连接的基础上，保留右边没有对应关系的记录

select * from emp right join dep on emp.dep_id = dep.id;
+------+-----------+--------+------+--------+------+--------------+
| id   | name      | sex    | age  | dep_id | id   | name         |
+------+-----------+--------+------+--------+------+--------------+
|    1 | egon      | male   |   18 |    200 |  200 | 技术         |
|    2 | alex      | female |   48 |    201 |  201 | 人力资源     |
|    3 | wupeiqi   | male   |   38 |    201 |  201 | 人力资源     |
|    4 | yuanhao   | female |   28 |    202 |  202 | 销售         |
|    5 | liwenzhou | male   |   18 |    200 |  200 | 技术         |
| NULL | NULL      | NULL   | NULL |   NULL |  203 | 运营         |
+------+-----------+--------+------+--------+------+--------------+

10.104 全连接

在内连接的基础上，保留左、右边没有对应关系的记录，并去重

select * from emp left join dep on emp.dep_id = dep.id
union
select * from emp right join dep on emp.dep_id = dep.id;
+------+------------+--------+------+--------+------+--------------+
| id   | name       | sex    | age  | dep_id | id   | name         |
+------+------------+--------+------+--------+------+--------------+
|    1 | egon       | male   |   18 |    200 |  200 | 技术         |
|    5 | liwenzhou  | male   |   18 |    200 |  200 | 技术         |
|    2 | alex       | female |   48 |    201 |  201 | 人力资源     |
|    3 | wupeiqi    | male   |   38 |    201 |  201 | 人力资源     |
|    4 | yuanhao    | female |   28 |    202 |  202 | 销售         |
|    6 | jingliyang | female |   18 |    204 | NULL | NULL         |
| NULL | NULL       | NULL   | NULL |   NULL |  203 | 运营         |
+------+------------+--------+------+--------+------+--------------+

补充：多表连接可以不断地与虚拟表连接

#查找各部门最高工资
select t1.* from emp as t1 inner join (select post,max(salary) as ms from emp group by post) as t2
on t1.post = t2.post
where t1.salary = t2.ms;

10.11 子查询

把一个查询语句用括号括起来，当做另外一条查询语句的条件去用，称为子查询

#查询技术部员工的名字
select emp.name from emp inner join dep on emp.dep_id = dep.id where dep.name="技术";#连接查询