mybatis教程，Python与MySQL交互

• 显示商品的种类

select cate_name from goods group by cate_name;

• 求所有电脑产品的平均价格,并且保留两位小数

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

• 显示每种商品的平均价格

select cate_name,avg(price) from goods group by cate_name;

• 查询每种类型的商品中 最贵、最便宜、平均价、数量

select cate_name,max(price),min(price),avg(price),count(* ) from goods group by cate_name;

• 查询所有价格大于平均价格的商品，并且按价格降序排序

select id,name,price from goods

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

order by price desc;

• 查询每种类型中最贵的电脑信息

select * from goods

inner join

(

select

cate_name,

max(price) as max_price,

min(price) as min_price,

avg(price) as avg_price,

count(* ) from goods group by cate_name

) as goods_new_info

on goods.cate_name=goods_new_info.cate_name and goods.price=goods_new_info.max_price;

1.2 创建 "商品分类"表

• 创建商品分类表

create table if not exists goods_cates(

id int unsigned primary key auto_increment,

name varchar(40) not null

);

• 查询goods表中商品的种类

select cate_name from goods group by cate_name;

• 将分组结果写入到goods_cates数据表

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

1.3 同步表数据

• 通过goods_cates数据表来更新goods表

update goods as g inner join goods_cates as c on g.cate_name=c.name set g.cate_name=c.id;

1.4 创建 “商品品牌表” 表

通过create…select来创建数据表并且同时写入记录,一步到位

– select brand_name from goods group by brand_name;

– 在创建数据表的时候一起插入数据

– 注意: 需要对brand_name 用as起别名，否则name字段就没有值

create table goods_brands (

id int unsigned primary key auto_increment,

name varchar(40) not null) select brand_name as name from goods group by brand_name;

1.5 同步数据

通过goods_brands数据表来更新goods数据表

update goods as g inner join goods_brands as b on g.brand_name=b.name set g.brand_name=b.id;

1.6 修改表结构

• 查看 goods 的数据表结构,会发现 cate_name 和 brand_name对应的类型为 varchar 但是存储的都是数