--建库
create database Student
on primary
(
name='Student_db',--逻辑名
filename='e:\Student_db.mdf',
size=5mb,
maxsize=100mb,
filegrowth=15%
)
log on
(
name='Student_log',
filename='e:\Student_log.ldf',
size=2mb,
filegrowth=1mb
)
--建库(带次要的数据库文件和多个日志文件)
create database Employee
on primary
(
name='emp_db',
filename='D:\Employee.mdf',
size=5mb,
filegrowth=15%
),
(
--次要的
name='emp_ndb',
filename='E:\Employee.ndf',
size=3mb,
filegrowth=10%
)
log on
(
name='emp_log1',
filename='E:\Employee_log1.ldf',
size=2mb,
filegrowth=20%
),
(
name='emp_log2',
filename='F:\Employee_log2.ldf',
size=1mb,
filegrowth=10%
)
--master数据库的sysdatabases用于查询所有数据库信息
select * from sysdatabases
--删除数据库 (drop database 数据库名)
if exists
(select * from sysdatabases where name='Student')
drop database Student
--建表
use student
create table StuInfo
(
ID int identity(1,1) primary key,
StuName varchar(50) not null,
Age int ,
Sex bit,
Seat int,
Address varchar(100)
)
--学生成绩表
create table StuMark
(
ID int primary key identity(1,1),
StuID int ,
WrittenExam int,
LabExam int
)
select * from sysobjects--查看当前数据库的所有对象,包括表;
--删除表DROP TABLE 表名
if exists
(select * from sysobject where name='StuMark')
drop Table StuMark
--创建主键约束
alter table StuInfo
add constraint PK_ID primary key (ID)
--唯一约束
alter table stuInfo
add constraint UQ_stuName unique (stuName)
--默认约束
alter table stuInfo
add constraint DF_Address default('地址不详')
for Address
--创建检查约束
alter table StuInfo
add constraint CK_Age check(Age between 18 and 40)
---创建外键约束
alter table StuMark
add constraint FK_StuID foreign key(StuID)
references StuInfo(ID)
--删除约束
alter table StuMark drop constraint UQ_StuID
--创建SQLServer登录名
exec sp_addlogin 'zhangsan',123
--创建数据库用户
exec sp_grantdbaccess 'zhangsan','zs'
--授予数据库用户操作权限
grant select,insert,update,delete on stuInfo to zs