create database school --创建数据库

--创建班级信息表
create table Class
(
 classid int identity(1,1),
 classname nvarchar(10),
 classtime datetime ,
 classsex bit,
 classDscription nvarchar(50)
)

--创建学生信息表
create table Student
(
stuid int identity(1,1) ,
stuname nvarchar(20),
stusex bit check(stusex='男' or stusex='女') default('男'),
stuage int ,
stuaddress nvarchar(100),
stuclassid int
)

--创建课程信息表
create table Course
(
courid int,
courname nvarchar(20),
courDscription nvarchar(50)
)

--创建成绩信息表
create table Grade
(
gradeid int ,
gradestuid int,
gradecourid int,
grade int,
)


--------------------------------------------

--修改班级信息表结构
alter table Class
alter column ClassDscription nvarchar(max)

alter table Class
drop column classsex  

--约束班级信息表进行设置
alter table Class
add constraint PK_Class_classid primary key (classid)--设置主键为课程编号（主键约束）

alter table Class
add constraint CK_Class_classname check(classname is not null)--check检查约束，不能为空

alter table Class
add constraint UQ_Class_classname unique(classname)--unique唯一约束，字段不能重复

--修改学生信息表结构
alter table Student
alter column stuname nvarchar(max)

alter table Student
alter column stuaddress nvarchar(max)

--约束学生信息表进行设置
alter table Student
add constraint PK_Student_stuid primary key(stuid)

alter table Student 
add constraint stusex check(stusex is not null)
 
alter table Student
add constraint stuage check(stuage is not null)

alter table Student
add constraint DF_Student_stuaddress default('湖北武汉')

--修改课程信息表结构
alter table Course
alter column courname nvarchar(max)

alter table Course 
alter column courDescription nvarchar(max)

--约束课程信息表进行设置
alter table Course 
add constraint PK_Course_courid primary key(courid)

alter table Course
add constraint UQ_Course_courname unique(Courname)

alter table Course
add constraint CK_Course_courname check(courname is not null)

--修改成绩信息表结构
alter table Grade
alter column gradestuid bigint

--约束成绩信息表进行设置
alter table Grade
add constraint PK_Grade_gradeid primary key(gradeid)

alter table Grade
add constraint gradename check(gradename is not null)

alter table Grade
add constraint gradeclassid check(gradeclassid is not null)

alter table Grade 
add constraint grade check(grade>=0 and grade<=100)