php中cout和max函数,php学习-Mysql练习group，having与统计函数max,min,avg,sum,count

燕十八 公益PHP培训

课堂地址:YY频道88354001

学习社区:www.zixue.it

现在有一商品表goods,表内容如下:

goods_id为产品id,goods_name为产品名称，cat_id为产品所属分类，shop_price为产品销售价,market_price为市场价，goods_number为库存数量

查询最贵的产品的价格

select max(shop_price) from goods;

查询最贵的产品的价格

select min(shop_price) from goods;

查询发布最早的产品id

select min(goods_id) from goods;

查询库存金额(库存数量*销售价)

select sum(goods_number*shop_price) from goods;

查看商品的平均价格

select avg(shop_price) from goods;

第3个栏目下所有商品的库存量之和

select sum(goods_number) from goods where cat_id=3;

每个栏目下的库存量之和

select cat_id,sum(goods_number) from goods group

by cat_id;

每个栏目下的商品的平均价格

select cat_id,avg(shop_price) from goods group by cat_id;

查询本店价比市场价省的钱,并且要求省钱200元以上的取出来

select goods_id, goods_name,market_price-shop_price from goods

where market_price-shop_price>200;

select goods_id, goods_name,market_price-shop_price as discount

from goods having discount>200;

查询出每种商品所积压的货款

select goods_id,goods_number*shop_price from goods;

查询出栏目的积压货款金额,且筛选出积压金额>20000的栏目

select cat_id,sum(goods_number*shop_price) as kucun from goods

group by cat_id having kucun> 20000;

查询本店价比市场价省的钱,且筛选出省钱200以上的商品.

select goods_name,market_price-shop_price as discount from goods

having discount>200;