基本sql语句--子查询

时间:2022-10-17 19:20:46
子查询有三种:where型子查询,from型子查询,exist子查询。
 
where型子查询
 
where子查询:指把内层的查询结果当成外层查询的比较条件。
 
例句:查询出最新的商品:select good_id,good_name from good where good_id = (select max(good_id) from good);--此句为子查询
                                       select good_id,good_name from good order by good_id DESC limit 1;
 
from型子查询
 
from子查询:把内层的查询结果当成临时表,供外层sql再次查询。
 
例:查询每个栏目下最新的商品:
          select good_id,cat_id,good_name from good where good_id in (select max(good_id) from good group by cat_id);---典型的where型子查询
          select * from (select good_id,cat_id,good_name from good order by cat_id asc,good_id desc)  as tmp group by cat_id;--from型子查询
 
exists型子查询
 
exists子查询:把外层的查询结果,拿到内层,看内层的查询是否成立。
 
典型示例:select cat_id,cat_name from category where exists (select * from good where good.cat_id = category.cat_id);
该子查询现在比较少用,已被join取代。select cat_id,cat_name from category join good ON category.cat_id = good.cat_id;