本篇开始是子查询的学习笔记,首先是一道题目:
查出最新的(goods_id 最大)的一条商品,比较简单,按照 order by 降序排列然后 limit 1即可:
select goods_id,goods_name,shop_price from goods order by goods_id desc limit 1;
where 型子查询
如果上面那个题目多了一个要求,不能用排序。这样子做比较难。如果再加一个条件,最大的 goods_id 就是 33,那么就可以这样写:
select goods_id,goods_name,shop_price from goods where goods_id = 33;
但是这个做法不具有通用性,增加或者删除数据以后,可能就不能查出正确结果。问题的关键就在于怎么知道最大的 goods_id ,前面的学习知道,用统计函数可以查到最大的 goods_id:
select max(goods_id) from goods
这句语句的返回值始终是指向最大的 goods_id。把这两句结合起来:
select goods_id,goods_name,shop_price from goods where goods_id = (select max(goods_id) from goods);
就像在变成语言中,先把查出来最大的 goods_id 赋值给中间变量,再利用中间变量查询
如果需要取出每个栏目下 goods_id 最大的商品信息,由于有多个栏目,所以 goods_id 也就形成了一个集合,所以需要用到 in :
select goods_id,goods_name,shop_price from goods where goods_id in (select max(goods_id) from goods group by cat_id);
where 型子查询,如果是 where 列 =(内层 sql) 则内层 sql 返回的必须是单行单列,单个值。
where 型子查询,如果是 where 列 in(内层 sql) 则内层 sql 返回的必须是单列,可以多行。
from 型子查询
在学习 from 子查询之前,需要理解一个概念:查询结果集在结构上可以当成表看,那就可以当成临时表对他进行再次查询:
取出每个栏目下 goods_id 最大的商品信息:
首先执行下面这句语句以后,能得到一个结果,其中每个栏目的第一行都是 goods_id 最大的行:
select goods_id,cat_id,goods_name from goods order by cat_id asc,goods_id desc;
假设存在这张表,表名叫 tmp ,那么:
select * from (select goods_id,cat_id,goods_name from goods order by cat_id asc,goods_id desc) as tmp group by cat_id;
不过上面这句语句在 8.0 或者某些 5.7版本中会报错。本段主要是为了理解 from 型子查询,实际业务中按照实际情况写语句即可,中心思想就是把结果当成表看,然后起一个别名。
exists 型子查询
exists 型子查询是指外层 sql 的结果,拿到内层 sql 去测试,如果内层 sql 成立,则该行取出。学习之前先建一张表:
create table category(
-> cat_id int auto_increment primary key,
-> cat_name varchar(20) not null default ''
-> )engine myisam;
向里面插入原来 ecshop.ecs_category 中的部分数据:
insert into test.category select cat_id,cat_name from ecshop.ecs_category;
取栏目表且只取出下面有商品的栏目表
那么假设某个栏目的 cat_id 为 N,则 select * from goods where cat_id = N如果能取出,则说明该栏目下有商品:
select cat_id,cat_name from category where exists (select * from goods where goods.cat_id=category.cat_id);
可以理解为,先把第一个 cat_id=1 取出来,带入到内层,那么内层就变成了:
select * from goods where goods.cat_id=1;
查询发现没有对应的值,返回 empty ,那么category 中 cat_id=1 这一行就不要了。接下来看 cat_id=2,代入,发现可以取出货物 goods 来,那么 category 中 cat_id=2 这一行就取出来