T-SQL语言程序设计基础.ppt

Principle and Application of Database System,AnQing Teachers College Department of Computer & Information,数据库原理与应用 Principle and Application of Database system,安庆师范学院计算机与信息学院,Principle and Application of Database System,10 T-SQL语言程序设计基础,(1) 数据定义语言（DDL）。,Principle and Application of Database System,(2) 数据操纵语言（DML）。,Principle and Application of Database System,(3) 数据控制语言（DCL）。,Principle and Application of Database System,10.1.1 常量、变量与数据类型, 常 量,1.字符串常量,Principle and Application of Database System,2.二进制常量,二进制常量具有前辍 0x 并且是十六进制数字字符串。这些常量不使用引号。二进制常量的示例为： 0xAE 0x12Ef 0x69048AEFDD010E 0x (empty binary string),Principle and Application of Database System,3.bit 常量 bit 常量使用数字 0 或 1 表示，并且不使用引号。如果使用一个大于 1 的数字，它将被转换为 1。,Principle and Application of Database System,4. integer 常量 integer 常量由没有用引号括起来且不含小数点的一串数字表示。integer 常量必须是整数，不能包含小数点。下面是一些 integer 常量的示例：1894 2,Principle and Application of Database System,5. 实型常量,Principle and Application of Database System,6. 日期时间常量,时间格式: 14:30:24 04:24 PM,日期时间型:April 20,2000 14:30:24,Principle and Application of Database System,Principle and Application of Database System, 数据类型,1. 系统数据类型,2. 用户自定义数据类型,Principle and Application of Database System,Principle and Application of Database System,1) 利用企业管理器定义,Principle and Application of Database System,Principle and Application of Database System,2) 利用命令定义数据类型,语法格式,Principle and Application of Database System,sp_addtype student_no,char(5),not null,Principle and Application of Database System, 数 据 类 型,3. 自定义数据类型的删除 1)用企业管理器删除自定义数据类型,Principle and Application of Database System, 数 据 类 型,2) 利用命令删除自定据类型,删除student_no类型的语句为：,sp_droptype student_no,Principle and Application of Database System, 数 据 类 型,4. 利用自定义类型定义字段,Principle and Application of Database System, 数 据 类 型,CREATE TABLE student ( sno student_no PRIMARY KEY, sname char(8), ssex char(2), sbirthday smalldatetime, class char(5) ),Principle and Application of Database System, 变 量,1. 变 量,1) 标识符,(1) 常规标识符,(2) 分隔标识符:用“ “ 或 ,2) 变量的分类,(1) 全局变量,(2) 局部变量,由若干个中文、字母、数字、_、$、#构成，#不能开头，最多128字符。,Principle and Application of Database System, 变 量,2. 局部变量的使用 1) 局部变量的定义与赋值 (1) 局部变量的定义,(2) 局部变量的赋值,Principle and Application of Database System, 变 量,例如：创建局部变量var1、var2，并赋值，然后输出变量的值。,DECLARE var1 varchar(20),var2 varchar(20) SET var1=中国 SET var2=var1+是一个伟大的国家! SELECT var1,var2 GO,Principle and Application of Database System,用SELECT语句赋值 SELECT local_variable=expression,n 如果SELECT语句没有返回值，变量将保留当前值 如果expression是不返回值的标量子查询，则将变量设为NULL,Principle and Application of Database System,DECLARE var1 nvarchar(30) SELECT var1=刘丰 SELECT var1=sname FROM student WHERE sno=110 SELECT var1 AS NAME,Principle and Application of Database System, 变 量,例如：查询用于给 var1 赋值。在 student表中sno不存在，因此子查询不返回值，并将变量var1设为 NULL。,DECLARE var1 nvarchar(30) SELECT var1=刘丰 SELECT var1= (SELECT sname FROM student WHERE sno= 110 ) SELECT var1 AS NAME GO,Principle and Application of Database System, 变 量,2）局部游标变量的定义与赋值 (1) 局部游标变量的定义,Principle and Application of Database System, 变 量,Principle and Application of Database System, 变 量,Principle and Application of Database System, 变 量,(3) 游标变量的使用步骤,例如：使用游标变量,DECLARE st_CURSOR CURSOR SET st_CURSOR= CURSOR SCROLL DYNAMIC FOR SELECT sno,sname,class FROM student,Principle and Application of Database System, 变 量,OPEN st_CURSOR FETCH NEXT FROM st_CURSOR WHILE FETCH_STATUS=0 FETCH NEXT FROM st_CURSOR CLOSE st_CURSOR DEALLOCATE st_CURSOR,Principle and Application of Database System,10.2 运算符与表达式,1算术运算符,+ 、-、*、/、%,例： DECLARE a int,b int SET a=11 SET b=3 SELECT a+b AS a+b,a-b AS a-b,a*b AS a*b,a/b AS a/b,a%b AS a%b,Principle and Application of Database System,10.2 运算符与表达式,2.位运算符,Principle and Application of Database System,10.2 运算符与表达式,例如： 在maste数据库中，建立表bitop，并插入一行，然后将a字段和 b字段上的值进行位运算。,Principle and Application of Database System,10.2 运算符与表达式,Principle and Application of Database System,10.2 运算符与表达式,Principle and Application of Database System,10.2 运算符与表达式,3. 比较运算符,Principle and Application of Database System,10.2 运算符与表达式,4. 逻辑运算符,Principle and Application of Database System,10.2 运算符与表达式,5. 字符串联接运算符,例如：多个字符串的联接。,SELECT (sno+space(2)+sname) AS 学号 姓名 FROM student,Principle and Application of Database System,10.2 运算符与表达式,6. 一元运算,7. 赋值运算符,指给局部变量赋值的SET和SELECT语句中使用的“=”。,Principle and Application of Database System,10.2 运算符与表达式,8. 运算符的优先顺序,Principle and Application of Database System,10.3 流程控制语句,Principle and Application of Database System,10.3.1 IF.ELSE语句,Principle and Application of Database System,10.3.1 IF.ELSE语句,Principle and Application of Database System,10.3.1 IF.ELSE语句,例如：如果3-105课程的平均成绩大于80分，显示“3-105课程成绩还不错”，否则显示“3-105课程成绩一般”。,IF (SELECT AVG(degree) FROM score WHERE cno=3-105)80 PRINT 3-105课程成绩还不错 ELSE PRINT 3-105课程成绩一般,Principle and Application of Database System,10.3.2 WHILE、BREAK和CONTINUE语句,1. WHILE循环语句,Principle and Application of Database System,Principle and Application of Database System,DECLARE s int,i int SET s=0 SET i=1 WHILE i=100 BEGIN SET s=s+i SET i=i+1 END SELECT i,s,Principle and Application of Database System,例如:显示字符串“China“中每个字符的 ASCII 值和字符。,DECLARE position int,string char(8) SET position=1 SET string=China WHILE position =DATALENGTH(string) BEGIN SELECT ASCII(SUBSTRING(string,position,1) SELECT SUBSTRING(string,position,1) SET position=position+1 END,Principle and Application of Database System,2.BREAK语句 一般用于循环语句中，用于退出本层循环。 3. CONTINUE语句 一般用于循环语句中，结束本次循环，进行下一次循环条件的判断。,Principle and Application of Database System,10.3.3 GOTO语句,Principle and Application of Database System,DECLARE s int,i int SET s=0 SET i=1 loop: SET s=s+i SET i=i+1 IF i=100 GOTO loop SELECT i,s,Principle and Application of Database System,10.3.4 RETURN语句,用于从过程、批处理或语句块中无条件退出，不执行位于RETURN之后的语句。,Principle and Application of Database System,DECLARE avg float IF NOT EXISTS(SELECT * FROM score WHERE sno=108) GOTO label1 BEGIN PRINT 108学生的平均成绩: SELECT avg=AVG(degree) FROM score WHERE sno=108 PRINT avg RETURN END label1: PRINT 108学生无成绩,Principle and Application of Database System,CREATE PROC mypro no char(5) AS RETURN (SELECT AVG(degree) FROM score WHERE sno=no) DECLARE no char(5),avg float SET no=108 EXEC avg=mypro no SELECT no,avg,Principle and Application of Database System,10.3.5 WAITFOR语句,例如：语句设定在早上八点执行存储过程 ，添加角色Manager。,Principle and Application of Database System,BEGIN WAITFOR DELAY 00:00:05 EXEC sp_addrole Manager END,Principle and Application of Database System,10.4函数,编程语言中的函数是用于封装经常执行的逻辑的子例程。任何代码若必须执行函数所包含的逻辑，都可以调用该函数，而不必重复所有的函数逻辑。 SQL Server 2000支持两种函数类型：内置函数和用户定义函数。,Principle and Application of Database System,10.4.1内置函数,聚合函数 行集函数,Principle and Application of Database System,1. 数学函数,Principle and Application of Database System,例如：下面程序返回给定角的 ACOS 值。,Principle and Application of Database System,例如：下面程序通过 RAND 函数产生随机值。,Principle and Application of Database System,2. 字符串处理函数,Principle and Application of Database System,例如：返回课程名最左边的3 个字。,SELECT LEFT(cname,3) FROM course,Principle and Application of Database System,例如：使用 LTRIM 字符删除字符变量中的起始空格。,DECLARE string varchar(40) SET string= 中国是一个古老而又伟大的国家！ SELECT LTRIM(string),Principle and Application of Database System,例如：用 REPLACE实现字符串的替换。,DECLARE str1 char(20),str2 char(20),str3 char(20) SET str1=数据库原理 SET str2=原理 SET str3=应用 SELECT REPLACE(str1,str2,str3),Principle and Application of Database System,DECLARE str1 char(20) SET str1=数据库原理 SELECT substring(str1,1,2),Principle and Application of Database System,例如：下面程序用于查询101学生的平均成绩。,SELECT 101学生的平均成绩为:+STR(AVG(degree) FROM score WHERE sno=101,Principle and Application of Database System,3. 系统函数,1) CASE函数,Principle and Application of Database System,Principle and Application of Database System,【例】查询score表sno,sname,degree列，对degree列按以下规则进行转换；若degree为90100，替换为“优秀”，若degree为80 89，替换为“良好”，若degree在70 79之间，替换为“中等”，若degree为60 69之间，替换为“及格”，若degree为0 59之间，替换为“不及格”，列标题更改为“evaluation”。,Principle and Application of Database System,SELECT sno, cno, evaluation= CASE WHEN degree=90 AND degree=80 and degree=70 and degree=60 and degree=69 THEN 及格 ELSE 不及格 END FROM score,Principle and Application of Database System,2) CAST 和 CONVERT函数,将日期型转换为字符型 将数值型转换为字符型 将money或smallmoney转换为字符型,Principle and Application of Database System,例如：下面程序将检索成绩大于80分的选课记录，并将成绩转换为 char(20)。,-用CAST实现 SELECT sno,cno,CAST(degree AS CHAR(20) FROM score WHERE degree80 -用CONVERT实现 SELECT sno,cno,CONVERT(CHAR(20),degree) FROM score WHERE degree80,Principle and Application of Database System,日期型转换为字符型时style的常用取值,Principle and Application of Database System,DECLARE date SMALLDATETIME SET date=getdate() SELECT CONVERT(CHAR(30),date,0),Principle and Application of Database System,float或real型转换为字符型时style的常用取值,Principle and Application of Database System,-浮点型转换成字符型 DECLARE float FLOAT SET float=12354.23666666666666666 SELECT CONVERT(CHAR(30),float,0),Principle and Application of Database System,money或smallmoney型转换为字符型时style的常用取值,Principle and Application of Database System,-货币型转换成字符型 SELECT CONVERT(CHAR(30),$12354.236,0),Principle and Application of Database System,4. 日期时间函数,Principle and Application of Database System,datepart的取值,Principle and Application of Database System,SELECT GETDATE() SELECT DATEPART(yy,2005-10-20 10:49:21:023) SELECT DATEDIFF(yy,2005-1-10 00:00:00:000,getdate(),Principle and Application of Database System,5. 游标函数,Principle and Application of Database System,Principle and Application of Database System,Principle and Application of Database System,6. 元数据函数,Principle and Application of Database System,10.4.2 用户定义函数,根据用户定义函数的返回值的类型，可将用户定义函数分为如下两类：,标量值函数：返回值为标量值（返回值的类 型为基本类型）。,表值函数：返回值为TABLE（表）。,Principle and Application of Database System,表值函数根据函数主体的定义方式，可分为： 内嵌表值函数：没有相关联的返回变量，RETURNS 子句指定的 TABLE 不附带列的列表，一般使用单个 SELECT 语句组成了函数的主体，该函数返回的表的列来自定义该函数的 SELECT 语句的 SELECT 列表。,Principle and Application of Database System,多语句表值函数：RETURNS 子句指定的 TABLE 类型带有列及其数据类型，使用一个TABLE 变量用于存储函数值返回的行。,Principle and Application of Database System,在SQL Server中，用于描述数据库对象的信息均记录在系统表中，通常把这样的表称为元数据表。例如，在数据库创建表、视图、用户函数、存储过程、触发器等对象，都要在系统表sysobjects中登记，如果该数据库对象已经存在，再对其进行定义，则会报错，因些，在定义一个数据库对象前，最好先在系统表sysobjecs中检测该对象是否已经存在，若存在，可先删除之，然后定义新的对象。,1 系统表sysobjects,Principle and Application of Database System,对象标识符,系统表sysobjects的主要字段,Principle and Application of Database System,2 用户函数的定义与调用,Principle and Application of Database System,从上述语法形式，归纳出标量函数的一般定义形式如下：,Principle and Application of Database System,函数体,指定一系列 Transact-SQL 语句定义函数的值。function_body 只用于标量函数和多语句表值函数。 在标量函数中，function_body 是一系列合起来求得标量值的 Transact-SQL 语句。 在多语句表值函数中，function_body 是一系列填充表变量的 Transact-SQL 语句。,Principle and Application of Database System,DECLARE 语句，该语句定义函数局部变量 赋值语句 控制流程语句 SELECT 语句：表达式将值赋予函数的局部变量 INSERT、UPDATE 和 DELETE 语句，这些语句修改函数的局部 table 变量,Principle and Application of Database System,【例】定义一个计算长方体体积的用户自定义函数CubicVolume。（P175）,Principle and Application of Database System,IF EXISTS(SELECT * FROM sysobjects WHERE name=CubicVolume AND type=FN) DROP FUNCTION CubicVolume GO,Principle and Application of Database System,CREATE FUNCTION CubicVolume ( CubeLength decimal(4,1),CubeWidth decimal(4,1),CubeHeight decimal(4,1) RETURNS decimal(12,3) AS BEGIN RETURN(CubeLength*CubeWidth*CubeHeight) END,Principle and Application of Database System,2) 标量函数的调用,(1) 在SELECT语句中调用,【例】调用上例定义的CubicVolume函数，计算 长、宽、高分别为6、4、3的长方体的体积。,Principle and Application of Database System,DECLARE CubeLength decimal(4,1),CubeWidth decimal(4,1),CubeHeight decimal(4,1),CubeVolume decimal(12,3) SET CubeLength=6 SET CubeWidth=4 SET CubeHeight=3 SELECT CubeVolume=dbo.CubicVolume(CubeLength,CubeWidth,CubeHeight) SELECT CubeVolume AS 长、宽、高分别为6、4、3长方体的体积为：,Principle and Application of Database System,(2) 利用EXEC语句执行,DECLARE CubeLength decimal(4,1),CubeWidth decimal(4,1),CubeHeight decimal(4,1),CubeVolume decimal(12,3) SET CubeLength=6 SET CubeWidth=4 SET CubeHeight=3 EXEC CubeVolume=dbo.CubicVolume CubeLength,CubeWidth,CubeHeight SELECT CubeVolume AS 长、宽、高分别为6、4、3长方体的体积为：,Principle and Application of Database System,内嵌表值函数可用于实现参数化视图的功能，例如，有如下视图： CREATE VIEW VIEW1 AS SELECT sno,sname FROM student WHERE class=95031,2. 内嵌表值函数,Principle and Application of Database System,2. 内嵌表值函数,Principle and Application of Database System,IF EXISTS(SELECT * FROM sysobjects WHERE name=fn_view AND type=IF) DROP FUNCTION fn_view GO,Principle and Application of Database System,CREATE FUNCTION fn_view(para char(5) RETURNS TABLE AS RETURN SELECT sno,sname FROM student WHERE class=para,Principle and Application of Database System,2) 内嵌表值函数的调用,【例5】调用fn_view()函数，查询95033班学生的学号和姓名。,SELECT * FROM fn_view(95033),Principle and Application of Database System,3. 多语句表值函数,Principle and Application of Database System,Principle and Application of Database System,【例】在school数据库中创建返回table的函数student1，通过以班号为实参，调用该函数，查询95031班所有学生的考试成绩记录，包括学号、姓名、选修的课程号、课程名及成绩。,Principle and Application of Database System,IF EXISTS(SELECT * FROM sysobjects WHERE name=student1 AND type=TF) DROP FUNCTION student1 GO,Principle and Application of Database System,CREATE FUNCTION student1(class char(5) RETURNS st TABLE (sno char(5), sname char(8), cno char(10), cname char(16), degree numeric(18,1) ),Principle and Application of Database System,AS BEGIN INSERT INTO st SELECT student.sno,sname,o,cname,degree FROM student,score,course WHERE student.sno=score.sno AND o=o AND class=class RETURN END,Principle and Application of Database System,SELECT * FROM student1(95031),Principle and Application of Database System,3. 利用企业管理器创建用户定义函数,Principle and Application of Database System,Principle and Application of Database System,4.用户函数的删除,Principle and Application of Database System,综合应用训练,对于CPXS数据库，定义完成如下功能的函数，然后对相应函数进行调用: （1）根据产品名称，查询该产品的相关信息； （2）按某年某季度统计给定产品名称的销售数量 及销 售金额； （3）根据销售商名称，统计其在某年某季度内 销售商品名称、数量及金额。,Principle and Application o