第5章 SQL Server的数据查询.ppt

1、第5章 SQL Server的数据查询,第5章 SQL Server的数据查询,教学提示：数据库查询是数据库系统中最基本的也是最重要的操作。 教学目标：通过本章的学习，要求掌握各种查询方法，包括单表条件查询、单表多条件查询、多表多条件查询、嵌套查询，并能对查询结果进行排序、分组和汇总等操作。,SQL（Structured Query language，结构化查询语言）是关系数据库的标准通用语言。 1992年ANSI（美国国家标准局）发布了ANSI SQL-92标准，绝大多数关系数据库都遵从这个标准。 SQL Server使用的是ANSI SQL-92的扩展集，即通常所说的Transact-SQ

2、L，简写为T-SQL。,5.1 Transact-SQL概述,SQL语言是一种用于存取和查询数据，更新并管理关系数据库系统的数据查询和编程语言。 SQL语言的语法结构类似于英语，易学易用，书写随意。例如：显示图书的所有信息，写成SQL语句为： SELECT * FROM book1 或： SELECT * FROM book1,5.1 Transact-SQL概述,SQL语言的结构和书写规则 在编写SQL语句时，遵从某种准则以提高语句的可读性，使其易于编辑，是很有好处的。 以下是一些通常的准则： (1)SQL语句对大小写不敏感。不过，为了提高SQL语句的可读性，子句开头的关键字通常采用大写形式

3、。 (2)SQL语句可写成一行或多行，习惯上每个子句占用一行。,5.1 Transact-SQL概述,SQL语言的特点：简洁易懂、高度非过程化、二种使用方式灵活方便。 SQL语言的使用： 交互式-输入语句系统执行显示结果 嵌入式-嵌入到高级语言中使用，所用的高级语言称为宿主语言(VB , PB , C#,Delphi等),5.1 Transact-SQL概述,SQL语言特别适用于Client/Server体系结构，Client用SQL语句发出请求，Server处理Client发出的请求，SQL语言是一种说明性语言。 SQL语言由数据定义语言(DDL)、数据操作语言(DML) 和数据控制语言(D

4、CL)组成： 1) 数据定义语言(DDL) Create Alter Drop 2) 数据操作语言(DML) Select Insert Update Delete 3) 数据控制语言(DCL) Grant Revoke,5.1 Transact-SQL概述,5.2.1 打开一个数据库 在对book数据库中的数据进行操作之前，必须先使用USE命令打开book数据库，并使用GO语句作为结束行： USE book GO,5.2 SELECT语句,查询分类,查询某些列,查询某些行,多表查询,查询中有查询,统计查询,5.2.2 SELECT 语句（简单查询） SELECT 列名1,.列名n FROM

5、表名,5.2.2 简单查询,一、使用星号(*)和列名 星号(*) -返回所有列。 指定列名-返回指定列。,【例5.2】 从 book1表中查询所有书的信息。 USE book GO SELECT * FROM book1,【例5.3】 从 book1表中查询书的编号和书名。 USE book GO SELECT 编号,书名 FROM book1,5.2.2 简单查询,在SELECT之后使用DISTINCT关键字，会消除指定列的值都相同的那些行。 【例5.4】 从图书表book1中查询图书的出版社，要求消除值相同的那些行。 USE book GO SELECT DISTINCT 出版社 FROM

6、 book1,二、 使用DISTINCT消除重复值,5.2.2 简单查询,三、使用TOP n PERCENT仅返回n行 使用TOP关键字，可以从结果集中仅返回前n行。如果指定了PERCENT关键字，则返回前n%行，此时n必须介于0100之间。,【例5.5】从book1中查询所有图书的信息，要求只显示前5行数据。 USE book GO SELECT TOP 5 * FROM book1,【例5.5】 显示前%5的数据。 USE book GO SELECT TOP 5 PERCENT * FROM book1,5.2.2 简单查询,四、修改查询结果中列的标题(设置列别名) 设置字段别名有以下3

7、种方法。 第1种方法：将要显示的列标题用单引号括起来后接等号(=)，后接要查询的列名。 第2种方法：将要显示的列标题用单引号括起来后，写在列名后面，两者之间使用空格隔开。 第3种方法：将要显示的列标题用单引号括起来后，写在列名后面，两者之间使用AS关键字。,5.2.2 简单查询,【例5.6】 查询图书表中图书编号、ISBN号、定价、出版社，要求查询结果显示如下： 图书编号 图书书号 图书定价 图书出版社 XH5468 7504447536 19.8 中国商业 . (1) 使用第1种方法： USE book GO SELECT 图书编号=编号,图书书号=ISBN号,图书定价=定价,图书出版社=出

8、版社 FROM book1,5.2.2 简单查询,(2) 使用第2种方法： USE book GO SELECT 编号 图书编号, ISBN号 图书书号, 定价 图书定价, 出版社 图书出版社 FROM book1 GO,5.2.2 简单查询,(3) 使用第3种方法： USE book GO SELECT 编号 AS 图书编号, ISBN号 AS 图书书号, 定价 AS 图书定价, 出版社 AS 图书出版社 FROM book1 GO,注意：修改的只是查询结果的列标题，表中的列名 没有改变。,5.2.2 简单查询,五、 在查询结果中显示字符串 在SELECT子句中，将要增加的字符串用单引号括起

9、来，然后和列的名字写在一起，中间用逗号分隔开。 【例5.7】 查询book1表的信息，要求给出查询结果为： 书名 定价 目标市场 图书定价为：24.0 . GO SELECT 书名,图书定价为：,定价 FROM book1,5.2.2 简单查询,六、 列为表达式 SELECT子句中的选项列表也可以是表达式。 【例】查询Book1表中书的售价（假设售价为定价的九折）。 USE book GO SELECT 编号,书名,定价*0.9 AS 售价 FROM book1,5.2.2 简单查询,SELECT 列名1,.列名n FROM 表名 WHERE 条件表达式 系统在执行有条件的查询时，逐条地对表中

10、的每一行进行比较，检查它们是否满足条件，如满足条件则取出该行的有关信息，如不满足，则不取出该行的有关信息。,5.2.3 条件查询,条件 查询,1.比较表达式作查询条件,2.逻辑表达式作查询条件,3.使用BETWEEN关键字,4.使用IN关键字,5.使用LIKE关键字(模糊查询),5.2.3 条件查询,1比较表达式作查询条件(单条件) 在WHERE子句的条件表达式中，可以使用的比较运算符有,5.2.3 条件查询,使用WHERE子句时，结果集将返回表达式为真的数据行。 【例5.8】 在图书book1表中查询书名为“红楼梦图咏(3册)”的书的出版社。 USE book GO SELECT 出版社 F

11、ROM book1 WHERE 书名= 红楼梦图咏(3册),思考练习： 1.查询定价在30元以下的书 2.查询没有登记出版日期的书的信息,5.2.3 条件查询,【例5.18】 查询book1表中没有出版社的图书名称和出版社。 USE book GO SELECT 书名,出版社 FROM book1 WHERE 出版社 IS NULL GO,5.2.3 条件查询,2逻辑表达式作查询条件(多条件) 逻辑运算符- AND、OR、NOT。 由逻辑运算符将多个条件表达式将组合在一起构成查询条件。 表达式1 AND或OR 表达式2 或：NOT 表达式 示例：查询选修了计算机网络课(课程号为C001)或信息

12、安全技术课(课程号为C002)的学生的学号。 SELECT 学号 FROM 成绩表 WHERE 课程代码C001 OR 课程代码C002 ；,5.2.3 条件查询,3 使用BETWEEN关键字 用于查询指定范围内的信息。 表达式 NOT BETWEEN 表达式1 AND 表达式2 【例5.21】 查询图书定价小于2000元并且大于1000元的所有图书，要求查询结果按照定价升序排序。 USE book GO SELECT 书名,定价 FROM book1 WHERE 定价 BETWEEN 1000 AND 2000 GO,5.2.3 条件查询,4 使用IN关键字 【例5.12】 查询图书编号为“

13、XH5468”、“YBZT0001”、“YBZT0024”的书的名称。 USE book GO SELECT 书名 FROM book1 WHERE 编号= XH5468 OR 编号= YBZT0001 OR 编号=YBZT0024 GO,5.2.3 条件查询,【例5.12】 查询图书编号为“XH5468”、“YBZT0001”、“YBZT0024”的书的名称。 USE book GO SELECT 书名 FROM book1 WHERE 编号 IN (XH5468,YBZT0001,YBZT0024),使用IN关键字用于查询值满足集合的信息。 表达式 NOT IN (表达式1, 表达式2,表

14、达式n),5.2.3 条件查询,5. 使用LIKE关键字进行查询 表达式 NOT LIKE 格式串 查询与给定的某些字符串相匹配的数据。 LIKE是匹配运算符，它与字符串表达式相匹配，字符串表达式由字符串和通配符组成。,5.2.3 条件查询,SQL的4个通配符为： (1)% 百分号：匹配包含0个或多个字符的字符串。 (2)_ 下划线：匹配任何单个的字符。 (3) 排列通配符：匹配任何在范围或集合之内的单个字符，例如，m-p匹配的是m、n、o、p单个字符。 (4)不在范围之内的字符，匹配任何不在范围或集合之内的单个字符，例如，mnop或m-p匹配的是除了m、n、o、p之外的任何字符。(相关字符间

15、不需要分割符空格或逗号) 通配符和字符串必须括在单引号中。,5.2.3 条件查询,【例5.14】 查询以“中”开头的书名。 USE book GO SELECT * FROM book1 WHERE 书名 LIKE 中% GO,5.2.3 条件查询,【例5.15】 查询以“大学”两字结尾的书名的所有书。 USE book GO SELECT * FROM book1 WHERE 书名 LIKE %大学 GO,思考练习： 1.查询姓名为两个字的姓李的学生的信息。,5.2.3 条件查询,【例5.17】 查询书名第一个字不为“半”的所有图书。 USE book GO SELECT * FROM bo

16、ok1 WHERE 书名 LIKE 半% GO,5.2.3 条件查询,1比较表达式作查询条件 表达式1 比较运算符 表达式2 2逻辑表达式作查询条件 条件表达式 1 AND或OR 条件表达式 2 或：NOT 条件表达式 3使用BETWEEN关键字 表达式 NOT BETWEEN 表达式1 AND 表达式2 4使用IN关键字 表达式 NOT IN (表达式1, 表达式2,表达式n) 5使用LIKE关键字(模糊查询) 表达式 NOT LIKE 格式串 通配符： % _(下划线) (封闭方括号) ,条件子句组成总结：,小 结,一、抄题写在课堂作业本（使用book库) 1.查询中国少儿出版社的所有书的

17、信息 2.查询出版社的名称，去掉重复信息 3.查询book表中前10条记录，以及前10%的记录 4.查询中国少儿出版社和中国长安的所有书的信息，要求输出列为：编号、书名、定价、出版社 5.查询出版社属性为NULL的所有书的信息 6.查询查询书名中有中或天的信息（提示：中,天） 7.查询出版日期在2000年1月1日到2010年12月31日的书的信息 8.查询某4个出版社的信息（用IN） 9.分别查询书名中有中、第二个字为中、倒数第二个为中 、最后一个为中 的命令 二、P125-127 填空已学部分 1） （填在书上）,作 业,1.排序查询 2.聚合运算函数运算查询 3.分组查询 4.插入新表查询

18、 5.合并结果集 6.汇总计算,5.2.3 排序、分组汇总,1 排序查询-对查询结果排序 可以使用ORDER BY子句对查询结果重新排序，可以规定升序(从低到高)或降序(从高到低)，方法是使用关键字ASC(升序)或DESC(降序)。系统则默认为升序。 可以在ORDER BY子句中指定多个列，查询结果首先按第1列进行排序，对第1列值相同的那些数据行，再按照第2列排序依此类推，ORDER BY子句，要写在WHERE子句的后面。,5.2.3 排序、分组汇总,【例】 查询中国长安出版社出版的所有图书，要求查询结果按照定价升序排序。 USE book GO SELECT 书名,定价 FROM book1

19、 WHERE 出版社=中国长安 ORDER BY 定价 GO,5.2.3 排序、分组汇总,【例5.11】 查询book1表的编号、出版社、书名，要求查询结果首先按照书名降序排序，书名相同时，则按编号升序排序。 USE book GO SELECT 书名,编号,出版社 FROM book1 ORDER BY 书名 DESC, 编号 ASC GO,思考练习： 1.按学号递增、课程成绩递减的显示顺序 显示全部学生的成绩。,5.2.3 排序、分组汇总,2.聚合运算函数运算查询(汇总查询) P96,5.2.3 排序、分组汇总,SELECT子句中的选项列表可以是要指定的表达式或列的列表，表达式可以是列名、

20、函数或常数的列表。 【例5.9】 查询图书book1表中的最小定价、最大定价和平均定价。 USE book GO SELECT MIN(定价) AS 最小定价, MAX(定价) AS 最大定价, AVG(定价) AS 平均定价 FROM book1 GO,5.2.3 排序、分组汇总,思考1：计算所有学生所学课程的最高分数、最低分数和平均分数，用哪些聚合函数？哪张表？ SELECT MAX(成绩) AS 最高分 , MIN(成绩) 最低分 , 平均分= AVG(成绩) FROM 成绩表 思考2：加个条件：某位同学的呢？,WHERE 学号=G0610001,5.2.3 排序、分组汇总,思考3：计算

21、所有开设的课程数，用哪个聚合函数？哪张表？ SELECT count(*) as 开设的课程数 FROM 课程设置表; 思考4：统计全部学生的数量呢？加个条件：某个班的的？ SELECT count(*) as 学生的数量 FROM 学生信息表,WHERE 班级=0601,5.2.3 排序、分组汇总,再来： 1.统计某门课程的总分。,问题提出：在学生信息表中查询每个专业的人数？,首先，按专业代码分组,然后，计算每组的人数,3.分组查询 使用GROUP BY子句用来将列的值分成若干组再加以统计。 SELECT 列名1,列名2， FROM 表名1， GROUP BY 列名1,列名2, HAVING

22、 分组条件 ；,5.2.3 排序、分组汇总, 使用GROUP BY子句 【例】计算每个专业的学生人数? SELECT 专业代码，COUNT(*) AS 人数 FROM 学生信息表 GROUP BY 专业代码,5.2.3 排序、分组汇总,【例5.25】 按出版社分类统计每个出版社出书的本数。 USE book GO SELECT 出版社, count(出版社) AS 本数 FROM book1 GROUP BY 出版社 GO,5.2.3 排序、分组汇总,思考1： 计算每位学生的平均成绩？ SELECT 学号，AVG(成绩) AS 平均成绩 FROM 成绩表 GROUP BY 学号,5.2.3 排

23、序、分组汇总,GROUP BY子句中还可以按照多列进行分组。 思考2： 计算每个专业的男、女学生人数? SELECT 专业代码，COUNT(*) AS 人数 FROM 学生信息表 GROUP BY 专业代码，性别 要点： 1.在GROUP BY后出现的列名,一定出现在SELECT 后； 2.有GROUP BY 分组一定有计算,5.2.3 排序、分组汇总, HAVING子句 在数据查询中，有时只希望选择满足一定条件的分组。一般是利用GROUP BY子句进行分组，利用HAVING子句判断分组应满足的条件。 示例：查询学生总数超过100人的专业及其具体的总人数。 SELECT 专业代码，COUNT(

24、*) AS 人数 FROM 学生信息表 GROUP BY 专业代码 HAVING COUNT(*)100 ； 思考：查询学生总数超过55人的班级？,5.2.3 排序、分组汇总,HAVING子句常常用在GROUP BY子句之后，在分组之后再进行判断哪些分组需要被保留。 如果查询条件需要在分组之前被应用，则使用WHERE子句，其限制查询条件比使用HAVING子句更有效，这种技巧减少了要进行分组的行数。,5.2.3 排序、分组汇总,【例5.27】 查询不同出版社所出书的平均价格大于60元的书。 USE book GO SELECT 出版社 , AVG(定价) AS 平均价格 FROM book1 G

25、ROUP BY 出版社 HAVING AVG(定价) 60 GO 总结：WHERE子句对原始记录进行过滤，HAVING子句对结果进行过滤。,思考：给5.25题加个条件数量超过100本的的出版社？,5.2.3 排序、分组汇总,【例5.26】 查询出版社为“中国长安”所出书的平均价格。 USE book GO SELECT 出版社 , AVG(定价) AS 平均价格 FROM book1 GROUP BY 出版社 HAVING 出版社=中国长安 GO 使用WHERE子句的SELECT语句如下： USE book GO SELECT 出版社,AVG(定价) AS 平均价格 FROM book1 WH

26、ERE 出版社=中国长安 见P109页错误 GROUP BY 出版社 GO,常用的比较完整的SELECT简单查询语句： SELECT 列名1, FROM 表名1, WHERE 条件表达式 GROUP BY 列名1， HAVING 分组条件 ORDER BY 列名 ASC|DESC，; 其中，只有两个黑色表示的子句是必须的，其它子句可以根据查询和结果显示要求选择或缺省。,重要!熟记基本语法与顺序!,5. 合并结果集:使用UNION运算符 UNION运算符用于将两个或多个查询结果合并成一个结果，当使用UNION运算符时，需要遵循以下两个规则： (1) 所有查询中列数和列的顺序必须相同。 (2) 所

27、有查询中按顺序对应列的数据类型必须兼容。,5.2.3 排序、分组汇总,SELECT查询语句 UNION ALL SELECT查询语句,注意： (1)结果集中的列名来自第一个SELECT语句。 (2)最后一个SELECT查询可以带ORDER BY子句,对整个UNION 操作结果集起作用。且只能用第一个SELECT查询中的字段作排序列。 (3)系统自动删除结果集中重复的记录，除非使用ALL关键字。,5.2.3 排序、分组汇总,【例5.29】 从图书book1表中查询图书名，从图书book2表中查询图书编号，使用UNION运算符合并这两个查询结果。 USE book GO SELECT 书名 FRO

28、M book1 UNION SELECT 编号 FROM book2,5.2.3 排序、分组汇总,【例】查询选修了c001号课程或c002号课程的学生学号。 select 学号 from 成绩表 where 课程代码=c001 union select 学号 from 成绩表 where 课程代码=c002,5.2.3 排序、分组汇总,6. 汇总计算 使用COMPUTE子句 COMPUTE子句用来计算总计或进行分组小计，总计值或小计值将作为附加的新行出现在查询结果中。该子句用在WHERE子句之后。 与分组查询子句汇总计算的比较： 相当于 明细+汇总计算,5.2.3 排序、分组汇总,【例5.23

29、】 查询出版社“中国长安”出版图书的本数，包括编号、书名、ISBN号和出版社，并计算平均定价。 USE BOOK GO SELECT 编号,书名,ISBN号,定价,出版社 FROM book1 WHERE 出版社=中国长安 COMPUTE AVG (定价),5.2.3 排序、分组汇总,例如：-compute的应用 use student go Select * from 成绩表 compute max(成绩), min(成绩), avg(成绩),5.2.3 排序、分组汇总,多表连接的分四大类: 交叉连接：笛卡尔积。 内连接：相等连接，自然连接（相等连接去掉重 复列），不等值连接，带有选择条件的

30、连接。 外连接：左外连接，右外连接，全外连接。 自连接：所连接的两张表为同一张表。,5.2.4 连接查询,1.笛卡儿积（交叉连接） 关系R为n目(属性)，关系S为m目(属性)，则关系R与S的笛卡儿积RS为m+n目，元组数为nm。 即每个元组的前n个分量来自R，后m个分量来自S.,示例： A B C A B R.A R.B R.C S.A S.B a b c b g = a b c b g d a f d a a b c d a c b d d a f b g d a f d a c b d b g c b d d a,5.2.4 连接查询,交叉连接方法： SELECT * FROM 表1 CR

31、OSS JOIN 表2 或 : SELECT * FROM 表1,表2,5.2.4 连接查询,运行分析下面的下列程序,得出什么是笛卡儿积。 USE book GO SELECT * FROM book1,teacher GO 结果大多数没有实际意义,5.2.4 连接查询,2.内连接 满足指定条件的记录连接成一条新的记录。 相等连接 相等连接是将要连接的列作相等比较后所作的连接，相等连接会产生冗余，因为连接的列要显示两次。 【例5.32】查询所有图书和所有作者的信息。 USE book GO SELECT * FROM book1,teacher WHERE book1.编号=teacher.编

32、号 GO,5.2.4 连接查询,book1,teacher,内连接有两种连接方式： 使用where子句进行连接 SELECT FROM 表名1 , 表名2 ， Where 表名1.列名=表名2.列名 and 使用from子句进行连接 SELECT FROM 表名1 INNER JOIN 表名2 ON 表名1.列名=表名2.列名 ,5.2.4 连接查询,【例5.32】解法二： 查询所有图书和所有作者的信息。 在from中指定连接，SELECT语句如下： USE book GO SELECT * FROM book1 INNER JOIN teacher ON book1.编号=teacher.编

33、号 GO,5.2.4 连接查询,多表连接查询 注意： 1.连接条件数是连接表的个数-1 2.连接数目太多，影响查询效率,5.2.4 连接查询,自然连接 P117 例题5.33 在相等连接的基础上，SELECT子句指定输出的列名，删除重复的列。 USE book GO SELECT book1.*, 作者姓名, 职称 FROM book1 INNER JOIN teacher ON book1.编号=teacher.编号 GO,5.2.4 连接查询,【例5.34】查询图书的定价在50元以上的图书的书名、定价、作者姓名和出版社。 USE book GO SELECT 书名,定价,作者姓名,出版社

34、FROM book1 INNER JOIN teacher ON book1.编号=teacher.编号 where 定价50 GO,5.2.4 连接查询,【例】查询图书的编号，书名，作者姓名和职称。 USE book GO SELECT 编号,书名,作者姓名,职称 FROM book1 INNER JOIN teacher ON book1.编号=teacher.编号 GO 在连接查询中，如果列名在多个表中重名，在select子句中，一定要在列名前加上表的前缀，即：表名.列名。,5.2.4 连接查询,可以给表定义别名，以方便查询时对列的引用和简化连接条件的书写。 定义别名的方法：在子句中将要

35、定义别名的表用空格间隔，然后紧随所定义的别名的字符。 【例5.41】 查询所有作者所出的图书的信息。 USE book GO SELECT b.书名,b.定价,t.作者姓名 FROM teacher t , book1 b WHERE t.编号=b.编号 GO,5.2.4 连接查询,谢谢！,End！,5.2.4 连接查询,课程表,复 习-内连接,内连接-满足指定条件的记录连接成一条新的记录，即只有在两个表中同时匹配的行才能在结果集中出现。,教师表,SELECT 课程名称, 姓名 as 任课教师 FROM 课程表 inner join 教师表 ON 课程表.教师编号=教师表.教师编号 或 SELECT 课程名称, 姓名 as 任课教师 FROM 课程表 , 教师表 WHERE 课程表.教师编号=教师表.教师编号 注意：多表连接时，一定要有连接条件。,将一个查询块嵌套在另一个查询块的条件子句中的查询被称为嵌套查询（又称子查询）。 SQL Server 允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询。 嵌套查询一般的求解方法是由里向外进行处理，即每个子查询在上一级查询处理之前求解。 子查询中不能使用order