常用函数 | |
---|---|
COUNT | 计算表中的记录数 |
SUM | 计算表中数据列中数据的合计值 |
AVG | 计算表中数据列中数据的平均数 |
MAX | 求出表中数据列中数据的最大值 |
MIN | 求出表中数据列中数据的最小值 |
# 使用COUNT(*) 计算表中的全部行数
SELECT COUNT(*) # COUNT(参数)
FROM Product;
# 输出值为返回值
+-------------------+
| COUNT(sale_price) |
+-------------------+
| 8 |
+-------------------+
# 将具体的对象作为参数,传给COUNT()
SELECT COUNT(purchase_price)
FROM Product;
SELECT COUNT(*), COUNT(purchase_price)
FROM Product;
# 输出结果
+----------+-----------------------+
| COUNT(*) | COUNT(purchase_price) |
+----------+-----------------------+
| 8 | 5 |
+----------+-----------------------+
COUNT
函数的结果根据参数的不同而不同COUNT(*)
会得到包含NULL的数据行数COUNT(<列名>)
会得到NULL之外的数据行数SELECT SUM(sale_price)
FROM Product;
+-----------------+
| SUM(sale_price) |
+-----------------+
| 16780 |
+-----------------+
SUM
函数来说,即使包含NULL
,也可以计算出合计值NULL
排除在外;COUNT(*)
例外SELECT AVG(sale_price)
FROM Product;
+-----------------+
| AVG(sale_price) |
+-----------------+
| 2097.5000 |
+-----------------+
NULL
行,值的个数 = 总行数 - NULL的行数
SELECT MAX(sale_price)
FROM Product;
+------------------+
| MAX(sale_price ) |
+------------------+
| 6800 |
+------------------+
SUM/AVG
函数只能对数值类型的列使用MAX/MIN
函数原则上可以适用于任何数据类型的列SELECT MAX(product_name)
FROM Product;
+-------------------+
| MAX(product_name) |
+-------------------+
| 高压锅 |
+-------------------+
# 去除重复数据后的数据行数
SELECT COUNT(DISTINCT product_type)
FROM Product;
+------------------------------+
| COUNT(DISTINCT product_type) |
+------------------------------+
| 3 |
+------------------------------+
# 先计算数据行数再删除重复数据的结果
SELECT DISTINCT COUNT(product_type)
FROM Product;
COUNT
,所有聚合函数都可以使用DISTINCT
# 语法
SELECT <列名1>, <列名2>, <列名3>, ...
FROM <表名>
GROUP BY <列名1>, <列名2>, ...; # 组合键 / 分组列
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type;
+--------------+----------+
| product_type | COUNT(*) |
+--------------+----------+
| 衣服 | 2 |
| 办公用品 | 2 |
| 厨房用具 | 4 |
+--------------+----------+
SELECT -> FROM -> WHERE -> GROUP BY
SELECT purchase_price, COUNT(*)
FROM Product
GROUP BY purchase_price;
+----------------+----------+
| purchase_price | COUNT(*) |
+----------------+----------+
| 500 | 1 |
| 320 | 1 |
| 2800 | 2 |
| 5000 | 1 |
| NULL | 3 |
+----------------+----------+
SELECT purchase_price, COUNT(*)
FROM Product
WHERE product_type = '衣服'
GROUP BY purchase_price;
+----------------+----------+
| purchase_price | COUNT(*) |
+----------------+----------+
| 500 | 1 |
| 2800 | 1 |
+----------------+----------+
法则1:使用GROUP BY
子句时,SELECT
子句中不能出现聚合键之外的列名
法则2:在GROUP BY
子句中不能使用SELECT
子句中定义的别名
法则3:GROUP BY
子句结果的显示是无序的
法则4:只有SELECT
子句和HAVING
子句(以及ORDER BY
子句)中能够使用聚合函数
【说明】:
DISTINCT
GROUP BY
WHERE
子句只能指定(行)的条件,而不能用来指定组的条件HAVING
子句的语法SELECT <列名1>, <列名2>, <列名3>, ...
FROM <表名>
GROUP BY <列名1>, <列名2>, <列名3>, ...
HAVING <分组结果对应的条件>;
HAVING
子句时SELECT
语句的顺序SELECT -> FROM -> WHERE -> GROUP BY -> HAVING
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type
HAVING COUNT(*) = 2;
+--------------+----------+
| product_type | COUNT(*) |
+--------------+----------+
| 衣服 | 2 |
| 办公用品 | 2 |
+--------------+----------+
GROUP BY
子句中指定的列名(即聚合键)SELECT <列名1>, <列名2>, ...
FROM <表名>
ORDER BY <排序基准列1>, <排序基准列2>,... # 排序键
ORDER BY
子句时SELECT
语句的顺序SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY
SELECT product_id, product_name, sale_price, purchase_price
FROM Product
ORDER BY sale_price;
# 按照 sale_price进行升序排列
+------------+--------------+------------+----------------+
| product_id | product_name | sale_price | purchase_price |
+------------+--------------+------------+----------------+
| 0008 | 圆珠笔 | 100 | NULL |
| 0002 | 打孔器 | 500 | 320 |
| 0006 | 叉子 | 500 | NULL |
| 0007 | 擦彩板 | 880 | NULL |
| 0001 | T恤衫 | 1000 | 500 |
| 0004 | 菜刀 | 3000 | 2800 |
| 0003 | 运动T恤 | 4000 | 2800 |
| 0005 | 高压锅 | 6800 | 5000 |
+------------+--------------+------------+----------------+
# 默认指定为升序,正式书写可用关键字 ASC
# 在列名后使用 DESC关键字,指定降序排列
SELECT product_id, product_name, sale_price, purchase_price
FROM Product
ORDER BY sale_price DESC;
+------------+--------------+------------+----------------+
| product_id | product_name | sale_price | purchase_price |
+------------+--------------+------------+----------------+
| 0005 | 高压锅 | 6800 | 5000 |
| 0003 | 运动T恤 | 4000 | 2800 |
| 0004 | 菜刀 | 3000 | 2800 |
| 0001 | T恤衫 | 1000 | 500 |
| 0007 | 擦彩板 | 880 | NULL |
| 0002 | 打孔器 | 500 | 320 |
| 0006 | 叉子 | 500 | NULL |
| 0008 | 圆珠笔 | 100 | NULL |
+------------+--------------+------------+----------------+
ORDER BY
子句中同时指定多个排序键;规则是优先使用左侧的键# 优先按照sale_price进行升序排列
# 在sale_price相同的情况下,按照product_id排序
SELECT product_id, product_name, sale_price, purchase_price
FROM Product
ORDER BY sale_price, product_id;
+------------+--------------+------------+----------------+
| product_id | product_name | sale_price | purchase_price |
+------------+--------------+------------+----------------+
| 0008 | 圆珠笔 | 100 | NULL |
| 0002 | 打孔器 | 500 | 320 |
| 0006 | 叉子 | 500 | NULL |
| 0007 | 擦彩板 | 880 | NULL |
| 0001 | T恤衫 | 1000 | 500 |
| 0004 | 菜刀 | 3000 | 2800 |
| 0003 | 运动T恤 | 4000 | 2800 |
| 0005 | 高压锅 | 6800 | 5000 |
+------------+--------------+------------+----------------+
SELECT product_id, product_name, sale_price, purchase_price
FROM Product
ORDER BY purchase_price;
SELECT product_id AS id, product_name, sale_price AS sp, purchase_price
FROM Product
ORDER BY sp, id;
+------+--------------+------+----------------+
| id | product_name | sp | purchase_price |
+------+--------------+------+----------------+
| 0008 | 圆珠笔 | 100 | NULL |
| 0002 | 打孔器 | 500 | 320 |
| 0006 | 叉子 | 500 | NULL |
| 0007 | 擦彩板 | 880 | NULL |
| 0001 | T恤衫 | 1000 | 500 |
| 0004 | 菜刀 | 3000 | 2800 |
| 0003 | 运动T恤 | 4000 | 2800 |
| 0005 | 高压锅 | 6800 | 5000 |
+------+--------------+------+----------------+
SELECT product_name, sale_price, purchase_price
FROM Product
ORDER BY product_id; # 可以使用 SELECT中未包含的列
SELECT product_name, sale_price, purchase_price
FROM Product
GROUP BY product_type
ORDER BY COUNT(*); # 可以使用 聚合函数
ORDER BY
子句中可使用SELECT子句中未使用的列和聚合函数ORDER BY
子句中不要使用列编号SELECT product_id, SUM(product_name)
-- 本SELECT语句中存在错误
FROM Product
GROUP BY product_type;
WHERE regist_date > '2009-09-01'
/*
1. WHERE 应该在 GROUP之前
2. SELECT中不能出现 非聚合键
*/
SELECT sale_price, purchase_price
FROM Product
GROUP BY product_type
HAVING SUM(sale_price) >= 1.5 * SUM(purchase_price);
本文地址:https://blog.csdn.net/GrayD1419/article/details/107170736
如对本文有疑问, 点击进行留言回复!!
荐 灵活使用线程池 & 创建用户账号信息SQL数据库--社交APP-笔记整理
PostgreSQL select for update指定列(兼容oracle)
SQL语句中的WHERE、聚合函数(SUM、MIN、MAX、AVG、COUNT)、HAVING
sql server登录名、服务器角色、数据库用户、数据库角色、架构区别联系
网友评论