stuDB数据库脚本

/*---建数据库stuDB---*/
create database stuDB
on
(
name='stuDB_data',
filename='D:/project/stuDB_data.mdf',
size=10mb,
filegrowth=15%
)
log on
(
name='stuDB_log',
filename='D:/project/stuDB_data.ldf',
size=1mb,
maxsize=20mb,
filegrowth=15%
)
go

use stuDB
go
if exists(select * from sysobjects where name='stuInfo')
drop table stuInfo
go
/*--建表stuInfo--*/
create table stuInfo
(
stuName varchar(8) not null,
stuNo varchar(6) not null,
stuSex char(2) not null,
stuAge numeric(2,0) not null,
stuSeat numeric(2,0) not null,
stuAddress text not null
)
go
select * from stuInfo
go

/*---添加表stuInfo约束---*/
alter table stuInfo
add constraint pk_StuName primary key(stuName)

alter table stuInfo
add constraint uq_stuNo unique (stuNo)

alter table stuInfo
add constraint ck_stuNo check (stuNo like 's253__')

alter table stuInfo
add constraint ck_stuSex check (stuSex='男' or stuSex='女')

alter table stuInfo
add constraint ck_stuAge check (stuAge between 15 and 40)

alter table stuInfo
add constraint ck_stuSeat check (stuSeat between 1 and 30)

alter table stuInfo
add constraint df_stuAddress default('地址不详') for stuAddress

go

 

/*---向表stuInfo插入测试数据---*/
insert into stuInfo
(stuName,stuNO,stuSex,stuAge,stuSeat,stuAddress)
values('张丽秋','s25301','男',18,1,'北京海淀')

insert into stuInfo
(stuName,stuNO,stuSex,stuAge,stuSeat,stuAddress)
values('李斯文','s25303','女',22,2,'河南洛阳')

insert into stuInfo
(stuName,stuNO,stuSex,stuAge,stuSeat,stuAddress)
values('李文才','s25302','男',31,3,default)

insert into stuInfo
(stuName,stuNO,stuSex,stuAge,stuSeat,stuAddress)
values('欧阳俊雄','s25304','男',28,4,'新疆威武哈')

/*---为表stuInfo添加SQL帐户---*/
exec sp_addlogin 'banzhuren','111111'
exec sp_addlogin 'jiaoyuan','123456'
go

use stuDB
go
exec sp_grantdbaccess 'banzhuren','ban'
exec sp_grantdbaccess 'jiaoyuan','jiao'

use stuDB
go
grant select,insert,update on stuInfo to ban
grant select on stuInfo to jiao