《數(shù)據(jù)庫實(shí)驗報告》word版.doc

《《數(shù)據(jù)庫實(shí)驗報告》word版.doc》由會員分享，可在線閱讀，更多相關(guān)《《數(shù)據(jù)庫實(shí)驗報告》word版.doc（36頁珍藏版）》請在裝配圖網(wǎng)上搜索。

數(shù)據(jù)庫原理 實(shí)驗報告 學(xué) 號： 4110115 姓 名： 王善斌 提交日期： 2013-06-20 成 績： 東北大學(xué)秦皇島分校 【實(shí)驗內(nèi)容】 2在企業(yè)管理器中創(chuàng)建一個數(shù)據(jù)庫，要求如下： （1） 數(shù)據(jù)庫名稱 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)建一個數(shù)據(jù)庫，要求如下： （1） 數(shù)據(jù)庫名稱 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ù)庫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ù)庫更名：把test1數(shù)據(jù)庫更名為new_test1 alter database test1 modify name=new_test1 5.在企業(yè)管理器中刪除new_test1數(shù)據(jù)庫，在查詢分析器中刪除test2數(shù)據(jù)庫。 在企業(yè)管理器中刪除new_test1前 在企業(yè)管理器中刪除new_test1后 在查詢分析器中刪除test3前 在查詢分析器中刪除test3后 drop database test3 6.為sql示例數(shù)據(jù)庫northwind創(chuàng)建一個備份：northwindBK，并使用該備份文件恢復(fù)northwind數(shù)據(jù)庫。 exec sp_addumpdevice disk,northwindBK,d:\數(shù)據(jù)庫備份\MyNwind-1.dat BACKUP DATABASE northwind TO northwindBK 實(shí)驗二 創(chuàng)建并管理表 1. 創(chuàng)建數(shù)據(jù)庫，包含如下表，創(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)) 以下為各個表的數(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增加一個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ù)驗證約束 use yzw go alter table score add constraint grade check (grade >0 and grade <100) //建立了一個約束條件 可以知道約束條件起作用了。 三 數(shù)據(jù)更新 實(shí)驗內(nèi)容：在已經(jīng)建立的studentinfo數(shù)據(jù)庫和3個students、courses、score基礎(chǔ)上完成下列操作。 1. 向students表添加一個學(xué)生記錄，學(xué)號為20010112，性別為男，姓名為stefen，年齡25歲，所在系為藝術(shù)系art。 use yzw go insert student values (20010112,stefen,M,25,Art) 2.向score表添加一個選課記錄，學(xué)生學(xué)好為20010112，所選課程號為C2 use yzw go insert score values (20010112,C2,null) 3.建立臨時表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é)生的成績加5分 update score set grade=grade+5 5.將姓名為sue的學(xué)生所在系改為電子信息系 update student set department=電子信息系 where student_name=sue 6.將選課的database的學(xué)生成績加10分 update score set score.grade=score.grade+10 where score.course_id=C3 7.刪除所有成績?yōu)榭盏倪x修記錄 Delete score Where grade=’null’ 8.刪除學(xué)生姓名為deepa的學(xué)生記錄 由于下表有調(diào)用因而刪除不掉 四 數(shù)據(jù)查詢 1、 查詢?nèi)w學(xué)生的學(xué)號、姓名、所在系，并為結(jié)果集的各列設(shè)置中文名稱。 select student_id學(xué)號,student_name姓名,sex性別,age年齡,department學(xué)院 from student 2.查詢?nèi)w學(xué)生的選課情況，并為所有成績加5分 select * from score update score set grade=grade+5 3.顯示所有選課學(xué)生的學(xué)號，去掉重復(fù)行 select distinct student.student_id from student 4.查詢選課成績大于80分的學(xué)生 select score.student_id from score where grade>80 5. 查詢年齡在20到30之間的學(xué)生學(xué)號，姓名，所在系 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é)號，姓名。 select student.student_id,student.student_name from student where department in (math,電子信息系,Art) 7. 查詢姓名第二個字符為u并且只有3個字符的學(xué)生學(xué)號，姓名 select student.student_id,student.student_name from student where student_name like _u_ 8. 查詢所有以S開頭的學(xué)生。 select student.student_id,student.student_name from student where student_name like S% 9. 查詢姓名不以S、D、或J開頭的學(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查詢沒有考試成績的學(xué)生和相應(yīng)課程號（成績值為空） 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語言課程的學(xué)生平均成績、最高分、最低分學(xué)生。 select AVG (grade)平均成績,max(grade)最高分,min(grade)最低分 from score where course_id=c1 13.求學(xué)號為20010101的學(xué)生總成績 select sum (grade)總成績 from score where student_id=20010101 14求每個選課學(xué)生的學(xué)號，姓名，總成績 select student.student_id,student_name,score.grade from student,score where student.student_id=score.student_id 15.查詢所有姓李且全名為3個漢字的學(xué)生姓名，學(xué)號，性別 select student_id,student_name,sex from student where student_name=李__ 16.求課程號及相應(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é)號 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.查詢每個學(xué)生基本信息及選課情況 select * from student,course 2.查詢每個學(xué)生學(xué)號姓名及選修的課程名、成績 select student.student_id,student.student_name,course_id,grade from student,score where student.student_id=score.student_id 3.求電子信息系選修課程超過2門課的學(xué)生學(xué)號姓名、平均成績并按平均成績降序排列 4.查詢與sue在同一個系學(xué)習(xí)的所有學(xué)生的學(xué)號姓名 select student.student_id,student.student_name from student where department=(select department from student where student_name=sue) 5.查詢所有學(xué)生的選課情況，要求包括所有選修了課程的學(xué)生和沒有選課的學(xué)生，顯示他們的姓名學(xué)號課程號和成績（如果有） 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ù)庫中products表建立一個聚集索引，索引字段為產(chǎn)品類型和產(chǎn)品編號。 2在已經(jīng)建立的studentInfo數(shù)據(jù)庫的3個表基礎(chǔ)上，完成下列操作： （1） 建立數(shù)學(xué)系的學(xué)生視圖； create view mathsthdentview as select * from student where student.department =math （2） 建立計算機(jī)系選修了課程名為database的學(xué)生的視圖，視圖名為compStudentview，該視圖的列名為學(xué)號、姓名、成績 create view compstudentview as select student.student_id 學(xué)號,student_name 姓名,grade 成績 from student,score where student.department =computer and student.student_id=score.student_id and score.course_id=C3 （3） 創(chuàng)建一個名為studentSumview的視圖，包含所有學(xué)生學(xué)號和總成績 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） 建立一個計算機(jī)系學(xué)生選修了課程名為database并且成績大于80分的學(xué)生視圖，視圖名為CompsutdentView1，視圖的列為學(xué)號姓名成績。 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語句刪除compsutdentview1視圖。 drop view Compstudentview1 數(shù)據(jù)庫查詢綜合實(shí)驗 1.使用查詢分析器建立上述數(shù)據(jù)庫和表； 圖書表 讀者表 借閱表 2、基于以上數(shù)據(jù)庫使用sql語句完成下列對表操作： （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）為讀者表中“辦公電話”一列增加一個CHECK約束，要求電話號碼的前五位是“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個表，使用sql語句完成以下數(shù)據(jù)更新操作 1）向讀者表加入一個新讀者，該讀者的信息為，讀者號：001980，姓名：余暇，性別：女，辦公電話：88320564，部門：藝術(shù)系 insert into 讀者表 values (001980,余暇,女,88320564,藝術(shù)系) 2）向借閱表插入一個借閱記錄，表示讀者“王平”借閱了一本書，圖書號：TP316/ZW6，借出日期為當(dāng)天日期，歸還日期為空值； insert into 借閱表 values (001973,TP316/ZW6,2013-6-17,null) （3）讀者“王平”應(yīng)在借出日期的10天之后歸還該書； update 借閱表 set 歸還日期=2013-6-27 where 讀者號=001973 and 圖書號=TP316/ZW6 （4）當(dāng)讀者“王平”按期歸還書籍后，從借閱表中刪除上述借閱記錄 delete from 借閱表 where 讀者號=001973 and 圖書號=TP316/ZW6 4、針對以上3個表，完成下列單表查詢 （1）查詢?nèi)w圖書的信息； select * from 圖書表 （2）查詢?nèi)w圖書信息，其中單價進(jìn)行打8折操作，顯示列名為“折扣價”； select 圖書號,圖書名,作者,出版社,0.8*單價 折扣價 from 圖書表 （3）顯示目前所有借閱讀者信息，去掉重復(fù)行； select distinct * from 借閱表 （4）顯示所有單價在20-30元之間的圖書信息； select * from 圖書表 where 單價 between 20 and 30 （5）查詢所有單價不在20-30元之間的圖書信息； select * from 圖書表 where 單價 not between 20 and 30 （6）查詢機(jī)械工業(yè)出版社，科學(xué)出版社、人民郵電出版社的圖書信息； select * from 圖書表 where 出版社 in (機(jī)械工業(yè)出版社,科學(xué)出版社,人民郵電出版社的圖書信息) （7）查詢非人民郵電出版社的信息； select * from 圖書表 where 出版社 not in (人民郵電出版社的圖書信息) （8）查找姓名第二個字符是“建”并且只有2個字符姓名的讀者信息； select * from 讀者表 where 姓名 like _建 （9）查找姓名以“王”開頭的所有讀者的讀者號及姓名； select 讀者號,姓名 from 讀者表 where 姓名 like 王% （10）查找以“王”、“張”、“李”開頭的所有讀者的讀者號姓名； select 讀者號,姓名 from 讀者表 where 姓名 like 王% or 姓名 Like張% or 姓名 LIke 李% （11）查找以不是“張”、“李”開頭的所有讀者的讀者號姓名； select 讀者號,姓名 from 讀者表 where 姓名 not Like張% and 姓名 not LIke 李% （12）查詢無歸還日期的借閱記錄 select * from 借閱表 where 歸還日期 is null （13）查詢單價在20元以上、30元以下的機(jī)械工業(yè)出版社的圖書及單價； select 圖書名,單價 from 圖書表 where 單價 between 20 and 30 and 出版社=機(jī)械工業(yè)出版社 14）查詢讀者總?cè)藬?shù)； select count (*) from 讀者表 （15）查詢借閱了圖書的讀者總?cè)藬?shù)； select distinct count (*) from 借閱表 16）查詢機(jī)械工業(yè)出版社圖書的平均價格、最高價、最低價； select AVG(單價),max(單價),min (單價) from 圖書表 where 出版社=機(jī)械工業(yè)出版社 （17）查詢借閱圖書超過2本的讀者號、總本數(shù)，并按照借閱本數(shù)從大到小排列； select 讀者號,count(*)總本數(shù) from 借閱表 group by 讀者號 having count(*)>=2 order by count(*) desc 5、針對以上3個表，完成下列各項多表連接查詢 (1)查詢讀者的基本信息和借閱情況 select 讀者表.*,借閱表.* from 讀者表,借閱表 where 讀者表.讀者號=借閱表.讀者號 （2）查詢讀者的讀者號，姓名，借閱的圖書名、借出日期及歸還日期 select 讀者表.讀者號,姓名,借閱表.圖書號,借出日期,歸還日期 from 讀者表,借閱表 where 讀者表.讀者號=借閱表.讀者號 （3）查詢借閱了機(jī)械工業(yè)出版社出版的，并且書名中包括“數(shù)據(jù)庫”3個字的圖書的讀者的讀者號，姓名，借閱的圖書名、出版社、借出日期及歸還日期。 select 讀者表.讀者號,姓名,借閱表.圖書號,出版社,借出日期,歸還日期 from 讀者表,借閱表,圖書表 where 讀者表.讀者號=借閱表.讀者號 and 出版社=機(jī)械工業(yè)出版社 and 圖書名 like%數(shù)據(jù)庫%and 圖書表.圖書號=借閱表.圖書號 （4）查詢至少借閱過1本機(jī)械工業(yè)出版社出版的圖書的讀者的讀者號，姓名，借閱的圖書名、借閱的本數(shù)，并按借閱本數(shù)從多到少排序。 select 讀者表.讀者號,姓名,借閱表.圖書號,count(*)借閱的本數(shù) from 讀者表,借閱表 where 讀者表.讀者號=借閱表.讀者號 and 借閱表.讀者號 exists ( select 借閱表.讀者號,count(*) from 圖書表,借閱表 where 圖書表.圖書號=借閱表.圖書號 and 出版社=機(jī)械工業(yè)出版社 group by 讀者號 having count(*)>=1 ) order by count (*) desc （5）查詢與讀者“王平”辦公電話相同的讀者姓名及借閱情況 select 讀者表.姓名,借閱表.* from 讀者表,借閱表 where 讀者表.讀者號=借閱表.讀者號 and 辦公電話 =( select 辦公電話 from 讀者表 where 姓名=王平 ) （6）查詢辦公電話為“88320701”的所有讀者的借閱情況，要求包括借閱了圖書的作者和沒有借閱的讀者，顯示他們的讀者號、姓名、書名及借閱日期。 select 讀者表.讀者號,讀者表.姓名,圖書名,借出日期 from 讀者表,借閱表,圖書表 where 讀者表LEFT OUT JOIN 借閱表ON讀者表.讀者號=借閱表.讀者號 and 圖書表.圖書號=借閱表.圖書號 and 辦公電話=88320701 6、基于數(shù)據(jù)庫的3張表建立下列視圖 1）創(chuàng)建機(jī)械工業(yè)出版社圖書的視圖； create view 機(jī)械工業(yè)出版社圖書視圖 as select * from 圖書表 where 出版社=機(jī)械工業(yè)出版社 2）創(chuàng)建一個統(tǒng)計視圖，名為CountView，包含讀者的讀者號和總借閱本數(shù)； create view countview as select 讀者號,count(*)總借閱本數(shù) from 借閱表 group by 讀者號 3）創(chuàng)建一個借閱統(tǒng)計視圖，名為CountView10，包含借閱本數(shù)大于2的讀者號和總借閱本數(shù)。 create view countview10 as select 讀者號,count(*)總借閱本數(shù) from 借閱表 group by 讀者號 having count(*)>=2