SQL多表查询

首先我们需要明确两张表中之间的关系是靠外键来维护的，而在哪张表中设置外键呢？

我们的数据库表经常是一对多的关系，比如部门（一）和员工（多），专业（一）和学生（多），这时候我们就需要在多的一方设置一个字段为外键指向另一方的主键。

而当有多对多的关系时，我们就需要建立第三张表作为中间表，表中至少有两个字段，这两个字段作为外键分别指向两张表的主键。

多表查询有以下几种：

1. 合并结果集:UNION 、 UNION ALL

2. 连接查询

2.1内连接 [INNER] JOIN ON

2.2外连接 OUTER JOIN ON

    - 左外连接 LEFT [OUTER] JOIN

    - 右外连接 RIGHT [OUTER] JOIN

    - 全外连接（MySQL不支持）FULL JOIN

2.3 自然连接 NATURAL JOIN

3.子查询

1.合并结果集

合并结果集就是把两个 select 语句的查询结果合并到一起。被合并的两个结果的列数和列类型必须相同！

它包含两种处理方式：UNION 和  UNION ALL

二者的区别就是UNION会去除重复记录，而UNION ALL不会去除重复记录

SELECT* FROM t1 UNION SELECT * FROM t2;
SELECT * FROM t1 UNION ALL SELECT * FROM t2;

2.连接查询

连接查询就是求出多个表的乘积（笛卡尔积）。

例如：假设集合 A={a,b} ，集合 B={0,1,2} ，则两个集合的笛卡尔积为 {(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。

那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢，就是通过条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。

 

2.1内连接

内连接查询出的结果一定满足条件（和外连接对应，外连接查询出的结果并不一定满足条件）

语法：

select 列名 from 表1 inner join 表2 on 表1.列名=表2.列名 where.....（其他条件）

它等价于：

select 列名 from 表1,表2 where 表1.列名=表2.列名 and ...(其他条件)

其中表1.列名=表2.列名就是两张表中外键列的等值关系。

示例：

//查询学生表中的学生姓名和分数表中的分数 .
select name,score from student as s inner join scores as c on s.studentid=c.stuid 
等价于： 
select name,score from student as s,scores as c where s.studentid=c.stuid

当有三张表时语法为：

select 列名 from 表1
 inner join 表2 on 表1.列名=表2.列名
 inner join 表3 on 表1或表2.列名=表3.列名 
 where...

等价于：

select 列名 from 表1,表2,表3
 where 表1.列名=表2.列名 and 表1/表2.列名=表3.列名 and (其他条件)

2.2外连接

包括左外连接和右外连接，外连接的特点：查询出的结果存在不满足条件的可能。

比如当我们想要查询员工对应的部门时（员工表和部门表是两张表），我们可以使用内连接，最后显示的结果是每个员工和自己的部门。

而当我们还想要输出没有部门的员工或者没有员工的部门时，这时候内连接就不起作用了，我们就需要外连接。

语法：

//左外连
select 列名 from 主表 left join 次表 on 主表.列名=次表.列名
//右外连
select 列名 from 次表 right join 主表 on 主表.列名=次表.列名

我们可以观察到，在外连接中就有了主表和次表的区分，而主表和次表在外连接中是有严格区分的，不能像内连接一样两个表随意变换位置。

区别：主表数据全部显示，次表数据匹配显示，能匹配到的显示数据，匹配不成功的显示 null

而左外连接和右外连接的区别也仅仅是前面还是后面的表作为主表

二者流程一致，都会先查询出主表的所有记录，然后显示次表中所有满足条件的数据，不满足的条件的就显示NULL

2.3自然连接

自然连接是一种特殊的等值连接，他要求两个关系表中进行连接的必须是相同的属性列（名字相同），无须添加连接条件， 并且在结果中消除重复的属性列。

3.子查询

子查询就是在一个查询语句中包含另一个完整的查询语句，即嵌套查询。

子查询出现的位置：

• where之后，作为被查询条件的一部分。此时还可以使用any、all关键字
• from之后，作为一个表

示例：

查询工资高于张三的工资的员工信息：

第一步我们需要查询张三的工资：

SELECT sal FROM emp WHERE name='张三';

第二步我们查询高于张三工资的员工信息：

SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='张三');