create database StudentInfo
go
use StudentInfo
go
create table CourseInfo
(
CourseNO int not null,
CourseName char(20) not null,
Marks int not null
)
create table StuInfo
(
StuID int not null,
StuName char(10) not null,
StuSex bit not null
)
create table StuExam
(
ExamNO int not null,
StuID int not null,
CourseNO int not null,
Score int not null
)
alter table CourseInfo
add constraint PK_courseNO primary key (CourseNO)
alter table CourseInfo
add constraint DF_marks default (1) for Marks
alter table CourseInfo
add constraint CH_marks check (Marks >=1 and Marks <=5)
alter table StuInfo
add constraint PK_StuID primary key (StuID)
alter table StuInfo
add constraint CH_StuID check (StuID >0 and StuID<=1000)
alter table StuExam
add constraint PK_ExamNO primary key (ExamNO)
alter table StuExam
add constraint FK_StuID foreign key (StuID) references StuInfo (StuID)
alter table StuExam
add constraint FK_CourseNO foreign key (CourseNO) references CourseInfo (CourseNO)
alter table stuExam
add constraint DF_Score default (0) for Score
--修改列(StuSex)把列的类型改为char
alter table StuInfo alter column StuSex char(10)
go
use StudentInfo
go
create table CourseInfo
(
CourseNO int not null,
CourseName char(20) not null,
Marks int not null
)
create table StuInfo
(
StuID int not null,
StuName char(10) not null,
StuSex bit not null
)
create table StuExam
(
ExamNO int not null,
StuID int not null,
CourseNO int not null,
Score int not null
)
alter table CourseInfo
add constraint PK_courseNO primary key (CourseNO)
alter table CourseInfo
add constraint DF_marks default (1) for Marks
alter table CourseInfo
add constraint CH_marks check (Marks >=1 and Marks <=5)
alter table StuInfo
add constraint PK_StuID primary key (StuID)
alter table StuInfo
add constraint CH_StuID check (StuID >0 and StuID<=1000)
alter table StuExam
add constraint PK_ExamNO primary key (ExamNO)
alter table StuExam
add constraint FK_StuID foreign key (StuID) references StuInfo (StuID)
alter table StuExam
add constraint FK_CourseNO foreign key (CourseNO) references CourseInfo (CourseNO)
alter table stuExam
add constraint DF_Score default (0) for Score
--修改列(StuSex)把列的类型改为char
alter table StuInfo alter column StuSex char(10)