MySQL多表操作

子查询与连接在多表操作中的使用

插入

 INSERT tdb_goods_cates(cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;

 多表更新
 UPDATE table_reference
 SET col_name1={expr|DEFAULT}
 [,col_name1={expr|DEFAULT}]...
 [WHERE where_condition]

 *table_reference:表的参照关系

 内连接，外连接，左连接，右连接

UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate=cate_name SET goods_cate=cate_id;

 有相同列名时

  UPDATE tdb_goods INNER JOIN tdb_goods_brands ON
    -> tdb_goods.brand_name=tdb_goods_brands.brand_name
    -> SET tdb_goods.brand_name=tdb_goods_brands.brand_id;
   UPDATE tdb_goods AS  g INNER JOIN tdb_goods_brands AS b ON
    -> g.brand_name=b.brand_name
    -> SET g.brand_name=b.brand_id;

 
 创建表
 CREATE TABLE [IF NOT EXISTS] table_name(
 )select_statement

  CREATE TABLE tdb_goods_brands(
    -> brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> brand_name VARCHAR(40) NOT NULL
    -> )
    -> SELECT brand_name FROM tdb_goods GROUP BY brand_name;

      CREATE TABLE tdb_goods_brands(
    -> brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> brand_name VARCHAR(40) NOT NULL
    -> )
    -> SELECT brand_name FROM tdb_goods GROUP BY brand_name;

做一下修改~~   
================================================================

    ALTER TABLE tdb_goods
    -> CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,
    -> CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;    

=================================================================


自身连接（用于无限分类数据表）
-- 无限分类的数据表设计

   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 JOIN tdb_goods_types AS p
    -> ON s.parent_id = p.type_id;    

   
多表删除重复记录

DELETE t1 FROM tdb_goods AS t1 LEFT JOIN (
    -> SELECT goods_name,goods_id 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;