前端学数据库之子查询

定义

子查询(Subquery)是指出现在其他SQL语句内的SELECT子句

SELECT  *  FROM  t1  WHERE   col1=(SELECT  col2  FROM  t2);

其中，SELECT * FROM t1，称为外层查询(Outer Query/Outer Statement)，SELECT col2 FROM t2，称为子查询(SubQuery)

　　在使用子查询时，需要注意的是

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

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

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

　　4、子查询可以返回值：标量、一行、一列或者子查询

比较运算符

　　使用比较符是其中一类子查询

operand comparison_operator subquery

　　比较运算符包括=、!=、<>、<=> 、>、<、>=、<=

数据准备

　　下载数据文件，建立数据库，数据表，并存入相应记录

mysql> use table mysql;

ERROR 1049 (42000): Unknown database 'table'

mysql> create database db5;

Query OK, 1 row affected (0.01 sec)

mysql> use db5;

Database changed

mysql> 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

-> ) DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.04 sec)

mysql> INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Y400N 14.0英寸笔记本电脑','笔记本','联想','4899',DEFAULT,DEFAULT);

Query OK, 1 row affected (0.39 sec)

mysql> INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('G150TH 15.6英寸游戏本','游戏本','雷神','8499',DEFAULT,DEFAULT);

Query OK, 1 row affected (0.02 sec)

mysql> INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X550CC 15.6英寸笔记本','笔记本','华硕','2799',DEFAULT,DEFAULT);

Query OK, 1 row affected (0.00 sec)

mysql> INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X240(20ALA0EYCD) 12.5英寸超极本','超级本','联想','4999',DEFAULT,DEFAULT);

Query OK, 1 row affected (0.01 sec)

mysql> INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X240(20ALA0EYCD) 16.5英寸超极本','超级本','联想','6999',DEFAULT,DEFAULT);

求所有电脑产品的平均价格,并且保留两位小数，AVG、MAX、MIN、COUNT、SUM为聚合函数

[注意]AVG()是一个用来求平均值的函数

mysql> select AVG(goods_price) from tdb_goods;

mysql> select round(AVG(goods_price),2) from tdb_goods;

查询所有价格大于平均价格的商品

SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price gt 5299.00;

通过子查询来实现相同的需求

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

查询类型为“超级本”的商品价格

mysql> select goods_price from tdb_goods where goods_cate='超级本';

mysql> select * from tdb_goods where goods_cate="超级本"\G;

查询价格大于或等于"超级本"价格的商品

mysql> select goods_id,goods_name,goods_price from tdb_goods where goods_price > (select goods_price from tdb_goods where goods_cate='超极本');

系统提示错误，这时就需要用到接下来要介绍的修饰关键字

修饰关键字

　　修饰关键字包括ANY、SOME、ALL三个，如果子查询返回多个值时，可以使用它们

operand comparison_operator ANY（子查询）

operand comparison_operator SOME（子查询）

operand comparison_operator ALL（子查询）

情况处理

　　1、运算符为>或>=，使用ANY关键字时，表示大于子查询结果中的最小值

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

由结果可知，返回的都大于4299的值，即最小值

mysql> SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price > SOME (SELECT goods_price FRROM tdb_goods WHERE goods_cate = '超级本');

2、运算符为>或>=，使用ALL关键字时，表示大于子查询结果中的最大值

mysql> SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price > ALL (SELECT goods_price FROOM tdb_goods WHERE goods_cate = '超级本');

3、运算符为<或<=，使用ANY或SOME关键字时，表示小于子查询结果中的最大值；使用ALL关键字时，表示小于子查询结果中的最小值

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

[NOT]IN

operand comparison_operator [NOT] IN (subquery)

第二种子查询是由IN 或 NOT IN引发的子查询，与比较运算符使用的方法基本相同 

其中，= ANY 运算符与 IN 等效，!= ALL或 <> ALL运算符与 NOT IN 等效

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

 

mysql> SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price NOT IN (SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本');

[NOT] EXISTS

　　第三种子查询是由EXISTS 或 NOT EXISTS引发的子查询。如果子查询返回任何行，EXISTS将返回TRUE；否则返回FALSE

存储查询结果

　　我们可以把查询结果统一存储到一个新的数据表中，而不需要一条一条地录入

　　下面，先创建一个“商品分类”空表

mysql> create table if not exists goods_cates(

-> id int unsigned primary key auto_increment,

-> name varchar(40) not null

-> );

Query OK, 0 rows affected (0.01 sec)

然后，查询tdb_goods表的所有记录，并且按"类别"分组

mysql> select goods_cate from tdb_goods group by goods_cate;

将分组结果写入到“商品分类”数据表中

INSERT [INTO] tbl_name [(col_name),...)] SELECT...;

mysql> desc goods_cates;

mysql> insert goods_cates(name) select goods_cate from tdb_goods group by goods_cate;

mysql> select * from goods_cates;