WindowsServer数据库课件--第03章SQL语言.ppt

数据库系统概论AnIntroductiontoDatabaseSystem第三章关系数据库标准语言SQL 4 复习 1 统计每人不及格的门数 2 查询显示开设课程的名称 及格人数及最高分 3 查询显示所有计算机系学生的姓名 以及他们所选课程和相应的成绩 复习 单表查询 SELECTSno count cno FROMSCWHEREgrade 60groupbysno 1 统计每人不及格的门数 复习 单表查询 SELECTcno课程号 Count sno 及格人数 Max grade 最高分FROMSC CourseWHERESC cno Coandgrade 60groupbycno 2 查询显示开设课程的名称 及格人数及最高分 3 显示全部计算机系学生的姓名 以及他们所选课程名和相应的成绩 复习 连接查询 SELECTFROMWHERE sname cname gradestudent sc o oandstudent sno sc snoandsdept CS 复习 连接查询 SELECT FROMStudent SC 4 描述下列语句的含义 WHEREStudent Sno SC Sno Student Sno Sname Ssex Sage Sdept Cno Grade SELECTC1 Cno C2 CpnoFROMCourseC1 CourseC2WHEREC1 Cpno C2 Cno 学习目标 了解外连接以及查询实现掌握不相关子查询 IN 比较运算符 ANY ALL 掌握相关子查询 Exists谓词 3 4查询 3 4 1单表查询3 4 2连接查询3 4 3嵌套查询3 4 4集合查询 广义笛卡尔积查询等值连接 含自然连接 查询非等值连接查询自身连接查询外连接查询复合条件连接查询 SQL中连接查询的主要类型 4 外连接 1 外连接与普通连接的区别普通连接操作只输出满足连接条件的元组 外连接操作以指定表为连接主体 将主体表中的全部记录输出显示 例 将每个学生的基本信息与其所选课程信息对应显示 结果 Student SnoSnameSsexSageSdeptCnoGrade0215121李勇男20CS1920215121李勇男20CS2850215121李勇男20CS3880215122刘晨女19IS2900215122刘晨女19IS3800215123王敏女18MA0215125张立男19IS 示例 外连接 SELECT FROM 连接类型 JOIN JOIN ON ON ONWHERE INNER LEFT RIGHT FULL CROSS 提示 JOIN的顺序和ON连接条件的顺序相反 外连接只能对两个表进行 2 语法格式 4 外连接 Student Sc SELECTstudent sc FROMstudentLEFTJOINscONstudent sno sc sno RIGHT FULL 例 查询所有学生的个人信息及选课信息 4 外连接 4 外连接 SELECTsname gradeFROMstudentJOINscJOINcourseONo oONstudent sno sc snoWHEREcname 数学 例 查询选修 数学 课程的学生的姓名和该课程成绩 查询显示全部计算机系学生的姓名 以及他们所选课程的门数 练习 SELECTsname count cno 课程门数FROMstudentleftjoinscONstudent sno sc snoWHEREsdept CS GROUPBYSname 特点 WHERE子句中含多个条件 例35 查询选修2号课程且成绩在90分以上所有学生的学号 姓名 SELECTStudent Sno student SnameFROMStudent SCWHEREStudent Sno SC SnoANDSC Cno 2 ANDSC Grade 90 5 复合条件连接 特点 连接操作在两个以上的表之间进行 例36 查询每个学生的学号 姓名 选修的课程名及成绩 SELECTStudent Sno Sname Cname GradeFROMStudent SC CourseWHEREStudent Sno SC SnoandSC Cno Course Cno 6 多表连接 3 4查询 3 4 1单表查询3 4 2连接查询3 4 3嵌套查询3 4 4集合查询 3 4 3嵌套查询 嵌套查询概述嵌套查询分类及求解方法引出子查询的谓词 1 一个SELECT FROM WHERE语句称为一个查询块 2 将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语条件中的查询称为嵌套查询 1 嵌套查询概述 例 SELECTSname外层查询 父查询FROMStudentWHERESnoIN SELECTSno内层查询 子查询FROMSCWHERECno 2 嵌套查询概述 3 说明 子查询的限制注意 子查询不能使用ORDERBY子句ORDERBY只对查询最终结果的排序层层嵌套方式反映了SQL语言的结构化不同的DBMS允许的最大嵌套层数不同有些嵌套查询可以用连接查询替代 3 4 3嵌套查询 嵌套查询概述嵌套查询分类及求解方法引出子查询的谓词 1 不相关子查询 子查询的查询条件不依赖于父查询查询过程 由里向外逐层处理每个子查询在上一级查询处理之前先求解 子查询的结果用于建立父查询的查找条件 2 嵌套查询的分类 1 不相关子查询 2 嵌套查询的分类 例 SELECTSname外层查询 父查询FROMStudentWHERESnoIN SELECTSno内层查询 子查询FROMSCWHERECno 2 2 相关子查询 子查询的查询条件依赖于父查询查询过程 首先取外层查询表的第一个元组 根据它与内层查询相关的属性值处理内层查询 若WHERE子句返回值为真 则取此元组放入结果表 然后再取外层表的下一个元组 重复这一过程 直至外层表全部检查完为止 嵌套查询的分类 3 4 3嵌套查询 嵌套查询概述嵌套查询分类及求解方法引出子查询的谓词 3 引出子查询的谓词 带有IN谓词的子查询带有比较运算符的子查询带有ANY或ALL谓词的子查询带有EXISTS谓词的子查询 例39 查询与刘晨在同一个系学习的学生 可以分步实现 确定 刘晨 所在系名SELECTSdeptFROMStudentWHERESname 刘晨 1 带有IN谓词的子查询 结果为 deptIS 例39 查询与刘晨在同一个系学习的学生 查找所有在IS系学习的学生SELECTSno Sname SdeptFROMStudentWHERESdept IS 结果为 SnoSnameSdept0215122刘晨IS0215125张立IS 1 带有IN谓词的子查询 续 1 带有IN谓词的子查询 续 方法1 将第一步查询嵌入到第二步查询的条件中SELECTSno Sname SdeptFROMStudentWHERESdeptIN SELECTSdeptFROMStudentWHERESname 刘晨 构造嵌套查询 说明 此查询为不相关子查询 DBMS求解该查询时也是分步去做的 方法2 也可用自身连接完成本查询要求S1S2SELECTS1 Sno S1 Sname S1 SdeptFROMStudentS1 StudentS2WHERES1 Sdept S2 SdeptANDS2 Sname 刘晨 1 带有IN谓词的子查询 续 例38 查询选修了课程名为 信息系统 的学生学号和姓名 1 带有IN谓词的子查询 续 最后在Student关系中取出Sno和SnameSELECTSno SnameFROMStudentWHERESnoIN SELECTCnoFROMCourseWHERECname 信息系统 首先在Course关系中找出 信息系统 的课程号 结果 3 然后在SC关系中找出选修了3号课程的学生学号SELECTSnoFROMSCWHERECnoIN 组合以后的代码 SELECTSno SnameFROMStudentWHERESnoIN SELECTSnoFROMSCWHERECnoIN SELECTCnoFROMCourseWHERECname 信息系统 1 带有IN谓词的子查询 续 结果 SnoSname0215121李勇0215122刘晨 1 带有IN谓词的子查询 续 另 本例也可用连接查询SELECTSno SnameFROMStudent SC CourseWHEREStudent Sno SC SnoANDSC Cno Course CnoANDCourse Cname 信息系统 是指父查询和子查询之间用比较运算符连接的查询适用范围 当能确切知道内层查询返回单值时 内层查询返回值是集合 要与ANY或ALL谓词配合使用后 2 带有比较运算符的子查询 2 带有比较运算符的子查询 续 例 假设一个学生只可能在一个系学习 并且必须属于一个系 则在例37可以改写为 SELECTSno Sname SdeptFROMStudentWHERESdept SELECTSdeptFROMStudentWHERESname 刘晨 注意 1 子查询一定要用括号括起来 2 带有比较运算符的子查询 续 注意 2 子查询一定要跟在比较符之后 SELECTSno Sname SdeptFROMStudentWHERE SELECTSdeptFROMStudentWHERESname 刘晨 Sdept 谓词语义 ANY 表示任意一个值 ALL 表示所有值 3 带有ANY或ALL谓词子查询 需要配合使用比较运算符 ANY大于 大于等于 子查询结果中的某个值 ALL大于 大于等于 子查询结果中的所有值 ANY小于 小于等于 子查询结果中的某个值 ALL小于 小于等于 子查询结果中的所有值 ANY等于子查询结果中的某个值 ALL等于子查询结果中的所有值 通常无意义 ANY不等于子查询结果中的某个值 ALL不等于子查询结果中的任何一个值 3 带有ANY或ALL谓词子查询 哪些无实际意义 例42 查询其他系中比信息系任一个 其中某一个 学生年龄小的学生姓名和年龄 SELECTSname SageFROMStudentWHERESage ANY SELECTSageFROMStudentWHERESdept IS 执行过程 3 带有ANY或ALL谓词子查询 续 ANDSdept IS 是哪个查询的条件 ANY和ALL谓词有时可以用集函数实现ANY ALL与集函数的对应关系 3 带有ANY或ALL谓词子查询 续 IN MAX MAX MIN MIN NOTIN MIN MIN MAX MAX 用ANY实现例39如下 SELECTSname SageFROMStudentWHERESage IS 3 带有ANY或ALL谓词子查询 续 用集函数实现例39如下 SELECTSname SageFROMStudentWHERESage IS 例43 查询其他系中比IS系所有学生年龄都小的学生姓名及年龄 方法一 用ALL谓词SELECTSname SageFROMStudentWHERESage IS 3 带有ANY或ALL谓词子查询 续 例40 查询其他系中比IS系所有学生年龄都小的学生姓名及年龄 方法二 用集函数SELECTSname SageFROMStudentWHERESage IS 3 带有ANY或ALL谓词子查询 续 用聚集函数实现子查询能够减少比较次数 提高查询效率 CPU 比较两种方法的效率 3 4 3嵌套查询 带有IN谓词的子查询带有比较运算符的子查询带有ANY或ALL谓词的子查询带有EXISTS谓词的子查询 4 带有EXISTS谓词的子查询 1 EXISTS谓词 2 NOTEXISTS谓词 3 不同形式的查询间的替换 4 相关子查询的效率 5 用EXISTS NOTEXISTS实现全称量词 6 用EXISTS NOTEXISTS实现逻辑蕴函 4 带有EXISTS谓词的子查询 1 EXISTS谓词相当于存在量词 带有EXISTS谓词的子查询不返回具体数据 只产生逻辑值 True 或 False 由EXISTS引出的子查询 其目标列表达式通常都用 2 NOTEXISTS谓词 例44 查询所有选修了2号课程的学生姓名 4 带有EXISTS谓词的子查询 用带in谓词的嵌套查询SELECTSnameFROMStudentWHERESnoin SELECTSnoFROMSCWHERECno 2 例44 查询所有选修了2号课程的学生姓名 用带EXISTS谓词的嵌套查询SELECTSnameFROMStudentWHEREEXISTS SELECT FROMSCWHERESno Student SnoANDCno 2 4 带有EXISTS谓词的子查询 用带EXISTS谓词的嵌套查询SELECTSnameFROMStudentWHEREEXISTS SELECT FROMSCWHERESno Student SnoANDCno 2 4 带有EXISTS谓词的子查询 外 内 带EXISTS谓词查询的执行过程 内 4 带有EXISTS谓词的子查询 外 内 内 内 内 带EXISTS谓词查询的执行过程 用带EXISTS谓词的嵌套查询SELECTSnameFROMStudentWHEREEXISTS SELECT FROMSCWHERESno Student SnoANDCno 2 4 带有EXISTS谓词的子查询 外 内 内 内 内 内 内 带EXISTS谓词查询的执行过程 用带EXISTS谓词的嵌套查询SELECTSnameFROMStudentWHEREEXISTS SELECT FROMSCWHERESno Student SnoANDCno 2 用带EXISTS谓词的嵌套查询SELECTSnameFROMStudentWHEREEXISTS SELECT FROMSCWHERESno Student SnoANDCno 2 4 带有EXISTS谓词的子查询 带EXISTS谓词查询的执行过程 外 内 内 内 内 内 内 用带EXISTS谓词的嵌套查询SELECTSnameFROMStudentWHEREEXISTS SELECT FROMSCWHERESno Student SnoANDCno 2 4 带有EXISTS谓词的子查询 带EXISTS谓词查询的执行过程 内 外 例44 查询所有选修了2号课程的学生姓名 4 带有EXISTS谓词的子查询 本查询涉及Student和SC关系 在Student中依次取每个元组的Sno值 用此值去检查SC关系 若SC中存在这样的元组 其Sno值等于此Student Sno值 并且其Cno 2 则取此Student Sname送入结果关系 父查询指针下移 子查询回到首行 否则 子查询移到底部才返回False值 总结 对每一个父查询的元组 都要求解一遍子查询 例44 查询所有选修了2号课程的学生姓名 4 带有EXISTS谓词的子查询 用ExistsSELECTSnameFROMStudentWHEREEXISTS SELECT FROMSCWHERESno Student SnoANDCno 2 用INSELECTSnameFROMStudentWHERESnoin SELECTSnoFROMSCWHERECno 2 例44 查询所有选修了2号课程的学生姓名 4 带有EXISTS谓词的子查询 用连接查询SELECTSnameFROMStudent SCWHEREStudent Sno SC SnoANDSC Cno 2 例45 查询没有选修2号课程的学生姓名 SELECTSnameFROMStudentWHERENOTEXISTS SELECT FROMSCWHERESno Student SnoANDCno 2 4 带有EXISTS谓词的子查询 该要求很难用连接查询实现 4 带有EXISTS谓词的子查询 用嵌套查询SELECTSnameFROMStudentWHERENOTEXISTS SELECT FROMSCWHERESno Student SnoANDCno 2 例45 查询没有选修2号课程的学生姓名 4 带有EXISTS谓词的子查询 3 不同形式的查询间的替换一些带EXISTS或NOTEXISTS谓词的子查询不能被其他形式的子查询等价替换所有带IN谓词 比较运算符 ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换 总结 EXISTS谓词的表达能力高于其他谓词的表达形式的 例37 查询与刘晨在同一个系学习的学生 用带EXISTS谓词的子查询替换 SELECTSno Sname SdeptFROMStudentS1WHEREEXISTS SELECT FROMStudentWHERESdept S1 SdeptANDSname 刘晨 4 带有EXISTS谓词的子查询 4 带有EXISTS谓词的子查询 4 相关子查询的效率由于带EXISTS谓词的相关子查询只关心内层查询是否有返回值 并不需要查具体值 因此其效率并不一定低于其他查询 4 带有EXISTS谓词的子查询 5 用EXISTS NOTEXISTS实现全称量词 难点 SQL语言中没有全称量词 Forall 可以把带有全称量词的谓词转换为等价的带有存在量词的谓词 x P x P 例43 查询选修了全部课程的学生姓名 SELECTSnameFROMStudentWHERENOTEXISTS SELECT FROMCourseWHERENOTEXISTS SELECT FROMSCWHERESno Student SnoANDCno Course Cno 4 带有EXISTS谓词的子查询 4 带有EXISTS谓词的子查询 Student Sc 外 中 内 SELECTSnameFROMStudentWHERENOTEXISTS SELECT FROMCourseWHERENOTEXISTS SELECT FROMSCWHERESno Student SnoANDCno Course Cno SELECTSnameFROMStudentWHERENOTEXISTS SELECT FROMCourseWHERENOTEXISTS SELECT FROMSCWHERESno Student SnoANDCno Course Cno 4 带有EXISTS谓词的子查询 Student Sc 外 中 内 内 SELECTSnameFROMStudentWHERENOTEXISTS SELECT FROMCourseWHERENOTEXISTS SELECT FROMSCWHERESno Student SnoANDCno Course Cno 4 带有EXISTS谓词的子查询 Student Sc 外 中 内 内 内 内 内 内 SELECTSnameFROMStudentWHERENOTEXISTS SELECT FROMCourseWHERENOTEXISTS SELECT FROMSCWHERESno Student SnoANDCno Course Cno 4 带有EXISTS谓词的子查询 Student Sc 外 中 内 内 内 SELECTSnameFROMStudentWHERENOTEXISTS SELECT FROMCourseWHERENOTEXISTS SELECT FROMSCWHERESno Student SnoANDCno Course Cno 4 带有EXISTS谓词的子查询 Student Sc 外 中 内 内 内 内 SELECTSnameFROMStudentWHERENOTEXISTS SELECT FROMCourseWHERENOTEXISTS SELECT FROMSCWHERESno Student SnoANDCno Course Cno 4 带有EXISTS谓词的子查询 Student Sc 外 中 内 内 内 内 内 SELECTSnameFROMStudentWHERENOTEXISTS SELECT FROMCourseWHERENOTEXISTS SELECT FROMSCWHERESno Student SnoANDCno Course Cno 4 带有EXISTS谓词的子查询 Student Sc 外 中 内 内 内 内 内 例 查询选修了课程的学生姓名方法一 SelectsnameFromstudentWhereExists Select FromscWherestudent sno sc sno 方法二 SelectDISTINCTsnameFromstudent scWherestudent sno sc sno 方法三 SelectsnameFromstudentWheresnoin selectdistinctsnofromsc 请比较 本例中三种方法的查询效率如何 3 4查询 3 4 1单表查询3 4 2连接查询3 4 3嵌套查询3 4 4集合查询 3 4 4集合查询 标准SQL直接支持的集合操作种类并操作 UNION 一般商用数据库支持的集合操作种类并操作 UNION 交操作 INTERSECT 差操作 EXCEPT 3 4 4集合查询 1 并操作 UNION 参加UNION操作的各结果表的列数必须相同 对应项的数据类型也必须相同 一般格式为 UNION 例48 查询计算机系的学生及年龄不大于19岁的学生 方法一 SELECT FROMStudentWHERESdept CS UNIONSELECT FROMStudentWHERESage 19 3 4 4集合查询 方法二 SELECTDISTINCT FROMStudentWHERESdept CS ORSage 19 例49 查询选修了课程1或者选修了课程2的学生 方法一 SELECTSnoFROMSCWHERECno 1 UNIONSELECTSnoFROMSCWHERECno 2 3 4 4集合查询 结果有几条记录 方法二 SELECTDISTINCTSnoFROMSCWHERECno 1 ORCno 2 补充 设数据库中有教师表Teacher Tno Tname 查询学校中所有师生的姓名 SELECTSnameFROMStudentUNIONSELECTTnameFROMTeacher 3 4 4集合查询 还能用前面类似的等价方式表示吗 2 交操作 INTERSECT 标准SQL没有提供交操作 但SQLSERVER2008提供 例50 查询计算机系的学生与年龄不大于19岁的学生的交集 SELECT FROMStudentWHERESdept CS ANDSage 19 SELECT FROMStudentWHERESdept CS INTERSECTSELECT FROMStudentWHERESage 19 3 4 4集合查询 例51 查询既选修1号课又选修2号课的学生集合 3 4 4集合查询 SELECTSnoFROMSCWHERECno 1 ANDSnoIN SELECTSnoFROMSCWHERECno 2 思考 查询教师和学生同名的集合 SELECTSnoFROMSCWHERECno 1 INTERSECTSELECTSnoFROMSCWHERECno 2 3 差操作 EXCEPT 标准SQL中没有提供差操作 但SQLSERVER2008提供了此操作 3 4 4集合查询 例52 查询计算机科学系的学生与年龄不大于19岁的学生的差集 3 4 4集合查询 SELECT FROMStudentWHERESdept CS EXCEPTSELECT FROMStudentWHERESage 19 SELECT FROMStudentWHERESdept CS ANDSage 19 例53 查询学生和教师不重名的学生姓名 3 4 4集合查询 SELECTSnameFROMStudentEXCEPTSELECTTnameFROMTeacher SELECTsnameFROMStudentWHEREsnamenotin selectsnamefromteacher 3 4 4集合查询 SELECT FROMStudentORDERBYSnoUNIONSELECT FROMTeacherORDERBYTno new SELECTSnameFROMStudentUNIONSELECTTnameFROMTeacherORDERBYTname 4 对集合操作结果的排序 4 对集合操作结果的排序ORDERBY子句通常用数字序号指定排序属性 只用于对最终查询结果排序 不能对中间结果排序任何情况下 ORDERBY子句只能出现在最后 new 3 4 4集合查询 SELECTSnameFROMStudentUNIONSELECTTnameFROMTeacherORDERBYSname 1 SELECT语句的一般格式 SELECT ALL DISTINCT 别名 FROM 别名 别名 WHERE GROUPBY HAVING ORDERBY ASC DESC 3 4查询小结 3 5数据更新 3 5 1插入数据3 5 2修改数据3 5 3删除数据 3 5 1插入数据 两种插入数据方式 插入单个元组插入子查询结果 3 5 1插入数据 1 插入单个元组语句格式 INSERTINTO VALUES 功能 将新元组插入指定表中 3 5 1插入数据 INTO子句指定要插入数据的表名及属性列属性列顺序与表定义中的顺序可以不一致省略属性列表 表示插入一条完整元组 且属性列属性与表定义顺序一致指定部分属性列 插入的元组在其余属性列上取空值 省略属性时要注意 VALUES子句提供的值必须与INTO子句中的属性匹配值的个数 类型 3 5 1插入数据 注意 DBMS在执行插入语句时会检查所插元组是否破坏表上已定义的完整性规则实体完整性参照完整性用户定义的完整性对于有NOTNULL约束对于有UNIQUE约束对于有值域约束 例1 将一个新学生记录插入到Student表中 内容如下 95020 陈冬 男 IS 18岁 INSERTINTOStudentVALUES 0215128 陈冬 男 IS 18 3 5 1插入数据 例2 向SC表插入一条选课记录 0215128 1 INSERTINTOSC Sno Cno VALUES 0215128 1 新插入的记录在Grade列上取空值 3 5 1插入数据 2 插入子查询结果语句格式INSERTINTO 子查询 功能 将子查询结果插入指定表中 3 5 1插入数据 例3 对每一个系 求学生的平均年龄 并把结果存入数据库 首先建表 CREATETABLEDept avgage SdeptCHAR 15 定义系名 AvgageSMALLINT 定义平均年龄 3 5 1插入数据 例3 对每一个系 求