数据库实验报告.doc

数据库原理实验报告学 号：4110115姓 名：王善斌提交日期：2013-06-20成 绩：东北大学秦皇岛分校【实验内容】2在企业管理器中创建一个数据库，要求如下：（1） 数据库名称 Test1。（2） 主要数据文件：逻辑文件名为Test1_data1，物理文件名为Test1_data1.mdf，初始容量为1MB，最大容量为10MB，增幅为1MB。（3） 次要数据文件：逻辑文件名为Test1_data2，物理文件名为Test1_data2.ndf，初始容量为1MB，最大容量为10MB，增幅为1MB。（4） 事务日志文件：逻辑文件名为Test1_log1，物理文件名为Test1_log1.ldf，初始容量为1MB，最大容量为5MB，增幅为512KB。3在查询分析器中创建一个数据库，要求如下：（1） 数据库名称 Test2。（2） 主要数据文件：逻辑文件名为Test2_data1，物理文件名为Test2_data1.mdf，初始容量为1MB，最大容量为10MB，增幅为1MB。（3） 次要数据文件：逻辑文件名为Test2_data2，物理文件名为Test2_data2.ndf，初始容量为1MB，最大容量为10MB，增幅为1MB。（4） 事务日志文件：逻辑文件名为Test2_log1，物理文件名为Test2_log1.ldf，初始容量为1MB，最大容量为5MB，增幅为1MB。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.在查询分析器中按照下列要求修改第3题中创建的数据库test2（1） 主要数据文件的容量为2MB，最大容量为20MB，增幅为2MB。（2） 次要数据文件的容量为2MB，最大容量为20MB，增幅为2MB。事务日志文件的容量为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 (name=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.数据库更名：把test1数据库更名为new_test1alter database test1modify name=new_test15.在企业管理器中删除new_test1数据库，在查询分析器中删除test2数据库。在企业管理器中删除new_test1前在企业管理器中删除new_test1后在查询分析器中删除test3前 在查询分析器中删除test3后drop database test36.为sql示例数据库northwind创建一个备份：northwindBK，并使用该备份文件恢复northwind数据库。exec sp_addumpdevice 'disk','northwindBK','d:数据库备份MyNwind-1.dat'BACKUP DATABASE northwind TO northwindBK实验二 创建并管理表1. 创建数据库，包含如下表，创建这些表并按要求定义约束use studentinfo gocreate 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 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 studentinfo 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)以下为各个表的数据Students表数据Student_idStudent_namesexagedepartment20010101JoneM19Computer20010102SueF20Computer20010103SmithM19Math20030101AllenM18Automation20030102deepaF21ArtCourse表数据Course_idCourse_namePreCouIdCreditsC1English4C2MathC52C3databaseC22Score表数据Student_idCourse_idGrade20010101C19020010103C18820010102C29420010102C2622.增加，修改，删除字段，要求 （1）为表student增加一个memo（备注）字段，类型为varchar(200)代码： use yzwgoalter table student add memo varchar (200)(2).将MEMO字段的数据类型更改为varchar(300) 代码：use yzwgoalter table student alter column memo varchar (300)(3)删除memo字段use yzwgoalter table student drop column memo3.向表中插入数据验证约束use yzwgoalter table score add constraint grade check (grade >0 and grade <100)/建立了一个约束条件可以知道约束条件起作用了。 三 数据更新实验内容：在已经建立的studentinfo数据库和3个students、courses、score基础上完成下列操作。1. 向students表添加一个学生记录，学号为20010112，性别为男，姓名为stefen，年龄25岁，所在系为艺术系art。use yzwgoinsert studentvalues ('20010112','stefen','M','25','Art')2.向score表添加一个选课记录，学生学好为20010112，所选课程号为C2use yzwgoinsert scorevalues ('20010112','C2',null)3.建立临时表tempstudent,结构与students结构相同，其记录均从student表获取use yzwgocreate 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,departmentfrom student4，将所有学生的成绩加5分update scoreset grade=grade+55.将姓名为sue的学生所在系改为电子信息系update studentset department='电子信息系'where student_name='sue'6.将选课的database的学生成绩加10分update scoreset score.grade=score.grade+10where score.course_id='C3'7.删除所有成绩为空的选修记录Delete score Where grade=null8.删除学生姓名为deepa的学生记录由于下表有调用因而删除不掉 四 数据查询1、 查询全体学生的学号、姓名、所在系，并为结果集的各列设置中文名称。select student_id'学号',student_name'姓名',sex'性别',age'年龄',department'学院'from student2.查询全体学生的选课情况，并为所有成绩加5分select *from scoreupdate score set grade=grade+53.显示所有选课学生的学号，去掉重复行select distinct student.student_idfrom student4.查询选课成绩大于80分的学生select score.student_idfrom scorewhere grade>805. 查询年龄在20到30之间的学生学号，姓名，所在系select student.student_id,student.student_name,student.department,student.agefrom studentwhere age between 20 and 306.查询数学系、电子信息系、艺术系的学生学号，姓名。select student.student_id,student.student_namefrom studentwhere department in ('math','电子信息系','Art')7. 查询姓名第二个字符为u并且只有3个字符的学生学号，姓名select student.student_id,student.student_namefrom studentwhere student_name like '_u_'8. 查询所有以S开头的学生。select student.student_id,student.student_namefrom studentwhere student_name like 'S%'9. 查询姓名不以S、D、或J开头的学生select student.student_id,student.student_namefrom studentwhere student_name not like 'S%' and student_name not like 'D%'and student_name not like 'J%'10查询没有考试成绩的学生和相应课程号（成绩值为空）select student_id,course_idfrom scorewhere grade is null11. 求年龄大于19岁的学生的总人数select count (*) '总数'from studentwhere age >1912 别求选修了c1语言课程的学生平均成绩、最高分、最低分学生。select AVG (grade)'平均成绩',max(grade)'最高分',min(grade)'最低分'from scorewhere course_id='c1'13.求学号为20010101的学生总成绩select sum (grade)'总成绩'from scorewhere student_id='20010101'14求每个选课学生的学号，姓名，总成绩select student.student_id,student_name,score.gradefrom student,scorewhere student.student_id=score.student_id15.查询所有姓李且全名为3个汉字的学生姓名，学号，性别select student_id,student_name,sexfrom studentwhere student_name='李_'16.求课程号及相应课程的所有的选课人数select course_id,count(*)'Sum'from score,studentwhere student.student_id=score.student_idgroup by course_id17查询选修了3门以上课程的学生姓名学号select student_id,student_namefrom studentwhere EXISTS (select *from scorewhere score.student_id=student.student_id group by student_id having count(*)>=3) 18.查询每个学生基本信息及选课情况select *from student,course2.查询每个学生学号姓名及选修的课程名、成绩select student.student_id,student.student_name,course_id,gradefrom student,scorewhere student.student_id=score.student_id3.求电子信息系选修课程超过2门课的学生学号姓名、平均成绩并按平均成绩降序排列4.查询与sue在同一个系学习的所有学生的学号姓名select student.student_id,student.student_namefrom studentwhere department=(select departmentfrom studentwhere student_name='sue')5.查询所有学生的选课情况，要求包括所有选修了课程的学生和没有选课的学生，显示他们的姓名学号课程号和成绩（如果有）select student.student_id,student.student_name,score.course_id,score.gradefrom student,scorewhere student.student_id=score.student_id五 索引和视图1、 分别使用企业管理器和查询分析器为northwind数据库中products表建立一个聚集索引，索引字段为产品类型和产品编号。2在已经建立的studentInfo数据库的3个表基础上，完成下列操作：（1） 建立数学系的学生视图；create view mathsthdentview asselect *from studentwhere student.department ='math'（2） 建立计算机系选修了课程名为database的学生的视图，视图名为compStudentview，该视图的列名为学号、姓名、成绩create view compstudentviewasselect student.student_id '学号',student_name '姓名',grade '成绩'from student,scorewhere student.department ='computer' and student.student_id=score.student_id and score.course_id='C3'（3） 创建一个名为studentSumview的视图，包含所有学生学号和总成绩create view studentSumview asselect student.student_id,sum(grade)'sum'from student,scorewhere student.student_id=score.student_idgroup by student.student_id（4） 建立一个计算机系学生选修了课程名为database并且成绩大于80分的学生视图，视图名为CompsutdentView1，视图的列为学号姓名成绩。 create view Compstudentview1asselect student.student_id,student_name,gradefrom student,scorewhere student.department='computer'and score.course_id='C3' and grade>80 and student.student_id=score.student_id（5） 使用sql语句删除compsutdentview1视图。 drop view Compstudentview1数据库查询综合实验1.使用查询分析器建立上述数据库和表； 图书表 读者表 借阅表2、基于以上数据库使用sql语句完成下列对表操作： （1）给图书表增加一列“ISBN”，数据类型为CHAR（10）；alter table 图书表add ISBN char (10)（2）为刚添加的ISBN列增加默认值约束，约束名为ISBNDEF，默认值为7111085949；alter table 图书表add constraint ISBNDEF default 7111085949 for ISBN（3）为读者表中“办公电话”一列增加一个CHECK约束，要求电话号码的前五位是“88320” alter table 读者表 add constraint c1 check (办公电话 LIKE '88320_' )（4）删除图书表中ISBN列增加的默认值约束alter table 图书表drop constraint ISBNDEF（5）删除读者表中“办公电话”列的CHECK约束alter table 读者表drop constraint c16）删除图书表中的新增ISBN列。alter table 图书表drop column ISBN3、基于以上3个表，使用sql语句完成以下数据更新操作1）向读者表加入一个新读者，该读者的信息为，读者号：001980，姓名：余暇，性别：女，办公电话：88320564，部门：艺术系insert into 读者表values ('001980','余暇','女','88320564','艺术系')2）向借阅表插入一个借阅记录，表示读者“王平”借阅了一本书，图书号：TP316/ZW6，借出日期为当天日期，归还日期为空值；insert into 借阅表values ('001973','TP316/ZW6','2013-6-17',null)（3）读者“王平”应在借出日期的10天之后归还该书；update 借阅表set 归还日期='2013-6-27'where 读者号='001973' and 图书号='TP316/ZW6'（4）当读者“王平”按期归还书籍后，从借阅表中删除上述借阅记录 delete from 借阅表where 读者号='001973' and 图书号='TP316/ZW6'4、针对以上3个表，完成下列单表查询（1）查询全体图书的信息； select *from 图书表（2）查询全体图书信息，其中单价进行打8折操作，显示列名为“折扣价”；select 图书号,图书名,作者,出版社,0.8*单价 '折扣价'from 图书表（3）显示目前所有借阅读者信息，去掉重复行；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）查询机械工业出版社，科学出版社、人民邮电出版社的图书信息；select *from 图书表where 出版社 in ('机械工业出版社','科学出版社','人民邮电出版社的图书信息')（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元以下的机械工业出版社的图书及单价；select 图书名,单价from 图书表where 单价 between 20 and 30 and 出版社='机械工业出版社'14）查询读者总人数；select count (*)from 读者表（15）查询借阅了图书的读者总人数；select distinct count (*)from 借阅表16）查询机械工业出版社图书的平均价格、最高价、最低价；select AVG(单价),max(单价),min (单价)from 图书表where 出版社='机械工业出版社'（17）查询借阅图书超过2本的读者号、总本数，并按照借阅本数从大到小排列；select 读者号,count(*)'总本数'from 借阅表group by 读者号 having count(*)>=2order by count(*) desc5、针对以上3个表，完成下列各项多表连接查询(1)查询读者的基本信息和借阅情况select 读者表.*,借阅表.*from 读者表,借阅表where 读者表.读者号=借阅表.读者号（2）查询读者的读者号，姓名，借阅的图书名、借出日期及归还日期select 读者表.读者号,姓名,借阅表.图书号,借出日期,归还日期from 读者表,借阅表where 读者表.读者号=借阅表.读者号（3）查询借阅了机械工业出版社出版的，并且书名中包括“数据库”3个字的图书的读者的读者号，姓名，借阅的图书名、出版社、借出日期及归还日期。select 读者表.读者号,姓名,借阅表.图书号,出版社,借出日期,归还日期from 读者表,借阅表,图书表where 读者表.读者号=借阅表.读者号 and 出版社='机械工业出版社' and 图书名 like'%数据库%'and 图书表.图书号=借阅表.图书号（4）查询至少借阅过1本机械工业出版社出版的图书的读者的读者号，姓名，借阅的图书名、借阅的本数，并按借阅本数从多到少排序。select 读者表.读者号,姓名,借阅表.图书号,count(*)'借阅的本数'from 读者表,借阅表where 读者表.读者号=借阅表.读者号 and 借阅表.读者号 exists (select 借阅表.读者号,count(*)from 图书表,借阅表where 图书表.图书号=借阅表.图书号 and 出版社='机械工业出版社'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、基于数据库的3张表建立下列视图1）创建机械工业出版社图书的视图；create view 机械工业出版社图书视图asselect *from 图书表where 出版社='机械工业出版社'2）创建一个统计视图，名为CountView，包含读者的读者号和总借阅本数； create view countviewasselect 读者号,count(*)'总借阅本数'from 借阅表group by 读者号3）创建一个借阅统计视图，名为CountView10，包含借阅本数大于2的读者号和总借阅本数。 create view countview10asselect 读者号,count(*)'总借阅本数'from 借阅表group by 读者号 having count(*)>=2