（中职）数据库管理与应用案例教程04-Chap04课件

1、（中职）数据库管理与应用案例教程04-Chap04ppt课件数据库管理与应用实例教程彭勇 主编第4章 数据查询 本章导航本章的主要内容及其在SQL Server2005数据库管理系统中的位置如图示。 SQL server 2005基础数据模型数据库技术概述数据库的创建数据库的管理数据库基础视图索引表存储过程触发器数据库对象子查询连接查询简单查询联合查询查询技术数据库备份与恢复数据库的安全游标、事务和锁数据库分享与附加数据库导入与导出数据库安全与管理数据库设计和应用程序开发学习要点（1）简单查询（2）多表查询（3）子查询 （4）联合查询简单查询 一、SELECT语句的结构 完整的SELECT语法

2、非常复杂，但是大多数SELECT都需要包括以下几个成分来描述返回什么样的结果集。要查询哪些表，以及表之间的逻辑关系。结果集中包括哪些列，即要从表中取哪些列的数据。以何种条件从表中取数据，即表中的行被包含在结果集中的条件。结果集中行的排列顺序。以下是SELECT语句的基本语法结构： SELECT ALL|DISTINCT ,INTO FROM ,WHERE GROUP BY HAVING ORDER BY ASC|DESC二、SQL的运算符与表达式1运算符算术运算符算术运算符运算符描 述+加法运算-减法运算*乘法运算/除法运算，如果两个表达式都是整数，则小数部分被截断%取模运算，返回两数相除后的

3、余数，例如12%7的值是5 逻辑运算符 逻辑运算符运算符描 述ALL用于比较标量值与单列集中的值。如果一组的比较都为TRUE，则比较结果为TRUEAND组合两个布尔表达式，如果两个表达式都是TRUE，则组合结果为TRUEANY用于比较标量值与单列集中的值。如果一组的比较中任何一个为TRUE，则比较结果为TRUEBETWEEN如果操作数在某个范围之内，那么结果为TRUEEXISTS如果子查询中包含了一些行，那么结果为TRUEIN如果操作数等于表达式列表中的一个，那么结果为TRUELIKE如果操作数与某种模式相匹配，那么结果为TRUENOT对任何其他布尔运算符的结果值取反OR如果两个布尔表达式中的

4、任何一个为TRUE，那么结果为TRUESOME如果在一组比较中，有些比较为TRUE，那么结果为TRUE 比较运算符 比较运算符运算符描 述运算符描 述=等于不等于大于!=不等于小于!=大于等于!不大于、=、=、！=、！、！、（比较运算符）5（位异或）、|（位或）6NOT7AND8ALL，ANY，BETWEEN，IN，LIKE，OR，SOME9=(赋值)2表达式 在Transact-SQL语言中，表达式是由标识符、变量、常量、标量函数、子查询、运算符等组成的。在Microsoft SQL Server 2005系统中，表达式可以在多个不同的位置使用，这些位置包括查询中搜索数据的一部分、搜索数据的

5、条件等。表达式可以分为简单表达式和复杂表达式两种类型。简单表达式只是一个变量、常量、列名、标量函数，复杂表达式是由两个或更多个简单表达式通过运算符连接起来的表达式。在复杂表达式中，两个或多个表达式有相同的数据类型，优先级低的数据类型可以隐式地转换为优先级高的数据类型。使用比较运算符或逻辑运算符组成两个或多个表达式时，生成的数据为布尔型值，即TRUE、FALSE、UNKNOWN。 三、课堂案例1简单查询的应用 【案例学习目标】 掌握SELECT语句在books数据库中的简单使用。【案例知识要点】使用TOP、DISTINCT、ALL关键字来限制返回的结果，重新指定查询结果列标题，使用INTO子句，

6、使用WHERE子句限定查询条件。【案例完成步骤】1使用TOP、DISTINCT、ALL关键字来限制返回的结果（1）查询books数据库中所有图书信息。完成语句如下：SELECT * FROM Book（2）查询图书信息表Book中前10条记录。完成语句如下：SELECT TOP 10 * FROM Book (3) 查询图书信息表Book中前10%条记录。完成语句如下：SELECT TOP 10 PERCENT * FROM Book (4) 查询图书信息表Book中印刷图书的印刷厂名称。完成语句如下：SELECT DINTINCT vprintery FROM Book用户可比较以下两条语句

7、：SELECT ALL vprintery FROM BookSELECT vprintery FROM Book2重新指定查询结果列标题 查询books数据库Book表中图书名称、图书价格和印刷厂名称。 SELECT vBookName 图书名称,mprice 图书价格,vprintery 印刷厂名称 FROM BookSELECT vBookName AS图书名称,mprice AS图书价格,vprintery AS印刷厂名称 FROM BookSELECT图书名称=vBookName,图书价格=mprice,印刷厂名称=vprintery FROM Book3使用INTO子句 使用INT

8、O子句创建一个新表，用于存放图书信息Book表中的出版社编号、图书名称和图书价格三类信息。SELECT cPublisherID, vBookName, mPrice INTO TEMP_Book FROM Book4使用WHERE子句限定查询条件 （1）比较大小和确定范围如要查询图书价格在30到50之间的出版社编号、图书名称和价格信息。完成语句如下：SELECT cPublisherID, vBookName, mPrice FROM Book WHERE mprice between 30 and 50如要查询出版社编号为“P0001”、“P0002”以外的所有图书信息。完成语句如下：SE

9、LECT * FROM Book WHERE cPublisherID NOT IN(P0001,P0002) （2）模糊查询 使用LIKE运算符的一般格式为：LIKE通配符及说明运算符描 述_（下划线）表示任意单个字符%表示任意长度的字符与特定范围（如af）或特定集（如abcdef）中的任意单字符匹配与特定范围（如af）或特定集（如abcdef）之外的任意单字符匹配（3）空值查询 在读者信息表中查询没有联系电话的读者编号、部门编号和读者姓名。SELECT cReaderID,cDepartmentID,vReaderName FROM Reader WHERE vphone IS NULL

10、在读者信息表中查询有联系电话的读者编号、部门编号和读者姓名。SELECT cReaderID,cDepartmentID,vReaderName FROM Reader WHERE vphone IS NOT NULL(4) 使用GROUP BY 子句和聚合函数进行分组查询常用的聚合函数聚合函数语 义COUNT(DISTINCT|ALL*)统计表的记录个数COUNT(DISTINCT|ALL列名)统计某列中值不为空值的个数SUN(DISTINCT|ALL列名)计算某列值的总和（此列必须为数值型）AVG(DISTINCT|ALL列名)计算某列值的平均值（此列必须为数值型）MAX(DISTINCT

11、|ALL列名)求一列中的最大值MIN(DISTINCT|ALL列名)求一列中的最小值(5) 使用ORDER BY子句为返回结果排序 查询读者信息表中的读者信息，并将结果按读者姓名的降序排列。完成语句如下：SELECT * FROM Reader ORDER BY vReaderName DESC 查询读者信息表中的读者信息，要求按部门分组和排序。SELECT cDepartmentID,count(*) FROM Reader GROUP BY cDepartmentID ORDER BY cDepartmentID课堂实践1 【任务1】查询读者信息表Reader中前8%条记录信息，并按部门编

12、号降序排列。【任务2】查询读者信息表中读者编号cReaderID、部门编号cDepartmentID、姓名vReaderName、性别cGender信息，分别将这些列重新指定列标题为读者编号、部门编号、姓名和性别。【任务3】统计读者信息表中部门编号为D0002的读者人数。交叉连接使用CROSS JOIN关键字来创建。其基本语法格式如下：SELECT 列名 ,nFROM 表名A CROSS JOIN 表名B n连接查询 一、交叉连接二、内连接其基本语法格式如下：SELECT 列名 ,nFROM 表名A INNER JOIN 表名B nON 三、外连接1左外连接 左外连接的关键字是LEFT OUT

13、ER JOIN，其基本语法格式如下：SELECT 列名 ,nFROM 表名A LEFT OUTER JOIN 表名B nON 2右外连接 右外连接的关键字是RIGHT OUTER JOIN，其基本语法格式如下：SELECT 列名 ,nFROM 表名A RIGHT OUTER JOIN 表名B nON 3全外连接 全外连接的关键字是FULL OUTER JOIN，其基本语法格式如下：SELECT 列名 ,nFROM 表名A FULL OUTER JOIN 表名B nON 四、多表连接与自身连接 在进行内连接时，有时候出于某种特殊需要，可能涉及三张表甚至更多表进行连接。三张表甚至更多表进行连接的基

14、本原理和两张表连接是相同的，先把两张表连接成一个大表，再将其和第三张表进行连接，以此类推。当一个表与其自己进行连接时，称为表的自身连接。要查询的内容均在同一张表中，可以将表分别取两个别名，一个是X，一个是Y。将X，Y中满足查询条件的行连接起来。这实际上是同一张表的自身连接。 五、课堂案例2连接查询的应用 【案例学习目标】 在books数据库中，从多个表中查询所需的数据。【案例知识要点】 内连接，外连接，多表查询与自身连接。【案例完成步骤】（1）查询图书的ISBN号、图书名称、图书价格、出版社名称和出版社地址等信息。完成语句如下：SELECT cISBN,vBookName,mPrice,vPu

15、blisherName,vAddress FROM Book INNER JOIN PublisherON Book.cPublisherID=Publisher.cPublisherID（2）查询所有读者的读者姓名和借书信息。完成语句如下：SELECT * FROM Reader LEFT OUTER JOIN BorrowBookON Reader.cReaderID=BorrowBook.cReaderID（3）查询所有出版社出版图书的信息。完成语句如下：SELECT * FROM Book RIGHT OUTER JOIN PublisherON Book.cPublisherID=P

16、ublisher.cPublisherID（4）查询图书名称、图书的出版社编号、图书的出版社名称、图书登记的员工编号和员工姓名。完成语句如下：SELECT vBookName,Book.cPublisherID,vPublisherName,Book.cEmpID,vEmpName FROM Book INNER JOIN Publisher ON Book.cPublisherID=Publisher.cPublisherIDINNER JOIN Employee ON Book.cEmpID=Employee.cEmpID（5）查询图书信息表中高于“C程序设计教程”价格的图书名称和图书价格

17、。并按图书价格的升序排列。完成语句如下：SELECT b2.vBookName,b2.mprice FROM Book b1INNER JOIN Book b2 ON b1.vBookName=C程序设计教程 AND b2.mprice=b1.mpriceORDER BY b2.mprice如果将SELECT子句中将b2换成b1，查询结果将会出现图所示的结果。 课堂实践2 【任务1】在books数据库中，查询每本图书的图书名称和出版社名称，并按图书名称的降序排列。【任务2】 实现读者表和借阅表的左外连接。 实现图书表与出版社表的右外连接。子查询 一、子查询分类 NOTIN子查询 比较子查询 其

18、通常格式为：ALL|ANY3. NOTEXISTS子查询 使用子查询还可以向表中添加多条记录。其语法格式如下：INSERT 表名(字段列表)SELECT 字段列表 FROM 表名 WHERE 条件表达式二、课堂案例3子查询的应用 【案例学习目标】 学会在books数据库中使用嵌套查询。【案例知识要点】 NOTIN子查询，比较子查询，NOTEXISTS子查询。【案例完成步骤】（1）查询出版社名称为“安徽大学出版社、安徽教育出版社、安徽科学技术出版社”的图书名称和图书价格。SELECT vBookName,mPrice FROM Book WHERE cPublisherID IN(SELECT

19、cPublisherID FROM Publisher WHERE vPublisherName IN(安徽大学出版社,安徽教育出版社,安徽科学技术出版社)（2）查询出版社名称不为“安徽大学出版社、安徽教育出版社、安徽科学技术出版社”的图书名称和图书价格。SELECT vBookName,mPrice FROM Book WHERE cPublisherID NOT IN(SELECT cPublisherID FROM Publisher WHERE vPublisherName IN(安徽大学出版社,安徽教育出版社,安徽科学技术出版社)(3) 在图书信息表Book中，查询其他印刷厂中比“北

20、京兴顺印刷厂”印刷的图书价格都高的图书名称和价格。 SELECT vBookName,mPrice FROM Book WHERE mPriceall(SELECT mPrice FROM Book WHERE vPrintery=北京兴顺印刷厂)（4）查询“北京大学出版社”出版的图书名称。SELECT vBookName FROM Book WHERE EXISTS(SELECT * FROM Publisher WHERE vPublisherName=北京大学出版社 AND Book.cPublisherID=Publisher.cPublisherID)课堂实践3 【任务1】查询档案室

21、管理人员（员工身份）登记的所有图书的图书名称和图书价格。【任务2】在图书信息表Book中，查询其他出版社中，比编号为“P0001”的出版社出版的图书价格都高的图书名称和价格。 联合查询1集合的并运算（UNION运算符）例如，利用UNION运算符查询并显示出“安徽教育出版社”和“安徽大学出版社”出版的图书名称和出版社编号。完成语句如下：SELECT vBookName,Book.cPublisherID FROM Book INNER JOIN PublisherON Book.cPublisherID=Publisher.cPublisherID AND vPublisherName=安徽教育出版社UNIONSELECT vBookName,Book.cPublisherID FROM Book INNER JOIN PublisherON Book.cPublisherID=Publisher.cPublisherID AND vPublisherName=安徽大学出版社2集合的差运算（EXCEPT操作符）3集合的交运算（INTE