SQL Server存储过程、触发器

1.标识符、常量和变量（1）标识符：由用户定义的名称，用来标识各种对象如服务器、数据库、数据库对象、变量等。＊如果标志符中包含空格，要用双引号（“”）或方括号([])扩起来。如：StudentName不合法,必须将其表示为[StudentName]或＂StudentName＂。4.3.4T-SQL的运算功能和控制流程T-SQL虽然和高级语言不同，但它也有运算、控制等功能，以支持复杂的数据检索和集合操纵．（2）常量：在程序运行过程中值不变的量。字符串常量、日期时间常量要用单引号扩起，例如‘LiPing’，‘1990-05-04’。常量可分为字符串常量、整型常量、实型常量、日期时间常量、货币常量和唯一标识常量。单引号中的字符串包含单引号，可以使用两个单引号表示嵌入的单引号。（3）变量：在程序运行过程中其值可以被改变的量。1)变量的分类•局部变量：由用户定义和使用，其名称前有一个@符号。•全局变量：全局变量由系统定义和维护，其名称前有两个@符号。2）局部变量的定义和赋值定义格式：DECLARE@局部变量名数据类型【例】DECLARE@xfloat,@varchar(8)该定义语句定义了变量x是浮点实型，变量var是长度为8的定长字符数据类型。如在一条语句中声明多个变量，各变量之间用“,”分隔。局部变量被定义后其初始值为NULL。局部变量赋值的基本语法格式：SET@局部变量名=表达式SELECT@局部变量名=表达式【例4-53】定义变量c_code为长度为6的定长字符串，score为精度为1的浮点型，并分别用SET和SELECT语句为它们赋值，然后用这两个变量查询T_Grade表中课程代码为“110006”且成绩低于90分的记录。DECLARE@c_codechar(6),@scoreNumeric(3,1)SET@c_code='130001'SELECT@score=80SELECT*FROMT_GradeWHERECourseCode=@c_codeANDGrade@score图4-51变量查询示例1【例4-54】将学号为“05101103”的学生姓名存放到变量@sname中。DECLARE@snamevarchar(16)SELECT@sname=(SELECTStudentNameFROMT_StudentWHEREStudentCode='05101103')SELECT@snameas'姓名'2．运算符和表达式(1)运算符1)算术运算符：加（+）、减（-）、乘（*）、除（/）、模运算（%）2)比较运算符：比较两个表达式值之间的关系，运算结果是布尔数据类型（TRUE或FALSE）。通常用在查询的WHERE或HAVING子句中。。3）逻辑运算符：对某个条件进行判断。运算结果与比较运算符相同，也是运算结果是布尔数据类型（TRUE或FALSE）。4）字符串连接运算符：+,例如:'ABCD'&'1234'='ABCD1234'(2)表达式：表达式是由运算对象、运算符及圆括号组成。可在SQL查询分析器中使用Select语句查看表达式的结果例：SELECT'ABCD'+'1234'3．常用函数(1)数学函数表4-15常用数学函数函数名函数功能函数名函数功能ABS(x)计算x的绝对值SQRT(x)计算x的平方根ATN(x)计算x的反正切值SIGN(x)返回x的特征符号COS(x)计算x的余弦值TAN(x)计算x的正切值EXP(x)计算exRAND([n])产生0～1之间的随机float值LOG(x)计算自然对数lnxROUND（x,n）将x四舍五入为指定的长度或精度nSIN(x)计算x的正弦值(2)日期时间函数函数名函数功能DAY(x)返回指定日期中所表示的日，x是日期，例如：‘2008-1-1’MONTH(x)返回指定日期中所表示的月YEAR(x)返回指定日期中所表示的年份GETDATE(x)返回当前系统日期和时间(3)字符串函数表4-15常用数学函数(4)聚合函数函数名函数功能函数名函数功能ASCII(s)返回字符串s最左端字符的ASCII码STR(n)将数字数据n转换为字符数据CHAR(n)将ASCII转换为字符SPACE(n)返回n个空格LEFT(s,n)返回字符串s左边的n个字符SUBSTRING(s,m,n)返回字符串s起始m长度为n的子串RIGHT(s,n)返回字符串s右边的若n字符LOWER(s)将字符串中的字母转换为小写字母LEN(s)返回字符串s的长度（字符的个数）UPPER(s)将字符串中的字母转换为大写字母LTRIM(s)删除字符串s开始处的空格REPLACE(s1,s2,s3)用s3替换s1中包含的s2RTRIM(s)删除字符串s结尾处的空格函数名函数功能AVG([ALL|DISTINCT]expression)计算某一字段的平均值（此字段的值必须是数值型）COUNT([ALL|DISTINCT]expression)统计某一字段的个数MAX([ALL|DISTINCT]expression)查找某一字段的最大值MIN([ALL|DISTINCT]expression)查找某一字段的最小值SUM([ALL|DISTINCT]expression)计算某一字段的总和（此字段的值必须是数值型）4．流程控制语句T-SQL语言也提供了一些流程控制语句，使得对数据库中数据的检索、更新、插入等操作更加方便和容易(1)BEGIN…END语句多条T-SQL语句使用BEGIN…END组合起来形成一个语句块。BEGIN…END可以嵌套使用。语法格式：BEGINSQL语句1SQL语句2…END（2）IF…ELSE语句通过判定给定的条件来决定执行哪条语句或语句块。语法格式：IF条件表达式SQL语句1[ELSESQL语句2]说明：•该语句计算条件表达式的值，如果为TRUE，则执行IF后面的语句块，否则执行ELSE后面的语句块。•如果是单分支流程，可不含ELSE。•如果条件表达式中包含SELECT语句，则必须用圆括号将SELECT语句括起来。•IF…ELSE可嵌套。【例4-55】统计学号为“05101101”的学生的选课数目，如果不少于三门课就显示“你选了××门课。很好，你完成了任务！“否则显示“你选了××门课。选课太少，加油！”。（其中××表示选课数目）。USESchoolDECLARE@cnsmallint,@textvarchar(100)SET@cn=(SELECTcount(StudentCode)FROMT_GradeWHEREStudentCode='05101101')IF@cn=3BEGINSET@text='你选了'+CAST(@cnASchar(2))/*CAST函数将@cn的值转换为长度为2的字符数据*/SET@text=@text+'门课。很好，你完成了任务！'ENDELSEBEGINSET@text='你选了'+CAST(@cnASchar(2))SET@text=@text+'门课。选课太少，加油！'ENDSELECT@textAS选课提示图4-53统计选课数目结果（3）WHILE语句实现一条SQL语句或SQL语句块重复执行。语法格式：WHILE条件表达式SQL语句1[BREAK]SQL语句2[CONTINUE]说明：•该语句计算条件表达式的值，如果为TRUE，则执行WHILE后的语句块。•BREAK为从本层WHILE循环中退出，当存在多层循环嵌套时，使用BREAK语句只能退出其所在的内层循环，然后重新开始外层的循环。•CONTINUE为结束本次循环，开始下一次循环的判断。【例4-56】计算10！。DECLARE@pint,@ismallint,@textvarchar(100)SET@p=1SET@i=1WHILE@i=10BEGINSET@p=@p*@iSET@i=@i+1ENDSET@text='1×2×3×……×10='+CAST(@pASchar(10))SELECT@textAS计算结果图4-54计算10！的结果（4）RETURN语句语法格式：RETURN表达式使程序从一个查询或存储过程中无条件返回，并返回表达式的值，其后的语句不再执行。（4）CASE语句CASE语句是多分支的选择语句。该语句具有两种形式：简单CASE函数形式：将某个表达式与一组简单表达式进行比较以确定结果。CASE搜索函数形式：计算一组条件表达式以确定结果。1）简单CASE函数语法格式：CASE输入表达式WHEN情况表达式THEN结果表达式...[ELSE结果表达式]END说明：1)当输入表达式的值与某一个WHEN子句的情况表达式的值相等时，就返回该WHEN子句中结果表达式的值。2)如果所有WHEN子句中的情况表达式的值都没有与输入表达式的值相等，则返回ELSE子句后的结果表达式的值，若没有ELSE子句，则返回NULL值。【例4-57】查询07级女同学的住校情况。SELECTStudentNameAS'姓名',CASELiveInDormWHEN0THEN'未住校'WHEN1THEN'住校'ENDAS'是否住校'FROMT_StudentWHERESex='女'ANDLEFT(StudentCode,2)='07'字段：是否住校的表达2）CASE搜索函数语法格式：CASEWHEN条件表达式THEN结果表达式...[ELSE结果表达式]END按顺序计算WHEN子句的条件表达式，当布尔表达式的值为TRUE，则返回THEN后面的结果表达式的值，然后跳出CASE语句。【例4-58】统计每个学生平均成绩并划分等级。SELECTStudentCodeAS'学号',STR(AVG(Grade),5,2)AS'平均成绩',CASEWHENAVG(Grade)=90THEN'A'WHENAVG(Grade)=80THEN'B'WHENAVG(Grade)=70THEN'C'WHENAVG(Grade)=60THEN'D'WHENAVG(Grade)60THEN'E'ENDAS'等级'FROMT_GradeGROUPBYStudentCodeSELECT图4-56成绩等级部分结果5．注释T-SQL中提供两种注释方法，分别用于单行和多行注释。（1）单行注释（--）语法格式：--注释文本内容文本字符串与要执行的代码可同处一行，也可另起一行。（2）多行注释（/*…*/）语法格式：/*注释文本内容*/例如：USESchool--打开School数据库.SELECT*FROMT_Student/*执行一个SELECT语句，显示学生表中所有学生信息*/4.4视图(1)视图是一个虚拟表，其内容来自对表查询的基础上。(2)视图的行和列数据来自于定义视图的查询所引用的表，并在引用表时动态生成。(3)视图的结构和显示的数据行并没有实际地以视图结构存储在数据库中，而是存储在视图所引用的基本表中。(4)视图集中、简化和自定义每个用户对数据库的不同认识,是数据库的外模式。用户可以将数据库中自己感兴趣的特定数据展现在视图中。(5)视图也可作为一种安全机制，允许用户通过视图访问数据，而不授予用户直接访问视图基础表的权限。数据库的三级模式结构数据库应用1应用2应用1应用2外视图A外视图B…………外模式B外模式A逻辑视图内视图模式内模式视图示例：课程号课程名任课教师学号课程号成绩学号姓名性别出生日期班号学生成绩视图05101101陈佳迪男1987-9-21051011学生表100002数学分析王章课程表0510110110000290选课表陈佳迪数学分析90姓名课程名成绩学生生日视图陈佳迪1987-9-21姓名05101101陈佳迪男1987-9-21051011学生表姓名性别出生日期班号姓名出生日期4.4.1创建视图创建视图的方法：1）在企业管理器中创建；2）使用T-SQL的CREATEVIEW语句。使用企业管理器创建好视图后，可以使用“生成SQL脚本”功能查看和保存其对应的SQL定义语句。创建视图时必须遵循以下原则：只能在当前数