Oracle数据库-图书管理系统实验报告

图书管理系统（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）创建匿名块setserveroutputondeclare out_textvarchar2(50);begin out_text:=程序块示例; dbms_output.put_line(out_text);exception whenothersthen 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 crea