(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)
//获得 商品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)