華南理工大學(xué)-計(jì)算機(jī)學(xué)院-數(shù)據(jù)庫-實(shí)驗(yàn)一-報(bào)告WORD

1、文檔可能無法思考全面，請瀏覽后下載! 華南理工大學(xué)數(shù)據(jù)庫課程實(shí)驗(yàn)報(bào)告實(shí)驗(yàn)題目： 實(shí)驗(yàn)一：SQL的使用 姓名： 學(xué)號： 班級： 計(jì)科一班 組別： 無 合作者： 無 指導(dǎo)教師： 董守玲 實(shí)驗(yàn)概述【實(shí)驗(yàn)?zāi)康募耙蟆繉?shí)驗(yàn)?zāi)康模?通過交互式SQL的使用， 掌握數(shù)據(jù)庫的創(chuàng)建、插入、更新、查詢等基本操作實(shí)驗(yàn)要求：1 創(chuàng)建Student數(shù)據(jù)庫（oracle可以不創(chuàng)建新的數(shù)據(jù)庫，直接創(chuàng)建下列表就可以了）包括Students,Courses,SC表，表結(jié)構(gòu)如下：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的男生的學(xué)號和姓名；（2）查詢計(jì)算機(jī)系秋季所開課程的課程號和學(xué)分?jǐn)?shù)；（3）查詢選修計(jì)算機(jī)系秋季所開課程的男生的姓名、課程號、學(xué)分?jǐn)?shù)、成績；（4）查詢至少選修一門電機(jī)系課程的女生的姓名（假設(shè)電機(jī)系課程的課程號以EE開頭）；（5）查詢每位學(xué)生已選修課程的門數(shù)和總平均成績；（6）查詢每門課程選課的學(xué)生人數(shù),最高成績,最低成績和平均成績；（7）查詢所有課程的成績都在80分以上的學(xué)生的姓名、學(xué)號、且按學(xué)號升序排列；（8）

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

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

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

6、,Courses,SC表，表結(jié)構(gòu)如下：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、生的學(xué)號和姓名；SELECT SNO,SNAMEFROM StudentsWHERE HEIGHT>1.80 AND SEX LIKE '男'（2）查詢計(jì)算機(jī)系秋季所開課程的課程號和學(xué)分?jǐn)?shù)；SELECT CNO,CREDITFROM CoursesWHERE CNO LIKE'CS%' AND SEMESTER LIKE'秋'（3）查詢選修計(jì)算機(jī)系秋季所開課程的男生的姓名、課程號、學(xué)分?jǐn)?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）查詢至少選修一門電機(jī)系課程的女生的姓名（假設(shè)電機(jī)系課程的課程號以EE開頭）；SELECT DISTINCT SNAMEFROM Students,SCWHERE Students.SNO=SC.SNO AND CNO LIKE'EE%' AND SEX LIKE'女'（5）查詢每位學(xué)生已選修課程的門數(shù)和總平均成績；SELECT SNO,COUNT(*),AVG(GRADE)FROM

10、SCGROUP BY SNO;（6）查詢每門課程選課的學(xué)生人數(shù),最高成績,最低成績和平均成績；SELECT CNO,COUNT(*),MAX(GRADE),MIN(GRADE),AVG(GRADE)FROM SCGROUP BY CNO;（7）查詢所有課程的成績都在80分以上的學(xué)生的姓名、學(xué)號、且按學(xué)號升序排列；SELECT DISTINCT SNAME,SC.SNOFROM Students,SCWHERE Students.SNO=SC.SNO AND GRADE>80ORDER BY SC.SNO ASC;（8）查詢?nèi)背煽兊膶W(xué)生的姓名，缺成績的課程號及其學(xué)分?jǐn)?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）查詢有一門以上(含一門)三個(gè)學(xué)分以上課程的成績低于70分的學(xué)生的姓名；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年出生的學(xué)生的姓名,總平均成績及已修學(xué)分?jǐn)?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關(guān)系中，刪去SNO以01開關(guān)的所有記錄。DELETE FROM StudentsWHERE SNO LIKE'01%'DELETE FROM SCWHERE SN

13、O LIKE'01%'（12）在關(guān)系中增加以下記錄： <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的學(xué)分?jǐn)?shù)增為，講課時(shí)數(shù)增為UPDATE CoursesSET CREDIT=3WHERE CNO LIKE'CS-221'UPDATE CoursesSET LHOUR ='60'WHERE CNO LIKE'CS-221'3補(bǔ)充題：(1) 統(tǒng)計(jì)各系的男生和女生的人數(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) 列出學(xué)習(xí)過編譯原理，數(shù)據(jù)庫或體系結(jié)構(gòu)課程，且這些課程的成績之一在90分以上的學(xué)生的名字。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'體系結(jié)構(gòu)');(3) 列出未修選電子技術(shù)課程，

16、但選修了數(shù)字電路或數(shù)字邏輯課程的學(xué)生數(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'電子技術(shù)');(4) 按課程排序列出所有學(xué)生的成績，尚無學(xué)生選修的課程，也需要列出，相關(guān)的學(xué)生成績用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) 列出平均成績最高的學(xué)生名字和成績。(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選做題：對每門課增加“先修課程”的屬性，用來表示某一門課程的先修課程，每門課程應(yīng)可記錄多于一門的先修課程。要求：ALTER TABLE CoursesADD PRE1 VARCHAR(10),ADD PRE2 VARCHAR(10);1) 修改表結(jié)構(gòu)的定義，應(yīng)盡量避免數(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ù)庫課程的所有學(xué)生。(該學(xué)生已經(jīng)選修過數(shù)據(jù)庫課程的所有先修課，并達(dá)到合格成績。)首先，設(shè)置數(shù)據(jù)庫課程的先修課。UPDATE CoursesSET PRE1='C1'WHERE CNAME='數(shù)據(jù)庫'結(jié)果：SELECT SNOFROM SC,CoursesWHERE SC.CNO=Courses.CNO AND SC.CNO=(SELECT PRE1 FROM Cou

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

21、而且后面直接跟用戶名，沒有on什么的。4.不是每一個(gè)表都要主鍵5.foreign key 在列級和在表級的不同表達(dá)，在列級只需要references 表名（列），在表級需要在前面加上一個(gè)6.foreign key（列名）。!注意啊，這里的列名都要加括號。而且要定義多個(gè)外碼的時(shí)候在表級要分開語句，不能用逗號分開（主碼是沒有表的參照的，所以是可以的）。7.不在表定義的時(shí)候給表添加外鍵：        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.“至少一個(gè)，一個(gè)以上”是比較能迷惑人的表達(dá)事實(shí)上有就可以了。12.如果是問空值的話，如果一旦判度有多個(gè)表的時(shí)候就要想外連接的問題。13.外鏈接是在from語句的時(shí)候就：表連接類型表on鏈接條件（就像where的一樣）。14.如果是多個(gè)表的外連接怎么辦？ 思想就是把前一個(gè)表當(dāng)作整體。15.思考方式是先看需要什么表，然后是需要什么列，看列要判斷是否要去除重復(fù)。16.插入數(shù)據(jù)是數(shù)據(jù)更新的一部分，想要定