数据库SQLyog之多表查询

创建表

我们先创建三个表，之后的查询均以这三个表为例

根据如图所示的字段及属性建立三张表，建立过程省略。注意sno,cno,在每张表中的格式要一致，就是我们所说的学号和课程号是固定的，一个学生只能有一个学号，一个课程只能有一个课程号。
插入后的数据格式如下（可以自行改变）：
course：

score表：

student表：

表和表之间的对应关系

• 一对一
  在一对一关系中，关系表的每一边都只能存在一条记录，每个数据表中的关键字在 对应的关系表中只能存在一条记录或者没有对应的记录。这种关系类似于现实生活中配 偶的关系，如果一个人已经结婚，那么只有一个配偶，如果没有结婚，那么没有配偶。
  假设我们已经创建如图所示的两张表
  

从图6.1中可以看到，user表和user_text表是一对一的关系。此处使用一对一的意 义实际上是数据库优化，用户备注字段utext–般有比较多的文字，属于大文本字段，但 这个字段又不是每次都要用到，如果存放到user表中，在查询用户数据的时候会影响user表的查询效率，因此将utext字段单独拆分出来，放到从表中，当需要utext字段时 进行两张表的关联查询即可。

• 一对多和多对一
  在一对多关系中，主键数据表中只能含有一个记录，而在其关系表中这条记录可以 与一个或者多个记录相关，也可以没有记录与之相关。这种关系类似于现实生活中父母 与子女的关系，每个孩子都有一个父亲，但一个父亲可能有多个孩子，也可能没有孩子。 多对一是从不同的角度来看问题，例如从孩子的角度来看，一个孩子只能有一个父亲， 多个孩子也可能是同一个父亲。
  假设创建如图所示的表：
  

从图6.2中可以看到，student表和score表是一对多的关系，每个学生可能有多个 成绩，但一个成绩只能属于一个学生，这就是一对多的关系。如果从score表来看问题, 多个成绩可以属于一个学生，但一个成绩不能属于多个学生，这就是多对一的关系。

• 多对多的关系
  在多对多关系中，两个数据表里的每条记录都可以和另一个数据表里任意数量的记 录相关。这种关系类似于现实生活中学生与选修课的关系，一个学生可以选择多门选修 课，一门选修课也可以供多个学生选择。
  假设已创建如下图所示的表：
  

从图6.3中可以看到，teacher表和stu表都与中间表tea stu关联，且都是一对多的关系，因此teacher表和stu表是多对多的关系，即一个老师可以有多个学生，一个学生 也可以有多个老师，这就是多对多关系的应用场景。

合并结果集

• 使用UNION关键字合并
  在多表查询中，有时可能需要将两条查询语句的结果合并到一起，MySQL提供了 UNION关键字用于合并结果集。
  （注意：UNION只能合并两个表中相同的字段及类型，对于两个表中不同的字段UNION将无法使用）
  例：

SELECT tname FROM student UNION SELECT tname FROM course 

结果：

由合并结果可知，两表中均有小明和小胡，但是合并后自动过滤掉了重复的数据。

• 使用UNION ALL 合并
  前面学习了 UNION关键字的用法，UNION ALL关键字与之类似，但使用UNION ALL关键字查询出两张表的数据合并结果集后不会过滤掉重复的数据。
  例：

SELECT tname FROM student UNION ALL SELECT tname FROM course 

可以看到并没有过滤掉重复部分。

连接查询

在关系型数据库中建立数据表时不必确定各个数据之间的关系，通常将每个实体的 所有信息存放在一个表中。当两个或多个表中存在相同意义的字段时，便可以通过这些 字段对不同的表进行连接查询。

• 笛卡尔积
  笛卡儿积在SQL中的实现方式是交叉连接(cross join),所有连接方式都会先生成 临时笛卡儿积表。笛卡儿积是关系代数里的一个概念，表示两个表中的每一行数据任意 组合。接下来通过一个案例演示笛卡儿积问题，此处使用交叉査询来演示，其语法格式 如下。

SELECT 査询所属表字段 FROM 表 1 CROSS JOIN 表 2;

例：

SELECT s.Sno,s.Sname,ss.Cno,ss.Degree FROM score ss CROSS JOIN student s 

（score ss,是给score字段起别名，方便书写用的）
通过查询我们可以查询到两个表的数据组合，但这并没有实际意义，因为得到的数据是随意排列的。
只有加上限制条件才会有实际意义。
例：

SELECT s.Sno,s.Sname,ss.Cno,ss.Degree FROM score ss CROSS JOIN student s 
WHERE ss.sno=s.sno

需要两个表中有相同的字段类型，这样才能建立关系来加上限制条件。
结果如图：

可以看到此时查询的数据已经有了对应关系

内连接

内连接的连接杳询结果集中仅包含满足条件的行，在MySQL中默认的连接方式就是内连接。前面学习了交叉连接的语法，但该语法并不是SQL标准中的查询方式，可以 理解为方言。SQL标准中的内连接的语法格式如下。

SELECT 査询所属表字段 FROM 表 1 [INNER] JOIN 表 2
ON表1.关系字段=表2 .关系字段      WHERE查询条件；

在以上语法格式中，INNER JOIN用于连接两个表，其中INNER可以省略，因为 MySQL默认的连接方式就是内连接，ON用来指定连接条件，类似于WHERE关键字。
例：查询一名学生的课程名、课程号、成绩。

SELECT c.Cno,c.Cname,s.Degree FROM course c JOIN score s 
ON c.`Cno`=s.Cno

分析，该查询 要求查询课成名，课称号以及成家，分别分布在score表和course表中，通过观察两个表中有相同字段CNO，这样我们就可以建立关系来实现内连接。
查询结果：

外连接

检面讲解了内连接的查询，返回的结果只包含符合査询条件和连接条件的数据，然 而有时还需要包含没有关联的数据，返回的查询结果中不仅包含符合条件的数据，还包 含左表或右表或两个表中的所有数据，此时就需要用到外连接查询。外连接查询包括左 外连接和右外连接两种查询类型，接下来进行详细讲解。

• 左外连接
  左外连接是以左表中的数据为基准，若左表中有数据且右表中没有数据，则显示左表中的数据，右表中的数据显示为空。左外连接的语法格式如下。

SELECT 查询字段 FROM 表 1 LEFT [OUTER] JOIN 表 2
ON表1.关系字段=表2 .关系字段WHERE查询条件；

例：
查询学生的姓名，学号，成绩，所学课程的课程号

SELECT stu.Sno,stu.Sname,s.Degree,s.Cno FROM student stu LEFT JOIN score s 
ON stu.Sno=s.Sno

结果：

其中student为左表，所以最后一行显示左表的数据而右表的数据为空。

• 右外连接，和左外连接相似，只是右外连接显示多余的右表中的数据，左表中没有的数据显示为空。
  格式：

SELECT 查询字段 FROM 表 1 RIGHT [OUTER] JOIN 表 2
ON表1.关系字段=表2 .关系字段WHERE查询条件；

例：

SELECT stu.Sno,stu.Sname,s.Degree,s.Cno FROM student stu RIGHT JOIN score s 
ON stu.Sno=s.Sno

结果：

多表连接

前面学习了内连接和外连接，它们都是两张表之间的连接查询。实际上随着业务的 复杂，可能需要连接更多的表（3张、4张甚至更多），但表若连接过多会严重影响查询 效率，因此连接查询一般不超出7张表的连接。多表连接的语法格式如下。

SELECT査询字段FROM表1 ［别名］
JOIN表2 ［别名］ON 表1 .关系字段=表2 .关系字段  JOIN 表 m  ON—;

多表的连接方式可以类比内连接，内连接的建立需要两个表中有相同的字段及类型这样才能建立关系。而多表，比如3个表中，若要建立起关系，就需要三个表有相同的字段或者至少有一个表包含其他两个表的字段。
比如我们的例子中，score表中既有student表的Sno，又有course表中的cno,所有就可以通过score这个表将三个表联系起来。
例：
查询一个学生的姓名、出生年月、所学课程名称、成绩。

SELECT stu.Sname,stu.Sbirthday,s.Degree,c.Cname FROM student stu  JOIN score s 
ON stu.Sno=s.Sno JOIN course c ON s.Cno=c.Cno

结果：

自然连接

前面学习了表的连接查询，需要指定表与表之间的连接字段。在SQL标准中还有一种自然连接，不需要指定连接字段，表与表之间列名和数据类型相同的字段会被自动匹 配。自然连接默认按内连接的方式进行查询，语法格式如下。

SELECT查询字段FROM表1 ［别名］NATURAL JOIN表2 ［别名］;

在以上语法格式中，通过NATURAL关键字使两张表进行自然
连接，默认按内连接 的方式进行查询。
例：

SELECT stu.Sname,stu.Sbirthday,s.Cno FROM student stu NATURAL JOIN  score s

结果：

子查询

子查询就是嵌套査询，即在SELECT中包含SELECT,子查询可以在WHERE关键字后面作
为査询条件，也可以在FROM关键字后面作为表来使用。
例1：
查询学生出生日期大于1997年的所有信息：

SELECT * FROM student 
WHERE YEAR(Sbirthday)> YEAR((SELECT Sbirthday FROM student WHERE YEAR(Sbirthday)=1997))

该语句会先执行括号里的语句，及查询出生日期在1997年----的学生，然后利用year函数转化为整型后再执行下一个查询语句并执行where条件。
结果：

例2：查询和姓名为“li”同学同一个班级同学的所有信息

SELECT * FROM student 
WHERE class= (SELECT class FROM student WHERE Sname ='li')

子查询作为表

前面讲解了将子查询作为査询条件来使用，子査询还可以作为表来使用，即把 SELECT子句放在FROM关键字的后面。在执行查询语句时，首先会执行子查询中的语句，然后将返回结果作为外层查询的数据源使用。
例：
查询学号为103学生的姓名，性别，成绩，以及所学课程的课程号

SELECT stu.Sname ,stu.Ssex,s.Cno,s.Degree FROM student stu,(SELECT Cno,Sno, Degree FROM score) s
WHERE  stu.Sno=s.Sno AND stu.Sno='103'

结果：

但是这样不如直接用内连接方便，其实原理是是相似的