DB2 常用SQL语句

1. 序列管理

创建从1开始，自增为1的序列：

-- MySchema: 模式名，可以省略
-- MySeq: 序列名
CREATE SEQUENCE "MySchema"."MySeq" AS INTEGER START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE
NO CYCLE CACHE 20 NO ORDER

查询序列：

SELECT
    SEQNAME,
    INCREMENT,
    "START",
    MAXVALUE,
    MINVALUE,
    "CYCLE",
    CACHE,
    "ORDER"
FROM
    sysibm.syssequences

查询当前值：

SELECT
    "MySchema"."MySeq".PREVVAL
FROM
    sysibm.dual;

查询下一个值：

SELECT
    "MySchema"."MySeq".NEXTVAL
FROM
    sysibm.dual;

重设下一个值：

-- 重设下一个值为168
ALTER SEQUENCE "MySchema"."MySeq" RESTART WITH 168

-- 重设下一个值，此时的下一个值为1
ALTER SEQUENCE "MySchema"."MySeq" RESTART

修改最大值：

-- 将序列的最大值改为99999
ALTER SEQUENCE "MySchema"."MySeq" MAXVALUE 99999

-- 不指定最大值
ALTER SEQUENCE "MySchema"."MySeq" NO MAXVALUE

注：
i. 不指定时的最大值将为序列类型的最大值：如果是INT，为2147483647；如果是BIGINT，为9223372036854775807
ii. MAXVALUE不可以写成分开的形式，否则报错

修改最小值：

-- 指定最小值为10
ALTER SEQUENCE "MySchema"."MySeq" MINVALUE 10

-- 不指定最小值
ALTER SEQUENCE "MySchema"."MySeq" NO MINVALUE

注：
i. 修改最小值后，获取的下一个值会变为新的最小值
ii. 不指定时的最小值将为序列类型的最小值：INT和BIGINT都为1
iii. MINVALUE不可以写成分开的形式，否则报错

修改递增值：

ALTER SEQUENCE "MySchema"."MySeq" INCREMENT BY 2

此时序列的当前值会在前一个值的基础上加2

修改缓存：

-- 缓存20个数据
ALTER SEQUENCE "MySchema"."MySeq" CACHE 20

-- 不缓存数据，可以写成NOCACHE
ALTER SEQUENCE "MySchema"."MySeq" NO CACHE

注：不缓存时，缓存的数据个数为1

切换排序与否：

-- 排序
ALTER SEQUENCE "MySchema"."MySeq" ORDER;

-- 不排序，可以写成NOORDER
ALTER SEQUENCE "MySchema"."MySeq" NO ORDER;

切换是否循环使用序列：

  序列配置成不能循环使用时，获取超过最大值的下一个值会报错。如序列当前值为30，最大值为30，步进为1，获取NEXTVAL会报错

-- 循环使用
ALTER SEQUENCE "MySchema"."MySeq" CYCLE;

-- 不循环使用，可以写成NOCYCLE
ALTER SEQUENCE "MySchema"."MySeq" NO CYCLE;

删除序列：

DROP SEQUENCE "MySchema"."MySeq"

2. 表管理
创建表：

CREATE TABLE
    person
    (
        id BIGINT NOT NULL,
        name VARCHAR(20) NOT NULL,
        age SMALLINT,
        CONSTRAINT PK_PERSON PRIMARY KEY (ID)
    )
    IN TBS_DAT_8K INDEX IN TBS_IDX_32K;

引号和大小写
表名和字段名加上引号时，DB2创建（修改）的表和字段的大小写和引号内的内容一致；

不加引号时，全部转成大写。

修改表：

-- person表重命名为t_b
ALTER TABLE
    person RENAME TO t_b;

-- 去除name的not null约束
ALTER TABLE
    t_b DATA CAPTURE none;
ALTER TABLE
    t_b ALTER name DROP NOT NULL;
ALTER TABLE
    t_b DATA CAPTURE CHANGES;

-- 添加列sex和create_time，可以添加多个列，COLUMN可以省略
ALTER TABLE
    t_b ADD COLUMN sex CHAR(1) ADD create_time TIMESTAMP DEFAULT SYSDATE;

-- 重命名列sex为gender
ALTER TABLE
    t_b ALTER COLUMN sex gender CHAR(1);

-- 修改列age的数据类型为整型
ALTER TABLE
    t_b ALTER age SET DATA TYPE INTEGER;

-- 删除列create_time
ALTER TABLE
    t_b DROP create_time;

删除表：

DROP TABLE
    t_b;

3. 批量插入

INSERT
INTO
    t_b
    (
        ID, NAME, AGE, GENDER
    )
    VALUES
    (
        MySeq.nextVal,'Lilei',22,'M'
    )
    ,
    (
        MySeq.nextVal,'Lucy',18,'F'
    );

4. 数据复制

将t_a中除ID外的字段值拷贝到t_b中，t_b的ID为主键，由seq生成：

INSERT
INTO
    t_b
    (
        ID, NAME, AGE, GENDER
    )
SELECT
    MySeq.nextVal, NAME, AGE, GENDER
FROM
    t_a;

将t_a中除ID外的字段值更新到t_b中：

UPDATE
    t_b b
SET
    (
        b.NAME,
        b.AGE,
        b.GENDER
    )
    =
    (
        SELECT
            a.NAME,
            a.AGE,
            a.GENDER
        FROM
            t_a a
        WHERE
            a.name = b.name
        FETCH
            FIRST 1 ROWS ONLY) -- Avoid multiple rows that causes the update failed
WHERE -- The limit is necessary, otherwise it will update all the records in t_b
    b.ID IN
    (
        SELECT
            b.ID
        FROM
            t_a a2
        WHERE
            a2.name = b.name
        FETCH
            FIRST 1 ROWS ONLY)

5. 分页查询

SELECT
    *
FROM
    (
        SELECT
            ROW_NUMBER() OVER(ORDER BY NAME ASC, AGE DESC) AS row_num,
            NAME,
            AGE
        FROM
            t_b) t
WHERE
    t.row_num >= 21
AND t.row_num <= 30

注：
OVER()的参数指定按什么字段来排序，可以为空。
可以用BETWEEN 21 AND 30来替换上面的分页条件。


6. 查询前几条记录

查询年龄在25岁以上的前三条记录：

a. FETCH

SELECT
    *
FROM
    t_b
WHERE
    AGE > 25
ORDER BY
    AGE ASC
FETCH
    FIRST 3 ROWS ONLY

b. ROW_NUMBER()

SELECT
    *
FROM
    (
        SELECT
            ROW_NUMBER() OVER(ORDER BY AGE ASC) AS num,
            ID,
            NAME,
            AGE,
            GENDER
        FROM
            t_b
        WHERE
            AGE > 25) t
WHERE
    num <= 3

查出25岁以上的男女前3条记录：

SELECT
    *
FROM
    (
        SELECT
            ROW_NUMBER() OVER(PARTITION BY GENGER ORDER BY AGE ASC) AS num,
            ID,
            NAME,
            AGE,
            GENDER
        FROM
            t_b
        WHERE
            AGE > 25) t
WHERE
    num <= 3

PARTITION的作用是把BY后面字段相同的数据放到一起，类似于GROUP BY的功能。区别是GROUP BY语句除了能使用聚集函数（COUNT等）外，只能检索GROUP BY后面的字段；而PARTITION没有此限制。

7. 模式管理

查询当前模式列表：

SELECT SCHEMANAME, OWNER, CREATE_TIME FROM syscat.schemata;

创建模式：

-- 显式创建
CREATE SCHEMA "MySchema"; -- 模式名：加引号会严格按照大小写；不加为全部大写

-- 隐式创建
CREATE TABLE "MySchema"."STRENGTH"(id INT, name VARCHAR(40));

删除模式：

DROP SCHEMA "MySchema" RESTRICT;

删除schema需要使用RESTRICT关键字,而且该schema中无对象存在。

8. 索引管理

创建索引：

在同一个Schema中，索引名称不能重复，否则创建失败。

CREATE INDEX
    "IDX_ACCOUNT_NO"
ON
    "MySchema"."T_ACCOUNT" -- schema可以省略
    (
        "ACCOUNT_NO"
    )

逻辑：没有则创建；否则返回。所以重复执行创建语句是可以的。

如果要创建唯一索引，在INDEX前加入UNIQUE即可：

CREATE UNIQUE INDEX
    "IDX_ACCOUNT_NO"
ON
    T_ACCOUNT
    (
        ACCOUNT_NO
    )

（唯一）索引可以建立在多个字段上：

CREATE UNIQUE INDEX
    "IDX_ACCOUNT_NO"
ON
    T_ACCOUNT
    (
        COMPANY_ID, ACCOUNT_NO
    )

还可以根据字段升（降）序来索引：

CREATE UNIQUE INDEX
    "IDX_ACCOUNT_NO"
ON
    T_ACCOUNT
    (
        COMPANY_ID ASC,
        ACCOUNT_NO DESC
    )

删除（唯一）索引：

DROP INDEX "IDX_ACCOUNT_NO"

删除不存在的索引会报错。

9. IF EXISTS

DB2不支持下面的写法：

DROP TABLE IF EXISTS t_b

10. 执行系统命令

在DB2客户端执行REORG等命令会报错，可以调用系统存储过程：

CALL SYSPROC.ADMIN_CMD('REORG TABLE t_b');