DB2基础语法与简单使用

1、启动

[root@MyClone hadoop]# su db2inst1

[db2inst1@MyClone hadoop]$ db2start

2、创建数据库

[db2inst1@MyClone hadoop]$ db2 create database hiber;

3、 列出数据库

[db2inst1@MyClone hadoop]$ db2 list db directory

4、连接数据库

[db2inst1@MyClone hadoop]$ db2 connect to hiber;

5、列出表

[db2inst1@MyClone hadoop]$ db2 list tables;

6、创建表

[db2inst1@MyClone hadoop]$ db2 "create table tb_user(id integer primary key, user_name varchar(10) not null, user_age integer not null)"

 在db2上，primary key的字段一定要指明not null才行：所以上述语句报出错误 可以如下创建表

[db2inst1@MyClone hadoop]$ db2 "create table tb_user(id integer not null primary key, user_name varchar(10) not null, user_age integer not null)"

 7、执行代码块

[db2inst1@MyClone hadoop]$ db2 -td@

#db2 -td@表示命令以@结束

db2 =>  begin atomic
db2 (cont.) => declare v_id int;
db2 (cont.) => declare v_name varchar(10);
db2 (cont.) => declare v_age int;
db2 (cont.) => set v_id=1;
db2 (cont.) => while (v_id<=100) do
db2 (cont.) => insert into tb_user values(v_id, 'user_' || v_id, v_id);
db2 (cont.) => set v_id=v_id+1;
db2 (cont.) => end while;
db2 (cont.) => end @

8、取得前N条数据 

 

db2 => select * from tb_user fetch first 10 rows only@

9、清空表truncate

 

db2 => truncate table tb_user immediate@

10、存储过程

创建

db2 => create procedure pro_insert_user(in row_count integer)
db2 (cont.) => begin 
db2 (cont.) => declare v_id int;
db2 (cont.) =>  declare v_name varchar(10);
db2 (cont.) => declare v_age int;
db2 (cont.) => set v_id=1;
db2 (cont.) => while (v_id<=row_count) do
db2 (cont.) =>  insert into tb_user values(v_id, 'user_' || v_id, v_id);
db2 (cont.) => set v_id=v_id+1;
db2 (cont.) => end while;
db2 (cont.) => end @
DB20000I  The SQL command completed successfully.

调用

db2 => call pro_insert_user(10)@

  Return Status = 0

11、控制语句 if else

db2 => create procedure pro_insert_user(in row_count integer)
db2 (cont.) => begin
db2 (cont.) =>  declare v_id int;
db2 (cont.) =>  declare v_name varchar(10);
db2 (cont.) =>  declare v_age int;
db2 (cont.) =>  declare v_count int;
db2 (cont.) =>  declare v_cnt int;
db2 (cont.) =>  set v_count = 1;
db2 (cont.) =>  set v_cnt = 0;
db2 (cont.) =>  select count(id) into v_cnt from tb_user;
db2 (cont.) =>  if (v_cnt >= 1) then
db2 (cont.) =>    select max(id)+1 into v_id from tb_user;
db2 (cont.) =>  else
db2 (cont.) =>    set v_id = 1;
db2 (cont.) =>  end if;
db2 (cont.) =>  while (v_count<=row_count) do
db2 (cont.) =>      select cast(rand()*100 as int) into v_age from sysibm.sysdummy1;
db2 (cont.) =>      insert into tb_user values(v_id, 'user_' || v_id, v_age);
db2 (cont.) =>      set v_id=v_id+1;
db2 (cont.) =>      set v_count=v_count+1;
db2 (cont.) =>    end while;
db2 (cont.) => end @
DB20000I  The SQL command completed successfully.