前端学数据库之多表操作
准备工作
在上一篇博文，我们将详细数据存储到tdb_goods数据表中，将详细数据中的类别信息存储到tdb_goods_cates数据表中
mysql> use db5;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from tdb_goods;
mysql> select * from goods_cates;
接下来，我们要研究如何通过goods_cates数据表来更新tdb_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_referenceON conditional_expr
从结果中看出，tdb_goods数据表中goods_cate列中的值已经更新为goods_cates数据表中对应的cate_id的值。这样一来，用数字替代字符串，极大地节省了存储空间
两步更新
在上面的多表更新的操作中，实际上我们经过了两个步骤，先创建了一个空表，将原数据表的查询结果写入空表，再利用写入结果的表反向更新原数据表
如果使用CREATE SELECT语句将可以实现两步更新，在创建数据表同时将查询结果写入到数据表(合并了CREATE和INSERT...SELECT两个操作步骤)，再利用写入结果的表反向更新原数据表
CREATE TABLE [IF NOT EXISTS] tbl_name[(create_definition,...)]select_statement
下面来处理原数据表tdb_goods中的品牌信息，首先查询tdb_goods表的"品牌"，并分组
SELECT brand_name FROM tdb_goods GROUP BY brand_name;将品牌信息放入新表goods_brands中
CREATE TABLE tdb_goods_brands (
brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
brand_name VARCHAR(40) NOT NULL
) SELECT brand_name FROM tdb_goods GROUP BY brand_name;
再参照品牌表，更新原商品数据表
update tdb_goods INNER JOIN tdb_goods_brands ON brand_name=bramd_name SET brand_name=brand_id;这里要注意的是，两张表中，同时存在brand_name这个字段。要区分它们，需要给它们起不同的别名或在字段前面加入表名
mysql> 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;查看商品数据表的列结构，我们发现，虽然数据被修改为了数字，但数据类型仍然是字符型
mysql> show columns from tdb_goods;下面修改商品数据表中goods_cate和brand_name的列名称和列类型
alter table tdb_goods
change goods_cate cate_id smallint unsigned not null,
change brand_name brand_id smallint unsigned not null;
这是由于MySQL中不能创建自增字段
解决方法：
删除或注释掉sql_mode.重启mysql.
将my.ini（etc目录下）中的：
# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
改为：
# Set the SQL mode to strict[separator]
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
停止 使用 service 停止:service mysqld stop
重启 使用 service 启动:service mysqld restart
这样，我们已经将一个大的数据表分为小的数据表进行存储了。现在，分别在tdb_goods_cates和tdb_goods_brands表再插入几条新的记录
mysql> INSERT tdb_goods_cates(cate_name) VALUES ( '路由器' ),( '交换机' ),( '网卡' );
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> INSERT tdb_goods_brands(brand_name) VALUES ( '海尔' ),( '清华同方' ),( '神舟' );
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0在tdb_goods数据表也写入新的记录
INSERT tdb_goods(goods_name,cate_id,brand_id,goods_price) VALUES(' LaserJet Pro P1606dn 黑白激光打印机','12','4','1849');连接
通过上面的操作，已经把重复的数据分散到不同的数据表中进行存储了，尽可能的节省存储空间了。但是，显示时，却需要把原来的数据显示出来，这就需要使用下面要介绍的概念——连接
语法结构
MySQL在SELECT语句、多表更新、多表删除语句中支持连接(JOIN)操作
table_reference{[INNER | CROSS] JOIN |{LEFT|RIGHT} [OUTER] JOIN}table_referenceON conditional_expr
数据表参照(table_reference)时，数据表可以使用tbl_name AS alias_name 或tbl_name alias_name赋予别名
table_subquery可以作为子查询使用在FROM子句中，这样的子查询必须为其赋予别名
tbl_name[[AS] alias] | table_subquery [AS] alias
连接类型
连接类型主要包括内连接(INNER JOIN)、左外连接(LEFT [OUTER] JOIN)、右外连接(RIGHT [OUTER] JOIN)
在mysql中，JOIN、CROSS JOIN 和 INNER JOIN是等价的
连接条件
使用ON关键字来设定连接条件，也可以使用WHERE来代替。一般地，使用ON关键字来设定连接条件，使用WHERE关键字进行结果集记录的过滤
内连接
内连接显示左表及右表符合连接条件的记录
multilist10
下面通过内连接来查询所有商品的详细信息：
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;关于内连接，有以**意：使用内连接查找的记录在连接数据表中不存在，并且在WHERE子句中尝试一下操作：column_name IS NULL 。如果 column_name 被指定为 NOT NULL，MySQL将在找到符合连接着条件的记录后停止搜索更多的行(查找冲突)
左外连接
左外连接指显示左表的全部记录及右表符合连接条件的记录
multilist13
下面通过左外连接来查询所有商品的详细信息，原来商品表中有24件商品，现在也显示出24件，但由于商品的分类为NULL，这是因为右表的这一个分类不符合条件，所以显示为NULL
右外连接
右外连接指显示右表的全部记录及左表符合连接条件的记录
multilist15
下面通过右外连接来查询所有商品的详细信息，原来商品表中有24件商品，现在显示出26件，多出来的是符合右表但不符合左表的记录
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;关于外连接，有以下几点注意，以左外连接为例：
A LEFT JOIN B join_condition
数据表B的结果集依赖于数据表A，数据表A的结果集根据左连接条件依赖所有数据表(B表除外)
左外连接条件决定如何检索数据表B(在没有指定WHERE条件的情况下)
如果数据表A的某条记录符合WHERE条件，但是在数据表B不存在符合连接条件的记录，将生成一个所有列为空的额外的B行
多表连接
三张表以上的连接称为多表连接，原理与两张表的连接相同
下面通过内连接实现查询所有商品的详细信息
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;无限级表
select * from tdb_goods_cates;上图中是tdb_goods_cates表的记录。但实际的分类并非这10类，而是无限分类。下面来介绍无限分类的数据表的实现
无限级表至少需要三个列，一个是类型id，一个类型名称，一个是父级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);然后，写入给定数据
mysql> show columns from tdb_goods_types;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);自身连接
自身连接指同一个数据表对其自身进行连接。为作区分，需要添加别名。字表别名定义为s，父表别名定义为p
下面来查找所有分类及其父类
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;下面来查找所有分类及其子类
mysql> 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_coun 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;删除重复项
从记录中，可以看出24条记录中存在重复的项，现在要想办法把重复的项删除
首先，先查找到重复的项
mysql> SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2;然后，需要使用多表删除来实现删除操作
DELETE tbl_name[.*][,tbl_name[.*]]...FROM table_references[WHERE where_condition]
mysql> 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;
Query OK, 2 rows affected (0.02 sec)
代码来源：
https://blog.csdn.net/aaronthon/article/details/107439417