MySQL之排序、分组(五)

时间:2022-08-15 09:53:20

一、排序

格式: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 *| 字段...
fromwhere 查询条件
group by 分组字段
    having 分组条件
order by 排序字段 asc|desc

待续..........................