Prepare data:

– create a "shop" Database

CREATE DATABASE shop CHARSET=utf8;

– use "Jingdong" Database
use shop;

– create a goods data table

Insert data (if you don't understand the meaning of each field name, read the insert data below, you will understand it, but you still don't understand it Message)

CREATE TABLE goods(
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
    name VARCHAR(150) NOT NULL,
    cate_name VARCHAR(40) NOT NULL,
    brand_name VARCHAR(40) NOT NULL,
    price DECIMAL(10,3) NOT NULL DEFAULT 0,
    is_show BIT NOT NULL DEFAULT 1,
    is_seleoff BIT NOT NULL DEFAULT 0
    );

– insert data into the goods table (directly copy, too much time is wasted typing one by one)

insert into goods values(0,'r510vc 15.6 Inch notebook','Notebook','ASUS','3399',default,default); 
insert into goods values(0,'y400n 14.0 Inch Laptop','Notebook','association','4999',default,default);
insert into goods values(0,'g150th 15.6 Inch Game Book','Game book','Raytheon','8499',default,default); 
insert into goods values(0,'x550cc 15.6 Inch notebook','Notebook','ASUS','2799',default,default); 
insert into goods values(0,'x240 Ultra extreme copy','Super edition','association','4880',default,default); 
insert into goods values(0,'u330p 13.3 Inch ultrabook','Super edition','association','4299',default,default); 
insert into goods values(0,'svp13226scb Touch ultrabook','Super edition','SONY','7999',default,default); 
insert into goods values(0,'ipad mini 7.9 Inch tablet','Tablet PC','Apple','1998',default,default);
insert into goods values(0,'ipad air 9.7 Inch tablet','Tablet PC','Apple','3388',default,default); 
insert into goods values(0,'ipad mini Equipment retina Display','Tablet PC','Apple','2788',default,default); 
insert into goods values(0,'ideacentre c340 20 Inch all in one computer ','Desktop computer','association','3499',default,default); 
insert into goods values(0,'vostro 3800-r1206 Desktop computer','Desktop computer','DELL','2899',default,default); 
insert into goods values(0,'imac me086ch/a 21.5 Inch all in one computer','Desktop computer','Apple','9188',default,default); 
insert into goods values(0,'at7-7414lp Desktop computer linux )','Desktop computer','Acer','3699',default,default); 
insert into goods values(0,'z220sff f4f06pa Workstation','The server/Workstation','HP','4288',default,default); 
insert into goods values(0,'poweredge ii The server','The server/Workstation','DELL','5388',default,default); 
insert into goods values(0,'mac pro Professional desktop','The server/Workstation','Apple','28888',default,default); 
insert into goods values(0,'hmz-t3w Head wear display device','Notebook accessories','SONY','6999',default,default); 
insert into goods values(0,'Business Backpack','Notebook accessories','SONY','99',default,default); 
insert into goods values(0,'x3250 m4 rack server ','The server/Workstation','ibm','6888',default,default); 
insert into goods values(0,'Business Backpack','Notebook accessories','SONY','99',default,default);

Add: (those who are not interested can be skipped directly)

select group_concat(name) as 'Name', brand_name,group_concat(cate_name) as 'type', group_concat(price) as 'Price'from (select n.id, n.name, n.cate_name, n.brand_name, n.price  from goods as n inner join (select avg(price) as avg_price, cate_name from goods group by cate_name)as new_goods on n.price > new_goods.avg_price and n.cate_name=new_goods.cate_name) as goods_infos group by goods_infos.brand_name;

If you want to write a long sql sentence (Fei Hua), you can try to read this sentence if you are interested in it, and use the basic grammar of the previous article to a large extent.

Start learning

– create "commodity classification" table

 CREATE IF NOT EXISTS goods_cates(
     id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
     name VARCHAR(40) NOT NULL
     );

IF NOT EXISTS: create the "goods" table if it does not exist
– insert the data of the cat field in the goods table into the goods table and name it name

INSERT INTO goods_cates(name) SELECT cate_name FROM goods GROUP BY cate_name;

- (key *) synchronize table data: update the goods table through the goods \

UPDATE goods AS g INNER JOIN goods_cates AS c ON g.cate_name = c.name SET g.cate_name=c.id;

– create a 'goods' brand table

 CREATE TABLE goods_brands(
     id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
     name VARCHAR(40) NOT NULL) SELECT goods.brand_name AS name FROM goods group BY brand_name;

– synchronization table data

UPDATA goods AS g INNER JOIN goods_brands AS b ON g.brand_name=b.name SET g.brand_name=b.id;

– modify table structure

ALTER TABLE goods
    CHANGE cate_name cate_id INT UNSIGNED NOT NULL,
    CHANGER brand_name brand_id INT UNSIGNED NOT NULL;

In this way, only some basic data is saved in the main table, and other data are stored in other tables, which further conforms to the E-R model
Click to view E-R model introduction (Reprint)

Note: key words should be capitalized as much as possible