MySQL | 数据查询语言之子查询

作者 | 川石信息
来源 | 今日头条

子查询

分治思想. 复杂的查询分解为若干个简单的查询

▲ 子查询的引入

#找出商店售价比 诺基亚E66 要贵的产品的信息
s1. get 诺基亚E66 price
SELECT shop_price FROM ecs_goods
WHERE goods_name = '诺基亚E66';
#2298.00
s2. ALL price > 2298.0
SELECT * FROM ecs_goods
WHERE shop_price > 2298.0;
SELECT * FROM ecs_goods
WHERE shop_price > (SELECT shop_price FROM ecs_goods
WHERE goods_name = '诺基亚E66');

1、子查询定义与分类

▲ 定义

子查询本质上就是一个select表达式(凡是能返回一个虚表的式子),可以嵌套在select语句的子句中,其返回的结果可以被select语句所用.

▲ 分类

根据其返回的行与列的个数不同分类:

○ 表子查询

其返回的结果是一个虚表.

rows >= 1 cols >= 1

○ 标量子查询

其返回的结果是一个值.

rows = 1 cols = 1

其他分类方式:

外层查询 内层查询

父查询 子查询

▲ 子查询的使用场合

2、select后的子查询

select子句后只能放置标量子查询,要求每次只能返回一个值.

语法:

select ... (select expression) ...
from tabs
where search_condition

order by sort_columns;
#列出商品表中各商品的类型名称
SELECT goods_id,
(SELECT cat_name FROM ecs_goods_type WHERE cat_id = g.goods_type) AS type_name,
goods_name
FROM ecs_goods g
ORDER BY 2;
#模拟Oracle的rownum(行编号)
SELECT (SELECT COUNT(*) FROM ecs_goods e WHERE e.goods_id <= g.goods_id) AS rownum,
goods_id,
goods_name
FROM ecs_goods g
ORDER BY 1;

3、from后的子查询

子查询返回的结果当做数据源来使用.

语法:

#单表
select list from tab_name ....
select list from (select expression) alias_name ... #MySQL内联视图必须有别名
#连接
select list
from left_tab join_type right_tab
on join_condition ...
select list
from (select expression) alias_name join_type (select expression) alias_name
on join_condition ...

#哪些用户购买了市场价格大于2000的商品
SELECT * FROM ecs_users;
SELECT * FROM ecs_order_info;
SELECT * FROM ecs_order_goods;
#SELECT * FROM ecs_goods;
SELECT u.user_id, u.user_name, t.goods_name, t.market_price
FROM(
ecs_users u
INNER JOIN ecs_order_info oi
ON u.user_id = oi.user_id
)INNER JOIN(SELECT * FROM ecs_order_goods og
WHERE og.market_price > 2000) t
ON oi.order_id = t.order_id;

4、where后的子查询

○ 比较谓词中的子查询

where ve1 VS ve2
#ve1 or ve2 可以使用 select expression 替换
where ve VS (select expression)

#找出市场价格与P806相同的商品信息
SELECT *
FROM ecs_goods
WHERE market_price = (SELECT market_price FROM ecs_goods WHERE goods_name = 'P806');

▲ Subquery returns more than 1 row错误

原因: 子查询返回的行数>=2

SELECT *
FROM ecs_goods
WHERE market_price = (SELECT market_price FROM ecs_goods WHERE brand_id = 1);

规避: 让子查询返回的行数<=1

SELECT *
FROM ecs_goods
WHERE market_price = (SELECT market_price FROM ecs_goods WHERE brand_id = 1 limit 0,1);

▲ limit子句

语法:

limit [offset,]row_count
limit row_count OFFSET offset
其中,offset为偏移量,可以认为是从多少行以后开始取记录,若不写则默认为0
row_count为获取的行数

例子:
limit 0,1 #获取第一行
limit 5,8 #从第5行后面开始取,总共获取8行,即返回6-13行
limit 5 #获取前5行

○ 集合成员谓词中的子查询

where ve [not] in (ve1,ve2,...,ven)
#(ve1,ve2,...,ven) 可以使用 select expression 代替
#其本质上就是一个n行1列的表
where ve [not] in (select expression)

#哪些商品被客户购买过?
SELECT * FROM ecs_goods;
SELECT * FROM ecs_order_goods;
SELECT *
FROM ecs_goods
WHERE goods_id IN (SELECT goods_id FROM ecs_order_goods);
#哪些商品没有被客户购买过?
SELECT *
FROM ecs_goods
WHERE goods_id NOT IN (SELECT goods_id FROM ecs_order_goods);

▲ not in陷阱

原因: not in后的集合中包含了null元素

#更改并更新品牌表
ALTER TABLE ecs_goods MODIFY brand_id SMALLINT(5) UNSIGNED NULL;
UPDATE ecs_goods
SET brand_id = NULL
WHERE brand_id = 9;
COMMIT;
#商品品牌表中有哪些品牌没有出现在商品表中
SELECT * FROM ecs_brand;
SELECT * FROM ecs_goods;

SELECT *
FROM ecs_brand
WHERE brand_id NOT IN (SELECT brand_id FROM ecs_goods);
WHERE bi NOT IN (1,2,3,4,NULL) -->
WHERE NOT bi IN (1,2,3,4,NULL) -->
WHERE NOT (bi = 1 OR bi = 2 OR bi = 3 OR bi = 4 OR bi = NULL) -->
WHERE (bi != 1 AND bi != 2 AND bi != 3 AND bi != 4) AND bi != NULL -->
(TRUE OR FALSE OR unknown) AND unknown --> unknown or false 恒为假

规避: 去掉not in后集合中的null元素

SELECT *
FROM ecs_brand
WHERE brand_id NOT IN (SELECT brand_id FROM ecs_goods WHERE brand_id IS NOT NULL);

○ 存在谓词中的子查询

where [not] exists (select expression)
#select expression 返回 非空集 表示存在 exists谓词返回 true
#select expression 返回 空集 表示不存在 exists谓词返回 false

#哪些商品被客户购买过?
SELECT * FROM ecs_goods;
SELECT * FROM ecs_order_goods;

SELECT *
FROM ecs_goods g
WHERE EXISTS (SELECT * FROM ecs_order_goods
WHERE goods_id = g.goods_id);

#哪些产品没有被客户购买过?
SELECT *
FROM ecs_goods g
WHERE NOT EXISTS (SELECT * FROM ecs_order_goods
WHERE goods_id = g.goods_id);

▲ 关联子查询与非关联子查询

#非关联子查询
子查询能够独立运行返回一个结果
父查询不需要向子查询传递数据
SELECT * FROM ecs_goods
WHERE shop_price > (SELECT shop_price FROM ecs_goods
WHERE goods_name = '诺基亚E66');

#关联子查询
子查询不能够独立运行,
需要父查询逐行向其传递数据
SELECT *
FROM ecs_goods g
WHERE EXISTS (SELECT * FROM ecs_order_goods
WHERE goods_id = g.goods_id);