高级查询PPT课件

专题 SQL高级查询 除法SQL实现及其它 snosname1张三2李四3王五4马六 表S 表C cnocname1数据库2离散数学3操作系统 表SC snocnograde1178128721663280335643684190137742812283 select fromscorderbysno snocnograde1178128713772166228332803356419042814368 select fromscorderbycno snocnograde1178216641901287328042812283335643681377 查询选修了全部课程的学生号码 关系代数表达式 Sno Cno SC Cno C 结果应该是 1 4 两个学号SQL表达式 Selectsnofromscwherecnoin selectcnofromc 1 2 3 这样对吗 显然不对 因为结果只是选修了某一门或某几门课程的学生学号 而不是选修了全部课程的学生学号 查询选修了全部课程的学生号码 关系代数表达式 Sno Cno SC Cno C 怎么办 说起来也简单 既然是学了全部课程 那么全部课程有几门 Selectcount fromc得到全部课程数3再按学号分组统计每个学号对应的修课数目 Selectsno count fromscgroupbysno 查询选修了全部课程的学生号码 Selectsno count fromscgroupbysno结果 snocount 132232全部课程数是343 Selectsnofromscgroupbysnohavingcount Selectcount fromc 查询被全部学生选修的课程号码 关系代数表达式 Cno Sno SC sno S Selectcount fromS得到全部学生数4再按课号分组统计每个课号对应的修课人数 Selectcno count fromscgroupbycno 查询被全部学生选修的课程号码 Selectcno count fromscgroupbycno结果 cnocount 1324全部学生数是433 Selectcnofromscgroupbycnohavingcount Selectcount fromS 麻烦的是类似这样的查询要求 查询至少选修了2号学生所学的全部课程的学生学号同样我们不能这样做 selectsnofromscwherecnoin selectcnofromscwheresno 2 我们需要EXISTS来帮忙 10 exists是用来判断是否存在的 当exists 查询 中的查询存在结果时则返回真 否则返回假 notexists则相反它所在的查询属于相关子查询 即子查询的查询条件依赖于外层父查询的某个属性值 处理过程一般为 取外层查询的第一个元组 根据它与内层查询相关的属性值处理内层查询 若where子句返回true 则将此元组放入结果表 然后取下一个元组 重复这个过程直到全部检查完为止 11 即 exists做为where条件时 是先对where前的主查询进行查询 然后用主查询的结果一个一个的代入exists的子查询进行判断 如果为真则输出当前这一条主查询的结果 否则不输出 12 使用EXISTS 首先检查主查询 然后运行子查询直到它找到第一个匹配项 这就节省了时间 而执行IN子查询时 首先执行子查询 并将获得的结果列表存放在一个加了索引的临时表中 在执行子查询之前 系统先将主查询挂起 待子查询执行完毕 存放在临时表中以后再执行主查询 因此 使用EXISTS比使用IN通常查询速度快 13 查询至少选修了学号是2号的学生所选修的全部课程的学生号码 它表示的语义为 不存在这样的课程 学号为2号的学生选了 而学号为h的学生却没有选Notexists select fromscasywheresno 2 andnotexists select fromscwheresno handcno o 注意 千万不能写成下面这样 Notexists select fromscasywheresno 2 andexists select fromscwheresno handcno o 14 理解了上式 完整的SQL查询为 selectdistinctsnofromscasxwherenotexists select fromscasywheresno 2 andnotexists select fromscwheresno x snoandcno o sno124 表SC select fromscasywheresno 2 snocnograde21662283 snocnograde1178128713772166228332803356419042814368 selectdistinctsnofromscasxwherenotexists select fromscasywheresno 2 andnotexists select fromscwheresno x snoandcno o 真难理解啊 好理解的解法还有木有 有 请看以下查询 select fromscasx select fromscwheresno 2 o o snocnograde1178128721663280335643684190137742812283 snocnograde21662283 snocnogradesno 1cno 1grade 111782166128722832166216622832283328022834190216642812283 X Y 查询结果 根据分析 改写原SQL语句 select fromscasx select fromscwheresno 2 o o完整表达式如下 Selectx snofromscasx select fromscwheresno 2 o ogroupbyx snohavingcount selectcount fromscwheresno 2 最牛最简单的除法 Select fromswherenotexists Selectcnofromscwheresno 2 ExceptSelectcnofromscwheresno s sno 每次子查询代入父查询S表的一个学号 若差集为空 不就表示s sno这个学号的学生至少学了 2 号学生所学的全部课程了吗 notexists 空 返回true 那么父查询就显示该学生 否则就不显示 不管显示与否 继续代入s表的下一个学号 直到处理完s的所有记录 整个查询结束 What 还不理解 暂时算了 发给你慢慢琢磨吧 查询每一门课程及其先修课程 查询结果中显示课程号 课程名和先修课程名 由于SQL是按行处理数据的 所以在单个course cno cname cpno 表中只能得到课程号 课程名和先修课程号 而不是先修课程名 为了得到先修课程名 必须将先修课程号转换成先修课程名 而先修课程号作为一门课程 它只不过是course表中的另外一条记录 需要两个course表 别名分别取A B 做一个等值连接 等值条件是什么 0000001DB Design00000060000002C语言基础00000270000003UNIX00000130000004C 程序设计00000020000005现代物流概论NULL0000006数据库原理00000100000007JAVA程序设计00000020000008电子商务00000270000009实用英语NULL0000010数据结构00000020000011邓小平理论NULL0000012体育NULL0000013操作系统00000020000014经济基础知识NULL0000027计算机基础NULL0000032多媒体技术00000270000034高等数学NULL0000039基础会计NULL0000045软件工程00000100000052财务会计0000039 0000001DB Design00000060000002C语言基础00000270000003UNIX00000130000004C 程序设计00000020000005现代物流概论NULL0000006数据库原理00000100000007JAVA程序设计00000020000008电子商务00000270000009实用英语NULL0000010数据结构00000020000011邓小平理论NULL0000012体育NULL0000013操作系统00000020000014经济基础知识NULL0000027计算机基础NULL0000032多媒体技术00000270000034高等数学NULL0000039基础会计NULL0000045软件工程00000100000052财务会计0000039 A course cnocnamecpno B course cnocnamecpno SelectA cno A cname B cnamefromscA scBwhereA cpno B cno 查询和 刘涛 在一个班级的学生的信息 很简单的查询 关键在于熟悉表的结构以及表间关系 首先 刘涛在哪个班级呢 selectclassfromstudentswheresname 刘涛 刘涛所在的班级名字不就知道了 一个或几个字符串而已 接下来一步搞定 select fromstudentswhereclassin 或自连接 单独一个students表不行 selects1 fromstudentss1 studentss2wheres1 class s2 classands2 sname 刘涛 查询选修了 计算机基础 课的学生的学号 姓名 1 连接方式selectstudents sno snamefromstudents sc coursewherestudents sno o oandcname 计算机基础 2 子查询嵌套方式selectsno snamefromstudentswheresnoin selectsnofromscwherecnoin selectcnofromcoursewherecname 计算机基础 查询没有选修课程的学生的信息 1 子查询in方式select fromstudentswheresnonotin selectdistinctsnofromsc 2 子查询exists方式select fromstudentswherenotexists select fromscwherestudents sno sc sno 查询每个学生超过该门课程平均成绩的学号 课号 根据查询要求 分析此查询只涉及sc表termsnocnogradepoint103011010000011881103011020000011751 5103011020000027791 5103111010000008861103111010000009580103111010000011851103111010000027871 查询每个学生超过该门课程平均成绩的学号 课号 根据cno分组 求每门课的平均成绩Selectcno avg grade fromscgroupbycno cnoavg grade 000000182 5000000295 0000000674 0000000787 0000000874 625000000969 57000001072 0000001180 0000001386 0000002781 57000003475 625000003971 75000005275 5 termsnocnogradepoint103011010000011881103011020000011751 5103011020000027791 510311101000000886110311101000000958010311101000001185103111010000027871sc表比较条件 cno相等 grade avg grade 查询每个学生超过该门课程平均成绩的学号 课号 selectsno cnofromscassc1wheregrade selectavg grade o cno 查询每个学生超过该门课程平均成绩的学号 课号 selectsno cnofromscassc1wheregrade selectavg grade o cno 查询既选修了 计算机基础 又选修了 C语言基础 的学生的学号 方法1 自连接selectsc1 snofromscsc1 scsc2wheresc1 sno o selectcnofromcoursewher