MYSQL————联合查询

联合多个表进行查询

设计数据时把表进行拆分，为了消除表中字段的依赖关系，比如部分函数依赖，传递依赖，这时会导致一条SQL查出来的数据，对于业务来说是不完整的，我们就可以使用联合查询把关系中的数据全部查出来，在一个数据行中显示详细信息

两张表建立主外键关系

这是两张表，而在实际生活中，我们想要的是将两张表合并，去掉不需要的部分

列入下面这张表：

select student.id 学生编号,student.name 学生姓名,class.name 班级名称 from student,class where student.class_id=class.id;

 上面这段代码就是一句简单的联合查询

---

1.联合查询时MYSQL是如何执行的

1.1取多张表的笛卡尔积

语法：

select *from 表名1，表名2;

对多张表进行笛卡尔积的过程

1.先从第一张表中取一条记录，然后再与第二张表中的第一条记录进行组合，生成一条新的记录

2.先从第一张表中取一条记录， 然后再与第二张表中的第二条记录进行组合，生成一条新的记录

以此类推，将第一张表的每一条记录都与第二张表的每一条记录都组合一遍，生成新的记录

最后得到的结果就是一个全排列结果集

通过观察，两张表取笛卡尔积之后，有些数据是无效数据

如何过滤这些无效数据呢？ 

---

1.2通过连接条件过滤掉无效数据

在创建两张表时，我们在表中设置了主外键（即每一张表中的班级编号），故此，我们可以判断两张表的班级编号是否相等（也就是主外键是否相等）来过滤掉无效的数据

class_id在两张表中都存在，MYSQL分不清当前语句中的class_id来自哪一个表，我们可以通过表名.列名的方法来解决这个问题

通过判断主外键是否相等来达到过滤无效数据的效果

---

1.3能够指定列查询，来精减结果集

在第二步时，查询出来的表还达不到我们想要的效果，即我们不想要class_id和id列出现

查询列表中通过表名.列名的方式指定要查询的字段

 select student.id,student.name,class.name from student,class where student.class_id=class.id;

在1上面的代码中，我们发现每次要指定哪个表中的哪一个列时，都需要写一遍表名，然后在点上列名，当表明很长时就很麻烦，这是我们可以给表名起别名来简化SQL语句

给表起完别名后，直接使用别名.列名就可以指定想要的列了

---

在上述的操作中，好有一个不完美的地方，就是班级名和学生姓名都用的name，那么我们可以给列取别名来达到一个区分的效果

 联合查询也叫做表连接查询

1.首先确定哪几张表要参与查询

2.根据表与表之间的主外键关系，确定过滤条件

3.精减查询字段，得到想要的结果

2.内连接

语法：

标准的内连接写法：

select 字段 表1 别名1[inner]join 表2 别名2 on 连接条件 and其他条件;

上面使用的方式：

select 字段from 表1 别名1，表2 别名2 where连接条件 and 其他条件;

3.外连接

外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示就是左外连接，右侧的表完全显示就是右外连接

语法：

select 字段 from 表名1 left/right join 表名2 on 条件 and其他条件;

示例：

当使用左外连接时：

以left join左边的表为基准，将满足条件的左表中的记录以及他们对应右表中的记录都打印出来了 

但使用右外连接时：

 由于右表class表有一班，二班，三班，四班，而四班在student表中没有记录，所以当使用右外连接时，打印右表中所有满足条件的记录，而左表中没有与右表对应的记录将会被null代替

注意：MYSQL中不支持全外连接full join

4.自连接

自己和自己进行表连接

可以把行转化为列，在查询的时候可以使用where条件进行过滤，也就是说可以实现行与行之间的比较功能

---

示例：在下面这个表中，我们要找出数学成绩高于英语成绩的学生

1.缺定涉及的表

scores 表

2.取笛卡尔积

 

在去笛卡尔积时，两张表的表名都为scores，这时我们需要给这两张表取一个别名

3.设置连接条件，也就是两张表的id要相等

4.设置过滤条件，即数学成绩大于英语成绩

5.将不需要的列去掉

5.子查询

子查询也叫做嵌套查询

子查询是把一条SQL的查询结果，当做另一条SQL的查询条件，可以嵌套很多层

可以看出子查询是由很多条SQL语句组成的，也可以把子查询拆分成一条一条单独的语句取执行

最后再把结果和条件拼装在一起，得到查询结果

由于嵌套查询的层数是没有限制的，如果多层嵌套查询效率是不可控的，需要谨慎使用

---

5.1单行子查询：

示例：

在下列表中查询与孙悟空同一个班的学生

1.参与查询的表

只有一个学生表

2.先查出孙悟空的班级编号

3.在学生表中查出与‘孙悟空’的同班同学，条件是与‘孙悟空’班级编号相同的的所有学生信息

上面这个结果集是我们想要的

4.把查询条件的1用子查询去处理

5.对整体加过滤条件

过滤掉孙悟空这一条记录

子查询只是把单独的SQL拼装在一起而已

单行子查询：返回一行记录的子查询（返回的是一个对象）

---

5.2多行子查询

多行子查询：返回多行记录的子查询（返回的是一个集合，集合中包含多个对象）

语法：

select*from table1 where table1.id in (select id from table2 where xxx=....);

示例：查询语文或英语课程的成绩信息

1.涉及哪些表

课程表和成绩表

2.在课程表中获取语文和英语课程的编号

3.根据获取的课程id，在成绩表中查询对应课程的分数

4.把以上分步查询的SQL语句拼装起来，变成子查询

查询重复的分数

可以使用分组查询的方式

1.同一个学生，同一门课程，同样的成绩，按这三个列同时去分组

2.分组之后在having子句中，用count（*）判断分组中的记录数

加入外层查询

外层查询中的条件字段，与内层查询中的结果，一一做比较，如果相等则满足条件，其中只要有一个不相等则不满足条件

[not]exists关键字

语法：

select *from 表名 where exists(select*from 表名1);

exists后面括号中的查询语句，如果有结果返回，则执行外层的查询，如果没有结果返回，则不执行外层的查询

内层查询返回非空结果集

exists相当于if语句的判断条件，有结果返回true，没有结果返回false

内层查询返回空结果集

外层也返回空结果集，也可以说外层查询没有执行

特殊情况：

返回的结果集是一个非空的，只不过列名为null，值也为null

6.合并查询

将表结构相同的表的查询结果，合并多个查询结果到一个结果集中

关键字：

union	去除重复的记录
union all	保留重复的记录

示例：将id小于5和数学成绩大于80的学生记录合并

使用union关键字 

使用union关键字，去掉了重复的记录

---

使用union all关键字 

使用union all关键字将所有的两次查询的记录都打印了出来，并没有进行去重操作 

除此之外，当两张表的字段结构相同时，也可以使用合并查询（不需要表的所有结构都相同）