表连接查询

1.什么是多表查询

• 准备数据

# 创建部门表
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 列或多列。

多表查询的分类：

2. 笛卡尔积现象

2.1 什么是笛卡尔积现象

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

2.2 如何清除笛卡尔积现象的影响

我们发现不是所有的数据组合都是有用的，只有 员工表.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`;

3. 内连接

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

3.1 隐私内连接

看不到 JOIN 关键字，条件使用 WHERE 指定

-- SELECT 字段名 FROM 左表, 右表 WHERE 条件
select * from emp,dept where emp.`dept_id` = dept.`id`;

3.2 显示内连接

使用 INNER JOIN ... ON 语句, 可以省略 INNER

-- SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件

查询唐僧的信息，显示员工 id，姓名，性别，工资和所在的部门名称，我们发现需要联合 2 张表同时才能查询出需要的数据，使用内连接。

• 1）确实查询哪些表

select * from emp inner join dept;

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

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

• 3）确定查询条件，我们查询的是唐僧的信息，员工表.name="唐僧"

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

• 4）确定查询字段，查询唐僧的信息，显示员工 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`="唐僧";

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

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`="唐僧";

3.3 总结内连接查询步骤：

• 1)确定查询哪些表
• 2)确定表连接的条件
• 3)确定查询的条件
• 4)确定查询的字段

4. 左外连接

使用 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`;

5. 右外连接

使用 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`;

OK,这篇就到这里