第八章:子查询
在SQL语言中,一个SELECT-FROM-WHERE语句称为一个查询块。当获得一个查询的答案需要多个步骤的操作,首先必须创建一个查询来确定用户不知道但包含在数据库中的值,将一个查询块嵌套在另一个查询块的WHERE字句或HAVING短语的条件中查询块称为子查询或内层查询。上层的查询块曾为父查询或外层查询。子查询的结果作为输入传递回“父查询”或“外部查询”。父查询将这个值结合到计算中,以便确定最后的输出。
SQL语言允许多层嵌套查询,即一个子查询中还可以嵌套其他子查询。以层层嵌套的方式来构造程序正是SQL中"结构化"的含义所在。
子查询是本质上就是一个完整 的SELECT 语句,它可以使一个 SELECT、SELECT...INTO 语句、INSERT...INTO 语句、DELETE 语句、或 UPDATE 语句或嵌套在另一子查询中。子查询的输出可以包括一个单独的值(单行子查询)、几行值(多行子查询)、或者多列数据(多列子查询)。
本节目标:理解什么是子查询;
什么是子查询?
子查询是指嵌套在其他SQL语句内的查询语句。
例:SELECT * FROM table WHERE col1=(SELECT col2 FROM table2);
子查询:是在嵌套在查询内部,且必须始终出现在圆括号内。在数据计算时,是先计算子查询的。子查询的结果作为外层另一个查询的过滤条件。
子查询可以添加到SELECT、UPDATE和DELETE等语句。
案例:身份证号为’ 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';
用ANY、ALL、SOME关键字修饰子查询
ANY、ALL、SOME关键字放在比较运算符的后面。
ANY和SOME是同义词,表示满足内层子查询中的任何一个条件。
ALL表示需要同时满足所有内层查询的条件。
ANY或SOME关键字
测试数据(在案例之前需要添加测试数据)
insert into bookcategory(category,parent_id)values('眼科学',2),('临床医学',2); insert into bookinfo(book_id,book_category_id,book_name,author,price,press,pubdate,store) values (20151101, 6, '临床诊断学', '万学红,陈红吴,汉妮 等', 115, '人民卫生出版社', '2015-06-01',10), (20151102, 6, '临床综合基本技能', '李雁, 潘涛',27.8, '人民卫生出版社', '2015-07-01', 5);
为了演示的需要将该读者的余额改为500
update readerinfo set balance = 500 where card_id = '210210199901012222';
向借阅信息表插入一条测试数据
insert into borrowinfo(book_id,card_id,borrow_date,return_date,status) values (20151101,'210210199901012222','2017-10-10','2017-11-10','否');
举例:
查看书价大于图书类别编号为4的任一图书价格的所有图书信息。
SELECT * FROM bookinfo where price > ANY (select price from bookinfo where book_category_id=4);
或
SELECT * FROM bookinfo where price > SOME (SELECT price from bookinfo where book_category_id=4);
ALL关键字
举例:
查看书价大于图书类别编号为4的全部图书价格的所有图书信息。
SELECT * from bookinfo where price > ALL (SELECT price from bookinfo WHERE book_category_id =4 );
案例:
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); -- 35.5 ~ 95 大于子查询的最小值 select * from bookinfo where price > ALL (select price from bookinfo where book_category_id =4); -- 大于子查询结果的最大值
本节目标:
熟悉使用[NOT]IN操作符引发的子查询;
了解EXISTS操作符引发的子查询;
1、使用[NOT]IN的子查询
IN关键字进行子查询时,内层查询语句仅仅返回一个数据列,这个数据列里的值将提供给外层查询语句进行比较操作。
NOT IN整好与IN的作用相反。
案例
查询图书信息表,显示图书类别为不是‘医学’的所有图书信息。
SELECT * FROM bookinfo WHERE book_category_id NOT IN (SELECT catetory_id FROM bookcategory where parent_id=2);
2、使用[NOT] EXISTS的子查询
例: SELECT * FROM table1 WHERE EXISTS(子查询);
判断子查询是否返回行∶
如果返回,那么EXISTS的结果为true ;
如果没有返回任何行,那么EXISTS返回的结果为false。
案例:
1)、查询图书信息表,显示图书类别为’医学’的所有图书信息。
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);
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='儿科学');
本节目标:熟悉插入记录时使用子查询的操作。
1、INSERT……SELECT
INSERT INTO SELECT 语句从一个表复制数据,然后把数据插入到一个已存在的表中。
也就是通过子查询将查询结果写入到另外一个数据表中。
例:INSERT INTO table2 SELECT * FROM FROM table1;
案例:
1)、由于业务的需要,需要创建一张罚款记录信息表,包含如下信息:图书编号、身份证号、应还日期、实际还书日期,罚款金额
表中记录来源于借阅信息表超出还书时间还未还书的读者。
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;
2)、今天身份证号为210210199901012222的读者将超限的图书20151101归还,根据描述实现如下需求:
①、更新借阅信息表,将借阅状态(status)更新为‘是’。
②、更新罚款记录信息表,更新实际还书日期和罚款金额,罚款金额为每超出一天扣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;