基于数据库技术及应用实验：实验二 多表查询

实验二 多表查询

一、实验目的

掌握两表、三表连接查询；

掌握子查询。

二、实验内容(格式10分，每题4分)

完成下面题目，代码复制到报告中并将结果截图。

1.查询图书名称、主编

select bms_book.bname,bms_book.editor

from  bms_book;

2.查询所有课程的课程名称、教材编号，字段名显示课程名称、教材编号。

select bms_course.cname as 课程名称,bms_book.bno as 教材编号

from bms_course inner join bms_book on bms_book.bno=bms_course.bno;

3.查询C语言课程的课程名称、教材编号，字段名显示课程名称、教材编号。

select bms_course.cname as 课程名称,bms_book.bno as 教材编号

from bms_course inner join bms_book on bms_book.bno=bms_course.bno

where bms_course.cname='C语言';

4.查询李春葆主编的教材，显示教材名称，主编姓名

select bms_book.bname as 教材名称,bms_book.editor as 主编姓名

from bms_book

where bms_book.editor='李春葆';

5.查询有谢娜参与副主编的教材名称，副主编名称

select bms_book.bname as 教材名称,bms_book.assaditor as 副主编姓名

from bms_book

where bms_book.assaditor like '%谢娜%' ;

6.查询bno、bname及planbook，planbook为“是”的显示为“规划教材”，为“否”的显示为“非规划教材”

select bms_book.bno,bms_book.bname,bms_book.planbook,

case bms_book.planbook

when '是' then '规划教材'

when '否' then '非规划教材'

end as book_type

from bms_book

where bms_book.planbook in ('是','否');

7.查询课程名称、教材名称、教材出版时间，并按教材出版时间从近到远排序

select bms_course.cname,bms_book.bname,bms_book.pubtime

from bms_book inner join bms_course on bms_course.bno=bms_book.bno

order by  pubtime desc;

8.查询C语言课程的课程名称、教材名称及出版社名称

select bms_course.cname,bms_book.bname,bms_publish.pname

from bms_course inner join bms_book on bms_book.bno=bms_course.bno inner join bms_publish on bms_book.pno=bms_publish.pno

where bms_course.cname='C语言';

9.统计选用各出版社教材数量

select count(bms_book.bname) as 教材数量

from bms_book;

10.查询规划教材的平均价格，显示规划平均价格。

select avg(bms_book.price) as 平均价格

from bms_book

where planbook = ’是’;

11.查询级别为规划教材中价格最低的教材名称及价格

select bms_book.bname, min(bms_book.price)

from bms_book

where planbook =’是’;

12.查询教材名称、出版社名称、出版社等级、价格，并按出版社等级降序、价格升序排序。

select bms_book.bname,bms_publish.pname, bms_publish.grade, bms_book.price

from bms_book inner join bms_publish on bms_book.pno=bms_publish.pno

order by grade desc, price asc;

13.查看所有课程的课程名称、教材名称及出版社名称，并按出版社分组

select bms_course.cname,bms_book.bname,bms_publish.pname

from bms_course inner join bms_book on bms_book.bno=bms_course.bno inner join bms_publish on bms_book.pno=bms_publish.pno

group by bms_publish.pname;

【显示每组第一条记录】

14.统计每个级别的出版社的数量，显示出版社名称、级别及数量

select  bms_publish.pname,bms_publish.grade,count(*) as 数量

from bms_publish

group by pname, grade;

15.查询主编为李春葆的教材名称、主编、出版社名称、价格

select bms_book.bname,bms_book.editor,bms_publish.pname,bms_book.price

from bms_book inner join bms_publish on bms_book.pno=bms_publish.pno

where bms_book.editor='李春葆';

16.查询选用规划教材并且教材价格在50元以下的课程名称、教材名称、教材级别和教材价格

select bms_course.cname,bms_book.bname,bms_publish.grade,bms_book.price

from bms_course inner join bms_book on bms_book.bno=bms_course.bno inner join bms_publish on bms_book.pno=bms_publish.pno

where planbook =’是’ and  price<50;

17.查询课程选择用教材情况，显示课程名称、教材名称、规划教材

select bms_course.cname,bms_book.bname,bms_book.planbook

from bms_course inner join bms_book on bms_book.bno=bms_course.bno;

18.查询哪些课程未选用规划教材，显示未选用规划教材的课程名、书名、教材级别及出版社名称

select bms_course.cname,bms_book.bname,bms_publish.grade,bms_publish.pname

from bms_course inner join bms_book on bms_book.bno=bms_course.bno inner join bms_publish on bms_book.pno=bms_publish.pno

where bms_book.planbook='否';

19.查询和“数据结构与算法分析”同一出版社的教材，显示教材名称，出版社名称。

select bms_book.bname as 教材名称,bms_publish.pname as 出版社名称

from bms_book inner join bms_publish on bms_book.pno=bms_publish.pno

where bms_publish.pname=(select bms_publish.pname from bms_book inner join bms_publish on bms_book.pno=bms_publish.pno where bname='数据结构与算法分析');

20.查询和深度学习课程教材同一出版社的教材，显示课程名称、教材名称，出版社名称。

select bms_course.cname as '课程名称' , bms_book.bname as '教材名称' , bms_publish.pname as '出版社名称' from bms_course inner join bms_book on bms_course.bno = bms_book.bno inner join bms_publish on bms_book.pno = bms_publish.pno where bms_publish.pname = (select bms_publish.pname as '出版社名称' from bms_course inner join bms_book on bms_course.bno = bms_book.bno inner join bms_publish on bms_book.pno = bms_publish.pno where bms_course.cname = '深度学习');

三、实验中遇到的问题及解决方法、总结（10分，写无或空着得0分）

先来说说代码插入时候的问题吧，在我插入bms代码的时候，系统自动给我报了一个warning，如下图所示：

系统提示我们第一行的pno数据被截断了,接下来我们去bms代码以及bms_publish找一下原因，然后我们发现是数据类型所给的存储空间不够，在bms_publish中，定义pno的类型为char(8),而我们存储的数据也刚好8位，当然这个问题也不影响我们正常插入数据，但是我们也要时刻注意，系统报的每一个错误，接下来我们插入bms_book,以及bms_course的数据时候，系统都会给我们报下面这个错误，并调过了数据中的几条记录，如下图所示：

这个问题在我做实验一的时候已经遇到过了，我们只要把bms_publish,bms_book,bms_course中的数据重新保存为UTF8就可以解决，这样我们插入数据的时候就没有问题了。如图所示：

解决完数据插入的问题后，下面讲一下，我在做此次实验的时候遇到的一些问题，首先第一个问题就是在查询谢娜为副主编的时候，我用的where语句是这样的where bms_book.assaditor like '_谢娜' 当然考虑用到的是模糊匹配，当然想到的是用_代表主编，后面接着谢娜副主编，这样就能查出来了，但实际运行起来，系统提示说查找不到我想要的记录

这时候我回去bms_book简单看了一下，发现按照我上面的思路，根本不行，因为主编和副主编可以有好几个，所以不能用_，后面考虑到了用where bms_book.assaditor like '%谢娜%' ;这个语句，一运行便成功查找出来，再来简单讲一下第19和20题的思路，其实在第一次做的时候，我只能想到把“数据结构与算法分析”这本教材查找出来，但题目要求是查找同意出版社，这时候就遇到难题了，后来学习了一下，发现只要搞个子函数再嵌套进去就可以了首先第一个子函数先查找出同一出版社，后面再嵌套一个查找“数据结构与算法分析”这本教材的代码，把两个函数进行嵌套就可以了，第20题思路也是差不多。