2022年sqlserver图书管理系统 .pdf

《2022年sqlserver图书管理系统 .pdf》由会员分享，可在线阅读，更多相关《2022年sqlserver图书管理系统 .pdf（6页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、dropdatabase book -创建数据库createdatabase book On ( Name=TeachDataFile, Filename=T:DataBaseProjectbook.mdf, size=30MB, Maxsize=100MB , filegrowth=5Mb ) log on ( name =TechLogFile, fileName=T:DataBaseProjectbook_log.ldf, size=30mb , maxsize=50mb , filegrowth=5mb ) -自动收缩alterdatabase book set auto_shrink

2、 on-脱机 | 联机alterdatabase book set offline alterdatabase book set online use book; -创建读者种类信息表createtable readertype( typeno varchar( 4)notnullprimarykey, typename varchar( 20 )notnull, booknumber numericnull, bookday numericnull, userfulllift numericnull, memo text) -创建读者信息表createtable readers( reade

3、rno varchar( 8)notnullprimarykey, readername varchar( 20 )notnull, readersex varchar( 4)notnull, readertype varchar( 4)nullconstraint fk_readertype foreignkey references readertype( typeno), readerdep varchar( 30 )null, address varchar( 40 )null, readertel varchar( 13 )null, Email varchar( 30 )null,

4、 checkdate datetimenull, readermemo varchar( 400 )null) -创建书籍种类信息表createtable booktype( booktypeno varchar( 4)notnullprimarykey, typename varchar( 20 )notnull, keyword textnotnull) -创建书籍信息表createtable books( bookid varchar( 13 )notnullprimarykey, bookname varchar( 30 )notnull, 名师资料总结 - - -精品资料欢迎下载 -

5、 - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 6 页 - - - - - - - - - booktype varchar( 4)notnullconstraint fk_type foreignkeyreferencesbooktype( booktypeno), bookauthor varchar( 8)notnull, bookpub varchar( 30 )null, bookpubdate datetimenull, bookpages numericnull, bookkeyword varcha

6、r( 20 )null, bookindate datetimenull, putup varchar( 2)null, bookmemo text) -创建借阅读信息表createtable borrowinfo( borrowno varchar( 4)notnullprimarykey, readerid varchar( 8)notnullconstraint fk_readerid foreignkeyreferences readers( readerno), readername varchar( 4)notnull, bookid varchar( 13 )nullconstr

7、aint fk_bookid foreignkeyreferencesbooks( bookid), bookname varchar( 30 )null, borrowdate datetimenull, returndate datetimenull, memo text ) - 插入信息use book select* from books insertintobooks( bookid, bookname, booktype, bookauthor, bookpub, bookpubdate, bookpages) values( 0001, J2EE技术内幕 , 0001, Paul

8、, 机械工业出版社 , 2002-7-1, 580 ) insertintobooks( bookid, bookname, booktype, bookauthor, bookpub, bookpubdate, bookpages) values( 0002, oracle数据管理 , 0001, Paul, 中国电子出版社 , 2000-1-1, 655 ) insertintobooks( bookid, bookname, booktype, bookauthor, bookpub, bookpubdate, bookpages) values( 0003, Java解惑 , 0001

9、, Neal, 人民邮电出版社 , 2003-5-1, 283 ) insertintobooks( bookid, bookname, booktype, bookauthor, bookpub, bookpubdate, bookpages) values( 0004, C+ 网络编程 , 0001, Huston, 华中科技大学出版社 , 2004-7-1, 303 ) insertintobooks( bookid, bookname, booktype, bookauthor, bookpub, bookpubdate, bookpages) values( 0005, 西游记 ,

10、0003, 吴承恩 , 北方妇女儿童出版社 , 2006-1-1, 722 ) insertintobooks( bookid, bookname, booktype, bookauthor, bookpub, bookpubdate, bookpages) 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 6 页 - - - - - - - - - values( 0006, 红楼梦 , 0003, 曹雪芹 , 北方妇女儿童出版社 , 2006-1-1, 924 ) in

11、sertintobooks( bookid, bookname, booktype, bookauthor, bookpub, bookpubdate, bookpages) values( 0007, UNIX高级编程 , 0001, Unix, 华中科技大学出版社 , 2001-1-1, 832 ) insertintobooks( bookid, bookname, booktype, bookauthor, bookpub, bookpubdate, bookpages) values( 0008, Linux宝典 , 0001, 于兰 , 电子工业出版社 , 2003-7-1, 54

12、1 ) insertintobooks( bookid, bookname, booktype, bookauthor, bookpub, bookpubdate, bookpages) values( 0009, 设计模式解析 , 0001, Alian, 人民邮电出版社 , 2004-7-1, 288 ) insertintobooks( bookid, bookname, booktype, bookauthor, bookpub, bookpubdate, bookpages) values( 0010, BEA Weblogic大全 , 0001, Mark, 机械工业出版社 , 2

13、005-1-1, 832 ) insertintobooks( bookid, bookname, booktype, bookauthor, bookpub, bookpubdate, bookpages) values( 0011, 带一本书去欧洲 , 0003, 朱自清 , 陕西师范大学出版社 , 2007-7-1, 219 ) insertintobooks( bookid, bookname, booktype, bookauthor, bookpub, bookpubdate, bookpages) values( 0012, 我是鸡汤 , 0003, 连岳 , 华东师范大学出版社

14、 , 2003-12-1, 187 ) - 表booktype插入数据insertinto booktype( booktypeno, typename, keyword) values( 0001, 计算机 , 计算机 ); insertinto booktype( booktypeno, typename, keyword) values( 0002, 人力资源 , 人力资源 ); insertinto booktype( booktypeno, typename, keyword) values( 0003, 文学 , 文学 ); - 表readertype插入数据insertinto

15、readertype values( 0001, 学生 , 10 , 30 , 90 , 学生 ) insertinto readertype values( 0002, 工程师 , 10 , 30 , 100 , 工程师 ) insertinto readertype values( 0003, 教师 , 15 , 50 , 150 , 教师 ) insertinto readertype values( 0004, 人事人员 , 10 , 30 , 90 ,null) insertinto readertype values( 0005, 市场人员 , 10 , 30 , 90 ,null

16、) - 表readers插入数据insertinto readers( readerno, readername, readersex, readertype, readerdep, address, readertel, email, checkdate) values( 0001, 张伟 , 1, 0001, 福州一中 , 福州一中 , 81111111, , 2007-10-1) 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 6 页 - - - - - - - -

17、 - insertinto readers( readerno, readername, readersex, readertype, readerdep, address, readertel, email, checkdate) values( 0002, 于山 , 1, 0002, 福州一中 , 福州一中 , 81111111, , 2007-10-1) insertinto readers( readerno, readername, readersex, readertype, readerdep, address, readertel, email, checkdate) valu

18、es( 0002, 于山 , 1, 0002, 福州一中 , 福州一中 , 83121212, , 2007-10-1) insertinto readers( readerno, readername, readersex, readertype, readerdep, address, readertel, email, checkdate) values( 0003, 秦华 , 0, 0003, 福州一中 , 福州一中 , 89874533, , 2007-9-15) insertintoreaders( readerno, readername, readersex, readerty

19、pe, readerdep, address, readertel, email, checkdate) values( 0004, 陈伟 , 1, 0002, 华为技术 , 深圳坂田 , 87679090, , 2008-1-1) insertinto readers( readerno, readername, readersex, readertype, readerdep, address, readertel, email, checkdate) values( 0005, 方英 , 0, 0004, 中兴通信 , 深圳南山 , 88332323, , 2008-1-1) - 表bo

20、rrowinfo插入数据insertinto borrowinfo values ( 0001, 0001, 张三 , 0001, J2EE技术内幕 , 2007-7-2, 2007-8-2,null) insertinto borrowinfo values ( 0002, 0002, 李四 , 0002, oracle数据管理 , 2007-7-2, 2007-8-2,null) insertinto borrowinfo values ( 0003, 0001, 王四 , 0001, J2EE技术内幕 , 2007-7-2, 2007-8-2,null) insertinto borro

21、winfo values ( 0004, 0003, 刘英 , 0004, C+ 网络编程 , 2007-7-2, 2007-8-2,null) insertinto borrowinfo values ( 0005, 0001, 徐静 , 0001, J2EE技术内幕 , 2007-8-1, 2007-10-1,null) insertinto borrowinfo values ( 0006, 0002, 黄珍 , 0010, BEA Weblogic大全 , 2007-8-1, 2007-10-1,null) insertinto borrowinfo values ( 0007, 00

22、02, 黄磊 , 0009, 设计模式解析 , 2007-8-5, 2007-9-15,null) insertinto borrowinfo values ( 0008, 0002, 陈晨 , 0008, Linux宝典 , 2007-8-5, 2007-9-15,null) - 最后select* from borrowinfo where bookid in ( SELECT bookid FROM Books WHERE ( bookpub = 人民邮电出版社 ) - use book declare count int select count=count(*)from books

23、where bookpub= 机械工业出版社 if count=0 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 6 页 - - - - - - - - - print 没找到机械工业出版社相关图书, 书籍数目为： +cast( count asvarchar) else beginprint 机器工业出版社的图书为： +cast( count asvarchar) end -试图 -借书概率最高的createview view_borrow_book_top asse

24、lect t. bookno as 借书次数 , books .*from books,( select bookname, count(*)as bookno from borrowInfo groupbybookname) as t where books. bookname=t . bookname select* from view_borrow_book_top -对借书人 id 创建索引use book createindex in_readerid on borrowinfo( readerid) -函数返回表use book createprocedure book_borro

25、w( bookname varchar( 30 ) as beginselect* from books where bookid in(select bookid from borrowinfo where readerid in( select readerid from borrowinfo where bookname=bookname ) and bookname bookname end exec book_borrow J2EE技术内幕 use book createtrigger not_delete_table ondatabase for drop_table as pri

26、ntsorry you can not delete table rollback go -dml触发器：防止rud 数据-ddl触发器，防止数据库表结构被修改-事务select* from booktype begintran insert booktype values( 0004, 物理 , 物理 ) iferror 0 or rowcount 1 goto label insert booktype values( 0005, 英语 , 英语 ) 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - -

27、- - - - 第 5 页，共 6 页 - - - - - - - - - iferror 0 or rowcount 1 goto label select* from booktype - 删除最后插入的记录deletefrom booktype where booktypeno in(selectmax( booktypeno) frombooktype) iferror 0 or rowcount 1 begin label: rollbacktran end else committran - 事务结束- 事务结束后的数据select* from booktype -备份和还原bac

28、kupdatabase book todisk=T:DataBaseProject backupdatabase book-差异备份to book 设备with differential restoredatabase book -恢复from book 设备restoredatabase book - 差异恢复from book 设备withfile=1, norecovery - 不再产生新日志go restoredatabase book from book 设备withfile=2 go -授权grantselecton dbo . t_student to book_user exec sp_addloginguanxianfei, 111111, book use book grantselecton dbo . books to guanxianfei /* 服务器角色 -服务器登录名-数据库用户-数据库角色 -架构新建顺序：登录名-数据库 - 架构 -数据库用户*/ 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 6 页 - - - - - - - - -