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)创建数据库

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