MySQL基础：表连接查询

目录

表连接查询

数据准备

多表查询的分类

笛卡尔积现象

内连接

隐式内连接

显式内连接

总结内连接查询步骤

外连接

左外连接

右外连接

---

表连接查询

数据准备

# 创建部门表
create table dept(
 id int primary key auto_increment,
 name varchar(20)
)
insert into dept (name) values ('开发部'),('市场部'),('财务部'); 
# 创建员工表
create table emp (
 id int primary key auto_increment,
 name varchar(10),
 gender char(1), -- 性别
 salary double, -- 工资
 join_date date, -- 入职日期
 dept_id int,
 foreign key (dept_id) references dept(id) -- 外键，关联部门表(部门表的主键)
) 
insert into emp(name,gender,salary,join_date,dept_id) values('孙悟空','男',
7200,'2013-02-24',1);
insert into emp(name,gender,salary,join_date,dept_id) values('猪八戒','男
',3600,'2010-12-02',2);
insert into emp(name,gender,salary,join_date,dept_id) values('唐僧','男',
9000,'2008-08-08',2);
insert into emp(name,gender,salary,join_date,dept_id) values('白骨精','女',
5000,'2015-10-07',3);
insert into emp(name,gender,salary,join_date,dept_id) values('蜘蛛精','女',
4500,'2011-03-14',1);

• 多表查询的作用：比如：我们想查询孙悟空的名字和他所在的部门的名字，则需要使用多表查询。
• 如果一条 SQL 语句查询多张表，因为查询结果在多张不同的表中。每张表取 1 列或多列。

多表查询的分类

笛卡尔积现象

• 什么是笛卡尔积

-- 需求：查询所有的员工和所有的部门
select * from emp,dept;

• 结果分析

• 如何清除笛卡尔积现象的影响
  • 不是所有的数据组合都是有用的，只有员工表.dept_id = 部门表.id 的数据才是有用的。
  • 所以需要通过条件过滤掉没用的数据。

-- 设置过滤条件 Column 'id' in where clause is ambiguous
select * from emp,dept where id=5;
select * from emp,dept where emp.`dept_id` = dept.`id`;
-- 查询员工和部门的名字
select emp.`name`, dept.`name` from emp,dept where emp.`dept_id` = dept.`id`;

内连接

• 用左边表的记录去匹配右边表的记录，如果符合条件的则显示。
  • 如：从表.外键=主表.主键

隐式内连接

• 隐式内连接：看不到 JOIN 关键字，条件使用 WHERE 指定

select * from emp,dept where emp.`dept_id` = dept.`id`;

显式内连接

• 显示内连接：使用 INNER JOIN ... ON 语句, 可以省略 INNER
• 查询唐僧的信息，显示员工 id，姓名，性别，工资和所在的部门名称，需要联合 2 张表同时才能查询出需要的数据，使用内连接

• 确定查询哪些表

select * from emp inner join dept;

• 确定表连接条件，员工表.dept_id = 部门表.id 的数据才是有效的

select * from emp e inner join dept d on e.`dept_id` = d.`id`;

• 确定查询条件，查询的是唐僧的信息，员工表.name='唐僧'

select * from emp e inner join dept d on e.`dept_id` = d.`id` where e.`name`='唐僧';

• 确定查询字段，查询唐僧的信息，显示员工 id，姓名，性别，工资和所在的部门名称

select e.`id`,e.`name`,e.`gender`,e.`salary`,d.`name` from emp e inner join dept d 
on e.`dept_id` = d.`id` where e.`name`='唐僧';

• 发现写表名有点长，可以给表取别名，显示的字段名也使用别名

select e.`id` 编号,e.`name` 姓名,e.`gender` 性别,e.`salary` 工资,d.`name` 部门名字 from
emp e inner join dept d on e.`dept_id` = d.`id` where e.`name`='唐僧';

总结内连接查询步骤

• 确定查询哪些表
• 确定表连接的条件
• 确定查询的条件
• 确定查询的字段

外连接

左外连接

• 左外连接：使用 LEFT OUTER JOIN ... ON，OUTER 可以省略

SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件

• 用左边表的记录去匹配右边表的记录，如果符合条件的则显示；否则，显示 NULL
• 可以理解为：在内连接的基础上保证左表的数据全部显示(左表是部门，右表员工)

-- 在部门表中增加一个销售部
insert into dept (name) values ('销售部');
select * from dept;
-- 使用内连接查询
select * from dept d inner join emp e on d.`id` = e.`dept_id`;
-- 使用左外连接查询
select * from dept d left join emp e on d.`id` = e.`dept_id`;

右外连接

• 右外连接：使用 RIGHT OUTER JOIN ... ON，OUTER 可以省略

SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件

• 用右边表的记录去匹配左边表的记录，如果符合条件的则显示；否则，显示 NULL
• 可以理解为：在内连接的基础上保证右表的数据全部显示
  • 如：在员工表中增加一个员工

-- 在员工表中增加一个员工
insert into emp values (null, '沙僧','男',6666,'2013-12-05',null);
select * from emp;
-- 使用内连接查询
select * from dept inner join emp on dept.`id` = emp.`dept_id`;
-- 使用右外连接查询
select * from dept right join emp on dept.`id` = emp.`dept_id`;

• 【注】参考黑马MySQL教程