子查询

子查询

例一：查询分类名称为手机数码的所有商品
-- 1.查询分类名为手机数码的ID
SELECT cid FROM category WHERE cname='手机数码';
-- 2.得出ID为1的结果
SELECT * FROM product WHERE cno = 1;
-- 用子查询
SELECT * FROM product WHERE cno =(SELECT cid FROM category WHERE cname='手机数码');



例二：查询出(商品名称，商品分类名称)信息
-- 左连接
SELECT p.pname,c.cname FROM product  AS p LEFT OUTER JOIN category AS c ON p.cno= c.cid;
-- 用子查询
SELECT pname,(SELECT cname FROM category c WHERE p.cno=c.cid) AS 商品分类名称 FROM product AS p;