T-SQL 高级编程（基本语法）

T-SQL 高级编程

 
/*-变量应用-*/
 --局部变量以标记@为前缀
 --全局变量以标记@@为前缀
 
 --局部变量声明
 declare @变量名 数据类型
 
 --局部变量赋值:使用set语句或select语句

 --set 赋值语句一般赋给变量指定的数据常量
 set @变量名 = 值
 
 --select 赋值语句一般用于从表中查询数据,然后再赋给变量
 --当用select赋值时,不进行筛选会将最后一条记录赋给变量
 select @变量名 = 列名值 from 表名 where 条件




 
 

   
   全局变量表
  
  
变量
含义
@@error
最后一个T-SQL错误的错误号
@@identity
最后一次插入的标识值
@@language
当前使用的语言的名称
@@max_connections
可以创建的同时连接的最大数目
@@rowcount
受上一个SQL语句影响的行数
@@servername
本地服务器的名称
@@servicename
该计算机上的SQL服务器的名称
@@timeticks
当前计算上每刻度的微秒数
@@transcount
当前连接打开的事物数
@@version
SQL
 Sever的版本信息

/*-输出语句-*/
 print convert(varchar(大小),变量或字符串) --只适合打印字符和字符串
 select  变量 as 自定义列名--适合所有类型/*-逻辑控制语句-*/--if-else条件语句
 if(条件)
  begin
   --语句块
  end
 else
  begin
   --语句块
  end
  
 --while循环语句
 while(条件)
  begin
   --语句块
  end
 
 --case多分支语句
 case 
  when 条件1 then 结果1
  when 条件2 then 结果2
  else 结果3
 end
 列名 = case 
  when 列值 then 表达式
  else 表达式
 end


/*-高级查询-*/--简单子查询
 select 列名 from 表名 where 条件列名 = (select 条件列名 from 表名 where 条件)
 
 --in和not in子查询
 select 列名 from 表名 where 条件列名 in (select 条件列名 from 表名 where 条件)
 select 列名 from 表名 where 条件列名 not in (select 条件列名 from 表名 where 条件)
 
 --exists和not exists子查询
 if exists(select * from 表名 where 条件)
  begin
   --语句块
  end
 if not exists(select * from 表名 where 条件)
  begin
   --语句块
  end


/*-事务-*/--管理事务开始事务:begin transaction 提交事务:commit transaction 
 回滚事务:rollback transaction--分类
  显示事务:用begin transaction 明确指定事务的开始
  隐式事务:通过设置set implicit_transaction on 语句,将隐式事务模式设置为开.
  当隐式事务模式操作时,SQL Server将在提交或回滚事务后自动启动新事务.无法描述事务
  的开始,只需要提交或回滚每个事务.
  自动提交事务:这是SQL Server 的默认模式,它将每条单独的T-SQL 语句视为一个事务.
  如果成功执行,则自动提交.如果错误,则自动回滚.--开始事务
 begin transaction
 --定义变量,用于累计错误
 declare @errorSum int
 --初始化变量
 set @errorSum=0
  --语句--累计错误
 set @errorSum = @errorSum + @@error
 if @errorSum <>0
  begin
   print --错误提示--回滚事务
   rollback transaction
  end
 else
  begin
   print --成功提示
 
 

--提交事务 commit transaction end

 

/*-索引-*/
--索引类型唯一索引:unique 聚集索引:clustered 非聚集索引:nonclusered
use  数据库名
go
--查找索引
if exists (select * from sysindexes where name = ' 索引名 ')
--删除索引
drop index  表名.索引名
--创建索引
create nonclustered index  索引名
on  表名 ( 索引列名 )
where fillfactor =  --大小为0-100之间的值
go

--查询索引
select * from  表名  (index =  索引名 ) where  由索引列名组成的条件


/*-创建视图-*/
use  数据库名
go
--查询视图
if exists (select * from sysobjects where name = ' 视图名 ')
--删除视图
drop view  视图名
--创建视图
create view  视图名
as
--查询语句
go

--使用视图
select * from  视图名

常用的系统存储过程

系统存储过程	说明
sp_databases	列出服务器上所有的数据库
sp_helpdb	报告有关指定数据库或所有数据库的信息
sp_renamedb 旧名,新名	更改数据库的名称
sp_tables	返回当前环境下可查询的对象的列表
sp_columns	返回某个表列的信息
sp_help	查看某个表的所有信息
sp_helpconstraint 表名	查看某个表的约束
sp_helpindex 表名	查看某个表的索引
sp_stored_procedures	列出当前环境中所有存储过程
sp_password	添加或修改登录账户的密码
sp_helptext	显示默认值、未加密的存储过程、用户自定义的存储过程、出发器或视图的实际文本
xp_cmdshell 'Dos命令'	完成DOS命名下的一些操作

--自定义存储过程
--不带参的存储过程
use 数据库名
go
--查询存储过程
if exists (select * from sysobjects where name = '存储过程')
--删除存储过程
drop procedure 存储过程名
go
--创建存储过程
create procedure 存储过程名
as
declare @变量名 数据类型
--SQL语句(一般用于查询语句)
go

--调用存储过程
exec 存储过程名


--创建带输入参数的存储过程
use 数据库名
go
--查询存储过程
if exists (select * from sysobjects where name = '存储过程')
--删除存储过程
drop procedure 存储过程名
go
--创建存储过程
create procedure 存储过程名
@变量名1 数据类型 --输入参数(当多个参数时用,隔开)
as
declare @变量名2 数据类型
--SQL语句(一般用于插入、修改、删除语句)
go

--调用存储过程
exec 存储过程名 给变量名1赋值



--创建带输出参数的存储过程
use 数据库名
go
--查询存储过程
if exists (select * from sysobjects where name = '存储过程')
--删除存储过程
drop procedure 存储过程名
go
--创建存储过程
create procedure 存储过程名
@变量名1 数据类型 output,--output关键字,否则视为输入参数
@变量名2 数据类型        --输入参数(当多个参数时用,隔开)
as
declare @变量名3 数据类型
--SQL语句(一般用于查询语句)
go

--调用存储过程
declare @变量名4 数据类型  --定义变量，用于存放存储过程变量名1的返回的结果
exec 存储过程名 @变量名4 output,@变量名2 数据类型  


/*-处理错误信息-*/
use 数据库名
go
--查询存储过程
if exists (select * from sysobjects where name = '存储过程')
--删除存储过程
drop procedure 存储过程名
go
--创建存储过程
create procedure 存储过程名
@变量名1 数据类型,
@变量名2 output 
as
declare @变量名3 数据类型
--错误处理
msg_id:在sysmessages系统表中指定的用户定义错误信息
msg_str:用户定义的特定信息,最长为255个字符
serverity:与特定信息相关联,表示用户定义的严重级别.用户可以使用0~18级,19~25级
  是sysadmin固定角色的成员预留的,并且需要指定with log选项,20~25级错
  误被认为是致命的错误
state:表示错误的状态,是1~127的值

raiserror('msg_id | msg_str',severity,state);
--SQL语句(一般用于查询语句)
go

--调用存储过程
declare @变量名4 数据类型  --定义变量，用于存放存储过程d的返回的结果
exec 存储过程名 @变量名4 output,@变量名2 数据类型