gbase8s sql的使用（语法） 一

此篇笔记主要是非过程语言的集合，主要是分为五部分：第一部分和第二部分主要分为DDL和DML语言的写法；第三部分为其他一些升级语法的演示；第四部分为SELECT语句对系统表和一些函数的使用；第五部分为各种需求的SQL开发内容。

DDL

CREATE：包含表和各种特殊对象的创建

ALTER：包含表结构的修改、权限的修改和名称的修改

DROP：和各种对象的删除

DML

INSERT：针对各种类型的数据插入

DELETE：包含各种语法的删除

UPDATE：对数据的修改

SELECT：SELECT的语法

升级语法

SELECT语法其他内容的引用

根据需求对系统表的相关简单查询

SELECT语句对函数的应用

SQL开发内容

系统表的组合应用

针对需求做的剪切和拼接应用

一、DDL的语法-CREATE

(一) 数据库的CREATE创建

1. 数据库的多种格式的创建

--不记录日志
create database nolog;
 --记录缓冲日志
create database bufferdb with buffered log;
--无缓冲日志
create database logdb with log;
--特殊形式ansi记录日志无缓冲
create database ANSIDB with log mode ANSI;

2. 创建数据库指定数据空间

 --创建数据库指定数据空间
 create database log1db in datadbs1  with log;

3. 大小写相关参数

CREATE DATABASE stores2 IN datadbs1 WITH LOG NLSCASE INSENSITIVE; 

数据类型限制：NLSCASE INSENSITIVE 设置仅影响 NCHAR 和 NVARCHAR 类型的字符串。对于 CHAR、LVARCHAR 和 VARCHAR 类型，数据库仍然保持区分大小写。

查询优化：由于大小写不敏感的特性，查询时需要特别注意大小写相关的条件表达式，确保与数据库的实际行为相符。

跨数据库连接：具有 NLSCASE INSENSITIVE 属性的数据库只能与具有相同属性的数据库进行跨数据库或跨服务器查询。不能与区分大小写的数据库进行连接。

数据迁移与升级：在升级或迁移数据库时，需确保目标数据库的大小写敏感性设置与原数据库一致，避免因大小写规则改变导致的数据不一致或查询异常。

(二) 表的创建和各种特殊类型的创建

1. 表的相关创建事项

• varchar不指定长度会默认为1
• 当输入的数据超过指定长度时不会报错而是进行截断

2. 普通表的创建

create table test1(id int,name varchar(10);

3. 带有序列、非空、默认值、检查约束和主键，并指定数据空间和自动扩展

--带有序列、非空、默认值、检查约束和主键，并指定数据空间和自动扩展
create table pu_test1(
  sno serial not null,
  sname varchar(10) default 'zhang',
  ssex char(2) check (ssex in('男','女')),
  primary key(sno)
) 
in datadbs1 
extent size 640 next size 320 ;

4. 主键、非空和外键的表

#主键，非空，外键
create table sc(
cno serial(1001) primary key,
cdate datetime year to fraction(3),
sno int not null,
cgrade float,
foreign key (sno) references student(sno) on delete cascade);

5. 带有大对象的表创建

--简单大对象text、byte
create table test_text_byte(
  id int,
  tx text in blobdbs,
  by byte in blobdbs
);


--智能大对象blob、clob
create table test_blob_clob(
  id int,
  bl blob,
  cl clob)
  put 
  bl in(sbspace1),
  cl in(sbspace1);

6. 复合类型row、set的创建

create table fuhe(
  id int,
  info row(age int,name varchar),
  aihao set(varchar(100) not null),
  gx set(row(gage int,gname varchar(10)) not null)
);

7. 创建带有空格列名的表

--增加环境变量
export DELIMIDENT=TRUE
create table t2("k c" int);

(三) 临时表的创建

1. 临时表的创建

退出页面临时表会被清空

create temp table t1(id int);
insert into t1 values(11);
select * from t1;
         id
         11
[gbasedbt@fu gbasedb1_dbs]$ dbaccess test -
Your evaluation license will expire on 2025-01-02 00:00:00
Database selected.
> select * from t1;
  206: The specified table (t1) is not in the database.

  111: ISAM error:  no record found.
Error in line 1
Near character position 16

(四) 分区表的创建

1. 分区表方式的简介

分区表又名分片表，其通过分区的方式、分区表位置和分区表名称设置三种构成：

• 分区表的方式

• • 表达式分片
  • 轮训法分片
  • 列表分区
  • 自动分片

• 分区表位置

• • 每个分片表一个数据空间
  • 多个分片表的位置设置在一个数据空间

• 分区表名称

• • 指定分区表名称
  • 不指定分区表名称

注意事项

• remainder分片没有需要的数据也会被扫描，所以尽量避免使用reaminder分片

2. 表达式分片单数据空间指定分区表名称

create table p2(id int,name varchar(100)) 
fragment by expression 
partition p2_1 id <100 in datadbs1,
partition p2_2 remainder in datadbs1；

3. 表达式分片多数据空间指定分区表名称

--指定分区表的名称
create table p1_cp(id int,name varchar(10)) 
fragment by expression 
partition p1_cp1 id<100 in datadbs1,
partition p1_cp2 id>99 and id<200 in datadbs2,
partition p1_cp3 id>199 and id <300 and name='a' in datadbs3,
partition p1_cp4 remainder in datadbs4;

4. 表达式分片多数据空间未指定分区表名称

--未指定分区表的表名
create table p1(id int,name varchar(10)) 
fragment by expression 
id<100 in datadbs1,
id>99 and id<200 in datadbs2,
id>199 and id <300 and name='a' in datadbs3,
remainder in datadbs4;

5. 列表分片单数据空间指定分区表名称

create table p3(name varchar(10),keben varchar(10),fen int) 
fragment by list(name) 
partition p3_a values('a') in datadbs1,
partition p3_b values('b') in datadbs1,
partition p3_re remainder in datadbs1;

6. 列表分片多数据空间指定分区表名称

create table p3(name varchar(10),keben varchar(10),fen int) 
fragment by list(name) 
partition p3_a values('a') in datadbs1,
partition p3_b values('b') in datadbs2,
partition p3_re remainder in datadbs3;

7. 数值间隔分区

需要配置空值的分区和小于0的分区，需要两个数据空间，其他的分区表名称会自动创建

• interval（1000）表示数值没间隔1000就建立一个分区

create table p1(id int,name varchar(10)) 
fragment by range(id) 
interval(1000) store in(datadbs1,datadbs2) 
partition p0 values is null in datadbs1,
partition p1_1 values<0 in datadbs2;

8. 时间间隔分区

需要确定现场环境的时间类型格式

• interval：1 units day间隔1天
• store in：将会存储的数据空间
• 需要建立第一个数据空间

create table t17(id int,name varchar(110),k timestamp) 
fragment by range (k)  
interval(1 units day) 
store in(datadbs1,datadbs2) 
partition p10 values<date("2020-1-1") in rootdbs;

(五) 索引的创建

1. 普通btree索引的创建

create index test1 on test1(id) using btree;

2. 唯一索引的创建

create unique index test1 on test1(id) using btree;

3. 排序后的索引

--倒序
create unique index test1 on test1(id desc) using btree;
--正序
create unique index test1 on test1(id asc) using btree;

4. 分区表索引的创建

• 表不分片也可以创建分片索引

--表达式索引
create index test1_idx on test1(id) 
fragment by 
expression (id <=100) in datadbs,
(id>100) in data2dbs;

5. 其他索引

--去重索引
create distinct index test1 on test1(id asc) using btree;

--集簇索引
create cluster index test1 on test1(id asc) using btree;

(六) 其他对象的创建

1. 视图

--无检查条件视图
create view v_t2 as select * from t_v2 where age>18 ;
--有检查条件视图
 create view v_t2 as select * from t_v2 where age>18 with check option;

2. 序列

create sequence seqname
INCREMENT BY 步长
start with 开始值
maxvalue 最大值|NOMAXVALUE
minvalue 最小值|nominvalue
CYCLE|NOCYCLE
cache|nocache；

 create sequence seq1 
 increment by 1 
 start with 0 
 maxvalue 20  
 cycle;

3. 外部表

create external table table_name_ext 
sameas table_name 
	using
  (datafiles ("DISK:/home/gbase/exp/table_name.unl"),
format "delimited", 
   express, 
   rejectfile "/tmp/table_name_ext.err", escape on
  );

rm /home/gbasedbt/tb1.unl
rm /home/gbasedbt/tb2.unl
dbacess gbaedb -
drop table if exists tb1_new;
create external table tb1_new sameas tb1
using
(datafiles ("DISK:/home/gbasedbt/tb1.unl",
"DISK:/home/gbasedbt/tb2.unl"),  
	format "delimited",
	DELIMITER "|",
	express,
	rejectfile "/home/gbasedbt/errorlog.err",
	maxerrors 1000
);

time echo "alter table tb1 type(raw);
set pdqpriority 96;
set isolation dirty read;
set environment IFX_BATCHEDREAD_TABLE '1';
insert into tb1_new select * from tb1;" | dbaccess -e gbasedb -

二、DDL语言-结构的更改

(一) 表的更改

1. 普通表增加字段

--增加age字段，类型为int
alter table p1 add age int;
alter table tablename add(age int );

2. 删除字段

alter table p1 drop age;

3. 更改字段类型

alter table p1 modify age varchar(10);

(二) 约束

1. 增加主键和删除主键约束

--增加主键
alter table p1  add constraint  primary key(id)  constraint p1_pk;
--删除主键
alter table p1 drop constraint p1_pk;

2. 增加唯一约束

--增加唯一约束
alter table p1 add constraint unique(id) constraint p1_un;
--删除唯一约束
alter table p1 drop constraint p1_un;

3. 非空约束

alter table t3 modify id int not null;

alter table t3 nodify (id int not null);

4.增加外键

--外键指的是一张表的某个字段和另外一张表的主键进行关联，要求reference后面的表需要有唯一索引或者主键
--创建fk1表，其中 class是科目字段
create table fk1(id int,name varchar(10),class varchar(10));
--创建表fk2，其中class字段为主键，credit为科目对应的学分
create table fk2(class varchar(10) primary key,credit int);
--对表fk1增加外键
alter table fk1 add constraint foreign key(class) references fk2(class) constraint fk1_fk;

 

(三) 特殊对象-分区表的改动

1. 注意事项

• alter fragment是单个事务，所有的移动或者增加都会写事务，注意产生长事务，可以考虑关闭日志或者将操作分成多个小的alter fragment进行操作

• alter fragment 执行时需要表级锁，面对是否记录日志将会有两种情况

• • 记录日志：当数据移动时，原先的数据会被删除移动到新的片，但删除数据时，数据占用的extent仍是被使用状态，只有所有的数据移动后才可直接使用，需要有足够的剩余空间
  • 不记录日志：原先的片会被保留，直到成功后会被释放

2. 针对分区表无法获取表级锁的特殊操作

alter fragment语句需要表级锁，在摘除分片或者挂载分片时可能由于其他业务要求无法独占表，所以为了避免这种情况，将会停止其他访问这张表的事务暂时终止的措施

--通过force_ddl_exec指定整数值，数据库尝试在这段时间内把其他事务进行回滚，以便相关操作可以进行
set environment force_ddl_exec "60";
set lock mode to wait 10;
set environment force_ddl_exec "off";

• set environment force_ddl_exec

• • 在日志记录或者ansi的数据库中使用
  • 只有dbsa用户可以使用
  • 如果没有设置锁等待上限，语句不会生效
  • 如果另外一个用户在同一张表上执行DDL语句，alter fragment将会失败
  • 如果有其他的用户也在执行这个语句，，第二个用户将会返回错误
  • 如果系统表被锁住，alter fragment将会返回错误

3. 分区表初始为普通表

--将分区表p2初始化成普通表在数据空间datadbs1
alter  fragment on table p2 init in datadbs1;

4. 普通表初始化为分区表

alter fragment on table p2 
init fragment by list(name) 
partition p2_1 values('a') in datadbs1,
partition p2_2 values('b') in datadbs1,
partition p2_3 remainder in datadbs1;

5. 分区表单分区变为普通表

--表达式分区，将分区表p3的分片p3_2和p3_3分离出去，两种写法
alter fragment on table p3 detach p3_3 p3_p;
alter fragment on table p3 detach partition  p3_2 p3_2p;

--轮询分区，将p4分片p4_3分离出去
alter fragment on table p4 detach p4_3 p4_3p;

--列表分区
alter fragment on table p2 detach partition p2_4 p2_4p;

--间隔分区，将分区表p1的分片sys_p34剔除变为表p_34
alter fragment online on table p1 detach partition sys_p34 p_34;

6. 分区表将普通表表加入分区（轮询不可以）

--表达式分区，将分割出去的表p3_p重新加入分区并命名为p3_3
alter fragment on table p3 
attach p3_p  
as 
partition p3_3 id>300 and id <400;

--列表分区
alter fragment on table p2
attach p2_b 
as  
partition p2_2 values('b');

--间隔分区
alter fragment on table p1 
attach p_34 
as 
partition sysp34 values<34000;

(四) 分区规则的改动

1. 增加一个分区规则（间隔分区不可以）

--表达式分区
alter fragment on table p3 add partition p3_3 id>300 and id <400 in datadbs1;

--轮询分区
alter fragment on table t6 add datadbs1;
alter fragment on table p4 add partition  p4_4 in datadbs1;

--列表分区
alter fragment on table  p2 add partition p2_4 values('c') in datadbs2;

2. 更改分区表的整体分区规则

alter fragment on table p1 
init fragment by range (id)  
interval(100) store in(datadbs1,datadbs2)
 partition p0 values is null in datadbs1,
partition p1 values <0 in datadbs2;

3. 更改分区表单个分区规则

alter fragment on table k1 
modify k1_2 
to   
id <400 and id >100 in datadbs1;

4. 删除一个分区

--普通表达式分区
alter fragment on table p3 drop p3_3;
--轮询分区
alter fragment on table p4 drop partition p4_4;
--列表分区
 alter  fragment on table p2 drop partition p2_3;
 --间隔分区
 alter fragment on table p1 drop partition sysp34;

三、DML语言

(一) 特殊对象的插入

1. insert的注意事项和特殊字段类型的描述

• 特殊事项

• • 如果不指定列名则默认全部列，列数必须匹配否则失效

• 特殊字段

• • varchar

• • • 默认为1字节
    • 超过指定字节的数据插入不报错而是自动截断

2. 复合类型ROW和SET类型字段的插入

--复合类型的更新和插入
> insert into fuhe values(1,row(11,'kk'),set{'g','g1'},set{row(1,'fdsf'),row(2,'fsdf')});
1 row(s) inserted.
> select * from fuhe;
id     1
info   ROW(11         ,'k')
aihao  SET{'g','g1'}
gx     SET{ROW(1          ,'fdsf'),ROW(2          ,'fsdf')}
1 row(s) retrieved.

3. 简单大对象的插入

text和byte为简单大对象，此类值只能使用load进行插入

--创建表
create table t1(id int,js text,js2 byte);
--需要插入的数据
cat t1.txt
1|jfdoslfjsdlofjso|000010011001
2|fjdolsfjdlskfjsd|010010101011
--将数据插入到表内
load from '/home/gbasedbt/t1.txt' delimiter "|"  insert into t1;

4. 智能大对象在oracle模式gbase模式下插入的区别

• gbase模式

> insert into t5 values(filetoblob('/home/gbasedbt/sh/create.sql','gbasedb1'));
1 row(s) inserted.
> insert into t2 values(1,filetoblob('/home/gbasedbt/sh/create.sql','server'),filetoclob('/home/gbasedbt/sh/p.png','gbasedb1'));
1 row(s) inserted.
> insert into t2 values(1,filetoblob('/home/gbasedbt/sh/create.sql','gbasedb1'),filetoclob('/home/gbasedbt/sh/p.png','gbasedb1'));
1 row(s) inserted.

• oracle模式

#设置特有的大对象数据空间
onspace -c -P sbdbs -p sbchk -o 0 -s 512000
#将sbdbs设置为默认的智能大对象数据空间
SBSPACENAME sbdbs
#dbaccess gbasedb1 -进入环境，开启oracle兼容模式
set environment sqlmode 'oracle';
#创建有智能大对象的表且插入
> create table test4(id int,test blob) put test in(dbdbs);
Table created.
> insert into test4 values(1,'fdsfsdf');
(U0001) - blob_input: cannot convert LO from argument string
Error in line 1
Near character position 37
> set environment sqlmode 'oracle';
Environment set.
> insert into test4 values(1,'fdsfsdf');
1 row(s) inserted.

(二) 对象的更新

1. 简单大对象的更新

2. 智能大对象的更新

--准备数据
> create table b1(id int,b blob,c clob);
> insert into b1 values(1,filetoblob('/home/gbasedbt/t1.txt','server'),filetoclob('/home/gbasedbt/t1.txt','server'));
1 row(s) inserted.
> select * from b1;
id  1
b   <SBlob Data>
c
1|jfd|000010011001
2|fjd|010010101011
1 row(s) retrieved.

--使用update更改c字段，将b2表的c字段覆盖到b1表的c字段
> update b1(c) set c=(select locopy(c,'b2','c') from b2 where id=1) where id=1;
> select * from b1;
id  1
b   <SBlob Data>
c
1|jfd|000010011
2|fjd|0101011

3. 复合类型ROW和SET类型字段的更新

--复合类型的更新
> update fuhe set aihao=set{'g2','g4'};

1 row(s) updated.

> select * from fuhe ;



id     1
info   ROW(11         ,'k')
aihao  SET{'g2','g4'}
gx     SET{ROW(1          ,'fdsf'),ROW(2          ,'fsdf')}

1 row(s) retrieved.

(三) 其他特殊数据变动语法

1. 将大对象复制到文件中

--创建表
create table t1(id int,js text,js2 byte);
--将字段js导出到文件js.txt内
> select lotofile(js,'/home/gbasedbt/js.txt','server') from t1;
(expression)  /home/gbasedbt/js.txt.0000000065d55fd0
(expression)  /home/gbasedbt/js.txt.0000000065d55fd1

2. merge into数据批量插入

> select * from t5;
         id name
         19 yuan
         20 liu
         18 su
         16 li
         22 li
5 row(s) retrieved.
> select * from t6;
         id name
          1 sss
         16 dong
         18 su
3 row(s) retrieved.

> merge into t5 using t6 on t5.id=t6.id
when matched then update set t5.name=t6.name 
when not matched then insert values(t6.id,t6.name);
3 row(s) merged.
> select * from t5;
         id name
         19 yuan
         20 liu
         18 su
          1 sss
         16 dong
         22 li
6 row(s) retrieved.

四、select的语法

(一) select的数据查询

1. 关联子查询和非关联子查询

关联子查询可能出现性能不好的情况，可以将关联子查询转为非关联或者表连接查询

--关联子查询
> select * from t_v2 where exists(select * from t4 where t_v2.id=t4.id);
         id         age name
          1          18 su
1 row(s) retrieved.

--非关联子查询
> select * from t_v2 where id =(select id from t4);
         id         age name
          1          18 su
1 row(s) retrieved.

--关联子查询的改动及性能
> select count(*) from p1 where exists(select * from t3 where id=p1.id);
      (count(*))
             865
1 row(s) retrieved.
Elapsed time: 18.189 sec


> select count(*) from p1 where id in(select id from t3)；
      (count(*))
             865
1 row(s) retrieved.
Elapsed time: 0.040 sec

2. 分布式查询

• 未完成

定义

• 分布式查询时指在一个查询语句中同时访问多个数据库中的数据
• 跨实例分布式查询：在一个查询语句中同时访问不在同一实例数据库中的数据
• 访问远程的数据库对象时，应该指定相应的数据库服务器名和数据库名，语法格式如下：

• • databasename@servername:[owner.]objectname
  • objectname可以是表名，视图名或同义语等

--一个实例，两个数据库

> select * from test1@gbasedb2:t1 as k;
         id name
         12 su
          1 fu
2 row(s) retrieved.

--一个服务器两个实例

3. select的外连接

select * from a,outer b where a.id=b.id;

(二) select的进阶写法

1. select和case

select * from t_v2;


         id         age name

          3          19 yuan
          2          20 liu
          1          18 su
          4          16 yi
          5          16 li
          6          22 li

6 row(s) retrieved.

> select case when name='li' then 'not know' when name='su' then 's' when name='liu' then 'erdeng' else 'weizhi' end from t_v2;


(expression)

weizhi
erdeng
s
weizhi
not know
not know

6 row(s) retrieved.

2. 窗口函数

select ID,row_number() over(partition by id) from t1;
select id,rank() over(partition by id order by name) from t1;

3. with as写法

WITH q1 AS (
  select sum(id) as n from t1
)
, q2 AS (
  select first 1 id as m from t2 order by id desc
)
SELECT q1.n/q2.m AS  result
FROM q1,q2

五、函数调用

(一) select对函数的使用

1. 生成随机数在gbase和oracle模式下的使用

gbase下没有生成随机数的函数需要编写，在过程语言中有相关生成sql

数据库自定义内容

oracle模式下

#set environment sqlmode 'oracle';
> select DBMS_RANDOM.value(1,10)::int from t1;


(EXPRESSION)

           4

1 row(s) retrieved.

> select * from t1 where id::int = (select DBMS_RANDOM.value(1,10)::int from dual);


ID         ID1

2          fdsf

1 row(s) retrieved.

2. select对时间日期的计算

1. 一天后的时间
   select sysdate + numtodsinterval(1, 'DAY') from dual;
   
2. 一小时前的时间
   select sysdate + numtodsinterval(-1, 'HOUR') from dual;   

select to_date('2013-2-28 12:32:40', 'yyyy-mm-dd hh:mi:ss') init_day,
 
to_date('2013-2-28 12:32:40', 'yyyy-mm-dd hh:mi:ss') +
 
numtodsinterval(31, 'day') as add_day,
 
to_date('2013-2-28 12:32:40', 'yyyy-mm-dd hh:mi:ss') +
 
numtodsinterval(31, 'hour') as add_hour,
 
to_date('2013-2-28 12:32:40', 'yyyy-mm-dd hh:mi:ss') +
 
numtodsinterval(31, 'minute') as add_minute,
 
to_date('2013-2-28 12:32:40', 'yyyy-mm-dd hh:mi:ss') +
 
numtodsinterval(31, 'second') as add_second
 
from dual;

六、其他sql

(一) 数据导出导入

1. unload数据导出

unload to fileName.txt select * from tableName

unload to ‘fileName.dmp’ select * from tableName;

unload to /opt/informix/fileName.csv delimiter "," 
select * from tableName;

2. load加载

> unload to a.txt select * from tt;

4 row(s) unloaded.

> load from a.txt  insert into tt(id,name,age);

4 row(s) loaded.

七、快捷键的使用

(一) 查询的快捷命令

1. 查询所有表

su - gbasedbt
dbaccess test -
info tables;

2. 查看单表所有的列

su - gbasedbt
dbaccess test -
info columns for tabname

3. 查看索引

su - gbasedbt
dbaccess test -
info indexes for p2;