SQL Server 数据库基础及应用-7

第7章Transact-SQL语言基础6第六节用户定义函数下一页返回7.1常量与变量常量是指在程序运行过程中，其值不会改变的量。SQL中的常量分为5种，即字符型常量、整型常量、实型常量、日期型常量、货币常量等。1)字符型常量，如:`abode‘.2)整型常量，如:11,70,1200等。3)实型常量，如:3.14,3.5等。4)日期型常量，如:6/25/83,may192000等。5)货币常量，如:$1000等。下一页返回7.1常量与变量7.1.2变量变量是指由用户定义并可赋值的实体。变量有全局变量和局部变量两种:1)全局变量:由系统维护和定义，名字由两个@符号开始。2)局部变量:用DECLARE语句声明并且由SET语句或SELECT语句赋值，只能用在声明该变量的过程体内，名字由一个@符号开始。下面主要介绍局部变量的操作。用DECLARE语句声明变量，并用SET或SELECT语句给其指派值。所有变量在声明后均初始化为NULL上一页下一页返回7.1常量与变量局部变量的声明格式为:DECLARE局部变量名称数据类型[，局部变量名称数据类型…」其中，局部变量名称必须以符号“@”开头，必须符合标识符命名规则。数据类型是任何由系统提供的或用户定义的数据类型。变量不能是text~ntext或image数据类型。【例7一1】定义变量@VAR1为char字符类型，长度为20;定义变量@VAR2为int字符类型。其命令及执行结果如图7一1所示。上一页下一页返回7.1常量与变量1局部变量赋值格式一SET变量名=表达式变量名:是除cursor~text~ntext或image外的任何类型变量的名称。表达式:是任何有效的SQLServe:表达式。【例7-2】为例7一1所定义的变量@VARI、@VAR2赋值。其命令及执行结果如图7-2所示。公局部变量赋值格式二SELECT变量名=表达式或SELECT子句【例7-3】为例7一1所定义的变量@VARI、@VAR2赋值。其命令及执行结果如图7一3所示。上一页返回7.2运算符和表达式运算符用来执行列或变量间的数学运算或值的比较，SQLServe:支持的运算符有:算术运算符、比较运算符、字符串连接运算符、逻辑运算符、位运算符。1算术运算符算术运算符用于数字列或变量间的算术运算。算术运算符包括:+(加)、一(减)、*(乘)、/(除)、%(取模)运算。2比较运算符比较运算符用来比较两个表达式之间的区别。比较运算符包括:=(等于)、>(大于)、<(小于)、>=(大于等于)、<=(小于等于)、<>(不等于)。下一页返回7.2运算符和表达式3字符串连接运算符字符串连接运算符(+)用于连接字符串值，字符类型的数据都可以由字符串连接运算符操作。例如，'abc'+'123’最后表达式的结果是’abc123'.4逻辑运算符逻辑运算符一般用于连接检索条件。逻辑运算符包括:and(与)、or(或)、not(非)。上一页下一页返回7.2运算符和表达式位运算符位运算符对数据进行位与(&)、或(||)、异或()、求反(一)运算。运算时，会先把要操作的数据转换成二进制数据。在Ttansact-SQL中，位运算符已很少使用。SQLServer中的各种运算符和在数学中的运算符一样，也有优先级别，运算符的优先级由高到低为:括号:()求反:-乘、除、取模:*、/、%加、减:+、.按位异或:按位与:&按位或:逻辑非:NOT逻辑与:AND逻辑或:OR上一页返回7.3数据查询操作在SQLServer中，用SELECT语句实现对数据库的查询操作。SQLServer2005提供了丰富的查询语句，除了用SELECT语句实现对数据库的查询操作外，同时，还可以使用各种子句对查询结果进行分组统计、合计、排序等操作，使数据库的I作效率有了极大程度的提高。7.3.1查询的基本语法SQL的数据查询通过使用SELECT语句来实现，可以方便快速地检索数据，但不会改变数据库中的数据记录。同时SELECT语句也是SQL的核心，并且语句比较复杂，其语法结构如下所示:下一页返回7.3数据查询操作上一页下一页返回7.3数据查询操作参数解释如下:令SELECT:指定要选择的列、计算结果或行及其限制。令FROM:指定查询语句的表或视图，可以同时根据多个表查询数据记录。令WHERE:指定查询条件，以进行数据记录行的过滤。只有符合条件的数据记录才会被显示、统计和分组。查询条件可以和另外的表或视图进行链接，从而进行链接查询和子查询等操作。令CROUPBY:根据分组表达式中出现的列指定分组的依据，在查询生成的数据记录集中，根据作为分组依据的列对数据记录集进行有序显示。上一页下一页返回7.3数据查询操作令HAVING:首先根据CROUPBY子句指定分组数据，再使用HAVING子句指定对分组后的数据记录集进行过滤。HAVING子句的存在以CROUPBY子句的存在为前提，反之不然。令ORDERBY:根据排序表达式中出现的列的顺序指定排序的先后依据。7.3.2简单查询简单查询是数据查询中一种最简单的查询形式，它只涉及一个表，但却是应用系统中使用最为广泛的一种查询操作。简单查询可以从单个表中检索指定列和指定行的数据记录集，也可以对数据记录集进行排序、分组、统计，甚至进行汇总以生成非二维结构的报表。上一页下一页返回7.3数据查询操作(1)选择所有列SELECT子句在选择表达式中使用“*”表示选择表中的所有列生成数据记录集进行显示。[例7-4]查询student表中的所有信息。其命令及执行结果如图7-4所示。(2)查询指定列有时候不需要显示表的全部列，此时可以通过在SELECT子句中指定列来实现，其语法格式为SELECT<例1>[，<列2>FROM[用户方案」<表或视图>例7-5在学生成绩数据库中查询全体学生的学号与姓名。其命令及执行结果如图7-5所示。上一页下一页返回7.3数据查询操作(3)查询计算列SELECT语句可以方便地对数据库表中已有的数据用运算符表达式进行批量计算，得到用户想要的数据。【例7-6】在学生成绩数据库中查询全体学生的姓名及其年龄。其命令及执行结果如图7-6所示。"year(getdate))一year(出生年月)”为上述查询中的表达式，在查询语句中既可以使用算术表达式，也可以使用字符串常量、函数。如果列值原来为空，则所有运算结果仍为空。

上一页下一页返回7.3数据查询操作从以上查询结果可以看出，用计算方式得出的列在上述语句的查询结果中是没有列名的，不能让用户直观地看出查询的成果，事实上，我们可以使用AS语句来对查询计算出来的新列进行命名。【例7-7】查询全体学生的姓名及其年龄，并将列名以“姓名”“年龄”显示。其命令及执行结果如图7-7所示。(4)去除查询结果中的重复行【例7-8】查询全体学生的系别。其命令及执行结果如图7一8所示。上一页下一页返回7.3数据查询操作检查上述查询结果，会发现有重复值。那如何去掉重复值呢，可以用DISTINCT选项，去除重复值(默认为ALL)其命令及执行结果如图7-9所示。（5）带条件的查询当需要对数据记录集的行进行过滤时，可以通过WHERE子句设置查询条件来实现，以选择符合要求的行。【例7-9】查询计算机科学与技术系全体学生的姓名。其命令及执行结果如图7一10所示。上一页下一页返回7.3数据查询操作1比较运算符的使用(比较大小)比较运算符包括:(>、>=、=、<=、<、<>、，>、，<)。【例7一10】在student表中，查询所有在“1993-01-01”前出生的学生信息。其命令及执行结果如图7一11所示。注:字符型、数值类型、日期类型的数据都可以比较大小，但注意类型要匹配。2范围运算符的使用(确定范围)使用between...and…和notbetween...and...，查找列值在(或不在)指定范围内的记录。上一页下一页返回7.3数据查询操作1.列表运算符的使用(确定集合)使用in指定查询集合。如果查询范围内的数据值数量有限，也可以通过枚举的方式在集合中指定，并使用in关键字从集合中逐个比较。上一页下一页返回7.3数据查询操作4模式匹配符(字符匹配)模式匹配符为like或notlike。一般用于模糊查询，判断列值是否与指定的字符串格式相匹配。在判断列值是否与指定的字符串格式相匹配的过程中，会用到以下的通配符:代表任意类型和长度的字符串。代表任意单个字符。指定一个字符、字符范围。与方括号取值相同，但是所匹配对象为指定字符以外的任一字符。上一页下一页返回7.3数据查询操作空值判断符(涉及空值的查询)【例7一17】在average表中，查询学生成绩为空值的学生的学号、课程号。其命令及执行结果如图7一18所示。例7一18在average表中，查询学生成绩不为空值的学生的学号、课程号。其命令及执行结果如图7一19所示。逻辑运算符(多重条件查询)当有多个查询条件时，可以使用逻辑运算符(NOT,AND,OR)来连接。【例7-19】在student表中，查询计算机科学与技术系的出生年月在1993-01-01之前的学生姓名。

其命令及执行结果如图7-20所示。上一页下一页返回7.3数据查询操作7.3.3查询结果排序在输出查询数据记录集时，默认的数据记录显示顺序是这些数据记录在原表中的逻辑排列顺序，如果需要定制查询数据记录集的输出顺序，则可以使用ORDERBY子句实现对查询结果按照一列或多列的升序(ASC)或降序(DESC)排列(缺省值时为升序)。

【例7-20】查询所有学生信息，并按学号降序排列。其命令及执行结果如图7-21所示。上一页下一页返回7.3数据查询操作7.3.4查询统计在SELECT语句中，可以使用集函数对数据库中的数据进行统计。常用的集函数有COUNTgDISTINCT

ALL

<列名>)统计个数SUMODISTINCT

ALL

<列名>)计算总和AV引rDISTINCT

ALL

<列名>)计算平均值MAXgDIStINCtALL

<列名>)求最大值MINgDIStINCt

ALL

<列名>)求最小值[例7-22]在student表中，查询学生的总人数。其命令及执行结果如图7-23所示。上一页下一页返回7.3数据查询操作【例7-23】在average表中，查询考试学生的总人次和总人数。其命令及执行结果如图7-24所示。【例7-24】在average表中，统计所有学生的平均分数。其命令及执行结果如图7-25所示。【例7-25】在average表中，查出课程号为“060073”的课程最高分信息。其命令及执行结果如图7-26所示。上一页下一页返回7.3数据查询操作7.3.5分组查询有时候需要对查询数据记录按列或表达式进行分组，以便于分析数据，此时可以通过使用CROUPBY子句来实现。Ttansact-SQL使用groupby子句将查询结果表按某一列或多列值进行分组，值相等的为一组。分组查询通常和集函数一起使用，以实现分组统计。【例7-26】查询每位学生的平均成绩。其命令及执行结果如图7-27所示。【例7-27】在average表中，计算每门课程的选课人数。其命令及执行结果如图7-28所示。上一页下一页返回7.3数据查询操作having子句:如果分组后还要求按一定的条件对这些组进行筛选，并且最终只输出满足指定条件的组，则可以使用having子句。【例7-28在average表中，查询平均分在80分以上的学生。其命令及执行结果如图7-29所示。7.3.6连接查询前面介绍的查询都是只针对一个表实施查询操作，实际上，数据库实例中的各个表之间可能存在某些内在关联，通过这些关联，可以为应用程序提供一些涉及多个表的复杂信息，如主表和外表之间就存在主键和外键的关联。SQL为这种多个表之间存在的关联的查询提供了检索数据的方法，称为连接查询。

上一页下一页返回7.3数据查询操作连接查询主要包括:内连接查询、外连接查询、交叉连接查询。1内连接查询内连接是使用比较运算符作为连接条件的连接方式。根据连接条件中的关系运算符“=”，可将内连接分为等值连接、非等值连接和自连接。1)等值连接:查询结果将列出所连接表中的所有列(包括重复列)。公外连接查询在内连接中，只有那些满足连接条件的数据记录才被显示，不满足连接条件的数据记录将不被显示。有时候，需要显示一个表的所有数据记录和另一个表的若干记录，这时候就需要使用外连接。上一页下一页返回7.3数据查询操作7.3.7子查询

一个select-from-where语句称为一个查询块。将一个查询块嵌套在另一个查询块的where子句或having短语的条件中的查询称为嵌套查询。前者可以称为子查询或内查询，后者相应地称为外查询。SQL对嵌套查询的处理方法是:从内层向外层处理，即先处理最内层的子查询，然后把查询的结果用于其外查询的查询条件，再层层向外求解，最后得出查询结果。嵌套查询包括以下几种子查询:带有IN谓词的子查询、带有比较运算符的子查询、带有ALL谓词的子查询及带有EXISTS谓词的子查询。上一页下一页返回7.3数据查询操作1带有IN谓词的子查询2带有比较运算符的子查询3带有aLL谓词的子查询4带有EXISTS谓词的子查询上一页下一页返回7.4程序流程控制语句流程控制语句用来控制SQL语句、语句块或存储过程的执行流程，Ttansact-SQL支持流程控制语句。7.4IF...ELSE语句IF...ELSE语句格式:IF布尔表达式{SQL语句或语句块}ELSE{SQL语句或语句块}7.4.2无条件转移语句GOTO语句GOT。语句格式GOT。标号上一页下一页返回7.3数据查询操作7.4.3WHILE、BREAD,CONTINUE语句WHILE,BREAK和CONtINUE语句格式WHILE布尔表达式BEGIN命令行或程序块[BREAK][CONTINUE]ENDBREAK:使程序从循环中跳出。CONTINUE:使程序跳过循环体内其后续语句，结束本次循环，执行下一次循环。上一页下一页返回7.5系统内置函数函数可以帮助用户获取信息、执行数学计算和统计功能、实现数据的类型转换等。Ttansact-SQL主要提供了三种类型的函数。Transact-SQL编程语言提供三种函数:行集函数:可以像SQL语句中表引用一样使用。聚合函数:对一组值操作，但返回单一的汇总值。标量函数:对单一值操作，返回单一值。上一页返回7.5系统内置函数7.5.1行集函数从sQLServer7.0开始，新增了行集函数，它们所返回的对象可以像表一样被Transact-SQL语句所参照。这些行集函数返回的对象可在Transact-SQL语句中用作表引用。行集函数主要有以下几个:OPENQUERY,OPENROWSET,CONTAINSTABLE、FREETEXTTABLE,OPENDATASOURCE,OPENXML所有行集函数都不具有确定性。每次用一组特定输入值调用它们时，所返回的结果不总是相同的。有关函数确定性的更多信息，请参见:确定性函数和非确定性函数。下一页返回7.5系统内置函数1.OPENQUERY行集函数对连接服务器上的OLEDB数据源执行查询操作，并返回查询结果集合，可以在查询的FROM子句中像引用表名那样引用OPENQUERY函数。依据OLEDB提供程序的能力，还可以将OPENQUERY函数引用为INSERT,UPDATE或DE-LETS语句的目标表。尽管查询可能返回多个结果集，但是OPENQUERY只返回第一个。语法格式:OPENQUERY(连接服务器名称，查询命令)上一页下一页返回7.5系统内置函数2OPENROWSET行集函数对远程OLEDB数据源执行查询操作，其功能与OPENQUERY函数相似。语法格式:OPENROWST(字符串，连接参数，查询语句)3CONTAINSTABLE行集函数对表中所有列或指定列进行包含式全文查询。4FREETEXTTABLE行集函数对表中所有列或指定列进行自由格式全文查询，即在一个表的所有列或指定列中搜索一个自由文本格式的字符串，并返回与该字符串相匹配的数据行。上一页下一页返回7.5系统内置函数7.5.2聚合函数聚合函数用于计算SELECT语句查询行的统计值，通常与CROUPBY语句一起使用，进行分组统计。集合函数不能用于SELECT语句的WHERE子句。主要的聚合函数:AVC(expr):列值的平均值。该列只能包含数字数据。COUNT(expr):计算表达式中非空值的数量。COUNT(*):计算所有行数，包含空值。

上一页下一页返回7.5系统内置函数MAX(expr):列中最大的值(文本数据类型中按字母顺序)。忽略空值。MIN(expr):列中最小的值(文本数据类型中按字母顺序)。忽略空值。SUM(expr):列值的合计。该列只能包含数字数据。7.5.3标量函数标量函数操作某一单一的值，并返回一个单一的值。只要是能够使用表达式的地方，就可以使用标量函数。常用的标量函数有数学函数:实现各种数学运算。字符串函数:实现字符串的转换、查找等操作。系统函数:返回sQLServer服务器和数据库的特殊信息。日期与时间函数:用于处理datetime和smalldatetime类型的数据。上一页返回7.6用户定义函数SQLServer支持两种函数类型:内置函数:按Transact-SQL参考中定义的方式运行且不能修改。只有使用Transact-SQL参考中所定义的语法中的Transact-SQL语句才能引用这类函数。用户定义函数:用CREATEFUNCTION语句定义自己的Transact-SQL函数，来补充和扩展系统支持的内置函数。用户定义函数接受零个或多个输入参数，并返回单值。下一页返回7.6用户定义函数7.6.1用户函数的定义利用CREATEFUNCTION语句创建用户自定义函数，用户自定义函数名称必须唯一。语法格式: