普通表

创建


create table table_name(a int)data capture changes;

含义：创建了一个最简单的普通表，表中只有a 一种数据类型(int)，表名为table_name


删除


drop table

含义：删除刚才创建的数据库表table_name


重命名


rename table <old_name> to

含义：将表的名字替换为新的名字，本操作为原子操作，它会立刻修改表的名称，重命名后，与表关联的索引，约束，触发器也会自动更新


truncate(清表)


truncate table

含义：立刻删除表中的所有数据，但是表的结构不变，操作不可逆，一旦执行无法撤销。


增加列


alter table <table_name> add column <int_name> int;

含义：改变表中的结构，增加了一列，列的属性自己定义，本操作增加了一列int，名字为int_name。


删除列


alter table <table_name> drop column

含义：table_name是表的名字，column_name是列的名字，本操作删除了已经建立表中的一列。

若在删除列后，插入数据失败需加上如下指令：

REORG TABLE <table_name>;


修改列定义


标准格式：

alter table <table_name> alter column <column_name>set data type int;

含义：将列的数据类型的定义改为int，列名字为column_name，表的名字为table_name。

例子：

alter table table_com_alter_col alter column age set data type smallint;

将表中integer的类型数据属性改为smallint。

alter table table_com_alter_col alter column sex set data type varchar(20);

将表中varchar(10)的属性改为varchar(20)。

alter table table_com_alter_col alter column dept set default '研发部';

将表中dept列的默认值修改为研发部。

alter table table_com_alter_col alter column dept drop default;

将表中的默认值移除。

REORG TABLE table_com_alter_col;

//本命令只能在db client中执行，sqldev不支持

含义：若想在更改完列的属性后，再继续正常插入数据，必须执行此命令，否则就会报错，reorg命令的作用就是将数据库重组，重新放置数据位置，这样我们更改的结构进行重构后，就可以继续插入等操作。

另一个优化器是runstats,在对表进行reorg操作后，执行runstats可以更新统计信息，方便查询，收集表和索引的统计信息。

runstats on table <table_name>.<column_name>

runstats on index <table_name>.<index_name>


重命名列


alter table <table_name> rename column <column_name> to

含义：将数据库表中的column_name列名字替换为column_new_name，数据库表名字为table_name

重命名后，不需要reorg table，就能继续进行DML和DDL操作。


增加comment(注释）


含义：对表添加注释：comment on table <table_name> is 'db2表的注释'

含义：对表中列添加注释：comment on column <table_name>.<table_column> is '列的注释'


reorg table(重组)


含义：在对表的列进行操作时，列的属性如果发生变化，就需要进行重组操作：reorg table ，然后才能对数据库表进行插入等操作。


set integrity

 (完整性约束)


分区表的attach（合并分区）中，我将别的表作为分区表的一部分合并到分区表中，被合并的表会消失，作为分区表的一部分而存在，但是合并完成后，你是无法查询到合并进去后的数据的，需要进行禁用完整性约束并启用立即完整性检查，加快分区的附加过程，跳过约束检查的步骤。

set integrity for <合并后的分区表的名字> all immediate unchecked;

关键词含义：

set integrity ：设置表的完整性属性关键词，

for 表名 ：指定要设置完整性属性的表或分区范围的名称，

all：表示指定表的范围或分区的范围中的所有行设置完整性属性，

immediate：指定在更改后立即进行完整性检查，

unchecked：此选项在完整性检查过程中禁用引用完整性约束检查。


带属性

(如IMPLICITLY HIDDEN，

GENERATED ALWAYS等)

定义表的列的特性，隐式隐藏，始终生成等。

<column_name> <column_type> IMPLICITLY HIDDEN

含义：用于将列在查询结果中隐藏，除非明确请求显示该列，此隐式可以用于隐藏一些敏感或不常用的列，以提高查询结果的可读性。

c1 INT,

c2 DOUBLE,

c3 DOUBLE GENERATED ALWAYS AS (c1 + c2)

含义：始终生成用于指定列的值是通过计算或表达式生成的，而不是由用户提供，这个属性通常用于创建计算列或自动生成的列，列如自增主键。


用户授权grant/revoke


db2 grant use of tablespace bigtablespace to public

含义：授权所有用户组 public 对名为 bigtablespace 的表空间的使用权限。public 是 DB2 中一个预定义的用户组，代表所有用户。

分区表

创建分区表


指定创建表中的（sales_date date）作为分区依据进行分区PARTITION BY RANGE(sales_date)

分区表的分区是和表一起创建的，不能分开执行创建。

PARTITION BY range(age_id)

( PART p1 STARTING(minvalue)  ENDING(10),

STARTING(11)  ENDING(20), )

含义：其中分区表的分区依据为age_id，第一个分区名字为p1，第二个分区名字为p2，最后类推。

alter table <table_name> add partition PART P3 STARTING(21) ENDING

 

添加列


alter table <table_name> add column <column_name> <column_type>

含义：正常添加列就可以，也不需要reorg table，列添加完成后，插入数据和查询也不受影响。


删除列


alter table <table_name> drop column <column_name> ;

含义：你添加列的时候，不需要对表进行reorg table操作，但是删除列的时候，列的属性已经发生变化，此时需要对表进行reorg重组操作才能进行插入等DML，DDL操作。

reorg table <table_name>；终端执行


修改列定义


alter table <table_name> alter column <column_name> set date type

reorg table <table_name>；终端执行

含义：修改列的定义，已经将列的属性改变，所以，在更改分区表的列属性时，和更改非分区表的属性一样，都需要在终端执行reorg table <table_name>进行数据库表重构。


重命名表和列


rename table <table_name> to

alter table <table_name> rename column <column_name> to

含义：重命名表和列，都不需要reorg重组，正常重名名即可，但是重命名后，原来名字就失效了。


truncate(清表)


truncate table <table_name> immediate;

含义：立刻清除表中所有数据，且不留下日志，本操作飞快，根本没得反应，重要表慎重。


注释


comment on table <table_name> is 'db2表-ranges分区表';

comment on column <table_name>.<column_name> is '列的注释';


range分区

创建

指定创建表中的（sales_date date）作为分区依据进行分区PARTITION BY RANGE(sales_date)

分区表的分区是和表一起创建的，不能分开执行创建。

PARTITION BY range(age_id)

( PART p1 STARTING(0) ENDING(10),

 PART p2 STARTING(11) ENDING(20),

PART P3 STARTING(21) ENDING AT MAXVALUE )

含义：其中分区表的分区依据为age_id，第一个分区名字为p1，第二个分区名字为p2，最后类推。



删除

alter table <table_name> detach partition <partition_name> into table

drop table

含义：分区表是将一个表分成不同的分区，用来形成多个表的效果，而分区表的删除是将分区的一部分从表中分离出来成为一个表，再将表进行删除。



增加分区

alter table <table_name> add partition <partition_name> starting(2011) ending(2015);

向分区表中添加分区从2011到2015。



attach分区

合并分区

alter table <table_name_分区表> attach  partition <p1> starting from (2006)ending at(2015) from table <被合并的表>

含义：此指令，将一个独立的表合并到另一个分区表中，且作为分区表的一部分，执行后，原来的表会消失，且新合并的分区表，两个表的结构必要相同，且2006和2015是分区表中作为分区的依据的数据范围，所以新表合并时，才可以这样填写，原表已经对这数据附近的数据进行了分区。



detach分区

删除分区

alter table <table_name> detach partition <partition_name> into table

drop table

含义：分区表是将一个表分成不同的分区，用来形成多个表的效果，而分区表的删除是将分区的一部分从表中分离出来成为一个表，再将表进行删除。


组合分区

创建

create table table_hash_multi_part(

a int,

b int,

c int        )DISTRIBUTE BY HASH(a)

PARTITION BY RANGE(b)

(PART P1 STARTING 1 ENDING 5,

 PART P2 STARTING 6 ENDING 10)

ORGANIZE BY DIMENSIONS(c)

含义：创建组合分区表，a为哈希映射，对a数据进行哈希函数处理，将结果映射到相应节点上，具有相同的哈希的值被分发到相同的节点上。


 

新增分区

alter table <table_name> add partition p3


 

删除分区

alter table <table_name> detach partition p2

drop table


 

合并分区

ALTER TABLE <table_name> attach partition p2


 

添加列

ALTER TABLE <table_name> add column c1 varchar(10);


 

删除列

ALTER TABLE <table_name> drop column <column_name>;

reorg table


 

修改列的定义

alter table <table_name> alter column c2 set data type smallint;


 

truncate

truncate table <table_name> immediate;


 

rename表

rename table <table_name> to <table_name_new>;


 

rename列

alter table table_com rename column


 

注释

comment on table table_com is  'db2表-组合分区表';

comment on column table_com.a is '列a';

索引

普通索引

创建

CREATE INDEX idx_name ON

含义：为"students"表格中的"name"字段添加一个普通索引，并命名为"idx_name"。这样可以在查询"students"表格时使用"name"字段作为查询条件，从而加速查询操作的速度。



删除

drop index idx_name;


唯一索引

创建

CREATE UNIQUE INDEX unique_name ON

含义：唯一索引是一种特殊类型的索引，它确保表格中的某个字段只包含唯一值。因此，唯一索引可以用于确保数据的完整性，防止重复数据的插入或更新。其他类型的索引则不一定具有这种唯一性约束。



删除

drop index unique_name


主键索引

创建

<NAME> <DATATYPE> primary key;

含义：主键索引是通过在列定义中指定PRIMARY KEY关键字来创建的,且主键索引是和表创建时，一起创建的。



删除

alter table <table_name> drop primary key;

reorg table <table_name>;


升序索引

创建

create unique index <index_name> on <table_name>(<column_name> ASC);

含义：语法中index_name是索引名字，table_name是数据库表的名字，column_name是在其上创建索引的名称，ASC表示升序排序，可定义多个升序索引，名为联合升序索引



删除

drop index <index_name>;


降序索引

创建

create unqiue index <index_name> on <table_name>(<column_name> DESC);

含义：语法中index_name是索引名字，table_name是数据库表的名字，column_name是在其上创建索引的名称，DESC表示降序排序，可定义多个降序索引，名为联合降序索引



删除

drop index <index_name>;


纯索引

创建

create unique index <index_name> on

含义：语法中index_name是索引的名称，table_name是创建索引表格的名称,column_name是要在其上创建索引的列名称。



删除

drop index <index_name>;


双向索引

创建

create index <index_name> on <table_name>(<column_name> ASC，<column_name> DESC) ;

create index <index_name> on <table_name>(column_name)  allow reverse scans;

含义：index_name是要创建的索引的名称，table_name是要在其上创建索引的表格名称，column_name是要在其上创建索引的列名称。注意，语法中列名称需要分别指定为升序（ASC）和降序（DESC）



删除

drop index <index_name>;


非分区索引

创建

create index <index_name> on <table_name>(<column_name>) not partitioned;

含义：index_name是创建的索引名称，table_name是在其上创建索引的表格的名称，column_name是要在其上创建索引的列名的名称。



删除

drop index <index_name>;


分区索引

创建

create index <index_name> on <table_name> (<column_name>) partition by range(column_name) ( starting <value1> ending <value1>,starting <value1> ending

create index <index_name> on <table_name> (<column_name>) partitioned;

含义：index_name是要创建的索引的名称，table_name是要在其上创建索引的表格名称，column_name是要在其上创建索引的列名称。PARTITION BY子句用于指定分区键，即用于将表格分成多个分区的列。RANGE子句用于指定分区范围，即将分区键的值范围分成多个不同的分区。



删除

drop index <index_name>;


指定参数索引

创建

含义：pctfree是用于定义索引中的空闲空间百分比的参数，指定索引页中保持为空闲空间的空间比例，以便后续操作中，提供空间，而无需频繁的进行页的分裂和重组，在新的索引插入时，数据库会检查索引空间利用率，不够的话，进行页的分裂和重组。

create index <index_name> on <table_name>(<column_name>) pctfree 10;

create unique index <index_name> on  <table_name> ( a1, a2 )  pctfree 49;



删除

 drop index <index_name>;

约束

主键约束

（命名/未命名）

创建

主键约束用来作为标识表中的行，使其不为空，具有唯一性。

命名主键约束：constraint <constaint_name> primary key

含义：<constraint_name>为命名主键约束的名字，可定义column_name列为命名主键

未命名主键约束：<column_name> <column_type> not null primary key

含义：<column_name>为列名，<column_type>为列的数据类型，命名主键约束的维护性和可读性更好。



删除

alter table <table_name> drop primary key;


唯一约束

（命名/未命名）

创建

命名：constraint <un_constraint_name> unique(column_name);

含义：<un_constraint_name>为唯一约束的名字，<column_name>为要约束的列。

未命名：<column_name> <column_type> not null unique;

含义：<column_name> 为列名 <column_type>为列的格式。



删除

alter table <table_name> drop unqiue


非空约束

（命名/未命名）

创建

未命名：<column_name> <column_type> not null;

含义：column_name为列名，column_type为列的类型。

建表后：alter table <table_name> alter <column_name> set not null;

含义：<table_name>为表名 <column_name>为列名。



删除

alter table <table_name> alter <constraint_column_name> drop not null;


外键约束

（cascade）

创建

命名：constraint <constraint_name> foreign key(<column_name>) references

含义：<constraint_name> 为约束的名字, <column_name>为列的名字,<table_name>为表的名字。

未命名： foreign key(<column_name>) references

含义：column_name为列名，table_name为表名，column_name为表名。



删除

alter table <table_name> drop foreign key


检查约束

创建

<column_name> <column_type> check

含义：<column_name>为列名字，后面是列的类型<column_type>，检查约束目的是控制输入的数据，要符合约束的范围，如：此例子是控制，年龄超过18岁。



删除

alter table <table_name> drop check

存储过程

普通存储过程

创建

create or replace procedure

 begin

insert into <table_name> values(100,'an',sysdate,11,'ann');

end

含义：执行call dds_proc_test()，dds_proc_test是存储过程的名字



删除

drop procedure <procedure_name>;


带wrapped

创建

对存储过程进行加密，传递一个包装过的存储过程定义作为参数

call dbms_ddl.create_wrapped(

'create or replace procedure

begin

 insert into proc_tab_wrapped values(100,''an'',sysdate,11,''ann'')'

end

);

含义：存储过程的定义和执行同时进行了，隐藏了存储过程的实际逻辑或保护存储过程的定义不能被直接访问



删除

drop procedure <procedure_name>;


带loop循环

创建

 CREATE PROCEDURE LOOP_UNTIL_SPACE(OUT counter INTEGER)//输出

 LANGUAGE SQL//声明

BEGIN

       DECLARE v_counter INTEGER DEFAULT 0;//声明变量

       DECLARE v_firstnme VARCHAR(12);//声明变量

       DECLARE v_midinit CHAR(1);//声明变量

       DECLARE v_lastname VARCHAR(15);//声明变量

CURSOR FOR SELECT firstnme, midinit, lastname FROM

  DECLARE CONTINUE HANDLER

         SET counter = -1;

  OPEN

       fetch_loop://loop循环的声明

       LOOP

  FETCH c1 INTO

 IF v_midinit = ' ' THEN

LEAVE

  END IF;//判断条件，如果判断为空，则跳出本次循环

SET

  END LOOP fetch_loop;//结束循环

       SET counter = v_counter;//将v_counter赋值给counter

       CLOSE c1;//关闭游标c1

     END

解释：该存储过程的目标是在employee表中计算满足midinit字段不为空格字符的记录数，并将结果存储在输出参数counter中。



删除

drop procedure <procedure_name>;


带for循环

创建

create or replace PROCEDURE

             LANGUAGE SQL //函数中使用了sql语句，需要此语句指定声明sql语句的使用，像变量一样使用前声明   

       BEGIN ATOMIC         //存储过程的逻辑开始，ATOMIC表示将整个存储过程作为原子操作执行。                    

 DECLARE fullname CHAR(40); //声明fullname为一个char(40)类型的变量，存储员工全名                    

FOR v AS cur1 CURSOR FOR

 //声明了一个名为cur1的游标，该游标从employee表中选择firstnme,midinit,lastname列的数据。        

DO                                                       

v as cur1中v为循环变量

              SET fullname = v.lastname || ',' || v.firstnme  ||' ' || v.midinit;

       //语句将员工的全名构建并存储到fullname变量中。                 

INSERT INTO proc_tab_for VALUES (fullname);

//将每个员工的全名插入到名为proc_tab_for的表中。

END FOR;

END

含义：该存储过程是遍历表employee中的数据，构建每个员工的全名，并将全名插到名为proc_tab_for的表中，使用游标和循环结构，逐行处理employee表中的数据，并将结果插入到另一个表中



删除

drop procedure <procedure_name>;


带cursor

游标

创建

介绍：cursor游标，可以定义游标将每一行的查询结果存储在游标中，游标根据不同条件的查询结果获得结果集。游标的使用需要声明游标，然后打开游标，使用完成后，需要关闭游标。

CREATE TABLE

CREATE TABLE

CREATE procedure

BEGIN

DECLARE int_a int;//声明一个int类型变量

DECLARE cursor1 CURSOR FOR SELECT * FROM

OPEN

FETCH cursor1 INTO

CLOSE

INSERT INTO test_2 VALUES

END

SELECT * FROM test_1;

SELECT * FROM test_2;

CALL cursor_procedure();

例2：

 create table proc_tab_cursor(name varchar(50)) data capture changes

 create or replace PROCEDURE

LANGUAGE SQL

BEGIN ATOMIC//保持事务完整性，若一个地方出错，全部回滚。

DECLARE fullname CHAR(40);

FOR v AS cur1 CURSOR FOR

 DO

 SET

 ||' ' || v.midinit;

INSERT INTO proc_tab_cursor VALUES

END FOR;

END

含义：for循环是默认和游标配合，使得游标从表的第一列到最后一列循环，遍历表employee中的数据，构建每个员工的全名，并将全名插到名为proc_tab_for的表中，使用游标和循环结构，逐行处理employee表中的数据，并将结果插入到另一个表中。



删除

drop procedure <procedure_name>;

函数

不带参数

创建

create or replace

returns

begin                

return

end

含义：调用该函数时候，函数会返回字符串"hello world"但是在调用时，每行都会被调用。



删除

drop function <function_name>



调用

select <function_name> from

含义：函数会逐行的对表中的数据进行操作。


带参数

创建

create or replace function

returns varchar(20)

begin                                 

declare

select f2 into lc_name from <table_name> where

--p_name:=lc_name;         //注释行                        

return

end

f1列中等于传入的参数p_id的值，那行的列f2会被查询出来，并存储到定义的变量lc_name中，并且返回此变量值，调用该函数时，每一列都会被调用。



删除

drop function <function_name>



调用

SELECT <function_name>(103,'john') FROM <table_name>;

视图

视图

创建

介绍：视图是一个虚拟的表，基于一个或多个表的查询结果创建的，通过创建视图，可以将复杂的逻辑封装起来，以便在使用时可以像操作表一样方便的查询和使用数据。

视图： create view <view_name> as

含义：该命令，创建了一个视图，可通过视图去查询表中的数据，该视图选择了表中所有的列

只读视图：create view <view_name> as

含义：该命令创建了一个只读视图，不能通过视图进行修改表中的数据，只能通过视图查看表中的数据。



删除

drop view <view_name>


视图用户授权

创建

grant update on  <table_name>   to <user_name>  with grant option;  

 为表<table_name>授予用户<user_name>更新权限，且该用户有将此权限授权给其他用户的权力。



删除

drop view <view_name>

注释

表注释


comment on table <table_name> is '表注释'


列注释


comment on table <table_name>.<table_column> is '列注释'

数据导入命令

IMPORT

数据导入

db2 "import from Exp.del of del MODIFIED BY CODEPAGE=1386 TIMESTAMPFORMAT=\"yyyy-mm-dd hh:mm:ss tt\"  INSERT INTO LIWXSMALL.SMALL2";

import from Exp.del of del：导入以逗号分隔的文本形式的文件。

MODIFIED BY CODEPAGE=1386：

TIMESTAMPFORMAT=\"yyyy-mm-dd hh:mm:ss tt\"  ：指定时间戳的格式。

INSERT INTO LIWXSMALL.SMALL2"：数据插入到库为LIWXSMALL表名为SMALL2中。

db2 "import from Exp.ixf of ixf INSERT INTO LIWXSMALL.SMALL2";

含义：不指定编码格式导入到库为LIWXSMALL表名为SMALL2中。


EXPORT

数据导出

db2 "export to Exp.del of del MODIFIED BY CODEPAGE=1386 TIMESTAMPFORMAT=\"yyyy-mm-dd hh:mm:ss tt\" SELECT * FROM <database_name>.<table_name>"

含义：本命令的作用是将查询的结果导出到名为Exp.del的以逗号分隔的文本文件中。

export to Exp.del of del ：导出命令的语法，Exp.del 是导出的文件名,del表示以逗号分隔的文本格式。

MODIFIED BY CODEPAGE=1386：这是一个选项，代码页1386表示使用阿拉伯字符编码，也是GBK。

TIMESTAMPFORMAT = \"yyyy-mm-dd hh:mm:ss tt\"：指定时间戳的格式，为年-月-日 时:分:秒。

SELECT * FROM <database_name>.<table_name>：从数据库表中查询数据。

db2 "export to Exp.ixf of ixf MODIFIED BY CODEPAGE=1386  SELECT * FROM LIWXSMALL.SMALL"

export to Exp.ixf of ixf ：表示使用db2存储器格式。

db2 "export to test1.del of del MODIFIED BY CODEPAGE=1386  SELECT * FROM LIWXSMALL.TEST1"


LOAD

数据导入

load from data_file_of_type

of <type> <MODIFIED BY CODEPAGE=1386> 可选编码方式

<METHOD options> 指定load操作的方法选项

<MESSAGES msg_file> 指定消息文件的路径

INSERT | REPLACE | RESTART 指定数据的处理方式插入，替换，重新开始加载

INTO <table_name>.<column_name>加载数据的目标表及列列表


INGEST

高效加载数据，类似load

--指定字符集为gbk

db2 "INGEST FROM FILE  test1386.del  FORMAT DELIMITED input codepage 1386 INSERT INTO LIWXSMALL.TEST2"

INGEST FROM FILE test1386.del FORMAT DELIMITED ：数据导入的命令语法，test1386.del是包含要导入数据的文件名，FORMAT DELIMITED ：表示文件是以分隔符格式存储的。

FORMAT DELIMITED input codepage 1386：指定字符为GBK编码格式。

INSERT INTO LIWXSMALL.TEST2：插入数据的目标表，LIWXSMALL.TEST2是目标表名。

--指定字符集为utf8

db2 "export to test1208.del of del MODIFIED BY CODEPAGE=1208  SELECT * FROM LIWXSMALL.TEST1";

db2 "INGEST FROM FILE  test1208.del  FORMAT DELIMITED input codepage 1208 INSERT INTO LIWXSMALL.TEST2"

--不指定字符集

db2 "export to test1.del of del  SELECT * FROM LIWXSMALL.TEST1"

db2 "INGEST FROM FILE  test1.del  FORMAT DELIMITED INSERT INTO LIWXSMALL.TEST2"

数据恢复命令



数据恢复命令需要临时关闭自动提交，不建议永久关闭，commit提交。

在终端执行:

1、db2 update command options using C off

2、插入数据

3、db2 rollback 数据恢复

DB有数据创建索引可以生效吗_数据库

多个DDL/DML 连续执行



ddl数据定义语言：定义数据表中对象和更改对象类型,表，索引，视图，函数，存储过程

dml数据操作语言：增删改查等