PostgreSQL学习笔记（五）：查询函数、谓词、case表达式、集合运算、VACUUM

PostgreSQL学习笔记（五）：查询函数、谓词、case表达式、集合运算、VACUUM

查询函数

1. 常用函数

• 聚合函数
• 数学函数
• 三角函数
• 字符处理函数
• 数据类型转换函数

2. 可以在SELECT 语句以及WHERE中使用,包括

• String Functions
• Format Functions
• Date & Time Fuctions
• Aggregate Functions

3. 通用 PostgreSQL内置函数的列表:

• COUNT 函数：用于计算数据库表中的行数。
• MAX函数：用于查询某一特定列中最大值。
• MIN函数：用于查询某一特定列中最小值。
• AVG 函数：用于计算某一特定列中平均值。
• SUM函数：用于计算数字列所有值的总和。
• ARRAY 函数：用于输入值(包括null)添加到数组中。
• Numeric 函数：完整列出一个SQL中所需的操作数的函数。
• String 函数：完整列出一个SQL中所需的操作字符的函数。

谓词

我们在SQL中接触的BETWEEN、LIKE、IN、IS NULL、=、<、**>**等关键词都可以

称之为SQL中的谓词，它是一种返回值只为真值（true、false或者unknown）的特殊

函数。它的目的是为了给SQL命题提供判断真假的手段,谓词逻辑的出现对SQL而言具有

划时代的意义。

非空判断：IS NULL / IS NOT NUNLL

case表达式

SQL CASE表达式是一种通用的条件表达，类似于其它语言中的if/else语句。

CASE WHEN condition THEN result

​ [WHEN … ]
​ [ELSE result]
END

SELECT adate,
       CASE WHEN temp > 15 THEN '暖和'
            WHEN temp < 15 THEN '微凉'
            ELSE 'other'
       END
    FROM test;

集合运算

PostgreSQL 同样支持集合论中的集合操作，包括

• 并集（UNION）

  UNION操作符用于将两个查询结果合并成一个结果集，返回出现在第一个查询或者出现在
  第二个查询中的数据：

  SELECT column1, column2
  FROM table1
  UNION [DISTINCT | ALL]
  SELECT col1, col2
  FROM table2;

  其中，DISTINCT表示将合并后的结果集进行去重；ALL表示保留结果集中的重复记录；如果省
  略，默认为DISTINCT。

• 交集（INTERSECT）

  INTERSECT操作符用于返回两个查询结果中的共同部分，即同时出现在第一个查询结果和
  第二个查询结果中的数据：

  SELECT column1, column2
  FROM table1
  INTERSECT [DISTINCT | ALL]
  SELECT col1, col2
  FROM table2;

  其中，DISTINCT表示将合并后的结果集进行去重；ALL表示保留结果集中的重复记录；如果省
  略。默认为DISTINCT。

• 差集（EXCEPT）

​ EXCEPT操作符用于返回出现在第一个查询结果中，但不在第二个查询结果中的数据:

​ SELECT column1, column2
​ FROM table1
​ EXCEPT [DISTINCT | ALL]
​ SELECT col1, col2
​ FROM table2;

​ 其中，DISTINCT表示将合并后的结果集进行去重；ALL表示保留结果集中的重复记录；如
​ 果省略，默认为DISTINCT。

表连接

PostgreSQL 支持各种类型的SQL连接查询:

• 内连接（INNER JOIN）
• 左外连接（LEFT OUTER JOIN）
• 右外连接（RIGHT OUTER JOIN）
• 全外连接（FULL OUTER JOIN）
• 交叉连接（CROSS JOIN）
• 自然连接（NATURAL JOIN）
• 自连接（Self Join）

VACUUM

主要作用
磁盘清理（清理dead tuple）；更新统计信息;重组数据;解决事务ID回卷问题
语法结构
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, … ] ) ] ]
◆说明:
vacuum ：不要求获得排它锁,找到那些旧的“死”数据，标记为可用状态,不进行空间合并；
vacuum full：就是除了vacuum,进行空间合并,它需要lock table；
vacuum analyze：更新统计信息,使得优化器能够选择更好的方案执行sql；
vacuum freeze：表记录冻结,可解决事务id回卷的问题；