在PostgreSQL中,函数是处理和操作数据的强大工具。以下是一些常用函数的用法示例。
1. 字符串函数
字符串函数用于操作和处理文本数据,常见操作包括字符串连接、截取、替换、转换大小写等。
LENGTH
: 返回字符串的长度。-- 查询语句 SELECT LENGTH('PostgreSQL') AS string_length; -- 结果 string_length -------------- 10
CONCAT
: 将多个字符串连接为一个字符串。
-- 查询语句
SELECT CONCAT('Postgre', 'SQL') AS full_string;
-- 结果
full_string
-------------
PostgreSQL
SUBSTRING
: 从指定字符串中提取子字符串。
-- 查询语句
SELECT SUBSTRING('PostgreSQL', 1, 6) AS short_string;
-- 结果
short_string
--------------
Postgre
REPLACE
: 将字符串中的子字符串替换为另一个字符串。
-- 查询语句
SELECT REPLACE('PostgreSQL', 'SQL', 'DB') AS replaced_string;
-- 结果
replaced_string
-----------------
PostgreDB
-
UPPER
: 将字符串转换为大写。-- 查询语句 SELECT UPPER('postgresql') AS upper_string; -- 结果 upper_string -------------- POSTGRESQL
-
LOWER
: 将字符串转换为小写。-- 查询语句 SELECT LOWER('PostgreSQL') AS lower_string; -- 结果 lower_string -------------- postgresql
-
TRIM
: 删除字符串开头和结尾的空白或指定字符。-- 查询语句 SELECT TRIM(BOTH ' ' FROM ' PostgreSQL ') AS trimmed_string; -- 结果 trimmed_string ---------------- PostgreSQL
-
POSITION
: 查找子字符串在另一个字符串中的位置(起始位置)。-- 查询语句 SELECT POSITION('SQL' IN 'PostgreSQL') AS substring_position; -- 结果 substring_position -------------------- 8
2. 日期和时间函数
日期和时间函数用于处理日期、时间和时间戳数据,支持时间差计算、格式化输出、提取特定时间部分等。
-
CURRENT_DATE
: 返回当前日期。-- 查询语句 SELECT CURRENT_DATE AS today; -- 结果 today ------------ 2024-09-03
-
CURRENT_TIME
: 返回当前时间(不包含日期)。-- 查询语句 SELECT CURRENT_TIME AS now_time; -- 结果 now_time ------------- 14:30:00
-
CURRENT_TIMESTAMP
: 返回当前日期和时间(带有时区信息)。-- 查询语句 SELECT CURRENT_TIMESTAMP AS current_datetime; -- 结果 current_datetime ----------------------- 2024-09-03 14:30:00+00
-
DATE_TRUNC
: 截断日期或时间戳,保留指定的时间单位(如年、月、日)。-- 查询语句 SELECT DATE_TRUNC('month', NOW()) AS month_start; -- 结果 month_start ---------------------- 2024-09-01 00:00:00
-
EXTRACT
: 从日期或时间戳中提取特定部分(如年、月、日、小时等)。-- 查询语句 SELECT EXTRACT(YEAR FROM CURRENT_DATE) AS current_year; -- 结果 current_year -------------- 2024
-
AGE
: 计算两个时间戳之间的差值,并以年、月、日的形式显示。-- 查询语句 SELECT AGE('2020-01-01') AS time_diff; -- 结果 time_diff ------------------ 4 years 8 mons
-
TO_CHAR
: 将日期或数字转换为指定格式的字符串。-- 查询语句 SELECT TO_CHAR(NOW(), 'YYYY-MM-DD') AS formatted_date; -- 结果 formatted_date ---------------- 2024-09-03
-
DATE_PART
: 类似于EXTRACT
,用于提取日期或时间戳的特定部分,以浮点数形式返回。-- 查询语句 SELECT DATE_PART('hour', CURRENT_TIMESTAMP) AS current_hour; -- 结果 current_hour -------------- 14
3. 数学函数
数学函数用于数值计算,包括四舍五入、取整、求平方根等操作,适用于统计分析、财务计算等场景。
-
ROUND
: 对数字进行四舍五入,保留指定的小数位数。-- 查询语句 SELECT ROUND(123.456, 2) AS rounded_value; -- 结果 rounded_value --------------- 123.46
-
FLOOR
: 返回小于或等于给定数值的最大整数。-- 查询语句 SELECT FLOOR(123.456) AS floor_value; -- 结果 floor_value ------------- 123
-
CEIL
: 返回大于或等于给定数值的最小整数。-- 查询语句 SELECT CEIL(123.456) AS ceil_value; -- 结果 ceil_value ------------ 124
-
ABS
: 返回数值的绝对值。-- 查询语句 SELECT ABS(-123.456) AS absolute_value; -- 结果 absolute_value ---------------- 123.456
-
MOD
: 返回两个数相除的余数。-- 查询语句 SELECT MOD(10, 3) AS remainder; -- 结果 remainder ----------- 1
-
POWER
: 返回指定数值的指定次幂。-- 查询语句 SELECT POWER(2, 3) AS result; -- 结果 result -------- 8
-
SQRT
: 返回数值的平方根。-- 查询语句 SELECT SQRT(16) AS square_root; -- 结果 square_root ------------- 4
-
EXP
: 返回e的指定次幂。-- 查询语句 SELECT EXP(1) AS e_value; -- 结果 e_value --------- 2.71828
-
LN
: 返回数值的自然对数(底数为e)。-- 查询语句 SELECT LN(2.71828) AS natural_log; -- 结果 natural_log ------------- 1.0000067
-
LOG
: 返回指定基数的对数。-- 查询语句 SELECT LOG(10, 100) AS log_value; -- 结果 log_value ----------- 2
-
SIGN
: 返回数值的符号,正数返回1,负数返回-1,零返回0。-- 查询语句 SELECT SIGN(-123.456) AS sign_value; -- 结果 sign_value ------------ -1
4. 控制流函数
控制流函数用于处理NULL值,选择最大值或最小值等。这些函数在处理数据不确定性或条件判断时非常有用。
-
COALESCE
: 返回第一个非NULL的值,如果所有值都是NULL,则返回NULL。-- 查询语句 SELECT COALESCE(NULL, NULL, 'default') AS result_value; -- 结果 result_value -------------- default
-
GREATEST
: 返回提供的多个值中的最大值。-- 查询语句 SELECT GREATEST(10, 20, 30, 15) AS max_value; -- 结果 max_value ----------- 30
-
LEAST
: 返回提供的多个值中的最小值。-- 查询语句 SELECT LEAST(10, 20, 30, 15) AS min_value; -- 结果 min_value ----------- 10
5. 随机数与序列函数
随机数与序列函数用于生成随机数、自动递增序列等操作,适用于数据采样、模拟测试等场景。
-
RANDOM
: 生成一个0到1之间的随机浮点数。-- 查询语句 SELECT RANDOM() AS random_value; -- 结果 random_value -------------- 0.345678
-
SERIAL
: 用于生成自动递增的整数序列,通常用于主键字段。-- 查询语句 CREATE TABLE test_table ( id SERIAL PRIMARY KEY, name VARCHAR(100) ); -- 插入数据 INSERT INTO test_table (name) VALUES ('Alice'), ('Bob'); -- 查询结果 SELECT * FROM test_table; -- 结果 id | name ----+------ 1 | Alice 2 | Bob
-
GENERATE_SERIES
: 生成一个指定范围的整数序列。-- 查询语句 SELECT * FROM GENERATE_SERIES(1, 5); -- 结果 generate_series ---------------- 1 2 3 4 5
继续对PostgreSQL中的常用函数进行分类讲解,以下是更多的函数类别以及对应的函数用法示例。每个示例都包含了SQL查询语句和预期的查询结果,帮助您更直观地理解这些函数的作用和用法。
6. 聚合函数
聚合函数用于对一组值执行计算并返回单一的结果,常用于统计和数据分析。例如,计算总和、平均值、计数、最大值和最小值等。这些函数在报告生成、数据汇总等场景中非常实用。
-
COUNT
: 计算行数或非NULL值的数量。-- 查询语句:计算表中员工的总数 SELECT COUNT(*) AS total_employees FROM employees; -- 结果 total_employees ---------------- 50
-
SUM
: 计算数值列的总和。-- 查询语句:计算所有员工的工资总和 SELECT SUM(salary) AS total_salary FROM employees; -- 结果 total_salary ------------- 500000
-
AVG
: 计算数值列的平均值。-- 查询语句:计算员工工资的平均值 SELECT AVG(salary) AS average_salary FROM employees; -- 结果 average_salary --------------- 10000
-
MAX
: 返回数值列中的最大值。-- 查询语句:获取最高工资 SELECT MAX(salary) AS highest_salary FROM employees; -- 结果 highest_salary --------------- 20000
-
MIN
: 返回数值列中的最小值。-- 查询语句:获取最低工资 SELECT MIN(salary) AS lowest_salary FROM employees; -- 结果 lowest_salary -------------- 5000
-
STRING_AGG
: 将字符串值连接成一个字符串,并使用指定的分隔符。-- 查询语句:将所有部门名称连接成一个字符串 SELECT STRING_AGG(department_name, ', ') AS departments FROM departments; -- 结果 departments ------------------------------- Sales, Marketing, HR, IT, Finance
-
ARRAY_AGG
: 将输入值收集到一个数组中。-- 查询语句:将所有员工的姓名收集到一个数组中 SELECT ARRAY_AGG(employee_name) AS employee_names FROM employees; -- 结果 employee_names -------------------------------------------- {Alice,Bob,Charlie,David,Eva,...}
7. JSON函数
JSON函数用于在PostgreSQL中处理JSON数据。随着NoSQL和JSON数据格式的流行,PostgreSQL提供了丰富的函数来存储、查询和操作JSON数据,适用于处理复杂和嵌套的数据结构。
-
TO_JSON
: 将任意行或数组转换为JSON格式。-- 查询语句:将文本转换为JSON格式 SELECT TO_JSON('PostgreSQL') AS json_value; -- 结果 json_value ------------ "PostgreSQL"
-
TO_JSONB
: 将任意行或数组转换为二进制JSON(JSONB)格式,具有更好的性能。-- 查询语句:将数组转换为JSONB格式 SELECT TO_JSONB(ARRAY[1, 2, 3]) AS jsonb_value; -- 结果 jsonb_value ------------- [1, 2, 3]
-
JSON_BUILD_OBJECT
: 构建一个JSON对象。-- 查询语句:创建一个JSON对象表示员工信息 SELECT JSON_BUILD_OBJECT( 'id', 1, 'name', 'Alice', 'department', 'HR' ) AS employee_json; -- 结果 employee_json ------------------------------------------- {"id": 1, "name": "Alice", "department": "HR"}
-
JSON_EXTRACT_PATH_TEXT
: 从JSON对象中提取指定路径的文本值。-- 查询语句:从JSON数据中提取员工姓名 SELECT JSON_EXTRACT_PATH_TEXT('{"id":1,"name":"Alice"}', 'name') AS employee_name; -- 结果 employee_name -------------- Alice
-
JSONB_AGG
: 将多行数据聚合为JSONB数组。-- 查询语句:将所有员工信息聚合为JSONB数组 SELECT JSONB_AGG( JSONB_BUILD_OBJECT( 'id', id, 'name', name, 'department', department ) ) AS employees_json FROM employees; -- 结果 employees_json ----------------------------------------------------------------------- [ {"id": 1, "name": "Alice", "department": "HR"}, {"id": 2, "name": "Bob", "department": "IT"}, ... ]
-
JSONB_SET
: 更新JSONB对象中的指定键值对。-- 查询语句:更新员工JSON对象中的部门信息 SELECT JSONB_SET('{"id":1,"name":"Alice","department":"HR"}', '{department}', '"Finance"') AS updated_json; -- 结果 updated_json ----------------------------------------------- {"id": 1, "name": "Alice", "department": "Finance"}
8. 数组函数
数组函数用于创建、处理和查询数组类型的数据。在PostgreSQL中,数组是一等公民,可以直接存储和操作多值数据,适用于需要存储列表或集合的场景。
-
ARRAY
: 创建一个数组。-- 查询语句:创建一个整数数组 SELECT ARRAY[1, 2, 3, 4, 5] AS number_array; -- 结果 number_array ---------------- {1,2,3,4,5}
-
ARRAY_APPEND
: 向数组末尾添加元素。-- 查询语句:在数组末尾添加一个元素 SELECT ARRAY_APPEND(ARRAY[1, 2, 3], 4) AS updated_array; -- 结果 updated_array --------------- {1,2,3,4}
-
ARRAY_PREPEND
: 向数组开头添加元素。-- 查询语句:在数组开头添加一个元素 SELECT ARRAY_PREPEND(0, ARRAY[1, 2, 3]) AS updated_array; -- 结果 updated_array --------------- {0,1,2,3}
-
UNNEST
: 将数组展开为多行。-- 查询语句:将数组元素展开为多行 SELECT UNNEST(ARRAY['Alice', 'Bob', 'Charlie']) AS employee_name; -- 结果 employee_name -------------- Alice Bob Charlie
-
ARRAY_AGG
: 将多行数据聚合为数组。-- 查询语句:将所有部门名称聚合为一个数组 SELECT ARRAY_AGG(department_name) AS departments_array FROM departments; -- 结果 departments_array ------------------------------- {Sales,Marketing,HR,IT,Finance}
-
CARDINALITY
: 返回数组的元素数量。-- 查询语句:获取数组的元素数量 SELECT CARDINALITY(ARRAY[1, 2, 3, 4, 5]) AS array_size; -- 结果 array_size ----------- 5
-
ARRAY_CONTAINS
: 检查数组是否包含指定元素。-- 查询语句:检查数组是否包含元素3 SELECT 3 = ANY(ARRAY[1, 2, 3, 4, 5]) AS contains_element; -- 结果 contains_element ---------------- t
9. 类型转换函数
类型转换函数用于在不同数据类型之间进行转换,确保数据在存储和处理时具有正确的类型。这些函数在数据清洗和预处理过程中非常重要。
-
CAST
: 将一个值显式转换为指定类型。-- 查询语句:将字符串转换为整数 SELECT CAST('123' AS INTEGER) AS integer_value; -- 结果 integer_value -------------- 123
-
::
(类型转换运算符): 另一种类型转换的简写形式。-- 查询语句:将字符串转换为日期 SELECT '2024-09-03'::DATE AS date_value; -- 结果 date_value ------------ 2024-09-03
-
TO_CHAR
: 将数字或日期转换为字符串,支持格式化。-- 查询语句:将数字格式化为带有千位分隔符的字符串 SELECT TO_CHAR(1234567.89, 'FM9,999,999.00') AS formatted_number; -- 结果 formatted_number ----------------- 1,234,567.89
-
TO_NUMBER
: 将字符串转换为数字,支持解析格式化的字符串。-- 查询语句:将格式化字符串转换为数字 SELECT TO_NUMBER('1,234,567.89', 'FM9,999,999.00') AS numeric_value; -- 结果 numeric_value -------------- 1234567.89
-
TO_TIMESTAMP
: 将字符串转换为时间戳,支持指定格式。-- 查询语句:将字符串转换为时间戳 SELECT TO_TIMESTAMP('2024-09-03 14:30:00', 'YYYY-MM-DD HH24:MI:SS') AS timestamp_value; -- 结果 timestamp_value ------------------------ 2024-09-03 14:30:00+00
-
TO_DATE
: 将字符串转换为日期,支持指定格式。-- 查询语句:将字符串转换为日期 SELECT TO_DATE('03-09-2024', 'DD-MM-YYYY') AS date_value; -- 结果 date_value ------------ 2024-09-03
10. 条件函数
条件函数用于在SQL查询中实现条件逻辑,类似于编程语言中的if-else结构。这些函数可以根据不同的条件返回不同的结果,增强查询的灵活性和智能性。
-
CASE WHEN
: 实现条件判断,根据条件返回不同的值。-- 查询语句:根据员工工资等级返回相应的级别描述 SELECT employee_name, salary, CASE WHEN salary >= 15000 THEN 'High' WHEN salary >= 10000 THEN 'Medium' ELSE 'Low' END AS salary_level FROM employees; -- 结果 employee_name | salary | salary_level --------------+--------+-------------- Alice | 16000 | High Bob | 11000 | Medium Charlie | 8000 | Low
-
NULLIF
: 如果两个参数相等,返回NULL,否则返回第一个参数。-- 查询语句:如果销售额为0,返回NULL,否则返回销售额 SELECT NULLIF(sales, 0) AS adjusted_sales FROM sales_data; -- 结果 adjusted_sales --------------- NULL 5000 10000
-
COALESCE
: 返回参数列表中第一个非NULL的值。-- 查询语句:获取员工的联系邮箱,如果没有则使用默认邮箱 SELECT COALESCE(personal_email, work_email, 'noemail@example.com') AS contact_email FROM employees; -- 结果 contact_email ---------------------- alice@personal.com bob@work.com noemail@example.com
-
GREATEST
: 返回参数列表中的最大值。-- 查询语句:获取每个员工的最高评分 SELECT employee_name, GREATEST(score1, score2, score3) AS highest_score FROM performance_reviews; -- 结果 employee_name | highest_score --------------+--------------- Alice | 95 Bob | 88 Charlie | 92
-
LEAST
: 返回参数列表中的最小值。-- 查询语句:获取每个员工的最低评分 SELECT employee_name, LEAST(score1, score2, score3) AS lowest_score FROM performance_reviews; -- 结果 employee_name | lowest_score --------------+-------------- Alice | 85 Bob | 78 Charlie | 80
通过对这些函数讲解,你可以更好地理解PostgreSQL的丰富功能,并在不同的场景下选择合适的函数来提高查询效率和数据处理能力。