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;

unique key 与 primary key的区别是：

1. unique key 可以是为空的，但整张表的那个字段只允许有一个空。而primary key是不允许为空的。

2. unique key 可以存在多个字段，而primary key 只能一个。
   

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;

 SELECT p.type_id,p.type_name,s.type_name FROM tdb_goods_types AS p LEFT JOIN

    -> tdb_goods_types s ON s.parent_id = p.type_id;

外连接需要注意的地方

左外连接

显示左表的全部记录及右表符合连接条件的记录

右外连接

显示右表的全部记录及左表符合连接条件的记录

内连接：显示量表符合条件的记录

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

连接的语法结构

为表赋予别名

多表更新一步到位

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;

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;双表双列同名的处理

更改列名

ALTER TABLE tdb_goods

    -> CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,

    -> CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;

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

多表更新

 CREATE TABLE IF NOT EXISTS tdb_goods_cates(

    -> cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

    -> cate_name VARCHAR(40) NOT NULL

    -> );

 SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;查找商品类别

 INSERT tdb_goods_cates(cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;使用insert select 插入记录

使用[NOT ] IN 的子查询

IN= ANY

!= ALL  <>ALL和NOT IN 等效

由比较运算符引发的子查询

例如：

SELECT goods_id ,goods_name,goods_price FROM tdb_goods WHERE goods_price>=(SELECT ROUND(AVG(goods_price),2) FROM tdb_goods)；

ANY任意 、SOME 一些 ALL所有关键字

子查询

嵌套在查询内部，必须在圆括号内。

可包括多个关键字或条件

如DISTINCT GROUP BY ORDER BY LIMIT

外层查询可以是SELECT INSERT UPDATE SET DO

限制查询结果返回的数量

SELECT * FROM users LIMIT 2

LIMIT 2；指返回第三行。

LIMIT 2,2;指从第三行开始,返回2行。

即LIMIT a,b;指从第a+1行开始返回b行。

插入记录

INSERT test (username)SELECT username FROM users WHERE age>=30

对查询结果进行排序

SELECT * FROM users ORDER BY id DESC