《數(shù)據(jù)庫(kù)實(shí)驗(yàn)報(bào)告》word版.doc

《《數(shù)據(jù)庫(kù)實(shí)驗(yàn)報(bào)告》word版.doc》由會(huì)員分享，可在線閱讀，更多相關(guān)《《數(shù)據(jù)庫(kù)實(shí)驗(yàn)報(bào)告》word版.doc（36頁(yè)珍藏版）》請(qǐng)?jiān)谘b配圖網(wǎng)上搜索。

數(shù)據(jù)庫(kù)原理 實(shí)驗(yàn)報(bào)告 學(xué) 號(hào)： 4110115 姓 名： 王善斌 提交日期： 2013-06-20 成 績(jī)： 東北大學(xué)秦皇島分校 【實(shí)驗(yàn)內(nèi)容】 2在企業(yè)管理器中創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)，要求如下： （1） 數(shù)據(jù)庫(kù)名稱 Test1。 （2） 主要數(shù)據(jù)文件：邏輯文件名為Test1_data1，物理文件名為Test1_data1.mdf，初始容量為1MB，最大容量為10MB，增幅為1MB。 （3） 次要數(shù)據(jù)文件：邏輯文件名為Test1_data2，物理文件名為Test1_data2.ndf，初始容量為1MB，最大容量為10MB，增幅為1MB。 （4） 事務(wù)日志文件：邏輯文件名為Test1_log1，物理文件名為Test1_log1.ldf，初始容量為1MB，最大容量為5MB，增幅為512KB。 3在查詢分析器中創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)，要求如下： （1） 數(shù)據(jù)庫(kù)名稱 Test2。 （2） 主要數(shù)據(jù)文件：邏輯文件名為Test2_data1，物理文件名為Test2_data1.mdf，初始容量為1MB，最大容量為10MB，增幅為1MB。 （3） 次要數(shù)據(jù)文件：邏輯文件名為Test2_data2，物理文件名為Test2_data2.ndf，初始容量為1MB，最大容量為10MB，增幅為1MB。 （4） 事務(wù)日志文件：邏輯文件名為Test2_log1，物理文件名為Test2_log1.ldf，初始容量為1MB，最大容量為5MB，增幅為1MB。 create database test2 on primary (name =test2_data1, filename =d:\sqlex\test2_data1.mdf, size=1, maxsize=10, filegrowth=1), (name=test2_data2, filename=d:\sqlex\test2_data2.ndf, size =1, maxsize=10, filegrowth=1) log on(name=test2_log, filename=d:\sqlex\stu_log1.ndf, size=1, maxsize=5, filegrowth=2) 4.在查詢分析器中按照下列要求修改第3題中創(chuàng)建的數(shù)據(jù)庫(kù)test2 （1） 主要數(shù)據(jù)文件的容量為2MB，最大容量為20MB，增幅為2MB。 （2） 次要數(shù)據(jù)文件的容量為2MB，最大容量為20MB，增幅為2MB。 事務(wù)日志文件的容量為1MB，最大容量為10MB，增幅為2MB alter database test3 modify name=test2 alter database test2 modify file (name=test2_data2, filename=d:\sqlex\test2_data2.ndf, size =2, maxsize =20, filegrowth=2) alter database test2 modify file (name=test2_log, filename=d:\sqlex\test2_log.ldf, size =1, maxsize =10, filegrowth=1) alter database test2 modify file (name=test2_data1, size=2, maxsize=20, filegrowth =2) 4.數(shù)據(jù)庫(kù)更名：把test1數(shù)據(jù)庫(kù)更名為new_test1 alter database test1 modify name=new_test1 5.在企業(yè)管理器中刪除new_test1數(shù)據(jù)庫(kù)，在查詢分析器中刪除test2數(shù)據(jù)庫(kù)。 在企業(yè)管理器中刪除new_test1前 在企業(yè)管理器中刪除new_test1后 在查詢分析器中刪除test3前 在查詢分析器中刪除test3后 drop database test3 6.為sql示例數(shù)據(jù)庫(kù)northwind創(chuàng)建一個(gè)備份：northwindBK，并使用該備份文件恢復(fù)northwind數(shù)據(jù)庫(kù)。 exec sp_addumpdevice disk,northwindBK,d:\數(shù)據(jù)庫(kù)備份\MyNwind-1.dat BACKUP DATABASE northwind TO northwindBK 實(shí)驗(yàn)二 創(chuàng)建并管理表 1. 創(chuàng)建數(shù)據(jù)庫(kù)，包含如下表，創(chuàng)建這些表并按要求定義約束 use studentinfo go create table student ( student_id char (10) primary key, student_name char (10) not null, sex char (1) not null, age int null, department char (15) default 電子信息系) use studentinfo go create table course ( course_id char (6) primary key, course_name char (20) not null, precould char (6) null, credits numeric (3,1) not null) use studentinfo go create table score ( student_id char (10), course_id char (6) not null, grade numeric (3,1) null, primary key (student_id,course_id), foreign key (student_id) references student (student_id), foreign key (course_id) references course (course_id)) 以下為各個(gè)表的數(shù)據(jù) Students表數(shù)據(jù) Student_id Student_name sex age department 20010101 Jone M 19 Computer 20010102 Sue F 20 Computer 20010103 Smith M 19 Math 20030101 Allen M 18 Automation 20030102 deepa F 21 Art Course表數(shù)據(jù) Course_id Course_name PreCouId Credits C1 English 4 C2 Math C5 2 C3 database C2 2 Score表數(shù)據(jù) Student_id Course_id Grade 20010101 C1 90 20010103 C1 88 20010102 C2 94 20010102 C2 62 2.增加，修改，刪除字段，要求 （1）為表student增加一個(gè)memo（備注）字段，類型為varchar(200) 代碼： use yzw go alter table student add memo varchar (200) (2).將MEMO字段的數(shù)據(jù)類型更改為varchar(300) 代碼：use yzw go alter table student alter column memo varchar (300) (3)．刪除memo字段 use yzw go alter table student drop column memo 3.向表中插入數(shù)據(jù)驗(yàn)證約束 use yzw go alter table score add constraint grade check (grade >0 and grade <100) //建立了一個(gè)約束條件 可以知道約束條件起作用了。 三 數(shù)據(jù)更新 實(shí)驗(yàn)內(nèi)容：在已經(jīng)建立的studentinfo數(shù)據(jù)庫(kù)和3個(gè)students、courses、score基礎(chǔ)上完成下列操作。 1. 向students表添加一個(gè)學(xué)生記錄，學(xué)號(hào)為20010112，性別為男，姓名為stefen，年齡25歲，所在系為藝術(shù)系art。 use yzw go insert student values (20010112,stefen,M,25,Art) 2.向score表添加一個(gè)選課記錄，學(xué)生學(xué)好為20010112，所選課程號(hào)為C2 use yzw go insert score values (20010112,C2,null) 3.建立臨時(shí)表tempstudent,結(jié)構(gòu)與students結(jié)構(gòu)相同，其記錄均從student表獲取 use yzw go create table tempstudent ( student_id char (10) primary key, student_name char (10) not null, sex char (1) not null, age int null, department char (15) ) insert into tempstudent select student_id,student_name,sex,age,department from student 4，將所有學(xué)生的成績(jī)加5分 update score set grade=grade+5 5.將姓名為sue的學(xué)生所在系改為電子信息系 update student set department=電子信息系 where student_name=sue 6.將選課的database的學(xué)生成績(jī)加10分 update score set score.grade=score.grade+10 where score.course_id=C3 7.刪除所有成績(jī)?yōu)榭盏倪x修記錄 Delete score Where grade=’null’ 8.刪除學(xué)生姓名為deepa的學(xué)生記錄 由于下表有調(diào)用因而刪除不掉 四 數(shù)據(jù)查詢 1、 查詢?nèi)w學(xué)生的學(xué)號(hào)、姓名、所在系，并為結(jié)果集的各列設(shè)置中文名稱。 select student_id學(xué)號(hào),student_name姓名,sex性別,age年齡,department學(xué)院 from student 2.查詢?nèi)w學(xué)生的選課情況，并為所有成績(jī)加5分 select * from score update score set grade=grade+5 3.顯示所有選課學(xué)生的學(xué)號(hào)，去掉重復(fù)行 select distinct student.student_id from student 4.查詢選課成績(jī)大于80分的學(xué)生 select score.student_id from score where grade>80 5. 查詢年齡在20到30之間的學(xué)生學(xué)號(hào)，姓名，所在系 select student.student_id,student.student_name,student.department,student.age from student where age between 20 and 30 6.查詢數(shù)學(xué)系、電子信息系、藝術(shù)系的學(xué)生學(xué)號(hào)，姓名。 select student.student_id,student.student_name from student where department in (math,電子信息系,Art) 7. 查詢姓名第二個(gè)字符為u并且只有3個(gè)字符的學(xué)生學(xué)號(hào)，姓名 select student.student_id,student.student_name from student where student_name like _u_ 8. 查詢所有以S開(kāi)頭的學(xué)生。 select student.student_id,student.student_name from student where student_name like S% 9. 查詢姓名不以S、D、或J開(kāi)頭的學(xué)生 select student.student_id,student.student_name from student where student_name not like S% and student_name not like D%and student_name not like J% 10查詢沒(méi)有考試成績(jī)的學(xué)生和相應(yīng)課程號(hào)（成績(jī)值為空） select student_id,course_id from score where grade is null 11. 求年齡大于19歲的學(xué)生的總?cè)藬?shù) select count (*) 總數(shù) from student where age >19 12 別求選修了c1語(yǔ)言課程的學(xué)生平均成績(jī)、最高分、最低分學(xué)生。 select AVG (grade)平均成績(jī),max(grade)最高分,min(grade)最低分 from score where course_id=c1 13.求學(xué)號(hào)為20010101的學(xué)生總成績(jī) select sum (grade)總成績(jī) from score where student_id=20010101 14求每個(gè)選課學(xué)生的學(xué)號(hào)，姓名，總成績(jī) select student.student_id,student_name,score.grade from student,score where student.student_id=score.student_id 15.查詢所有姓李且全名為3個(gè)漢字的學(xué)生姓名，學(xué)號(hào)，性別 select student_id,student_name,sex from student where student_name=李__ 16.求課程號(hào)及相應(yīng)課程的所有的選課人數(shù) select course_id,count(*)Sum from score,student where student.student_id=score.student_id group by course_id 17查詢選修了3門以上課程的學(xué)生姓名學(xué)號(hào) select student_id,student_name from student where EXISTS ( select * from score where score.student_id=student.student_id group by student_id having count(*)>=3 ) 18.查詢每個(gè)學(xué)生基本信息及選課情況 select * from student,course 2.查詢每個(gè)學(xué)生學(xué)號(hào)姓名及選修的課程名、成績(jī) select student.student_id,student.student_name,course_id,grade from student,score where student.student_id=score.student_id 3.求電子信息系選修課程超過(guò)2門課的學(xué)生學(xué)號(hào)姓名、平均成績(jī)并按平均成績(jī)降序排列 4.查詢與sue在同一個(gè)系學(xué)習(xí)的所有學(xué)生的學(xué)號(hào)姓名 select student.student_id,student.student_name from student where department=(select department from student where student_name=sue) 5.查詢所有學(xué)生的選課情況，要求包括所有選修了課程的學(xué)生和沒(méi)有選課的學(xué)生，顯示他們的姓名學(xué)號(hào)課程號(hào)和成績(jī)（如果有） select student.student_id,student.student_name,score.course_id,score.grade from student,score where student.student_id=score.student_id 五 索引和視圖 1、 分別使用企業(yè)管理器和查詢分析器為northwind數(shù)據(jù)庫(kù)中products表建立一個(gè)聚集索引，索引字段為產(chǎn)品類型和產(chǎn)品編號(hào)。 2在已經(jīng)建立的studentInfo數(shù)據(jù)庫(kù)的3個(gè)表基礎(chǔ)上，完成下列操作： （1） 建立數(shù)學(xué)系的學(xué)生視圖； create view mathsthdentview as select * from student where student.department =math （2） 建立計(jì)算機(jī)系選修了課程名為database的學(xué)生的視圖，視圖名為compStudentview，該視圖的列名為學(xué)號(hào)、姓名、成績(jī) create view compstudentview as select student.student_id 學(xué)號(hào),student_name 姓名,grade 成績(jī) from student,score where student.department =computer and student.student_id=score.student_id and score.course_id=C3 （3） 創(chuàng)建一個(gè)名為studentSumview的視圖，包含所有學(xué)生學(xué)號(hào)和總成績(jī) create view studentSumview as select student.student_id,sum(grade)sum from student,score where student.student_id=score.student_id group by student.student_id （4） 建立一個(gè)計(jì)算機(jī)系學(xué)生選修了課程名為database并且成績(jī)大于80分的學(xué)生視圖，視圖名為CompsutdentView1，視圖的列為學(xué)號(hào)姓名成績(jī)。 create view Compstudentview1 as select student.student_id,student_name,grade from student,score where student.department=computerand score.course_id=C3 and grade>80 and student.student_id=score.student_id （5） 使用sql語(yǔ)句刪除compsutdentview1視圖。 drop view Compstudentview1 數(shù)據(jù)庫(kù)查詢綜合實(shí)驗(yàn) 1.使用查詢分析器建立上述數(shù)據(jù)庫(kù)和表； 圖書表 讀者表 借閱表 2、基于以上數(shù)據(jù)庫(kù)使用sql語(yǔ)句完成下列對(duì)表操作： （1）給圖書表增加一列“ISBN”，數(shù)據(jù)類型為CHAR（10）； alter table 圖書表 add ISBN char (10) （2）為剛添加的ISBN列增加默認(rèn)值約束，約束名為ISBNDEF，默認(rèn)值為‘7111085949’； alter table 圖書表 add constraint ISBNDEF default 7111085949 for ISBN （3）為讀者表中“辦公電話”一列增加一個(gè)CHECK約束，要求電話號(hào)碼的前五位是“88320” alter table 讀者表 add constraint c1 check (辦公電話 LIKE 88320___ ) （4）刪除圖書表中ISBN列增加的默認(rèn)值約束 alter table 圖書表 drop constraint ISBNDEF （5）刪除讀者表中“辦公電話”列的CHECK約束 alter table 讀者表 drop constraint c1 6）刪除圖書表中的新增ISBN列。 alter table 圖書表 drop column ISBN 3、基于以上3個(gè)表，使用sql語(yǔ)句完成以下數(shù)據(jù)更新操作 1）向讀者表加入一個(gè)新讀者，該讀者的信息為，讀者號(hào)：001980，姓名：余暇，性別：女，辦公電話：88320564，部門：藝術(shù)系 insert into 讀者表 values (001980,余暇,女,88320564,藝術(shù)系) 2）向借閱表插入一個(gè)借閱記錄，表示讀者“王平”借閱了一本書，圖書號(hào)：TP316/ZW6，借出日期為當(dāng)天日期，歸還日期為空值； insert into 借閱表 values (001973,TP316/ZW6,2013-6-17,null) （3）讀者“王平”應(yīng)在借出日期的10天之后歸還該書； update 借閱表 set 歸還日期=2013-6-27 where 讀者號(hào)=001973 and 圖書號(hào)=TP316/ZW6 （4）當(dāng)讀者“王平”按期歸還書籍后，從借閱表中刪除上述借閱記錄 delete from 借閱表 where 讀者號(hào)=001973 and 圖書號(hào)=TP316/ZW6 4、針對(duì)以上3個(gè)表，完成下列單表查詢 （1）查詢?nèi)w圖書的信息； select * from 圖書表 （2）查詢?nèi)w圖書信息，其中單價(jià)進(jìn)行打8折操作，顯示列名為“折扣價(jià)”； select 圖書號(hào),圖書名,作者,出版社,0.8*單價(jià) 折扣價(jià) from 圖書表 （3）顯示目前所有借閱讀者信息，去掉重復(fù)行； select distinct * from 借閱表 （4）顯示所有單價(jià)在20-30元之間的圖書信息； select * from 圖書表 where 單價(jià) between 20 and 30 （5）查詢所有單價(jià)不在20-30元之間的圖書信息； select * from 圖書表 where 單價(jià) not between 20 and 30 （6）查詢機(jī)械工業(yè)出版社，科學(xué)出版社、人民郵電出版社的圖書信息； select * from 圖書表 where 出版社 in (機(jī)械工業(yè)出版社,科學(xué)出版社,人民郵電出版社的圖書信息) （7）查詢非人民郵電出版社的信息； select * from 圖書表 where 出版社 not in (人民郵電出版社的圖書信息) （8）查找姓名第二個(gè)字符是“建”并且只有2個(gè)字符姓名的讀者信息； select * from 讀者表 where 姓名 like _建 （9）查找姓名以“王”開(kāi)頭的所有讀者的讀者號(hào)及姓名； select 讀者號(hào),姓名 from 讀者表 where 姓名 like 王% （10）查找以“王”、“張”、“李”開(kāi)頭的所有讀者的讀者號(hào)姓名； select 讀者號(hào),姓名 from 讀者表 where 姓名 like 王% or 姓名 Like張% or 姓名 LIke 李% （11）查找以不是“張”、“李”開(kāi)頭的所有讀者的讀者號(hào)姓名； select 讀者號(hào),姓名 from 讀者表 where 姓名 not Like張% and 姓名 not LIke 李% （12）查詢無(wú)歸還日期的借閱記錄 select * from 借閱表 where 歸還日期 is null （13）查詢單價(jià)在20元以上、30元以下的機(jī)械工業(yè)出版社的圖書及單價(jià)； select 圖書名,單價(jià) from 圖書表 where 單價(jià) between 20 and 30 and 出版社=機(jī)械工業(yè)出版社 14）查詢讀者總?cè)藬?shù)； select count (*) from 讀者表 （15）查詢借閱了圖書的讀者總?cè)藬?shù)； select distinct count (*) from 借閱表 16）查詢機(jī)械工業(yè)出版社圖書的平均價(jià)格、最高價(jià)、最低價(jià)； select AVG(單價(jià)),max(單價(jià)),min (單價(jià)) from 圖書表 where 出版社=機(jī)械工業(yè)出版社 （17）查詢借閱圖書超過(guò)2本的讀者號(hào)、總本數(shù)，并按照借閱本數(shù)從大到小排列； select 讀者號(hào),count(*)總本數(shù) from 借閱表 group by 讀者號(hào) having count(*)>=2 order by count(*) desc 5、針對(duì)以上3個(gè)表，完成下列各項(xiàng)多表連接查詢 (1)查詢讀者的基本信息和借閱情況 select 讀者表.*,借閱表.* from 讀者表,借閱表 where 讀者表.讀者號(hào)=借閱表.讀者號(hào) （2）查詢讀者的讀者號(hào)，姓名，借閱的圖書名、借出日期及歸還日期 select 讀者表.讀者號(hào),姓名,借閱表.圖書號(hào),借出日期,歸還日期 from 讀者表,借閱表 where 讀者表.讀者號(hào)=借閱表.讀者號(hào) （3）查詢借閱了機(jī)械工業(yè)出版社出版的，并且書名中包括“數(shù)據(jù)庫(kù)”3個(gè)字的圖書的讀者的讀者號(hào)，姓名，借閱的圖書名、出版社、借出日期及歸還日期。 select 讀者表.讀者號(hào),姓名,借閱表.圖書號(hào),出版社,借出日期,歸還日期 from 讀者表,借閱表,圖書表 where 讀者表.讀者號(hào)=借閱表.讀者號(hào) and 出版社=機(jī)械工業(yè)出版社 and 圖書名 like%數(shù)據(jù)庫(kù)%and 圖書表.圖書號(hào)=借閱表.圖書號(hào) （4）查詢至少借閱過(guò)1本機(jī)械工業(yè)出版社出版的圖書的讀者的讀者號(hào)，姓名，借閱的圖書名、借閱的本數(shù)，并按借閱本數(shù)從多到少排序。 select 讀者表.讀者號(hào),姓名,借閱表.圖書號(hào),count(*)借閱的本數(shù) from 讀者表,借閱表 where 讀者表.讀者號(hào)=借閱表.讀者號(hào) and 借閱表.讀者號(hào) exists ( select 借閱表.讀者號(hào),count(*) from 圖書表,借閱表 where 圖書表.圖書號(hào)=借閱表.圖書號(hào) and 出版社=機(jī)械工業(yè)出版社 group by 讀者號(hào) having count(*)>=1 ) order by count (*) desc （5）查詢與讀者“王平”辦公電話相同的讀者姓名及借閱情況 select 讀者表.姓名,借閱表.* from 讀者表,借閱表 where 讀者表.讀者號(hào)=借閱表.讀者號(hào) and 辦公電話 =( select 辦公電話 from 讀者表 where 姓名=王平 ) （6）查詢辦公電話為“88320701”的所有讀者的借閱情況，要求包括借閱了圖書的作者和沒(méi)有借閱的讀者，顯示他們的讀者號(hào)、姓名、書名及借閱日期。 select 讀者表.讀者號(hào),讀者表.姓名,圖書名,借出日期 from 讀者表,借閱表,圖書表 where 讀者表LEFT OUT JOIN 借閱表ON讀者表.讀者號(hào)=借閱表.讀者號(hào) and 圖書表.圖書號(hào)=借閱表.圖書號(hào) and 辦公電話=88320701 6、基于數(shù)據(jù)庫(kù)的3張表建立下列視圖 1）創(chuàng)建機(jī)械工業(yè)出版社圖書的視圖； create view 機(jī)械工業(yè)出版社圖書視圖 as select * from 圖書表 where 出版社=機(jī)械工業(yè)出版社 2）創(chuàng)建一個(gè)統(tǒng)計(jì)視圖，名為CountView，包含讀者的讀者號(hào)和總借閱本數(shù)； create view countview as select 讀者號(hào),count(*)總借閱本數(shù) from 借閱表 group by 讀者號(hào) 3）創(chuàng)建一個(gè)借閱統(tǒng)計(jì)視圖，名為CountView10，包含借閱本數(shù)大于2的讀者號(hào)和總借閱本數(shù)。 create view countview10 as select 讀者號(hào),count(*)總借閱本數(shù) from 借閱表 group by 讀者號(hào) having count(*)>=2