mysql强化练习_MySQL加强练习

(1)in()在某个范围(集合)之间 //获得 商品id 为 3， 5， 7 ，8 这几个商品的信息  select * from ecs_goods where goods_id in(3,5,7,9); (2)Between and 在两个值之间的数据 //查询价格在1000-1500之间的商品信息 select * from ecs_goods where shop_price between 1000 and 1500; (3)Order by排序  排序方法(升序ASC，降序DESC) //将获得的商品按照 价格 由高到低 排序 (4)limit限定获得数据的数量 //获得价格最高的前十个商品 select * from ecs_goods order by market_price asc limit 2; limit 偏移量，想要获得的记录数量s //获得价格最高的  十个商品  除掉最高的前十个  select * from ecs_goods  order by market_price desc limit 10,10;  delete from ecs_goods  order by market_price desc limit 10 查询不同的商品分类下面，商品价格的总和 select sum(shop_price) from ecs_goods group by cat_id; 查询不同的商品分类下面，最高的商品  select goods_name,max(shop_price) from ecs_goods group by cat_id; 查询不同的商品分类下面，商品价格的平均数 select avg(shop_price) from ecs_goods group by cat_id; 查询不同的商品分类下面，商品价格的平均数大于1000的 select goods_name,avg(shop_price)as avg from ecs_goods group by cat_id  having avg>1000; mysql> select cat_name from ecs_category where cat_id=1 union select cat_name fr om ecs_category where parent_id=1; +----------+ | cat_name | +----------+ | 手机类型        | | CDMA手机    | | GSM手机     | | 3G手机      | | 双模手机      | +----------+ GSM手机下的 所有商品  select goods_name from ecs_goods  where cat_id=(select cat_id from ecs_category where  cat_name='GSM手机'); mysql> select goods_name from(select * from ecs_goods where cat_id in(3,5)) as t em_goods where goods_name like '诺基亚%'; +------------------------+ | goods_name             | +------------------------+ | 诺基亚E66                   | | 诺基亚5320 XpressMusic      | | 诺基亚N85                   | | 诺基亚N96                   | +------------------------+ 4 rows in set (0.03 sec)