接上一篇，本节继续介绍GaussDB数据库常用基础函数
目录
5、范围函数
6、窗口函数
7、聚集函数
8、安全函数
9、系统信息函数
10、动态脱敏函数
GaussDB常用基础函数介绍与示例
5、范围函数
在GaussDB数据库中，范围函数是指用于处理数据库范围的函数。以下是一些常见的数据库范围函数：
|
–numrange(numeric, numeric, [text]) –描述：表示一个范围。 –返回类型：范围元素类型 –lower(anyrange) –描述：范围的下界。 –返回类型：范围元素类型 –upper(anyrange) –描述：范围的上界 –返回类型：范围元素类型 –isempty(anyrange) –描述：范围是否为空 –返回类型：Boolean SELECT numrange(1,2.5) AS RESULT1 ,numrange(1,2.5, ‘()’) AS RESULT2 ,lower(numrange(1,2.5)) AS RESULT3 ,upper(numrange(1,2.5)) AS RESULT4 ,isempty(numrange(1,2.5)) AS RESULT5 ; |
6、窗口函数
在GaussDB数据库中，窗口函数是指用于处理窗口（例如表的行级别或列级别）的函数。这些函数通常用于查询、更新和排序数据，以确保数据的正确性和完整性。
列存表目前只支持rank(expression)和row_number(expression)两个函数。
窗口函数与OVER语句一起使用。OVER语句用于对数据进行分组，并对组内元素进行排序。窗口函数用于给组内的值生成序号。
窗口函数中的order by后面必须跟字段名，若order by后面跟数字，该数字会被按照常量处理，因此对目标列没有起到排序的作用。
以下是一些常见的数据库窗口函数：
|
–RANK() –描述：RANK函数为各组内值生成跳跃排序序号，其中，相同的值具有相同序号。 –返回值类型：BIGINT –ROW_NUMBER() –描述：ROW_NUMBER函数为各组内值生成连续排序序号，其中，相同的值其序号也不相同。 –返回值类型：BIGINT |
7、聚集函数
在GaussDB数据库中，数据库聚集函数是指用于对数据库中的数据进行聚合计算的函数。这些函数通常用于处理大量数据，例如表中的行数据。
以下是一些常见的数据库聚集函数：
|
–sum(expression) –描述：所有输入行的expression总和。 –返回类型： 通常情况下输入数据类型和输出数据类型是相同的，但以下情况会发生类型转换： –对于SMALLINT或INT输入，输出类型为BIGINT。 –对于BIGINT输入，输出类型为NUMBER 。 –对于浮点数输入，输出类型为DOUBLE PRECISION。 –max(expression)、min(expression) –描述：所有输入行中expression的最大值、最小值。 –参数类型：任意数组、数值、字符串、日期/时间类型。 –返回类型：与参数数据类型相同 –avg(expression) –描述：所有输入值的均值（算术平均）。 –返回类型：对于任何整数类型输入，结果都是NUMBER类型。对于任何浮点输入，结果都是DOUBLE PRECISION类型。否则和输入数据类型相同。 –count(expression) 描述：返回表中满足expression不为NULL的行数。 count(*) 描述：返回表中的记录行数。 –返回类型：BIGINT select sum(column1),max(column1),min(column1),avg(column1),count(column1),count(*) from test1 |
|
–array_agg(expression) –描述：将所有输入值（包括空）连接成一个数组。 –返回类型：参数类型的数组 –string_agg(expression, delimiter) –描述：将输入值连接成为一个字符串，用分隔符分开。 –返回类型：和参数数据类型相同。 select array_agg(column1),string_agg(column1,’|’) from test1 |
|
–listagg(expression [, delimiter]) WITHIN GROUP(ORDER BY order-list) –描述：将聚集列数据按WITHIN GROUP指定的排序方式排列，并用delimiter指定的分隔符拼接成一个字符串。 –expression：必选。指定聚集列名或基于列的有效表达式，不支持DISTINCT关键字和VARIADIC参数。 –delimiter：可选。指定分隔符，可以是字符串常数或基于分组列的确定性表达式，缺省时表示分隔符为空。 –order-list：必选。指定分组内的排序方式。 –返回类型：text select column1,listagg(column2,’|’) within group(order by column1) from test1 group by column1; |
8、安全函数
在GaussDB数据库中，安全函数是指用于提高数据库安全性的函数。这些函数通常用于检查和处理数据库操作时可能出现的数据安全情况，例如加密等。
以下是一些常见的数据库安全函数：
--gs_encrypt_aes128(encryptstr,keystr)
--描述：使用基于keystr派生出的密钥对encryptstr字符串进行加密，返回加密后的字符串。keystr的长度范围为8~16字节，并且至少包含大写字母、小写字母、数字和特殊字符中的三种字符。
--支持的加密数据类型：目前数据库支持的数值类型，字符类型，二进制类型中的RAW，日期/时间类型中的DATE、TIMESTAMP、SMALLDATETIME。
--返回值类型：text
--返回值长度：至少为92字节，不超过4*[(Len+68)/3]字节，其中Len为加密前数据长度（单位为字节）。
--说明：由于该函数的执行过程需要传入口令，为了安全起见，gsql工具不会将包含该函数名字样的SQL记录入执行历史。即无法在gsql里通过上下翻页功能找到该函数的执行历史。
--gs_decrypt_aes128(decryptstr,keystr)
--描述：使用基于keystr派生出的密钥对decrypt字符串进行解密，返回解密后的字符串。解密使用的keystr必须保证与加密时使用的keystr一致才能正确解密。keystr不得为空。
--说明：此函数需要结合gs_encrypt_aes128加密函数共同使用。
--返回值类型：text
--加密
SELECT gs_encrypt_aes128('123','P@ssw0rd');
--解密
select gs_decrypt_aes128('XG09Q6u9VOBUf+eWC6/COTR3PX+AYm+G6vcMbdqjG8muGsGYplPdXP2vPVFRyU+vGLUHtbNWaxnpAO+6pGKSq9bLsIU=','P@ssw0rd')
--gs_encrypt(encryptstr,keystr, encrypttype)
--描述：根据encrypttype，以keystr为密钥对encryptstr字符串进行加密，返回加密后的字符串。keystr的长度范围为8~16字节，至少包含3种字符（大写字母、小写字母、数字、特殊字符）, encrypttype可以是aes128或sm4。
--返回值类型：text
--说明：由于该函数的执行过程需要传入解密口令，为了安全起见，gsql工具不会将包含该函数名字样的SQL记录入执行历史。即无法在gsql里通过上下翻页功能找到该函数的执行历史。
--gs_decrypt(decryptstr,keystr，decrypttype)
--描述：根据decrypttype，以keystr为密钥对decrypt字符串进行解密，返回解密后的字符串。
--解密使用的decrypttype 及keystr必须保证与加密时使用的encrypttype 及keystr一致才能正常解密。keystr不得为空。decrypttype可以是aes128或sm4。
--此函数需要结合gs_encrypt加密函数共同使用。
--返回值类型：text
--说明：由于该函数的执行过程需要传入解密口令，为了安全起见，gsql工具不会将包含该函数名字样的SQL记录入执行历史；即无法在gsql里通过上下翻页功能找到该函数的执行历史。
SELECT gs_encrypt('123','P@ssw0rd','sm4'),gs_encrypt('123','P@ssw0rd','aes128');
select gs_decrypt('h4NdduY5s7++5Qp7m1pgdgKORQ==','P@ssw0rd','sm4')
,gs_decrypt('roBb4Wg/GjiW9g6gfpth7+ZliaCoYI+/nQd5FruETUpnOcj2veZ3gulEyxLnGh02tUj1/mdL4NJPaeNi3WAskdTs3wU=','P@ssw0rd','aes128')
;
9、系统信息函数
在GaussDB数据库中，主要是包含数据库相关的信息，例如数据库系统版本信息、状态信息、用户信息等，这些系统信息函数通常用于数据库管理系统中，用于管理和监控数据库的运行状态和性能指标。
以下是一些常见的数据库系统信息函数：
|
–current_catalog –描述：当前数据库的名称（在标准SQL中称”catalog”）。 –返回值类型：name –current_database() –描述：当前数据库的名称。 –返回值类型：name –current_schema() –描述：当前模式的名称。 –返回值类型：name –备注：current_schema返回在搜索路径中第一个顺位有效的模式名。（如果搜索路径为空则返回NULL，没有有效的模式名也返回NULL）。如果创建表或者其他命名对象时没有声明目标模式，则将使用这些对象的模式。 SELECT current_catalog,current_database(),current_schema(); |
|
–current_user –描述：当前执行环境下的用户名。 –返回值类型：name –备注：current_user是用于权限检查的用户标识。通常，他表示会话用户，但是可以通过SET ROLE改变他。在函数执行的过程中随着属性SECURITY DEFINER的改变，其值也会改变。 –definer_current_user –描述：当前执行环境下的用户名。 –返回值类型：name –备注：大多数情况下definer_current_user和current_user结果相同，但在存储过程中执行该函数会返回定义当前存储过程的用户名。 select current_user,definer_current_user(); |
|
–inet_client_addr() –描述：连接的远端地址。inet_client_addr返回当前客户端的IP地址。 –说明：此函数只有在远程连接模式下有效。 –返回值类型：inet –inet_client_port() –描述：连接的远端端口。inet_client_port返回当前客户端的端口号。 –说明：此函数只有在远程连接模式下有效。 –返回值类型：int –inet_server_addr() –描述：连接的本地地址。inet_server_addr返回服务器接收当前连接用的IP地址。 –说明：此函数只有在远程连接模式下有效。 –返回值类型：inet –inet_server_port() –描述：连接的本地端口。inet_server_port返回接收当前连接的端口号。如果是通过Unix-domain socket连接的，则所有这些函数都返回NULL。 –说明：此函数只有在远程连接模式下有效。 –返回值类型：int SELECT inet_client_addr(),inet_server_port(),inet_server_addr(),inet_server_port(); |
|
–pg_postmaster_start_time() –描述：服务器启动时间。pg_postmaster_start_time返回服务器启动时的timestamp with time zone。 –返回值类型：timestamp with time zone select pg_postmaster_start_time() ; |
|
–opengauss_version() –描述：引用的openGauss内核版本信息。 –返回值类型：text –version() –描述：版本信息。version返回一个描述服务器版本信息的字符串。 –返回值类型：text –getdatabaseencoding() –描述：获取数据库编码方式。 –返回值类型：name select opengauss_version(),version(),getdatabaseencoding(); |
|
–session_user –描述：会话用户名。 –返回值类型：name –备注：session_user通常是连接当前数据库的初始用户，不过系统管理员可以用SET SESSION AUTHORIZATION修改这个设置。 –user –描述：等价于current_user。 –返回值类型：name –getpgusername() –描述：获取数据库用户名。 –返回值类型：name SELECT session_user,user,current_user; |
|
–pg_get_tabledef(table_name) –描述：根据table_name获取表定义。 –返回类型：text –备注：pg_get_tabledef重构出表定义的CREATE语句，包含了表定义本身、索引信息、comments信息。 –对于表对象依赖的group、schema、tablespace、server等信息，需要用户自己去创建，表定义里不会有这些对象的创建语句。 –pg_get_viewdef(view_name) –描述：为视图获取底层的SELECT命令 select * from pg_get_tabledef(‘test1’),pg_get_viewdef(‘test1_view’); |
|
–has_any_column_privilege(user, table, privilege) –描述：指定用户是否有访问表任何列的权限。 –返回类型：Boolean –has_any_column_privilege(table, privilege) –描述：当前用户是否有访问表任何列的权限，合法参数类型见表2。 –返回类型：Boolean –has_column_privilege(user, table, column, privilege) –描述：指定用户是否有访问列的权限。 –返回类型：Boolean –has_column_privilege(table, column, privilege) –描述：当前用户是否有访问列的权限，合法参数类型见表3。 –返回类型：Boolean –以上的table、column 可以替换成database、function、schema、sequence、tablespace等对象 –参数privilege：SELECT：允许对表的指定列执行SELECT语句。 INSERT：允许对表的指定列执行INSERT语句。UPDATE：允许对表的指定列执行UPDATE语句。 –REFERENCES：允许创建一个外键约束。COMMENT：允许对表的指定列执行COMMENT语句。 select has_any_column_privilege(‘root’,’test1′,’select’),has_any_column_privilege(‘test1′,’select’) |
10、动态脱敏函数
在GaussDB数据库中，动态脱敏函数是指根据用户输入的查询条件动态生成敏感信息，并进行脱敏处理的函数。这些函数通常用于对用户输入的敏感数据进行脱敏处理，以保护数据的安全性和隐私性。
以下是一些常见的数据库动态脱敏函数：
|
–creditcardmasking(col text, letter char default ‘x’) –描述：将col字符串后四位之前的数字使用letter替换。 –参数：待替换的字符串、替换字符。 –返回值类型：text –alldigitsmasking(col text, letter char default ‘0’) –描述：将col字符串中出现的数字使用letter替换。 –参数：待替换的字符串、替换字符。 –返回值类型：text –randommasking(col text) –描述：将col字符串中的字符随机化。 –参数：待替换的字符串、替换字符。 –返回值类型：text –shufflemasking(col text) –描述：将col字符串中的字符乱序排列。 –参数：待替换的字符串、替换字符。 –返回值类型：text select creditcardmasking(‘15021750702’, ‘*’),alldigitsmasking(‘123abc’,’0′),randommasking(‘123abc’),shufflemasking(‘123abc’); |
本次介绍到此结束， 更多函数可参考官网资料文档~ 欢迎大家交流、学习！
https://support.huaweicloud.com/opengauss/index.html
