高等教育SQL-Server-课件

Transact-SQL语言Transact-SQL语言1Transact-SQL语言SQL是一种结构化查询语言，它包括数据定义、查询、操纵和控制四个方面的功能，是一种功能齐全的数据库语言。SQL是ANSI（美国国家标准局）确认的关系型数据库语言标准，因此无论是像Oracle,Sybase,Informix,SQLServer这些大型数据库管理系统，还是像VisualFoxPro等微机上常用的数据库开发系统，都支持SQL语言作为查询语言。

Transact-SQL语言SQL是一种结构化查询语言，它包SQL语言及其特点1）非过程化语言在SQL语言中，数据的存取路径并不一定要求用户指定，存取路径的选择和SQL语言的操作过程都是由系统自动完成的，用户只需提出“做什么”，而不必指明“怎么做”，从而提高了数据的独立性。

SQL语言及其特点1）非过程化语言2）统一的语言SQL语言集数据定义语言（DDL）、数据操纵语言（DML)和数据控制语言（DCL)的功能于一体，可以独立完成数据库生命周期中的全部活动，包括定义关系模式、建立数据库、录入数据、查询、更新、维护、数据库安全性控制等一系列操作，为数据库应用系统的开发提供良好的环境。以前的数据库管理系统为上述各类操作提供单独的语言，而SQL可用于所有的用户的数据库活动模型，包括系统管理员、数据库管理、应用程序员、决策支持系统人员及许多其他类型的终端用户，将全部任务统一在一种语言中。SQL语言及其特点2）统一的语言SQL语言及其特点3）是所有关系数据库的公共语言由于所有主要的关系数据库管理系统（RDBMS）都支持SQL语言，所以用户可以将使用SQL的技能从一个RDBMS转到另一个RDBMS，所有用SQL编写的程序都可以从一个RDBMS移植的另一个RDBMS。SQL语言及其特点3）是所有关系数据库的公共语言SQL语言及其特点运算符P94算术运算符包括加（+）、减（-）、乘（*）、除（/）和取模（%）赋值运算符Transact-SQL中只有一个赋值运算符，即等号（=）位运算符包括按位与（&）、按位或（|）、按位异或（^）和求反（~）。位运算符用来对整型数据或者二进制数据（image数据类型除外）之间执行位操作。要求在位运算符左右两侧的操作数不能同时是二进制数据。运算符P94算术运算符运算符比较运算符比较运算符包括：等于（=）、大于（>）、大于或等于（>=）、小于（<）、小于或等于（<=）、不等于（<>或!=）、不小于（!<）、不大于（!>）。逻辑运算符逻辑运算符包括与（AND）、或（OR）和非（NOT）等运算符。逻辑运算返回布尔值，值为TRUE或FALSE。字符串连接运算符（+）例如：‘abc’+‘123’‘abc123’运算符比较运算符ALL、ANY分别用于判断一个表达式的值与一个子查询结果集合中的所有或任一个值间的关系是满足指定的比较条件。它们的语法格式为：expression{=|!=|>|>=|!>|<|<=|!<}{ALL|ANY}(subquery)其中，expression为被测试表达式。Subquery为子查询，它返回单列的结果集合，其中的集合成员具有相同的数据类型。EXISTS表示子查询包含任何行时，为真。运算符ALL、ANY运算符运算符的优先级括号：（）；乘、除、求模运算符：*，/，%；加减运算符：＋，－；比较运算符：=，>，<，>=，<=，<>，!=，!>，!<；位运算符：^，&，|；逻辑运算符：NOT；逻辑运算符：AND；逻辑运算符：OR。运算符运算符的优先级运算符变量变量是一种语言中必不可少的组成部分。Transact-SQL语言中有两种形式的变量，一种是用户自己定义的局部变量，另外一种是系统提供的全局变量。变量变量是一种语言中必不可少的组成部分。Transact-S全局变量全局变量在整个SQLServer系统内使用。存储的通常是一些SQLServer的配置设定值和统计数据。在使用全局变量时应该注意以下几点：全局变量是在服务器级定义的。用户只能使用预先定义的全局变量。引用全局变量时，必须以标记符“@@”开头。全局变量对用户来说是只读的。局部变量的名称不能与全局变量的名称相同。SQLServer中可用的系统全局变量见P102表4.5全局变量全局变量在整个SQLServer系统内使用。存储的局部变量P103局部变量是一个能够拥有特定数据类型的对象，它的作用范围仅限制在程序内部。利用局部变量可以保存程序执行过程中的中间数据值，保存由存储过程返回的数据值等。局部变量被引用时要在其名称前加上标志“＠”，而且必须先用DECLARE命令定义后才可以使用。局部变量P103局部变量是一个能够拥有特定数据类型的局部变量局部变量的定义

DECLAER@local_variabledata_type[,@local_variabledata_type…]

局部变量的赋值方法

SET@local_variable=expression[,@local_variable=expression]或者SELECT@local_variable=expression[,@local_variable=expression]局部变量局部变量的定义控制语句P110流程控制语句是指那些用来控制程序执行和流程分支的命令，在SQLServer2000中，流程控制语句主要用来控制SQL语句、语句块或者存储过程的执行流程。控制语句P110流程控制语句是指那些用来控制程序执行BEGIN…END程序块BEGIN…END能够将多个Transact-SQL语句组合成一个语句块，并将它们视为一个单元处理。在条件语句和循环等控制流程语句中，当符合特定条件便要执行两个或者多个语句时，就需要使用BEGIN…END语句，其语法形式为：BEGIN

{sql_statement

|statement_block

}

END

BEGIN…END程序块BEGIN…END能够将多个TranIF…ELSE语句IF…ELSE语句是条件判断语句，其中，ELSE子句是可选的，最简单的IF语句没有ELSE子句部分。IF…ELSE语句用来判断当某一条件成立时执行某段程序，条件不成立时执行另一段程序。SQLServer允许嵌套使用IF…ELSE语句，而且嵌套层数没有限制。IF…ELSE语句IF…ELSE语句是条件判断语句，其中，EIF…ELSE语句IF…ELSE语句的语法形式

IFBoolean_expression

{sql_statement|statement_block}

[ELSE

{sql_statement|statement_block}]

例：对X、Y、Z三个数排序输出。使用SET和SELECT对变量赋值的区别使用PRINT和SELECT输出变量值的区别IF…ELSE语句IF…ELSE语句的语法形式CASE语句CASE语句严格说起来，不叫语句，应该叫CASE表达式(或叫函数)，因为它不能单独执行，只能作为其它语句中的一部分。CASE表达式可以计算多个条件式，并将其中一个符合条件的结果表达式返回。CASE表达式按照使用形式的不同，可以分为简单CASE表达式和搜索CASE表达式。CASE语句CASE语句严格说起来，不叫语句，应该叫CASE简单CASE表达式CASEinput_expression

WHENwhen_expressionTHENresult_expression

[...n]

ELSEelse_result_expression

END

例：DECLARE@xint,@yintSET@x=‘2’SET@y=CASE@xWHEN‘1’THEN@x+5WHEN‘2’THEN@x*5ENDSELECT@x,@y简单CASE表达式CASEinput_expression搜索CASE表达式CASE

WHENBoolean_expressionTHENresult_expression

[...n]

ELSEelse_result_expressionEND

例：DECLARE@xint,@yint

CASEWHEN@x=‘1’THEN@x+5WHEN@x=‘2’THEN@x*5END搜索CASE表达式CASE

WHENBoolean_exWHILE…CONTINUE…BREAK语句WHILE…CONTINUE…BREAK语句用于设置重复执行SQL语句或语句块的条件。只要指定的条件为真，就重复执行语句。其中，CONTINUE语句可以使程序跳过CONTINUE语句后面的语句，回到WHILE循环的第一行命令。BREAK语句则使程序完全跳出循环，结束WHILE语句的执行。

WHILE语句可以嵌套使用。WHILE…CONTINUE…BREAK语句WHILE…COWHILE…CONTINUE…BREAK语句结束WHILE语句的语法形式为：WHILEBoolean_expression

BEGIN

{sql_statement|statement_block}

[BREAK]

{sql_statement|statement_block}

[CONTINUE]

{sql_statement|statement_block}

END例：P113【例4.8】求出150~180之间的所有素数。WHILE…CONTINUE…BREAK语句结束WHILE语WAITFOR语句WAITFOR语句用于暂时停止执行SQL语句、语句块或者存储过程等，直到所设定的时间已过或者所设定的时间已到才继续执行。WAITFOR语句的语法形式为：

WAITFOR{DELAY'time'|TIME'time'}

其中，DELAY用于指定时间间隔，TIME用于指定某一时刻，其数据类型为datetime，格式为‘hh:mm:ss’。WAITFOR语句WAITFOR语句用于暂时停止执行SQL语RETURN语句RETURN语句用于无条件地终止一个查询、存储过程或者批处理，此时位于RETURN语句之后的程序将不会被执行。RETURN语句的语法形式为：

RETURN[integer_expression

]

其中，参数integer_expression为返回的整型值。存储过程可以给调用过程或应用程序返回整型值。RETURN语句RETURN语句用于无条件地终止一个查询、存SQL语言的命令SQL功能命令动词说明数据定义语言（DDL）CREATE按特点的模式创建一个新表、视图和表索引ALTERTABLE修改表结构DROP删除一张表、视图、表索引数据操纵语言（DML）SELECT从一个表或多个表中查询行和列INSERT向一个表中增加行UPDATE更新表中存在行的某几列DELETE从一个表中删除行数据控制语言（DCL）GRANT向用户授予操作权限REVOKE向用户收回操作权限SQL语言的命令SQL功能命令动词说明数据定义语言（DDL）数据定义P1141）定义基本表CREATETABLE<表名>（<列名><数据类型>〔约束条件〕〔，<列名><数据类型>〔约束条件〕…〕）；

数据定义P1141）定义基本表2）修改基本表ALTERTABLE<表名>〔ADD<新列名><数据类型>〔约束条件〕〕〔DROP<约束条件>〕〔MODIFY（<新列名><数据类型>）〕；3）删除基本表DROPTABLE<表名>;数据定义P1192）修改基本表数据定义P1193）建立索引CREATE〔UNIQUE〕〔CLUSTER〕INDEX<索引名>

ON<表名>（<列名>〔<次序>〕〔，<列名>〔<次序>〕〕…）；数据定义3）建立索引数据定义4）修改索引ALTERINDEX<索引名>TO[NOT]CLUSTER；5）删除索引DROPINDEX<索引名>；数据定义4）修改索引数据定义数据操纵语言P124数据操纵语言（DML）是指用来查询、添加、修改和删除数据库中数据的语言，这些语言包括以下四种：SELECT语句INSERT语句UPDATE语句DELETE语句数据操纵语言P124数据操纵语言（DML）是指用来SELECT语句SELECT语句从数据库中检索行，并允许从一个或多个表中选择一个或多个行或列。根据WHERE子句中的表达式，从表中找出满足条件的元组，形成结果表。SELECT语句的基本格式如下：SELECT[Distinct][ALL|TOPn]<目标列表达式>[，<目标列表达式>]…[INTOnew_table]FROM<表名或视图名>[，<表名或视图名>]…[WHERE<条件表达式>][GROUPBY<分组列名>[HAVING<条件表达式>]][ORDERBY<列名>[ASC|DESC][，<列名>[ASC|DESC]]

…]其中，[]表示可选项。

SELECT语句SELECT语句从数据库中检索行，并允许从一SELECT语句的意义是：根据FROM子句中提供的表，按照WHERE子句中的条件（表间的连接条件和选择条件）表达式，从表中找出满足条件的记录。按照SELECT子句中给出的目标列，选出记录中的字段值，形成查询结果的数据表。目标列上可以是字段名，字段表达式，也可以是使用汇总函数对字段值进行统计计算的值。SELECT语句的功能很强。可以完成对各种数据的查询，可以通过WHERE子句的变化，以不同的语句形式，完成相同的查询任务。SELECT还可以以子查询形式嵌入到SELECT语句、INSERT(插入记录）语句、DELETE(删除记录)语句和UPDATE(修改记录)语句中，作为这些语句操作的条件，构成嵌套查询或带有查询的更新（增、删、改）语句。SELECT语句SELECT语句的意义是：根据FROM子句中提供的表，按照WSELECT语句中各字句的作用如下：①

SELECT子句：用来指定由查询返回的列（字段）。②

INTO子句：用来创建新表，并将查询结果行插入新表中。③

FROM子句：用来指定从中查询行的表。④

WHERE子句：用来指定限制返回的行的搜索条件。⑤

GROUPBY子句：用来指定查询结果的分组条件，可以利用它进行分组汇总。⑥HAVING子句：必须跟随GROUPBY使用，用来指定分组必须满足的条件。HAVING子句能使用累计函数（如SUM,AVG等函数），而WHERE子句不能。⑦

ORDERBY子句：用来指定结果集的排序方式。SELECT语句SELECT语句中各字句的作用如下：SELECT语句SELECT语句单表查询（不带条件查询）查询指定属性列查询所有属性列重命名查询的属性列名对查询结果排序消除查询取值重复的行SELECT语句单表查询（不带条件查询）条件查询Where子句用来设置一个筛选条件，使得查询结果中仅包含那些满足条件的数据。比较运算符利用空值查询BETWEEN关键字在WHERE子句中，可以使用BETWEEN、NOTBETWEEN两个运算符来确定表达式的取值是否在范围之内，并以此作为筛选的条件。用法是：SELECTselect_listFROMtable_sourceWHEREexpression[NOT]BETWEENexpressionANDexpression其中：[]表示可选项。条件查询Where子句用来设置一个筛选条件，使得查询结果中条件查询IN关键字在WHERE子句中，可以使用IN、NOTIN两个运算符来确定表达式的取值是否属于列表值之一，并以此作为筛选的条件。用法是：SELECTselect_listFROMtable_sourceWHEREexpression[NOT]IN(value_list)其中：[]表示可选项，value_list表示值列表，若有多个值，值之间用逗号分隔。条件查询IN关键字条件查询P95LIKE关键字LIKE、NOTLIKE字符串匹配运算符可以把表达式与字符串进行比较，并以此作为筛选的条件。用法是：SELECTselect_listFROMtable_sourceWHEREexpression[NOT]LIKE‘string’

其中：[]表示可选项，string表示用来进行比较的字符串。在string中，可以使用通配符，以实现对字符串的模糊查询。SQLServer提供了四种通配符：％代表任意多个字符，_（下划线）代表单个字符，[]代表指定范围内的单个字符，[^]代表不在指定范围内的单个字符。

注：含通配符的字符串须用单引号引起来。

条件查询P95LIKE关键字汇总数据汇总函数会将某个特定的一组数值进行计算并将结果以单一值来返回。除了count函数之外，其余的汇总函数会忽略所有Null的值。—般来说，其汇总函数通常会与SELECT语句中的GROUPBY一起使用。要使用汇总函数之前，必须要注意总和并不是适用在每一个TRANSACT-SQL语句中使用，它只能在如下所示的语句或子句中来使用：(1)SELECT语句的选择列表(子查询或外部查询)。(2)COMPUTE或COMPUTEBY子句。(3)HAVING子句。汇总数据汇总函数会将某个特定的一组数值进行计算并将结果以单一简单的计算查询P109

聚合函数在函数执行过程中对某一数据集（表）中的数据进行聚合运算,执行完毕后的输出结果是一个简单的数值。用于计算检索的函数有：1）COUNT求一组值中一共有多少个2）SUM为一组值求和3）AVG为一组值求平均值4）MAX求一组值中最大值5）MIN求一组值中最小值这些函数可以用在SELECT短语中对查询结果进行计算简单的计算查询P109聚合函数在函数执行过程中对某一数分组与计算查询

GROUPBY子句通常用于对某一个数据集(或表）中的某一列上具有相同值的数据进行操作，即按指定列上值相等的原则分组，然后再对组进行相应的操作，这称为分组查询。GROUPBY子句用来为结果集中的每一行产生集合值。如果集合函数没有使用GROUPBY子句，则只为SELECT语句报告一个集合值。HAVING子句指定组或集合的搜索条件，HAVING通常与GROUPBY一起使用。HAVING的行为与WHERE子句一样，惟一差别就是HAVING子句可以使用累计函数。分组与计算查询GROUPBY子句通常用于对某一个数据集(子查询

子查询是一个包含在另一条SELECT语句里的SELECT语句。外层的SELECT语句叫外部查询，内层的SELECT语句叫内部查询（或子查询）。通常，任何允许使用表达式的地方都可以使用子查询。包含子查询的SELECT语句主要采用以下格式中的一种：1）WHEREexpression[NOT]IN(subquery)2）WHEREexpressioncomparison_operator[ANY|ALL](subquery)3）WHEREexpression[NOT]EXISTS(subquery)子查询子查询是一个包含在另一条SELECT语句里的SELE子查询P94使用IN的子查询使用IN（或NOTIN）引入的子查询返回的查询结果是一列，该列有零值或更多值。子查询返回结果之后，外部查询可以使用这些结果。其格式为：WHEREexpression[NOT]IN(subquery)子查询P94使用IN的子查询子查询P94使用比较运算符的子查询子查询可由一个比较运算符引入。比较运算符可以是=、<>、>、>=、<、!>,!<或<=等等。其格式为：WHEREexpressioncomparison_operator[ANY|ALL](subquery)其中：comparison_operator为比较运算符，ALL表示子查询subquery返回的查询结果中的每一个值，ANY表示子查询中的任意一个值。子查询P94使用比较运算符的子查询子查询使用EXISTS的子查询使用EXISTS(或NOTEXISTS)关键字引入一个子查询时，就相当于进行一次存在测试。外部查询的WHERE子句测试子查询返回的行是否存在。子查询实际上不产生任何数据；它只返回TRUE或FALSE值。其格式：WHERE[NOT]EXISTS(subquery)子查询使用EXISTS的子查询连接查询P129连接查询可以在FROM或WHERE子句中指定连接条件，建议在FROM子句中指定连接，因为这样可以将指定的连接条件与WHERE子句中可能指定的搜索条件分开，连接查询的主要用法是：

SELECTselect_listFROMfirst_tablejoin_typesecond_table[ON(连接条件)][WHERE筛选条件][ORDERBY排序表达式[DESC]]其中，join_type指定所执行的连接类型，”连接条件”指定连接条件。连接类型可分为内连接(INNERJOIN)、外连接(OUTERJOIN)和交叉(CROSSJOIN)三类。连接查询P129连接查询可以在FROM或WHERE子内连接(INNERJOIN)它利用两个关系中共有的一个属性，将该属性值相等的元组内容连接起来，去掉其中的重复属性作为新的关系中的一个元组。连接过程：执行连接时，系统先将左边的表记录指针定位在首记录上，然后再右边的表中寻找符合条件的记录，每找到一个就与左边的表中的当前记录按指定字段进行连接，生成目标结果集中的一条记录；待右边的记录都处理完后，当前表的记录指针则再向下移动一个记录；重复上述寻找与连接过程，直至当前表的所有记录处理完为止。内连接(INNERJOIN)它利用两个关系中共有的一个属性外连接(OUTERJOIN)外连接中参与连接的表有主从之分，以主表的每行数据去匹配从表的数据行，符合连接条件的数据将直接返回到查询结果中；如果主表的行在从表中没有相匹配的行，与内连接丢弃不匹配行的做法不同，主表的行不会被丢弃，而是也返回到查询结果中，相对应的从表的行的列位置将被填上NULL值后再返回到结果集中。外连接又分为：左连接（LEFTOUTERJOIN）右连接（RIGHTOUTERJOIN）完全连接（FULLOUTERJOIN）（两个连接表中的所有数据行）

外连接(OUTERJOIN)外连接中参与连接的表有主从之分交叉(CROSSJOIN)交叉连接产生的查询结果的行数为第一个表的行数乘以第二个表的行数。注意：如果交叉连接带有WHERE子句，则交叉连接的作用同内连接一样。

交叉(CROSSJOIN)交叉连接产生的查询结果的行数为第自连接自连接中，使用同一个表的相同列进行比较连接。这时，对于同一个表应给出不同的的别名。自连接自连接中，使用同一个表的相同列进行比较连接。这时，对于联合查询联合查询就是使用UNION字句将来自不同查询的结果合并成为一个结果，UNION会自动将重复的数据行删除。带UNION子句的SELECT语句的主要用法是：SELECTselect_listFROMtable_source[WHERE筛选条件]{UNIONSELECTselect_listFROMtable_source[WHERE筛选条件]}[,...n]ORDERBY{排序表达式[DESC]}[,...n]其中：[]表示可选项；{}表示必选项；[,...n]表示前面的项可重复n次注意：为了进行并运算，要求进行并运算的两个查询结果具有相同的字段个数，并且具有相同的数据类型和取值范围。

联合查询联合查询就是使用UNION字句将来自不同查询的结果合基于查询生成新表有时候可能需要将查询结果保存下来，使用INTO子句可以生成一个新表并将查询结果保存在这个新表中。其主要用法是：SELECTselect_listINTOnew_tableFROMtable_source[WHEREsearch_condition]其中：new_table为要新建的表的名称。基于查询生成新表有时候可能需要将查询结果保存下来，使用INTINSERT(P131)INSERTINTO表名(字段1,字段2,…字段m)values(值1,值2,…值m）表名：表示将要添加数据的表。字段1,字段2,…字段m：表示将要用后面的值添加数据的列值1,值2,…值m：是要向上述列中添加的数据注意：如果表中某列定义了IDENTITY属性或者将某列的DEFAULT属性定义成NEWID()，则这样的列的值不能用INSERT语句给出，而是在使用INSERT语句添加新行时由系统自动生成。INSERT(P