数据库技术及应用（SQL Server ）2.5

数据库技术及应用（SQLServer）教学单元2.5第7章

SELECT数据查询（ANSI和子查询）案例2-7-1图书管理ANSI连接查询案例2-7-2图书管理SELECT子查询SELECT数据查询学习导航2SELECT数据查询知识框架3单元2.5SELECT数据查询（ANSI和子查询）能力目标能够根据数据库应用系统的功能需求对表进行ANSI连接查询能够根据数据库应用系统的功能需求对表进行子查询能够根据数据库应用系统的功能需求对表进行联合查询能够阅读并熟练书写SELECT查询语句和有关参数(英文)知识目标ANSI连接查询的语法子查询的基本应用联合查询的基本应用通过了解“图书馆集成管理系统”的查询界面（图书数据查询、读者数据查询、流通数据查询、流通日志查询），培养解决复杂问题的能力。*拓展阅读7图书馆集成管理系统—应用界面24单元2.5SELECT数据查询（ANSI和子查询）知识目标ANSI连接查询的语法子查询的基本应用联合查询的基本应用素质目标培养与用户的沟通能力，以便很好地满足用户的应用需求培养应用所学知识解决实际问题的能力5案例2图书管理系统案例2-7-1图书管理ANSI连接查询案例2-7-2图书管理SELECT子查询工作任务单元2.5SELECT数据查询（ANSI和子查询）6单元2.5SELECT数据查询（ANSI和子查询）7ANSI连接查询一子查询二联合查询三一、ANSI连接查询8案例2-7-1图书管理ANSI连接查询根据图书管理系统的功能需求，应用T-SQL的SELECT查询语句对数据库“Library”中所创建的表进行ANSI连接查询。工作任务回顾：SELECT查询语句基本结构9SELECT[ALL|DISTINCT][TOPn]表达式列表[INTO新表名]FROM{基表|视图}[,...n]

--回顾表的连接，视图见后续[WHERE查询条件][GROUPBY分组列名表][HAVING逻辑表达式][ORDERBY排序列名表[ASC|DESC]]SELECT…FROM一、ANSI连接查询10说明：在SQLServer中，可以使用两种语法形式FROM子句（已介绍），连接条件写在WHERE子句的逻辑表达式中，从而实现表的连接（早期）。ANSI（AmericanNationalStandardsInstitute—美国国家标准学会）连接语法形式，在FROM子句中使用JOIN…ON关键字，连接条件写在ON之后，从而实现表的连接。SQLServer2022推荐使用ANSI形式的连接。SELECT…FROM…JOIN…ON一、ANSI连接查询11语法：FROM表名1{[连接类型]JOIN表名2ON连接条件}[...n]说明：实现表与表的两两连接，表1和表2连接之后还可以继续与表3,…,表n连接，最多可以连接256个表。连接条件放在ON关键字后。特别注意的是此语句也可以连接视图，下一章介绍。连接类型:INNERJOIN：内连接。LEFT[OUTER]JOIN：左外连接。RIGHT[OUTER]JOIN：右外连接。CROSSJOIN：交叉连接。1.SELECT…FROM…JOIN…ON一、ANSI连接查询12语法：FROM表名1INNERJOIN表名2ON

连接表达式说明：从两个或两个以上的表的笛卡儿积中，选出符合连接条件的数据行。如果数据行无法满足连接条件，则将其丢弃。内连接消除了与另一个表中不匹配的数据行。2.内连接一、ANSI连接查询13【例7-34】从图书管理数据库“Library”中查询每位读者的详细信息（读者及读者类型），允许有重复列。代码：USELibraryGOSELECTReader.*,ReaderType.*FROMReaderINNERJOINReaderTypeONReader.TypeID=ReaderType.TypeID--内连接（1）等值连接一、ANSI连接查询14代码：SELECTReader.*,ReaderType.*FROMReaderINNERJOINReaderTypeON

Reader.TypeID=ReaderType.TypeID--内连接查询结果：（1）等值连接一、ANSI连接查询15【例7-35】查询每个读者的详细信息（读者及借阅图书的信息），不允许有重复列。代码：SELECTReader.*,Borrow.LendDate,Borrow.ReturnDate,Book.BID,Book.Bname,Book.Author,Book.Price--投影消除重复列FROMReaderINNERJOINBorrowON

Reader.RID=Borrow.RID--表1内连接表2INNERJOINBookON

Borrow.BID=Book.BID--再内连接表3（2）自然连接一、ANSI连接查询16查询结果：（2）自然连接一、ANSI连接查询17外连接返回FROM子句中指定的至少一个表或视图中的所有行，只要这些行符合任何WHERE选择（不包含ON之后的连接条件）或HAVING限定条件。外连接又分为左外连接、右外连接和全外连接。左外连接对连接中左边的表不加限制；右外连接对连接中右边的表不加限制；全外连接对两个表都不加限制，两个表中的所有行都会包括在结果集中。3.外连接一、ANSI连接查询18语法：FROM表名1LEFT[OUTER]JOIN表名2ON连接表达式说明：连接结果保留表1没形成连接的行，表2相应的各列为NULL值。1.左外连接一、ANSI连接查询19【例7-36】从表“Reader”和表“Borrow”中查询出读者的借阅情况，包括没有借过书的读者情况。代码：SELECTReader.*,Borrow.RID,BIDFROMReaderLEFTOUTERJOINBorrow--左外连接ONReader.RID=Borrow.RID查询结果：1.左外连接一、ANSI连接查询20说明：连接结果保留了表“Reader”中不满足等值条件的第3行和第4行，表“Borrow”中相应的各列为NULL值，说明读者张英和李亚茜没有借过书。1.左外连接一、ANSI连接查询21语法：FROM表名1RIGHT[OUTER]JOIN表名2ON连接表达式说明：连接结果保留表2没形成连接的行，表1相应的列为NULL值。案例：从表“Borrow”和表“Book”中查询出图书被借阅的情况，包括没有被借的图书情况。2.右外连接一、ANSI连接查询22【例7-37】从表“Borrow”和表“Book”中查询出图书被借阅的情况，包括没有被借的图书情况。代码：SELECTBorrow.RID,Borrow.BID,Book.BID,Bname,AuthorFROMBorrowRIGHTOUTERJOINBook--右外连接ONBorrow.BID=Book.BID2.右外连接一、ANSI连接查询23查询结果：2.右外连接I一、ANSI连接查询24说明：连接结果加入了表“Book”中不满足等值条件的第1、4、8、10行，表“Borrow”中相应的列为NULL，说明图书《ERP从内部集成开始》、《ERP系统的集成应用》、《SQLServer2008数据库设计与实现》和《数据库系统概论》四本书没有被借出过，《SAP基础教程》被借出过两次。2.右外连接一、ANSI连接查询25语法：FROM表名1FULL[OUTER]JOIN表名2ON连接表达式说明：连接结果保留表1没形成连接的元组，表2相应的列为NULL值；连接结果也保留表2没形成连接的元组，表1相应的列为NULL值。3.全外连接一、ANSI连接查询26【例7-38】借阅和读者全外连接。代码：SELECTReader.*,Borrow.RID,Borrow.BIDFROMBorrowFULLOUTERJOINReader--全外连接ONBorrow.RID=Reader.RID查询结果：查询结果与左外连接相同，因为借阅表中读者的编号均在读者表中存在，都能形成连接。3.全外连接一、ANSI连接查询27语法：FROM表名1

别名1JOIN表名1

别名2ON

连接表达式说明：表可以通过自连接实现自身的连接运算。自连接可以看作是一张表的两个副本之间进行的连接，在自连接中，必须为表指定两个不同的别名，使之在逻辑上成为两张表。4.自连接一、ANSI连接查询28【例7-39】从图书管理数据库“Library”中查询出借了两本以上图书的读者的借书信息。步骤1：表“Borrow”与表“Borrow”自连接代码：SELECTx.RID,x.BID,x.ReturnDate,y.RID,y.BIDFROMBorrowxJOINBorrowyONx.RID=y.RID--读者编号等值条件自连接WHEREx.ReturnDateISNULLANDy.ReturnDateISNULL--还期为空，即尚未归还4.自连接一、ANSI连接查询29查询结果：4.自连接一、ANSI连接查询30说明：仔细观察，可以发现连接查询结果的第5行和第6行数据中读者编号“RID”相同，但书号“BID”不同，说明该读者至少借阅了两本书。在以上查询的WHERE子句中加上“x.BID<>y.BID”查询条件，就可以查询出借阅了两本以上的读者编号了。这里“x.ReturnDateISNULL”描述了读者借阅图书尚未归还的条件。4.自连接一、ANSI连接查询31步骤2：对表“Borrow”与表“Borrow”自连接加选择条件和投影操作。代码：SELECTx.RID,x.BID,x.ReturnDate--投影去重复列名FROMBorrowxJOINBorrowyONx.RID=y.RID--读者编号等值条件自连接WHEREx.ReturnDateISNULLANDy.ReturnDateISNULL--还期为空，即尚未归还ANDx.BID<>y.BID--图书编号不同，即两本不同的书4.自连接一、ANSI连接查询32查询结果：说明：仔细观察，可以发现连接查询结果的第5行和第6行数据中读者编号“RID”相同，但书号“BID”不同，说明该读者至少借阅了两本书。在以上查询的WHERE子句中加上“x.BID<>y.BID”查询条件，就可以查询出借阅了两本以上的读者编号了。这里“x.ReturnDateISNULL”描述了读者借阅图书尚未归还的条件。4.自连接一、ANSI连接查询33语法：FROM表名1CROSSJOIN表名2说明：两个表进行笛卡儿积计算，等价于FROM表名1，表名2之后不加WHERE连接条件逻辑表达式。一般没有意义5.交叉连接单元2.5SELECT数据查询（ANSI和子查询）34ANSI连接查询一子查询二联合查询三二、子查询35案例2-7-2图书管理SELECT子查询根据图书管理系统的功能需求，应用T-SQL的SELECT子查询对数据库“Library”中所创建的表进行数据查询和数据更新操作。工作任务二、子查询36功能说明：子查询指在一个SELECT查询语句的WHERE子句中包含另一个SELCET查询语句，或者将一个SELECT查询语句嵌入在另一个语句中成为其一部分。在查询语句中，在外层SELECT查询语句称为主查询，WHERE子句中的SELECT查询语句被称为子查询。WHERE子句中的子查询可描述复杂的查询条件，也称为嵌套查询。嵌套查询一般会涉及到两个以上的表，所做的查询有的也可以采用连接查询或者用几条查询语句完成。采用子查询有时会提高算法的时间和空间效率，但算法不易读懂，读者应权衡利弊进行选择。SELECT…FROM…SELECT…（一）IN子查询37语法：表达式[NOT]IN(列表|子查询)说明：如果表达式的值（NOT，不）与子查询返回的任何值相等，逻辑表达式的值为真。子查询的SELECT投影列表中只能指定一个表达式。此表达式的行数据构成了括号内集合的所有元素，与集合的概念相同，集合内的元素是消除了重复值的。[NOT]IN子查询（一）IN子查询38【例7-41】从图书管理数据库“Library”中查询出借阅过“人民邮电出版社”出版图书的读者编号（不包括重复的行）。代码：USELibrarySELECTDISTINCTRID--投影满足条件的读者编号，去重复行FROMBorrow--对于表“Borrow”中的每一行数据--判断其所借图书的编号是否在对表“Book”子查询的集合中WHEREBIDIN(SELECTBIDFROMBookWHEREPublisher='人民邮电出版社')--人民邮电出版社图书的编号集合查询结果：[NOT]IN子查询（一）IN子查询39【例7-42】从图书管理数据库“Library”中查询出没有借过书的读者信息。代码：SELECT*FROMReader --对于表中的每一位读者--判断其读者编号是否不在子查询的集合中WHERERIDNOTIN(SELECTRIDFROMBorrow) --借了书的读者编号集合查询结果：[NOT]IN子查询语法：表达式比较运算符SOME|ANY(子查询)说明：若表达式的值在比较关系上满足子查询返回的任何一个值，则逻辑表达式的值为真。子查询的SELECT投影列表中只能指定一个表达式。SOME和ANY的用法相同。“=SOME|ANY”等价于“IN”，“<>SOME|ANY”没有意义。1.SOME|ANY子查询（二）SOME|ANY和ALL子查询40=SOME|ANY(子查询)等价IN(子查询)【例7-43】从图书数据库“Library”中查询当前借出的清华大学出版社的图书借期等信息。代码：SELECTBorrow.RID,Borrow.BID,Borrow.LendDate,Book.Bname,Book.Publisher,Book.LentOutFROMBorrow,Book --对于借阅表中的每借出的一本书WHEREBorrow.BID=SOME--图书编号是否在子查询集合中(SELECTBID--已经借出清华大学出版社的图书编号集合FROMBook--已经借出的清华大学出版社出版的图书WHERELentOut='True'ANDPublisher='清华大学出版社')ANDBorrow.BID=Book.BID --等值连接条件（1）SOME|ANY子查询（二）SOME|ANY和ALL子查询41【例7-43】从图书数据库“Library”中查询当前借出的清华大学出版社的图书借期等信息。查询结果：（1）SOME|ANY子查询（二）SOME|ANY和ALL子查询42语法：表达式比较运算符ALL(子查询)说明：如果表达式的值在比较关系上满足子查询返回的每一个值，则逻辑表达式的值为真。子查询的SELECT投影列表中只能指定一个表达式。“<>ALL”等价于“NOTIN”，“=ALL”没有意义。2.ALL子查询（二）SOME|ANY和ALL子查询43<>ALL(子查询)等价NOTIN(子查询)（二）SOME|ANY和ALL子查询44【例7-44】从借阅表“Borrow”中查询出读者编号RID最大的读者的借书情况。代码：SELECT*FROMBorrowWHERERID>=ALL(SELECTRIDFROMReader)--子查询查询结果：2.ALL子查询RID>=ALL(SELECTRIDFROMReader)等价RID=(SELECTMAX(RID)FROMReader)语法：[NOT]EXISTS(子查询)说明：当子查询的结果存在（不为空集）时，逻辑表达式的值为真，不存在（空集）逻辑表达式的值为假。NOTEXISTS则与EXISTS相反。在EXISTS引入子查询时，在子查询的SELECT投影列表中可以指定多个表达式。3.EXISTS(子查询)（三）EXISTS子查询45（三）EXISTS子查询46【例7-45】从图书管理数据库“Library”中，用EXISTS子查询查询出借阅了“人民邮电出版社”出版图书的读者编号。代码：SELECTDISTINCTRIDFROMBorrow--对于每一条借阅信息的图书编号BIDWHEREEXISTS--判定子查询是否有满足子查询条件的返回值--主查询中的Borrow.BID等于子查询中的Book.BID(SELECT*FROMBookWHEREBorrow.BID=Book.BID

ANDPublisher='人民邮电出版社')--人民邮电出版社出版的图书EXISTS(子查询)（三）EXISTS子查询47【例7-45】从图书管理数据库“Library”中，用EXISTS子查询查询出借阅了“人民邮电出版社”出版图书的读者编号。查询结果：注意：代码中WHERE子句后的“Borrow.BID=Book.BID”并不是等值连接条件，而是子查询中的选择条件，判断主查询的“Borrow.BID”与子查询的“Book.BID”是否相等。EXISTS(子查询)等价语句：SELECTDISTINCTBorrow.RIDFROMBook,BorrowWHEREBook.BID=Borrow.BIDANDPublisher='人民邮电出版社'UPDATE更新数据语句：回顾[例6-15】计算读者表“Reader”中的已借书数量列“Lendnum”代码：UPDATEReaderSETLendnum=--从借阅表“Borrow”中统计出每个读者借书的册数(SELECTCOUNT(*)FROMBorrowWHEREReturnDateISNULLANDReader.RID=Borrow.RID)在UPDATE语句中的使用（四）子查询在其他语句中的使用48【例7-46】计算借阅表“Borrow”中的应还日期列“SReturnDate”值。代码：UPDATEBorrow--更新借阅表数据SETSReturnDate=--对借阅表的每一行的应还日期列赋值--函数的第二个参数“限借天数”加上第三个参数“借期”得到“应还日期”DATEADD(dd,(SELECTReaderType.LimitDays--子查询得到限借天数FROMReaderINNERJOINReaderType--等值连接ONReader.TypeID=ReaderType.TypeID--等值条件描述WHEREBorrow.RID=Reader.RID),--借阅表对应的读者编号条件描述Borrow.LendDate)--函数的第三个参数为借期在UPDATE语句中的使用（四）子查询在其他语句中的使用49（四）子查询在其他语句中的使用50【例7-46】“SreturnDate”值。查询结果：在UPDATE语句中的使用主要区别：子查询中的表和主查询中的表分为内外嵌套的查询，而连接查询是同在一个层面的查询。（五）子查询与连接查询的比较51【例7-47】查询出图书表“Book”中价格最低的图书的编号和书名，采用子查询完成此任务。代码：SELECTBIDAS图书编号,BnameAS书名FROMBookWHEREPrice=(SELECTMIN(Price)FROMBook)查询结果：图书编号

书名-------------------------------------------------------TP311.138/78数据库系统概论（五）子查询与连接查询的比较52子查询子查询(内)：SELECTMIN(Price)FROMBook为25.00主查询(外)：Price=SOME(SELECTMIN(Price)FROMBook)等价Price=(SELECTMIN(Price)FROMBook)等价Price=25.00【例7-48】查询读者编号、读者姓名、所借图书名和借阅时间。代码：SELECTReader.RID,Reader.Rname,Book.Bname,Borrow.LendDateFROMReader,Borrow,BookWHEREReader.RID=Borrow.RIDANDBook.BID=Borrow.BID连接查询（五）子查询与连接查询的比较53（五）子查询与连接查询的比较54代码：SELECTReader.RID,Reader.Rname,Book.Bname,Borrow.LendDateFROMReader,Borrow,BookWHEREReader.RID=Borrow.RIDANDBook.BID=Borrow.BID连接查询三个表同在一个层面上单元2.5SELECT数据查询（ANSI和子查询）55ANSI连接查询一子查询二联合查询三三、联合查询56语法：SELECT_1UNION[ALL]SELECT_2UNION[ALL]SELECT_3]...说明：UNION操作符对查询进行并运算，ALL参数表示运算结果包括重复行。使用UNION运算符合并的所有查询必须在其目标列表中有相同数目的表达式。1.UNION操作符三、联合查询57【例7-49】从图书管理数据库“Library”的图书表“Book”中，查询出“人民邮电出版社”出版的图书和“清华大学出版社”出版的图书的作者名，不包括重复的行。代码：SELECTAuthorFROMBookWHEREPublisher='人民邮电出版社'UNION

--并运算，不包括重复行SELECTAuthorFROMBookWHEREPublis