6.mysql 基础之多表查询-CSDN博客

本文链接：https://blog.csdn.net/zzx_nihao/article/details/123737318

本文详细介绍如何使用SQL创建表并进行数据插入，通过多个示例演示不同类型的表连接查询，包括左连接、三表连查等，帮助读者掌握实用的SQL技能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

数据准备：创建表 emploees

create table emploees
(
id int,
name varchar(20),
gender varchar(100),
saraly double(10,2),
department_id int,
phone_number int,
job_id varchar(20),
manger_id varchar(10)
)

数据准备：创建表 departments

create table departments
(
department_id int,
department_name varchar(20),
manger_id varchar(10)，
location_id varchar(20)
)

数据准备：创建表 locations

create table locations
(
location_id varchar(20),
street_address varchar(20),
city varchar(10)，
state_province varchar(20)
)

数据准备：插入数据

insert into emploees values(101,'tom','m',5000,20,'zhangsan');
insert into emploees values(102,'tom1','m',6000,30,'zhangsan');
insert into emploees values(103,'tom2','m',7000,40,'zhangsan');
insert into emploees values(104,'tom3','m',8000,50,'zhangsan');
insert into emploees values(105,'tom4','m',9000,60,'zhangsan');
insert into emploees values(106,'tom5','m',10000,70,'zhangsan');
insert into emploees values(107,'kate1','f',5000,20,'lisi');
insert into emploees values(108,'kate2','f',6000,30,'lisi');
insert into emploees values(109,'kate3','f',7000,40,'lisi');
insert into emploees values(107,'kate4','f',8000,50,'lisi');
insert into emploees values(107,'kate5','f',9000,60,'lisi');
insert into emploees values(107,'kate6','f',9000,60,null);

#查询所有员工姓名，部门编号，部门名称（左连接，一般带有‘所有’都为外连接）

select e.name,d.department_id,department_name from emploees e left join departments d

on e.department_id=d.department_id

#查询员工姓名，部门编号，部门名称,城市（三表连查）

select e.name,d.department_id,department_name，l.city from emploees e left join departments d on e.department_id=d.department_id left join location l

on d.location_id =l.location_id

#查询部门编号为20的员工姓名，部门编号，部门名称（内连接，关键字inner 可以省略）

select e.name,d.department_id,department_name from emploees e join departments d

on e.department_id=d.department_id

where d.department_id=20

#查询员工姓名和领导信息（自连接）

select e.name,m.name from emploees e join emploees m

on e.id =m.manger_id

#查询没有员工的部门信息

select e.name,d.department_id,department_name from departments d left join emploees e

on d.department_id =e.department_id

where e.department_id is null