SQLServer数据库学习汇总
数据库不区分大小写。
临时表(tempDB)：
创建：create table #table(id int)
局部临时表以#开头命名表，链接断开时临时表即被删除，局部临时表是有当前用户创建的，并且只有当前用户的会话才可以访问,且只能在同一个批处理中使用该临时变量。
全局临时表以##开头命名表。全局可访问
使用方式： (使用前可以不用创建该表，会自动根据查询结果创建表结构)
select * into ##table from table
insert into ##table select * from table
判断是否存在该表：
1. if exists (select * from tempdb.dbo.sysobjects where name='##table')
2. IF object_id('tempdb.dbo.##table') is null
命名规则：
变量名：类型缩写+类型名 chrFirstName
数据库对象名：pr+存储过程名 prHelloWorld
tr+触发器名+修改语句类型（_IU\_D） trOrder_IU
标识符：以字母或者_、@、#开始。
如果含有不合法字符如空格或者标识符是系统关键字。使用[],""包围。如[user] ,"Iden id"
单引号分隔标识符，双引号分隔字符串。
数据类型：
字符串：char、varchar、text (8000,8000,2GB)
unicode字符串：nchar,nvarchar,ntext ，针对该类型，插入或者执行的字符串使用N''来标记为unicode类型
整数：int，smallint,tinyint,bigint,bit(4,2,1,8字节,1位)
近似数字：real,float 精确度13,15位。
decimal,numeric，精确度38位 weight decimal(5,2) 表示精确度5位，小数位2位
货币数据类型： money,smallmoney (8,4字节) 货币类型: $1220
二进制:binary,varbinary,image (8000,8000,2GB)
timestamp:用于存储用作记录版本数字的二进制，记录每次更新时，都会更新。在数据库中唯一
uniqueindentifier:16字节的二进制。Guid
表变量：table 如下：
declare @mytable table (id int primary key,lookup varchar(50))
这样就形成一个临时表。可以查询并返回数据给客户，作为临时表存在。
不过该变量是非持久对象，使用rollback Transaction 语句之后不能对它回滚
通常用于需要用于作为中间处理的存储对象。
alter 存在则修改，不存在则创建
drop 删除
create 创建
变量：
局部变量：一个存储过程不能访问其他存储过程中定义的变量，需要建立持久对象可以使用表或临时表。
声明变量：
Declare @Education varchar(50),
@AspId tinyint output,
@id int = 0
变量赋值：
SELECT:
不查询直接赋值:select @Education='database',
@tinyint=0
查询赋值:
select @Education=Equipment.Make from Equipment
注意事项：
1.查询返回最后一条记录，查询结果为空，变量在查询前已经赋值，则值不变，否则为NULL
2.不能再赋值中返回一个结果集。例如：
select @eqtype=(select eqtype from eqtype )--wrong
3.向变量赋值的 SELECT 语句不能与数据检索操作结合使用。例如：
select make,@model=model from equipment--wrong
4.使用select只赋值，不返回结果集
SET:
普通：
set @Education='database'
set @tinyint=0
在update中赋值：
set @cost=cost=cost*10 后面一个等号重新设置cost的数据库中值，然后将值赋值给@cost
全局变量：
服务器维持变量的值，携带服务器或当期会话特定信息。全局变量以@@开头。
@@identity(int)：服务器中每个表都有一个可以定义为identity列，自动在这个列中生成唯一值。
@@identity用于获取当前会话生成最后一个标识值。
最好使用 ：scope_identity()
@@error(int):在每个T-sql语句之后，服务器都会对这个变量设置一个整型值。
0.表示执行成功。否则返回错误编码。
保存@@error的唯一方法就是保存在局部变量中，用于错误处理
@@rowcount:每个T-sql语句之后，返回该语句所影响到的记录条数。
@@trancount:表示事务数量，commit后就减少。
@@Fetch_Status:游标读取状态，为0表示有数据，否则无用于
流程控制语句(表达式)：
is not null,is null,not exists,exists,or , and ,<>,=,>,<...
if else,while,
begin end:按照逻辑单元对语句进行分组
goto：..GOto lable
lable:
强制服务器从lable处执行
waitfor:
注意：当服务器等待该执行语句时，连接保持冻结状态，使用SQL 代理调度作业会比waitfor time好很多.
当使用了waitfor 语句之后，所有当前都冻结。直到到了指定时间，返回执行结果。
waitfor delay '00:00:10' 等待一定时间后执行，这个例子是十秒后执行
waitfor time '22:10:00' 指定的时候执行，这个例子在22点十分执行该语句
多表连接：
对连接的表做笛卡尔乘绩
假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}。
可以把一条记录看成一个元素或者子集合，可见连接之后数量集爆增。
之后根据连接条件赛选出满足条件的数据。可以理解为筛选出：子集合中某列值相等的行。
left join:左连接，找不出相等的那行留左边
right join:右连接，找不到相等的留右边
inner join:内连接，只取出可以找到相等的那行
with B as ( select * from A )：这时B可以作为一个临时表变量使用
函数：
1.标量函数：值函数只返回一个值
(1)系统函数：
(2)日期时间函数：
(3)字符串函数：
(4)数学函数：
(5)元数据函数：
(6)安全函数：
(7)文本和图像函数：
(8)游标函数：
(9)配置函数：
(10)系统统计函数：
case:早起case被归为表达式，7.0之后归为函数
有两种形式：
1. case when object='aa' then ..
else ..
end
1. case object when 'aa' then ..
else ..
end
判断函数：
isdate():判断是否合法的日期，返回1和0
isnumeric():判断能否转换为一数据类型
nullif():nullif(rent,0),如果rent列值为0,使用null代替
ifnull():ifnull(rent,0),如果rent值为null,使用0替代
isnull():isnull(rent,0),如果null则为0
注意：统计函数通常会排出null项。如平均值时，不计算null的记录。
转换函数:
cast(data as datatype):将变量转换为另外一种类型
convert(datatype,data,style):将变量转换成指定类型，并设置类型风格
日期函数：
getdate():获取当前时间
dateadd(part,num,datetime):加减多长时间
day():获取指定日期的当月那一天。
datediff(part,starttime,endtime):获取两时间指定时间差(差的day,month,year,hour,second,minute数)
datepart(part,time):返回指定时间位置的值
字符串函数：
CHARINDEX(',',data):字符串索引。返回data第一个逗号索引。从1开始
datalength():返回字节数
len():返回字符数
left(data,length)
right(date,length):从左或右开始length长度个字符
substring(date,start,length):指定位置开始指定长度
upper(),lower():大小写转换
ltrim(),rtrim():删除左右空格符
char(),nchar():将指定整数编码转换为ascii和unicode的编码所对应字符
ascii(),unicode():返回指定字符串第一个字符的整数编码
NewID()：得到一个guid
元数据函数：主要用于或许数据库以及数据库对象的相关信息
分页取行：ROW_NUMBER() OVER (ORDER BY topics Asc)
通过这个取得从1开始的排序列
分页存储过程如：WITH B AS (
SELECT ROW_NUMBER() OVER ( ORDER BY IndexSort Desc) AS row,* FROM A )
SELECT * FROM B WHERE row BETWEEN @startindex AND @endIndex
2.聚合函数：
聚合函数用于：select 语句选择列表，having子句中,compute子句中。
聚合函数返回分组中的统计数据，除了count()外，其他聚合函数都忽略集合中记录为空的记录
3.行集函数：
主要用于连接远程服务器或者其他数据库
见p143
批处理、脚本和事务
批处理：是一个t-sql语句集，做为一个单一单元发送到服务器由服务器执行
使用go显示创建不同的批处理。
执行批处理时，分为三个步骤：解析，编译和执行
在执行一个批处理时，语句都会被解析和编译：之后执行，在执行过程中如果哪句出现错误，将暂停执行之后
的语句。同时之前执行的语句不做回滚。
DDL批处理：
ddl语句的批处理中必须单独存在。(创建视图存储过程、触发器、以及修改表格)这些都必须在单独批处理中
变量作用域：局部变量要位于同一批处理中。
注释作用域：注释范围在同一批处理中。否则出错。如/**/需要位于同一批处理中
事务：
隐式事务： 每一句t-sql都是一个事务，不出错自动提交事务到数据库
显示事务：begin transaction
if @@error<>0 goto error
commit transaction
error:
rollback transaction
return
事务命名：
begin transaction t1
commit transaction t1
rollback transaction t1
事务处理结构：
每一个更改都被写入事务日志(.ldf)，该记录用于事务回滚，或将更改提交到数据库中
包括：所执行的所有逻辑操作
更改前后的数据映像(数据副本)。
事务嵌套：
@@trancount 数目表示事务数量。
当执行了rollback 后。不管嵌套了几个事务，@@trancount数量回归0。
commit后事务数量@@trancount减一。
所以在执行rollback时判断@@trancount是否大于0，否则报错(使用事务命名也同样报错)
例子：
--删除角色形象商品关系记录
BEGIN TRANSACTION
--使用事务
DECLARE @errorSum INT
SET @errorSum = 0
DELETE FROM dbo.CharactersImagesGoodsRelation
WHERE FK_CharactersImages_ID = @CharactersImagesId
SET @errorSum = @errorSum + @@ERROR
--删除角色形象
DELETE FROM dbo.CharactersImages
WHERE AutoCode = @CharactersImagesId
SET @errorSum = @errorSum + @@ERROR
IF ( @errorSum > 0 )
GOTO ERROR--错误提交
COMMIT TRANSACTION--提交事务
ERROR:
IF ( @@trancount > 0 )
BEGIN
ROLLBACK TRANSACTION--回滚
RETURN 0
END
ELSE
RETURN 1
事务锁：
事务隔离性：某事物对数据库修改对其他事务不可见。使用锁来确保隔离性。
锁的释放：事务提交或者回滚后，锁释放。
共享锁：holdlock ，在事务中对某张表使用holdlock后。其他事务中的修改要等到该锁释放转换为排他锁为止
。这样就确保了表数据在该事务中的一致性。
排他锁：更新修改删除增加数据
触发器：
当发生指定动作后执行。
create trigger name
on tablename
after\(instead of) delete\insert\update
as
t-sql
触发器需要维持两个虚拟表：deleted :被删除的记录和更新前的旧记录
inserted: 插入的记录和更新后的记录
自定义函数
create function functionName(@arument datatype)--参数
returns int\table--数据类型
as
return --返回相应数据
游标：
1.游标
定义游标：declare tCs cursor
for select dd,ss from table
开开游标：
open tcs
读取游标值：
Fetch next from tCs into @dd,@ss --这边数据列需和定义的游标列数一致
while (@@Fetch_Status=0)--表示读取成功
begin
需要的处理过程
Fetch next from tCs into @dd,@ss--读取下一条，如果没有则@@Tetch...不为0
end
2.等效游标的处理方法
以下两种方法各有弊端：第一种，只适用于id为自增长的情况。id出现重复时，则重复的数据只处理了一条。
第二种，适用于比较小数量的处理。
获取数据中最小ID,逐条处理数据。
获取需要处理数据的最小ID：select @minid=min(id) from table
进入循环体：select * from where id=@minid--取出这条记录处理
初始化下一个最小ID:select @minId=min(id) from table where id>@minid
如果取出结果为空null: isnull(@minId,0)=0 break--跳出循环
将数据存入临时表中，此临时表设置自增长主键。通过它循环处理数据
创建表变量：
create @table table( id int identity(1,1),na varchar)
insert into @table select na from table
select @id=select min(id),@count=count(1) from @table
while @count>@id
select * from table where id=@id
处理
set @id=@id+1
