MySQL之函数

聚合函数 统计指定列其值不为NULL的记录行数

SELECT COUNT (*) FROM 表名

计算指定列的最大值，如果指定列是字符串类型则使用字符串排序运算

SELECT MAX (字段名) FROM 表名;

计算指定列的最小值，如果指定列是字符串类型则使用字符串排序运算

SELECT MIN(字段名) FROM 表名;

计算指定列的数值和，如果指定列类型不是数值类型则计算结果为0

SELECT SUM (字段名）FROM 表名；

计算指定列的平均值，如果指定列类型不是数值类型则计算结果为0

SELECT AVG (字段名）FROM 表名;

例子： 创建一个表

CREATE TABLE student (
	sid CHAR(6),
	sname VARCHAR(50),
	age INT,
	gender VARCHAR(50) DEFAULT male
);
INSERT INTO student (sid,sname,age,gender) VALUES (S_1001, lili, 14, male);
INSERT INTO student (sid,sname,age,gender) VALUES (S_1002, wang, 15, female);
INSERT INTO student (sid,sname,age,gender) VALUES (S_1003, tywd, 16, male);
INSERT INTO student (sid,sname,age,gender) VALUES (S_1004, hfgs, 17, female);
INSERT INTO student (sid,sname,age,gender) VALUES (S_1005, qwer, 18, male);
INSERT INTO student (sid,sname,age,gender) VALUES (S_1006, zxsd, 19, female);
INSERT INTO student (sid,sname,age,gender) VALUES (S_1007, hjop, 16, male);
INSERT INTO student (sid,sname,age,gender) VALUES (S_1008, tyop, 15, female);
INSERT INTO student (sid,sname,age,gender) VALUES (S_1009, nhmk, 13, male);
INSERT INTO student (sid,sname,age,gender) VALUES (S_1010, xdfv, 17, female);
INSERT INTO student (sid,sname,age,gender) VALUES (S_1012, lili, 14, male);
INSERT INTO student (sid,sname,age,gender) VALUES (S_1013, wang, 15, female);

COUNT

mysql> SELECT COUNT(*) FROM student;
+----------+
| COUNT(*) |
+----------+
|       12 |
+----------+
1 row in set (0.05 sec)

mysql> SELECT COUNT(age) FROM student;
+------------+
| COUNT(age) |
+------------+
|         12 |
+------------+
1 row in set (0.04 sec)

MAX

mysql> SELECT MAX(age) FROM student;
+----------+
| MAX(age) |
+----------+
|       19 |
+----------+
1 row in set (0.08 sec)

MIN

mysql> SELECT MIN(age) FROM student;
+----------+
| MIN(age) |
+----------+
|       13 |
+----------+
1 row in set (0.05 sec)

SUM

mysql> SELECT SUM(age) FROM student;
+----------+
| SUM(age) |
+----------+
| 189      |
+----------+
1 row in set (0.05 sec)

AVG

mysql> SELECT AVG(age) FROM student;
+----------+
| AVG(age) |
+----------+
| 15.7500  |
+----------+
1 row in set (0.07 sec)

时间函数 字符串函数 数学函数 跟编程语言中的输出没什么区别，这里就不进行描述。