数据库实验二 数据查询

最新推荐文章于 2023-12-06 20:07:34 发布
最新推荐文章于 2023-12-06 20:07:34 发布
阅读量7.7k 收藏 54
点赞数 12
分类专栏： 数据库实验 文章标签： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_40593308/article/details/105560468
版权

一、实验目的
（1）掌握查询语句的一般格式；
（2）熟练掌握单表查询、连接查询、集合查询、统计查询和嵌套查询。
二、实验环境
PLSQL Developer 12
Oracle Database 11 home
三、实验步骤、出现的问题及解决方案
实验步骤：
1、查询“红楼梦”目前可借的各图书编号，及所属版本信息。（是否借出为‘否‘的图书）
SELECT 图书.图书编号,书目.ISBN,书目.书名,书目.作者,书目.出版单位
FROM 书目,图书
WHERE 书目.ISBN=图书.ISBN AND 书目.书名=‘红楼梦’ AND 是否借出=‘否’;
在这里插入图片描述
2、查找高等教育出版社的所有书目及单价，结果按单价降序排序。
SELECT *
FROM 书目
WHERE 出版单位=‘高等教育出版社’
ORDER BY 单价 DESC;
在这里插入图片描述
3、统计“红楼梦”各版的藏书数量（ISBN不同则版本不同）。
SELECT 书目.ISBN,COUNT(书目.ISBN)
FROM 书目,图书
WHERE 书目.ISBN=图书.ISBN AND 书目.书名=‘红楼梦’
GROUP BY 书目.ISBN;
在这里插入图片描述
4、查询学号“20061234”号借书证借阅未还的图书的信息。
SELECT 书目.*
FROM 书目,图书,借阅
WHERE 书目.ISBN=图书.ISBN AND 图书.图书编号=借阅.图书编号 AND 借阅.借书证号=‘20061234’;
在这里插入图片描述
5、查询各个出版社的图书最高单价、平均单价。
SELECT 书目.出版单位,MAX(单价),AVG(单价)
FROM 书目,图书
WHERE 书目.ISBN=图书.ISBN
GROUP BY 书目.出版单位;
在这里插入图片描述
6、要查询借阅了两本和两本以上图书的读者的个人信息。
SELECT 读者.*
FROM 读者
WHERE 读者.借书证号 IN
(
SELECT 借阅.借书证号
FROM 读者,借阅
WHERE 读者.借书证号=借阅.借书证号
GROUP BY 借阅.借书证号
HAVING COUNT()>=2
);
在这里插入图片描述
7、查询“王菲”的单位、所借图书的书名和借阅日期。
SELECT 书目.书名,借阅.借书日期
FROM 书目,图书,借阅
WHERE 书目.ISBN=图书.ISBN AND 图书.图书编号=借阅.图书编号 AND 借阅.借书证号 IN
(
SELECT 读者.借书证号
FROM 读者
WHERE 读者.单位=
(
SELECT 读者.单位
FROM 读者
WHERE 读者.姓名=‘王菲’
)
);
在这里插入图片描述
8、查询每类图书的册数和平均单价。
SELECT 图书分类.类名,COUNT(),AVG(单价)
FROM 图书分类,书目,图书
WHERE 图书分类.图书分类号=书目.图书分类号 AND 书目.ISBN=图书.ISBN
GROUP BY 图书分类.类名;
在这里插入图片描述
9、统计从未借书的读者人数。
SELECT COUNT()
FROM 读者
WHERE 读者.借书证号 NOT IN
(
SELECT 借阅.借书证号
FROM 借阅
);
在这里插入图片描述
10、统计参与借书的人数。
SELECT COUNT()
FROM 读者
WHERE 读者.借书证号 IN
(
SELECT 借阅.借书证号
FROM 借阅
);
在这里插入图片描述
11、找出所有借书未还的读者的信息及所借图书编号及名称。
SELECT 读者.,图书.图书编号,书目.书名
FROM 读者,图书,书目,借阅
WHERE 图书.ISBN=书目.ISBN AND 借阅.图书编号=图书.图书编号 AND 借阅.借书证号=读者.借书证号 AND 借阅.归还日期 IS NULL;
在这里插入图片描述
12、检索书名是以“Internet”开头的所有图书的书名和作者。
SELECT 书目.书名,书目.作者
FROM 书目
WHERE REGEXP_LIKE(书目.书名,’^Internet’);
在这里插入图片描述
13、查询各图书的罚款总数。
SELECT 图书.图书编号,罚款.罚款总数
FROM 图书
LEFT JOIN
(
SELECT 借阅.图书编号,SUM(罚款分类.罚金) AS 罚款总数
FROM 借阅,罚款分类
WHERE 罚款分类.罚款分类号=借阅.罚款分类号
GROUP BY 借阅.图书编号
) 罚款
ON 罚款.图书编号=图书.图书编号;
在这里插入图片描述
14、查询借阅及罚款分类信息，如果有罚款则显示借阅信息及罚款名称、罚金，如果没有罚款则罚款名称、罚金显示空（左外连接）
SELECT 借阅.,罚款分类.罚款名称,罚款分类.罚金
FROM 借阅
LEFT JOIN 罚款分类
ON 罚款分类.罚款分类号=借阅.罚款分类号;
在这里插入图片描述
15、查询借阅了所有“文学”类书目的读者的姓名、单位。
（1）方法一，双重否定表肯定，查询的读者不存在文学类的书没有借过
SELECT 读者.姓名,读者.单位
FROM 读者
WHERE NOT EXISTS
(
SELECT * FROM 图书分类,书目
WHERE 图书分类.图书分类号=书目.图书分类号 AND 图书分类.类名=‘文学’ AND NOT EXISTS
(
SELECT *
FROM 借阅,图书
WHERE 借阅.图书编号=图书.图书编号 AND 借阅.借书证号=读者.借书证号 AND 书目.ISBN=图书.ISBN
)
);
在这里插入图片描述
（2）方法二，查询的读者借的文学类书的数目（去重同一借书证借同一ISBN的文学书后的数目）等于文学类书的数目
SELECT 读者.姓名,读者.单位
FROM 读者
WHERE 读者.借书证号 IN
(
SELECT 读者文学类借书.借书证号
FROM
(
SELECT 文学借书数.借书证号,COUNT() AS 读者文学类数借书数
FROM
(
SELECT 借阅.借书证号,图书.ISBN
FROM 借阅,图书分类,书目,图书
WHERE 借阅.图书编号=图书.图书编号 AND 图书.ISBN=书目.ISBN AND 书目.图书分类号=图书分类.图书分类号 AND 图书分类.类名=‘文学’
GROUP BY (借阅.借书证号,图书.ISBN)
) 文学借书数
GROUP BY 文学借书数.借书证号
) 读者文学类借书,
(
SELECT COUNT() AS 总数
FROM 图书分类,书目
WHERE 图书分类.图书分类号=书目.图书分类号 AND 图书分类.类名=‘文学’
) 文学类书总数
WHERE 读者文学类借书.读者文学类数借书数=文学类书总数.总数
);
在这里插入图片描述
扩展：
1、在书目关系中新增“出版年份”，并在该属性下添加数据。
在这里插入图片描述

ALTER TABLE 书目 ADD 出版年份 VARCHAR(100);
UPDATE 书目 SET 出版年份=‘2005’ WHERE ISBN=‘7040195836’;
UPDATE 书目 SET 出版年份=‘1983’ WHERE ISBN=‘9787508040110’;
UPDATE 书目 SET 出版年份=‘2008’ WHERE ISBN=‘9787506336239’;
UPDATE 书目 SET 出版年份=‘2009’ WHERE ISBN=‘9787010073750’;
COMMIT;在这里插入图片描述
2、求总藏书量、藏书总金额、最高价、最低价。
SELECT COUNT(),SUM(单价),MAX(单价),MIN(单价)
FROM 书目,图书
WHERE 书目.ISBN=图书.ISBN;
在这里插入图片描述
3、列出藏书在5本以上的书目（书名、作者、出版社、出版年份）。
SELECT 书目.书名,书目.作者,书目.出版单位,书目.出版年份
FROM 书目
WHERE 书目.ISBN IN
(
SELECT 书目.ISBN
FROM 书目,图书
WHERE 书目.ISBN=图书.ISBN
GROUP BY 书目.ISBN
HAVING COUNT()>5
);
在这里插入图片描述
4、列出年份最久远的书?
SELECT *
FROM 书目
WHERE 书目.出版年份=
(
SELECT MAX(出版年份)
FROM 书目
);
在这里插入图片描述
5、 目前实际已借出多少册书？
SELECT COUNT()
FROM 图书
WHERE 图书.是否借出=‘是’;
在这里插入图片描述
6.、哪一年的图书最多？
SELECT 年份.出版年份
FROM
(
SELECT 书目.出版年份,COUNT(书目.出版年份) AS NUM
FROM 书目,图书
WHERE 书目.ISBN=图书.ISBN
GROUP BY 书目.出版年份
) 年份,
(
SELECT MAX(COUNT()) AS MAX
FROM 书目,图书
WHERE 书目.ISBN=图书.ISBN
GROUP BY 书目.出版年份
) 最多
WHERE 年份.NUM=最多.MAX;
在这里插入图片描述
7、 哪本借书证未归还的图书最多？
SELECT 统计.借书证号
FROM
(
SELECT MAX(COUNT(借阅.借书证号)) AS MAX
FROM 借阅
WHERE 借阅.归还日期 IS NULL
GROUP BY 借阅.借书证号
) 最多,
(
SELECT 借阅.借书证号,COUNT(借阅.借书证号) AS NUM
FROM 借阅
WHERE 借阅.归还日期 IS NULL
GROUP BY 借阅.借书证号
) 统计
WHERE 统计.NUM=最多.MAX;
在这里插入图片描述
8、平均每本借书证的借书册数。
SELECT SUM(借阅数.借阅_NUM/读者数.读者_NUM)
FROM
(
SELECT COUNT() AS 借阅_NUM
FROM 借阅
) 借阅数,
(
SELECT COUNT() AS 读者_NUM
FROM 读者
) 读者数;
在这里插入图片描述
9、哪个单位的读者平均借书册数最多？
SELECT 单位平均数.单位
FROM
(
SELECT 读者单位人数.单位,SUM(读者单位借书数.借书数/读者单位人数.人数) AS 平均数
FROM
(
SELECT 读者.单位,COUNT() AS 人数
FROM 读者
GROUP BY 读者.单位
) 读者单位人数,
(
SELECT 读者.单位,COUNT() AS 借书数
FROM 读者,借阅
WHERE 读者.借书证号=借阅.借书证号
GROUP BY 读者.单位
) 读者单位借书数
WHERE 读者单位人数.单位=读者单位借书数.单位
GROUP BY 读者单位人数.单位
) 单位平均数,
(
SELECT MAX(SUM(读者单位借书数.借书数/读者单位人数.人数)) AS 借书数
FROM
(
SELECT 读者.单位,COUNT() AS 人数
FROM 读者
GROUP BY 读者.单位
) 读者单位人数,
(
SELECT 读者.单位,COUNT() AS 借书数
FROM 读者,借阅
WHERE 读者.借书证号=借阅.借书证号
GROUP BY 读者.单位
) 读者单位借书数
WHERE 读者单位人数.单位=读者单位借书数.单位
GROUP BY 读者单位人数.单位
) 单位最多借书数
WHERE 单位平均数.平均数=单位最多借书数.借书数;
在这里插入图片描述
10、 最近两年都未被借过的书。
SELECT 图书.图书编号
FROM 图书
WHERE 图书.图书编号 NOT IN
(
SELECT 借阅.图书编号
FROM 借阅
WHERE MONTHS_BETWEEN(SYSDATE,借阅.借书日期)/12<=2
);
在这里插入图片描述
11、今年未借过书的借书证。
SELECT 读者.借书证号
FROM 读者
WHERE 读者.借书证号 NOT IN
(
SELECT 借阅.借书证号
FROM 借阅
WHERE 借阅.借书日期>
(
SELECT TRUNC(SYSDATE,‘YYYY’) FROM DUAL
)
);
在这里插入图片描述
出现的问题及解决方案:
1、无法调用派生表及聚集函数的列，解决方案：使用派生表中的列，需要给派生表取别名，才能在WHERE语句中调用，调用方法：别名.列名，如果子查询结果中使用了聚集函数，需要给聚集函数这列取别名，才能调用，调用时使用别名。表取别名不能用AS。
2、对15题，查询借阅了所有“文学”类书目的读者的姓名、单位，产生了错误的理解，题目的意思是查询到的读者要借阅了所有的文学类书，而不是借阅了文学类的书的读者有哪些，解决方案：
（1）方法一，双重否定表肯定，查询的读者不存在文学类的书没有借过
SELECT 读者.姓名,读者.单位
FROM 读者
WHERE NOT EXISTS
(
SELECT *
FROM 图书分类,书目
WHERE 图书分类.图书分类号=书目.图书分类号 AND 图书分类.类名=‘文学’ AND NOT EXISTS
(
SELECT *
FROM 借阅,图书
WHERE 借阅.图书编号=图书.图书编号 AND 借阅.借书证号=读者.借书证号 AND 书目.ISBN=图书.ISBN
)
);
（2）方法二，查询的读者借的文学类书的数目（去重同一借书证借同一ISBN的文学书后的数目）等于文学类书的数目
SELECT 读者.姓名,读者.单位
FROM 读者
WHERE 读者.借书证号 IN
(
SELECT 读者文学类借书.借书证号
FROM
(
SELECT 文学借书数.借书证号,COUNT() AS 读者文学类数借书数
FROM
(
SELECT 借阅.借书证号,图书.ISBN
FROM 借阅,图书分类,书目,图书
WHERE 借阅.图书编号=图书.图书编号 AND 图书.ISBN=书目.ISBN AND 书目.图书分类号=图书分类.图书分类号 AND 图书分类.类名=‘文学’
GROUP BY (借阅.借书证号,图书.ISBN)
) 文学借书数
GROUP BY 文学借书数.借书证号
) 读者文学类借书,
(
SELECT COUNT() AS 总数
FROM 图书分类,书目
WHERE 图书分类.图书分类号=书目.图书分类号 AND 图书分类.类名=‘文学’
) 文学类书总数
WHERE 读者文学类借书.读者文学类数借书数=文学类书总数.总数
);
3、ORACLE不支持使用DATEDIFF()函数计算时间差，解决方案：使用MONTHS_BETWEEN()方法来计算两个日期的月份之差，然后除以12，得到年份之差，就可以查询最近两年都未被借过的书了，MONTHS_BETWEEN()函数中的参数不能颠倒，否则会为负数。
4、怎么获取今年的第一天的日期？解决方案：使用TRUNC（）函数将使用SYSDATE查询的日期从年份截去，就可以获取今年的第一天的日期，SELECT TRUNC(SYSDATE,‘YYYY’) FROM DUAL。
四、实验心得体会
通过本次实验熟练的掌握了查询的基本用法，对查询的嵌套有了更加深刻直观的认识，通过实验练习了一些难度较大的查询，对查询设计的思路更加清晰，知道该如何嵌套使用子查询以及怎样查询更清晰，不过也认识到了自己的不足，如15题，对于相关子查询掌握还不够好，思路不够清晰，理解不够透彻。

孤雪胜悲鸣
关注
专栏目录
数据库上机三 ： 数据查询（二）
01-21
数据库上机（三）：数据查询（二）、数据更新 一、实验目的 1.掌握SQL嵌套查询和集合查询等各种高级查询的设计方法。 2.熟悉数据库的数据更新操作，能够使用SQL语句对数据库进行数据的插入、修改、删除操作。 二、...
mysql library DB实操练习
热门推荐
2301_76916026的博客
06-20 1万+
0001 | 张小东 | 1 | 软件学院 | 有效 | 100005 | 321124 | 0001 | 2020-10-15 | NULL | 借阅 || 0001 | 张小东 | 1 | 软件学院 | 有效 | 100008 | 411111 | 0001 | 2020-09-25 | NULL | 借阅 |
数据库-实验二-数据查询
yangyps
12-05 8558
--查询“红楼梦”目前可借的各图书编号，及所属版本信息 select 图书编号，出版单位 from 书目,图书 where 书目.isbn=图书.isbn  and 书名='红楼梦' and 是否借出='否'; --查找高等教育出版社的所有书目及单价，结果按单价降序排序。 select 书名，单价 from 书目 where 出版单位='高等教育出版社' order by 单价 desc; -...
​数据库原理及应用上机（实验三 SQL数据查询）
VLOKL的博客
05-22 7510
​数据库原理及应用上机（实验三 SQL数据查询）
数据库原理及应用（MySQL版）——实验三 MySQL查询
qq_45280880的博客
11-17 7463
实验三 MySQL查询1、实验目的2、实验内容及要求 1、实验目的 熟悉SELECT语句的语法结构； 熟练掌握组合使用where、group by、having和order by等子句； 熟悉聚合函数的使用； 熟悉子查询、多表查询的使用。 2、实验内容及要求 设有一个图书馆数据库，其中包括三个表，即图书表、读者表和借阅表。三个表的结构和数据如下： 图书表book（书号bno,书名bname,作者author,出版社publish,单价price） 读者表reader（读者号rno,姓名rname,
数据库 练习（一）
weixin_45615602的博客
10-27 839
创建表 create table exam( id int primary key auto_increment, name varchar(20) not null, chinese double, math double, english double ); 插入数据 insert into exam values(null,‘关羽’,85,76,70); insert into exam v...
数据库实验数据定义.zip
11-21
数据库实验数据定义数据库实验数据定义.zip数据库实验数据定义.zip数据库实验数据定义.zip数据库实验数据定义.zip数据库实验数据定义.zip数据库实验数据定义.zip数据库实验数据定义.zip数据库实验数据定义.zip数据库...
mysql数据库实验报告 数据表的操作
07-27
实验报告 - 数据表的操作 本实验旨在深入理解MySQL数据库的基本操作，包括数据库的创建、查看、删除和使用，以及表结构的设计与管理。通过实际操作，我们将掌握如何创建和修改表结构，设置表约束，以及处理表数据的...
数据库实验常规数据查询.doc
06-05
数据库实验常规数据查询 数据库实验常规数据查询是数据库管理系统中的一个重要组件，负责处理用户的查询请求，检索和提取所需的数据。本文档将详细介绍数据库实验常规数据查询的基本概念、实验目的、实验步骤、测试...
MySQL数据查询
最新发布
weixin_66395717的博客
12-06 3782
SELECT语句可以实现对表的选择、投影、及连接操作。通常生成一个临时表。
数据库基本语言操作实验
代码小生
10-01 7710
二、 实验内容 （一）创建数据库和模式 1、通过SQL语句创建图书信息管理数据库，命名为“db_Library”，数据文件和日志文件放在 D盘下以自己学号和姓名命名的文件夹中，数据文件的逻辑名为 db_Library_data，数据文件的物理名为 db_Library_data.mdf，文件初始大小为 10MB，最大可增加至 300MB，增幅为 10%；日志文件的逻辑名为 db_Library_log，日志文件的物理名为 db_Library_data.ldf，文件初始大小为 5MB，...
SQL语句基础3：查询操作
wzy88996的博客
10-28 7602
接SQL语句基础1，2 1.找出姓李的读者姓名和所在单位 use bookManager select readerName,readerDepartment from reader where readerName like '李%' 2.列出图书库中所有藏书的书名及出版单位 use bookManager select distinct bookName,bookPublish fr...
数据库实验三 数据查询二
Junds0的博客
11-16 2757
统计每本书借阅的次数,显示书名和借阅次数（借阅次数命名为jycs),按借阅次数降序排列,借阅次数相同的按书名降序排列。统计借阅次数在2次以上的图书的借阅的次数,显示书名和借阅次数，按借阅次数降序排列,借阅次数相同的按书名降序排列。统计每个出版社的图书的借阅次数,显示出版社的名称和借阅次数，按借阅次数降序排列,借阅次数相同的按出版社降序排列。统计研究生读者借阅的次数,显示姓名和借阅次数，按借阅次数降序排列，借阅次数相同的按姓名降序排列。查询与孙思旺借过相同图书的读者的读者证号和姓名，按读者证号升序排列。
Django聚合查询和分组查询
ccc369639963的博客
06-16 848
聚合查询是指对一个数据表（Model）中某个字段的数据进行部分或者全部统计查询的一种方式，比如所有全部书的平均价格或者是书籍的总数量等等，在这些时候就会使用到聚合查询这种方法。而分组查询同样也属于聚合查询中的一种，只是更加复杂一点而已，在学习本节的知识时候，如果你有较好的 MySQL 知识储备，那么学习本节知识将会变得再简单不过了。下面就让我们开始学习吧。1) 不带分组的聚合查询对数据表计算统计值，需要使用 aggregate 方法，提供的参数可以是一个或者多个聚合函数，aggregate 是 QueryS
数据库的查询和视图-实验
m0_66402939的博客
04-13 1070
创建一个视图 users_view，用来查询user_id、user_name、Dep_name、Book_out_time、book_id、book_name、class_name、con_name。定义一个游标user_cur ,用来查询user_id、user_name、Dep_name、Book_out_time、book_id、book_name、class_name、con_name。--图书books(图书编号，图书名称，图书类别，存放货柜，图书照片，出版社，作者，简介，单价，数量)
10-164 查询图书表中全部图书的最高售价、最低售价和平均售价
dqd66的博客
10-05 7625
分数 3全屏浏览题目切换布局作者 刘冬霞单位 山东理工大学本题目要求编写SQL语句，查询图书表中全部图书的最高售价、最低售价和平均售价。提示：请使用SELECT语句作答。
MYSQL 数据查询.多表查询及统计分组
m0_62428181的博客
10-11 5792
sql语句执行顺序，外连接，简单实例
数据库实验2：数据查询与统计分析
本实验文档主要涉及数据库操作实践中的数据查询任务，涵盖了多个关键知识点。以下是详细的内容解读： 1. **查询特定年级学生信息**： - 使用`SELECT`语句查询学生表(`STUDENTS`)中年级为'1'的学生名称，结果按照...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值