openGauss开源数据库实战七

最新推荐文章于 2025-04-19 10:13:58 发布

倾歌为君的01

最新推荐文章于 2025-04-19 10:13:58 发布

阅读量913

点赞数 23

分类专栏：数据库实战文章标签：数据库开源 gaussdb centos

本文链接：https://blog.youkuaiyun.com/weixin_73321443/article/details/143169277

版权

数据库实战专栏收录该内容

28 篇文章

订阅专栏

任务七测试 openGaussDBMS 的数据类型

任务目标

在设计数据库时，需要为表的属性选择合适的数据类型。opengaussDBMS支持多种数据类型，熟悉和掌握这些数据类型非常重要。本任务的目的是让读者尽快掌握和使用openGauss DBMS的各种数据类型。

实验步骤

一、登陆到数据库 studentdb

使用Linux用户omm,打开一个Linux终端窗口,执行如下的命令,使用数据库用户student
登录到数据库studentdb:

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r

二、测试数值数据类型

1. 整数类型

(1)smallint smallint类型的数据用两个字节(B)存储,最小值为-32768,最大值为32767。执行下面的语句系列,可以验证smallint类型数据的取值范围:

DROP TABLE IF EXISTS test;
CREATE TABLE test(col1 smallint);
INSERT INTO test VALUES(-32769);         -- 超范围出错
INSERT INTO test VALUES(-32768);         -- 范围之内的最小值
INSERT INTO test VALUES(32767);          -- 范围之内的最大值
INSERT INTO test VALUES(32768);          -- 超范围出错

Fig_01
(2)integer integer类型的数据用4个字节存储,最小值为-2147483648,最大值为2147483647。执行下面的语句系列,可以验证integer类型数据的取值范围:

DROP TABLE IF EXISTS test;
CREATE TABLE test(col1 integer);
INSERT INTO test VALUES(-2147483649);         -- 超范围出错
INSERT INTO test VALUES(-2147483648);         -- 范围之内的最小值
INSERT INTO test VALUES(2147483647);          -- 范围之内的最大值
INSERT INTO test VALUES(2147483648);          -- 超范围出错

(3)bigint bigint类型的数据用8个字节存储,其最小值为一9223372036854775808,最大值为9223372036854775807。执行下面的语句系列,可以验证bigint类型数据的取值范围:

DROP TABLE IF EXISTS test;
CREATE TABLE test(col1 bigint);
INSERT INTO test VALUES(-9223372036854775809);         -- 超范围出错
INSERT INTO test VALUES(-9223372036854775808);         -- 范围之内的最小值
INSERT INTO test VALUES(9223372036854775807);          -- 范围之内的最大值
INSERT INTO test VALUES(9223372036854775808);          -- 超范围出错

Fig07_02

2.任意精度数据类型

任意精度数据类型有numberic(precision,scale)和decimal(precision,scale)两种写法,这两种写法是等价的。其中,precision表示总共占多少位,scale表示小数部分占多少位。
执行下面的SQL语句系列,测试任意精度数据类型:

DROP TABLE IF EXISTS test;
CREATE TABLE test(col1 number(4,2));
INSERT INTO test VALUES (-100);            -- 超范围出错
INSERT INTO test VALUES (-99.995);         -- 四舍五入后，超范围出错
INSERT INTO test VALUES (-99.994);         -- 四舍五入后，在范围之内
INSERT INTO test VALUES (-99.99);          -- 在范围之内
INSERT INTO test VALUES (99.99);           -- 在范围之内
INSERT INTO test VALUES (99.994);          -- 四舍五入后，在范围之内
INSERT INTO test VALUES (99.9949);         -- 四舍五入后，在范围之内
INSERT INTO test VALUES (99.995);          -- 四舍五入后，超范围出错
INSERT INTO test VALUES (100);             -- 超范围出错

Fig07_03

3.浮点类型

(1)real real类型的数据用4个字节存储。用户输人real类型的数,有可能在openGauss数据库中保存的是一个近似值(不是精确值)。
执行下面的SQL语句系列,测试real数据类型:

DROP TABLE IF EXISTS test;
CREATE TABLE test(col1 real);
INSERT INTO test VALUES (12345678901234567890.12345678901234567890);
INSERT INTO test VALUES ('Infinity');
INSERT INTO test VALUES ('-Infinity');
INSERT INTO test VALUES ('NaN');
SELECT * FROM test;

这里插入图片描述

foat和double precision 数据类型 foat 和 double precision是等价的。这两种类型的数据在openGauss中都是用8个字节存储的。用户输入float类型或者double precision类型的数,有可
能在openGauss数据库中保存的是一个近似值(不是精确值)。
执行下面的SQL语句系列,测试foat数据类型:

DROP TABLE IF EXISTS test;
CREATE TABLE test(col1 float);
INSERT INTO test 
VALUES (12345678901234567890.12345678901234567890);
INSERT INTO test VALUES (92.3);
INSERT INTO test VALUES (92.345678);
INSERT INTO test VALUES (92345678.9);
INSERT INTO test VALUES ('Infinity');
INSERT INTO test VALUES ('-Infinity');
INSERT INTO test VALUES ('NaN');
SELECT * FROM test;

对比real和foat数据类型的测试可以发现,12345678901234567890.12345678901234567890
是real类型时在openGauss数据库中存储为“1.23457e+19”,是fioat类型时在openGauss数据库中存储为“1.23456789012346e+19”,后者显然存储的精度更高,代价是在数据库中占用更多的存储空间。
在这里插入图片描述
(3)foat§ 可以为foat类型的数据指定一个精度值p,p的取值范围是[1,53],它是二进
制位表示的最大可接受精度。
执行下面的SQL语句系列,测试float§数据类型:

DROP TABLE IF EXISTS test;
CREATE TABLE test (col1 float(2));
INSERT INTO test VALUES (92.3);
INSERT INTO test VALUES (92.34);
INSERT INTO test VALUES (92.345);
INSERT INTO test VALUES (92.3456);
INSERT INTO test VALUES (92.34567);
INSERT INTO test VALUES (92.345678);
INSERT INTO test VALUES (92.3456789);
SELECT * FROM test;

在这里插入图片描述
可以看到,指定float(2)后,数据库中存储的数最多保留了小数点后面的4位。

4.序列类型

(1)smallserial smallserial类型的数据用两个字节存储,范围是1~32767执行下面的SQL语句系列,测试smallserial数据类型:

DROP TABLE IF EXISTS test;
-- 创建一个表，表的列col1数据类型是smallserial
CREATE TABLE test (col1 smallserial,col2 char(10));
-- 查看序列的信息
\d test_col1_seq; 
-- 使用序列插入2条记录
INSERT INTO test(col2) VALUES('gray'); 
INSERT INTO test(col2) VALUES('gray'); 
SELECT * FROM test;

-- 将序列的当前值设置为32766
SELECT setval('test_col1_seq', 32766, false);
-- 使用序列插入3条记录（插入第3条的时候会出错！）
INSERT INTO test(col2) VALUES('gray'); --正常
INSERT INTO test(col2) VALUES('gray'); --正常
INSERT INTO test(col2) VALUES('gray'); --出错，系列超过了最大值
-- 查看序列的信息
\d test_col1_seq;

你可能会好奇SELECT setval('test_col1_seq', 32766, false);的作用是什么，setval()其实是一个函数，通过设置当前值（last_value）和调用标志（is_called）来调整序列的行为。
在这里插入图片描述
(2)serial serial类型的数据用4个字节存储,范围是1~2147483647。执行下面的SQL语句系列,测试serial数据类型:

DROP TABLE IF EXISTS test;
CREATE TABLE test (col1 serial,col2 char(10));
-- 查看序列的信息
\d test_col1_seq; 

-- 使用序列插入2条记录
INSERT INTO test(col2) VALUES('gray'); 
INSERT INTO test(col2) VALUES('gray'); 
SELECT * FROM test;

-- 将序列的当前值设置为2147483646
SELECT setval('test_col1_seq',2147483646, false);
-- 使用序列插入3条记录（插入第3条的时候会出错！）
INSERT INTO test(col2) VALUES('gray'); --正常
INSERT INTO test(col2) VALUES('gray'); --正常
INSERT INTO test(col2) VALUES('gray'); --出错，系列超过了最大值
-- 查看序列的信息
\d test_col1_seq;

(3)bigserial bigserial类型的数据用8个字节存储,范围是1~9223372036854775807。执行下面的SQL语句系列,测试bigserial数据类型:

DROP TABLE IF EXISTS test;
CREATE TABLE test (col1 bigserial,col2 char(10));
-- 查看序列的信息
\d test_col1_seq; 
-- 使用序列插入2条记录
INSERT INTO test(col2) VALUES('gray'); 
INSERT INTO test(col2) VALUES('gray'); 
SELECT * FROM test;

-- 将序列的当前值设置为9223372036854775806
SELECT setval('test_col1_seq', 9223372036854775806, false);
-- 使用序列插入3条记录（插入第3条的时候会出错！）
INSERT INTO test(col2) VALUES('gray'); --正常
INSERT INTO test(col2) VALUES('gray'); --正常
INSERT INTO test(col2) VALUES('gray'); --出错，系列超过了最大值
-- 查看序列的信息
\d test_col1_seq;

5.货币类型

使用money数据类型时,会根据环境变量lc_monetary值的不同,显式不同的货币符号。执行下面的SQL语句系列,测试money数据类型:

DROP TABLE IF EXISTS test;
CREATE TABLE test(col1 money);
SHOW lc_monetary;
INSERT INTO test VALUES (100);
SELECT * FROM test;
SET lc_monetary="zh_CN.utf8";
SELECT * FROM test;
SET lc_monetary="en_US.utf8";
SELECT * FROM test;

Fig07_08

三、测试字符串类型

1.character(n)或者char(n)

character(n)或者char(n)是定长字符串类型,该类型数据长度不足时会使用空格来进行填充。
执行下面的SOL语句系列,测试character(n)数据类型:

DROP TABLE IF EXISTS test;
CREATE TABLE test (col1 character(10));
INSERT INTO test VALUES ('1234567890');  -- 在长度范围之内
INSERT INTO test VALUES ('12345678901'); -- 出错，超过了最大长度
INSERT INTO test VALUES ('123');         -- 在长度范围之内
SELECT col1, length(col1) FROM test;

在这里插入图片描述

2.character varying(n)或者 varchar(n)

charactervarying(n)和varchar(n)这两种数据类型等价,都是变长字符申类型,该类型数据按实际的字符数进行存储,但是不能超过最大的预定义长度n。
执行下面的SQL语句系列,测试charactervarying(n)数据类型:

DROP TABLE IF EXISTS test;
CREATE TABLE test (col1 character varying(10));
INSERT INTO test VALUES ('1234567890');  -- 在长度范围之内
INSERT INTO test VALUES ('12345678901'); -- 出错，超过了最大长度
INSERT INTO test VALUES ('123');         -- 在长度范围之内
SELECT col1, length(col1) FROM test;

DROP TABLE IF EXISTS test;
CREATE TABLE test (col1 varchar(10));
INSERT INTO test VALUES ('1234567890');  -- 在长度范围之内
INSERT INTO test VALUES ('12345678901'); -- 出错，超过了最大长度
INSERT INTO test VALUES ('123');         -- 在长度范围之内
SELECT col1, length(col1) FROM test;

3. text

text是变长字符串类型,其最大存储空间为1GB。text等价于varchar(这里varchar没有限制
最大长度n)。
执行下面的SOL语句系列,测试text数据类型:

DROP TABLE IF EXISTS test;
CREATE TABLE test (col1 text);
INSERT INTO test VALUES ('1234567890'); 
SELECT col1, length(col1) FROM test;

DROP TABLE IF EXISTS test;
CREATE TABLE test (col1 varchar);  -- varchar没有限制长度的n
INSERT INTO test VALUES ('1234567890'); 
SELECT col1, length(col1) FROM test;

四、测试布尔类型

boolean是布尔类型,该类型的数据只存储两个值:true或者false。
执行下面的SQL语句系列,测试boolean数据类型:

DROP TABLE IF EXISTS test;
CREATE TABLE test (col1 boolean);
INSERT INTO test VALUES ('t'); 
INSERT INTO test VALUES ('f'); 
INSERT INTO test VALUES ('y'); 
INSERT INTO test VALUES ('n'); 
INSERT INTO test VALUES ('1'); 
INSERT INTO test VALUES ('0'); 
INSERT INTO test VALUES ('on'); 
INSERT INTO test VALUES ('off'); 
INSERT INTO test VALUES ('yes'); 
INSERT INTO test VALUES ('no'); 
INSERT INTO test VALUES ('true'); 
INSERT INTO test VALUES ('false'); 
SELECT col1 FROM test;

可以看到,无论在插入时使用什么来表示bool类型的值,在查询显示时都显示为“”或者“f”。

五、测试日期类型和时间类型

1.时区

时区是当地时间与格林尼治时间的时差。格林尼治时间也称为格林尼治标准时(Greenwich
MeanTime,GMT),现在称为协调世界时(英:Coordinated UniversalTime。法:TempsUniversel
Coordonne),又称为世界统一时间、世界标准时间、国际协调时间。其英文缩写(CUT)和法文缩写(TUC)不同,作为妥协,简称为UTC。
时区有多种表示方法。第一种表示方法是POSIX时区表示法,如STDoffset、STDoffsetDST,
其中STD是一个区域的缩写,ofrset是相对于UTC的偏移量,DST是一个可选的夏令时区域缩
写。第二种表示方法使用时区的缩写,如PST、PRC。第三种表示方法使用完整的时区名字,如America/New_York

2.DATE数据类型

DATE数据类型使用4个字节来存储日期(即年月日,不包括一天中的时分秒)数据。执行下面的语句系列,为表的列输人DATE类型的数据:

DROP TABLE IF EXISTS test;
CREATE TABLE test ( col1 DATE );
show datestyle;        --显示日期的格式
INSERT INTO test VALUES (date '08-09-2017');
set datestyle='YMD';   --设置日期的格式
INSERT INTO test VALUES (date '2018-09-10');
SELECT * FROM test;

在这里插入图片描述
下面是另外一种输入日期类型数据的方法,它使用了to_date函数:

DROP TABLE test;
CREATE TABLE test ( col1 DATE );
INSERT INTO test VALUES (to_date('08-09-2017','MM-DD-YYYY'));
INSERT INTO test VALUES (to_date('08-09-2017 01:02:03','MM-DD-YYYY HH:MI:SS'));
SELECT col1,length(col1) FROM test;
SELECT (to_char(col1,'HH24:MI:SS')) col1_TIME FROM  test ;
SELECT (to_char(col1,'MM-DD-YYYY')) col1_Date FROM  test;
SELECT (to_char(col1,'MM-DD-YYYY HH:MI:SS')) col1_DateAndTime FROM  test;

3.TIMESTAMPTZ§和 TIMESTAMP§with time zone

TIMESTAMPTZ§和TIMESTAMP§withtime zone这两种数据类型存储了带有时区信息的
时间截数据。
执行下面的语句系列,为表的列输入带有时区信息的时间截数据:

DROP TABLE test;
CREATE TABLE test ( col1 TIMESTAMPTZ(3));
show datestyle;
INSERT INTO test VALUES (timestamp'08-09-2017 9:00:01.234');
INSERT INTO test VALUES (timestamp'08-09-2018 9:00:01.2345');
SELECT col1 FROM test;
select now();   -- now()函数返回一个带时区的时间戳

下面是另外一种输入时间戳数据的方法,它使用了to_timestamp函数:

DROP TABLE test;
CREATE TABLE test( col1 TIMESTAMPTZ(3));
INSERT INTO test VALUES (to_timestamp('08-09-2017 9:00:01','MM-DD-YYYY hh24:mi:ss'));
INSERT INTO test VALUES (now());
SELECT col1 FROM test;

4.TIMESTAMP§和 TIMESTAMP§whithout time zone

TIMESTAMP§和TIMESTAMP§without time zone这两种数据类型存储了不带有时区信息
的时间截数据。
执行下面的语句系列,为表的列输入不带有时区信息的时间截数据:

DROP TABLE test;
CREATE TABLE test ( col1 TIMESTAMP(3));
INSERT INTO test VALUES (timestamp'08-09-2017 9:00:01.234');
INSERT INTO test VALUES (timestamp'08-09-2018 9:00:01.2345');
SELECT col1 FROM test;

下面是另外一种输人时间截数据的方法,它使用了to_timestamp函数:

DROP TABLE test;
CREATE TABLE test ( col1 TIMESTAMP(3));
INSERT INTO test VALUES (to_timestamp('08-09-2017 9:00:01','MM-DD-YYYY hh24:mi:ss'));
INSERT INTO test VALUES (now());
SELECT col1 FROM test;

5.TIME§with time zon

TIME§withtimezone数据类型存储了带有时区信息的时间(时分秒)数据。
执行下面的语句系列,为表的列输人带有时区信息的时间(时分秒)数据:

DROP TABLE test;
CREATE TABLE test ( col1 TIME(3) with time zone);
INSERT INTO test VALUES ('9:00:01');
INSERT INTO test VALUES ('9:00:01.234');
INSERT INTO test VALUES ('9:00:01.235');
SELECT col1 FROM test;

6.TIME§without time zone

TIME§withouttimezone数据类型存储了不带有时区信息的时间(时分秒)数据。
执行下面的语句系列,为表的列输入不带有时区信息的时间(时分秒)数据:

DROP TABLE test;
CREATE TABLE test ( col1 TIME(3) without time zone);
INSERT INTO test VALUES ('9:00:01');
SELECT col1 FROM test;

7.interval

Interval数据类型存储两个时间截计算出的时间间隔数据。

DROP TABLE test;
CREATE TABLE test ( col1 TIMESTAMPTZ,col2 TIMESTAMPTZ);
INSERT INTO test VALUES (to_timestamp('08-09-2017 9:00:01','MM-DD-YYYY hh24:mi:ss'),now());
SELECT col2-col1 days FROM test;

DROP TABLE test;
CREATE TABLE test ( col1 interval day(3) to second(4));
INSERT INTO test VALUES (interval '3' DAY);
INSERT INTO test VALUES (interval '3' YEAR);
SELECT * FROM test;

8.使用日期类型和时间类型的好处

使用日期类型和时间类型最大的好处是,可以支持日期和时间运算。

DROP TABLE test;
CREATE TABLE test (col1 DATE);
insert into test values(now());
insert into test values(now()+7);
insert into test values(now()-31);
select (to_char(col1,'MM-DD-YYYY HH:MI:SS')) col1_DateAndTime from  test;
select * from test;

9.时间值函数

(1)now()函数 now()函数返回当前的日期和时间。

select now();

(2)ADD_MONTHS()函数 ADD_MONTHS()函数有两个参数:DATE类型数据和一个数(正
负表示时间的前后)。

DROP TABLE test;
CREATE TABLE test (col1 DATE);
insert into test values(now());
select * from test;
select ADD_MONTHS(col1,12) from test;
select ADD_MONTHS(col1,-12) from test;

(3)LAST_DAY()函数 LAST_DAY()函数返回时间点当月最后一天的时间。

select * from test;
select LAST_DAY(col1) from test;

(4)NEXT_DAY()函数 NEXT_DAY()函数有两个参数:第1个参数x是一个时间；第2个参数y表示星期几,1表示星期日,7表示星期六。该函数用于计算从时间点x开始的下一个星期几(y)对应的时间。

DROP TABLE test;
CREATE TABLE test (
	col1 DATE
);
insert into test values(now());
select col1,NEXT_DAY(col1,1) from test;
select col1,NEXT_DAY(col1,7) from test;

六、测试枚举类型

数据库中的枚举类型,对应于一些编程语言中的cnum类型。
执行下面的语句,创建和测试一个枚举类型:

CREATE TYPE weekday as
            ENUM('Sun','Mon','Tues','Wed','Thur','Fri','Sat');
DROP   TABLE   test;
CREATE TABLE test(col1 weekday);
INSERT INTO  test VALUES ('Sun'); 
INSERT INTO  test VALUES ('Mon'); 
INSERT INTO  test VALUES ('Tues'); 
INSERT INTO  test VALUES ('Wed'); 
INSERT INTO  test VALUES ('Thur'); 
INSERT INTO  test VALUES ('Fri'); 
INSERT INTO  test VALUES ('Sat'); 
SELECT * FROM test;