Mysql多表查询

---

Mysql多表查询

多表查询的基础都是建立在笛卡尔积上！，要理解笛卡尔积的执行过程！

• 驱动表只扫描一次，被驱动表可能要查询多次

一、内连接

内连接：求两个表的交集（共有的）

select * from 表1 t, 表二 c where t.id = ci.d;（隐式内连接）
select * from 表1 t inner join 表二 c on t.id = ci.d;（隐式内连接）
select * from 表1 t join 表二 c on t.id = ci.d;（显式内连接）

• 内连接中，【驱动表】是系统优化后自动选取的，会将执行计划中【扫描次数少】的表选做【驱动表】

二、外连接

内连接和外连接的区别：

• 对于【内连接】中的两个表，若【驱动表】中的记录在【被驱动表】中找不到与之匹配的记录，则该记录不会被加入到最后的结果集中。
• 对于【外连接】中的两个表，即使【驱动表】中的记录在【被驱动表】中找不到与之匹配的记录，也要将该记录加入到最后的结果集中，针对不同的【驱动表的选择】，又可以将外连接分为【左外连接】和【右外连接】。

左外

左表为驱动表：

select * from 表一 t left join 表二 c on t.id = c.id;

右外

右表为驱动表：

select * from 表一 t right join 表二 c on t.id = c.id;

外连接和内连接的区别

• left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
• right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
• inner join(等值连接) 只返回两个表中联结字段相等的行（交集）

left join

sql语句: select * from A left join B on A.aId = B.bId

结果如下:
aId　　　　　aNum　　　　　bId　　　　　bName
1　　　　　a20050111　　　　1　　　　　2006032401
2　　　　　a20050112　　　　2　　　　　2006032402
3　　　　　a20050113　　　　3　　　　　2006032403
4　　　　　a20050114　　　　4　　　　　2006032404
5　　　　　a20050115　　　　NULL　　　　　NULL　　　
6　　　　　a20050116　　　　NULL　　　　　NULL

（所影响的行数为 5 行）
结果说明:
left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.
换句话说,

• 左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aId = B.bId).
• B表记录不满足搜索条件的的地方均为NULL（NULL来填补）.

而join求的是两表的交集，不满足的不会显示出来！（因为左连接是以左表为基准，左表数据全都要有，右表不满足条件的NULL补上！！！）

这说明inner join（join）并不以谁为基础,它只显示符合条件的记录.

right join

sql语句: select * from A right join B on A.aId = B.bId

结果如下:
aId　　　　　aNum　　　　　bId　　　　　bName
1　　　　　a20050111　　　　1　　　　　2006032401
2　　　　　a20050112　　　　2　　　　　2006032402
3　　　　　a20050113　　　　3　　　　　2006032403
4　　　　　a20050114　　　　4　　　　　2006032404
NULL　　　　　NULL　　　　　8　　　　 2006032408
————————————————

仔细观察一下,就会发现,和left join的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方用NULL填充

三、全连接

虽然我的 MySQL 不支持此种方式，可以用其他方式替代解决，如下。

SELECT * from teacher t right outer join course c on c.t_id = t.id
union
SELECT * from teacher t left outer join course c on c.t_id = t.id

在这个结果中，不管是左表还是右表，所有的数据都被包含在了结果集当中：(去重)

四、子查询

• 环境准备：

DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;


# 创建部门表
	CREATE TABLE dept(
        did INT PRIMARY KEY AUTO_INCREMENT,
        dname VARCHAR(20)
    );

	# 创建员工表
	CREATE TABLE emp (
        id INT PRIMARY KEY AUTO_INCREMENT,
        NAME VARCHAR(10),
        gender CHAR(1), -- 性别
        salary DOUBLE, -- 工资
        join_date DATE, -- 入职日期
        dep_id INT,
        FOREIGN KEY (dep_id) REFERENCES dept(did) -- 外键，关联部门表(部门表的主键)
    );
	-- 添加部门数据
	INSERT INTO dept (dNAME) VALUES ('研发部'),('市场部'),('财务部'),('销售部');
	-- 添加员工数据
	INSERT INTO emp(NAME,gender,salary,join_date,dep_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),
	('小白龙','男',2500,'2011-02-14',null);	

• 概念

查询中嵌套查询，称嵌套查询为子查询。

什么是查询中嵌套查询呢？我们通过一个例子来看：

需求：查询工资高于猪八戒的员工信息。

来实现这个需求，我们就可以通过二步实现，第一步：先查询出来 猪八戒的工资

select salary from emp where name = '猪八戒'

第二步：查询工资高于猪八戒的员工信息

select * from emp where salary > 3600;

第二步中的3600可以通过第一步的sql查询出来，所以将3600用第一步的sql语句进行替换

select * from emp where salary > (select salary from emp where name = '猪八戒');

这就是查询语句中嵌套查询语句。

子查询根据查询结果不同，作用不同

• 子查询语句结果是单行单列，子查询语句作为条件值，使用 = != > < 等进行条件判断
• 子查询语句结果是多行单列，子查询语句作为条件值，使用 in 等关键字进行条件判断
• 子查询语句结果是多行多列，子查询语句作为虚拟表

**案例：**查询 ‘财务部’ 和 ‘市场部’ 所有的员工信息

SELECT * from dept where dname = '财务部';
--其实我们只要部门的id即可，即：
SELECT did from dept where dname = '财务部';

SELECT did from dept where dname = '市场部';

--综上两句：
SELECT did from dept where dname = '财务部' or dname = '市场部';
--结果为多行单列！

子查询语句：根据子查询结果的id来嵌套即可

-- 查询 '财务部' 或者 '市场部' 所有的员工的部门did
select did from dept where dname = '财务部' or dname = '市场部';

select * from emp where dep_id in (select did from dept where dname = '财务部' or dname = '市场部');

案例：查询入职日期是 ‘2011-11-11’ 之后的员工信息和部门信息

--查询入职日期是'2011-11-11' 之后的员工信息
select * from emp where join_date > '2011-11-11';
--查询结果为一张多行多列的表！(虚拟表)
--将虚拟表再和部门信息表内连接查询
select * from (select * from emp where join_date > '2011-11-11') t1 join dept on t1.dep_id = dept.did;