MySQL之子查询

在前面介绍了数据的记录的操作，主要包括两类：

记录的写操作：增加记录，修改记录，删除记录

记录的读取操作：记录的查找

首先在 test 数据库 中创建一个数据表 goods 

root@localhost test>CREATE TABLE goods(
    -> goods_id SMALLINT(5) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> goods_name varchar(150),
    -> goods_cate varchar(40),
    -> brand_name varchar(40),
    -> goods_price decimal(15,3) unsigned DEFAULT 0.000,
    -> is_show tinyint(1) DEFAULT 1,
    -> is_saleoff tinyint(1) DEFAULT 0
    -> );

使用命令查看创建情况，该命令用于查看表的结构：

root@localhost test>show columns from goods;

现在插入若干条记录

insert goods(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Vostr','lapPC','lennovo',3990,default,default);
insert goods(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('honorX6','phone','honor',990,default,default);
insert goods(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('lingyue','lapPC','HP',3990,default,default);
insert goods(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('back-2','backpack','sony',690,default,default);
insert goods(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('mi-9','phone','xiaomi',2990,default,default);
insert goods(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('sdkf','Trolley-bag','xuan',290,default,default);
insert goods(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('x-3','TV','TCL',1890,default,default);
insert goods(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('A-6','camera','sony',4990,default,default);

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

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

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

子查询是指嵌套在查询内部，且必须始终出现在()内，子查询可以包括多个关键字和条件，如DISTINCT,GROUP BY,ORDER BY,LIMIT，函数等；子查询的外层查询可以是：SELECT,INSERT,UPDATE,DET,DO.

子查询的结果可以是一个标量，一行，一列或者子查询

1、使用比较运算符的子查询

使用比较运算符的子查询，其中比较运算符有  =、>、 <、>=、<=、<>、！=、<=>  ，其语法结构为：

operand comparision_operator subquery

首先对表中的所有商品的价格进行平均值求解：

root@localhost test>SELECT AVG(goods_price) FROM goods;

小数点的位数太多，这里进行指定为为小数点后取两位

root@localhost test>SELECT ROUND(AVG(goods_price),2) FROM goods;

现在查询哪些商品的价格大于平均值呢

root@localhost test>SELECT goods_id,goods_name,goods_price FROM goods WHERE goods_price >= 2477.50;

这里使用的 2477.50 就是上一个查询结果的值，能否将两个语句“合并”，就利用子查询进行实现

root@localhost test>SELECT goods_id,goods_name,goods_price FROM goods WHERE goods_price >= (SELECT ROUND(AVG(goods_price),2) FROM goods);

这样就实现了子查询的功能。

现在来查询一下，分类中goods_cate为phone的有哪些商品

这里查询的时候对表达式goods_cate=phone中没有对phone进行使用引号报错了，加上引号之后：

root@localhost test>SELECT goods_price FROM goods WHERE goods_cate='phone';

现在想知道哪些商品的价格大于这些 phone 

root@localhost test>SELECT goods_id,goods_name,goods_price FROM goods WHERE goods_price > (SELECT goods_price FROM goods WHERE goods_cate='phone');

此时系统会提示 ERROR 1242  (21000):Subquery returns maore than 1 row 因为在上述查询中知道满足goods_cate='phone'的结果有两个，子查询返回了三条结果，所以应该指定到底是大于990还是2990,此时会用到以下语法

operand comparsion_operator ANY (subquery)
operand comparsion_operator SOME (subquery)
operand comparsion_operator ALL (subquery)

当子查询返回多个结果时，可以使用以上的语法进行修饰

root@localhost test>SELECT goods_id,goods_name,goods_price FROM goods WHERE goods_price > ANY (SELECT goods_price FROM goods WHERE goods_cate='phone');

root@localhost test>SELECT goods_id,goods_name,goods_price FROM goods WHERE goods_price > ALL (SELECT goods_price FROM goods WHERE goods_cate='phone');

2、使用[NOT] IN的子查询

使用IN 或者 NOT IN 的子查询和上述的方法基本相同，其语法结构如下

operand comparsion_operator [NOT] IN (subquery)

而该语法又与ALL，ANY又一定的联系，其中 =ANY与IN与算符等价，!=ALL或者<>ALL运算符与NOT IN运算符等价

root@localhost test>SELECT goods_id,goods_name,goods_price FROM goods WHERE goods_price  NOT IN (SELECT goods_price FROM goods WHERE goods_cate='phone');

查找产品类型不为phone 的记录

3、使用[NOT] EXISTS的子查询

这种情况使用的较少，如果子查询返回任何行，EXISTS将返回TRUE否则返回FALSE

4、多表更新

在数据表中可以发现，存在很多重复的字段，利用命令查看，如产品类型goods_cate中的LapPC，phone和品牌brand_name中的sony等，目前因为数据表只涉及了8条记录，但是如果有越来越多的数据记录，这将造成 查找时候效率降低 ，想着可以用外键来实现：

root@localhost test>select * from goods\G;

而外键的实现是利用两张数据表，现在只有一张数据表，因此需要创建一张数据表来存储分类和品牌,那如何才能得到这些商品的不同分类，以方便实现存储呢？就是在查找的时候按照分组就可以

root@localhost test> CREATE TABLE IF NOT EXISTS goods_cates(
    -> cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> cate_name VARCHAR(40) NOT NULL
    -> );

注意这里的数据表的名称为goods_cates和数据表goods中的记录goods_cate不要混淆

root@localhost test>SELECT goods_cate FROM goods GROUP BY goods_cate;

利用分类进行查看，这里共有6类，8条记录共有6类，随着商品记录不断增加，在类别上可能会大大的减少

将查找的类别写入到数据表中，可以一条一条的写入，当然这种方法面对很多类别的时候，操作相当麻烦，因此可以是如下语句

INSERT [INFO] table_name [(col_name，...)] SELECT...

将查询结果写入到数据表中

root@localhost test>select * from goods_cates;

数据表goods_cates为空，数据表的结结构为：

root@localhost test>DESC goods_cates;

这张数据表有两个字段，现在写入记录

root@localhost test>INSERT goods_cates(cate_name) SELECT goods_cate FROM goods GROUP BY goods_cate;

这里在INSERT的时候省略了VALUES，要注意

这6条记录已经成功写入到数据表中了，但是没有使用外键实现，而在数据表goods中依然存储的是lapPC,phone，而根据设计目的，在goods_cates数据表中应该将lapPC存储为3，将phone存储为 4等等，因此现在需要参照分类表goods_cates来更新商品表goods，就是参照别的表来更新当前的表，因此需要用到多表更新。其语法结构为

UPDATE table_references SET col_name1={expr1|DEFAULT} [,col_name2=expr2|DEFAULT}]...[WHERE where_condition]

table_reference {[INNER|CROSS] JOIN | {LEFT|RIGHT} [OUTER] JOIN} table_reference ON conditional_expr

root@localhost test>UPDATE goods INNER JOIN goods_cates ON goods_cate = cate_name
    -> SET goods_cate = cate_id;

这里需要更新的是表goods中的goods_cate使用内连接的方式,将goods表中的goods_cate修改为表goods_cates表中的cate_id,(如将goods中的goods_cate 为lapPC修为为3，因为goods_cates表的lapPC为3，goods_cate和cate_name中的值是相等的，)因此上述语句中连接条件为ON goods_cate = cate_name,条件有了，那么更新的值是什么呢，那就是SET  goods_cate = cate_id

因此多表更新可以总结为：

1、创建新表，这里是goods_cates

2、插入记录，这里是将goods_cate中的值插入到表goods_cates中的cate_name

3、多表的更新

发现上述步骤较为繁琐，可以实行CREATE...SELECT来实现

即是创建数据表同时将查询结果写入到数据表中，其语法结构为：

CREATE TABLE [IF NOT EXISTS] table_name [(create_defination,...)] select_statement

将上述的中1、2步骤合成一个命令,首先查看一下有多少种品牌

root@localhost test>SELECT brand_name FROM goods GROUP BY brand_name;

root@localhost test>CREATE TABLE table_goods_brands(
    -> brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> brand_name VARCHAR(40) NOT NULL
    -> )
    -> SELECT brand_name FROM goods GROUP BY brand_name;

注意这里在创建数据结束的时候没有加";"，而是在SELECT语句之后加上的“；”

此时需要参照品牌表table_goods_brands更新商品表goods,分别查询两个表中的结构情况

root@localhost test>UPDATE goods INNER JOIN table_goods_brands ON brand_name = brand_name
    -> SET brand_name = brand_id;

提示错误！因为两个表中均有brand_name，此时利用起别名进行区分是哪个表的grand_name

root@localhost test>UPDATE goods AS A INNER JOIN table_goods_brands AS B ON A.brand_name = B.brand_name
    -> SET A.brand_name = B.brand_id;

再查询一下结构

发现goods_cate和brand_name依然是字符型，因此需要尽量的去修改结构

root@localhost test>alter table goods
    -> change goods_cate cate_id smallint unsigned not null,
    -> change brand_name brand_id smallint unsigned not null;

再次查找发现现在才更改为smallint unsigned进行了“瘦身”操作。目前将数据进行分开存储了，那要不要做外键呢？其实外键不一定非要物理的外键,就是非要有FOREIGN KEY ，也可以是使用上述形式的外键，这种外键往往称之为事实的外键，也是常用的一种形式，而真正的物理的外键就是有 FOREIGN KEY用的不是特别多.

使用命令 select * from goods\G 进行查看

数字所占的字节数要远远小于字节占的字节数。现在再在goods和table_goods_brand  插入记录

root@localhost test>INSERT goods_cates VALUES (default,'route'),(default,'switch'),(default,'ethnet');

root@localhost test>INSERT table_goods_brands VALUES (default,'Huawei'),(default,'Huawei'),(default,'H3C');

再在goods数据表中写入一条记录

root@localhost test>INSERT goods(goods_name,cate_id,brand_id,goods_price) VALUES ('Laserjet Pro P16','12','4','2998');

注意这里的cate_id为12，用于并不存在为12的分类，但是有外键存在，所以也是成功的