mysql聚合函数，运算操作
样板，空值(null)自改create database sky;
use sky;
create table m1(
id int(11),
name char(20),
age tinyint(10),
sex enum('男','女'),
score tinyint(10),
address char(20)
) default
insert into m1 values
(1,'L1',21,'男',90,'北京'),
(2,'L2',19,'男',91,'上海'),
(3,'L3',24,'女',95,'广州'),
(4,'L4',22,'男',89,'广州'),
(5,'L5',20,'女',86,'上海'),
(6,'L6',19,'女',99,'广州');
分类
1. avg(字段名)：字段的平均值
2. sum(字段名):字段的和
3. max(字段名):求字段的最大值
4. min(字段名) ; 求字段的最小值
5. count(字段名): 统计该字段有记录的个数
例如：
mysql> select * from m1;
+------+------+------+------+-------+---------+
|> +------+------+------+------+-------+---------+
| 2 | L2 | NULL | 男 | 91 | 上海 |
| 3 | L3 | 24 | 女 | 95 | 广州 |
| 4 | L4 | 22 | 男 | 101 | 广州 |
| 5 | L5 | NULL | 女 | 86 | 上海 |
| 6 | L6 | 19 | 女 | 99 | 北京 |
| 1 | L1 | 21 | 男 | 90 | 北京 |
+------+------+------+------+-------+---------+
6 rows in set (0.00 sec)
显示score字段最高的
mysql> select max(score) as SCORE from m1; # as 别名
+-------+
| SCORE |
+-------+
| 101 |
+-------+
1 row in set (0.00 sec)
统计字段有几行,as 别名
mysql> select count(name) as N,count(age) as A from m1;
+---+---+
| N | A |
+---+---+
| 6 | 4 |
+---+---+
1 row in set (0.02 sec)
计算总分
mysql> select sum(score) as score from m1;
+-------+
| score |
+-------+
| 562 |
+-------+
1 row in set (0.01 sec)
计算每个城市的平局分数
mysql> select address,avg(score) from m1 group by address;
+---------+------------+
| address | avg(score) |
+---------+------------+
| 上海 | 88.5000 |
| 北京 | 94.5000 |
| 广州 | 98.0000 |
+---------+------------+
3 rows in set (0.00 sec)
查看那两个国家人最多，显示前两名
mysql> select address,count(name) as name_count from m1
-> group by address
-> order by name_count desc
-> limit 2;
+---------+------------+
| address | name_count |
+---------+------------+
| 广州 | 3 |
| 上海 | 2 |
+---------+------------+
2 rows in set (0.00 sec)
distinct
作用：去重
distinct 处理的是存在的字段名
有多少个城市
mysql> select distinct address from m1;
+---------+
| address |
+---------+
| 北京 |
| 上海 |
| 广州 |
+---------+
3 rows in set (0.00 sec)
页:
[1]