常用查询条件\分组 group by\having和where的区别\union查询

(1)in()在某个范围（集合）之间
//获得 商品id 为 3， 5， 7 ，8 这几个商品的信息
select goods_name from  ecs_goods where goods_id in(3,5,7,8);

2)Between and 在两个值之间的数据
//查询价格在1000-1500之间的商品信息
select shop_price from ecs_goods
where shop_price between 1000 and 1500;

3)Order by排序  排序方法（升序ASC，降序DESC）
//将 获得的商品按照 价格 由高到低 排序
select shop_price from ecs_goods order by shop_price DESC;

(4)limit限定获得数据的数量
//获得价格最高的前十个商品
select shop_price from ecs_goods
order by shop_price desc limit 10;

limit 偏移量，想要获得的记录数量
//获得价格最高的  十个商品  除掉最高的前十个
select shop_price from ecs_goods
order by shop_price desc limit 10,10;(掠过前面10个取后面的10个)
（5）******合计函数
查询不同分类下商品的数量
select count(*) from ecs_goods group by cat_id;
或：select cat_id,count(cat_id) from ecs_goods group by cat_id;

查询不同分类下价格的总和
select sum(shop_price) from ecs_goods group by cat_id;

计算商品平均价格大于1000的商品分类
mysql> select avg(shop_price) as avg_price from ecs_goods group by cat_id having avg_price >1000;

查询不同商品分类下，价格最高的商品
select goods_name,max(shop_price) from ecs_goods group by cat_id;

查询不同的商品分类下面，商品价格的平均数
select avg(shop_price) from ecs_goods group by cat_id;

（6）union 联合 连接（union可以将多个查询语句连接起来，select1 union select2）
select cat_name from ecs_category where cat_id=1 union select cat_name fr
om ecs_category where parent_id=1;

子查询
select子查询
查询类别为‘GSM手机’的商品
select goods_name from ecs_goods  where cat_id=(select cat_id from ecs_ca
tegory where cat_name='GSM手机');

from子查询(必须有别名)
select goods_name from(select * from ecs_goods where cat_id in(3,5)) as t
em_goods where goods_name like '诺基亚%';