多表查询详解：内连接与外连接示例-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_47267628/article/details/121323321

多表查询的分类：

在这里插入图片描述

多表查询的语法

select
	字段列1,
	字段列2,
	...
from
	表名1,
	表名2,
	...
where
	...

由于在数据库查询中需要涉及到多张表的查询，但是在多表查询过程会出现笛卡尔积问题。

笛卡尔积的概念：
如有两个集合A，B取这两个集合的所有组成情况，但是笛卡尔积中会出现我们不需要的数据，所以要完成多表查询，就需要消除无用的数据存在。

消除无用的数据就需要涉及到：

内连接查询
外连接查询
子查询

内连接查询

内连接查询分为：隐式内连接查询和显示内连接查询

隐式内连接查询

隐式内连接语法：

隐式内连接语法：
select 
	字段列1,
	字段列2,
	...
from
	 表1,
	 表2,
	 ...
where
	 条件;

显示内连接查询
显示内连接查询语法：

显示内连接语法：
select
	 字段列1,
	 字段列2,
	 ...
from
	 表1,
【inner】
join
 	表2 
on 
	条件

注意是：隐式内连接是不会出现join和on关键字的，而显示内连接而不会出现where关键字；并且inner关键可以省略不写

数据准备区

CREATE TABLE dept(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(32) NOT NULL
);

CREATE TABLE emp(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(32) NOT NULL,
	gender CHAR(5), -- 性别
	salary DOUBLE, -- 工资
	join_date DATE ,-- 入职日期
	dept_id INT ,
	
	FOREIGN KEY (dept_id) REFERENCES dept (id)
);

INSERT INTO dept (NAME) VALUE('开发部'),('市场部'),('财务部');
SELECT * FROM dept;
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);
SELECT * FROM emp;

隐式内连接查询区

例子：
1.查询所有员工的信息和对应的部门信息
2.查询员工的名称，性别，部门表的名称
3.查询员工的名称，性别，部门表的名称（标准写法）

-- 查询所有员工信息和对应的部门信息
SELECT * FROM emp,dept WHERE emp.`dept_id`=dept.id;

-- 查询员工表的名称，性别，部门表的名称
SELECT emp.name,emp.`gender`,dept.`name` FROM emp,dept WHERE emp.`dept_id`=dept.`id`;

-- 查询员工表的名称，性别，部门表的名称（由于上述写法不标准,如果表名太长，不方便所以最好起别名）

SELECT 
	e1.`name`,	-- 员工名称
	e1.`gender`,	-- 员工表性别
	d1.`name`	-- 部门名称
FROM
	emp e1,   -- 员工表,起别名为e1
	dept d1   -- 部门表,起别名为d1
WHERE 
	e1.`dept_id`=d1.`id`;

显示内连接查询区

例子：
1.查询所有员工的信息和对应的部门信息
2.查询员工的名称，性别，部门表的名称
3.查询员工的名称，性别，部门表的名称（标准写法）
4.确定查询字段，查询唐僧的信息，显示员工 id，姓名，性别，工资和所在的部门名称
5.我们发现写表名有点长，可以给表取别名，显示的字段名也使用别名


-- 查询所有员工信息和对应的部门信息
SELECT * FROM emp INNER JOIN dept ON  emp.`dept_id`=dept.`id`;

-- 显示内链接查询
-- 查询所有员工的信息和对应部门信息
SELECT * FROM emp JOIN dept ON emp.`dept_id`=dept.`id`;

-- 查询所有员工的名称，性别，部门表名称
SELECT emp.`name`,emp.`gender`,dept.`name` FROM emp JOIN dept ON emp.`dept_id`=dept.`id` ;
-- 查询所有员工的名称，性别，部门表名称（标准写法）
SELECT 
	e1.name,	-- 员工名称
	e1.gender,	-- 员工性别
	d1.name		-- 部门名称
FROM 	
	emp e1		-- 员工表，别名e1
JOIN	
	dept d1		-- 部门表，别名d1
	
ON	
	e1.`dept_id`=d1.`id`;
	
-- 确定查询字段，查询唐僧的信息，显示员工 id，姓名，性别，工资和所在的部门名称
SELECT e.id,e.name,e.gender,e.salary,d.name FROM emp e 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 
JOIN
	 dept d
ON
	 e.`dept_id`=d.`id`
WHERE
	 e.`name`="孙悟空";

综合练习区：

-- 例子：查询唐僧的id,性别，姓名，工资和所在部门名称
/*
	唐僧的id,姓名，姓名和工资需要用到emp表，部门名称需要用到dept表
	条件
	name='唐僧'
	emp.dept_id=dept.id
*/
-- 使用隐式查询
SELECT 
	emp.id,
	emp.`gender`,
	emp.`name`,
	emp.`salary`,
	dept.`name`
FROM 
	emp,
	dept
WHERE 
	emp.`dept_id`=dept.`id`
		AND
	emp.`name`='唐僧';
	
-- 使用显示查询
/*
	唐僧的id,姓名，姓名和工资需要用到emp表，部门名称需要用到dept表
	条件
	name='唐僧'
	emp.dept_id=dept.id
*/
SELECT 
	emp.id,
	emp.`name`,
	emp.`gender`,
	emp.`salary`,
	dept.`name`
FROM
	emp
JOIN	
	dept
ON
	emp.`name`='唐僧' 
		AND
	emp.`dept_id`=dept.`id`;