5、查询练习

准备数据

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);

练习1：查询大于或等于“超级本”价格的商品，并且按价格降序排列

select * from goods where price > some (select price from goods where cate='超级本') order by price desc;

练习2：求所有电脑产品大于平均价格，按降序排序

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

数据分表

1. 创建“商品分类表”

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

2. 查询goods表的所有记录，并且按“类别”分组

select cate from goods group by cate;

3. 将查询的数据插入goods_cates数据表中

insert into goods_cates (cate_name) select distinct cate from goods;

4. 通过goods_cates更新goods表

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

5. 通过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;

6. 更新goods_brands表

update goods as g 
inner join goods_brands as c 
on g.brand_name = c.brand_name set g.brand_name = c.brand_id;

7. 插入数据

# 插入goods_cates
insert into goods_cates(cate_name) values ('路由器'),('交换机'),('网卡'); 
# 插入goods_brands
insert into goods_brands(brand_name) values ('海尔'),('清华同方'),('神舟');

8. 在goods数据表中写入任意记录

insert into goods (name,cate_id,brand_id,price) values('LaserJet Pro P1606dn 黑白激光打印机','12','4','1849');

9. 查询所有商品的详情(通过左右连接来生成)

select * from goods 
left join goods_cates on goods.cate=goods_cates.cate_id 
inner join goods_brands on goods.brand_name=goods_brands.brand_id

返回目录