T-SQL语言.ppt

1、数据库设计与应用,浙江工业大学软件学院,T-SQL语言,T-SQL其它语言元素,注释 变量 运算符 函数 流程控制语句,1. 注释,T-SQL 提供两种类型的注释字符： 一种是ANSI标准的注释符“-”，用于单行注释； 另一种是与C语言相同的程序注释符号，即“/* */”，用于注释多行文字。,注释是程序代码中不执行的文本字符串。,2. 变量,1. 变量 变量名必须是一个合法的标识符。 1）标识符 (1) 常规标识符 不允许使用T-SQL的保留字，且不允许嵌入空格或其他特殊字符。 (2) 分隔标识符 包含在双引号 (“) 或者方括号 ( ) 内的常规标识符或不符合常规标识符规则的标识符。若标识符

2、中有空格，必须使用双引号 或方括号 。,（2）变量的分类,Transact-SQL语言包括两种形式的变量： 用户自己定义的局部变量； 系统提供的全局变量。,1. 局部变量,局部变量是一个能够拥有特定数据类型的对象，它的作用范围仅限在程序内部。 局部变量必须先定义才能被引用。使用DECLARE命令定义局部变量，引用局部变量时必须在其名称前加上标志“” 。 局部变量用于保存单个数据值，供控制流语句测试以及保存由存储过程返回的数据值等。例如，保存运算的中间结果，作为循环变量等。,1) 局部变量的定义 在批处理或过程中用 DECLARE 语句声明局部变量。语法格式： DECLARE local_var

3、iable data_type ,.n,2. 局部变量的使用, local_variable 指定局部变量的名称; datatype设置局部变量的数据类型及大小。 局部变量可以为除text，ntext，image类型以外的任何数据类型。 所有局部变量在声明后均初始化为NULL，可以使用SELECT或SET设定相应的值。,2) 局部变量赋值（SET或SELECT语句）,将DECLARE语句创建的局部变量设置为给定表达式的值。 SET语句语法格式： SET local_variableexpression,【例1】创建局部变量var1、var2，并赋值，然后输出变量的值。 DECLARE var1

4、,var2 char(20) SET var1中国 /*一个SET语句只能给一个变量赋值*/ SET var2var1+是一个伟大的国家 SELECT var1,var2 /*输出变量的值*/ Go,【例3】使用查询给变量赋值。 USE XSCJ DECLARE student char(8) SET student=(SELECT 姓名 FROM XS) GO /*该语句若XS表中只有一条记录，可正常执行，否则将出错。*/,【例2】创建一个名为sex的局部变量，并在 SELECT 语句中使用该局部变量查找表XS中所有女同学的学号、姓名。 USE XSCJ DECLARE sex bit SE

5、T sex=0 SELECT 学号,姓名 FROM XS WHERE 性别=sex GO,SELECT赋值语句的语法格式： SELECT local_variable=expression ,n 说明如下： 1）SELECT语句通常用于将单个值返回到变量中，若有多个值时，将返回的最后一个值赋给变量； 2）若无返回行，则变量将保留当前值； 3）若expression不返回值，则变量设为NULL； 4）一个SELECT语句可以初始化多个局部变量。,SELECT语句也可用于局部变量的查看，格式如下：SELECT local_variable PRINT local_variable 也可用于局部变量

6、的查看,【例4】在 XS 表中不存在符合要求的结果，因此对该表的查询不返回结果，变量var1将保留原值。 USE XSCJ DECLARE var1 nvarchar(30) SELECT var1 = 刘丰 SELECT var1 = 姓名 /*将姓名列的值赋给变量*/ FROM XS WHERE 学号 = 64122312111 SELECT var1 AS NAME /* var1 为刘丰*/,3）局部游标变量的定义与赋值 (1) 局部游标变量的定义 语法格式： DECLARE cursor_variable_name CURSOR ,.n CURSOR表示该变量为游标变量。,(2) 游

7、标变量的使用步骤 定义游标变量给游标变量赋值打开游标利用游标读取行(记录)使用结束后关闭游标删除游标的引用。,(3) 局部游标变量的赋值：利用SET语句赋值 语法格式： SET cursor_variable= cursor_variable| /*将一个已存在的并且赋值的游标变量的值赋给另一局部游标变量*/ cursor_name| /*将一个已申明的游标名赋给指定的局部游标变量*/ CURSOR 子句 /*申明一个游标，同时将其赋给指定的局部游标变量*/ ,【例5】使用游标变量 USE XSCJ DECLARE CursorVar CURSOR /*定义游标变量*/ SET CursorV

8、ar = CURSOR SCROLL DYNAMIC /*给游标变量赋值*/ FOR SELECT 学号, 姓名 FROM XS WHERE 姓名 LIKE 王% OPEN CursorVar /*打开游标*/ FETCH NEXT FROM CursorVar /*游标指向第一行记录*/ WHILE FETCH_STATUS = 0 /* FETCH_STATUS 保存FETCH语句的执行状态，其值为0表示上一条执行成功；为-1表示FETCH 语句失败或此行不在结果集中；为-2表示被提取的行不存在（已删除）。*/ BEGIN FETCH NEXT FROM CursorVar /*通过游标读

9、行记录*/ END CLOSE CursorVar /*关闭游标*/ DEALLOCATE CursorVar /*删除对游标的引用*/,3.全局变量的使用,全局变量由系统提供且预先声明，是SQL Server系统内部使用的变量，其作用范围并不仅限于某一程序，而是任何程序均可以随时调用。 通常存储SQL Server的配置设定值和统计数据。 用户可以在程序中用全局变量来测试系统的设定值或者是Transact-SQL命令执行后的状态值。,使用全局变量时应该注意以下几点：,全局变量不是由用户的程序定义的，而是在服务器级定义的。 用户只能使用预先定义的全局变量。 引用全局变量时，必须以标记符“”开头

10、。 局部变量的名称不能与全局变量的名称相同，否则会在应用程序中出现不可预测的结果。,SQL Server支持的全局变量主要包括：,CONNECTIONS：返回自最近一次启动SQL Server以来连接或试图连接的次数。 ERROR：返回最后执行SQL语句的错误代码。 ROWCOUNT：返回上一次语句影响的数据行的行数。 SERVERNAME：返回运行SQL Server的本地服务器的名称。 VERSION：返回SQL Server当前安装的日期、版本和处理器类型。 全局变量的查看语句同局部变量：SELECT variable,SQL Server 2000的运算符主要有以下六大类： 算术运算符

11、 位运算符 比较运算符 逻辑运算符 字符串串联运算符 赋值运算符,2 运算符与表达式,1算术运算符,算术运算符有：+（加）、-（减）、*（乘）、/（除）和%（求模）五种运算。+ (加) 和(减) 运算符也可用于对 datetime 及 smalldatetime 值进行算术运算。 【例6】求学生的年龄。 USE XSCJ SET NOCOUNT ON DECLARE startdate datetime SET startdate = getdate() /*当前日期*/ SELECT startdate 出生时间 AS 年龄 FROM XS,位运算符在两个表达式之间执行位操作，这两个表达式的

12、类型可为整型或与整型兼容的数据类型（如：字符型等，但不能为 image 类型）。位运算符如表所示。,2位运算符,比较运算符(又称关系运算符)如表所示，用于测试两个表达式的值是否相同，其运算结果为逻辑值，可以为三种之一：TRUE、FALSE 及 UNKNOWN。,3. 比较运算符,逻辑运算符用于对某个条件进行测试，运算结果为 TRUE 或 FALSE。SQL Server提供的逻辑运算符如表所示。,4. 逻辑运算符,LIKE的使用 语法格式： match_expression NOT LIKE pattern ESCAPE escape_character ,【例7】查询课程名以C或A开头的情况

13、。 USE XSCJ SELECT * FROM KC WHERE 课程名 LIKE AC%,通过运算符“+”实现两个字符串的联接运算。将“+”号两边的字符串串联成一个字符串。 【例8】多个字符串的联接。 USE XSCJ SELECT (学号+ , + SPACE(1) + 姓名) AS 学号及姓名 FROM XS WHERE SUBSTRING(学号, 1, 2) = 19 /*取子串*/,5. 字符串联接运算符,一元运算符有+（正）、-（负）和（按位取反）。 如a的值为12（0000 0000 0000 1100） 则a的值为：1111 1111 1111 0011,6. 一元运算符,7

14、. 赋值运算符,T-SQL 只有一个赋值运算符，即等号（=） 。 赋值运算符可以将数据值指派给特定的对象。如给局部变量赋值的SET和SELECT语句中使用的“=”。 利用赋值运算符可以在列标题和为列定义值的表达式之间建立对应关系。,3 流程控制语句,SQL Server中提供了如表所示的流程控制语句,3.1 IF.ELSE语句,在程序中如果要对给定的条件进行判定，当条件为真或假时分别执行不同的 T-SQL 语句，可用IF.ELSE语句实现。 语法格式： IF Boolean_expression /*条件表达式*/ sql_statement | statement_block /*条件表达式

15、为真时执行*/ ELSE sql_statement | statement_block /*条件表达式为假时执行*/,IFELSE语句中，IF子句是必须的，ELSE子句是可选的，最简单的IF语句没有ELSE子句部分。 SQL Server允许嵌套使用IFELSE语句，而且嵌套层数没有限制。,IF 条件表达式 A /* T-SQL语句或语句块*/ ELSE B /*T-SQL语句或语句块*/ 当条件表达式的值为真时执行A，然后执行IF语句的下一语句；条件表达式的值为假时执行B，然后执行IF语句的下一语句。,【例8】如下程序用于查询总学分40的学生人数。 USE XSCJ DECLARE num

16、 int SELECT num= ( SELECT COUNT(姓名) FROM XS WHERE 总学分40) IF num0 SELECT num AS 总学分40的人数,【例9】如果“数据库原理课程”的平均成绩高于75分，则显示“平均成绩高于75分”。 USE XSCJ DECLARE text1 char(20) SET text1=平均成绩高于75. IF ( SELECT AVG(成绩) FROM XS_KC,KC WHERE XS_KC.课程号=KC.课程号 AND KC.课程名=数据库原理 ) 75 SELECT text1=平均成绩低于75. ELSE SELECT text

17、1,【例10】IF.ELSE语句的嵌套使用。 USE XSCJ IF ( SELECT AVG(成绩) FROM XS_KC,KC WHERE XS_KC.课程号=KC.课程号 AND KC.课程名=数据库原理 ) 75 SELECT 平均成绩高于75,BEGINEND语句,BEGINEND语句将多个T-SQL语句组合成一个语句块，并将它们视为一个单元处理。 在条件语句和循环等控制流程语句中，当符合特定条件便要执行两个或者多个语句时，就需要使用BEGINEND语句。 语法形式为： BEGIN sql_statement | statement_block END,3.2 GOTO语句,GOTO

18、语句可以使程序直接跳到指定的标有标识符的位置处继续执行，而位于GOTO语句和标识符之间的程序将不会被执行。通常，应尽量少用GOTO语句。 GOTO语句和标识符可以用在语句块、批处理和存储过程中，标识符可以为数字与字符的组合，但必须以“：”结尾。 语法形式如下： GOTO label label:,3.3 WHILECONTINUEBREAK语句,WHILECONTINUEBREAK语句用于设置重复执行 SQL 语句或语句块的条件。只要指定的条件为真，就重复执行语句。 CONTINUE语句可以使程序跳过CONTINUE语句后面的语句，回到WHILE循环的第一行命令。 BREAK语句则使程序退出其

19、所在的这一层循环，结束该层循环语句的执行。,WHILE语句的语法形式为：,WHILE Boolean_expression sql_statement | statement_block BREAK sql_statement | statement_block CONTINUE ,【例11】显示字符串China中每个字符的 ASCII 值和字符。 DECLARE position int, string char(8) SET position = 1 SET string = China WHILE position = DATALENGTH(string) BEGIN SELECT AS

20、CII(SUBSTRING(string, position, 1), CHAR(ASCII(SUBSTRING(string, position, 1) SET position = position + 1 END,RETURN语句用于无条件地终止一个查询、存储过程或者批处理，此时位于RETURN语句之后的程序将不会被执行。 语法形式为： RETURN integer_expression 其中，参数integer_expression为返回的整型值。 存储过程可以给调用过程或应用程序返回整型值。 NOTE：除非特别指明，所有系统存储过程返回 0 值表示成功，返回非零值则表示失败。,3.4

21、 RETURN语句,【例12】检查学生的平均成绩，若75，将返回状态代码 1，将返回状态代码 2。 USE XSCJ CREATE PROCEDURE checkavg param varchar(10) AS IF (SELECT AVG(成绩) FROM XS_KC WHERE XS_KC学号 = param GROUP BY 学号) 75 RETURN 1 ELSE RETURN 2,例13,查询课程号为“C01”课程的平均成绩，若平均成绩小于50，则用原成绩*1.2来更新该门课成绩。若最高成绩大于90分，则退出，否则继续执行。,USE XSCJ GO WHILE (SELECT AVG

22、(成绩) FROM XS_KC WHERE 课程号 =C01) 90 BREAK -若最高分大于90，则退出循环 ELSE CONTINUE -否则，继续执行 END,例14利用GOTO语句求出从1加到5的总和；利用WHILE语句求1-50的偶数和。,declare sum int, count int select sum=0, count=1 label_1: select sum=sum+count select count=count+1 if count=5 goto label_1 select count, sum 也可用WHILE语句来实现,declare sum int, c

23、ount int select sum=0, count=2 While (count=50 ) begin select sum=sum+count select count=count+2 end Print s=+str(sum),1数学函数,数学函数用于对数字表达式进行数学运算并返回结果。 主要包括： ABS（numeric_expr）：返回绝对值 SQRT（ float_expr ）：返回给定值的平方根 CEILING（numeric_expr）：返回不小于给定值的最小整数 FLOOR（numeric_expr）：返回不大于给定值的最大整数 ROUND（numeric_expr,le

24、ngth）：将给定的数值四舍五入到指定的长度 SIGN（numeric_expr）：判断给定的数值是否为正（1）、负（-1）或零（0） RAND（SEED）：产生随机数函数,4. 系统内置函数介绍,2.字符串函数,字符串函数可以对二进制数据、字符串和表达式执行不同的运算。 大多数字符串函数只能用于char和varchar数据类型以及明确转换成char和varchar的数据类型，少数几个字符串函数也可以用于binary和varbinary数据类型。 某些字符串函数还能处理text、ntext、image数据类型的数据。,字符串函数的分类,基本字符串函数：UPPER、LOWER、SPACE、REP

25、LICATE、STUFF、REVERSE、LTRIM、RTRIM。 字符串查找函数：CHARINDEX、PATINDEX。 长度和分析函数：DATALENGTH、SUBSTRING、RIGHT、LEFT。 转换函数：ASCII、CHAR、STR、SOUNDEX、DIFFERENCE。,2) CHAR：将ASCII码转换为字符 语法格式： CHAR ( integer_expression ),1) ASCII：返回左端第一个字符的ASCII值 语法格式： ASCII ( character_expression ),3) LEFT：返回从字符串左边开始指定个数的字符 语法格式： LEFT (

26、character_expression , integer_expression ) RIGHT：返回从字符串右边开始指定个数的字符,【例15】返回课程名最左边的 8 个字符，最右边的2个字符，并显示出相应的ASCII码。 USE XSCJ SELECT LEFT(课程名, 8), RIGHT(课程名, 2) , ASCII(LEFT(课程名, 8), ASCII(RIGHT(课程名, 2) FROM KC ORDER BY 课程号 GO,【例16】使用 LTRIM 字符删除字符变量中的起始空格。 DECLARE string varchar(40) SET string = 中国，一个古老

27、而伟大的国家 SELECT LTRIM(string) GO,4) LTRIM：删除字符串左边的空格，并返回字符串 语法格式： LTRIM ( character_expression ) RTRIM：删除字符串右边的空格,5) REPLACE：用第三个表达式的值替换第一个表达式中包含第二个表达式的部分。 语法格式： REPLACE ( string_expression1 , string_expression2 , string_expression3 ) 例：SELECT REPLACE(abcd,b,bb),6) SUBSTRING：求子串函数 语法格式： SUBSTRING ( ex

28、pression , start , length ) Expression：指定的字符串 Start：子串的起始位置 Length：子串的长度，即要返回的字节数,【例17】如下程序在一列中返回XS表中的姓氏，在另一列中返回表中学生的名。 USEXSCJ SELECT SUBSTRING(姓名, 1,1), SUBSTRING(姓名, 2, LEN(姓名)-1) FROM XS ORDER BY 姓名,SPACE(integer_expr)：返回指定长度的空格 UPPER(char_expr)：把给定的字符串变成大写字母 LOWER(char_expr)：把给定的字符串变成小写字母 STUFF

29、(char_expr1, star, length, char_expr2) ：在char_expr1中，把从位置star开始，长度为length的字符串用char_expr2代替 REVERSE(char_expr)：将指定的字符串反向 PATINDEX(%pattern%, expr） CHARINDEX (pattern, expr）,在给定的表达式中查找指定字符串的起始位置,在选择价格位于$11和$20之间的书名时，利用SUBSTRING函数从字符串中返回指定的字符串，并使用 LOWER 函数、UPPER 函数返回大写和小写的书名。其源程序清单为： USE pubs SELECT LO

30、WER(SUBSTRING(title,1,20) AS Lower, UPPER(SUBSTRING(title,1,20) AS Upper, FROM titles WHERE price between 11.00 AND 20.00,例18,定义一个局部变量，其类型为VARCHAR，并给该变量输入值为“ this is test ”。利用字符串函数实现以下操作，并显示结果值： 1。删除该字符变量起始空格和末尾空格； 2。返回该变量右边开始的四个字符； 3。将该字符变量取反； 4。将该字符变量中的“is”子串用“are”字符串替代。,DECLARE tempstr VARCHAR(20

31、) SET tempstr= this is test SELECT RTRIM(LTRIM(tempstr), RIGHT(RTRIM(LTRIM(tempstr),4), REVERSE(tempstr), REPLACE(tempstr,is,are),3. 系统函数,系统函数用于返回有关SQL Server系统、用户、数据库和数据库对象的信息。 与其它函数一样，可以在SELECT语句的SELECT子句和WHERE子句以及表达式中使用系统函数。,1）CASE函数,CASE函数可以计算多个条件式，并将其中一个符合条件的结果表达式返回。,CASE函数的语法形式,CASE WHEN Boolean_expression THEN result_expression .n ELSE else_result_expressionEND,【例19】根据学生的年龄范围显示相应信息。 /*使用CASE 搜索函数的 SELECT 语句*/ USE XSCJ SELECT 学号,年龄= CASE WHEN getdate()-出生