《实验5 sql语句练习——图书馆数据库答案（2020年10月整理）.pptx》由会员分享，可在线阅读，更多相关《实验5 sql语句练习——图书馆数据库答案（2020年10月整理）.pptx（5页珍藏版）》请在金锄头文库上搜索。
1、实验 5 sql 语句练习图书馆数据库 实验 5sql 语句练习图书馆数据库 实验目的,了解 SQL Server 数据库的逻辑结构和物理结构； 了解表的结构特点； 了解 SQL Server 的基本数据类型； 了解空值概念； 学会在企业管理器中创建数据库和表； 学会使用T-SQL 语句创建数据库和表。 学会使用T-SQL 语句更新数据。 （7）学会使用T-SQL 语句创建多种查询。 实验准备 首先要明确，能够创建数据库的用户必须是系统管理员，或是被授权使用 CREATE DATABASE 语句的用户。 其次创建数据库必须要确定数据库名、数据库大小（最初的大小、最大的大小、是否允许增长及增长方
2、式） 和存储数据库的文件。 然后，确定数据库包含哪些表，以及所包含的各表的结构，还要了解 SQL Server 的常用数据类型，以创建 数据库的表。 此外还要了解两种常用的创建数据库、表的方法，即在企业管理器中创建和使用 T-SQL 的 CREATE DATABASE 语句。 实验内容,假设有 5 本书 设有一图书馆数据库，其中包括 3 个表，即图书表、读者表和借阅表。三个表的结构如图： 图书表结构,1,读者表结构,2,假设有 10 位读者,借阅表结构,用 Sql 语句创建图书馆数据库 Create database Lab05 用 Sql 语句创建上述 3 个表 create table b
3、ook ( bookId char(10)primary key, bookName varchar(50), bookWriter varchar(30), bookPublish varchar(30), bookPrice float ) create table reader ( readerId char(10) primary key, readerName varchar(8)not null, readerSex char(2)not null, readerOfficeTel char(8), readerDepartment varchar(30) ) create tab
4、le 借阅表 ( readerId char(10), bookId char(10), checkOutTime char(8), checkInTime char(8), primary key(readerId,bookId), foreign key (readerId) references reader(readerId), foreign key (bookId) references book(bookId),3,) 基于图书馆数据库的 3 个表，用 sql 语言完成一下操作： 为图书表增加一列“ISBN”，数据类型为 CHAR(10) alter table book add
5、 ISBN char(10) 为刚添加的ISBN 列增加缺省值约束，约束名为 ISBNDEF，缺省值为7111085949 ALTER TABLE book ADD CONSTRAINT ISBNDEF DEFAULT (7111085949) FOR ISBN 删除图书表中ISBN 列增加的缺省值约束 alter table book drop ISBNDEF 删除图书表中新增的ISBN 列 ALTER TABLE book DROP COLUMN ISBN 查询全体图书的图书号、书名、作者、出版社和单价 select bookId,bookName,bookWriter,bookPubli
6、sh,bookPrice from book 查询全体图书的信息，其中单价打 8 折，并设置该列的别名为打折价 select bookId,bookName,bookWriter,bookPublish,(bookPrice*0.8) as 打折价 from book 显示所有借阅者的读者号，并去掉重复行 select distinct readerId from 借阅表 查询所有单价在 2030 元之间的图书信息 select bookId,bookName,bookWriter,bookPublish,bookPrice,ISBN from book where bookPrice bet
7、ween 20 and 30 查询机械工业出版社、科学出版社、人民邮电出版社的图书信息 select bookId,bookName,bookWriter,bookPublish,bookPrice,ISBN from book where bookPublish in(机械工业出版社 , 科学出版社,人民邮电出版社),10）,12）,查询既不是机械工业出版社、人民邮电出版社、也不是科学出版社出版的图书信息 select bookId,bookName,bookWriter,bookPublish,bookPrice,ISBN from book where bookPublish not i
8、n(机械工业出版社 , 科学出版社,人民邮电出版社) 11）查询姓名的第二个字符是建，并且只有 2 个字的读者的读者号及姓名 select readerId,readerName from reader where readerName like _建 查询姓名不是以王、张或李开头的所有读者的读者号及姓名 【方式一】查询出来的结果有问题！ select readerId,readerName from reader where readerName not in (王%,张%,李%) 【方式二】 select readerId,readerName from reader where read
9、erName not in,(,4,select readerName from reader where readerName like王%or readerName like张%or readerName like李% ),13）,查询无归还日期的借阅信息,select,book.bookId,book.bookName,reader.readerId, reader.readerName,借阅表.checkOutTime as 借书时间, 借阅表.checkInTime as 还书时间 from借阅表,book,reader where借阅表.bookId = book.bookId a
10、nd借阅表.readerId = reader.readerId and借阅表.checkInTime isnull,14）,15）,查询机械工业出版社图书的平均价格、最高价、最低价 select avg(bookPrice)as 平均价格,max(bookPrice) as 最高价 ,min(bookPrice) as 最低价 from book where bookPublish = 机械工业出版社图书 查询读者的基本信息及借阅情况 select reader.readerId,reader.readerName ,借阅表.bookId,book.bookName,book.bookPub
11、lish from reader , 借阅表 ,book,where,reader.readerId = 借阅表.readerId and book.bookId = 借阅表.bookId and 借阅表.readerId =1000000007,查询至少借阅过 1 本机械工业出版社出版的图书的读者的读者号、姓名、书名及借阅本数，并按借 阅本书多少降序排列 select r.Rno,Rname,count(borrow.Bno) 借阅册数 from borrow,b,r where b.bno=borrow.bno and press=机械工业出版社 and borrow.rno=r.rno
12、group by r.rno,Rname order by count(borrow.Bno) desc 查询与王小平的办公电话相同的读者的姓名,18）,/*使用“自连接方式”求解 */ select b.readerName,b.readerId,a.readerOfficeTel from reader a, reader b where a.readerName like 王小平and a.readerOfficeTel = b.readerOfficeTel /* 查询所有单价小于平均单价的图书的书号、书名及出版社 select bookId,bookName,bookPublish,
13、bookPrice from book where bookPrice ,( select avg(bookPrice) as averagePrice from book ),19）,查询科学出版社的图书单价比机械工业出版社最高单价还高的图书书名及单价 select bookId,bookName,bookPublish,bookPrice,5,from book where bookPublish like 科学出版社 and bookPrice ( select max(bookPrice) from book where bookPublish = 机械工业出版社 ),20）,查询科学
14、出版社的图书中单价比机械工业出版社最低单价高的图书书名及单价 select bookId,bookName,bookPublish,bookPrice from book wherebookPrice ,( select max(bookPrice) from book where bookPublish = 机械工业出版社 ),21）,and bookPublish like 科学出版社 创建机械工业出版社图书的视图 CREATE VIEW 机械工业出版社 View AS SELECT bookId,bookName,bookPrice FROM book WHERE bookPublish
15、 = 机械工业出版社,创建一个借阅统计视图，名为CountView，包含读者的读者号和总借阅本数 CREATE VIEW CountView AS SELECT readerId as 读者号,count(*) as 总借阅本数 FROM 借阅表 GROUP BY readerId 创建一个借阅统计视图，名为CountView10,包含借阅总本数打于 2 的读者号和总借阅本数 CREATE VIEW 借阅统计视图 View AS SELECT readerId as 读者号,count(*) as 总借阅本数 FROM 借阅表 GROUP BY readerId HAVING COUNT(*) 2,
