mysql数据库知识8--子查询

/*身份证号为’ 210210199901015555’的用户，今天借了一本图书编号为’ 20150301’的图书，完成下列业务需求：
更新读者信息表的余额。
*/

select price from bookinfo where book_id = 20150301;

select * from readerinfo;

update readerinfo set balance = balance - (select price from bookinfo where book_id = 20150301) * 0.05 where card_id = '210210199901015555';

 

-- 1、查询借阅信息表, 显示借 《 Spring源码深度解析》这本书的借阅记录。
select * from borrowinfo where book_id = (select book_id from bookinfo where book_name = 'Spring源码深度解析');

-- 2、查询图书信息表, 显示图书价格小于图书平均价格的所有图书信息。
select * from bookinfo where price < (select round(avg(price),2) from bookinfo);

-- 3、查询图书信息表，显示图书类别不是’数据库’的所有图书信息。
select * from bookinfo where book_category_id<>(select category_id from bookcategory where category = '数据库');

-- 查询图书信息表，显示图书类别为’计算机’的所有图书信息。
select * from bookcategory;

select * from bookinfo where book_category_id = ANY(select category_id from bookcategory where parent_id = 1);

select * from bookinfo where price > ANY (select price from bookinfo where book_category_id =4); -- 35.5 ~ 95 大于子查询的最小值

select * from bookinfo where price > ALL (select price from bookinfo where book_category_id =4); -- 大于子查询结果的最大值

 

-- 查询图书信息表，显示图书类别为’医学’的所有图书信息。
-- in 后面的子查询返回一个数据列，等于数据列里的任意一个值都是满足条件的。
select * from bookinfo where book_category_id in (select category_id from bookcategory where parent_id = 2);
select * from bookinfo where book_category_id = any (select category_id from bookcategory where parent_id = 2);

-- 查看图书类别表中是否有’临床医学’的类别，如果有，则查看图书信息表。
select * from bookinfo where exists (select category_id from bookcategory where category='临床医学');

select * from bookinfo where exists (select category_id from bookcategory where category='儿科学');

 

/*由于业务的需要，需要创建一张罚款记录信息表，包含如下信息：图书编号、身份证号、应还日期、实际还书日期，罚款金额

表中记录来源于借阅信息表超出还书时间还未还书的读者.*/

create table readerfee(
book_id int,
card_id char(18),
return_date date,
actual_return_date date,
book_fee decimal(7,3),
primary key(book_id,card_id)
);

select book_id,card_id,return_date from borrowinfo where datediff(sysdate(),return_date)>0 and status = '否';

insert into readerfee(book_id,card_id,return_date) select book_id,card_id,return_date from borrowinfo where datediff(sysdate(),return_date)>0 and status = '否';

select * from readerfee;

/*
今天身份证号为210210199901012222的读者将超限的图书20151101归还，根据描述实现如下需求：
1、更新借阅信息表，将借阅状态（status）更新为‘是’。
2、更新罚款记录信息表，更新实际还书日期和罚款金额，罚款金额为每超出一天扣0.2元。
*/

update borrowinfo set status = '是' where book_id = 20151101 and card_id = '210210199901012222';

select * from borrowinfo;

update readerfee set actual_return_date=sysdate(), book_fee=datediff(sysdate(),return_date)*0.2 where book_id = 20151101 and card_id = '210210199901012222';

select * from readerfee;