and type = 'U') drop table TeachManage go
if exists (select 1
from sysindexes
where id = object_id('TeacherManage') and name = 'Relationship_6_FK' and indid > 0 and indid < 255)
drop index TeacherManage.Relationship_6_FK go
if exists (select 1
from sysobjects
where id = object_id('TeacherManage') and type = 'U') drop table TeacherManage go
if exists (select 1
from sysindexes
where id = object_id('studentmanage') and name = 'Relationship_2_FK' and indid > 0 and indid < 255)
drop index studentmanage.Relationship_2_FK go
if exists (select 1
from sysindexes
where id = object_id('studentmanage') and name = 'Relationship_1_FK' and indid > 0 and indid < 255)
drop index studentmanage.Relationship_1_FK go
if exists (select 1
from sysobjects
where id = object_id('studentmanage') and type = 'U') drop table studentmanage go
15
if exists (select 1
from sysindexes
where id = object_id('教材使用') and name = '教材使用2_FK' and indid > 0 and indid < 255)
drop index 教材使用.教材使用2_FK go
if exists (select 1
from sysindexes
where id = object_id('教材使用') and name = '教材使用_FK' and indid > 0 and indid < 255)
drop index 教材使用.教材使用_FK go
if exists (select 1
from sysobjects
where id = object_id('教材使用') and type = 'U') drop table 教材使用 go
if exists (select 1
from sysindexes
where id = object_id('教材发放') and name = '教材发放2_FK' and indid > 0 and indid < 255)
drop index 教材发放.教材发放2_FK go
if exists (select 1
from sysindexes
where id = object_id('教材发放') and name = '教材发放_FK' and indid > 0 and indid < 255)
drop index 教材发放.教材发放_FK go
16
if exists (select 1
from sysobjects
where id = object_id('教材发放') and type = 'U') drop table 教材发放 go
alter table Classmangement
add constraint FK_CLASSMAN_RELATIONS_DEPARTME foreign key (Depar_Id) references Departmentform (Depar_Id) go
alter table GradeManagement
add constraint FK_GRADEMAN_RELATIONS_STUDENTM foreign key (St_Id) references studentmanage (St_Id) go
alter table GradeManagement
add constraint FK_GRADEMAN_RELATIONS_COURSEIN foreign key (Course_Id) references Courseinformationform (Course_Id) go
alter table TeachManage
add constraint FK_TEACHMAN_RELATIONS_COURSEIN foreign key (Course_Id) references Courseinformationform (Course_Id) go
alter table TeachManage
add constraint FK_TEACHMAN_RELATIONS_TEACHERM foreign key (T_Id) references TeacherManage (T_Id) go
alter table TeacherManage
add constraint FK_TEACHERM_RELATIONS_COURSEIN foreign key (Course_Id) references Courseinformationform (Course_Id) go
alter table studentmanage
add constraint FK_STUDENTM_RELATIONS_DEPARTME foreign key (Depar_Id) references Departmentform (Depar_Id) go
alter table studentmanage
add constraint FK_STUDENTM_RELATIONS_CLASSMAN foreign key (class_id)
17
references Classmangement (class_id) go
alter table 教材使用
add constraint FK_教材使用_教材使用_COURSEIN foreign key (Course_Id) references Courseinformationform (Course_Id) go
alter table 教材使用
add constraint FK_教材使用_教材使用2_SBMANAGE foreign key (SUBID) references SBMANAGE (SUBID) go
alter table 教材发放
add constraint FK_教材发放_教材发放_STUDENTM foreign key (St_Id) references studentmanage (St_Id) go
alter table 教材发放
add constraint FK_教材发放_教材发放2_SBMANAGE foreign key (SUBID) references SBMANAGE (SUBID) go
（一）学生管理（11）
/*==============================================================*/ /* Table: studentmanage 建立学生信息表 */ /*==============================================================*/ create table studentmanage (
St_Name varchar(10) not null, 学生姓名 St_Id numeric(20) not null, 学生编号 Depar_Id numeric(10) null, 系部编号 class_id numeric(10) null, 班级编号 St_Sex varchar(10) not null, 学生性别 St_Address varchar(50) not null, 学生住址 St_Tel numeric(20) not null, 学生电话 St_Bir datetime not null, 学生生日
constraint PK_STUDENTMANAGE primary key nonclustered (St_Id) 建立主键：学生编号
18
) Go
1、数据插入
学生管理插入的相关信息：
insert into dbo.studentmanage values('王一',100,1,1,'男','芙蓉路一号',135641,1989-06-22) insert into dbo.studentmanage values('王二',101,1,1,'男','芙蓉路二号',165941,1989-05-12) insert into dbo.studentmanage values('王三',102,4,2,'男','芙蓉路三号',169941,1988-12-12) insert into dbo.studentmanage values('王四',103,4,2,'男','芙蓉路三号',149941,1988-12-12) insert into dbo.studentmanage values('王五',104,3,5,'男','芙蓉路四号',129941,1990-01-12) insert into dbo.studentmanage values('王六',105,4,6,'男','芙蓉路五号',119941,1990-02-12) insert into dbo.studentmanage values('王七',106,5,7,'男','芙蓉路六号',165541,1990-03-12) insert into dbo.studentmanage values('王八',107,6,8,'男','芙蓉路七号',163341,1990-04-12) insert into dbo.studentmanage values('王九',108,7,9,'男','芙蓉路八号',160041,1990-05-12) insert into dbo.studentmanage values('王十',109,8,10,'男','芙蓉路九号',167941,1990-06-12) insert into dbo.studentmanage values('王十一',110,9,11,'男','芙蓉路十号',163941,1990-07-12)
2、查询结果
select * from dbo.studentmanage
19