中南大学 数据库实验报告
中南大学
数据库原理实验报告
学院：信息科学与工程学院
班级：电子1102 学号： 0909101329 姓名：毛艾力
指导老师：张祖平
完成时间： 2014.4.27
实验一、熟悉ORALCE环境并练习SQL的定义
一、目的与要求
本实验主要是熟悉ORACLE的运行环境，在CLIENT端进行联接设置与服务器联通，进入ORACLE的SQL*PLUS的操作环境，进一步建立表格，并考虑主键,外部键,值约束。
二、操作环境
硬件：主频2GHz以上服务器（内存2GB以上、硬件空闲2.2GB以上），主频1GHz以上微机，内存1GB以上。
软件：WINDOWS XP/2000/2003/或win7/2008等，如操作系统是SERVER版可安装ORACLE/9i/10g/11g/12C FOR NT/WINDOWS(注意在32位与64位的区别，可选企业版) 如果Windows非server如XP/win7等，安装时请选择个人版(PERSONAL)
三、实验内容
1.上机步骤
在实验室上机时,一般安装时管理员已设置好.先找到windows中的oracle服务（一般为OracleServiceORCL，如下图1）,启动此服务,再找到sql*plus所在的位置（一般桌面上有如下图2的程序组有）,运行即可进行登录界面.
图1 Oracle在计算机系统中的服务
图2称Sql*plus在系统程序组中的位置
实验室现有环境操作步骤：
通过桌面我的电脑找到管理服务的入口，如图3，或者通过资源管理器找到管理。
图3 通过桌面我的电脑找到管理服务的入口
然后找到服务，如图4。
图4 系统中的服务
再找启动oracle的服务如图1：
（1）从桌面或在程序中（图2）执行SQL*PLUS
（2）输入：system/manager
（3）在SQL>状态输入建立用户命令:
create user username identified by password;
其中username与password不要数字开头，用户名username中按规则加班级学号后缀如：U_11221表示1班学号尾数为1221号的用户。
（4）给用户授权：
grant resource,connect to username;
(5) 连接用户：
connect username/password
在SQL>就可以建表等操作了
2.根据要求建立如下表
定义基本表格book_?????（书）与reader_?????(读者）及lend_?????(借阅），关系模式如下：
book_?????(bno char(10) Primary key, btitle VARCHAR2(32),author VARCHAR2(32),publisher varchar2(32)，price number(7,2) <10000）
reader_?????(RNO char(10) primary key, RNAME VARCHAR2(32), sex VARCHAR2(4)
‘男’,’女’,’其它’,’其他’),age number(3)，tel varchar2(13),e-mail varchar2(32) 检
查邮箱格式的合法性)
lend_?????(RNO char(10) FOREIGN key, bno char(10) FOREIGN key, BORROWDATE DATE,
RETURNDATE/*应还书日期*/ DATE, realRetDate /*实际还书日期*/ date,，PRIMARY KEY(RNO,
BNO, BORROWDate),check(RETURNDATE- BORROWDATE<=100))
注意表的命名规则
3.插入样本数据
每个表至少插入10条以上的记录，要求在reader表中要有自己的信息，可以学号为读
者号，可以参考教材上的数据。
四、思考题：
（1）能否任意改变表的名称？
不能任意改变表名称；虽然可以改变表名称但任意改变表名称后会让其他与之相
关的表中出现错误。
（2）能否先删除reader表或book表？
不能，要从后往前删；因为reader中的rno和book中的bno是lend表中的外键，要先删除lend表，
再删book表和reader表。
（3）能否改变book表中BNO类型与长度，或改变READER表中Rno的类型与长度。
不能，会出现列类型与引用的列类型不兼容错误；因为lend表中引用到了bno和rno。
（4）测试主键，外键与值的约束条件。
一个表中主键的值不能相同，即book_21606中bno 0119320111只能有一个，不然会违反唯一性约束；
外键的值只能是所对应主键有的值，如insert into lend_21606
values('0909112313','0119320120',TO_DATE('2014-03-20','yyyy-MM-dd'),TO_DATE ('2014-05-25','yyyy-MM-dd'),TO_DATE('2014-03-30','yyyy-MM-dd'));由于reader 中BNO中未添加0909112313将会出现
五、实验结论
（1）将V ARCHAR2(n) 写成varchar2(n )出现提示缺失右括号错误；有的类型可以小写char(n )有的则必须大写V ARCHAR2(n)；
（2）定义外键时references少写s出现提示缺失右括号错误；
（3）日期不会输入，通过TO_DATE进行转换。
（4）熟悉了oracle的使用。
六、源代码：
Create user U_21329 identified by maoaili;
Grant resource,connect to u_21329;
Connect u_21329/maoaili;
Create table
book_21329(bno char(10) Primary key,
btitle VARCHAR2(32),
author VARCHAR2(32),
publisher varchar2(32)，
price number(7,2) check (price between 0 and 10000));
Create table
reader_21329 (RNO char(10) primary key,
RNAME VARCHAR2(32),
sex VARCHAR2(4) check (sex in ('男','女','其他','其它')),
age number(3)，
tel varchar2(13),
e_mail varchar2(32) check(e_mail like '%@%_%'));
Ceate table
lend_21329(RNO char(10) ,FOREIGN key(RNO) references reader_21329(RNO), bno char(10) ,FOREIGN key(bno) references book_21329(bno), BORROWDATE DATE,
RETURNDATE/*应还书日期*/ DATE,
realRetDate /*实际还书日期*/ date,
PRIMARY KEY(RNO, BNO, BORROWDate),
check(RETURNDATE- BORROWDATE<=100));
insert into book_21329
values('0119320111','昆曲的源头','李莉','远方出版社','38');
insert into book_21329
values('0119320112','铁道学院的历史发展','张三丰','铁道出版社','25');
insert into book_21329
values('0119320113','英雄的意义','诺言','上海文艺出版社','30');
insert into book_21329
values('0119320114','乾隆的厨房','洪七公','山西文艺出版社','38');
insert into book_21329
values('0119320115','无间道','李明才','光明日报出版社','45');
insert into book_21329
values('0119320116','数据库应用','孙红雷','邮政出版社','33');
insert into book_21329
values('0119320117','故事会','范冰冰','人民出版社','38');
insert into book_21329
values('0119320118','人类群星闪耀的时刻','贾克斯','自然出版社','42');
insert into book_21329
values('0119320119','红楼梦','贾宝玉','人民邮政出版社','30');
insert into book_21329
values('0119320120','史蒂夫·乔布斯传','史蒂夫·乔布斯','中信出版社','22'); insert into book_21329
values('0119320121','数据库','李若彤','科学出版社','38');
insert into book_21329
values('0119320122','数据库原理及应用','张祖平','中南大学出版社','38');
insert into reader_21329
values('0909101329','毛艾力','男','22','151********','416783747@https://www.wendangku.net/doc/6716654710.html,'); insert into reader_21329
values('0909101328','张信哲','男','22','151********','37562135@https://www.wendangku.net/doc/6716654710.html,'); insert into reader_21329
values('0909101327','阿朵','男','23','151********','375621353@https://www.wendangku.net/doc/6716654710.html,'); insert into reader_21329
values('0909101326','陈冠希','女','21','151********','392677513@https://www.wendangku.net/doc/6716654710.html,'); insert into reader_21329
values('0909101325','泰勒·斯威夫特','男','21','131********','3729863254@https://www.wendangku.net/doc/6716654710.html,');
insert into reader_21329
values('0909101324','王若琳','女','21','131********','3326163254@https://www.wendangku.net/doc/6716654710.html,'); insert into reader_21329
values('0909101323','贾乃亮','女','22','131********','376211369@https://www.wendangku.net/doc/6716654710.html,');
insert into reader_21329
values('0909101322','王若冰','女','22','137********','376211395@https://www.wendangku.net/doc/6716654710.html,'); insert into reader_21329
values('0909101321','李宗盛','女','22','187********','376211365@https://www.wendangku.net/doc/6716654710.html,'); insert into reader_21329
values('0909101320','王大锤','男','22','187********','376211262@https://www.wendangku.net/doc/6716654710.html,'); insert into reader_21329
values('0909101319','李正四','男','23','157********','376211262@https://www.wendangku.net/doc/6716654710.html,');
insert into lend_21329
values('0909101329','0119320111',TO_DATE('2013-12-22','yyyy-MM-dd'),TO_DAT E('2014-03-22','yyyy-MM-dd'),TO_DATE('2014-03-12','yyyy-MM-dd'));
insert into lend_21329
values('0909101329','0119320122',TO_DATE('2013-12-22','yyyy-MM-dd'),TO_DAT E('2014-03-22','yyyy-MM-dd'),TO_DATE('2014-03-25','yyyy-MM-dd'));
insert into lend_21329
values('0909101328','0119320111',TO_DATE('2013-12-22','yyyy-MM-dd'),TO_DAT E('2014-03-22','yyyy-MM-dd'),TO_DATE('2014-03-12','yyyy-MM-dd'));
insert into lend_21329
values('0909101328','0119320122',TO_DATE('2013-12-22','yyyy-MM-dd'),TO_DAT E('2014-03-22','yyyy-MM-dd'),TO_DATE('2014-04-12','yyyy-MM-dd'));
insert into lend_21329
values('0909101327','0119320113',TO_DATE('2014-03-16','yyyy-MM-dd'),TO_DAT E('2014-05-21','yyyy-MM-dd'),TO_DATE('2014-03-27','yyyy-MM-dd'));
insert into lend_21329
values('0909101327','0119320114',TO_DATE('2014-03-17','yyyy-MM-dd'),TO_DAT E('2014-05-22','yyyy-MM-dd'),TO_DATE('2014-03-29','yyyy-MM-dd'));
insert into lend_21329
values('0909101326','0119320114',TO_DATE('2014-03-17','yyyy-MM-dd'),TO_DAT E('2014-05-22','yyyy-MM-dd'),TO_DATE('2014-03-29','yyyy-MM-dd'));
insert into lend_21329
values('0909101326','0119320122',TO_DATE('2014-03-20','yyyy-MM-dd'),TO_DAT E('2014-05-25','yyyy-MM-dd'),TO_DATE('2014-03-30','yyyy-MM-dd'));
insert into lend_21329
values('0909101325','0119320119',TO_DATE('2014-03-20','yyyy-MM-dd'),TO_DAT E('2014-05-25','yyyy-MM-dd'),TO_DATE('2014-03-30','yyyy-MM-dd'));
insert into lend_21329
values('0909101325','0119320117',TO_DATE('2014-03-20','yyyy-MM-dd'),TO_DAT E('2014-05-25','yyyy-MM-dd'),TO_DATE('2014-03-30','yyyy-MM-dd'));
insert into lend_21329
values('0909101324','0119320116',TO_DATE('2014-03-20','yyyy-MM-dd'),TO_DAT E('2014-05-25','yyyy-MM-dd'),TO_DATE('2014-03-30','yyyy-MM-dd'));
insert into lend_21329
values('0909101324','0119320116',TO_DATE('2014-03-20','yyyy-MM-dd'),TO_DAT
E('2014-05-25','yyyy-MM-dd'),TO_DATE('2014-03-30','yyyy-MM-dd'));
insert into lend_21329
values('0909101323','0119320120',TO_DATE('2014-03-20','yyyy-MM-dd'),TO_DAT E('2014-05-25','yyyy-MM-dd'),TO_DATE('2014-03-30','yyyy-MM-dd'));
insert into lend_21329
values('0909101323','0119320121',TO_DATE('2014-03-23','yyyy-MM-dd'),TO_DAT E('2014-05-23','yyyy-MM-dd'),TO_DATE('2014-04-02','yyyy-MM-dd'));
insert into lend_21329
values('0909101322','0119320121',TO_DATE('2014-03-25','yyyy-MM-dd'),TO_DAT E('2014-05-25','yyyy-MM-dd'),TO_DATE('2014-04-01','yyyy-MM-dd'));
insert into lend_21329
values('0909101322','0119320121',TO_DATE('2014-03-25','yyyy-MM-dd'),TO_DAT E('2014-05-25','yyyy-MM-dd'),TO_DATE('2014-04-01','yyyy-MM-dd'));
insert into lend_21329
values('0909101321','0119320117',TO_DATE('2014-02-25','yyyy-MM-dd'),TO_DAT E('2014-04-25','yyyy-MM-dd'),TO_DATE('2014-04-03','yyyy-MM-dd'));
insert into lend_21329
values('0909101321','0119320119',TO_DATE('2014-02-25','yyyy-MM-dd'),TO_DAT E('2014-04-25','yyyy-MM-dd'),TO_DATE('2014-04-03','yyyy-MM-dd'));
insert into lend_21329
values('0909101320','0119320117',TO_DATE('2014-02-23','yyyy-MM-dd'),TO_DAT E('2014-04-23','yyyy-MM-dd'),TO_DATE('2014-04-02','yyyy-MM-dd'));
insert into lend_21329
values('0909101320','0119320119',TO_DATE('2014-02-23','yyyy-MM-dd'),TO_DAT E('2014-04-23','yyyy-MM-dd'),TO_DATE('2014-05-02','yyyy-MM-dd'));
insert into lend_21329
values('0909101319','0119320121',TO_DATE('2014-03-25','yyyy-MM-dd'),TO_DAT E('2014-05-25','yyyy-MM-dd'),TO_DATE('2014-05-27','yyyy-MM-dd'));
实验二、数据更新操作
一、目的与要求
本实验主要是熟悉在SQL*PLUS环境下进行数据记录查询、更新与删除，其中查询考虑简单查询,复杂查询,嵌套查询。
二、操作环境
同实验一。
三、实验内容
1．查询所有书的记录
2．查询所有读者的记录，并按读者姓名升序，读者号降序排列
3.查询有自己姓名的记录
4.查询’李正四’的RNO，RNAME，TEL，E-MAIL
5.查询借阅过数据库书的所有读者记录
6.查询借阅书的本数与人数，如1本，1000人，2本200人，…,100本，10人等
7.查询’李正四’的RNO，RNAME，BNO， BTITLE，借书本数
8.在lend表中增加是否过期（IsOverDue）字段,并求出相应的值（考虑应还书日期与实际还书日期的关系）（用alter 与update）
9.删除’中南出版社’的所有图书及相应的借书记录
四、源代码
select * from book_21329;
select * from reader_21329
order by RNAME ASC ,RNO DESC;
select
reader_21329.RNO,RNAME,book_21329.bno,btitle,lend_21329.BORROWDAT E from book_21329,reader_21329,lend_21329
where reader_21329.RNAME='毛艾力' and book_21329.bno=lend_21329.bno and reader_21329.RNO=lend_21329.RNO;
select RNO,RNAME,tel,e_mail from reader_21329
where RNAME='李正四';
select
book_21329.btitle,reader_21329.RNO,RNAME,tel,e_mail,lend_21329.BORROW DATE from book_21329,reader_21329,lend_21329
where book_21329.btitle like '%数据库%' and book_21329.bno=lend_21329.bno and reader_21329.RNO=lend_21329.RNO
order by RNAME ASC ,RNO desc;
grant create view to U_21329; /*授权创建视图*/
create VIEW count_21329
AS
select count(lend_21329.bno) as c_count,lend_21329.RNO as c_RNO
from lend_21329
group by lend_21329.RNO;
select c_count as 本数,count(c_RNO) 人数
from count_21329
group by c_count;
select reader_21329.RNO,RNAME,book_21329.bno,btitle,BORROWDATE from book_21329,reader_21329,lend_21329
where book_21329.bno=lend_21329.bno and reader_21329.RNO=lend_21329.RNO and reader_21329.RNAME='李正四'; alter table lend_21329
add isoverdue char(2) check(isoverdue in('是','否'));
update lend_21329
set isoverdue='是'
where realRetDate>RETURNDATE;
update lend_21329
set isoverdue='否'
where realretdate select * from book_21329 where publisher='中南大学出版社'; select * from lend_21329 where bno in (select bno from book_21329 where publisher='中南大学出版社'); delete from lend_21329 where bno in (select bno from book_21329 where publisher='中南大学出版社'); delete from book_21329 where publisher='中南大学出版社'; select * from book_21329 where publisher='中南大学出版社'; select * from lend_21329 where bno in (select bno from book_21329 where publisher='中南大学出版社'); 实验三、视图及权限控制 一、目的与要求 本实验主要是熟悉在SQL*PLUS环境下进行数据视图及权限控制的操作。 二、操作环境 同实验一。 三、实验内容（综合设计与实现） 批处理实现为每个读者建立用户，并授予connect角色，建立视图并给相应查询权限，让每个员工用自己的用户登录能查询到自己的整个信息与相应的借书信息。 四、实验报告要求 1．实验内容 2．调试后的各命令清单 3．写出在实验过程中遇到的问题及解决方法 4．要求字迹端正、条理清晰、概念正确 五、实验源代码 grant create user to U_21329; grant connect,resource to U_21329 with admin option; select 'create user u_'||substr(reader_21329.RNO,7,4)||' identified by p'||substr(reader_21329.RNO,7,4)||';' from reader_21329; create user u_1329 identified by p1329; create user u_1328 identified by p1328; create user u_1327 identified by p1327; create user u_1326 identified by p1326; create user u_1325 identified by p1325; create user u_1324 identified by p1324; create user u_1323 identified by p1323; create user u_1322 identified by p1322; create user u_1321 identified by p1321; create user u_1320 identified by p1320; create user u_1319 identified by p1319; select 'grant connect,resource to u_'||substr(reader_21329.RNO,7,4)||';' from reader_21329; grant connect,resource to u_1329; grant connect,resource to u_1328; grant connect,resource to u_1327; grant connect,resource to u_1326; grant connect,resource to u_1325; grant connect,resource to u_1324; grant connect,resource to u_1323; grant connect,resource to u_1322; grant connect,resource to u_1321; grant connect,resource to u_1320; grant connect,resource to u_1319; create view v_reader as select reader_21329.RNO,reader_21329.RNAME,book_21329.bno,book_21329.btitle,lend_21329.B ORROWDATE from book_21329,reader_21329,lend_21329 where substr(reader_21329.RNO,7,4)=substr(user,3,4) and lend_21329.RNO=reader_21329.RNO and book_21329.bno=lend_21329.bno; select 'grant select on v_reader to u_'||substr(reader_21329.RNO,7,4)||';'from reader_21329; grant select on v_reader to u_1329; grant select on v_reader to u_1328; grant select on v_reader to u_1327; grant select on v_reader to u_1326; grant select on v_reader to u_1325; grant select on v_reader to u_1324; grant select on v_reader to u_1323; grant select on v_reader to u_1322; grant select on v_reader to u_1321; grant select on v_reader to u_1320; grant select on v_reader to u_1319; conn u_1329/p1329; select * from U_21329.v_reader; conn u_1328/p1328; select * from U_21329.v_reader; create table storebook_21329( bno char(10) references U_21329.book_21329(bno), sumnum int, remain int); insert into storebook_21329 values('0119320111','5','3'); insert into storebook_21329 values('0119320112','3','3'); insert into storebook_21329 values('0119320113','3','2'); insert into storebook_21329 values('0119320114','5','3'); insert into storebook_21329 values('0119320115','3','3'); insert into storebook_21329 values('0119320116','5','3'); insert into storebook_21329 values('0119320117','3','1'); insert into storebook_21329 values('0119320118','3','3'); insert into storebook_21329 values('0119320119','5','1'); insert into storebook_21329 values('0119320120','3','2'); insert into storebook_21329 values('0119320121','5','1'); insert into storebook_21329 values('0119320122','5','2'); 实验四、数据库触发器 一、目的与要求 本实验主要是熟悉在SQL*PLUS环境下数据库触发器的建立及作用，了解大型数据库编程。 二、操作环境 同实验一。 三、实验内容 1．先建立表storebook_?????(bno,sumnum /*总数*/,remain/*剩余数*/) 2．建立触发器 满足如下需求：插入删除或修改lend_?????表中的记录时，将表storebook中的remain /*剩余数*/进行相应的处理，如插入时增减少相应的一本，删除时将增加相应的一本等。 如果remail<=0时，提示不能再借书。 4.测试与调试触发器 四、实验报告要求 1．实验内容 2．调试后的程序清单 3．写出在实验过程中遇到的问题及解决方法 4．要求字迹端正、条理清晰、概念正确 五、实验源代码 CREATE or REPLACE TRIGGER tri_del_lend before delete or insert on lend_21329 /*借书表增加或删除时触发*/ FOR EACH ROW /*每行发生变化时触发1次*/ BEGIN if inserting then update storebook_21329 set remain=remain-1 /*每借出一本书时，库存减1*/ where bno=:new.bno; end if; if deleting then update storebook_21329 set remain=remain+1 where bno=:old.bno; end if; end; /*** CREATE or REPLACE TRIGGER tri_del_lend before delete or insert on lend_21329 FOR EACH ROW declare remain_now; remain_now int; BEGIN if inserting then update storebook_21329 set remain=remain-1 where bno=:new.bno; end if; if deleting then select remain into remain_now from storebook_21329 where bno=xxy_bno; if remain_now<=0 then raise_application_error( -20000,' 剩余0本，无法借书'); end if; update storebook_21329 set remain=remain+1 where bno=:old.bno; end if; end; **/ /*触发器2*/ CREATE or REPLACE TRIGGER tri_cal_lend before update ON storebook_21329 FOR EACH ROW BEGIN if :new.remain<0 then raise_application_error( -20000,' 剩余0本，无法借书'); end if; end; insert into lend_21329 /*实例添加数据*/ values('0909101318','0119320122',TO_DATE('2014-03-20','yyyy-MM-dd'),TO_DATE('2014-05-25','yyyy-MM-dd'),TO_DATE('2014-03-30','yyyy-MM-dd')); delete from lend_21329 where RNO='0909101320' and bno='0119320117'; select * from storebook_21329; 实验五、数据库过程 一、目的与要求 本实验主要是熟悉在SQL*PLUS环境下数据库过程的建立及作用，了解大型数据库编程。 二、操作环境 同实验一。 三、实验内容 1．先建立表SumLendBook_?????(rno,sumnum /*总数*/,sumoverdue/*总过期数*/) 2．设计并调试过程 （1）分读者编号计算，sumnum /*总数*/,sumoverdue/*总过期数*/，如建立Pro_sumlend_?????过程，运行时： Pro Sumlend_????(‘0909110112’); 运行结果为：‘0909110112’显示读者的相应统计数值，并存入表SumLendBook中 四、实验报告要求 1．实验内容 2．调试后的程序清单 3．写出在实验过程中遇到的问题及解决方法 4．要求字迹端正、条理清晰、概念正确 五、实验源代码 /*创建sumlendbook表*/ create table SumLendBook_21329( RNO char(10) references U_21329.reader_21329(RNO), sumnum number, sumoverdue number); /*创建过程*/ create or replace procedure Pro_sumlend_21329(s_RNO in varchar) is vin_RNO char(10); v_RNO char(10); v_sumnum number; v_sumoverdue number; begin vin_RNO :='0'||s_RNO; select count(lend_21329.bno) into v_sumnum from lend_21329 where RNO=vin_RNO; select count(lend_21329.bno) into v_sumoverdue from lend_21329 where lend_21329.isoverdue='是' and RNO=vin_RNO; insert into SumLendBook_21329 values(vin_RNO,v_sumnum,v_sumoverdue); DBMS_OUTPUT.put_line('学号0'||vin_RNO||'总借书数'||v_sumnum||'过期数'||v_sumoverdue); /*显示输出*/ exception /*没数据时*/ when no_data_found then DBMS_OUTPUT.put_line('未找到数据！'); end Pro_sumlend_21329; execute Pro_sumlend_21329(0909101329) select reader_21329.RNO,book_21329.bno,lend_21329.BORROWDATE,RETURNdate from lend_21329,book_21329,reader_21329 where reader_21329.RNO(+)=lend_21329.RNO and lend_21329.bno=book_21329.bno order by reader_21329.RNO;