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

实验实验 SQLSQL 语言语言 一 一 实验目的实验目的 1 理解数据库以及数据表的设计 2 熟悉 SQL Server2005 中的数据类型 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 课程编号 Course number 课程名 Course name 先修课 The first course 学分 credit 2 通过 SQL 语句将以下数据表创建在该数据库的默认模式 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 借阅信息表 tb borrow 图书编号 读者编号 借阅日期 Borrowing date 归还日期 Return date 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 图书信息表 图书编号类别编号书名作者出版社定价库存数 100013数据库管理王珊高等教育出版社35 5010 100023软件测试贺平机械工业出版社24 605 100033C 程序设计谭浩强清华大学出版社30 008 100044红楼梦曹雪芹人民文学出版社70 005 100054西游记罗贯中人民文学出版社60 008 100064红与黑司汤达人民文学出版社50 005 100071高等数学李翼清华大学出版社28 004 100088有机化学张翔高等教育出版社29 005 100092大学英语王琳高等教育出版社25 0010 100102英语教程王琳高等教育出版社25 005 3 读者信息表 读者编号姓名性别学号班级所在系 R10001张小航男085110108511计算机系 R10002王文广女085110208511计算机系 R10003李理女085110308511计算机系 R10004李彦宏男085120108512计算机系 R10005张丽霞女085120208512计算机系 R10006王强男072110407211电子系 R10007张宝田男072120407212电子系 R10008宋文霞女076110407611建工系 R10009刘芳菲女088110408811外语系 R10010常江宁男088120408812外语系 4 借阅信息表 图书编号读者编号借阅日期归还日期 10002R100032009 9 202009 10 20 10003R100032009 9 202009 10 20 10004R100032009 9 302009 10 30 10009R100032009 9 302009 10 30 10009R100072009 5 202009 6 20 10010R100072009 5 202009 6 20 10009R100092009 5 302009 6 30 10010R100092009 5 222009 6 22 10002R100092009 5 222009 6 22 10003R100092009 5 302009 6 30 对以上数据表 完成以下操作 1 查询每本图书的所有信息 2 查询每个读者的读者编号 姓名和班级 3 查询每条借阅记录的借阅天数 函数 DATEDIFF 获取两个日期的差 4 查询被借阅过的图书的图书编号 5 查询图书编号为 10006 的书名和作者 6 查询库存数在 5 到 10 本之间的图书的图书编号和书名 7 查询计算机系或电子系姓张的读者信息 8 查询书名包括 英语 的图书信息 9 统计男读者 女读者的人数 10 统计各类图书的类别编号 平均定价以及库存总数 11 统计每本书籍借阅的人数 要求输出图书编号和所借人数 查询结果按人数降序排列 12 查询有库存的各类别图书的类别编号 类别名称和借阅数量 13 查询借阅了 大学英语 一书的读者 输出读者姓名 性别 系部 14 查询每个读者的读者编号 姓名 所借图书编号以及所借阅日期 LEFT OUTER JOIN 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 create database db Library on primary name N db Library data filename E SQL db Library data mdf size 10MB maxsize 300MB filegrowth 10 log on name db Library log filename E SQL db 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 tb booktype 图书类别信息表 Typenumber int Categoryname char 20 go create table tb book 图书信息表 ISBN int Typenumber int Title char 20 Author char 20 BookPublic char 20 BookPrice float Inventorynumber int go create table tb reader 读者信息表 ReaderID char 20 Name char 20 Sex char 2 StudentID int Class char 20 Pastern char 20 go create table tb borrow 借阅信息表 ISBN int ReaderID char 20 Borrowingdate char 20 Returndate char 20 go alter table tb reader drop column Pastern alter table tb reader add column Pastern alter table tb book alter column BookPrice real drop table tb course 创建和删除索引 create index IX S QUANTITY on tb book Inventorynumber create index IX S NAME on tb reader Name asc drop index IX S QUANTITY drop index IX S NAME 图书类别信息表信息 insert into tb booktype values 1 数学 insert into tb booktype values 2 英语 insert into tb booktype values 3 计算机 insert into tb booktype values 4 文学 insert into tb booktype values 5 艺术 insert into tb booktype values 6 电子信息 go 图书信息表信息 insert into tb book values 10001 3 数据库管理 王珊 高等教育出版社 35 50 10 insert into tb book values 10002 3 软件测试 贺平 机械工业出版社 24 60 5 insert into tb book values 10003 3 C 程序设计 谭浩强 清华大学出版社 30 00 8 insert into tb book values 10004 4 红楼梦 曹雪芹 人民文学出版社 70 00 5 insert into tb book values 10005 4 西游记 罗贯中 人民文学出版社 60 00 8 insert into tb book values 10006 4 红与黑 司汤达 人民文学出版社 50 00 5 insert into tb book values 10007 1 高等数学 李翼 清华大学出版社 28 00 4 insert into tb book values 10008 8 有机化学 张翔 高等教育出版社 29 00 5 insert into tb book values 10009 2 大学英语 王琳 高等教育出版社 25 00 10 insert into tb book values 10010 2 英语教程 王琳 高等教育出版社 25 00 5 go 读者信息表信息 insert into tb reader values R10001 张小航 男 0851101 08511 计算机系 insert into tb reader values R10002 王文广 女 0851102 08511 计算机系 insert into tb reader values R10003 李理 女 0851103 08511 计算机系 insert into tb reader values R10004 李彦宏 男 0851201 08512 计算机系 insert into tb reader values R10005 张丽霞 女 0851202 08512 计算机系 insert into tb reader values R10006 王强 男 0721104 07211 电子系 insert into tb reader values R10007 张宝田 男 0721204 07212 电子系 insert into tb reader values R10008 宋文霞 女 0761104 07611 建工系 insert into tb reader values R10009 刘芳菲 女 0881104 08811 外语系 insert into tb reader values R10010 常江宁 男 0881204 08812 外语系 go 借阅信息表信息 insert into tb borrow values 10002 R10003 2009 9 20 2009 10 20 insert into tb borrow values 10003 R10003 2009 9 20 2009 10 20 insert into tb borrow values 10004 R10003 2009 9 30 2009 10 30 insert into tb borrow values 10009 R10003 2009 9 30 2009 10 30 insert into tb borrow values 10009 R10007 2009 5 20 2009 6 20 insert into tb borrow values 10010 R10007 2009 5 20 2009 6 20 insert into tb borrow values 10009 R10009 2009 5 30 2009 6 30 insert into tb borrow values 10010 R10009 2009 5 22 2009 6 22 insert into tb borrow values 10002 R10009 2009 5 22 2009 6 22 insert into tb borrow values 10003 R10009 2009 5 30 2009 6 30 go 1 查询每本图书的所有信息 select from tb book 2 查询每个读者的读者编号 姓名和班级 select ReaderID Name Class from tb reader 3 查询每条借阅记录的借阅天数 select datediff DAY Borrowingdate Returndate from tb borrow 4 查询被借阅过的图书的图书编号 select distinct ISBN from tb borrow 5 查询图书编号为 的书名和作者 select Title Author from tb book where ISBN 10006 6 查询库存数在到本之间的图书的图书编号和书名 SELECT ISBN Title from tb book where Inventorynumber between 5 and 10 7 查询计算机系或电子系姓张的读者信息 select from tb reader where pastern in 计算机系 电子系 8 查询书名包括 英语 的图书信息 select from tb book where Title 英语 9 统计男读者 女读者的人数 select Sex COUNT from tb reader group by Sex 10 统计各类图书的编号 平均定价以及库存总数 select Typenumber AVG BookPrice as 平均定价 SUM Inventorynumber as 库存总数 from tb book group by Typenumber 11 统计每本书籍借阅的人数 要求输出图书编号和所借人数 查询结果按人数降序排列 select ISBN COUNT from tb borrow group by ISBN order by COUNT desc 12 查询有库存的各类别图书的类别编号 类别名称和借阅数量 select tb book Typenumber Categoryname COUNT from tb book tb booktype tb borrow where tb book Typenumber tb booktype Typenumber and tb book ISBN tb borrow ISBN group by tb book Typenumber tb booktype Categoryname 13 查询借阅了 大学英语 一书的读者 输出读者姓名 性别 系部 select Name Sex Pastern from tb reader where ReaderID in select ReaderID from tb borrow where ISBN in select ISBN from tb book where Title 大学英语 14 查询每个读者的读者编号 姓名 所借图书编号及所借阅日期 select tb reader ReaderID Name Borrowingdate from tb reader left outer join tb borrow on tb reader ReaderID tb borrow ReaderID 15 查询现有图书中价格最高的图书 输出书名 作者 定价 select Title Author BookPrice from tb book where BookPrice select MAX BookPrice from tb book 16 查询借阅了 大学英语 但没有借阅 C 程序设计 的读者 输出读者姓名 性别 系部 select Name Sex Pastern from tb reader where Name in select Name from tb borrow where ISBN in select ISBN from tb book where Title 大学英语 and Name not in select Name from tb borrow where ISBN in select ISBN from tb book where Title C 程序设计 17 统计借阅了本以上图书的读者信息 select from tb reader where ReaderID in select ReaderID from tb borrow group by ReaderID having COUNT 2 18 查询计算机系中比其他系所有读者借书数量都多的读者的信息 select from tb reader tbr1 where Pastern 计算机系 and ReaderID in select tb reader ReaderID from tb reader tb borrow where tb reader ReaderID tb borrow ReaderID and tb reader ReaderID tbr1 ReaderID group by tb reader ReaderID having count ISBN any select count ISBN from tb reader tb borrow where tb reader ReaderID tb borrow ReaderID and Pastern 计算机系 group by tb reader ReaderID 19 查询借阅了 大学英语 一书或者借阅了 C 程序设计 一书的读者信息 用集合查询完 成 select from tb reader where ReaderID in select ReaderID from tb borrow tb book where tb borrow ISBN tb book ISBN and Title 大学英语 union select from tb reader where ReaderID in select ReaderID from tb borrow tb book where tb borrow ISBN tb book ISBN and Title C 程序设计 20 查询既借阅了 大学英语 一书又借阅了 C 程序设计 一书的读者信息 用集合查询完成 select from tb reader where ReaderID in select ReaderID from tb borrow tb book where tb borrow ISBN tb book ISBN and Title 大学英语 intersect select from tb reader where ReaderID in select ReaderID from tb borrow tb book where tb borrow ISBN tb book 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 booknew select tb book ISBN tb book Title tb booktype Categoryname from tb book tb booktype where tb book Typenumber