常用函数
COUNT(expression) | 返回查询的字段总数,参数是一个字段或者 * 号 |
---|---|
SUM(expression) | 返回指定字段的总和 |
AVG(expression) | 返回一个表达式的平均值,expression 是一个字段 |
MAX(expression),MIN(expression) | 返回字段 expression 中的最大值或最小值 |
ABS(num) | 返回 num 的绝对值 |
CEILING(num) | 向上取整 |
FLOOR(num) | 向下取整 |
FORMAT(num, decimal_places) | 保留小数位数 |
LEAST(num1, num2, num3, …) | 返回列表中的最小值 |
GREATEST(num1, num2, num3, …) | 返回列表中的最大值 |
MOD(num1, num2) | 返回 num1 除以 num2 以后的余数 |
CONV(num, from_base, to_base) | 进制转换 |
RAND() | 返回 0 到 1 的随机数 |
count 合计函数的使用
count(列名) 会排除掉为null 的情况,即null不统计
快速入门案例
mysql> select * from student;
+----+--------+---------+---------+------+
| id | name | chinese | english | math |
+----+--------+---------+---------+------+
| 1 | 宋江 | 88 | 85 | 95 |
| 2 | 李逵 | 81 | 55 | 65 |
| 3 | 林冲 | 89 | 85 | 95 |
| 4 | 鲁智深 | 99 | 85 | 95 |
| 5 | 杨志 | 97 | 85 | 96 |
| 6 | 晁盖 | 88 | 75 | 95 |
+----+--------+---------+---------+------+
6 rows in set (0.00 sec)
1.统计一个班共有多少学生
mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
| 6 |
+----------+
1 row in set (0.03 sec)
2.统计语文大于90 的学生有多少个
mysql> select count(*) from student where chinese>90;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
3.统计总分大于270的人数
mysql> select count(*) from student where (chinese+english+math)>270;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
// 现在 我想知道成绩大于270的具体是谁?
// count() 是个函数 只会显示一条 想看具体是谁需要用联合查询 后边会讲
mysql> select count(*),name from student where (chinese+english+math)>270;
+----------+--------+
| count(*) | name |
+----------+--------+
| 2 | 鲁智深 |
+----------+--------+
1 row in set (0.00 sec)
count(*) 和 count(列名) 的区别
// count(*) 会统计一共的记录数,count(列名) 会排除掉为null 的情况
mysql> select * from test;
+------+-------+
| id | name |
+------+-------+
| 100 | hello |
| 200 | NULL |
+------+-------+
2 rows in set (0.00 sec)
mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
mysql> select count(id) from test;
+-----------+
| count(id) |
+-----------+
| 2 |
+-----------+
1 row in set (0.00 sec)
mysql> select count(name) from test;
+-------------+
| count(name) |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)
sum 列求和函数的使用
快速入门案例
mysql> select * from student;
+----+--------+---------+---------+------+
| id | name | chinese | english | math |
+----+--------+---------+---------+------+
| 1 | 宋江 | 88 | 85 | 95 |
| 2 | 李逵 | 81 | 55 | 65 |
| 3 | 林冲 | 89 | 85 | 95 |
| 4 | 鲁智深 | 99 | 85 | 95 |
| 5 | 杨志 | 97 | 85 | 96 |
| 6 | 晁盖 | 88 | 75 | 95 |
+----+--------+---------+---------+------+
6 rows in set (0.00 sec)
1.统计一个班数学总成绩
mysql> select sum(math) from student;
+-----------+
| sum(math) |
+-----------+
| 541 |
+-----------+
1 row in set (0.00 sec)
mysql> select sum(math) as '数学总分' from student;
+----------+
| 数学总分 |
+----------+
| 541 |
+----------+
1 row in set (0.00 sec)
2.统计一个班语数英各科总成绩
mysql> select sum(chinese) as '语文总分',sum(math) as '数学总分',sum(english) as '英语总分' from student;
+----------+----------+----------+
| 语文总分 | 数学总分 | 英语总分 |
+----------+----------+----------+
| 542 | 541 | 470 |
+----------+----------+----------+
1 row in set (0.00 sec)
3.统计一个班语数英所有成绩总和
//mysql中加法运算任何数和null相加都为null,如果宋江的数学成绩为null,那么统计结果会少宋江的成绩
mysql> select sum(chinese+english+math) as '成绩总和' from student;
+----------+
| 成绩总和 |
+----------+
| 1553 |
+----------+
1 row in set (0.00 sec)
// 推荐用这种方式 这种方法就算有人成绩为null 也不影响,因为没有加法运算
mysql> select sum(chinese)+sum(math)+sum(english) from student;
+-------------------------------------+
| sum(chinese)+sum(math)+sum(english) |
+-------------------------------------+
| 1553 |
+-------------------------------------+
1 row in set (0.00 sec)
4.统计一个班数学平均成绩
mysql> select sum(math)/count(*) as '数学平均成绩' from student;
+-------------------+
| 数学平均成绩 |
+-------------------+
| 90.16666666666667 |
+-------------------+
1 row in set (0.00 sec)
// round() 方法,自定义小数点后保留几位
mysql> select round(sum(math)/count(*)) as '数学平均成绩' from student;
+--------------+
| 数学平均成绩 |
+--------------+
| 90 |
+--------------+
1 row in set (0.02 sec)
avg 求平均值函数的使用
只讨论数据不为null的情况,建表时数据全部设置为not null
快速入门案例
mysql> select * from student;
+----+--------+---------+---------+------+
| id | name | chinese | english | math |
+----+--------+---------+---------+------+
| 1 | 宋江 | 88 | 85 | 95 |
| 2 | 李逵 | 81 | 55 | 65 |
| 3 | 林冲 | 89 | 85 | 95 |
| 4 | 鲁智深 | 99 | 85 | 95 |
| 5 | 杨志 | 97 | 85 | 96 |
| 6 | 晁盖 | 88 | 75 | 95 |
+----+--------+---------+---------+------+
6 rows in set (0.00 sec)
1.求一个班级数学平均数
mysql> select avg(math) from student;
+-------------------+
| avg(math) |
+-------------------+
| 90.16666666666667 |
+-------------------+
1 row in set (0.00 sec)
mysql> select round(avg(math),2) from student;
+--------------------+
| round(avg(math),2) |
+--------------------+
| 90.17 |
+--------------------+
1 row in set (0.00 sec)
2.求一个班级总分平均分
mysql> select round(avg(chinese+math+english),2) from student;
+------------------------------------+
| round(avg(chinese+math+english),2) |
+------------------------------------+
| 258.83 |
+------------------------------------+
1 row in set (0.00 sec)
max 和 min的使用
快速入门案例
mysql> select * from student;
+----+--------+---------+---------+------+
| id | name | chinese | english | math |
+----+--------+---------+---------+------+
| 1 | 宋江 | 88 | 85 | 95 |
| 2 | 李逵 | 81 | 55 | 65 |
| 3 | 林冲 | 89 | 85 | 95 |
| 4 | 鲁智深 | 99 | 85 | 95 |
| 5 | 杨志 | 97 | 85 | 96 |
| 6 | 晁盖 | 88 | 75 | 95 |
+----+--------+---------+---------+------+
6 rows in set (0.00 sec)
1.查询总分的最高分和最低分
mysql> select max(chinese+english+math) as '最高分', min(chinese+english+math) as '最低分' from student;
+--------+--------+
| 最高分 | 最低分 |
+--------+--------+
| 279 | 201 |
+--------+--------+
1 row in set (0.00 sec)
其他数学函数的使用
mysql> select * from student;
+----+--------+---------+---------+-------+
| id | name | chinese | english | math |
+----+--------+---------+---------+-------+
| 3 | 鲁智深 | 33.00 | 32.00 | 12.00 |
| 2 | 林冲 | 95.00 | 98.00 | 91.00 |
| 1 | 李逵 | 18.00 | 25.00 | 11.00 |
+----+--------+---------+---------+-------+
3 rows in set (0.00 sec)
//ABS(num) 返回 num 的绝对值
mysql> select abs(-11.22);
+-------------+
| abs(-11.22) |
+-------------+
| 11.22 |
+-------------+
1 row in set (0.00 sec)
//CEILING(num) 向上取整 FLOOR(num) 向下取整
mysql> select ceiling(12.22), floor(12.22),ceiling(-12.22), floor(-12.22);
+----------------+--------------+-----------------+---------------+
| ceiling(12.22) | floor(12.22) | ceiling(-12.22) | floor(-12.22) |
+----------------+--------------+-----------------+---------------+
| 13 | 12 | -12 | -13 |
+----------------+--------------+-----------------+---------------+
1 row in set (0.00 sec)
//FORMAT(num, decimal_places) 保留小数位数
mysql> select format(12.345,2),format(-12.345,2),format(12.123,2),format(-12.123,2);
+------------------+-------------------+------------------+-------------------+
| format(12.345,2) | format(-12.345,2) | format(12.123,2) | format(-12.123,2) |
+------------------+-------------------+------------------+-------------------+
| 12.35 | -12.35 | 12.12 | -12.12 |
+------------------+-------------------+------------------+-------------------+
1 row in set (0.00 sec)
//LEAST() 返回列表中的最小值 GREATEST() 返回列表中的最大值
mysql> select least(1,2,3), greatest(1,2,3);
+--------------+-----------------+
| least(1,2,3) | greatest(1,2,3) |
+--------------+-----------------+
| 1 | 3 |
+--------------+-----------------+
1 row in set (0.00 sec)
//MOD(num1, num2) 返回 num1 除以 num2 以后的余数
mysql> select mod(10,3),mod(10,5);
+-----------+-----------+
| mod(10,3) | mod(10,5) |
+-----------+-----------+
| 1 | 0 |
+-----------+-----------+
1 row in set (0.00 sec)
//RAND() 返回 0 到 1 的随机数
mysql> select rand(), rand()*100;
+-------------------+-------------------+
| rand() | rand()*100 |
+-------------------+-------------------+
| 0.397183169980704 | 50.65288259708591 |
+-------------------+-------------------+
1 row in set (0.00 sec)