華南理工大學-計算機學院-數(shù)據(jù)庫-實驗一-報告WORD

1、文檔可能無法思考全面，請瀏覽后下載! 華南理工大學數(shù)據(jù)庫課程實驗報告實驗題目： 實驗一：SQL的使用 姓名： 學號： 班級： 計科一班 組別： 無 合作者： 無 指導教師： 董守玲 實驗概述【實驗目的及要求】實驗目的： 通過交互式SQL的使用， 掌握數(shù)據(jù)庫的創(chuàng)建、插入、更新、查詢等基本操作實驗要求：1 創(chuàng)建Student數(shù)據(jù)庫（oracle可以不創(chuàng)建新的數(shù)據(jù)庫，直接創(chuàng)建下列表就可以了）包括Students,Courses,SC表，表結構如下：Students(SNO,SNAME,SEX,BDATE,HEIGHT,DEPARTMENT)Courses(CNO,CNAME,LHOUR,CREDIT

2、,SEMESTER)SC(SNO,CNO,GRADE)(注：下劃線表示主鍵，斜體表示外鍵)，并插入一定數(shù)據(jù)。24 / 242 完成如下的查詢要求及更新的要求。（1）查詢身高大于1.80m的男生的學號和姓名；（2）查詢計算機系秋季所開課程的課程號和學分數(shù)；（3）查詢選修計算機系秋季所開課程的男生的姓名、課程號、學分數(shù)、成績；（4）查詢至少選修一門電機系課程的女生的姓名（假設電機系課程的課程號以EE開頭）；（5）查詢每位學生已選修課程的門數(shù)和總平均成績；（6）查詢每門課程選課的學生人數(shù),最高成績,最低成績和平均成績；（7）查詢所有課程的成績都在80分以上的學生的姓名、學號、且按學號升序排列；（8）

3、查詢缺成績的學生的姓名，缺成績的課程號及其學分數(shù)；（9）查詢有一門以上(含一門)三個學分以上課程的成績低于70分的學生的姓名；（10）查詢1984年1986年出生的學生的姓名,總平均成績及已修學分數(shù)。（11） 在STUDENT和SC關系中，刪去SNO以01開關的所有記錄。（12）在關系中增加以下記錄：     <0409101  何平女1987-03-021.62>     <0408130  向陽男1986-12-111.75>（13）將課程CS-2

4、21的學分數(shù)增為，講課時數(shù)增為3補充題：(1) 統(tǒng)計各系的男生和女生的人數(shù)。(2) 列出學習過編譯原理，數(shù)據(jù)庫或體系結構課程，且這些課程的成績之一在90分以上的學生的名字。(3) 列出未修選電子技術課程，但選修了數(shù)字電路或數(shù)字邏輯課程的學生數(shù)。(4) 按課程排序列出所有學生的成績，尚無學生選修的課程，也需要列出，相關的學生成績用NULL表示。(5) 列出平均成績最高的學生名字和成績。(SELECT句中不得使用TOP n子句)4選做題：對每門課增加“先修課程”的屬性，用來表示某一門課程的先修課程，每門課程應可記錄多于一門的先修課程。要求：1) 修改表結構的定義，應盡量避免數(shù)據(jù)冗余，建立必要的主鍵

5、，外鍵。2) 設計并插入必要的測試數(shù)據(jù)，完成以下查詢：列出有資格選修數(shù)據(jù)庫課程的所有學生。(該學生已經選修過數(shù)據(jù)庫課程的所有先修課，并達到合格成績。)注意：須設計每個查詢的測試數(shù)據(jù)，并在查詢之前用INSERT語句插入表中?！緦嶒灜h(huán)境】PC機，WINDOWS操作系統(tǒng)，Oracle  或Microsoft SQL Server 數(shù)據(jù)庫實驗內容【實驗過程】一、實驗步驟：.連接數(shù)據(jù)庫.準備數(shù)據(jù).建立模式（數(shù)據(jù)庫）.建立表.插入數(shù)據(jù).開始查詢二、實驗數(shù)據(jù)：（如給定文件）三、實驗主要過程：1 創(chuàng)建Student數(shù)據(jù)庫（oracle可以不創(chuàng)建新的數(shù)據(jù)庫，直接創(chuàng)建下列表就可以了）包括Students

6、,Courses,SC表，表結構如下：Students(SNO,SNAME,SEX,BDATE,HEIGHT,DEPARTMENT)Courses(CNO,CNAME,LHOUR,CREDIT,SEMESTER)SC(SNO,CNO,GRADE) (注：下劃線表示主鍵，斜體表示外鍵)，并插入一定數(shù)據(jù)。CREATE TABLE Students(SNO VARCHAR(15) PRIMARY KEY,SNAME VARCHAR(5),SEX VARCHAR(3),BDATE VARCHAR(20),HEIGHT NUMERIC(3,2),DEPARTMENT VARCHAR(10);CREATE

7、 TABLE Courses(CNO VARCHAR(10) PRIMARY KEY,CNAME VARCHAR(10),LHOUR VARCHAR(3),CREDIT NUMERIC(2,1),SEMESTER VARCHAR(2);CREATE TABLE SC(SNO VARCHAR(15),CNO VARCHAR(10),GRADE SMALLINT,FOREIGN KEY (SNO) REFERENCES Students(SNO),FOREIGN KEY (CNO) REFERENCES Courses(CNO);2 完成如下的查詢要求及更新的要求。（1）查詢身高大于1.80m的男

8、生的學號和姓名；SELECT SNO,SNAMEFROM StudentsWHERE HEIGHT>1.80 AND SEX LIKE '男'（2）查詢計算機系秋季所開課程的課程號和學分數(shù)；SELECT CNO,CREDITFROM CoursesWHERE CNO LIKE'CS%' AND SEMESTER LIKE'秋'（3）查詢選修計算機系秋季所開課程的男生的姓名、課程號、學分數(shù)、成績；SELECT SNAME,SC.CNO,CREDIT,GRADEFROM Students,Courses,SCWHERE Students.SNO

9、=SC.SNO AND Courses.CNO=SC.CNO AND SC.CNO LIKE'CS%' AND SEMESTER LIKE'秋'（4）查詢至少選修一門電機系課程的女生的姓名（假設電機系課程的課程號以EE開頭）；SELECT DISTINCT SNAMEFROM Students,SCWHERE Students.SNO=SC.SNO AND CNO LIKE'EE%' AND SEX LIKE'女'（5）查詢每位學生已選修課程的門數(shù)和總平均成績；SELECT SNO,COUNT(*),AVG(GRADE)FROM

10、SCGROUP BY SNO;（6）查詢每門課程選課的學生人數(shù),最高成績,最低成績和平均成績；SELECT CNO,COUNT(*),MAX(GRADE),MIN(GRADE),AVG(GRADE)FROM SCGROUP BY CNO;（7）查詢所有課程的成績都在80分以上的學生的姓名、學號、且按學號升序排列；SELECT DISTINCT SNAME,SC.SNOFROM Students,SCWHERE Students.SNO=SC.SNO AND GRADE>80ORDER BY SC.SNO ASC;（8）查詢缺成績的學生的姓名，缺成績的課程號及其學分數(shù)；SELECT SNA

11、ME,SC.CNO,CREDITFROM SC LEFT OUTER JOIN Students ON SC.SNO=Students.SNO LEFT OUTER JOIN Courses ON SC.CNO=Courses.CNOWHERE GRADE IS NULL;（9）查詢有一門以上(含一門)三個學分以上課程的成績低于70分的學生的姓名；SELECT DISTINCT SNAMEFROM Students,Courses,SCWHERE Students.SNO=SC.SNO AND Courses.CNO=SC.CNO AND CREDIT>3 AND GRADE<70

12、;（10）查詢1984年1986年出生的學生的姓名,總平均成績及已修學分數(shù)SELECT SNAME,AVG(GRADE),SUM(CREDIT)FROM Students,Courses,SCWHERE Students.SNO=SC.SNO AND Courses.CNO=SC.CNO AND (BDATE BETWEEN 1984 AND 1986 ) GROUP BY SNAME;（11） 在STUDENT和SC關系中，刪去SNO以01開關的所有記錄。DELETE FROM StudentsWHERE SNO LIKE'01%'DELETE FROM SCWHERE SN

13、O LIKE'01%'（12）在關系中增加以下記錄： <0409101 何平女1987-03-021.62> <0408130 向陽男1986-12-111.75>INSERTINTO Students(SNO,SNAME,SEX,BDATE,HEIGHT)VALUES(0409101,'何平','女',1987-03-02,1.62);INSERTINTO Students(SNO,SNAME,SEX,BDATE,HEIGHT)VALUES(0408130,'向陽','男',1986-12

14、-11,1.75);（13）將課程CS-221的學分數(shù)增為，講課時數(shù)增為UPDATE CoursesSET CREDIT=3WHERE CNO LIKE'CS-221'UPDATE CoursesSET LHOUR ='60'WHERE CNO LIKE'CS-221'3補充題：(1) 統(tǒng)計各系的男生和女生的人數(shù)。SELECT DEPARTMENT, SUM(CASE WHEN SEX='男' THEN 1 ELSE 0 END)男生人數(shù), SUM(CASE WHEN SEX='女' THEN 1 ELSE 0 E

15、ND)女生人數(shù)FROM StudentsGROUP BY DEPARTMENT;(2) 列出學習過編譯原理，數(shù)據(jù)庫或體系結構課程，且這些課程的成績之一在90分以上的學生的名字。SELECT DISTINCT SNAMEFROM Students,SC,CoursesWHERE Students.SNO=SC.SNO AND Courses.CNO=SC.CNO AND GRADE>90 AND (CNAME LIKE'編譯原理' OR CNAME LIKE'數(shù)據(jù)庫' OR CNAME LIKE'體系結構');(3) 列出未修選電子技術課程，

16、但選修了數(shù)字電路或數(shù)字邏輯課程的學生數(shù)。SELECT COUNT(*)FROM CoursesWHERE (CNAME LIKE'數(shù)字電路'OR CNAME LIKE'數(shù)字邏輯')AND NOT EXISTS (SELECT* FROM SC WHERE SC.CNO=Courses.CNO AND CNAME LIKE'電子技術');(4) 按課程排序列出所有學生的成績，尚無學生選修的課程，也需要列出，相關的學生成績用NULL表示。SELECT DISTINCT SC.CNO,CNAME,SNO,GRADEFROM Courses LEFT O

17、UTER JOIN SC ON Courses.CNO=SC.CNOGROUP BY SC.CNO,CNAME,SNO,GRADEORDER BY SC.CNO,CNAME,SNO,GRADE;(5) 列出平均成績最高的學生名字和成績。(SELECT句中不得使用TOP n子句)SELECT SNAME,AVG_GRADEFROM Students,(SELECT SNO,AVG(GRADE)AVG_GRADE FROM SC GROUP BY SNO)AS AVG_SCWHERE Students.SNO=AVG_SC.SNO AND AVG_GRADE=(SELECT MAX(AVG_GRA

18、DE2) FROM (SELECT SNO,AVG(GRADE)AVG_GRADE2 FROM SC GROUP BY SNO)AS AVG_SC2);4選做題：對每門課增加“先修課程”的屬性，用來表示某一門課程的先修課程，每門課程應可記錄多于一門的先修課程。要求：ALTER TABLE CoursesADD PRE1 VARCHAR(10),ADD PRE2 VARCHAR(10);1) 修改表結構的定義，應盡量避免數(shù)據(jù)冗余，建立必要的主鍵，外鍵。ALTER TABLE CoursesADD CONSTRAINT THE_KEYFOREIGN KEY(PRE1)REFERENCES Cour

19、ses(CNO);UPDATE CoursesSET PRE1='C1'WHERE CNAME='數(shù)據(jù)庫'2) 設計并插入必要的測試數(shù)據(jù)，完成以下查詢：列出有資格選修數(shù)據(jù)庫課程的所有學生。(該學生已經選修過數(shù)據(jù)庫課程的所有先修課，并達到合格成績。)首先，設置數(shù)據(jù)庫課程的先修課。UPDATE CoursesSET PRE1='C1'WHERE CNAME='數(shù)據(jù)庫'結果：SELECT SNOFROM SC,CoursesWHERE SC.CNO=Courses.CNO AND SC.CNO=(SELECT PRE1 FROM Cou

20、rses WHERE CNO='C2');小結通過這次實驗，我收獲了很多。首先，是課本的理論知識通過實驗得到了驗證，加深了我對它們的理解。其次，對于數(shù)據(jù)庫實踐的操作，通過各個習題的練習，我較為熟練地掌握了它們的用法。因此，對日后使用數(shù)據(jù)庫進行操作也增加了信心。最后，對于具體的題目，通過實際編程這種更為嚴謹?shù)倪^程。我也總結了不少的技巧和方法。較為系統(tǒng)的知識在課程學習過程中大致已經掌握，現(xiàn)將實踐中一些小的知識點歸納如下：1.sql啟動的時候要用：mysql -uroot -p,不需要密碼。2.在使用之前要先選擇數(shù)據(jù)庫3.創(chuàng)建模式是authorization而不是authorize，

21、而且后面直接跟用戶名，沒有on什么的。4.不是每一個表都要主鍵5.foreign key 在列級和在表級的不同表達，在列級只需要references 表名（列），在表級需要在前面加上一個6.foreign key（列名）。!注意啊，這里的列名都要加括號。而且要定義多個外碼的時候在表級要分開語句，不能用逗號分開（主碼是沒有表的參照的，所以是可以的）。7.不在表定義的時候給表添加外鍵：        alter table A        add constraint foreign_key  &

22、#160;     foreign key(the_key)        references B(.)8.一定要注意reference 的s!9.對于主碼和外鍵，數(shù)據(jù)類型必須是相同的，即便是字符串類型的位數(shù)也必須是一樣的。10.sql語句的注釋：單行;多行*/11.“至少一個，一個以上”是比較能迷惑人的表達事實上有就可以了。12.如果是問空值的話，如果一旦判度有多個表的時候就要想外連接的問題。13.外鏈接是在from語句的時候就：表連接類型表on鏈接條件（就像where的一樣）。14.如果是多個表的外連接怎么辦？ 思想就是把前一個表當作整體。15.思考方式是先看需要什么表，然后是需要什么列，看列要判斷是否要去除重復。16.插入數(shù)據(jù)是數(shù)據(jù)更新的一部分，想要定