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

• • 连表查询（内查询，左查询，右查询，全外查询）
    • 说明
    • 正文
      • 创建表单
      • 内连接
      • 左连接
      • 右连接
      • 全外连接

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

说明

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

---

---

正文

创建表单

1：创建部门表：department

1：创建部门表单：department

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

2：插入数据

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

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

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

---

内连接

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

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

左连接

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

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

右连接

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

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

全外连接

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

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;