又是礼拜六了-MySQL特训

  hi

又是周二,又是磨蹭个一早上~午饭后初叶吧

1、MySQL

—–子查询与连接(三)—–

—-使用INSERT…SELECT插入记录

–数据库内容的英文版本

出于自身的WAMP中的MYSQL控制台突显汉语无力,在转移进度核有气无力,遂改变战术,把数据变成了全英文的,现分享出去

— 创设数据表

CREATE TABLE IF NOT EXISTS goods(
goods_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
goods_name VARCHAR(150) NOT NULL,
goods_cate VARCHAR(40) NOT NULL,
brand_name VARCHAR(40) NOT NULL,
goods_price DECIMAL(15,3) UNSIGNED NOT NULL DEFAULT 0,
is_show BOOLEAN NOT NULL DEFAULT 1,
is_saleoff BOOLEAN NOT NULL DEFAULT 0
);

— 写入记录

INSERT goods
(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)
VALUES(‘R510VC 15.6Inches
Laptop’,’Laptop’,’ASUS’,’3399′,DEFAULT,DEFAULT);

INSERT goods
(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)
VALUES(‘Y400N 14.0Inches
Laptop’,’Laptop’,’Lenovo’,’4899′,DEFAULT,DEFAULT);

INSERT goods
(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)
VALUES(‘G150TH 15.6Inches
Gamebook’,’Gamebook’,’ThunderGod’,’8499′,DEFAULT,DEFAULT);

INSERT goods
(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)
VALUES(‘X550CC 15.6Inches
Laptop’,’Laptop’,’ASUS’,’2799′,DEFAULT,DEFAULT);

INSERT goods
(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)
VALUES(‘X240(20ALA0EYCD)
12.5InchesUltrabook’,’Ultrabook’,’Lenovo’,’4999′,DEFAULT,DEFAULT);

INSERT goods
(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)
VALUES(‘U330P 13.3Inches
Ultrabook’,’Ultrabook’,’Lenovo’,’4299′,DEFAULT,DEFAULT);

INSERT goods
(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)
VALUES(‘SVP13226SCB 13.3Inches Touch
Ultrabook’,’Ultrabook’,’Sony’,’7999′,DEFAULT,DEFAULT);

INSERT goods
(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)
VALUES(‘iPad mini MD531CH/A 7.9Inches
Tablet’,’Tablet’,’Apple’,’1998′,DEFAULT,DEFAULT);

INSERT goods
(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)
VALUES(‘iPad Air MD788CH/A 9.7Inches Tablet (16G WiFi
Vision)’,’Tablet’,’Apple’,’3388′,DEFAULT,DEFAULT);

INSERT goods
(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)
VALUES(‘ iPad mini ME279CH/A with Retina screen 7.9InchesTablet (16G
WiFi Vision)’,’Tablet’,’Apple’,’2788′,DEFAULT,DEFAULT);

INSERT goods
(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)
VALUES(‘IdeaCentre C340 20Inches All-in-one
‘,’Desktop’,’Lenovo’,’3499′,DEFAULT,DEFAULT);

INSERT goods
(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)
VALUES(‘Vostro 3800-R1206
desktop’,’Desktop’,’Dell’,’2899′,DEFAULT,DEFAULT);

INSERT goods
(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)
VALUES(‘iMac ME086CH/A 21.5Inches
All-in-one’,’Desktop’,’Apple’,’9188′,DEFAULT,DEFAULT);

INSERT goods
(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)
VALUES(‘AT7-7414LP Desktop (i5-3450 4G 500G 2GIndependent graphics card
DVD Keyboard&Mouse Linux )’,’Desktop’,’Acer’,’3699′,DEFAULT,DEFAULT);

INSERT goods
(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)
VALUES(‘Z220SFF F4F06PAWork station’,’server/Work
station’,’HP’,’4288′,DEFAULT,DEFAULT);

INSERT goods
(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)
VALUES(‘PowerEdge T110 IIserver’,’server/Work
station’,’Dell’,’5388′,DEFAULT,DEFAULT);

INSERT goods
(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)
VALUES(‘Mac Pro MD878CH/A Professional Desktop’,’server/Work
station’,’Apple’,’28888′,DEFAULT,DEFAULT);

INSERT goods
(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)
VALUES(‘ HMZ-T3W Headset Display Device’,’Laptop
accessory’,’Sony’,’6999′,DEFAULT,DEFAULT);

INSERT goods
(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)
VALUES(‘Business
Backpage’,’Laptopaccessory’,’Sony’,’99’,DEFAULT,DEFAULT);

INSERT goods
(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)
VALUES(‘X3250 M4 server 2583i14′,’server/Work
station’,’IBM’,’6888′,DEFAULT,DEFAULT);

INSERT goods
(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)
VALUES(‘Xuanlong Laptop Radiator’,’Laptop
accessory’,’Windgod’,”,DEFAULT,DEFAULT);

INSERT goods
(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)
VALUES(‘ HMZ-T3W Headset Display Device’,’Laptop
accessory’,’Sony’,’6999′,DEFAULT,DEFAULT);

INSERT goods
(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)
VALUES(‘Business Backpage’,’Laptop
accessory’,’Sony’,’99’,DEFAULT,DEFAULT);

—INSERT…SELECT

INSERT [INTO] tbl_name [(col_name)] SELECT

动用的时候就像是此,成立一个新的表

CREATE TABLE IF NOT EXISTS cates(

cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

cate_name VARCHAR(40) NOT NULL

);

接下来把原数据表中的东西有拔取性的插入进来

INSERT INTO CATES(CATE_NAME) SELECT GOODS_CATE FROM GOODS GROUP BY
GOODS_CATE;

即使急需根据目录表cates,来更新我们的参数表——多表更新

—-多表更新

–多步

UPDATE table_ref SET col_name=… …

个中,ref是表的参阅关系

此地有连日关系,INNER JOIN LEFT JOIN 等

最不难易行的是INNER,内连接

mysql> UPDATE GOODS INNER JOIN CATES ON GOODS_CATE=CATE_NAME
-> SET GOODS_CATE=CATE_ID;

(请见谅自己不想打小写的了。。)

解释一下:更新goods这张表,内连接cates那张表,在怎样标准吧——goods_cate=cate_name,设定的值是怎么着吗,是cates那张表中的cate_id

mysql> SELECT * FROM GOODS\G; 验证一下

–一步

CREATE…SELECT…

即创制表的同时写入连接的数额

此处的例子是:对品牌做连接更新

mysql> SELECT BRAND_NAME FROM GOODS GROUP BY BRAND_NAME;

mysql> CREATE TABLE IF NOT EXISTS brands(
-> brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> brand_name VARCHAR(40) NOT NULL
-> )
-> SELECT BRAND_NAME FROM GOODS GROUP BY BRAND_NAME;

就足以省去INSERT的操作了

只是此时更新要出错

mysql> UPDATE GOODS INNER JOIN BRANDS ON BRAND_NAME=BRAND_NAME
-> SET BRAND_NAME=BRAND_ID;
ERROR 1052 (23000): Champ: ‘BRAND_NAME’ dans field list est ambigu

因为名字一样,要改字段,或者起别名AS

mysql> UPDATE GOODS AS G INNER JOIN BRANDS AS B ON
G.BRAND_NAME=B.BRAND_NAME
-> SET G.BRAND_NAME=BRAND_ID;

尽管如此达成了外部的换代,不过,比如说goods_cate的品种或者varchar,而我辈实际上希望主表中,它是int类型的。这时候由于需求修改字段名以及数据类型,用ALTER
CHANGE

mysql> ALTER TABLE GOODS
-> CHANGE GOODS_CATE CATE_ID SMALLINT UNSIGNED NOT NULL,
-> CHANGE BRAND_NAME BRAND_ID SMALLINT UNSIGNED NOT NULL;

那种外键称为事实外键,比foreign key的情理外键还要多

—-连接

出于大家贯彻了多表的仓储,不过出于显得等急需,要求八个表联动,需要动用连接

数据表参照ref:tbl_name [[AS] alias] | table_subquery [AS] alias

—INNER JOIN

拔取ON来设定连接条件,WHERE一般做进一步的筛选

内连接,仅显示符合连接条件的笔录

 SELECT GOODS_ID,GOODS_NAME,CATE_NAME FROM GOODS INNER JOIN CATES ON
GOODS.CATE_ID=CATES.CATE_ID;

翻译一下:拔取goods表中的goods_id,goods_name,cate_name字段,不过来得的时候,必要内连接cates表(因为我们不想突显的是项目标数字代表),条件是当双方的cate_id字段相等;那里由于cate_id没有给别名,所以写成表名.cate_id

会发觉,那一个内连接的显得,只显示了on条件的结果

—外连接

外接连又分为左外和右外

LEFT JOIN突显左表的一切记录和右表符合链接条件的笔录,例子如下

+———-+——————————————————————————————–+———————+
| GOODS_ID | GOODS_NAME | CATE_NAME |
+———-+——————————————————————————————–+———————+
| 1 | R510VC 15.6Inches Laptop | Laptop |
| 2 | Y400N 14.0Inches Laptop | Laptop |
| 3 | G150TH 15.6Inches Gamebook | Gamebook |
| 4 | X550CC 15.6Inches Laptop | Laptop |
| 5 | X240(20ALA0EYCD) 12.5InchesUltrabook | Ultrabook |
| 6 | U330P 13.3Inches Ultrabook | Ultrabook |
| 7 | SVP13226SCB 13.3Inches Touch Ultrabook | Ultrabook |
| 8 | iPad mini MD531CH/A 7.9Inches Tablet | Tablet |
| 9 | iPad Air MD788CH/A 9.7Inches Tablet ?16G WiFi Vision? | Tablet |
| 10 | iPad mini ME279CH/A with Retina screen 7.9InchesTablet ?16G WiFi
Vision? | Tablet |
| 11 | IdeaCentre C340 20Inches All-in-one | Desktop |
| 12 | Vostro 3800-R1206 desktop | Desktop |
| 13 | iMac ME086CH/A 21.5Inches All-in-one | Desktop |
| 14 | AT7-7414LP Desktop ?i5-3450 4G 500G 2GIndependent graphics card
DVD Keyboard&Mouse Linux ? | Desktop |
| 15 | Z220SFF F4F06PAWork station | server/Work station |
| 16 | PowerEdge T110 IIserver | server/Work station |
| 17 | Mac Pro MD878CH/A Professional Desktop | server/Work station |
| 18 | HMZ-T3W Headset Display Device | Laptop accessory |
| 19 | Business Backpage | Laptop accessory |
| 20 | X3250 M4 server 2583i14 | server/Work station |
| 21 | Xuanlong Laptop Radiator | Laptop accessory |
| 22 | HMZ-T3W Headset Display Device | Laptop accessory |
| 23 | Business Backpage | Laptop accessory |
+———-+——————————————————————————————–+———————+
23 rows in set (0.00 sec)

mysql> SELECT GOODS_ID,GOODS_NAME,CATE_NAME FROM GOODS LEFT JOIN
CATES ON GOODS.CATE_ID=CATES.CATE_ID;
+———-+——————————————————————————————–+———————+
| GOODS_ID | GOODS_NAME | CATE_NAME |
+———-+——————————————————————————————–+———————+
| 1 | R510VC 15.6Inches Laptop | Laptop |
| 2 | Y400N 14.0Inches Laptop | Laptop |
| 3 | G150TH 15.6Inches Gamebook | Gamebook |
| 4 | X550CC 15.6Inches Laptop | Laptop |
| 5 | X240(20ALA0EYCD) 12.5InchesUltrabook | Ultrabook |
| 6 | U330P 13.3Inches Ultrabook | Ultrabook |
| 7 | SVP13226SCB 13.3Inches Touch Ultrabook | Ultrabook |
| 8 | iPad mini MD531CH/A 7.9Inches Tablet | Tablet |
| 9 | iPad Air MD788CH/A 9.7Inches Tablet ?16G WiFi Vision? | Tablet |
| 10 | iPad mini ME279CH/A with Retina screen 7.9InchesTablet ?16G WiFi
Vision? | Tablet |
| 11 | IdeaCentre C340 20Inches All-in-one | Desktop |
| 12 | Vostro 3800-R1206 desktop | Desktop |
| 13 | iMac ME086CH/A 21.5Inches All-in-one | Desktop |
| 14 | AT7-7414LP Desktop ?i5-3450 4G 500G 2GIndependent graphics card
DVD Keyboard&Mouse Linux ? | Desktop |
| 15 | Z220SFF F4F06PAWork station | server/Work station |
| 16 | PowerEdge T110 IIserver | server/Work station |
| 17 | Mac Pro MD878CH/A Professional Desktop | server/Work station |
| 18 | HMZ-T3W Headset Display Device | Laptop accessory |
| 19 | Business Backpage | Laptop accessory |
| 20 | X3250 M4 server 2583i14 | server/Work station |
| 21 | Xuanlong Laptop Radiator | Laptop accessory |
| 22 | HMZ-T3W Headset Display Device | Laptop accessory |
| 23 | Business Backpage | Laptop accessory |
| 24 | LaserJet Pro P1606dn Black&White blazer printer | NULL |
+———-+——————————————————————————————–+———————+
24 rows in set (0.00 sec)

 注意最后一个记录的类名是null

RIGHT JOIN是那般的

mysql> SELECT GOODS_ID,GOODS_NAME,CATE_NAME FROM GOODS RIGHT JOIN
CATES ON GOODS.CATE_ID=CATES.CATE_ID;
+———-+——————————————————————————————–+———————+
| GOODS_ID | GOODS_NAME | CATE_NAME |
+———-+——————————————————————————————–+———————+
| 1 | R510VC 15.6Inches Laptop | Laptop |
| 2 | Y400N 14.0Inches Laptop | Laptop |
| 3 | G150TH 15.6Inches Gamebook | Gamebook |
| 4 | X550CC 15.6Inches Laptop | Laptop |
| 5 | X240(20ALA0EYCD) 12.5InchesUltrabook | Ultrabook |
| 6 | U330P 13.3Inches Ultrabook | Ultrabook |
| 7 | SVP13226SCB 13.3Inches Touch Ultrabook | Ultrabook |
| 8 | iPad mini MD531CH/A 7.9Inches Tablet | Tablet |
| 9 | iPad Air MD788CH/A 9.7Inches Tablet ?16G WiFi Vision? | Tablet |
| 10 | iPad mini ME279CH/A with Retina screen 7.9InchesTablet ?16G WiFi
Vision? | Tablet |
| 11 | IdeaCentre C340 20Inches All-in-one | Desktop |
| 12 | Vostro 3800-R1206 desktop | Desktop |
| 13 | iMac ME086CH/A 21.5Inches All-in-one | Desktop |
| 14 | AT7-7414LP Desktop ?i5-3450 4G 500G 2GIndependent graphics card
DVD Keyboard&Mouse Linux ? | Desktop |
| 15 | Z220SFF F4F06PAWork station | server/Work station |
| 16 | PowerEdge T110 IIserver | server/Work station |
| 17 | Mac Pro MD878CH/A Professional Desktop | server/Work station |
| 18 | HMZ-T3W Headset Display Device | Laptop accessory |
| 19 | Business Backpage | Laptop accessory |
| 20 | X3250 M4 server 2583i14 | server/Work station |
| 21 | Xuanlong Laptop Radiator | Laptop accessory |
| 22 | HMZ-T3W Headset Display Device | Laptop accessory |
| 23 | Business Backpage | Laptop accessory |
| NULL | NULL | Adaptor |
| NULL | NULL | Exchanger |
| NULL | NULL | Netcard |
+———-+——————————————————————————————–+———————+
26 rows in set (0.00 sec)

—-多表连接

mysql> SELECT
GOODS_ID,GOODS_NAME,CATE_NAME,BRAND_NAME,GOODS_PRICE FROM GOODS AS
G
-> INNER JOIN CATES AS C ON G.CATE_ID=C.CATE_ID
-> INNER JOIN BRANDS AS B ON G.BRAND_ID=B.BRAND_ID;

每连接一个表就需求一个JOIN,注意起别名给数据表!

那会儿的显得纵然跟一开端一样,但已毕分裂,意义大不同

—-无限级分类表设计

一般实际中会现身无限级数据表,不断往下分

举个例子

CREATE TABLE goods_types(
type_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
type_name VARCHAR(20) NOT NULL,
parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0
);

INSERT goods_types(type_name,parent_id) VALUES(‘Home
Electronics’,DEFAULT);
INSERT goods_types(type_name,parent_id)
VALUES(‘PC、Office’,DEFAULT);
INSERT goods_types(type_name,parent_id) VALUES(‘Appliances’,1);
INSERT goods_types(type_name,parent_id) VALUES(‘Life
Appliances’,1);
INSERT goods_types(type_name,parent_id) VALUES(‘Tablet TV’,3);
INSERT goods_types(type_name,parent_id) VALUES(‘Air
Conditioner’,3);
INSERT goods_types(type_name,parent_id) VALUES(‘Electrical Fan’,4);
INSERT goods_types(type_name,parent_id) VALUES(‘Water
Dispenser’,4);
INSERT goods_types(type_name,parent_id) VALUES(‘Complete
Machine’,2);
INSERT goods_types(type_name,parent_id) VALUES(‘Accessory’,2);
INSERT goods_types(type_name,parent_id) VALUES(‘Laptop’,9);
INSERT goods_types(type_name,parent_id) VALUES(‘Ultrabook’,9);
INSERT goods_types(type_name,parent_id) VALUES(‘Gamebook’,9);
INSERT goods_types(type_name,parent_id) VALUES(‘CPU’,10);
INSERT goods_types(type_name,parent_id) VALUES(‘Host Computer’,10);

mysql> SELECT * FROM GOODS_TYPES;
+———+——————+———–+
| type_id | type_name | parent_id |
+———+——————+———–+
| 1 | Home Electronics | 0 |
| 2 | PC?Office | 0 |
| 3 | Appliances | 1 |
| 4 | Life Appliances | 1 |
| 5 | Tablet TV | 3 |
| 6 | Air Conditioner | 3 |
| 7 | Electrical Fan | 4 |
| 8 | Water Dispenser | 4 |
| 9 | Complete Machine | 2 |
| 10 | Accessory | 2 |
| 11 | Laptop | 9 |
| 12 | Ultrabook | 9 |
| 13 | Gamebook | 9 |
| 14 | CPU | 10 |
| 15 | Host Computer | 10 |
+———+——————+———–+

注意parent_id,是指改节点的级别,比如0,表示home
electronics没有父节点,是个一级节点

追寻的兑现,是透过自我连接来完成的

—自身连接

举个例证,突显所有种类的父类是哪些

此地,自身的总是,可以设想左边有一张一模一样的表(左侧也足以,有些东西稍微变一下就行)

mysql> SELECT S.TYPE_ID,S.TYPE_NAME,P.TYPE_NAME FROM GOODS_TYPES
AS S LEFT JOIN GOODS_TYPES AS P
-> ON S.PARENT_ID=P.TYPE_ID;
+———+——————+——————+
| TYPE_ID | TYPE_NAME | TYPE_NAME |
+———+——————+——————+
| 1 | Home Electronics | NULL |
| 2 | PC?Office | NULL |
| 3 | Appliances | Home Electronics |
| 4 | Life Appliances | Home Electronics |
| 5 | Tablet TV | Appliances |
| 6 | Air Conditioner | Appliances |
| 7 | Electrical Fan | Life Appliances |
| 8 | Water Dispenser | Life Appliances |
| 9 | Complete Machine | PC?Office |
| 10 | Accessory | PC?Office |
| 11 | Laptop | Complete Machine |
| 12 | Ultrabook | Complete Machine |
| 13 | Gamebook | Complete Machine |
| 14 | CPU | Accessory |
| 15 | Host Computer | Accessory |
+———+——————+——————+

翻译一下,就是,可以先从标准说起,当子表s中的parent_id=父表中的type_id时,选用子表的type_id,type_name,以及父表的type_name呈现,那里的别名可以在言辞的末尾处突显

要把这几个逻辑关系先说说/想想清楚,再翻译为sql语句

扭转,查找所有系列的子类

先考虑逻辑关系:当父表的type_id=子表的parent_id时,取 P.TYPE_ID,P.TYPE_NAME,S.TYPE_NAME
——再写出语句

mysql> SELECT P.TYPE_ID,P.TYPE_NAME,S.TYPE_NAME FROM GOODS_TYPES
AS P LEFT JOIN GOODS_TYPES AS S
-> ON P.TYPE_ID=S.PARENT_ID;
+———+——————+——————+
| TYPE_ID | TYPE_NAME | TYPE_NAME |
+———+——————+——————+
| 1 | Home Electronics | Appliances |
| 1 | Home Electronics | Life Appliances |
| 3 | Appliances | Tablet TV |
| 3 | Appliances | Air Conditioner |
| 4 | Life Appliances | Electrical Fan |
| 4 | Life Appliances | Water Dispenser |
| 2 | PC?Office | Complete Machine |
| 2 | PC?Office | Accessory |
| 9 | Complete Machine | Laptop |
| 9 | Complete Machine | Ultrabook |
| 9 | Complete Machine | Gamebook |
| 10 | Accessory | CPU |
| 10 | Accessory | Host Computer |
| 5 | Tablet TV | NULL |
| 6 | Air Conditioner | NULL |
| 7 | Electrical Fan | NULL |
| 8 | Water Dispenser | NULL |
| 11 | Laptop | NULL |
| 12 | Ultrabook | NULL |
| 13 | Gamebook | NULL |
| 14 | CPU | NULL |
| 15 | Host Computer | NULL |
+———+——————+——————+

再繁杂一点,且预留你们自己翻译

mysql> SELECT P.TYPE_ID,P.TYPE_NAME,COUNT(S.TYPE_NAME)
CHILD_COUNT FROM GOODS_TYPES AS P LEFT JOIN GOODS_TYPES AS S
-> ON P.TYPE_ID=S.PARENT_ID GROUP BY P.TYPE_NAME ORDER BY
P.TYPE_ID;
+———+——————+————-+
| TYPE_ID | TYPE_NAME | CHILD_COUNT |
+———+——————+————-+
| 1 | Home Electronics | 2 |
| 2 | PC?Office | 2 |
| 3 | Appliances | 2 |
| 4 | Life Appliances | 2 |
| 5 | Tablet TV | 0 |
| 6 | Air Conditioner | 0 |
| 7 | Electrical Fan | 0 |
| 8 | Water Dispenser | 0 |
| 9 | Complete Machine | 3 |
| 10 | Accessory | 2 |
| 11 | Laptop | 0 |
| 12 | Ultrabook | 0 |
| 13 | Gamebook | 0 |
| 14 | CPU | 0 |
| 15 | Host Computer | 0 |
+———+——————+————-+

—-多表删除

基本思路也是由此一张表模拟多张表的操作

懒了,直接复制看吗,须要了再去做

— INSERT … SELECT完结复制

INSERT goods(goods_name,cate_id,brand_id) SELECT
goods_name,cate_id,brand_id FROM goods WHERE goods_id IN (19,20);

— 查找重复记录

SELECT goods_id,goods_name FROM goods GROUP BY goods_name HAVING
count(goods_name) >= 2;

— 删除重复记录

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

 

网站地图xml地图