MySql教程(11)--MySQL 子查询操作

一：什么是子查询
　　定义：

　　子查询允许把一个查询嵌套在另一个查询当中。

　　子查询，又叫内部查询，相对于内部查询，包含内部查询的就称为外部查询。

　　子查询可以包含普通select可以包括的任何子句，比如：distinct、 group by、order by、limit、join和union等；但是对应的外部查询必须是以下语句之一：select、insert、update、delete、set或 者do。

　　子查询的位置：
　　　　select 中、from 后、where 中.group by 和order by 中无实用意义。
举例
/*身份证号为’ 210210199901015555’的用户，今天借了一本图书编号为’ 20150301’的图书，完成下列业务需求：
更新读者信息表的余额。
*/

select price from bookinfo where book_id = 20150301; 返回69

select * from readerinfo;

update readerinfo set balance = balance - 69* 0.05 where card_id = ‘210210199901015555’;

或者update readerinfo set balance = balance - (select price from bookinfo where book_id = 20150301) * 0.05 where card_id = ‘210210199901015555’; 这就是子查询，嵌套在其他sql语句里的语句，必须在括号内

二：运算符子查询
– 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 = ‘数据库’);
– 4、查询图书信息表，显示图书类别为’计算机’的所有图书信息。
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); – 大于子查询的最小值

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

三：使用[NOT]IN或EXISTS的子查询
– 查询图书信息表，显示图书类别为’医学’的所有图书信息。
– 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 not in (select category_id from bookcategory where parent_id = 2);和in相反
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;