第6章 TSQL编程与应用+习题.docx
《第6章 TSQL编程与应用+习题.docx》由会员分享，可在线阅读，更多相关《第6章 TSQL编程与应用+习题.docx（64页珍藏版）》请在冰豆网上搜索。
第6章TSQL编程与应用+习题
第6章T-SQL编程与应用
6.1T-SQL语言基础
6.1.1T-SQL语言的编程功能
1.基本功能
支持ANSISQL-92标准：
DDL数据定义,DML数据操纵,DCL数据控制,DD数据字典
2.扩展功能
加入程序流程控制结构
加入局部变量，系统变量等
6.1.2标识符
1.标识符分类
常规标识符Regularidentifer（严格遵守标识符格式规则）
界定标识符Delimitedidentifer（引号’或方括号[]）
2.标识符格式规则
SQLserver7.0以前的版本，标识符长度限制在30个字符以内。
SQLserver2000的标识符：
1~128个字符；临时表名1~116个字符。
标识符的第一个字符必须是：
大、小写字母、下划线、@、#。
其中，@和#在TSQL中有专门的含义。
接下来的字符必须是符合Unicode2.0（统一码）标准的字母,或者是十进制数字,或是特殊字符@,#,_,$。
标识符不能与任何SQLServer保留字匹配。
标识符不能包含空格，或别的特殊字符。
不符合规则的标识符必须加以界定（双引号””或方括号[]）
注意：
数据库名、表名必须符合标识符规范。
3.对象命名规则
所有数据库对象的引用由下面四部分构成：
server_name.[database_name].[schema_name].object_name
|database_name.[schema_name].object_name
|schema_name.object_name
|object_name
说明：
server_name
指定链接的服务器名称或远程服务器名称。
database_name
如果对象驻留在SQLServer的本地实例中，则指定SQLServer数据库的名称。
如果对象在链接服务器中，则database_name将指定OLEDB目录。
schema_name
如果对象在SQLServer数据库中，则指定包含对象的架构的名称。
如果对象在链接服务器中，则schema_name将指定OLEDB架构名称。
object_name
对象的名称。
说明：
从SQLServer2005开始，每个对象都属于一个数据库架构。
数据库架构是一个独立于数据库用户的非重复命名空间。
您可以将架构视为对象的容器。
可以在数据库中创建和更改架构，并且可以授予用户访问架构的权限。
任何用户都可以拥有架构，并且架构所有权可以转移。
在SQLServer2000中架构和用户是没有多大的区别，我们在2000中一般是指所有者。
2005后，用户和架构开始明确的分开，架构可以理解为对象的容器或者命名空间。
2000中服务器名.数据库名.拥有者名.对象名
6.1.3注释
1.注释多行
/*fshjhfjkshfjsdhfsdjf
fsjdkfljskdlfjkldsfjkdslfjfjfj*/
2.注释单行
--ghjfghkfdjhgkfhgjfdhgkgjfdh
6.2表达式
6.2.1常量
常量是指在程序运行中值不变的量。
根据常量的类型不同分为字符型常量，整型常量，日期时间型常量、实型常量、货币常量、全局唯一标识符。
1字符串常量
字符串常量分为ASCII字符串常量、UNICODE字符串常量。
ØASCII常量：
用单引号括起来，由ASCII构成的字符串。
如，'abcde'
ØUNICODE常量：
前面有一个N，如N'abcde'。
（N在SQL92规范中表示国际语言，必须大写）
●字符串常量必须放在单引号或双引号中。
由字母、数字、下划线、特殊字符（!
@,#）组成。
●当单引号括住的字符串常量中包含单引号时，用2个单引号表示字符串中的单引号。
如，I'mZYT写作'I''mZYT'。
●T-SQL中设置SETQUOTED_IDENTIFIER{ON|OFF}设置。
当SETQUOTED_IDENTIFIER为ON时，标识符可以由双引号分隔，而文字必须由单引号分隔。
不允许用双括号括住字符串常量因为双括号括的是标识符。
SETQUOTED_IDENTIFIER为OFF时，标识符不可加引号，且必须遵守所有Transact-SQL标识符规则。
允许用双括号括住字符串常量。
MicrosoftSQL客户端和ODBC驱动程序自动使用ON。
说明：
Unicode（统一码、万国码、单一码）是一种在计算机上使用的字符编码。
它为每种语言中的每个字符设定了统一并且唯一的二进制编码，以满足跨语言、跨平台进行文本转换、处理的要求。
强烈建议：
用单括号括住字符串常量，别被双引号括的到底是标识符还是字符串搞糊涂。
2整型常量
二进制整型常量，0，1组成，如111001。
十进制整型常量，如1982
十六进制整型常量，用0x开头，如0x3e，0x，只有0x表示空十六进制数。
3日期时间型常量
用单引号将日期时间字符串扩起来。
如'july22,2007''22-july-2007'
'06-24-1983''06/24/1983''1981-05-23''19820624''1982年10月1日'
4实型常量
实型常量有定点和浮点。
如165.234，10E23
5货币常量
用货币符号开头。
如￥542324432.25。
SQLServer不强制分组，也就是每隔三个数字插一个逗号之类的。
6全局唯一标识符
全局唯一标识符（GloballyUniqueIdentificationNumbers,GUID）是16字节长的二进制数据类型，是SQLServer根据计算机网络适配器地址和主机时钟产生的唯一号码生成的全局唯一标识符。
例如：
6F9619FF-8B86-D011-B42D-00C04FC964FF即为有效的GUID值。
世界上的任何两台计算机都不会生成重复的GUID值。
GUID主要用于在拥有多个节点、多台计算机的网络或系统中，分配必须具有唯一性的标识符。
在Windows平台上，GUID应用非常广泛：
注册表、类及接口标识、数据库、甚至自动生成的机器名、目录名等。
6.2.2数据类型
在SQLServer2005中，每个列、局部变量、表达式和参数都具有一个相关的数据类型。
数据类型是一种属性，用于指定对象可保存的数据的类型：
整数数据、字符数据、货币数据、日期和时间数据、二进制字符串等。
第3章已经提到。
6.2.3变量
变量就是在程序执行过程中其值可以改变的量。
1局部变量
局部变量是作用域局限在一定范围内的T-SQL对象。
作用域：
若局部变量在一个批处理、存储过程、触发器中被声明或定义，则其作用域就在批处理、存储过程或触发器内。
（1）局部变量声明
DECLARE
{
@变量名数据类型，@变量名数据类型
}
注意：
✓变量名必须以at符（@）开头。
局部变量名必须符合有关标识符的规则。
✓数据类型：
是系统提供的类型、CLR用户定义类型或别名数据类型。
变量不能是text、ntext或image数据类型。
✓变量先声明或定义，然后就可以在SQL命令中使用。
默认初值NULL。
（2）赋值
格式：
SET@变量名=表达式
格式：
SELECT@变量名=表达式/SELECT变量名=输出值FROM表where..
说明：
变量名是除cursor,text,ntext,image外的任何类型变量名；表达式是任何有效的SQLServer表达式。
SELECT@变量名=表达式用于将单个值返回到变量中，如果表达式为列名，则返回多个。
若SELECT语句返回多个值，则将返回的最后一个值赋给变量。
若SELECT语句没有返回值，变量保留当前值；若表达式是不返回值的子查询，则变量为NULL。
例6-1：
SELECT命令赋值，执行脚本
USEeduc
GO
DECLARE@var1varchar（8）--声明局部变量
SELECT@var1='学生姓名'--为局部变量赋初始值
SELECT@var1=Sname--查询结果赋值给变量
FROMstudent
WHERESID='bj10001'
SELECT@var1as'学生姓名'--显示局部变量结果
执行结果：
例6-2：
SELECT命令赋值，多个返回值中取最后一个
USEeduc
go
DECLARE@var1varchar（8）
SELECT@var1='读者姓名'
SELECT@var1=Sname--查询结果赋值，返回的是整个列的全部值，但最后一个给变量
FROMstudent
SELECT@var1AS'读者姓名'--显示局部变量的结果
执行结果：
例6-3：
SET命令赋值
USEeduc
go
DECLARE@novarchar（10）
SET@no='Bj10001'--变量赋值
SELECTSID,Sname
FROMstudent
WHERESID=@no
执行结果：
2.全局变量
系统全局变量是SQLServer系统提供并赋值的变量。
用户不能建立全局变量，也不能用SET语句改变全局变量的值。
格式：
@@变量名
记录SQLServer服务器活动状态的一组数据，系统提供的30个全局变量。
以下是几个全局变量介绍：
@@ERROR 最后一个Ｔ－ＳＱＬ错误的错误号
@@IDENTITY 最后一个插入的标识值
@@LANGUAGE 当前使用语言的名称
@@MAX_CONNECTIONS 可以创建的同时链接的最大数目
@@ROWCOUNT 受上一个ＳＱＬ语言影响的行数
@@SERVERNAME 本地服务器的名称
@@SERVICENAME 该计算机上的ＳＱＬ服务的名称
@@TIMETICKS 当前计算机上每刻度的微秒数
@@TRANSCOUNT 当前连接打开的事务数
@@VERSION SQLServer的版本信息
注意：
全局变量由@@开始，由系统定义和维护，用户只能显示和读取，不能修改；局部变量由一个@开始，由用户定义和赋值。
全局变量总共有33个。
例6-4：
显示SQLServer的版本。
select@@version
select@@servername--本地服务器名
6.2.4函数
SQLServer2005提供了一些内置函数，用户可以使用这些函数方便的实现一些功能。
以下举例说明一些常用的函数，其他函数请参考联机手册。
1.聚合函数
聚合函数对一组值执行计算并返回单一的值。
除COUNT函数之外，聚合函数忽略空值。
聚合函数经常与SELECT语句的GROUPBY子句一同使用。
所有聚合函数都具有确定性就是任何时候用一组给定的输入值调用它们时，都返回相同的值。
仅在下列项中聚合函数允许作为表达式使用：
∙SELECT语句的选择列表（子查询或外部查询）。
∙COMPUTE或COMPUTEBY子句。
∙HAVING子句。
例：
查询出最高分的学号和最高分
USEeduc
GO
SELECTsid,grade
FROMsc
wheregrade=（selectmax（grade）fromsc）
想想为啥用子查询。
2.日期时间函数
日期时间函数对日期和时间输入值执行操作，并返回一个字符串、数字值或日期和时间值。
下表列出日期和时间函数以及它们的确定性属性。
（1）DATEADD（）：
在向指定日期加上一段时间的基础上，返回新的datetime值
语法：
DATEADD（datepart,number,date）
参数说明：
datepart
是规定应向日期的哪一部分返回新值的参数。
下表列出了Microsoft®SQLServer™识别的日期部分和缩写
number
是用来增加datepart的值。
如果指定一个不是整数的值，则将废弃此值的小数部分。
date
是返回datetime或smalldatetime值或日期格式字符串的表达式。
DECLARE@OLDTimedatetime--定义日期时间型数据
SET@OLDTime='12-02-200406:
30pm'
SELECTDATEADD（hh,4,@OldTime）
（2）DATEDIFF（）：
两时间之差
语法
DATEDIFF（datepart,startdate,enddate）
参数
datepart
是规定了应在日期的哪一部分计算差额的参数。
下表列出了Microsoft®SQLServer™识别的日期部分和缩写。
DECLARE@FirstTimedatetime,@SecondTimedatetime
SET@FirstTime='03-24-20066:
30pm'
SET@SecondTime='03-24-20066:
33pm'
SELECTDATEDIFF（ms,@FirstTime,@SecondTime）astime1--第一个参数表示毫秒
（3）DATENAME（）：
返回年月日星期等字符串。
语法
DATENAME（datepart,date）
参数
datepart
是指定应返回的日期部分的参数。
下表列出了Microsoft®SQLServer™识别的日期部分和缩写。
DECLARE@StatementDatedatetime
SET@StatementDate='2006-3-143:
00PM'
SELECTDATENAME（dw,@StatementDate）
3.字符函数
这些函数对字符串输入值执行操作，返回字符串或数字值。
（1）ASCII（）
返回字符表达式最左端字符的ASCII代码值。
DECLARE@StringTestchar（10）
SET@StringTest=ASCII（'Robin'）
SELECT@StringTest
（2）CHAR（）
将intASCII代码转换为字符的字符串函数。
DECLARE@StringTestchar（10）
SET@StringTest=ASCII（'R'）
SELECT@StringTest
selectchar（@StringTest）
（3）LEFT（）
返回从字符串左边开始指定个数的字符。
DECLARE@StringTestchar（10）
SET@StringTest='Robin'
SELECTLEFT（@StringTest,3）
（4）LOWER（）
将大写字符数据转换为小写字符数据后返回字符表达式
DECLARE@StringTestchar（10）
SET@StringTest='Robin'
SELECTLOWER（LEFT（@StringTest,3））
（5）LTRIM（）
删除起始空格后返回字符表达式
DECLARE@StringTestchar（10）
SET@StringTest='Robin'
SELECT'Start-'+LTRIM（@StringTest）,'Start-'+@StringTest
（6）RIGHT（）
返回字符串中从右边开始指定个数的integer_expression字符。
DECLARE@StringTestchar（10）
SET@StringTest='Robin'
SELECTRIGHT（@StringTest,3）
（7）RTRIM（）
截断所有尾随空格后返回一个字符串。
DECLARE@StringTestchar（10）
SET@StringTest='Robin'
SELECT@StringTest+'-End',RTRIM（@StringTest）+'-End'
（8）STR（）
由数字数据转换来的字符数据。
SELECT'A'+82
SELECT'A'+'82'
SELECT'A'+STR（82）
SELECT'A'+LTRIM（STR（82））
（9）SUBSTRING（）
求子串函数
DECLARE@StringTestchar（10）
SET@StringTest='Robin'
SELECTSUBSTRING（@StringTest,3,LEN（@StringTest））
（10）UPPER（）
返回将小写字符数据转换为大写的字符表达式。
DECLARE@StringTestchar（10）
SET@StringTest='Robin'
SELECTUPPER（@StringTest）
4.空值置换函数ISNULL（空值，指定的空值），用指定的值代替空值。
USELibrary
GO
SELECTLendnum,ISNULL（Lendnum,0）AS空值置换
FROMReader
WHEREISNULL（Lendnum,0）=0
查询结果：
6.2.5运算符
SQLServer2005的运算符和其他高级语言类似，用于指定要在一个或多个表达式中执行的操作，将变量、常量和函数连接起来。
优先级
运算符类别
所包含运算符
1
一元运算符
+（正）、-（负）、~（取反）
2
算术运算符
*（乖）、/（除）、%（取模）
3
算术字符串运算符
+（加）、-（减）、+（连接）
4
比较运算符
=（等于）、>（大于）、>=（大于等于）、<（小于）、<=（小于等于）、<>（或!
=不等于）、!
<（不小于）、!
>（不大于）
5
按位运算符
&（位与）、|（位或）、^（位异或）
6
逻辑运算符
not（非）
7
逻辑运算符
and（与）
8
逻辑运算符
all（所有）、any（任意一个）、between（两者之间）、exists（存在）、in（在范围内）、like（匹配）、or（或）、some（任意一个）
9
赋值运算符
=（赋值）
6.3批处理与脚本
6.3.1批处理？
1概念
批处理是指包含一条或多条T-SQL语句的语句组，被一次性的执行。
是作为一个单元发出的一个和多个SQL语句的集合。
SQLServer将批处理编译成一个可执行单元，称为执行计划。
批中如果某处发生编译错误，整个执行计划都无法执行。
1.批处理：
指包含一条或多条T-SQL语句的语句组，这组语句从应用程序一次性地发送到SQL server服务器执行。
2.执行单元：
SQL server服务器将批处理语句编译成一个可执行单元，这种单元称为执行单元。
3.若批处理中的某条语句编译出错，则无法执行。
若运行出错，则视情况而定。
4.书写批处理时,GO语句作为批处理命令的结束标志，当编译器读取到GO语句时，会把GO语句前的所有语句当作一个批处理，并将这些语句打包发送给服务器。
GO语句本身不是T-SQL语句的的组成部分，只是一个表示批处理结束的前端指令。
2批处理有以下规则：
（1）.create default,create rule,create trigger,createprocedure和create view等语句在同一个批处理中只能提交一个。
（2）.不能在删除一个对象之后，在同一批处理中再次引用这个对象。
（3）.不能把规则和默认值绑定到表字段或者自定义字段上之后，立即在同一批处理中使用它们。
（4）.不能定义一个check约束之后，立即在同一个批处理中使用。
（5）.不能修改表中一个字段名之后，立即在同一个批处理中引用这个新字段。
（6）.使用set语句设置的某些set选项不能应用于同一个批处理中的查询
（7）.若批处理中第一个语句是执行某个存储过程的execute语句，则execute关键字可以省略。
若该语句不是第一个语句，则必须写上。
3几种指定批处理的方法
（1）应用程序作为一个执行单元发出的所有SQL语句构成一个批处理，并生成单个执行计划。
（2）存储过程或触发器内的所有语句构成一个批处理，每个存储过程或触发器都编译为一个执行计划。
（3）由EXECUTE语句执行的字符串是一个批处理，并编译为一个执行计划。
（4）由sp_executesql存储过程执行的字符串是一个批处理，并编译为一个执行计划。
说明：
若应用程序发出的批处理过程中含有EXECUTE语句，已执行字符串或存储过程的执行计划将和包含EXECUTE语句的执行计划分开执行。
若sp_executesql存储过程所执行的字符串生成的执行计划也与包含sp_executesql调用的批处理执行计划分开执行。
若批处理中的语句激发了触发器，则触发器执行疾患将和原始的批处理分开执行。
2批处理的结束和推出
⏹批处理结束语句：
GO
作为批处理的结束标志。
也就是说当编译器执行到GO时会把GO之前的所有语句当作一个批处理来执行。
GO不是T-SQL语句。
GO命令和T-SQL语句不可在同一行，在批处理中的第一条语句后执行任何存储过程必须包含EXECUTE关键字。
局部（用户定义）变量的作用域限制在一个批处理中，不可在GO命令后引用。
在联机帮助里，GO解释为SignalstheendofabatchofTransact-SQLstatementstotheMicrosoft®SQLServer™utilities.就是一个语句的结束信号。
也可以理解为一个分段执行命令（有些地方如此解释，不太好）。
GOisnotaTransact-SQLstatement;itisacommandrecognizedbytheosqlandisqlutilitiesandSQLQueryAnalyzer
EXECUTE
功能：
执行标量值的用户定义函数、系统过程、用户定义存储过程或扩展存储过程。
同时支持Transact-SQL批处理内的字符串的执行
联机帮助里解释为ExecutesacommandstringorcharacterstringwithinaTransact-SQLbatch,oroneofthefollowingmodules:
systemstoredprocedure,user-definedstoredprocedure,scalar-valueduser-definedfunction,orextendedstoredprocedure.
⏹批处理退出语句：
RETU
升级会员 