SQL基本用法

——索引
判断索引是否存在
if exists(select * from sys.indexes where name='索引名')
drop index 表名.索引名
创建索引：
create [unique（唯一索引）][clustered/nonclustered（/非聚集索引）] index 索引名 on 表名(列名) with fillfactor=填充因子
使用索引：
select * from 表名 with(index=索引名) where ……
补充：一般情况下，不需要在查询时显示指定某个索引；

——视图
判断视图是否存在
if exists(select * from sys.objects where name='视图名')
drop view 视图名
创建视图
create view 视图名 as 语句
使用视图：
select 视图中的列名 from 视图名 where 条件语句

——同义词
创建同义词
create synonym [架构名.（一般为dbo.）] 同义词名 for [服务器名.][数据库名].[基对象的架构名.]基对象名 
补充：基对象可以是：表、视图、存储过程及函数（聚合函数除外）；

——函数
判断是否存在函数：
if exists (select * from sys.objects where name='函数名')
drop function 函数名

创建函数:
——1、创建标量值函数：
create function [架构名.]函数名(@函数参数名 参数数据类型[=默认值])
returns 返回值类型
as
begin
     函数体语句
     return 返回值
end
go
使用标量值函数：select dbo.函数名(参数列表) 或使用print

——2、创建表值函数
(1)创建多语句表值函数
create function [架构名.]函数名(@函数参数名 参数数据类型[=默认值])
returns @表名 table(表结构)
as
begin
     函数体语句
     return （此处无需写值或表达式）
end
go

(2)创建内联表值函数
create function [架构名.]函数名(@函数参数名 参数数据类型[=默认值])
returns table
as
     函数体语句
     return （select语句）
go
补充：内联表值函数只能有一条select语句，且不需要定义返回table类型的变量的结构，在return关键字后面直接返回select语句的结果；

使用表值函数：可以将其作为普通表一样使用；

存储过程
判断存储过程是否存在：
if exists(select * from sys.objects where name='存储过程名'）
删除存储过程：
drop procedure 存储过程名
创建存储过程：
create procedure 存储过程名(@参数名  参数数据类型[=默认值])
as
存储过程主题语句
go

补充：可以为参数指定output关键字，表示参数为传出参数；
使用存储过程：
exec 存储过程名 @** output '值'……
或：exec 存储过程名 @**1='值'，@**2='值'……
注意：调用存储过程时，没有“()”
补充：带输出参数的存储过程，输出参数必须在存储过程定义时使用output关键字进行声明；输出参数无默认值，在调用带传出参数的存储过程时，需要先定义对应的变量作为实际参数，并且在实际参数后面必须使用output关键字。

错误处理：
raiserror ('用户自定义错误信息或特定信息',severity,state)
说明：severity指用户自定义严重性级别，可以使用的级别是0~18级，19~25级是为sysadmin固定角色的成员预留的，20~25级错误被认为是致命错误，
eg:raiserror('****',15,1)；state表示错误的状态，取值在1~127之间；

触发器
(1)DDL触发器
判断DDL触发器是否存在：
if exists (select * from sys.triggers where [parent_class=0 and] name='触发器名')
删除DDL触发器：
drop trigger 触发器名 on database/all server
创建DDL触发器：
create trigger 触发器名
on all server/database
for/after 激发触发器的语言事件 （ eg:drop_table,alter_table,drop_procedure）
补充：若禁止执行某一操作，可以在触发器中写入rollback 语句；
禁用和启用DDL触发器
disable（禁用）/enable（启用） trigger on[all server/database]

(2)DML触发器
判断DML触发器是否存在：
if exists(select * from sys.objects where name='触发器名' [and type='tr'])
删除触发器：
drop trigger 触发器名
创建触发器：
create trigger [架构名.]触发器名
on 表名/视图名
for/after/instead of
[insert][.][update][.][delete]
as
触发后的操作语句
go
补充：instead of可以对视图和表执行触发器，after只能定义在表上；[insert][.][update][.][delete]指定数据修改语句，必须至少指定一个选项；instead of 用来替代通常的触发动作；DML触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建DML触发器；
每个触发器都有两个特殊的表：inserted表和delected表，这两个表的结构与该触发器作用的表结构相同；
update触发器可以针对单个列或整个表进行更新的验证；eg:if(UPDATE (列名))；delete触发器只能作用于表上，而不能作用于列上；

——游标
1、创建游标：
declare 游标名 cursor
[local/global（全局）]  --（游标作用域）
[forward_only/scroll]  --（滚动类型）
[static/keyset/dynamic/fast_forward]  --（游标类型）
[read_only/scroll_locks/optimistic]--（锁定方式）
[type_warning]
for select语句
for update[of column_name[………n]]]
说明：forward_only指定游标只能从第一行向最后一行滚动，并且只能通过fetch next来提取数据行(不能使用fetch first)，默认情况下，游标是forward only;
static指定游标是静态的，keyset 指定游标是键集驱动游标，打开游标时的顺序是固定的；dynamic 指定游标是动态游标，在结果集中反映对数据所作的任何更改； fast_forward 指定游标是快速只进游标，指定了游标是forward only和readonly后，不能再指定fast_forward、scroll及for update，即forward only与fast_forward 是互斥的；
read_only指定游标是只读的，禁止使用游标更新数据，不能在update或delete语句的current of 子句中使用这种游标；
scroll_locks 指定通过游标进行行定位更新或删除数据一定能够成功，scroll_locks与fast_forward 不能同时使用；
optimistic 指定如果行在读入游标后被更新， 则通过游标进行定位更新或删除将失败；
type_warning 指定游标隐式转换类型时，向客户端发送警告信息，update[of columname[……n]] 定义游标的可更新列；
2 、打开游标
open 游标名
3、提取数据行
fetch 游标名
补充：
@@fetch_status:返回上一个fetch语句执行后结果的一个正整数，值为零，说明游标正确的提取到了行，非零说明提取失败；
@@cursor_rows：返回当前打开的游标中符合条件的数据行数；
fetch语句提取数据常用方法：
fetch first 提取游标中的第一行；
fetch next  提取上次提取行之后的行；
fetch prior 提取上次提取行之前的行；
fetch last  提取游标中的最后一行；
fetch absolute n :按绝对位置提取行。n 是正整数时，则从第一行开始计数；n是负整数时，则从倒数第一行开始计数；n是零，则不提取任何行；
fetch relative n:按相对位置提取上次提取行之后的第n行，n是正整数，则提取上一次所提取行之后的第n行，n是负整数时，则提取上次所提取行之前的第N行，n是零，则同一行再次被提取；
补充：
使用游标更新数据 ：更新语句 where current of 游标名
使用游标为变量赋值：fetch first/next 游标名 into @bianliang1,@bianliang2……
存储过程输出参数也可以指定为cursor数据类型，参数形式为：@参数名 cursor varying output--（参数名即表示一个游标）

4、关闭游标
close 游标名
5、释放游标
deallocate 游标名

————补充：
用语句添加约束：
1、添加限制约束：
alter table 表名 add constraint ck_列名 check(条件)
eg:alter table s add constraint ck_lie check(lie>3 and lie<=5)
2、添加默认约束：
alter table 表名 add constraint DF_列名 default('值' )for 列名
eg:alter table transinf add constraint df_transdate default(getdate()) for transdate
3、添加主外键约束：
alter table 表名 add constraint FK_名称 foreign key( 主键列名)references 外键表名（外键列名）
4、添加唯一约束，语法如下：
alter table 表名 add constraint uq_lie unique(列名)
eg:  alter table userinfo add constraint uq_userid unique(userid)