子查询
where型子查询, 将子查询的结果作为父查询的比较条件
select 字段名 from 数据表名 where 字段 = (子查询);
实例:
以 products,category 这两个数据表为实例
select max(price) from products; ------------------------------------ 先找出products中最高的价格
select * from products where = (select max(price) from products);-------------------------- 将上条查询语句用作本条查询语句的条件筛选值
from型子查询, 将子查询结果作为一张表, 提供给父查询使用
select 字段名 from (子查询) 表别名 where 条件;
实例:
select * from category;
select * from product p inner join (select * from category) on p. category_id = c.cid where p.price > 5000;
exists型子查询, 子查询的结果为单列多行, 类似一个数组, 父层查询使用 in 函数 包含子查询结果
select 字段名 from 数据表名 where 字段名 in (子查询);
select distinct category_id from products where price < 2000;
select * from category where cid in (select distinct category_id from products where price < 2000);