达梦数据库常用函数

达梦数据库

-- 查询库中所有表
SELECT * FROM ALL_TABLES;

-- 查询库中所有视图
SELECT * FROM ALL_VIEWS;

-- 查询库中所有表的字段信息
SELECT * FROM ALL_TAB_COLUMNS;

-- 查询库中所有索引信息
SELECT * FROM ALL_INDEXES;
 
-- 查询库中索引的列定义信息
SELECT * FROM ALL_IND_COLUMNS;

-- 截取字符串中的数字
select CAST(REGEXP_REPLACE('测试5692列车', '[^0-9]', '') AS INT) AS extracted_number

-- REGEXP_REPLACE 是达梦数据库中用于替换字符串中匹配正则表达式的部分的函数
-- CAST 函数用于将一个表达式转换为指定的数据类型

case …when …排序

xxx order by case when xx is null OR xx == '' then 1 else 0 END, TCRQ ASC

ORDER BY 
 CASE 
  WHEN NAME LIKE '%低%' THEN 1 
  WHEN NAME LIKE '%中%' THEN 2 
  WHEN NAME LIKE '%高%' THEN 3 
 ELSE 4 END

sysdate 系统日期

-- sbustr 左闭右开
select substr(sysdate, 0, 10) -- 获取年月日

to_char 格式化日期/数值字符串

to_char(日期字段, 日期格式) 
例子：
select to_char('2023-06-06 14:38:43.000000', 'yyyy') year , to_char('2023-06-06 14:38:43.000000', 'MM') month
结果：
year|--|month
2023|--|06

-- 格式化数值，保留三个小数
select to_char('123.00000', 'FM9990.000')

• FM：表示去除值中的前导空格，并且不显示小数点后多余的0。
• 9990.000：这是数字格式的模板，具体含义如下：
  • 9 表示一个数字占位，如果没有对应数字则显示空格。
  • 0 表示一个数字占位，如果没有对应数字则显示0。
  • . 表示小数点。

生成uuid

SYS_GUID() 生成uuid
UPPER()    转化为大写
例如：
SELECT UPPER(SYS_GUID()) AS uuid

导入外部SQL文件

语法：` + sql文件路径;
-- 例如：
`C:\Users\WEI\Desktop\备份数据\test.sql;

LOCATE

LOCATE函数用于查找一个字符串在另一个字符串中第一次出现的位置，并返回其位置。如果找到了匹配的子字符串，就会返回它在父字符串中的位置；如果没有找到，则返回0。

SELECT LOCATE('子字符串', '父字符串');

-- 例如：判断字符串'Hello, World!' 是否包含子字符串'World'
SELECT LOCATE('World', 'Hello, World!');

字符串替换函数

regexp_replace 是一个用于在字符串中替换匹配正则表达式模式的函数。通过指定要替换的模式、要替换成的字符串以及要操作的输入字符串，可以使用 regexp_replace 函数来实现替换功能。

regexp_replace(DYDJ_START, '[^0-9]', '')

这个正则表达式函数的作用是将字符串 BUS_NAME_END 中的非数字字符替换为空，只保留数字部分。这样可以用来提取字符串中的数字部分或者去除非数字字符。

类型转化函数

CAST是在数据库中用于将一个数据类型转换为另一个数据类型的函数。它可以用于在查询语句中将一个列或表达式的数据类型转换为所需的类型

SELECT CAST('123' AS number)

想要根据字符串中的数字进行排序，需要进行类型转化；

-- 根据字符串中的数字进行排序
order by CAST(regexp_replace(字段, '[^0-9]', '') AS number)

substr截取字符串

SUBSTR(string, start , length)
# string：要截取的字符串。
# start：指定开始截取的位置，从1开始计数。
# length：可选参数，指定要截取的长度。如果省略，则从开始位置截取到字符串的末尾。

select SUBSTR('123', 1, 2) #结果：12

instr查找指定字符的索引

INSTR(string, substring) 
或
INSTR(string, substring, start_position, occurrence)
-- 参数说明：
-- string：要查找的主字符串。
-- substring：要查找的子字符串。
-- start_position：开始查找的位置，默认为1。
-- occurrence：要查找的第几个出现的子字符串。默认为1。

-- 返回值：
-- 当找到子字符串时，返回子字符串在主字符串中的位置（从1开始计数）。
-- 没有找到子字符串时，返回0。

-- 例：
SELECT INSTR('hello world', 'world'); -- 返回 7
SELECT INSTR('hello world', 'l', 4, 2); -- 返回 10

DECODE

可以使用 DECODE 函数来实现类似的条件判断功能

DECODE(expr, search1, result1 [, search2, result2, ...] [, default])

• expr 是要比较的表达式。
• search1, search2, … 是要与 expr 进行比较的值。
• result1, result2, … 是与 search1, search2, … 相对应的结果。
• default 是可选的默认值，如果没有找到匹配的值，则返回 default。

-- 例1：根据条件匹配结果
SELECT DECODE('001',
              '001', 'Finance',
              '002', 'Marketing',
              '003', 'Sales',
              'Unknown')
              
 -- 例2：如果值为null，则返回''，否则不变
 select decode(target, 
               null,'',
               target)
 

ROUND 格式化数据（四舍五入）

ROUND(number, [decimal_places])

• number 是要四舍五入的数值；
• decimal_places 是可选参数，用于指定要保留的小数位数，默认为 0。

select ROUND(123.11) -- 结果：123
select ROUND(123.11, 1) -- 结果：123.1

REGEXP_SUBSTR 根据正则表达式截取

-- 截取#号及其后面的数字
select REGEXP_SUBSTR('adcd#3dd#2342' , '#[0-9]+') 

null排序

nulls last null在最后

nulls first null 排在最前

select * from xxx order by xx,nulls last

★ REGEXP_LIKE

REGEXP_LIKE 是一个用于在 SQL 中进行正则表达式匹配的函数，匹配到返回1，匹配不到返回0

用法：

REGEXP_LIKE (source_string, pattern [, match_parameter])

• source_string 是要进行匹配的源字符串。
• pattern 是正则表达式模式，用于指定要匹配的规则。
• match_parameter 是可选参数，用于指定匹配选项。

--  查找所有包含 "apple" 的字符串：
SELECT * FROM table_name WHERE REGEXP_LIKE(column_name, 'apple');
-- 查找所有以数字开头并且包含至少一个字母的字符串
SELECT * FROM table_name WHERE REGEXP_LIKE(column_name, '^\d+[a-zA-Z]+');

★ REGEXP_REPLACE

regexp_replace是一种在SQL中常见的函数，用于执行基于正则表达式的字符串搜索和替换操作

REGEXP_REPLACE(source_string, pattern, replacement)

• source_string：要进行替换操作的源字符串。
• pattern：用于匹配的正则表达式模式。
• replacement：替换匹配到的模式的新字符串。

-- 将其中的所有小写字母 'o' 替换为数字 '0'
SELECT REGEXP_REPLACE('Hello, world!', 'o', '0'); 

★ LISTAGG 多行多列合并数据

LISTAGG 是一种聚合函数，用于在查询结果中将多行数据按照指定的分隔符合并成一行，并返回合并后的结果。它通常用于将某一列的多个值连接成一个字符串。

语法

LISTAGG(column, delimiter) WITHIN GROUP (ORDER BY column)

• column: 要合并的列名。
• delimiter: 分隔符，用来分隔合并后的值。
• WITHIN GROUP (ORDER BY column): 指定排序规则，可以按照指定的列对要合并的值进行排序。

-- 使用 LISTAGG 合并员工姓名
SELECT LISTAGG(employee_name, ', ') WITHIN GROUP (ORDER BY employee_id) AS all_employee_names  FROM employees;

WM_CONCAT 多个行值合并

WM_CONCAT 是 一个聚合函数，用于将多个行的值连接成一个字符串 （多行记录使用逗号分隔)

-- 假设你有一张表 EMPLOYEES，其中有两列 DEPARTMENT_ID 和 EMPLOYEE_NAME，你想要获取每个部门的员工姓名列表，可以使用 WM_CONCAT 函数，示例如下：

SELECT 
    DEPARTMENT_ID, 
    WM_CONCAT(EMPLOYEE_NAME) AS EMPLOYEES
FROM 
    EMPLOYEES
GROUP BY 
    DEPARTMENT_ID;

★ WITH AS

SQL 查询中使用 WITH 子句时，你可以创建一个临时的命名结果集（也称为公共表表达式或 CTE），以便后续查询可以引用它。这在复杂的查询中特别有用，因为它可以提高查询的可读性和可维护性。

语法：

WITH 
    cte1 AS (SELECT ...),
    cte2 AS (SELECT ...)
SELECT * FROM cte1 JOIN cte2 ON ...

-- 其中，cte1、cte2 是你定义的临时表表达式的名称，而 SELECT ... 部分则是定义临时表的查询语句
-- 在一个查询中多次引用相同的子查询，你可以使用 WITH 子句将该子查询定义为一个临时表，然后在主查询中引用它。这样一来，不仅可以简化查询语句，还可以避免多次执行相同的子查询，提高了查询的性能。

COALESCE 如果为空则取其他值

COALESCE 是一种 SQL 函数，用于从参数列表中返回第一个非空（非 NULL）的表达式的值。它接受一个或多个参数，并按顺序返回第一个非空值。如果所有参数均为 NULL，则 COALESCE 返回 NULL。

语法：

COALESCE(expression1, expression2, ...)

其中 expression1, expression2, … 可以是字段、常量或者任何返回值的表达式。

SELECT COALESCE(null_column, 'default_value') AS result FROM your_table;
-- 检查 null_column 字段是否为 NULL，如果是则返回 'default_value'，否则返回 null_column 的实际值。

SELECT COALESCE(null_column1, null_column2, null_column3, 'default_value') AS result FROM your_table;
-- 如果 null_column1、null_column2、null_column3 中任何一个字段不为 NULL，则返回该字段的值；如果所有字段均为 NULL，则返回 'default_value'。

NVL 函数用于处理空值

语法

sqlCopy CodeNVL(expression1, expression2)

• expression1：需要检查的表达式。
• expression2：当 expression1 为 NULL 时返回的值

SELECT NVL(column_name, 'default_value') AS result
FROM table_name;
# 如果 column_name 的值为 NULL，将返回 'default_value'

注意：使用 NVL 函数时要注意数据类型的一致性。如果 expression1 和 expression2 的数据类型不同，可能会导致类型转换错误