实验名称:实验四:数据查询(2)
实验目的:
1.要求学生了解如何进行交叉连接查询;
2.要求学生了解如何进行内连接查询;
3.要求学生掌握外连接查询;
4.要求学生掌握复合条件连接查询;
5.要求学生掌握子查询。
实验内容:根据“网上书店”(或者“WSSD”)数据库实现以下操作:
1.连接查询和子查询
(1)输出所有图书的图书名称、价格以及所属类别名称;
(2)对category表和book表进行交叉连接;
(3)输出订购了《平凡的世界》的会员昵称、联系电话、订购数量;
(4)输出订购了图书的会员昵称和联系电话;
(5)输出无人订购的图书名称和价格;
(6)输出详细订购信息,包括订购图书的会员昵称、联系电话、所订图书名称、数量、价格、折扣价;
实验名称:实验四:数据查询(2)
实验代码:
连接查询和子查询
(1)select b.cid,price,bname,cname,c.cid
from book as b join category as c
where b.cid=c.cid;
(2)select * from category cross join book;
或者
select distinct * from book
as a cross join category as b on a.cid=b.cid;
(3)select uname as 会员昵称,tnum as 联系电话,ordernum as 订购数量
from book as b join b_order as o
on b.bid=o.bid join user as u on o.uid=u.uid
where bname=’平凡的世界’;
或者
select uname as 会员昵称,tnum as 联系电话,ordernum as 订购数量
from user as a cross join b_order as o
on a.uid=o.uid
where bid=any(select bid from book where bname=’平凡的世界’);
(4)select distinct uname as 会员昵称,tnum as 联系电话
from b_order as o
cross join user as u
on o.uid=u.uid;
(5)select bname as 无人订购的图书名称,price as 价格
from book
where bid not in (select distinct bid from b_order);
(6)select distinct uname as 会员昵称,tnum as 联系电话,bname as 所订图书名称,ordernum as 数量,price as 价格,distinct as 折扣价
from book as b join b_order as o
on b.bid=o.bid join user as u on o.uid=u.uid
where u.uid=o.uid;
子查询
查询用户编号为1001的用户订购的图书的书名。
(1)select bname
from book
where bid in (select bid
from b_order
where uid='1001');
(2)查询与《心灵鸡汤》同一个类型的图书的书名和作者(要求用子查询完成);
select bname,author
from book
where cid=(select cid
from book
where bname='心灵鸡汤');
(3)查询无人订购的图书名称和价格。
select bid,bname
from book
where bid not in(select distinct bid
from b_order);
(4)查询所有订购了5号图书的用户姓名(分别用带有any和带有exists谓词的子查询完成)。
带有any谓词的子查询:
SELECT uname
FROM user
WHERE uid=any
(SELECT uid
FROM b_order
WHERE uid=user.uid AND bid= '5');
带有exists谓词的子查询:
SELECT uname
FROM user
WHERE EXISTS
(SELECT *
FROM b_order
WHERE uid=user.uid AND bid