Python-4.43 数据库连表操作

• 内连接：只取两张表的共同部分
• 左连接:在内连接的基础上保留左表的记录
• 右连接：在内连接的基础上保留右表的记录
• 全外连接：在内连接的基础上保留左右两表没有对应关系的记录

准备表

#建表
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 |
+----+------------+--------+------+--------+

• 内连接

mysql> select * from employee inner join department on employee.dep_id = department.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)

• 左连接

mysql> select * from employee left join department on employee.dep_id = department.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)

• 右连接

mysql> select * from employee right join department on employee.dep_id = department.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)

• 全外连接
  mysql不支持全外连接 full JOIN（select * from employee full join department on employee.dep_id = department.id；）

mysql> 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 | 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)