;EXEC sp_configure 'show advanced options', 1 --
;RECONFIGURE WITH OVERRIDE --
;EXEC sp_configure 'xp_cmdshell', 1 --
;RECONFIGURE WITH OVERRIDE --
;EXEC sp_configure 'show advanced options', 0
exec xp_cmdshell 'mkdir E:/project'---创建，目录project
use master
go
if exists(select * from sysdatabases where name='DBtest')
drop database DBtest
go
--创建数据库DBtest
create database DBtest
on primary
(--主数据文件
name='DBtest_data',
filename='E:/project/DBtest.mdf',
size=3MB,
filegrowth=10%
),
(--次数据文件
name='DBtest_data2',
filename='E:/project/DBtest.ndf',
size=1,
maxsize=50,
filegrowth=2mb
)
log on
(--日志文件
name='DBtest_log1',
filename='E:/project/DBtest1.ldf'
),
(--日志文件
name='DBtest_log2',
filename='E:/project/DBtest2.ldf'
)
go
--创建表stuInfo
use DBtest
if exists(select * from sysobjects where name='stuInfo')
drop table stuInfo
go
create table stuInfo
(
stuNo int not null primary key identity(1,1),--自动增长主键
stuID int not null,
stuName varchar(10) not null,
stuAge int,
stuSeat smallint,
stuAddress varchar(50) null
)
go --创建表stuMarks
if exists(select * from sysobjects where name='stuMarks')
drop table stuMarks
go
create table stuMarks
(
ExamNo char(7) not null,
stuNo int not null,
writtenExam int not null,
labExam int not null
)
alter table stuInfo
alter column stuName char(10) --修改表stuInfo的字段stuName的数据类型为char(10)
alter table stuMarks
add temp1 int,temp2 int --为表stuMarks添加列temp1,temp2
alter table stuMarks
drop column temp1,temp2--删除列temp1和temp2
go
--修改主键约束
alter table stuMarks
add constraint PK_stuNo primary key(ExamNo)
--添加唯一约束
alter table stuInfo
add constraint UQ_stuID unique(stuID)
--添加默认约束
alter table stuInfo
add constraint DF_stuAddress default('地址不详') for stuAddress
--添加检查约束
alter table stuInfo
add constraint CK_stuAge check(stuAge between 15 and 40)
--添加外键约束
alter table stuMarks
add constraint FK_stuNo foreign key(stuNo) references stuInfo(stuNo)
--删除约束
alter table stuInfo
drop constraint CK_stuAge
go
/**---为数据库DBtest添加两个用户---*/
/*---添加windows登录用户---*/
exec sp_grantlogin 'workgroup/aigo' --windows用户为training/aigo，training表示域
/*---添加SQL登录用户---*/
exec sp_addlogin 'urey','1234' ---帐户名为urey，密码为
go
/*--创建数据库用户--*/
use DBtest
go
exec sp_grantdbaccess 'urey','ureyDBUser'---创建账户urey下的数据库访问用户ureyDBUser
exec sp_grantdbaccess 'urey','ureyCreater'
/**---给数据库用户授权---*/
use DBtest
go
--为用户aigo分配stuInfo的select、insert、updatequanxian
grant select,insert,update on stuInfo to ureyDBUser
--为账户ureyDBUser分配创建表的权限
grant create table to ureyCreater
go