子查询与连接在多表操作中的使用
插入
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;