DB2 SQL语法系列（一）

一、创建数据库

1、创建一个名为“test”的数据库

db2 “create database test automatic storage yes on ‘/home/db2inst1/dbdata’ dbpath on ‘/home/db2inst1/dbctl’using codeset utf-8 territory cn collate using system”

分析：

collate using < system | identity | compatibility >，指定数据库的字符串整理排序类型，默认参数为system，即以系统代码集来排序；identity参数指以十六进制来排序；compatibility参数指使用DB2版本2顺序来排序。

创建数据库时，需要事先创建好相应的文件目录。

 

 

二、创建缓冲池以及表空间

1、 创建缓冲池

db2 “create bufferpool bp32k size 1000 pagesize 32k”

分析：

该缓冲池总大小为320M。size表示页数，pagesize表示页数大小，size*pagesize就是缓冲池的内存大小。

Bufferpool的pagesize大小必须与表空间的pagesize大小一致，否则无法创建。

 

2、 创建表空间

（1）创建一个数据库管理的表空间

db2 “create tablespace tbs_data pagesize 32k managed by database using (file ‘/data/tbs_data/data1’100M，file ‘/data/tbs_data/data2’100M ) extentsize 32 prefetchsize automatic bufferpool bp32k no file system caching”

# file system caching，默认为no（关闭），即关闭文件系统缓存。但应该将大对象数据创建在独立的表空间上，并使用file system caching选项。

#块大小为32。

#自动预取

 

（2）创建一个系统管理的系统临时表空间

db2 “create system temporary tablespace tbs_temp pagesize 32k managed by system using (‘/data/tbs_temp’) bufferpool bp32k”

 

（3）创建一个系统管理的用户临时表空间

db2 “create user temporary tablespace tbs_user_temp pagesize 32k managed by system using (‘/data/tbs_user_temp’) bufferpool bp32k”

 

（4）创建一个自动存储管理的索引表空间

db2 “create tablespace tbs_index pagesize 32k bufferpool bp32k”

 

（5）创建一个自动存储管理的数据表空间

db2 “create tablespace tbs_data2 initialsize 100M increasesize 100M maxsize 50G”

分析：

DMS支持的容器类型是文件和裸设备（raw device），对于数据表空间来说，建议使用DMS管理。

SMS支持的容器类型只能是目录，对于临时表空间来说，建议使用SMS管理。

如何确定表空间是否采用了自动存储管理呢，则使用：db2 get snapshot for tablespaces on db_name | more

 

 

三、创建表以及表操作

1、表的数据类型

数字型

整型：smallint、int、bigint

小数：decimal、num

decimal（p，s），p是数据的总位数，必须小于32，s是小数点后面的位数。

 

字符串型

单字节：char、varchar、clob

#clob存储字符类数据，如文本内容等，当字符字段长度超过32kb时，才考虑用clob。否则用varchar（32k）

双字节：dbclob

二进制型：blob

#blob存储二进制类数据，如音频等，当字符字段长度超过32kb时，才考虑用blob。否则用varchar（32k）for bit data。

 

时间型：data、time、timestamp

XML型：xml

 

2、 创建表

（1）创建一张普通的user表

$ create table user
( user_id int not null primary key，
user_name char(20) not null，
user_sex char(4) not null，
user_address char(30) not null，
user_photo blob (1M)，
constraint user_check check (user_sex=‘M’or user_sex=‘F’)
enforced
)
in tbs_data
index in tbs_index
long in tbs_long

说明：

In指定数据表空间。

Index in指定索引存储的表空间。

Long in 指定大对象数据存储的表空间。

注：使用表时，可在前面加上数据库名，如sample.user。同理，表和列亦是。

 

（2）创建多维集群（MDC）表

多维集群允许物理地同时在多个键或维上将表集群。每个指定的维可以用一个或多个列来定义。对于每个指定的维，会自动创建维块索引。每个块页是磁盘上的一组连续的页。使用organize by参数指定维（列）。

$ create table user_mdc
( year int,
Nation char(25),
Colour varchar(10),)
Organize by(year,nation,colour)

 

（3）表分区

数据库分区可以使数据存放在不同的服务器上；而表分区允许将单个表扩展到多个表空间。

例如，窗机创建一张分区表用于将24个月的数据存储在4个表空间的24个分区中：

$ db2 create table tb1
(tb1_id char(7)，
tb1_data data，
) in tbs1,tbs2,tbs3,tbs4
Partition by range(tb1_data)
(starting from(‘2005-01-01’)
ending (‘2006-12-31’)
every1month)

 

3、插入表数据

（1）使用insert语句插入表数据

该语句有三种形式，分别是inser values语句（可向表中插入一行或多行数据）、insert set语句（指定插入行中列的值，也可只指定部分列的值）、insert select语句（向表中插入其他表的数据）。

用法举列

显示数据库test中表user的表结构：

$ db2 describe table test.user

 

1）inser values语句：

插入一行数据：

$ db2 insert into user
values（1000，‘xiao xu’，‘M’，‘cheng du’，‘1757794282’）

插入多行数据：

$ db2 insert into user（user_id，user_name，user_address）
values（1000，‘xiao xu’，‘cheng du’），
     （1001，‘ren yaun’，‘mei shan’）

 

2）insert set语句：

$ db2 insert into user
set user_name=‘renyuan’，user_address=‘meishan’，user_sex=‘default’

 

3）insert select语句：

首先创建一个与user表结构相同的custome表

$ db2 create table custome like user

再向custome表批量插入数据

$ db2 insert into custome
select * from user

 

（2）使用replace语句插入表数据

使用replace语句在插入数据之前，将表中与待插入的新记录相冲突的旧记录删除，从而保证新记录能够正常插入。

例如，将user表中原有的一条记录（1000，xiaoxu，chngdu），被新记录（1000，xiao wang，wu han）替换。

$ db2 replace into user（user_id，user_name，user_address）
values（1000，‘xiao wang’，‘wu han’）

 

 

4、 使用delete语句删除表数据

delete语句仅用于删除表中的一行或多行数据。

（1）从单个表中删除数据：

使用delete语句删除user表中用户名为“xiao wang“的信息。

$ db2 delete from user where user_name=‘xiao wang’

 

（2）从多个表中删除数据：

例如，数据库中有三个表tb1、tb2、tb3，它们均有id列，现要求删除表tb1等于tb2和tb3中id值的所有行。

$ db2 delete tb1，tb2 from tb1，tb2，tb3
where tb1.id=tb2.id and tb2.id=tb3.id

 

5、使用update语句更新、修改表数据

（1）修改单个表：

将user表中用户名为“xiaowang“的地址信息更新为”chong qing“。

$ db2 update user set user_address=‘chong qing’
where user_name=‘xiao wang’

 

（2）修改多个表：

在tb1和tb2表中id值相同时，将表tb1中name列的值修改为“li ming“，tb2中name列的值修改为”xiao ming“。

$ db2 update tb1，tb2
set tb1.name=‘li ming’，tb2.name=‘xiao ming’
where tb1.id=tb2.id

 

6、表的基本管理

（1）表压缩以及重组

$ db2 alter table db2inst1.tab_name compress yes

$ db2 reorg table db2inst1.tab_name

表压缩特性在数据量大的数据仓库或经营分析系统中应用更广泛。数据量一般的情况下，则不建议使用。

 

（2）DB2的系统视图

Syscat：系统视图

Sysibmadm：系统管理视图

Sysstat：统计视图

如果视图的查询效率低，可以做成物化视图（MQT），相当于把视图中SQL语句的查询结果以一个物理表的形式存储起来，在数据仓库或分析类系统中，MQT是解决性能问题的利器。

 

（3）自增序列

在数据库级别，DB2提供了如下三种方法解决字段值唯一性的问题：

Genearate_unique函数

Identity标示字段

Sequence对象

 

①Genearate_unique函数用法：

创建一张表：

create table custome
（cust_no char(10) for bit data,
cust_name varchar(16)
）

插入数据：

insert into custome values(genearate_unique( ),’zhang san’)，(genearate_unique( ),’li si’)

 

②Identity标识字段用法：

create table custome
（cust_no int not null generated by default as identity(
 start with 500,     --自增主键的起始值
increment by 1,        --每次主键生成的增量
Minvalue 500,         --最小值
Maxvalue 10000000,    --最大值
No cycle,             --是否可循环
No cache,             --是否缓存
No order
),
Cust_name varchar(16)
)

 

插入数据：

insert into custome(cust_name) values(‘xiao xu’)
insert into custome(cust_no，cust_name) values(100，‘ren yuan’)

 

③sequence对象用法

Sequence是一个数据库中的对象，作用于整个数据库，和表没有任何关系。而identity指定于表中的某一列，作用范围就是这个表。

首先创建一个sequence：

$ db2 create sequence my_seq
Start with 1       --序列从1开始
Increment by 1    --序列增量为1
Cache 100        --缓存值

 

创建一张表：

$ db2 create table t1 (id int，name char(20))

 

插入数据：

$ db2 insert into t1
values( nextval for my_seq，‘xiao xu‘)，
     (nextval for my_seq，‘ren yuan‘)

 

查询表数据：

$ db2 select * from t1

 

（4）内联LOB（inline lob）

Inline lob减少了I/O，并且可以利用bufferpool缓存，提高了查询lob数据的速度。同时，inline lob也能使用数据压缩特性，减少数据存储空间。

如果应用程序对非lob列的查询性能要求很高，或对lob列的访问很少，则慎重使用该特性。

Inline lob特性是通过create table字句的inline length选项或alter table语句来指定。如果通过alter table语句来更改，需要对表做reorg。

 

用法举例

创建一张t2表

$ db2 create table t2
( cust_no char(10) ,
cust_name char(20),
cust_ume clob(20000) inline length 3500,
cust_age int
)

 

插入表数据

$ db2 insert into t2
values (‘1000’,’xiao xu’,’aaaaaaaaaaaaaaaaaaaaaaaa’,20)

 

使用表函数admin_est_inline_length 辅助估算 inline length的长度

$ db2 select max(admin_est_inline_length(cust_ume)) as max_inline_length from t2

 

根据其查询出来的值修改inline length长度

$ db2 alter table t2 alter column cust_ume set inline length 30

 

重组表

$ db2 reorg table t2 longlobdata

Longlobdata参数只对long和lob数据类型的列有效。

 

查询相关信息

$ db2 select substr(tabname,1,18) as table,substr(colname,1,20) as column,inline_length from syscat.columns where tabname=’T2’

使用db2dart/dd选项观察inline lob的存储方式

 

（5）表的基本维护

设置列的默认值

$ db2 alter table t2 alter column cust_no set default ‘20’

 

将cust_no列的数据类型修改为int

 $ db2 alter table t2 alter column cust_no set data type int

 

删除t2表中cust_no列的not null属性

$ db2 alter table t2 alter column cust_no drop not null

 

使用db2look命令获取创建表的DDL信息

$ db2look –d db_name –e  -t  tab_name