浙大远程数据库技术实验报告

1、.实 验 报 告 (使用 MySql 数据库 )实验目的： SQL 查询练习，掌握SQL 查询语句的语法结构和各子句的使用方法。实验要求：根据给定的 14 个查询命题给出相应的 SQL 语句，并在示例数据库中加以执行，获取相应的查询结果，予以记录。实验内容：1 找出所有姓中以S.开头的学生。SQL 语句： SELECT * FROM students WHERE LAST_NAME LIKE S% 返回结果：2 找出每个专业的学生人数、已得最高学分、最低学分、平均学分、学分总数。SQL 语句：SELECT MAJOR AS专业 ,count(MAJOR) AS人数 ,MAX(CURRENT_C

2、REDITS) AS最高学分 ,MIN(CURRENT_CREDITS)AS最 低 学 分 ,AVG(CURRENT_CREDITS)as 平 均 学分,SUM(CURRENT_CREDITS) AS学分总数FROM students GROUP BY MAJOR返回结果：3 找出所有教室的座位数，最大教室的座位数、最小座位数。SQL 语句：SELECT SUM(NUMBER_SEA TS) AS 座位数 ,MAX(NUMBER_SEATS) AS 最大座位数,MIN(NUMBER_SEATS) AS 最小座位数 FROM rooms返回结果：4 找出各大楼的最大教室座位数，最小教室座位数，平

3、均座位数、座位总数。SQL 语句：.SELECTBUILDINGAS大楼,MAX(NUMBER_SEATS)AS最大座位数 ,MIN(NUMBER_SEA TS) AS 最 小 座 位 数 ,AVG(NUMBER_SEATS) AS 平 均 座 位数,SUM(NUMBER_SEATS) AS 座位总数 FROM rooms GROUP BY BUILDING返回结果：5 找出各课程尚可选修的人数。SQL 语句：SELECT Course AS 开课课程 ,DESCRIPTION AS 课程名称 , (MAX_STUDENTS - CURRENT_STUDENTS) AS 可选修人数 FROM

4、classes返回结果：6 找出开课最多的系。SQL 语句：SELECTDEPARTMENTAS系 别 ,COUNT(COURSE)AS开 课 数FROMregistered_students GROUP BY DEPARTMENT ORDER BY 2 DESC LIMIT 1返回结果：7 找出开课最少的系。.SQL 语句：SELECTDEPARTMENTAS系 别 ,COUNT(COURSE)AS开 课 数FROMregistered_students GROUP BY DEPARTMENT ORDER BY 2 ASC LIMIT 1返回结果：8 找出选课最多的学生。SQL 语句：SEL

5、ECTSTUDENT_IDAS学 生 号 ,COUNT(STUDENT_ID)AS选 课 数FROMregistered_studentsGROUPBYSTUDENT_IDHA VINGCOUNT(COURSE)=all(SELECT COUNT(COURSE) FROM registered_students GROUP BY STUDENT_ID)返回结果：9 找出选课最少的学生。SQL 语句：SELECT STUDENT_ID AS 学 生 号 ,COUNT(STUDENT_ID) AS 选 课 数 FROM registered_students GROUP BY STUDENT_ID

6、 HA VING COUNT(COURSE) = all(SELECT COUNT(COURSE) FROM registered_students GROUP BY STUDENT_ID)返回结果：10找出不及格的学生。SQL 语句：ALTER TABLE registered_students ADD FRACTION INTUPDATE registered_students SET FRACTION = CASE GRADE WHEN A THEN 90 WHEN B THEN 80 WHEN C THEN 70 WHEN D THEN 60 ELSE 50 ENDSELECT * F

7、ROM registered_students WHERE FRACTION 60.返回结果：11找出各课程平均分以下的学生。SQL 语句：SELECTa.ID AS 学号 ,a.FIRST_NAMEAS 名 ,a.LAST_NAMEAS 姓 ,b.COURSEAS 科目 ,b.FRACTION AS分数 ,c.平均分 FROM students as a,registered_students as b,(SELECT COURSE,A VG(FRACTION) as平均分FROM registered_students GROUPBY COURSE) as cWHERE a.ID = b.

8、STUDENT_ID AND b.COURSE = c.COURSE AND b.FRACTION c. 平均分返回结果：12找出各系所占教室的座位数。SQL 语句：SELECT a.DEPARTMENT AS 系别 ,SUM(b.NUMBER_SEATS) FROM classes AS a, rooms AS b WHERE a.ROOM_ID = b.ROOM_ID GROUP BY a.DEPARTMENT返回结果：.13 分别创建一个学生、 选修课程的历史表， 要求按时间存储学生、选修课程信息。将当前学生、选修信息全部存储到该历史表。当前学生毕业，将当前学生删除。SQL 语句：创建学

9、生历史表：增加入学日期（REGDA TE ）、学习状态字段(STATUS)CREATE TABLE demo.students_his(ID SMALLINT NULL,FIRST_NAME varchar(20) NULL,LAST_NAME varchar(20) NULL,MAJOR varchar(30) NULL,CURRENT_CREDITS SMALLINT NULL,REGDATE DATETIME NULL,STATUS varchar(10) null,PRIMARY KEY(ID)ENGINE=MyISAM DEFAULT CHARSET=utf8;创建选修课程历史表：增

10、加选课日期字段（ENRDATE ）CREATE TABLE demo.registered_students_his (STUDENT_ID smallint(6) DEFAULT NULL,DEPARTMENT varchar(3) DEFAULT NULL,COURSE smallint(6) DEFAULT NULL,GRADE varchar(1) DEFAULT NULL,FRACTION int(11) DEFAULT NULL,ENRDA TE datetime DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8;导入当前学生信息in

11、sertintostudents_his(ID,FIRST_NAME,LAST_NAME,MAJOR,CURRENT_CREDITS)SELECT * FROM students更新入学日期、学习状态update students_his set REGDATE = 20170901,STATUS = 在学 导入当前选修信息insertintoregistered_students_his(STUDENT_ID,DEPARTMENT,COURSE,GRADE,FRACTION)select * from registered_students更新选课日期update registered_st

12、udents_his set ENRDATE = 20170915删除毕业学生 :delete from students_his where STATUS = 毕业 .delete from registered_students_his where STUDENT_ID not in (select ID from students_his)14找出选修了HIS 系开的所有课程的学生。SQL 语句：SELECT s.ID AS 学号 ,s.FIRST_NAME AS 名 ,s.LAST_NAME AS 姓 ,s.MAJOR AS 专业,s.CURRENT_CREDITS AS 已得学分 F

13、ROM students s,registered_students rs WHEREs.ID = rs.STUDENT_ID AND rs.DEPARTMENT = HIS返回结果：数据库改造1 ALTER TABLE registered_students ADD COLUMN temp INTEGER-增加字段2 UPDATE registered_students SET temp = ASCII(GRADE)-将 grade 转换为数字插入temp3 UPDATE registered_students SET temp=99 WHERE temp=65;-A4 UPDATE reg

14、istered_students SET temp=89 WHERE temp=66;-B5 UPDATE registered_students SET temp=79 WHERE temp=67;-C6 UPDATE registered_students SET temp=69 WHERE temp=68;-D7 UPDATE registered_students SET temp=59 WHERE temp=69;-E8 alter table registered_students modify column GRADE INTEGER; -修改 grade 字段类型9 UPDAT

15、E registered_students SET GRADE = temp- 修改 grade 的值为 temp 列的值10alter table registered_students drop column temp- 删除 temp 字段- 改造后，重写数据库说明.doc里的 10、 11。.10找出不及格的学生SQL：SELECT ID AS 学号 ,FIRST_NAME AS 名 ,LAST_NAME AS 姓 ,GRADE AS 分数 FROM students, registered_students WHERE ID=STUDENT_ID AND GRADE 60结果：11找

16、出各课程平均分以下的学生SQL：SELECTa.ID AS 学号 ,a.FIRST_NAMEAS 名 ,a.LAST_NAMEAS 姓 ,b.COURSEAS 科目 ,b.GRADE AS分数 ,c.平均分 FROM students as a,registered_students as b,(SELECT COURSE,A VG(GRADE) as平均分FROM registered_students GROUP BYCOURSE) as cWHERE a.ID = b.STUDENT_ID AND b.COURSE = c.COURSE AND b.GRADE 35 THEN1ELSE0

17、END) AS 年长教师数 ,SUM(CASEWHENc.AGE 35 THEN1ELSE0END) AS 年轻教师数FROM teacher c结果：统计各系年轻教师中职称为“教授”的教师数量。SQL：SELECTc.DEPARTMENTAS 系别 ,COUNT(t.P_TITLE)AS 教授数量FROMteachert,classes cWHEREt.ID=c.TEACHER_IDANDt.P_TITLE= 教 授 GROUPBYc.DEPARTMENT结果：.5. 统计每个教师带的学生个数。SQL：SELECT t.ID,t.NAME,SUM(c.CURRENT_STUDENTS) FROM classes c,teacher t WHERE c.TEACHER_ID=t.ID GROUP BY c.TEACHER_ID结果：6.统计每个教师的工作量，教师的工作量按照学生人数* 所授课程的学分数进行统计SQL：SELECT t.ID,t.NAME,SUM(c.CURRENT_STUDENTS)*c.NUM_CREDITS FROM classes c,teacher t WHERE c.TEACHER_ID=t.ID GROUP BY c.TEACHER_ID结果：7. 根据指定的教师名字或教师工号确定教师现在的授课课程及课程的上课