python 之 数据库（多表查询之连接查询、子查询、pymysql模块的使用）

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         |
+----&#