--by王义飞
和oracle的区别
在客户端,sql最好是选中了执行,否则可能会不执行
gbase的数据类型
默认大小写不敏感,但在引号中的内容则是大小写敏感的,oracle引号内外均不敏感
数值型:
integer/int,
serial:自增类型,默认从1开始,可设定初始值,如:serial(n)
serial8
smallint
bigint
bigserial
decimal和numeric(p,s):对应oracle的number(p,s)
smallfloat和real:对应oracle的float
float和double precision:对应oracle的double
money(p,s):数据自带货币符号
时间型
date:日期类型,默认格式为MM/DD/YYYY,可通过GL_DATE环境变量修改:exportGL_DATE="%m/%d/%iY",修改环境变量后,需要重启实例才会生效
datetime:日期时间类型,默认格式为YYYY-MM-DD HH:MM:SS.FFF,可通过GL_DATETIME环境变量修改格式:exportGL_DATETIME="%iY-%m-%d%H:%M:%S",修改环境变量后,需要重启实例才会生效
interval:时间跨度,格式与datetime相同
字符型
char(n):超出长度,自动截断,不会报错
characater varying(n,r)
varchar(n,r),varchar的最大长度为255,最好都改成lvarchar
lvarchar(m):长度为32739,oracle的varchar2(m)的长度为32767
nchar(n)
nvarchar(m,r)
大对象
byte:简单大对象,储存在blobspace之上,储存声音,图片等二进制数据,最大支持2G
text:简单大对象,储存在blobspace之上,储存大块文本信息,如:文档,程序源代码,及文本文件等,相较于char(n),varchar(n),lvarchar(n)的优势就是text容量大,最大支持2G
blob:智能大对象,存储在sbspace之上,储存数字对象,如图像,声音,视频等二进制数据,最大支持4T大小
clob:智能大对象,存储在sbspace之上,储存文本类信息,最大大小为4TB
智能大对象优势:可恢复性,可以设置日志记录所有的写操作
读取和存储text和byte数据,我们一般使用支持嵌入sql的程序的方式,如java,esql/c,可以select,insert或者update来访问和操作简单大对象数据
与 Text 或者 Byte 数据一样，读取和存储智能大对象数据，我们一般使用支持嵌入 SQL 的程序方式，如 Java、ESQL/C，可以 select、insert 或者 update 来访问和操作简单大对象数据。同时 8s 针对智能大对象数据提供了专用的 API 来更智能的操作智能大对象数据。
Oracle中的Long Varchar等超过GBase 8s数据类型长度上限的，可以使用智能大对象来代替，CLOB或BLOB
布尔类型
boolean:t或f,oracle的boolean类型为true或false
复合数据类型
由一个或多个数据类型组合而成,
row:由一个或多个任意的数据类型组合而成
collection:由一个或多个同样的数据类型组合而成,包括set,list,multise,其中set:不予许集合重有重复的数据,multiset和list中都允许重复的数据,collection不允许有null元素,所以在定义collection时,必须指定not null约束
用户定义数据类型
distinct:
opaque:
row,set类型用法:
CREATE TABLE customer
( inx serial not null,
info ROW( sname CHAR(10),iage int),
interest set(char(20) not null),
bz set(row(s_bz char(10),i_bz int ) not null)
);
insert into customer (info,interest,bz)
values (row('张三',20),set{'音乐','足球','旅行'},set{row('xxx',1),row('xxx',2)});
update customer set info=row('李四',20) where inx=1;
> select * from customer;
inx 1
info ROW('李四 ',20 )
interest SET{'音乐 ','足球 ','旅行 '}
bz SET{ROW('xxx ',1 ),ROW('xxx ',2 )}
如何向date类型中插入数据
可以直接插入字符串(需要满足格式要求),或者用date函数将字符串转换后插入
create table test(
my_date date
)
insert into test values('02/17/2001');
insert into test values(date('02/17/2001'));
如何向datetime类型插入数据
可以直接插入字符串(需要满足格式要求),或者用datetime函数将字符串转换后插入
create table test(
my_datetime datetime year to fraction(2)
)
insert into test values('2009-01-17 12:12:12.22');
insert into test values(datetime('2009-01-17 12:12:12.22') year to fraction(2))
如何向interval类型插入数据
需要用interval函数进行转换
create table test(
my_interval interval hour to second
)
insert into test values(interval(04:06:07) hour to second);
如何创建byte类型字段
create table test(
my_byte byte in blobspace1
)
如何创建text类型字段
create table test(
id integer,
my_text text in blobspace1
)
如何查询text类型字段
select my_text[1,1000] from test where id = 100;
如何获取text类型字段保存数据的长度
select length(my_text) from test;
如何获取text不为空的数据
select my_text[1,1000] from test where my_text is[not] null;
如何向text类型字段插入数据
insert语句,只能将null值插入,不过可以使用insert...select...from方式拷贝数据
如何对text类型字段进行更新
update语句,只能更新为null,不过可以通过子查询的方式返回text/byte来更新
如何创建blob类型字段
create table test(
id int,
my_blob blob,
my_blob2 blob
)
put my_blob in(sbspace2), myblob2 in(sbspace2)
如何向blob类型插入数据
insert into test values(1,filetoblob('D:pic.jpg','client'),filetoblob('/tmp/test.txt','server'))
client:代表从客户端上传文件
server:代表从服务端上传文件
如何更新blob类型的数据
update test(my_blob) set my_blob = (select locopy(my_blob,'fbi_list','my_blob') from fbi_list where fbi_list.id = 200) where test.id = 1;
如何将blob的数据查询出来
select id,lotofile(my_blob,'F:my_blob.jpg','client') from test where id = 1;
使用LOTOFILE函数将数据库中存储的大对象信息，保存到指定目录。
如何创建integer类型字段
create table test(
id integer
)
如何创建int类型字段
create table test(
id int
)
系统数据库:
sysadmin:管理系统数据库,主要用来管理gbase8s系统管理相关的信息,
sysmaster:最重要的系统数据库,该数据库保存实例(instance)系统级别的信息,如实例运行的总体信息,所有的表等
sysuser
sysutils
sysmaster常用的表:
sysconfig:服务器配置参数
sysprofile:服务器统计信息 （onstat -p）
syslog:日志信息
sysvpprof:虚拟处理器
sysdbspaces:数据空间信息
syschunks:数据文件信息
sysdatabases:数据库信息
systabnames:数据库中的表
sysextents:表所占区域
sysptprof:表的i/o信息
syssessions:连接会话信息
syssesprof:用户统计信息
syslocks:用户锁
如何查询当前登录用户,实例名,以及数据库名
sql:
select a.cf_original, DBINFO('dbname') dbname,user user from sysmaster:sysconfig a where cf_name = 'DBSERVERNAME';
查询结果:
cf_original lisi
dbname testdb
user gbasedbt
如何查询实例中所有数据库名
sql:
select name from sysmaster:sysdatabases;
查询结果:
name sysmaster
name sysutils
name sysuser
name sysadmin
name testdb
查询testdb数据库中所有表名称。
sql:
select dbsname,tabname from sysmaster:systabnames where dbsname='testdb';
查询结果:
dbsname testdb
tabname systables
dbsname testdb
tabname syscolumns
查询实例中用户权限。
sql:
SELECT username,usertype FROM sysmaster:sysusers;
查询结果:
username usertype
gbasedbt D
public C
其中:userType:D为DBA,C为Connect,R为Resource,G为角色
查询数据库版本信息。
sql:
select DBINFO('version','full') version from sysmaster:sysdual;
查询结果:
version
GBase 8s Database Server Version 12.10.FC4G1TL
查询数据库基本信息
select dbinfo('UTC_TO_DATETIME',sh_boottime) start_time,
current year to second - dbinfo('UTC_TO_DATETIME',sh_boottime) run_time,
sh_maxchunks as maxchunks, sh_maxdbspaces maxdbspaces,
sh_maxuserthreads maxuserthreads, sh_maxtrans maxtrans,
sh_maxlocks locks,sh_nlrus buff_lrus, sh_longtx longtxs,
dbinfo('UTC_TO_DATETIME',sh_pfclrtime) onstat_z_running_time
from sysmaster:sysshmvals;
数据权限:
用户对某一数据对象的的操作权利被称为权限。用户权限是由数据库对象和操作类型两个要素组成的，定义一个用户的权限就是定义这个用户可以对哪些数据对象进行哪些类型的操作。GBase 8s使用了三级权限来保证数据的安全性，它们分别是数据库级权限，表级权限和字段级权限。
数据库级别权限：
connect:这是级别最低的一种数据库级别的用户权限。拥有该权限的用户可以执行SELETE ，UPDATE，INSERT，DELETE语句，针对数据表执行存储过程，创建数据表的视图，创建临时表。
resource:拥有该权限的用户除了拥有CONNECT全部权限外，还可以创建新的表，并可以对自己创建的表执行ALTER和DROP操作，创建索引。
dba:数据库的建立者和拥有者被自动授予这种权限。拥有DBA权限的用户除拥有RESOURCE全部权限外，还可以对其他用户授予或解除CONNECT，RESOURCE，DBA权限，可以对所有数据库对象进行操作。
表级与字段级权限：
insert:
delete:
select:
update:
references:
index:
alter:
all:
如何给用户赋权限
登录dba账号
GRANT 权限 TO 用户名 [ ,用户名 ]
REVOKE 权限 FROM 用户名 [,用户名]
我们可以创建四种类型的数据库(database)：
不记录日志:CREATE DATABASE mydb;
缓冲式的记录日志:CREATE DATABASE mydb WITH BUFFERED LOG;
无缓冲式的记录日志:CREATE DATABASE mydb WITH LOG;
ANSI (记录日志时无缓冲 ):CREATE DATABASE mydb WITH LOG MODE ANSI;
数据库的日志模式
不记录日志:无日志记录，如果硬件故障，损失从上次备份以来的所有记录。
缓冲式的记录日志:日志记录经由共享内存的逻辑日志缓冲区。如果硬件故障可能损失数个最近的改变。系统的速度因 逻辑日志缓冲区而提升。
无缓冲式的记录日志:日志记录直接写入磁盘。如果硬件故障，只会损失未提交的事务(uncommitted transactions)。
ANSI:与 unbuffered logging 同样，遵从 ANSI 规则。
日志模式之间允许切换
创建表:
CREATE [ TEMP ] TABLE <表名>
( <列名1> <数据类型> [列级完整性约束] ,
<列名1> <数据类型> [列级完整性约束] ,
......
[ 表级完整性约束1 ],
[ 表级完整性约束2 ],
......) [ IN “< 表的存放路径>” ]
gbase8s中,不支持表名为#,$等符号,oracle支持
gbase8s中,表名长度为128字节,oracle中为30字节
其中temp表示建的是临时表,表名要唯一,字段要唯一,不可对临时表进行alter和drop操作,
在不同回话中,gbase8s可以建立同名临时表,oracle则不可以
例如:
create table student(
id serial not null,
name lvarchar(15) default '张三'
sex lvarchar(32) check(sex in ('男','女')),
age int,
primary key (id)
) in datadbs1 extent size 640 next size 320;
extent size指定了表的初始大小
next size指定了再次分配大小
oracle中,是storage inital和storage next,而minextents,maxextents,pictincrease在gbase8s中没有对应项,需要去除
create table school(
id serial(1001) primary key,
date datetime year to fraction(3),
num integer not null,
grade float,
foreing key (id) references student (id) on delete cascade,
)
删除表:
drop table 表名;
实例:
drop table student;
截断表:
truncate [table] student[drop|resume storage]
约束:
主键约束:跟oracle一样,例如:id serial primary key,
外键约束:跟oracle一样,例如:foreing key (id) references student (id) on delete cascade,
检查约束:用来对输入的数据按照设置的逻辑进行检查,例如:sex lvarchar(32) check(sex in ('男','女'))
default约束:如果是字符串类型,default后面必须要加单引号,oracle中不必加,例如:name lvarchar(32) default '张三'
可以通过select as的方式来创建表
create table table1 as select * from table2 where 1 = 2;
这种方式创建的表,不能复制主键和索引
如果只想复制表结构,而不导入数据,可以加上where 1=2的条件
如果想复制表结构的同时,复制表数据,可以不加where 1=2
创建视图的语法:
create view 视图名 [(<字段名>,<字段名>...)]
as <子查询>
[with check option]
with check option 选项与视图更新有关，如果使用该选项，则对试图进行INSERT INTO、UPDATE和DELETE操作时，要满足<子查询>中的条件表达式。
注意子查询中,不能使用order by和union
视图名后面可跟字段名,也可以不加
[]的东西可以写也可以不写
gbase8s建立视图的语句和oracle类似,但Oracle中force，no force，read only需要删除
示例:
create view v_student as select t.* from student t where t.sex = '男' with check option;
更新视图:
update v_student t set t.age = t.age + 1 where t.name = '张三';--由于上面加上了with check option,只能更新sex='男'的数据
向视图插入数据
insert into v_student values('1','张三','男','18');--由于上面加上了with check option,只能插入sex为男的数据
删除视图的语法:
drop view 视图名 cascade
加上cascade后,可以将该视图和他导出的所有视图一起删除
实例:
drop view v_student;
建立索引的语法:
create [unique|distinct] [cluster] index <索引名> on <表名> (<列名1>[asc|desc][,<列名2>[asc|desc]]...);
unique:将该索引设置为唯一索引
cluster:将该索引设置为群簇索引
asc|desc:表名该列是按照升序还是降序索引
gbase8s创建索引:create index index_student on student (name) [in datadbs1] [online];
oracle中创建索引:create index index_student on student (name) tablespace idx;
gbase8s中,索引储存位置默认和表所在dbspace一致
删除索引
drop index <索引名> [on <表名>];
实例:
drop index index_student on student (name);
序列:
1.第一次nextval返回的是初始值；随后的nextval会自动增加你定义的increment by值，然后返回增加后的值。
currval总是返回当前sequence的值，但是在第一次nextval初始化之后才能使用currval，否则会出错。
一次nextval会增加一次sequence的值。但是如果你在同一个条SQL语句里面针对同一个sequence使用多次nextval，其值都是一样的。
2.如果指定cache值，就可以预先在内存里面放置一些sequence，这样存取的快些。cache里面的取完后，自动再取一组到cache。
使用cache或许会跳号， 比如数据库突然不正常down掉（shutdown abort),cache中的sequence就会丢失.
所以可以在create sequence的时候用nocache防止这种情况
创建序列:
create sequence <序列名称>
[increment by 步长]
[start with 开始值]
[maxvalue 最大值|nomaxvalue]
[minvalue 最小值|nominvalue]
[cycle|nocycle]
[cache 缓存大小|nocache];
调用序列:
通过nextval来获取下一个序列值
通过currval来获取当前序列值
每个序列占8个字节空间
删除序列:
drop sequence 序列名;
示例:
drop sequence student_seq
select语法:
select [all|distinct] <列表达式>,...
from <表名>,...
[where <条件表达式>]
[group by <列名>,...[having <条件表达式>]]
[order by <列名> [asc|desc],...]
case when:
和oracle一样
外链接:
inner join
left join
right join
full join
和oracle一样
unio和union all
和oracle的一样
子查询:
和oracle的一样
exists关键字:
和oracle的一样
分布式查询:
在一个查询语句中,同时访问多个数据库的数据
跨实例分布式查询
在一个查询语句中,同时访问不在同一实例数据库中的数据
连接来自同一8s实例的两个数据库表:
SELECT a.lname, a.fname
FROM CustomerDB@1stinst:Customer a, OrdersDB@1stinst.orders b
WHERE a.customer_num = b.customer_num
连接来自两个不同的 GBase 8s 实例的数据库表
SELECT a.lname, a.fname
FROM CustomerDB@1stinst:Customer a, OrdersDB@2ndinst.orders b
WHERE a.customer_num.b = customer_num
批量插入:
和oracle的一样
更新数据:
和oracle的一样
删除数据:
和oracle的一样
如果sql有问题,如何定位问题的位置(调试sql的方法):
需要用xshell连上gbase数据库
用db-access命令来执行sql,然后就可以定位了
用xshell连公司的gbase数据库
ip:192.168.56.43
端口号:17934
用户名:informix
密码:123456
进入后,
输入命名:dbaccess
选择datatable,回车
选择select,回车
选择数据库,回车
选择exit,回车
选择query-language,回车
选择new,回车
将sql拷贝过来,按下esc
回到命令行后,选择run,回车,这是会提示报错信息
选择modify,回车,此时会定位到sql出错的地方
选择modify,再次回车,就可以编辑sql了,按照上面modify给出的命令来进行操作,改完后,按下esc,选择run,就可以运行了
如何导出导入单表数据:
使用unload和load
用db-access执行下面的命令
dbaccess mjzz -
导出表数据:unload to /tmp/mjzz_user.db select * from mjzz_user;
导入表数据:load from /home/informix/temp/mjzz_user.db insert into mjzz_user;
如何导出导入整个数据库
用xshell连接gbase数据库
切换为informix用户
su - informix
将所有有关的应用停掉,所有链接的可视化工具停掉
导出执行:
dbexport gos -o "/home/informix/tmp"
dbexport mjzz -o "/home/informix/tmp"
dbexport cnpo -o "/home/informix/tmp"
gos为表空间, -o 后面指定导出的文件夹
导入执行:
如果有数据,则需要删除数据:
删除数据库
dbaccess - -
drop database cnpo;
drop database mjzz;
drop database gos;
dbimport gos -d datadbs1 -l buffered -i "指定到文件所在目录即可"
dbimport gos -d datadbs1 -l buffered -i "/home/informix/tmp"
dbimport mjzz -d datadbs1 -l buffered -i "/home/informix/tmp"
dbimport cnpo -d datadbs1 -l buffered -i "/home/informix/tmp"
可以指定目录,也可以不指定目录,在文件所在目录执行:dbimport gos -d datadbs1 -l buffered
gos为导入的数据
报错后,可以按照提示信息去服务器上查询报错原因:
需要用xshell连上gbase数据库
输入命令:finderr 1265
后面的数字是在可视化工具上报错的数字
然后会显示报错的原因
如何获取系统的当前时间
前提需要创建一个dual表
select sysdate from dual;(含年月日时分秒五位精度的毫秒)
select sysdate from dual;(含年月日时分秒三位精度的毫秒)
select today from dual;(含年月日)
如何获取系统的前一天的时间
前提需要创建一个dual表
select sysdate - 1 units day from dual;(含年月日时分秒五位数的毫秒)
select today - 1 from dual;(含年月日)
select sysdate - 1*interval(24) hour to hour from dual;
如何获取系统的前一个月的时间
前提需要创建一个dual表
select sysdate - 1 units month from dual;
如何获取系统的前一年的时间
前提需要创建一个dual表
select sysdate - 1 units year from dual;
如何获取系统的前一小时的时间
前提需要创建一个dual表
select sysdate - 1 units hour from dual;
如何获取系统的前一分钟的时间
前提需要创建一个dual表
select sysdate - 1 units minute from dual;
如何获取系统的前一秒钟的时间
前提需要创建一个dual表
select sysdate - 1 units second from dual;
如何将日期转换为字符串
select to_char(sysdate,'%Y-%m-%d') from dual;--2020-04-09
select to_char(sysdate,'%Y-%m-%d') from dual;--20-04-09
如何将字符串转换为日期
select to_date('2020-04-04','%Y-%m-%d') from dual;
如何获取日期的年月日
select extend(sysdate,year to year) from dual;
select extend(sysdate,year to month) from dual;
select extend(sysdate,year to day) from dual;
select extend(sysdate,year to hour) from dual;
select extend(sysdate,year to minute) from dual;
select extend(sysdate,year to second) from dual;
select extend(sysdate,month to month) from dual;
select extend(sysdate,day to day) from dual;
select extend(sysdate,hour to hour) from dual;
select extend(sysdate,minute to minute) from dual;
select extend(sysdate,second to second) from dual;
如何修改字段名
rename column tableName.column to newColumn;
如何计算两个日期之间间隔了多少天
select (((current - (current - 2 units day))::interval day(9) to day)||'')::integer from dual;
这里的9表示精度
如何计算两个日期之间间隔了多少分钟
select (((current - (current - 2 units day))::interval minute(9) to minute)||'')::integer from dual;
这里的9表示精度
执行insert的时候报错:
Unique constraint (informix.u303_203) violated
这个是违反了唯一约束条件
通过可视化工具打开该表
然后找到对应的u303_203约束
然后将该约束去掉,重试
可能是唯一约束,也可能是索引约束
和oracle不同的地方
获取序列不兼容
没有select seq.nextval from dual这种写法
解决办法:创建一个dual表,并且添加一条数据
CREATE TABLE cnpo:dual (
id varchar(32)
)
in datadbs1 ;
insert into dual values('1');
这样gbase和oracle都能通用了
批量获取序列不兼容
没有select 1 from all_objects where rownum <= ?,这种写法
解决办法:创建一个all_objects表
CREATE TABLE cnpo:all_objects (
rownum numeric(32,0)
)
in datadbs1 ;
CREATE SEQUENCE all_objects_seq
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 999999999999
NOCYCLE
CACHE 20;
--执行多次,差不都有五千条数据就够了
insert into all_objects select all_objects_seq.nextval from tai_sys_user;
select * from all_objects where rownum <= 500;
select max(rownum) from all_objects;
这样gbase和oracle可以通用
插入操作不兼容
insert into tableName (column1,column2,...) (select column1,column2... from dual)
解决方案:insert into tableName (column1,column2,...) select column1,column2... from dual
去掉两个括号
oracle也适配上面的写法
批量插入不兼容
insert into tableName (column1,column2,...) (select content1,content2... from dual) union all (select content1,content2... from dual)
解决方案:insert into tableName (column1,column2,...) select * from (select content1 as column1,content2 as column2... from dual union all select content1 as column1,content2 as column2... from dual)
oracle也适配上面的写法
需要给字段起别名,否则oracle会报错,未明确定义的列
批量插入不兼容:
用#好不好使
insert into tai_sys_user_resource (resourceid,userid)
select * from (
<foreach collection="array" item="item" index="index" separator="union all">
select #{item.resourceid},#{item.userid} from dual
</foreach>
)
解决方案:改成$
insert into tai_sys_user_resource (resourceid,userid)
select * from (
<foreach collection="array" item="item" index="index" separator="union all">
select '${item.resourceid}' as resourceid,'${item.userid}' as userid from dual
</foreach>
)
oracle也适配上面的写法
必须要加别名,否则oracle中,会报错,未明确定义的列
可视化工具的表的非空标识并不是完全正确的,有的字段有非空校验,但是显示的是可以非空
foreach标签中,只能用$不能用#
gbase返回的结果字段默认为小写,oracle返回的结果集字段默认为大写
解决方案:在resultType中不能用map,要用实体
这样oracle和gbase都可以适配
不能用null来设置为字段
select null as column from dual;
会报语法错误
需要将varchar
问题:同步信用的用户中,orgid有空格
问题:orgid为什么会是固定的四位长度呢,这个找一下原因
substring用法:
和oracle的substr用法不同
select substring('123456' from 1 for 4) from dual;
--结果为:1234
从第一位开始截取,截取4位长度
replace:
用法和oracle一样
select replace('111','1','2') from dual;
trim:
用法和oracle一样
select trim(' 11 ') from dual;
启动gbase数据库
xShell连上服务器
切换到gbase数据库:su - informix
查看使用量:onstat -d
启动gbase:oninit -vy
发表评论