数据库图书信息管理数据库SQL语句.docx
- 文档编号:27894545
- 上传时间:2023-07-06
- 格式:DOCX
- 页数:18
- 大小:23.17KB
数据库图书信息管理数据库SQL语句.docx
《数据库图书信息管理数据库SQL语句.docx》由会员分享,可在线阅读,更多相关《数据库图书信息管理数据库SQL语句.docx(18页珍藏版)》请在冰豆网上搜索。
数据库图书信息管理数据库SQL语句
实验SQL语言
一、实验目的
1、理解数据库以及数据表的设计;
2、熟悉SQLServer2005中的数据类型;
3、熟悉使用SQL语句创建和删除模式和索引;
4、掌握使用SQL语句创建、修改和删除数据表;
5、掌握使用SQL语句查询表中的数据;
6、掌握使用SQL语句插入、修改和删除数据表中的数据;
7、掌握使用SQL语句创建、删除、查询和更新视图。
二、实验内容
(一)创建数据库和模式
1、通过SQL语句创建图书信息管理数据库,命名为“db_Library”,数据文件和日志文件放在D盘下以自己学号和姓名命名的文件夹中,数据文件的逻辑名为db_Library_data,数据文件的操作系统名为db_Library_data.mdf,文件初始大小为10MB,最大可增加至300MB,增幅为10%;日志文件的逻辑名为db_Library_log,日志文件的操作系统名为db_Library_data.ldf,文件初始大小为5MB,最大可增加至200MB,增幅为2MB。
2、通过SQL语句在该数据库中创建模式L-C。
(二)创建和管理数据表
要求为各数据表的字段选择合适的数据类型及名称;为各数据表设置相应的完整性约束条件。
1、通过SQL语句将以下数据表创建在L-C模式下:
课程信息表(tb_course)——课程编号Coursenumber、课程名Coursename、先修课Thefirstcourse、学分credit
2、通过SQL语句将以下数据表创建在该数据库的默认模式dbo下:
图书类别信息表(tb_booktype)——类别编号Typenumber、类别名称Categoryname
图书信息表(tb_book)——图书编号ISBN、类别编号Typenumber、书名title、作者author、出版社BookPublic、定价BookPrice、库存数Inventorynumber
读者信息表(tb_reader)——读者编号ReaderID、姓名、性别、学号StudentID、班级、系部pastern
借阅信息表(tb_borrow)——图书编号、读者编号、借阅日期Borrowingdate、归还日期Returndate
3、通过SQL语句对读者信息表进行修改:
删除系部字段、添加所在系字段。
4、通过SQL语句对图书信息表进行修改:
将定价的数据类型改为REAL。
5、通过SQL语句删除课程信息表。
(三)创建和删除索引
1、使用SQL语句在图书信息表上创建一个非聚簇索引IX_S_QUANTITY,要求按照该表中库存数字段的降序创建。
2、使用SQL语句在读者信息表上创建一个唯一的非聚簇索引IX_S_NAME,要求按照该表中的姓名字段的升序创建。
3、使用SQL语句删除之前创建的两个索引。
(四)数据库及数据表设计
根据周围的实际应用情况,自选一个小型的数据库应用项目进行研究,完成该系统的设计。
要求2人一组,通过需求分析,列出系统的主要功能,并完成该系统数据库的逻辑结构设计。
例如可选择学籍管理系统、企业进销存管理系统、人事管理系统或在线考试系统等。
(五)数据查询
通过SSMS向各数据表中添加以下记录。
(1)图书类别信息表
类别编号
类别名称
类别编号
类别名称
类别编号
类别名称
1
数学
4
文学
7
建筑
2
英语
5
艺术
8
化学
3
计算机
6
电子信息
9
物理
(2)图书信息表
图书编号
类别编号
书名
作者
出版社
定价
库存数
10001
3
数据库管理
王珊
高等教育出版社
35.50
10
10002
3
软件测试
贺平
机械工业出版社
24.60
5
10003
3
C++程序设计
谭浩强
清华大学出版社
30.00
8
10004
4
红楼梦
曹雪芹
人民文学出版社
70.00
5
10005
4
西游记
罗贯中
人民文学出版社
60.00
8
10006
4
红与黑
司汤达
人民文学出版社
50.00
5
10007
1
高等数学
李翼
清华大学出版社
28.00
4
10008
8
有机化学
张翔
高等教育出版社
29.00
5
10009
2
大学英语
王琳
高等教育出版社
25.00
10
10010
2
英语教程
王琳
高等教育出版社
25.00
5
(3)读者信息表
读者编号
姓名
性别
学号
班级
所在系
R10001
张小航
男
0851101
08511
计算机系
R10002
王文广
女
0851102
08511
计算机系
R10003
李理
女
0851103
08511
计算机系
R10004
李彦宏
男
0851201
08512
计算机系
R10005
张丽霞
女
0851202
08512
计算机系
R10006
王强
男
0721104
07211
电子系
R10007
张宝田
男
0721204
07212
电子系
R10008
宋文霞
女
0761104
07611
建工系
R10009
刘芳菲
女
0881104
08811
外语系
R10010
常江宁
男
0881204
08812
外语系
(4)借阅信息表
图书编号
读者编号
借阅日期
归还日期
10002
R10003
2009-9-20
2009-10-20
10003
R10003
2009-9-20
2009-10-20
10004
R10003
2009-9-30
2009-10-30
10009
R10003
2009-9-30
2009-10-30
10009
R10007
2009-5-20
2009-6-20
10010
R10007
2009-5-20
2009-6-20
10009
R10009
2009-5-30
2009-6-30
10010
R10009
2009-5-22
2009-6-22
10002
R10009
2009-5-22
2009-6-22
10003
R10009
2009-5-30
2009-6-30
对以上数据表,完成以下操作:
(1)查询每本图书的所有信息;
(2)查询每个读者的读者编号、姓名和班级;
(3)查询每条借阅记录的借阅天数(函数DATEDIFF获取两个日期的差);
(4)查询被借阅过的图书的图书编号;
(5)查询图书编号为“10006”的书名和作者;
(6)查询库存数在5到10本之间的图书的图书编号和书名;
(7)查询计算机系或电子系姓张的读者信息;
(8)查询书名包括“英语”的图书信息;
(9)统计男读者、女读者的人数;
(10)统计各类图书的类别编号、平均定价以及库存总数;
(11)统计每本书籍借阅的人数,要求输出图书编号和所借人数,查询结果按人数降序排列;
(12)查询有库存的各类别图书的类别编号、类别名称和借阅数量;
(13)查询借阅了“大学英语”一书的读者,输出读者姓名、性别、系部;
(14)查询每个读者的读者编号、姓名、所借图书编号以及所借阅日期;(LEFTOUTERJOIN)
(15)查询现有图书中价格最高的图书,输出书名、作者、定价;
(16)查询借阅了“大学英语”但没有借阅“C++程序设计”的读者,输出读者姓名、性别、系部;
(17)统计借阅了2本以上图书的读者信息;
(18)查询借阅了“大学英语”一书或者借阅了“C++程序设计”一书的读者信息;(用集合查询完成)
(19)查询既借阅了“大学英语”一书又借阅了“C++程序设计”一书的读者信息;(用集合查询完成)
(20)查询计算机系中比其他系所有读者借书数量都多的读者的信息;
(21)在读者信息表中插入一条新的记录(读者编号:
R10011;姓名:
张三;所在系:
电子系);
(22)定义一个表tb_booknew,包含图书编号、书名和类别名称字段,要求将类编编号为“3”的图书的图书编号、书名和类别名称插入到tb_bknew表中;
(23)将类别编号为“3”的所有图书的库存数增加5;
(24)将“C++程序设计”这本书的归还日期增加一个月(函数DATEADD)。
(25)删除姓名为“张三”的读者的信息;
(26)删除tb_bknew表中的所有数据;
(27)创建一个名为“读者借阅信息_VIEW”的视图,要求显示计算机系所有读者的借阅信息,包括读者编号、姓名、所在系、图书编号、书名和借阅日期等字段,更新该视图时要保证只有计算机系的读者借阅信息;
(28)创建一个名为“图示借阅信息_VIEW”的视图,要求显示图书的借阅情况,包括图书编号、书名、库存数、借阅次数字段;
(29)查询借阅次数大于2的图书的图书编号、书名、库存数和借阅次数;
(30)删除“图示借阅信息_VIEW”视图。
三、实验步骤
use[master]
go
createdatabasedb_Library
onprimary
(
name=N'db_Library_data',
filename='E:
\SQL\db_Library_data.mdf',
size=10MB,
maxsize=300MB,
filegrowth=10%
)
logon
(
name='db_Library_log',
filename='E:
\SQL\db_Library_data.ldf',
size=5MB,
maxsize=200MB,
filegrowth=2MB
)
go
usedb_Library
go
createschema"L-C"authorizationguest
createtabletb_course--课程信息表
(Coursenumberint,
Coursenamechar(20),
Thefirstcoursechar(20),
Creditint)
go
createtabletb_booktype--图书类别信息表
(Typenumberint,
Categorynamechar(20)
)
go
createtabletb_book--图书信息表
(ISBNint,
Typenumberint,
Titlechar(20),
Authorchar(20),
BookPublicchar(20),
BookPricefloat,
Inventorynumberint)
go
createtabletb_reader--读者信息表
(
ReaderIDchar(20),
Namechar(20),
Sexchar
(2),
StudentIDint,
Classchar(20),
Pasternchar(20)
)
go
createtabletb_borrow--借阅信息表
(
ISBNint,
ReaderIDchar(20),
Borrowingdatechar(20),
Returndatechar(20))
go
altertabletb_readerdropcolumnPastern
altertabletb_readeraddcolumnPastern
altertabletb_bookaltercolumnBookPricereal
droptabletb_course
--创建和删除索引
createindexIX_S_QUANTITYontb_book(Inventorynumber)
createindexIX_S_NAMEontb_reader(Nameasc)
dropindexIX_S_QUANTITY
dropindexIX_S_NAME--图书类别信息表信息
insertintotb_booktypevalues(1,'数学')
insertintotb_booktypevalues(2,'英语')
insertintotb_booktypevalues(3,'计算机')
insertintotb_booktypevalues(4,'文学')
insertintotb_booktypevalues(5,'艺术')
insertintotb_booktypevalues(6,'电子信息')
go
--图书信息表信息
insertintotb_bookvalues(10001,3,'数据库管理','王珊','高等教育出版社',35.50,10)
insertintotb_bookvalues(10002,3,'软件测试','贺平','机械工业出版社',24.60,5)
insertintotb_bookvalues(10003,3,'C++程序设计','谭浩强','清华大学出版社',30.00,8)
insertintotb_bookvalues(10004,4,'红楼梦','曹雪芹','人民文学出版社',70.00,5)
insertintotb_bookvalues(10005,4,'西游记','罗贯中','人民文学出版社',60.00,8)
insertintotb_bookvalues(10006,4,'红与黑','司汤达','人民文学出版社',50.00,5)
insertintotb_bookvalues(10007,1,'高等数学','李翼','清华大学出版社',28.00,4)
insertintotb_bookvalues(10008,8,'有机化学','张翔','高等教育出版社',29.00,5)
insertintotb_bookvalues(10009,2,'大学英语','王琳','高等教育出版社',25.00,10)
insertintotb_bookvalues(10010,2,'英语教程','王琳','高等教育出版社',25.00,5)
go
--读者信息表信息
insertintotb_readervalues('R10001','张小航','男',0851101,'08511','计算机系')
insertintotb_readervalues('R10002','王文广','女',0851102,'08511','计算机系')
insertintotb_readervalues('R10003','李理','女',0851103,'08511','计算机系')
insertintotb_readervalues('R10004','李彦宏','男',0851201,'08512','计算机系')
insertintotb_readervalues('R10005','张丽霞','女',0851202,'08512','计算机系')
insertintotb_readervalues('R10006','王强','男',0721104,'07211','电子系')
insertintotb_readervalues('R10007','张宝田','男',0721204,'07212','电子系')
insertintotb_readervalues('R10008','宋文霞','女',0761104,'07611','建工系')
insertintotb_readervalues('R10009','刘芳菲','女',0881104,'08811','外语系')
insertintotb_readervalues('R10010','常江宁','男',0881204,'08812','外语系')
go
--借阅信息表信息
insertintotb_borrowvalues(10002,'R10003','2009-9-20','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')
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_borrowvalues(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*
fromtb_book
--
(2)查询每个读者的读者编号、姓名和班级;
selectReaderID,Name,Class
fromtb_reader
--(3)查询每条借阅记录的借阅天数;
selectdatediff(DAY,Borrowingdate,Returndate)
fromtb_borrow
--(4)查询被借阅过的图书的图书编号;
selectdistinctISBN
fromtb_borrow
--(5)查询图书编号为“”的书名和作者;
selectTitle,Author
fromtb_book
whereISBN='10006'
--(6)查询库存数在到本之间的图书的图书编号和书名;
SELECTISBN,Title
fromtb_book
whereInventorynumberbetween5and10
--(7)查询计算机系或电子系姓张的读者信息;
select*fromtb_reader
wherepasternin('计算机系','电子系')
--(8)查询书名包括“英语”的图书信息;
select*fromtb_book
whereTitle='英语'
--(9)统计男读者、女读者的人数;
selectSex,COUNT(*)fromtb_reader
groupbySex
--(10)统计各类图书的编号、平均定价以及库存总数;
selectTypenumber,AVG(BookPrice)as平均定价,SUM(Inventorynumber)as库存总数
fromtb_book
groupbyTypenumber
--(11)统计每本书籍借阅的人数,要求输出图书编号和所借人数,查询结果按人数降序排列;
selectISBN,COUNT(*)fromtb_borrow
groupbyISBN
orderbyCOUNT(*)desc
--(12)查询有库存的各类别图书的类别编号、类别名称和借阅数量;
selecttb_book.Typenumber,Categoryname,COUNT(*)
fromtb_book,tb_booktype,tb_borrow
wheretb_book.Typenumber=tb_booktype.Typenumberandtb_book.ISBN=tb_borrow.ISBN
groupbytb_book.Typenumber,tb_booktype.Categoryname
--(13)查询借阅了“大学英语”一书的读者,输出读者姓名、性别、系部;
selectName,Sex,Pastern
fromtb_reader
whereReaderIDin
(selectReaderID
fromtb_borrow
whereISBNin
(selectISBNfromtb_bookwhereTitle='大学英语')
)
--(14)查询每个读者的读者编号、姓名、所借图书编号及所借阅日期;
selecttb_reader.ReaderID,Name,Borrowingdate
fromtb_readerleftouterjointb_borrowontb_reader.ReaderID=tb_borrow.ReaderID
--(15)查询现有图书中价格最高的图书,输出书名、作者、定价;
selectTitle,Author,BookPrice
fromtb_book
whereBookPrice=
(selectMAX(BookPrice)
fromtb_book
)
--(16)查询借阅了“大学英语”但没有借阅“C++程序设计”的读者,输出读者姓名、性别、系部
selectName,Sex,Pastern
fromtb_readerwhereNamein(selectName
fromtb_borrowwhereISBNin(selectISBNfromtb_bookwhereTitle='大学英语'))
andNamenotin(selectNamefromtb_borrowwhereISBNin
(selectISBNfromtb_bookwhereTitle='C++程序设计'))
--(17)统计借阅了本以上图书的读者信息;
select*
fromtb_reader
whereReaderIDin
(selectReaderIDfromtb_borrowgroupbyReaderIDhavingCOUNT(*)>2)
--(18)查询计算机系中比其他系所有读者借书数量都多的读者的信息;
select*fromtb_readertbr1
wherePastern='计算机系'andReaderIDin
(selecttb_reader.Re
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 图书 信息管理 SQL 语句
![提示](https://static.bdocx.com/images/bang_tan.gif)