MySQL学习15：子查询(一)

一数据准备 (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)创建数据库

(2)创建数据表

CREATE TABLE IF NOT EXISTS tdb_goods(

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 = '超级本');