/*---建数据库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