第4章 数据库的查询和视图.ppt

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

1、第第4章章 数据库的查询和视图数据库的查询和视图4.1 数据库的查询数据库的查询4.2 视图视图4.3 游标游标4.1 数据库的查询数据库的查询n nSELECTSELECT语法：语法：SELECT SELECT INTO INTO FROM FROM WHERE WHERE GROUP BY GROUP BY HAVING HAVING ORDER BY ORDER BY ASC|DESC ASC|DESC 4.1.1 选择列选择列 通过SELECT语句的项组成结果表的列。:=SELECT ALL|DISTINCT TOP n PERCENT WITH TIES */*选择当前表或视图的所有

2、列*/|table_name|view_name|table_alias .*/*选择指定的表或视图的所有列*/|colume_name|expression|IDENTITYCOL|ROWGUIDCOL AS column_alias /*选择指定的列*/|column_alias=expression /*选择指定列并更改列标题*/,n 本小节讨论上述格式中的常用表示方法。1.选择一个表中指定的列选择一个表中指定的列 一般情况下，希望包含在结果表中的列表清单在SELECT关键字之后，就像前面用到的那样，当在SELECT关键字后面指定*时，就表示全部列都包含在结果表中。不指定*，可以列出一个

3、表中的某些列，各列名之间要以逗号分隔。4.1.1 选择列选择列 【例例4.1】查询XSCJ数据库的XS表中各个同学的姓名、专业名和总学分。USE XSCJ SELECT 姓名,专业名,总学分 FROM XS 执行结果如图4.1所示。图图4.1 在在XS表中选择列表中选择列4.1.1 选择列选择列【例例4.2】查询XS表中计算机专业同学的学号、姓名和总学分。查询XS表中的所有列。SELECT 学号，姓名，总学分 FROM XSWHERE 专业名=计算机GOSELECT *FROM XSGO 当在SELECT语句指定列的位置上使用*号时，表示选择表的所有列。执行后将列出XS表中的所有数据，如图4.

4、2所示：4.1.1 选择列选择列图图4.2 XS表中的所有数据记录表中的所有数据记录4.1.1 选择列选择列2.修改查询结果中的列标题修改查询结果中的列标题 当希望查询结果中的某些列或所有列显示时使用自己选择的列标题时，可以在列名之后使用AS子句来更改查询结果的列标题名。其中column_alias是指定的列标题。【例例4.3】查询XS表中计算机系同学的学号、姓名和总学分，结果中各列的标题 分别指定为number、name和mark。SELECT 学号 AS number,姓名 AS name,总学分 AS mark FROM XS WHERE 专业名=计算机 4.1.1 选择列选择列该语句的

5、执行结果如图4.3所示。更改查询结果中的列标题也可以使用column_alias=expression的形式。例如：SELECT number=学号,name=姓名,mark=总学分FROM XS WHERE 专业名=计算机 该语句的执行结果与上例的结果完全相同。图图4.3 更改查询结果的列标题更改查询结果的列标题4.1.1 选择列选择列 注意，当自定义的列标题中含有空格时，必须使用引号将标题括起来。例如：SELECT Student number=学号，姓名 AS Student name，mark=总学分 FROM XSWHERE 专业名=计算机 4.1.1 选择列选择列 3.替换查询结果

6、中的数据替换查询结果中的数据 在对表进行查询时，有时对所查询的某些列希望得到的是一种概念而不是具体的数据。例如查询XS表的总学分，所希望知道的是学习的总体情况，这时，就可以用等级来替换总学分的具体数字。4.1.1 选择列选择列 3.替换查询结果中的数据替换查询结果中的数据 要替换查询结果中的数据，则要使用查询中的CASE表达式，格式为：CASE WHEN 条件1 THEN 表达式1 WHEN 条件2 THEN 表达式2 ELSE 表达式 END SQL Server按照顺序计算每一个条件，如果找到条件为真的语句，SQL Server就执行TEHN关键字后的表达式；否则执行可选的ELSE关键字后

7、的语句。若没有为真的条件，也没有关键字，则CASE表达式返回值为空。4.1.1 选择列选择列 【例例4.4】查询XS表中计算机系各同学的学号、姓名和总学分，对其总学分按以下规则进行替换：若总学分为空值，替换为“尚未选课”；若总学分小于50，替换为“不及格”；若总学分在50与52之间，替换为“合格”；若总学分大于52，替换为“优秀”。列标题更改为“等级”。SELECT 学号,姓名,等级=CASE WHEN 总学分 IS NULL THEN 尚未选课 WHEN 总学分=50 and 总学分=52 THEN 合格 ELSE 优秀 ENDFROM XSWHERE 专业名=计算机4.1.1 选择列选择列

8、该语句的执行结果如图4.4所示。图图4.4 替换查询结果中的数据替换查询结果中的数据4.1.1 选择列选择列4.计算列值计算列值 使用SELECT对列进行查询时，在结果中可以输出对列值计算后的值，即SELECT子句可使用表达式作为结果，格式为：SELECT expression ,expression 4.1.1 选择列选择列【例例4.5】按120分计算成绩显示。SELECT 学号,课程号,成绩120=成绩*1.20 FROM XS_KC 该语句的执行结果如图4.5所示。图图4.5 计算成绩显示计算成绩显示4.1.1 选择列选择列计算列值使用算术运算符：l +（加）l （减）l *（乘）l /

9、（除）l%（取余）其中5种算术运算符（+、*、/）可以用于任何数字类型的列，包括：int、smallint、tinyint、decimal、numeric、float、real、money和smallmoney；%可以用于上述除money和smallmoney以外的数字类型。4.1.1 选择列选择列5.消除结果集中的重复行消除结果集中的重复行 对表只选择其某些列时，可能会出现重复行。例如，若对XSCJ数据库的XS表只选择专业名和总学分，则出现多行重复的情况。可以使用DISTINCT关键字消除结果集中的重复行，其格式是：SELECT DISTINCT column_name ,column_na

10、me 关键字DISTINCT的含义是对结果集中的重复行只选择一个，保证行的唯一性。4.1.1 选择列选择列5.消除结果集中的重复行消除结果集中的重复行 【例例4.6】对XSCJ数据库的XS表只选择专业名和总学分，消除结果集中的重复行。SELECT DISTINCT 专业名,总学分 FROM XS该语句的执行结果如图4.6所示。图图4.6消除重复行消除重复行4.1.1 选择列选择列 与DISTINCT相反，当使用关键字ALL时，将保留结果集的所有行。当SELECT语句中缺省ALL与DISTINCT时，默认值为ALL。4.1.1 选择列选择列6.限制结果集返回行数限制结果集返回行数 如果SELEC

11、T语句返回的结果集的行数非常多，可以使用TOP选项限制其返回的行数。TOP选项的基本格式为：TOP n PERCENT 其中n是一个正整数，表示返回查询结果集的前n行。若带PERCENT关键字，则表示返回结果集的前n%行。4.1.1 选择列选择列【例例4.7】对XSCJ数据库的XS表选择姓名、专业名和总学分，只返回结果集的前6行。SELECT TOP 6 姓名，专业名，总学分 FROM XS该语句执行后结果如图4.7所示。图图4.7限制结果集返回行数限制结果集返回行数 4.1.2 选择行选择行 在SQL Server中，选择行是通过在SELECT语句中WHERE子句指定选择的条件来实现的。这一

12、节将详细讨论WHERE子句中查询条件的构成。WHERE子句必须紧跟FROM子句之后，其基本格式为：4.1.2 选择行选择行 在SQL中，返回逻辑值（TRUE或FALSE）的运算符或关键字都可称为谓词。1.表达式比较表达式比较 比较运算符用于比较两个表达式值，共有9个，分别是：=（等于）、（小于）、（大于）、=（大于等于）、（不等于）、!=（不等于）、!（不大于）。比较运算的格式为：expression =|=|!=|!expression 其中expression是除text、ntext和image外类型的表达式。当两个表达式值均不为空值（NULL）时，比较运算返回逻辑值TRUE（真）或FAL

13、SE（假）；而当两个表达式值中有一个为空值或都为空值时，比较运算将返回UNKNOWN。4.1.2 选择行选择行1.表达式比较表达式比较 【例例4.8】查询XSCJ数据库XS表中通信工程专业总学分大于等于42的同学的情况。SELECT *FROM XS WHERE 专业名=通信工程 AND 总学分=424.1.2 选择行选择行2.模式匹配模式匹配 LIKE谓词用于指出一个字符串是否与指定的字符串相匹配，其运算对象可以是char、varchar、text、ntext、datetime和smalldatetime类型的数据，返回逻辑值TRUE或FALSE。LIKE谓词表达式的格式为：string_e

14、xpression NOT LIKE string_expression ESCAPE escape_character 4.1.2 选择行选择行【例例4.9】查询XSCJ数据库XS表中计算机系的学生情况。查询XSCJ数据库XS表中姓“王”且单名的学生情况。SELECT *FROM XS WHERE 专业名 LIKE 计算机 GO SELECT *FROM XS WHERE 姓名 LIKE 王_ GO4.1.2 选择行选择行 使用LIKE进行模式匹配时，常使用通配符，即可进行模糊查询。有关通配符的格式和含义请见附录中介绍TSQL语言的相关说明。执行结果如图4.8所示。图图4.8模糊查询模糊查询

15、 若要匹配的内容为通配符的字符（包括%、_、），可使用关键字ESCAPE。以告诉系统其后的每个字符均作为实际匹配的字符，而不再作为通配符。4.1.2 选择行选择行3.范围比较范围比较 用于范围比较的关键字有两个：BETWEEN和IN。当要查询的条件是某个值的范围时，可以使用BETWEEN关键字。BETWEEN关键字指出查询范围，格式为：expression NOT BETWEEN expression1 AND expression2 4.1.2 选择行选择行 当不使用NOT时，若表达式expression的值在表达式expression1与expression2之间（包括这两个值），则返回T

16、RUE，否则返回FALSE；使用NOT时，返回值刚好相反。注意注意：expression1的值不能大于expression2的值。【例例4.10】查询XSCJ数据库XS表中不在1989年出生的学生情况。SELECT *FROM XS WHERE 出生时间 NOT BETWEEN 198911 and 19891231 GO 4.1.2 选择行选择行 使用IN关键字可以指定一个值表，值表中列出所有可能的值，当与值表中的任一个匹配时，即返回TRUE，否则返回FALSE。使用IN关键字指定值表的格式为：expression IN(expression,n)【例例4.10】查询XSCJ数据库XS表中专

17、业名为“计算机”或“通信工程”或“无线电”的学生的情况。SELECT *FROM XS WHERE 专业名 IN (计算机,通信工程,无线电)GO4.1.2 选择行选择行例4.10中的语句与下列语句等价：SELECT*FROM XS WHERE 专业名=计算机 OR 专业名=通信工程 OR 专业名=无线电 说明说明：IN关键字最主要的作用是表达子查询。4.1.2 选择行选择行 4.空值比较空值比较 如果两个值当中有一个或者都为空，则对这两个值的比较结果是未知的。NULL谓词提供了一种方法，用来测试值为空或者非空。格式为：expression IS NOT NULL 当不使用NOT时，若表达式e

18、xpression的值为空值，返回TRUE，否则返回FALSE；当使用NOT时，结果刚好相反。【例例4.11】查询XSCJ数据库中总学分尚不定的学生情况。SELECT *FROM XS WHERE 总学分 IS NULL4.1.2 选择行选择行5.CONTAINS谓词谓词 若需要在表中搜索指定的单词、短语或近义词等，可以使用CONTAINS谓词。CONTAINS谓词用于在表中搜索指定的字符串，可以是精确匹配，也可以是模糊匹配，还可以是加权匹配。要使用CONTAINS谓词，必须在操作的表上事先建立全文索引。CONTAINS(column|*,)4.1.2 选择行选择行5.CONTAINS谓词谓词

19、 上式中，column表示在指定的列中搜索，*表示在所有列中搜索；为搜索的限定或说明。:=|()AND|AND NOT|OR n 说明：说明：simple_term：用于说明搜索的是单词还是短语，格式为：word|“phrase”，word为单词，即不含空格和标点符号的字符串；短语是含一个或多个空格的字符串。如果搜索的是短语，则需要用双引号将其括起来。4.1.2 选择行选择行 prefix_term：给出了要搜索的单词或短语必须匹配的前缀，格式为：“word*”|“phase*”其中word为单词，phase为短语，当查询的串是是短语时，需用双引号定界。generation_term：说明搜索

20、包含原词的派生词，所谓派生词是指原词的名词单、复数形式或动词的各种时态等。格式为：FORMSOF(INFLECTIONAL,n)4.1.2 选择行选择行 proximity_term：表示搜索包含NEAR或运算符左右两边的词或短语，格式为：|NEAR|n weight_term：指明本语句是加权搜索，即查询的数据与给定的权重进行加权匹配，格式为：ISABOUT(|WEUGHT(weight_value),n)其中weight_value是一个01之间的数，表示权重。4.1.2 选择行选择行6.FREETEXT谓词谓词 与CONTAINS谓词类似，FREETEXT谓词也用于在一个表中搜索单词或短

21、语，并要求表已建全文索引。格式为：FREETEXT(column|*,freetext_string)其中freetext_string是要搜索的字符串。FREETEXT的查询精度没有CONTAINS高，他并不要求对它们进行严格的模式匹配。FREETEXT对所查询的串也没有写法要求，因此FREETEXT也称为自由式查询。4.1.2 选择行选择行7.子查询子查询 子查询通常与IN、EXIST谓词及比较运算符结合使用。（1）IN子查询 IN子查询用于进行一个给定值是否在子查询结果集中的判断，格式为：expression NOT IN (subquery)其中subquery是子查询。当表达式exp

22、ression与子查询subquery的结果表中的某个值相等时，IN谓词返回TRUE，否则返回FALSE；若使用了NOT，则返回的值刚好相反。4.1.2 选择行选择行7.子查询子查询（1）IN子查询 【例例4.12】在XSCJ数据库中有描述课程情况的表KC和描述学生成绩表的表XS_KC，表的结构和样本数据见附录A。要查找选修了课程号为101的课程的学生的情况：SELECT *FROM XS WHERE 学号 IN (SELECT 学号 FROM XS_KC WHERE 课程号=101)4.1.2 选择行选择行 【例例4.13】查找未选修离散数学的学生的情况。SELECT *FROM XS WH

23、ERE 学号 NOT IN(SELECT 学号 FROM XS_KC WHERE 课程号 IN (SELECT 课程号 FROM KC WHERE 课程名=离散数学)4.1.2 选择行选择行执行结果如图4.9所示。图图4.9未选修离散数学的学生情况未选修离散数学的学生情况4.1.2 选择行选择行（2）比较子查询 这种子查询可以认为是IN子查询的扩展，它使表达式的值与子查询的结果进行比较运算，格式为：expression|=|!=|!ALL|SOME|ANY (subquery)其中expression为要进行比较的表达式，subquery是子查询。ALL、SOME和ANY说明对比较运算的限制。

24、ALL指定表达式要与子查询结果集中的每个值都进行比较，当表达式与每个值都满足比较的关系时，才返回TRUE，否则返回FALSE；SOME或ANY表示表达式只要与子查询结果集中的某个值满足比较的关系时，就返回 TRUE，否则返回FALSE。4.1.2 选择行选择行【例例4.14】查找比所有计算机系的学生年龄都大的学生。SELECT*FROM XSWHERE 出生时间 ALL (SELECT 出生时间 FROM XS WHERE 专业名=计算机 )执行结果如图4.10所示。图图4.10比计算机系年龄都大的学生比计算机系年龄都大的学生4.1.2 选择行选择行 【例例4.15】查找课程号206的成绩不低

25、于课程号101的最低成绩的学生的学号。SELECT 学号 FROM XS_KCWHERE 课程号=206 AND 成绩!ANY(SELECT 成绩 FROM XS_KC WHERE 课程号=101)4.1.2 选择行选择行（3）EXISTS子查询 EXISTS谓词用于测试子查询的结果是否为空表，若子查询的结果集不为空，则EXISTS返回TRUE，否则返回FALSE。EXISTS还可与NOT结合使用，即NOT EXISTS，其返回值与EXIST刚好相反。格式为：NOT EXISTS(subquery)4.1.2 选择行选择行【例例4.16】查找选修206号课程的学生姓名。SELECT 姓名 FR

26、OM XS WHERE EXISTS (SELECT *FROM XS_KC WHERE 学号=XS.学号 AND 课程号=206 )分析：分析：本例在子查询的条件中使用了限定形式的列名引用XS.学号，表示这里的学号列出自表XS。本例与前面的子查询例子不同点是，前面的例子中，内层查询只处理一次，得到一个结果集，再依次处理外层查询；而本例的内层查询要处理多次，因为内层查询与XS.学号有关，外层查询中XS表的不同行有不同的学号值。这类子查询称为相关子查询，因为子查询的条件依赖与外层查询中的某些值。4.1.2 选择行选择行【例例4.17】查找选修了全部课程的同学的姓名。SELECT 姓名 FROM

27、XS WHERE NOT EXISTS (SELECT *FROM KC WHERE NOT EXISTS (SELECT *FROM XS_KC WHERE 学号=XS.学号 AND 课程号=KC.课程号 )4.1.3 FROM子句子句 SELECT的查询对象由FROM子句指定，其格式为：FROM ,n 其中table_source指出了要查询的表或视图。:=table_name AS table_alias WITH(,)/*查询表，可以指定别名*/|view_name AS table_alias /*查询视图*/|rowset_function AS table_alias /*行集函

28、数*/|OPENXML /*XML文档*/|derived_table AS table_alias (column_alias,n)/*子查询*/|/*连接表*/1.table_name table_name指定SELECT语句要查询的表，从前面的例子中已经了解了其作用。可以使用AS选项为表指定别名，AS也可以省略，直接给出别名即可。别名主要用在相关子查询及连接查询中。4.1.3 FROM子句子句 【例例4.18】查找选修了学号为081102同学所选修的全部课程的同学的学号。分析：分析：本例即要查找这样的学号y，对所有的课程号x，若081102号同学选修了该课，那么y也选修了该课。SELEC

29、T DISTINCT 学号 FROM XS_KC AS CJ1 WHERE NOT EXISTS (SELECT *FROM XS_KC AS CJ2 WHERE CJ2.学号=081102 AND NOT EXISTS (SELECT *FROM XS_KC AS CJ3 WHERE CJ3.学号=CJ1.学号 AND CJ3.课程号=CJ2.课程号 )4.1.3 FROM子句子句 4.1.3 FROM子句子句2.view_name view_name为视图名，也可以为其指定别名。有关视图的介绍请见4.3节。3.rowset_function rowset_function是一个行集函数，行

30、集函数通常返回一个表或视图。主要的行集函数有CONTAINSTABLE、FREETEXTTABLE、OPENDATASOURCE、OPENQUERY、OPENROWSET和OPENXML。4.1.3 FROM子句子句3.rowset_function (1)CONTAINSTABLE函数 该函数与CONTAINS谓词相对应，用于对表进行全文查询，并且要求所查询的表上建立了全文索引。CONTAINSTABLE函数的语法格式为：CONTAINSTABLE(table,column|*,top_n_by_rank )其中table是进行全文查询的表，column指定被查询的列，*指对所有列进行查询。

31、contains_search_condition与CONTAINS谓词中的搜索条件完全相同。4.1.3 FROM子句子句3.rowset_function (1)CONTAINSTABLE函数 CONTAINSTABLE函数返回包含满足匹配条件的行，共有两列：KEY和RANK，其中KEY是包含被检索的字符串的行的主键值，RANK是一个等级值，指明行与字符串匹配的情况。可选项top_n_by_rank，说明只返回按RANK降序排列的结果表的前n行，其中n是正整数。CONTAINSTABLE函数常与INNER JOIN结合使用。4.1.3 FROM子句子句 (2)FREETEXTTABLE函数

32、FREETEXTTABLE函数与FREETEXT谓词相对应，它的使用与CONTAINSTABLE函数类似，格式为：FREETEXTTABLE(table,column|*,freetext_string ,top_n_by_rank )该函数使用与FREETEXT谓词相同的搜索条件。(3)OPENDATASOURCE函数 该函数使用户连接到服务器。格式为：OPENDATASOURCE(provider_name,init_string)其中provider_name是注册为用于访问数据源的 OLE DB 提供程序的 PROGID 的名称，init_string是连接字符串，这些字符串将要传递给

33、目标提供程序的 IDataInitialize 接口。4.1.3 FROM子句子句 (3)OPENDATASOURCE函数例如：SELECT *FROM OPENDATASOURCE （SQLOLEDB,Data Source=ServerName;User ID=MyUID;Password=MyPass ).Northwind.dbo.Categories 4.1.3 FROM子句子句 (4)OPENQUERY函数 该函数在给定的链接服务器（一个 OLE DB 数据源）上执行指定的直接传递查询，返回查询的结果集。格式为：OPENQUERY(linked_server,query)其中lin

34、ked_server为连接的服务器名，query是查询命令串。例如：EXEC sp_addlinkedserver OSvr,Oracle 7.3,MSDAORA,ORCLDB GO SELECT*FROM OPENQUERY(OSvr,SELECT title,id FROM al.book)GO 该例使用为Oracle提供的OLE DB对Oracle数据库创建了一个名为Osvr的连接服务器，然后对该其进行检索。4.1.3 FROM子句子句 (5)OPENROWSET函数 该函数与OPENQUERY函数功能相同。(6)OPENXML函数 OPENXML 通过XML文档提供行集视图。4.1.3

35、 FROM子句子句 4.derived_table derived_table是由SELECT查询语句的执行而返回的表，必须使用为其指定一个别名，也可以为列指定别名。【例例4.19】在XS表中查找1990年1月1日以前出生的学生的姓名和专业名，分别使用别名stu_name和speciality表示。SELECT m.stu_name,m.speciality FROM (SELECT*FROM XS WHERE 出生时间=80 执行结果如图4.13所示。图图4.13 查询结果查询结果 4.1.4 连接连接 【例例4.23】查找选修了“计算机基础”课程且成绩在80分以上的学生学号、姓名、课程名及

36、成绩。SELECT XS.学号,姓名,课程名,成绩 FROM XS,KC,XS_KC WHERE XS.学号=XS_KC.学号 AND KC.课程号=XS_KC.课程号 AND 课程名=计算机基础 AND 成绩=80 执行结果如图4.14所示。图图4.14 查询结果查询结果 4.1.4 连接连接 2.以JOIN关键字指定的连接 对两个表之间的简单内部连接（不匹配的行将从结果中删除），通过在FROM字句中列出多个，并在WHERE子句中加上连接条件即可完成这个功能。而对更复诊的外部连接（不匹配的行需要保留），或者多一个一个连接的查询中用到的表多于两个，那么在FORM子句中需要明确的连接句法。TSQ

37、L扩展了以JOIN关键字指定连接的表示方式，使表的连接运算能力有了增强。FROM子句的joined_table表示将多个表连接起来。4.1.4 连接连接 :=ON|CROSS JOIN|其中table_source为指定要在TransactSQL 语句中使用的表或视图（带或不带别名均可），join_type表示连接类型，ON用于指定连接条件。join_type的格式为：INNER|LEFT|RIGHT|FULL OUTER JOIN 其中INNER表示内连接，OUTER表示外连接，join_hint是连接提示。CROSS JOIN表示交叉连接。4.1.4 连接连接因此，以JOIN关键字指定的连

38、接有三种类型：(1)内连接 内连接按照ON所指定的连接条件合并两个表，返回满足条件的行。【例例4.24】查找XSCJ数据库每个学生的情况以及选修的课程情况。SELECT *FROM XS INNER JOIN XS_KC ON XS.学号=XS_KC.学号 结果表将包含XS表和XS_KC表的所有字段（不去除重复字段学号）。若要去除重复的学号字段，可将SELECT子句改为：SELECT XS.*,课程号,成绩 4.1.4 连接连接 内连接是系统默认的，可以省略INNER关键字。使用内连接后仍可使用WHERE子句指定条件。【例例4.25】用FROM的JOIN关键字表达下列查询：查找选修了206课程

39、且成绩在80分以上的学生姓名及成绩。SELECT 姓名,成绩 FROM XS JOIN XS_KC ON XS.学号=XS_KC.学号 WHERE 课程号=206 AND 成绩=80 4.1.4 连接连接内连接还可以用于多个表的连接。【例例4.26】用FROM的JOIN关键字表达下列查询：查找选修了“计算机基础”课程且成绩在80分以上的学生学号、姓名、课程名及成绩。SELECT XS.学号,姓名,课程名,成绩 FROM XS JOIN XS_KC JOIN KC ON XS_KC.课程号=KC.课程号 ON XS.学号=XS_KC.学号 WHERE 课程名=计算机基础 AND 成绩=80 4.

40、1.4 连接连接 作为一种特例，可以将一个表与它自身进行连接，称为自连接。若要在一个表中查找具有相同列值的行，则可以使用自连接。使用自连接时需为表指定两个别名，且对所有列的引用均要用别名限定。【例例4.27】查找不同课程成绩相同的学生的学号、课程号和成绩。SELECT a.学号,a.课程号,b.课程号,a.成绩 FROM XS_KC a JOIN XS_KC b ON a.成绩=b.成绩 AND a.学号=b.学号 AND a.课程号!=b.课程号执行结果如图4.15所示。图图4.15查找不同课程成绩相同的学生的学号、课程号和成绩查找不同课程成绩相同的学生的学号、课程号和成绩 4.1.4 连接

41、连接 (2)外连接 外连接的结果表不但包含满足连接条件的行，还包括相应表中的所有行。外连接包括三种：左外连接（LEFT OUTER JOIN）：结果表中除了包括满足连接条件的行外，还包括左表的所有行；右外连接（RIGHT OUTER JOIN）：结果表中除了包括满足连接条件的行外，还包括右表的所有行；完全外连接（FULL OUTER JOIN）：结果表中除了包括满足连接条件的行外，还包括两个表的所有行。其中的OUTER关键字均可省略。4.1.4 连接连接【例例4.28】查找所有学生情况，及他们选修的课程号，若学生未选修任何课，也要包括其情况。SELECT XS.*,课程号 FROM XS LE

42、FT OUTER JOIN XS_KC ON XS.学号=XS_KC.学号 本例执行时，若有学生未选任何课程，则结果表中相应行的课程号字段值为NULL。4.1.4 连接连接【例例4.29】查找被选修了的课程的选修情况和所有开设的课程名。SELECT XS_KC.*,课程名 FROM XS_KC RIGHT JOIN KC ON XS_KC.课程号=KC.课程号 本例执行时，若某课程未被选修，则结果表中相应行的学号、课程号和成绩字段值均为NULL。注意外连接只能对两个表进行。4.1.4 连接连接 (3)交叉连接 交叉连接实际上是将两个表进行笛卡尔积运算，结果表是由第一个表的每行与第二个表的每一行

43、拼接后形成的表，因此结果表的行数等于两个表行数之积。【例例4.30】列出学生所有可能的选课情况。SELECT 学号，姓名，课程号，课程名 FROM XS CROSS JOIN KC 注意注意:交叉连接不能有条件，且不能带WHERE子句。4.1.5 数据汇总数据汇总 对表数据进行检索时，经常需要对结果进行汇总或计算，例如在学生成绩数据库中求某门功课的总成绩、统计各分数段的人数等。本小节将讨论SELECT语句中用于数据统计的子句及函数。1.聚合函数聚合函数 聚合函数用于计算表中的数据，返回单个计算结果。SQL Server 2000所提供的聚合函数列于表4.10中。4.1.5 数据汇总数据汇总表表

44、4.10 聚合函数表聚合函数表函数名函数名说说明明AVG求组中值的平均值。BINARY_CHECKSUM返回对表中的行或表达式列表计算的二进制校验值，可用于检测表中行的更改。CHECKSUM返回在表的行上或在表达式列表上计算的校验值，用于生成哈希索引。CHECKSUM_AGG返回组中值的校验值。COUNT求组中项数，返回int类型整数。COUNT_BIG求组中项数，返回bigint类型整数。GROUPING产生一个附加的列。MAX求最大值。MIN求最小值。SUM返回表达式中所有值的和。STDEV返回给定表达式中所有值的统计标准偏差。STDEVP返回给定表达式中所有值的填充统计标准偏差。VAR返

45、回给定表达式中所有值的统计方差。VARP返回给定表达式中所有值的填充的统计方差。4.1.5 数据汇总数据汇总 下面对常用的几个聚合函数加以介绍。（1）SUM和AVG SUM和AVG分别用于求表达式中所有值项的总和与平均值，语法格式为：SUM/AVG(ALL|DISTINCT expression)其中expression是常量、列、函数或表达式，其数据类型只能是：int、smallint、tinyint、bigint、decimal、numeric、float、real、money和smallmoney。ALL表示对所有值进行运算，DISTINCT表示去除重复值，缺省为ALL。SUM/AVG忽

46、略NULL值。4.1.5 数据汇总数据汇总【例例4.31】求选修101课程的学生的平均成绩。SELECT AVG(成绩)AS 课程101平均成绩 FROM XS_KC WHERE 课程号=101 执行结果为：课程101平均成绩 78 使用聚合函数作为SELECT的选择列时，若不为其指定列标题，则系统将对该列输出标题“（无列名）”。4.1.5 数据汇总数据汇总（2）MAX和MIN MAX和MIN分别用于求表达式中所有值项的最大值与最小值，语法格式为：MAX/MIN(ALL|DISTINCT expression)其中expression是常量、列、函数或表达式，其数据类型可以是数字、字符和时间日

47、期类型。ALL、DISTINCT的含义及缺省值与SUM/AVG函数相同。MAX/MIN忽略NULL值。4.1.5 数据汇总数据汇总【例例4.32】求选修101课程的学生的最高分和最低分。SELECT MAX(成绩)AS 课程101的最高分,MIN(成绩)AS 课程101的最低分 FROM XS_KC WHERE 课程号=101 执行结果为：课程101的最高分 课程101的最低分 95 62 4.1.5 数据汇总数据汇总（3）COUNT COUNT用于统计组中满足条件的行数或总行数，格式为：COUNT(ALL|DISTINCT expression|*)其中expression是一个表达式，其数

48、据类型是除uniqueidentifier、text、image或ntext之外的任何类型。ALL、DISTINCT的含义及缺省值与SUM/AVG函数相同。选择*时将统计总行数。COUNT忽略NULL值。4.1.5 数据汇总数据汇总【例例4.33】求学生的总人数。SELECT COUNT(*)AS 学生总数 FROM XS 其中：COUNT(*)不需要任何参数。【例例4.34】求选修了课程的学生总人数。SELECT COUNT(DISTINCT 学号)FROM XS_KC4.1.5 数据汇总数据汇总【例例4.35】统计离散数学课程成绩在85分以上的人数。SELECT COUNT(成绩)AS 离

49、散数学85分以上的人数 FROM XS_KC WHERE 成绩=85 AND 课程号 IN (SELECT 课程号 FROM KC WHERE 课程名=离散数学 )执行结果为：离散数学85分以上的人数 2 COUNT_BIG函数的格式、功能与COUNT函数都相同，区别仅在于COUNT_BIG返回bigint类型值。4.1.5 数据汇总数据汇总 （4）GROUPING GROUPING函数为输出的结果表产生一个附加列，该列的值为1或0，格式为：GROUPING（column_name）当用 CUBE 或 ROLLUP 运算符添加行时，附加的列输出值为1，当所添加的行不是由 CUBE 或 ROLL

50、UP 产生时，附加列值为0。该函数只能与带有CUBE或ROLLUP运算符的GROUP BY子句一起使用。4.1.5 数据汇总数据汇总2.GROUP BY子句子句 GROUP BY子句用于对表或视图中的数据按字段分组，格式为：GROUP BY ALL group_by_expression,n WITH CUBE|ROLLUP 说明说明:group_by_expression:用于分组的表达式，其中通常包含字段名。指定ALL将显示所有组。使用GROUP BY子句后，SELECT子句中的列表中只能包含在GROUP BY中指出的列或在聚合函数中指定的列。WITH指定CUBE或ROLLUP操作符，CU