1、,电子信息工程系 韩雪 2011年8月10日,第7章 查询,一、课程分析及培养目标,课程分五部分：用SSMS创建表和管理表及表数据、用T-SQL语句创建管理表、用T-SQL语句管理表数据、数据导入与导出，进行项目2的实训。,韩雪制作,（1）知识目标：熟练掌握理解数据完整性概念； 掌握使 用约束实施数据完整性的方法； 掌握使用规则实施数据完整性的方法； 掌握使用默认值对象实施数据完整性的方； 掌握标识列的使用。 （2）能力目标：培养学生建立数据约束理解能力。 （3）素质结构目标：培养学生探索数据库奥秘的兴趣和逻辑、全面思考问题的能力。 通过对T-SQL的学习，全面了解数据库的编程规范与技巧。,韩
2、雪制作,本课程的授课班级为10计网班，该班未学过数据库方面的课程，对数据库理论知识没有知识储备，需要补充一些基础知识，这使本课程的学习具备了一定的难度。,韩雪制作,为了突破重点，解决难点，顺利达成教学目标，我结合教材特点和学情分析的基础上，为了激发学生的兴趣，力求形成学生强烈的学习动机和主观能动性的学习态度，教学方法设计如下：,自主探究法,韩雪制作,参照完整性约束,T-SQL建 立约束,规则使用,T-SQL管理约束,1、创设情境导入新课、引入概念情境创设：展示数据库管理系统，导入新课；引入概念：重点介绍建库过程；知识扩展：T-SQL语言等。,韩雪制作,第7章 数据查询,知识技能目标:1理解数据
3、查询的意义。2熟练掌握各种查询技术，包括单表查询、多表查询、汇总查询、分类汇总、汇总计算、子查询等，并能对查询结果排序、合并、保存。,引导案例：按照第2章的设计在计算机上建立起教务管理数据库后，就可以访问其中的数据了，如查看某个班的学生信息，查看某门课程的信息，查看某个学生某门课程的成绩，统计某班某门课程的最好成绩、最差成绩、平均成绩等。如何实现这些操作呢？这就需要使用数据查询技术。本章就来介绍各种数据查询技术。,7.1 SELECT语句7.2 简单查询7.3 多表查询7.4 汇总查询7.5 子查询 7.6 小结,第7章 数据查询,7.1 SELECT语句 P137,查询是关系数据库中最基本的
4、数据操作，通过SELECT语句来实现,基本语法格式： SELECT ALL | DISTINCT |top 字段列表 INTO 目标数据表 FROM 源表名或视图名1，n WHERE 条件表达式 GROUP BY 列名1,列名n HAVING 搜索表达式 ORDER BY 排序表达式 ，n ASC DESC COMPUTE 行聚合函数名（统计表达式），n BY 分类表达式 ，n ,返回目录,SELECT子句：指定由查询返回的列。 INTO子句：将检索结果存储到新表或视图中。 FROM子句：用于指定引用的列所在的表和视图，每一个SELECT子句必须有一个FROM子句。 WHERE子句：用来给出查
5、询的条件或者多个表之间的连接条件。当FROM子句包含多个表（视图）时，必须在WHERE后面给出连接条件。 GROUP BY子句：指定用来放置输出行的组，并且如果SELECT子句中包含聚合函数，则计算每组的汇总值。 HAVING子句：指定组或聚合的搜索条件。HAVING通常与GROUP BY子句一起使用。如果不使用GROUP BY子句，HAVING 的行为与WHERE子句一样。 ORDER BY子句：指定结果集的排序。,7.2.1基本查询 1SELECT子句 SELECT子句用于指定由查询返回的列的数据集。格式如下: SELECT ALL|DISTINCTTOP NPERCENT列名1,列名2,
6、列 名NFROM 表名或视图名 其中各参数的含义如下。 ALL：指定在结果集中可以显示重复行。ALL是默认设置。 DISTINCT：指定在结果集中只能显示唯一行，即表示输出无重复的所有记录。 TOP N PERCENT：指定只从查询结果集中输出前N行。如果还指定了 PERCENT，则只输出前百分之N行。,7.2 简单查询,2SELECT语句的执行方式 在对数据库中的数据进行操作之前，必须先启动数据库服务器，再使用USE命令打开该数据库，并使用GO语句作为结束行。 例7.1 在学生表中查询性别为“女”的学生的姓名、出生日期 USE 教务管理系统 GO SELECT 姓名, 出生日期 FROM 学
7、生表 WHERE 性别=女 go 3SELECT语句的各种使用方法 （1）使用星号（*）和列名。 在SELECT子句中使用星号（*），可以从FROM子句中指定的表或视图中查询并返回所有列，如果在SELECT子句中给出具体的列名，则只查询所指定列名的信息。, 查询所有的列 例7.2 从学生表中检索所有学生的所有信息 USE 教务管理系统 GO SELECT * FROM 学生表 GO -将学生表的所有信息显示出来。 查询特定的列 例7.3 从课程表中检索所有课程的课程名、学时、学分。 SELECT 课程名,学时,学分 FROM 课程表 GO （2）使用DISTINCT不显示重复值。在SELECT
8、之后使用DISTINCT关键字，可以消除指定列值相同的行。,（3）使用TOP关键字。让用户指定返回前面一定数量的数据语法格式如下： SELECT TOP n | TOP n PERCENT 列名1,列名2,列名n FROM 表名 其中，各参数的含义如下。 TOP n：表示返回最前面的n行，n表示返回的行数。 TOP n PERCENT：表示返回前面的n%行。 例7.5 从学生表表中返回前面10行的记录。 SELECT TOP 10 * FROM 学生表 GO -只显示查询结果前10行的数据。,（4）设置字段别名 在显示查询的结果时，为了阅读方便，用户可以根据实际需要对查询数据的列标题进行修改，
9、或为没有标题的列加上临时标题。 语法格式： 表达式 as 别名 或 字段别名= 表达式 例7.3 在教务管理数据库中查询学生姓名、性别和年龄。 USE 教务管理数据库 SELECT 姓名 as 学生姓名,性别,datediff(year,出生日期,getdate() as 年龄FROM 学生表或 SELECT 学生姓名=姓名,性别, 年龄=datediff(year,出生日期,getdate() FROM 学生表 或 SELECT 姓名 学生姓名,性别,datediff(year,出生日期,getdate() 年龄FROM 学生表,返回目录,（5）查询全部记录 要查询全部记录可在SELECT后
10、使用ALL，ALL是默认设置，因此也可以省略。 例7.4 在教务管理数据库中查询所有班级的人数。 SELECT ALL 班号,班名,人数FROM 班级表 （6）取消重复行 要在查询结果中去掉重复的行，需在SELECT子句中使用关键字DISTINCT。 例7.5在教务管理数据库中查询被选的课程。 SELECT distinct 课程号FROM 选课表,返回目录,7.2.2 条件查询,条件查询是指在数据库中按给定条件进行查询，在SELECT语句中 使用WHERE子句可以给定查询条件。语法格式：SELECT 字段名1，字段名nFROM 表名WHERE 条件表达式 构成这个条件表达式的运算符包括：P9
11、6 比较运算符：=、!、!=、=、!= 逻辑运算符：NOT、AND 、OR、BETWEEN AND、IN、ANY、SOME、ALL、LIKE、EXISTS、IS NULL、IS NOT NULL 1使用比较运算符,返回目录,WHERE 子句中常用的查询条件,例7.8 在教务管理数据库中查询各门课程的不及格学生的学号、课程号、成绩。 SELECT 学号,课程号,成绩 FROM 选课表WHERE 成绩60 2使用逻辑运算符 例7.9 在教务管理数据库中查询00001号课程的不及格学生的学号、课程号、成绩。SELECT 学号,课程号,成绩 FROM 选课表WHERE (成绩60) AND (课程号=
12、000001),返回目录,3使用BETWEEN关键字 格式：表达式 NOT BETWEEN 表达式1 AND 表达式2 当表达式的值在表达式1的值与表达式2的值之间时，返回真，否则返回假。 例7.10 在教务管理数据库中查询00001号课程成绩在90分到100分之间的学生。 SELECT 学号,课程号,成绩 FROM 选课表 WHERE (课程号=000001)AND(成绩 BETWEEN 90 AND 100),返回目录,4使用IN关键字 格式： 表达式 NOT IN (表达式1,表达式2,表达式n) 当表达式的值为IN后面括号中的表达式1，表达式2，表达式n的值之一时，返回真，否则返回假。
13、 例7.11 在教务管理数据库中查询jy071、jy072、jw071班的学生USE 教务管理数据库SELECT 姓名,性别,班级FROM 学生表WHERE 班级 IN (jsj01,jsj02,jd01) 5使用LIKE关键字 LIKE关键字用于模糊查询。在实际的应用中，有时不能精确地给出查询条件，只知道一些不确切的查询线索，这时就需要进行模糊查询。,返回目录,格式：表达式 NOT LIKE 格式串其中格式串通常与通配符配合使用，给出一个模式。 百分号 % ：代表任意长度的字符串。 下划线 _ ：代表任意单个字符。 ：代表方括号里列出的范围的任意一个字符。 ：代表任意一个没有在方括号里列出的
14、字符。 例7.12 查询jy071、jy072和jw071班学生的姓名和班级。 SELECT 姓名,班级FROM 学生表WHERE 班级 LIKE jyw0712 例7.13 查询学生表中所有姓“张”的学生的信息。 SELECT * FROM 学生表 WHERE 姓名 LIKE 张% GO,返回目录,6使用IS NOT NULL关键字在涉及空值的查询中，可以使用IS NULL 或者 IS NOT NULL来指定这种查询条件。通常，该查询条件要与其他的查询条件共同使用，NULL 值表示列的数据值未知或不可用。 语法格式： 表达式 IS NOT NULL 例7.13 在选课表中查询没有成绩的记录。
15、 SELECT 学号,课程号,成绩FROM 选课表WHERE 成绩 IS NULL,返回目录,7.2.3 查询结果排序,对查询结果进行排序，需使用ORDER BY子句。语法格式：ORDER BY 表达式1 ASC|DESC ，n 功能：查询结果按照ORDER BY后的表达式的值排序，ASC表示升序，DESC表示降序，默认为升序。 例7.14 查询0000000001号课程的学生成绩，结果按分数从高到低的显示，成绩相同的情况按学号顺序排列。USE 教务管理数据库SELECT 课程号,学号,成绩FROM 选课表WHERE 课程号=000000001ORDER BY 成绩 DESC,学号,返回目录,
16、7.2.4 查询结果保存,查询的结果可显示，也可以不直接显示，而是保存到一个数据表或一个临时表中。保存查询结果，使用INTO子句。 语法格式：INTO 目标数据表 7.15 查询jy071班学生信息，将结果保存到表tb1中.SELECT *INTO tb1FROM 学生表WHERE 班级=jy071 如果要将查询结果存放到临时表，则在临时表名前要加“#”号. 例7.16 将不及格学生的学号、课程号、成绩存入临时表中.SELECT *INTO #bjgFROM 选课表WHERE 成绩60,返回目录,7.3 多表查询P132,在上一节的查询举例中，所查询的数据都来自一个表，而在实际应用中，要查询的
17、数据经常来自多个表，这时就需要进行多个表之间的连接查询。 连接查询实际上是通过多个表之间的共同列的相关性来查询数据的，是数据库查询最主要的方式。连接查询就是关系运算的连接运算。 在SQL Server中连接查询分为内连接查询、外连接查询、交叉连接查询。 多表查询中的表或视图可以在同一个数据库中，也可以来自不同的数据库。在FROM子句中最多可以使用16个表或视图。,返回目录,7.3.1 内连接查询,内连接也叫自然连接，是将两个表中的列进行比较，返回每对匹配的行，如果未指定连接类型，则默认设置为内连接。 语法格式： FROM 表1 inner JION 表2 ON 条件表达式 语法一：SELECT
18、 列FROM 表1 inner JION 表2ON 表1.列=表2.列 语法二：SELECT 列FROM 表1，表2，表3WHERE 条件 例7.17 从教学管理数据库中查询学生的学号、姓名和所学课程的课程号、成绩。SELECT 学生表.学号,姓名,课程号,成绩FROM 学生表 JOIN 选课表ON 学生表.学号= 选课表.学号,返回目录,例7.18 从教学管理数据库中查询学生的学号、姓名、课程号、课程名和成绩 select 学生表.学号 ,学生表.姓名 ,课程表.课程号 ,课程表.课程名 ,选课表.成绩 from 学生表,课程表,选课表 where(学生表.学号=选课表.学号 and 选课表
19、.课程号=课程表.课程号) 使用别名(简化编程)： select xs.学号 ,xs.姓名 ,kc.课程号 ,kc.课程名 ,xk.成绩 from 学生表 xs,课程表 kc,选课表 xk where(xs.学号=xk.学号 and xk.课程号=kc.课程号),7.3.2 外连接查询,使用外连接，以主表中每行的数据去匹配从表中的数据行：如果符合连接条件则返回到结果集中；如果没有找到匹配行，则主表的行 仍然返回到结果集中，相应的从表中的数据行被填上NULL值后也返回到结果集中。 根据返回行的主从表形式不同，外连接可以分为左外连接、右外连接和全外连接。 左外连接是以连接中的左表为主表，对左表不加
20、限 制； 右外连接是以连接中的右表为主表，对右表不加限制； 全外连接对两个表都不加限制，所有两个表中的行都会包括在结果集中。,返回目录,1左外连接,左外连接是指返回所有的匹配行，并从关键字JOIN左边的表中返回所有不匹配行。由此可知，即使不匹配，JOIN关键字左边的表中数据也将被保留，所以在左外连接中JOIN关键字左边的表为主表，右边的表为从表。语法格式：FROM 表1 LEFT OUTER JION 表2 ON 条件表达式 例7.19 从教学管理数据库中查询学生的学号、姓名、课程名和成绩（包括没有成绩的新入学同学的学号、姓名） SELECT XS.学号,姓名,KC.课程名,成绩FROM 学生
21、表 AS XSLEFT JOIN 选课表 AS XK ON XK.学号=XS.学号LEFT JOIN 课程表 AS KC ON XK.课程号=KC.课程号,返回目录,与左外连接相反，右外连接返回所有的匹配行，从关键字JOIN右边的表中返回所有不匹配的行。因此，在右外连接中JOIN关键字右边的为主表，而关键字左边的为从表，右外连接返回结果与左外连接相同，即不满足匹配的结果集在相应列中填入NULL值。语法格式：FROM 表1 RIGHT OUTER JION 表2 ON 条件表达式 例7.20 从教学管理数据库中查询学生的学号、姓名、课程名和成绩（包括还没有被选过的新课的名称）。 SELECT X
22、S.学号,姓名,KC.课程名,成绩FROM 选课表 AS XKJOIN 学生表 AS XS ON XK.学号=XS.学号RIGHT JOIN 课程表 AS KC ON XK.课程号=KC.课程号,返回目录,2右外连接,7.4 汇总查询P113,汇总查询是用SELECT语句对表中数据进行统计汇总，得到汇总信息。 汇总查询需使用聚合函数，常用的聚合函数有sum( )、avg( )、max( )、min( )、count( )等。 7.4.1 简单汇总 简单汇总是在SELECT语句的选择列表中只使用聚合函数对表中的指定列进行计算，将所有符合查询条件的数据汇总到一起，生成一个汇总数据记录。 例7.25
23、 在教务管理数据库中查询000000001号课程的最高分、最低分、平均分、总分和选修这门课的学生人数。 SELECT max(成绩) 最高分,min(成绩) 最低分,avg(成绩) 均分,sum(成绩) 总分,count(*) AS 人数FROM 选课表WHERE 课程号=000000001,返回目录,7.4.2 分组汇总,分组汇总是将数据记录依据设置的条件分成多个组，分别进行统计汇总，生成多个汇总数据记录。分组汇总需要使用GROUP BY子句来完成。语法格式：GROUP BY 分组表达式 ,.n HAVING 搜索条件 例7.26 在教务管理数据库中统计各门课程的最高分、最低分和平均分。 S
24、ELECT 课程号,max(成绩) 最高分,min(成绩) 最低分,avg(成绩) 均分FROM 选课表GROUP BY 课程号,返回目录,使用GROUP BY子句应注意： SELECT子句后面每一列数据除了出现在统计函数中的列以外，都必须在GROUP BY子句中应用； GROUP BY子句中不支持对列设置别名，也不支持任何使用了统计函数的集合列。 例7.27 分别统计各班男生、女生的最大年龄、最小年龄、平均年龄和人数。 select 班级, 性别, max(datediff(year,出生日期,getdate() 最大年龄,min(datediff(year,出生日期,getdate() 最
25、小年龄,avg(datediff(year,出生日期,getdate() 平均年龄,count(*) as 人数from 学生表group by 班级,性别,返回目录,在使用GROUP BY子句进行分组汇总时，还可以使用HAVING子句对分组汇总查询的结果进行筛选。 HAVING子句通常与GROUP BY子句一起使用，与where类似从分组汇总的结果中检索满足条件的组。 例7.28 查询各班均分在70分以上的学生的班级、学号、姓名和均分，结果按均分降序排列。SELECT 班级, 选课表.学号,姓名,AVG(成绩) AS 均分FROM 选课表,学生表WHERE 选课表.学号=学生表.学号GROU
26、P BY 班级,选课表.学号, 姓名HAVING AVG(成绩) 60 ORDER BY AVG(成绩) DESC,返回目录,7.4.3 汇总计算,汇总计算使用COMPUTE或COMPUTE BY子句，最后不仅显示统计结果，同时还显示各个统计项的明细数据。格式： COMPUTE 聚合函数名(统计表达式),nBY 字段名 ,.n 其中行聚合函数为SUM、AVG、MAX、MIN、COUNT等，还可以是统计方差或标准偏差等。 将结果按照by后面的字段进行分组。例7.29 查询学了000001号课程的学生的学号、姓名、课程名和成绩，并统计出这门课的最高分、最低分和均分。 SELECT 学生表.学号,姓
27、名,课程名,成绩FROM 学生表,选课表,课程表WHERE 学生表.学号=选课表.学号 AND 课程表.课程号=选课表.课程号 AND 选课表.课程号=000001COMPUTE MAX(成绩),MIN(成绩),AVG(成绩),返回目录,例7.30 查询各门课程的学生的学号、姓名、课程名和成绩，并统计出各门课的最高分、最低分和均分。 SELECT 学生表.学号,姓名,课程名,成绩FROM 学生表,选课表,课程表WHERE 学生表.学号=选课表.学号 AND 课程表.课程号=选课表.课程号ORDER BY 选课表.课程号COMPUTE MAX(成绩),MIN(成绩),AVG(成绩) BY 选课表
28、.课程号 COMPUTE或COMPUTE BY子句中的表达式，必须出现在select中，不能使用在选择列表中指定的列的别名。 如使用COMPUTE BY，则必须用ORDER BY。COMPUTE BY指定的列必须与ORDER BY指定的列相同，或是其子集，并且必须有相同的顺序。Eg：ORDER BY a，b，c，则COMPUTE BY子句可以是下面的任意一个： COMPUTE BY a, b, c COMPUTE BY a, b COMPUTE BY a COMPUTE BY子句中，不能用ntext、text和image数据类型。 INTO子句不能与COMPUTE子句一起使用。,书P83,以d
29、obo数据库为例讲解,7.5 子查询P125,在SELECT语句的WHERE子句或HAVING子句中嵌套另一条SELECT语句的查询称为嵌套查询。 外层的SELECT语句称为外层查询或父查询，内层的SELECT语句称为内层查询或子查询。 子查询分为不相关子查询和相关子查询。 很多子查询可以改用多表查询。 7.5.1 不相关子查询 不相关子查询的查询条件不依赖于父查询，其执行过程是：首先执行子查询，子查询得到的结果集不显示，而是作为父查询的查询条件的一部分，用于父查询；然后执行父查询，显示查询结果。,返回目录,1子查询返回单个值对于子查询返回单个值的情况，可以用比较运算符，=，等连接父查询与子查
30、询。例7.31 查询学了大学英语课的学生的学号和姓名 SELECT 选课表.学号,姓名FROM 选课表,学生表WHERE 选课表.学号=学生表.学号 AND 课程号=(SELECT 课程号 FROM 课程表 WHERE 课程名=大学英语) -子查询返回的单个值 例7.32 查询其他班比计算机网络2班学生年龄都小的学生姓名和年龄 SELECT 姓名, datediff(year,出生日期,getdate() 年龄 FROM 学生表 WHERE 班级计算机网络2班AND datediff(year,出生日期,getdate()(SELECT MIN(datediff(year,出生日期,getda
31、te() FROM 学生表 WHERE 班级=计算机网络2班),返回目录,2子查询返回多个值,对于子查询返回多个值的情况，经常用IN、NOT IN连接父查询与子查询。 IN表示在子查询返回的多个值中，即等于其中的一个值； NOT IN表示不在子查询返回的多个值中，即不等于其中的任何一个值。 例7.33 查询jy072班没有学习000001课程的学生的学号和姓名.SELECT 学号,姓名FROM 学生表WHERE 班级=jy072 AND 学号 NOT IN(SELECT 学号 FROM 选课表 WHERE 课程号=000001) 例7.34 在教务管理数据库中查询没有被选过的课程 SELECT
32、 课程号,课程名FROM 课程表WHERE 课程号ALL(SELECT 课程号 FROM 选课表),返回目录,例7.35 在选课表1中插入选课表中所有课程号为000001的记录。 INSERT INTO 选课表1SELECT * FROM 选课表WHERE 课程号=000001 例7.36 将选课表1中课程号为0000001的成绩都加5分。 UPDATE 选课表SET 成绩=成绩+5WHERE 学号 IN(SELECT 学号 FROM 学生表 WHERE 班级=计算机软件1班),7.5.2 相关子查询,相关子查询的查询条件依赖于外层父查询的某个属性值。相关子查询在执行时，要使用到外层父查询的数
33、据，子查询的结果集取决于外层父查询当前的数据行。 相关子查询的执行过程是：首先选取外层查询表中的第一行记录，内层的子查询则利用此行中相关的属性值进行查询，然后外层查询根据子查询返回的结果判断此行是否满足查询条件，如果满足条件，则把该行放入外层查询结果集合中。重复执行这一过程，直到处理完外层查询表中的每一行数据。 相关子查询通常使用EXISTS连接父查询与子查询。,返回目录,例7.37查询选修了000001号课程的学生的学号和姓名。 SELECT 学号,姓名FROM 学生表WHERE EXISTS (SELECT 学号 FROM 选课表 WHERE 选课表.学号=学生表.学号 AND 课程号=0
34、00000001) 例7.38 查询没有选修任何课程的学生的学号和姓名。 SELECT 学号,姓名 FROM 学生表 WHERE NOT EXISTS (SELECT * FROM 选课表 WHERE 选课表.学号=学生表.学号) 子查询可以多层嵌套；子查询的SELECT语句不能使用COMPUTE子句. 子查询的SELECT语句不能使用ORDER BY子句，ORDER BY子句只能对最终查询结果排序。,返回目录,7.6 实训六,1、实训目的 掌握对数据表的约束的使用操作，包括约束的创建、修改、删除和重命名。 2、实训要求 （1）正确理解数据表中约束的含义。 （2）正确理解T-SQL语句创建与管
35、理约束。 3、实训内容与步骤 （1）创建学生管理系统中的各个约束，用图形界面创建数据表的约束和修改删除约束； （2）使用T-SQL语句创建数据表的约束与管理数据表的约束。,小结,1、数据查询是数据库系统中最基本也是最重要的操作。本章通过实例详细讲述了利用SELECT 语句进行数据查询的各种方法，包括基本查询、条件查询、查询结果排序、查询结果保存、连接查询、合并查询、汇总查询和子查询等。 2、在基本查询中介绍了如何查询指定字段，如何设置字段别名，如何去除查询结果中的重复行，如何查询前面部分记录。 3、在条件查询中介绍了使用比较运算符、逻辑运算符、BETWEEN、IN、LIKE等关键字描述查询条件。 4、在连接查询中介绍了内连接、外连接和交叉连接。另外还介绍了自连接。 5、在汇总查询中介绍了简单汇总、分组汇总和汇总计算。 6、在子查询中介绍了不相关子查询和相关子查询。通过本章的学习，应熟练掌握各种查询方法，并在实际数据库应用程序设计中灵活使用。,返回目录,根据以上的教学说明，我对本课进行简要评价：本节课，我做到以案例驱动为主线，以展开案例几大要素为发散点，以重点知识点为核心，以培养学生面向对象编程能力为目的，把教、学、做融为一体，使学生初步具备了T-SQL语言建立数据表、管理数据表，对数据进行导入和导出的能力。,韩雪制作,本章结束 再见！,
