浙大遠程數(shù)據(jù)庫技術實驗報告

1、.實 驗 報 告 (使用 MySql 數(shù)據(jù)庫 )實驗目的： SQL 查詢練習，掌握SQL 查詢語句的語法結構和各子句的使用方法。實驗要求：根據(jù)給定的 14 個查詢命題給出相應的 SQL 語句，并在示例數(shù)據(jù)庫中加以執(zhí)行，獲取相應的查詢結果，予以記錄。實驗內(nèi)容：1 找出所有姓中以S.開頭的學生。SQL 語句： SELECT * FROM students WHERE LAST_NAME LIKE S% 返回結果：2 找出每個專業(yè)的學生人數(shù)、已得最高學分、最低學分、平均學分、學分總數(shù)。SQL 語句：SELECT MAJOR AS專業(yè) ,count(MAJOR) AS人數(shù) ,MAX(CURRENT_C

2、REDITS) AS最高學分 ,MIN(CURRENT_CREDITS)AS最 低 學 分 ,AVG(CURRENT_CREDITS)as 平 均 學分,SUM(CURRENT_CREDITS) AS學分總數(shù)FROM students GROUP BY MAJOR返回結果：3 找出所有教室的座位數(shù)，最大教室的座位數(shù)、最小座位數(shù)。SQL 語句：SELECT SUM(NUMBER_SEA TS) AS 座位數(shù) ,MAX(NUMBER_SEATS) AS 最大座位數(shù),MIN(NUMBER_SEATS) AS 最小座位數(shù) FROM rooms返回結果：4 找出各大樓的最大教室座位數(shù)，最小教室座位數(shù)，平

3、均座位數(shù)、座位總數(shù)。SQL 語句：.SELECTBUILDINGAS大樓,MAX(NUMBER_SEATS)AS最大座位數(shù) ,MIN(NUMBER_SEA TS) AS 最 小 座 位 數(shù) ,AVG(NUMBER_SEATS) AS 平 均 座 位數(shù),SUM(NUMBER_SEATS) AS 座位總數(shù) FROM rooms GROUP BY BUILDING返回結果：5 找出各課程尚可選修的人數(shù)。SQL 語句：SELECT Course AS 開課課程 ,DESCRIPTION AS 課程名稱 , (MAX_STUDENTS - CURRENT_STUDENTS) AS 可選修人數(shù) FROM

4、classes返回結果：6 找出開課最多的系。SQL 語句：SELECTDEPARTMENTAS系 別 ,COUNT(COURSE)AS開 課 數(shù)FROMregistered_students GROUP BY DEPARTMENT ORDER BY 2 DESC LIMIT 1返回結果：7 找出開課最少的系。.SQL 語句：SELECTDEPARTMENTAS系 別 ,COUNT(COURSE)AS開 課 數(shù)FROMregistered_students GROUP BY DEPARTMENT ORDER BY 2 ASC LIMIT 1返回結果：8 找出選課最多的學生。SQL 語句：SEL

5、ECTSTUDENT_IDAS學 生 號 ,COUNT(STUDENT_ID)AS選 課 數(shù)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 選 課 數(shù) 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分數(shù) ,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找出各系所占教室的座位數(shù)。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 分別創(chuàng)建一個學生、 選修課程的歷史表， 要求按時間存儲學生、選修課程信息。將當前學生、選修信息全部存儲到該歷史表。當前學生畢業(yè)，將當前學生刪除。SQL 語句：創(chuàng)建學

9、生歷史表：增加入學日期（REGDA TE ）、學習狀態(tài)字段(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;創(chuàng)建選修課程歷史表：增

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更新入學日期、學習狀態(tài)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刪除畢業(yè)學生 :delete from students_his where STATUS = 畢業(yè) .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 專業(yè),s.CURRENT_CREDITS AS 已得學分 F

13、ROM students s,registered_students rs WHEREs.ID = rs.STUDENT_ID AND rs.DEPARTMENT = HIS返回結果：數(shù)據(jù)庫改造1 ALTER TABLE registered_students ADD COLUMN temp INTEGER-增加字段2 UPDATE registered_students SET temp = ASCII(GRADE)-將 grade 轉換為數(shù)字插入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 字段- 改造后，重寫數(shù)據(jù)庫說明.doc里的 10、 11。.10找出不及格的學生SQL：SELECT ID AS 學號 ,FIRST_NAME AS 名 ,LAST_NAME AS 姓 ,GRADE AS 分數(shù) 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分數(shù) ,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 年長教師數(shù) ,SUM(CASEWHENc.AGE 35 THEN1ELSE0END) AS 年輕教師數(shù)FROM teacher c結果：統(tǒng)計各系年輕教師中職稱為“教授”的教師數(shù)量。SQL：SELECTc.DEPARTMENTAS 系別 ,COUNT(t.P_TITLE)AS 教授數(shù)量FROMteachert,classes cWHEREt.ID=c.TEACHER_IDANDt.P_TITLE= 教 授 GROUPBYc.DEPARTMENT結果：.5. 統(tǒng)計每個教師帶的學生個數(shù)。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.統(tǒng)計每個教師的工作量，教師的工作量按照學生人數(shù)* 所授課程的學分數(shù)進行統(tǒng)計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. 根據(jù)指定的教師名字或教師工號確定教師現(xiàn)在的授課課程及課程的上課