SQL命令讲解（二）

DQL单表查询

 

练习：

示例1: 查询购买金额最多的前10名

语句一：

select customerid,sum(totalprice)from sellsum group by customerid ORDER BY sum(totalprice) desc limit 10;

语句二：

select customerid,sum(totalprice) as total from sellsum 
group by customerid 
ORDER BY total desc limit 10;
示例2：查询联通会员以方便通知联通用户

语句一：

select customerphone customername from customer where customerphone like '130%' or customerphone like '131%' or customerphone like '186%';

语句二：

select customerphone customername from customer where substr(customerphone,1,3) in ('130','131','186')

示例3：查询2017年第四季度的商品销售数量，并按照金额倒序排序

语句：

select goodstype, sum(subtotal) as total from sell  where createtime BETWEEN  '2017-10-01 00:00:00' and '2017-12-31 23:59:59' group by  goodstype order by total desc; 

示例4: 查询商品批次和数量

语句：select batchname,quantity from goods;

结果：

 

示例5：查询进货批次和数量

语句：select batchname,count(*) from goods group by batchname;

结果：

示例6：查询每个批次进货的数量

语句：select batchname,sum(quantity) from goods GROUP BY batchname;

结果：

 示例7：查询进货数量大于500的批次

语句：

Select batchname,sum(quantity) as sum from goods group by batchname having sum >500;