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

图书管理系统（Oracle）1、创建表空间student_library2、（1）创建表Administrator（2）创建表library（3）创建表student（4）创建表lent_library（5）创建表booktype3、(1)创建表空间student_librarycreatetablespacestudent_librarydatafile'C:\oracle\product\10.2.0\oradata\orcl\student_library'size100Mautoextendon;(2)创建表AdministratorcreatetableAdministrator(AdministratorIDnumber(20)primarykey,AdministratorNamevarchar2(25),sexvarchar2(5),beizhuvarchar2(30),bookIDvarchar2(20),foreignkey(bookID)referenceslibrary(bookID))tablespacestudent_library；向Administrator表插入数据insertintoAdministratorvalues(100421101,'张三','男','',100421)insertintoAdministratorvalues(100421102,'李四','女','',100422)insertintoAdministratorvalues(100421103,'王五','男','',100423)对Administrator表删除数据deletefromAdministratorwhereAdministratorID=100421103——删除Administrator表中管理员编号为‘100421103’的一行数据对Administrator表查询数据select*fromAdministratororderbyAdministratorID——按照管理员的ID号对Administrator表进行排列向Administrator表修改数据updateAdministratorsetAdministratorName=‘王明’whereAdministratorID=100421101——对Administrator表中管理员编号为‘100421101’的姓名改为“王明”updateAdministratorsetsex=‘女’whereAdministratorID=100421103——对Administrator表中管理员编号为‘100421103’的性别改为“女”(3)创建表librarycreatetablelibrary(bookIDnumber(20)primarykey,bookNamevarchar2(20),authorvarchar2(10),pressvarchar2(20),publishDatevarchar2(50),pricenumber(20),leibienumbernumber(20),statevarchar2(8),studentIDnumber(20),foreignkey(studentID)referencesstudent(studentID))tablespacestudent_library;向library表插入数据insertintolibraryvalues(100421,'Oracle数据库管理','马晓玉','清华大学出版社','2011-11-8',40,'013999','',100421203)insertintolibraryvalues(100422,'ASP.NET网站设计','唐慧','南京大学出版社','2011-5-18',32,'165923','',100421205)insertintolibraryvalues(100423,'JavaScript程序设计','徐元','苏州大学出版社','2011-2-12',28,'265894','',100421207)对library表删除数据deletefromlibrarywherebookID=100422——删除library表中书籍编号为‘100422’的一行数据向library表查询数据selectbookID,bookName,author,press,publishDate,pricefromlibraryorderbybookID——查询library表中的bookID,bookName,author,press,publishDate,price的数据根据书籍的编号进行排序向library表修改数据updatelibrarysetauthor=‘张敏’wherebookID=100422——对library表中书籍编号为‘100422’的作者改为“张敏”(4)创建表studentcreatetablestudent(studentIDnumber(20)primarykey,studentNamevarchar2(50),sexvarchar2(2),departmentvarchar2(10),studentPhonenumber(15),borrowIDnumber(20),passwordnumber(10))tablespacestudent_library;向student表插入数据insertintostudentvalues(100421203,'王逸','男','信电系','13913645678','123456','521568')insertintostudentvalues(100421205,'刘娟','女','食品系','18862312345','256302','269756')insertintostudentvalues(100421207,'张一凡','男','园林园艺系','13962369850','687456','305621')对student表删除数据deletefromstudentwherestudentName=‘张一凡’——删除student表中学生姓名为‘张一凡’的一行数据向student表修改数据updatestudentsetdepartment=‘经贸系’wherestudentID=100421205——对student表中学生学号为‘100421205’的系部名称改为“经贸系”(5)创建表lent_librarycreatetablelent_library(lentIDnumber(20)primarykey,bookIDnumber(20),borrowbookIDnumber(20),borrowDatevarchar2(50),borrowStatevarchar2(30),foreignkey(bookID)referenceslibrary(bookID))tablespacestudent_library;向lent_library表插入数据insertintolent_libraryvalues(12345,'100421','1360','2012-3-15','还未归还')insertintolent_libraryvalues(21350,'100422','2035','2012-1-6','已经归还')insertintolent_libraryvalues(13056,'100423','1503','2012-3-22','还未归还')向lent_library表删除数据deletefromlent_librarywherebookID=‘100423’——删除lent_library表中书籍编号为‘100423’的一行数据向lent_library表查询数据selectlentID,bookID,borrowbookID,borrowStatefromlent_librarywherebookID=‘10042%’——查询lent_library表中的lentID,bookID,borrowbookID,borrowState数据根据书籍编号前五位数位‘10042’进行排序(6)创建表booktypecreatetablebooktype(Leibienumbernumber(20)primarykey,booktypevarchar2(20))tablespacestudent_library;向booktype表插入数据insertintobooktypevalues('4630','文学类')insertintobooktypevalues('1623','科普类')insertintobooktypevalues('2412','理工科类')向booktype表删除数据deletefrombooktypewhereleibienumber=‘1623’——删除booktype表中类别编号为‘1623’的一行数据向booktype表修改数据updatebooktypesetbooktype=‘计算机类’whereleibienumber=‘2412’——对booktype表中类别编号为‘2412’的书籍类型改为“计算机类”4、（1）创建索引【1】createindexAdministratorID_indexonAdministrator(AdministratorID)tablespaceusers;—在Administrator表的AdministratorID列创建一个名为Aid_index的索引【2】createbitmapindexstudentName_indexonstudent(studentName)tablespaceusers;—在student表的学生姓名列上创建位图索引（2）创建视图createorreplaceviewview_libraryasselectbookID,bookName,author,press,publishDate,pricefromlibrary;—创建视图view_library，查询library表中的bookID，bookName，author，press，publishDate，price5、（1）创建匿名块setserveroutputondeclareout_textvarchar2(50);beginout_text:='程序块示例';dbms_output.put_line(out_text);exceptionwhenothersthendbms_output.put_line('捕获一个异常');end;（2）创建存储过程createorreplaceprocedureInsertAdministratorasbegininsertintoAdministrator(AdministratorID,administratorName,sex)values(100421105,'李凡','男');exceptionwhendup_val_on_indexthendbms_output.put_line('重复的编号');whenothersthendbms_output.put_line('发生其他错误!');endInsertAdministrator;—创建一个存储过程InsertAdministrator，向Administrator表的AdministratorID，administratorName和sex列分别插入数据100421105,李凡和男三个值。（3）创建函数createorreplacefunctionFactorial(ninnumber)returnnumberasresultnumber:=1;beginforiin1...nloopresult:=result*i;endloop;return(resu