db2的语法

常用SQL

基本语法

select

count(brlvl) as cbrlvl,

sum(brlvl) as sumbrlvl,

avg(brlvl) as avgbrlvl,

min(brlvl) as minbrlvl,

max(brlvl) as maxbrlvl,

case

    when max(brlvl) = 0 then '总行'

    when max(brlvl) = 1 then '支行'

else '网点'

  end as brlvlname,

  brno

from z_orginf

where brno is not null

常用SQL

基本语法

select

count(brlvl) as cbrlvl,

sum(brlvl) as sumbrlvl,

avg(brlvl) as avgbrlvl,

min(brlvl) as minbrlvl,

max(brlvl) as maxbrlvl,

case

    when max(brlvl) = 0 then '总行'

    when max(brlvl) = 1 then '支行'

else '网点'

  end as brlvlname,

  brno

from z_orginf

where brno is not null

group by brlvl,brno

having max(brlvl)<5 

order by brlvl

fetch first 10 rows only   --取前10条记录

注：havingmax(brlvl)<5 不能写成 havingmaxbrlvl<5 会报错

分页

select * from 

( 

  select 

  row_number() over(order by hbrno asc,brno desc) as rownum,

    z_orginf.* 

  from z_orginf

) z_orginf

where rownum >= 1 and rownum <=10 

注：序号rownum从1开始;不排序可写成:row_number() over() as rownum;如果要保证序号的排序顺序必须要在over()定义排序，如果直接在where条件后加入order by排序那么分页的序号有混乱的情况出现

表关联

--左/右关联 :left [outer] join / right [outer]join：关联不上以左/右边数据为准

select ori.*, usi.* from z_orginf ori 

left join z_userinf usi on usi.brno = ori.brno

where ori.brlvl = 0

select ori.*, usi.* from z_userinf usi

right join z_orginf ori on ori.brno = usi.brno 

where ori.brlvl = 0

--内关联:inner [outer]join ：与左/又关联不同，内关联如果关联不上则没数据

select ori.*, usi.* from z_orginf ori

inner join z_userinf usi on usi.brno = ori.brno

where ori.brlvl = 0

select ori.*, usi.* from z_orginf ori,z_userinf usi

where usi.brno = ori.brno and ori.brlvl = 0

--全关联:full [outer]join:即左右关联的总和（去掉重复）

select ori.*, usi.* from z_orginf ori

full join z_userinf usi on usi.brno = ori.brno

where ori.brlvl = 0 

建表

注意：在一张表中只能对一个字段设置自增类型,并且自增类型必须为int、integer、smallint

create table tb1 (

id integernotnull,

  fname varchar(20) notnull,

  t_id1 int check (t_id1>0),

  t_id2 int check (t_id2>0),

primary key(id,fname) --不知道是否通用在10.1（Express-C版）的db2中是ok的

)

注：primary key(id,fname) 9.7（试用版）失败了，并且在9.7的（试用版）中控制了表空间的大小，如果对字段长度定义很长的话，建表失败

---

--安规则自增主键

create table tb1(

id varchar(30) primary key not null,  --主键（必须不能为空:not null）

--不可以自定义(id)值

id1 int generated alwaysas identity (start with 0,increment by 1),

  fname varchar(6) not null default 'xxx'

)

注意：

在英文编码（819）中varchar(6)/char(6)只能保存2个中文，如果插入3个中文的字符串就会报错，并且截取一个中文必须截取3个字符：substr(fname,1,3);而对于UTF-8 Unicode（1208）和GBK（1386）编码的数据库可以插入6个中文，当然使用：substr(fname,1,3)查出3个中文

---

--db2主要数据类型

create table tb1(

--不可以自定义(id)的主键

  id integer not null

generated always as identity (start with 0,increment by 1),

  fname varchar(20),

  f1 decimal(20,2),

  f2 date,

  f3 time,

  f4 timestamp default current_timestamp not null,    --日期时间（旧版本的db2不支持）

  f5 blob,       --二进制字符串 ,可保存图片和文件

  f6 smallint,

  f7 integer,

  f8 real,       --单精度浮点数

  f9 double,     --双精度浮点数

  f10 char(10),   

f11 varchar(10),   

constraint pk_tbname1 primary key (id)    

)

---

--可安规则自增或自定义字段值

create table tb1(

  --可自定义(id)值的主键

id integer primary key notnull 

generated by defaultas identity (start with 0,increment by 1),

  fname varchar(20)

)

--不建议使用，测试发现插入数据的时候下面的标红语句报错（在定义唯一约束或主键约束后）原因是--开始使用自定义的值插入数据后再使用规则自增，主键会从0开始计算，而使用自定义插入数据后，自增的起始值并不会改变因此很容易造成主键重复数据无法插入的情况

insert into tb1 (id,fname) values (0,'AA');

insert into tb1 (id,fname) values (1,'BB');

insert into tb1 (fname) values ('A');    --报错（主键重复）

insert into tb1 (fname) values ('B');    --报错（主键重复）

insert into tb1 (fname) values ('C');

insert into tb1 (fname) values ('D');

insert into tb1 (id,fname) values (4,'CC');

insert into tb1 (fname) values ('E');    --报错（主键重复）

insert into tb1 (fname) values ('F');

---

create table tb2 like tb1          --复制表结构，不复制数据(包括主键和索引)

insert into tb2 select * from tb1--复制数据

修改字段定义

--修改字段定义

alter table z_rolectrl alter column menuctrls set data type varchar(512);

alter table [tbname] add column [columnname] integer default 0;

alter table [tbname] drop column [columnname];

修改表约束

--主键约束(注意定义的字段必须not null)

alter table tb_main add constraint 约束名 primary key (id)

--外键约束：定义tb_sub表中的main_id字段为外键指向tb_main的主键（外键约束也叫引用约束）

alter table tb_sub add constraint 约束名 foreign key (main_id) references tb_main

--如果表tb_main没有定义主键那么就要指定外键关联的字段（不知道是否要唯一没测）

alter table tb_sub add constraint 约束名 foreign key (main_id) references tb_main(id)

--唯一约束（建表时id字段必须定义为：not null）

--如果建表的时候没有定义not null,即使后续添加null检查后也不能添加唯一约束 

--alter table tb_main add constraint 约束名 check (id is not null); 

alter table tb_main add constraint 约束名unique(id);

--检查约束（插入的main_id不为null并且必须大于0）

alter table tb_sub add constraint 约束名 check (main_id is not null)

alter table tb_sub add constraint 约束名 check (main_id>0)

--约束的删除

alter table tb_sub drop constraint 约束名;   --通用

alter table tb_sub drop primary key         --适用于主键删除

索引

注意：主键索引必须不为null并且是唯一索引

创建：create [unique] index ix_tbname on dbname.tbname (id,id2 desc)

删除: drop index ix_tbname

常用命令

查看当前数据库设置

db2set -all

连接数据库db2 connect to odsdb user db2admin using db2
不写的话，默认使用当前用户

显示当前数据库连接

db2 list applications

断掉当前数据库所有连接

db2 force applications all 

断开数据库连接

db2 connect reset--字面意思是“连接重置”;

db2 disconnect current --字面意思是“断开当前连接”;

quit ：退出交互模式

补充：db2 terminate    --断开与数据库的连接

导入存储过程

db2 -td@ -vf [存储过程文件名]

数据的导入导出

db2 "export to xyydemo.del of del select * from mbdemo"

db2 "load from xyydemo.del of del insert into mbdemo"   --不记日志（要权限）

db2 "import from xyydemo.del of del insert into mbdemo"

--不同编码的db2数据库数据导入和导出会出现乱码，可以定义导出导入的数据编码类型

db2 "export to xyydemo.del of del modified by codepage=1208 select * from mbdemo"

db2 "import from xyydemo.del of del modified by codepage=1208 insert into mbdemo"

查询插入数据

insert into tb1 values ('1','2','3')   --全插入

insert into tb2 select * from tb1

insert into tb2 (f1,f3) select f1,f3 from tb1

如果需要对组函数的结果作为条件，那么不能使用where子句，必须使用having子句。

group by brlvl,brno

having max(brlvl)<5 

order by brlvl

fetch first 10 rows only   --取前10条记录

注：havingmax(brlvl)<5 不能写成 havingmaxbrlvl<5 会报错

分页

select * from 

( 

  select 

  row_number() over(order by hbrno asc,brno desc) as rownum,

    z_orginf.* 

  from z_orginf

) z_orginf

where rownum >= 1 and rownum <=10 

注：序号rownum从1开始;不排序可写成:row_number() over() as rownum;如果要保证序号的排序顺序必须要在over()定义排序，如果直接在where条件后加入order by排序那么分页的序号有混乱的情况出现

表关联

--左/右关联 :left [outer] join / right [outer]join：关联不上以左/右边数据为准

select ori.*, usi.* from z_orginf ori 

left join z_userinf usi on usi.brno = ori.brno

where ori.brlvl = 0

select ori.*, usi.* from z_userinf usi

right join z_orginf ori on ori.brno = usi.brno 

where ori.brlvl = 0

--内关联:inner [outer]join ：与左/又关联不同，内关联如果关联不上则没数据

select ori.*, usi.* from z_orginf ori

inner join z_userinf usi on usi.brno = ori.brno

where ori.brlvl = 0

select ori.*, usi.* from z_orginf ori,z_userinf usi

where usi.brno = ori.brno and ori.brlvl = 0

--全关联:full [outer]join:即左右关联的总和（去掉重复）

select ori.*, usi.* from z_orginf ori

full join z_userinf usi on usi.brno = ori.brno

where ori.brlvl = 0 

建表

注意：在一张表中只能对一个字段设置自增类型,并且自增类型必须为int、integer、smallint

create table tb1 (

id integernotnull,

  fname varchar(20) notnull,

  t_id1 int check (t_id1>0),

  t_id2 int check (t_id2>0),

primary key(id,fname) --不知道是否通用在10.1（Express-C版）的db2中是ok的

)

注：primary key(id,fname) 9.7（试用版）失败了，并且在9.7的（试用版）中控制了表空间的大小，如果对字段长度定义很长的话，建表失败

---

--安规则自增主键

create table tb1(

id varchar(30) primary key not null,  --主键（必须不能为空:not null）

--不可以自定义(id)值

id1 int generated alwaysas identity (start with 0,increment by 1),

  fname varchar(6) not null default 'xxx'

)

注意：

在英文编码（819）中varchar(6)/char(6)只能保存2个中文，如果插入3个中文的字符串就会报错，并且截取一个中文必须截取3个字符：substr(fname,1,3);而对于UTF-8 Unicode（1208）和GBK（1386）编码的数据库可以插入6个中文，当然使用：substr(fname,1,3)查出3个中文

---

--db2主要数据类型

create table tb1(

--不可以自定义(id)的主键

  id integer not null

generated always as identity (start with 0,increment by 1),

  fname varchar(20),

  f1 decimal(20,2),

  f2 date,

  f3 time,

  f4 timestamp default current_timestamp not null,    --日期时间（旧版本的db2不支持）

  f5 blob,       --二进制字符串 ,可保存图片和文件

  f6 smallint,

  f7 integer,

  f8 real,       --单精度浮点数

  f9 double,     --双精度浮点数

  f10 char(10),   

f11 varchar(10),   

constraint pk_tbname1 primary key (id)    

)

---

--可安规则自增或自定义字段值

create table tb1(

  --可自定义(id)值的主键

id integer primary key notnull 

generated by defaultas identity (start with 0,increment by 1),

  fname varchar(20)

)

--不建议使用，测试发现插入数据的时候下面的标红语句报错（在定义唯一约束或主键约束后）原因是--开始使用自定义的值插入数据后再使用规则自增，主键会从0开始计算，而使用自定义插入数据后，自增的起始值并不会改变因此很容易造成主键重复数据无法插入的情况

insert into tb1 (id,fname) values (0,'AA');

insert into tb1 (id,fname) values (1,'BB');

insert into tb1 (fname) values ('A');    --报错（主键重复）

insert into tb1 (fname) values ('B');    --报错（主键重复）

insert into tb1 (fname) values ('C');

insert into tb1 (fname) values ('D');

insert into tb1 (id,fname) values (4,'CC');

insert into tb1 (fname) values ('E');    --报错（主键重复）

insert into tb1 (fname) values ('F');

---

create table tb2 like tb1          --复制表结构，不复制数据(包括主键和索引)

insert into tb2 select * from tb1--复制数据

修改字段定义

--修改字段定义

alter table z_rolectrl alter column menuctrls set data type varchar(512);

alter table [tbname] add column [columnname] integer default 0;

alter table [tbname] drop column [columnname];

修改表约束

--主键约束(注意定义的字段必须not null)

alter table tb_main add constraint 约束名 primary key (id)

--外键约束：定义tb_sub表中的main_id字段为外键指向tb_main的主键（外键约束也叫引用约束）

alter table tb_sub add constraint 约束名 foreign key (main_id) references tb_main

--如果表tb_main没有定义主键那么就要指定外键关联的字段（不知道是否要唯一没测）

alter table tb_sub add constraint 约束名 foreign key (main_id) references tb_main(id)

--唯一约束（建表时id字段必须定义为：not null）

--如果建表的时候没有定义not null,即使后续添加null检查后也不能添加唯一约束 

--alter table tb_main add constraint 约束名 check (id is not null); 

alter table tb_main add constraint 约束名unique(id);

--检查约束（插入的main_id不为null并且必须大于0）

alter table tb_sub add constraint 约束名 check (main_id is not null)

alter table tb_sub add constraint 约束名 check (main_id>0)

--约束的删除

alter table tb_sub drop constraint 约束名;   --通用

alter table tb_sub drop primary key         --适用于主键删除

索引

注意：主键索引必须不为null并且是唯一索引

创建：create [unique] index ix_tbname on dbname.tbname (id,id2 desc)

删除: drop index ix_tbname

常用命令

查看当前数据库设置

db2set -all

连接数据库db2 connect to odsdb user db2admin using db2
不写的话，默认使用当前用户

显示当前数据库连接

db2 list applications

断掉当前数据库所有连接

db2 force applications all 

断开数据库连接

db2 connect reset--字面意思是“连接重置”;

db2 disconnect current --字面意思是“断开当前连接”;

quit ：退出交互模式

补充：db2 terminate    --断开与数据库的连接

导入存储过程

db2 -td@ -vf [存储过程文件名]

数据的导入导出

db2 "export to xyydemo.del of del select * from mbdemo"

db2 "load from xyydemo.del of del insert into mbdemo"   --不记日志（要权限）

db2 "import from xyydemo.del of del insert into mbdemo"

--不同编码的db2数据库数据导入和导出会出现乱码，可以定义导出导入的数据编码类型

db2 "export to xyydemo.del of del modified by codepage=1208 select * from mbdemo"

db2 "import from xyydemo.del of del modified by codepage=1208 insert into mbdemo"

查询插入数据

insert into tb1 values ('1','2','3')   --全插入

insert into tb2 select * from tb1

insert into tb2 (f1,f3) select f1,f3 from tb1

常用SQL

基本语法

select

count(brlvl) as cbrlvl,

sum(brlvl) as sumbrlvl,

avg(brlvl) as avgbrlvl,

min(brlvl) as minbrlvl,

max(brlvl) as maxbrlvl,

case

    when max(brlvl) = 0 then '总行'

    when max(brlvl) = 1 then '支行'

else '网点'

  end as brlvlname,

  brno

from z_orginf

where brno is not null

group by brlvl,brno

having max(brlvl)<5 

order by brlvl

fetch first 10 rows only   --取前10条记录

注：havingmax(brlvl)<5 不能写成 havingmaxbrlvl<5 会报错

分页

select * from 

( 

  select 

  row_number() over(order by hbrno asc,brno desc) as rownum,

    z_orginf.* 

  from z_orginf

) z_orginf

where rownum >= 1 and rownum <=10 

注：序号rownum从1开始;不排序可写成:row_number() over() as rownum;如果要保证序号的排序顺序必须要在over()定义排序，如果直接在where条件后加入order by排序那么分页的序号有混乱的情况出现

表关联

--左/右关联 :left [outer] join / right [outer]join：关联不上以左/右边数据为准

select ori.*, usi.* from z_orginf ori 

left join z_userinf usi on usi.brno = ori.brno

where ori.brlvl = 0

select ori.*, usi.* from z_userinf usi

right join z_orginf ori on ori.brno = usi.brno 

where ori.brlvl = 0

--内关联:inner [outer]join ：与左/又关联不同，内关联如果关联不上则没数据

select ori.*, usi.* from z_orginf ori

inner join z_userinf usi on usi.brno = ori.brno

where ori.brlvl = 0

select ori.*, usi.* from z_orginf ori,z_userinf usi

where usi.brno = ori.brno and ori.brlvl = 0

--全关联:full [outer]join:即左右关联的总和（去掉重复）

select ori.*, usi.* from z_orginf ori

full join z_userinf usi on usi.brno = ori.brno

where ori.brlvl = 0 

建表

注意：在一张表中只能对一个字段设置自增类型,并且自增类型必须为int、integer、smallint

create table tb1 (

id integernotnull,

  fname varchar(20) notnull,

  t_id1 int check (t_id1>0),

  t_id2 int check (t_id2>0),

primary key(id,fname) --不知道是否通用在10.1（Express-C版）的db2中是ok的

)

注：primary key(id,fname) 9.7（试用版）失败了，并且在9.7的（试用版）中控制了表空间的大小，如果对字段长度定义很长的话，建表失败

---

--安规则自增主键

create table tb1(

id varchar(30) primary key not null,  --主键（必须不能为空:not null）

--不可以自定义(id)值

id1 int generated alwaysas identity (start with 0,increment by 1),

  fname varchar(6) not null default 'xxx'

)

注意：

在英文编码（819）中varchar(6)/char(6)只能保存2个中文，如果插入3个中文的字符串就会报错，并且截取一个中文必须截取3个字符：substr(fname,1,3);而对于UTF-8 Unicode（1208）和GBK（1386）编码的数据库可以插入6个中文，当然使用：substr(fname,1,3)查出3个中文

---

--db2主要数据类型

create table tb1(

--不可以自定义(id)的主键

  id integer not null

generated always as identity (start with 0,increment by 1),

  fname varchar(20),

  f1 decimal(20,2),

  f2 date,

  f3 time,

  f4 timestamp default current_timestamp not null,    --日期时间（旧版本的db2不支持）

  f5 blob,       --二进制字符串 ,可保存图片和文件

  f6 smallint,

  f7 integer,

  f8 real,       --单精度浮点数

  f9 double,     --双精度浮点数

  f10 char(10),   

f11 varchar(10),   

constraint pk_tbname1 primary key (id)    

)

---

--可安规则自增或自定义字段值

create table tb1(

  --可自定义(id)值的主键

id integer primary key notnull 

generated by defaultas identity (start with 0,increment by 1),

  fname varchar(20)

)

--不建议使用，测试发现插入数据的时候下面的标红语句报错（在定义唯一约束或主键约束后）原因是--开始使用自定义的值插入数据后再使用规则自增，主键会从0开始计算，而使用自定义插入数据后，自增的起始值并不会改变因此很容易造成主键重复数据无法插入的情况

insert into tb1 (id,fname) values (0,'AA');

insert into tb1 (id,fname) values (1,'BB');

insert into tb1 (fname) values ('A');    --报错（主键重复）

insert into tb1 (fname) values ('B');    --报错（主键重复）

insert into tb1 (fname) values ('C');

insert into tb1 (fname) values ('D');

insert into tb1 (id,fname) values (4,'CC');

insert into tb1 (fname) values ('E');    --报错（主键重复）

insert into tb1 (fname) values ('F');

---

create table tb2 like tb1          --复制表结构，不复制数据(包括主键和索引)

insert into tb2 select * from tb1--复制数据

修改字段定义

--修改字段定义

alter table z_rolectrl alter column menuctrls set data type varchar(512);

alter table [tbname] add column [columnname] integer default 0;

alter table [tbname] drop column [columnname];

修改表约束

--主键约束(注意定义的字段必须not null)

alter table tb_main add constraint 约束名 primary key (id)

--外键约束：定义tb_sub表中的main_id字段为外键指向tb_main的主键（外键约束也叫引用约束）

alter table tb_sub add constraint 约束名 foreign key (main_id) references tb_main

--如果表tb_main没有定义主键那么就要指定外键关联的字段（不知道是否要唯一没测）

alter table tb_sub add constraint 约束名 foreign key (main_id) references tb_main(id)

--唯一约束（建表时id字段必须定义为：not null）

--如果建表的时候没有定义not null,即使后续添加null检查后也不能添加唯一约束 

--alter table tb_main add constraint 约束名 check (id is not null); 

alter table tb_main add constraint 约束名unique(id);

--检查约束（插入的main_id不为null并且必须大于0）

alter table tb_sub add constraint 约束名 check (main_id is not null)

alter table tb_sub add constraint 约束名 check (main_id>0)

--约束的删除

alter table tb_sub drop constraint 约束名;   --通用

alter table tb_sub drop primary key         --适用于主键删除

索引

注意：主键索引必须不为null并且是唯一索引

创建：create [unique] index ix_tbname on dbname.tbname (id,id2 desc)

删除: drop index ix_tbname

常用命令

查看当前数据库设置

db2set -all

连接数据库db2 connect to odsdb user db2admin using db2
不写的话，默认使用当前用户

显示当前数据库连接

db2 list applications

断掉当前数据库所有连接

db2 force applications all 

断开数据库连接

db2 connect reset--字面意思是“连接重置”;

db2 disconnect current --字面意思是“断开当前连接”;

quit ：退出交互模式

补充：db2 terminate    --断开与数据库的连接

导入存储过程

db2 -td@ -vf [存储过程文件名]

数据的导入导出

db2 "export to xyydemo.del of del select * from mbdemo"

db2 "load from xyydemo.del of del insert into mbdemo"   --不记日志（要权限）

db2 "import from xyydemo.del of del insert into mbdemo"

--不同编码的db2数据库数据导入和导出会出现乱码，可以定义导出导入的数据编码类型

db2 "export to xyydemo.del of del modified by codepage=1208 select * from mbdemo"

db2 "import from xyydemo.del of del modified by codepage=1208 insert into mbdemo"

查询插入数据

insert into tb1 values ('1','2','3')   --全插入

insert into tb2 select * from tb1

insert into tb2 (f1,f3) select f1,f3 from tb1