表准备：

院系表
课程表
教师表

一，多表联查原因

多表联查的原因有很多：主要列举三个

避免单表冗余和单表数据量过大，使用多表来解决，多表就必须涉及多表查询
多表如果不进行联查，而进行多表单查，程序的DAO压力会很大
数据库的表锁和行锁，如果不使用多表查询，多表单查锁的消耗会非常大

1，避免单表的问题

在数据库的设计时，必须避免单表数据过大，数据冗余问题，解决这些问题就必须使用表拆分，如：上图把院系，课程，教师分成了三种表，假如要查询课程对于的教师就需多表联查

当然，如果多表改成一张表就可以避免多表，如下图：但这样是非常冗余和难维护的，是非常不可取的

2，解决程序的DAO压力

假设现在有一个需求：查询userID为1003的教师和它所担任的课程

多表单查：

#先查询教师表
SELECT * FROM teacher WHERE userID IN(1003);
#再查询课程表
SELECT * FROM course WHERE teacherID IN(1003);

一个需求两次查询，需要两次查询,两次返回，假如是在应用程序中，查询压力大，使用多表查询就可以避免这个问题

3，多表单查锁的消耗会非常大

现在有一个需求：查询userID为1001的教师和它所担任的课程和院系

#先查询教师表
SELECT * FROM teacher WHERE userID IN(1003);
#再查询课程表
SELECT * FROM course WHERE teacherID IN(1003);
#查询院系(通过teacher获取collegaID)
SELECT * FROM college WHERE collegeID IN(2);

这个需求涉及到了三张表，假如不使用多表联查一次性解决，而是使用单表查询，那么三次单独查询需要锁定3次。那么假如表多了,需求复杂了就会造成严重的锁资源损耗

二，笛卡尔积错误和解决办法

一，笛卡尔积的概念和演示

笛卡尔乘积是一个数学运算，假设我有两个集合X,Y，那么X和Y的笛卡尔积就是X和Y的所有可能组合，也就是第一个对象来自于X，第二个对象来自于Y的使用可能；组合的个数即为两个集合中元素个数的乘积

在SQL92中，笛卡尔积也称为交叉连接，英文CROSS JOIN。在SQL99中也是使用CROSS JSON表示交叉连接，它的作用就是可以表示任意连接，即使这两张表不相关

演示笛卡尔积：查询两个表teacher(3条数据)和course(6条数据)，表中原始数据如下

① 方式一

SELECT userName,title,degree,courseName,courseTime FROM teacher,course;

②方式二：CROSS JOIN

SELECT userName,title,degree,courseName,courseTime FROM teacher CROSS JOIN course;

③方式三：INNER JOIN

SELECT userName,title,degree,courseName,courseTime FROM teacher INNER JOIN course;

结果：

二，笛卡尔积的原因和解决

笛卡尔积产生原因：没有多表的连接条件

省略多个表的连接条件(或关联条件)
连接条件(或关联条件)无效
所有表中的所有行互相连接

解决：在多表查询时，通过WHERE加入连接条件

演示：查询两个表teacher和course，通过userID连接

SELECT userName,title,degree,courseName,courseTime FROM teacher,course WHERE userID=teacherID;

当多表连接查询有字段是重复的，要使用表别名防止字段冲突

SELECT T.userName,T.title,T.degree,C.courseName,C.courseTime FROM teacher T,course C WHERE T.userID=C.teacherID;

注：连接条件数量 = 多表查询的数量 - 1

三，多表查询的分类

多表查询按不同的场景和情况分为三类：

等值连接 <---> 非等值连接
自连接 <---> 非自连接
内连接 <---> 外连接

注：这些分类是场景和使用的区分，它们可以相互包含，如自连接也可以是等值连接等

一，等值连接和非等值连接

等值连接：连接条件是相等了(=)
非等值连接：连接条件是不等的(<=,>=,<,>)

例1(等值连接)：查询查询两个表teacher和course，通过userID和teacherID连接并且两个字段相等

#teach表的userID和course表的teacherID是相等的关系
SELECT userName,title,degree,courseName,courseTime FROM teacher,course WHERE userID=teacherID;

例2(非等值连接)：查询查询两个表teacher和course，通过userID和teacherID连接，userID大于teacherID

#teach表的userID大于course表的teacherID是
SELECT userName,title,degree,courseName,courseTime FROM teacher,course WHERE userID > teacherID;

二，自连接和非自连接

自连接：自我连接，应用于特殊场景，只涉及单表
非自连接：多表联查，涉及到不同的表都属于非自连接，例：之前的等值和非等值都属于非自连接

自连接的演示：现有一个教师表，它有一个managerID字段，这个字段表示该老师的领导，而领导也存在于教师表中

表中的原数据：

现在要查询userID为1003的老师和它所属领导的信息：在逻辑上把teacher表分为普通teacher表和manager表

SELECT Tea.userName,Tea.title,Tea.degree,Man.userName '领导名称',Man.title '领导title' FROM teacher Tea,teacher Man WHERE Tea.managerID = Man.userID;

三，内连接和外连接

一，内连接

内连接：只查询满足条件的记录，结果集中不包含一个表与另一个表不匹配的行

在之前演示的等值连接,非等值连接,自连接都属于内连接，查询结果以满足WHERE为前提

内连接提供了固定的语法：

标准写法：表A INNER JOIN 表B ON 条件1 INNER JOIN 表C ON 条件2……
简单写法：表A JOIN 表B ON 条件……

ON就相当于之前的WHERE

演示：现在有两个表courseID和teacher，服装设计和软件工程没有teacherID

查询教师和对应的课程：

#写法一：
SELECT T.userName,T.title,T.degree,C.courseName,C.courseTime FROM teacher T,course C WHERE T.userID=C.teacherID;
#写法二：内连接提供的标准语法
SELECT T.userName,T.title,T.degree,C.courseName,C.courseTime FROM teacher T INNER JOIN course C ON T.userID=C.teacherID;

二，外连接

外连接：两个表在连接过程中除了返回满足条件的行以外还会返回左(或右)表中不满足条件的行，没有匹配的行在结果表中相应的列为空(NULL)

外连接的分类：

左外连接：两个表或者多个表在连接过程中除了返回满足连接条件的行以外还返回左表中不满足条件的行
右外连接：两个表或者多个表在连接过程中除了返回满足连接条件的行以外还返回右表中不满足条件的行
全外连接：两个表或者多个表在连接过程中除了返回满足连接条件的行以外还返回左表和右表不满足条件的行

注：① 在左连接中左表为主表，右表从表 ② 右连接同理

左连接的格式：

标准格式：表A LEFT OUTER JOIN 表B ON 关联条件……
简单格式：表A LEFT JOIN 表B ON 关联条件……

右连接的格式：

标准格式：表A RIGHT OUTER JOIN 表B ON 关联条件……
简单格式：表A RIGHTJOIN 表B ON 关联条件……

演示：原数据就是内连接举例的那张数据图，现使用左外连接的方式查询教师和对应的课程

SELECT T.userName,T.title,T.grade,C.courseName,C.courseType FROM course C LEFT JOIN teacher T ON C.teacherID = T.userID;

三，全外连接

全外连接属于外连接，因为他实现比较特殊所以单独讲

在SQL99的规范中，使用 FULL JOIN ON来实现全外连接，但在Mysql中不支持这种方式(Oracle是支持的)：

#如
SELECT T.userName,T.title,T.degree,C.courseName,C.courseTime FROM teacher T FULL JOIN course C ON T.userID=C.teacherID;

MySQL提供了UNION来解决全外连接：利用UNION关键字可以给出多条SELECT语句，并将它们的结果组合成单个结果集

注：和并时，两个表对应的列数和数据类型必须相同，并且相互对应，各个SELECT语句之间使用UNION或UNION ALL关键字分隔

关键字：

UNION：返回两个查询的结果的并集，并进行去重
UNION ALL：不进行去重

语法格式：

SELECT column,…… FROM table1
UNION [ALL]
SELECT column,…… FROM table2

在执行UNION ALL语句所需要的资源比UNION语句少，如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复数据，则尽量使用UNION ALL语句，以提高查询的效率

演示：原数据就是内连接举例的那张数据图，现使用全外连接的方式查询教师和对应的课程

SELECT T.userName,T.title,T.grade,C.courseName,C.courseType FROM course C LEFT JOIN teacher T ON C.teacherID = T.userID
UNION ALL #不去重
SELECT T.userName,T.title,T.grade,C.courseName,C.courseType FROM course C RIGHT JOIN teacher T ON C.teacherID = T.userID;

四，7种JOIN操作

按照连接查询的不同应用场景把JOIN连接查询分成了七种：

WQL

多表联查(复盘)