MySQL_表_进阶(1/2)

我们的进阶篇中，还是借四张表，来学习接下来最后关于表的需求，以此完成对表的基本学习。

照例给出四张表：

 学院表：(testdb.dept)

课程表：(testdb.course)

选课表:（testdb.sc）

学生表 :（testdb.stu）

需求如下：

1. 查询课程编号为“1001”的课程的学生成绩单，显示学号、姓名、成绩，按成绩降序排列。
2. 查询没有人选课的课程信息，显示课程编号、课程名称。

走起：

 需求一——降序/升序排列

看到需求一：查询课程编号为“1001”的课程的学生成绩单，显示学号、姓名、成绩，按成绩降序排列。

看第一眼，诶，指定查询：要么嵌套查询，要么单连接... 但最后这句“按成绩降序排列” ，我们是没提到过的。

降序/升序排列

ORDER BY 字段名 DESC/ASC;

#DESC 表示 降序
#ASC 表示 升序

为了使记忆具有完整性： DESC 是descending(下降)的缩写，根据词根词缀法，“de”往往表示“否定、向下”的含义，“cend”表示“行走”，desc就是向下走——降序

ASC是ascending(上升)的缩写。记住了de表示“否定、向下”也就记住了ASC是升序。

那么，这里按成绩排序，这句排序代码应该这样写：

ORDER BY score DESC;

 需求分析：

查询课程编号为“1001”的课程的学生成绩单，显示学号、姓名、成绩...

SELECT stuid,stuname,score
#显示学号、姓名、成绩...

显示什么字段，针对同名但不同表字段，我们还需要加前缀，否则会因指向不明确而报错。像stuname这种字段，只有stu（学生表）才有的，不用加前缀

SELECT sc.stuid,stuname,score

继续： 

SELECT sc.stuid,stuname,score
FROM testdb.sc

因为还涉及显示stu(学生表)的stuname字段信息，所以，我们还需要联一张表。 —— 使用JOIN...ON...语句

SELECT sc.stuid,stuname,score
FROM testdb.sc
#连接stu表
JOIN testdb.stu
#两张表都有同一字段，使用 = 连接
ON stu.stuid = sc.stuid 

对于stuid应该还有条件：查询课程编号为“1001”的课程的学生成绩单。没错，就是要求筛选出来的stuid对应在同表的cid = ‘1001’。 这条cid = '1001'与两表连接条件是需要同时满足的。前者，是对同表的stuid进行限制，后者，是为了查询到对应stuid的stuname。

所以：这条需求的参考代码如下

#查询课程编号为“1001”的课程的学生成绩单，显示学号、姓名、成绩，按成绩降序排列。
SELECT sc.stuid,stuname,score
FROM testdb.sc
JOIN testdb.stu
ON stu.stuid = sc.stuid AND sc.cid = '1001'
ORDER BY score ASC;

需求二——左右连接

需求二：查询没有人选课的课程信息，显示课程编号、课程名称。

需求分析：

这次，我们先来写个整体，细节不会的再根据实际情况去补充、完善。

#显示课程编号、课程名称。
SELECT sc.cid,course.cname
FROM testdb.sc
JOIN testdb.course
ON 
#显示的字段因为来自不同的表，所以需要连接表：因为sc,c表都有cid这个字段，所以显示的字段需要前缀
#其他字段，也可写前缀，提醒自己使用了哪些表的字段
#重点就落在ON 后面了

ON后面连接 查询条件，因为要显示的没有人选课的课程信息，注意是课程信息。语义上理解：其实最好选择的，是c表，其次从代码上：显示的cid,cname 字段c表都拥有。

SELECT sc.cid,course.cname
FROM testdb.course
JOIN testdb.sc
ON 

但是，这样写，我们的查询条件该怎么表达：“没有人选课”。

其实，默认的JOIN连接，只会返回两个表中有匹配的行。故而，为了查询没有人选课的课程信息，即需要找出在testdb.sc（选课表）中没有对应记录的testdb.course（课程表）中的课程。

默认的JOIN连接是做不到的，我们需要左/右连接。（前面说着写整体的代码，其实只是虚晃一枪哈，别被我带进沟里了哈哈哈哈哈哈）

左/右连接

关键语句：

LEFT JOIN 表名
ON 查询条件
#其实就只是在JOIN前加上LEFT/RIGHT

我们前面的显示字段的代码并没有发生改变：

SELECT course.cid,course.cname
FROM testdb.course
LEFT JOIN testdb.sc
ON
#不是有左/右连接两种连接嘛，那到底是用LEFT 还是 RIGHT呢？

 LEFT JOIN表示testdb.sc ON c.cid = sc.cid 表示将 testdb.c 表（左表）与 testdb.sc 表（右表）进行左连接。

左表、右表跟是否左连接，还是右连接并无关系，出现在显示语句后的FROM 的表（先出现的表）我们称为左表，而后面连接的表，我们则称为右表。

那左右连接中的“左右”指的是什么？左连接，意思是以左表为主，右表是作为查询条件存在；同样的道理，右连接，是将右表作为主表，左表自然成为查询条件。

怎么理解主表与作为查询条件的表的关系，同样两个表，左右连接的结果区别在哪呢？

咱们举个例子：比如这里course表(课程表)是左表，sc(选课表)是右表，此时代码是LEFT JOIN (左连接)，揭示出左表(course表)是主表。

SELECT course.cid,course.cname
FROM testdb.course
LEFT JOIN testdb.sc
ON

对于 testdb.course 表中的每一行(主表)，查询都会尝试在 testdb.sc 表中找到一个 cid 值与之匹配的行。

如果在 testdb.sc 表中找到了匹配的行，那么这两行就会被组合成一个结果行，包括来自两个表的所有列（但在这个查询中，我们只选择了 course.cid 和 course.cname）。

如果在 testdb.sc 表中没有找到匹配的行，那么结果行仍然会包括 testdb.c 表中的那行数据，但来自 testdb.sc 表的列将以 NULL 值填充。

我自己理解这个主表：左表（在这个例子中是testdb.course）的所有行都会被包含在结果集中，无论它们在右表（testdb.sc）中是否有匹配的行。

若右表记录行数比左表多，结果集的行数与左表的行数相同，主表为主的原则是不变的。

所以，这里，我们把课程表作为左连接：查询条件有两句：连接（两表）语句 和 筛选出 sc.cid 没有的值。

SELECT course.cid,course.cname
FROM testdb.course
LEFT JOIN testdb.sc
ON course.cid = sc.cid
# 按以前的习惯我们会用and再写个并列条件，这里该怎么表示course.cid有的cid但是sc.cid没有

如果，代码到这儿，LEFT JOIN会生成一个结果集，它含了左表的所有行以及右表中匹配的行（或NULL值，如果右表中没有匹配的行）

而接下来，我们再针对这个特点进行筛选，从左连接生成的结果集可知，如果 左表cid为 CS0017，CS1038 ... 对应的，在右表里并没有相关的选课记录，其字段cid,stuid,score全应为NULL（如果要求显示的话，就可观察到这个结果）

 选课表:（testdb.sc）

 课程表：(testdb.course)

WHERE子句——过滤结果集

这时候，我们再对这个临时的结果集，进行筛选，减少结果集的行数：（使用WHERE子句） 

WHERE sc.cid IS NULL;
#这句WHERE实际上是对前面LEFT JOIN产生的结果集进行筛选
#LEFT JOIN确保了testdb.course表中的所有行都出现在结果集中，而WHERE sc.cid IS NULL则进一步过滤掉了那些在testdb.sc表中有匹配项的课程，留下了没有被选的课程信息

由此，得到我们的需求二：

#查询没有人选课的课程信息，显示课程编号、课程名称。
SELECT course.cid,course.cname
FROM testdb.course
LEFT JOIN testdb.sc
ON course.cid = sc.cid
WHERE sc.cid IS NULL;

 运行一下：

关于右连接：其实就是把右表作为主表，结果集里面包含了右表的所有行，无论左表是否匹配。无法匹配就NULL；（主表有点霸总那味儿了） 

 小总结：参考代码

1. 查询课程编号为“1001”的课程的学生成绩单，显示学号、姓名、成绩，按成绩降序排列。
2. 查询没有人选课的课程信息，显示课程编号、课程名称。

参考代码：

#查询课程编号为“1001”的课程的学生成绩单，显示学号、姓名、成绩，按成绩降序排列。
SELECT sc.stuid,stuname,score
FROM testdb.sc
JOIN testdb.stu
ON stu.stuid = sc.stuid AND sc.cid = '1001'
ORDER BY score ASC;

#查询没有人选课的课程信息，显示课程编号、课程名称。
SELECT course.cid,course.cname
FROM testdb.course
LEFT JOIN testdb.sc
ON course.cid = sc.cid
WHERE sc.cid IS NULL;

---

 今天就到这里吧，明天就只还有俩了（高兴）