StudentMis

/*创建studentsys数据库*/ create database studentsys on (name=studentsys_data, filename='e:\studentsys_data.mdf', size=10, filegrowth=10% ) log on (name=studentsys_log, filename='e:\studentsys_log.ldf', size=5, filegrowth=10% ) go use studentsys /*创建系部表*/ create table tbldepart (depart_id varchar(4) primary key, depart_name varchar(40), depart_teachername varchar(40), tel varchar(20) ) /*插入系部表的内容*/ insert into tblDepart(depart_id,depart_name) values('1','电子') insert into tblDepart(depart_id,depart_name) values('2','机电') insert into tblDepart(depart_id,depart_name) values('3','计算机') insert into tblDepart(depart_id,depart_name) values('4','电气') insert into tblDepart(depart_id,depart_name) values('5','英语') insert into tblDepart(depart_id,depart_name) values('6','信管') insert into tblDepart(depart_id,depart_name) values('7','通讯') /*创建课程类型表*/ create table tblcoursetype (course_typeid varchar(8) primary key, course_type varchar(6) not null ) /*插入课程类型表的内容*/ insert into tblcourseType values('0','公共课') insert into tblcourseType values('1','专业课') insert into tblcourseType values('2','选修课') /*创建班级表*/ create table tblclass (class_id varchar(10) primary key, speciality varchar(8), enroll_year char(9), length char(1), teacher_name varchar(20), class_num tinyint, flag char(4), Depart_ID varchar(4) references tblDepart(Depart_ID) ) /*插入班级表的内容*/ insert into tblClass(class_id,speciality,enroll_year,length,class_num,flag,depart_id) values('310220','网络','2002','3',30,'毕业','3') insert into tblClass(class_id,speciality,enroll_year,length,class_num,flag,depart_id) values('310310','软件','2003','3',42,'在校','3') insert into tblClass(class_id,speciality,enroll_year,length,class_num,flag,depart_id) values('310430','系统维护','2004','5',null,'在校','3') insert into tblClass(class_id,speciality,enroll_year,length,class_num,flag,depart_id) values('330410','网络','2004','3',44,'毕业','3') /*创建学生表*/ create table tblstudent (stud_id varchar(12) primary key, stud_name varchar(8), stud_sex char(2) default'男' check(stud_sex in('男','女')), birth datetime, enroll_date datetime default(getdate()), address varchar(40), postcode char(6), tel varchar(20), class_id varchar(10) references tblclass(class_id), identity_id varchar(18) unique ) /*插入学生表的内容*/ insert into tblStudent(Stud_ID,Stud_Name,Stud_Sex,Birth,Enroll_Date,Identity_ID,Class_ID,address,postcode,tel) values('31022001','曹操',default,'1980-02-04','2002-09-01','111111111111111001','310220','淮安','223001','3807133') insert into tblStudent(Stud_ID,Stud_Name,Stud_Sex,Birth,Enroll_Date,Identity_ID,Class_ID,address,postcode,tel) values('31022002','司马懿',default,'1983-08-16','2002-09-01','111111111111111002','310220','北京','239658','2567894') insert into tblStudent(Stud_ID,Stud_Name,Stud_Sex,Birth,Enroll_Date,Identity_ID,Class_ID,address,postcode,tel) values('31022003','蔡文姬','女','1980-05-03','2002-09-01','111111111111111003','310220','徐州','221411','4772564') insert into tblStudent(Stud_ID,Stud_Name,Stud_Sex,Birth,Enroll_Date,Identity_ID,Class_ID,address,postcode,tel) values('31031001','刘备',default,'1981-03-03','2003-09-01','222222222222222001','310310','徐州','221411','1234567') insert into tblStudent(Stud_ID,Stud_Name,Stud_Sex,Birth,Enroll_Date,Identity_ID,Class_ID,address,postcode,tel) values('31031002','诸葛亮',default,'1982-01-01','2003-09-01','222222222222222002','310310','南京','222005','2587964') insert into tblStudent(Stud_ID,Stud_Name,Stud_Sex,Birth,Enroll_Date,Identity_ID,Class_ID,address,postcode,tel) values('31031003','赵云',default,'1983-08-16','2003-09-01','222222222222222003','310310','台州','333224','5467891') insert into tblStudent(Stud_ID,Stud_Name,Stud_Sex,Birth,Enroll_Date,Identity_ID,Class_ID,address,postcode,tel) values('31031004','关羽',default,'1982-01-02','2003-09-01','222222222222222004','310310','苏州','215212','6364895') insert into tblStudent(Stud_ID,Stud_Name,Stud_Sex,Birth,Enroll_Date,Identity_ID,Class_ID,address,postcode,tel) values('31031005','貂禅','女','1985-09-09','2003-09-01','222222222222222005','310310','台州','333224','1656978') insert into tblStudent(Stud_ID,Stud_Name,Stud_Sex,Birth,Enroll_Date,Identity_ID,Class_ID,address,postcode,tel) values('33041001','张飞','男','1985-09-09','2004-09-01','34212619850909789','330410','苏州','215212','6364894') insert into tblStudent(Stud_ID,Stud_Name,Stud_Sex,Birth,Enroll_Date,Identity_ID,Class_ID,address,postcode,tel) values('31043001','孙权',default,'1988-08-19','2004-09-01','333333333333333001','310430','南京','222005','2587955') insert into tblStudent(Stud_ID,Stud_Name,Stud_Sex,Birth,Enroll_Date,Identity_ID,Class_ID,address,postcode,tel) values('31043002','周瑜',default,'1988-02-02','2004-09-01','333333333333333002','310430','北京','239658','1235897') insert into tblStudent(Stud_ID,Stud_Name,Stud_Sex,Birth,Enroll_Date,Identity_ID,Class_ID,address,postcode,tel) values('31043003','小乔','女','1990-01-19','2004-09-01','333333333333333003','310430','苏州','215212','6364456') /*创建课程表*/ create table tblcourse (course_id varchar(5) primary key, course_name varchar(30) not null, course_typeid varchar(8) references tblcoursetype(course_typeid), course_credit tinyint check(course_credit>0), course_hour int check(course_hour>0) ) /*插入课程表的内容*/ insert into tblCourse values('30001','VB','0',6,90) insert into tblCourse values('30002','数据结构','0',6,90) insert into tblCourse values('30003','数据库','1',4,60) insert into tblCourse values('30004','软件工程','1',4,60) insert into tblCourse values('30005','J2EE','2',2,30) insert into tblCourse values('30006','Java','2',2,30) /*创建成绩表*/ create table tblscore (stud_id varchar(12) references tblstudent(stud_id), course_id varchar(5) references tblcourse(course_id), term tinyint check(term>0), score tinyint check(score between 0 and 100) default 0, makeup tinyint default 0 check(makeup>=0), score_bk tinyint, test_num tinyint check(test_num>=0), hours int check(hours>=0) primary key(stud_id,course_id) ) /*插入成绩表的内容*/ insert into tblScore(stud_id,course_id,term,score,makeup,score_bk) values('31022001','30001',1,88,null,null) insert into tblScore(stud_id,course_id,term,score,makeup,score_bk) values('31022001','30002',2,80,null,null) insert into tblScore(stud_id,course_id,term,score,makeup,score_bk) values('31022001','30003',3,95,null,null) insert into tblScore(stud_id,course_id,term,score,makeup,score_bk) values('31022001','30004',4,85,null,null) insert into tblScore(stud_id,course_id,term,score,makeup,score_bk) values('31022001','30005',4,83,null,null) insert into tblScore(stud_id,course_id,term,score,makeup,score_bk) values('31022002','30001',1,null,1,60) insert into tblScore(stud_id,course_id,term,score,makeup,score_bk) values('31022002','30002',2,98,null,null) insert into tblScore(stud_id,course_id,term,score,makeup,score_bk) values('31022002','30003',3,88,null,null) insert into tblScore(stud_id,course_id,term,score,makeup,score_bk) values('31022002','30004',4,85,null,null) insert into tblScore(stud_id,course_id,term,score,makeup,score_bk) values('31022002','30005',3,79,null,null) insert into tblScore(stud_id,course_id,term,score,makeup,score_bk) values('31022003','30001',1,78,null,null) insert into tblScore(stud_id,course_id,term,score,makeup,score_bk) values('31022003','30002',2,88,null,null) insert into tblScore(stud_id,course_id,term,score,makeup,score_bk) values('31022003','30003',3,90,null,null) insert into tblScore(stud_id,course_id,term,score,makeup,score_bk) values('31022003','30004',4,68,null,null) insert into tblScore(stud_id,course_id,term,score,makeup,score_bk) values('31031001','30001',1,78,null,null) insert into tblScore(stud_id,course_id,term