暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / GBase 8c 学习笔记 007 —— 数据类型&常用函数

GBase 8c 学习笔记 007 —— 数据类型&常用函数

心有阳光 2023-03-27
1183

GBase 8c 学习笔记 007 —— 数据类型&常用函数

数据类型

  • 什么是数据
    • 数据是事实或观察的结果，是对客观事物的逻辑归纳，用于表示客观事物的未加工的原始素材。
  • 数据库中的数据类型
    • 在 GBase 8c 中，数据类型是数据的一个基本属性，用于区分不同类型的数据。不同的数据类型所占用的存储空间不同，能够进行的操作也不相同。
    • 数据库中的数据存储在数据表中，数据表中的每一列都定义了其数据类型。当用户存储数据时，需要遵循这些数据类型的属性定义，否则可能会出现报错或精度丢失等问题。在GBase 8c中，主要的数据类型有：
      • 常用的数据类型
        • 包括：数值类型、字符类型、日期类型等。
      • 非常用的数据类型
        • 包括：布尔类型、二进制类型、XML类型、几何类型等。
      • 自定义数据类型

数值类型——整数类型

整数类型 描述 存储空间 范围
TINYINT 微整数，别名为INT1。 1字节 0 ~ 255
SMALLINT 小范围整数，别名为INT2。 2字节 -32,768 ~ +32,767
INTEGER 常用的整数，别名为INT4。 4字节 -2,147,483,648 ~ +2,147,483,647
BINARY_INTEGER INTEGER的别名。 4字节 -2,147,483,648 ~ +2,147,483,647
BIGINT 大范围的整数，别名为INT8。 8字节 -9,223,372,036,854,775,808 ~
+9,223,372,036,854,775,807
int16 十六字节的大范围整数，目前不支持用于建表等使用。 16字节 -170,141,183,460,469,231,731,687,303,715,884,105,728 ~+170,141,183,460,469,231,731,687,303,715,884,105,727 
--- 创建具有TINYINT类型数据的表。
postgres=# CREATE TABLE int_type_t1 (IT_COL1 TINYINT);
CREATE TABLE
postgres=#

--- 向创建的表中插入数据。
postgres=# INSERT INTO int_type_t1 VALUES(10);
INSERT 0 1
postgres=#

--- 查看数据。
postgres=# SELECT * FROM int_type_t1;
 it_col1
---------
 10
(1 row)

postgres=#

--- 删除表。
postgres=# DROP TABLE int_type_t1;
DROP TABLE
postgres=#

--- 创建具有TINYINT,SMALLINT,INTEGER,BIGINT类型数据的表。
postgres=# CREATE TABLE int_type_t2 (a TINYINT, b SMALLINT, c INTEGER, d BIGINT);
CREATE TABLE
postgres=#

--- 插入数据。
postgres=# INSERT INTO int_type_t2 VALUES(10, 100, 1000, 10000);
INSERT 0 1
postgres=#

--- 查看数据。
postgres=# SELECT * FROM int_type_t2;
 a  |  b  |  c   |   d
----+-----+------+-------
 10 | 100 | 1000 | 10000
(1 row)

postgres=#

--- 删除表。
postgres=# DROP TABLE int_type_t2;
DROP TABLE
postgres=#

gbase8c0084.png

数值类型——任意精度类型

任意精度类型 描述 存储空间 范围
NUMERIC[ (p[,s])]
DECIMAL[( p[,s])]		 精度p取值范围 [1,1000]，标度s取值范围 [0,p]。
说明：p为总位数，s为小数位数。		 用户声明精度。每四位（十进制位）占用两个字节，然后在整个数据上加上八个字节的额外开销。 未指定精度的情况下，小数点前最大131,072位，小数点后最大16,383位。
NUMBER[( p[,s])] NUMERIC 类型的别名。 用户声明精度。每四位（十进制位）占用两个字节，然后在整个数据上加上八个字节的额外开销。 未指定精度的情况下，小数点前最大131,072位，小数点后最大16,383位。 
--- 创建表。
postgres=# CREATE TABLE decimal_type_t1 (DT_COL1 DECIMAL(10,4));
CREATE TABLE
postgres=#

--- 插入数据。
postgres=# INSERT INTO decimal_type_t1 VALUES(123456.122331);
INSERT 0 1
postgres=#

--- 查询表中的数据。
postgres=# SELECT * FROM decimal_type_t1;
   dt_col1
-------------
 123456.1223
(1 row)

postgres=#

--- 删除表。
postgres=# DROP TABLE decimal_type_t1;
DROP TABLE
postgres=#

--- 创建表。
postgres=# CREATE TABLE numeric_type_t1 (NT_COL1 NUMERIC(10,4));
CREATE TABLE
postgres=#

--- 插入数据。
postgres=# INSERT INTO numeric_type_t1 VALUES(123456.12354);
INSERT 0 1
postgres=#

--- 查询表中的数据。
postgres=# SELECT * FROM numeric_type_t1;
   nt_col1
-------------
 123456.1235
(1 row)

postgres=#

--- 删除表。
postgres=# DROP TABLE numeric_type_t1;
DROP TABLE
postgres=#

gbase8c0085.png

数值类型——序列整数类型

序列整数类型 描述 存储空间 范围
SMALLSERIAL 二字节序列整型。 2字节 -32,768 ~ +32,767
SERIAL 四字节序列整型。 4字节 -2,147,483,648 ~ +2,147,483,647
BIGSERIAL 八字节序列整型。 8字节 -9,223,372,036,854,775,808 ~+9,223,372,036,854,775,807
LARGESERIAL 默认插入十六字节序列整形，实际类型和numeric相同。 变长类型，每四位（十进制位）占用两个字节，然后在整个数据上加上八个字节的额外开销。 小数点前最大131,072位，小数点后最大16,383位。 
--- 创建表。
postgres=# CREATE TABLE serial_type_tab(b SERIAL);
NOTICE:  CREATE TABLE will create implicit sequence "serial_type_tab_b_seq" for serial column "serial_type_tab.b"
CREATE TABLE
postgres=#

--插入数据。
postgres=# INSERT INTO serial_type_tab VALUES(default);
INSERT 0 1
postgres=#
postgres=# INSERT INTO serial_type_tab VALUES(default);
INSERT 0 1
postgres=#

--- 查看数据。
postgres=# SELECT * FROM serial_type_tab;
 b
---
 1
 2
(2 rows)

postgres=#

gbase8c0086.png

数值类型——浮点类型

浮点类型 描述 存储空间 范围
REAL, FLOAT4 单精度浮点数 4字节 -3.402E+38~3.402E+38，6位十进制数字精度。
DOUBLE PRECISION, BINARY_DOUBLE, FLOAT8 双精度浮点数 8字节 -1.79E+308~1.79E+308，15位十进制数字精度。
FLOAT [§] 精度p取值范围为[1,53]。
说明：p为精度，表示总位数。		 4字节或8字节 根据精度p不同选择REAL或DOUBLEPRECISION作为内部表示。如不指定精度，内部用DOUBLE PRECISION表示。 
--- 创建表。
CREATE TABLE float_type_t2 (
    FT_COL1 INTEGER, 
    FT_COL2 FLOAT4, 
    FT_COL3 FLOAT8, 
    FT_COL4 FLOAT(3), 
    FT_COL5 BINARY_DOUBLE, 
    FT_COL6 DECIMAL(10,4), 
    FT_COL7 INTEGER(6,3)
);

postgres=# CREATE TABLE float_type_t2 (
postgres(#     FT_COL1 INTEGER,
postgres(#     FT_COL2 FLOAT4,
postgres(#     FT_COL3 FLOAT8,
postgres(#     FT_COL4 FLOAT(3),
postgres(#     FT_COL5 BINARY_DOUBLE,
postgres(#     FT_COL6 DECIMAL(10,4),
postgres(#     FT_COL7 INTEGER(6,3)
postgres(# );
CREATE TABLE

--- 插入数据。
postgres=# INSERT INTO float_type_t2 VALUES(10,10.365456,123456.1234,10.3214, 321.321, 123.123654, 123.123654);
INSERT 0 1
postgres=#

--- 查看数据。
postgres=# SELECT * FROM float_type_t2 ;
 ft_col1 | ft_col2 |   ft_col3   | ft_col4 | ft_col5 | ft_col6  | ft_col7
---------+---------+-------------+---------+---------+----------+---------
      10 | 10.3655 | 123456.1234 | 10.3214 | 321.321 | 123.1237 | 123.124
(1 row)

postgres=#

--- 删除表。
postgres=# DROP TABLE float_type_t2;
DROP TABLE
postgres=#

gbase8c0087.png

字符类型

字符类型 描述 存储空间
CHAR(n)
CHARACTER(n)
NCHAR(n)		 定长字符串，不足补空格。
n是指字节长度，如不带精度n，默认精度为1。		 最大为10MB。
VARCHAR(n)
CHARACTER
VARYING(n)		 变长字符串。不同的兼容模式下，n表示的含义不同，如：PG兼容模式下，n是字符长度。A兼容模式下，n指代字节长度。 最大为10MB。
VARCHAR2(n) 变长字符串。是VARCHAR(n)类型的别名。 最大为10MB。
NVARCHAR2(n) 变长字符串。n指代字符长度。 最大为10MB。
NVARCHAR(n) 变长字符串。是NVARCHAR2(n)类型的别名。 最大为10MB。
TEXT 变长字符串。 最大为1GB-1， 但还需要考虑到列描述头信息的大小，以及列所在元组的大小限制（也小于1GB-1），因此TEXT类型最大大小可能小于1GB-1。
CLOB 文本大对象。是TEXT类型的别名。 最大为1GB-1，但还需要考虑到列描述头信息的大小， 以及列所在元组的大小限制（也小于1GB-1），因此CLOB类型最大大小可能小于1GB-1。 
--- (1) 定长字符类型
--- 创建表
postgres=# CREATE TABLE char_type_t1 (CT_COL1 CHARACTER(4));
CREATE TABLE
postgres=#
 
--- 插入数据
postgres=# INSERT INTO char_type_t1 VALUES ('ok');
INSERT 0 1
postgres=#
 
--- 查询表中的数据
postgres=# SELECT ct_col1, char_length(ct_col1) FROM char_type_t1;
 ct_col1 | char_length
---------+-------------
 ok      |           4
(1 row)

postgres=#

--- 删除表
postgres=# DROP TABLE char_type_t1;
DROP TABLE
postgres=#


--- （2）变长字符类型
--- 创建表
postgres=# CREATE TABLE char_type_t2 (CT_COL1 VARCHAR(5));
CREATE TABLE
postgres=#

--- 插入数据
postgres=# INSERT INTO char_type_t2 VALUES ('ok');
INSERT 0 1
postgres=# INSERT INTO char_type_t2 VALUES ('good');
INSERT 0 1
postgres=#

--- 插入的数据长度超过类型规定的长度报错。
postgres=# INSERT INTO char_type_t2 VALUES ('too long');
ERROR:  value too long for type character varying(5)
CONTEXT:  referenced column: ct_col1
postgres=#
 
--- 明确类型的长度，超过数据类型长度后会自动截断。
postgres=# INSERT INTO char_type_t2 VALUES ('too long'::varchar(5));
INSERT 0 1
postgres=#
 
--- 查询数据
postgres=# SELECT ct_col1, char_length(ct_col1) FROM char_type_t2;
 ct_col1 | char_length
---------+-------------
 good    |           4
 ok      |           2
 too l   |           5
(3 rows)

postgres=#
 
--- 删除数据
postgres=# DROP TABLE char_type_t2;
DROP TABLE
postgres=#

gbase8c0088.png

日期/时间类型

数据类型 描述 存储空间
DATE 日期和时间。 4字节，A兼容模式时8字节
TIME [§] [WITHOUT TIME ZONE] 只用于一日内时间。
p 表示小数点后的精度，取值范围为0~6。		 8字节
TIME [§] [WITH TIME ZONE] 只用于一日内时间，带时区。
p 表示小数点后的精度，取值范围为0~6。		 12字节
TIMESTAMP[§] [WITHOUT TIME ZONE] 日期和时间。
p 表示小数点后的精度，取值范围为0~6。		 8字节
TIMESTAMP[§] [WITH TIME ZONE] 日期和时间，带时区。别名为TIMESTAMPTZ。
p 表示小数点后的精度，取值范围为0~6。		 8字节
SMALLDATETIME 日期和时间，不带时区。
精确到分钟，秒位大于等于30秒进一位。		 8字节
INTERVAL DAY (l) TO SECOND§ 时间间隔，X天X小时X分X秒。
l：天数的精度，取值范围为06。<br>p：秒数的精度，取值范围为06。		 16字节
INTERVAL [fields] [ § ] 时间间隔。fields 包括：
YEAR，MONTH， DAY，HOUR，MINUTE， SECOND，DAY TO HOUR，DAY TO MINUTE，DAY TO SECOND，HOUR TO MINUTE， HOUR TO SECOND，MINUTE TO SECOND。
p：秒数的精度，取值范围为0~6。
fields 为 SECOND，DAY TO SECOND，HOUR TO SECOND或 MINUTE TO SECOND 时，参数 p 才有效。		 12字节 
---（1）date类型
--- 创建表
postgres=# CREATE TABLE date_type_tab(coll date);
CREATE TABLE
postgres=#

--- 插入数据
postgres=# INSERT INTO date_type_tab VALUES (date '5-10-2022');
INSERT 0 1
postgres=#

--- 查看数据
postgres=# SELECT * FROM date_type_tab;
        coll
---------------------
 2022-05-10 00:00:00
(1 row)

postgres=#

--- 删除表
postgres=# DROP TABLE date_type_tab;
DROP TABLE
postgres=#

---（2）time、timestamp类型
--- 创建表
  CREATE TABLE time_type_tab (
    da time without time zone ,
    dai time with time zone,
    dfgh timestamp without time zone,
    dfga timestamp with time zone, 
    vbg smalldatetime);

postgres=# CREATE TABLE time_type_tab (
postgres(#     da time without time zone ,
postgres(#     dai time with time zone,
postgres(#     dfgh timestamp without time zone,
postgres(#     dfga timestamp with time zone,
postgres(#     vbg smalldatetime);
CREATE TABLE
postgres=#
 
--- 插入数据
postgres=# INSERT INTO time_type_tab VALUES ('21:21:21','21:21:21 pst','2010-12-12','2013-12-11 pst','2003-04-12 04:05:06');
INSERT 0 1
postgres=#
 
--- 查看数据
postgres=# SELECT * FROM time_type_tab;
    da    |     dai     |        dfgh         |          dfga          |         vbg
----------+-------------+---------------------+------------------------+---------------------
 21:21:21 | 21:21:21-08 | 2010-12-12 00:00:00 | 2013-12-11 16:00:00+08 | 2003-04-12 04:05:00
(1 row)

postgres=#

--- 删除表
postgres=# DROP TABLE time_type_tab;
DROP TABLE
postgres=#

---（3）时间间隔类型
--- 创建表
postgres=# CREATE TABLE day_type_tab (a int,b INTERVAL DAY(3) TO SECOND (4));
CREATE TABLE
postgres=#

--- 插入数据
postgres=# INSERT INTO day_type_tab VALUES (1, INTERVAL '3' DAY);
INSERT 0 1
postgres=#
 
--- 查看数据
postgres=# SELECT * FROM day_type_tab;
 a |   b
---+--------
 1 | 3 days
(1 row)

postgres=#

--- 删除表
postgres=# DROP TABLE day_type_tab;
DROP TABLE
postgres=#
   
--- 创建表
postgres=# CREATE TABLE year_type_tab(a int, b interval year (6));
CREATE TABLE
postgres=#
 
--- 插入数据
postgres=# INSERT INTO year_type_tab VALUES(1,interval '2' year);
INSERT 0 1
postgres=#
 
--- 查看数据
postgres=# SELECT * FROM year_type_tab;
 a |    b
---+---------
 1 | 2 years
(1 row)

postgres=#

--- 删除表
postgres=# DROP TABLE year_type_tab;
DROP TABLE
postgres=#

gbase8c0089.png

gbase8c0090.png

二进制类型

数据类型 描述 存储空间
BLOB 二进制大对象 最大为1GB-8203字节
（即1073733621字节）。
RAW 变长的十六进制类型 最大为1GB-8203字节
（即1073733621字节）。
BYTEA 变长的二进制字符串 最大为1GB-8203字节
（即1073733621字节）。		 
--- 创建表
postgres=# CREATE TABLE blob_type_t1 (BT_COL1 INTEGER, BT_COL2 BLOB, BT_COL3 RAW, BT_COL4 BYTEA) ;
CREATE TABLE
postgres=#
 
--- 插入数据
postgres=# INSERT INTO blob_type_t1 VALUES(10,empty_blob(), HEXTORAW('DEADBEEF'),E'\\xDEADBEEF');
INSERT 0 1
postgres=#
 
--- 查询表中的数据
postgres=# SELECT * FROM blob_type_t1;
 bt_col1 | bt_col2 | bt_col3  |  bt_col4
---------+---------+----------+------------
      10 |         | DEADBEEF | \xdeadbeef
(1 row)

postgres=#

--- 删除表
postgres=# DROP TABLE blob_type_t1;
DROP TABLE
postgres=#

gbase8c0091.png

布尔类型

用于表示真假的数据类型。

数据类型 描述 存储空间
BOOLEAN 布尔类型 1字节

  • “真”值的有效文本值是：
    TRUE、‘t’、‘true’、‘y’、‘yes’、‘1’ 、‘TRUE’、true、整数范围内12^63-1、整数范围内-1-2^63。

  • “假”值的有效文本值是：
    FALSE、‘f’、‘false’、‘n’、‘no’、‘0’、0、‘FALSE’、false。
    使用TRUE和FALSE是比较规范的用法（也是SQL兼容的用法）。

--- 创建表
postgres=# CREATE TABLE bool_type_t1 (id serial, BT_COL1 BOOLEAN, BT_COL2 TEXT);
NOTICE:  CREATE TABLE will create implicit sequence "bool_type_t1_id_seq" for serial column "bool_type_t1.id"
CREATE TABLE
postgres=#
 
--- 插入数据
postgres=# INSERT INTO bool_type_t1 (BT_COL1, BT_COL2) VALUES (TRUE, 'sic est');
INSERT 0 1
postgres=# INSERT INTO bool_type_t1 (BT_COL1, BT_COL2) VALUES (FALSE, 'non est');
INSERT 0 1
postgres=#
 
--- 查看数据
postgres=# SELECT * FROM bool_type_t1;
 id | bt_col1 | bt_col2
----+---------+---------
  1 | t       | sic est
  2 | f       | non est
(2 rows)

postgres=# SELECT * FROM bool_type_t1 WHERE bt_col1 = 't';
 id | bt_col1 | bt_col2
----+---------+---------
  1 | t       | sic est
(1 row)

postgres=#

 --- 删除表
postgres=# DROP TABLE bool_type_t1;
DROP TABLE
postgres=#

gbase8c0092.png

位串类型

位串就是一串1和0的字符串。它们可以用于存储位掩码。

数据类型 描述
bit(n) bit类型的数据必须准确匹配长度n，如果存储短或者长的数据都会报错。
bit varying(n) bit varying类型的数据是最长为n的变长类型，超过n的类型会被拒绝。
  • 如果用户明确地把一个位串值转换成bit(n)，则此位串右边的内容将被截断或者在右边补齐零，直到刚好n位，而不会抛出任何错误。
  • 如果用户明确地把一个位串数值转换成bit varying(n)，如果它超过了n位，则它的右边将被截断。
--- 创建表
postgres=# CREATE TABLE bit_type_t1 (BT_COL1 INTEGER, BT_COL2  BIT(3), BT_COL3 BIT VARYING(5)) ;
CREATE TABLE
postgres=#
 
--- 插入数据
postgres=# INSERT INTO bit_type_t1 VALUES(1, B'101', B'00');
INSERT 0 1
postgres=#
 
--- 插入数据的长度不符合类型的标准会报错
postgres=# INSERT INTO bit_type_t1 VALUES(2, B'10', B'101');
ERROR:  dn1: bit string length 2 does not match type bit(3)
CONTEXT:  referenced column: bt_col2
postgres=#
 
--- 将不符合类型长度的数据进行转换
postgres=# INSERT INTO bit_type_t1 VALUES(2, B'10'::bit(3), B'101');
INSERT 0 1
postgres=#
 
--- 查看数据
postgres=# SELECT * FROM bit_type_t1;
 bt_col1 | bt_col2 | bt_col3
---------+---------+---------
       1 | 101     | 00
       2 | 100     | 101
(2 rows)

postgres=#

--- 删除表
postgres=# DROP TABLE bit_type_t1;
DROP TABLE
postgres=#

gbase8c0093.png

其他数据类型

分类 数据类型 描述
文本搜索类型 tsvector 表示为文本搜索优化的文件格式，一个唯一标准词位的有序列表。
文本搜索类型 tsquery 表示检索条件，存储用于检索的词汇。
UUID 类型 uuid UUID是一个小写十六进制数字的序列，用来存储通用唯一标识符（UUID）。示例：a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11
JSON\JSONB 类型 JSON、JSONB 用来存储JSON数据，可以是单独的标量，也可以是数组，或键值对象。
XML 类型 xml 用来存储XML(可扩展标记语言)数据。

自定义数据类型

用户还可以通过 CREATE TYPE 语句创建自定义类型，支持 5 种：

  • 复合类型 由一个属性名和数据类型的列表指定。复合类型本质上和表的行类型相同，但是如果只想定义一种类型，使用CREATE TYPE避免了创建一个实际的表。单独的复合类型也是很有用的，例如可以作为函数的参数或者返回类型
  • 基本类型 用户可以自定义一种新的基本类型（标量类型）。通常来说这些函数必须是底层语言所编写。
  • shell类型 是一种用于后面要定义的类型的占位符。在创建基本类型时，需要shell类型作为一种向前引用。
  • 枚举类型 由若干个标签构成的列表，每一个标签值都是一个非空字符串，且字符串长度不能超过63个字节。
  • 集合类型 类似数组，但是没有长度限制，主要在存储过程中使用。
--- 创建一种复合类型，建表并插入数据以及查询。
postgres=# CREATE TYPE test_type1 AS ( f1 int, f2 text );
CREATE TYPE
postgres=# CREATE TABLE t1 ( a int, b  test_type1 );
CREATE TABLE
postgres=# INSERT INTO t1 values ( 1,( 1, 'demo' ) );
INSERT 0 1
postgres=# SELECT (b).f1 FROM t1 ;
 f1
----
  1
(1 row)

postgres=#

--- 创建一个枚举类型，建表并插入枚举值
postgres=# CREATE TYPE test_type2 AS ENUM ('create', 'modify', 'closed');
CREATE TYPE
postgres=#

postgres=# CREATE TABLE t2 ( a int, b  test_type2 );
CREATE TABLE
postgres=#

postgres=# INSERT INTO t2 values ( 1,'create' );
INSERT 0 1
postgres=#

--- 插入一个在 test_type2 中未定义的值时，将报错
postgres=# INSERT INTO t2 values ( 1,'new' );
ERROR:  invalid input value for enum test_type2: "new"
LINE 1: INSERT INTO t2 values ( 1,'new' );
                                  ^
CONTEXT:  referenced column: b
postgres=#

--- 创建一个集合类型
postgres=# CREATE TYPE test_type3 AS TABLE OF t1;
CREATE TYPE
postgres=#

--- 删除表
postgres=# DROP TABLE t1 CASCADE;
NOTICE:  drop cascades to type _t1[]
DROP TABLE
postgres=# DROP TABLE t2 CASCADE;
DROP TABLE
postgres=#

--- 删除自定义类型
postgres=# DROP TYPE  test_type1;
DROP TYPE
postgres=# DROP TYPE  test_type2;
DROP TYPE
postgres=#

gbase8c0094.png

常用函数

SQL函数的主要分类包括：（SQL函数指的是数据库内置函数，可以运用在SQL语句中实现特定的功能）

  • 单行函数（本课程主要介绍常用的单行函数）
  • 多行函数

单行函数 对于每一行数据进行计算后得到一行输出结果。

  • 单行函数的基本特性
    • 单行函数对单行操作
    • 单行函数可以写在 SELECT、WHERE、ORDER BY 子句中
    • 每行返回一个结果
    • 有些函数没有参数，有些函数包括一个或多个参数
    • 有可能返回值与原参数数据类型不一致
    • 函数可以嵌套
  • 根据数据类型分为 字符函数、数值函数、日期函数、转换函数 以及其他通用的函数等。
    • 字符函数：主要用于字符串与字符串、字符串与非字符串之间的连接，以及字符串的模式匹配操作。
    • 数值函数：主要用于数字操纵和数学计算等操作，如绝对值、平方根、随机值等。
    • 日期函数：主要用于获取系统时间，日期、时间类型的计算与格式化，如日期差值计算、日期截取等。
    • 转换函数：主要用于将一种数据类型转换成另一种数据类型，常见如数值与字符类型、字符与日期类型之间的转换以及转换时的格式化方式等。

常用字符函数

  • ascii(string)

描述：参数string的第一个字符的ASCII码。返回值类型integer

postgres=# SELECT ascii('xyz');
 ascii
-------
   120
(1 row)
  • btrim(string text [, characters text])

描述：从string开头和结尾删除只包含characters中字符（缺省是空白）的最长字符串。返回值类型：text

postgres=# SELECT btrim('sring' , 'ing');
 btrim
-------
 sr
(1 row)
  • ltrim(string [, characters])

描述：从字符串string的开头删除只包含characters中字符（缺省是一个空白）的最长的字符串。返回值类型：varchar

postgres=# SELECT ltrim('xxxxTRIM','x');
 ltrim
-------
 TRIM
(1 row)
  • rtrim(string [, characters])

描述：从字符串string的结尾删除只包含characters中字符（缺省是个空白）的最长的字符串。返回值类型：varchar

postgres=# SELECT rtrim('trimxxxx', 'x');
 rtrim
-------
 trim
(1 row)
  • upper(string)

描述：把字符串转化为大写。返回值类型：varchar

postgres=# SELECT upper('tom');
 upper
-------
 TOM
(1 row)
  • lower(string)

描述：把字符串转化为小写。返回值类型：varchar

postgres=# SELECT lower('TOM');
 lower
-------
 tom
(1 row)
  • concat(str1,str2)

描述：将字符串str1和str2连接并返回。返回值：varchar

postgres=# SELECT concat('Hello', ' World!');
    concat
--------------
 Hello World!
(1 row)
  • replace(string, substring)

描述：删除字符串string里出现的所有子字符串substring的内容。string 类型：text substring类型：text 。返回值类型：text

postgres=# SELECT replace('abcdefabcdef', 'cd');
 replace
----------
 abefabef
(1 row)
  • reverse(str)

描述：返回颠倒的字符串。返回值：text

postgres=# SELECT reverse('abcde');
 reverse
---------
 edcba
(1 row)
  • substrb(text,int,int)

描述：提取子字符串，第一个int表示提取的起始位置，第二个表示提取几位字符。返回值类型：text

postgres=# SELECT substrb('string',2,3);
 substrb
---------
 tri
(1 row)

gbase8c0095.png

gbase8c0096.png

其他常用字符函数

字符处理函数 描述 示例 结果
bit_length(string) 字符串的位数 SELECT bit_length(‘world’); 40
char_length(string) 字符串中字符的个数 SELECT char_length(‘hello’); 5
position(substring in string) 指定子字符串的位置。区分大小写。 SELECT position(‘ing’ in ‘string’); 4
string || string，string || non-string 连接字符串，或连接字符串和非字符串。 SELECT ‘GBase’||‘8c’； GBase8c
initcap(string) 将字符串中的每个单词的首字母转化为大写，其他字母转化为小写。 SELECT initcap(‘hi THOMAS’); Hi Thomas
right(str text, n int) 返回字符串中的后n个字符。 SELECT right ‘abcde’, 2); de
left(str text, n int) 返回字符串中的前n个字符。 SELECT left(‘abcde’, 2); ab

数字操作符

操作符 描述
+ 加
- 减
* 乘
/ 除（除法操作符不进行取整）
% 模运算
@ 绝对值
|/ 平方根
||/ 立方根
!! 阶乘（前缀操作符）
| 二进制OR
& 二进制AND
# 二进制XOR ~ 二进制NOT
^ 幂（指数运算）
<< 左移位
>> 右移位

常用数据函数

  • abs(exp), cos(exp), sin(exp): 返回表达式的绝对值，余弦值，正弦值。
postgres=# select abs(-10), cos(0), sin(0);
 abs | cos | sin
-----+-----+-----
  10 |   1 |   0
(1 row)
  • bitand(integer, integer)

描述：计算两个数字与运算(&)的结果。返回值类型： bigint类型数字

postgres=# SELECT bitand(127, 63);
 bitand
--------
     63
(1 row)
  • acos(exp), asin(exp): 返回表达式的反余弦值和反正弦值。
postgres=# SELECT acos(1), asin(0);
 acos | asin
------+------
    0 |    0
(1 row)
  • random()

描述：0.0到1.0之间的随机数。返回值类型： double precision

postgres=# SELECT random();
      random
------------------
 .169229347724468
(1 row)

gbase8c0097.png

其他常用数值函数

数值处理函数 描述 示例 结果
ceil(x) 不小于参数的最小的整数。 select ceil(-42.8); -42
floor(x) 不大于参数的最大整数。 select floor(-42.8); -43
ln(x) 自然对数。 select ln(2.0); .6931471805599453
log(x) 以10为底的对数。 select log(100.0); 2.0000000000000000
round(x) 离输入参数最近的整数。 select round(42.6); 43
sign(x) 输出此参数的符号。 -1表示负数，0表示0，1表示正数。 select sign(-8.4);
trunc(x) 截断（取整数部分）。 select trunc(42.8); 42
trunc(v numeric, s int) 截断为s位小数。 select trunc(42.4382, 2); 42.43

常用时间和日期函数

时间和日期函数 描述 示例 结果
current_date 当前时间。 select current_date; 2022-05-17
current_timestamp 当前日期及时间。 select current_timestamp; 2022-05-17 16:35:08.018834+08
date_trunc(text, timestamp) 截取到参数 text 指定的精度。 select date_trunc(‘hour’, timestamp '2001-02-16 20:38:40’); 2001-02-16 20:00:00
trunc(timestamp) 默认按天截取。 select trunc(timestamp ‘2001-02-16 20:38:40’); 2001-02-16 00:00:00
now() 当前日期及时间。 now(); 2022-05-17 16:47:22.123899+08
add_months(d,n) 用于计算时间点 d 再加上n 个月的时间。 select add_months(to_date(‘2017-5-29’, ‘yyyy-mm-dd’), 11); 2018-04-29 00:00:00
last_day(d) 用于计算时间点 d 当月最后一天的时间。 select last_day(to_date(‘2017-01-01’, ‘YYYY-MM-DD’)); 2017-01-31 00:00:00 
--- age(timestamp, timestamp): 将两个参数相减，并以年、月、日作为返回值。若相减值为负，则函数返回亦为负。
---                            两个参数类型必须相同，可以都带timezone，或都不带timezone。
postgres=# SELECT age(timestamp '2001-04-10', timestamp '1957-06-13');
           age
-------------------------
 43 years 9 mons 27 days
(1 row)

--- clock_timestamp(): 实时时钟的当前时间戳。
postgres=# SELECT clock_timestamp();
        clock_timestamp
-------------------------------
 2023-03-27 20:30:03.693875+08
(1 row)

--- current_date: 当前日期。
postgres=# SELECT current_date;
    date
------------
 2023-03-27
(1 row)

--- current_timestamp: 当前日期及时间。
postgres=# SELECT current_timestamp;
        pg_systimestamp
-------------------------------
 2023-03-27 20:30:31.285221+08
(1 row)

--- date_trunc(text, timestamp): 截取到参数text指定的精度。
postgres=# SELECT date_trunc('hour', timestamp '2001-02-16 20:38:40');
     date_trunc
---------------------
 2001-02-16 20:00:00
(1 row)

--- trunc(timestamp): 默认按天截取。
postgres=# SELECT trunc(timestamp '2001-02-16 20:38:40');
        trunc
---------------------
 2001-02-16 00:00:00
(1 row)

--- now(): 当前日期及时间。
postgres=# SELECT now();
              now
-------------------------------
 2023-03-27 20:31:24.979968+08
(1 row)

--- add_months(d,n): 用于计算时间点d再加上n个月的时间。
postgres=# SELECT add_months(to_date('2017-5-29', 'yyyy-mm-dd'), 11) FROM sys_dummy;
     add_months
---------------------
 2018-04-29 00:00:00
(1 row)

--- last_day(d): 用于计算时间点d当月最后一天的时间。
postgres=# SELECT last_day(to_date('2017-01-01', 'YYYY-MM-DD')) AS cal_result;
     cal_result
---------------------
 2017-01-31 00:00:00
(1 row)

gbase8c0098.png

时间日期操作符（+、-、*、/）

--- 时间日期操作符—(+)
postgres=# SELECT date '2001-9-28' + integer '7' AS RESULT;
       result
---------------------
 2001-10-05 00:00:00
(1 row)

postgres=# SELECT date '2001-09-28' + interval '1 hour' AS RESULT;
       result
---------------------
 2001-09-28 01:00:00
(1 row)

postgres=# SELECT date '2001-09-28' + time '03:00' AS RESULT;
       result
---------------------
 2001-09-28 03:00:00
(1 row)

postgres=# SELECT interval '1 day' + interval '1 hour' AS RESULT;
     result
----------------
 1 day 01:00:00
(1 row)

postgres=# SELECT timestamp '2001-09-28 01:00' + interval '23 hours' AS RESULT;
       result
---------------------
 2001-09-29 00:00:00
(1 row)

postgres=# SELECT time '01:00' + interval '3 hours' AS RESULT;
  result
----------
 04:00:00
(1 row)

--- 时间日期操作符—(-)
postgres=# SELECT date '2001-10-01' - integer '7' AS RESULT;
       result
---------------------
 2001-09-24 00:00:00
(1 row)

postgres=# SELECT date '2001-09-28' - interval '1 hour' AS RESULT;
       result
---------------------
 2001-09-27 23:00:00
(1 row)

postgres=# SELECT time '05:00' - time '03:00' AS RESULT;
  result
----------
 02:00:00
(1 row)

--- 时间日期操作符—(*)
postgres=# SELECT 900 * interval '1 second' AS RESULT;
  result
----------
 00:15:00
(1 row)

postgres=#
postgres=# SELECT 21 * interval '1 day' AS RESULT;
 result
---------
 21 days
(1 row)

postgres=#
postgres=# SELECT double precision '3.5' * interval '1 hour' AS RESULT;
  result
----------
 03:30:00
(1 row)

--- 时间日期操作符—(/)
postgres=# SELECT interval '1 hour' / double precision '1.5' AS RESULT;
  result
----------
 00:40:00
(1 row)

gbase8c0099.png

gbase8c0100.png

gbase8c0101.png

类型转换函数

  • cast(x as y)

描述：类型转换函数，将x转换成y指定的类型。

postgres=# SELECT cast('22-oct-1997' as timestamp);
      timestamp
---------------------
 1997-10-22 00:00:00
(1 row)
  • to_char(int, fmt)

描述：将整数类型的值转换为指定格式的字符串。fmt表示格式化方式。返回值类型： text

postgres=# SELECT to_char(125, '999');
 to_char
---------
  125
(1 row)
  • to_date(text,fmt)

描述：将字符串类型的值转换为指定格式的日期。 fmt表示格式化方式。

postgres=# SELECT to_date('05 Dec 2000', 'DD Mon YYYY');
       to_date
---------------------
 2000-12-05 00:00:00
(1 row)
  • to_number ( expr [, fmt])

描述：将expr按指定格式转换为一个NUMBER类型的值。fmt表示格式化方式。

postgres=# SELECT to_number('12,454.8-', '99G999D9S');
 to_number
-----------
  -12454.8
(1 row)
  • to_timestamp(text, fmt)

描述：将字符串类型的值转换为指定格式的时间戳。fmt表示格式化方式。

postgres=# SELECT to_timestamp('05 Dec 2000', 'DD Mon YYYY');
    to_timestamp
---------------------
 2000-12-05 00:00:00
(1 row)
  • to_bigint(varchar)

描述：将字符类型转换为bigint类型。

postgres=# SELECT to_bigint('123364545554455');
    to_bigint
-----------------
 123364545554455
(1 row)

gbase8c0102.png

日期\时间格式化模板

  • 小时
    • HH：一天的小时数（01-12）
    • HH12：一天的小时数（01-12）
    • HH24：一天的小时数（00-23）
  • 分
    • MI：分钟（00-59）
  • 秒
    • SS：秒（00-59）
    • FF：微秒（000000-999999）
    • SSSSS：午夜后的秒（0-86399）
  • 上、下午
    • AM或A.M.：上午标识
    • PM或P.M.： 下午标识
  • 年
    • Y,YYY：带逗号的年（4和更多位）
    • SYYYY：公元前四位年
    • YYYY：年（4和更多位）
    • YYY：年的后三位
    • YY：年的后两位
    • Y：年的最后一位
    • IYYY：ISO年（4位或更多位）
    • IYY：ISO年的最后三位
    • IY：ISO年的最后两位
    • I：ISO年的最后一位
    • RR：年的后两位（可在21世纪存储20世纪的年份）
    • RRRR：和YYYY相同。
    • BC或B.C.AD或A.D.：纪元标识。BC（公元前），AD（公元后）。
  • 月
    • MONTH：全长大写月份名（空白填充为9字符）
    • MON：大写缩写月份名（3字符）
    • MM：月份数（01-12）
    • RM：罗马数字的月份（I-XII ；I=JAN）（大写）
  • 天
    • DAY：全长大写日期名（空白填充为9字符）
    • DY：缩写大写日期名（3字符）
    • DDD：一年里的日（001-366）
    • DD：一个月里的日（01-31）
    • D：一周里的日（1-7 ；周日是 1）
  • 周
    • W：一个月里的周数（1-5）（第一周从该月第一天开始）
    • WW：一年里的周数（1-53）（第一周从该年的第一天开始）
    • IW：ISO一年里的周数（第一个星期四在第一周里）
  • 世纪
    • CC： 世纪（2位）（21 世纪从 2001-01-01 开始）
  • 儒略日
    • J： 儒略日（自公元前 4712 年 1 月 1 日来的天数）
  • 季度
    • Q： 季度

数值格式化模板

模式 描述
9 数位（如果无意义可以被删除）
0 数位（即便没有意义也不会被删除）
.(句号) 小数点
,(逗号) 分组（千）分隔符
PR 尖括号内的负值
S 带符号的数字（使用区域设置）
L 货币符号（使用区域设置）
D 小数点（使用区域设置）
G 分组分隔符（使用区域设置）
MI 在指明位置的负号（如果数字<0）
PL 在指明位置的正号（如果数字>0）
SG 在指定的位置的正/负号
RN 罗马数字（输入在1~3999之间）
TH 或 th 序数后缀
V 移动指定位（小数）
墨力计划gbase 8c
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

文章被以下合辑收录

GBase 8c 学习笔记（共17篇）
GBase 8c 学习笔记

评论