《数据库原理实验报告与作业(2005.12).doc》由会员分享，可在线阅读，更多相关《数据库原理实验报告与作业(2005.12).doc（43页珍藏版）》请在三一文库上搜索。
1、数据库原理与应用实验报告册 学 院：信息工程学院专 业：软件工程班 级：14软工一班学 号：1322120144姓 名：杨晓东信息工程学院2015-12实验项目列表序号实验项目名称实验性质成绩1实验一 数据定义语言设计型2实验二 数据完整性设计型3实验三 数据查询语言验证型4实验四 数据操作语言、视图、索引验证型5实验五 SQL语言综合练习验证型6实验六 数据库安全性验证型7实验七 数据库设计设计型89101112宁波大红鹰学院实验报告实验名称：数据定义语言实验时间：2016 年 4 月 7 日 实验类型： 实验地点： 成绩： 指导教师签字： 一、实验目的二、实验原理（语句）CREATE DA
2、TABASE ;USE ;DROP DATABASE 三、实验环境SQL Sever四、实验数据记录1、在新创建的数据库Library中创建三张表Books、Readers、L_R。1）Books（bookid，bookname，author，price，pubcompany，csl）字段说明：bookid图书编号，bookname图书名称，author作者，price单价，pubcompany出版社，csl藏书量create database librarycreate table books (bookid char(7) primary key,bookname char(20) not
3、 null,author varchar(30) not null,price money not null,pubcompany varchar(50) not null,csl int not null)2）Readers（rno，rname，rsex，spet，dept，bday）字段说明：rno读者编号，rname读者姓名，rsex性别，spet专业，dept分院，bday出生日期create table Readers (rno char(6) primary key,rname nvarchar(8) not null, rsex nvarchar(2) check(ssex in
4、(男,女),spet char(10) not null,dept char(20) not null,bday smalldatetime)3）L_R （rno，bookid，lenddate，limitdate，returndate，fine）字段说明：rno读者编号，bookid图书编号，lenddate借书时间，limitdate限定还书日期，returndate还书时间，fine罚金create table L_R(rno char(6) not null ,bookid char(7) not null,lenddate date not null,limitdate date n
5、ot null,returndate date not null,fine money not null,primary key(rno,bookid)3、ALTER TABLE语句练习。在已建好的三张表中，增加新的列、修改已有的列，删除列。1）在books中增加列classid(分类号) ALTER TABLE booksadd classid char(10)2）修改books中classid的数据类型ALTER TABLE booksALTER COLUMN classid Nvarchar(20)3）删除books中的列classidALTER TABLE booksDrop COLU
6、MN classid五、数据测试与分析分别在表中输入以下记录测试创建的表是否正确，如果有错试分析原因。（1）在Books中输入本教材的信息：987-7-04-019583-5 数据库系统概论 王珊 33.8 高等教育出版社 50（2）在Readers中输入本人的信息：如 09111111 小王（3）在L_R中输入下你借阅教材的信息：如09111111 987-7-04-019583-5 2011-9-1 2011-9-28 2011-9-28 0（1）Books中录入教材信息时出现的错误：将截断字符串或二进制数据 原因：数据类型不对 解决办法：（2）Readers中录入数据时出现的错误： 原因
7、： 解决办法：（3）L_R中录入借阅数据时出现的错误： 原因： 解决办法：六、讨论、心得（主要整理和总结整个实验过程中出现过的错误、解决办法和注意事项）宁波大红鹰学院实验报告实验名称：数据完整性实验时间： 2016 年 4 月 7日 实验类型： 实验地点： 成绩： 指导教师签字： 一、实验目的二、实验原理（语句）主键外键非空唯一检查缺省值（默认）三、实验环境SQL Sever四、实验数据记录1、创建数据库S_T。create database S_T2、新建一张学生信息表，该表至少包含学号，姓名，性别，出生日期，专业，身份证号，联系方式等属性，根据以下学生信息为每个属性选择合适的数据类型。学号
8、姓名性别出生日期专业身份证号联系方式09011101李彤女1989-6-5JK3020319890605234X654234要求：在建表的过程设置约束，根据实际情况选择属性设置主码(primary key)，唯一约束(unique)，为性别设置检查约束(check)使该属性只能取“男”或 “女”。create table S_X(sno char(8)primary key,sname nvarchar(8) not null,ssex nchar(2) check(ssex in(男,女),birthdate SMALLDATETIME,szy char(4) not null,sfzh c
9、har(18) not null ,sjh char(6) not null)3、新建一张专业信息表，该表至少包含专业号，专业名称，专业负责人，专业简介等属性，根据以下专业信息为每个属性选择合适的数据类型。专业号专业名称专业负责人专业简介JK计算机科学与技术张飞Null要求：在建表的过程设置约束，根据实际情况选择属性设置主码(primary key)，唯一约束(unique)。create table ZY_XX(zno char(8) primary key,zname char(20) not null,zyfzr char(8) not null,zyjs varchar(50)4、新建
10、一张课程信息表，该表至少包含课程号，课程名称，学分，开课学期等属性，根据以下课程信息为每个属性选择合适的数据类型。课程号课程名称学分开课学期10001数据库原理35要求：在建表的过程设置约束，根据实际情况选择属性设置主码(primary key)，为学分设置约束使该属性取值范围为1到10之间的整数。create table SKC_XX(kno char(5) primary key,kname char(20) not null,xf int not null,ccreadit smallint check(ccreadit =1and ccreadit =0and grade=1),pri
11、mary key (sno,cno)思考：如果要在选课信息表中为基点设置一个约束，使得基点在成绩小于60时取0，大于等于60时取大于等于1的值。该约束应如何定义？6、修改学生信息表，选择该表中合适的属性设置外码约束（foreign key），实现表间数据的参考完整性。create table student(sno char(9) primary key,sname varchar(20) not null,ssex char(2)check(ssex in(男,女)sbirth date,sdept varchar(10) not null,sid char(18) unique,sphon
12、e varchar(20),foreign key (sdept) references dept(dno) )7、修改选课信息表，选择该表中合适的属性设置外码约束（foreign key），要求允许级联更新，但不允许级联删除。create table sc(sno char(9) not null,cno char(5) not null,grade smallint check(grade=0 and grade=1),primary key(sno,cno),foreign key(sno) references student(sno),no delete cascadeon upda
13、te cascade;foreign key (cno) references course(cno)no delete cascadeon update cascade; )思考：选课信息表可以设置外码约束的属性有哪些?五、数据测试与分析表里的约束设置完后，可通过向表输入数据来检测设置的约束是否有效。写出测试方案。1、测试主码约束：insert into student values(09011101,李彤,女,1989-6-5,JK,33020319890605234X,654234)insert into student values(09011101,李彤,女,1989-6-5,JK,
14、33020319890605234X,654234)2、测试唯一约束insert into student values(09011101,李彤,女,1989-6-5,JK,33020319890605234X,654234)insert into S_X values(09011102,李彤,女,1989-6-3,JK,33020319890605234X,654234)3、测试检查约束：insert into student values(09011101,李彤,女,1989-6-5,JK,33020319890605234X,654234) insert into S_X values(
15、09011103,李彤,1,1989-6-5,JK,330203198906052342,654234)4、测试外码约束：insert into ZY_XX values(JL,计算机科学与技术,张飞,null)insert into student values(09011101,李彤,女,1989-6-5,JK,33020319890605234X,654234)六、讨论、心得 宁波大红鹰学院实验报告实验名称：数据查询语言实验时间： 年 月 日 实验类型： 实验地点： 成绩： 指导教师签字： 一、实验目的二、实验原理（语句）三、实验环境四、实验数据记录1）查询“上海人民出版社”出版的图书详
16、细信息。select * from Bookswhere pubcompany=上海人民出版社 2）查询图书单价介于10和30元之间的图书编号、书名、单价和作者（至少用两种方法）。方法一select bookid,bookname,price,author from Bookswhere price between 10 and 30方法二select bookid,bookname,price,author from Bookswhere price=10 and price=30 3）查询图书馆中的书来自哪些出版社（要求查询结果中“出版社”不重复）。提示：即查询books表中的出版社。SE
17、LECT pubcompany,COUNT(*) AS 出版社数量FROM Booksgroup by pubcompany4）查询分类号为“jsj”和“kh”的图书的图书编号、分类号和书名（至少用两种方法）。方法一select bookid ,classid,bookname from Bookswhere classid=jsjor classid=kh方法二select bookid ,classid,bookname from Bookswhere classid in(jsj , kh)5）查询姓“李”，且专业是“信管”的读者的姓名和专业。select rname ,spet fro
18、m Readerswhere rname like李% and spet=信管6）查询书名中包含“数据库”这三个字的图书信息：图书编号，分类号，书名，单价。select bookid,classid,bookname,price from Bookswhere bookname like%数据库%7）查询书名中包含“1%”这几个字的图书信息：图书编号，分类号，书名。select bookid ,classid ,bookname from Books where bookname like _%1_8）查询没有还书的读者的编号。（从L_R表中查询）select rno from L_R whe
19、re returndate is null9）查询“上海人民出版社”、“清华大学出版社”、“北京希望出版社”这三个出版社的图书信息，查询结果按藏书量（csl）作降序排列。SELECT * FROM BooksWHERE pubcompany IN(上海人民出版社,清华大学出版社,北京希望出版社)order by csl DESC10）查询所有图书，查询结果按藏书量降序排列，同一藏书量的按单价升序排列。SELECT * FROM Booksorder by csl DESC,price ASC11）统计图书信息表中图书的总藏书量（csl的总和）、平均单价和总的图书数量（按图书编号计算图书数量）。
20、SELECT count(csl) AS 总藏书量 ,AVG(price) AS 平均价格 ,count(bookid ) AS 总的图数量FROM BooksGROUP BY CSL12）统计图书馆中各个出版单位的图书数量，查询结果按图书数量作降序排列。SELECT pubcompany, COUNT(*) AS 图书数量FROM Books GROUP BY pubcompanyorder by 图书数量 DESC 13）根据借阅信息表，统计每天借出图书的数量，查询结果显示日期、借出数量，查询结果按借出数量降序排序。select lenddate ,COUNT (rno) AS 借出图书数
21、量 from L_R GROUP BY lenddate order by 借出图书数量 DESC14）查询总藏书量低于5本的图书的分类号。select csl,classid from Bookswhere csl316）查询“王小丫”的借书信息（借书证号，姓名，图书编号，书名，借书日期）。select readers.rno ,rname,books.bookid,Books.bookname,lenddatefrom L_R join Readers on L_R.rno=Readers.rno Join books on Books.bookid=L_R.bookid17）查询在“20
22、06-1-1”到“2006-12-31”之间借书的读者的姓名。SELECT Readers.rno,lenddate,Readers.rnameFROM Readers JOIN L_RON Readers.rno =L_R.rnoWHERE lenddate=2006-1-1 and lenddate=2006-12-3118）查询与达芬奇的密码同一出版社的图书名称。select bookname from Bookswhere bookname达芬奇的密码 and pubcompany=( select pubcompany from books where bookname=达芬奇的密码
23、)思考：还可以用什么方法完成本次查询？select pubcompany from books where bookname=达芬奇的密码select bookname from Books where pubcompany=上海人民出版社19）查询还没有还书的读者的姓名及其所在专业。select rname ,deptfrom L_R join Readers on L_R.rno=Readers.rnowhere fine0思考：还可以用什么方法完成本次查询？20）查询从来没有借过书的读者的借书证号、姓名和班级。21）查询所借图书中单价最高的3本图书书名。（允许并列的情况）select t
24、op 3 price ,bookname from Books join L_R on Books.bookid =L_R.bookid22）查询借阅过达芬奇的密码这本书的读者的信息。select * from L_R join Readers on L_R.rno=Readers.rno Join books on Books.bookid=L_R.bookidwhere bookname=达芬奇的密码23）统计有多少人借阅过达芬奇的密码这本书。select COUNT (L_R.bookid)from L_R join Readers on L_R.rno=Readers.rno Join
25、 books on Books.bookid=L_R.bookid where bookname=达芬奇的密码24）查询既借阅过达芬奇的密码又借阅过数据库系统概论的读者的姓名。select L_R.rno from Books join L_R on L_R.bookid=Books.bookidwhere bookname=达芬奇的密码 and bookname= 数据库系统概论25）查询借阅过达芬奇的密码或借阅过数据库系统概论的读者的姓名。select rname from Readerswhere rno in(select rno from L_R where bookid in(se
26、lect bookid from Books where bookname=达芬奇的密码or bookname=数据库系统概论) 26）查询没有按时还书的读者的编号。select rno from L_Rwhere fine027）查询没有按时还书的读者的姓名。select L_R.rno,rname from L_R join Readers on L_R.rno=Readers.rnowhere fine028）查询“SQL SERVER2005教程”这本图书被借出几次。select COUNT(L_R.lenddate) from L_R join Books on L_R.bookid
27、=Books.bookidwhere bookname=SQL SERVER2005教程五、出错记录与分析（请如实记录实验过程中出现的问题，并分析原因）六、讨论、心得 宁波大红鹰学院实验报告实验名称：数据操纵语言、视图、索引实验时间： 年 月 日 实验类型： 实验地点： 成绩： 指导教师签字： 一、实验目的二、实验原理（语句）三、实验环境四、实验数据记录1、在ReadBook中完成下列数据更新（1）向Books表添加一个新的元组。（元组的可自定义）INSERT INTO books(bookid,classid,bookname,author,price,pubcompany,csl) val
28、ues (sn9022,zh,我是外星人,杨洋,24,上海人民出版社,8)（2）完成“04204”读者所借的“sn9002”图书的还书操作。即将“04204”读者所借的“sn9002”图书的还书时间改为当前系统时间（getdate()），罚款金额改为0。UPDATE L_RSET returndate= getdate(),fine=0where rno=04204（3）修改“中国自助游”这本图书的藏书量，在原藏书量上加2。update Books set csl=csl+2where bookname=中国自助游 （4）删除（1）在Books表中添加的元组。DELETE FROM Books
29、WHERE bookid =sn9022（5）统计每个不同分类号图书的平均价格并将统计结果存入到ReadBook数据库中。 提示：用两种方法，即使用SELECT INTO和INSERT INTO SELECT语句这两个表复制语句完成。2、视图的创建与应用（1）定义视图V_RB，该视图反映所有的借阅情况，包含读者的编号、姓名、专业、图书编号、书名、借书日期及还书日期。CREATE VIEW V_RB(读者的编号,姓名,专业,图书编号,书名,借书日期,还书日期)as select R.rno,R.rname,R.spet,W.bookid,bookname,lenddate,returndatef
30、rom readers as R JOIN L_R as W on R.rno=W.rno Join Books as B on B.bookid=W.bookid（2）定义视图V_NR，该视图反映所有未还书学生信息，包括学生的学号、姓名、图书编号、书名及借书日期。（用两种方法）CREATE VIEW V_NR(学生的学号,姓名,图书编号,书名,借书日期)as select R.rno,R.rname,W.bookid,bookname,lenddatefrom readers as R JOIN L_R as W on R.rno=W.rno Join Books as B on B.boo
31、kid=W.bookid（3）定义视图V_R_AVG，该视图包含学生的学号和借书数量(Count（*）)。create view V_R_AVG as select rno ,COUNT(*) as totle from L_R group by rno（4）定义视图V_RN_AVG，该视图包含学生的学号、姓名和借书数量。create view V_RN_AVG asselect readers.rno,rname,COUNT(bookid) as 借书数量from readers left join L_R on Readers.rno=L_R.rnogroup by readers.rno
32、,rname3、在上一题定义的视图V_RB中完成下列查询（1）查询“网络”专业的借阅情况。 select 读者的编号 from V_RBwhere 专业=网络（2）统计每个专业借书人次(Count（*）)。select 专业,COUNT(读者的编号) from V_RBgroup by 专业4、索引的创建（1）为Books表的属性bookname创建索引ix_b_bname。五、出错记录与分析（请如实记录实验过程中出现的问题，并分析原因）六、讨论、心得思考题（1） 同一个insert语句第一次执行是正确的，第二次再执行的时候系统提示出错，试分析原因。（2） 哪些情况下需要为视图指定列名？ 计算
33、列 没名字的时候 宁波大红鹰学院实验报告实验名称：SQL语言综合练习实验时间： 年 月 日 实验类型： 实验地点： 成绩： 指导教师签字： 一、实验目的二、实验原理（语句）三、实验环境四、实验数据记录1、在jxgl数据库中完成下列查询。（1）查询03物流1学生的详细信息。select * from Studentswhereclass=03物流（2）查询“入学成绩”在350到400分之间的学生的姓名和班级（至少用两种方法）。方法一select sname,class from Studentswheremgrade=350 and mgrade=400方法二select sname,class
34、 from Studentswheremgrade between 350 and 400（3） 查询students表中现有的班级（要求查询结果中班级名称不要重复）。select class from Students group by class （4）查询具有“教授”或“副教授”职称的教师的教师编号和姓名（至少用两种方法）。方法一select tno ,tname from Teachers where ps=教授or ps=副教授方法二select tno ,tname from Teachers where ps in(教授,副教授)（5）查询姓“陈”，且籍贯是“宁波”的学生的姓名，
35、出生日期，入学成绩。select sname,bday,mgrade from Studentswhere sname like陈_and bplace=宁波（6）查询students表中“09级”的学生信息。 select * from Studentswhere class like09%（7）查询姓名中包含“菲”字的学生的学号、姓名、性别、班级。select sname ,sno,ssex,class from Studentswhere sname like _菲_（8）查询教师上课情况表中还没有安排好上课教师的班级和对应的课程号。select class ,cno from TCwh
36、ere tno is null（9）查询全体学生情况，查询结果按所在班级名升序排列，同一班级中的学生按出生日期降序排列。 select * from Students order by class ASC,bday DESC（10）统计03物流1学生“入学成绩”的平均分、最高分、最低分。select AVG(mgrade),MAX(mgrade),MIN (mgrade) from Students where class=03物流（11）统计students表中的学生人数。select count (sno ) from students（12）统计各个班级的学生人数，按统计结果做降序排列。select count (sno )from students group by class