MySQL基础学习记录（三）

1.连接 join

语法结构

1.1内连接inner join

1.2左外连接

1.3右外连接

1.4 多表连接

select goods_id ，goods_name，cate_name，brand_name，goods_price from tdb_goods AS g

  inner join tdb_goods_cate as c on g.cate_id = c. cate_id,

  innner join tdb_goods_brands AS b on g.brand_name= b.brand_name;

可以看到与一开始单表的信息一样

但是现在这张表是3张表连接后实现的，而且原表真正存的cate和brand_name都已经是后两张表的id值了

1.5几点说明

1.6无限极分类表设计

自身连接

example：

查找所有类的父类

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;

查找父类下的子类

2.多表删除

example

删除单表内的重复记录，使用单表模拟多表

delete t1 from tdb_goods as t1 left join (select goods_id, goods_name from tdb_goods group by

goods_name having count(goods_name)>=2) as t2 on t1.goods_name

=t2.goods_name where t1.goods_id>t2.goods_id;