SQL Server T-SQL编程

SQL Server T-SQL编程
 
Transact-SQL（事务-结构化查询语言）
 
 由一条或多条T-SQL语句组成。
 
一、T-SQL基础
 
1、结束批处理
 
默认情况下，批处理分隔关键字为go
 
注：go 必须独占一行，不可包括其他任何字符 （包括注释）
 
不过也可在选项中自定义批处理分隔符，结束批处理时，将销毁该批处理的所有局部
 
变量，临时表和游标
 
2、切换数据库
 
use 数据库名
 
3、执行批处理
 
F5
 
4、执行存储过程
 
Exec 存储过程名 参数
 
二、格式化T-SQL代码
 
1、结束语句
 
ANSI SQL标准规定在每个命令末尾使用分号来结束它，但分号是可选的。
 
但有一些硬性规则
 
 a、try and 后不可加分号
 
 b、if条件后不可加分号
 
 c、CTE之前必须添加分号
 
2、续行
 
可以使用换行、空格
 
3、注释
 
单行：--
 
多行：/*   */ 使用这种注释的特点：可选择注释中的SQL语句执行，测试。
 
而无需首先取消注释。
 
4、调试T-SQL
 
使用select print
 
waitfor  delay  ‘00:00:02’暂停2秒执行
 
三、变量
 
T-SQL变量是使用命令declare创建的
 
：
 
declare 变量名1 数据类型，变量名2 数据类型
 
注：可用的数据类型包括创建表时可用的数据类型以及数据类型table和SQLVariant。
 
1、变量的默认值和作用域
 
变量的作用域（可用范围和生命周期）为当前批处理。
 
新声明的变量的默认值为null，在使用前需初始化。
 
go用来分隔不同的批处理。
 
2、变量的赋值
 
使用set命令和select命令
 
set命令和select命令都可以将表达式的值赋给变量
 
主要区别：
 
select可以从数据源（如表，子查询或视图）检索数据，
 
set只能获取表达式的值。
 
注：如果select语句检索多行，那么只有最后一行的数据将被赋给变量，而不会
 
导致错误。
 
如：
 
declare @tempid int,@name varchar(20)
 
set @tempid=20
 
select @name=truename from yuangong where id=3
 
select @name,@tempid
 
注：如果select 语句没有返回任何行，变量的值将不会受影响。
 
3、在SQL查询中，使用变量
 
在SQL查询中，可以使用表达式的地方都可以使用变量。
 
如：
 
declare @tempid int
 
set @tempid=1
 
select * from yuangong where id=@tempid
 
四、流程控制
 
1、if条件语句
 
格式：
 
if condintion
 
statement;
 
注：只可控制单个命令（一条语句）
 
1.1 begin/end
 
相当于程序中的 {  }
 
可以让多条命令在if的控制下执行。
 
格式：
 
if condition
 
begin
 
multiple lines;
 
end
 
1.2 if Exists()
 
在括号中使用select语句查询时，选择特定列，还是全部列，没有本质的不同
 
，但选择全部列时SQL Server能够根据索引选择最佳的列。
 
1.3if  else
 
if condition
 
...
 
else
 
...
 
2、while
 
while condition
 
begin
 
...
 
end
 
中间可使用break和continue
 
3、goto
 
label1 :
 
...
 
   goto label1
 
五、使用代码获取SQL Server信息
 
1、sp_help
 
sp_help及其变体返回有关服务器、数据库、对象、连接等方面的信息
 
如：
 
sp_help 表名
 
返回表的结构等具体信息：
 
如:
 
sp_help yuangong
 
结果：
 
Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
 
id int no 4 10    0     no (n/a) (n/a) NULL
 
bmID int no 4 10    0     no (n/a) (n/a) NULL
 
zwID int no 4 10    0     no (n/a) (n/a) NULL
 
username varchar no 50             no no no Chinese_PRC_CI_AS
 
pwd varchar no 50             no no no Chinese_PRC_CI_AS
 
truename varchar no 50             yes no yes Chinese_PRC_CI_AS
 
sex varchar no 10             yes no yes Chinese_PRC_CI_AS
 
birthday datetime no 8             yes (n/a) (n/a) NULL
 
tele varchar no 50             yes no yes Chinese_PRC_CI_AS
 
note text no 16             yes (n/a) (n/a) Chinese_PRC_CI_AS
 
rzsj datetime no 8             yes (n/a) (n/a) NULL
 
lzsj datetime no 8             yes (n/a) (n/a) NULL
 
isdelete int no 4 10    0     yes (n/a) (n/a) NULL
 
2、全局变量
 
以@@打头
 
全局变量
全局变量是SQL Server系统内部使用的变量，其作用范围并不仅仅局限于某一程序，而是任何程序均可以随时调用。全局变量通常存储一些SQL Server的配置设定值和统计数据。用户可以在程序中用全局变量来测试系统的设定值或者是Transact-SQL命令执行后的状态值。
 
使用全局变量时应该注意以下几点：
①全局变量不是由用户的程序定义的，它们是在服务器级定义的。
②用户只能使用预先定义的全局变量。
③引用全局变量时，必须以标记符“@@”开头。
④局部变量的名称不能与全局变量的名称相同，否则会在应用程序中出现不可预测的结果。
 
select @@ERROR 返回最后执行的 Transact-SQL 语句的错误代码(integer) （0，表示
 
没错误；1，表示有错误）
 
select APP_NAME ( ) as w --当前会话的应用程序
 
select @@IDENTITY   --返回最后插入的标识值
select USER_NAME()    --返回用户数据库用户名
 
SELECT @@CONNECTIONS  --返回自上次SQL启动以来连接或试图连接的次数。
SELECT GETDATE() --当前时间
SELECT @@cpu_BUSY/100  --返回自上次启动SQL 以来 CPU 的工作时间，单位为毫
 
秒
 
USE tempdb SELECT @@DBTS  as w  --为当前数据库返回当前 timestamp 数据类型
 
的值。这一 timestamp 值保证在数据库中是唯一的。
select @@IDENTITY as w --返回最后插入的标识值
SELECT @@IDLE  as w  --返回SQL自上次启动后闲置的时间，单位为毫秒
SELECT @@IO_BUSY AS w   --返回SQL自上次启动后用于执行输入和输出操作的时间，
 
单位为毫秒
SELECT @@LANGID AS w   --返回当前所使用语言的本地语言标识符(ID)。
SELECT @@LANGUAGE AS w   --返回当前使用的语言名
SELECT @@LOCK_TIMEOUT as w  --当前会话的当前锁超时设置，单位为毫秒。
SELECT @@MAX_CONNECTIONS  as w  --返回SQL上允许的同时用户连接的最大数。
 
返回的数不必为当前配置的数值
EXEC sp_configure  --显示当前服务器的全局配置设置
SELECT @@MAX_PRECISION as w --返回 decimal 和 numeric 数据类型所用的精度
 
级别，即该服务器中当前设置的精度。默认最大精度38。
select @@OPTIONS  as w  --返回当前 SET 选项的信息。
SELECT @@PACK_RECEIVED as w  --返回SQL自启动后从网络上读取的输入数据包数
 
目。
SELECT @@PACK_SENT as w  --返回SQ自上次启动后写到网络上的输出数据包数目。
 
 
SELECT @@PACKET_ERRORS as w  --返回自SQL启动后，在SQL连接上发生的网络数
 
据包错误数。
SELECT @@SERVERNAME as w --返回运行SQL服务器名称。
SELECT @@SERVICENAME  as w --返回SQL正在其下运行的注册表键名
SELECT @@TIMETICKS  as w --返回SQL服务器一刻度的微秒数
SELECT @@TOTAL_ERRORS AS w  --返回 SQL服务器自启动后，所遇到的磁盘读/写错
 
误数。
SELECT @@TOTAL_READ  as w  --返回 SQL服务器自启动后读取磁盘的次数。
SELECT @@TOTAL_WRITE as w  --返回SQL服务器自启动后写入磁盘的次数。
SELECT @@TRANCOUNT  as w  --返回当前连接的活动事务数。
SELECT @@VERSION as w  --返回SQL服务器安装的日期、版本和处理器类型。
 
六、临时表和表变量
 
1、局部临时表
 
以#开头
 
Create table #名称
 
(
 
数据列定义
 
)
 
注：临时表的生命周期很短，只在相应的创建中可见（在同一个新建查询中可见）
 
2、全局临时表
 
所有用户都可以引用全局临时表，仅当最后一个引用它的会话结束后才删除
 
以##开头，在别的新建查询中也可见，只要创建的那个一会话结束，就不可
 
再访问。
 
3、表变量
 
表变量的作用域和生命周期与局部变量相同，
 
对表变量有如下限制
 
a、不能使用语法select * into 或 insert into @tablename exec 来创建表变量
 
b、不能在函数中创建表变量
 
c、不可使用外键和check约束
 
d、不可有依据表变量的对象，如：触发器和外键作为变量来声明。
 
格式：
 
declare @表名 table
 
(
 
列定义
 
)
 
在同一个批处理中可对这个表变量操作
 
六、动态SQL
 
七、错误处理
 
7.1try ... catch
 
begin try
 
...放正常执行的代码
 
end try
 
begin catch
 
...如果发生错误执行这里的代码。
 
end  catch
 
错误函数：
 
error_message() 错误消息文本
 
error_number() 错误编号
 
error_procedure() 发生错误的存储过程或解发器的名称
 
error_severity() 错误的严重程度
 
error_state() 错误的状态