MySQL数据库基础（笔记整理四）

多表查询

当你打算从多个数据表选取信息时，有一种方法叫做联结(join)。把一个数据表与另一个数据表中的信息结合起来才能得到查询结果。联结操作是通过并把两个（或多个）数据表里的同类数据进行匹配而完成的。多表操作的另一种方法是将SELECT语句嵌套在另一个SELECT语句里，前者叫做子查询。例如：

mysql> SELECT student.name,grade_event.date, score.score, grade_event.category
-> FROM grade_event INNER JOIN score INNER JOIN student
-> ON grade_event.event_id = score.event_id
-> AND score.student_id = student.student_id
-> WHERE grade_event.date = '2018-10-07' ;

这条查询先查出给定日期的grade_event行，再利用此行里的event_id 把score数据表里拥有同一event_id 的考试分数都查出来，并且把score和student数据表里都有student_id的数据列查出来，利用这个关系把学号映射为他们的姓名。每找到一组彼此都匹配的grade_event行和score行，score行和student行，就把学生的姓名、考试分数、日期和考试事件的类型显示出来。这条查询与单表查询相比：

• 在FROM子句里，我们列举出了多个数据表的名字，因为我们要从多个数据表里检索数据


• 在ON子句里，我们给出了grade_event数据表和score数据表，score数据表和student数据表的联结条件，这grade_event和score数据表里的event_id 列的值必须相互匹配（并且 为了消除歧义，这里对event_id进行了完全限定)，而score和student数据表里的student_id列的值必须相互匹配。

换一种情况，加入现在有一个表是记录学生们的考试缺考情况（absence表），如果你想看到缺考的学生信息，就需要把absence表与student表通过student_id 列的值联结起来。例如：

mysql> SELECT student.student_id, student.name,
-> COUNT(absence.date) AS absences
-> FROM student INNER JOIN absence
-> ON student.student_id = absence.student_id
-> GROUP BY student.student_id;

上面的这条查询对于想知道都有哪些学生缺考的情况，查询结果正是我们需要的。但假如你需要对所有学生的考生出勤情况做一个统计，（即需要同时显示出没有缺考的学生信息）那么我们就需要用LEFT JOIN来代替普通的联结操作。 LEFT JOIN 将使MySQL 为联结操作中第一个数据表（即关键字LEFT JOIN左边的数据表)里的每一个中选数据行生成一个输出行。上例可以修改为：

mysql> SELECT student.student_id, student.name,
-> COUNT(absence.date) AS absences
-> FROM student LEFT JOIN absence
-> ON student.student_id = absence.student_id
-> GROUP BY student.student_id;

上面这条查询，不仅可以列出缺考的学生的信息，还会返回没有缺考的学生的信息，并且缺考次数统计为0 。

RIGHT OUTER JOIN ....ON 即为右联结，与左联结正好相反，用法相同。其实对左联结来说，将表的顺序交换就是右联结。还有全联结（全外联结），但MySQL不支持此种联结方式。

联结操作并非只能作用于不同的数据表，你可以把某个数据表和它自身结合起来。例如：你想知道在student表中是否有两个同学籍贯相同。

mysql> SELECT s1.name, s1.city, s1.state FROM student AS s1 INNER JOIN student AS s2
-> ON s1.city = s2.city AND s1.state =s2.state
-> WHERE (s1.name != s2.name)
-> ORDER BY state, city , name;

这条查询有两个需要注意的地方：

• 它需要两次用到同一个表，所以我们必须为它创建两个别名（s1 和s2）才能把表中的同名数据列区分开来。


• 用WHERE子句来确保每名学生的记录只能与其他学生相匹配，来剔除“记录与它本身相匹配”的情况。

MySQL认为任何一个查询都是一次“关联” ，并不仅仅是一个查询需要两个表匹配才叫关联，所以在MySQL中，每一个查询，每一个片段（包括子查询，甚至是基于单表查 询）都可以是一次关联。MySQL执行关联的策略很简单：MySQL对任何关联都执行嵌套循环关联操作。即MySQL先在一个表中循环取出单条数据，然后在嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中的匹配行为止。然后根据各个表匹配的行，返回查询需要的各个列。

多表检索的另一类方法，嵌套查询

嵌套查询也就是它一条SELECT语句嵌套在另一条里。例如：

mysql> SELECT * FROM student WHERE student_id
-> NOT IN(SELECT student_id FROM absence);

上面的这条查询把全体没有缺勤的学生找出来。嵌套与内层的SELECT语句用来生成一个absence数据表里出现过的student_id 值的集合，外层的SELECT语句负责把与该机合理的任何一个ID值都不匹配的student数据行检索出来(NOT IN)。

2018/10/14 MySQL数据库基础笔记整理（四）