use master
go
if exists (select * from sysdatabases where name='MyDatabase')—判断master数据库sysdatagbases表中是否存在将要创建的数据库名
drop database MyDatabase—如果sysdatabases表中存在该数据库名,则将它删除
go
exec xp_cmdshell ‘md D:/MyDatabases'—利用存储过程创建一个文件夹用于存储数据物理文件(数据文件，日志文件),DOS命令(mkdir=md)
go
create database MyDatabase—创建数据库
on
(
name='MyDatabase_data',--指定逻辑文件名
filename='D:/MyDatabases/MyDatabase_data.mdf',--指定物理文件名
size=5mb,--初始大小
maxsize=50mb,--指定物理文件最大容量,可选
filegrowth=20%--增长量
)
log on
(
name='MyDatabase_log',--指定逻辑日志文件名
filename='D:/MyDatabases/MyDatabase_log.ldf',--指定日志物理文件名
size=5mb,--初始大小
maxsize=50mb,--指定日志物理文件最大容量,可选
filegrowth=20%--增长量
)
go
use MyDatabase
go

If exists (select * from sysobjects where name='Mytable')
drop table Mytable
go
create table Mytable
(
ID int not null identity(1,1) primary key,--标识种子1,标识增量1,设该列为主键
name nchar(10) not null,--不可为空
degree numeric(18,0)—身份证,numeric(18,0)代表18位数字,小数位数为0
)
Go

Alter table stuInfo add constraint PK_stuNo primary key(stuNo)—主键
alter table stuMarks add constraint FK_stuNo foreign key(stuNo) references stuInfo(stuNo)—外键
alter table stuInfo add constraint UQ_stuID unique(stuID)—唯一约束
alter table stuInfo add constraint df_stuAddress default(‘地址不详') for stuAddres—默认约束
alter table stuMarks add constraint ck_stuAge check(stuAge between 15 and 40)—检查约束
alter table stuMarks drop constraint ck_stuAge—删除约束

exec sp_grantlogin 'jbtraining/s26301' --windows用户为jbtraining/s26301,jbtraining 表示域
exec sp_addlogin 'admin','0533131'--SQL登录帐户,帐户: 'admin',密码:0533131.

exec sp_grantdbaccess 'jbtraining/s26301','s26301dbuser'--s26301dbuser为数据库用户名
exec sp_grantdbaccess 'admin', 's26301dbuser'--s26301dbuser为数据库名

/*为s26301dbuser分配对表mytable的select,insert,updata,delete权限*/
grant select,insert,update,delete on mytable to s26301dbuser
/*为s26301dbuser用户分配创建表的权限
grant create table to s26301dbuser

Declare @errorsum int
Set @errorsum=0
Update ……………………….
Set @errorsum=@errorsum+@@error
Update………………………..
Set @errorsum=@errorsum+@@error
If @errorsum<>0
Begin
Rollback transaction--回滚事务
End
Else
Begin
Commit transaction--提交事务
End
Go

If exists (select name from sysindexes where name='myindex')
Drop index 表名.myindex
Create nonclustered index myindex
on
student(id) with fillfactor=30
go

If exists (select * from sysobjects where name-‘myview')
Drop view myview
Go
Create view myview
As
Select 姓名=stuName,学员=sutInfo from stuInfo left join stuMarks
on stuInfo.stuNO=stuMarks.stuNo
Go

Create proc procedureName
As
SQL 语句
Go

Create proc procedureName
@number int =默认值,
@n varchar(20)
As
SQL 语句
Go
调用语法:
Exec procedureName 200,'lyh'
Exec procedureName @n='lyh'

Create proc procedureName
@number int output,
@name char(20)
As
SQL 语句
Set @number=1000
Go
调用语法
Declare @dd int
Exec procedureName @dd output,'lyh'