MariaDB中Avg()的使用是怎样 有什么窍门
|
本篇内容介绍了“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) 上述内容具有一定的借鉴价值，感兴趣的朋友可以参考，希望能对大家有帮助。 （编辑：银川站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
