浙大远程数据库技术实验报告.docx
《浙大远程数据库技术实验报告.docx》由会员分享，可在线阅读，更多相关《浙大远程数据库技术实验报告.docx（14页珍藏版）》请在冰点文库上搜索。
浙大远程数据库技术实验报告
实验报告（使用MySql数据库）
实验目的：
SQL查询练习，掌握SQL查询语句的语法结构和各子句的使用方法。
实验要求：
根据给定的14个查询命题给出相应的SQL语句，并在示例数据库中加以执行，获取相应的查询结果，予以记录。
实验内容：
1．找出所有姓中以S.开头的学生。
SQL语句：
SELECT*FROMstudentsWHERELAST_NAMELIKE'S%'
返回结果：
2．找出每个专业的学生人数、已得最高学分、最低学分、平均学分、学分总数。
SQL语句：
SELECTMAJORAS专业,count（MAJOR）AS人数,MAX（CURRENT_CREDITS）AS最高学分,MIN（CURRENT_CREDITS）AS最低学分,AVG（CURRENT_CREDITS）as平均学分,SUM（CURRENT_CREDITS）AS学分总数FROMstudentsGROUPBYMAJOR
返回结果：
3．找出所有教室的座位数，最大教室的座位数、最小座位数。
SQL语句：
SELECTSUM（NUMBER_SEATS）AS座位数,MAX（NUMBER_SEATS）AS最大座位数,MIN（NUMBER_SEATS）AS最小座位数FROMrooms
返回结果：
4．找出各大楼的最大教室座位数，最小教室座位数，平均座位数、座位总数。
SQL语句：
SELECTBUILDINGAS大楼,MAX（NUMBER_SEATS）AS最大座位数,MIN（NUMBER_SEATS）AS最小座位数,AVG（NUMBER_SEATS）AS平均座位数,SUM（NUMBER_SEATS）AS座位总数FROMroomsGROUPBYBUILDING
返回结果：
5．找出各课程尚可选修的人数。
SQL语句：
SELECTCourseAS开课课程,DESCRIPTIONAS课程名称,（MAX_STUDENTS-CURRENT_STUDENTS）AS可选修人数FROMclasses
返回结果：
6．找出开课最多的系。
SQL语句：
SELECTDEPARTMENTAS系别,COUNT（COURSE）AS开课数FROMregistered_studentsGROUPBYDEPARTMENTORDERBY2DESCLIMIT1
返回结果：
7．找出开课最少的系。
SQL语句：
SELECTDEPARTMENTAS系别,COUNT（COURSE）AS开课数FROMregistered_studentsGROUPBYDEPARTMENTORDERBY2ASCLIMIT1
返回结果：
8．找出选课最多的学生。
SQL语句：
SELECTSTUDENT_IDAS学生号,COUNT（STUDENT_ID）AS选课数FROMregistered_studentsGROUPBYSTUDENT_IDHAVINGCOUNT（COURSE）>=all（SELECTCOUNT（COURSE）FROMregistered_studentsGROUPBYSTUDENT_ID）
返回结果：
9．找出选课最少的学生。
SQL语句：
SELECTSTUDENT_IDAS学生号,COUNT（STUDENT_ID）AS选课数FROMregistered_studentsGROUPBYSTUDENT_IDHAVINGCOUNT（COURSE）<=all（SELECTCOUNT（COURSE）FROMregistered_studentsGROUPBYSTUDENT_ID）
返回结果：
10．找出不及格的学生。
SQL语句：
ALTERTABLEregistered_studentsADDFRACTIONINT
UPDATEregistered_studentsSETFRACTION=CASEGRADEWHEN'A'THEN90WHEN'B'THEN80WHEN'C'THEN70WHEN'D'THEN60ELSE50END
SELECT*FROMregistered_studentsWHEREFRACTION<60
返回结果：
11．找出各课程平均分以下的学生。
SQL语句：
SELECTa.IDAS学号,a.FIRST_NAMEAS名,a.LAST_NAMEAS姓,b.COURSEAS科目,b.FRACTIONAS分数,c.`平均分`FROMstudentsasa,registered_studentsasb,
（SELECTCOURSE,AVG（FRACTION）as平均分FROMregistered_studentsGROUPBYCOURSE）asc
WHEREa.ID=b.STUDENT_IDANDb.COURSE=c.COURSEANDb.FRACTION返回结果：
12．找出各系所占教室的座位数。
SQL语句：
SELECTa.DEPARTMENTAS系别,SUM（b.NUMBER_SEATS）FROMclassesASa,roomsASbWHEREa.ROOM_ID=b.ROOM_IDGROUPBYa.DEPARTMENT
返回结果：
13．分别创建一个学生、选修课程的历史表，要求按时间存储学生、选修课程信息。
将当前学生、选修信息全部存储到该历史表。
当前学生毕业，将当前学生删除。
SQL语句：
创建学生历史表：
增加入学日期（REGDATE）、学习状态字段（STATUS）
CREATETABLEdemo.students_his（
`ID`SMALLINTNULL,
`FIRST_NAME`varchar（20）NULL,
`LAST_NAME`varchar（20）NULL,
`MAJOR`varchar（30）NULL,
`CURRENT_CREDITS`SMALLINTNULL,
`REGDATE`DATETIMENULL,
`STATUS`varchar（10）null,
PRIMARYKEY（`ID`）
）ENGINE=MyISAMDEFAULTCHARSET=utf8;
创建选修课程历史表：
增加选课日期字段（ENRDATE）
CREATETABLEdemo.registered_students_his（
`STUDENT_ID`smallint（6）DEFAULTNULL,
`DEPARTMENT`varchar（3）DEFAULTNULL,
`COURSE`smallint（6）DEFAULTNULL,
`GRADE`varchar
（1）DEFAULTNULL,
`FRACTION`int（11）DEFAULTNULL,
`ENRDATE`datetimeDEFAULTNULL
）ENGINE=MyISAMDEFAULTCHARSET=utf8;
导入当前学生信息
insertintostudents_his（ID,FIRST_NAME,LAST_NAME,MAJOR,CURRENT_CREDITS）SELECT*FROMstudents
更新入学日期、学习状态
updatestudents_hissetREGDATE='20170901',STATUS='在学'
导入当前选修信息
insertintoregistered_students_his（STUDENT_ID,DEPARTMENT,COURSE,GRADE,FRACTION）select*fromregistered_students
更新选课日期
updateregistered_students_hissetENRDATE='20170915'
删除毕业学生:
deletefromstudents_hiswhereSTATUS='毕业'
deletefromregistered_students_hiswhereSTUDENT_IDnotin（selectIDfromstudents_his）
14．找出选修了HIS系开的所有课程的学生。
SQL语句：
SELECTs.IDAS学号,s.FIRST_NAMEAS名,s.LAST_NAMEAS姓,s.MAJORAS专业,s.CURRENT_CREDITSAS已得学分FROMstudentss,registered_studentsrsWHEREs.ID=rs.STUDENT_IDANDrs.DEPARTMENT='HIS'
返回结果：
数据库改造
1．ALTERTABLEregistered_studentsADDCOLUMNtempINTEGER---增加字段
2．UPDATEregistered_studentsSETtemp=ASCII（GRADE）----将grade转换为数字插入temp
3．UPDATEregistered_studentsSETtemp=99WHEREtemp=65;----A
4．UPDATEregistered_studentsSETtemp=89WHEREtemp=66;----B
5．UPDATEregistered_studentsSETtemp=79WHEREtemp=67;-----C
6．UPDATEregistered_studentsSETtemp=69WHEREtemp=68;-----D
7．UPDATEregistered_studentsSETtemp=59WHEREtemp=69;-----E
8．altertableregistered_studentsmodifycolumnGRADEINTEGER;-----修改grade字段类型
9．UPDATEregistered_studentsSETGRADE=temp---修改grade的值为temp列的值
10．altertableregistered_studentsdropcolumntemp----删除temp字段
--------------改造后，重写‘数据库说明.doc’里的10、11。
10．找出不及格的学生
SQL：
SELECTIDAS学号,FIRST_NAMEAS名,LAST_NAMEAS姓,GRADEAS分数FROMstudents,registered_studentsWHEREID=STUDENT_IDANDGRADE<60
结果：
11．找出各课程平均分以下的学生
SQL：
SELECTa.IDAS学号,a.FIRST_NAMEAS名,a.LAST_NAMEAS姓,b.COURSEAS科目,b.GRADEAS分数,c.`平均分`FROMstudentsasa,registered_studentsasb,
（SELECTCOURSE,AVG（GRADE）as平均分FROMregistered_studentsGROUPBYCOURSE）asc
WHEREa.ID=b.STUDENT_IDANDb.COURSE=c.COURSEANDb.GRADE结果：
数据库设计
思路：
新建教师表（teacher），包括工号、姓名、性别、年龄、职称字段，在class表中增加teacher_id（教师工号）外键。
Teacher表
Classes表
1.根据性别统计教师数量
SQL：
SELECTt.SEX,count
（1）ascountFROMteachertGROUPBYt.SEX
结果：
2.根据职称统计教师的数量。
SQL：
SELECTt.P_TITLE,count
（1）ascountFROMteachertGROUPBYt.P_TITLE
结果：
3.统计各系的教师数量。
SQL：
SELECTc.DEPARTMENT,COUNT（distinctc.TEACHER_ID）as教师数量FROMclassescGROUPBYc.DEPARTMENT
结果：
4.以35岁为界统计年轻教师和年长教师的数量，并统计各系年轻教师中职称为“教授”的教师数量。
SQL:
统计年轻教师和年长教书的数量
SELECTSUM（
CASE
WHENc.AGE>'35'THEN
1
ELSE
0
END
）AS年长教师数,
SUM（
CASE
WHENc.AGE<'35'THEN
1
ELSE
0
END
）AS年轻教师数
FROMteacherc
结果：
统计各系年轻教师中职称为“教授”的教师数量。
SQL：
SELECTc.DEPARTMENTAS系别,COUNT（t.P_TITLE）AS教授数量FROMteachert,classescWHEREt.ID=c.TEACHER_IDANDt.P_TITLE='教授'GROUPBYc.DEPARTMENT
结果：
5.统计每个教师带的学生个数。
SQL：
SELECTt.ID,t.NAME,SUM（c.CURRENT_STUDENTS）FROMclassesc,teachertWHEREc.TEACHER_ID=t.IDGROUPBYc.TEACHER_ID
结果：
6.统计每个教师的工作量，教师的工作量按照学生人数*所授课程的学分数进行统计
SQL：
SELECTt.ID,t.NAME,SUM（c.CURRENT_STUDENTS）*c.NUM_CREDITSFROMclassesc,teachertWHEREc.TEACHER_ID=t.IDGROUPBYc.TEACHER_ID
结果：
7.根据指定的教师名字或教师工号确定教师现在的授课课程及课程的上课地点（教室号）
SQL：
t.ID=和t.NAME都是可以替换的
SELECT
r.*,temp.DEPARTMENT,
temp.COURSE
FROM
roomsr
RIGHTJOIN（
SELECT
c.DEPARTMENT,
c.TEACHER_ID,
c.COURSE,
c.ROOM_ID
FROM
classesc
WHERE
EXISTS（
SELECT
1
FROM
teachert
WHERE
c.TEACHER_ID=t.ID
AND（t.ID=1ORt.NAME='赵清'）
）
）tempONr.ROOM_ID=temp.ROOM_ID
结果：
升级会员 