MySQL学习笔记（八）子查询

1.

//创建tdb_goods数据表
CREATE TABLE tdb_goods(
                               goods_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
                               goods_name VARCHAR(150) NOT NULL,
                               goods_cate VARCHAR(40) NOT NULL,
                               brand_name VARCHAR(40) NOT NULL,
                               goods_price DECIMAL(15,3) NOT NULL DEFAULT 0.000,
                               is_show BOOLEAN NOT NULL DEFAULT 1,
                               is_saleoff BOOLEAN NOT NULL DEFAULT 0)<font size="4">;
<span style="font-size:18px;"><span style="font-size:18px;">//<span style="font-size:18px;">插入记录</span></span>
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) 
VALUES('R510VC 15.6英寸笔记本','笔记本','华硕','3399',DEFAULT,DEFAULT); 
  
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) 
VALUES('Y400N 14.0英寸笔记本电脑','笔记本','联想','4899',DEFAULT,DEFAULT); 
  
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) 
VALUES('G150TH 15.6英寸游戏本','游戏本','雷神','8499',DEFAULT,DEFAULT); 
  
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) 
VALUES('X550CC 15.6英寸笔记本','笔记本','华硕','2799',DEFAULT,DEFAULT); 
  
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) 
VALUES('X240(20ALA0EYCD) 12.5英寸超极本','超级本','联想','4999',DEFAULT,DEFAULT); 
  
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) 
VALUES('U330P 13.3英寸超极本','超级本','联想','4299',DEFAULT,DEFAULT); 
  
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) 
VALUES('SVP13226SCB 13.3英寸触控超极本','超级本','索尼','7999',DEFAULT,DEFAULT); 
  
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) 
VALUES('iPad mini MD531CH/A 7.9英寸平板电脑','平板电脑','苹果','1998',DEFAULT,DEFAULT); 
  
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) 
VALUES('iPad Air MD788CH/A 9.7英寸平板电脑 （16G WiFi版）','平板电脑','苹果','3388',DEFAULT,DEFAULT); 
  
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' iPad mini 
ME279CH/A 配备 Retina 显示屏 7.9英寸平板电脑 （16G WiFi版）','平板电脑','苹果','2788',DEFAULT,DEFAULT); 
  
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) 
VALUES('IdeaCentre C340 20英寸一体电脑 ','台式机','联想','3499',DEFAULT,DEFAULT); 
  
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) 
VALUES('Vostro 3800-R1206 台式电脑','台式机','戴尔','2899',DEFAULT,DEFAULT); 
  
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) 
VALUES('iMac ME086CH/A 21.5英寸一体电脑','台式机','苹果','9188',DEFAULT,DEFAULT); 
  
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('AT7-7414LP 
台式电脑 （i5-3450四核 4G 500G 2G独显 DVD 键鼠 Linux ）','台式机','宏碁','3699',DEFAULT,DEFAULT); 
  
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) 
VALUES('Z220SFF F4F06PA工作站','服务器/工作站','惠普','4288',DEFAULT,DEFAULT); 
  
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) 
VALUES('PowerEdge T110 II服务器','服务器/工作站','戴尔','5388',DEFAULT,DEFAULT); 
  
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) 
VALUES('Mac Pro MD878CH/A 专业级台式电脑','服务器/工作站','苹果','28888',DEFAULT,DEFAULT); 
  
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) 
VALUES(' HMZ-T3W 头戴显示设备','笔记本配件','索尼','6999',DEFAULT,DEFAULT); 
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) 
VALUES('商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT); 
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) 
VALUES('X3250 M4机架式服务器 2583i14','服务器/工作站','IBM','6888',DEFAULT,DEFAULT); 
  
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) 
VALUES('玄龙精英版 笔记本散热器','笔记本配件','九州风神','',DEFAULT,DEFAULT); 
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) 
VALUES(' HMZ-T3W 头戴显示设备','笔记本配件','索尼','6999',DEFAULT,DEFAULT); 
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) 
VALUES('商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT);
//显示数据结构,注意SET NAMES gbk可以切换字符集格式。
SHOW COLUMNS FROM tdb_goods\G;

2. 子查询

Subquery是指出现在其他SQL语句内的SELECT子句，例如：

SELECT * FROMt1 WHERE col1 = (SELECT col2 FROM t2),其中SELECT * FROMt1称为外查询（Outer Query），而SELECT col2 FROM t2称为子查询（SubQuery）

注意：

(1)子查询指嵌套在查询内部，且必须始终出现在圆括号内。

(2) 子查询可以包含多个关键字或条件，如DISTINCT、GROUP BY、ORDER BY、LIMIT、函数等。

(3) 子查询的外层查询可以是：SELECT、INSERT、UPDATE、SET或DO。

子查询的返回值可以为返回标量、一行、一列或子查询。

3. 使用比较运算符的子查询

比较运算符如：=、>、<、>=、<=、<>、!=、<=>

语法结构为：operand comparison_oprator subquery

示例：

//查找tdb_goods表中所有商品的平均价格，而且对结果进行四舍五入

SELECT ROUND(AVG(goods_price),2) FROM tdb_goods;

//查看所有商品中价格高于平均价格的商品

SELECT goods_id,goods_name,goods_price FROM  tdb_goods WHERE goods_price>= (SELECT ROUND(AVG(goods_price),2) FROM tdb_goods);

//查询超级本

SELECT goods_id,goods_name,goods_price  FROM tdb_goods WHERE goods_cate = '超级本';

//查询大于这些超级本价格的设备

SELECTgoods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price > (SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本');//系统将提示错误

4. 用ANY、SOME、ALL修饰的比较运算符,其中ANY与SOME是等价的，只需符合其中的一个即可，ALL需全部符号

operand comparison_operator ANY (subquery)

operand comparison_operator SOME (subquery)

operand comparison_operator ALL (subquery)

	ANY	SOME	ALL
>、>=	最小值	最小值	最大值
<、<=	最大值	最大值	最小值
=	任意值	任意值
<>、！=			任意值

//显示超级本的价格

SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本'；

//加入ANY关键字之后，将会输出价格大于最低超级本价格的字段

SELECTgoods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price > ANY (SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本');

5. 使用[NOT] IN的子查询

语法结构：

operand comparison_operator [NOT] IN (subquery)

注意：=ANY运算符与IN等效，!=ALL或<>ALL运算符与NOT IN等效。

示例：

SELECTgoods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price IN (SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本');

6.使用[NOT] EXISTS的子查询（一般很少使用）

如果子查询返回任何行，EXISTS将返回TRUE，否则为FALSE。