查询实战

准备数据

创建表：

create table goods(
    id int unsigned primary key auto_increment not null,
    name varchar(150) not null,
    cate varchar(40) not null,
    brand_name varchar(40) not null,
    price decimal(10,3) not null default 0
);

insert into goods values(0,' Apple MacBook Air 13.3英寸笔记本电脑','笔记本','苹果','6588'); 
insert into goods values(0,'联想(Lenovo)拯救者R720 15.6英寸大屏','笔记本','联想','6099');
insert into goods values(0,'法国酒庄直采原瓶原装进口AOC级艾落干红葡萄酒','红酒','法国','499'); 
insert into goods values(0,'x550cc 15.6英寸笔记本','笔记本','华硕','2799'); 
insert into goods values(0,'清扬(CLEAR)洗发水','洗发水','清扬','35'); 
insert into goods values(0,'荣耀MagicBook 14英寸轻薄窄边框笔记本','笔记本','联想','4299'); 
insert into goods values(0,'svp13226scb 触控超极本','超级本','索尼','7999'); 
insert into goods values(0,'海飞丝洗发水清爽去油750ml','洗发水','海飞丝','98');
insert into goods values(0,'ipad air 9.7英寸平板电脑','平板电脑','苹果','3388'); 
insert into goods values(0,'轩尼诗（Hennessy）洋酒 新点干邑白兰地 200ml','白酒','轩尼诗','199'); 
insert into goods values(0,'ideacentre c340 20英寸一体电脑 ','台式机','联想','3499'); 
insert into goods values(0,'vostro 3800-r1206 台式电脑','台式机','戴尔','2899'); 
insert into goods values(0,'imac me086ch/a 21.5英寸一体电脑','台式机','苹果','9188'); 
insert into goods values(0,'阿道夫（ADOLPH）轻柔丝滑洗护组合3件套','洗发水','阿道夫','3699'); 
insert into goods values(0,'z220sff f4f06pa工作站','服务器/工作站','惠普','4288'); 
insert into goods values(0,'poweredge ii服务器','服务器/工作站','戴尔','5388'); 
insert into goods values(0,' 三星（SAMSUNG）C27F390FHC 27英寸 1800R曲率 ','显示器','三星','1300'); 
insert into goods values(0,'戴尔（DELL） U2417H 23.8英寸四边微边框旋转升降IPS屏','显示器','戴尔,'1500');

查询：

1、查询goods表中所有的商品

select * from goods;

2、查询所有产品的平均价格,并且保留两位小数

select round(avg(price),2) as avg_price from goods;

3、通过子查询来实现，查询所有价格大于平均价格的商品，并且按价格降序排序

select id,name,price from goods 
where price > (select round(avg(price),2) as avg_price from goods) 
order by price desc;

4、查询所有 "联想" 的产品

select * from goods where brand_name='联想';

5、查询价格大于或等于"联想"价格的商品，并且按价格降序排列

select id,name,price from goods  where price >= any(select price from goods where brand_name = '联想')  order by price desc;

6、查询每个产品类型的最低价格的，通过cate字段进行分组。

select cate,min(price) from goods group by cate;

7、查询价格区间在4500-6500之间的笔记本

select * from goods where price between 4500 and 6500 and cate='笔记本';

查询数据分表

创建一个商品表

create table if not exists goods_cates(
    cate_id int unsigned primary key auto_increment,
    cate_name varchar(40)
);

1、查询goods表中所有的商品，并且按"类别"分组

select cate from goods group by cate;

2、将分组后的结果写入到刚才创建的表中

insert into goods_cates (cate_name) select cate from goods group by cate;

3、通过goods_cates数据表来更新goods表，将goods表中的cate字段，修改成goods_cates的id字段

update goods as g inner join goods_cates as c on g.cate = c.cate_name 
set cate = cate_id;

4、字段 brand_name 进行分表。

    create table if not exists goods_brands(
    brand_id int unsigned primary key auto_increment,
    brand_name varchar(40)
);

insert into goods_brands(brand_name) select brand_name from goods group by brand_name;

5、通过goods_brands数据表来更新goods表，将goods表中的barnd_name字段，修改成goods_brands的id字段

update goods as g inner JOIN goods_brands as j on g.brand_name=j.brand_name set g.brand_name=j.brand_id;

6、查看goods表结构，发现 cate，、brand_name 两个字段都是varchar字段，需要修改成int类型字段。

desc goods;

alter table goods  
change cate cate_id int unsigned not null,
change brand_name brand_id int unsigned not null;

7、通过左连接查询所有商品的信息

select id,name,cate_name,brand_name,price from goods as g
left join goods_cates as c on g.cate_id = c.cate_id
left join goods_brands as b on g.brand_id = b.brand_id;

8、通过右连接查询所有商品的信息

select id,name,cate_name,brand_name,price from goods as g
right join goods_cates as c on g.cate_id = c.cate_id
right join goods_brands as b on g.brand_id = b.brand_id;