进击のpython
数据库——多表查询
其实最开始创建多表的目的就是为了将单表里的数据分出来
变成两个三个表，为了逻辑清晰，也为了省内存，就多表分离了
但是，当我们想查看的时候，就需要将分离出来的东西“拼”回去
也就是我们接下来要讲的，多表查询~
那在开始之前，我们先准备两个表：
#建表
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
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204)
;
#查看表结构和数据
mysql> desc department;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
mysql> desc employee;
+--------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | NO | | male | |
| age | int(11) | YES | | NULL | |
| dep_id | int(11) | YES | | NULL | |
+--------+-----------------------+------+-----+---------+----------------+
mysql> select * from department;
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
+------+--------------+
mysql> select * from employee;
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
+----+------------+--------+------+--------+
哎哎哎别说我赋值赋错了啊我是故意让两个表分别有一条对应不上的！
既然说是提到了多表查询，毕竟也是查询嘛，所以有的同学想这样：
select * from department,employee;
mysql> select * from department,employee;
+------+--------------+----+------------+--------+------+--------+
| id | name | id | name | sex | age | dep_id |
+------+--------------+----+------------+--------+------+--------+
| 200 | 技术 | 1 | egon | male | 18 | 200 |
| 201 | 人力资源 | 1 | egon | male | 18 | 200 |
| 202 | 销售 | 1 | egon | male | 18 | 200 |
| 203 | 运营 | 1 | egon | male | 18 | 200 |
| 200 | 技术 | 2 | alex | female | 48 | 201 |
| 201 | 人力资源 | 2 | alex | female | 48 | 201 |
| 202 | 销售 | 2 | alex | female | 48 | 201 |
| 203 | 运营 | 2 | alex | female | 48 | 201 |
| 200 | 技术 | 3 | wupeiqi | male | 38 | 201 |
| 201 | 人力资源 | 3 | wupeiqi | male | 38 | 201 |
| 202 | 销售 | 3 | wupeiqi | male | 38 | 201 |
| 203 | 运营 | 3 | wupeiqi | male | 38 | 201 |
| 200 | 技术 | 4 | yuanhao | female | 28 | 202 |
| 201 | 人力资源 | 4 | yuanhao | female | 28 | 202 |
| 202 | 销售 | 4 | yuanhao | female | 28 | 202 |
| 203 | 运营 | 4 | yuanhao | female | 28 | 202 |
| 200 | 技术 | 5 | liwenzhou | male | 18 | 200 |
| 201 | 人力资源 | 5 | liwenzhou | male | 18 | 200 |
| 202 | 销售 | 5 | liwenzhou | male | 18 | 200 |
| 203 | 运营 | 5 | liwenzhou | male | 18 | 200 |
| 200 | 技术 | 6 | jingliyang | female | 18 | 204 |
| 201 | 人力资源 | 6 | jingliyang | female | 18 | 204 |
| 202 | 销售 | 6 | jingliyang | female | 18 | 204 |
| 203 | 运营 | 6 | jingliyang | female | 18 | 204 |
+------+--------------+----+------------+--------+------+--------+
诶？？分析一下！这是把department的每一条数据都和employee的每一条数据组合起来打印了
虽然说打印出来的东西没啥用，但是确实两个表连在一起了~
而这种情况叫做：笛卡尔积
再继续分析，其实也并不是都没用，employee(dep_id)和department(id)相等的数据才是对我们有用的
那如何拿到这种数据呢？
select * from department,employee having department.id = employee.dep_id;
mysql> select * from department,employee having department.id = employee.dep_id;
+------+--------------+----+-----------+--------+------+--------+
| id | name | id | name | sex | age | dep_id |
+------+--------------+----+-----------+--------+------+--------+
| 200 | 技术 | 1 | egon | male | 18 | 200 |
| 201 | 人力资源 | 2 | alex | female | 48 | 201 |
| 201 | 人力资源 | 3 | wupeiqi | male | 38 | 201 |
| 202 | 销售 | 4 | yuanhao | female | 28 | 202 |
| 200 | 技术 | 5 | liwenzhou | male | 18 | 200 |
+------+--------------+----+-----------+--------+------+--------+
5 rows in set (0.32 sec)
这是不是就是我们想要的东西了~
但是！！！！发现了吗？没有对应的数据，没取出来（dep_id = 204的数据和id=203的数据）
还要说明的是，这么写可不可以？可以，但是不推荐这么写
mysql提供了方法来解决这个问题，其实这些方法的本质都是在笛卡尔积的基础上进行的操作
看来我们错怪笛卡尔积了，他还是挺有用的
一共有四种方法：内连接，左连接，右连接，全（外）连接
内连接
内连接就是查找两个表的公共部分
select * from employee inner join department on department.id = employee.dep_id;
关联表 inner join 被关联表 on 条件
mysql> select * from employee inner join department on department.id = employee.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 | 技术 |
+----+-----------+--------+------+--------+------+--------------+
5 rows in set (0.00 sec)
左连接
左连接就是在保证公共部分的基础上，保留左表
select * from employee left join department on department.id = employee.dep_id;
mysql> select * from employee left join department on department.id = employee.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 |
+----+------------+--------+------+--------+------+--------------+
6 rows in set (0.00 sec)
是不是坐标中没有关联的也打印出来了
右连接
左连接都会了，右连接不会？说不过去吧！
定义自己定义
select * from employee right join department on department.id = employee.dep_id;
mysql> select * from employee right join department on department.id = employee.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 | 运营 |
+------+-----------+--------+------+--------+------+--------------+
6 rows in set (0.00 sec)
全（外）连接
你猜猜这是是干什么的？
（保证公共部分的基础上，保留左右表）
有的想这么做：
select * from employee full join department on department.id = employee.dep_id;
结果报错了，因为MySQL是不支持full join的 别的数据库是支持的
所以我们可以拿到左右连接再去重，是不是就是全（外）连接了？可太机智了！
union 联合去重
select * from employee left join department on department.id = employee.dep_id
union
select * from employee right join department on department.id = employee.dep_id;
mysql> select * from employee left join department on department.id = employee.dep_id
-> union
-> select * from employee right join department on department.id = employee.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 | 运营 |
+------+------------+--------+------+--------+------+--------------+
7 rows in set (0.00 sec)
是不是就完美解决了~