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

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

其中数据如下:

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

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

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

建立课程表格:

-- ----------------------------
-- 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', '大学英语');

如下数据:

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

建立分数表格:

-- ----------------------------
-- 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');

表格数据如下:

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

为什么要这么关联?

像刚才讲的那样,为了避免一些数据的冗余和数据联动。如果不用这样设计的话在score中,需要把学生的姓名和课程的名字都写进去,这样在平时的操作中是不会出现问题的,但在一些特殊的情况下就很不适用了,在极端的情况下如果有个学生的名字改变了那需要修改几张表呢?在这里需要修改两张表 stuudentscore ,还能忙的过来,如果系统较为复杂,那工作量就大了,或者另一种情况是学校的两个学生名字是一样的,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来表示studentsscore ,通过students 的id和scorestudent_id建立联系,在连表中所有的字段名称前面需要加上表名来区分是从哪张表。

结果如下:

查询了两个表,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

显示的结果为:

使用关键字 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 

排序的结果如下:

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

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 = '大学英语' 得出 大学英语coursesid,用这个条件来过滤,结果如下:

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

外联接

  • 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%'

查询结果为:

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


所有评论

写了这么多年博客,收到的优秀评论少之又少。在这个属于 SNS 的时代也并不缺少向作者反馈的渠道。

还没有评论

撰写评论