数据库原理与应用：第6章 表数据的选择操作

1、扬州大学新闻与传媒学院扬州大学新闻与传媒学院第第6章章SQL Server 2005 T-SQL数据查询数据查询数据库原理与应用数据库原理与应用 SQL Server 2005SQL Server 2005SQL Server 2005SQL Server 2005第第6讲讲 SQL Server 2005 T-SQL数据查询数据查询教学目标：教学目标：熟练掌握查询语法格式和使用熟练掌握查询语法格式和使用 掌握投影查询、连接查询、选择查询、分组统计查询、掌握投影查询、连接查询、选择查询、分组统计查询、限定查询、排序查询和保存查询限定查询、排序查询和保存查询掌握常用的连接查询掌握常用的连接查询

2、一般掌握子查询一般掌握子查询 SQL Server 2005SQL Server 2005第第6章章 主要内容主要内容 SELECT查询语句1连接查询2 子查询3 联合查询41 小结5SQL Server 2005SQL Server 20056.1 SELECT查询语句查询语句6.1.1 SELECT6.1.1 SELECT语句的语法格式语句的语法格式SELECT ALL|DISTINCT 列表达式列表达式 INTO 新表名新表名 FROM 表名列表表名列表 WHERE 逻辑表达式逻辑表达式 GROUP BY 列名列名 HAVING逻辑表达式逻辑表达式 ORDER BY 列名列名ASC|DE

3、SC SQL Server 2005SQL Server 20056.1.2 SELECT语句的执行方式语句的执行方式 步骤： 单击工具栏上的“新建查询”按钮，在右边窗口输入查询语句，单击工具栏或“查询”菜单中的“执行”，可在右下方的窗口看到查询的结果。例子：图书管理系统数据库Library表Reader(RID,Rname,Rsex,RnativeP,Rbirthday,TypeID,Lendnum) PK：RID FK:TypeID表Book (BID,Bname,Author, PubComp, PubDate,Price) PK：BID表Borrow (RID, BID,LendDat

4、e, ReturnDate)PK：RID, BID FK：RID和BID表ReaderType(TypeID, Typename,LimitNnm, LimitDays) SQL Server 2005SQL Server 20056.1.3 SELECT子句投影查询子句投影查询语法：语法：SELECT ALL|DISTINCT TOP integer|TOP integer PERCENT WITH TIES 列名表达式列名表达式1, 列名表达式列名表达式2, 列名表达式列名表达式n 其中：表达式中含列名，常量，运算符，列函数其中：表达式中含列名，常量，运算符，列函数下面通过几个例子将参数应

5、用进行说明：下面通过几个例子将参数应用进行说明：1. 投影部分列2. 投影所有列3. TOP关键字限制返回行数4. 是否去重复元组5. 自定义列名6. 字段函数（列函数）7. INTO保存查询SQL Server 2005SQL Server 20056.1.3 SELECT子句投影查询子句投影查询投影部分列投影部分列 Exp1:从读者表从读者表Reader中中查询出读者的编号、姓名和性别中中查询出读者的编号、姓名和性别三列的记录。三列的记录。 USE USE LibLib GO GO SELECT SELECT RID,Rname,RsexRID,Rname,Rsex FROM Reader

6、 FROM ReaderSQL Server 2005SQL Server 20056.1.3 SELECT子句投影查询子句投影查询投影所有列投影所有列 ：( (通配符*：所有字段 ) Exp2：从图书管理数据库：从图书管理数据库Library的读者类型表的读者类型表ReaderType中查询所有纪录中查询所有纪录 . USE Lib USE Lib SELECT SELECT * * FROM FROM ReaderTypeReaderTypeSQL Server 2005SQL Server 20056.1.3 SELECT子句投影查询子句投影查询TOP关键字限制返回行数关键字限制返回行数

7、 Exp3:图书管理数据库图书管理数据库Library的图书表的图书表Book中查询出前中查询出前5条纪条纪录录。 USE Lib USE Lib GO GO SELECT TOP 5 SELECT TOP 5 BID,Bname,AuthorBID,Bname,Author FROM Book FROM Book GO GO SQL Server 2005SQL Server 20056.1.3 SELECT子句投影查询子句投影查询是否去重复元组是否去重复元组 ： All：检出全部信息（默认） Distinct：去掉重复信息 Exp4:从读者表从读者表Reader中查询出籍贯中查询出籍贯Rn

8、ativeP的名称。的名称。 USE USE LibLib GO GO SELECT DISTINCT SELECT DISTINCT RnativePRnativeP FROM Reader FROM ReaderSQL Server 2005SQL Server 20056.1.3 SELECT子句投影查询子句投影查询自定义列名自定义列名 ： 指定的列标题 =列名 或者 列名 AS 指定的列标题 Exp5：用中文显示：用中文显示ReaderType列名列名 SELECT SELECT TypeIDTypeID AS AS 类型编号类型编号, , TypenameTypename AS AS

9、 类型名称类型名称, , LimitNumLimitNum AS AS 限借数量限借数量, , LimitDaysLimitDays AS AS 限借天数限借天数 FROM FROM ReaderTypeReaderTypeSQL Server 2005SQL Server 20056.1.3 SELECT子句投影查询子句投影查询字段函数（列函数）：字段函数（列函数）： 求和：SUM 平均：AVG 最大：MAX 最小：MIN 统计：COUNT Exp6:从图书管理数据库从图书管理数据库Library中图书表中图书表Book中查询出中查询出 图书最高价和最低价图书最高价和最低价 SELECT M

10、AX(Price) AS SELECT MAX(Price) AS 最高价最高价, , MIN(Price) AS MIN(Price) AS 最低价最低价FROM BookFROM BookSQL Server 2005SQL Server 20056.1.3 SELECT子句投影查询子句投影查询INTO保存查询保存查询 ： into 临时表名（根据查询建立临时基本表临时表名（根据查询建立临时基本表 ） Exp7：从借阅表中将读者的借书证号、所借书号、借出日期：从借阅表中将读者的借书证号、所借书号、借出日期 的内容另存在临时表的内容另存在临时表Reader_Borrrow中中 SELECT

11、SELECT RID,BID,LendDateRID,BID,LendDate INTO INTO Reader_BorrowReader_Borrow FROM Borrow FROM BorrowSQL Server 2005SQL Server 20056.1.4 WHERE子句选择查询子句选择查询格式：格式：WHERE 逻辑表达式逻辑表达式 功能：实现有条件的查询运算功能：实现有条件的查询运算下面通过几个例子将不同运算符应用进行说明：下面通过几个例子将不同运算符应用进行说明：1. 比较运算符 2. 逻辑运算符3. 范围运算符4. 模式匹配运算符5. 列表运算符6. 空值判断符SQL S

12、erver 2005SQL Server 20056.1.4 WHERE子句选择查询子句选择查询比较运算符 ( ，,=,22 and )+122 and RSexRSex = =女女 SQL Server 2005SQL Server 20056.1.4 WHERE子句选择查询子句选择查询范围运算符 格式：列名 not between 开始值 and 结束值 说明：列名是否在开始值 and 结束值之间。 等效：列名=开始值 and 列名=结束值 列名结束值 （选not） 例3：从图书表中查询出定价在10元到15元之间的图书信息 SELECT BID AS SELECT BID AS 图书编号图

13、书编号, , BnameBname AS AS 书名书名, , Price AS Price AS 定价定价FROM BookFROM BookWHERE Price between 10 and 15WHERE Price between 10 and 15SQL Server 2005SQL Server 20056.1.4 WHERE子句选择查询子句选择查询模式匹配运算符模式匹配运算符 语法：语法：NOT LIKE 通配符通配符 说明：通配符说明：通配符_ _：一个任意字符；通配符：任意个任意字符：一个任意字符；通配符：任意个任意字符 例4：查询出姓“王”的所有读者的信息 SELECT

14、SELECT * * FROM Reader FROM Reader WHERE WHERE RnameRname LIKE LIKE 王王% ms-help:/MS.SQLCC.v9/MS.SQLSVR.v9.zh-CHS/tsqlref9/html/581fb289-29f9-412b-869c-18d33a9e93d5.htmSQL Server 2005SQL Server 20056.1.4 WHERE子句选择查询子句选择查询列表运算符列表运算符 语法：表达式语法：表达式NOTIN ( (列表列表| |子查询子查询) ) 说明：表达式的值在（不在）列表所列出的值中说明：表达式的值在（

15、不在）列表所列出的值中 子查询在子查询在6.36.3中介绍中介绍 例5：查询借书证号为001和002的学生的信息 SELECT SELECT * * FROM ReaderFROM ReaderWHERE RID in(001,002)WHERE RID in(001,002) SQL Server 2005SQL Server 20056.1.4 WHERE子句选择查询子句选择查询空值判断符空值判断符 语法：语法：IS NOT NULL 例6：从借阅表中查询出没有还的图书编号.SELECT BIDSELECT BIDFROM BorrowFROM BorrowWHERE WHERE Retu

16、rnDateReturnDate IS NULL IS NULLSQL Server 2005SQL Server 20056.1.5 GROUP BY子句分组统计查询子句分组统计查询格式：格式：GROUP BY 列名列名 功能：与列名或列函数配合实现分组统计功能：与列名或列函数配合实现分组统计说明：投影列名必须出现相应的说明：投影列名必须出现相应的GROUP BY列名列名 例7：从图书表中查询各出版社图书的总价。 SELECT SELECT PubCompPubComp, , 总价总价=SUM(price)=SUM(price)FROM BookFROM BookGROUP BY GROUP

17、 BY PubCompPubCompSQL Server 2005SQL Server 20056.1.6 HAVING子句限定查询子句限定查询格式：格式：HAVING 逻辑表达式逻辑表达式功能：功能：与与GROUP BY选项配合筛选（选择）统计结果选项配合筛选（选择）统计结果 说明：常用列函数作为条件，列函数不能放在说明：常用列函数作为条件，列函数不能放在WHERE中中 例8：从借阅表中查询借书超过10本的读者的编号 SELECT RID, SELECT RID, 借书本数借书本数=Count(BID)=Count(BID) FROM Borrow FROM Borrow GROUP BY

18、RID GROUP BY RID HAVING Count(BID) 10 HAVING Count(BID) 10 SQL Server 2005SQL Server 20056.1.7 ORDER BY排序查询排序查询格式：格式：ORDER BY 列名表达式表列名表达式表 asc/ desc功能：排序功能：排序 例子：查询每个读者借书的本数并按借书的多少进行排序 SELECT RID, SELECT RID, COUNT(BID) AS COUNT(BID) AS 借书本数借书本数FROM BorrowFROM BorrowGROUP BY RIDGROUP BY RIDORDER BY

19、COUNT(BID)ORDER BY COUNT(BID)SQL Server 2005SQL Server 20056.2 6.2 连接查询连接查询连接方法和种类1内连接2341 外连接5 自连接6SQL Server 2005SQL Server 20056.2.1 连接方法和种类连接方法和种类SQL Server提供了不同的语法格式支持不同的连接方式提供了不同的语法格式支持不同的连接方式 用于用于FROM子句的连接语法形式子句的连接语法形式 SELECT 列名列表 FROM 表名1连接类型 JOIN 表名2 ON 连接条件 WHERE 逻辑表达式 用于用于WHERE子句的子句的SQL S

20、erver连接语法形式连接语法形式 SELECT 列名列表 FROM 表名列表 WHERE 表名.列名 JOIN_OPERATOR 表名.列名n ON 逻辑表达式 连接种类连接种类 内连接 ， 外连接， 交叉连接 SQL Server 2005SQL Server 20056.2.2 内连接连接格式：格式：from 表名表名1 inner join 表名表名2 on 连接表达式连接表达式（1）等值连接）等值连接 在连接条件中使用等于号在连接条件中使用等于号(=)(=)运算符比较被连接列的列值，运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复其查询结果中列出被连接表中

21、的所有列，包括其中的重复列。列。例1：查询每个读者的详细信息包括读者信息以及借阅图书信息，允许有重复列。 SELECT Reader. SELECT Reader.* *, , Borrow. Borrow.* * FROM Reader FROM Reader INNER JOININNER JOIN Borrow Borrow ON Reader.RID=Borrow.RID ON Reader.RID=Borrow.RID SQL Server 2005SQL Server 20056.2.2 内连接内连接（2 2）自然连接）自然连接 例2：查询每个读者的详细信息包括读者信息以及借阅图书

22、信息，不允许有重复列。 SELECT SELECT Reader.RID,Reader.RID,Reader.RnameReader.Rname, ,Book.BID,Book.BID,Book.BnameBook.Bname, ,LendDateLendDate, ,ReturnDateReturnDate FROM Borrow FROM Borrow INNER JOIN Reader ON Reader.RID=Borrow.RID INNER JOIN Reader ON Reader.RID=Borrow.RID INNER JOIN Book ON Borrow.BID=Book

23、.BID INNER JOIN Book ON Borrow.BID=Book.BIDSQL Server 2005SQL Server 20056.2.3 外连接外连接（1 1）左外连接）左外连接格式：格式：from 表名表名1 left outer join 表名表名2 on 连接表达式连接表达式功能：加入表功能：加入表1没形成连接的元组，表没形成连接的元组，表2列为列为NULL。SELECT Reader.SELECT Reader.* *,RID,BID,RID,BIDFROM Reader FROM Reader LEFT OUTER JOIN Borrow ON Reader.RI

24、D=Borrow.RID LEFT OUTER JOIN Borrow ON Reader.RID=Borrow.RIDSQL Server 2005SQL Server 20056.2.3 外连接外连接（2 2）右外连接）右外连接格式：格式：from 表名表名1 right outer join 表名表名2 on 连接表达式连接表达式功能：加入表功能：加入表2没形成连接的元组，表没形成连接的元组，表1列为列为NULL。 SELECT Reader. SELECT Reader.* *, , RID, RID, BID BID FROM Reader FROM Reader RIGHT OUT

25、ER JOIN Borrow ON Reader.RID=Borrow.RIDRIGHT OUTER JOIN Borrow ON Reader.RID=Borrow.RIDSQL Server 2005SQL Server 20056.2.3 外连接外连接（3 3）全外连接）全外连接格式：格式：from 表名表名1 full outer join 表名表名2 on 连接表达式连接表达式功能：加入表功能：加入表1没形成连接的元组，表没形成连接的元组，表2列为列为NULL， 加入表加入表2没形成连接的元组，表没形成连接的元组，表1列为列为NULL。SELECT Reader.SELECT Rea

26、der.* *, , Borrow.RID, Borrow.RID, BID BIDFROM Borrow FROM Borrow FULL OUTER JOIN Reader ON Reader.RID=Borrow.RIDFULL OUTER JOIN Reader ON Reader.RID=Borrow.RIDSQL Server 2005SQL Server 20056.2.4 自连接自连接格式：格式：from表名表名1 a join 表名表名1 b on 连接表达式连接表达式 SELECT a.BID,SELECT a.BID, a.Bnamea.Bname, , a.Author

27、a.AuthorFROM book a JOIN book b on FROM book a JOIN book b on a.Bnamea.Bname= =b.Bnameb.BnameWHERE a.BIDb.BIDWHERE a.BIDb.BIDSQL Server 2005SQL Server 20056.2.5 交叉连接交叉连接格式：格式：from 表名表名1 cross join 表名表名2 on 连接表达式连接表达式 说明：两个表做笛卡尔积说明：两个表做笛卡尔积 SELECT Reader. SELECT Reader.* *, , Borrow.RID,BIDBorrow.RID

28、,BID FROM Reader CROSS JOIN Borrow FROM Reader CROSS JOIN BorrowSQL Server 2005SQL Server 20056.36.3 子查询子查询NOTIN子查询1比较子查询2NOTEXISTS子查询3在其他语句中使用4SQL Server 2005SQL Server 20056.3.1 NOT IN 子查询子查询 列名列名 not in (常量表常量表)|(子查询子查询) 说明：列值被包含或不（说明：列值被包含或不（not）被包含在集合中）被包含在集合中 等价：列名等价：列名=any(子查询子查询)例子：查询没有借过书的读

29、者的信息 SELECT SELECT * * FROM Reader FROM Reader WHERE RID WHERE RID NOT INNOT IN (SELECT DISTINCT RID FROM Borrow)(SELECT DISTINCT RID FROM Borrow)SQL Server 2005SQL Server 20056.3.2 比较子查询比较子查询 （1 1）列名列名 比较符比较符 all （子查询）（子查询） 说明：子查询中的说明：子查询中的每个每个值都满足比较条件值都满足比较条件例子：查询读者编号RID最大的读者的借书情况 SELECT SELECT *

30、* FROM Borrow FROM Borrow WHERE WHERE RID=ALLRID=ALL (SELECT RID FROM Reader)(SELECT RID FROM Reader)SQL Server 2005SQL Server 20056.3.2 比较子查询比较子查询 （2 2）列名比较符列名比较符 any|some （子查询）（子查询） 说明：子查询中的任一个值满足比较条件说明：子查询中的任一个值满足比较条件 例子：查询选修C+语言课程的学生 SELECT 姓名姓名, 成绩成绩 FROM 学生学生 AS x INNER JOIN 选修选修 AS y ON x.学号学

31、号=y.学号学号 WHERE 课程号课程号=any (select 课程号课程号 from 课程课程 where 课程名课程名=C+语言语言); SQL Server 2005SQL Server 20056.3.3 NOT EXISTS子查询子查询 功能：用集合运算实现元组与（子查询）功能：用集合运算实现元组与（子查询）说明：子查询中空或非空说明：子查询中空或非空 例子：查询借阅了清华大学的图书的读者的编号 SELECT DISTINCT RID SELECT DISTINCT RID FROM Borrow FROM Borrow WHERE WHERE EXISTSEXISTS (SEL

32、ECT (SELECT * * FROM books FROM books WHERE Borrow. BID= books. BID AND WHERE Borrow. BID= books. BID AND PubCompPubComp=清华大学清华大学) )SQL Server 2005SQL Server 20056.3.4在其他语句中使用子查询在其他语句中使用子查询 例子：计算读者中的已借数量的值 UPDATE ReaderUPDATE Reader SET SET LendnumLendnum =( =( SELECT COUNT( SELECT COUNT(* *) ) FROM

33、 FROM dbo.Borrowdbo.Borrow WHERE WHERE ReturnDateReturnDate IS NULL and Reader. IS NULL and Reader. RID =Borrow. RID)RID =Borrow. RID)SQL Server 2005SQL Server 20056.4 6.4 联合查询联合查询UNION操作符1联合查询结果排序2对单张表使用UNION操作符3交操作和叉操作41UNION操作符和JOIN操作符区别与联系5连接查询和子查询的比较6SQL Server 2005SQL Server 20056.4.1 UNION操作符

34、操作符格式：SELECT_1 UNION ALL SELECT_2 UNION ALL SELECT_n 例1：查询出“清华大学”出版的图书的编号和被借阅过的图书的编号，不包括重复的列 SELECT BID FROM books SELECT BID FROM books WHERE WHERE PubCompPubComp=清华大学清华大学 UNIONUNION SELECT BID FROM Borrow SELECT BID FROM BorrowSQL Server 2005SQL Server 20056.4.1 UNION操作符操作符例2：查询出“清华大学”出版的图书的编号和被借阅

35、过的图书的编号，可以包括重复的列 SELECT BID FROM books SELECT BID FROM books WHERE WHERE PubCompPubComp=清华大学清华大学 UNION ALLUNION ALL SELECT BID FROM Borrow SELECT BID FROM BorrowSQL Server 2005SQL Server 20056.4.2 联合查询结果排序联合查询结果排序 例3：查询出“清华大学”出版的图书和被借阅过的图书的编号，不包括重复的列并将查询结果进行排序 SELECT BID SELECT BID FROM books FROM b

36、ooks WHERE WHERE PubCompPubComp=人民出版社人民出版社 UNIONUNION SELECT BID SELECT BID FROM Borrow FROM Borrow ORDER BYORDER BYSQL Server 2005SQL Server 20056.4.3 对单张表使用对单张表使用UNION操作符操作符 例4：查询“清华大学”和“微软”出版的图书的书名和作者 SELECT SELECT Bname,AuthorBname,Author FROM books FROM books WHERE WHERE PubCompPubComp=清华大学清华大学

37、 UNIONUNION SELECT SELECT Bname,AuthorBname,Author FROM books FROM books WHERE WHERE PubCompPubComp=微软微软 SQL Server 2005SQL Server 20056.4.4 交操作和差操作交操作和差操作例1：查询被借阅过的“清华大学”出版的图书的图书编号 SELECT BID SELECT BID FROM books FROM books WHERE WHERE PubCompPubComp=清华大学清华大学 AND EXISTSAND EXISTS (SELECT (SELECT * * FROM Borrow FROM Borrow WHERE Borrow. BID= books. BID) WHERE Borrow. BID= books. BID) SQL Server 2005SQL Server 20056.4.4 交操作和差操作交操作和差操作例2：查询没被借阅过的“清华大学”出版的图书的图书编号 SELECT BID SELECT BID FROM