3第3章数据库系统.ppt

3 1SQL概述3 2数据定义3 3查询3 4数据更新3 5视图3 6数据控制3 7嵌入式SQL 第三章关系数据库标准语言SQL 3 1关系数据库标准语言SQL概述 SQL是StructuredQueryLanguage的缩写 即结构化查询语言 3 1 1SQL语言特点 1 一体化特点SQL提供了一系列完整的数据定义 数据操纵和数据控制功能 用SQL可以实现数据库生命周期中的全部活动 2 高度非过程化SQL是非过程语言 即用户依据 做什么 来说明操作 而无须说明 怎么做 存取路径的选择和SQL语句的操作过程由系统自动完成 3 面向集合的操作方式SQL语言对关系进行操作 其操作对象和结果都是二维表 特点是一次一集合 set at a time 4 统一的语法结构实现两种使用方式无论是联机交互使用方式 还是嵌入到高级语言中使用 其语法结构基本一致 5 语言简洁 易学易用SQL语言功能极强 但由于设计巧妙 语言十分简洁 完成核心功能只用了九个动词 如下表所示 3 1 2SQL语言的基本概念SQL语言支持关系数据库三级模式结构如图所示 基本表是本身独立存在的表 在SQL中一个关系对应一个表 一些基本表对应一个存储文件 一个表可以带若干索引 索引存放在存储文件中 所有基本表的逻辑结构的总和构成数据库的模式 存储文件的逻辑结构组成了关系数据库的内模式 存储文件的物理文件结构是任意的 视图是从基本表或其他视图中导出的表 它本身不独立存储在数据库中 也就是说数据库中只存放图的定义而不存放视图对应的数据 这些数据仍存放在导出视图的基本表中 因此视图是一个虚表 视图以及部分基本表构成数据库的外模式 3 2数据定义关系数据库由模式 外模式和内模式组成 即关系数据库的基本对象是表 视图和索引 因此SQL的数据定义功能包括定义表 定义视图和定义索引 如表所示 修改 ALTERTABLE 3 2 1定义 删除与修改基本表 参考第5章 数据类型一般有 CHAR 字符型 NUMBER 数值型 INT 整型 SMALLINT 整型 MONEY 货币型 DATE 日期型 等 有的系统可能还会提供BINARY 二进制型 DATETIME 日期时间型 TIME 时间型 TEXT 文本型 和IMAGE 图象型 等 1 定义基本表CREATETABLE 列级完整性约束条件 列级完整性约束条件 1 是所要定义的基本表的名字 一个表它可以由一个或多个属性组成 2 一般取有实际意义的名字 3 可以是前面介绍的数据类型 4 SQL中实现完整性控制的方法 实体完整性 参照完整性 用户定义完整性 实体完整性 主键约束 用primarykey定义 CREATETABLEStudent SnoCHAR 5 PRIMARYKEY SnameCHAR 20 SsexCHAR 2 SageINT SdeptCHAR 15 CREATETABLECourse CnoCHAR 4 PRIMARYKEY CnameCHAR 20 CpnoCHAR 4 CcreditINT CREATETABLESC SnoCHAR 5 CnoCHAR 4 GradeINT PRIMARYKEY Sno Cno 1 检查主码值是否唯一 2 检查主码各个属性是否为空 参照完整性 外键约束 用foreignkey定义 CREATETABLESC SnoCHAR 5 CnoCHAR 4 GradeINT PRIMARYKEY Sno Cno FOREIGNKEY sno REFERENCESStudent Sno FOREIGNKEY cno REFERENCESCourse Cno 1 SC表插入一个元组 用到的sno和cno在学生表和课程表中必须存在 2 设置级联操作后 修改或删除被参照表中的主键值后 SC表 参照表 中对应的所有sno或cno跟着修改或删除对应的所有元组 3 没有设置级联 修改或删除被参照表中的主键值后 参照表中对应的属性值将被置空 注意这里的sno和cno是不可以的 为什么 CREATETABLESC SnoCHAR 5 CnoCHAR 4 GradeINT PRIMARYKEY Sno Cno FOREIGNKEYREFERENCESStudent Sno ONDELETECASCADEONUODATECASCADE FOREIGNKEYREFERENCESCourse Cno ONDELETEACTIONONUODATECASCADE 说明 ACTION拒绝级连操作 CASCADE进行级连操作 级连操作设置 2 修改基本表SQL语句用ALTERTABLE语句修改基本表 其一般格式为 ALTERTABLE ADD 完整性约束 DROP ALTERCOLUMN 其中ADD子句用来增加新列和新的完整新约束条件 DROP子句用于删除指定的完整性约束条件 ALTER子句用于修改原有的列定义 例 向Student表增加 入学时间列 其数据类型为日期型 ALTERTABLEStudentADDScomeDATE 例 将年龄的数据类型改为小整数 ALTERTABLEStudentALTERCOLUMNSageSMALLINT 用户定义完整性 1 不允许取空值ALTERTABLEStudentALTERCOLUMNSnameCHAR 20 NOTNULL 2 列值唯一ALTERTABLEStudentaddUNIQUE Sname 3 检查约束ALTERTABLEStudentaddcheck ssexin 男 女 ALTERTABLESCaddcheck grade 0andgrade 100 3 删除基本表 可以用SQL语句DROPTABLE进行删除基本表 一般格式为 DROPTABLE例7 删除学生选课这张表 DROPTABLESC 3 2 2建立与删除索引建立索引是加快表的查询速度的有效手段 1 建立索引用CREATINDEX语句 格式为 CREATE UNIQUE CLUSTER INDEXON 其中 指定为要建索引的基本表的名字 索引可以建在该表的一列或多列上 各列之间用逗号分隔 每个后面还可以用指定索引值的排列次序 包括ASC 升序 和DESC 降序 UNIQUE表示此索引的每一个索引值只对应唯一的数据记录 CLUSTER表示要建立的索引是聚簇索引 聚簇索引指索引项的顺序与表中记录的物理顺序一致的索引 例8 为学生课程数据库中的Student Course SC等3个表建立索引 其中Student表按学号升序建立唯一索引 Course表按课程号升序建立唯一索引 SC表按学号升序和课程降序建唯一索引 CREATEUNIQUEINDEXStusnoONStudent Sno CREATEUNIQUEINDEXCoucnoONCourse Cno CREATEUNIQUEINDEXScnoONSC SnoASC CnoDESC 作为一般规则 不应该在一个表上建立太多的索引 一般不超过2 3个 索引能改善查询效果 但也耗了磁盘空间 降低了更新操作的性能 这是因为系统必须花时间来维护这些索引 除了为数据完整性而建立的唯一索引外 建议在表较大时再建立普通索引 表中的数据越多 索引的优越性越明显 2 删除索引DROPINDEX 例如 DROPINDEXCoucno 3 3查询SQL语言提供了SELECT语句进行数据库的查询 一般格式为 SELECT ALL DISTINCT FROM WHERE GROUPBY HAVING ORDERBY ASC DESC 其基本语句形式的示意图如下 语句完整形式的示意图如下 3 3 1单表查询 仅涉及一个数据库的查询1 选择表中的若干列 1 查询指定列例1 查询全体学生的学号与姓名 关系代数为 sno sname Student Sno Sname Student 无 所以 该查询的SQL语句为 SELECTSno SnameFROMStudent 假设学生表Student表中的数据如下 SelectSno SnameFromStudent语句的查询结果 对关系数据库中表的操作无非就是从一张表得到另一张新的表 在新的表中存放着满足条件的信息 也就是包含着你所要查询的信息 其执行结果为 例2 求已经开始招生的系的名称 假设学生表Student表中的数据如下 selectdistinctsdeptfromStudent 已经开始招生的系也就是已经有在读学生的那些系 所以只要将学生表中所有学生所在的系的名称查出来就行 由于 一个系包括多个学生 所以要去掉重复的系名 能完成该查询的Select语句表示如下 关系代数表达式为 sdept Student 注意 如果查询结果包含了重复的行 要去掉表中的重复行 必须指定DISTINCT语句 没有指定DISTINCT语句 则缺省值为ALL 即保留取值重复的行 结果如右图所示 2 查询全部列 可以用 指定例3 查询全体学生的详细记录SELECT FROMStudent 再如 要查询所有的课程信息 我们可以写出相应的Select查询语句如下 Course表 SELECT FROMCourse 3 查询经过计算的值不仅可以是表中的列名 还可以是有关表达式例4 查询全体学生的姓名及其出生年份 SELECTSname 2002 SageFROMStudent 所以 上述查询在实际应用中应写成 SELECTSname year date SageFROMStudent 一个算术表达式 不仅可以是算术表达式 还可以是字符串常量 函数等 因为在学生表中 没有出生年月的信息 我们可以通过当前的年份 减去学生的年龄 计算出出生年份 当前年份 例5 查询全体学生的姓名 出身年月和所在系 要求用小写字母表示所有系名 selectsname yearofbirth 2002 sage lower sdept fromStudent Student表 结果表 用户可以通过指定别名来改变查询结果的列名 例如 上题的查询结果中 第二 三 四列的名字分别是目标列表达式的名字 对于用户来说可能因此对表中信息的真实含义不理解 涉及到实际应用时 甚至让用户对所查出的信息产生误解 所以查询结果表中的信息必须尽可能直观 那么 我们可写出改进后的Select语句 查询结果为 SelectSnameNAME 2002 SageBIRTH YEAR lower Sdept DEPARTMENTFromStudent 注意 在不同DBMS系统中 各种Select语句的格式会有所不同 2 选择表中的若干元组 1 消除取值重复的行查询结果可能包含了许多重复的行 要去掉表中的重复行 必须指定DISTINCT语句 没有指定DISTINCT语句 则缺省值为ALL 即保留取值重复的行 例子 查询选修过课程的学生的学号 SelectdisticntsnoFromsc 计算机执行过程为 第一步 得到中间结果 放在缓冲区中 去掉重复元组得到最后结果 2 查询满足一定条件的元组 查询满足一定条件的元组 可以通过WHERE语句实现 WHERE子句常用的查询条件如下表所示 1 比较大小用于比较的运算符有 NOT 上述比较运算符逻辑运算符 and or查询时 如需满足多个条件 那么可用逻辑运算符将条件连起来形成多重条件 例6 查计算机系全体学生的信息 Student Sdept CS 信息系 IS 例7 查询信息系 IS系 全体女生本题查询的信息需要同时满足两个条件 两个条件之间的关系是 与 的关系 Student Sdept IS ssex 女 例8 查询信息系年龄小于20岁的学生学号 姓名 selectsno snamefromStudentwheresdept IS andsage 20 关系代数表达式为 sno sname sdept IS sage 20 Student 例9 查询选修了 3 号课程的学生的学号 成绩 selectsno gradefromSCwherecno 3 关系代数表达式 sno grade cno 3 SC 例10 查询选修了 3 号课程且成绩在60分以下的学生的学号 成绩 selectsno gradefromSCwherecno 3 andgrade 60 sno grade cno 3 grade 60 SC 例11 查询数据库这门课的先行课号 SelectcpnoFromcourseWherecname 数据库 练习题一 1 查询计算机系 CS 所有学生的姓名 性别和年龄 2 查询数学系 MA 小于18岁的学生的学号 姓名以及性别和年龄3 查询成绩不及格的学生的学号 课程号和成绩 4 查询选修了 c02 课程的学生的学号和成绩5 查询 9531101 学生的成绩在80分以上的选课情况 6 查询数学系学生的学号 姓名和出生年份 要求对各个字段进行重新命名 1 查询计算机系 CS 所有学生的姓名 性别和年龄 Selectsname ssex sageFromstudentWheresdept CS 2 查询数学系 MA 小于18岁的学生的学号 姓名以及性别和年龄SELECTsno sname ssex sageFromstudentWheresdept MA ANDsage 18 3 查询成绩不及格的学生的学号 课程号和成绩 Selectsno cno gradeFromscWheregrade 604 查询选修了 c02 课程的学生的学号和成绩Selectsno gradeFromscWherecno c02 5 查询 9531101 学生的成绩在80分以上的选课情况 Select FromscWheresno 9531101 andgrade 806 查询数学系学生的学号 姓名和出生年份 要求对各个字段进行重新命名 Selectsno 学号 sname 姓名 2004 sage 出生年份 FromstudentWheresdept MA 2 确定范围谓词BETWEEN AND 和NOTBETWEEN AND 用来指定属性在 或不在 指定范围内的元组例12 查询年龄在20至23岁之间的姓名 系别和年龄 SELECTSname Sdept SageFROMStudentWHERESageBETWEEN20AND23 不在 NOTBETWEEN20AND23 例13 查询选修了 2 号课程且成绩在80至90之间的学生的学号和成绩 SELECTsno gradeFROMScWHERECno 2 andgradeBETWEEN80AND90 等价于 selectsno gradefromscwhereCno 2 andgrade 80andgrade 90 3 确定集合谓词IN和NOTIN用来查找属性值属于 或不属于 指定集合的元组 这里IN相当于集合运算符 例14 查信息系 IS 数学系 MA 和计算机科学系 CS 的学生的姓名和性别 SELECTSname SsexFROMStudentWHERESdeptIN IS MA CS 等价于 SELECTSname SsexFROMStudentwheresdept IS orsdept MA orsdept CS 例15 求至少选修了下列课程中的一门的学生的学号 课程为 1 5 6 8 SELECTsnoFROMScWHEREcnoIN 1 5 6 8 等价于 SELECTsnoFROMScWHEREcno 1 ORcno 5 ORcno 6 ORcno 8 例16 查不在信息系 IS 数学系 MA 和计算机科学系 CS 的学生的姓名和性别 SELECTSname SsexFROMStudentWHERESdeptnotIN IS MA CS 这样做对不对 考虑用关系代数怎么做 思考题 例17 求下列课程都没有选修的学生的学号 课程为 1 5 6 8 SELECTsnoFROMScWHEREcnonotIN 1 5 6 8 这样做对不对 考虑用关系代数怎么做 SC表 查询结果 实际结果 4 字符匹配 谓词LIKE可以用来字符串的匹配 格式是 NOT LIKE ESCAPE 该语句一般用在模糊查询中 可以是一个完整的字符串 也可以含有通配符 和 其中 百分号代表任意长度的字符串 含空字符串 即长度为零的串 下横线代表任意单个字符 长度为1 ESCAPE具有转义作用 例如 DB 表示以 DB 开头的任意字符串 DB 表示长度为3的以 DB 开头的 第3个字符任意的字符串 又如 DB 表示以 DB 开头的 第四个字符任意的 长度至少为4的任意字符串 DB ESCAPE 表示以 DB 开头的 任意的字符串 例18 查所有姓刘的学生的姓名 学号和性别 SELECTSname Sno SsexFROMStudentWHERESnameLIKE 刘 且名字只有两个字的学生的姓名 学号和性别 LIKE 刘 例19 查名字中第二字为 阳 字的学生的姓名和学号 SELECTSname SnoFROMStudentWHERESnameLIKE 阳 例20 查课程名为DB Design课程的课程号和学分 SELECTCno CcreditFROMCourseWHERECnameLIKE DB Design ESCAPE ESCAPE 短语表示 为换码字符 紧跟在 后面的字符 被转义为普通的 字符 而不是匹配符 例21 查询姓 王 的且名字为三个字的学生的学号 姓名 系别 Selectsno sname sdeptFromstudentWheresnamelike 王 例22 查询不姓 王 的学生的学号 姓名 系别 Selectsno sname sdeptFromstudentWheresnamenotlike 王 注意 通配符只有跟在LIKE后面才能起作用 例如语句 Selectsno sname sdeptFromstudentWheresname 王 执行时 查出姓名为王 的学生的学号 姓名和系别 5 涉及空值的查询谓词ISNULL和ISNOTNULL可用于查询空值和非空值 例23 查询缺考学生的学号和相应的课程号 SELECTSno CnoFROMSCWHEREGradeISNULL 注意 与grade 0是不一样的 关系SC Grade 0的元组 GradeISNULL的元组 例如 SELECTSno CnoFROMSCWHEREGrade NULL 注意 NULL前面只能用IS或者ISNOT 例24 查询系别为空的学生学号 姓名 Selectsno snameFromstudentWheresdeptisnull 例25 查询系别不为空的学生学号 姓名 Selectsno snameFromstudentWheresdeptisnotnull 3 对查询结果排序用户可以用ORDERBY子句指定按照一个或多个属性列的升序 ASC 或降序 DESC 重新排列查询结果 其中ASC为缺省值 例26 查询选修了3号课程的学生的学号及其成绩 查询结果按分数的降序排列 SELECTSno GradeFROMSCWHERECno 3 ORDERBYGradeDESC 例27 查询每个学生的选课信息 按学号升序排列 如果学号相同 按课程号降序排列 Selectsno cno gradeFromscOrderbysnoASC cnoDESC 关系SC 结果表 Orderby后可以跟多个关键字 练习二 1 查询信息系年龄在18到20岁之间的女生的信息 2 查询姓李的且在计算机系的学生的学号 姓名以及年龄3 查询选修了 c02 c05 c03 三门课程中的任何一门课程的学生的学号 课程号和成绩 4 查询没有先行课的课程的课程号 课程名以及学分和课程类别5 查询 c03 课程的成绩 按成绩的降序排列 6 查询课程名中包含 计算机 字样的课程的信息 1 查询信息系年龄在18到20岁之间的女生的信息 Select FromstudentWheresdept IS andsagebetween18and20andssex 女 2 查询姓李的且在计算机系的学生的学号 姓名以及年龄Selectsno sname sageFromstudentWheresdept CS ANDsnamelike 李 3 查询选修了 c02 c05 c03 三门课程中的任何一门课程的学生的学号 课程号和成绩 Selectsno cno gradeFromscWherecnoin c02 c05 c03 或者Selectsno cno gradeFromscWherecno c02 orcno c05 orcno c03 4 查询没有先行课的课程的课程号 课程名以及学分和课程类别Selectcno cname ccredit ctypeFromcourseWherecpnoisnull5 查询 c03 课程的成绩 按成绩的降序排列 Selectsno gradeFromscWherecno c03 Orderbygradedesc 6 查询课程名中包含 计算机 字样的课程的信息 Select FromcourseWherecnamelike 计算机 4 使用集函数SQL提供了许多集函数 主要包括 如果指定DINTINCT短语 则表示在计算时要取消指定列中的重复值 如果不指定DISTINCT短语或指定ALL短语 ALL为缺省值 则表示不取消重复值 以上函数中 除COUNT函数外 对列的数据类型都有一定的限制 如SUM AVG函数只能对数值类型的列进行计算 而MAX MIN类型不能对逻辑类型 TEXT类型或MEMO类型 IMAGE类型等数据进行统计 例28 查询学生总人数 SELECTCOUNT FROMStudent 或者 Selectcount sno Fromstudent 例29 查询1号课程的平均成绩 最高成绩和最低成绩 SelectAVG grade MAX grade MIN grade FromscWherecno 1 计算机执行过程是 先求出所有1号课程的选课记录 放在缓冲区作为中间结果 对中间结果进行求统计值 最后结果 例30 查询 95002 学生的总分 平均成绩 最高成绩和最低成绩 SelectSUM grade AVG grade MAX grade MIN grade FromscWheresno 95002 例31 查询所开课程的门数 Selectcount distinctcno Fromsc 问题 与selectcount fromsc 有什么不同 问题是 如果要查询出每个学生的总分 平均成绩 最高成绩和最低成绩 该怎么办 怎么办 5 对查询结果分组GROUPBY子句可以将查询结果表的各行按一列或多列取值相等的原则进行分组 即分组进行统计 比如按系别进行分组 或按部门进行分组 按车间进行分组统计等 查询结果 例32 查询各个系的在校学生人数 Selectsdept count FromstudentGroupbysdept 或者 Selectsdept count sno FromstudentGroupbysdept 注意 目标列中 一定要包括Groupby后面的作为分组标准的属性列 本例中如果目标列中没有sdept列 那么 其执行结果将是 例33 查询各门课程的选课人数 SELECTCno COUNT Sno FROMSCGROUPBYCno CnoCOUNT Sno 122234344433 例34 查询每个学生的选课门数 平均成绩 总分 最高分和最低分 Selectsno count cno avg grade sum grade max grade min grade FromscGroupbysno 关系SC 结果表 例35 查询男 女学生的人数 Selectssex count FromstudentGroupbyssex 结果表 6 对分组统计结果进行筛选havingHaving条件的作用对象是分组统计的结果 例36 查询选修了三门以上课程的学生的学号 与平均成绩 求解思路 第一步 先求出每个学生的平均成绩以及选课门数 第二步 再筛选出选课门数大于3门的学生学号和平均成绩 第一步 Selectsno avg grade count cno FromscGroupbysno 第二步 Havingcount cno 3 例37 查询每个系的学生的人数 列出学生人数大于200的系的名字与人数 第一步 Selectsdept count sno FromstudentGroupbysdept Havingcount sno 200 第二步 例38 查询平均成绩大于80分的且最低成绩高于75分的学生的平均成绩 并按平均成绩降序排列 第一步 Selectsno avg grade min grade FromscGroupbysno Havingavg grade 80andmin grade 75 第二步 Orderbyavg grade DESC 第三步 例39 查询选修人数小于15个的课程号 第一步 Selectcno count sno FromscGroupbycno Havingcount sno 15 第二步 比较where子句 groupby子句和having子句 1 作用时间 首先 where条件用来选择出满足条件的元组 然后 进行分组统计 等分组统计结果出来之后 最后 用having条件进行最后筛选 2 形式 where条件中不能出现集函数 groupby后一般跟一个列用来表明按什么进行统计 having条件后一定是一个集函数 注意 orderby子句的作用时间总是在数据结果全部出来之后再进行排序 哇 这么多数据 Student表 Course表 SC表 那我们该如何从多张表中查询出更多信息呢 那下面让我们学学表的连接查询吧 3 3 2连接查询 3 3 2连接查询 使用连接查询的情况 1 所要查询的信息涉及两张或两张以上的表 2 查询时所要满足的条件涉及两张或两张以上的表 3 所求信息和查询条件来自不同的表 结果表 关系SC 连接条件是 Student Sno SC Sno 这里实现的是两张表的等值连接 完成这样一种表的横向 组装 的SQL语句是 SelectStudent SC FromStudent SCWHEREstudent sno SC cno 连接条件 关系SC 连接条件仍旧是 Student Sno SC Sno 这里连接条件不变 只是去掉了结果表中的重复列 而这样的连接就从 等值连接 变成了 自然连接 其相应的SQL语句是 SelectStudent Sno Sname Ssex Sage Sdept Cno GradeFromStudent SCWHEREstudent sno SC cno 例41 自然连接Course表和SC表 查询出每个学生所选的课程号 课程名 成绩以及学分 SELECTSno SC cno cname Grade creditFROMSC CourseWHERESC cno Co Course表 SC表 2 自身连接 表与其自己进行连接例42 求5号课程的间接先行课号 C1 Course C2 Course Selectc2 cpnoFromCoursec1 Coursec2Wo 5 andc1 cpno o 例43 求5号课程的间接先行课的课程名 C1 C2 SameFromcoursec1 coursec2 coursec3Wo 5 andc1 cpno oandc2 cpno o C3 3 外连接 在通常的连接中 只有满足连接条件的元组才能作为结果输出 比如以下的查询 SELECTStudent Sno Sname Ssex Sage Sdept Cno GradeFROMStudent SCWHEREStudent Sno SC Sno 我们再来看一下它的执行过程 关系SC 结果表 连接条件是 Student Sno SC Sno 关系SC 外连接条件是 Student Sno SC Sno 万能的空行 万能的空行 相应的SQL语句为 SELECTStudent Sno Sname Ssex Sage Sdept Cno GradeFROMStudent SCWHEREStudent Sno SC Sno 解释 外连接好象是为 号指定的表 即SC表 增加一个万能的 空 行 它可以和另一个表 即student 中所有不能与SC表的其它行连接的元组进行连接 外连接会返回FROM子句中提到的至少一个表的所有行 只要这些行符合任何WHERE或HAVING搜索条件 分类 LEFTOUTERJOIN或LEFTJOIN 左外连接RIGHTOUTERJOIN或RIGHTJOIN 右外连接 1 使用左外连接 左向外连接运算符LEFTOUTERJOIN指明 不管第二个表中是否有匹配的数据 结果将包含第一个表中的所有行 例如SELECTStudent Sno Sname Ssex Sage Sdept Cno GradeFROMStudentLEFTOUTERJOINSCONStudent Sno SC Sno 外连接条件是 Student Sno SC Sno 万能的空行 万能的空行 相应的SQL语句为 SELECTStudent Sno Sname Ssex Sage Sdept Cno GradeFROMStudent SCWHEREStudent Sno SC Sno 解释 外连接输出 号指定的表的所有行 不管另一张表有没有与其匹配的行 或者说是在另一张表设置了一个万能的空行 与找不到匹配行的记录进行匹配 2 使用右外连接 右外连接运算符RIGHTOUTERJOIN指明 不管第一个表中是否有匹配的数据 结果将包含第二个表中的所有行 例如 查询每门课的选课情况 包括输出没有被选的课程的情况 SelectSC sno SC cno C cname C creditFromSCRIGHTOUTERJOINCONSC cno C cno等价于 SelectSC sno SC cno C cname C creditFromCLEFTOUTERJOINSCONC cno SC cno 当然还可以写成 SelectSC sno SC cno C cname C creditFromC SCWHERESC cno C cno 或者 SelectSC sno SC cno C cname C creditFromSC CWHEREC cno C cno 4 复合条件连接WHERE字句中有多个条件的连接操作例44 查询选修2号课程并且成绩在90分以上的所有学生的学号和姓名 结果表 先做连接 连接条件是 Student Sno SC Sno 再做选择运算 条件是 cno 2andgrade 90 Sc表 最后做投影运算 投影到学号和姓名 即我们所要的信息 其结果表为 Student sno sname Student sc Student sno sc sno cno 2 grade 90 例45 查询每个学生的姓名 所选课程名 成绩 前两个表连接 连接条件是 Student sno SC sno Sc表 Course表 再与第三个表连接 连接条件是 SC cno Co 最后 做投影 投影到sname cname grade 相应的查询语句为 Selectsname cname gradeFromstudent sc courseWherestudent sno o o 小结 1 如果查询涉及到两张表以上 即所求信息来自多张表或所求信息和查询条件来自不同的表或者条件涉及多张表 则必须要进行连接查询 再where子句中有连接条件 2 from后面有N张表 则连接条件有N 1个 3 通过多张表的连接查寻 我门实现了从多张表查询出更多综合信息的目标 习题三1 求选了1号课程的学生的学号 姓名 系别 2 求选修了2号课程且分数大于90分的会计系学生的名字 学号 课程号与成绩 3 求数学系或选修了3号课程的学生学号 姓名 系别 4 求数学系且选修了3号课程的学生学号 姓名 系别 5 查询至少选修了一门其直接先行课号为 6 号的课程的学生的学号 6 查询选修 数据库 这门课程的学生的学号 姓名 系别 1 求选了1号课程的学生的学号 姓名 系别 Selectstudent sno sname sdeptFromstudent scWherestudent sno sc snoandcno 1 2 求选修了2号课程且分数大于90分的会计系学生的名字 学号 课程号与成绩 Selectstudent sno sname cno gradeFromstudent scWherestudent sno sc snoand cno 2 andgrade 90andsdept 会计 3 求数学系或选修了3号课程的学生学号 姓名 系别 Selectstudent sno sname sdeptFromstudent scWherestudent sno sc snoand sdept ma orcno 3 4 求数学系且选修了3号课程的学生学号 姓名 系别 Selectstudent sno sname sdeptFromstudent scWherestudent sno sc snoand sdept ma andcno 3 5 查询至少选修了一门其直接先行课号为 6 号的课程的学生的学号 SelectsnoFromsc courseWo oandcpno 6 6 查询选修 数据库 这门课程的学生的学号 姓名 系别 Selectsno sname sdeptFromstudent sc courseWherestudent sno o oandcname 数据库 3 3 3嵌套查询 子查询 将一个查询块嵌套在另一个查询块的WHERE字句或HAVING短语的条件中的查询 例 SELECTSnameFROMStudentWHERESnoIN SELECTSnoFROMSCWHERECno 2 注意 内层查询不能含有ORDERBY子句 ORDERBY永远只能对最终查询结果排序 外层查询或父查询或主查询 子查询或内层查询 1 带有IN谓词的子查询父查询和子查询之间用IN进行连接 判断某个属性列值是否在子查询的结果集中 例47 查询与 刘晨 在同一个系学习的学生 传统做法可以分步进行 1 确定 刘晨 所在的系SELECTSdeptFROMStudentWHERESname 刘晨 其结果是集合 IS 2 查找所有在IS系学习的学生 SELECTSno Sname SdeptFROMStudentWHERESdeptIN IS 用第一步中的查询语句代替集合 IS 且用小括号将该语句括起来 得到嵌套的SQL查询语句为 SELECTSno Sname SdeptFROMStudentWHERESdeptIN SELECTSdeptFROMStudentWHERESname 刘晨 用连接查询 得到 Selects1 sno s1 sname s1 sdeptFromstudents1 students2Wheres1 sdept s2 sdeptands2 sname 刘晨 例48 求选了1号课程的学生的学号 姓名 系别 Selectsno sname sdeptFromstudentWheresnoin selectsnofromscwherecno 1 例49 求选修了2号课程且分数大于90分的计算机系学生的名字 学号 Selectsno snameFromstudentWheresnoin selectsnofromscwherecno 2 andgrade 90 andsdept CS 例50 查询选修 数据库 这门课程的学生的学号 姓名 系别Selecsno sname sdeptFromstudentWheresnoin selectsnofromscwherecnoin selectcnofromcoursewherecname 数据库 例50 2 查询没有选修一门先行课为6号的课程的学生的学号 姓名 系别Selecsno sname sdeptFromstudentWheresnonotin selectsnofromscwherecnoin selectcnofromcoursewherecpno 6 2 带有比较运算符的子查询带有比较运算符的子查询就是父查询与子查询之间用比较运算符进行连接 当用户能确切知道内层查询返回单个结果时 可以用 等比较运算符 如在例47中 查询与 刘晨 在同一个系学习的学生 因为一个学生只能在一个系学习 所以内层查询求出 刘晨 所在的系肯定只有一个结果 查询语句可写为 SELECTSno Sname SdeptFROMStudentWHERESdept SELECTSdeptFROMStudentWHERESname 刘晨 又在例50中查询选修 数据库 这门课程的学生的学号 姓名 系别 最内层求 数据库 课程的课程号时 结果也是唯一的 所以起SQL语句也可以写为 Selecsno sname sdeptFromstudentWheresnoin selectsnofromscwherecno selectcnofromcoursewherecname 数据库 求出选了2号课程的且分数小于平均成绩的学生的学号 姓名与系别 Selectsno sname sdeptFromstudentWheresnoin selectsnofromscwherecno 2 andgrade selectavg grade fromscwherecno 2 求出学生平均成绩小于计算机系学生平均成绩的系的名字 SelectsdeptFromstudent scwherestudetn sno sc snoGroupbysdeptHavingavg grade selectavg grade fromscwheresnoin selectsnofromstudentwheresdept IS 3 带有ANY或ALL谓词的子查询例51 查询其他系中比IS系某一学生年龄小的学生名单 SELECTSname SageFROMStudentWHERESage IS ORDERBYSageDESC 练习四 1 查询5号课程的间接先行课 2 查询平均成绩在90分以上的且最低分在85分以上的学生的学号 姓名 和系别 3 查询学生人数在100人以下的系别 4 查询选修人数在150人以上的课程的名字 5 查询选修课程门数在三门以下的学生的学号 姓名 系别 6 查询至少选修了一门 刘晨 同学所选的课程的学生的学号 姓名 7 查询选修了3号课程的且成绩低于平均成绩的学生的学号和姓名 系别 1 查询5号课程的间接先行课 Selectc2 cpnoFromcoursec1 coursec2Wherec1 cpno o 5 或SelectcpnoFromcourseWherecno Selectcpnofromcoursewherecno 5 2 查询平均成绩在90分以上的且最低分在85分以上的学生的学号 姓名 和系别 Selectsno sname sdeptFromstudentWheresnoin selectsnofromscgroupbysnohavingavg grade 90andmin grade 85 3 查询学生人数在100人以下的系别 SelectsdeptFromstudentGroupbysdeptHavingcount sno 100 4 查询选修人数在150人以上的课程的名字 SelectcnameFromcourseWherecnoin selectcnofromscgroupbycnohavingcount sno 150 5 查询选修课程门数在三门以下的学生的学号 姓名 系别 Selectsno sname sdeptFromstudentWheresnoin selectsnofromscgroupbysnohavingcount cno 3 6 查询至少选修了一门 刘晨 同学所选的课程的学生的学号 姓名 Selectsno snameFromstudentWheresnoin selectsnofromscwherecnoin selectcnofromscwheresno selectsnofromstudentwheresname 刘晨 7 查询选修了3号课程的且成绩低于平均成绩的学生的学号和姓名 系别 Selectsno sname sdeptFromstudentWheresnoin selectsnofromscwherecno 3 andgrade selectavg grade fromscwherecno 3 4 带有EXISTS谓词的子查询EXISTS代表存在量词 带有EXISTS谓词的子查询不返回任何实际数据 它只产生逻辑真值 true 或逻辑假值 false 例52 查询所有选修了1号课程的学生姓名 思路 与前面不同 这里是逐个判断student表中每个学生 在sc表中是否存在这个学生的1号课程的选课记录 如果存在 表示该学生选了1号课程 把该学生的姓名加到结果集 否则 表示该学生未选1号课程 接着判断下一个学生是否存在1号课程的选课记录 直到所有学生都判断过 SelectsnameFromstudentWhereexists select fromscwheresno student snoandcno 1 表示与外层查询相对应的选课记录 也称相关条件 1 使用存在量词EXISTS后 若内层查询结果非空 则外层的where子句返回真值 否则返回假值 2 内层查询的目标列只用 3 内层查询依赖于外层查询 例53 求选修了2号课程且分数大于90分的计算机系学生的名字 学号 Selectsno snameFromstudentWheresdept CS andexists select fromscwheresno student snoandcno 2 andgrade 90 例54 查询选修 数据库 这门课程的学生的学号 姓名 系别Selecsno sname sdeptFromstudentWhereexists select fromscwheresno student snoandcnoin selectcnofromcoursewherecname 数据库 解法2 Selecsno sname sdeptFromstudentWheresnoin selectsnofromscwhereexists select fromcoursewherecno oandcname 数据库 例55 查询没有选课的学生的姓名与系别 例56 查询没有选3号课程的学生的姓名 例55 查询没有选课的学生的姓名与系别 Selectsname sdeptFromstudentWherenotexists select fromscwheresno student sno 解法二 Selectsname sdep