数据库教程-mariadb教程-MariaDB Avg()函数

跨零代码为大家提供高品质的解决方案,请大家多多来访,跨零不胜感激,在此谢过。

MariaDB Avg()函数用于检索表达式的平均值。

语法:

SELECT AVG(aggregate_expression)   FROM tables   [WHERE conditions]; 

或者 –

SELECT expression1, expression2, ... expression_n,   AVG(aggregate_expression)   FROM tables   [WHERE conditions]   GROUP BY expression1, expression2, ... expression_n; 

数据准备

"testdb"数据库中创建一个"students"表,并插入一些数据。参考以下创建语句 –

USE testdb; DROP TABLE students; -- 创建新表 CREATE TABLE students(       student_id INT NOT NULL AUTO_INCREMENT,       student_name VARCHAR(100) NOT NULL,       student_address VARCHAR(40) default NULL,      admission_date DATE,     score float(4, 1) default NULL,      PRIMARY KEY ( student_id ) ); -- 插入数据 INSERT INTO students   (student_id, student_name, student_address,score, admission_date)   VALUES(1,'Maxsu','Haikou', 99.5,'2017-01-07 00:00:00');  INSERT INTO students   (student_id, student_name, student_address, score, admission_date)   VALUES   (2,'Crurry','Beijing',86,'2016-05-07 00:00:00'), (3,'JMaster','Beijing',91,'2016-05-07 00:00:00'),   (4,'Mahesh','Guangzhou',78,'2016-06-07 00:00:00'),   (5,'Kobe','Shanghai',89,'2016-02-07 00:00:00'),   (6,'Blaba','Shengzhen',100,'2016-08-07 00:00:00'); 

1. AVG()函数与单表达式

示例:

查询Student表的平均分数。参考以下查询语句 –

SELECT AVG(Score) AS "Average  Score" FROM students; 

执行上面查询语句,得到以下结果 –

MariaDB [testdb]> SELECT AVG(Score) AS "Average  Score" FROM students; +----------------+ | Average  Score | +----------------+ |       90.58333 | +----------------+ 1 row in set (0.04 sec) 

2. AVG()函数与公式

也可以在AVG()函数使用数学公式。 例如,如果要将学生的平均成绩提高50%,则可以使用以下公式来计算:

当前数据库中的记录如下 –

MariaDB [testdb]> select * from students; +------------+--------------+-----------------+----------------+-------+ | student_id | student_name | student_address | admission_date | score | +------------+--------------+-----------------+----------------+-------+ |          1 | Maxsu        | Haikou          | 2017-01-07     |  99.5 | |          2 | Crurry       | Beijing         | 2016-05-07     |  86.0 | |          3 | JMaster      | Beijing         | 2016-05-07     |  91.0 | |          4 | Mahesh       | Guangzhou       | 2016-06-07     |  78.0 | |          5 | Kobe         | Shanghai        | 2016-02-07     |  89.0 | |          6 | Blaba        | Shengzhen       | 2016-08-07     | 100.0 | +------------+--------------+-----------------+----------------+-------+ 6 rows in set (0.07 sec) 

示例:

SELECT AVG(score * 1.5) AS "New Score"  FROM students; 

执行上面查询语句,得到以下结果 –

MariaDB [testdb]> SELECT AVG(score * 1.5) AS "New Score"  FROM students; +-----------+ | New Score | +-----------+ | 135.87500 | +-----------+ 1 row in set (0.01 sec) 

2. AVG()函数与Order By子句

为了更容易说明问题,这里再插入一条记录 –

INSERT INTO students   (student_name, student_address,score, admission_date)   VALUES('Maxsu','Haikou', 90,'2017-11-17 00:00:00'); 

当前students表中,有以下数据 –

MariaDB [testdb]> select * from students; +------------+--------------+-----------------+----------------+-------+ | student_id | student_name | student_address | admission_date | score | +------------+--------------+-----------------+----------------+-------+ |          1 | Maxsu        | Haikou          | 2017-01-07     |  99.5 | |          2 | Crurry       | Beijing         | 2016-05-07     |  86.0 | |          3 | JMaster      | Beijing         | 2016-05-07     |  91.0 | |          4 | Mahesh       | Guangzhou       | 2016-06-07     |  78.0 | |          5 | Kobe         | Shanghai        | 2016-02-07     |  89.0 | |          6 | Blaba        | Shengzhen       | 2016-08-07     | 100.0 | |          7 | Maxsu        | Haikou          | 2017-11-17     |  90.0 | +------------+--------------+-----------------+----------------+-------+ 7 rows in set (0.00 sec) 

参考下查询语句 –

SELECT student_name, AVG(score) AS "Average Salary"   FROM students   GROUP BY student_name; 

执行上面查询语句,得到以下结果 –

MariaDB [testdb]> SELECT student_name, AVG(score) AS "Average Salary"     -> FROM students     -> GROUP BY student_name; +--------------+----------------+ | student_name | Average Salary | +--------------+----------------+ | Blaba        |      100.00000 | | Crurry       |       86.00000 | | JMaster      |       91.00000 | | Kobe         |       89.00000 | | Mahesh       |       78.00000 | | Maxsu        |       94.75000 | +--------------+----------------+ 6 rows in set (0.02 sec) 

  

从零到一,创造未来!跨零综合IT问题解决服务站,欢迎你的到来。Oracle数据库教程 mariadb教程 只为你绽放。

本文固定链接: http://kua0.com/2019/01/31/数据库教程-mariadb教程-mariadb-avg函数/

为您推荐

发表评论

电子邮件地址不会被公开。 必填项已用*标注