随手一记（PostgreSql）

LFly_ice

已于 2024-07-15 10:13:00 修改

阅读量1.5k

点赞数 29

文章标签： postgresql 数据库

于 2024-06-18 17:35:37 首次发布

本文链接：https://blog.youkuaiyun.com/MDS_jiuyue/article/details/139379183

版权

一、类型转换

在 SQL 中，:: 是类型转换操作符，用于将一个值显式地转换为指定的类型。在你提供的 SQL 片段中，::numeric 将括号内计算的总和结果转换为 numeric 类型。

在 PostgreSQL 中，numeric 类型可以指定两个数字：第一个数字表示数值中允许的总位数，第二个数字表示小数点后的位数。如果没有指定第二个数字，那么默认为没有小数。

(... )::numeric, 2  --将括号中的数值转换成numeric类型并保留两位小数。

二、 `COALESCE()`

COALESCE() 是 SQL 中的一个函数，它的作用是从其参数列表中返回第一个非空（NOT NULL）值。如果所有的参数都是空的（NULL），那么 COALESCE() 函数将返回 NULL。

语法：

COALESCE(expression [, ...])

这里的 expression 可以是一个或多个，它们是 COALESCE() 函数的参数，可以是列名或者具体的值。

示例：

假设你有一个叫做 employees 的表，其中包含 name 和 nickname 两列：

CREATE TABLE employees ( id INT, name VARCHAR(100), nickname VARCHAR(50) );

并且你想要查询员工的名字，但如果 name 是 NULL 的话，你希望返回 nickname：

SELECT id, COALESCE(name, nickname) AS full_name FROM employees;

在这个例子中，如果 name 不是 NULL，COALESCE() 将返回 name 的值；如果 name 是 NULL，它将返回 nickname 的值。如果 name 和 nickname 都是 NULL，那么 full_name 列的结果将是 NULL。

COALESCE() 可以接受多个参数，如果有多个可能的替代值，它将返回第一个非 NULL 的值。

例如：

SELECT COALESCE(null_value1, null_value2, null_value3, default_value) AS result;

在这个例子中，如果 null_value1 不是 NULL，result 将被设置为 null_value1 的值。如果 null_value1 是 NULL，但 null_value2 不是，那么 result 将被设置为 null_value2 的值，以此类推。

三、`ILIKE`

在 PostgreSQL 中，并没有 ilke 这个关键字或函数。可能您想要询问的是 ILIKE，这是一个 PostgreSQL 中的 SQL 函数，用于执行不区分大小写的 LIKE 匹配。

ILIKE 的作用：

ILIKE 操作符与 LIKE 操作符类似，但它在进行字符串比较时不区分大小写。这在需要忽略大小写差异进行搜索时非常有用。

使用示例：

不区分大小写地匹配包含特定文本的字符串：
```
SELECT * FROM table_name WHERE column_name ILIKE '%Text%';
```
这将选取 column_name 中包含 "Text" 的所有记录，无论 "Text" 是大写、小写还是混合大小写。
不区分大小写地匹配以特定文本开头的字符串：
```
SELECT * FROM table_name WHERE column_name ILIKE 'Text%';
```
这将选取以 "Text" 开头的所有记录，同样不区分大小写。
不区分大小写地匹配以特定文本结尾的字符串：
```
SELECT * FROM table_name WHERE column_name ILIKE '%Text';
```
这将选取以 "Text" 结尾的所有记录，不区分大小写。

四、从JSON对象中提取特定的数据

查询JSON格式的列或者JSON类型的字段中用于从JSON对象中提取特定的数据，例如：PostgreSQL数据库中的JSONB类型的列

示例

假设我们有一个表sales，其中有一个JSONB类型的列realtime，这条语句可能会被用在以下SQL查询中：

SELECT realtime ->> 'lastSaleTime' FROM sales;

->>：这是一个操作符，用于从JSON对象中提取文本（字符串）类型的值。左边是JSON对象，右边是要提取的键名。
realtime：这通常是一个列名，它包含了JSON格式的数据。
'lastSaleTime'：这是JSON对象中的键名，我们想要从JSON对象中提取与这个键名对应的值。

这条查询将返回sales表中每一行的realtime列里'lastSaleTime'键对应的值。

注意

这种语法可能因不同的数据库系统而异。在某些数据库中，可能需要使用不同的函数或操作符来实现相同的功能。
JSONB是PostgreSQL特有的数据类型，其他数据库系统可能使用不同的数据类型来存储JSON数据。
如果realtime列不是JSONB类型，那么这条语句可能不适用，需要根据实际使用的数据库系统进行调整。

五、ABS ( )

在 SQL 语句中，表达式 ABS(total_amount - paid_amount - disc_amount) >= 0.01 用于计算 total_amount、paid_amount 和 disc_amount 之间的数学关系，并且确保计算结果的绝对值大于或等于 0.01。这里的关键点是 ABS 函数，它返回一个数值的绝对值，即不考虑正负号的数值大小。

表达式分解：

total_amount：可能是一个表中的字段，代表总金额或总数量。
paid_amount：代表已支付金额或数量的字段。
disc_amount：代表折扣金额或数量的字段。

表达式含义：

total_amount - paid_amount - disc_amount：这个计算得到的是总金额减去已支付金额和折扣金额后的差额，可能是未支付金额或未结算数量。
ABS(...)：ABS 函数取上述差额的绝对值，确保无论差额是正数还是负数，都被视为正值。
>= 0.01：这个条件检查绝对值是否大于或等于 0.01。如果大于或等于 0.01，它意味着有至少 0.01 的金额或数量未支付或未结算。

使用场景：

这个表达式通常用于筛选出那些有足够剩余金额或数量值得关注的记录。例如，在财务或销售上下文中，您可能想要找出所有未支付金额或未结算数量至少为 0.01 的记录。

示例：

假设您有一个销售订单表 sales_orders，其中包含了每个订单的总金额、已支付金额和折扣金额。使用这个表达式，您可以找出所有未完全支付的订单：

SELECT * FROM sales_orders WHERE ABS(total_amount - paid_amount - disc_amount) >= 0.01;

这条 SQL 语句将返回所有那些未支付金额或未结算数量至少为 0.01 的订单记录。这可以帮助您追踪和分析未完全支付的订单，以便进行后续的财务处理或催款活动。

六、`TO_CHAR` ( )

TO_CHAR 函数来格式化日期,将日期转化成对应的char类型的格式。

例：根据列 date_column 的日期生成的新字符串 XYYYYMMDD。

TO_CHAR(date_column AT TIME ZONE 'UTC', 'YYYYMMDD')

上面的语句是将 date_column 格式化为 YYYYMMDD 格式的字符串。这里使用 AT TIME ZONE 'UTC' 来确保时间转换为协调世界时（UTC），如果你不需要时区转换，可以省略这部分。

七、SUBSTRING ( )

SUBSTRING 函数来定位和修改特定的字符串。

例：从 your_column 的第 9 个字符开始截取剩余的字符串。

SUBSTRING(your_column FROM 9)

上述语句是从 your_column 的第 9 个字符开始截取剩余的字符串。

八、CONCAT( )

concat函数用于连接新的日期字符串和剩余的原始字符串。

例：连接a和b

CONCAT('a', 'b')

上面语句运行的结果是 'ab'。

九、查询jsonb为null的sql

可以使用 jsonb 类型的特定函数来检查 jsonb 字段是否非空。

// 1. 使用 ->> 操作符将 jsonb 字段转换为文本，然后检查它是否不是 NULL。
SELECT * FROM info_item_prop 
WHERE company_id = 3205 AND avail_attr_combine->>'' IS NOT NULL;

// 2. jsonb_typeof 函数可以检查 jsonb 字段的类型。如果字段不是空的，它的类型就不会是 'null'。
SELECT * FROM info_item_prop 
WHERE company_id = 3205 AND jsonb_typeof(avail_attr_combine) <> 'null';

// 3. jsonb_ne 操作符可以用来比较两个 jsonb 值是否不相等。你可以将其与 null 进行比较。
SELECT * FROM info_item_prop 
WHERE company_id = 3205 AND avail_attr_combine <> 'null'::jsonb;

// 4. COALESCE 函数可以用来返回第一个非 NULL 值。如果 avail_attr_combine 是空的，你可以使用 COALESCE 来提供一个非空的默认值，然后检查这个默认值。
SELECT * FROM info_item_prop 
WHERE company_id = 3205 AND COALESCE(avail_attr_combine, 'non_empty_default'::jsonb) <> 'non_empty_default'::jsonb;

十、解析jsonb格式数据

可以使用操作符 -> 来提取 jsonb 类型字段的值。@> 操作符，它是一个 JSONB 包含操作符，用于检查左边的 JSONB 值是否包含右边的 JSONB 对象。

SELECT  //其中->0是拿到jsonb字段的第一个值。以为再我查的表中jsonb字段最外层是array
    mum_attributes ->0-> 'distinctStock' AS distinctStock 
        FROM 
            info_item_prop 
        WHERE 
            mum_attributes @> '{"attrID": "1308"}';