T-SQL学习笔记
下面是学习笔记：
DECLARE @ERRORNO INT /*定义一个局部变量@ERRORNO*/
INSERT INTO Student(StuNo,StuName,StuID)
VALUES('050301001','王高林','324382475454342321')
SET @ERRORNO=@@ERROR /*把错误号临时保存起来*/
IF @ERRORNO<>0 /*如果插入语句出错，输出出错信息及错误号*/
PRINT '插入错误！'+'错误号'+CONVERT(varchar,@ERRORNO)
go
select * from sysmessages
--查询错误号对应的错误信息
use master
go
select * from sysmessages
--了解数据信息有哪些表(TABLES)和表中有哪些列(COLUMNS)
select * from INFORMATION_SCHEMA.COLUMNS
--备份和还原数据库中的表，先备份数据库，再还原数据库，再使用下面的语句对数据进行还原
select * into aa from databasename.dbo.tablename
---------------------------------------------------------------------------------
print @@MAX_CONNECTIONS
print @@TOTAL_ERRORS
--返回列名
declare @tem varchar(100)
declare @object int
select @object=OBJECT_ID('AA_Enum')
select @tem=COL_NAME(@object,2) from AA_Enum
print @tem
----------------------------------------------临时表和表变量--------------------------------------------------------临时表和表变量
--可以创建本地和全局临时表。本地临时表仅在当前会话（open connection或者如果你Management Studio中试验，打开一个查询页签就代表一个会话）中可见；全局临时表在所有会话中都可见。
--本地临时表的名称前面有一个编号符 (#table_name)，而全局临时表的名称前面有两个编号符 (##table_name)(都在数据库tempdb中)。
--如果本地临时表由存储过程创建或由多个用户同时执行的应用程序创建，则 SQL Server 必须能够区分由不同用户创建的表。为此，SQL Server 在内部为每个本地临时表的表名追
--加一个数字后缀。存储在 tempdb 数据库的 sysobjects 表中的临时表，其全名由 CREATE TABLE 语句中指定的表名和系统生成的数字后缀组成。为了允许追加后缀，为本地临时表指定的表名 table_name 不能超过 116 个字符。
--除非使用 DROP TABLE 语句显式除去临时表，否则临时表将在退出其作用域时由系统自动除去：
--当存储过程完成时，将自动除去在存储过程中创建的本地临时表。由创建表的存储过程执行的所有嵌套存储过程都可以引用此表。但调用创建此表的存储过程的进程无法引用此表
--所有其它本地临时表在当前会话结束时自动除去。
--全局临时表在创建此表的会话结束且其它任务停止对其引用时自动除去。任务与表之间的关联只在单个 Transact-SQL 语句的生存周期内保持。换言之，当创建全局临时表的会话
--结束时，最后一条引用此表的 Transact-SQL 语句完成后，将自动除去此表。
--在存储过程或触发器中创建的本地临时表与在调用存储过程或触发器之前创建的同名临时表不同。如果查询引用临时表，而同时有两个同名的临时表，则不定义针对哪个表解析该
--查询。嵌套存储过程同样可以创建与调用它的存储过程所创建的临时表同名的临时表。嵌套存储过程中对表名的所有引用都被解释为是针对该嵌套过程所创建的表，
create table #tem1(
id int identity not null,
age char(3),
sex varchar(10),
primary key(id)
--CONSTRAINT pk_tem1 primary key clustered(id)
)
create table ##tem1(
id int identity not null,
age char(3),
sex varchar(10),
primary key(id)
--CONSTRAINT pk_tem1 primary key clustered(id)
)
--创建普通表并插入数据
create table temtable(
cno char(4),
PJF varchar(100),
ZGF varchar(20),
ZDF varchar(42)
--CONSTRAINT [PK_temtable] PRIMARY KEY CLUSTERED
--(
-- cno ASC
--)
)
select * from temtable
declare @i int
set @i=0
while @i<10
begin
insert into temtable values('aa'+convert(char,@i),'ss'+convert(char,@i),'dd'+convert(char,@i),'ee'+convert(char,@i))
set @i=@i+1
end
select * from temtable
--声明一个表变量，并插入数据
declare @t1 table(cno char(4),pjk varchar(100),ZGF varchar(20),ZDF varchar(42))
--!!!!!!!!!
insert into @t1 select * from temtable --表表变量写入数据
select * into #AAAtemtable from temtable --临时表写入数据
select * from #AAAtemtable
--!!!!!!!!!
select * from temtable,@t1 t where temtable.PJF=t.pjk
--临时表是利用了硬盘(tempdb数据库) ，表名变量是占用内存，因此小数据量当然是内存中的表变量更快。当大数据量时，就不能用表变量了，太耗内存了。大数据量时适合用临时表。
--临时表缺省使用硬盘，一般来说速度比较慢，那是不是就不用临时表呢？也不是，在数据量比较大的时候，如果使用表变量，会把内存耗尽，然后使用 TEMPDB的空间，这样主要还是使用硬盘空间，但同时把内存基本耗尽，增加了内存调入调出的机会，反而降低速度。这种情况建议先给TEMPDB一次分配合适的空间，然后使用临时表。
--建议：触发器、自定义函数用表变量；存储过程看情况，大部分用表变量；特殊的应用，大数据量的场合用临时表。
--表变量有明确的作用域，在定义表变量的函数、存储过程或批处理结束时，会自动清除表变量。
--在存储过程中使用表变量与使用临时表相比，减少了存储过程的重新编译量。
--涉及表变量的事务只在表变量更新期间存在。这样就减少了表变量对锁定和记录资源的需求。
--表变量需要事先知道表结构，普通临时表，只在当前会话中可用与表变量相同into一下就可以了,方便；全局临时表：可在多个会话中使用存在于temp中需显示的drop。（不知道表结构情况下临时表方便一些）
--全局临时表的功能是表变量没法达到的。
--表变量不必删除，也就不会有命名冲突，临时表特别是全局临时表用的时候必须解决命名冲突。
--应避免频繁创建和删除临时表，减少系统表资源的消耗。
--在新建临时表时，如果一次性插入数据量很大，那么可以使用select into代替create table，避免log，提高速度；如果数据量不大，为了缓和系统表的资源，建议先create table，然后insert。
--如果临时表的数据量较大，需要建立索引，那么应该将创建临时表和建立索引的过程放在单独一个子存储过程中，这样才能保证系统能够很好的使用到该临时表的索引。
--如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先truncate table，然后drop table，这样可以避免系统表的较长时间锁定。
--慎用大的临时表与其他大表的连接查询和修改，减低系统表负担，因为这种操作会在一条语句中多次使用tempdb的系统表。
----------------------------------------------游标--------------------------------------------------------游标
--游标其实可以理解成一个定义在特定数据集上的指针，我们可以控制这个指针遍历数据集。
--运用游标打印表temtable中的值
set nocount on --不返回更改行数
declare @cno char(4),
@PJF varchar(100),
@ZGF varchar(20),
@ZDF varchar(42)
declare objCursor Cursor
local Forward_Only ----声明为scroll支持游标在定义的数据集中向任何方向移动，而Forward_Only(default--more fast) 只能fetch next
--local意味着游标的生存周期只在批处理或函数或存储过程中可见，而另一个Global则对于特定上下文，全局内有效。
--------------------
--如果使用local，以go结束作用域后，此游标就不可使用了
---------------------
for
select * from temtable
--上面为定义一个游标
open objCursor
Print 'cno '+' PJF '+' ZGF '+' ZDF '
fetch next from objCursor --此处next可以替换成Last，Prior，first，absolute(n)-直接跳到某行，relative(n)-相对于目前跳几行
into @cno,@PJF,@ZGF,@ZDF
while @@FETCH_STATUS=0
begin
Print @cno+' '+' '+@PJF+' '+' '+@ZGF+' '+' '+@ZDF+' '
fetch next from objCursor
into @cno,@PJF,@ZGF,@ZDF
end
close objcursor
deallocate objcursor --释放游标
go
--运用游标修改temtable中的值
-------------------------------------------------如果要修改游标的值必须具有主键
set nocount on
declare @cno char(4),
@PJF varchar(100),
@ZGF varchar(20),
@ZDF varchar(42)
declare @i int
set @i=0
declare objCursor Cursor
local Forward_Only
for
select * from temtable
for update of PJF --说明要修改的列为PJK--如果是通过游标删除，此处可写for update，省略字段或者全部写上
--上面为定义一个游标
open objCursor
Print 'cno '+' PJF '+' ZGF '+' ZDF '
fetch next from objCursor
into @cno,@PJF,@ZGF,@ZDF
while @@FETCH_STATUS=0
begin
update temtable set PJF=PJF+CONVERT(varchar(3),@i) --根据游标修改表中的数据（也可以是delete）
where CURRENT of objcursor --定位在修改的数据行
set @i=@i+5
fetch next from objCursor
into @cno,@PJF,@ZGF,@ZDF
end
close objcursor
deallocate objcursor --释放游标
go
select * from temtable
--游标使用建议：
--如果能不用游标就尽量不用
--用完之后一定关闭和释放
--尽量不使用游标更新数据
--尽量使用Forward_Only参数
--在性能上，游标会吃更多的内存，减少可用的并发，占用宽带，锁定资源
--游标通常会比使用集合慢2-3倍，当数据量大时，会更多。
--尽量使用while，子查询，临时表，函数，表变量来替代游标
----------------------------------------------存储过程--------------------------------------------------------存储过程
--创建存储过程
go
create procedure protem --如果修改存储,就将create改为alter就行了
as
select * from temtable
--查看存储过程信息
exec sp_help protem --查看对象(此处对象可以是表等)创建时间，拥有者等
exec sp_helptext protem--查看对象文本
--删除存储过程
drop procedure protem--删除多个可以使用”，“隔开
--创建具有参数的存储过程
go
create procedure parapro @para varchar(20)--不能将create procedure语句与其它sql语句组合到批处理中
as
select * from temtable where cno=@para
exec parapro 'aa0'
--将执行结果生成真实的表
declare @t1 table(cno varchar(40),de int)--声明一个临时table变量
insert @t1
exec xp_dirtree "C:\U8SOFT" --将执行结果放入临时table变量中--xp_dirtree返回系统盘的目录树
select * into tem from @t1 where de='1'--将临时table变量中的数据导入tem表中(效果就是在数据库中复制一个真实的@t1表)
--执行cmd下面的命令，并将结果打印成文本显示出来
exec xp_cmdshell "dir *.exe"
--通过exec执行语句来实现删除数据库的功能
create database dba
declare @UFDatadb varchar(30)
set @UFDatadb='dba'
exec('drop database '+@UFDatadb)--exec可以执行sql语句
