mysql查询实例（MySQL数据查询语言之子查询）
mysql查询实例（MySQL数据查询语言之子查询）rows >= 1 cols >= 1○ 标量子查询其返回的结果是一个虚表.▲ 分类根据其返回的行与列的个数不同分类:○ 表子查询
分治思想. 复杂的查询分解为若干个简单的查询
▲ 子查询的引入
#找出商店售价比 诺基亚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
其他分类方式:
外层查询 内层查询
父查询 子查询
▲ 子查询的使用场合
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);
