MYSQL 第五章 子查询，多表更新，外连接，左连接，右连接，内连接，多表连接，无限级分类表设计，多表删除

5-1数据准备（就是前面博客里面插入的数据）

记录操作 INSERT UPDATE DELETE (前三个为记录的增删改划分为一类，称为记录的写操作)SELECT（记录的读取操作）
SET NAMES gbk;

子查询  连接

DROP TABLE tdb_goods;删除这个数据表

5-2 子查询
子查询（Subquery）是指出现在其他sql语句内的SELECT子句。
例如：SELECT * FROM t1 WHERE col1=(SELECT col2 FROM t2);
其中 SELECT * FROM t1称为外层子查询，SELECT col2 FROM t2称为子查询SubQuery

子查询指嵌套在查询内部，且必须始终出现在圆括号内。
子查询可以包含多个关键字或条件，如DISTINCT,GROUP BY,ORDER BY,LIMIT,函数等。
子查询的外层查询可以是：SELECT,INSERT,UPDATE,SET或DO.
子查询可以返回标量、一行、一列或子查询。

5-3 由比较运算符引发的子查询(第一种)
使用比较运算符的子查询
=，>,<,>=,<=,<>,!=,<=>
语法结构 operand comparison_operator subquery
对价格求平均值：SELECT AVG(goods_price) FROM tdb_goods;
SELECT ROUND(AVG(goods_price),2) FROM tdb_goods;  对平均值的结果进行四舍五入，并且保留到小数点后面的两位
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price>=5521.74;看看有几个大于平均值的
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price>=(SELECT ROUND(AVG(goods_price),2) FROM tdb_goods);
这个跟上面效果一样，这里用到了子查询，因为用到了 = 即等号，等号是典型的子查询，而且这里子查询括到了括号内
 go看看类型为超极本的价格
SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本';
SELECT * FROM tdb_goods WHERE goods_cate='超级本'\G;
接下来想看看哪些商品的价格大于这些超极本的价格

SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price >(SELECT goods_price FROM tdb_goods WHERE goods_cate='超级本');
这里会显示错误，显示子返回大于一行，即超级本有好几个，价格也有好几个，不知道大于这几个价格中的哪一个
在 > 即大于号后面加上ANY

SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price >ANY (SELECT goods_price FROM tdb_goods WHERE goods_cate='超级本');即表示大于超极本中的价格最低的一个

 把ANY换成ALL       

SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price >ALL(SELECT goods_price FROM tdb_goods WHERE goods_cate='超级本');即表示大于超极s本中的价格最高的一个

SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price <ANY(SELECT goods_price FROM tdb_goods WHERE goods_cate='超级本');小于ANY，即需要小于超极本中的价格最大的

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='超级本');指的是等于超极本价格的任意一个都行

SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price !=ALL(SELECT goods_price FROM tdb_goods WHERE goods_cate='超级本');指的是不等于超极本的价格就行


5-4 使用IN或者NOT IN的子查询（第二种）
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='超级本');

第三种 EXISTS或者NOT EXISTS的子查询
如果子查询返回任何行，EXISTS将返回TRUE；否则为FALSE
这个用的比较少


5-5使用INSERT SELECT 插入数据
创建一张新表
CREATE TABLE 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;

将查询的结果写入到新创建的数据表中
SELECT * FROM tdb_goods_cates;先看看新创建的表，里面没有内容
SHOW COLUMNS FROM tdb_goods_cates;
INSERT tdb_goods_cates(cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;
SELECT * FROM tdb_goods_cates;
要参照分类表tdb_goods_cates去更新商品表tdb_goods ，这是多表的更新

5-6多表更新
连接类型  INNNER 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; SET后面表示更新的值也就是把tdb_goods中的goods_cate都变成了tdb_goods_cates中的cate_id的1到7
SELECT * FROM tdb_goods\G; 可以看到品牌的类型cate_name都变成了1到6 之间的数字
这就是多表的更新，它指的是参照别的表来进行本表的更新。

第一步  先创建一个表  
第二步  通过INSERT SELECT把记录写入到表中
第三部  多表的更新


5-7多表更新之一步到位  CREATE SELECT
通过CREATE SELECT ,这是在创建数据表的同时将查询结果写入到数据表
SELECT brand_name FROM tdb_goods GROUP BY brand_name;
CREATE TABLE tdb_goods_brands(brand_id SMALLINT PRIMARY KEY AUTO_INCREMENT,brand_name VARCHAR(40) NOT NULL) SELECT brand_name FROM tdb_goods GROUP BY brand_name;
参照品牌表来更新商品表。
SHOW COLUMNS FROM tdb_goods;
SHOW COLUMNS FROM tdb_goods_brands;
SELECT * FROM tdb_goods_brands;
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;
SHOW COLUMNS FROM tdb_goods;可以看到tdb_name和tdb_cate仍然是字符型而不是被改成的整型数字

ALTER TABLE 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;
SELECT * FROM tdb_goods\G; 重点看下cate_id和brand_id
-- 分别在tdb_goods_cates和tdb_goods_brands表插入记录


 INSERT tdb_goods_cates(cate_name) VALUES('路由器'),('交换机'),('网卡');

 INSERT tdb_goods_brands(brand_name) VALUES('海尔'),('清华同方'),('神舟');
- 在tdb_goods数据表写入任意记录

   INSERT tdb_goods(goods_name,cate_id,brand_id,goods_price) VALUES(' LaserJet Pro P1606dn 黑白激光打印机','12','4','1849');
   SELECT * FROM tdb_goods_cates; 
   SELECT * FROM tdb_goods\G;可以发现并不存在第12个cate_id分类，但是没有物理的外键（FOREIGN KEY）所以说这条记录写入是成功的，只要符合INSERT的语法结构就行了



5-8 连接的语法结构
mysql在SELECT语句、多表更新、多表删除语句中支持JOIN操作。

语法结构
table_reference {[INNER|CROSS] JOIN|{LEFT|RIGHT} [OUTER] JOIN} table reference ON conditonal_exor
基本结构就是   A表  连接类型  B表  两张表的连接条件
数据表参照
table_reference
tbl_name [[AS] alias] | table_subquery [AS] alias
数据表可以使用tbl_name AS alias_name或tbl_name alias_name赋予别名。
table_subquery可以作为子查询使用在FORM子句中，这样的子查询必须为其赋予别名。


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

使用ON 关键字来设定连接条件，也可以使用WHERE来代替。
通常使用 ON关键字来设定连接条件，使用WHERE关键字进行结果集记录的过滤。
WHEREQ子句作用于基本表或视图。HAVING短语作用于组，在GROUP IN 后面 ，从中选择满足条件的组。

内连接  显示左表和右表符合条件的记录，连接有交叉的圆的公共部分
SELECT * FROM tdb_goods;
SELECT * FROM tdb_goods_cates;
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\G; 
应该是24条但是结果是23条，为什么，因为它不符合连接的条件，因为12不符合条件
SELECT * FROM tdb_goods_cates;里面有路由器，交换机，网卡但是他也没有呈现出来，因为这三个在商品清单中是没有的

这就是内连接，仅显示符合条件的记录

5-10外连接 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;
可以看到应该能得到24条，但是第24条商品的分类为NULL
把INNER改成了LEFT,它表示左表的全部和右表中符合条件的，如果右表中没有符合条件的，那么它会显示为空


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\G;
把LEFT改成了RIGHT,它显示有25个或者26个之多，他表示右表的全部和左表中符合条件的


价格斜杠和G看看为什么这么多
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\G;


这三种连接中内连接用的相对而言比较的多一些



5-11多表连接
SHOW COLUMNS FROM tdb_goods;
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\G;
发现又恢复到了以前最最原始的结果，但是这个结果是通过多张表的连接来实现的，而以前是通过纯粹的单张数据表的查询来实现的


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

如果使用内连接查找的记录在连接数据库表中不存在，并且在WHERE子句中尝试以下操作：col_name IS NULL时，如果col_name被定义为NOT NULL,MYSQL将在找到符合连接条件的记录后

停止搜索更多的行。


5-13无限级分类表设计
SHOW COLUMNS FROM tdb_goods_cates;
SELECT * FROM tdb_goods_cates\G;

-- 无限分类的数据表设计

   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
  ); 

 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);

SHOW COLUMNS  FROM tdb_goods_types;
SELECT * FROM tdb_goods_types;

自身连接
同一数据表对其自身进行连接



- 查找所有分类及其父类（右边是子类，左边是父类）

   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;


  SELECT * FROM tdb_goods_types;


-- 查找所有分类及其子类（右边是父表，左边是子表）


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


-- 查找所有分类及其子类的数目


  SELECT p.type_id,p.type_name,count(s.type_name) AS children_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;

5-14多表删除
SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name;


SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name having count(goods_name)>=2;


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;


SELECT * FROM tdb_goods\G;