《数据库原理与应用》06.表数据的选择操作.ppt

孙发勤 ,扬州大学新闻与传媒学院,第6章 SQL Server 2005 T-SQL数据查询,数据库原理与应用 SQL Server 2005,SQL Server 2005,第6讲 SQL Server 2005 T-SQL数据查询,教学目标： 熟练掌握查询语法格式和使用 掌握投影查询、连接查询、选择查询、分组统计查询、限定查询、排序查询和保存查询 掌握常用的连接查询 一般掌握子查询,SQL Server 2005,第6章 主要内容,SELECT查询语句,1,连接查询,2,子查询,3,联合查询,4,1,小结,5,SQL Server 2005,6.1 SELECT查询语句,6.1.1 SELECT语句的语法格式 SELECT ALL|DISTINCT 列表达式 INTO 新表名 FROM 表名列表 WHERE 逻辑表达式 GROUP BY 列名 HAVING逻辑表达式 ORDER BY 列名ASC|DESC,SQL Server 2005,6.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,LendDate, ReturnDate) PK：RID, BID FK：RID和BID 表ReaderType(TypeID, Typename,LimitNnm, LimitDays),SQL Server 2005,6.1.3 SELECT子句投影查询,语法：SELECT ALL|DISTINCT TOP integer|TOP integer PERCENT WITH TIES 列名表达式1, 列名表达式2, 列名表达式n 其中：表达式中含列名，常量，运算符，列函数 下面通过几个例子将参数应用进行说明： 投影部分列 投影所有列 TOP关键字限制返回行数 是否去重复元组 自定义列名 字段函数（列函数） INTO保存查询,SQL Server 2005,6.1.3 SELECT子句投影查询,投影部分列 Exp1:从读者表Reader中中查询出读者的编号、姓名和性别三列的记录。,USE Lib GO SELECT RID,Rname,Rsex FROM Reader,SQL Server 2005,6.1.3 SELECT子句投影查询,投影所有列 ：(通配符*：所有字段 ) Exp2：从图书管理数据库Library的读者类型表ReaderType中查询所有纪录 .,USE Lib SELECT * FROM ReaderType,SQL Server 2005,6.1.3 SELECT子句投影查询,TOP关键字限制返回行数 Exp3:图书管理数据库Library的图书表Book中查询出前5条纪录。,USE Lib GO SELECT TOP 5 BID,Bname,Author FROM Book GO,SQL Server 2005,6.1.3 SELECT子句投影查询,是否去重复元组 ： All：检出全部信息（默认） Distinct：去掉重复信息 Exp4:从读者表Reader中查询出籍贯RnativeP的名称。,USE Lib GO SELECT DISTINCT RnativeP FROM Reader,SQL Server 2005,6.1.3 SELECT子句投影查询,自定义列名 ： 指定的列标题 =列名 或者 列名 AS 指定的列标题 Exp5：用中文显示ReaderType列名,SELECT TypeID AS 类型编号, Typename AS 类型名称, LimitNum AS 限借数量, LimitDays AS 限借天数 FROM ReaderType,SQL Server 2005,6.1.3 SELECT子句投影查询,字段函数（列函数）： 求和：SUM 平均：AVG 最大：MAX 最小：MIN 统计：COUNT Exp6:从图书管理数据库Library中图书表Book中查询出 图书最高价和最低价,SELECT MAX(Price) AS 最高价, MIN(Price) AS 最低价 FROM Book,SQL Server 2005,6.1.3 SELECT子句投影查询,INTO保存查询 ： into 临时表名（根据查询建立临时基本表 ） Exp7：从借阅表中将读者的借书证号、所借书号、借出日期 的内容另存在临时表Reader_Borrrow中,SELECT RID,BID,LendDate INTO Reader_Borrow FROM Borrow,SQL Server 2005,6.1.4 WHERE子句选择查询,格式：WHERE 逻辑表达式 功能：实现有条件的查询运算 下面通过几个例子将不同运算符应用进行说明： 比较运算符 逻辑运算符 范围运算符 模式匹配运算符 列表运算符 空值判断符,SQL Server 2005,6.1.4 WHERE子句选择查询,比较运算符 ( ，,=,=) 例1：选择所有男 读者的信息.,SELECT * FROM Reader WHERE RSex=男,逻辑运算符 (and,or) 例2：从学生表中查询出年龄超过22岁的女生的信息,SELECT * FROM Reader WHERE year(getdate()-year(RBirthday)+122 and RSex =女,SQL Server 2005,6.1.4 WHERE子句选择查询,范围运算符 格式：列名 not between 开始值 and 结束值 说明：列名是否在开始值 and 结束值之间。 等效：列名=开始值 and 列名结束值 （选not） 例3：从图书表中查询出定价在10元到15元之间的图书信息,SELECT BID AS 图书编号, Bname AS 书名, Price AS 定价 FROM Book WHERE Price between 10 and 15,SQL Server 2005,6.1.4 WHERE子句选择查询,模式匹配运算符 语法：NOT LIKE 通配符 说明：通配符_：一个任意字符；通配符：任意个任意字符 例4：查询出姓“王”的所有读者的信息,SELECT * FROM Reader WHERE Rname LIKE 王%,ms-help:/MS.SQLCC.v9/MS.SQLSVR.v9.zh-CHS/tsqlref9/html/581fb289-29f9-412b-869c-18d33a9e93d5.htm,SQL Server 2005,6.1.4 WHERE子句选择查询,列表运算符 语法：表达式NOTIN (列表|子查询) 说明：表达式的值在（不在）列表所列出的值中 子查询在6.3中介绍 例5：查询借书证号为001和002的学生的信息,SELECT * FROM Reader WHERE RID in(001,002),SQL Server 2005,6.1.4 WHERE子句选择查询,空值判断符 语法：IS NOT NULL 例6：从借阅表中查询出没有还的图书编号.,SELECT BID FROM Borrow WHERE ReturnDate IS NULL,SQL Server 2005,6.1.5 GROUP BY子句分组统计查询,格式：GROUP BY 列名 功能：与列名或列函数配合实现分组统计 说明：投影列名必须出现相应的GROUP BY列名 例7：从图书表中查询各出版社图书的总价。,SELECT PubComp, 总价=SUM(price) FROM Book GROUP BY PubComp,SQL Server 2005,6.1.6 HAVING子句限定查询,格式：HAVING 逻辑表达式 功能：与GROUP BY选项配合筛选（选择）统计结果 说明：常用列函数作为条件，列函数不能放在WHERE中 例8：从借阅表中查询借书超过10本的读者的编号,SELECT RID, 借书本数=Count(BID) FROM Borrow GROUP BY RID HAVING Count(BID) 10,SQL Server 2005,6.1.7 ORDER BY排序查询,格式：ORDER BY 列名表达式表 asc/ desc 功能：排序 例子：查询每个读者借书的本数并按借书的多少进行排序,SELECT RID, COUNT(BID) AS 借书本数 FROM Borrow GROUP BY RID ORDER BY COUNT(BID),SQL Server 2005,6.2 连接查询,连接方法和种类,1,内连接,2,3,4,1,外连接,5,自连接,6,SQL Server 2005,6.2.1 连接方法和种类,SQL Server提供了不同的语法格式支持不同的连接方式 用于FROM子句的连接语法形式 SELECT 列名列表 FROM 表名1连接类型 JOIN 表名2 ON 连接条件 WHERE 逻辑表达式 用于WHERE子句的SQL Server连接语法形式 SELECT 列名列表 FROM 表名列表 WHERE 表名.列名 JOIN_OPERATOR 表名.列名n ON 逻辑表达式 连接种类 内连接 ， 外连接， 交叉连接,SQL Server 2005,6.2.2 内连接,格式：from 表名1 inner join 表名2 on 连接表达式 （1）等值连接 在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。 例1：查询每个读者的详细信息包括读者信息以及借阅图书信息，允许有重复列。,SELECT Reader.*, Borrow.* FROM Reader INNER JOIN Borrow ON Reader.RID=Borrow.RID,SQL Server 2005,6.2.2 内连接,（2）自然连接 例2：查询每个读者的详细信息包括读者信息以及借阅图书信息，不允许有重复列。,SELECT Reader.RID, Reader.Rname, Book.BID, Book.Bname, LendDate, ReturnDate FROM Borrow INNER JOIN Reader ON Reader.RID=Borrow.RID INNER JOIN Book ON Borrow.BID=Book.BID,SQL Server 2005,6.2.3 外连接,（1）左外连接 格式：from 表名1 left outer join 表名2 on 连接表达式 功能：加入表1没形成连接的元组，表2列为NULL。,SELECT Reader.*,RID,BID FROM Reader LEFT OUTER JOIN Borrow ON Reader.RID=Borrow.RID,SQL Server 2005,6.2.3 外连接,（2）右外连接 格式：from 表名1 right outer join 表名2 on 连接表达式 功能：加入表2没形成连接的元组，表1列为NULL。,SELECT Reader.*, RID, BID FROM Reader RIGHT OUTER JOIN Borrow ON Reader.RID=Borrow.RID,SQL Server 2005,6.2.3 外连接,（3）全外连接 格式：from 表名1 full outer join 表名2 on 连接表达式 功能：加入表1没形成连接的元组，表2列为NULL， 加入表2没形成连接的元组，表1列为NULL。,SELECT Reader.*, Borrow.RID, BID FROM Borrow FULL OUTER JOIN Reader ON Reader.RID=Borrow.RID,SQL Server 2005,6.2.4 自连接,格式：from表名1 a join 表名1 b on 连接表达式,SELECT a.BID, a.Bname, a.Author FROM book a JOIN book b on a.Bname=b.Bname WHERE a.BIDb.BID,SQL Server 2005,6.2.5 交叉连接,格式：from 表名1 cross join 表名2 on 连接表达式 说明：两个表做笛卡尔积,SELECT Reader.*, Borrow.RID,BID FROM Reader CROSS JOIN Borrow,SQL Server 2005,6.3 子查询,NOTIN子查询,1,比较子查询,2,NOTEXISTS子查询,3,在其他语句中使用,4,SQL Server 2005,6.3.1 NOT IN 子查询,列名 not in (常量表)|(子查询) 说明：列值被包含或不（not）被包含在集合中 等价：列名=any(子查询) 例子：查询没有借过书的读者的信息,SELECT * FROM Reader WHERE RID NOT IN (SELECT DISTINCT RID FROM Borrow),SQL Server 2005,6.3.2 比较子查询,（1）列名 比较符 all （子查询） 说明：子查询中的每个值都满足比较条件 例子：查询读者编号RID最大的读者的借书情况,SELECT * FROM Borrow WHERE RID=ALL (SELECT RID FROM Reader),SQL Server 2005,6.3.2 比较子查询,（2）列名比较符 any|some （子查询） 说明：子查询中的任一个值满足比较条件 例子：查询选修C+语言课程的学生,SELECT 姓名, 成绩 FROM 学生 AS x INNER JOIN 选修 AS y ON x.学号=y.学号 WHERE 课程号=any (select 课程号 from 课程 where 课程名=C+语言);,SQL Server 2005,6.3.3 NOT EXISTS子查询,功能：用集合运算实现元组与（子查询） 说明：子查询中空或非空 例子：查询借阅了清华大学的图书的读者的编号,SELECT DISTINCT RID FROM Borrow WHERE EXISTS (SELECT * FROM books WHERE Borrow. BID= books. BID AND PubComp=清华大学),SQL Server 2005,6.3.4在其他语句中使用子查询,例子：计算读者中的已借数量的值,UPDATE Reader SET Lendnum =( SELECT COUNT(*) FROM dbo.Borrow WHERE ReturnDate IS NULL and Reader. RID =Borrow. RID),SQL Server 2005,6.4 联合查询,UNION操作符,1,联合查询结果排序,2,对单张表使用UNION操作符,3,交操作和叉操作,4,1,UNION操作符和JOIN操作符区别与联系,5,连接查询和子查询的比较,6,SQL Server 2005,6.4.1 UNION操作符,格式：SELECT_1 UNION ALL SELECT_2 UNION ALL SELECT_n 例1：查询出“清华大学”出版的图书的编号和被借阅过的图书的编号，不包括重复的列,SELECT BID FROM books WHERE PubComp=清华大学 UNION SELECT BID FROM Borrow,SQL Server 2005,6.4.1 UNION操作符,例2：查询出“清华大学”出版的图书的编号和被借阅过的图书的编号，可以包括重复的列,SELECT BID FROM books WHERE PubComp=清华大学 UNION ALL SELECT BID FROM Borrow,SQL Server 2005,6.4.2 联合查询结果排序,例3：查询出“清华大学”出版的图书和被借阅过的图书的编号，不包括重复的列并将查询结果进行排序,SELECT BID FROM books WHERE PubComp=人民出版社 UNION SELECT BID FROM Borrow ORDER BY,SQL Server 2005,6.4.3 对单张表使用UNION操作符,例4：查询“清华大学”和“微软”出版的图书的书名和作者,SELECT Bname,Author FROM books WHERE PubComp=清华大学 UNION SELECT Bname,Author FROM books WHERE PubComp=微软,SQL Server 2005,6.4.4 交操作和差操作,例1：查询被借阅过的“清华大学”出版的图书的图书编号,SELECT BID FROM books WHERE PubComp=清华大学 AND EXISTS (SELECT * FROM Borrow WHERE Borrow. BID= books. BID),SQL Server 2005,6.4.4 交操作和差操作,例2：查询没被借阅过的“清华大学”出版的图书的图书编号,SELECT BID FROM books W