數(shù)據(jù)庫實(shí)驗(yàn)報(bào)告

1、合肥工業(yè)大學(xué) 數(shù)據(jù)庫系統(tǒng)原理及應(yīng)用合 肥 工 業(yè) 大 學(xué)實(shí) 驗(yàn) 報(bào) 告 一課程名稱：數(shù)據(jù)庫系統(tǒng)原理及應(yīng)用學(xué)號(hào)： 20095378 姓名： 李偉 專業(yè)班級(jí)： 電子商務(wù)09-1班 指導(dǎo)教師： 馬華偉 二零一一年十月一、實(shí)驗(yàn)?zāi)康模海?）熟練掌握數(shù)據(jù)更新語句，靈活地操作插入數(shù)據(jù)、修改數(shù)據(jù)和刪除數(shù)據(jù)；（2）熟練掌握關(guān)系數(shù)據(jù)庫中的完整性概念的應(yīng)用；（3）了解數(shù)據(jù)錄入的方法和過程。（4）掌握單表查詢的基本方法；（5）掌握連接查詢的基本方法；（6）熟練掌握以下練習(xí)，并進(jìn)行以下各類查詢： 選擇表中的若干列、查詢?nèi)苛小⒉樵兘?jīng)過計(jì)算的值； 選擇表中的若干元組，即消除取值重復(fù)的行與查詢滿足指定條件的元組（包括：比較

2、大小、確定范圍、確定集合、字符匹配、涉及空值和多種條件查詢）； 對(duì)查詢結(jié)果排序； 使用集函數(shù)； 對(duì)查詢結(jié)果分組（7）熟練掌握以下練習(xí)，并進(jìn)行下列各類連接查詢： 等值與非等值連接查詢； 自身連接； 外連接； 復(fù)合條件連接；（8）掌握嵌套查詢的基本方法；（9）掌握集合查詢的基本方法；（10）熟練掌握查詢視圖操作（11）熟練掌握更新視圖操作（12）熟練關(guān)系的完整性概念，領(lǐng)會(huì)視圖的用途（13）練習(xí)以下各類查詢： 帶有IN謂詞的子查詢； 帶有比較運(yùn)算符的子查詢； 帶有ANY或ALL謂詞的子查詢； 帶有EXISTS謂詞的子查詢； 視圖查詢與更新操作；二、實(shí)驗(yàn)要求：（1）為了便于程序正確性測(cè)試，需要對(duì)自己建

3、立起來的數(shù)據(jù)表錄入一些模擬數(shù)據(jù)（模擬數(shù)據(jù)是指符合數(shù)據(jù)格式要求的假數(shù)據(jù)），然后按照教材中結(jié)構(gòu)化查詢語言SQL章節(jié)例題和習(xí)題自己組織SQL語句：（2）設(shè)計(jì)的SQL程序應(yīng)該涵蓋數(shù)據(jù)更新操作的全部?jī)?nèi)容不，包括插入、修改、刪除；（3）綜合運(yùn)用SQL語句實(shí)現(xiàn)數(shù)據(jù)插入、修改、刪除等操作的綜合運(yùn)用能力。（4）按照SQL語言編程要求，實(shí)現(xiàn)各類查詢和檢索操作；（5）利用后臺(tái)的SQL平臺(tái)環(huán)境，編程驗(yàn)證數(shù)據(jù)庫的控制保護(hù)功能。（6）將查詢視圖命令等價(jià)改寫為對(duì)數(shù)據(jù)表的查詢操作命令，利用SQL編程設(shè)計(jì)完成并進(jìn)行效率分析。三、實(shí)驗(yàn)內(nèi)容：（1）根據(jù)以下給定的數(shù)據(jù)表信息分別對(duì)student, course, score, tea

4、cher, teching 表進(jìn)行數(shù)據(jù)插入和修改，以完成給定的數(shù)據(jù)錄入，同時(shí)要求輸入班級(jí)同學(xué)名錄模擬客觀真實(shí)情況，給student、score表添加30條以上記錄，以便以后查詢應(yīng)用方便。Student表：SnosnameSbirthdaySclass011110101章海潮1982.02.07信管系0101011111103王天力1982.03.04電商系0101011111204董丞悟1982.06.06電商系0102011112110馬麗鵑1982.04.03工商系0101011113104楊乾坤1982.03.23會(huì)計(jì)系0101011113221劉抗日1983.01.16會(huì)計(jì)系01020

5、11114111丘海棠1983.02.08旅游系0101011115208齊振國1982.07.22旅游系0102001011101王曉悅1982.01.12材料系0001001011212程伊蓮1982.03.21材料系0002course表：CnoCnameCpnoCreditCtimeC001數(shù)據(jù)庫原理C005464C002高等數(shù)學(xué)472C003管理信息系統(tǒng)C001454C004操作系統(tǒng)C006332C005數(shù)據(jù)結(jié)構(gòu)C007432C006計(jì)算機(jī)文化基礎(chǔ)232C007C語言程序設(shè)計(jì)C006332score表：SnoCnoScore011110101C00190011111103C00185

6、011111204C00173011112110C00198011113104C00196011113221C00197011114111C00183011115208C00185011110101C00792011111103C00797teacher表：TnoTnameTsexDepartmentT001江承基男信息管理系T002梁其征男信息管理系T003徐茉莉女信息管理系T004吳雅云女信息管理系T005楊運(yùn)鐸男電子商務(wù)系T006張昌盛男電子商務(wù)系T007程力衡男電子商務(wù)系Teaching表：TnocnotdateclassroomsclassT001C0051988-01-09西二40

7、5信管系0101T002C0071990-02-01西二406電商系0101T003C0011985-09-10西二504電商系0102T004C0061984-07-15主401工商系0101T005C0041989-11-12主402會(huì)計(jì)系0101T006C0021991-05-21西二506會(huì)計(jì)系0102（2）將兩個(gè)同學(xué)（數(shù)據(jù)自己臨時(shí)設(shè)置，用后即刪除）的兩門課程的成績(jī)以運(yùn)行SQL語句插入的形式加入score表中。該題用以驗(yàn)證、理解和掌握關(guān)系模型的完整性規(guī)則；（3）求每門課的平均成績(jī)，并把結(jié)果存入average表；（4）將學(xué)生“馬麗鵑”的出生日期改為“1982.8.20”；（5）將所有學(xué)生

8、的zipcode屬性列值填補(bǔ)上；（6）將average表中的所有課程的平均成績(jī)置零；（7）刪除average表中的課程號(hào)為c007的平均成績(jī)記錄；（8）刪除所有average表中平均成績(jī)記錄；（9）建立一個(gè)臨時(shí)學(xué)生信息表（tstudent），刪除該表中的學(xué)號(hào)前六位為001011的所有學(xué)生記錄。（10）查詢?nèi)w學(xué)生的學(xué)號(hào)與姓名；（11）查詢?nèi)w學(xué)生的學(xué)號(hào)、姓名、所屬班級(jí)；（12）查詢?nèi)w學(xué)生的詳細(xì)記錄；（13）查詢?nèi)w學(xué)生的姓名及其年齡；（14）查詢?nèi)w學(xué)生的姓名、出生年份；（15）查詢所有修過課的學(xué)生的學(xué)號(hào)；（16）查詢“信管系0101”班全體學(xué)生名單；（17）查詢查詢所有年齡在27歲以下的學(xué)

9、生姓名及其年齡；（18）查詢考試成績(jī)有不及格的學(xué)生的學(xué)號(hào)；（19）查詢年齡在15至28歲之間的學(xué)生姓名、班級(jí)和年齡；（20）查詢年齡不在15至28歲之間的學(xué)生姓名、班級(jí)和年齡；（21）查詢“信管系0101”和“電商系0102”班的學(xué)生的姓名和班級(jí)信息；（22）查詢既不是“信管系0101”也不是“電商系0102”班的學(xué)生的姓名和班級(jí)信息；（23）查詢學(xué)號(hào)為“011113104”的學(xué)生的詳細(xì)情況；（24）查詢學(xué)號(hào)以“0111”打頭的學(xué)生信息；（25）查詢所有姓“張”學(xué)生的學(xué)號(hào)、姓名、性別、年齡；（26）查詢名字中第二個(gè)字有“?！弊值膶W(xué)生的學(xué)號(hào)、姓名、性別、年齡；（27）查詢所有不姓“劉”學(xué)生的姓名

10、；（28）查詢課程號(hào)以“C”開頭的最后兩個(gè)字母為“05”的課程號(hào)和課程名；（29）某些學(xué)生選修某門課程后沒有參加考試，所以有選修課記錄，但沒有考試成績(jī)，試查找缺少考試成績(jī)的學(xué)生和相應(yīng)的課程號(hào)；（30）查找全部有成績(jī)記錄的學(xué)生學(xué)號(hào)、課程號(hào)；（31）查找“電商系0101”班年齡在27歲以下的學(xué)生學(xué)號(hào)、姓名；（32）查找選修了“C001”號(hào)課程的學(xué)生學(xué)號(hào)及其成績(jī)，查詢結(jié)果按分?jǐn)?shù)降序排序；（33）查詢?nèi)w學(xué)生情況，查詢結(jié)果按所在班級(jí)升序排列，對(duì)同一班級(jí)中的學(xué)生按年齡降序排列；（34）查詢學(xué)生總?cè)藬?shù)；（35）查詢選修了課程的學(xué)生人數(shù)；（36）在所有課程中查詢最高分的學(xué)生學(xué)號(hào)和成績(jī)；（37）查詢學(xué)習(xí)“C0

11、01”課程的學(xué)生最高分?jǐn)?shù)；（38）計(jì)算各個(gè)課程號(hào)與相應(yīng)的選課人數(shù)；（39）查詢“電商系0102”班選修了兩門課程以上的學(xué)生學(xué)號(hào)、姓名；（40）自然連接student和score表；（41）使用自身連接查詢每一門課程的間接先行課（即先行課的先行課）（42）使用復(fù)合條件連接查詢選修“c001”號(hào)課程且成績(jī)?cè)?0分以上的所有同學(xué)；（43）使用復(fù)合條件連接查詢每個(gè)學(xué)生選修的課程名及其成績(jī)；（44）查詢選修了全部課程的學(xué)生；（45）查詢至少選修全部學(xué)分?jǐn)?shù)為4個(gè)學(xué)分的課程的學(xué)生的學(xué)號(hào)、姓名；（46）查詢所有選修了C001號(hào)課程的學(xué)生學(xué)號(hào)、姓名；（47）查詢選修了課程C001或c007的學(xué)生學(xué)號(hào)、姓名；（4

12、8）查詢“會(huì)計(jì)系0102”班的學(xué)生及年齡不大于27歲（現(xiàn)有年齡）的學(xué)生；（49）查詢既選修了課程C001又選修了課程c007的所有學(xué)生學(xué)號(hào)、姓名；（50）查詢選修了課程名為“數(shù)據(jù)庫原理”的學(xué)生的學(xué)號(hào)、姓名、性別、年齡；（51）查詢其他班中比“信管系0101”班所有學(xué)生年齡都小的學(xué)生名單；（52）查詢與“齊振國”在同一個(gè)班學(xué)習(xí)的學(xué)生學(xué)號(hào)、姓名、性別、年齡；（53）建立“信管系0101”班學(xué)生的視圖，定義視圖名為“info_student1”；（54）建立“信管系0101”班學(xué)生的視圖，定義視圖名為“info_student2”，并要求進(jìn)行修改與插入時(shí)，仍須保證該視圖只有“信管系0101”班學(xué)生；

13、（55）建立“信管系0101”班選修了“C001”課程的學(xué)生的視圖，定義視圖名為“info_C001_student1”；（56）建立“信管系0101”班選修了“C001”課程且成績(jī)?cè)?0分以上的學(xué)生的視圖，定義視圖名為“info_c001_student2”；（57）定義一個(gè)反映學(xué)生年齡的視圖，定義視圖名為“vbirthday_student”；（58）將學(xué)生表中所有女生記錄定義為一個(gè)視圖，視圖名為“vfemale_student”；（59）將學(xué)生的學(xué)號(hào)及其平均成績(jī)定義為一個(gè)視圖，視圖名為“vaverage_student”；（60）刪除視圖“info_student1”，刪除后即重建；（6

14、1）在“信管系0101”班學(xué)生視圖中找出年齡小于27歲（現(xiàn)在的年齡）的學(xué)生；（62）利用視圖查詢“信管系0101”班選修了“C001”課程的學(xué)生；（63）通過“信管系0101”班info_student2視圖中學(xué)號(hào)“011111103”的學(xué)生姓名改為“潘長(zhǎng)江”；（64）向“信管系0101”班info_student1視圖中插入一個(gè)新學(xué)生記錄，其中：學(xué)號(hào)：011111136，姓名：張藝謀，性別：男，出生日期：1987.11.9；（65）通過視圖info_student1刪除信管系0101班學(xué)號(hào)為“011111135”、姓名為“黃健中”的學(xué)生記錄；四、實(shí)驗(yàn)程序代碼及結(jié)果：（1）建表并輸入數(shù)據(jù)cre

15、ate table student ( Sno Char(9),snameChar(10) constraint c1 not null,SbirthdayDatetime,SclassChar(20),constraint pk_student primary key(sno) );create table course ( Cno Char(6),CnameChar(20),CpnoChar(6),CreditNumeric(2),CtimeNumeric(2), constraint pk_course primary key(cno) );create table score ( Sn

16、o Char(20), CnoChar(6),Score char(20),constraint pk_score primary key(Sno,Cno) );create table teacher ( Tno Char(20), TnameChar(20), TsexChar(2) constraint c2 check(Tsex in (男,女), Department Char(20), constraint pk_teacher primary key(Tno) );create table Teaching ( Tno Char(20), CnoChar(6),tdate cha

17、r(20),classroom char(20), Sclass char(20), constraint pk_Teaching primary key(Tno) );-（2） 插入刪除 insert into Score(sno,cno,score) values (01110011,C003,90) insert into Score(sno,cno,score) values(011110110,C005,92) select sno ,cno ,score from Score where sno=01110011 or sno=011110110; delete from Scor

18、e where sno=01110011 or sno=011110110; select sno ,cno ,score from Score where sno=01110011 or sno=011110110; -（3）求每門課的平均成績(jī)，并把結(jié)果存入average表； Create table average (Cno char(4) Primary key, ave smallint);Insert into average(Cno,ave)Select Cno,AVG(Score) from score group by Cno;-（4）將學(xué)生“馬麗鵑”的出生日期改為“.8.20

19、”；update student set Sbirthday=1982.8.20 where sname=馬麗鵑-（5）將所有學(xué)生的zipcode屬性列值填補(bǔ)上； update student set zipcode=230000-（6）將average表中的所有課程的平均成績(jī)置零；Update average set ave=0;- (7)刪除average表中的課程號(hào)為c007的平均成績(jī)記錄； Delete from average where Cno=C007;- (8)刪除所有average表中平均成績(jī)記錄； Delete from average where ave;- (9)建立一

20、個(gè)臨時(shí)學(xué)生信息表（tstudent），刪除該表中的學(xué)號(hào)前六位為的所有學(xué)生記 錄。 Create table tstudent (Sno char(12) Primary key); Delete from tstudent where Sno like 001011%;- (10)查詢?nèi)w學(xué)生的學(xué)號(hào)與姓名； Select Sno,Sname from student;- (11)查詢?nèi)w學(xué)生的學(xué)號(hào)、姓名、所屬班級(jí)； Select Sno,Sname,Sclass from student;-（12）查詢?nèi)w學(xué)生的詳細(xì)記錄； Select * from student;-（13）查詢?nèi)w學(xué)生的姓

21、名及其年齡；Select Sname,2011-year(Sbirthday) from student;-（14）查詢?nèi)w學(xué)生的姓名、出生年份；Select Sname,Sbirthday from student;-（15）查詢所有修過課的學(xué)生的學(xué)號(hào)；Select distinct Sno from score where Cno ;-（16）查詢“信管系”班全體學(xué)生名單；Select Sname from student where Sclass=信管系 -（17）查詢查詢所有年齡在歲以下的學(xué)生姓名及其年齡；Select Sname,2011-year(Sbirthday) from s

22、tudent where 2011-Sbirthday27;-（18）查詢考試成績(jī)有不及格的學(xué)生的學(xué)號(hào)；Select distinct Sno from score where Score60;-(19)查詢年齡在至歲之間的學(xué)生姓名、班級(jí)和年齡； Select Sname,Sclass,2011-year(Sbirthday) from student where 2011-Sbirthday between 15 and 28;-（20）查詢年齡不在至歲之間的學(xué)生姓名、班級(jí)和年齡； Select Sname,Sclass,2011-year(Sbirthday) from student w

23、here 2011-Sbirthday not between 15 and 28;-（21）查詢“信管系”和“電商系”班的學(xué)生的姓名和班級(jí)信息；select * from student where Sclass in(信管系,電商系);-（22）查詢既不是“信管系”也不是“電商系”班的學(xué)生的姓名和班級(jí)信息；select * from student where Sclass not in(信管系,電商系);-（23）查詢學(xué)號(hào)為“”的學(xué)生的詳細(xì)情況；Select Sname,Sbirthday,Sclass,Score from student,score where student.Sno

24、=score.Sno and student.Sno=011113104;-（24）查詢學(xué)號(hào)以“”打頭的學(xué)生信息；Select Sname,Sbirthday,Sclass,Score from student,score where student.Sno=score.Sno and student.Sno like0111%;-（25）查詢所有姓“張”學(xué)生的學(xué)號(hào)、姓名、性別、年齡；Select Sno,Sname,2011-year(Sbirthday) from student where Sname like張%;-（26）查詢名字中第二個(gè)字有“?！弊值膶W(xué)生的學(xué)號(hào)、姓名、性別、年齡；S

25、elect Sno,Sname,2011-year(Sbirthday) from student where Sname like_海%;-（27）查詢所有不姓“劉”學(xué)生的姓名；Select Sname from student where Sname not like劉%;-（28）查詢課程號(hào)以“C”開頭的最后兩個(gè)字母為“”的課程號(hào)和課程名；Select Cno,Cname from course where Cno like C%05;-（29）某些學(xué)生選修某門課程后沒有參加考試，所以有選修課記錄，但沒有考試成績(jī)，試查找缺少考試成績(jī)的學(xué)生和相應(yīng)的課程號(hào)；Select Sno,Cno fr

26、om score where score is null;-(30)查找全部有成績(jī)記錄的學(xué)生學(xué)號(hào)、課程號(hào)； Select Sno,Cno from score where score is not null;-(31)查找“電商系”班年齡在歲以下的學(xué)生學(xué)號(hào)、姓名； Select Sno,Sname from student where Sclass=電商系and 2011-Sbirthday =2);-(40)自然連接student和score表； Select student.Sno,Sname,Sbirthday,Sclass,Cno,score from student,score wh

27、ere student.Sno=score.Sno;-(41)使用自身連接查詢每一門課程的間接先行課（即先行課的先行課） Select first.Cno,second.Cpno from course first,course second where first.Cpno=second.Cno;-(42)使用復(fù)合條件連接查詢選修“c001”號(hào)課程且成績(jī)?cè)诜忠陨系乃型瑢W(xué)； Select student.Sno from student,Score where student.Sno=score.Sno and Cno=c001 and Score90;-(43)使用復(fù)合條件連接查詢每個(gè)學(xué)生

28、選修的課程名及其成績(jī)；select student.sno,sname,cname,score from Course,Score ,student where Co=So and student.sno=Score.sno;-*(44)查詢選修了全部課程的學(xué)生； select student.sno,sname ,score from student,Score where student.sno=Score.sno and student.sno in (select Score.sno from Score group by Score.sno having count(cno)=7);

29、-(45)查詢至少選修全部學(xué)分?jǐn)?shù)為個(gè)學(xué)分的課程的學(xué)生的學(xué)號(hào)、姓名； Select Sno,Sname from student where -(46)查詢所有選修了C001號(hào)課程的學(xué)生學(xué)號(hào)、姓名； Select student.Sno,Sname from student,score where student.Sno=score.Sno and Cno=C001;-(47)查詢選修了課程C001或c007的學(xué)生學(xué)號(hào)、姓名； Select student.Sno,Sname from student,score where student.Sno=score.Sno and Cno in (C

30、001,C007);-(48)查詢“會(huì)計(jì)系”班的學(xué)生及年齡不大于歲（現(xiàn)有年齡）的學(xué)生； Select Sno from student where Sclass=會(huì)計(jì)系 and 2011-Sbirthday=27;-(49)查詢既選修了課程C001又選修了課程c007的所有學(xué)生學(xué)號(hào)、姓名； Select student.Sno,Sname from student,score where student.Sno=score.Sno and Cno=C001 and Cno=C007;-(50)查詢選修了課程名為“數(shù)據(jù)庫原理”的學(xué)生的學(xué)號(hào)、姓名、性別、年齡； Select student.Sno

31、,Sname,2011-Sbirthday from student,score,course where student.Sno=score.Sno and score.Cno=course.Cno and Cname=數(shù)據(jù)庫原理;-(51)查詢其他班中比“信管系”班所有學(xué)生年齡都小的學(xué)生名單； Select Sname from student where 2011-Sbirthdayall(select 2011-year(Sbirthday) from student where Sclass=信管系)and Sclass信管系;-(52)查詢與“齊振國”在同一個(gè)班學(xué)習(xí)的學(xué)生學(xué)號(hào)、姓名

32、、性別、年齡； Select Sno,Sname,2011-year(Sbirthday) from student where Sclass =(select Sclass from student where Sname=齊振國);-（53）建立“信管系”班學(xué)生的視圖，定義視圖名為“info_student1”； create view info_student1 as select Sno,Sname,2011-year(Sbirthday) bir from student where Sclass=信管系;-（54）建立“信管系”班學(xué)生的視圖，定義視圖名為“info_student2

33、”，并要求進(jìn)行修改與插入時(shí)，仍須保證該視圖只有“信管系”班學(xué)生； create view info_student2 as select Sno,Sname,2011-year(Sbirthday) bir from student where Sclass=信管系 with check option;-（55）建立“信管系”班選修了“C001”課程的學(xué)生的視圖，定義視圖名為“info_C001_student1”； create view info_C001_student1 as select student.Sno,Sname,2011-year(Sbirthday) bir,Score

34、 from student,score where Sclass=信管系and student.Sno=score.Sno and score.Cno=C001;-（56）建立“信管系”班選修了“C001”課程且成績(jī)?cè)诜忠陨系膶W(xué)生的視圖，定義視圖名為“info_c001_student2”； create view info_c001_student2 as select Sno,Sname, bir,Score from info_C001_student1 where score=90;-（57）定義一個(gè)反映學(xué)生年齡的視圖，定義視圖名為“vbirthday_student”； create view vbirthday_student as selec