【數(shù)據(jù)庫原理與應(yīng)用實(shí)驗(yàn)】

《【數(shù)據(jù)庫原理與應(yīng)用實(shí)驗(yàn)】》由會員分享，可在線閱讀，更多相關(guān)《【數(shù)據(jù)庫原理與應(yīng)用實(shí)驗(yàn)】（20頁珍藏版）》請?jiān)谘b配圖網(wǎng)上搜索。

1、 《數(shù)據(jù)庫原理與應(yīng)用實(shí)驗(yàn)》 實(shí)驗(yàn)報(bào)告冊 學(xué)年 第學(xué)期 學(xué) 院： 專 業(yè)： 年 級： 姓 名： 學(xué) 號： 任課教師： MySQL+Navicat安裝步驟與下載地址 百度地址： https://wenku.baidu./view/c0544d36777f5acfa1c7aa00b52acfc789eb9fea Navicat配置與簡單使用 百度地址： https://wenku.baidu./view/96b842d9b1717fd5360cba1aa8114431b90d8eee 本文檔所書寫的代碼，為本

2、人純手工敲打，并且通過軟件測試成功，歡迎大家進(jìn)行學(xué)習(xí)，如有錯誤，可聯(lián)系本人2960223883qq.實(shí)驗(yàn)一 創(chuàng)建和維護(hù)數(shù)據(jù)庫 一、實(shí)驗(yàn)?zāi)康? （1）掌握在Windows 平臺下安裝與配置MySQL 5.5 的方法。 （2）掌握啟動服務(wù)并登錄MySQL 5.5 數(shù)據(jù)庫的方法和步驟。 （3）掌握MySQL 數(shù)據(jù)庫的相關(guān)概念。 （4）掌握使用Navicat 工具和SQL 語句創(chuàng)建數(shù)據(jù)庫的方法。 （5）掌握使用Navicat 工具和SQL 語句刪除數(shù)據(jù)庫的方法。 二、實(shí)驗(yàn)要求 （1）學(xué)生提前準(zhǔn)備好實(shí)驗(yàn)報(bào)告，預(yù)習(xí)并熟悉實(shí)驗(yàn)步驟； （2）遵守實(shí)驗(yàn)室紀(jì)律，在規(guī)定的時(shí)間完成要求的容； 三

3、、實(shí)驗(yàn)容及步驟 （1）在Windows 平臺下安裝與配置MySQL 5.5.36 版。 （2）在服務(wù)對話框中，手動啟動或者關(guān)閉MySQL 服務(wù)。 （3）使用Net 命令啟動或關(guān)閉MySQL 服務(wù)。 （4）分別用Navicat 工具和命令行方式登錄MySQL。 （5）在my.ini 文件中將數(shù)據(jù)庫的存儲位置改為D:\MYSQL\DATA。 （6）創(chuàng)建數(shù)據(jù)庫。 ①使用Navicat 創(chuàng)建學(xué)生信息管理數(shù)據(jù)庫gradem。 ②使用SQL 語句創(chuàng)建數(shù)據(jù)庫MyDB。 （7）刪除數(shù)據(jù)庫。 ①使用Navicat 圖形工具刪除gradem 數(shù)據(jù)庫。 ②使用SQL 語句刪除MyDB 數(shù)據(jù)庫。

4、 四、思考題 常見的數(shù)據(jù)庫產(chǎn)品有哪些？ 五、實(shí)驗(yàn)總結(jié) 1、收獲 2、存在的問題 實(shí)驗(yàn)二 管理表 一、實(shí)驗(yàn)?zāi)康? (1) 掌握表的基礎(chǔ)知識。 (2) 掌握使用Navicat管理工具和SQL語句創(chuàng)建表的方法。 (3) 掌握表的修改、查看、刪除等基本操作方法。 二、實(shí)驗(yàn)要求 （1）學(xué)生提前準(zhǔn)備好實(shí)驗(yàn)報(bào)告，預(yù)習(xí)并熟悉實(shí)驗(yàn)步驟； （2）遵守實(shí)驗(yàn)室紀(jì)律，在規(guī)定的時(shí)間完成要求的容； 三、實(shí)驗(yàn)容及步驟 (1)在gradem數(shù)據(jù)庫中創(chuàng)建表2.1～表2.5所示結(jié)構(gòu)的表。 表2.1student表的表結(jié)構(gòu) 字段名稱 數(shù)據(jù)類型 長度 小數(shù)位數(shù) 是否允許NULL值 說明 s

5、no char 10 否 主碼 sname varchar 8 是 ssex char 2 是 sbirthday datetime 是 saddress varchar 50 是 sdept char 16 是 speciality varchar 20 是 表2.2 course表(課程名稱表)的表結(jié)構(gòu) 字段名稱 數(shù)據(jù)類型 長度 小數(shù)位數(shù) 是否允許NULL值 說明 o char 5 否 主碼 ame varchar 20 否 表2.3

6、 sc表(成績表)的表結(jié)構(gòu) 字段名稱 數(shù)據(jù)類型 長度 小數(shù)位數(shù) 是否允許NULL值 說明 sno char 10 否 組合主碼、外碼 o char 5 否 組合主碼、外碼 degree decimal 4 1 是 1～100 表2.4teacher表(教師表)的表結(jié)構(gòu) 字段名稱 數(shù)據(jù)類型 長度 小數(shù)位數(shù) 是否允許NULL值 說明 tno char 3 否 主碼 tname varchar 8 是 tsex char 2 是 tbirthday date 是 tde

7、pt char 16 是 表2.5teaching表(授課表)的表結(jié)構(gòu) 字段名稱 數(shù)據(jù)類型 長度 小數(shù)位數(shù) 是否允許NULL值 說明 o char 5 否 組合主碼、外碼 tno char 3 否 組合主碼、外碼 cterm tinyint 1 0 是 1～10 (2)向表2.1至表2.5輸入數(shù)據(jù)記錄，見表2.6～表2.10。 表2.6學(xué)生關(guān)系表student sno sname ssex sbirthday saddress sdept speciality 20050101 勇 男 1987-01

8、-12 計(jì)算機(jī)工程系 計(jì)算機(jī)應(yīng)用 20050201 晨 女 1988-06-04 信息工程系 電子商務(wù) 20050301 王敏 女 1989-12-23 數(shù)學(xué)系 數(shù)學(xué) 20050202 立 男 1988-08-25 信息工程系 電子商務(wù) 表2.7課程關(guān)系表course o ame o ame C01 數(shù)據(jù)庫 C03 信息系統(tǒng) C02 數(shù)學(xué) C04 操作系統(tǒng) 表2.8成績表sc sno o degree 20050101 C01 92 20050101 C02 85 20050101 C03

9、 88 20050201 C02 90 20050201 C03 80 表2.9教師表teacher tno tname tsex tbirthday tdept 101 新 男 1977-01-12 計(jì)算機(jī)工程系 102 錢軍 女 1968-06-04 計(jì)算機(jī)工程系 201 王小花 女 1979-12-23 信息工程系 202 小青 男 1968-08-25 信息工程系 表2.10授課表teaching o tno cterm C01 101 2 C02 102 1 C03 201 3 C04 2

10、02 4 (3)在navicat下修改表結(jié)構(gòu)。 ① 向student表中增加“入學(xué)時(shí)間”列，其數(shù)據(jù)類型為日期時(shí)間型。 ② 將student表中的sdept字段長度改為20。 ③ 將student表中的speciality字段刪除。 ④ 刪除student表。 (4) 利用SQL命令（create table、alter table、drop table）完成對表的操作 ①利用create talbe 命令完成student表和course表的定義。 ②利用alter table、drop table命令實(shí)現(xiàn)（3）中的所有任務(wù)。 四、思考題 (1) 在定義基本表語句時(shí)，NO

11、T NULL參數(shù)的作用是什么？ (2) 主碼可以建立在“值可以為NULL”的列上嗎？ 五、實(shí)驗(yàn)總結(jié)： 1、收獲 2、存在的問題 實(shí)驗(yàn)三 簡單查詢——單表無條件和有條件查詢 一、實(shí)驗(yàn)?zāi)康? (1) 掌握SELECT語句的基本用法。 (2) 使用WHERE子句進(jìn)行有條件的查詢。 (3) 掌握使用IN和NOT IN，BETWEEN…AND和NOT BETWEEN…AND來縮小查詢圍的方法。 (4) 利用LIKE子句實(shí)現(xiàn)字符串匹配查詢。 二、實(shí)驗(yàn)要求 1、學(xué)生提前準(zhǔn)備好實(shí)驗(yàn)報(bào)告，預(yù)習(xí)并熟悉實(shí)驗(yàn)步驟； 2、遵守實(shí)驗(yàn)室紀(jì)律，在規(guī)定的時(shí)間完成要求的容； 三、實(shí)驗(yàn)容及步驟 在上次

12、實(shí)驗(yàn)建立的Gradem或Gradem1數(shù)據(jù)庫中完成下面查詢： (1) 查詢所有學(xué)生的基本信息、所有課程的基本信息和所有學(xué)生的成績信息（用三條SQL語句）。 SELECT * FROM student; SELECT * FROM course; SELECT * FROM sc; (2) 查詢所有學(xué)生的學(xué)號、、性別和出生日期。 SELECT sno,sname,ssex,sbirthday FROM student; (3) 查詢所有課程的課程名稱。 SELECTame FROM course (4) 查詢前10門課程的課號及課程名稱。 SELECTo,ame

13、FROM course WHEREo<=10 (5) 查詢所有學(xué)生的及年齡。 SELECT sname ,year(now())-year(sbirthday) FROM student; (6) 查詢所有年齡大于18歲的女生的學(xué)號和。 SELECT sno,sname FROM student WHERE ssex='女'and year(now())-year(sbirthday)>18 (7) 查詢所有男生的信息。 SELECT * FROM student,teacher WHERE ssex='男'and tsex='男' (8) 查詢所有任課教師的（Tn

14、ame）和所在系別（Tdept）。 SELECT tname,tdept FROM teacher (9) 查詢“電子商務(wù)”專業(yè)的學(xué)生、性別和出生日期。 SELECT sname,ssex,sbirthday FROM student WHERE sdept='電子商務(wù)' (10) 查詢Student表中的所有系名。 SELECT sdept FROM student (11) 查詢“C01”課程的開課學(xué)期。 SELECT cterm FROM teaching WHEREo='c01' (12) 查詢成績在80～90分之間的學(xué)生學(xué)號及課號。 SELECT sno

15、,o FROM sc WHERE degree BETWEEN 80 and 90 (13) 查詢在1970年1月1日之前出生的男教師信息。 SELECT * FROM teacher WHERE tbirthday<'1970-01-01'and tsex=’男’; (14) 輸出有成績的學(xué)生學(xué)號。 SELECT sno FROM sc WHERE degree is NOT NULL; (15) 查詢所有姓“”的學(xué)生信息。 SELECT * FROM student WHERE sname LIKE'%' (16) 查詢生源地不是省的學(xué)生信息。 SELECT

16、 * FROM student WHERE saddress NOT LIKE'%' (17) 查詢成績?yōu)?9分、89分或99分的記錄。 SELECT * FROM sc WHERE degree='79' or degree='89' or degree='99' (18) 查詢名字中第二個(gè)字是“小”字的男生的學(xué)生和地址。 SELECT sname,saddress FROM student WHERE ssex='男' and sname LIKE'_小' (19) 查詢名稱以“計(jì)算機(jī)_”開頭的課程名稱。 SELECTame FROM course WHEREa

17、me LIKE'計(jì)算機(jī)_' (20) 查詢計(jì)算機(jī)工程系和軟件工程系的學(xué)生信息。 SELECT * FROM student WHERE sdept='計(jì)算機(jī)工程系' OR sdept='軟件工程系' 四、思考題 1、LIKE的通配符有哪些？分別代表什么含義？ 通配符 含義 % 有零個(gè)或更多個(gè)字符組成的任意字符串 _ 任意單個(gè)字符 [ ] 用于指定圍，例如[A ~ F]，表示A ~ F圍的任何單個(gè)字符 [ ^ ] 表示指定圍之外的，例如[ ^A ~ F ]，表示A ~ F 圍以外的任何單個(gè)字符。

18、 2、知道學(xué)生的出生日期，如何求出其年齡？ SELECT year(now())-year(sbirthday) FROM student; 3、IS能用“=”來代替嗎？ 不能 is是用來判斷null的，比如 remark is null 或者remark is not null =是用來直接比較值的。 4、關(guān)鍵字ALL和DISTINCT有什么不同的含義？ ALL表示所有的字段，有重復(fù)的2條，2 條都顯示， DISTINCT表示如果有重復(fù)的，只取一條。 五、實(shí)驗(yàn)總結(jié)： 1、收獲 經(jīng)過這節(jié)數(shù)據(jù)庫課程的學(xué)習(xí)，我基本上掌握了創(chuàng)建數(shù)據(jù)庫以及對數(shù)據(jù)庫的操作的基礎(chǔ)知識。學(xué)習(xí)了SQ

19、L數(shù)據(jù)庫中的增、刪、改、查等功能，數(shù)據(jù)庫這門課涉及到以前的知識不多，是一門從頭學(xué)起的課程，即使基礎(chǔ)不是很好，只要認(rèn)真聽講、復(fù)習(xí)功課，還是一門比較容易掌握的課。 2、存在的問題 不夠熟悉對數(shù)據(jù)庫系統(tǒng)的操作，不會運(yùn)用快捷鍵，需要更多的練習(xí)和操作。 實(shí)驗(yàn)四 簡單查詢——分組與排序 一、實(shí)驗(yàn)?zāi)康模? (1) 利用GROUP BY子句對查詢結(jié)果分組。 (2) 利用ORDER BY子句對查詢結(jié)果排序。 (3) 掌握聚集函數(shù)的使用方法。 二、實(shí)驗(yàn)要求 1、學(xué)生提前準(zhǔn)備好實(shí)驗(yàn)報(bào)告，預(yù)習(xí)并熟悉實(shí)驗(yàn)步驟； 2、遵守實(shí)驗(yàn)室紀(jì)律，在規(guī)定的時(shí)間完成要求的容； 三、實(shí)驗(yàn)容及步驟 在Gradem

20、或Gradem1數(shù)據(jù)庫中完成下面查詢： (1) 統(tǒng)計(jì)有學(xué)生選修的課程的門數(shù)。 SELECT COUNT(DISTINCTo) FROM course (2) 計(jì)算“c01”課程的平均成績。 SELECT AVG(degree) FROM sc WHEREo='c01'; (3) 查詢選修了“c03”課程的學(xué)生的學(xué)號及其成績，查詢結(jié)果按分?jǐn)?shù)降序排列。 SELECT sno,degree FROM sc WHEREo='c03' ORDER BY degree DESC; (4) 查詢各個(gè)課程號及相應(yīng)的選課人數(shù)。 SELECTo,COUNT(sno) FROM sc

21、 GROUP BYo (5) 統(tǒng)計(jì)每門課程的選課人數(shù)和最高分。 SELECT COUNT(sno),MAX(degree) FROM sc GROUP BYo (6) 統(tǒng)計(jì)每個(gè)學(xué)生的選課門數(shù)和考試總成績，并按選課門數(shù)降序排列。 SELECT COUNT(sno),sum(degree) FROM sc GROUP BY sno ORDER BY COUNT(sno) DESC; (7) 查詢選修了3門以上課程的學(xué)生學(xué)號。 SELECT sno FROM sc GROUP BY sno HAVING COUNT(o)>3 (8) 查詢成績不及格的學(xué)生學(xué)號及課號，并

22、按成績降序排列。 SELECT sno,o FROM sc WHERE degree<60 ORDER BY degree DESC (9) 查詢至少選修一門課程的學(xué)生學(xué)號。 SELECT sno FROM sc GROUP BY sno HAVING COUNT(o)>1 (10) 統(tǒng)計(jì)輸出各系學(xué)生的人數(shù) SELECT sdept ,COUNT(*) FROM student GROUP BY sdept (11) 統(tǒng)計(jì)各系的男、女生人數(shù)。（兩條命令） Select sdept,ssex,count(*) From student Group by sdep

23、t ,ssex (12) 統(tǒng)計(jì)籍貫的男、女生人數(shù)。（兩條命令） Select saddress,ssex,count(*) From student Group by saddress ,ssex (13) 統(tǒng)計(jì)各系的老師人數(shù)，并按人數(shù)升序排序。 SELECT tdept ,COUNT(*) FROM teacher GROUP BY tdept ORDER BY COUNT(*) DESC (14) 統(tǒng)計(jì)不及格人數(shù)超過10人的課程號。 SELECTo FROM sc GROUP BYo HAVING COUNT(degree<60)>10 (15)統(tǒng)計(jì)選修人數(shù)超

24、過10人的課程號。 SELECTo FROM sc GROUP BYo HAVING COUNT(*)>10 (16) 查詢軟件系的男生信息，查詢結(jié)果按出生日期升序排序，出生日期相同的按地址降序排序。 SELECT * FROM student WHERE ssex='男' and sdept='信息工程系' ORDER BY sbirthday,saddress DESC 四、思考題 (1) 聚集函數(shù)能否直接使用在SELECT子句、HAVING子句、WHERE子句、GROUP BY子句中？ (2) WHERE子句與HAVING子句有何不同？ 五、實(shí)驗(yàn)總結(jié)： 1、

25、收獲 2、存在的問題 實(shí)驗(yàn)五 多表查詢 一、實(shí)驗(yàn)?zāi)康? (1) 掌握SELECT語句在多表查詢中的應(yīng)用。 (2) 掌握多表連接的幾種連接方式及應(yīng)用。 二、實(shí)驗(yàn)要求 1、學(xué)生提前準(zhǔn)備好實(shí)驗(yàn)報(bào)告，預(yù)習(xí)并熟悉實(shí)驗(yàn)步驟； 2、遵守實(shí)驗(yàn)室紀(jì)律，在規(guī)定的時(shí)間完成要求的容； 三、實(shí)驗(yàn)容及步驟： 在Gradem或Gradem1數(shù)據(jù)庫中完成下面查詢： (1) 查詢計(jì)算機(jī)工程系女學(xué)生的學(xué)生學(xué)號、及考試成績。 SELECT student.sno,sname,sc.degree FROM student,sc WHERE student.sno=sc.sno and student.sd

26、ept='計(jì)算機(jī)工程系' and student.ssex='女'; (2) 查詢“勇”同學(xué)所選課程的成績。(不考慮重名) SELECT sc.degree FROM student,sc WHERE student.sno=sc.sno and student.sname='勇' (3) 查詢“新”老師所授課程的課程名稱。 SELECT course.ame FROM teacher,teaching,course WHERE teacher.tno=teaching.tno and teaching.o=course.o and teacher.tname='新' (4)

27、 查詢女教師所授課程的課程號及課程名稱。 SELECT course.ame,teaching.o FROM teacher,teaching,course WHERE teacher.tno=teaching.tno and teaching.o=course.o and teacher.tsex='女' (5) 查詢至少選修一門課程的女學(xué)生。 SELECT student.sname FROM student,sc WHERE student.sno=sc.sno AND student.ssex='女' GROUP BY student.sno HAVING COUNT

28、(sc.o)>1 (6) 查詢姓“王”的學(xué)生所學(xué)的課程名稱。 SELECT course.ame FROM student,sc,course WHERE student.sno=sc.sno AND sc.o=course.o AND sname='王%' (7) 查詢選修“數(shù)據(jù)庫”課程且成績在80～90分之間的學(xué)生學(xué)號及成績。 SELECT sc.degree,student.sno FROM student,sc,course WHERE student.sno=sc.sno AND sc.o=course.o ANDame='數(shù)據(jù)庫' AND degree BETW

29、EEN 80 AND 90 (8) 查詢課程成績及格的男同學(xué)的學(xué)生信息及課程號與成績。 SELECT student.*,course.o,sc.degree FROM student,sc,course WHERE student.sno=sc.sno AND sc.o=course.o AND degree>60 AND ssex='男' (9) 查詢選修“c04”課程的學(xué)生的平均年齡。 SELECT AVG(year(NOW())-YEAR(sbirthday)) FROM student,sc,course WHERE student.sno=sc.sno AND

30、sc.o=course.o AND course.o='c04' (10) 查詢學(xué)習(xí)課程名為“數(shù)學(xué)”的學(xué)生學(xué)號和。 SELECT student.sno,sname FROM student,sc,course WHERE student.sno=sc.sno AND sc.o=course.o ANDame='數(shù)學(xué)' (11) 查詢“錢軍”教師任課的課程號，選修其課程的學(xué)生的學(xué)號、和成績。 SELECT course.o,student.sno,sname,sc.degree FROM teacher,teaching,course,student,sc WHERE teac

31、her.tno=teaching.tno and teaching.o=course.o and course.o=sc.o and sc.sno=student.sno and teacher.tname='錢軍' (12) 查詢在第3學(xué)期所開課程的課程名稱及成績。 SELECT course.ame,sc.degree FROM course,sc,teaching WHERE teaching.o=course.o and course.o=sc.o and cterm='3' (13) 查詢“c02”號課程不及格的學(xué)生信息。 SELECT student.* FROM

32、 student,sc WHERE student.sno=sc.sno ando='c02' and degree<60 and degree IS NULL (14) 查詢軟件系成績在90分以上的學(xué)生、性別和課程名稱。 SELECT student.sname,ssex,course.ame FROM student,sc,course WHERE student.sno=sc.sno and sc.o=course.o and sdept='軟件系' AND degree>90 (15) 查詢同時(shí)選修了“c04”和“c02”課程的學(xué)生和成績。 SELECT student.

33、sname,sc.degree FROM student,sc,course WHERE student.sno=sc.sno and sc.o=course.o and course.o ='c04' AND course.o ='c02' 四、思考題 (1) 指定一個(gè)較短的別名有什么好處？ 更容易辨識 方便查看 (2) 連接與外連接有什么區(qū)別？ 連接: 只有兩個(gè)表相匹配的行才能在結(jié)果集中出現(xiàn) 外連接: 包括 左外連接(左邊的表不加限制) 右外連接(右邊的表不加限制) 全外連接(左右兩表都不加限制) (3) “=”與IN在什么情況下作用相同？ 選值只

34、有一個(gè) 五、實(shí)驗(yàn)總結(jié) 1、收獲 經(jīng)過近一個(gè)小時(shí)的調(diào)試，編寫，最終得出結(jié)論，并進(jìn)行書寫。使我加深了對mysql進(jìn)一步學(xué)習(xí)和認(rèn)識，更加熟練了對軟件的使用，收獲頗豐。 2、存在的問題 對于語句的使用略顯生疏，需要進(jìn)一步的練習(xí)，加深認(rèn)識。 實(shí)驗(yàn)六 嵌套查詢 一、實(shí)驗(yàn)?zāi)康? (1) 掌握嵌套查詢的使用方法。 (2) 掌握相關(guān)子查詢與嵌套子查詢的區(qū)別。 (3) 掌握帶IN謂詞的子查詢的使用方法。 (4) 掌握帶比較運(yùn)算符的子查詢的使用方法。 二、實(shí)驗(yàn)要求 1、學(xué)生提前準(zhǔn)備好實(shí)驗(yàn)報(bào)告，預(yù)習(xí)并熟悉實(shí)驗(yàn)步驟； 2、遵守實(shí)驗(yàn)室紀(jì)律，在規(guī)定的時(shí)間完成要求的容； 三、實(shí)驗(yàn)容及步驟

35、 在Grademanager數(shù)據(jù)庫中完成下面查詢： (1) 查詢計(jì)算機(jī)系(CS)學(xué)生的學(xué)生學(xué)號、及考試成績。 SELECT student.sno,student.sname,SC.degree FROM student,sc WHERE student.sno=sc.sno AND sdept='計(jì)算機(jī)系' ; (2) 查詢“勇”同學(xué)所選課程的成績。 SELECT sc.degree FROM sc,student WHERE student.sno=sc.sno AND sname='勇'; (3) 查詢“新”老師所授課程的課程名稱。 SELECT course.am

36、e FROM course,teacher,teaching WHERE teacher.tno=teaching.tno AND teaching.o=course.o AND tname='新'; (4) 查詢女教師所授課程的課程號及課程名稱。 SELECT teaching.o,course.ame FROM teaching,teacher,course WHERE teacher.tno=teaching.tno AND teaching.o=course.o AND tsex='女'; (5) 查詢姓“王”的學(xué)生所學(xué)的課程名稱。 SELECT course.ame

37、 FROM course,student,sc WHERE student.sno=sc.sno AND sc.o=course.o AND sname LIKE '王%'; (6) 查詢選修“數(shù)據(jù)庫”課程且成績在80～90分之間的學(xué)生學(xué)號及成績。 SELECT sc.sno,sc.degree FROM course,sc WHERE course.o=sc.o AND course.ame='數(shù)據(jù)庫' AND degree BETWEEN 80 AND 90; (7) 查詢選修“C04”課程的學(xué)生的平均年齡。 SELECT AVG((YEAR(CURDATE())-YEAR(

38、student.sbirthday))) AS 選修C04課程的學(xué)生平均年齡 FROM student,sc WHERE student.sno=sc.sno AND sc.o='c04' (8) 查詢學(xué)習(xí)課程名為“數(shù)學(xué)”的學(xué)生學(xué)號和。 SELECT student.sno,student.sname FROM student,course,sc WHERE student.sno=sc.sno AND course.o=sc.o AND course.ame='數(shù)學(xué)' (9) 查詢“錢軍”教師任課的課程號，選修其課程的學(xué)生的學(xué)號和成績。 SELECTo,sno,degree

39、 FROM sc WHEREo in(SELECTo FROM teaching,teacher WHERE teacher.tno=teaching.tno AND tname='錢軍') (10) 查詢在第3學(xué)期所開課程的課程號及成績。 SELECTo,degree FROM sc WHEREo in(SELECTo FROM teaching WHERE cterm='3') (11) 查詢與“勇”同一個(gè)系的同學(xué)。 SELECT sname from student WHERE sdept in( SELECT sdept FROM student WHERE sna

40、me='勇') (12) 查詢學(xué)號比“晨”同學(xué)大，而出生日期比他小的學(xué)生。 SELECT sname from student WHERE snoall (SELECT year(sbirthday) from student WHERE sname='晨') (13) 查詢出生日期大于所有女同學(xué)出生日期的男同學(xué)的及系別。 select sname,sdept from student where year(sbirthday)>all(sel

41、ect year(sbirthday)from student where ssex='女' ) and ssex='男' (14) 查詢成績比該課程平均成績高的學(xué)生的學(xué)號及成績。 select sno,degree from sc where degree >=(select avg(degree) from student,sc where student.sno=sc.sno) (15) 查詢不講授“C01”課的教師。 select tname from teacher ,teaching where teacher.tno=teaching.tno ando !='c0

42、1' (16) 查詢沒有選修“C02”課程的學(xué)生學(xué)號及。 select student.sno,sname from student,sc where student.sno=sc.sno ando!='C02' (17) 查詢選修了“數(shù)據(jù)庫”課程的學(xué)生學(xué)號、及系別。 select sname,student.sno,sdept from student,sc,course where student.sno=sc.sno and sc.o=course.o andame='數(shù)據(jù)庫' (18) 查詢“C02”號課程不及格的學(xué)生信息。 select * from stude

43、nt,sc where student.sno=sc.sno ando='c02' and degree<60 四、思考題 (1) 子查詢一般分為幾種？ 4種 (2) 相關(guān)子查詢的執(zhí)行過程是什么？ 依賴于父查詢的查詢 五、實(shí)驗(yàn)總結(jié) 1、收獲 進(jìn)一步的練習(xí)了嵌套查詢和奪標(biāo)連接，學(xué)到更多的知識，受益良多。 2、 存在的問題 還是不夠熟悉，需要進(jìn)一步的學(xué)習(xí)和復(fù)習(xí) 實(shí)驗(yàn)七 數(shù)據(jù)更新 一、實(shí)驗(yàn)?zāi)康模? (1) 掌握利用INSERT命令實(shí)現(xiàn)對表數(shù)據(jù)的插入操作。 (2) 掌握利用UPDATE命令實(shí)現(xiàn)對表數(shù)據(jù)的修改操作。 (3) 掌握利用DELETE命令實(shí)現(xiàn)對表數(shù)據(jù)的刪除操作

44、。 二、實(shí)驗(yàn)要求 1、學(xué)生提前準(zhǔn)備好實(shí)驗(yàn)報(bào)告，預(yù)習(xí)并熟悉實(shí)驗(yàn)步驟； 2、遵守實(shí)驗(yàn)室紀(jì)律，在規(guī)定的時(shí)間完成要求的容； 三、實(shí)驗(yàn)容及步驟 在Gradem或Gradem1數(shù)據(jù)庫中完成下面操作： 注意：利用SELECT INTO…命令備份Student、SC、Course這3個(gè)表，備份表名自定。 (1) 向Student表中插入記錄("20050203"，"靜"，"1981-3-21"，"女"，"CS"，"電子商務(wù)")。 INSERT INTO student(sno,sname,sbirthday,ssex,sdept,speciality) VALUES('20050203',

45、'靜','1982-3-21','女','CS','電子商務(wù)'); (2) 插入學(xué)號為“20050302”、為“四”的學(xué)生信息。 INSERT INTO student(sno,sname) VALUES('20050202','四'); (3) 把計(jì)算機(jī)系的學(xué)生記錄保存到表TS中(TS表已存在，表結(jié)構(gòu)與Student表相同)。 INSERT INTO ts SELECT * FROM student WHERE sdept='計(jì)算機(jī)' (4) 將學(xué)號為“20050202”的學(xué)生改為“華”，系別改為“CS”，專業(yè)改為“多媒體技術(shù)”。 UPDATE student SE

46、T sname='華',sdept='cs',speciality='多媒體技術(shù)' WHERE sno='20050202'; (5) 將“勇”同學(xué)的專業(yè)改為“計(jì)算機(jī)信息管理”。 UPDATE student SET speciality='計(jì)算機(jī)管理' WHERE sname='勇'; (6) 把選修了“數(shù)據(jù)庫”課程而成績不及格的學(xué)生的成績?nèi)臑榭罩?NULL)。 UPDATE SC SET degree='NULL' WHEREo in(SELECTo from course WHEREame='數(shù)據(jù)庫') AND degree<60 (7) 刪除學(xué)號為“2005

47、0302”的學(xué)生記錄。 DELETE FROM student WHERE sno='20050302' (8) 刪除“計(jì)算機(jī)系”所有學(xué)生的選課記錄。 DELETE FROM sc WHERE sno in(SELECT sno from student WHERE sdept='計(jì)算機(jī)系') (9) 刪除SC表無成績的選課記錄。 DELETE FROM sc WHERE degree='NULL' (10) 把“晨”同學(xué)的成績記錄全部刪除。 DELETE FROM sc WHERE sno in(SELECT sno FROM student WHERE

48、 sname='晨') 四、思考題 (1) DROP命令和DELETE命令的本質(zhì)區(qū)別是什么？ DROP刪除整個(gè)表，表結(jié)構(gòu)和數(shù)據(jù) DELETE刪除表的某一行，不刪除表結(jié)構(gòu) (2) 利用INSERT、UPDATE和DELETE命令可以同時(shí)對多個(gè)表進(jìn)行操作嗎？ 不能，只能單個(gè)表 五、實(shí)驗(yàn)總結(jié)： 1、收獲 進(jìn)一步的熟悉了mysql的運(yùn)用，記住了很多插入，修改，刪除的語句。 2、存在的問題 不夠熟練，需要背語句結(jié)構(gòu)。 實(shí)驗(yàn)八 視圖 一、實(shí)驗(yàn)?zāi)康模? (1) 理解視圖的概念。 (2) 掌握創(chuàng)建、更改、刪除視圖的方法。 (3) 掌握使用視圖來訪問數(shù)據(jù)的方法。 二、實(shí)驗(yàn)要求

49、 1、學(xué)生提前準(zhǔn)備好實(shí)驗(yàn)報(bào)告，預(yù)習(xí)并熟悉實(shí)驗(yàn)步驟； 2、遵守實(shí)驗(yàn)室紀(jì)律，在規(guī)定的時(shí)間完成要求的容； 三、實(shí)驗(yàn)容及步驟 1) 使用SQL語句創(chuàng)建、管理視圖 (1) 創(chuàng)建視圖。 ① 創(chuàng)建一個(gè)名為sc_view1的視圖，從數(shù)據(jù)庫gradem的sc表中查詢出成績大于90分的所有學(xué)生選修成績的信息。 ② 創(chuàng)建一個(gè)名為sc_view2的視圖，從數(shù)據(jù)庫gradem的sc表中查詢出成績小于80分的所有學(xué)生的學(xué)號、課程號、成績等信息。 ③ 創(chuàng)建一個(gè)名為sc_view3的視圖，由數(shù)據(jù)庫gradem的student、course、sc表創(chuàng)建一個(gè)顯示“20070303”班學(xué)生選修課程(包括學(xué)生、課程名

50、稱、成績等信息)的視圖。 ④ 創(chuàng)建一個(gè)從視圖sc_view1中查詢出課程號“c01”的所有學(xué)生的視圖。 (2) 修改視圖的定義。 修改視圖sc_view1，使其從數(shù)據(jù)庫gradem的sc表中查詢出成績大于90分且第3學(xué)期的所有學(xué)生選修成績的信息。 (3) 視圖的刪除。 ① 將視圖sc_view1刪除。 (4) 管理視圖中的數(shù)據(jù)。 ① 從視圖sc_view2中查詢出學(xué)號為“2007030125”、課程號為“a01”的學(xué)生選修成績的信息。 ② 將視圖sc_view2中學(xué)號為“2007030122”、課程號為“c02”的成績改為87。 ③ 從視圖sc_view2中將學(xué)號為“200

51、7030123”、課程號為“a01”的學(xué)生信息刪除。 四、思考題 向視圖中插入的數(shù)據(jù)能進(jìn)入到基本表中去嗎？修改基本表的數(shù)據(jù)會自動反映到相應(yīng)的視圖中去嗎？ 五、實(shí)驗(yàn)總結(jié) 1、收獲 2、存在的問題 實(shí)驗(yàn)九 數(shù)據(jù)庫的安全性 一、實(shí)驗(yàn)?zāi)康模? (1) 理解My SQL的權(quán)限系統(tǒng)的工作原理。 (2) 理解My SQL賬戶及權(quán)限的概念。 (3) 掌握管理My SQL賬戶和權(quán)限的方法。 (4) 學(xué)會創(chuàng)建和刪除普通用戶的方法和密碼管理的方法。 (5) 學(xué)會如何進(jìn)行權(quán)限管理。 二、實(shí)驗(yàn)要求 1、學(xué)生提前準(zhǔn)備好實(shí)驗(yàn)報(bào)告，預(yù)習(xí)并熟悉實(shí)驗(yàn)步驟； 2、遵守實(shí)驗(yàn)室紀(jì)律，在規(guī)定的時(shí)間完成要求的

52、容； 三、實(shí)驗(yàn)容及步驟 1)利用Navicat圖形工具實(shí)現(xiàn)下列操作： ① 使用root用戶創(chuàng)建aric用戶，初始密碼設(shè)置為abcdef。讓該用戶對gradem數(shù)據(jù)庫擁有SELECT、UPDATE、DROP權(quán)限。 ②用aric用戶登錄，利用aric用戶來驗(yàn)證自己是否有GRANT權(quán)限和CREATE權(quán)限。 ③用root用戶登錄，收回aric用戶的刪除權(quán)限。 2) 利用命令實(shí)現(xiàn)下列操作： 進(jìn)行本實(shí)驗(yàn)的準(zhǔn)備工作是：以root的身份完成了表student、course、sc的創(chuàng)建。 （一） 授權(quán) 1.以root的身份登陸mysql，創(chuàng)建用戶u1+學(xué)號后四位，u2+學(xué)號后四位，u3+

53、學(xué)號后四位，u4+學(xué)號后四位。 2.用戶root把查詢Student表權(quán)限授給用戶u1+學(xué)號后四位，驗(yàn)證u1能夠執(zhí)行相應(yīng)的查詢。 1） 查詢所有姓的學(xué)生的、學(xué)號和性別。 2） 查詢名字中第二字為“勇”字的學(xué)生的和學(xué)號。 3. 用戶root授予用戶u4+學(xué)號后四位對student表的insert權(quán)限，并允許此權(quán)限傳播。 4. 用戶u4+學(xué)號后四位將對表student的insert權(quán)限授予u3+學(xué)號后四位，并允許將權(quán)限轉(zhuǎn)授給其他用戶（例如：u3+學(xué)號后四位），描述過程及操作。 （二） 回收權(quán)限 1． 收回所有用戶對表sc的查詢權(quán)限 2． 收

54、回用戶u4對student表的insert權(quán)限 3． 在回收權(quán)限之后驗(yàn)證用戶是否真正喪失了該權(quán)限（查詢表，插入記錄），描述過程及提示信息。 四、實(shí)驗(yàn)總結(jié) 1、收獲 2、 存在的問題 實(shí)驗(yàn)十 數(shù)據(jù)庫的完整性 一、實(shí)驗(yàn)?zāi)康模? (1) 理解My SQL的實(shí)體完整性的定義。 (2) 理解My SQL的參照完整性的定義。 (3) 理解My SQL的自定義完整性的定義。 二、實(shí)驗(yàn)要求 1、學(xué)生提前準(zhǔn)備好實(shí)驗(yàn)報(bào)告，預(yù)習(xí)并熟悉實(shí)驗(yàn)步驟； 2、遵守實(shí)驗(yàn)室紀(jì)律，在規(guī)定的時(shí)間完成要求的容； 三、 實(shí)驗(yàn)容及步驟 （一） 實(shí)體完整性 1．建立部門表DEPT(deptno,dept

55、name)，部門編號Deptno列為主碼。寫出兩種方式：一種在列級定義主碼，一種是在表級定義主碼。 2．建立成績表sc1(sno,o,grade)，將sno、o屬性組定義為碼。 （二） 參照完整性 1． 建立成績表sc2(sno,o,grade)，（sno、o）是主碼，sno、o分別參照引用student表上的sno和course表的o主碼。 2．insert操作驗(yàn)證外鍵約束 （1）在sc2中插入數(shù)據(jù)（’1001’,’01’,95），假設(shè)o=’01’的數(shù)據(jù)存在，而sno=’1001’的數(shù)據(jù)不存在，執(zhí)行結(jié)果是什么？ （2）在student表中添加sno=’1001’的數(shù)據(jù)，然后繼續(xù)執(zhí)

56、行（1）中的插入數(shù)據(jù)操作，執(zhí)行結(jié)果是什么？ 3、delete操作驗(yàn)證外鍵約束 （1）MySQL在外鍵的刪除上有RESTRICT、NO ACTION(類似RESTRICT)、CASCADE和SET NULL四種行為，默認(rèn)是那種行為？在默認(rèn)情況下，對student表中的sno=’1001’的記錄，執(zhí)行刪除操作時(shí)，執(zhí)行結(jié)果是什么？若執(zhí)意要刪除該條記錄，需要如何操作？ （2）SET NULL指當(dāng)刪除主表中被引用列的數(shù)據(jù)時(shí)，將子表中相應(yīng)引用列的值設(shè)置為NULL值。SET NULL有個(gè)前提就是外鍵引用列必須可以設(shè)置為NULL。 把sc表的sno外鍵約束刪除，將外鍵約束的刪除行為改為SET NULL

57、，并驗(yàn)證該刪除策略。 （3）CASCADE指當(dāng)刪除主表中被引用列的數(shù)據(jù)時(shí)，級聯(lián)刪除子表中相應(yīng)的數(shù)據(jù)行。 把sc表的sno外鍵約束刪除，將外鍵約束的刪除行為改為CASCADE。，并驗(yàn)證該刪除策略。 （三）自定義完整性 1．修改部門表DEPT，要求添加約束constraint c1：部門名稱Dname列取值唯一。 2．建立學(xué)生登記表Student2，要求學(xué)號在9000至9999之間（約束名默認(rèn)），年齡<29（約束名為a2）,性別只能是’男’或’女’ （約束名默認(rèn)），非空。 3．修改表Student的結(jié)構(gòu)，由年齡小于29改為小于40。（先刪除原有的約束，再添加新約束） 四、實(shí)驗(yàn)總結(jié) 1、收獲 2、存在的問題 20 / 20