转载

第七章 SQL的联结和组合查询

教程目录苦练SQL：初学者入门

在SQL的数据设计中有一个常见的规则，这个规则是为了去除冗余的数据，避免冗余的数据使用的方式把数据分开，放在不同的表格中，选择用表格的关联，用联表的方式驱动数据的联动。下面考虑如何设计学生系统，在school中已经有一张学生表。

其中数据如下：

第七章 SQL的联结和组合查询

如果要建立一张学生的成绩单，该如何来设计这张表格呢？

先来设计课程表，课程表比较简单，字段是课程名称。

执行下面的代码建立两张表格：

建立课程表格：

-- ----------------------------
-- Table structure for `courses`
-- ----------------------------
DROP TABLE IF EXISTS `courses`;
CREATE TABLE `courses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`int`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of courses
-- ----------------------------
INSERT INTO `courses` VALUES ('1', '高等数学');
INSERT INTO `courses` VALUES ('2', '大学英语');

如下数据：

第七章 SQL的联结和组合查询

这个表格比较容易理解，建立了两门课程，高等数学和大学英语。

建立分数表格：

-- ----------------------------
-- Table structure for `score`
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `course_id` int(11) DEFAULT NULL,
  `student_id` int(11) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`int`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('1', '1', '1', '78');
INSERT INTO `score` VALUES ('2', '1', '2', '67');
INSERT INTO `score` VALUES ('3', '1', '3', '82');
INSERT INTO `score` VALUES ('4', '1', '4', '76');
INSERT INTO `score` VALUES ('5', '1', '5', '90');
INSERT INTO `score` VALUES ('6', '1', '6', '88');
INSERT INTO `score` VALUES ('7', '2', '1', '77');
INSERT INTO `score` VALUES ('8', '2', '2', '85');
INSERT INTO `score` VALUES ('9', '2', '3', '65');
INSERT INTO `score` VALUES ('10', '2', '4', '66');
INSERT INTO `score` VALUES ('11', '2', '5', '70');
INSERT INTO `score` VALUES ('12', '2', '6', '75');

表格数据如下：

第七章 SQL的联结和组合查询

在 student 中每个学生都有一个id，用这个id关联到score 的student_id，course 表中同样的每一门课程都有一个id，同样的用课程id关联到score的course_id。

为什么要这么关联？

像刚才讲的那样，为了避免一些数据的冗余和数据联动。如果不用这样设计的话在score中，需要把学生的姓名和课程的名字都写进去，这样在平时的操作中是不会出现问题的，但在一些特殊的情况下就很不适用了，在极端的情况下如果有个学生的名字改变了那需要修改几张表呢？在这里需要修改两张表 stuudent 和 score ，还能忙的过来，如果系统较为复杂，那工作量就大了，或者另一种情况是学校的两个学生名字是一样的，score是以名字为表示的，那到底是标记哪个学生呢？

这种去冗余的数据库设计方式，在学生系统的设计中是非常合适的，方便数据的驱动。

表联结

通过表格的联结，可以联结两张表进行查询，两张表的关联通过关联键进行连接，它是这两个表的联结关系。

如下要查询所有同学的大学英语成绩，SQL为：

SELECT A.`name`,'英语',B.score FROM `students` A, score B
where A.id = B.student_id and B.course_id = 2

两张表还是放在 from 后面，用A,B来表示students 和 score ，通过students 的id和score的student_id建立联系，在连表中所有的字段名称前面需要加上表名来区分是从哪张表。

结果如下：

第七章 SQL的联结和组合查询

查询了两个表，students和score ，分别取名为A表和B表，可以通过where用A.id = B.student_id 建立两个表的关系，这里我们默认 course_id = 2 为英语。

通过相同的规则，可以把三个表通过关系联结起来，查询所有的成绩，这三个表的数据都可以显示，这里显示我们感兴趣的字段。

SELECT A.`name`,C.`name`,B.score FROM `students` A, score B,courses C
where A.id = B.student_id  and B.course_id = C.id

显示的结果为：

第七章 SQL的联结和组合查询

使用关键字 ORDER BY 可以对字段进行排序：

SELECT A.`name`,C.`name`,B.score FROM `students` A, score B,courses C
where A.id = B.student_id  and B.course_id = C.id ORDER BY B.score desc

排序的结果如下：

第七章 SQL的联结和组合查询

可以用一个语句的结果用来做过滤的条件，跟连表相似，目的是引入了另一张表的内容；

SELECT A.`name`,'大学英语',B.score FROM `students` A, score B
where A.id = B.student_id and B.course_id in (SELECT id FROM courses where name = '大学英语')

用 SELECT id FROM courses where name = ‘大学英语’ 得出大学英语的 courses 的 id，用这个条件来过滤，结果如下：

第七章 SQL的联结和组合查询

这种联结方式成为内联结，联结的两个表通过关系进行联结，如果其中一方没有数据，则不显示，还有其他几种方式：

外联接

LEFT JOIN或LEFT OUTER JOIN

左向外联接的结果集包括 LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。
RIGHT JOIN 或 RIGHT OUTER JOIN

右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。
FULL JOIN 或 FULL OUTER JOIN 完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。

组合查询

组合查询的关键字为union，相当于并集。用来连接连接两个相同结果的查询语句。

select * from students where age in (20,21)
UNION 
select * from students where tel LIKE '13%'

查询结果为：

第七章 SQL的联结和组合查询

在处理表联结时需要清楚表跟表之间的联结关系是什么，通过哪个键关联的，这是处理过程的基础，通过不同的需求要决定使用的是内联还是外联，不同的连接方式得到的结果不相同，在考虑通过引入另外一张表数据时可以把一个 select 的结果当成过滤条件对另一个语句进行过滤，相当于一个交集，而 UNION 相当于对结果的一个合并，都是多表操作经常用到的。

此文转自 http://bugingcode.com/sql_start/ex7.html

更多课程和文章尽在微信号：「datartisan数据工匠」

第七章 SQL的联结和组合查询