一、排序
格式：select * from 表 order by 字段 asc|desc
1、查询所有的商品进行排序（升序asc、降序desc）
mysql> select * from product order by price asc; +-----+--------+-------+---------------------+ | pid | pname | price | pdate | +-----+--------+-------+---------------------+ | 3 | 小赵 | -50 | 2019-01-28 15:49:32 | | 1 | 小兵 | 0.03 | 2019-01-28 15:48:46 | | 2 | 小王 | 33 | 2019-01-28 15:49:11 | | 4 | 小王 | 33 | 2019-01-28 16:16:45 | +-----+--------+-------+---------------------+ 4 rows in set (0.03 sec) mysql> select * from product order by price desc; +-----+--------+-------+---------------------+ | pid | pname | price | pdate | +-----+--------+-------+---------------------+ | 2 | 小王 | 33 | 2019-01-28 15:49:11 | | 4 | 小王 | 33 | 2019-01-28 16:16:45 | | 1 | 小兵 | 0.03 | 2019-01-28 15:48:46 | | 3 | 小赵 | -50 | 2019-01-28 15:49:32 | +-----+--------+-------+---------------------+ 4 rows in set (0.00 sec)
2、查询名称中含有“王”字的商品，按照价格降序排序！
mysql> select * from product where pname like '%王%' order by price desc; +-----+--------+-------+---------------------+ | pid | pname | price | pdate | +-----+--------+-------+---------------------+ | 4 | 小王 | 35 | 2019-01-29 15:29:59 | | 2 | 小王 | 33 | 2019-01-28 15:49:11 | +-----+--------+-------+---------------------+ 2 rows in set (0.00 sec)
二、常用的聚合函数：sum()求和,avg()平均,max()最大值,min()最小值,count()计数;
注意:聚合函数不统计 null值
1、获得所有商品价格的总和；
mysql> select sum(price) from product; +------------+ | sum(price) | +------------+ | 18.03 | +------------+ 1 row in set (0.04 sec)
2、获取所有价格的平均价格：
mysql> select avg(price) from product; +------------+ | avg(price) | +------------+ | 4.5075 | +------------+ 1 row in set (0.00 sec)
3、获得所有商品的个数；
mysql> select count(*) from product; +----------+ | count(*) | +----------+ | 4 | +----------+ 1 row in set (0.00 sec)
三、分组
1、准备数据
mysql> alter table product add cid varchar(32); //添加分类 Query OK, 0 rows affected (1.18 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc product; +-------+-------------+------+-----+-------------------+----------------------------- | Field | Type | Null | Key | Default | Extra +-------+-------------+------+-----+-------------------+----------------------------- | pid | int(11) | NO | PRI | NULL | auto_increment | pname | varchar(20) | YES | | NULL | | price | double | YES | | NULL | | pdate | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | cid | varchar(32) | YES | | NULL | +-------+-------------+------+-----+-------------------+----------------------------- 5 rows in set (0.11 sec)
//初始化数据 mysql> update product set cid='1'; Query OK, 4 rows affected (0.15 sec) Rows matched: 4 Changed: 4 Warnings: 0 mysql> update product set cid='2' where pid in (2,3,4); Query OK, 3 rows affected (0.14 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> select * from product; +-----+--------+-------+---------------------+------+ | pid | pname | price | pdate | cid | +-----+--------+-------+---------------------+------+ | 1 | 小兵 | 0.03 | 2019-01-31 15:53:07 | 1 | | 2 | 小王 | 33 | 2019-01-31 15:54:25 | 2 | | 3 | 小赵 | -50 | 2019-01-31 15:54:25 | 2 | | 4 | 小王 | 35 | 2019-01-31 15:54:25 | 2 | +-----+--------+-------+---------------------+------+ 4 rows in set (0.00 sec)
2、查询：根据cid分组,分组后统计商品的个数.
mysql> select cid , count(*) from product group by cid; +------+----------+ | cid | count(*) | +------+----------+ | 1 | 1 | | 2 | 3 | +------+----------+ 2 rows in set (0.05 sec)
3、查询：根据cid分组,分组统计每组商品的平均价格:
mysql> select cid,avg(price) from product group by cid having avg(price) > 5; +------+------------+ | cid | avg(price) | +------+------------+ | 2 | 6 | +------+------------+ 1 row in set (0.07 sec)
4、查询经验总结，优先级顺序如下：
select distinct *| 字段... from 表 where 查询条件 group by 分组字段 having 分组条件 order by 排序字段 asc|desc
待续..........................