Oracle数据库-图书管理系统实验报告(共16页).doc

精选优质文档-倾情为你奉上图书管理系统（Oracle）1、创建表空间student_library2、（1）创建表Administrator（2）创建表library（3）创建表student（4）创建表lent_library（5）创建表booktype3、(1)创建表空间student_librarycreate tablespace student_library datafile 'C:oracleproduct10.2.0oradataorclstudent_library' size 100M autoextend on;(2)创建表Administratorcreate table Administrator( AdministratorID number(20) primary key, AdministratorName varchar2(25), sex varchar2(5), beizhu varchar2(30), bookID varchar2(20),foreign key(bookID) references library(bookID)tablespace student_library；向Administrator表插入数据insert into Administratorvalues(,'张三','男',' ',)insert into Administratorvalues(,'李四','女',' ',)insert into Administratorvalues(,'王五','男',' ',)对Administrator表删除数据delete from Administrator where AdministratorID=删除Administrator表中管理员编号为的一行数据对Administrator表查询数据select * from Administrator order by AdministratorID按照管理员的ID号对Administrator表进行排列向Administrator表修改数据update Administrator set AdministratorName=王明where AdministratorID=对Administrator表中管理员编号为的姓名改为“王明”update Administrator set sex=女where AdministratorID=对Administrator表中管理员编号为的性别改为“女”(3)创建表librarycreate table library( bookID number(20) primary key, bookName varchar2(20), author varchar2(10), press varchar2(20), publishDate varchar2(50), price number(20), leibienumber number(20), state varchar2(8), studentID number(20), foreign key(studentID) references student(studentID)tablespace student_library;向library表插入数据insert into library values(,'Oracle数据库管理','马晓玉','清华大学出版社','2011-11-8',40,'',' ',)insert into library values(,'ASP.NET网站设计','唐慧','南京大学出版社','2011-5-18',32,'',' ',)insert into library values(,'JavaScript程序设计','徐元','苏州大学出版社','2011-2-12',28,'',' ',)对library表删除数据delete from library where bookID=删除library表中书籍编号为的一行数据向library表查询数据select bookID,bookName,author,press,publishDate,price from library order by bookID查询library表中的bookID,bookName,author,press,publishDate,price的数据根据书籍的编号进行排序向library表修改数据update library set author=张敏where bookID=对library表中书籍编号为的作者改为“张敏”(4)创建表studentcreate table student( studentID number(20) primary key, studentName varchar2(50), sex varchar2(2), department varchar2(10), studentPhone number(15), borrowID number(20), password number(10)tablespace student_library;向student表插入数据insert into studentvalues(,'王逸','男','信电系','','','')insert into studentvalues(,'刘娟','女','食品系','','','')insert into studentvalues(,'张一凡','男','园林园艺系','','','')对student表删除数据delete from student where studentName=张一凡删除student表中学生姓名为张一凡的一行数据向student表修改数据update student set department=经贸系where studentID=对student表中学生学号为的系部名称改为“经贸系”(5)创建表lent_librarycreate table lent_library( lentID number(20) primary key, bookID number(20), borrowbookID number(20), borrowDate varchar2(50), borrowState varchar2(30), foreign key(bookID) references library(bookID)tablespace student_library;向lent_library表插入数据insert into lent_libraryvalues(12345,'','1360','2012-3-15','还未归还')insert into lent_libraryvalues(21350,'','2035','2012-1-6','已经归还')insert into lent_libraryvalues(13056,'','1503','2012-3-22','还未归还')向lent_library表删除数据delete from lent_library where bookID=删除lent_library表中书籍编号为的一行数据向lent_library表查询数据select lentID,bookID,borrowbookID,borrowState from lent_library where bookID=10042%查询lent_library表中的lentID,bookID,borrowbookID,borrowState数据根据书籍编号前五位数位10042进行排序(6)创建表booktypecreate table booktype( Leibienumber number (20) primary key, booktype varchar2 (20)tablespace student_library;向booktype表插入数据insert into booktypevalues('4630','文学类')insert into booktypevalues('1623','科普类')insert into booktypevalues('2412','理工科类')向booktype表删除数据delete from booktype where leibienumber=1623删除booktype表中类别编号为1623的一行数据向booktype表修改数据update booktype set booktype=计算机类where leibienumber=2412对booktype表中类别编号为2412的书籍类型改为“计算机类”4、（1）创建索引【1】create index AdministratorID _index on Administrator (AdministratorID)tablespace users; 在Administrator表的AdministratorID列创建一个名为Aid_index的索引【2】create bitmap index studentName_index on student(studentName)tablespace users;在student表的学生姓名列上创建位图索引（2）创建视图create or replace view view_library as select bookID,bookName,author,press,publishDate,price from library;创建视图view_library，查询library表中的bookID，bookName，author，press，publishDate，price5、（1）创建匿名块set serveroutput ondeclare out_text varchar2(50);begin out_text:='程序块示例' dbms_output.put_line(out_text);exception when others then dbms_output.put_line('捕获一个异常'); end;（2）创建存储过程create or replace procedure InsertAdministrator asbegin insert into Administrator(AdministratorID,administratorName,sex) values(,'李凡','男');exception when dup_val_on_index then dbms_output.put_line('重复的编号'); when others then dbms_output.put_line('发生其他错误!');end InsertAdministrator;创建一个存储过程InsertAdministrator，向Administrator表的AdministratorID，administratorName和sex列分别插入数据,李凡和男三个值。（3）创建函数create or replace function Factorial(n in number) return numberas result number: =1;begin for i in 1.n loop result: =result*i; end loop; return(result);end Factorial;（4）创建触发器create table student_log( studentID number(20), studentName varchar2(50), sex varchar2(2), department varchar2(10);创建一个日志表，记录对student表所做的修改，日志表名为student_logcreate or replace trigger insertlog_trigger after insert on studentbegin insert into student_log values(,'路奇', '女', '园林系');end;在student表上创建一个语句级Insert触发器6、用户权限和角色管理(1) 创建用户user_studentcreate user user_studentidentified by studentdefault tablespace student_library(2) 为用户user_student设置系统权限grant create session to user_student;（3）为用户user_student撤销系统权限 revoke create session from user_student;（4）创建角色user_library create role user_libraryidentified by student(5)为角色user_library授予权限 grant user_library to public;（6）启用角色user_library set role user_libraryidentified by student专心-专注-专业