SELECT goods_id,goods_name,cate_name 
FROM tdb_goods 
INNER JOIN tdb_goods_cate 
ON tdb_goods.cate_id = tdb_goods_cate.cate_id;

--將tdb_goods表中用中文存儲的goods_cate修改為tdb_goods_cates表中對應的cate_id,以節(jié)省空間
UPDATE tdb_goods 
INNER JOIN tdb_goods_cates
ON goods_cate=cate_name 
SET goods_cate=cate_id;
--tdb_goods 想要更改的表名
--tdb_goods_cates 關聯(lián)的附表
--goods_cate=cate_name 兩個表對應列的關系
--goods_cate=cate_id; 設置 值

SELECT goods_id,goods_name,b.cate_name,c.brand_name,goods_price
FROM products AS a 
INNER JOIN products_cate AS b 
ON a.goods_cate = b.cate_id
INNER JOIN products_brand AS c 
ON a.brand_name = c.brand_id;

--查找顯示父類id對應的名稱
SELECT s.type_id,s.type_name,p.type_name AS parent_name
FROM tdb_goods_types AS s 
LEFT JOIN tdb_goods_types AS p 
ON s.parent_id=p.type_id;

--查找子類id對應的名稱
SELECT p.type_id,p.type_name,s.type_name AS child_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,COUNT(s.type_name) AS child_count
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;

DELETE t1 
FROM tdb_goods AS t1 
LEFT JOIN( --選出goods_name重復的記錄
 SELECT goods_id,goods_name 
 FROM tdb_goods 
 GROUP BY goods_name --MySQL 5.7.5以上版本啟用了only_full_group_by SQL模式，select的列都要在group中,或者本身是聚合列(SUM,AVG,MAX,MIN)才行,這里沒有啟用
 HAVING COUNT(goods_name)>=2) 
AS t2 --將t1與t2進行左連接,其實這里內連接和右連接也行
ON t1.goods_name=t2.goods_name --t1和t2的連接條件
WHERE t1.goods_id>t2.goods_id; --在LEFT JOIN結果集中,選出滿足t1.goods_id>t2.goods_id的記錄