数据库图书信息管理数据库SQL语句

1、实验 SQL 语言实验目的1 、 理解数据库以及数据表的设计；2、 熟悉 SQL Server2005 中的数据类型；3、熟悉使用SQL语句创建和删除模式和索引；4、掌握使用SQL语句创建、修改和删除数据表；5、掌握使用SQL语句查询表中的数据；6、掌握使用SQL语句插入、修改和删除数据表中的数据;7、掌握使用SQL语句创建、删除、查询和更新视图。实验内容(一)创建数据库和模式1、通过SQL语句创建图书信息管理数据库，命名为“ db_Library "，数据文件和日志文件放在D 盘下以自己学号和姓名命名的文件夹中，数据文件的逻辑名为db_Library_data ， 数据文件的操作系

2、统名为db_Library_data.mdf ,文件初始大小为10MB最大可增加至 300MB增巾吊为10%日志文件的逻辑名为db_Library_log ，日志文件的操作系统名为db_Library_data.ldf ，文件初始大小为5MB最大可增加至 200MB增巾高为2MB2、通过SQL语句在该数据库中创建模式L-C。(二)创建和管理数据表要求为各数据表的字段选择合适的数据类型及名称；为各数据表设置相应的完整性约束条件。1、通过SQL语句将以下数据表创建在L-C模式下：课程信息表( tb_course ) 课程编号 Course number 、 课程名 Course name 、 先修

3、课 The firstcourse 、学分 credit2、通过SQL语句将以下数据表创建在t数据库的默认模式dbo下：图书类别信息表(tb_booktype) 类别编号 Type number 、类别名称Category name图书信息表( tb_book ) 图书编号 ISBN 、 类别编号Type number、 书名 title 、 作者 author 、出版社 BookPublic 、定价 BookPrice 、库存数Inventory number读者信息表(tb_reader )读者编号 Reader ID 、姓名、性别、学号Student ID 、班级、系部 pastern借

4、阅信息表( tb_borrow) 图书编号、 读者编号、借阅日期Borrowing date 、 归还日期Returndate3、通过SQL语句对读者信息表进行修改：删除系部字段、添加所在系字段。4、通过SQL语句对图书信息表进行修改：将定价的数据类型改为REAL5、通过SQL语句删除课程信息表。(三)创建和删除索引1、使用SQL语句在图书信息表上创建一个非聚簇索引IX_S_QUANTITY要求按照该表中库存数字段的降序创建。2、使用SQL语句在读者信息表上创建一个唯一的非聚簇索引IX_S_NAME要求按照该表中的姓名字段的升序创建。3、使用SQL语句删除之前创建的两个索引。(四)数据库及数据

5、表设计根据周围的实际应用情况，自选一个小型的数据库应用项目进行研究，完成该系统的设计。要 求2人一组，通过需求分析，列出系统的主要功能，并完成该系统数据库的逻辑结构设计。例如可 选择学籍管理系统、企业进销存管理系统、人事管理系统或在线考试系统等。(五)数据查询通过ssmSo各数据表中添加以下记录。(1)图书类别信息表类别编号类别名称类别编号类别名称类别编号类别名称1数学4乂字7建筑2英语5艺术8化学3计算机6电子信息9物理(2)图书信息表图书编号类别编号书名作者出版社定价库存数100013数据库管理王珊高等教育出版社35.5010100023软件测试贺平机械工业出版社24.605100033C

6、+程序设计谭浩强清华大学出版社30.008100044红楼梦曹雪芹人民文学出版社70.005100054西游记罗贯中人民文学出版社60.008100064红与黑司汤达人民文学出版社50.005100071高等数学李翼清华大学出版社28.004100088有机化学张翔高等教育出版社29.005100092大学英语王琳高等教育出版社25.0010100102英语教程王琳高等教育出版社25.005(3)读者信息表读名编p姓名性别学号班级所在系R10001张小航:男：085110108511计算机系R10002王文J女085110208511计算机系R10003李理女085110308511计算机系R

7、10004李彦宏男085120108512r计算机系R10005张丽霞女085120208512计算机系R10006王强男072110407211电子系R10007张宝田r男1072120407212电子系R10008宋文霞女076110407611建工系R10009刘芳菲女088110408811外语系R10010常江宁男088120408812外语系(4)借阅信息表图书编号读名编p借阅日期归还日期10002R100032009-9-202009-10-2010003R100032009-9-202009-10-2010004R100032009-9-302009-10-3010009R10

8、0032009-9-302009-10-3010009R100072009-5-202009-6-2010010R100072009-5-202009-6-2010009R100092009-5-302009-6-3010010R100092009-5-222009-6-2210002R100092009-5-222009-6-2210003R100092009-5-302009-6-30对以上数据表，完成以下操作：(1)查询每本图书的所有信息；(2)查询每个读者的读者编号、姓名和班级；(3)查询每条借阅记录的借阅天数(函数DATEDIF或取两个日期的差)；(4)查询被借阅过的图书的图书编号；

9、(5)查询图书编号为“ 10006”的书名和作者；(6)查询库存数在 5到10本之间的图书的图书编号和书名；(7)查询计算机系或电子系姓张的读者信息；(8)查询书名包括“英语”的图书信息；(9)统计男读者、女读者的人数；(10)统计各类图书的类别编号、平均定价以及库存总数；(11)统计每本书籍借阅的人数，要求输出图书编号和所借人数，查询结果按人数降序排列；(12)查询有库存的各类别图书的类别编号、类别名称和借阅数量；(13)查询借阅了 “大学英语” 一书的读者，输出读者姓名、性别、系部；(14)查询每个读者的读者编号、姓名、所借图书编号以及所借阅日期；(LEFT OUTER JOIN(15)查

10、询现有图书中价格最高的图书，输出书名、作者、定价；(16)查询借阅了 “大学英语”但没有借阅“C+程序设计”的读者，输出读者姓名、性别、系部；(17)统计借阅了 2本以上图书的读者信息；(18)查询借阅了 “大学英语” 一书或者借阅了 “C+强序设计” 一书的读者信息；(用集合查询完成)(19)查询既借阅了 “大学英语” 一书又借阅了 “C+强序设计” 一书的读者信息；(用集合查询完成)(20)查询计算机系中比其他系所有读者借书数量都多的读者的信息；(21)在读者信息表中插入一条新的记录(读者编号：R10011;姓名：张三；所在系：电子系)；(22)定义一个表tb_booknew,包含图书编号

11、、书名和类别名称字段，要求将类编编号为“3”的图书的图书编号、书名和类别名称插入到tb_bknew表中；(23)将类别编号为“ 3”的所有图书的库存数增加 5;(24)将“C+程序设计”这本书的归还日期增加一个月(函数DATEADR(25)删除姓名为“张三”的读者的信息；( 26)删除tb_bknew 表中的所有数据；(27)创建一个名为“读者借阅信息_VIEW的视图，要求显示计算机系所有读者的借阅信息，包括读者编号、姓名、所在系、图书编号、书名和借阅日期等字段，更新该视图时要保证只有计算 机系的读者借阅信息；( 28) 创建一个名为 “图示借阅信息_VIEW” 的视图，要求显示图书的借阅情况

12、，包括图书编号、书名、库存数、借阅次数字段；( 29)查询借阅次数大于2 的图书的图书编号、书名、库存数和借阅次数；(30)删除“图示借阅信息 _VIEW视图。三、 实验步骤 use master go create database db_Library on primary (name=N'db_Library_data' ,filename ='E:SQLdb_Library_data.mdf' , size =10 MB, maxsize =300MB, filegrowth =10% ) log on (name='db_Library_log

13、' ,filename ='E:SQLdb_Library_data.ldf' , size =5MB, maxsize =200MB, filegrowth =2MB ) go use db_Library go create schema "L-C" authorization guest create table tb_course - 课程信息表 ( Coursenumber int ,Coursename char ( 20),Thefirstcourse char ( 20), Credit int ) go create table t

14、b_booktype - 图书类别信息表 ( Typenumber int ,Categoryname char ( 20)gocreate table tb_book - 图书信息表( ISBN int ,Typenumber int ,Title char ( 20),Author char ( 20 ),BookPublic char ( 20), BookPrice float , Inventorynumber int )gocreate table tb_reader - 读者信息表 (ReaderID char ( 20),Name char ( 20),Sex char ( 2

15、),StudentID int ,Class char ( 20),Pastern char ( 20)gocreate table tb_borrow - 借阅信息表 (ISBN int ,ReaderID char ( 20),Borrowingdatechar ( 20 ),Returndate char ( 20)goaltertabletb_readerdropcolumnPasternaltertabletb_readeraddcolumnPasternalter table tb_book alter column BookPrice real drop table tb_cou

16、rse- 创建和删除索引createindexIX_S_QUANTITYontb_book ( Inventorynumber )createindexIX_S_NAMEon tb_reader ( Name asc)dropindexIX_S_QUANTITYdropindexIX_S_NAME- 图书类别信息表信息insertintotb_booktypevalues( 1, ' 数学 ' )insertintotb_booktypevalues( 2, ' 英语 ' )insertintotb_booktypevalues( 3, ' 计算机

17、9; )insertintotb_booktypevalues( 4, ' 文学 ' )insertintotb_booktypevalues( 5, ' 艺术 ' )insertintotb_booktypevalues( 6, ' 电子信息' )go- 图书信息表信息insertintotb_bookvalues ( 10001, 3,' 数据库管理' , ' 王珊 ' , ' 高等教育出版社' , 35.50 , 10)insertintotb_bookvalues ( 10002, 3,&#

18、39; 软件测试' , ' 贺平 ' , ' 机械工业出版社', 24.60 , 5)insertintotb_bookvalues ( 10003, 3,'C+程序设计，谭浩强，清华大学出版社,30.00 , 8)insertintotb_bookvalues ( 10004, 4,' 红楼梦' , ' 曹雪芹' , ' 人民文学出版社', 70.00 , 5)insertintotb_bookvalues ( 10005, 4,' 西游记' , ' 罗贯中' ,

19、' 人民文学出版社', 60.00 , 8)insertintotb_bookvalues ( 10006, 4,' 红与黑' , ' 司汤达' , ' 人民文学出版社', 50.00 , 5)insertintotb_bookvalues ( 10007, 1,' 高等数学' , ' 李翼 ' , ' 清华大学出版社', 28.00 , 4)insertintotb_bookvalues ( 10008, 8,' 有机化学' , ' 张翔 ' , &

20、#39; 高等教育出版社', 29.00 , 5)insertintotb_bookvalues ( 10009, 2,' 大学英语' , ' 王琳 ' , ' 高等教育出版社', 25.00 , 10)insertintotb_bookvalues ( 10010, 2,' 英语教程' , ' 王琳 ' , ' 高等教育出版社', 25.00 , 5)go- 读者信息表信息insertintotb_readervalues ('R10001', ' 张小航'

21、,' 男 ' ,0851101,'08511' ,' 计算机系' )insertintotb_readervalues ('R10002', ' 王文广', '女 ' , 0851102,'08511', ' 计算机系' )insertintotb_readervalues ('R10003', ' 李理 ' ,'女 ' , 0851103,'08511', ' 计算机系' )insert

22、intotb_readervalues ('R10004', ' 李彦宏', '男 ' , 0851201,'08512', ' 计算机系' )insertintotb_readervalues ('R10005', ' 张丽霞', '女 ' , 0851202,'08512', ' 计算机系' )insertintotb_readervalues ('R10006', ' 王强 ' ,' 男 &

23、#39; , 0721104,'07211', ' 电子系' )insertintotb_readervalues ('R10007', ' 张宝田', ' 男 ' , 0721204,'07212', ' 电子系' )insertintotb_readervalues ('R10008', ' 宋文霞', '女 ' , 0761104,'07611', ' 建工系' )insertintotb_read

24、ervalues ('R10009', ' 刘芳菲', '女 ' , 0881104,'08811', ' 外语系' )insertintotb_readervalues ('R10010', ' 常江宁', '男 ' , 0881204,'08812', ' 外语系' )go- 借阅信息表信息insertintotb_borrowvalues (10002, 'R10003', '2009-9-20',

25、 '2009-10-20')insertintotb_borrowvalues (10003, 'R10003', '2009-9-20', '2009-10-20')insertintotb_borrowvalues (10004, 'R10003', '2009-9-30', '2009-10-30')insertintotb_borrowvalues (10009, 'R10003', '2009-9-30', '2009-10-30&#

26、39;)insertintotb_borrowvalues (10009, 'R10007', '2009-5-20', '2009-6-20')insertintotb_borrowvalues (10010, 'R10007', '2009-5-20', '2009-6-20')insertintotb_borrowvalues (10009, 'R10009', '2009-5-30', '2009-6-30')insertintotb_borr

27、owvalues (10010, 'R10009', '2009-5-22', '2009-6-22')insertintotb_borrowvalues (10002, 'R10009', '2009-5-22', '2009-6-22')insertintotb_borrowvalues (10003, 'R10009', '2009-5-30', '2009-6-30')go- -(1) 查询每本图书的所有信息；select *from tb_b

28、ook- - ( 2)查询每个读者的读者编号、姓名和班级；select ReaderID , Name, Classfrom tb_reader- - ( 3)查询每条借阅记录的借阅天数；select datediff ( DAY, Borrowingdate , Returndate ) from tb_borrow- - （ 4）查询被借阅过的图书的图书编号；select distinct ISBN from tb_borrow- - （ 5）查询图书编号为“”的书名和作者；select Title , Authorfrom tb_bookwhere ISBN='10006'

29、;- - （ 6）查询库存数在到本之间的图书的图书编号和书名；SELECTISBN, Title from tb_bookwhere Inventorynumber between 5 and 10- - （ 7）查询计算机系或电子系姓张的读者信息；select * from tb_readerwhere pastern in（ ' 计算机系' , ' 电子系 ' ）- - （ 8）查询书名包括“英语”的图书信息；select * from tb_bookwhere Title = ' 英语 '- - （ 9）统计男读者、女读者的人数；selec

30、t Sex , COUN（T*） from tb_readergroup by Sex- - （ 10）统计各类图书的编号、平均定价以及库存总数；select Typenumber, AVG（ BookPrice ） as 平均定价, SUM（ Inventorynumber ） as 库存总数from tb_book group by Typenumber- - （ 11）统计每本书籍借阅的人数，要求输出图书编号和所借人数，查询结果按人数降序排列；select ISBN , COUN（T*） from tb_borrowgroup by ISBNorder by COUN（T*） desc-

31、 - （ 12）查询有库存的各类别图书的类别编号、类别名称和借阅数量；select tb_book . Typenumber , Categoryname , COUN（T*）from tb_book , tb_booktype , tb_borrowwhere tb_book . Typenumber =tb_booktype . Typenumber and tb_book . ISBN=tb_borrow . ISBN group by tb_book . Typenumber , tb_booktype . Categoryname - （ 13）查询借阅了“大学英语”一书的读者，输出

32、读者姓名、性别、系部；select Name , Sex , Pasternfrom tb_readerwhere ReaderID in （ select ReaderID from tb_borrowwhere ISBN in（ select ISBN from tb_book where Title =' 大学英语' ）- - （ 14）查询每个读者的读者编号、姓名、所借图书编号及所借阅日期；select tb_reader . ReaderID , Name, Borrowingdatefrom tb_reader left outer join tb_borrow o

33、n tb_reader . ReaderID =tb_borrow . ReaderID - ( 15)查询现有图书中价格最高的图书，输出书名、作者、定价；select Title , Author , BookPrice from tb_book where BookPrice = ( select MAX( BookPrice ) from tbbook -（16）查询借阅了 “大学英语”但没有借阅“ C+和序设计”的读者，输出读者姓名、性别、系部 select Name , Sex , PasternNameISBN from tb_book where Title =' 大学英

34、语' )from tb_reader where Name in ( select from tb_borrow where ISBN in ( selectand Name not in ( select Name from tb_borrow where ISBN in(select ISBN from tb_book where Title ='C+程序设计') - （ 17）统计借阅了本以上图书的读者信息； select * from tbreader where ReaderID in ( select ReaderID from tb_borrow grou

35、p by ReaderID having COUN(T*)> 2)- - （ 18）查询计算机系中比其他系所有读者借书数量都多的读者的信息；select * from tb_reader tbr1where Pastern =' 计算机系' and ReaderID in ( select tb_reader . ReaderID from tb_reader , tb_borrowwhere tb_reader . ReaderID =tb_borrow . ReaderID and tb_reader . ReaderID =tbr1 . ReaderIDgroup

36、by tb_reader . ReaderID having count ( ISBN)>any (select count ( ISBN) from tb_reader , tb_borrowwhere tb_reader . ReaderID =tb_borrow . ReaderID and Pastern <>' 计算机系'group by tb_reader . ReaderID ) )- -(19)查询借阅了 “大学英语” 一书或者借阅了 “C+唯序设计” 一书的读者信息；(用集合查询 完成)select * from tbreaderwhere

37、ReaderID in tb_borrow . ISBN=tb_book . ISBN union select * from tb_reader where ReaderID in tb_borrow . ISBN=tb_book . ISBN( selectReaderIDfromand Title =' 大学英语' )( selectReaderIDfromand Title ='C+ 程序设计' )tb_borrow , tb_book wheretb_borrow , tb_book where- -20 ）查询既借阅了 “大学英语” 一书又借阅了 “

38、 C+和序设计” 一书的读者信息。（用集合查询完 成）select * from tb_readerwhere ReaderID in ( select ReaderID from tb_borrow , tb_book where tb_borrow . ISBN =tb_book . ISBN and Title =' 大学英语' )intersectselect * from tb_readerwhere ReaderID in ( select ReaderID from tb_borrow , tb_book where tb_borrow . ISBN=tb_boo

39、k . ISBN and Title ='C+ 程序设计' )- -21 )在读者信息表中插入一条新的记录(读者编号： R10011;姓名：张三；所在系：电子系)；insert into tb_reader ( ReaderID , Sex , Pastern ) values ( 'R10011' , ' 张三 ' , ' 电子系 ' )- - ( 22) 假设存在一个表tb_booknew， 包含图书编号、书名和类别名称字段，要求将类编编号为“”的图书的图书编号、书名和类别名称插入到tb_bknew表中；create table tb_booknew( ISBN char ( 10), Title varchar ( 20), Typename varchar ( 20)insert into tb_booknewselect tb_book . ISBN , tb_book . Title , tb_booktype . Categorynamefrom tb_book , tb_booktypewhere tb_book . Typenumber =tb_b