SQL-Server-2012数据库技术及应用2.5

1、教学单元2.5第7章 SELECT数据查询（ANSI和子查询）SQL Server 2012数据库技术及应用案例2-7-1 图书管理ANSI连接查询案例2-7-2 图书管理SELECT子查询SELECT数据查询 学习导航2SELECT数据查询 知识框架3单元2.5 SELECT数据查询（ANSI和子查询）能力目标能够根据数据库应用系统的功能需求对表进行ANSI连接查询能够根据数据库应用系统的功能需求对表进行子查询能够根据数据库应用系统的功能需求对表进行联合查询能够阅读并熟练书写SELECT查询语句和有关参数（英文）4知识目标ANSI连接查询的语法子查询的基本应用联合查询的基本应用素质目标培养与

2、用户的沟通能力，以便很好地满足用户的应用需求培养应用所学知识解决实际问题的能力5单元2.5 SELECT数据查询（ANSI和子查询）案例2 图书管理系统案例2-7-1 图书管理ANSI连接查询案例2-7-2 图书管理SELECT子查询工作任务6单元2.5 SELECT数据查询（ANSI和子查询）7ANSI连接查询一子查询二联合查询三单元2.5 SELECT数据查询（ANSI和子查询）一、ANSI连接查询案例2-7-1 图书管理ANSI连接查询根据图书管理系统的功能需求，应用T-SQL的SELECT查询语句对数据库“Library”中所创建的表进行ANSI连接查询。工作任务8回顾：SELECT查

3、询语句基本结构SELECT ALL|DISTINCT TOP n 表达式列表INTO新表名FROM 基表|视图,.n -回顾表的连接，视图见后续WHERE 查询条件GROUP BY 分组列名表HAVING逻辑表达式ORDER BY 排序列名表ASC|DESCSELECTFROM9一、ANSI连接查询说明：在SQL Server中，可以使用两种语法形式FROM子句（已介绍），连接条件写在WHERE子句的逻辑表达式中，从而实现表的连接（早期）。ANSI（American National Standards Institute美国国家标准学会）连接语法形式，在FROM子句中使用JOINON关键字，

4、连接条件写在ON之后，从而实现表的连接。SQL Server 2012推荐使用ANSI形式的连接。SELECTFROMJOINON10一、ANSI连接查询语法：FROM 表名1 连接类型 JOIN 表名2 ON 连接条件说明：实现表与表的两两连接，表1和表2连接之后还可以继续与表3,表n连接，最多可以连接256个表。连接条件放在ON关键字后。特别注意的是此语句也可以连接视图，下一章介绍。连接类型:INNER JOIN：内连接。LEFT OUTER JOIN：左外连接。RIGHT OUTER JOIN：右外连接。CROSS JOIN：交叉连接。1.SELECTFROMJOINON11一、ANSI

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

6、连接13一、ANSI连接查询代码：SELECT Reader.*,ReaderType.*FROM Reader INNER JOIN ReaderType ON Reader.TypeID=ReaderType.TypeID -内连接查询结果：（1）等值连接14一、ANSI连接查询案例：查询每个读者的详细信息（读者及借阅图书的信息），不允许有重复列。代码：SELECT Reader.*,Borrow.LendDate,Borrow.ReturnDate, Book.BID,Book.Bname,Book.Author,Book.Price -投影消除重复列FROM Reader INNER

7、JOIN Borrow ON Reader.RID=Borrow.RID -表1内连接表2INNER JOIN Book ON Borrow.BID=Book.BID -再内连接表3（2）自然连接15一、ANSI连接查询查询结果：（2）自然连接16一、ANSI连接查询外连接返回FROM子句中指定的至少一个表或视图中的所有行，只要这些行符合任何WHERE选择（不包含ON之后的连接条件）或HAVING限定条件。外连接又分为左外连接、右外连接和全外连接。左外连接对连接中左边的表不加限制；右外连接对连接中右边的表不加限制；全外连接对两个表都不加限制，两个表中的所有行都会包括在结果集中。3.外连接17一

8、、ANSI连接查询语法：FROM 表名1 LEFT OUTER JOIN 表名2 ON 连接表达式说明：连接结果保留表1没形成连接的行，表2相应的各列为NULL值。（1）左外连接18一、ANSI连接查询案例：从表“Reader”和表“Borrow”中查询出读者的借阅情况，包括没有借过书的读者情况。代码：SELECT Reader.*,Borrow.RID,BIDFROM Reader LEFT OUTER JOIN Borrow -左外连接ON Reader.RID=Borrow.RID查询结果：（1）左外连接19一、ANSI连接查询说明：连接结果保留了表“Reader”中不满足等值条件的第3

9、行和第4行，表“Borrow”中相应的各列为NULL值，说明读者张英和李亚茜没有借过书。（1）左外连接20一、ANSI连接查询语法：FROM 表名1 RIGHT OUTER JOIN 表名2 ON 连接表达式说明：连接结果保留表2没形成连接的行，表1相应的列为NULL值。案例：从表“Borrow”和表“Book”中查询出图书被借阅的情况，包括没有被借的图书情况。（2）右外连接21一、ANSI连接查询案例：从表“Borrow”和表“Book”中查询出图书被借阅的情况，包括没有被借的图书情况。代码：SELECT Borrow.RID,Borrow.BID,Book.BID,Bname,Author

10、FROM Borrow RIGHT OUTER JOIN Book -右外连接ON Borrow.BID=Book.BID（2）右外连接22一、ANSI连接查询查询结果：说明：连接结果加入了表“Book”中不满足等值条件的第1、4、8、10行，表“Borrow”中相应的列为NULL，说明图书ERP从内部集成开始、ERP系统的集成应用、SQL Server 2008数据库设计与实现和数据库系统概论四本书没有被借出过，SAP基础教程被借出过两次。（2）右外连接23I一、ANSI连接查询说明：连接结果加入了表“Book”中不满足等值条件的第1、4、8、10行，表“Borrow”中相应的列为NULL，

11、说明图书ERP从内部集成开始、ERP系统的集成应用、SQL Server 2008数据库设计与实现和数据库系统概论四本书没有被借出过，SAP基础教程被借出过两次。（2）右外连接24一、ANSI连接查询语法：FROM 表名1 FULL OUTER JOIN 表名2 ON 连接表达式说明：连接结果保留表1没形成连接的元组，表2相应的列为NULL值；连接结果也保留表2没形成连接的元组，表1相应的列为NULL值。案例：借阅和读者全外连接。（3）全外连接25一、ANSI连接查询案例：借阅和读者全外连接。代码：SELECT Reader.*, Borrow.RID, Borrow.BIDFROM Borr

12、ow FULL OUTER JOIN Reader -全外连接ON Borrow.RID=Reader.RID查询结果：查询结果与左外连接相同，因为借阅表中读者的编号均在读者表中存在，都能形成连接。（3）全外连接26一、ANSI连接查询语法：FROM 表名1 别名1 JOIN 表名1 别名2 ON 连接表达式说明：表可以通过自连接实现自身的连接运算。自连接可以看作是一张表的两个副本之间进行的连接，在自连接中，必须为表指定两个不同的别名，使之在逻辑上成为两张表。4.自连接27一、ANSI连接查询案例： 从图书管理数据库“Library”中查询出借了两本以上图书的读者的借书信息。步骤1：表“Bor

13、row”与表“Borrow”自连接代码：SELECT x.RID,x.BID,x.ReturnDate,y.RID,y.BIDFROM Borrow x JOIN Borrow y ON x.RID=y.RID -读者编号等值条件自连接WHERE x.ReturnDate IS NULL AND y.ReturnDate IS NULL -还期为空，即尚未归还4.自连接28一、ANSI连接查询查询结果：4.自连接29一、ANSI连接查询说明：仔细观察，可以发现连接查询结果的第5行和第6行数据中读者编号“RID”相同，但书号“BID”不同，说明该读者至少借阅了两本书。在以上查询的WHERE子句中

14、加上“x.BIDy.BID”查询条件，就可以查询出借阅了两本以上的读者编号了。这里“x.ReturnDate IS NULL”描述了读者借阅图书尚未归还的条件。4.自连接30一、ANSI连接查询步骤2：对表“Borrow”与表“Borrow”自连接加选择条件和投影操作。代码：SELECT x.RID,x.BID,x.ReturnDate -投影去重复列名FROM Borrow x JOIN Borrow y ON x.RID=y.RID -读者编号等值条件自连接 WHERE x.ReturnDate IS NULL AND y.ReturnDate IS NULL -还期为空，即尚未归还AND

15、 x.BIDy.BID -图书编号不同，即两本不同的书4.自连接31一、ANSI连接查询查询结果：说明：仔细观察，可以发现连接查询结果的第5行和第6行数据中读者编号“RID”相同，但书号“BID”不同，说明该读者至少借阅了两本书。在以上查询的WHERE子句中加上“x.BIDy.BID”查询条件，就可以查询出借阅了两本以上的读者编号了。这里“x.ReturnDate IS NULL”描述了读者借阅图书尚未归还的条件。4.自连接32一、ANSI连接查询语法：FROM 表名1 CROSS JOIN 表名2说明：两个表进行笛卡儿积计算，等价于FROM表名1，表名2之后不加WHERE连接条件逻辑表达式。

16、一般没有意义5.交叉连接33一、ANSI连接查询语法：FROM 表名1 JOIN 表名2 ON 连接表达式 JOIN 表名3 ON 连接表达式.说明：最多可以连接256个表，通常为810个。6.多表连接34一、ANSI连接查询案例：对读者表“Reader”、借阅表“Borrow”和图书表“Book”三个表进行等值连接。代码：SELECT t1.RID,Rname,Bname,LendDateFROM Reader t1 JOIN Borrow t2 ON t1.RID=t2.RIDJOIN Book t3 ON t2.BID=t3.BID6.多表连接35一、ANSI连接查询查询结果：6.多表连

17、接36单元2.5 SELECT数据查询（ANSI和子查询）37ANSI连接查询一子查询二联合查询三二、子查询案例2-7-2 图书管理SELECT子查询根据图书管理系统的功能需求，应用T-SQL的SELECT子查询对数据库“Library”中所创建的表进行数据查询和数据更新操作。工作任务38二、子查询功能说明：子查询指在一个SELECT查询语句的WHERE子句中包含另一个SELCET查询语句，或者将一个SELECT查询语句嵌入在另一个语句中成为其一部分。在查询语句中，在外层SELECT查询语句称为主查询，WHERE子句中的SELECT查询语句被称为子查询。WHERE子句中的子查询可描述复杂的查询

18、条件，也称为嵌套查询。嵌套查询一般会涉及到两个以上的表，所做的查询有的也可以采用连接查询或者用几条查询语句完成。采用子查询有时会提高算法的时间和空间效率，但算法不易读懂，读者应权衡利弊进行选择。SELECTFROMSELECT39（一）IN 子查询语法：表达式 NOT IN (列表|子查询)说明：如果表达式的值（NOT，不）与子查询返回的任何值相等，逻辑表达式的值为真。子查询的SELECT投影列表中只能指定一个表达式。此表达式的行数据构成了括号内集合的所有元素，与集合的概念相同，集合内的元素是消除了重复值的。NOT IN 子查询40（一）IN 子查询案例：从图书管理数据库“Library”中查

19、询出借阅过“人民邮电出版社”出版图书的读者编号（不包括重复的行）。代码：USE LibrarySELECT DISTINCT RID-投影满足条件的读者编号，去重复行FROM Borrow -对于表“Borrow”中的每一行数据-判断其所借图书的编号是否在对表“Book”子查询的集合中WHERE BID IN (SELECT BID FROM Book WHERE Publisher=人民邮电出版社) -人民邮电出版社图书的编号集合NOT IN 子查询41（一）IN 子查询代码：USE LibrarySELECT DISTINCT RID-投影满足条件的读者编号，去重复行FROM Borrow

20、 -对于表“Borrow”中的每一行数据-判断其所借图书的编号是否在对表“Book”子查询的集合中WHERE BID IN (SELECT BID FROM Book WHERE Publisher=人民邮电出版社) -人民邮电出版社图书的编号集合查询结果：NOT IN 子查询42（一）IN 子查询案例： 从图书管理数据库“Library”中查询出没有借过书的读者信息。代码：SELECT *FROM Reader -对于表中的每一位读者-判断其读者编号是否不在子查询的集合中WHERE RID NOT IN (SELECT RID FROM Borrow) -借了书的读者编号集合查询结果：NOT

21、 IN 子查询43语法：表达式 比较运算符 SOME | ANY (子查询)说明：若表达式的值在比较关系上满足子查询返回的任何一个值，则逻辑表达式的值为真。子查询的SELECT投影列表中只能指定一个表达式。SOME和ANY的用法相同。“= SOME | ANY ”等价于“IN”，“ SOME | ANY ”没有意义。（1）SOME|ANY子查询44= SOME | ANY (子查询)等价 IN (子查询)（二） SOME|ANY和ALL子查询案例：从图书数据库“Library”中查询当前借出的清华大学出版社的图书借期等信息。代码：SELECT Borrow.RID,Borrow.BID,Bor

22、row.LendDate, Book.Bname,Book.Publisher,Book.LentOutFROM Borrow,Book -对于借阅表中的每借出的一本书WHERE Borrow.BID=SOME -图书编号是否在子查询集合中(SELECT BID -已经借出清华大学出版社的图书编号集合 FROM Book -已经借出的清华大学出版社出版的图书WHERE LentOut=True AND Publisher=清华大学出版社)AND Borrow.BID=Book.BID -等值连接条件（1）SOME|ANY子查询45（二） SOME|ANY和ALL子查询案例：从图书数据库“Lib

23、rary”中查询当前借出的清华大学出版社的图书借期等信息。查询结果：（1）SOME|ANY子查询46（二） SOME|ANY和ALL子查询语法：表达式 比较运算符 ALL(子查询)说明：如果表达式的值在比较关系上满足子查询返回的每一个值，则逻辑表达式的值为真。子查询的SELECT投影列表中只能指定一个表达式。“ALL”等价于“NOT IN”，“=ALL”没有意义。（2）ALL子查询47 ALL (子查询)等价NOT IN (子查询)（二） SOME|ANY和ALL子查询（二） SOME|ANY和ALL子查询案例：从借阅表“Borrow”中查询出读者编号RID最大的读者的借书情况。代码：SELE

24、CT * FROM BorrowWHERE RID=ALL(SELECT RID FROM Reader) -子查询查询结果：（2）ALL子查询48RID=ALL(SELECT RID FROM Reader)等价RID=(SELECT MAX(RID) FROM Reader)语法：NOT EXISTS(子查询)说明：当子查询的结果存在（不为空集）时，逻辑表达式的值为真，不存在（空集）逻辑表达式的值为假。NOT EXISTS则与EXISTS相反。在EXISTS引入子查询时，在子查询的SELECT投影列表中可以指定多个表达式。EXISTS(子查询)49（三）EXISTS子查询（三）EXISTS

25、子查询案例：从图书管理数据库“Library”中，用EXISTS子查询查询出借阅了“人民邮电出版社”出版图书的读者编号。代码：SELECT DISTINCT RIDFROM Borrow -对于每一条借阅信息的图书编号BIDWHERE EXISTS -判定子查询是否有满足子查询条件的返回值-主查询中的Borrow.BID等于子查询中的Book.BID (SELECT * FROM Book WHERE Borrow.BID=Book.BID AND Publisher=人民邮电出版社) -人民邮电出版社出版的图书EXISTS(子查询)50（三）EXISTS子查询案例：从图书管理数据库“Libr

26、ary”中，用EXISTS子查询查询出借阅了“人民邮电出版社”出版图书的读者编号。查询结果：注意：代码中WHERE子句后的“Borrow. BID=Book. BID”并不是等值连接条件，而是子查询中的选择条件，判断主查询的“Borrow. BID”与子查询的“Book. BID”是否相等。EXISTS(子查询)等价语句：SELECT DISTINCT Borrow.RIDFROM Book,BorrowWHERE Book.BID=Borrow.BID AND Publisher=人民邮电出版社51UPDATE更新数据语句： 案例：计算读者表“Reader”中的已借书数量列“Lendnum”

27、代码：UPDATE ReaderSET Lendnum=-从借阅表“Borrow”中统计出每个读者借书的册数 (SELECT COUNT(*) FROM Borrow WHERE ReturnDate IS NULL AND Reader.RID=Borrow.RID)在UPDATE语句中的使用52（四）子查询在其他语句中的使用案例： 计算借阅表“Borrow”中的应还日期列“SReturnDate”值。代码：UPDATE Borrow -更新借阅表数据SET SReturnDate= -对借阅表的每一行的应还日期列赋值-函数的第二个参数“限借天数”加上第三个参数“借期”得到“应还日期” DA

28、TEADD(dd,(SELECT ReaderType.LimitDays -子查询得到限借天数FROM Reader INNER JOIN ReaderType -等值连接ON Reader.TypeID=ReaderType.TypeID -等值条件描述WHERE Borrow.RID=Reader.RID), -借阅表对应的读者编号条件描述Borrow.LendDate) -函数的第三个参数为借期在UPDATE语句中的使用53（四）子查询在其他语句中的使用（四）子查询在其他语句中的使用案例： 计算借阅表“Borrow”中的应还日期列“SreturnDate”值。查询结果：在UPDATE语

29、句中的使用54主要区别：子查询中的表和主查询中的表分为内外嵌套的查询，而连接查询是同在一个层面的查询。55（五）子查询与连接查询的比较案例(子查询)：查询出图书表“Book”中价格最低的图书的编号和书名，采用子查询完成此任务。代码：SELECT BID AS 图书编号, Bname AS 书名FROM BookWHERE Price=(SELECT MIN(Price) FROM Book)查询结果：图书编号 书名- -TP311.138/78 数据库系统概论56子查询子查询(内)：SELECT MIN(Price) FROM Book为25.00主查询(外)：Price=SOME (SELE

30、CT MIN(Price) FROM Book)等价Price= (SELECT MIN(Price) FROM Book)等价Price=25.00（五）子查询与连接查询的比较案例(连接查询)：查询读者编号、读者姓名、所借图书名和借阅时间。代码：SELECT Reader.RID,Reader.Rname,Book.Bname,Borrow.LendDateFROM Reader,Borrow,BookWHERE Reader.RID=Borrow.RID AND Book.BID=Borrow.BID连接查询57（五）子查询与连接查询的比较（五）子查询与连接查询的比较代码：SELECT R

31、eader.RID,Reader.Rname,Book.Bname,Borrow.LendDateFROM Reader,Borrow,BookWHERE Reader.RID=Borrow.RID AND Book.BID=Borrow.BID连接查询58三个表同在一个层面上单元2.5 SELECT数据查询（ANSI和子查询）59ANSI连接查询一子查询二联合查询三三、联合查询语法：SELECT_1 UNION ALL SELECT_2UNION ALLSELECT_3.说明：UNION操作符对查询进行并运算，ALL参数表示运算结果包括重复行。使用UNION运算符合并的所有查询必须在其目标列表中有相同数目的表达式。1.UNION操作符60三、联合查询案例（不包括重复行） ：从图书管理数据库“Library”的图书表“Book”中，查询出“人民邮电出版社”出版的图书和“清华大学出版社”出版的图书的作者名，不包括重复的行。代码：SELECT Author FROM Book WHERE Publisher=人民邮电出版