读书笔记--SQL必知必会09--汇总数据
2023-09-14 08:59:10 时间
9.1 聚集函数
聚集函数(aggregate function),对某些行运行的函数,计算并返回一个值。
使用聚集函数可以汇总数据而不必将涉及的数据实际检索出来。
可利用标准的算术操作符,实现更高级的汇总。
AVG() --- 返回某列的平均值
COUNT() --- 返回某列的行数
MAX() --- 返回某列的最大值
MIN() --- 返回某列的最小值
SUM() --- 返回某列值之和
9.1.1 AVG()函数
AVG()函数可用来返回特定列或行的平均值。
也可以返回多个列的平均值,此时必须使用多个AVG()函数。
AVG()函数忽略列值为NULL的行。
MariaDB [sqlbzbh]> SELECT * FROM Products;
+---------+---------+---------------------+------------+-----------------------------------------------------------------------+
| prod_id | vend_id | prod_name | prod_price | prod_desc |
+---------+---------+---------------------+------------+-----------------------------------------------------------------------+
| BNBG01 | DLL01 | Fish bean bag toy | 3.49 | Fish bean bag toy, complete with bean bag worms with which to feed it |
| BNBG02 | DLL01 | Bird bean bag toy | 3.49 | Bird bean bag toy, eggs are not included |
| BNBG03 | DLL01 | Rabbit bean bag toy | 3.49 | Rabbit bean bag toy, comes with bean bag carrots |
| BR01 | BRS01 | 8 inch teddy bear | 5.99 | 8 inch teddy bear, comes with cap and jacket |
| BR02 | BRS01 | 12 inch teddy bear | 8.99 | 12 inch teddy bear, comes with cap and jacket |
| BR03 | BRS01 | 18 inch teddy bear | 11.99 | 18 inch teddy bear, comes with cap and jacket |
| RGAN01 | DLL01 | Raggedy Ann | 4.99 | 18 inch Raggedy Ann doll |
| RYL01 | FNG01 | King doll | 9.49 | 12 inch king doll with royal garments and crown |
| RYL02 | FNG01 | Queen doll | 9.49 | 12 inch queen doll with royal garments and crown |
+---------+---------+---------------------+------------+-----------------------------------------------------------------------+
9 rows in set (0.00 sec)
MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> SELECT AVG(prod_price) AS avg_price FROM Products;
+-----------+
| avg_price |
+-----------+
| 6.823333 |
+-----------+
1 row in set (0.00 sec)
MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> SELECT AVG(prod_price) AS avg_price FROM Products WHERE vend_id = 'DLL01';
+-----------+
| avg_price |
+-----------+
| 3.865000 |
+-----------+
1 row in set (0.00 sec)
MariaDB [sqlbzbh]>
9.1.2 COUNT()函数
可利用COUNT()函数获取表中行的数目或者符合特定条件的行的数目。
- COUNT(*)统计表中行的数目,包括NULL的行。
- COUNT(column)统计特定列中的行的数目,忽略NULL的行。
MariaDB [sqlbzbh]> SELECT * FROM Customers;
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
| cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
| 1000000001 | Village Toys | 200 Maple Lane | Detroit | MI | 44444 | USA | John Smith | sales@villagetoys.com |
| 1000000002 | Kids Place | 333 South Lake Drive | Columbus | OH | 43333 | USA | Michelle Green | NULL |
| 1000000003 | Fun4All | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | jjones@fun4all.com |
| 1000000004 | Fun4All | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Denise L. Stephens | dstephens@fun4all.com |
| 1000000005 | The Toy Store | 4545 53rd Street | Chicago | IL | 54545 | USA | Kim Howard | NULL |
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
5 rows in set (0.00 sec)
MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> SELECT COUNT(*) AS num_cust FROM Customers;
+----------+
| num_cust |
+----------+
| 5 |
+----------+
1 row in set (0.00 sec)
MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> SELECT COUNT(cust_email) AS num_cust FROM Customers;
+----------+
| num_cust |
+----------+
| 3 |
+----------+
1 row in set (0.00 sec)
MariaDB [sqlbzbh]>
9.1.3 MAX()函数
MAX()函数一般用来找出最大的数值或日期值。必须指定列名。
在用于文本数据时,则是返回该列排序后的最后一行。
MAX()函数忽略列值为NULL的行。
MariaDB [sqlbzbh]> SELECT * FROM Products;
+---------+---------+---------------------+------------+-----------------------------------------------------------------------+
| prod_id | vend_id | prod_name | prod_price | prod_desc |
+---------+---------+---------------------+------------+-----------------------------------------------------------------------+
| BNBG01 | DLL01 | Fish bean bag toy | 3.49 | Fish bean bag toy, complete with bean bag worms with which to feed it |
| BNBG02 | DLL01 | Bird bean bag toy | 3.49 | Bird bean bag toy, eggs are not included |
| BNBG03 | DLL01 | Rabbit bean bag toy | 3.49 | Rabbit bean bag toy, comes with bean bag carrots |
| BR01 | BRS01 | 8 inch teddy bear | 5.99 | 8 inch teddy bear, comes with cap and jacket |
| BR02 | BRS01 | 12 inch teddy bear | 8.99 | 12 inch teddy bear, comes with cap and jacket |
| BR03 | BRS01 | 18 inch teddy bear | 11.99 | 18 inch teddy bear, comes with cap and jacket |
| RGAN01 | DLL01 | Raggedy Ann | 4.99 | 18 inch Raggedy Ann doll |
| RYL01 | FNG01 | King doll | 9.49 | 12 inch king doll with royal garments and crown |
| RYL02 | FNG01 | Queen doll | 9.49 | 12 inch queen doll with royal garments and crown |
+---------+---------+---------------------+------------+-----------------------------------------------------------------------+
9 rows in set (0.00 sec)
MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> SELECT MAX(prod_price) AS max_price FROM Products;
+-----------+
| max_price |
+-----------+
| 11.99 |
+-----------+
1 row in set (0.00 sec)
MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> SELECT MAX(prod_name) AS max_prod_name FROM Products;
+---------------+
| max_prod_name |
+---------------+
| Raggedy Ann |
+---------------+
1 row in set (0.00 sec)
MariaDB [sqlbzbh]>
9.1.4 MIN()函数
MIN()函数返回最小的数值或日期值。必须指定列名。
在用于文本数据时,则是返回该列排序后的最前面的行。
MIN()函数忽略列值为NULL的行。
MariaDB [sqlbzbh]> SELECT MIN(prod_price) AS min_price FROM Products;
+-----------+
| min_price |
+-----------+
| 3.49 |
+-----------+
1 row in set (0.00 sec)
MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> SELECT MIN(prod_name) AS min_prod_name FROM Products;
+--------------------+
| min_prod_name |
+--------------------+
| 12 inch teddy bear |
+--------------------+
1 row in set (0.00 sec)
MariaDB [sqlbzbh]>
9.1.5 SUM()函数
SUM()函数用来返回指定列值的和。
SUM()函数忽略列值为NULL的行。
MariaDB [sqlbzbh]> SELECT * FROM OrderItems WHERE order_num = 20005;
+-----------+------------+---------+----------+------------+
| order_num | order_item | prod_id | quantity | item_price |
+-----------+------------+---------+----------+------------+
| 20005 | 1 | BR01 | 100 | 5.49 |
| 20005 | 2 | BR03 | 100 | 10.99 |
+-----------+------------+---------+----------+------------+
2 rows in set (0.00 sec)
MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> SELECT SUM(quantity) AS items_ordered FROM OrderItems WHERE order_num = 20005;
+---------------+
| items_ordered |
+---------------+
| 200 |
+---------------+
1 row in set (0.00 sec)
MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> SELECT SUM(item_price*quantity) AS total_price FROM OrderItems WHERE order_num = 20005;
+-------------+
| total_price |
+-------------+
| 1648.00 |
+-------------+
1 row in set (0.00 sec)
MariaDB [sqlbzbh]>
9.2 聚集不同值
对以上5个聚集函数都可以如下使用:
- 对所有行执行计算,指定ALL参数或不指定参数(ALL参数是默认行为)
- 只包含不同的值,指定DISTINCT参数。
MariaDB [sqlbzbh]> SELECT AVG(prod_price) AS avg_price FROM Products WHERE vend_id = 'DLL01';
+-----------+
| avg_price |
+-----------+
| 3.865000 |
+-----------+
1 row in set (0.00 sec)
MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> SELECT AVG(DISTINCT prod_price) AS avg_price FROM Products WHERE vend_id = 'DLL01';
+-----------+
| avg_price |
+-----------+
| 4.240000 |
+-----------+
1 row in set (0.00 sec)
MariaDB [sqlbzbh]>
9.3 组合聚集函数
SELECT语句可根据需要包含多个聚集函数。
注意:指定别名,不应该使用表中实际的列名。
MariaDB [sqlbzbh]> SELECT COUNT(*) AS num_items, MIN(prod_price) AS price_min, MAX(prod_price) AS price_max, AVG(prod_price) AS avg_price FROM Products;
+-----------+-----------+-----------+-----------+
| num_items | price_min | price_max | avg_price |
+-----------+-----------+-----------+-----------+
| 9 | 3.49 | 11.99 | 6.823333 |
+-----------+-----------+-----------+-----------+
1 row in set (0.00 sec)
MariaDB [sqlbzbh]>
相关文章
- oracle SQL Develop导出数据库中的表格数据到excel
- MP SQL 分析打印功能
- 读书笔记--SQL必知必会03--排序检索数据
- 读书笔记--SQL必知必会02--检索数据
- sql 语句系列(每个季度的开始日期和结束日期)[八百章之第二十二章]
- 手把手教你-----巧用Excel批量生成SQL语句,处理大量数据
- sql基础--怎样查询单列数据
- SQL Server 如何跨服务器复制表中数据
- 大数据基础之Hive(2)Hive SQL执行过程之SQL解析过程
- Hive之sql查询语句的处理步骤
- 读书笔记--SQL必知必会10--分组数据
- 读书笔记--SQL必知必会03--排序检索数据
- 面向对语法读取mysql数据库数据例:$db->query($sql)、$result->fetch_array()
- SQL工具性能实测:居然比Navicat还快,数百万行数据导出仅51秒
- MySQL关于根据日期查询数据的sql语句
- mysql索引_效率测试(包含测试sql脚本300万条数据),可用作教学案例。
- 也谈SQL Server 2008 处理隐式数据类型转换在运行计划中的增强 (续)
- MySQL 整体架构与 SQL 执行原理,数据库事务原理
- sql server 批量备份数据库及删除N天前的备份数据
- 009-Hadoop Hive sql语法详解4-DQL 操作:数据查询SQL-select、join、union、udtf
- 第2讲:SQL语言的通用语法及分类
- 【SQL开发实战技巧】系列(二十):数据仓库中时间类型操作(进阶)获取季度开始结束时间以及如何统计非连续性时间的数据
- SQL注入 Sqli-labs-Less-21(笔记)——还是回显注入 使用union select即可 但是要注意sql括号闭合 也可以报错注入
- 大数据Hadoop之——基于内存型SQL查询引擎Presto(Presto-Trino环境部署)