新建数据库-添加数据-添加约束

---新建数据库use master goif exists( select * from sysdatabases where name='S2222')drop database S2222create database S2222on 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 goif exists( select * from sysdatabases where name='S2222')drop database S2222--新增数据库S2222中添加Student表use S2222create 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 S2222create 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'UNIONSELECT 2,'S2'UNIONSELECT 3,'Y2'--添加主键约束ALTER TABLE StudentADD CONSTRAINT PK_Student_Grade_StudenNo PRIMARY KEY(StudentNo)ALTER TABLE GradeADD CONSTRAINT PK_Grade_Student_GradeID PRIMARY KEY(GradeID)--添加唯一约束ALTER TABLE StudentADD 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')