MySQL 中的三种子查询

本篇开始是子查询的学习笔记，首先是一道题目：

1. 查出最新的（goods_id 最大）的一条商品，比较简单，按照 order by 降序排列然后 limit 1即可：

   select goods_id,goods_name,shop_price from goods order by goods_id desc limit 1;
   

where 型子查询

1. 如果上面那个题目多了一个要求，不能用排序。这样子做比较难。如果再加一个条件，最大的 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 赋值给中间变量，再利用中间变量查询

2. 如果需要取出每个栏目下 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 子查询之前，需要理解一个概念：查询结果集在结构上可以当成表看，那就可以当成临时表对他进行再次查询：

1. 取出每个栏目下 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;

1. 取栏目表且只取出下面有商品的栏目表

   那么假设某个栏目的 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 这一行就取出来