MySQL学习笔记（十）内外连接及多表连接

1. 内外连接的区别

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

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;
只会显示23条记录，而缺少了一条记录，因为商品表中cates_id为12的表与种类表不符合连接条件。

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

//可以看出有第24条记录，但是其cate_id为NULL

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;

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

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;

2. 多表连接

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;

3. 连接的几点说明

外联接：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将在找到符合连接条件的记录后停止搜索更多的行。

4. 无限级分类表设计

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

//创建商品种类表，第三个字段存储表自身的type_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
  );

//插入记录

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 * 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 p.type_id,p.type_name,s.type_name FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON p.type_id = s.parent_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 p.type_id = s.parent_id GROUP BY p.type_name ORDER BY p.type_id;

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