MySQL学习15：子查询(一)
- WBOY原創
- 2016-06-07 14:49:461278瀏覽
一数据准备 (1)创建数据库 CREATE DATABASE IF NOT EXISTS t2 CHARACTER SET gbk; (2)创建数据表 CREATE TABLE IF NOT EXISTS tdb_goods( goods_id SMALLINT UNSIGNED PRIMARY KEYAUTO_INCREMENT, goods_name VARCHAR(150) NOT NULL, goods_cate VARCHAR(40)
一数据准备
(1)创建数据库
CREATE DATABASE IF NOT EXISTS t2 CHARACTER SET gbk;(2)创建数据表
CREATE TABLE IF NOT EXISTS tdb_goods(
goods_id SMALLINT UNSIGNED 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) UNSIGNED NOT NULL DEFAULT 0,
is_show BOOLEAN NOT NULL DEFAULT 1,
is_saleoff BOOLEAN NOT NULL DEFAULT 0
);
(3)查看数据表结构
SHOW COLUMNS FROM tdb_goods;
(4)写入记录
设置MySQL数据库客户端显示的中文字符码格式：
SET NAMES gbk;
插入记录：
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);
INSERTtdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X240(20ALA0
EYCD) 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);
INSERTtdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('SVP13226SC
B 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('玄龙精英版
笔记本散热器','笔记本配件','九州风神','134',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('商务双肩背
包','笔记本配件','索尼','99',DEFAULT,DEFAULT);
SELECT * FROM tdb_goods\G;
二子查询
(1)子查询相关概念
1子查询
子查询(Subquery)是指出现在其他SQL语句内的(也就是另一个查询语句中的)SELECT子句。
例句：
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
其中，SELECT * FROM t1，称为Outer Query(外查询或者Outer Statement)，SELECT column1 FROM t2，称
为Sub Query(子查询)。
2子查询组成
子查询指嵌套在查询内部，且必须始终出现在圆括号内。
子查询可以包含多个关键字或条件，如DISTINCT、GROUP BY、ORDER BY、LIMIT和相关函数等。
子查询的外层查询可以是：SELECT、INSERT、UPDATE、SET或DO。
所以，我们说子查询是嵌套在外查询内部。而事实上它有可能在子查询内部再嵌套子查询，子查询必须出现在圆
括号之间。
3子查询返回值
子查询可以返回标量、一行、一列或子查询。
(2)三类子查询
1由比较运算符引发的子查询
比较运算符包括：=、>、=、、!=、。使用比较运算符引发的子查询的语法结构为；operand
comparsion_operator subquery。
例子：
首先设置MySQL数据库客户端数据显示的编码格式：
SET NAMES gbk;
1)查询所有商品价格的平均值：
SELECT AVG(goods_price) FROM tdb_goods;
2)查询所有商品价格的平均值，并且结果保留两位小数
SELECT ROUND(AVG(goods_price),2) FROM tdb_goods;
3)查询所有商品价格中大于平均价格的商品
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price >= 5324.32;
4)通过子查询查询所有商品价格中大于平均价格的商品
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price >= (SELECT
ROUND(AVG(goods_price),2) FROM tdb_goods);
由此看出3)和4)的查询结果是一致的，这说明子查询起作用了。
5)查询商品类型是超极本的商品价格
SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本';
6)查询商品类型是超级本的商品
SELECT * FROM tdb_goods WHERE goods_cate = '超级本'\G;
2使用ANY、SOME或ALL修饰的比较运算符的子查询
当子查询返回多个结果时可以使用ANY、SOME或ALL修饰的比较运算符，其中ANY和SOME是等价的，也就
是只需要符合其中一个即可，而ALL表示符合全部返回结果。其中语法结构为：
operand comparsion_operator ANY (subquery);
operand comparsion_operator SOME (subquery);
operand comparsion_operator ALL (subquery);
比较运算符使用ANY、SOME或ALL关键字的不同结果：
1)查询所有商品中价格不小于超级本价格(最小价格)的商品
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price >= ANY (SELECT
goods_price FROM tdb_goods WHERE goods_cate = '超级本');
2)查询所有商品中价格不小于超级本价格(最大价格)的商品
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price >= ALL (SELECT
goods_price FROM tdb_goods WHERE goods_cate = '超级本');
3)查询所有商品中价格等于超级本价格(任意一个)的商品
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price = ANY (SELECT
goods_price FROM tdb_goods WHERE goods_cate = '超级本');
