SinoDB 基本数据类型
SinoDB 数据类型
数据类型演示
创建员工信息表
databbase testdb;
create table temployee(
c_employeeid serial,
c_employeename varchar(50),
c_age int,
c_weight float,
c_height decimal(18,6),
c_salary money,
c_joindate date,
c_inputdate datetime year to second);
验证serial数据类型
插入三条记录，只输入员工姓名
insert into temployee(c_employeename) values('Lily');
insert into temployee(c_employeename) values('Tiger');
insert into temployee(c_employeename) values('Jim');
查询员工ID和员工姓名
> select c_employeeid, c_employeename from temployee;
c_employeeid c_employeename
1 Lily
2 Tiger
3 Jim
3 row(s) retrieved.
>
结论：在不提供Serial字段数值情况下，Serial字段会自动为新插入记录生成一个递增数值。
插入两条记录，输入员工ID和员工姓名
insert into temployee(c_employeeid, c_employeename) values(5, 'Henry');
insert into temployee(c_employeeid, c_employeename) values(8, 'Rose');
查询员工ID和员工姓名
> select c_employeeid, c_employeename from temployee;
c_employeeid c_employeename
1 Lily
2 Tiger
3 Jim
5 Henry
8 Rose
5 row(s) retrieved.
>
结论：在提供Serial字段数值情况下，新插入记录的Serial字段使用提供的数值，不再自动生成新值。
插入一条记录，只输入员工姓名
insert into temployee(c_employeename) values('Bily');
查询员工ID和员工姓名
> select c_employeeid, c_employeename from temployee;
c_employeeid c_employeename
1 Lily
2 Tiger
3 Jim
5 Henry
8 Rose
9 Bily
6 row(s) retrieved.
>
结论：当新插入的数据未提供Serial字段数值时，新记录的Serial字段值为当前表记录最大值加1。
插入一条记录，输入员工ID和员工姓名
insert into temployee(c_employeeid, c_employeename) values(6, 'Kitty');
查询员工ID和员工姓名
> select c_employeeid, c_employeename from temployee;
c_employeeid c_employeename
1 Lily
2 Tiger
3 Jim
5 Henry
8 Rose
9 Bily
6 Kitty
7 row(s) retrieved.
>
插入一条记录，输入员工姓名
insert into temployee(c_employeename) values('Sean');
查询员工ID和员工姓名
> select c_employeeid, c_employeename from temployee;
c_employeeid c_employeename
1 Lily
2 Tiger
3 Jim
5 Henry
8 Rose
9 Bily
6 Kitty
10 Sean
8 row(s) retrieved.
>
结论：当Serial字段数值出现【空位置】时，可以指定确定的值进入插入。
验证money类型
更新Bill的c_salary字段
update temployee set c_salary = 1234567890.1234 where c_employeeid = 9;
查询员工ID,员工姓名，薪水
> select c_employeeid, c_employeename, c_salary from temployee where c_employeeid > 8;
c_employeeid 9
c_employeename Bily
c_salary RMB 1234567890.12
c_employeeid 10
c_employeename Sean
c_salary
2 row(s) retrieved.
>
导出文本查看
> unload to '/home/informix/temployee.txt' delimiter ',' select c_employeeid,c_employeename, c_salary from temployee where c_employeeid > 8;
2 row(s) unloaded.
> !cat /home/informix/temployee.txt
9,Bily,1234567890.12,
10,Sean,,
>
结论：Money类型会根据本地化设置，自动显示相应的货币符号和保留对应的小数位数，但数据本身仍然是一个定点小数，并不存储有关货币符号的信息。
验证date类型
默认的date类型格式为月/日/年（如02/15/2022），当未设置任何有关date数据类型的环境变量时
[informix@vm84145 ~]$ export GL_DATE="%m/%d/%iY"
[informix@vm84145 ~]$ echo $GL_DATE
%m/%d/%iY
[informix@vm84145 ~]$ dbaccess testdb -
Database selected.
> update temployee set c_joindate = '02/15/2022' where c_employeeid = 1;
1 row(s) updated.
>
执行date数据操作时，如果日期格式不匹配，可能出现下面的错误
> update temployee set c_joindate = '2021-03-16' where c_employeeid = 1;
1205: Invalid month in date
Error in line 1
Near character position 68
>
设置如下环境变量，设置自己使用的日期格式
[informix@vm84145 ~]$ export GL_DATE="%iY-%m-%d"
[informix@vm84145 ~]$ echo $GL_DATE
%iY-%m-%d
[informix@vm84145 ~]$ dbaccess testdb -
Database selected.
> update temployee set c_joindate = '2021-03-16' where c_employeeid = 1;
1 row(s) updated.
> select c_employeeid, c_employeename,c_joindate from temployee where c_employeeid = 1;
c_employeeid c_employeename c_joindate
1 Lily 2021-03-16
1 row(s) retrieved.
结论：SinoDB可以根据数据中日期的具体格式，设置对应的GL_DATE，完成日期格式的输入。
验证datetime类型
假设设置的datetime类型格式为【2020-06-18 12:34:56】。。
update temployee set c_inputdate = '2021-07-23 12:34:56' where c_employeeid = 2;
select c_employeeid, c_employeename,c_inputdate from temployee where c_employeeid = 2;
> update temployee set c_inputdate = '2021-07-23 12:34:56' where c_employeeid = 2;
1 row(s) updated.
> select c_employeeid, c_employeename,c_inputdate from temployee where c_employeeid = 2;
c_employeeid 2
c_employeename Tiger
c_inputdate 2021-07-23 12:34:56
1 row(s) retrieved.
>
验证int,float,decimal类型
通过员工ID，更新员工年龄
> update temployee set c_age=33 where c_employeeid=1;
1 row(s) updated.
> select c_employeeid, c_employeename,c_age from temployee where c_employeeid = 1;
c_employeeid c_employeename c_age
1 Lily 33
1 row(s) retrieved.
>
通过员工ID，更新员工体重
> update temployee set c_weight=53.67 where c_employeeid=1;
1 row(s) updated.
> select c_employeeid, c_employeename,c_weight from temployee where c_employeeid = 1;
c_employeeid 1
c_employeename Lily
c_weight 53.67000000000
1 row(s) retrieved.
>
通过员工ID，更新员工身高
> update temployee set c_height=1.63 where c_employeeid=1;
1 row(s) updated.
> select c_employeeid, c_employeename,c_height from temployee where c_employeeid=1;
c_employeeid 1
c_employeename Lily
c_height 1.630000
1 row(s) retrieved.
总结
SinoDB 支持多种数据类型。
SinoDB 的基本数据类型，多数与其它数据库产品兼容。
SinoDB 提供了一些特有的数据类型，如Serial，Money，方便用户使用。
SinoDB 的日期时间类型，可通过环境变量设置，定义日期时间格式，方便用户使用。