MYSQL统计函数基础

主要使用到的表格以及数据

CREATE TABLE student (
	id INT NOT NULL DEFAULT 1,
	NAME VARCHAR ( 20 ) NOT NULL DEFAULT '',
	chinese FLOAT NOT NULL DEFAULT 0.0,
	english FLOAT NOT NULL DEFAULT 0.0,
	math FLOAT NOT NULL DEFAULT 0.0 
);
INSERT INTO student ( id, NAME, chinese, english, math )
VALUES
	( 1, '韩顺平', 89, 78, 90 );
INSERT INTO student ( id, NAME, chinese, english, math )
VALUES
	( 2, '张飞', 67, 98, 56 );
INSERT INTO student ( id, NAME, chinese, english, math )
VALUES
	( 3, '宋江', 87, 78, 77 );
INSERT INTO student ( id, NAME, chinese, english, math )
VALUES
	( 4, '关羽', 88, 98, 90 );
INSERT INTO student ( id, NAME, chinese, english, math )
VALUES
	( 5, '赵云', 82, 84, 67 );
INSERT INTO student ( id, NAME, chinese, english, math )
VALUES
	( 6, '欧阳锋', 55, 85, 45 );
INSERT INTO student ( id, NAME, chinese, english, math )
VALUES
	( 7, '黄蓉', 75, 65, 30 );
INSERT INTO student ( id, NAME, chinese, english, math )
VALUES
	( 8, '韩信', 45, 65, 99 );
SELECT * FROM student;

1、count()函数：统计表中数据的条数

使用到的表格以及数据

CREATE TABLE t15 ( `name` VARCHAR(20));
INSERT INTO t15 VALUES('tom');
INSERT INTO t15 VALUES('jack');
INSERT INTO t15 VALUES('mary');
INSERT INTO t15 VALUES(NULL);

统计表数据条数

SELECT count(*) FROM t15;

统计某列段条数(空值不被统计)

SELECT count(name) FROM t15;

 

以下函数仅对数字字段有效
 

2、sum()函数：求和

SELECT
	SUM( math ) 数学成绩总和,
	SUM( chinese + english + math ) 总成绩总和 
FROM
	student;

 

3、avg()函数：计算平均值

SELECT
    AVG( math ) 数学成绩平均值,
    AVG( chinese + english + math ) 总成绩平均值 
FROM
	student;

 

4、max()函数：查询最大值
5、min()函数：查询最小值

SELECT
	MAX( math ) 最高分,
	MIN( math ) 最低分 
FROM
	student;