where子查询
把查询语句的结果当where的判断条件进行查询,子查询的结果为单列。
1、查出本网站最新的(goods_id最大)的一条商品
使用排序:按照goods_id、desc排序,再取第一行
mysql> select goods_id,goods_name from goods order by goods_id desc limit 1;
不用排序
mysql> select max(goods_id),goods_name from goods;
mysql> select goods_id,goods_name from goods where goods_id=(select max(goods_id) from goods);
2、把每个栏目下goods_id最大的商品查出来
mysql> select goods_id,goods_name from goods where goods_id in (select max(goods_id) from goods group by cat_id);
from子查询
查询结果集在结构上可以当表输入继续进行查询
3、取出每个栏目价格最高的商品
mysql> select cat_id,shop_price from (select cat_id,goods_name,shop_price from goods order by cat_id,shop_price desc) as tmp group by cat_id;
exists子查询
把外层sql的结果拿到内层去测试,如果内层的sql成立则该行取出。
4、查出有商品的栏目(有的栏目下是其他子栏目)
条件:设某栏目cat_id为N,则select * from goods where cat_id=N为真时表示该栏目有商品
category为一张保存了栏目信息的表
mysql> create table category( cat_id int auto_increment primary key, cat_name varchar(20) not null default '' )engine myisam charset utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> select cat_id,cat_name from category where exists (select * from goods where goods.cat_id=category.cat_id);