浙大遠(yuǎn)程數(shù)據(jù)庫技術(shù)實(shí)驗(yàn)報(bào)告

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

2、REDITS) AS最高學(xué)分 ,MIN(CURRENT_CREDITS)AS最 低 學(xué) 分 ,AVG(CURRENT_CREDITS)as 平 均 學(xué)分,SUM(CURRENT_CREDITS) AS學(xué)分總數(shù)FROM students GROUP BY MAJOR返回結(jié)果：3 找出所有教室的座位數(shù)，最大教室的座位數(shù)、最小座位數(shù)。SQL 語句：SELECT SUM(NUMBER_SEA TS) AS 座位數(shù) ,MAX(NUMBER_SEATS) AS 最大座位數(shù),MIN(NUMBER_SEATS) AS 最小座位數(shù) FROM rooms返回結(jié)果：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返回結(jié)果：5 找出各課程尚可選修的人數(shù)。SQL 語句：SELECT Course AS 開課課程 ,DESCRIPTION AS 課程名稱 , (MAX_STUDENTS - CURRENT_STUDENTS) AS 可選修人數(shù) FROM

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

5、ECTSTUDENT_IDAS學(xué) 生 號(hào) ,COUNT(STUDENT_ID)AS選 課 數(shù)FROMregistered_studentsGROUPBYSTUDENT_IDHA VINGCOUNT(COURSE)=all(SELECT COUNT(COURSE) FROM registered_students GROUP BY STUDENT_ID)返回結(jié)果：9 找出選課最少的學(xué)生。SQL 語句：SELECT STUDENT_ID AS 學(xué) 生 號(hào) ,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)返回結(jié)果：10找出不及格的學(xué)生。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.返回結(jié)果：11找出各課程平均分以下的學(xué)生。SQL 語句：SELECTa.ID AS 學(xué)號(hào) ,a.FIRST_NAMEAS 名 ,a.LAST_NAMEAS 姓 ,b.COURSEAS 科目 ,b.FRACTION AS分?jǐn)?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. 平均分返回結(jié)果：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返回結(jié)果：.13 分別創(chuàng)建一個(gè)學(xué)生、 選修課程的歷史表， 要求按時(shí)間存儲(chǔ)學(xué)生、選修課程信息。將當(dāng)前學(xué)生、選修信息全部存儲(chǔ)到該歷史表。當(dāng)前學(xué)生畢業(yè)，將當(dāng)前學(xué)生刪除。SQL 語句：創(chuàng)建學(xué)

9、生歷史表：增加入學(xué)日期（REGDA TE ）、學(xué)習(xí)狀態(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;導(dǎo)入當(dāng)前學(xué)生信息in

11、sertintostudents_his(ID,FIRST_NAME,LAST_NAME,MAJOR,CURRENT_CREDITS)SELECT * FROM students更新入學(xué)日期、學(xué)習(xí)狀態(tài)update students_his set REGDATE = 20170901,STATUS = 在學(xué) 導(dǎo)入當(dāng)前選修信息insertintoregistered_students_his(STUDENT_ID,DEPARTMENT,COURSE,GRADE,FRACTION)select * from registered_students更新選課日期update registered_st

12、udents_his set ENRDATE = 20170915刪除畢業(yè)學(xué)生 :delete from students_his where STATUS = 畢業(yè) .delete from registered_students_his where STUDENT_ID not in (select ID from students_his)14找出選修了HIS 系開的所有課程的學(xué)生。SQL 語句：SELECT s.ID AS 學(xué)號(hào) ,s.FIRST_NAME AS 名 ,s.LAST_NAME AS 姓 ,s.MAJOR AS 專業(yè),s.CURRENT_CREDITS AS 已得學(xué)分 F

13、ROM students s,registered_students rs WHEREs.ID = rs.STUDENT_ID AND rs.DEPARTMENT = HIS返回結(jié)果：數(shù)據(jù)庫改造1 ALTER TABLE registered_students ADD COLUMN temp INTEGER-增加字段2 UPDATE registered_students SET temp = ASCII(GRADE)-將 grade 轉(zhuǎn)換為數(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找出不及格的學(xué)生SQL：SELECT ID AS 學(xué)號(hào) ,FIRST_NAME AS 名 ,LAST_NAME AS 姓 ,GRADE AS 分?jǐn)?shù) FROM students, registered_students WHERE ID=STUDENT_ID AND GRADE 60結(jié)果：11找

16、出各課程平均分以下的學(xué)生SQL：SELECTa.ID AS 學(xué)號(hào) ,a.FIRST_NAMEAS 名 ,a.LAST_NAMEAS 姓 ,b.COURSEAS 科目 ,b.GRADE AS分?jǐn)?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 年長(zhǎng)教師數(shù) ,SUM(CASEWHENc.AGE 35 THEN1ELSE0END) AS 年輕教師數(shù)FROM teacher c結(jié)果：統(tǒng)計(jì)各系年輕教師中職稱為“教授”的教師數(shù)量。SQL：SELECTc.DEPARTMENTAS 系別 ,COUNT(t.P_TITLE)AS 教授數(shù)量FROMteachert,classes cWHEREt.ID=c.TEACHER_IDANDt.P_TITLE= 教 授 GROUPBYc.DEPARTMENT結(jié)果：.5. 統(tǒng)計(jì)每個(gè)教師帶的學(xué)生個(gè)數(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結(jié)果：6.統(tǒng)計(jì)每個(gè)教師的工作量，教師的工作量按照學(xué)生人數(shù)* 所授課程的學(xué)分?jǐn)?shù)進(jìn)行統(tǒng)計(jì)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結(jié)果：7. 根據(jù)指定的教師名字或教師工號(hào)確定教師現(xiàn)在的授課課程及課程的上課