MySQL--操作数据表5：内连接，外连接，多表连接，无限级分类表设计

INNER JOIN // 内连接
LEFT [OUTER] JOIN // 左外连接
RIGHT [OUTER] JOIN // 右外连接

用关键词 ON 来设定连接条件，也可使用 WHERE 来代替

通常使用关键字 ON 来设定连接条件

使用关键字 WHERE 关键字进行结果集记录的过滤

内、外连接查询：

SELECT goods_id,goods_name,cate_name FROM tdb_goods
INNER/LEFT/RIGHT JOIN tdb_goods_cates ON tdb_goods.cate_id=tdb_goods_cates.cate_id;

多表连接查询：

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;

无限级分类表设计：（用自身连接实现）

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

-- 查找所有分类及其父类

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