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子句中,这样的子查询必须为其赋予别名

 

阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/weixin_38500325/article/details/81592129
个人分类: Database(数据库)
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭