数据准备

回顾

记录操作：
写操作：INSERT,UPDATE,DELETE
读取操作：SELECT

这章主要学习：
子查询
连接
多表删除
多表更新

数据准备：
简单的商城数据库

Paste_Image.png

tdb_good表结构：

Paste_Image.png

插入数据：略（见下载文件中的”子查询.txt“）

注意编码方式，插入的时候是以utf8的形式插入的，显示会乱码，此时使用SET NAMES gbk;设置客户端的编码方式（不会影响服务器）

子查询简介

子查询：
子查询（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,DELETE,SET或DO。

子查询中的外层查询是指SQL语句的统称，而不仅仅是SELECT（SQL：结构化查询语言）

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

拿到结果后就可以在INSERT,UPDATE,SELECT,DELETE等其他的SQL语句中使用

由比较运算符引发的子查询

子查询分类：
使用比较运算符的子查询
使用比较运算符的子查询（=、>、<、>=、<=、<>、!=、<=>）
语法结构：operand comparison_operator subquery

查找平均价格
SELECT AVG(goods_price) FROM tdb_goods;
avg()聚合函数，和i有一个返回值，类似的函数还有sum(),count(),max(),min()

SELECT ROUND(AVG(goods_price),2) FROM tdb_goods;#ROUND(AVG(goods_price),2)指的是对平均值进行四舍五入，最后保留l两位小数

SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price>=5636.36;#选择价格大于平均价格（5636.36）的商品

将上两条查询合并：
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price>=(SELECT ROUND(AVG(goods_price),2) FROM tdb_goods);#使用了比较运算符，而且使用了小括号

查询超极本类型的价格：
SELECT goods_price FROM tdb_goods WHERE goods_cate='超极本';

查询价格大于超极本价格的商品：
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price>(SELECT goods_price FROM tdb_goods WHERE goods_cate='超极本';#错误，因为子查询中返回的不是一个数据而是三条记录，WHERE中应该告诉系统大于哪个数据

用ANY、SOME或ALL修饰的比较运算符

• operand comparison_operator ANY (subquery)
• operand comparison_operator SOM(subquery)
• operand comparison_operator ALL(subquery)

ANY、SOME是等价的，只要符合其中的一个就行，ALL是要符合全部

ANY、SOME、ALL关键字：

Paste_Image.png

ANY演示：
`SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price> ANY(SELECT goods_price FROM tdb_goods WHERE goods_cate='超极本');

ALL演示：
`SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price> ALL(SELECT goods_price FROM tdb_goods WHERE goods_cate='超极本');

等于ALL演示：
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/EXISTS引发的子查询

使用[NOT] IN的子查询
语法结构：
operand comparison_operator [NOT] IN (subquery)=ANY 运算符与IN等效。
!=ALL或<>ALL运算符与NOT IN等效

演示：
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price <> ALL(SELECT goods_price FROM tdb_goods WHERE goods_cate='超极本');
等价于
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将返回TRUE；否则返回FALSE。

用的较少，子查询返回了结果EXISTS返回TRUE，否则返回FALSE

使用INSERT...SELECT插入记录

之前讲过INSERT 和 INSERT SET的区别是INSERT SET 可以使用子查询（SET 可以使XX=XX引发子查询）

tdb_goods表中有很多弊，存在这很多重复的信息，如品牌有很多索尼，分类中有很多笔记本配件，字符串比数字占的字节数多，如果记录越来越多，数据表就会越来越庞大，查找时速度就会变慢，最好的方法是使用外键，需要两张数据表。

创建分类表：

CREATE TABLE IF NOT EXISTS tdb_goods_cates(
cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
cate_name VARCHAR(40) NOT NULL
);

不用一条一条分类的插入，应该使用子查询
SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;#一共有7类，需要写入分类表中
使用INSERT...SELECT将查询写入数据表：

INSERT...SELECT
将查询结果写入数据表
INSERT [INTO] tbl_name [(col_name,...)] SELECT ...

实现：
INSERT tdb_goods_cates(cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;#不能省略插入表的列名，不用写VALUES
使用SELECT * FROM tdb_goods_cates;查看已经插入成功

但是还是没有使用外键，应该参照分类表来更新商品表

多表更新

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

CREATE...SELECT
创建数据表同时将查询结果写入到数据表
CREATE TABLE [ID NOT EXISTS] tbl_name
[(create_definition,...)]
select_statement

FROM子句中的子查询
语法结构
SELECT...FROM(subquery) [AS] name...
说明：
名称为必选项，且子查询的列名称必须唯一。

连接
MySQL在SELECT语句、多表更新、多表删除语句中支持JOIN操作

表的连接条件，第一张表+连接类型+第二张表+连接条件
语法结构
table_reference
{[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN}
table_reference
ON conditional_expr

表的参照可以给表赋予别名也可以不赋予别名

连接类型
INNER JOIN,内连接
在MySQL中，JOIN，CROSS JOIN和INNER JOIN是等价的。
LEFT [OUTER] JOIN,左外连接
RIGHT [OUTER] JOIN,右外连接

UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name SET goods_cate = cate_id;#tdb_goods表中的goods_cate已经被更新

多表更新之一步到位

以上更新操作参照别的表更新了本表，一共使用了三步：
1.创建表
2.通过使用INSERT...SELECT把记录写入新表
3.多表更新

把三步合并为一步：
可以使用CREATE...SELECT实现：
CREATE...SELECT
创建数据表同时将查询结果写入到数据表
CREATE TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
select_statement

将表中的品牌也独立出一张表，创建表的同时将查询的数据写入：

CREATE TABLE tdb_goods_brands(
brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
brand_name VARCHAR(49) NOT NULL
)
SELECT brand name FROM tdb_goods GROUP BY brand_name;

查看tdb_goods_brands表可以看到数据写入成功
还有一步应该参照品牌表更新商品表中的品牌：
UPDATE tdb_goods INNER JOIN tdb_goods_brands ON brand_name = brand_name SET brand_name = brand_id;#会报错，提示brand_name含义不明确，因为两张表中都有brand_name，系统不知道那两个brand_name是哪张表中的

要解决这个问题，只能给表起别名或者在字段前边加上表名
通常是给表起别名：
UPDATE tdb_goods AS g INNER JOIN tdb_goods_brands AS b ON g.brand_name = b.brand_name SET g.brand_name = b.brand_id;#更新成功
使用SELECT * FROM tdb_goods\G查看表中的记录，看到表中的brand_name已经被更新

此时，使用SHOW COLUMNS FROM tdb_goods;查看表结构发现表中的goods_cate和brand_name仍然是varchar类型，表中的数字代表的是字符而不是tdb_goods_cate和tdb_goods_brands中的id（数字型）

修改标结构：

ALTER TABEL tdb_goods
CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,
CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;

再使用SHOW COLUMNS FROM tdb_goods;查看表结构看到表结构已经修改成功goods_cate和brand_name修改成了数字类型

关于使用外键：不一定要使用FORIGN KEY物理外键，可以用这种外键，称为事实外键，通常较多使用事实外键，物理外键用的不多

在分类表和品牌表中插入一些记录：

INSERT tdb_goods_cates(cate_name) VALUES('路由器'),('交换机'),('网卡');
INSERT tdb_goods_brands(brand_name) VALUES('海尔'),('清华同方'),('神舟');

插入三个不同的分类和三个不同的品牌

在商品表中插入记录：
INSERT tdb_goods(goods_name,cate_id,brand_id,goods_price) VALUES(' LaserJet Pro P1606dn 黑白激光打印机','12','4','1849');#此时插入数据成功，但是有一个小错误，goods_cate写的是12，但是tdb_goods_cate表中并没有id为12的分类
把表中的记录查询出来呈现出来，存储时cate_id和brand_id存储的是其他表中的id，显示的时候就不能这样直接显示了，应该显示商品品牌和分类而不是id，这时就需要使用到连接了

连接的语法结构

连接：
MySQL在SELECT语句、多表更新语句中支持JOIN操作

三种连接：内连接，左外连接，右外连接
A表+连接类型+B表+连接条件
语法结构
table_reference
{[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN}
table_reference
ON conditional_expr

数据表参照：
table_reference
tbl_name [[AS] alias] | table_subquery [AS] alias
数据表可以使用tbl_name AS alias_name或tbl_name alias_name赋予别名。
table_subquery可以作为子查询使用在FROM子句中，这样的子查询必须为其赋予别名。

内连接INNER JOIN

连接类型：
INNER JOIN,内连接
在MySQL中，JOIN，CROSS JOIN和INNER JOIN是等价的。
LEFT [OUTER] JOIN,左外连接
RIGHT [OUTER] JOIN,右外连接

连接条件:
使用ON关键字来设定连接条件，也可以使用WHERE来代替。
通常使用ON关键字来设定连接条件，
使用WHERE关键字进行结果集记录的过滤。

内链接：显示左表及右表符合连接条件的记录

内连接

实例：
SELECT goods_id,goods_name,cate_name FROM tdb_goods INNER JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate.id;
可以看到22条记录，并没有刚刚添加的第23条记录，因为第23条记录不符合连接条件，刚才添加的cate_id是12，在tdb_goods_cate表中并不存在id为12的记录，而且刚刚新添加的几个分类也没有显示出来，这就是内连接（两张表都会有的才会显示出来），仅显示符合连接条件的记录

外连接OUTER JOIN

左外连接：显示左表的全部记录及右表符合连接条件的记录

左外连接

演示：SELECT goods_id,goods_name,cate_name FROM tdb_goods LEFT JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate.id;#OUTER可以写，也可以不写
得到23条记录，但是第23条记录的cate_name为空,左外连接指的是左表中的全部和右表中符合条件的，如果右表中没有符合条件的会显示为NULL

右外连接：显示右表的全部记录及左表符合连接条件的记录

右外连接

演示：SELECT goods_id,goods_name,cate_name FROM tdb_goods RIGHT JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate.id;#OUTER可以写，也可以不写
得到25条记录，没有了那个cate_id为12的记录，又多了三条分类的记录，右外连接指的是显示右表中的全部和左表中符合连接条件的记录

这三种连接中内连接用的想对较多

多表连接

商品表中存在商品分类和品牌
实现三张表的连接：
演示：

SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g 
INNER JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id 
INNER JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id;

可以看到这是有恢复了我们最初原始的结果，不一样的是这次是通过多张表的连接实现的，以前是通过一张表查询出来的

其实表的连接就像是外键的逆向操作，外键把表分开存储，连接把多张表连接起来查询

关于连接的几点说明

A LEFT JOIN B join_condition

• 数据表B的结果集依赖数据表A。
• 数据表A的结果集根据左连接条件依赖所有数据表（B表除外）。
• 左外连接条件决定如何检索数据表B（在没有指定WHERE条件的情况下）。
• 如果数据表A的某条记录符合WHERE条件，但是在数据表B不存在符合连接条件的记录，将生成一个所有列为空的额外的B行。

查找到的结果为NULL但是含有约束NOT NULL的情况：
如果使用内连接查找的记录在连接数据表中不存在，并且在WHERE子句中尝试以下操作：col_namd IS NULL时，如果col_name被定义为NOT NULL，MySQL将在找到符合连接执行条件的记录后停止搜索更多的行。

无限极分类表设计

查看tdb_goods_cates表的记录，这些分类远远达不到现实中分类的要求，很多网站中，这些分类有很多级分类，一级分类、二级分类、三级分类……这种分类就是无限分类，数据表应该怎样设计，可以设计很多张表，随着分类的增多，表的数目也会逐渐增多，查找起来就不方便了，所以，一般都采用在表中增加父分类的id字段实现：

实例：

CREATE TABLE tdb_goods_types(
type_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
type_name VARCHAR(20) NOT NULL,
parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0
);#parent_id为父分类的id，为0表示没有父分类，为一级分类

然后插入数据：

  INSERT tdb_goods_types(type_name,parent_id) VALUES('家用电器',DEFAULT);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑、办公',DEFAULT);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('大家电',1);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('生活电器',1);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('平板电视',3);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('空调',3);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('电风扇',4);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('饮水机',4);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑整机',2);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑配件',2);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('笔记本',9);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('超级本',9);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('游戏本',9);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('CPU',10);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('主机',10);

那么问题来了：如何查询这张表

可以通过自身连接查询：
自身连接：同一个数据表对其自身进行连接

示例演示：
一张表做自身连接必须要起别名，要不就分不清这两个相同名称的字段从哪来的了
想象一下有两张相同的表，左边是父表，右边是子表
SELECT s.type_id,s.type_name,p.type_name FROM tdb_goods_types AS s LEFT JOIN tdb_goods_types AS p ON s.parent_id = p.type_id;#就可以查到子类的id，子类的名字以及父类的名字

查找子类，父类以及父类下的子类：
左边是子表，右边是父表
SELECT p.type_id,p.type_name,s.typename FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id;
含有重复的父类，使用GROUP BY分组：
SELECT p.type_id,p.type_name,s.typename FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id GROUP BY p.type_name ;
可以看到只有15个分类了，按照id排序：

SELECT p.type_id,p.type_name,s.typename FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id GROUP BY p.type_name ORDER BY p.type_id;

不要子类的名字，需要子类的数目：

SELECT p.type_id,p.type_name,count(s.typename) AS child_count FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id GROUP BY p.type_name ORDER BY p.type_id;

就可以看到子类的数量了

多表删除

DELETE tbl_name[.] [,tbl_name[.]]...
FROM table_references
[WHERE where_condition]

使用SELECT * FROM tdb_goods;查看表中的记录，看到第18、19和第21、22条记录是重复的，这是，想要把重复的记录删除，保留id较小的记录。
可以通过多表删除实现，也就是采用一张表模拟两张表实现
演示：
SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name;#从23条记录中得到了21个商品，因为有些记录是相同的

我们只想要相同商品名称超过两个以上的记录
SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING COUNT(goods_name) > 1;#得到重复商品的记录，这就是我们将要删除或者要保留的记录
可以通过这张表来删除原表中的数据：
DELETE t1 FROM tdb_goods AS t1 LEFT JOIN (SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2) AS t2 ON t1.goods_name=t2.goods_name WHERE t1.goods_id > t2.goods_id;