MySQL 连接

连接

table_reference

{[INNER | CROSS] JOIN | {LEFT |RIGHT} [OUTER] JOIN}

table_reference

ON conditional_expr

//多表连接

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.brands_id\G;

连接类型

MySQL中，JOIN，CROSS JOIN 和INNER JOIN是等价的

• INNER JOIN ,内连接:显示左、右表符合连接条件的记录
• LEFT [OUTER] JOIN ,左外连接:显示左表全部、右表符合条件的记录
• RIGHT [OUTER] JOIN ,右外连接:显示右表全部、左表符合条件的记录

连接条件

• 使用ON设定连接条件
• 使用WHERE进行结果的过滤

 

//查询所有商品的详细信息

内连接

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;

左外连接

SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g

LEFT JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id

LEFT JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id\G;

右外连接

SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g

RIGHT JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id

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

自身连接：对自己进行连接

//查询目前父类名字、子类id名字（一级查询）

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;

数据表参照

table_reference

tbl_name [[AS] alias] | table_subquery [AS] alias

数据表可以使用赋予别名

• tbl_name AS alias_name
• tbl_name alias_name

table_subquery可以作为子查询使用在FROM子句中,这样的子查询必须为其赋予别名