多表查询 -- 内外连接/左右连接

多表查询的定义

• 定义： 通过查询多张表格获取数据，至少涉及两张表
• 数据准备：
  • 创建部门表，插入三条数据

创建员工信息表添加外键约束，允许级联删除，并向三个部门插入对应的员工信息

## 创建部门信息表 
CREATE TABLE dept(  
id INT PRIMARY KEY AUTO_INCREMENT,  
dept_name VARCHAR(20),  
dept_manager VARCHAR(20),  
dept_location VARCHAR(20)  
); 
INSERT INTO dept VALUES(1,'研发部','张无忌','北京');
INSERT INTO dept VALUES(2,'运营部','赵敏','深圳');
INSERT INTO dept VALUES(3,'销售部','周芷若','成都');

# 创建员工信息表并添加级联删除的外键约束 
CREATE TABLE emp_part(  
emp_id INT PRIMARY KEY AUTO_INCREMENT,  
ename VARCHAR(20),  
age INT ,  
gender VARCHAR(10),
dept_id INT,
salary INT,
-- 添加外键约束 
CONSTRAINT emp_dept FOREIGN KEY(dept_id) REFERENCES dept(id)
-- 设置允许级联删除 
ON DELETE CASCADE
);

向员工信息表中插入数据

 

INSERT INTO emp_part VALUES(1,'令狐冲',25,'男','1',20000);
INSERT INTO emp_part VALUES(2,'任盈盈',23,'女','1',15000);  
INSERT INTO emp_part VALUES(3,'岳不群',45,'男','1',40000);
INSERT INTO emp_part VALUES(4,'任我行',40,'男','1',30000); 
INSERT INTO emp_part VALUES(5,'岳灵珊',21,'女','1',10000);
INSERT INTO emp_part VALUES(6,'赵灵儿',21,'女','2',7000); 
INSERT INTO emp_part VALUES(7,'林月如',22,'女','2',10000); 
INSERT INTO emp_part VALUES(8,'阿奴',20,'女','2',7000); 
INSERT INTO emp_part VALUES(9,'李逍遥',25,'男','2',15000); 
INSERT INTO emp_part VALUES(10,'景天',28,'男','2',20000);
INSERT INTO emp_part VALUES(11,'邱莹莹',21,'女','3',5000);
INSERT INTO emp_part VALUES(12,'关雎尔',22,'女','3',8000);
INSERT INTO emp_part VALUES(13,'曲筱绡',23,'女','3',10000);
INSERT INTO emp_part VALUES(14,'樊胜美',30,'女','3',10000);
INSERT INTO emp_part VALUES(15,'安迪',28,'女','3',20000);

笛卡尔积

• 定义: 笛卡尔积是一个数学概念，又称直积，它是指两个集合元素所有可能有序对的集合。

• 例子：

  • A={a,b},B={c,d}
  • A*B ={(a,c),(b,c),(a,d),(b,d)}

• 语法：select 字段名称 from 表1， 表2

内连接

• 内连接（INNER JOIN）：使用比较运算符进行表间某（些）列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组合成新的记录。匹配上显示，匹配不上不显示。
• 例子： 比如使用外键=主键这个条件过滤掉无效数据
• 按语法结构分为： 隐式内连接和显式内连接

隐式内连接

• 在笛卡尔积的的基础上，使用where条件过滤无用的数据，这种连接方式是隐式内连接. 无出现inner join这个明显字段就为隐式

• 语法：select [字段名称] from 表1，表2 where [条件]

• 例1： 筛选出运营部的员工的id，姓名以及所在城市

SELECT emp_id,ename,dept_location 
FROM emp_part,dept 
WHERE dept_id=id and dept_name="运营部";

显式内连接

• 显式内连接： 使用 select [字段名称] from [表1]inner join [表2] on [条件] 这种方式
• 列子： 用显式内连接查询运营部的员工的id，姓名以及所在城市

SELECT emp_id,dept_location,ename 
FROM emp_part 
INNER JOIN dept ON dept_id=id AND dept_name="运营部"

外连接查询

外连接

• 外连接查询：查询多个表中相关联的行，有时候需要包含没有关联的行中数据，即返回查询结果集合中不仅包含符合连接条件的行，还包括左表（左连接）、右表（右连接）中的所有数据行。

• 左外连接 , 使用 LEFT OUTER JOIN , OUTER 可以省略

• 右外连接 , 使用 RIGHT OUTER JOIN , OUTER 可以省略

 

 

左连接

 

• 左连接：以左表为基准匹配右表的数据，右表中没有的项，显示为空

• 语法：SELECT [字段] FROM [左表] LEFT JOIN [右表] ON [条件]

• 例子：公司新成立人力资源部，还未招聘员工，请使用左连接查询方式查询出公司所有部门员工的员工号，姓名，性别以及他们所在的部门名称和城市

#向部门表中插入人力资源部
INSERT INTO dept VALUES(4,'人力资源部','甄嬛','北京');
#查询出需要的数据
SELECT emp_id,ename,gender,dept_name,dept_location 
FROM dept LEFT JOIN emp_part ON dept.id=emp_part.dept_id

右连接

 

• 右连接：以右表为基准匹配左表的数据，左表中没有的项，显示为空

• 语法：SELECT [字段] FROM [左表] RIGHT JOIN [右表] ON [条件]

• 使用右连接的方式查询出所有员工信息以及他们所在的部门名称和城市

总结

• 内连接: inner join：只显示两个表匹配到的行
• 左连接: left join：显示左表里面所有的行；不仅包含匹配的行，而且包含不匹配的行；所以会出现某些信息为空状态；
• 右连接: right join：显示右表里面所有的行；不仅包含匹配的行，而且包含不匹配的行；所以会出现某些信息为空状态；
• 内连接和左连接使用居多