数据库实验报告.docx

1、数据库实验报告数据库原理实验报告学 号：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

2、.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

3、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题中创建的数据库t

4、est2（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_lo

5、g,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后在查询

6、分析器中删除test3前 在查询分析器中删除test3后drop database test36.为sql示例数据库northwind创建一个备份：northwindBK，并使用该备份文件恢复northwind数据库。exec sp_addumpdevice disk,northwindBK,d:数据库备份MyNwind-1.datBACKUP DATABASE northwind TO northwindBK实验二 创建并管理表1.创建数据库，包含如下表，创建这些表并按要求定义约束use studentinfo gocreate table student (student_id char

7、(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 t

8、able 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_namesexagedepartment20010101Jo

9、neM19Computer20010102SueF20Computer20010103SmithM19Math20030101AllenM18Automation20030102deepaF21ArtCourse表数据Course_idCourse_namePreCouIdCreditsC1English4C2MathC52C3databaseC22Score表数据Student_idCourse_idGrade20010101C19020010103C18820010102C29420010102C2622.增加，修改，删除字段，要求 （1）为表student增加一个memo（备注）字段，类

10、型为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

11、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个字符的学生学号，姓

12、名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 st

13、udent_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

14、=c113.求学号为20010101的学生总成绩select sum (grade)总成绩from scorewhere student_id=2001010114求每个选课学生的学号，姓名，总成绩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

15、=李_16.求课程号及相应课程的所有的选课人数select course_id,count(*)Sumfrom 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(*)=

16、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_nam

17、efrom 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、分别使用企业管理器和查询分析器为no

18、rthwind数据库中products表建立一个聚集索引，索引字段为产品类型和产品编号。2在已经建立的studentInfo数据库的3个表基础上，完成下列操作：（1）建立数学系的学生视图；create view mathsthdentview asselect *from studentwhere student.department =math（2）建立计算机系选修了课程名为database的学生的视图，视图名为compStudentview，该视图的列名为学号、姓名、成绩create view compstudentviewasselect student.student_id 学号,st

19、udent_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)sumfrom student,scorewhere student.student_id=score.student

20、_idgroup by student.student_id（4）建立一个计算机系学生选修了课程名为database并且成绩大于80分的学生视图，视图名为CompsutdentView1，视图的列为学号姓名成绩。 create view Compstudentview1asselect student.student_id,student_name,gradefrom student,scorewhere student.department=computerand score.course_id=C3 and grade80 and student.student_id=score.stud

21、ent_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

22、 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语句完成以

23、下数据更新操作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-27where 读者号=

24、001973 and 图书号=TP316/ZW6（4）当读者“王平”按期归还书籍后，从借阅表中删除上述借阅记录 delete from 借阅表where 读者号=001973 and 图书号=TP316/ZW64、针对以上3个表，完成下列单表查询（1）查询全体图书的信息； select *from 图书表（2）查询全体图书信息，其中单价进行打8折操作，显示列名为“折扣价”；select 图书号,图书名,作者,出版社,0.8*单价 折扣价from 图书表（3）显示目前所有借阅读者信息，去掉重复行；select distinct *from 借阅表（4）显示所有单价在20-30元之间的图书信息；s

25、elect *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）查找以“王”、“张”、“李”开头的所有读者的读者号姓名；