SQL语言课内部分数据查询

1. 嵌套查询课件 3.4.3一个SELECT里再套一个SELECT外层的叫父查询里层的叫子查询。写嵌套查询先问两件事。第一件内层的返回形状——形状决定外层能用哪个谓词。内层返回什么形状外层能用的谓词一个值一行一列标量比较运算符、、、、、!一列值一列多行一个集合IN、ANY、ALL有没有结果只回答有 / 没有不返回数据EXISTS/NOT EXISTS一整张表多行多列放进FROM里当表用见 §3第二件内层要不要靠外层才能算——这决定它什么时候被执行。不相关子查询内层不引用外层的列自己就能跑。DBMS 先把它算一次把结果当常量用。相关子查询内层引用了外层的列写法上内外用到同一张表就得起别名区分如SC x和SC y靠y.Sno x.Sno把两层连起来。内层自己跑不了——外层每取出一行就把这一行的值代进内层算一次效果等于两层嵌套循环。别名x、y叫相关名。起别名的原因很实际内外层是同一张表不区分就没法说清我这个 Sno 指的是外层那一行的还是内层这一行的。1.1 带 IN 谓词的子查询-- [例] 查询选修了课程名为信息系统的学生学号和姓名SELECTSno,Sname-- ③ 最后在 Student 关系中取出 Sno 和 SnameFROMStudentWHERESnoIN(SELECTSno-- ② 然后在 SC 关系中找出选修了 3 号课程的学生学号FROMSCWHERECnoIN(SELECTCno-- ① 首先在 Course 关系中找出信息系统的课程号假设为 3FROMCourseWHERECname信息系统));内层返回什么一列值多行——② 返回的是选了 3 号课的所有学号这一列可能有几十行。外层怎么用它对Student的每一行问一句我的Sno等于这一列里的某一个吗是就留下。所以IN不要求内层只返回一行返回多少行都行。这两层都是不相关子查询内层没有引用外层的列可以先算 ①、再算 ②、最后算 ③。写法上就是从最里层往外写先把最明确的条件Cname 信息系统定下来再一层层往外套。两条硬规则内层的列数必须是一列写成SELECT Cno, Cname会报ERROR 1241 Operand should contain 1 column(s)内层返回 0 行时IN的结果是假外层这一行不留下不报错。1.2 带比较运算符的子查询-- [例] 查询与刘晨同在一系的学生学号和姓名SELECTSno,Sname,SmajorFROMStudentWHERESmajor(SELECTSmajorFROMStudentWHERESname刘晨);内层返回什么一个值一行一列。这里Sname 刘晨只会有一个人所以内层只返回一个Smajor。外层怎么用它这个值就当成一个常量用Smajor (子查询)和Smajor CS是同一种比较只不过那个CS是临时查出来的。所以能不能用比较运算符取决于内层是不是只返回一个值内层返回结果恰好一行一列正常比较两行以上报错ERROR 1242 Subquery returns more than 1 rowDBMS 不知道拿哪一个来比零行子查询的值当作NULL的结果是 UNKNOWN这一行不满足条件不报错最后一行是常考的点查与不存在的人同系的学生结果不是报错而是一行都查不出来。相关子查询内层每行重算一次-- [例] 查询每个学生超过自己选修课程平均成绩的课程号SELECTSno,CnoFROMSC xWHEREGrade(SELECTAVG(Grade)FROMSC yWHEREy.Snox.Sno);把内层看成一个带参数的查询参数就是外层的当前行SELECTAVG(Grade)FROMSC yWHEREy.Sno?-- ? 由外层当前行的 x.Sno 提供执行一次完整的流程外层取到的当前行 x?内层实际执行的语句内层返回外层判断(2001, 1, 80)2001SELECT AVG(Grade) FROM SC y WHERE y.Sno 20018580 85假丢掉这一行(2001, 2, 90)2001同上还是这个学生8590 85真留下(2002, 2, 70)2002SELECT AVG(Grade) FROM SC y WHERE y.Sno 20027070 70真留下要分清这里面有两处比较各在各的位置内层自己的过滤WHERE y.Sno x.Sno内层把y表SC逐行拿来和自己的x.Sno当前值比只留下属于这个学生的行——它决定拿哪些行去算平均值。这个比较的结果不出内层也不返回给外层。外层的比较Grade (…)等内层算出一个数之后外层拿自己当前行的 Grade和这个数比——它决定这一行留不留。内层向外返回的就是一个数因为用了聚集函数AVG又没有GROUP BY内层永远恰好返回一行一列空集时返回一行 NULL。这也是它能直接放在比较运算符右边的唯一原因。其他要注意的为什么必须起别名内外层都是SC不写x、y就没法说清是外层那一行还是内层这一行。外层每换一行就重算一次是 DBMS 的做法语义上内层的结果只跟x.Sno有关同一个学生的多行算出来是同一个平均值。成绩里如果有 NULLAVG会跳过它如果某个学生所有成绩都是 NULLAVG返回 NULL比较结果就是 UNKNOWN这个学生一行都不会留下。1.3 带 ANYSOME或 ALL 谓词的子查询内层返回**一列值多行**时不能直接写比较运算符会撞上 1242要在比较运算符后面接ANY或ALL表示跟这一列里的值逐个比。谓词含义 ANY大于子查询结果中的某个值——只要比得过其中一个就算真 ANY/ ANY/ ANY/ ANY/! ANY同理与结果中的某个值比较成立即可 ALL大于子查询结果中的所有值——每一个都要比得过才算真 ALL/ ALL/ ALL/ ALL同理与结果中的每个值比较都要成立! ALL不等于子查询结果中的任何一个值SOME和ANY是同一个意思SOME是标准里的拼法。-- [例] 查询非 CS 专业中比 CS 专业任意一个学生年龄小的学生姓名和出生日期SELECTSname,SbirthdateFROMStudentWHERESbirthdateANY(SELECTSbirthdateFROMStudentWHERESMajorCS)ANDSMajorCS;内层返回的是CS 专业所有学生的出生日期这一列多行。Sbirthdate ANY (这一列)读作我的出生日期比这一列里的某一个晚 年龄比某一个小。“比某一个晚等价于比其中最早的那个晚”最早的那个就是MIN所以它能用聚集函数改写成单值比较子查询只算一次就行SELECTSname,SbirthdateFROMStudentWHERESbirthdate(SELECTMIN(Sbirthdate)FROMStudentWHERESMajorCS)ANDSMajorCS;ANY、ALL 与聚集函数、IN 的等价转换关系课件表格ANYIN— MAX MAX MIN MINALL—NOT IN MIN MIN MAX MAX这张表要会两个方向用看到 ALL就换成 MAX比所有人都大 比最大的还大看到 ALL就换成NOT IN。内层返回空集时有个反直觉的结果 ANY为假没有一个比得过 ALL为真找不到反例。1.4 带 EXISTS 谓词的子查询EXISTS代表存在量词 ∃。它的规则和前面几种根本不同内层不返回任何数据只产生逻辑真值 true / false内层结果非空为 true为空为 false。所以内层的SELECT列表写*就行——反正返回什么列都不看只看有没有行。-- [例] 查询所有选修了 1 号课程的学生姓名SELECTSnameFROMStudentWHEREEXISTS(SELECT*-- 目标列写 * 即可FROMSCWHERESnoStudent.Sno-- 相关条件①内层的 Sno 对上外层当前学生的 SnoANDCno1);-- 内层自己的过滤条件这里的Sno Student.Sno到底是什么一次说清它是内层的 WHERE 条件不是子查询的返回值和外层比较。子查询的返回值那些行压根不出内层外层拿到的只有一个 true / false。它比的是两个列内层SC的Sno和外层当前行的Student.Sno。写法上Student.Sno前必须带外层表名或别名不带的话会被当成内层SC的列。它是逐行的外层扫到刘晨那一行时内层就变成SELECT * FROM SC WHERE Sno 2001 AND Cno 1扫到王勇时Sno换成2002再来一遍。AND Cno 1是内层自己的条件跟外层无关。执行过程取外层的第一个元组 → 用它的Sno去跑内层查询 →WHERE为真就把这一行放进结果 → 取外层下一个元组直到外层表检查完。-- [例] 查询没有选修 1 号课程的学生姓名NOT EXISTS 把真假反过来SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMSCWHERESnoStudent.SnoANDCno1);改写规则课件所有带 IN、比较运算符、ANY、ALL 谓词的子查询都能等价改写成带 EXISTS 的子查询反过来不行——有些 EXISTS / NOT EXISTS 的子查询无法用其他形式等价替换。-- [例] 查询与刘晨在同一个主修专业学习的学生把 1.2 的例子改写成 EXISTS 形式SELECTSno,Sname,SmajorFROMStudent S1WHEREEXISTS(SELECT*FROMStudent S2WHERES2.SmajorS1.SmajorANDS2.Sname刘晨);同一个需求IN版是不相关子查询内层只算一次EXISTS版是相关子查询外层每行算一次。两者结果相同MySQL 优化器一般会把它们转成同一种执行计划别去背哪个更快用EXPLAIN看。1.5 比较的层次单值、列、行、集合前面几节都是列和子查询比。把镜头拉远一点SQL 里能参与比较的操作数一共四种形态比什么写法语义值和值WHERE Sage 18每行拿Sage和常量比列和列同一行内的两个列WHERE height weight每行拿这一行的height和这一行的weight比列和子查询返回的一个值WHERE Sage (SELECT AVG(Sage) FROM Student)内层必须一行一列§1.2列和子查询返回的一列多行Sage ALL (…)、Sage ANY (…)、Sage IN (…)与集合里的每一个 / 至少一个比§1.1、§1.3一行和一行(Sno, Cno) (SELECT Sno, Cno FROM SC WHERE …)列数一致按位置一一对应不看列名内层必须恰好一行一行和一列多行(Sno, Cno) IN (SELECT Sno, Cno FROM SC …)多行也行等于两列同时对上一行有没有结果EXISTS (…)不比值只看内层空不空§1.4-- 行构造器整行比较的两种写法WHERE(Sno,Cno)(SELECTSno,CnoFROMSCWHERE...)-- 行子查询内层必须恰好一行多行报 1242WHERE(Sno,Cno)IN(SELECTSno,CnoFROMSC)-- 多行也行等于两列同时对上一行列和列比本质还是逐行、单值比单值只不过右边从常量换成了另一列它和WHERE height 180在结构上没有区别。没有整列对整列的运算符写不出A.col1 B.col2就表示A 的每个值都大于 B 的每个值。要表达全部大于只能用聚集函数或双重否定WHERE(SELECTMIN(col1)FROMA)(SELECTMAX(col2)FROMB)-- 最小的 A 也大于最大的 BWHERENOTEXISTS(SELECT1FROMA,BWHEREA.col1B.col2)-- 不存在不大于的一对后一种就是 §1.6 里 ∀ 的写法两处用的是同一个双重否定。前一种在 B 为空表或取值全为 NULL 时会取到 NULL比较结果不成立。三层嵌套时相关条件里那种Sno Student.Sno AND Cno Course.Cno就是把两列同时对上一行用AND摊开写的每个比较仍然是单值比单值三层里最内层能看见中层和外层的所有列Student.Sno来自最外层Course.Cno来自中层这正是相关能一层层套下去的原因。跨表的列对列比较补充-- ① 同一行内的两个列比不需要连接逐行比就完了SELECT*FROMuserWHEREheightweight;-- ② 跨表的列对列比必须先把两张表的行配成对SELECT*FROMStudent,SCWHEREStudent.SnoSC.Sno-- 连接条件把 SC 的行配到它所属的学生上ANDStudent.SageSC.Grade;-- 比较条件配好对之后再逐对筛-- ③ 只写比较、不写连接条件会怎样SELECT*FROMStudent,SCWHEREStudent.SageSC.Grade;③ 的前提要说清两张表之间没有连接条件时DBMS 先把 A 的每一行和 B 的每一行全组合笛卡尔积1000 行 × 1000 行 100 万行再一对一对地判断比较条件。语法合法但结果通常没有业务意义而且很慢。A.col1 B.col2本身不是连接条件。ON A.id B.a_id那样的等值连接能把两边的行一一配上做不到它只回答这一对组合留不留配出来的可以是多对多。它是逐行筛选不是所有 A 都大于所有 B设 A {10, 20}、B {5, 15}四对组合里留下 (10,5)、(20,5)、(20,15) 三对——既不是全留也不是全丢。要每个 A 都大于每个 B得用上面那两种写法之一。最后回到子查询SELECT *里的*不表示整行参与比较只表示返回哪些列无所谓。子查询的返回值永远只在上面那几种形态里挑一种一个值 / 一列值 / 有没有不会拿一整行去和外层比。1.6 全称量词怎么用 NOT EXISTS 写本节难点SQL 里没有全称量词 ∀靠这个转换∀x P(x) ≡ ¬ ∃x(¬P(x))“全都满足” “不存在一个不满足的”。-- [例] 查询选修了全部课程的学生姓名-- 含义没有一门课是他不选的SELECTSnameFROMStudentWHERENOTEXISTS-- 不存在这样的课程(SELECT*FROMCourseWHERENOTEXISTS-- 这个学生没有选它(SELECT*FROMSCWHERESnoStudent.SnoANDCnoCourse.Cno));三层要一层层读注意哪一层的列是谁的层遍历什么条件里的列来自这一层在问最外层每个学生Student.Sno是当前学生对每个学生往下问中层每门课程Course.Cno是当前课程有没有这个学生没选这门课的记录最内层选课记录 SCStudent.Sno来自最外层Course.Cno来自中层这个学生选没选这门课只要有一门课没选中层结果非空NOT EXISTS为假该生被排除所有课都选了中层每门课的结果都为空NOT EXISTS为真该生留下。-- [例] 查询选修了全部课程的学生学号第二种写法除法转成计数相等SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT(Cno)(SELECTCOUNT(*)FROMCourse);第二种写法的前提是SC的主码为(Sno, Cno)一个学生一门课只有一条记录所以选课门数 课程总数才等价于全选了。-- [例] 查询至少选修了学生 95002 选修的全部课程的学生学号-- 记 p95002 选了课程 yq学生 s 也选了课程 y要的是 ∀y(p → q)-- 用 p → q ≡ ¬p ∨ q 化成 ¬∃y(p ∧ ¬q)不存在95002 选了而 s 没选的课程SELECTDISTINCTSnoFROMSC scxWHERENOTEXISTS(SELECT*FROMSC scyWHEREscy.Sno95002ANDNOTEXISTS(SELECT*FROMSC sczWHEREscz.Snoscx.SnoANDscz.Cnoscy.Cno));-- 方法二计数SELECTSnoFROMSCWHERECnoIN(SELECTCnoFROMSCWHERESno95002)GROUPBYSnoHAVINGCOUNT(Cno)(SELECTCOUNT(Cno)FROMSCWHERESno95002);方法二里WHERE Cno IN (…)已经把范围限制在95002 选过的课内所以后面COUNT(Cno)数出来的就是该生选了其中几门。练习课件 19 页查询全部学生都选修的课程课号SELECTCnoFROMSCGROUPBYCnoHAVINGCOUNT(DISTINCTSno)(SELECTCOUNT(*)FROMStudent);2. 集合查询课件 3.4.4-- 并查询选修了课程 1 或课程 2 的学生SELECTSnoFROMSCWHERECno1UNIONSELECTSnoFROMSCWHERECno2;-- 等价写法SELECTDISTINCTSnoFROMSCWHERECno1ORCno2;-- 交查询选修了课程 1 又选修了课程 2 的学生SELECTSnoFROMSCWHERECno1INTERSECTSELECTSnoFROMSCWHERECno2;-- MySQL 没有 INTERSECT用 IN 绕过去SELECTSnoFROMSCWHERECno1ANDSnoIN(SELECTSnoFROMSCWHERECno2);参加集合运算的各结果表列数必须相同对应项的数据类型也必须相同。标准 SQL 直接支持的只有UNIONINTERSECT、EXCEPT是一般商用库扩展的MySQL 不支持。UNION会去重排序后合并要保留重复行得写UNION ALL。3. 基于派生表查询课件 3.4.5子查询放在FROM子句里得到的就是一张临时派生表必须给它起别名-- [例] 查询选修了 1 号课程的学生姓名SELECTSnameFROMStudent,(SELECTSnoFROMSCWHERECno1)ASsc1WHEREStudent.Snosc1.Sno;-- 也可以先把子查询定义成 WITH公共表表达式再当表用WITHsc1(Sno)AS(SELECTSnoFROMSCWHERECno1)SELECTSnameFROMStudent,sc1WHEREStudent.Snosc1.Sno;子查询还可以放在SELECT子句里返回标量子查询一个具体值-- [例] 查询每门课程的选课人数信息SELECTCno,Cname,(SELECTCOUNT(*)FROMSCWHERESC.CnoCourse.Cno)ASnum_studFROMCourse;把它和连接 分组对照——下面两条结果并不相同课件 28~29 页问的就是这件事-- ① 内连接 分组没有人选的课不会出现在结果里SELECTSC.Cno,Cname,COUNT(Sno)ASnum_studFROMSC,CourseWHERESC.CnoCourse.CnoGROUPBYSC.Cno,Cname;-- ② 左外连接 分组没有人选的课也留下来人数记 0SELECTCourse.Cno,Cname,COUNT(Sno)ASnum_studFROMCourseLEFTJOINSCONSC.CnoCourse.CnoGROUPBYCourse.Cno,CnameORDERBYCourse.Cno;差别来自连接方式内连接只保留两边都匹配上的行没人选的课被丢掉左外连接保留左表Course的全部行这些行的SC侧全是NULLCOUNT(Sno)数出 0。课件那段代码把GROUP BY写成了sc.cno——没人选的课在SC侧是NULL会和别的课分不到一组、课号也取不出来要正确显示课号得按course.cno分组。4. 综合应用的注意点课件 30 页小结-- ① 聚集函数只能出现在 SELECT 子句和 HAVING 短语中不能出现在 WHERE 子句里SELECTSnoFROMSCWHEREAVG(Grade)80GROUPBYSno;-- ✗ 错SELECTSnoFROMSCGROUPBYSnoHAVINGAVG(Grade)80;-- ✓ 对-- ② 聚集函数不能嵌套要平均成绩最高的不能写 MAX(AVG(Grade))结果输出列只能取自最外层查询所用的表子查询里的属性不能作为最终输出输出涉及多个表的列时最外层必须写成连接查询。ORDER BY只对最终结果排序所以通常写在最后。