MySQL使用集合函数查询的实例

mysql使用集合函数查询的实例

mysql 集合函数

函数	作用
avg()	返回某列的平均值
count()	返回某列的行数
max()	返回某列的最大值
min()	返回某列的最小值
sum()	返回某列的的和

1. count() 函数

count() 函数统计数据表中包含的记录行数，或者根据查询结果返回列中包含的数据行数。

使用方法：

a.count(*) 计算表中总的行数，不管某列有数值或者为空值。
b.count(字段名) 计算指定列下总的行数，计算时将忽略空值的行。

【例】建立如下数据表

mysql> select  *  from  customers;
+-------+----------+-----------------------+---------+--------+-----------+-------------------+
| c_id  | c_name   | c_address             | c_city  | c_zip  | c_contact | c_email           |
+-------+----------+-----------------------+---------+--------+-----------+-------------------+
| 10001 | redhook  | 200 street            | tianjin | 300000 | liming    | [e-mail]    |
| 10002 | stars    | 333 fromage  lane     | dalian  | 116000 | zhangbo   | [e-mail] |
| 10003 | netbhood | 1  sunny  place       | qingdao | 266000 | luocong   | null              |
| 10004 | joto     | 829  riverside  drive | haikou  | 570000 | yangshan  | [e-mail]   |
+-------+----------+-----------------------+---------+--------+-----------+-------------------+

查询 customers 表中总的行数 ，sql 语句如下：

mysql> select  count(*)  as  cust_num
    -> from  customers;
+----------+
| cust_num |
+----------+
|        4 |
+----------+

查询 customers 表中有电子邮箱的顾客的总数，sql 语句如下：

mysql> select  count(c_email)  as  cust_num
    -> from  customers;
+----------+
| cust_num |
+----------+
|        3 |
+----------+

2. sum() 函数

sum() 是一个求总和的函数，返回指定列值得到总和。

sum() 函数在计算时， 会忽略列值为 null 的行

3. avg() 函数

avg( ) 函数通过计算返回的行数和每一行数据的和，求得指定列数据的平均值。

【例】fruits 表。

mysql> select *  from  fruits;
+------+------+------------+---------+
| f_id | s_id | f_name     | f_price |
+------+------+------------+---------+
| a1   |  101 | apple      |    5.20 |
| a2   |  103 | apricot    |    2.20 |
| b1   |  101 | blackberry |   10.20 |
| b2   |  104 | berry      |    7.60 |
| b5   |  107 | xxxx       |    3.60 |
| bs1  |  102 | orange     |   11.20 |
| bs2  |  105 | melon      |    8.20 |
| c0   |  101 | cherry     |    3.20 |
| l2   |  104 | lemon      |    6.40 |
| m1   |  106 | mango      |   15.60 |
| m2   |  105 | xbabay     |    2.60 |
| m3   |  105 | xxtt       |   11.60 |
| o2   |  103 | coconut    |    9.20 |
| t1   |  102 | blanana    |   10.30 |
| t2   |  102 | grape      |    5.30 |
| t4   |  107 | xbababa    |    3.60 |
+------+------+------------+---------+

查询 s_id=103 的供应商的水果价格的平均值。

mysql> select  avg(f_price)  as  avg_price
    -> from fruits
    -> where  s_id = 103;
+-----------+
| avg_price |
+-----------+
|  5.700000 |
+-----------+

avg( ) 可以与 group by 一起使用，来计算每个分组的平均值。

【例】在 fruits 表中，查询每个供应商的水果价格的平均值。sql 语句如下：

mysql> select s_id, avg(f_price)  as  avg_price
    -> from  fruits
    -> group by  s_id;
+------+-----------+
| s_id | avg_price |
+------+-----------+
|  101 |  6.200000 |
|  102 |  8.933333 |
|  103 |  5.700000 |
|  104 |  7.000000 |
|  105 |  7.466667 |
|  106 | 15.600000 |
|  107 |  3.600000 |
+------+-----------+

【提示】avg() 函数的参数为要计算的列名称，如果要得到多个列的多个平均值，则需要在每一列上都使用 avg() 函数。

4. max() 函数

max() 函数 返回指定列中的最大值。

【例】在 fruits 表中查找市场上价格最高的水果。

mysql> select max(f_price)  as  max_price  from  fruits;
+-----------+
| max_price |
+-----------+
|     15.60 |
+-----------+

max() 函数不仅适用于查找数值类型，也可以查找字符类型。

【例】在 fruits 表中查找 f_name 的最大值。sql 语句如下：

mysql> select  max(f_name) from  fruits;
+-------------+
| max(f_name) |
+-------------+
| xxxx        |
+-------------+

5. min() 函数

min() 函数返回查询列中的最小值。

【例】在 fruits 表中查找市场上价格最低的水果。

mysql> select  min(f_price)  as  min_price  from  fruits;
+-----------+
| min_price |
+-----------+
|      2.20 |
+-----------+

min( ) 可以与 group by 一起使用，求出每个分组的最小值。

【例】在 fruits 表中查询不同供应商的提供的价格最低的水果。sql 语句如下：

mysql> select  s_id , min(f_price)  as  min_price
    -> from  fruits
    -> group by s_id;
+------+-----------+
| s_id | min_price |
+------+-----------+
|  101 |      3.20 |
|  102 |      5.30 |
|  103 |      2.20 |
|  104 |      6.40 |
|  105 |      2.60 |
|  106 |     15.60 |
|  107 |      3.60 |
+------+-----------+

group by 关键字根据 s_id 字段对记录进行分组，然后计算出每个分组中的最小值。

min() 函数 max() 函数类似，不仅适用于查找数值类型，也可用于查找字符类型。