02select子句

select子句

SELECT 字段 FROM 数据表
where子句
group by子句
having子句
order by 子句
limit 子句;

聚合函数

  count:计数
  sum:求和
  max:最大值
  min:最小值
  avg:平均值
--查询最贵的商品价格
select max(shop_price) "最大商品价格" from goods
where 1;

--id小于3的数据
select count(*) "最大商品价格" from goods
where id < 3;

分组group by

select sex, count(*) from student group by sex;

--统计每个栏目下最贵的商品
select goods_name, cate_id, max(shop_price) from goods group by cate_id;
--统计每个栏目优惠最多的商品
select cate_id, max(market_price-shop_price) from goods group by cate_id;

having子句

--查询差价>200的商品信息

--where就不能给别名,查询的时候从表中字段名查的
select goods_name, market_price-shop_price from goods
where market_price-shop_price > 200;

select goods_name, market_price-shop_price cahjia from goods
having cahjia > 200;
-- group by... having子句
-- 查询该店每个栏目下面积压的总货款(shop_price*goods_number)
select goods_name, cate_id, sum(shop_price*goods_number) "jiya" from goods 
group by cate_id;
-- 查询积压货款>1000的栏目
select cate_id, sum(shop_price*goods_number) "jiya" from goods 
group by cate_id
having jiya > 1000;

order by

--对于所有的商品按照点击数从大到小排序 desc 从小到大asc默认
select * from goods order by click_count;
-- 按栏目由低到高排序,栏目内部按价格由高到低排序
select * from goods order by cate_id, shop_price desc;

limit 分页

-- 带一个参数, 表示显示多少条
-- limit N
-- 带二个参数, 表示从N条(偏移量)开始(从0开始算), 显示M条
-- limit N,M
-- 查询出id为7-11的5条数据 页码:偏移的数字/页面显示的数据+1
--第一页 5 / 0= 0 + 1 = 1 
select * from goods limit 0,5;  
--第二页 5 / 5= 1 + 1 = 2 
select * from goods limit 5,5;