《浙大远程数据库技术实验报告》由会员分享，可在线阅读，更多相关《浙大远程数据库技术实验报告（11页珍藏版）》请在金锄头文库上搜索。
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_CREDITS) AS 最高学分,
2、MIN(CURRENT_CREDITS) AS 最低学分,AVG(CURRENT_CREDITS) as 平均学分,SUM(CURRENT_CREDITS) AS 学分总数 FROM students GROUP BY MAJOR返回结果：3 找出所有教室的座位数，最大教室的座位数、最小座位数。SQL语句：SELECT SUM(NUMBER_SEATS) AS 座位数,MAX(NUMBER_SEATS) AS 最大座位数,MIN(NUMBER_SEATS) AS 最小座位数 FROM rooms返回结果：4 找出各大楼的最大教室座位数，最小教室座位数，平均座位数、座位总数。SQL语句：SELE
3、CT BUILDING AS 大楼,MAX(NUMBER_SEATS) AS 最大座位数,MIN(NUMBER_SEATS) 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 classes返回结果：6 找出开课最多的系。SQL语句：S
4、ELECT DEPARTMENT AS 系别,COUNT(COURSE) AS 开课数 FROM registered_students GROUP BY DEPARTMENT ORDER BY 2 DESC LIMIT 1返回结果：7 找出开课最少的系。SQL语句：SELECT DEPARTMENT AS 系别,COUNT(COURSE) AS 开课数 FROM registered_students GROUP BY DEPARTMENT ORDER BY 2 ASC LIMIT 1返回结果：8 找出选课最多的学生。SQL语句：SELECT STUDENT_ID AS 学生号,COUNT(
5、STUDENT_ID) AS 选课数 FROM registered_students GROUP BY STUDENT_ID HAVING COUNT(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 HAVING COUNT(COURSE) = al
6、l(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 * FROM registered_students WHE
7、RE FRACTION 60返回结果：11 找出各课程平均分以下的学生。SQL语句：SELECT a.ID AS 学号,a.FIRST_NAME AS 名,a.LAST_NAME AS 姓,b.COURSE AS 科目,b.FRACTION AS 分数,c.平均分 FROM students as a,registered_students as b,(SELECT COURSE,AVG(FRACTION) as 平均分 FROM registered_students GROUP BY COURSE) as c WHERE a.ID = b.STUDENT_ID AND b.COURSE =
8、 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语句：创建学生历史表：增加入学日期（REGDATE）、学习状态字段(STA
9、TUS)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;创建选修课程历史表：增加选课日期字段（ENRDATE）CREATE TABLE
10、 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,ENRDATE datetime DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8;导入当前学生信息insert into students_his(ID
11、,FIRST_NAME,LAST_NAME,MAJOR,CURRENT_CREDITS) SELECT * FROM students更新入学日期、学习状态update students_his set REGDATE = 20170901,STATUS = 在学导入当前选修信息insert into registered_students_his(STUDENT_ID,DEPARTMENT,COURSE,GRADE,FRACTION) select * from registered_students更新选课日期update registered_students_his set ENRDA
12、TE = 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 已得学分 FROM students s,registered_
13、students rs WHERE s.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 registered_students SET tem
14、p=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 UPDATE registered_students
15、SET GRADE = temp -修改grade的值为temp列的值10 alter 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 找出各课程平均分以下的学生SQL：SELECT a.ID AS 学号,a
