8-2杨梅-计算机等级考试-三级数据库技术-精-第7章 高级数据库查询

计算机等级考试三级数据库技术精讲班主讲老师：杨梅,1替代表达式的子查询在SELECT的选择列表中嵌入了一个只返回一个标量值的子查询。实例：ELECTCname，Address（SELECTCOUNT(*)FROMTable_CustomerbONa.CardID=b.CardIDWHERECustomerID=C001）ASTotalTimesFROMTable_CustmerWhereCustomerID=C001,第四节其他形式的子查询,2派生表也称为内联视图，是将子查询作为一个表处理，产生的新表为“派生表”。实例：查询至少买了C001和C002两种商品的顾客号和顾客名。SELECTCustomerID,CNameFROM(SELECT*FROMTable_SaleBillaJOINTable_SaleBillDetailbONa.SaleBillID=b.SaleBillIDWHEREGoodsID=G001)AST1,第四节其他形式的子查询,JOIN(SELECT*FROMTable_SaleBillaJOINTable_SaleBillDetailbONa.SaleBillID=b.SaleBillIDWHEREGoodsID=G002)AST2ONT1.CardID=T2.CardIDJOINTable_CustomercONc.CardID=T1.CardID,第四节其他形式的子查询,1开窗函数在SQLServer中，一组行被称为一个窗口。与聚合函数一样，开窗函数也是对行集组进行聚合计算，但是它不像普通聚合函数那样每组只返回一个值，开窗函数可以为每组返回多个值，因为开窗函数所执行聚合计算的行集组是窗口。,第五节其他一些查询功能,与聚合函数不同的是，开窗函数在聚合函数后增加了一个OVER关键字。开窗函数的调用格式为：函数名（列）OVER（选项）OVER关键字：表示把函数当成开窗函数而不是聚合函数。SQL标准允许将所有聚合函数用做开窗函数，使用OVER关键字来区分这两种用法。,第五节其他一些查询功能,实例1：将OVER字句与聚合函数结合使用设有课程表Course（Cno，CName，Credit，Semester）查询全部课程的Cno，CName，Semester，Credit信息及其该学期开设课程的总、最高、平均、最低Credit。,第五节其他一些查询功能,SELECTCno，CName，Semester,CreditSUM(Credit)OVER(PARTITIONBYSemester)ASTotal，AVG(Credit)OVER(PARTITIONBYSemester)ASAvg，MIN(Credit)OVER(PARTITIONBYSemester)ASMin，MAX(Credit)OVER(PARTITIONBYSemester)ASMax，FROMCourse,第五节其他一些查询功能,实例2：将OVER字句与排名函数一起使用排名函数RANK()RANK()OVER(,n)注意:排名函数具有不确定性。排名从1开始，不一定是连续整数。,第五节其他一些查询功能,SELECTOrderID,ProductID,OrderQtyRANK()OVER(PARTITIONBYOrderIDORDERBYOrderQtyDESC)ASRANKFROMOrderDetailORDERBYOrderID,第五节其他一些查询功能,其他排名函数DENSE_RANK()排名是连续整数NTILE()将有序分区中的行划分到指定数目的组中，编号从1开始，函数返回此行所属的组的编号。ROW_NUMBER()返回结果集中每个分区内的序列号，每个分区的第一行从1开始。,第五节其他一些查询功能,2公用表表达式公用表表达式（CommonTableExpression，CTE）：将查询结果集指定一个临时名字，这些命名的结果集就是公用表表达式。格式：WITH,n:=Expression_name(column_name,n)AS(SELECT语句),第五节其他一些查询功能,例：定义一个统计每个会员购买商品总次数的CTE，并利用CTE查询会员卡号和购买商品的次数。定义：WITHBuyCount（CardID,Counts）AS(SELECTCardID,Count(*)FROMTable_SaleBillGROUPBYCardID)使用：AS(SELECTCardID,CountsFROMBuyCountORDERBYCounts,第五节其他一些查询功能,1设某数据库中有旅客表A（旅客编号，城市）和旅客表B（旅客编号，城市），现将所有旅客的数据存储这两张表中。请补全如下查询语句，使得该查询语句能查询所有旅客所在的全部的不重复的城市。SELECT城市FROM旅客表A（）SELECT城市FROM旅客表B答案：UNION,例题讲解,2设有购买表（顾客号，商品号，购买时间）。现要查询顾客A与顾客B购买的相同商品。有下列查询语句：SELECT商品号FROM购买表WHERE顾客号=AAND商品号IN(SELECT商品号FROM购买表WHERE顾客号=B)SELECT商品号FROM购买表WHERE顾客号=AEXCEPTSELECT商品号FROM购买表WHERE顾客号=BSELECT商品号FROM购买表WHERE顾客号=AINTERSECTSELECT商品号FROM购买表WHERE顾客号=BSELECT商品号FROM购买表WHERE顾客号=AUNIONSELECT商品号FROM购买表WHERE顾客号=B,上述语句中，能够实现该查询要求的是（）。A仅和B仅和C仅和D仅答案：B,例题讲解,3设有选课表（学号，课程号，成绩），现要统计每门课程的选课人数，并将结果保存到新表：选课情况表。下列语句中正确的是（）。ASELECT课程号,COUNT(*)选课人数FROM选课表INTO选课情况表GROUPBY课程号BSELECT课程号,COUNT(*)选课人数INTO选课情况表FROM选课表GROUPBY课程号CSELECT课程号,COUNT(*)FROM选课表INTO选课情况表（课程号,选课人数）GROUPBY课程号DSELECT课程号,COUNT(*)INTO选课情况表（课程号,选课人数）FROM选课表GROUPBY课程号答案：B,例题讲解,4设某数据库中有学生表（学号，姓名，所在系）和选课表（学号，课程号，成绩）。现要查询没选课的学生姓名和所在系。下列语句中能够实现该查询要求的是（）。ASELECT姓名，所在系FROM学生表aLEFTJOIN选课表bONa学号=b学号WHEREa学号ISNULLBSELECT姓名，所在系FROM学生表aLEFTJOIN选课表bONa学号=b学号WHEREb学号ISNULLCSELECT姓名，所在系FROM学生表aRIGHTJOIN选课表bONa学号=b学号WHEREa学号ISNULLDSELECT姓名，所在系FROM学生表aRIGHTJOIN选课表bONa学号=b学号WHEREb学号ISNULL答案：B,5设在SQLServer2008中，用户U1在DB1数据库中创建了#Temp表。下列关于#Temp表的说法中，正确的是（）。A在所有用户U1发起的连接中，都可以查询#Temp表数据B只有在创建#Temp表的连接中才可以查询#Temp表数据C在创建#Temp表的连接未断开时，DB1数据库的所有用户都可以查询#Temp表数据D在创建#Temp表的连接断开时，DB1数据库的所有用户仍可以查询#Temp表数据答案：B,例题讲解,6设在采用SQLServer2008数据库的图书馆应用系统中有三个基本表，表结构如下所示，请用SQL语句完成下列两个查询：BORROWER：,例题讲解,LOANS：,例题讲解,BOOKS：（1）检索至少借了5本书的同学的借书证号、姓名、系名和借书数。（2）检索借书和王丽同学所借图书中的任意一本相同的学生姓名、系名、书名和借书日期。,例题讲解,【解题思路】（1）采用两表联合查询。以两表借书证号为相等条件，在结果集中用GROUPBY按照借书证号来分类，并且用HAVING关键字统计出符合条件的记录数。（2）采用IN关键字进行两表联合查询，在BORROWER和LOANS联合的结果集中查找满足第三个表指定的条件。,例题讲解,【参考答案】（1）SELECTLOANS.借书证号，姓名，系名，COUNT(*)AS借书数量FROMBORROWER,LOANSWHEREBORROWER.借书证号=LOANS.借书证号GROUPBYLOANS.借书证号HAVINGCOUNT(*)=5;,例题讲解,（2）SELECT姓名，系名，书名，借书日期FRO