SQLServer数据库的查询和视图.ppt

《SQLServer数据库的查询和视图.ppt》由会员分享，可在线阅读，更多相关《SQLServer数据库的查询和视图.ppt（86页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第4章 数据库的查询和视图,4.1 数据库的查询,4.2 视 图,4.1 数据库的查询,下面介绍SELECT语句，它是T-SQL的核心。语法主体格式如下： SELECT /*指定查询结果输出列*/ INTO 新表 /*指定查询结果存入新表*/ FROM , . /*指定查询源：表或视图*/ WHERE /*指定查询条件*/ GROUP BY /*指定查询结果分组条件*/ HAVING /*指定查询结果分组统计条件*/ ORDER BY /*指定查询结果排序顺序*/,4.1.1 选择查询结果输出列,1选择所有列 使用“*”表示选择一个表或视图中的所有列。 【例4.1】 查询pxscj数据库中xs

2、b表的所有记录。 T-SQL命令如下： USE pxscj SELECT * FROM xsb,4.1.1 选择查询结果输出列,执行结果如图4.1所示。,4.1.1 选择查询结果输出列,2选择一个表中指定的列 可选择一个表中的部分列，各列名之间要以逗号分隔。 【例4.2】 查询xsb表中计算机专业学生的学号、姓名和总学分。 T-SQL命令如下： SELECT 学号, 姓名, 总学分 FROM xsb WHERE 专业 = 计算机,4.1.1 选择查询结果输出列,3定义列别名 当希望查询结果中的列使用自己选择的列标题时，可以AS更改列标题名，该列标题称为该列的别名。 【例4.3】 查询xsb表中

3、计算机系同学的学号、姓名和总学分，查询结果中各列的标题分别指定为number、name和mark。 T-SQL命令如下： SELECT 学号 AS number, 姓名 AS name, 总学分 AS mark FROM xsb WHERE 专业= 计算机 执行结果如图4.2所示。,4.1.1 选择查询结果输出列,也可以使用“列别名=表达式”更改列标题。例如： SELECT number = 学号, name = 姓名, mark = 总学分 FROM xsb WHERE 专业= 计算机 当自定义的列标题中含有空格时，必须使用引号将标题括起来。例如： SELECT Student number

4、 = 学号,姓名 AS Student name, mark = 总学分 FROM xsb WHERE 专业= 计算机,4.1.1 选择查询结果输出列,4替换查询结果中的数据 在对表进行查询时，有时希望对所查询的某些列得到的数据进行变换。 要替换查询结果中的数据，则可使用CASE表达式，格式为： CASE WHEN 条件1 THEN 表达式1 WHEN 条件2 THEN 表达式2 ELSE 表达式 END,4.1.1 选择查询结果输出列,【例4.4】 查询xsb表中计算机系各同学的学号、姓名和总学分，对其总学分按以下规则进行替换，列标题更改为“等级”。 若总学分为空值，则替换为“尚未选课”；

5、若总学分小于50，则替换为“不及格”； 若总学分在50与52之间，则替换为“合格”； 若总学分大于52，则替换为“优秀”。 T-SQL命令如下： SELECT 学号, 姓名, 等级= CASE WHEN 总学分 IS NULL THEN 尚未选课 WHEN 总学分 =50 and 总学分=52 THEN 合格 ELSE 优秀 END FROM xsb WHERE 专业= 计算机,4.1.1 选择查询结果输出列,执行结果如图4.3所示。,4.1.1 选择查询结果输出列,5计算列值 使用SELECT对列进行查询时，在结果中除了输出列值，也可以输出表达式值。格式为： SELECT 表达式 , 表达式

6、 【例4.5】 查询通信工程专业学生的年龄。 T-SQL命令如下，执行结果如图4.4所示。 SELECT 学号,姓名,出生时间, year(getdate()-year(出生时间) AS 年龄 FROM xsb WHERE 专业=通信工程 计算列值使用算术运算符：+（加）、（减）、*（乘）、/（除）和%（取余），其中，算术运算符（+、*、/）可以用于任何数字类型的列，包括int、smallint、tinyint、decimal、numeric、float、real、money和smallmoney；%可以用于上述除money和smallmoney以外的数字类型。,4.1.1 选择查询结果输出列

7、,图4.4 执行结果,4.1.1 选择查询结果输出列,6消除结果集中的重复行 对表只选择其某些列时，可能会出现重复行。可以使用DISTINCT关键字消除结果集中的重复行，格式为： SELECT DISTINCT | ALL 列名 , 列名 【例4.6】 对pxscj数据库的xsb表只选择专业，消除结果集中的重复行。 代码如下，执行结果如图4.5所示。 SELECT DISTINCT 专业FROM xsb 与DISTINCT相反，当使用关键字ALL（默认值）时，将保留结果集的所有行。,4.1.1 选择查询结果输出列,7限制结果集返回行数 如果查询结果集的行数非常多，那么可以使用TOP选项限制其返

8、回的行数。格式为： TOP 表达式 PERCENT WITH TIES 其中，“表达式”可以是指定数目或百分比数目的行。若带PERCENT关键字，则以表达式值作为返回结果集百分数。 【例4.7】 对pxscj数据库的xsb表选择姓名、专业和总学分，返回结果集的前6行。 T-SQL命令如下： SELECT TOP 6 姓名,专业,总学分 FROM xsb,4.1.1 选择查询结果输出列,8聚合函数 聚合函数常常用于对一组值进行计算，然后返回单个值。通常与GROUP BY子句一起使用。如果一个SELECT语句中有一个GROUP BY子句，则这个聚合函数对所有列起作用；如果没有，则SELECT语句只

9、产生一行作为结果。SQL Server所提供的聚合函数列于表4.1中。,4.1.1 选择查询结果输出列,（1）SUM和AVG。 SUM和AVG分别用于求表达式中所有值项的总和与平均值，格式为： SUM /AVG ( ALL | DISTINCT 表达式 ) 其中，“表达式”可以是常量、列、函数或表达式，其数据类型只能是int、smallint、tinyint、bigint、decimal、numeric、float、real、money和smallmoney。 【例4.8】 求所有课程的总学分和选修101课程的学生的平均成绩。 T-SQL命令如下，执行结果如图4.6所示。 SELECT SUM

10、(学分) AS 总学分 FROM kcb SELECT AVG(成绩) AS 计算机基础平均成绩 FROM cjb WHERE 课程号 = 101,4.1.1 选择查询结果输出列,图4.6 执行结果,4.1.1 选择查询结果输出列,（2）MAX和MIN。 MAX和MIN分别用于求表达式中所有值项的最大值与最小值，语法格式为： MAX / MIN ( ALL | DISTINCT 表达式 ) 其中，“表达式”可以是常量、列、函数或表达式，其数据类型可以是数字、字符和时间日期类型。 【例4.9】 求选修101课程的学生的最高分和最低分。 T-SQL命令如下，执行结果如图4.7所示。 SELECT

11、MAX(成绩) AS 计算机基础最高分 , MIN(成绩) AS 计算机基础最低分 FROM cjb WHERE 课程号 = 101,4.1.1 选择查询结果输出列,（3）COUNT。 COUNT用于统计组中满足条件的行数或总行数，格式为： COUNT ( ALL | DISTINCT 表达式 | * ) 其中，“表达式”的数据类型是除text、image或ntext之外的任何类型。 【例4.10】 求学生的总数、专业个数和总学分在50分以上的人数。 T-SQL命令如下，执行结果如图4.8所示。 SELECT COUNT(*) AS 学生总数 , COUNT(DISTINCT 专业) AS 专

12、业个数 FROM xsb ; GO SELECT COUNT(总学分) AS 总学分50分人数 FROM xsb WHERE 总学分50 ; GO,4.1.2 选择查询条件：WHERE子句,1表达式比较 比较运算符用于比较两个表达式值，共有9个，分别是 =（等于）、（大于）、=（大于等于）、（不等于）、!=（不等于）、!（不大于）。比较运算的格式为： 表达式1 比较运算符 表达式2 其中，“表达式”是除text、ntext和image以外类型的表达式。 【例4.11】 查询xsb表中通信工程专业总学分大于等于42的同学的情况。 T-SQL命令如下： SELECT * FROM xsb WHER

13、E 专业= 通信工程 AND 总学分 = 42,4.1.2 选择查询条件：WHERE子句,2模式匹配 LIKE谓词用于指出字符串是否与指定的字符串相匹配，返回逻辑值TRUE或FALSE。格式为： 表达式 NOT LIKE 模式串 ESCAPE 转义符 说明： （1）表达式：一般为字符串表达式，在查询语句中可以是列名。 （2）模式串：可以使用通配符，表4.2列出了LIKE谓词可以使用的通配符及其说明。 （3）转义符：应为有效的SQL Server字符，没有默认值，且必须为单个字符。当模式串中含有与通配符相同的字符时，应通过该字符前的转义符指明其为模式串中的一个匹配字符。使用ESCAPE可指定转义

14、符。 （4）NOT LIKE：使用NOT LIKE与LIKE的作用相反。,4.1.2 选择查询条件：WHERE子句,【例4.12】 查询xsb表中姓“王”且单名的学生情况。 T-SQL命令如下，执行结果如图4.9所示。 SELECT * FROM xsb WHERE 姓名 LIKE 王_ ,4.1.2 选择查询条件：WHERE子句,【例4.13】 查询xsb表中学号倒数第5个数字为9，且倒数第1个数在15之间的学生学号、姓名及专业。 T-SQL命令如下，执行结果如图4.10所示。 SELECT 学号,姓名,专业 FROM xsb WHERE 学号 LIKE %9_ _ _1-5,4.1.2 选

15、择查询条件：WHERE子句,【例4.13】 查询xsb表姓名中倒数第2个字为玉的学生学号、姓名及专业。 T-SQL命令如下，执行结果如图4.10所示。 SELECT 学号,姓名,专业 FROM xsb WHERE 学号 LIKE %玉_,4.1.2 选择查询条件：WHERE子句,3范围比较 （1）当要查询的条件是某个值的范围时，可以使用BETWEEN关键字。BETWEEN关键字指出查询范围，格式为： 表达式 NOT BETWEEN 表达式1 AND 表达式2 当不使用NOT时，若“表达式”的值在“表达式1”与“表达式2”之间（包括这两个值），则返回TRUE，否则返回FALSE；当使用NOT时，

16、返回值刚好相反。 【例4.14】 查询xsb表中不在1995年出生的学生情况。 T-SQL命令如下，执行结果如图4.11所示。 SELECT 学号, 姓名, 专业, 出生时间 FROM xsb WHERE 出生时间 NOT BETWEEN 1995-1-1 and 1995-12-31 也可使用下列命令： SELECT 学号, 姓名, 专业, 出生时间 FROM xsb WHERE WHERE year(出生时间)!=1995,4.1.2 选择查询条件：WHERE子句,图4.11 执行结果,4.1.2 选择查询条件：WHERE子句,（2）使用IN关键字可以指定一个值表，值表中列出所有可能的值，

17、当与值表中的任一个匹配时，即返回TRUE，否则返回FALSE。格式为： 表达式 IN ( 表达式 , ) 【例4.15】 查询xsb表中专业为“软件工程”或“通信工程”学生情况。 T-SQL命令如下： SELECT * FROM xsb WHERE 专业 IN (软件工程, 通信工程) 下列T-SQL命令等价： SELECT * FROM xsb WHERE 专业= 计算机 OR 专业= 通信工程,4.1.2 选择查询条件：WHERE子句,4空值比较 当需要判定一个表达式的值是否为空值时，使用IS NULL关键字，格式为： 表达式 IS NOT NULL 当不使用NOT时，若表达式的值为空值，

18、则返回TRUE，否则返回FALSE；当使用NOT时，结果刚好相反。 【例4.16】 查询总学分尚不定的学生情况。 SELECT * FROM xsb WHERE 备注 IS NULL,4.1.2 选择查询条件：WHERE子句,5子查询 1）IN子查询 IN子查询用于进行一个给定值是否在子查询结果集中的判断，格式为： 表达式 NOT IN ( 子查询 ) 当表达式与子查询的结果表中的某个值相等时，IN谓词返回TRUE，否则返回FALSE；若使用了NOT，则返回的值刚好相反。 【例4.17】 查询选修了课程号为206的课程的学生情况。 T-SQL命令如下： SELECT * FROM xsb WH

19、ERE 学号 IN (SELECT 学号 FROM cjb WHERE 课程号 = 206) 本例中，先执行子查询： SELECT 学号FROM cjb WHERE 课程名 = 206,4.1.2 选择查询条件：WHERE子句,【例4.18】 查询未选修离散数学的学生学号和姓名。 T-SQL命令如下： SELECT 学号, 姓名 FROM xsb WHERE 学号 NOT IN ( SELECT 学号 FROM cjb WHERE 课程号 IN(SELECT 课程号 FROM kcb WHERE 课程名 = 离散数学 ) ),4.1.2 选择查询条件：WHERE子句,2）比较子查询 这种子查询

20、可以认为是IN子查询的扩展，它使表达式的值与子查询的结果进行比较运算，格式为： 表达式 比较运算符 ALL | SOME | ANY ( 子查询 ) 其中，ALL、SOME和ANY说明对比较运算的限制。 【例4.19】 查询选修了离散数学的学生学号和姓名。 T-SQL命令如下： SELECT 学号 FROM cjb WHERE 课程号 = ( SELECT 课程号 FROM kcb WHERE 课程名 =离散数学 );,4.1.2 选择查询条件：WHERE子句,【例4.20】 查询比所有计算机系的学生年龄都大的学生。 T-SQL命令如下： SELECT * FROM xsb WHERE 出生时

21、间 ALL ( SELECT 出生时间 FROM xsb WHERE 专业= 计算机 ) 【例4.21】 查询206课程号成绩不低于101课程号最低成绩的学生学号。 T-SQL命令如下： SELECT 学号 FROM cjb WHERE 课程号 = 206 AND 成绩 ! ANY ( SELECT 成绩 FROM cjb WHERE 课程号 = 101 ),4.1.2 选择查询条件：WHERE子句,3）EXISTS子查询 EXISTS谓词用于测试子查询的结果是否为空表，若子查询的结果集不为空，则EXISTS返回TRUE，否则返回FALSE。EXISTS还可与NOT结合使用，即NOT EXIS

22、TS，其返回值与EXISTS刚好相反。格式为： NOT EXISTS ( 子查询 ) 【例4.22】 查询选修206课程的学生姓名。 T-SQL命令如下： SELECT 姓名 FROM xsb WHERE EXISTS ( SELECT * FROM cjb WHERE 学号 = xsb.学号 AND 课程号 = 206 ),4.1.2 选择查询条件：WHERE子句,【例4.23】 查询选修了全部课程的同学的姓名。 SELECT 姓名 FROM xsb WHERE NOT EXISTS ( SELECT * FROM kcb WHERE NOT EXISTS ( SELECT * FROM c

23、jb WHERE 学号=xsb.学号 AND 课程号=kcb.课程号 ) ),4.1.2 选择查询条件：WHERE子句,另外，子查询还可以用在SELECT语句的其他子句中，如FROM子句。 SELECT关键字后面也可以定义子查询。 【例4.24】 从xsb表中查询所有女学生的姓名、学号及其与“191301”号学生的年龄差距。 T-SQL命令如下，执行结果如图4.12所示。 SELECT 学号, 姓名, YEAR(出生时间)- YEAR( ( SELECT 出生时间 FROM xsb WHERE 学号=191301 ) ) AS 年龄差距 FROM xsb WHERE 性别=0,4.1.3 指定

24、查询对象：FROM子句,1表或视图名 SELECT语句可查询的表或视图，表和视图可以是一个或多个，有关视图的内容将在下一节中介绍。 【例4.25】 查询“191301”号学生的计算机基础课的成绩。 T-SQL命令如下： SELECT 成绩 FROM cjb, kcb WHERE cjb.课程号=kcb.课程号 AND 学号=191301 AND 课程名=计算机基础,4.1.3 指定查询对象：FROM子句,【例4.26】 查询选修了与学号为191302的同学所选修的全部课程的同学的学号。 分析：本例即要查找这样的学号y，对所有的课程号x，若191302号同学选修了该课，那么y也选修了该课。 T-

25、SQL命令如下： SELECT DISTINCT 学号 FROM cjb AS CJ1 WHERE NOT EXISTS ( SELECT * FROM cjb AS CJ2 WHERE CJ2.学号 = 191302 AND NOT EXISTS ( SELECT * FROM cjb AS CJ3 WHERE CJ3. 学号= CJ1.学号 AND CJ3.课程号 = CJ2.课程号 ) ),4.1.3 指定查询对象：FROM子句,2导出表 导出表表示由子查询中SELECT语句的执行而返回的表，但必须使用AS关键字为子查询产生的中间表定义一个别名。 【例4.27】 从xsb表中查询总学分大

26、于50的男同学的姓名和学号。 T-SQL命令如下： SELECT 姓名, 学号, 总学分 FROM ( SELECT 姓名, 学号, 性别, 总学分 FROM xsb WHERE 总学分=50 ) AS student WHERE 性别=1 执行结果如图4.13所示。,4.1.3 指定查询对象：FROM子句,【例4.28】 在xsb表中查询在1995年1月1日以前出生的学生的姓名和专业。 T-SQL命令如下，执行结果如图4.14所示。 SELECT student.name, student.speciality FROM ( SELECT * FROM xsb WHERE 出生时间19950

27、101 ) AS student( num, name, sex, birthday, speciality,score, mem ),4.1.4 连接,1连接谓词 在SELECT语句的WHERE子句中使用比较运算符给出连接条件对表进行连接，称为连接谓词表示形式。 【例4.29】 查询pxscj数据库每个学生的情况以及选修的课程情况。 USE pxscj GO SELECT xsb.* , cjb.* FROM xsb , cjb WHERE xsb.学号 = cjb.学号,4.1.4 连接,（1）自然连接。 它在目标列中去除相同的字段名。 【例4.30】 自然连接查询。 T-SQL命令如下：

28、 SELECT xsb.* , cjb.课程号, cjb.成绩 FROM xsb , cjb WHERE xsb.学号= cjb.学号 本例所得的结果表包含xsb的使用字段、课程号、成绩。若选择的字段名在各个表中是唯一的，则可以省略字段名前的表名。 如本例的SELECT语句也可写为： SELECT xsb.* , 课程号, 成绩 FROM xsb , cjb WHERE xsb.学号 = cjb.学号,4.1.4 连接,【例4.31】 查询选修了206课程且成绩在80分以上的学生姓名及成绩。 T-SQL命令如下，执行结果如图4.15所示。 SELECT 姓名, 成绩 FROM xsb , cj

29、b WHERE xsb.学号 = cjb.学号 AND 课程号 = 206 AND 成绩 = 80,4.1.4 连接,（2）多表连接。 有时，用户所需要的字段来自两个以上的表，那么就要对两个以上的表进行连接，称之为多表连接。 【例4.32】 查询选修了“计算机基础”课程且成绩在80分以上的学生学号、姓名、课程名及成绩。 T-SQL命令如下，执行结果如图4.16所示。 SELECT xsb.学号, 姓名, 课程名, 成绩 FROM xsb , kcb , cjb WHERE xsb.学号 = cjb.学号 AND kcb.课程号 = cjb.课程号 AND 课程名 = 计算机基础 AND 成绩

30、= 80,4.1.4 连接,2以JOIN关键字指定的连接 T-SQL扩展了以JOIN关键字指定连接的表示方式，使表的连接运算能力有所增强。FROM子句的表示将多个表连接起来。 格式如下： := ON | CROSS JOIN | 左表源 CROSS | OUTER APPLY 右表源 | ( ) ,4.1.4 连接,说明： （1）：准备要连接的表。 （2）：表示连接类型。 格式为： := INNER | LEFT | RIGHT | FULL OUTER JOIN 其中，INNER表示内连接，OUTER表示外连接。 （3）ON：用于指定连接条件，为连接的条件。 （4）APPLY运算符：使用AP

31、PLY运算符可以为实现查询操作的外部表表达式返回的每个行调用表值函数。 （5）CROSS JOIN：表示交叉连接。,4.1.4 连接,因此，以JOIN关键字指定的连接有三种类型：内连接、外连接、交叉连接。 内连接。 指定了INNER关键字的连接是内连接，内连接按照ON所指定的连接条件合并两个表，返回满足条件的行。 【例4.33】 查询每个学生的情况以及选修的课程情况。 T-SQL命令如下： SELECT * FROM xsb INNER JOIN cjb ON xsb.学号 =cjb.学号 执行的结果将包含xsb表和cjb表的所有字段（不去除重复字段学号）。 【例4.34】查询选修了206课程

32、且成绩在80分以上的学生姓名及成绩。 T-SQL命令如下： SELECT 姓名, 成绩 FROM xsb JOIN cjb ON xsb.学号 = cjb.学号 WHERE 课程号 = 206 AND 成绩=80,4.1.4 连接,【例4.35】 查询选修了“计算机基础”课程且成绩在80分以上的学生学号、姓名、课程名及成绩。 T-SQL命令如下： SELECT xsb.学号, 姓名, 课程名, 成绩 FROM xsb JOIN cjb JOIN kcb ON cjb.课程号 = kcb.课程号 ON xsb.学号 = cjb.学号 WHERE 课程名= 计算机基础 AND 成绩=80 作为一种

33、特例，可以将一个表与它自身进行连接，称为自连接。 【例4.36】 查询不同课程成绩相同的学生的学号、课程号和成绩。 T-SQL命令如下： SELECT a.学号, a.课程号, b.课程号, a.成绩 FROM cjb a JOIN cjb b ON a.成绩=b.成绩 AND a.学号=b.学号 AND a.课程号!=b.课程号 执行结果如图4.17所示。,4.1.4 连接, 外连接。 指定了OUTER关键字的为外连接，外连接的结果表不但包含满足连接条件的行，还包括相应表中的所有行。外连接包括以下三种： LEFT OUTER JOIN（左外连接）：结果表中除了包括满足连接条件的行外，还包括左

34、表的所有行。 RIGHT OUTER JOIN（右外连接）：结果表中除了包括满足连接条件的行外，还包括右表的所有行。 FULL OUTER JOIN（完全外连接）：结果表中除了包括满足连接条件的行外，还包括两个表的所有行。,4.1.4 连接,【例4.37】 查询所有学生情况，以及他们选修的课程号。 T-SQL命令如下，执行结果如图4.18所示。 SELECT xsb.* , 课程号 FROM xsb LEFT OUTER JOIN cjb ON xsb.学号 = cjb.学号,4.1.4 连接,【例4.38】 查询被选修了的课程的选修情况和所有开设的课程名。 SELECT cjb.* , 课程

35、名 FROM cjb RIGHT JOIN kcb ON cjb.课程号= kcb.课程号,4.1.4 连接, 交叉连接。 交叉连接实际上是将两个表进行笛卡儿积运算，结果表是由第一个表的每一行与第二个表的每一行拼接后形成的表，因此结果表的行数等于两个表的行数之积。 【例4.39】 列出学生所有可能的选课情况。 T-SQL命令如下，执行结果如图4.19所示。 SELECT 学号, 姓名, 课程号, 课程名 FROM xsb CROSS JOIN kcb,4.1.5 指定查询结果分组方法：GROUP BY子句,这里介绍ISO标准的GROUP BY子句。 语法格式如下： GROUP BY | ROL

36、LUP ( ) | CUBE () | GROUPING SETS ( ) ,4.1.5 指定查询结果分组方法：GROUP BY子句,【例4.40】 在pxscj数据库xsb表中分别用GROUP BY ROLLUP、GROUP BY CUBE和GROUP BY GROUPING SETS对（专业,性别）产生一个结果集。 （1）采用GROUP BY ROLLUP。 SELECT 专业, 性别 , COUNT(*) AS 人数 FROM xsb GROUP BY ROLLUP(专业,性别) （2）采用GROUP BY CUBE。 SELECT 专业, 性别, COUNT(*) AS 人数 FROM

37、 xsb GROUP BY CUBE(专业,性别),4.1.5 指定查询结果分组方法：GROUP BY子句,（3）采用GROUP BY GROUPING SETS。 执行结果如图4.20所示。 SELECT 专业, 性别, COUNT(*) AS 人数 FROM xsb GROUP BY GROUPING SETS(专业,性别),(a) (b) (c) 图4.20 执行结果,4.1.6 指定查询结果分组后筛选条件：HAVING子句,使用GROUP BY子句和聚合函数对数据进行分组后，还可以使用HAVING子句对分组数据做进一步的筛选。格式为： HAVING 其中，与WHERE子句的查询条件类似

38、，不过HAVING子句中可以使用聚合函数，而WHERE子句中不可以。 【例4.41】 查询平均成绩在85分以上的学生的学号和平均成绩。 T-SQL命令如下，执行结果如图4.21所示。 SELECT 学号, AVG(成绩) AS 平均成绩 FROM cjb GROUP BY 学号 HAVING AVG(成绩) =85,4.1.6 指定查询结果分组后筛选条件：HAVING子句,【例4.42】 查询选修课程超过2门且成绩都在80分以上的学生的学号。 T-SQL命令如下： SELECT 学号 FROM cjb WHERE 成绩 = 80 GROUP BY 学号 HAVING COUNT(*) 2,4.

39、1.6 指定查询结果分组后筛选条件：HAVING子句,【例4.43】 查询通信工程专业平均成绩在85分以上的学生的学号和平均成绩。 T-SQL命令如下，执行结果如图4.22所示。 SELECT 学号, AVG(成绩) AS 平均成绩 FROM cjb WHERE 学号 IN ( SELECT 学号 FROM xsb WHERE 专业 = 通信工程 ) GROUP BY 学号 HAVING AVG(成绩) =85,4.1.7 指定查询结果排序顺序：ORDER BY子句,在应用中经常要对查询的结果排序输出，如将学生成绩由高到低排序。在SELECT语句中，使用ORDER BY子句对查询结果进行排序。

40、格式为： ORDER BY 排序表达式 COLLATE 排序名 ASC | DESC ,4.1.7 指定查询结果排序顺序：ORDER BY子句,【例4.44】 将通信工程专业的学生按出生时间先后顺序排序。 T-SQL命令如下，执行结果如图4.23所示。 SELECT * FROM xsb WHERE 专业= 通信工程 ORDER BY 出生时间,4.1.7 指定查询结果排序顺序：ORDER BY子句,【例4.45】 将计算机专业学生的“计算机基础”课程成绩按降序排列。 T-SQL命令如下，执行结果如图4.24所示。 SELECT 姓名, 课程名, 成绩 FROM xsb, kcb, cjb W

41、HERE xsb.学号 = cjb.学号 AND cjb.课程号 = kcb.课程号 AND 课程名= 计算机基础 AND 专业= 计算机,4.1.8 SELECT语句的其他语法,1INTO 使用INTO子句可以将SELECT查询所得的结果保存到一个新建的表中。INTO子句的格式为： INTO 新表 其中，“新表”是要创建的新表名。 【例4.46】 由xsb表创建“计算机学生”表，包括学号和姓名。 T-SQL命令如下： SELECT 学号, 姓名 INTO 计算机学生 FROM xsb WHERE 专业= 计算机,4.1.8 SELECT语句的其他语法,同样再创建一个“通信工程学生”表。完成后

42、，刷新表后的对象资源管理器如图4.25所示。,4.1.8 SELECT语句的其他语法,2UNION 使用UNION子句可以将两个或多个SELECT查询的结果合并成一个结果集，其格式为： | ( ) UNION ALL | ( ) UNION ALL | ( ) 其中，和都是SELECT查询语句。 使用UNION组合两个查询的结果集的基本规则是： （1）所有查询中的列数和列的顺序必须相同。 （2）数据类型必须兼容。,4.1.8 SELECT语句的其他语法,【例4.47】 在“计算机学生”表和“通信工程学生”表中查询学号为191301和学号为221301的两位同学的姓名。 T-SQL命令如下： S

43、ELECT * FROM 计算机学生 WHERE 学号= 191301 UNION ALL SELECT * FROM 通信工程学生 WHERE 学号= 221301 执行结果如图4.26所示。,4.1.8 SELECT语句的其他语法,3EXCEPT和INTERSECT EXCEPT和INTERSECT用于比较两个查询的结果，返回非重复值。语法格式如下： | ( ) EXCEPT | INTERSECT | ( ) 其中，和都是SELECT查询语句。 【例4.48】 查询计算机专业女学生的信息。 T-SQL命令如下： SELECT * FROM xsb WHERE 专业= 计算机 EXCEPT

44、 SELECT * FROM xsb WHERE 性别=1 执行结果如图4.27所示。,4.1.8 SELECT语句的其他语法,【例4.49】 查询总学分大于42的男学生信息。 T-SQL命令如下： SELECT * FROM xsb WHERE 总学分42 INTERSECT SELECT * FROM xsb WHERE 性别=1,4.1.8 SELECT语句的其他语法,4CTE 在SELECT语句的最前面可以使用一条WITH子句来指定临时结果集，语法格式如下： WITH , . SELECT . 其中： := 表达式名 ( 列名 , . ) AS ( CTE查询定义 ),4.1.8 SE

45、LECT语句的其他语法,【例4.50】 使用CTE从cjb表中查询选了101课程的学生学号、成绩，并定义新的列名为number、point。再使用SELECT语句从CTE和xsb中查询姓名为“王林”的学生学号和成绩情况。 T-SQL命令如下，执行结果如图4.28所示。 USE pxscj GO WITH cte_stu(number,point) AS (SELECT 学号,成绩 FROM cjb WHERE 课程号=101) SELECT number, point FROM cte_stu, xsb WHERE xsb.姓名=王林 AND xsb.学号=cte_stu.number,4.2

46、 视 图,4.2.1 视图概念 视图一经定义以后，就可以像表一样被查询、修改、删除和更新。 使用视图有下列优点： （1）为用户集中数据，简化用户的数据查询和处理。有时，用户所需要的数据分散在多个表中，定义视图可将它们集中在一起，从而方便用户进行数据查询和处理。 （2）屏蔽数据库的复杂性。用户不必了解复杂的数据库中的表结构，并且数据库表的更改也不影响用户对数据库的使用。 （3）简化用户权限的管理。只需授予用户使用视图的权限，而不必指定用户只能使用表的特定列，也增加了安全性。 （4）便于数据共享。各用户不必都定义和存储自己所需的数据，而可共享数据库的数据，这样，同样的数据只需存储一次。 （5）可以

47、重新组织数据以便输出到其他应用程序中。,4.2.2 创建视图,1通过界面创建视图 下面以在一个数据库中创建视图为例说明在“SSMS”中创建视图的过程。 【例4.51】 在pxscj数据库学生表(xsb)中创建名为cxs视图，视图中仅仅包含计算机专业学生。 1）创建名cxs视图 步骤如下： （1）在“对象资源管理器”中展开“数据库”“pxscj”，选择其中的“视图”项，右击鼠标，在弹出的快捷菜单上选择“新建视图”菜单项。 （2）在随后出现的“添加表”对话框中，添加视图所需要关联的对象。这里在“表”选项卡中选择“xsb”表，单击“添加”按钮，单击“关闭”按钮。,4.2.2 创建视图,（3）在窗口下

48、部“输出”栏中去除创建的视图中不所需的字段，在“排序类型”栏中指定列的排序方式，在“筛选器”栏中指定创建视图的规则。本例在“专业”字段的“筛选器”栏中填写“计算机”，如图4.29所示。,（4）完成后，单击面板上的“保存”按钮，在“保存视图”对话框中输入视图名“cxs”，并单击“确定”按钮，便完成了视图的创建。,4.2.2 创建视图,2）显示cxs视图 刷新pxscj数据库下的视图，展开“数据库”“pxscj”“视图”，选择“dbo.cxs”，右击鼠标，在弹出的快捷菜单中选择“设计”菜单项，可以查看并修改视图结构，选择“编辑前200行”菜单项，将可查看视图数据，如图4.30所示。,4.2.2 创

49、建视图,3）视图的好处 创建视图可以向最终用户隐藏复杂的表连接，简化了用户的SQL程序设计。通过在创建视图时指定限制条件和指定列来限制用户对基本表的访问。例如，若限定某用户只能查询视图cxs，实际上就是限制了它只能访问xsb表的专业字段值为“计算机”的行。,4.2.2 创建视图,2通过命令创建视图 T-SQL中用于创建视图的语句是CREATE VIEW语句，其格式为： CREATE VIEW 视图名 WITH , . AS SELECT 语句 ; WITH CHECK OPTION 1）语句主体 主体结构说明如下。 （1）架构名：数据库架构名。 （2）列名：视图中包含的列，可以有多个列名。若使用与源表或视图相同的列名，则不必给出列名。 （3）WITH ：指出视图的属性。 （4）SELECT语句：用来创建视图的语句，源表可以是基本表，也可以是视图。 （5）WITH CHECK OPTION：指出在视图上所进行的修改都要符合SELECT语