查询练习

准备数据

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

分别在 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;