首页 > 代码库 > DB2 SQL语法系列（一）
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_dataindex in tbs_indexlong 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,tbs4Partition 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 uservalues（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 userset user_name=‘renyuan’，user_address=‘meishan’，user_sex=‘default’
3）insert select语句：
首先创建一个与user表结构相同的custome表
$ db2 create table custome like user
再向custome表批量插入数据
$ db2 insert into customeselect * 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，tb3where 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，tb2set 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_seqStart with 1 --序列从1开始Increment by 1 --序列增量为1Cache 100 --缓存值
创建一张表：
$ db2 create table t1 (id int，name char(20))
插入数据：
$ db2 insert into t1values( 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 t2values (‘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 longlobdataLonglobdata参数只对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
DB2 SQL语法系列（一）