数据库的实现

---新建数据库
use master 
go
if exists( select * from sysdatabases where name='S2222')
drop database S2222
create database S2222
on primary(
name='S2222_data',
filename='D:\\S2222_data.mdf',
size=5mb,
maxsize=100mb,
filegrowth=15%
)
log on(
name='S2222_log',
filename='D:\\S2222_log.ldf',
size=3mb,
filegrowth=1mb
)

use master 
go
if exists( select * from sysdatabases where name='S2222')
drop database S2222

--新增数据库S2222中添加Student表
use S2222	
create table Student(
StudentNo int NOT NULL,
LoginPwd nvarchar(20) NOT NULL,
StudentName nvarchar(20) NOT NULL,
Sex nvarchar(20) NOT NULL,
GradeID int NOT NULL,
Age int NOT NULL
)
--新增数据库S2222中添加Grade表
use S2222	
create table Grade(
GradeID int NOT NULL,
GradeName nvarchar(20) NOT NULL
) 

--删除表
Drop table Student

--添加数据
INSERT INTO Student(StudentNo,LoginPwd,StudentName,Sex,GradeID,Age)
VALUES(1,'123','张三','男',1,20)
INSERT INTO Student(StudentNo,LoginPwd,StudentName,Sex,GradeID,Age)
VALUES(2,'123','李四','女',2,18)
INSERT INTO Student(StudentNo,LoginPwd,StudentName,Sex,GradeID,Age)
VALUES(3,'123','王五','男',3,20)

INSERT INTO Grade(GradeID,GradeName) 
SELECT 1,'S1'UNION
SELECT 2,'S2'UNION
SELECT 3,'Y2'
--添加主键约束
ALTER TABLE Student
ADD CONSTRAINT PK_Student_Grade_StudenNo PRIMARY KEY(StudentNo)

ALTER TABLE Grade
ADD CONSTRAINT PK_Grade_Student_GradeID PRIMARY KEY(GradeID)

--添加唯一约束
ALTER TABLE Student
ADD CONSTRAINT UQ_GradeID UNIQUE(GradeID)

--添加默认约束
ALTER TABLE Student 
ADD CONSTRAINT DF_LoginPwd DEFAULT('123') FOR LoginPwd

--添加检查约束 
ALTER TABLE Student 
ADD CONSTRAINT CK_LoginPwd CHECK (Age>='18')