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;