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

一天一个关于测试知识点，5分钟内讲解你最关心的软件测试问题，今天就接着来谈谈关于软件测试中的“数据查询语言之子查询”。

　　子查询

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

　　▲ 子查询的引入

　　#找出商店售价比 诺基亚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);