子查询
子查询(Subquery)是指出现在其它SQL语句内的SELECT语句.
SELECT * FROM t1 WHERE col1=(SELECT col2 FROM t2);
其中,SELECT * FROM t1 称为Outer Query / Outer Statement, SELECT col2 FROM t2 称为SubQuery.
- 子查询必须始终出现在圆括号内
- 子查询可以包含多个关键字或条件,如DISTINCT, ORDER BY, GROUP BY, LIMIT, 函数等
- 子查询的外部查询可以是SELECT, INSERT, UPDATE, SET或DO.
- 子查询可以返回标量,一行,一列或子查询
使用比较运算符的子查询
- 语法结构:operand comparision_operator ANY | SOME | ALL (subquery)
- 使用比较运算符的子查询,若比较运算符后跟的子查询结果有多个值时,用要用关键字ANY, SOME, ALL来做修饰
练习1
//求所有电脑产品的平均价格,并且保留两位小数,AVG,MAX,MIN、COUNT、SUM为聚合函数
SELECT ROUND(AVG(goods_price),2) AS avg_price FROM tdb_goods;
//查询所有价格大于平均价格的商品,并且按价格降序排序
SELECT goods_id, goods_name, goods_price FROM tdb_goods WHERE goods_price>5845.10 ORDER BY goods_price DESC;
使用子查询来实现
SELECT goods_id, goods_name, goods_price FROM tdb_goods
WHERE goods_price>(SELECT ROUND(AVG(goods_price),2) FROM tdb_goods)
ORDER BY goods_price DESC;
使用 [NOT] IN 的子查询
- 语法结构:operant [NOT] IN (SubQuery)
- =ANY, =SOME 等价于 IN; !=ALL,<>ALL d等价与NOT IN
练习2
//查询价格大于或等于"超级本"价格的商品,并且按价格降序排列
SELECT goods_id, goods_name, goods_price FROM tdb_goods
WHERE goods_price=ANY(SELECT goods_price FROM tdb_goods WHERE goods_cate='超级本')
ORDER BY goods_price DESC;
= ANY 或 = SOME 等价于 IN
SELECT