数据库原理及应用TSQL编程与应用学习教案.pptx

会计学1数据库原理数据库原理(yunl)及应用及应用TSQL编程与应编程与应用用第一页，共40页。数据库系统概论(giln)An Introduction to Database System第8章T-SQL编程与应用 第1页/共40页第二页，共40页。教学教学(jio xu)目标：目标：n n了解了解T-SQL的基本知识，的基本知识，n n掌握表达式中典型掌握表达式中典型(dinxng)的函数应用，的函数应用，n n掌握掌握T-SQL常用的语句以及简单应用。常用的语句以及简单应用。第2页/共40页第三页，共40页。第第8章章T-SQL编程与应用编程与应用(yngyng)8.1T-SQL语言基础(jch)8.2表达式 8.3流程控制语句 8.4CASE表达式第3页/共40页第四页，共40页。8.1T-SQL语言语言(yyn)基础基础n n语言的编程功能(gngnng)n n标识符n n注释第4页/共40页第五页，共40页。注释注释(zhsh)不执行不执行(zhxng)(zhxng)语句。语句。注释多行注释多行/*fshjhfjkshfjsdhfsdjf/*fshjhfjkshfjsdhfsdjffsjdkfljskdlfjkldsfjkdslfjfjfj*/fsjdkfljskdlfjkldsfjkdslfjfjfj*/注释单行注释单行-ghjfghkfdjhgkfhgjfdhgkgjfdh-ghjfghkfdjhgkfhgjfdhgkgjfdh第5页/共40页第六页，共40页。语言语言(yyn)的编程功能的编程功能1.1.基本功能基本功能基本功能基本功能支持支持支持支持ANSI SQL-92ANSI SQL-92标准：标准：标准：标准：DDL,DML,DCL,DDDDL,DML,DCL,DD2.2.扩展功能扩展功能扩展功能扩展功能加入程序流程控制结构加入程序流程控制结构加入程序流程控制结构加入程序流程控制结构加入局部变量，系统加入局部变量，系统加入局部变量，系统加入局部变量，系统(xt(xt ng)ng)变量等变量等变量等变量等第6页/共40页第七页，共40页。标识符标识符1.1.标识符分类标识符分类常规标识符（严格遵守标识符格式规则）常规标识符（严格遵守标识符格式规则）界定标识符（引号界定标识符（引号 或方括号或方括号）2.2.标识符格式规则标识符格式规则字母或字母或_ _、开头的字母数字或、开头的字母数字或_ _、$序列序列不与保留字相同不与保留字相同长度小于长度小于128128*不符合规则的标识符必须加以界定（双引号不符合规则的标识符必须加以界定（双引号”或方括或方括号号）3.3.对象对象(duxing)(duxing)命名规则命名规则服务器名服务器名.数据库名数据库名.拥有者名拥有者名.对象对象(duxing)(duxing)名名第7页/共40页第八页，共40页。8.2表达式表达式n n数据类型n n变量(binling)n n函数n n运算符第8页/共40页第九页，共40页。数据类型数据类型n n在 SQL Server 2005 中，每个列、局部变量、表达式和参数都具有(jyu)一个相关的数据类型。数据类型是一种属性，用于指定对象可保存的数据的类型：整数数据、字符数据、货币数据、日期和时间数据、二进制字符串等。第9页/共40页第十页，共40页。变量变量(binling)n n局部变量局部变量局部变量局部变量n n作用域：批处理、存储过程或触发器内。作用域：批处理、存储过程或触发器内。作用域：批处理、存储过程或触发器内。作用域：批处理、存储过程或触发器内。n n(1)(1)声明声明声明声明(shngmng)(shngmng)n nDECLAREDECLAREn n n n 变量名变量名变量名变量名 数据类型，数据类型，数据类型，数据类型，变量名变量名变量名变量名 数据类型数据类型数据类型数据类型n n n n(2)(2)赋值赋值赋值赋值n n格式：格式：格式：格式：SELECT SELECT 变量名变量名变量名变量名=表达式表达式表达式表达式/SELECT/SELECT 子句（最后一个值或空）子句（最后一个值或空）子句（最后一个值或空）子句（最后一个值或空）第10页/共40页第十一页，共40页。变量变量(binling)例：例：SELECTSELECT命令赋值，执行脚本命令赋值，执行脚本(ji(ji obn)obn)USE LibraryUSE LibraryGOGODECLARE var1 varchar(8)-DECLARE var1 varchar(8)-声明局部变量声明局部变量SELECT var1=SELECT var1=读者姓名读者姓名 -为局部变量赋值为局部变量赋值SELECT var1=Rname -SELECT var1=Rname -查询结果赋值给变量查询结果赋值给变量FROM ReaderFROM ReaderWHERE RID=2003216008WHERE RID=2003216008SELECT var1 as SELECT var1 as 读者姓名读者姓名 -显示局部变量结果显示局部变量结果第11页/共40页第十二页，共40页。变量变量(binling)例：例：例：例：SELECTSELECT命令赋值，多个返回值中取最后命令赋值，多个返回值中取最后命令赋值，多个返回值中取最后命令赋值，多个返回值中取最后(zuhu)(zuhu)一个一个一个一个USE LibraryUSE LibraryDECLARE var1 varchar(8)DECLARE var1 varchar(8)SELECT var1=SELECT var1=读者姓名读者姓名读者姓名读者姓名 SELECT var1=Rname -SELECT var1=Rname -查询结果赋值查询结果赋值查询结果赋值查询结果赋值FROM ReaderFROM ReaderSELECT var1 AS SELECT var1 AS 读者姓名读者姓名读者姓名读者姓名 -显示局部变量的结果显示局部变量的结果显示局部变量的结果显示局部变量的结果第12页/共40页第十三页，共40页。变量变量(binling)例：例：例：例：SETSET命令命令命令命令(mng lng)(mng lng)赋值赋值赋值赋值USE LibraryUSE LibraryDECLARE no varchar(10)DECLARE no varchar(10)SET no=2004060003 -SET no=2004060003 -变量赋值变量赋值变量赋值变量赋值SELECT RID,RnameSELECT RID,RnameFROM ReaderFROM ReaderWHERE RID=noWHERE RID=noGOGO第13页/共40页第十四页，共40页。变量变量(binling)n n全局变量全局变量全局变量全局变量n n变量名变量名变量名变量名n n记录记录记录记录SQL ServerSQL Server服务器活动状态的一组数据，系统提供的服务器活动状态的一组数据，系统提供的服务器活动状态的一组数据，系统提供的服务器活动状态的一组数据，系统提供的3030个全局变量。个全局变量。个全局变量。个全局变量。n n例：显示例：显示例：显示例：显示(xi(xi nsh)SQL Servernsh)SQL Server的版本。的版本。的版本。的版本。n nSelect versionSelect version第14页/共40页第十五页，共40页。函数函数(hnsh)n nSQL Server 2005 提供了一些内置函数，用户可以使用这些函数方便(fngbin)的实现一些功能。以下举例说明一些常用的函数，其他函数请参考联机手册 第15页/共40页第十六页，共40页。函数函数(hnsh)n n聚合函数：聚合函数：聚合函数：聚合函数：COUNT COUNT，SUM SUM，AVGAVG，MAXMAX，MINMINn n例：查询出图书例：查询出图书例：查询出图书例：查询出图书(tsh)(tsh)中价格最高的图书中价格最高的图书中价格最高的图书中价格最高的图书(tsh)(tsh)n nUSE LibraryUSE Libraryn nGOGOn nSELECT MAX(Price)SELECT MAX(Price)n nFROM BookFROM Book第16页/共40页第十七页，共40页。函数函数(hnsh)n n日期日期日期日期(rq)(rq)时间函数时间函数时间函数时间函数n nDATEADD()DATEADD()：返回加上一个时间的新时间：返回加上一个时间的新时间：返回加上一个时间的新时间：返回加上一个时间的新时间n n例：返回例：返回例：返回例：返回2007-3-24 2007-3-24 下午下午下午下午3 3点点点点+4+4小时后的新时间小时后的新时间小时后的新时间小时后的新时间n nDECLARE OLDTime datetimeDECLARE OLDTime datetimen nSET OLDTime=2007-3-24 15:00:00SET OLDTime=2007-3-24 15:00:00n nSELECT DATEADD(hh,4,OldTime)SELECT DATEADD(hh,4,OldTime)第17页/共40页第十八页，共40页。函数函数(hnsh)n nDATEDIFF()DATEDIFF()：两时间之差：两时间之差：两时间之差：两时间之差n n 例：返回例：返回例：返回例：返回2006-3-24 2006-3-24 下午下午下午下午(xiw(xiw)3)3点与下午点与下午点与下午点与下午(xiw(xiw)3)3点点点点3333分的时间分的时间分的时间分的时间差，并以差，并以差，并以差，并以msms为单位显示。为单位显示。为单位显示。为单位显示。n nDECLARE FirstTime datetime,SecondTime datetimeDECLARE FirstTime datetime,SecondTime datetimen nSET FirstTime=2006-3-24 15:00:00SET FirstTime=2006-3-24 15:00:00n nSET SecondTime=2006-3-24 15:33:00SET SecondTime=2006-3-24 15:33:00n nSELECT DATEDIFF(ms,FirstTime,SecondTime)SELECT DATEDIFF(ms,FirstTime,SecondTime)第18页/共40页第十九页，共40页。函数函数(hnsh)n n字符函数字符函数字符函数字符函数(hnsh)(hnsh)n nASCII()ASCII()n n例：返回字符串的例：返回字符串的例：返回字符串的例：返回字符串的ASCIIASCII码。码。码。码。n nDECLARE StringTest char(10)DECLARE StringTest char(10)n nSET StringTest=ASCII(Robin)SET StringTest=ASCII(Robin)n nSELECT StringTestSELECT StringTest第19页/共40页第二十页，共40页。函数函数(hnsh)n n字符字符字符字符(z f)(z f)函数函数函数函数n n 例：返回下列字符例：返回下列字符例：返回下列字符例：返回下列字符(z f)(z f)中从左起中从左起中从左起中从左起3 3个字符个字符个字符个字符(z f)(z f)的小写形式。的小写形式。的小写形式。的小写形式。n nDECLARE StringTest char(10)DECLARE StringTest char(10)n nSET StringTest=Robin SET StringTest=Robin n nSELECT LOWER(LEFT(StringTest,3)SELECT LOWER(LEFT(StringTest,3)第20页/共40页第二十一页，共40页。函数函数(hnsh)n n空值置换空值置换(zhhun)(zhhun)函数函数ISNULLISNULL（空值，指定的空值），用指定的值（空值，指定的空值），用指定的值代替空值。代替空值。n n 例例2020：用：用“空值置换空值置换(zhhun)”4(zhhun)”4个中文字符代替个中文字符代替readerreader表中表中lendnumlendnum列中的空值。列中的空值。n nUSE LibraryUSE Libraryn nGOGOn nSELECT Lendnum,ISNULL(Lendnum,0)AS SELECT Lendnum,ISNULL(Lendnum,0)AS 空值置换空值置换(zhhun)(zhhun)n nFROM ReaderFROM Readern nWHERE ISNULL(Lendnum,0)=0WHERE ISNULL(Lendnum,0)=0n nGOGO第21页/共40页第二十二页，共40页。运算符运算符优先级优先级运算符类别运算符类别所包含运算符所包含运算符1一元运算符+（正）、-（负）、（取反）2算术运算符*（乖）、/（除）、%（取模）3算术字符串运算符+（加）、-（减）、+（连接）4比较运算符=（等于）、（大于）、=（大于等于）、（小于）、=（小于等于）、（或!=不等于）、!（不大于）5按位运算符&（位与）、|（位或）、（位异或）6逻辑运算符not（非）7逻辑运算符and（与）8逻辑运算符all（所有）、any（任意一个）、between（两者之间）、exists（存在）、in（在范围内）、like（匹配）、or（或）、some（任意一个）9赋值运算符=（赋值）第22页/共40页第二十三页，共40页。8.3流程流程(lichng)控制语句控制语句n n语句(yj)n n语句(yj)n n语句(yj)n n语句(yj)第23页/共40页第二十四页，共40页。语句语句(yj)n n语法：语法：n nIF Boolean_expression IF Boolean_expression /*/*条件条件(tiojin)(tiojin)表达式表达式*/*/n n sql_statement|statement_block sql_statement|statement_block /*/*条件条件(tiojin)(tiojin)表表达式为达式为TRUETRUE时执行时执行*/*/n n ELSE ELSE n n sql_statement|statement_block sql_statement|statement_block /*/*条件条件(tiojin)(tiojin)表表达式为达式为FALSEFALSE时执行时执行*/*/第24页/共40页第二十五页，共40页。语句语句(yj)n n例：例：例：例：IFIF查询查询查询查询(chxn)(chxn)图书中有没有英语书。图书中有没有英语书。图书中有没有英语书。图书中有没有英语书。n nUSE LibraryUSE Libraryn nGOGOn n-如果图书中有英语书，统计其数量，否则显示没有英语书如果图书中有英语书，统计其数量，否则显示没有英语书如果图书中有英语书，统计其数量，否则显示没有英语书如果图书中有英语书，统计其数量，否则显示没有英语书n nIF exists(SELECT*FROM Book WHERE Bname=IF exists(SELECT*FROM Book WHERE Bname=英语英语英语英语)n n SELECT COUNT(*)AS SELECT COUNT(*)AS 英语图书数量英语图书数量英语图书数量英语图书数量n n FROM Book FROM Bookn n WHERE Bname=WHERE Bname=英语英语英语英语 n nELSEELSEn n PRINT PRINT 数据库中没有英语书数据库中没有英语书数据库中没有英语书数据库中没有英语书 第25页/共40页第二十六页，共40页。语句语句(yj)USE LibraryUSE LibraryGOGOIF exists(SELECT*FROM Book WHERE Bname=IF exists(SELECT*FROM Book WHERE Bname=英语英语)SELECT COUNT(*)AS SELECT COUNT(*)AS 英语图书数量英语图书数量(shling)(shling)FROM Book FROM Book WHERE Bname=WHERE Bname=英语英语 ELSEELSE IF exists(SELECT*FROM Book WHERE Bname=SQL Server 2005 IF exists(SELECT*FROM Book WHERE Bname=SQL Server 2005基础教程基础教程)SELECT COUNT(*)AS SQLServer2005 SELECT COUNT(*)AS SQLServer2005基础教程基础教程 FROM Book FROM Book WHERE Bname=SQL Server 2005 WHERE Bname=SQL Server 2005基础教程基础教程 ELSE ELSE PRINT PRINT 英语和英语和SQL Server 2005SQL Server 2005基础教程两种书都没有！基础教程两种书都没有！第26页/共40页第二十七页，共40页。语句语句(yj)BEGING ENDBEGING END图书查询图书查询USE LibraryUSE LibraryGOGODECLARE ebook int,cbook intDECLARE ebook int,cbook intIF exists(SELECT*FROM Book WHERE Bname=IF exists(SELECT*FROM Book WHERE Bname=英语英语(yn(yn y y)BEGIN BEGIN SELECT ebook=COUNT(*)FROM Book WHERE Bname=SELECT ebook=COUNT(*)FROM Book WHERE Bname=英语英语(yn(yn y y)PRINT PRINT 英语英语(yn(yn y y)书数量书数量+RTRIM(CAST(ebook AS char(4)+RTRIM(CAST(ebook AS char(4)+册册/*CAST/*CAST：转换数据类型，：转换数据类型，RTRIMRTRIM：去右空格：去右空格*/*/END ENDELSEELSE PRINT PRINT 英语英语(yn(yn y y)书没有！书没有！IF exists(SELECT*FROM Book WHERE Bname=SQL Server 2005IF exists(SELECT*FROM Book WHERE Bname=SQL Server 2005基础教程基础教程)BEGIN BEGIN SELECT cbook=COUNT(*)SELECT cbook=COUNT(*)FROM Book FROM Book WHERE Bname=SQL Server 2005WHERE Bname=SQL Server 2005基础教程基础教程 PRINT SQL Server 2005 PRINT SQL Server 2005基础教程基础教程+RTRIM(CAST(cbook AS char(4)+RTRIM(CAST(cbook AS char(4)+册册 END ENDELSE ELSE PRINT SQL Server 2005 PRINT SQL Server 2005基础教程没有！基础教程没有！第27页/共40页第二十八页，共40页。语句语句(yj)n n语法：n nWHILE 逻辑表达式n nBeginn n T-SQL语句(yj)组n nbreak/*终止整个语句(yj)的执行*/n ncontinue/*结束一次循环体的执行*/n nEND第28页/共40页第二十九页，共40页。语句语句(yj)例例例例2424：一个小循环程序：一个小循环程序：一个小循环程序：一个小循环程序(chngx)(chngx)DECLARE X intDECLARE X intSET X=0SET X=0WHILE x3WHILE x3 BEGIN BEGIN SET x=X+1 SET x=X+1 PRINT x=+convert(char(1),x)PRINT x=+convert(char(1),x)-类型转换函数类型转换函数类型转换函数类型转换函数convertconvert END ENDGOGO第29页/共40页第三十页，共40页。语句语句(yj)n nGOTO语句将执行语句无条件跳转到标签处，并从标签位置继续处理(chl)。GOTO语句和标签可在过程、批处理(chl)或语句块中的任何位置使用。其语法格式为：n nGOTO label第30页/共40页第三十一页，共40页。语句语句(yj)n nWAITFORWAITFOR语句，称为延迟语句，设定在达到指定时间或时间间隔语句，称为延迟语句，设定在达到指定时间或时间间隔(jin g)(jin g)之前，或者指定语句至少修改或返回一行之前，阻止执行批处理、存储之前，或者指定语句至少修改或返回一行之前，阻止执行批处理、存储过程或事务。其语法格式为：过程或事务。其语法格式为：n nWAITFOR WAITFOR n n DELAY time_to_pass /*DELAY time_to_pass /*设定等待时间设定等待时间*/*/n n|TIME time_to_execute /*|TIME time_to_execute /*设定等待带某一时刻设定等待带某一时刻*/*/n n 第31页/共40页第三十二页，共40页。语句语句(yj)n n例：延迟例：延迟(ynch)30(ynch)30秒执行查询。秒执行查询。n nUSE LibraryUSE Libraryn nGOGOn nWAITFOR DELAY 00:00:30WAITFOR DELAY 00:00:30n nSELECT *FROM Reader SELECT *FROM Reader 第32页/共40页第三十三页，共40页。语句语句(yj)例：在时刻例：在时刻例：在时刻例：在时刻21:20:0021:20:00执行执行执行执行(zhxng)(zhxng)查询。查询。查询。查询。USE LibraryUSE LibraryGOGOWAITFOR TIME 21:20:00WAITFOR TIME 21:20:00SELECT *FROM ReaderSELECT *FROM Reader第33页/共40页第三十四页，共40页。语句语句(yj)n nRETURNRETURN语句从查询或过程中无条件退出。语句从查询或过程中无条件退出。RETURNRETURN的执行是即的执行是即时且完全的，可在任何时候用于从过程、批处理或语句块中退出。时且完全的，可在任何时候用于从过程、批处理或语句块中退出。RETURNRETURN之后的语句是不执行的。如果用于存储过程，之后的语句是不执行的。如果用于存储过程，RETURNRETURN不能返回空值。其语法不能返回空值。其语法(y(y f f)格式为：格式为：n nRETURN integer_expression RETURN integer_expression 第34页/共40页第三十五页，共40页。8.4CASE表达式表达式n n计算条件(tiojin)列表并返回多个可能结果表达式之一。n nCASE 具有两种格式：n n简单 CASE 函数将某个表达式与一组简单表达式进行比较以确定结果。n nCASE 搜索函数计算一组布尔表达式以确定结果 第35页/共40页第三十六页，共40页。8.4CASE表达式表达式n n简单简单(jindn)式式n nCASE 表达式表达式n n WHEN 表达式的值表达式的值1 THEN 返回表达式返回表达式1n n WHEN 表达式的值表达式的值2 THEN 返回表达式返回表达式2n n n nELSE 返回表达式返回表达式nn nEND第36页/共40页第三十七页，共40页。8.4CASE表达式表达式例：显示各读者例：显示各读者(dzh)(dzh)可借书的数量。可借书的数量。USE LibraryUSE LibraryGOGOSELECT Rname,rt.Typename AS SELECT Rname,rt.Typename AS 类型类型,限借阅量限借阅量=CASE r.TypeID CASE r.TypeID WHEN 3 THEN WHEN 3 THEN 可以借本书可以借本书!WHEN 2 THEN WHEN 2 THEN 可以借本书可以借本书!WHEN 1 THEN WHEN 1 THEN 可以借本书可以借本书!ELSE ELSE 无规定无规定 END ENDFROM Reader r,ReaderType rtFROM Reader r,ReaderType rtWHERE r.TypeID=rt.TypeIDWHERE r.TypeID=rt.TypeID第37页/共40页第三十八页，共40页。8.4CASE表达式表达式n n搜索搜索搜索搜索(su su(su su)式式式式n nCASE CASE n n WHEN WHEN 逻辑表达式逻辑表达式逻辑表达式逻辑表达式1 THEN 1 THEN 返回表达式返回表达式返回表达式返回表达式1 1n n WHEN WHEN 逻辑表达式逻辑表达式逻辑表达式逻辑表达式2 THEN 2 THEN 返回表达式返回表达式返回表达式返回表达式2 2n n n nELSE ELSE 返回表达式返回表达式返回表达式返回表达式n nn nENDEND第38页/共40页第三十九页，共40页。8.4CASE表达式表达式例：显示各读者例：显示各读者(dzh)(dzh)可借书的数量可借书的数量CASE CASE USE LibraryUSE LibraryGOGOSELECT Rname,rt.Typename AS SELECT Rname,rt.Typename AS 类型类型,限借阅量限借阅量=CASE CASE WHEN r.TypeID=3 THEN WHEN r.TypeID=3 THEN 可以借本书可以借本书!WHEN r.TypeID=2 THEN WHEN r.TypeID=2 THEN 可以借本书可以借本书!WHEN r.TypeID=1 THEN WHEN r.TypeID=1 THEN 可以借本书可以借本书!ELSE ELSE 无规定无规定 END ENDFROM Reader r,ReaderType rtFROM Reader r,ReaderType rtWHERE r.TypeID=rt.TypeIDWHERE r.TypeID=rt.TypeID第39页/共40页第四十页，共40页。