第5章 SQL Server 2008高级应用,数据库系统原理及应用,一.常量 1.字符串常量 字符串常量括在单引号内并包含字母、数字字符（a-z、A-Z 和 0-9）以及特殊字符，如!、 和#。 2数值常量 包括Integer常量、Decimal常量、Float和Real常量如果要表示一个数是正数还是负数 如： 123.45，5.6,数据库系统原理及应用,5.1 Transact-SQL程序设计,3.日期时间常量 日期时间常量使用特定格式的字符日期时间值来表示，并被单引号括起来，例如12/8/2008，December 8, 2008，21:14:20等。 4.空值 空值是表示值未知，不同于空白或零值，用Null来表示。比较两个空值或将空值与任何其他值相比均返回未知，这是因为每个空值均为未知。在往表中添加记录时，如果不对某一列赋值则系统自动让该列取空值，或者也可以在Insert语句或Update语句中显式地对某列赋空值。,数据库系统原理及应用,二.变量 变量是可以对其赋值并参与运算的一个实体，其值在运行过程中可以发生改变。 变量可以分为全局变量和局部变量两类 全局变量由系统定义并维护，局部变量由用户定义并赋值。,数据库系统原理及应用,1. 全局变量 全局变量由系统定义，通常用来跟踪服务器范围和特定会话期间的信息，不能被用户显式地定义和赋值，但是我们可以通过访问全局变量来了解系统目前的一些状态信息,数据库系统原理及应用,SQL Server中较常用的全局变量,数据库系统原理及应用,2. 声明局部变量 局部变量一般出现在批处理、存储过程和触发器中，必须在使用前用Declare 语句声明 Declare语句的语法如下： Declare local_variable AS data_type | = value , 注： 名称的第一个字符必须是。 指定变量的数据类型，可以是系统提供的数据类型或用户自定义数据类型。对于字符型变量，还可以指定长度；数值型变量，指定精度和小数位数。赋初值Null,数据库系统原理及应用,使用Set语句或SELECT语句给变量赋值 格式如下： 1）SET local_variable = expression 2）SELECT local_variable = expression,n 注： local_variable是变量的名称， expression是任何有效的SQL Server表达式，可以是常量、变量、函数和表达式，还可以是子查询。 SELECT与SET不同的是：SELECT可以一次为多个变量赋值。,Declare num int, date datetime Set num=2 Set date=Getdate() 或 Select num=2, date=Getdate(),数据库系统原理及应用,三.运算符 Microsoft SQL Server 2008提供了7中类型的运算符： 算术运算符 赋值运算符 字符串串联运算符 位运算符 比较运算符 逻辑运算符 一元运算符,数据库系统原理及应用,1.算术运算符,数据库系统原理及应用,2.赋值运算符 等号 (=) 是唯一的 Transact-SQL 赋值运算符 3. 字符串串联运算符 加号 (+) 是字符串串联运算符，可以用它将字符串串联起来。 例如good + + luck的结果是good luck,数据库系统原理及应用,4. 比较运算符,数据库系统原理及应用,5. 逻辑运算符,数据库系统原理及应用,6. 按位运算符,7. 一元运算符,常用函数,1.数学函数,数据库系统原理及应用,2.字符串函数,数据库系统原理及应用,3.日期时间函数 Getdate ( )：返回系统当前的日期和时间 Year ( date )：返回表示指定参数 date 的“年”部分的整数 Month ( date )：返回表示指定参数 date 的“月”部分的整数 Day ( date )：返回表示指定参数date 的“日”部分的整数 Datediff ( datepart , startdate , enddate )：根据指定datepart参数返回两个指定日期之间的差值 Dateadd (datepart , number , date )：根据datepart参数将一个时间间隔与指定 date 参数相加，返回一个新的 datetime 值,数据库系统原理及应用,4.Convert函数 Convert函数可以将日期数据类型的表达式强制转换为另一种数据类型的表达式。 语法格式为： Convert ( data_type ( length ) , expression , style ) 参数说明： expression ：任何有效的表达式。 data_type ：目标数据类型。 length ：指定目标数据类型长度的可选整数。 style ：用于日期时间型数据类型和字符数据类型的转换。,1 101 美国 mm/dd/yyyy 2 102 ANSI yy.mm.dd 3 103 英国/法国 dd/mm/yy 4 104 德国 dd.mm.yy 5 105 意大利 dd-mm-yy 6 106 - dd mon yy 7 107 - mon dd, yy 8 108 - hh:mm:ss - 9 或 109 (*) 默认值 + 毫秒 mon dd yyyy hh:mi:ss:mmmAM（或 PM） 10 110 美国 mm-dd-yy 11 111 日本 yy/mm/dd 12 112 ISO yymmdd - 13 或 113 (*) 欧洲默认值 + 毫秒 dd mon yyyy hh:mm:ss:mmm(24h) 14 114 - hh:mi:ss:mmm(24h),数据库系统原理及应用,5.2 批处理和流程控制语句,一.注释,数据库系统原理及应用,二. 流程控制语句 1.BeginEnd语句 用于将多条Transact-SQL语句组成一个语句块，作为一个整体来执行 语法格式为： Begin sql_statement | statement_block End,数据库系统原理及应用,2.IFELSE条件语句 语法格式为： IF Boolean_expression sql_statement|statement_block ELSE sql_statement|statement_block,数据库系统原理及应用,例查询表score，求出课程号为1的课程的平均成绩，如果平均分大于80分，输出该课程成绩较好并输出平均分，否则输出该课程成绩不太理想及对应的平均分。代码如下： USE students GO IF (SELECT AVG(score) FROM score WHERE sub_no=1)80 BEGIN PRINT 该课程成绩较好 SELECT AVG(score) FROM score WHERE sub_no=1 END ELSE BEGIN PRINT 该课程成绩不太理想 SELECT AVG(score) FROM score WHERE sub_no=1 END,数据库系统原理及应用,3. Case分支语句 Case input_expression When when_expression Then result_expression .n Else else_result_expression End,数据库系统原理及应用,4. While循环语句 语法格式如下： While Boolean_expression sql_statement | statement_block Break sql_statement | statement_block Continue sql_statement | statement_block,参数说明： Boolean_expression 为布尔表达式，用来设置循环的条件，返回True或False。如果使用Select语句，则必须用括号括起来。 sql_statement | statement_block 循环体中的SQL语句或语句块。如果是语句块，则必须使用BeginEnd语句，否则While循环只对第一条语句有效。 Break子句 导致从最内层的While循环中退出，将执行循环体后面的语句。 Continue子句 使While循环重新开始执行，忽略Continue关键字后面的任何语句。,数据库系统原理及应用,例求和1+2+3+100，语句如下： DECLARE i int, total int /*定义变量*/ SET i=1 /*给变量赋值*/ SET total=0 WHILE i101 /*指定循环条件*/ BEGIN SET total=total+i /*循环体*/ SET i=i+1 END SELECT total AS 总和,数据库系统原理及应用,5.3存储过程与触发器,5.3.1.存储过程 1存储过程概念 存储过程（Stored Procedure）是一组编译好存储在服务器上的完成特定功能T-SQL代码，是某数据库的对象。客户端应用程序可以通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行存储过程。 2存储过程优点 (1)加快系统运行速度。 (2)封装复杂操作。 (3)实现代码重用。 (4)增强安全性。 (5)减少网络流量。,3设计存储过程,CREATE PROCEDURE 定义自身可以包括任意数量和类型的 SQL 语句，不能在存储过程的任何位置使用的语句如下表：,4存储过程的建立 SQL Server 2008创建存储过程有二种方法，分别是SSMS，另一种是T-SQL,5.3.2触发器,1触发器概念 触发器是一类特殊的存储过程，它与表紧密相连，在对特定表或视图发出 UPDATE、INSERT 或 DELETE 语句时自动执行。,2触发器的作用 1.完成比约束更复杂的数据约束 2检查所做的SQL是否允许 3.修改其它数据表里的数据 4.调用更多的存储过程 5.发送SQL Mail： 6.返回自定义的错误信息 7.更改原本要操作的SQL语句 8.防止数据表构结更改或数据表被删除,3 DML触发器的分类 1. After触发器：这类触发器是在记录已经改变完之后（after），才会被激活执行，它主要是用于记录变更后的处理或检查，一旦发现错误，也可以用Rollback Transaction语句来回滚本次的操作。 2. Instead Of触发器：这类触发器一般是用来取代原本的操作，在记录变更之前发生的，它并不去执行原来SQL语句里的操作（Insert、Update、Delete），而去执行触发器本身所定义的操作。,4创建DML触发器 创建DML触发器有2种方法， 一种是用SSMS创建触发器，一种是用T-SQL语言创建触发器。,3触发器种类 (1).DML触发器 DML 触发器是当数据库服务器中发生数据操作语言（Data Manipulation Language）事件时执行的存储过程。DML 事件包括在指定表或视图中修改数据的 INSERT 语句、UPDATE 语句或 DELETE 语句。DML 触发器可以查询其他表，还可以包含复杂的 T-SQL 语句。DML 触发器又分为两大类：After 触发器和Instead of触发器 (2).DDL 触发器 当服务器或数据库中发生数据定义语言（DDL）事件时将调用这些触发器。但与DML触发器不同的是，它们不会为响应针对表或视图的UPDATE、INSERT或DELETE语句而激发，相