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

1、數(shù)據(jù)庫(kù)原理實(shí)驗(yàn)報(bào)告學(xué) 號(hào)：4110115姓 名：王善斌提交日期：2013-06-20成 績(jī)：東北大學(xué)秦皇島分校網(wǎng)絡(luò)技術(shù) 實(shí)驗(yàn)報(bào)告【實(shí)驗(yàn)內(nèi)容】2在企業(yè)管理器中創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)，要求如下：（1） 數(shù)據(jù)庫(kù)名稱(chēng) 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

2、_log1.ldf，初始容量為1mb，最大容量為5mb，增幅為512kb。3在查詢(xún)分析器中創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)，要求如下：（1） 數(shù)據(jù)庫(kù)名稱(chēng) 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，增幅為1m

3、b。create database test2on primary (name =test2_data1,filename =d:sqlextest2_data1.mdf,size=1,maxsize=10,filegrowth=1),(name=test2_data2,filename=d:sqlextest2_data2.ndf,size =1,maxsize=10,filegrowth=1)log on(name=test2_log,filename=d:sqlexstu_log1.ndf,size=1,maxsize=5,filegrowth=2)4.在查詢(xún)分析器中按照下列要求修改第3

4、題中創(chuàng)建的數(shù)據(jù)庫(kù)test2（1） 主要數(shù)據(jù)文件的容量為2mb，最大容量為20mb，增幅為2mb。（2） 次要數(shù)據(jù)文件的容量為2mb，最大容量為20mb，增幅為2mb。事務(wù)日志文件的容量為1mb，最大容量為10mb，增幅為2mbalter database test3modify name=test2alter database test2modify file (name=test2_data2,filename=d:sqlextest2_data2.ndf,size =2,maxsize =20,filegrowth=2)alter database test2modify file (na

5、me=test2_log,filename=d:sqlextest2_log.ldf,size =1,maxsize =10,filegrowth=1)alter database test2modify file (name=test2_data1,size=2,maxsize=20,filegrowth =2)4.數(shù)據(jù)庫(kù)更名：把test1數(shù)據(jù)庫(kù)更名為new_test1alter database test1modify name=new_test15.在企業(yè)管理器中刪除new_test1數(shù)據(jù)庫(kù)，在查詢(xún)分析器中刪除test2數(shù)據(jù)庫(kù)。在企業(yè)管理器中刪除new_test1前在企業(yè)管理器中刪除ne

6、w_test1后在查詢(xún)分析器中刪除test3前 在查詢(xún)分析器中刪除test3后drop database test36.為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.datbackup database northwind to northwindbk實(shí)驗(yàn)二 創(chuàng)建并管理表1. 創(chuàng)建數(shù)據(jù)庫(kù)，包含如下表，創(chuàng)建這些表并按要求定義約束use studentinfo gocreate table student (stud

7、ent_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 gocreate 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 studentinf

8、o gocreate 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_idstudent_namesexagedepartme

9、nt20010101jonem19computer20010102suef20computer20010103smithm19math20030101allenm18automation20030102deepaf21artcourse表數(shù)據(jù)course_idcourse_nameprecouidcreditsc1english4c2mathc52c3databasec22score表數(shù)據(jù)student_idcourse_idgrade20010101c1902001010329420010102c2622.增加，修改，刪除字段，要求 （1）為表student增加一個(gè)

10、memo（備注）字段，類(lèi)型為varchar(200)代碼： use yzwgoalter table student add memo varchar (200)(2).將memo字段的數(shù)據(jù)類(lèi)型更改為varchar(300) 代碼：use yzwgoalter table student alter column memo varchar (300)(3)刪除memo字段use yzwgoalter table student drop column memo3.向表中插入數(shù)據(jù)驗(yàn)證約束use yzwgoalter table score add constraint grade check (

11、grade 0 and grade 805. 查詢(xún)年齡在20到30之間的學(xué)生學(xué)號(hào)，姓名，所在系select student.student_id,student.student_name,student.department,student.agefrom studentwhere age between 20 and 306.查詢(xún)數(shù)學(xué)系、電子信息系、藝術(shù)系的學(xué)生學(xué)號(hào)，姓名。select student.student_id,student.student_namefrom studentwhere department in (math,電子信息系,art)7. 查詢(xún)姓名第二個(gè)字符為u并且只

12、有3個(gè)字符的學(xué)生學(xué)號(hào)，姓名select student.student_id,student.student_namefrom studentwhere student_name like _u_8. 查詢(xún)所有以s開(kāi)頭的學(xué)生。select student.student_id,student.student_namefrom studentwhere student_name like s%9. 查詢(xún)姓名不以s、d、或j開(kāi)頭的學(xué)生select student.student_id,student.student_namefrom studentwhere student_name not li

13、ke s% and student_name not like d%and student_name not like j%10查詢(xún)沒(méi)有考試成績(jī)的學(xué)生和相應(yīng)課程號(hào)（成績(jī)值為空）select student_id,course_idfrom scorewhere grade is null11. 求年齡大于19歲的學(xué)生的總?cè)藬?shù)select count (*) 總數(shù)from studentwhere age 1912 別求選修了c1語(yǔ)言課程的學(xué)生平均成績(jī)、最高分、最低分學(xué)生。select avg (grade)平均成績(jī),max(grade)最高分,min(grade)最低分from scorewh

14、ere course_id=c113.求學(xué)號(hào)為20010101的學(xué)生總成績(jī)select sum (grade)總成績(jī)from scorewhere student_id=2001010114求每個(gè)選課學(xué)生的學(xué)號(hào)，姓名，總成績(jī)select student.student_id,student_name,score.gradefrom student,scorewhere student.student_id=score.student_id15.查詢(xún)所有姓李且全名為3個(gè)漢字的學(xué)生姓名，學(xué)號(hào)，性別select student_id,student_name,sexfrom studentwhere

15、 student_name=李_16.求課程號(hào)及相應(yīng)課程的所有的選課人數(shù)select course_id,count(*)sumfrom score,studentwhere student.student_id=score.student_idgroup by course_id17查詢(xún)選修了3門(mén)以上課程的學(xué)生姓名學(xué)號(hào)select student_id,student_namefrom studentwhere exists (select *from scorewhere score.student_id=student.student_id group by student_id hav

16、ing count(*)=3) 18.查詢(xún)每個(gè)學(xué)生基本信息及選課情況select *from student,course2.查詢(xún)每個(gè)學(xué)生學(xué)號(hào)姓名及選修的課程名、成績(jī)select student.student_id,student.student_name,course_id,gradefrom student,scorewhere student.student_id=score.student_id3.求電子信息系選修課程超過(guò)2門(mén)課的學(xué)生學(xué)號(hào)姓名、平均成績(jī)并按平均成績(jī)降序排列4.查詢(xún)與sue在同一個(gè)系學(xué)習(xí)的所有學(xué)生的學(xué)號(hào)姓名select student.student_id,studen

17、t.student_namefrom studentwhere department=(select departmentfrom studentwhere student_name=sue)5.查詢(xún)所有學(xué)生的選課情況，要求包括所有選修了課程的學(xué)生和沒(méi)有選課的學(xué)生，顯示他們的姓名學(xué)號(hào)課程號(hào)和成績(jī)（如果有）select student.student_id,student.student_name,score.course_id,score.gradefrom student,scorewhere student.student_id=score.student_id五 索引和視圖1、 分別使用

18、企業(yè)管理器和查詢(xún)分析器為northwind數(shù)據(jù)庫(kù)中products表建立一個(gè)聚集索引，索引字段為產(chǎn)品類(lèi)型和產(chǎn)品編號(hào)。2在已經(jīng)建立的studentinfo數(shù)據(jù)庫(kù)的3個(gè)表基礎(chǔ)上，完成下列操作：（1） 建立數(shù)學(xué)系的學(xué)生視圖；create view mathsthdentview asselect *from studentwhere student.department =math（2） 建立計(jì)算機(jī)系選修了課程名為database的學(xué)生的視圖，視圖名為compstudentview，該視圖的列名為學(xué)號(hào)、姓名、成績(jī)create view compstudentviewasselect student.

19、student_id 學(xué)號(hào),student_name 姓名,grade 成績(jī)from student,scorewhere 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 asselect student.student_id,sum(grade)sumfrom student,scorewhere student.student

20、_id=score.student_idgroup by student.student_id（4） 建立一個(gè)計(jì)算機(jī)系學(xué)生選修了課程名為database并且成績(jī)大于80分的學(xué)生視圖，視圖名為compsutdentview1，視圖的列為學(xué)號(hào)姓名成績(jī)。 create view compstudentview1asselect student.student_id,student_name,gradefrom student,scorewhere student.department=computerand score.course_id=c3 and grade80 and student.stu

21、dent_id=score.student_id（5） 使用sql語(yǔ)句刪除compsutdentview1視圖。 drop view compstudentview1數(shù)據(jù)庫(kù)查詢(xún)綜合實(shí)驗(yàn)1.使用查詢(xún)分析器建立上述數(shù)據(jù)庫(kù)和表； 圖書(shū)表 讀者表 借閱表2、基于以上數(shù)據(jù)庫(kù)使用sql語(yǔ)句完成下列對(duì)表操作： （1）給圖書(shū)表增加一列“isbn”，數(shù)據(jù)類(lèi)型為char（10）；alter table 圖書(shū)表add isbn char (10)（2）為剛添加的isbn列增加默認(rèn)值約束，約束名為isbndef，默認(rèn)值為7111085949；alter table 圖書(shū)表add constraint isbndef

22、default 7111085949 for isbn（3）為讀者表中“辦公電話(huà)”一列增加一個(gè)check約束，要求電話(huà)號(hào)碼的前五位是“88320” alter table 讀者表 add constraint c1 check (辦公電話(huà) like 88320_ )（4）刪除圖書(shū)表中isbn列增加的默認(rèn)值約束alter table 圖書(shū)表drop constraint isbndef（5）刪除讀者表中“辦公電話(huà)”列的check約束alter table 讀者表drop constraint c16）刪除圖書(shū)表中的新增isbn列。alter table 圖書(shū)表drop column isbn3、

23、基于以上3個(gè)表，使用sql語(yǔ)句完成以下數(shù)據(jù)更新操作1）向讀者表加入一個(gè)新讀者，該讀者的信息為，讀者號(hào)：001980，姓名：余暇，性別：女，辦公電話(huà)：88320564，部門(mén)：藝術(shù)系insert into 讀者表values (001980,余暇,女,88320564,藝術(shù)系)2）向借閱表插入一個(gè)借閱記錄，表示讀者“王平”借閱了一本書(shū)，圖書(shū)號(hào)：tp316/zw6，借出日期為當(dāng)天日期，歸還日期為空值；insert into 借閱表values (001973,tp316/zw6,2013-6-17,null)（3）讀者“王平”應(yīng)在借出日期的10天之后歸還該書(shū)；update 借閱表set 歸還日期=2

24、013-6-27where 讀者號(hào)=001973 and 圖書(shū)號(hào)=tp316/zw6（4）當(dāng)讀者“王平”按期歸還書(shū)籍后，從借閱表中刪除上述借閱記錄 delete from 借閱表where 讀者號(hào)=001973 and 圖書(shū)號(hào)=tp316/zw64、針對(duì)以上3個(gè)表，完成下列單表查詢(xún)（1）查詢(xún)?nèi)w圖書(shū)的信息； select *from 圖書(shū)表（2）查詢(xún)?nèi)w圖書(shū)信息，其中單價(jià)進(jìn)行打8折操作，顯示列名為“折扣價(jià)”；select 圖書(shū)號(hào),圖書(shū)名,作者,出版社,0.8*單價(jià) 折扣價(jià)from 圖書(shū)表（3）顯示目前所有借閱讀者信息，去掉重復(fù)行；select distinct *from 借閱表（4）顯示所有

25、單價(jià)在20-30元之間的圖書(shū)信息；select *from 圖書(shū)表where 單價(jià) between 20 and 30（5）查詢(xún)所有單價(jià)不在20-30元之間的圖書(shū)信息；select *from 圖書(shū)表where 單價(jià) not between 20 and 30（6）查詢(xún)機(jī)械工業(yè)出版社，科學(xué)出版社、人民郵電出版社的圖書(shū)信息；select *from 圖書(shū)表where 出版社 in (機(jī)械工業(yè)出版社,科學(xué)出版社,人民郵電出版社的圖書(shū)信息)（7）查詢(xún)非人民郵電出版社的信息；select *from 圖書(shū)表where 出版社 not in (人民郵電出版社的圖書(shū)信息)（8）查找姓名第二個(gè)字符是“建”并

26、且只有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）查

27、詢(xún)無(wú)歸還日期的借閱記錄select *from 借閱表where 歸還日期 is null（13）查詢(xún)單價(jià)在20元以上、30元以下的機(jī)械工業(yè)出版社的圖書(shū)及單價(jià)；select 圖書(shū)名,單價(jià)from 圖書(shū)表where 單價(jià) between 20 and 30 and 出版社=機(jī)械工業(yè)出版社14）查詢(xún)讀者總?cè)藬?shù)；select count (*)from 讀者表（15）查詢(xún)借閱了圖書(shū)的讀者總?cè)藬?shù)；select distinct count (*)from 借閱表16）查詢(xún)機(jī)械工業(yè)出版社圖書(shū)的平均價(jià)格、最高價(jià)、最低價(jià)；select avg(單價(jià)),max(單價(jià)),min (單價(jià))from 圖書(shū)表wher

28、e 出版社=機(jī)械工業(yè)出版社（17）查詢(xún)借閱圖書(shū)超過(guò)2本的讀者號(hào)、總本數(shù)，并按照借閱本數(shù)從大到小排列；select 讀者號(hào),count(*)總本數(shù)from 借閱表group by 讀者號(hào) having count(*)=2order by count(*) desc5、針對(duì)以上3個(gè)表，完成下列各項(xiàng)多表連接查詢(xún)(1)查詢(xún)讀者的基本信息和借閱情況select 讀者表.*,借閱表.*from 讀者表,借閱表where 讀者表.讀者號(hào)=借閱表.讀者號(hào)（2）查詢(xún)讀者的讀者號(hào)，姓名，借閱的圖書(shū)名、借出日期及歸還日期select 讀者表.讀者號(hào),姓名,借閱表.圖書(shū)號(hào),借出日期,歸還日期from 讀者表,借閱表where 讀者表.讀者號(hào)=借閱表.讀者號(hào)（3）查詢(xún)借閱了機(jī)械工業(yè)出版社出版的，并且書(shū)名中包括“數(shù)據(jù)庫(kù)”3個(gè)字的圖書(shū)的讀者的讀者號(hào)，姓名，借閱的圖書(shū)名、出版社、借出日期及歸還日期。select 讀者表.讀者號(hào),姓名,借閱表.圖書(shū)號(hào),出版社,借出日期,歸還日期from 讀者表,借閱表,圖書(shū)表where 讀者表.讀者號(hào)=借閱表.讀者號(hào) and 出版社=機(jī)械工業(yè)出版社 and 圖書(shū)名 like%數(shù)據(jù)庫(kù)%and 圖書(shū)表.圖書(shū)號(hào)=借閱表.圖書(shū)