【mysql】连表查询（内连接，左连接，右连接，全外连接）

爱被打了一巴掌 2022-05-15 06:41 288阅读 0赞

*   *  连表查询（内查询，左查询，右查询，全外查询）
        
         *  说明
         *  正文
            
             *  创建表单
                
                 *  1：创建部门表：department
                 *  2：创建员工表：employee
             *  内连接
             *  左连接
             *  右连接
             *  全外连接

## 连表查询（内查询，左查询，右查询，全外查询） ##

### 说明 ###

mysql版本：`Server version: 5.7.17 MySQL Community Server (GPL)`  
操作系统：`windows10`

--------------------

### 正文 ###

#### 创建表单 ####

##### 1：创建部门表：department #####

1：创建部门表单：department

create table department( id int, name varchar(20) );

![这里写图片描述][70]  
2：插入数据

insert into department values (200,'技术'), (201,'人力资源'), (202,'销售'), (203,'运营');

![这里写图片描述][70 1]

##### 2：创建员工表：employee #####

1：创建员工表单：employee

1：创建员工表单：employee
    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 );

![这里写图片描述][70 2]  
![这里写图片描述][70 3]  
2：插入数据

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

![这里写图片描述][70 4]  
![插入结果][70 5]

--------------------

#### 内连接 ####

内连接：只取两张变的共同部分

select * from employee,department where employee.dep_id = department.id;
     select * from employee inner join department on employee.dep_id = department.id;#内连接

![这里写图片描述][70 6]

#### 左连接 ####

左连接：在内连接的基础上保留左表的记录

select * from employee left join department on employee.dep_id = department.id;

![这里写图片描述][70 7]

#### 右连接 ####

右连接：在内连接的基础上保留右表的记录

select * from employee right join department on employee.dep_id = department.id;

![这里写图片描述][70 8]

#### 全外连接 ####

全外链接：在内连接的基础上保留左右两表没有对应关系的记录

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;

![这里写图片描述][70 9]

[70]: /images/20220515/355edda389024afcbaec445e91633677.png
[70 1]: /images/20220515/1eff9512d27e4ea98227408b18eea51a.png
[70 2]: /images/20220515/1dca7f67becf4e6ab9e843339ae1f308.png
[70 3]: /images/20220515/70e2b773c1a442c88c6930dfa5c11b02.png
[70 4]: /images/20220515/cbef3fe2b03b41848b994fae39c0901c.png
[70 5]: /images/20220515/81c383df928c4c29b57fc3a89422fe23.png
[70 6]: /images/20220515/80aef12c56b547768558563304bbd5fb.png
[70 7]: /images/20220515/60e7358d7c00458a953919a07cb1fbd0.png
[70 8]: /images/20220515/99b5ba13a7ca4f38bdb0771901fe01a5.png
[70 9]: /images/20220515/3912462b33504c3093833fe0a665d551.png