SQL 语句来创建数库

---------------------题目表－单选题目---------------------------------- use OnlineExamDB Go if object_id('T_SingleAnswer') is not null drop table T_SingleAnswer go create table T_SingleAnswer ( singleId int identity(1,1) primary key, courseId int Foreign key references T_Course(courseId), singleQuestion nvarchar(200), singleAnswerA nvarchar(50), singleAnswerB nvarchar(50), singleAnswerC nvarchar(50), singleAnswerD nvarchar(50), singleKey nvarchar(10), singleLevel int, singleScore int default(2) ) GO select * from T_SingleAnswer ----------------------------------------------------------- 多选题目 if object_id('T_MultipleAnswer') is not null drop table T_MultipleAnswer go create table T_MultipleAnswer ( multipleId int identity(1,1) primary key, courseId int Foreign key references T_Course(courseId), multipleQuestion nvarchar(200), multipleAnswerA nvarchar(50), multipleAnswerB nvarchar(50), multipleAnswerC nvarchar(50), multipleAnswerD nvarchar(50), multipleKey nvarchar(10), multipleLevel int, multipleScore int default(2) ) GO ======================================= if db_id('ExamOnline') is not null drop database ExamOnline Go Create Database ExamOnline Go Use ExamOnline if object_id('Users') is not null drop table Users Go create table Users ----------用户表 ( users_Id nchar(20) primary key, users_Pwd nchar(20), users_types nchar(20), Users_status nchar(20) ) Go insert into Users values('1001','123456','老师','可用') insert into Users values('1002','123456','学生','在校') Go select * from Users --------------------------------------------------学生表 If object_id('stu_Info') is not null drop table stu_Info Go create table stu_Info ( stu_Id nchar(20) primary key, Users_Id nchar(20) Foreign key references Users(Users_Id), stu_Name nchar(30), stu_Birthday DateTime, stu_Gender nchar(10), stu_Major nchar(30), class_Id nchar(20) Foreign key references Class(class_Id) ) Go insert into stu_Info values( '2001','1002','andy','2008-1-1','男','dotnet','200905') select * from stu_Info ----------------------------------------------------------- 教师表 create table Teach_Info ( Teacher_Id nchar(20) primary key, Userser_Id nchar(20) Foreign key references Users(Users_Id), Teacher_Name nchar(30), Teacher_Birthday DateTime, Teacher_Gender nchar(10), Teacher_Dep nchar(20), ) Go insert into Teach_Info values( '3001','1002','andy','1997-1-1','男','教务') select * from Teach_Info ---------------------------------------------------------- 科目表 if object_id('course') is not null drop table course Go create table course ( --课程号 course_Id nchar(20) not null primary key, --课程名称 course_Name nchar(20) not null, --教工编号 Teacher_Id nchar(20) Foreign Key references Teach_Info(Teacher_Id) ) insert into course values('001','C#','3001') select * from course ------------------------------------------------------------ 分数表 --create table score --( ----学号 --Teacher_Id nchar(20) not null references Teach_Info(Teacher_Id), ----课程号 --course_Id nchar(20) not null references course(course_Id), ----成绩 --degree decimal(4,1) --) -- --insert into score values('3001','001','80') -- --select * from score --------------------------------------------------------------班级表 create table Class ( class_Id nchar(20) primary key , class_Name nchar(20), class_Direction nchar(20), class_StatTime datetime, class_EndTime datetime ) insert into class values('200905','net5','net','2009-5-27','2009-9-24') select * from class -------------------------------------------------------题目表－单选题目 if object_id('SingleOption') is not null drop table SingleOption go create table SingleOption ( sp_Id int identity(1,1) primary key, course_Id nchar(20) foreign key references course(course_Id),-- 科目 sp_Question nchar(200), sp_AnserA nchar(40), sp_AnserB nchar(40), sp_AnserC nchar(40), sp_AnserD nchar(40), sp_Key nchar(10), sp_level nchar(10), sp_Score int ) insert into SingleOption values('001','net 是什么平台？','C#','Java','Jsp','C++','C#','1',2) select * from SingleOption ----------------------------------------------------------- 多选题目 if object_id('MultiOption') is not null drop table MultiOption go create table MultiOption ( mp_Id int identity(1,1) primary key, Course_Id nchar(20) foreign key references course(course_Id),-- 科目 mp_Question nchar(200), mp_AnserA nchar(40), mp_AnserB nchar(40), mp_AnserC nchar(40), mp_AnserD nchar(40), mp_KeyA nchar(10), mp_KeyB nchar(10), mp_KeyC nchar(10), mp_KeyD nchar(10), mp_level nchar(10), mp_Score int ) insert into MultiOption values('001','net 是什么平台？','C#','Java','Jsp','C++','A','B','C','D','1',2) select * from MultiOption --------------------------------------------------- 填空 if object_id('FillOption') is not null drop table FillOption go Create table FillOption ( fp_Id int identity(1,1) primary key, course_Id nchar(20) foreign key references course(course_Id),-- 科目 fp_Question nchar(200), fp_Key nchar(50), fp_level nchar(10), fp_score int ) insert into FillOption values('001','net 是什么平台？','是基于C#','2',2) select * from FillOption -----------------------------------------------------简答 if object_id('SimpleAns') is not null drop table SimpleAns go Create table SimpleAns ( SA_Id int identity(1,1) primary key, course_Id nchar(20) foreign key references course(course_Id),-- 科目 SA_Question nchar(200), SA_Key nchar(200), SA_level nchar(20), SA_Score int default(5) ) insert into SimpleAns values('001','net 是什么平台？','是基于C#','2',5) select * from SimpleAns -----------------------------------------------------编程 if object_id('Program') is not null drop table Program go Create table Program ( Pg_Id int identity(1,1) primary key, course_Id nchar(20) foreign key references course(course_Id),-- 科目 Pg_Question nchar(200), Pg_Key nchar(200), Pg_level nchar(20), Pg_Score int default(8) Title_Id ) insert into Program values('001','net 是什么平台？','是基于C#','2',8) select * from Program --------------------------------------------------------试卷表 if object_id('Paper') is not null drop table Paper Go create table Paper ( papeInfo_Id int identity(1,1) primary key, -- key references PaperInfo( papeInfo_Id), Teacher_Id nchar(20) foreign key references Teach_Info(Teacher_Id), course_Id nchar(20) foreign key references course(course_Id),-- 科目 paper_Title nchar(40), ---试题 Paper_state nchar(20), ) insert into Paper values('3001','001','200906.net5班ASP.net 试题','未发布') select * from Paper -------------------------------------------------------------------试卷详细信息表 if object_id('PaperInfo') is not null drop table PaperInfo go create table PaperInfo ( id int identity(1,1) primary key, papeInfo_Id int foreign key references Paper(papeInfo_Id) , course_Id nchar(20) foreign key references course(course_Id), ----题型 QuestionType nchar(20), -- check(必须是选择，填空，等等相关内容。。。 title_Id int, paper_Mark int ) insert into PaperInfo values(2,'001','单选',1,2) ---courseId 和title id 不能重复 select * from PaperInfo -------------------------------------------------------考生信息表 if object_id('ExamInfo') is not null drop table ExamInfo go create table ExamInfo ( exam_Id int identity(9001,1) primary key, papeInfo_Id int foreign key references Paper(papeInfo_Id), exam_State bit default(1), ---是否补考 class_Id nchar(20) foreign key references class(class_Id), stu_Id nchar(20) foreign key references stu_Info(stu_Id), paper_StartTime datetime, paper_EndTime datetime, paper_state nchar(20) -----是否发布考试 ) insert into ExamInfo values(2,1,'200905','2001','2009-9-1 14:00','2009-9-1 16:00','未发布') select * from ExamInfo select * from class select * from stu_Info select * from PaperInfo select * from Paper -------------------------------------------------------- 修改。。。 use examonline go if object_id('StuAnswer') is not null drop table StuAnswer Go create table StuAnswer ( stuAnswer_Id int identity(1,1) primary key, stu_Id nchar(20) foreign key references stu_Info(stu_Id), papeInfo_Id int foreign key references Paper(papeInfo_Id), paper_Title nchar(40), ---试题 stu_Answer nchar(200), --答案 stu_Time Datetime, Paper_state nchar(20) ) --------------------------------------------------------- 分数 if object_id('score')is not null drop table score Go create table score ( score_id int identity(1,1) primary key, stu_Id nchar(20) foreign key references stu_Info(stu_Id), papeInfo_Id int foreign key references Paper(papeInfo_Id), score int, exam_time Datetime, juge_time datetime ) =================项目过程用到的SQL语句================== USE OnLineExamDB go ------ --读取指定表的所有列名 select name from syscolumns where id=(select max(id) from sysobjects where xtype='u' and name='T_FillBlank') -- 读取库中的所有表名 SELECT name FROM sysobjects WHERE type = 'U' and name Like 'T%' ------------------------------------------------------------------------------------------------------ ---多表关连 /***其实 INNER JOIN ……ON的语法格式可以概括为： FROM (((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) INNER JOIN 表4 ON Member.字段号=表4.字段号) INNER JOIN 表X ON Member.字段号=表X.字段号 您只要套用该格式就可以了。****/ SELECT *FROM(((T_Paper INNER JOIN T_Examination ON T_Paper.paperID=T_Examination.paperID)INNER JOIN T_Answer ON T_Paper.paperID=T_Answer.paperID)INNER JOIN T_Student ON T_Student.studentID=T_Answer.studentID)INNER JOIN T_Score ON T_Score.studentID=T_Student.studentID go ------------------------------------------------------------------------------------------------------- -- 查询考试信息(存储过程) IF OBJECT_ID('SP_SELECT_EXAMINATION_TEACHER') IS NOT NULL DROP PROC SP_SELECT_EXAMINATION_TEACHER GO CREATE PROCEDURE SP_SELECT_EXAMINATION_TEACHER @examState NVARCHAR(20) AS BEGIN SELECT DISTINCT examId,courseName,studentName,T_Student.studentID,className,examStartime,examEndTime, paperTitle,T_Examination.paperId,makeup,T_Examination.examState FROM (((T_Student INNER JOIN T_Class ON T_Student.classID=T_Class.classID) INNER JOIN T_Examination ON T_Examination.classID=T_Class.classID) INNER JOIN T_Paper ON T_Paper.paperID=T_Examination.paperID) INNER JOIN T_Course ON T_Course.courseID=T_Paper.courseID WHERE T_Examination.studentid=T_Student.studentid AND T_Examination.classID=T_Class.classID AND T_Examination.paperID=T_Paper.paperID AND T_Examination.examState IN (@examState) ORDER BY examStarTime END ------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------- --发送考试信息(存储过程)(一条记录)--没有 CREATE PROCEDURE SP_SEND_EXAMINATION_BYEXAMID_TEACHER @examID INT AS BEGIN SELECT DISTINCT examId,courseName,studentName,T_Student.studentID,className,examStartime,examEndTime paperTitle,makeup,T_Examination.examState FROM (((T_Student INNER JOIN T_Class ON T_Student.classID=T_Class.classID) INNER JOIN T_Examination ON T_Examination.classID=T_Class.classID) INNER JOIN T_Paper ON T_Paper.paperID=T_Examination.paperID) INNER JOIN T_Course ON T_Course.courseID=T_Paper.courseID WHERE T_Examination.studentid=T_Student.studentid AND T_Examination.classID=T_Class.classID AND T_Examination.paperID=T_Paper.paperID AND examId=@examID ORDER BY examId END --------------------------------------------------------------------------------------------- SELECT DISTINCT examId,courseName,studentName,T_Student.studentID,className,examstartime, paperTitle,makeup,T_Examination.examState FROM (((T_Student INNER JOIN T_Class ON T_Student.classID=T_Class.classID) INNER JOIN T_Examination ON T_Examination.classID=T_Class.classID) INNER JOIN T_Paper ON T_Paper.paperID=T_Examination.paperID) INNER JOIN T_Course ON T_Course.courseID=T_Paper.courseID WHERE T_Examination.studentid=T_Student.studentid AND T_Examination.classID=T_Class.classID AND T_Examination.paperID=T_Paper.paperID AND examId in( 9011,9016,9019) ORDER BY examId ----------------------------------------------------------------------------------------------- select *from T_Examination where examState in ('&未发布&','已发布','已考') select *from T_Student update T_Examination set classid='3' where examID in ('9011','9012','9015') ----------------------------------------------------------------------------------------- --判断教师名与用户名是否一样，状态=‘发布’？ SELECT DISTINCT T_Teacher.teacherName,T_Examination.examState FROM T_Examination,T_Paper,T_Teacher WHERE examID IN ('9005') AND T_Examination.paperID=T_Paper.paperID and T_Paper.teacherId=T_Teacher.teacherId -------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------- --查询分数列表 select T_Paper.paperId,T_Student.studentId,T_Student.studentName,T_Student.studentGender,T_Score.className,T_Class.classTeacher,T_Score.score,T_Course.courseName, T_Class.classDirection from T_Student,T_Course,T_Score,T_Paper,T_Class where T_Student.studentId=T_Score.studentId and T_Paper.courseId=T_Course.courseId and T_Student.classId=T_Class.classId ---------------------------------------学生分数列表----------------------------------------- select T_Paper.paperId,T_Student.studentId,T_Student.studentName,T_Student.studentGender,T_Score.className,T_Class.classTeacher,T_Score.score,T_Course.courseName, T_Class.classDirection from T_Student,T_Course,T_Score,T_Paper,T_Class where T_Student.studentId=T_Score.studentId and T_Paper.courseId=T_Course.courseId and T_Student.classId=T_Class.classId And T_Class.ClassName='Net1' ---------------------------------------------------------------------------------- if object_id('SP_SameClass_AllStudentScore') is not null drop proc SP_SameClass_AllStudentScore go create proc SP_SameClass_AllStudentScore @courseName nvarchar(20), @ClassName nvarchar(20) as begin select T_Paper.paperId,T_Student.studentId,T_Student.studentName,T_Student.studentGender,T_Score.className,T_Class.classTeacher,T_Score.score,T_Course.courseName, T_Class.classDirection from T_Student,T_Course,T_Score,T_Paper,T_Class where T_Student.studentId=T_Score.studentId and T_Paper.courseId=T_Course.courseId and T_Student.classId=T_Class.classId and T_Course.courseName = @courseName and T_Score.className=@ClassName end exec SP_SameClass_AllStudentScore '统考','net5' ----成绩管理 ----------------------------------------某一个学生所有考试总成绩---------------------------- SELECT SUM(T_Score.score) AS SumStuScore,AVG(T_Score.score) AS AvgStuScore FROM T_Score,T_Student WHERE T_Score.studentId=T_Student.studentID AND T_Student.StudentName='张勇' --------------------------------------某一试卷编号所有学生总成绩和平均成绩------------------------------------- SELECT SUM(T_Score.score) AS SumPaperScore,avg(T_Score.score) AS AvgPaperScore FROM T_Score WHERE T_Score.paperId IN (SELECT PaperID FROM T_Paper WHERE paperTitle='') GROUP BY T_Score.paperId --------------------------------------某班某一试卷编号所有学生总成绩和平均成绩------------------------------------ SELECT SUM(T_Score.score) AS SumClassScore,Avg(T_Score.score) AS AvgClassScore FROM T_Score WHERE T_Score.paperId IN (SELECT PaperID FROM T_Paper WHERE paperTitle='') and T_Score.className='' GROUP BY T_Score.paperId,T_Score.className -------------------------------------------教师信息--------------------------------------------------------------------------------------- if object_id('SP_AllTeacherInfo')is not null drop proc SP_AllTeacherInfo Go create proc SP_AllTeacherInfo @TeacherId nvarchar(30) as begin Select distinct T_Teacher.teacherId,T_Teacher.teacherName,T_Teacher.teacherGender,T_Teacher.teacherDepartment, T_Teacher.teacherEmail from T_Teacher,T_Examination,T_Paper,T_Course,T_Answer,T_Class where T_Teacher.teacherId=T_Paper.teacherId and T_Paper.paperId=T_Examination.paperId and T_Examination.paperId=T_Answer.paperId and T_Teacher.teacherId=@TeacherId and T_Examination.classId=T_Class.classId and T_Paper.courseId=T_Course.courseId end exec SP_AllTeacherInfo 'T00001' ------------------------------------------教师所创建的最近考试列表的信息----------------------------------------------------------------------------- if object_id('SP_ALLExaminationInfo')is not null drop proc SP_ALLExaminationInfo Go create proc SP_ALLExaminationInfo @TeacherId nvarchar(30) as begin select distinct T_Teacher.teacherId,T_Paper.paperTitle,T_Course.courseName,T_Class.className,T_Answer.answerStartTime, T_Answer.answerEndTime ,T_Answer.answerState from T_Teacher,T_Examination,T_Paper,T_Course,T_Answer,T_Class where T_Teacher.teacherId=T_Paper.teacherId and T_Paper.paperId=T_Examination.paperId and T_Examination.paperId=T_Answer.paperId and T_Teacher.teacherId=@TeacherId and T_Examination.classId=T_Class.classId and T_Paper.courseId=T_Course.courseId end exec SP_ALLExaminationInfo 'T00001' ----------------------------------------浏览所有班级所有学生的成绩--------------------------------------------------------------------------------- if object_id('SP_BrowseAllScore')is not null drop proc SP_BrowseAllScore GO create proc SP_BrowseAllScore @CourseName nvarchar(20) as begin select distinct T_Student.studentId,T_Student.studentName,T_Student.studentGender,T_Score.className,T_Class.classTeacher, T_Score.score,T_Course.courseName,T_Class.classDirection,T_Score.examState,T_Answer.answerState from T_Student,T_Course,T_Score,T_Paper,T_Class,T_Answer where T_Student.studentId=T_Score.studentId and T_Paper.courseId=T_Course.courseId and T_Student.classId=T_Class.classId and T_Answer.studentId=T_Score.studentId and T_Course.courseName=@CourseName and T_Score.examState='完成考试' and T_Answer.answerState='已经阅卷' end exec SP_BrowseAllScore '统考' -----------------------------查询某个人的成绩------------------------------------------------------- if object_id('SP_StudentScore') is not null drop proc SP_StudentScore Go create proc SP_StudentScore @CourseName nvarchar(20), @ClassName nvarchar(20), @StudentName nvarchar(30) as begin select distinct T_Student.studentId,T_Student.studentName,T_Student.studentGender,T_Score.className,T_Class.classTeacher,T_Score.score,T_Course.courseName, T_Class.classDirection,T_Score.examState,T_Answer.answerState from T_Student,T_Course,T_Score,T_Paper,T_Class,T_Answer where T_Student.studentId=T_Score.studentId and T_Paper.courseId=T_Course.courseId and T_Student.classId=T_Class.classId and T_Answer.studentId=T_Score.studentId and T_Course.courseName = @courseName and T_Score.className=@ClassName and T_Student.studentName=@StudentName and T_Score.examState='完成考试' and T_Answer.answerState='已经阅卷' end exec SP_StudentScore '统考','net1','张勇' ------------------------------阅卷状态-------------------------------------------------- SELECT distinct T_Score.paperId,studentName,paperTitle,answerStartTime, answerEndTime,score,answerIP,answerState From T_Student,T_Paper,T_Score,T_Answer,T_Examination WHERE T_Student.studentId=T_Score.studentId and T_Score.studentId=T_Examination.studentId and T_Examination.studentId=T_Answer.studentId and T_Examination.studentId In (SELECT studentId FROM T_Examination WHERE T_Examination.examState='完成') and T_Score.paperId=T_Paper.paperId and T_Paper.paperId=T_Examination.paperId and T_Examination.paperId =T_Answer.PaperId and T_Answer.PaperId in (SELECT PaperId FROM T_Paper WHERE teacherId='T00001') go -------------------------------------------------------------------------------- --科目表 SELECT * FROM T_Course --学生信息表 SELECT *FROM T_Student order by classID --试卷表 SELECT * FROM T_Paper --考试信息表 SELECT *FROM T_Examination update T_Examination set makeup='考试' where makeup='正常考试' --班级表 SELECT *FROM T_Class --教师表 SELECT *FROM T_Teacher update T_teacher set teacherEnable='1' where teacherId='T00001' --试卷内容表 SELECT *FROM T_PaperContent --单选题表 SELECT *FROM T_SingleAnswer --多选题表 SELECT *FROM T_MultipleAnswer --填空题表 SELECT *FROM T_FillBlank --简答题表 SELECT *FROM T_SimpleProblem --编程题表 SELECT *FROM T_Program -- SELECT *FROM T_Special --题目类型 SELECT *FROM T_TitleType --答案信息表 SELECT *FROM T_Answer -- SELECT *FROM T_Teacher -- SELECT *FROM T_Score -- insert into T_TitleType (TitleName) values ( '填空题1') -- INSERT INTO T_Score VALUES('S00015','1','Net1','76','完成') UPDATE T_Score SET className='Net3' where studentId='S00003' DELETE FROM T_SCORE WHERE STUDENTid='S00001' -------------------------------------------------------------------------------------- ---------------------------------------题库题目重复问题------------------------------- --查询重复的记录 select*from T_SingleAnswer where singleQuestion in (select singleQuestion From T_SingleAnswer group by singleQuestion having count(singleQuestion)>1) -----是否已经存在？ select count(*) as NUM from T_SingleAnswer where singleQuestion='xx' ------------------------------------------------------------------------------------------------------------ if object_id('T_SingleAnswer') is not null drop table T_SingleAnswer go create table T_SingleAnswer ( singleId int identity(1,1) primary key, courseId int Foreign key references T_Course(courseId), singleQuestion nvarchar(200), singleAnswerA nvarchar(50), singleAnswerB nvarchar(50), singleAnswerC nvarchar(50), singleAnswerD nvarchar(50), singleKey nvarchar(10), singleLevel int, singleScore int default(2) ) GO ---------------T_Examination -----------------------增加是否补考------------- use OnlineExamDB Go alter table T_Examination add makeUp nvarchar(20) check(makeUp='考试' or makeUp='补考') default('考试') ----------------------------------------------------------------------------- --2.2-->3题库管理 -- 单选题列表页面 --科目列表 SELECT courseName FROM T_Course --单选题试题列表 SELECT * FROM T_Course INNER JOIN T_SingleAnswer ON T_SingleAnswer.courseId=T_Course.courseId --科目相对应的试题列表(@courseName) SELECT * FROM T_Course INNER JOIN T_SingleAnswer ON T_SingleAnswer.courseId=T_Course.courseId WHERE T_SingleAnswer.courseId IN (SELECT courseID FROM T_Course WHERE courseName='css') --SINGLEID 对应的试题 SELECT * FROM T_Course INNER JOIN T_SingleAnswer ON T_SingleAnswer.courseId=T_Course.courseId WHERE singleId='32' --SINGLEID 对应的试题(模式查询) SELECT * FROM T_Course INNER JOIN T_SingleAnswer ON T_SingleAnswer.courseId=T_Course.courseId WHERE singleId LIKE '%3%' --单选题的修改 UPDATE T_SingleAnswer SET courseID='2',singleQuestion='5*2=?',singleAnswerA='10',singleAnswerB='15',singleAnswerC='20',singleAnswerD='12',singleKey='C',singlelevel='2',singleScore='5' WHERE singleID='' --单选题的修改存储过程 CREATE PROCEDURE SP_UPDATE_SingleAnswer_BySingleID_Teacher @singleQuestion NCHAR(200),@singleAnswerA NCHAR(40),@singleAnswerB NCHAR(40),@singleAnswerC NCHAR(40),@singleAnswerD NCHAR(40),@singleKey NCHAR(10),@singlelevel NCHAR(10),@singleScore INT,@singleID INT AS BEGIN UPDATE T_SingleAnswer SET singleQuestion=@singleQuestion,singleAnswerA=@singleAnswerA,singleAnswerB=@singleAnswerB,singleAnswerC=@singleAnswerC,singleAnswerD=@singleAnswerD,singleKey=@singleKey,singlelevel=@singlelevel,singleScore=@singleScore WHERE SingleID=@SingleID END ------------------------------------------------------------------------------------ create proc SP_AllTeacherInfo @TeacherId nvarchar(30) as begin Select distinct T_Teacher.teacherId,T_Teacher.teacherName,T_Teacher.teacherGender,T_Teacher.teacherPwd,T_Teacher.teacherDepartment, T_Teacher.teacherEmail,T_Teacher.teacherEnable from T_Teacher,T_Examination,T_Paper,T_Course,T_Answer,T_Class where T_Teacher.teacherId=T_Paper.teacherId and T_Paper.paperId=T_Examination.paperId and T_Examination.paperId=T_Answer.paperId and T_Teacher.teacherId=@TeacherId and T_Examination.classId=T_Class.classId and T_Paper.courseId=T_Course.courseId end --单选题的删除 DELETE FROM T_SingleAnswer WHERE SingleID='42' --SingleSELECT 相对应ID的试题的编辑//'SingleID' SELECT *FROM T_SingleAnswer WHERE SingleId='' --单选题增加页面 --先在下拉菜单中固定好相对的科目 SELECT courseName FROM T_Course WHERE courseID='1' INSERT INTO T_SingleAnswer VALUES('001','5+4=?','8','11','6','9','C#','2','3') ---------------------------------------------------------------------------------------- --多选与单选基本相同 SELECT *FROM T_FillBlank --填空题 INSERT INTO T_FillBlank VALUES('001','asp是( )的( )平台','microsof,网站开发','3') --别的题型基本上与填空相同 ---------------------------------------------------------------------------------------- --4考试管理-->a).创建考试信息(CreateT_Examination.aspx) SELECT *FROM T_Examination --考试班级名称 SELECT className FROM T_Class where classID IN (SELECT classID FROM T_Examination) --考试班级名称 SELECT * FROM T_Class --这个班级的所有学生名称(然后就可从中选择补考学生) SELECT studentName FROM T_Student WHERE classID IN (SELECT classID FROM T_Class WHERE className='net5') --考试科目名称 SELECT courseName FROM T_course ----WHERE courseID IN( SELECT course_ID FROM T_Paper ) --考试试卷名称 SELECT paperTitle FROM T_Paper ----where Paper_ID IN(SELECT Paper_ID FROM T_Examination WHERE exam_ID='9005' ) --试卷编制(选择所有内容)-- SELECT*FROM T_PaperContent WHERE pContentID IN (SELECT paperID FROM T_Paper WHERE paperTitle='200906.net5班ASP.net 试题') --试卷表信息(T_Paper) INSERT INTO T_Paper VALUES('1','T00001','20090816.NET5C#考试','发布',GETDATE(),GETDATE(),'0') --保存考试表信息(试卷名称，班级，学生)(paperId,classId,studentId)???(科目包括在试卷表)--要一个一个学生进行插入 INSERT INTO T_Examination VALUES('1','2','S00003',GETDATE(),GETDATE(),'发布','补考') --保存考试表信息 --INSERT INTO T_Examination VALUES(@paperId,@classId,@studentId,@examStarTime,@examEndTime,@examState,@makeUp) -- 由班级ID来得到这个班级的全部学生信息 SELECT studentId,studentName,classId,studentGender FROM T_Student WHERE classId='1' -- SELECT studentId,studentName,classId,studentGender FROM T_Student WHERE classId IN (SELECT classID FROM T_Class WHERE className='Net1') --考试信息编制 UPDATE T_Examination SET studentId='S00002' WHERE examId='9010' --发布信息时，更新考试状态为“已发布” UPDATE T_Examination SET examState='已发布' WHERE examId <9011 -- 教师邮箱发送 SELECT teacherName,teacherEmail FROM T_Teacher ----------------------------------------------------------------------------------------- -->c).试卷的编制 SELECT *FROM T_PaperContent SELECT *FROM T_paper SELECT *FROM T_TitleType --试卷内容表--- INSERT INTO T_PaperContent VALUES('1','特殊','12','3') ----------------------------------------------------------------------------------------- ------------------------------查询别的表正在使用题目的记录------------------------ SELECT COUNT(*) FROM T_PaperContent WHERE pQuestionType='单选' AND pTitleId='40' ---------------------------------------------------------- update T_Examination set examStarTime='2009-9-21 18:05:00' where examId='9005' ----------------------补考与正常考试学生----------------------- SELECT * FROM T_Student WHERE classId='1' SELECT className FROM T_Class where classId In (SELECT classId FROM T_Student where classId='1') -----考试信息重复的不能添加 SELECT COUNT(*) FROM T_Examination where paperId='1' and examStartime='2009-9-21 16:10' and studentId='S00001' SELECT *FROM T_Examination -- update T_Student set studentPwd='E10ADC3949BA59ABBE56E057F20F883E' where studentId='90427UIF0701' -- SELECT id,singleQuestion,singleAnswerA,singleAnswerB,singleAnswerC,singleAnswerD,T_PaperContent.pScore FROM T_singleAnswer,T_PaperContent WHERE id in(SELECT pTitleId FROM T_PaperContent WHERE paperId=@paperId and pQuestionType=@pQuestionType) and T_singleAnswer.id=T_PaperContent.pTitleId and T_PaperContent.paperId=@paperId and pQuestionType=@pQuestionType ORDER BY NEWID() SELECT *FROM T_singleAnswer,T_PaperContent