GBase 8s 学习笔记 006 —— GBase 8s基本数据类型

GBase 8s数据类型

GBase 8s内置数据类型

数据类型演示

创建员工信息表

> database mydb;

Database selected.

> create table t_employee(
> f_employeeid serial,
> f_employeename varchar(50),
> f_age int,
> f_weight float,
> f_height decimal(18,6),
> f_salary money,
> f_joindate date,
> f_inputdate datetime year to second);

Table created.

> 

验证serial数据类型

插入三条记录，只输入员工姓名

> insert into t_employee(f_employeename) values('Tom');

1 row(s) inserted.

> insert into t_employee(f_employeename) values('Jack');

1 row(s) inserted.

> insert into t_employee(f_employeename) values('Mary');

1 row(s) inserted.

>

查询员工ID和员工姓名

> select f_employeeid, f_employeename from t_employee;


f_employeeid f_employeename

           1 Tom
           2 Jack
           3 Mary

3 row(s) retrieved.

>

结论： 在不提供Serial字段数值情况下，Serial字段会自动为新插入记录生成一个递增数值。

插入两条记录，输入员工ID和员工姓名

> insert into t_employee(f_employeeid, f_employeename) values(5, 'Henry');

1 row(s) inserted.

> insert into t_employee(f_employeeid, f_employeename) values(8, 'Rose');

1 row(s) inserted.

>

结论： 在提供Serial字段数值情况下，新插入记录的Serial字段使用提供的数值，不再自动生成新值。

插入一条记录，只输入员工姓名

> insert into t_employee(f_employeename) values('Bill');

1 row(s) inserted.

查询员工ID和员工姓名

> select f_employeeid, f_employeename from t_employee;


f_employeeid f_employeename

           1 Tom
           2 Jack
           3 Mary
           5 Henry
           8 Rose
           9 Bill

6 row(s) retrieved.

>

结论： 当新插入的数据未提供Serial字段数值时，新记录的Serial字段值为当前表记录最大值加1。

插入一条记录，输入员工ID和员工姓名

> insert into t_employee(f_employeeid, f_employeename) values(6, 'Kate');

1 row(s) inserted.

查询员工ID和员工姓名

> select f_employeeid, f_employeename from t_employee;


f_employeeid f_employeename

           1 Tom
           2 Jack
           3 Mary
           5 Henry
           8 Rose
           9 Bill
           6 Kate

7 row(s) retrieved.

插入一条记录，输入员工姓名

> insert into t_employee(f_employeename) values('Bob');

1 row(s) inserted.

查询员工ID和员工姓名

> select f_employeeid, f_employeename from t_employee;


f_employeeid f_employeename

           1 Tom
           2 Jack
           3 Mary
           5 Henry
           8 Rose
           9 Bill
           6 Kate
          10 Bob

8 row(s) retrieved.

结论： 当Serial字段数值出现【空位置】时，可以指定确定的值进入插入。

验证money类型

更新Bill的f_salary字段

> update t_employee set f_salary = 1234567890.1234 where f_employeeid = 9;

1 row(s) updated.

查询员工ID,员工姓名，薪水

> select f_employeeid, f_employeename, f_salary from t_employee where f_employeeid > 8;



f_employeeid    9
f_employeename  Bill
f_salary        RMB 1234567890.12

f_employeeid    10
f_employeename  Bob
f_salary

2 row(s) retrieved.

> unload to '/home/gbasedbt/employee.txt' delimiter ','
> select f_employeeid, f_employeename, f_salary
> from t_employee
> where f_employeeid > 8;

2 row(s) unloaded.

> !cat /home/gbasedbt/employee.txt;
9,Bill,1234567890.12,
10,Bob,,
>

结论： Money类型会根据本地化设置，自动显示相应的货币符号和保留对应的小数位数，但数据本身仍然是一个定点小数，并不存储有关货币符号的信息。

验证date类型

默认的date类型格式为月/日/年（如06/18/2020），当未设置任何有关date数据类型的环境变量时

> update t_employee set f_joindate = '06/18/2020' where f_employeeid = 1;

 1 row(s) updated.

执行date数据操作时，如果日期格式不匹配，可能出现下面的错误

> update t_employee set f_joindate = '2020-06-18' where f_employeeid = 1;

 1205: Invalid month in date
Error in line 1
Near character position 69
> 

设置如下环境变量，设置自己使用的日期格式

# 退出当前数据库环境
[gbasedbt@192 ~]$ export GL_DATE="%iY-%m-%d"
[gbasedbt@192 ~]$

再次执行更新操作

> !echo $GL_DATE
%iY-%m-%d
> update t_employee set f_joindate = '2020-06-18' where f_employeeid = 1;

1 row(s) updated.

> select f_employeeid, f_employeename,f_joindate from t_employee where f_employeeid = 1;


f_employeeid f_employeename                                     f_joindate

           1 Tom                                                2020-06-18

1 row(s) retrieved.

结论： GBase 8s可以根据数据中日期的具体格式，设置对应的GL_DATE，完成日期格式的输入。

验证datetime类型

默认的datetime类型格式为【2020-06-18 12:34:56】。

> update t_employee set f_inputdate = '2020-06-18 12:34:56' where f_employeeid = 2;

1 row(s) updated.

直接使用年月日时分秒的格式（如2020-06-18 12:34:56），更新datetime字段。

> update t_employee set f_inputdate = '2020-06-18 12:34:56' where f_employeeid = 2;

1 row(s) updated.

> select f_employeeid, f_employeename,f_inputdate from t_employee where f_employeeid = 2;



f_employeeid    2
f_employeename  Jack
f_inputdate     2020-06-18 12:34:56

1 row(s) retrieved.

验证int,float,decimal类型

通过员工ID，更新员工年龄

update t_employee set f_age = 28 where f_employeeid = 1;

查询员工ID,员工姓名，年龄

> update t_employee set f_age = 28 where f_employeeid = 1;

1 row(s) updated.

> select f_employeeid, f_employeename, f_age from t_employee where f_employeeid = 1;


f_employeeid f_employeename                                           f_age

           1 Tom                                                         28

1 row(s) retrieved.

>

通过员工ID，更新员工体重

update t_employee set f_weight = 56.78 where f_employeeid = 1;

> update t_employee set f_weight = 56.78 where f_employeeid = 1;

1 row(s) updated.

> select f_employeeid, f_employeename, f_weight from t_employee where f_employeeid = 1;



f_employeeid    1
f_employeename  Tom
f_weight        56.78000000000

1 row(s) retrieved.

>

通过员工ID，更新员工身高

update t_employee set f_height = 1.86 where f_employeeid = 1;

查询员工ID,员工姓名，身高

> update t_employee set f_height = 1.86 where f_employeeid = 1;

1 row(s) updated.

> select f_employeeid, f_employeename, f_height from t_employee where f_employeeid = 1;



f_employeeid    1
f_employeename  Tom
f_height        1.860000

1 row(s) retrieved.

>

总结

• GBase 8s 支持多种数据类型。
• GBase 8s 的基本数据类型，多数与其它数据库产品兼容。
• GBase 8s 提供了一些特有的数据类型，如Serial，Money，方便用户使用。
• GBase 8s 的日期时间类型，可通过环境变量设置，定义日期时间格式，方便用户使用。