1、陈慧萍,Principles of Database Systems （双语教学课程）,补充: TSQL,T-SQL Outline,T-SQL 简介 附加语言要素 函数 存储过程,1 T-SQL 简介,组成部分： DDL DML DCL 批处理 附加的语言元素 函数 存储过程,批处理,批处理是指包含一条或多条Transact-SQL语句的语句组，批处理中的所有Transact-SQL语句编译成一个执行计划，从应用程序一次性地发送到SQL Server数据库服务器执行。如果批处理中的某条语句发生编译错误，就导致批处理中的任何语句都无法执行。GO,2 附加语言要素,注释 变量 运算符 流程控制,
2、注释,注释是程序代码中不执行的文本字符串（也称为注解）。在SQL Server中，可以使用两种类型的注释字符： 行注释符：“-” ； 段注释符：“/* */”,变量,局部变量：由用户定义 命名：标识符 定义：Declare 标识符 类型 赋值： Set 标识符=值 Select 标识符=值 用途：函数和存储过程参数、临时变量等 全局变量：系统提供 命名：标识符,变量,二种形式的变量 局部变量：用户自定义 拥有特定数据类型的对象，它的作用范围仅限制在程序内部，用途：函数和存储过程参数、临时变量等 命名：标识符 定义：declare 变量名 类型 赋值： Set 标识符=值 Select 标识符=
3、值全局变量：系统变量 SQL Server系统内部使用的变量，任何程序均可以随时调用。 名字：标识符 服务器级定义,运算符,算术运算符：，*，/，%（取模） 赋值运算符： 位运算符 &（按位 AND） |（按位 OR） （按位互斥 OR） 比较运算符： =、=、!=、!、!； 逻辑运算符：AND，OR，NOT 字符串串联运算符： 例： SELECT abc+def,算术运算符,算术运算符,逻辑运算符,运算符的优先等级,从高到低排列： 括号：（）； 乘、除、求模运算符：*、/、%； 加减运算符：+、- ； 比较运算符：=、=、!=、!、!； 位运算符：、&、|； 逻辑运算符：NOT； 逻辑运算符
4、：AND； 逻辑运算符：OR。,流程控制,控制SQL语句、语句块或者存储过程的执行流程。 语句块：BEGIN END 分支：IF ELSE 多分支：CASE 循环：WHILE 转移：GOTO 等待：WAITFOR 返回：RETURN,语句块：BEGIN END,BEGIN sql_statement | statement_block END,分支：IF ELSE,IF Boolean_expression sql_statement | statement_block ELSE sql_statement | statement_block ,多分支：CASE,CASE input_expr
5、ession WHEN when_expression THEN result_expression .n ELSE else_result_expression END,循环：WHILE,WHILE Boolean_expression sql_statement | statement_block BREAK sql_statement | statement_block CONTINUE ,转移：GOTO,GOTO label,declare sum int, count int select sum=0, count=1 label_1:select sum=sum+countsele
6、ct count=count+1 if count=5 goto label_1 select count , sum,例,USE pubs GO WHILE (SELECT AVG(price) FROM titles) $50 BREAK ELSE CONTINUE END PRINT Too much for the market to bear,WAITFOR语句,暂时停止执行SQL语句、语句块或者存储过程等，直到所设定的时间已过或者所设定的时间已到才继续执行。,WAITFOR DELAY time | TIME time DELAY:指定时间间隔TIME: 指定某一时刻，其数据类型为
7、datetime，格式为hh:mm:ss。,RETURN语句,无条件地终止一个查询、存储过程或者批处理,RETURN integer_expression 其中，参数integer_expression为返回的整型值。存储过程可以给调用过程或应用程序返回整型值。,3 函数,用来执行一些特殊的运算 标准函数 字符串函数 日期和时间函数 数学函数 转换函数 聚合函数 其他 用户自定义函数 定义：create function,标量函数,字符串函数,字符串函数：用于对字符串进行处理 ASCII(字符表达式)：返回字符表达式最左端字符的ASCII值 CHAR(0255的整型表达式)：将ASCII码转换
8、为字符 LEFT (字符型表达式，整型表达式)：返回该字符表达式最左边给定整数个字符 RIGHT (字符型表达式，整型表达式) ：返回该字符表达式最右边给定整数个字符 SUBSTRING (字符串，表示开始位置的表达式，表示结束位置的表达式)：返回该字符串在起止位置之间的子串,字符串函数,字符串函数,日期和时间函数,GETDATE():返回当前的系统时间 DATEPART(datepart,date) :以整数形式返回给定date型数据的指定日期部分。 Datepart的取值为：year,quarter,month,dayof year,day,week,weekday,hour,minute
9、,second,millisecond DATENAME(datepart,date) :以字符串形式返回给定date型数据的指定日期部分 SELECT DATENAME(month,getdate() as 月份 DATEDIFF(datepart,start,enddate) :返回开始日期和结束日期在给定日期部分上的差值。 DAY() ，MONTH() ，YEAR(),日期和时间函数,日期和时间函数,数学函数,数学函数：在数字表达式上进行数学运算，然后将结果返回用户。常用的数学函数有： ABS（）：求绝对值函数 RAND(seed)：产生01之间的随机数 AVG（）：求平均值 COUNT
10、（）：计数 EXP（）：求自然指数值 SQRT（）：求平方根 SQUARE（）：求平方值 ROUND（数值表达式，整数）：四舍五入,数学函数,数学函数,转换函数,类型转换： 隐性转换 通过转换函数显式转换 常用的转换函数： CASTCONVERT,CAST ( expression AS data_type ),CONVERT (data_type(length),expression ,style),例,USE pubs SELECT title, ytd_sales FROM titles WHERE CAST(ytd_sales AS char(20) LIKE 15%AND type
11、= trad_cook运行结果为： Title ytd_sales - Fifty Years in Buckingham Palace Kitchens 15096,聚合函数,聚合函数可以返回整个或者几个列或者一个列的汇总数据，它常用来计算SELECT语句查询的统计值，一般与分组GROUP BY联合使用 AVG（） SUM（） MAX（） MIN（） COUNT（），COUNT（） 其它统计函数,聚合函数,聚合函数,用户自定义函数,三种类型的用户自定义函数 标量函数：返回标量值的函数。 内嵌表函数：函数包含单个SELECT语句且该语句可更新，则该函数返回的表也可更新。 多语句表函数：函数包含
12、多个SELECT语句，则该函数返回的表不可更新。,用户自定义函数的定义,定义： CREATE FUNCTION 所有者名.函数名(parameter_name as datatype)RETURNS 返回值类型 AS BEGIN函数体RETURN 标题表达式 END,例,例：定义函数：对于一给定的DNO值，查询该值在dept表中是否存在，若存在返回0，否则返回1 create function check_id(dno char(4)returns integer as begindeclare num intif exists(select DNO from dept where DNO=d
13、no)select num=0else select num=-1return numendgo,例,例：计算全体学生某门课程的平均成绩 CREATE FUNCTION average(cno char(8) RETURNS int AS BEGINDECLARE aver intSELECT aver=(SELECT avg(grade)FROM scWHERE cno=cnoGROUP BY CNO)RETURN aver END,用户自定义函数调用,标量函数的调用 所有者名.函数名（实参1，实参2，） 或：利用EXEC执行。 EXEC所有者名.函数名 形参名1=实参1， 例1：decla
14、re num intselect num=dbo.check_id(2)if num=0insert studentvalues(20040001),编写脚本技巧,查询编辑器模板的使用查询编辑器对象的使用,4 存储过程,存储过程：将一些固定的操作集中起来由SQL Server数据库服务器来完成，以实现某个任务。 采用存储过程优点： 执行速度快 模块化程序设计 减少网络通信量 保证系统安全性 分类 系统存储过程 用户自定义存储过程,存储过程的特点,类似于其他编程语言中的过程 可以接收参数，并以输出参数的形式返回多个参数 可以容纳对数据库进行查询、修改的编程语句，也可以调用其他的存储过程,用户自定
15、义存储过程,定义 向导 SQL Server管理平台 CREATE PROCEDURE调用 EXEC 存储过程名,定义存储过程,注意：创建存储过程前，应该考虑下列几个事项： 不能将 CREATE PROCEDURE 语句与其它 SQL 语句组合到单个批处理中。 创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授予其他用户。 存储过程是数据库对象，其名称必须遵守标识符规则。 只能在当前数据库中创建存储过程。 一个存储过程的最大尺寸为128M。,定义存储过程,命令格式：,CREATE PROCEDURE procedure_name parameter data_type =defaul
16、tOUTPUT ,.n AS sql_statement .n ,定义存储过程例,命令格式：,create procedure employeeadd (employeeid char(6),name char(10),birthday datetime, sex bit,address char(20),zip char(6),phonenumber char(12), emailaddress char(20),departmentid char(3) as begin insert into employees values(employeeid,name,birthday,sex,ad
17、dress,zip,phonenumber,emailaddress,departmentid) end return,调用存储过程,EXECUTE return_status= procedure_name;number|procedure_name_var parameter=value|variableOUTPUT|DEFAULT ,.n,调用存储过程例,exec employeeadd 990230,刘朝,890909,1,武汉小洪山5号,3,系统存储过程,系统存储过程是SQL Server系统创建的存储过程，它的目的在于能够方便地从系统表中查询信息，或者完成与更新数据库表相关的管理任
18、务或其它的系统管理任务。系统存储过程可以在任意一个数据库中执行。系统存储过程创建并存放于系统数据库master中，并且名称以sp_或者xp_开头。,常用的系统存储过程,Sp_help:报告有关数据库对象（sysobjects 表中列出的任何对象）、用户定义数据类型。 Sp_helptext name:显示规则、默认值、未加密的存储过程、用户定义函数、触发器或视图的文本。 Sp_addrole Sp_adduser Sp_addtype,触发器,触发器是实施复杂完整性约束和完整性的特殊类型的存储过程 触发器是一种很有效的保证数据库完整性和特殊业务规则的手段。 它的执行不是由程序调用，也不是手工启
19、动，而是由个事件来触发，当对它所保护的数据进行修改时，DBMS自动激活，以防止对数据进行不正确或不一致的修改。,SQL Server 2005触发器,两大类触发器： DML 触发器 DDL 触发器 DML触发器：当数据库中发生数据操作语言(DML)事件时要执行的操作。 DML事件：INSERT 语句、UPDATE 语句或 DELETE 语句。 用户可以针对INSERT、UPDATE或 DELETE等语句分别设置触发器，也可以针对表上的特定操作设置多个触发器。 DML触发器基于一个表创建，一张表可以有多个触发器，但可以针对多个表进行操作。,DML触发器,创建DML触发器SQL Server Ma
20、nagement Studio创建 Create trigger命令定义 激活DML触发器 ：自动 删除DML触发器,Create trigger定义DML触发器,CREATE TRIGGER trigger_name ON table|view FOR | AFTER | INSTEAD OF DELETE, INSERT , UPDATE AS sql_statement,CREATE TRIGGER trigger_name ON table INSTEAD OF INSERT AS sql_statement 功能：用 INSTEAD OF 触发器后的sql_statement代替IN
21、SERT动作,CREATE TRIGGER trigger_name ON table FOR INSERT AS sql_statement 功能：先进行插入操作，后执行sql_statement,Create trigger定义DML触发器,例：,CREATE TABLE my_table(a int NULL, b int NULL) GO CREATE TRIGGER my_trig ON my_table FOR INSERT AS IF UPDATE(b) PRINT Column b ModifiedGO,inserted和deleted表,inserted 和 deleted
22、表：临时表，存于高速缓存在中 inserted 和 deleted 表的作用： 扩展表间引用完整性 找到数据修改前后表状态的差异，并基于此差异采取行动。 deleted 表：存储 DELETE 和 UPDATE 语句所影响的旧行 Inserted表：存储 INSERT 和 UPDATE 语句所影响的新行,inserted和deleted表-例,在表之间实施参照完整性：级联删除创建如下表：s(sno,sname,sdno)dept(dno,dname,dean) 在二表中插入数据定义触发器， 使用触发器进行级联删除即： 当从dept中删除某系的信息时，同时从s表中删除所有该系的学生记录,inse
23、rted和deleted表-例,CREATE TRIGGER trig1 ON dbo.dept FOR DELETE ASbeginselect * from sdelete sfrom s where sdno in (select dno from deleted)select * from s end,inserted和deleted表-例,在表之间实施参照完整性：要求在s表中进行插入时，先验证其sdno是否已在表dept中出现，如没有，则不允许插入，否则允许插入 CREATE TRIGGER trig2 on s FOR INSERT AS beginselect sdno from insertedselect * from sif (select sdno from inserted) not in (select dno from dept)begin print insertion is stoppedrollbackendselect * from s end,Thank you !,
