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

《T-SQL语言程序设计基础.ppt》由会员分享，可在线阅读，更多相关《T-SQL语言程序设计基础.ppt（126页珍藏版）》请在三一办公上搜索。

1、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)数据

2、操纵语言（DML）。,Principle and Application of Database System,(3)数据控制语言（DCL）。,Principle and Application of Database System,10.1.1 常量、变量与数据类型,10.1.1.1 常 量,1.字符串常量,Principle and Application of Database System,2.二进制常量,二进制常量具有前辍 0 x 并且是十六进制数字字符串。这些常量不使用引号。二进制常量的示例为：0 xAE 0 x12Ef 0 x69048AEFDD010E 0 x(empty bi

3、nary 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 Sy

4、stem,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,10.1.1.2 数据类型,1.系统数据类型,2.用户自定义数据类型,Principle and Application of Database System,Principle an

5、d 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,10.1.1.2 数 据 类 型,3

6、.自定义数据类型的删除1)用企业管理器删除自定义数据类型,Principle and Application of Database System,10.1.1.2 数 据 类 型,2)利用命令删除自定据类型,删除student_no类型的语句为：,sp_droptype student_no,Principle and Application of Database System,10.1.1.2 数 据 类 型,4.利用自定义类型定义字段,Principle and Application of Database System,10.1.1.2 数 据 类 型,CREATE TABLE s

7、tudent(sno student_no PRIMARY KEY,sname char(8),ssex char(2),sbirthday smalldatetime,class char(5),Principle and Application of Database System,10.1.1.3 变 量,1.变 量,1)标识符,(1)常规标识符,(2)分隔标识符:用 或,2)变量的分类,(1)全局变量,(2)局部变量,由若干个中文、字母、数字、_、$、#构成，#不能开头，最多128字符。,Principle and Application of Database System,10.1

8、.1.3 变 量,2.局部变量的使用1)局部变量的定义与赋值(1)局部变量的定义,(2)局部变量的赋值,Principle and Application of Database System,10.1.1.3 变 量,例如：创建局部变量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

9、 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=110SELECT var1 AS NAME,Principle and Application of Database System,10.1.1.3 变 量,例如：查询

10、用于给 var1 赋值。在 student表中sno不存在，因此子查询不返回值，并将变量var1设为 NULL。,DECLARE var1 nvarchar(30)SELECT var1=刘丰SELECT var1=(SELECT sname FROM student WHERE sno=110)SELECT var1 AS NAMEGO,Principle and Application of Database System,10.1.1.3 变 量,2）局部游标变量的定义与赋值(1)局部游标变量的定义,Principle and Application of Database System

11、,10.1.1.3 变 量,Principle and Application of Database System,10.1.1.3 变 量,Principle and Application of Database System,10.1.1.3 变 量,(3)游标变量的使用步骤,例如：使用游标变量,DECLARE st_CURSOR CURSORSET st_CURSOR=CURSOR SCROLL DYNAMICFORSELECT sno,sname,class FROM student,Principle and Application of Database System,10.1

12、.1.3 变 量,OPEN st_CURSORFETCH NEXT FROM st_CURSORWHILE FETCH_STATUS=0 FETCH NEXT FROM st_CURSORCLOSE st_CURSORDEALLOCATE st_CURSOR,Principle and Application of Database System,10.2 运算符与表达式,1算术运算符,+、-、*、/、%,例：DECLARE a int,b intSET a=11SET b=3SELECT a+b AS a+b,a-b AS a-b,a*b AS a*b,a/b AS a/b,a%b AS a

13、%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 运算符与表达式,Principl

14、e 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,1

15、0.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.

16、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.WHI

17、LE循环语句,Principle and Application of Database System,Principle and Application of Database System,DECLARE s int,i intSET s=0SET i=1WHILE i=100BEGIN SET s=s+i SET i=i+1ENDSELECT i,s,Principle and Application of Database System,例如:显示字符串China中每个字符的 ASCII 值和字符。,DECLARE position int,string char(8)SET posi

18、tion=1SET string=ChinaWHILE 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 Appli

19、cation of Database System,10.3.3 GOTO语句,Principle and Application of Database System,DECLARE s int,i intSET s=0SET i=1loop:SET s=s+i SET i=i+1IF i=100 GOTO loopSELECT i,s,Principle and Application of Database System,10.3.4 RETURN语句,用于从过程、批处理或语句块中无条件退出，不执行位于RETURN之后的语句。,Principle and Application of D

20、atabase System,DECLARE avg floatIF NOT EXISTS(SELECT*FROM score WHERE sno=108)GOTO label1BEGIN PRINT 108学生的平均成绩:SELECT avg=AVG(degree)FROM score WHERE sno=108 PRINT avg RETURNENDlabel1:PRINT 108学生无成绩,Principle and Application of Database System,CREATE PROC mypro no char(5)ASRETURN(SELECT AVG(degree)

21、FROM score WHERE sno=no)DECLARE no char(5),avg floatSET no=108EXEC avg=mypro noSELECT 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 ManagerEND,Principle an

22、d Application of Database System,10.4函数,编程语言中的函数是用于封装经常执行的逻辑的子例程。任何代码若必须执行函数所包含的逻辑，都可以调用该函数，而不必重复所有的函数逻辑。SQL Server 2000支持两种函数类型：内置函数和用户定义函数。,Principle and Application of Database System,内置函数,聚合函数 行集函数,Principle and Application of Database System,1.数学函数,Principle and Application of Database System,例

23、如：下面程序返回给定角的 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 字符删

24、除字符变量中的起始空格。,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 Da

25、tabase 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 Databa

26、se 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,ev

27、aluation=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分的选课记录，并将成绩转换为

28、 char(20)。,-用CAST实现SELECT sno,cno,CAST(degree AS CHAR(20)FROM scoreWHERE degree80-用CONVERT实现SELECT sno,cno,CONVERT(CHAR(20),degree)FROM scoreWHERE degree80,Principle and Application of Database System,日期型转换为字符型时style的常用取值,Principle and Application of Database System,DECLARE date SMALLDATETIMESET dat

29、e=getdate()SELECT CONVERT(CHAR(30),date,0),Principle and Application of Database System,float或real型转换为字符型时style的常用取值,Principle and Application of Database System,-浮点型转换成字符型DECLARE float FLOATSET float=12354.23666666666666666SELECT CONVERT(CHAR(30),float,0),Principle and Application of Database Syste

30、m,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

31、()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.元数据函数,Principl

32、e and Application of Database System,10.4.2 用户定义函数,根据用户定义函数的返回值的类型，可将用户定义函数分为如下两类：,标量值函数：返回值为标量值（返回值的类 型为基本类型）。,表值函数：返回值为TABLE（表）。,Principle and Application of Database System,表值函数根据函数主体的定义方式，可分为：内嵌表值函数：没有相关联的返回变量，RETURNS 子句指定的 TABLE 不附带列的列表，一般使用单个 SELECT 语句组成了函数的主体，该函数返回的表的列来自定义该函数的 SELECT 语句的 SELE

33、CT 列表。,Principle and Application of Database System,多语句表值函数：RETURNS 子句指定的 TABLE 类型带有列及其数据类型，使用一个TABLE 变量用于存储函数值返回的行。,Principle and Application of Database System,在SQL Server中，用于描述数据库对象的信息均记录在系统表中，通常把这样的表称为元数据表。例如，在数据库创建表、视图、用户函数、存储过程、触发器等对象，都要在系统表sysobjects中登记，如果该数据库对象已经存在，再对其进行定义，则会报错，因些，在定义一个数据库对象

34、前，最好先在系统表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 Datab

35、ase System,函数体,指定一系列 Transact-SQL 语句定义函数的值。function_body 只用于标量函数和多语句表值函数。在标量函数中，function_body 是一系列合起来求得标量值的 Transact-SQL 语句。在多语句表值函数中，function_body 是一系列填充表变量的 Transact-SQL 语句。,Principle and Application of Database System,DECLARE 语句，该语句定义函数局部变量赋值语句控制流程语句SELECT 语句：表达式将值赋予函数的局部变量INSERT、UPDATE 和 DELETE

36、语句，这些语句修改函数的局部 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 CubicVolumeGO,Principle and Application of Database System,CREA

37、TE FUNCTION CubicVolume(CubeLength decimal(4,1),CubeWidth decimal(4,1),CubeHeight decimal(4,1)RETURNS decimal(12,3)ASBEGIN RETURN(CubeLength*CubeWidth*CubeHeight)END,Principle and Application of Database System,2)标量函数的调用,(1)在SELECT语句中调用,【例】调用上例定义的CubicVolume函数，计算 长、宽、高分别为6、4、3的长方体的体积。,Principle and

38、Application of Database System,DECLARE CubeLength decimal(4,1),CubeWidth decimal(4,1),CubeHeight decimal(4,1),CubeVolume decimal(12,3)SET CubeLength=6SET CubeWidth=4SET CubeHeight=3SELECT CubeVolume=dbo.CubicVolume(CubeLength,CubeWidth,CubeHeight)SELECT CubeVolume AS 长、宽、高分别为6、4、3长方体的体积为：,Principle

39、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=6SET CubeWidth=4SET CubeHeight=3EXEC CubeVolume=dbo.CubicVolume CubeLength,CubeWidth,CubeHeightSELECT CubeVolume AS 长、宽、高分别为6、4、3长方体的

40、体积为：,Principle and Application of Database System,内嵌表值函数可用于实现参数化视图的功能，例如，有如下视图：CREATE VIEW VIEW1ASSELECT 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

41、_view AND type=IF)DROP FUNCTION fn_viewGO,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 f

42、n_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

43、EXISTS(SELECT*FROM sysobjects WHERE name=student1 AND type=TF)DROP FUNCTION student1GO,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

44、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.利用企业管理器创建用户定义函数,Prin

45、ciple 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 of Da

46、tabase System,CREATE FUNCTION FU_CP(产品名称 char(10)RETURNS TABLEAS RETURN(SELECT*FROM CP WHERE 产品名称=产品名称)SELECT*FROM FU_CP(mp3),（1）：,Principle and Application of Database System,CREATE FUNCTION FU1_CPXS(YEAR INT,QUARTER INT,产品名称 char(10)RETURNS TABLEAS RETURN(SELECT 产品名称,SUM(数量)AS 销售数量,SUM(销售额)AS 销售总额

47、 FROM CPXSB,CP WHERE CPXSB.产品编号=CP.产品编号 AND 产品名称=产品名称 AND DATEPART(YY,销售日期)=YEAR AND DATEPART(QQ,销售日期)=QUARTER GROUP BY 产品名称),（2）：/*内嵌表值函数*/,Principle and Application of Database System,SELECT*FROM FU1_CPXS(2004,3,彩色电视机)SELECT*FROM FU1_CPXS(2004,1,洗衣机),Principle and Application of Database System,CR

48、EATE FUNCTION FU2_CPXS(YEAR INT,QUARTER INT,产品名称 char(10)RETURNS 销售情况 TABLE(产品名称 char(10),销售数量 int,销售金额 float)AS BEGIN INSERT INTO 销售情况 SELECT 产品名称,SUM(数量)AS 销售数量,SUM(销售额)AS 销售总额 FROM CPXSB,CP WHERE CPXSB.产品编号=CP.产品编号 AND 产品名称=产品名称 AND DATEPART(YY,销售日期)=YEAR AND DATEPART(QQ,销售日期)=QUARTER GROUP BY 产品

49、名称 RETURNEND,（2）：/*多语句表值函数*/,Principle and Application of Database System,SELECT*FROM FU2_CPXS(2004,3,彩色电视机)SELECT*FROM FU2_CPXS(2004,1,洗衣机),Principle and Application of Database System,CREATE FUNCTION FU3_CPXS(客户名称 char(10),YEAR INT,QUARTER INT)RETURNS TABLEAS RETURN(SELECT 产品名称,SUM(数量)AS 销售数量,SUM(销售额)AS 销售总额 FROM XSS,CPXSB,CP WHERE CPXSB.产品编号=CP.产品编号 AND CPXSB.客户编号=XSS.客户编号 AND 客户名称=客户名称 AND DATEPART(YY,销售日期)=YEAR AND DATEPART(QQ,销售日期)=QUARTER GROUP BY 产品名称),（3）：,Principle and Application of Database System,SELECT*FROM FU3_CPXS(广电公司,2004,1)SELECT*FROM FU3_CPXS(电器商场,2004,2),