PostgreSQL中文文档网

Appearance

表达式技巧 ​ ​

在 PostgreSQL 中，表达式是由常量、运算符、函数、列名等组成的。熟练掌握表达式技巧，能显著提高查询的效率和灵活性。

条件表达式 ​ ​

CASE 表达式 ​ ​

CASE 表达式允许在 SQL 查询中执行条件逻辑。

简单 CASE 表达式 ​ ​

sql
SELECT name, 
       CASE department_id
         WHEN 1 THEN 'HR'
         WHEN 2 THEN 'Engineering'
         ELSE 'Other'
       END AS department_name
FROM employees;

搜索 CASE 表达式 ​ ​

sql
SELECT name, 
       CASE
         WHEN salary 100000 THEN 'High Salary'
         WHEN salary BETWEEN 50000 AND 100000 THEN 'Medium Salary'
         ELSE 'Low Salary'
       END AS salary_level
FROM employees;

COALESCE 表达式 ​ ​

COALESCE 函数返回第一个非 NULL 的值。

sql
SELECT name, COALESCE(phone_number, 'N/A') AS contact FROM employees;

NULLIF 表达式 ​ ​

NULLIF 函数在两个值相等时返回 NULL，否则返回第一个值。

sql
SELECT name, NULLIF(salary, 0) AS adjusted_salary FROM employees;

数学表达式 ​ ​

PostgreSQL 提供了丰富的数学运算符和函数，用于处理数值类型的运算。

数学运算符 ​ ​

  • +：加法
  • -：减法
  • *：乘法
  • /：除法
  • %：取余
sql
SELECT salary * 1.1 AS increased_salary FROM employees;
SELECT salary % 1000 AS salary_remainder FROM employees;

使用常量 ​ ​

PostgreSQL 允许在查询中使用常量，例如：

sql
SELECT salary + 5000 AS salary_with_bonus FROM employees;

字符串表达式 ​ ​

字符串连接 ​ ​

使用 || 运算符进行字符串连接。

sql
SELECT first_name || ' ' || last_name AS full_name FROM employees;

字符串的模式匹配 ​ ​

  • LIKE：模糊匹配
  • ILIKE：不区分大小写的模糊匹配
sql
SELECT name FROM employees WHERE name LIKE 'A%';
SELECT name FROM employees WHERE name ILIKE 'a%';

正则表达式匹配 ​ ​

PostgreSQL 支持通过正则表达式进行复杂的模式匹配。

  • ~：匹配正则表达式
  • ~*：不区分大小写的正则匹配
sql
SELECT name FROM employees WHERE name ~ '^A.*';
SELECT name FROM employees WHERE email ~* 'example.com';

日期与时间表达式 ​ ​

日期运算 ​ ​

PostgreSQL 允许对日期进行加减运算，支持使用 INTERVAL 类型。

sql
SELECT CURRENT_DATE + INTERVAL '1 month' AS next_month;
SELECT CURRENT_DATE - INTERVAL '1 day' AS yesterday;

日期格式化 ​ ​

使用 TO_CHAR 函数格式化日期或时间值。

sql
SELECT TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD') AS formatted_date;
SELECT TO_CHAR(CURRENT_TIMESTAMP, 'DD-Mon-YYYY HH24:MI:SS') AS formatted_time;

提取日期部分 ​ ​

使用 EXTRACT 函数提取日期的特定部分，如年、月、日等。

sql
SELECT EXTRACT(YEAR FROM CURRENT_DATE) AS current_year;
SELECT EXTRACT(MONTH FROM CURRENT_DATE) AS current_month;

集合与数组表达式 ​ ​

PostgreSQL 支持数组和集合操作，可以方便地处理多个值。

数组构造 ​ ​

使用 {} 来构造数组。

sql
SELECT ARRAY[1, 2, 3] AS numbers;
SELECT ARRAY['a', 'b', 'c'] AS letters;

数组操作 ​ ​

PostgreSQL 提供了多种操作符和函数用于数组操作。

  • array_length()：返回数组的长度
  • array_append()：向数组中添加元素
sql
SELECT array_length(ARRAY[1, 2, 3], 1) AS array_size;
SELECT array_append(ARRAY[1, 2, 3], 4) AS updated_array;

ANY 和 ALL 操作符 ​ ​

ANY 和 ALL 用于与集合中的元素进行比较。

sql
SELECT * FROM employees WHERE salary ANY (SELECT salary FROM employees WHERE department_id = 1);
SELECT * FROM employees WHERE salary ALL (SELECT salary FROM employees WHERE department_id = 1);

聚合与窗口函数 ​ ​

PostgreSQL 提供了强大的聚合和窗口函数，用于在查询中进行数据聚合和排序计算。

聚合函数 ​ ​

如 SUM、AVG、COUNT、MAX、MIN 等可以在查询中进行统计计算。

sql
SELECT department, AVG(salary) AS average_salary FROM employees GROUP BY department;

窗口函数 ​ ​

窗口函数允许在查询中对分组后的数据进行进一步操作，如排名、累计等。

sql
SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

小结 ​ ​

表达式技巧是 PostgreSQL 查询中不可或缺的部分，通过合理使用各种运算符、函数和表达式，可以提升查询效率和复杂度，满足多种复杂的数据处理需求。掌握这些技巧有助于优化数据查询和操作，提高数据库的使用效能。

表达式技巧 ​ ​

在 PostgreSQL 中，表达式是由常量、运算符、函数、列名等组成的。熟练掌握表达式技巧，能显著提高查询的效率和灵活性。

条件表达式 ​ ​

CASE 表达式 ​ ​

CASE 表达式允许在 SQL 查询中执行条件逻辑。

简单 CASE 表达式 ​ ​

sql
SELECT name, 
       CASE department_id
         WHEN 1 THEN 'HR'
         WHEN 2 THEN 'Engineering'
         ELSE 'Other'
       END AS department_name
FROM employees;

搜索 CASE 表达式 ​ ​

sql
SELECT name, 
       CASE
         WHEN salary 100000 THEN 'High Salary'
         WHEN salary BETWEEN 50000 AND 100000 THEN 'Medium Salary'
         ELSE 'Low Salary'
       END AS salary_level
FROM employees;

COALESCE 表达式 ​ ​

COALESCE 函数返回第一个非 NULL 的值。

sql
SELECT name, COALESCE(phone_number, 'N/A') AS contact FROM employees;

NULLIF 表达式 ​ ​

NULLIF 函数在两个值相等时返回 NULL，否则返回第一个值。

sql
SELECT name, NULLIF(salary, 0) AS adjusted_salary FROM employees;

数学表达式 ​ ​

PostgreSQL 提供了丰富的数学运算符和函数，用于处理数值类型的运算。

数学运算符 ​ ​

  • +：加法
  • -：减法
  • *：乘法
  • /：除法
  • %：取余
sql
SELECT salary * 1.1 AS increased_salary FROM employees;
SELECT salary % 1000 AS salary_remainder FROM employees;

使用常量 ​ ​

PostgreSQL 允许在查询中使用常量，例如：

sql
SELECT salary + 5000 AS salary_with_bonus FROM employees;

字符串表达式 ​ ​

字符串连接 ​ ​

使用 || 运算符进行字符串连接。

sql
SELECT first_name || ' ' || last_name AS full_name FROM employees;

字符串的模式匹配 ​ ​

  • LIKE：模糊匹配
  • ILIKE：不区分大小写的模糊匹配
sql
SELECT name FROM employees WHERE name LIKE 'A%';
SELECT name FROM employees WHERE name ILIKE 'a%';

正则表达式匹配 ​ ​

PostgreSQL 支持通过正则表达式进行复杂的模式匹配。

  • ~：匹配正则表达式
  • ~*：不区分大小写的正则匹配
sql
SELECT name FROM employees WHERE name ~ '^A.*';
SELECT name FROM employees WHERE email ~* 'example.com';

日期与时间表达式 ​ ​

日期运算 ​ ​

PostgreSQL 允许对日期进行加减运算，支持使用 INTERVAL 类型。

sql
SELECT CURRENT_DATE + INTERVAL '1 month' AS next_month;
SELECT CURRENT_DATE - INTERVAL '1 day' AS yesterday;

日期格式化 ​ ​

使用 TO_CHAR 函数格式化日期或时间值。

sql
SELECT TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD') AS formatted_date;
SELECT TO_CHAR(CURRENT_TIMESTAMP, 'DD-Mon-YYYY HH24:MI:SS') AS formatted_time;

提取日期部分 ​ ​

使用 EXTRACT 函数提取日期的特定部分，如年、月、日等。

sql
SELECT EXTRACT(YEAR FROM CURRENT_DATE) AS current_year;
SELECT EXTRACT(MONTH FROM CURRENT_DATE) AS current_month;

集合与数组表达式 ​ ​

PostgreSQL 支持数组和集合操作，可以方便地处理多个值。

数组构造 ​ ​

使用 {} 来构造数组。

sql
SELECT ARRAY[1, 2, 3] AS numbers;
SELECT ARRAY['a', 'b', 'c'] AS letters;

数组操作 ​ ​

PostgreSQL 提供了多种操作符和函数用于数组操作。

  • array_length()：返回数组的长度
  • array_append()：向数组中添加元素
sql
SELECT array_length(ARRAY[1, 2, 3], 1) AS array_size;
SELECT array_append(ARRAY[1, 2, 3], 4) AS updated_array;

ANY 和 ALL 操作符 ​ ​

ANY 和 ALL 用于与集合中的元素进行比较。

sql
SELECT * FROM employees WHERE salary ANY (SELECT salary FROM employees WHERE department_id = 1);
SELECT * FROM employees WHERE salary ALL (SELECT salary FROM employees WHERE department_id = 1);

聚合与窗口函数 ​ ​

PostgreSQL 提供了强大的聚合和窗口函数，用于在查询中进行数据聚合和排序计算。

聚合函数 ​ ​

如 SUM、AVG、COUNT、MAX、MIN 等可以在查询中进行统计计算。

sql
SELECT department, AVG(salary) AS average_salary FROM employees GROUP BY department;

窗口函数 ​ ​

窗口函数允许在查询中对分组后的数据进行进一步操作，如排名、累计等。

sql
SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

小结 ​ ​

表达式技巧是 PostgreSQL 查询中不可或缺的部分，通过合理使用各种运算符、函数和表达式，可以提升查询效率和复杂度，满足多种复杂的数据处理需求。掌握这些技巧有助于优化数据查询和操作，提高数据库的使用效能。

Released under the MIT License.

Copyright © 2024-present PostgreSQL 中文社区