Python进阶----多表查询(内连,左连,右连), 子查询(in,带比较运算符)
一丶多表查询
多表连接查询的应用场景:
连接是关系数据库模型的主要特点,也是区别于其他类型数据管理系的一个标志.
通常来说表与表之间的关系不必确定,也就时实体与实体之间的关系不紧密,检索数据时,使用连表操作增强灵活性.可以再连表查询时增加新的字段,为不同实体创建新的表.
多表联查的基本语句:
# 多表连接查询语法
select 字段
from 表1
INNER|LEFT|RIGHT join 表2
on 表1.字段=表2.字段;
# 条件过滤
where 条件
# 1.形成新的表
# 2.灵活的操控两个表的所有字段
# 3.提高效率
交叉连接:
即笛卡尔积,将两个表所有的记录进行配对,数据大量冗余,没有实际意义
### 查询 employee 员工表 和 department 部门表的笛卡尔积
# 数据大量冗余 ,没有实际意义
select * from employee,department;
+----+------------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+------------+--------+------+--------+------+--------------+
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 1 | egon | male | 18 | 200 | 201 | 人力资源 |
| 1 | egon | male | 18 | 200 | 202 | 销售 |
| 1 | egon | male | 18 | 200 | 203 | 运营 |
| 2 | alex | female | 48 | 201 | 200 | 技术 |
| 2 | alex | female | 48 | 201 | 201 | 人力资源 |
| 2 | alex | female | 48 | 201 | 202 | 销售 |
| 2 | alex | female | 48 | 201 | 203 | 运营 |
| 3 | wupeiqi | male | 38 | 201 | 200 | 技术 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
| 3 | wupeiqi | male | 38 | 201 | 202 | 销售 |
| 3 | wupeiqi | male | 38 | 201 | 203 | 运营 |
| 4 | yuanhao | female | 28 | 202 | 200 | 技术 |
| 4 | yuanhao | female | 28 | 202 | 201 | 人力资源 |
| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
| 4 | yuanhao | female | 28 | 202 | 203 | 运营 |
| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |
| 5 | liwenzhou | male | 18 | 200 | 201 | 人力资源 |
| 5 | liwenzhou | male | 18 | 200 | 202 | 销售 |
| 5 | liwenzhou | male | 18 | 200 | 203 | 运营 |
| 6 | jingliyang | female | 18 | 204 | 200 | 技术 |
| 6 | jingliyang | female | 18 | 204 | 201 | 人力资源 |
| 6 | jingliyang | female | 18 | 204 | 202 | 销售 |
| 6 | jingliyang | female | 18 | 204 | 203 | 运营 |
+----+------------+--------+------+--------+------+--------------+
内连接:
内连接是一种最常用的连接类型,两个表的字段满足的连接条件,只显示两张表中互相匹配的项,其他不匹配项的不显示.
### 将员工表(employee)与部门表(department)进行连接, 员工表通过字段dep_id(部门id) 与 部门表字段id(部门id)进行连接
# 方式一 自连接 (特殊的内连接) 通过where 方式进行连接
select * from employee e ,department d where e.dep_id=d.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 | 技术 |
+----+-----------+--------+------+--------+------+--------------+
# 方式二 通过内连接 ... inner join ... on方式连接
select * from employee e inner join department d on e.dep_id=d.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 | 技术 |
+----+-----------+--------+------+--------+------+--------------+
外连接:
左外连接:
语法:A LEFT join B on A.XX=B.XX;
左表存显示所有,右表没有与左表匹配的则为 null.
右外连接:
语法:A RIGHT join B on A.XX=B.XX;
右表存显示所有,左表没有与右表匹配的则为 null.
### 员工表和部门表
# 左外连接 : 与左表没有匹配的则显示空,左表完全显示
select * from employee e left join department d on e.dep_id=d.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 | #### 重点~~~
+----+------------+--------+------+--------+------+--------------+
# 右外连接 : 与右表没有匹配的则显示空,右表完全显示
select * from employee e right join department d on e.dep_id=d.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 | 运营 | #### 重点~~
+------+-----------+--------+------+--------+------+--------------+
全连接:
union 关键字
mysql 不支持全外连接 full JOIN
mysql 使用 union 连接左连接和右连接，得到全连接
### union 全连接
# 必须是 左连接 + 右连接
select * from employee e left join department d on e.dep_id=d.id
union
select * from employee e right join department d on e.dep_id=d.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 | ### 重点