准备数据
create table goods(
id int unsigned primary key auto_increment,
name varchar(150),
cate varchar(40),
brand_name varchar(40),
price decimal(10,3) default 0,
is_show bit default 1,
is_saleoff bit default 0
);
insert into goods values(0,'r510vc 15.6英寸笔记本','笔记本','华硕','3399',default,default);
insert into goods values(0,'y400n 14.0英寸笔记本电脑','笔记本','联想','4999',default,default);
insert into goods values(0,'g150th 15.6英寸游戏本','游戏本','雷神','8499',default,default);
insert into goods values(0,'x550cc 15.6英寸笔记本','笔记本','华硕','2799',default,default);
insert into goods values(0,'x240 超极本','超级本','联想','4999',default,default);
insert into goods values(0,'u330p 13.3英寸超极本','超级本','联想','4299',default,default);
insert into goods values(0,'svp13226scb 触控超极本','超级本','索尼','7999',default,default);
insert into goods values(0,'ipad mini 7.9英寸平板电脑','平板电脑','苹果','1998',default,default);
insert into goods values(0,'ipad air 9.7英寸平板电脑','平板电脑','苹果','3388',default,default);
insert into goods values(0,'ipad mini 配备 retina 显示屏','平板电脑','苹果','2788',default,default);
insert into goods values(0,'ideacentre c340 20英寸一体电脑 ','台式机','联想','3499',default,default);
insert into goods values(0,'vostro 3800-r1206 台式电脑','台式机','戴尔','2899',default,default);
insert into goods values(0,'imac me086ch/a 21.5英寸一体电脑','台式机','苹果','9188',default,default);
insert into goods values(0,'at7-7414lp 台式电脑 linux ）','台式机','宏碁','3699',default,default);
insert into goods values(0,'z220sff f4f06pa工作站','服务器/工作站','惠普','4288',default,default);
insert into goods values(0,'poweredge ii服务器','服务器/工作站','戴尔','5388',default,default);
insert into goods values(0,'mac pro专业级台式电脑','服务器/工作站','苹果','28888',default,default);
insert into goods values(0,'hmz-t3w 头戴显示设备','笔记本配件','索尼','6999',default,default);
insert into goods values(0,'商务双肩背包','笔记本配件','索尼','99',default,default);
insert into goods values(0,'x3250 m4机架式服务器','服务器/工作站','ibm','6888',default,default);
insert into goods values(0,'hmz-t3w 头戴显示设备','笔记本配件','索尼','6999',default,default);
insert into goods values(0,'商务双肩背包','笔记本配件','索尼','99',default,default);
查询演练
求所有电脑产品的平均价格,并且保留两位小数
select round(avg(price),2) as avg_price from goods;
查询所有价格大于平均价格的商品，并且按价格降序排序
select id,name,price from goods
where price > (select round(avg(price),2) as avg_price from goods)
order by price desc;
查询类型为'超极本'的商品价格
select price from goods where cate = '超级本';
查询价格大于或等于"超级本"价格的商品，并且按价格降序排列
select id,name,price from goods
where price = any(select price from goods where cate = '超级本')
order by price desc;
= any 或者 =some 等价 in
select id,name,price from goods
where price in (select price from goods where cate = '超级本')
order by price desc;
!=all 等价于 not in
select id,name,price from goods
where price not in (select price from goods where cate = '超级本')
order by price desc;
数据分表
创建“商品分类”表
create table if not exists goods_cates(
cate_id int unsigned primary key auto_increment,
cate_name varchar(40)
);
查询goods表的所有记录，并且按"类别"分组
select cate from goods group by cate;
将分组结果写入到goods_cates数据表
insert into goods_cates (cate_name) select cate from goods group by cate;
通过goods_cates数据表来更新goods表
update goods as g inner join goods_cates as c on g.cate = c.cate_name
set cate = cate_id;
通过create...select来创建数据表并且同时写入记录,一步到位
create table goods_brands (
brand_id int unsigned primary key auto_increment,
brand_name varchar(40)) select brand_name from goods group by brand_name;
通过goods_brands数据表来更新goods数据表
update goods as g inner join goods_brands as b on g.brand_name = b.brand_name
set g.brand_name = b.brand_id;
查看 goods 的数据表结构,会发现 cate 和 brand_name对应的类型为 varchar 但是存储的都是字符串
修改数据表结构，把cate字段改为cate_id且类型为int unsigned，把brand_name字段改为brand_id且类型为int unsigned
经过优化，在goods表中cate和brand没有冗余数据
分别在 good_scates 和 goods_brands表中插入记录
insert into goods_cates(cate_name) values ('路由器'),('交换机'),('网卡');
insert into goods_brands(brand_name) values ('海尔'),('清华同方'),('神舟');
在 goods 数据表中写入任意记录
insert into goods (name,cate_id,brand_id,price)
values('LaserJet Pro P1606dn 黑白激光打印机','12','4','1849');
查询所有商品的详细信息 (通过内连接)
select id,name,cate_name,brand_name,price from goods as g
inner join goods_cates as c on g.cate_id = c.cate_id
inner join goods_brands as b on g.brand_id = b.brand_id;
查询所有商品的详细信息 (通过左连接)
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;
查询所有商品的详细信息 (通过右连接)
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;
