SQL 脚本一

//创建数据库

use master
go
if exists (select * from sysdatabases where name='stu1')
 drop database stu1
create database stu1 on
(name='stu1_data',filename='e:/project/stu1_data.mdf',size=10mb)
log on
(name='stu1_log',filename='e:/project/stu1_lgo.ldf')
go

//创建表

use stu1
go
if exists (select * from sysobjects where name='stuname')
 drop table stuname
create table stuname
(
id int PRIMARY KEY identity(1,1) not null,
uid nvarchar(50) ,
addr ntext,
dates datetime
)

 

//主键约束
ALTER TABLE stuname
 ADD CONSTRAINT PK_id PRIMARY KEY (id)

//唯一约束
ALTER TABLE stuname
 ADD CONSTRAINT UQ_stuID UNIQUE (stuID)

//默认约束
ALTER TABLE stuname
 ADD constraint DF_stuaddr DEFAULT ('地址不详') FOR addr

//默认约束
ALTER TABLE stuname
 ADD constraint DF_dates DEFAULT (getdate()) FOR dates

//添加检查check约束
ALTER TABLE stuname
    ADD CONSTRAINT CK_stuAge
        CHECK(stuAge BETWEEN 15 AND 40)

//外键约束
ALTER TABLE stuname
   ADD CONSTRAINT FK_stuNo         
       FOREIGN KEY(stuNo) REFERENCES stuInfo(stuNo)

//删除约束
ALTER TABLE stuname
drop constraint df_stuaddr

 

//SQL登录帐户
use stu1
if not exists (select *
from master.dbo.sysxlogins
where name = 'abc')
BEGIN
 exec sp_addlogin 'abc' ,'abc','stu1'
END
exec sp_grantdbaccess 'abc'
exec sp_addrolemember 'db_owner', 'abc'

 

//用户授权
USE  stuDB
GO
/*--为zhangsanDBUser分配对表stuInfo的select, insert, update权限--*/
GRANT select, insert, update
    ON  stuInfo  TO  zhangsanDBUser 
/*--为S26301DBUser分配建表的权限--*/
GRANT  create  table  TO  S26301DBUser