SQL Server 用法总结(全)

(一). 创建操作

1.创建数据库：
create database baiyunfei;

2.创建表：
create table stu_info    --创建带约束的表
(
  stu_id int identity(1,2) primary key foreign key references stu_info(stu_id), --表识列，主键约束，外键约束
  stu_xuehao int default' ' ,   --缺省约束
  stu_chengji int default' ' check(stu_chengji>=0 and stu_chengji<=100),--缺省约束，检查约束
  stu_banji char(20) default 'yiban', --缺省约束
  stu_name char(20) default' ',  --缺省约束
  stu_jiguan char(20) null unique nonclustered  --唯一约束
)

 create table course    --建表时创建级联更新
 (
 course_id int primary key,
 stu_id int foreign key references stu_info(stu_id) on update cascade,
 course_name  char(20)
 )

3.创建默认值：
create default default_name (默认值名) as
constant_expression_r(默认值，是一个常数表达式)

sp_helptext default_name(默认值名)  --查看默认值的脚本信息
sp_bindefault default_name(默认值名),'object_name'(要绑定的列名或用户自定义数据类型,若指定的是表中的某列，格式'table_name.column')；--绑定默认值
sp_unbindefault 'object_name'(格式：'table_name.column’) --解除绑定
例子：
use jwgl
go
create default profession_default as '讲师'
go
sp_bindefault profession_default,'teacher.profession'
go
sp_unbindefault 'teacher.profession'
drop default profession_default

4.创建规则:
create rule rule_name(规则名) as condition_expression
sp_helptext rule_name             --查看规则脚本信息
sp_bindrule rule_name,object_name(指定要与该规则相绑定的列名或用户自定义数据类型名，如果指定是列名，格式'table_name.column') --绑定规则
sp_unbindrule rule_name,obj_name   --解除规则
drop rule rule_name                --删除规则

5.创建标识列：
alter table stu_info
add stu_id int identity(seed,increment) --seed 标识列的种子值，increment 步长值

6.创建视图：
create view  view_stu_info with encryption(对视图定义进行加密)
as
select stu_id ,stu_name from stu_info
with check option (迫使通过试图执行的所有数据修改语句必须符合视图定义中设置的条件)

7.创建索引：
create unique(惟一索引)/ clustered(聚簇索引)/nonclustered(非聚簇索引) index index_name(索引名)
on table_name(表名)(column_name (字段列表) )    --多字段为联合索引
例子：
(创建惟一索引)
create unique clustered index book_id_index on book(book_id asc) with fillfactor=50(填充英子)
(创建联合索引)
create nonclustered index student_course_index on student_course(student_id asc ,course_id desc) with fillfactor=50

8.创建存储过程：
create procedure/proc proc_name(存储过程名) @parametr_name(参数)  type(类型) =[default](赋初值) [output](输出参数)[with encryption](对存储过程加密)[with recomile](允许重编译存储过程)
as
sql_statement

例子：
create procedure sp_stu_info   --创建存储过程
@stu_id int=null,
@stu_name char(30)=null,  --给参数付初始值
@stu_birth datetime=null,
@stu_age int=null,
@stu_banji char(30)=null,
@stu_jiguan char(30)=null,
@stu_tel char(20)=null,
@stu_scores int=null

as
if @stu_id is null or
   @stu_name is null or
   @stu_birth is null or
   @stu_banji is null or
   @stu_scores is null
begin
print '请重新输入该学生信息'
print '你必须提供学生的学号，姓名，出生日期，班级，分数'
print '联系电话，年龄，籍贯可以为空'
return
end

declare @bitbanji int
if @stu_banji='专修一班'
set @bitbanji=1
else if @stu_banji='专修二班'
set @bitbanji=2
else if @stu_banji='专修三班'
set @bitbanji=3
else
set @bitbanji=4

begin transaction  --开始事务
insert into stu_info
(stu_id,stu_name,stu_birth,stu_age,stu_banji,stu_jiguan,stu_tel,stu_scores) values
(@stu_id,@stu_name,@stu_birth,@stu_age,@bitbanji,@stu_jiguan,@stu_tel,@stu_scores)

if @@error<>0
begin
rollback tran --撤消事务
return
end
commit transaction  --结束事务

exec sp_stu_info 13,'wenhua','1985-12-23',30,'专修一班','hehe','123456',86  with recompile --重新编译存储过程并执行语句.
exec sp_stu_info 13,'wenhua','1985-12-23',30,'专修一班','hehe','123456',86   --使用存储过.
exec sp_stu_info 'wenhua','1985-12-23' output --带输出参数的存储过程在执行时应带上output参数.
sp_helptext proc_name --查看存储过程脚本信息

9.创建触发器:
create trigger trigger_name on table_name [with encryption] for update,insert,delete
as
sql_statement

例子：

if exists(select name from  sysobjects where type='TR' and name='updated')  --判断是否存在同名的触发器，如果存在则删除掉该触发器
drop trigger updated

create trigger updated   --触发器结合游标实现自动逐条更新
on salary with encryption
for update
as
declare c cursor for select emp_id from salary
declare @emp_id char(5)
open c
fetch next from c into @emp_id
update salary set work_age=(select'工龄'=round(datediff(mm, sdate,(getdate()))/12,2)  from salary whereemp_id=@emp_id)whereemp_id=@emp_id
while (@@fetch_status=0)
begin
fetch next from c into @emp_id
update salary set work_age=(select'工龄'=round(datediff(mm, sdate,(getdate()))/12,2)  from salary whereemp_id=@emp_id)whereemp_id=@emp_id
end
close c
deallocate c

10.创建游标
declare cursor_name(游标名) [scroll] (滚动游标) cursor for select_statement
for read only(只读)/update of column_name_list(可更新的字段列表)

open cursor_name -- 打开游标
fetch next(取下一行数据)/prior(取前一行数据)/first(取第一行数据)/last(取最后一条数据)/absolute(按绝对位置取数据)/relative(按相对位置取数据)
from cursor_name into fetch_target_list(变量列表)
close cursor_name --关闭游标
deallocate cursor cursor_name --释放游标

两个全局变量@@fetch_status,@@rowcount
@@fetch_status=0 表示成功完成fetch语句
@@fetch_status=-1 表示fetch语句执行有错误，或者当前游标位置已在结果集中的最后一行，结果集中不再有数据
@@fetch_status=-2 表示提取的行不存在
@@rowcount 保存着游标结果集中提取的行数,如果结果集中所有行都被提取，则@@rowcount的值就是该结果集的总行数,关闭游标时，该@@rowcount变量被删除.

11.使用事务(事务的优点，可以对数据进行回滚；缺点，建立临时回滚点，耗时长，效率低下，大数据量操作不适宜)
begin tran/transaction
sql_statement(语句序列)