26.5 多表查询：内连接（隐式、显示）、外连接（左外、右外）、嵌套查询、练习

目录

1 多表查询分类

1.2 多表查询出现笛卡尔积现象

1.3 内连接

内连接--隐式内连接

内连接--显式内连接

1.4 外连接

外连接--左外连接

外连接--右外连接

2 子查询(嵌套查询)

2.1 子查询的结果是单行单列的

2.2 子查询的结果是多行单列的

2.3 子查询的结果是多行多列的

3 多表查询练习

练习1：查询所有员工信息。（查询员工编号，员工姓名，工资，职务名称，职务描述）

练习2：查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置

练习3：查询员工姓名，工资，工资等级

练习4：查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

练习5.查询出部门编号、部门名称、部门位置、部门人数

练习 6.查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询

---

1 多表查询分类

分类

---

 

创建部门表

-- 创建部门表
CREATE TABLE dept(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)	
)CHARACTER SET utf8;
-- 插入数据
INSERT INTO dept (NAME) VALUES
('开发部'),
('市场部'),
('财务部');
SELECT * FROM dept;

 创建员工表

-- 创建员工表
CREATE TABLE emp(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	gender CHAR(1),-- 性别
	salary DOUBLE,-- 工资
	join_date DATE,-- 入职日期
	dept_id INT,
	CONSTRAINT emp_dept_id FOREIGN KEY(dept_id)REFERENCES dept(id)
	ON UPDATE CASCADE ON DELETE CASCADE		
)CHARACTER SET utf8;

SELECT * FROM emp;
-- 插入数据
INSERT INTO emp
	(NAME,gender,salary,join_date,dept_id) 
VALUES
	('孙悟空','男',7200,'2013-02-24',1),
	('猪八戒','男',3600,'2010-12-02',2),
	('唐僧','男',9000,'2008-08-08',2),
	('白骨精','女',5000,'2015-10-07',3),
	('蜘蛛精','女',4500,'2011-03-14',1);

1.2 多表查询出现笛卡尔积现象

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

所以需要设置过滤条件来清除笛卡尔积现象的影响

1.3 内连接

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

内连接--隐式内连接

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

-- 隐式内连接
SELECT * FROM emp,dept WHERE emp.`dept_id`=dept.`id`;

内连接--显式内连接

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

-- 确定表连接条件，员工表.dept_id = 部门表.id 的数据才是有效的
SELECT * FROM emp INNER JOIN dept ON emp.`dept_id`=dept.`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`='唐僧';

1.4 外连接

外连接--左外连接

使用 left outer join ... on，outer 可以省略，用左边表的记录去匹配右边表的记录，如果符合条件的则显示；否则，显示 NULL

-- 在部门表中增加一个销售部
INSERT INTO dept (NAME) VALUES ('销售部');
SELECT * FROM dept;

--  显式内连接
SELECT * FROM dept INNER JOIN emp ON dept.`id`=emp.`dept_id`;
--  外连接中的左连接
SELECT * FROM dept d LEFT JOIN emp e ON d.`id`=e.`dept_id`;

 

外连接--右外连接

使用 RIGHT OUTER JOIN ... ON，OUTER 可以省略  可以理解为：在内连接的基础上保证右表的数据全部显示

-- 在员工表中增加一个员工
INSERT INTO emp VALUES (NULL, '沙僧','男',6666,&#