KingbaseES数据库insert技巧_数据库insert怎么用

---

数据库版本：KingbaseES V008R006C008B0014

---

简介

    INSERT 语句用于将数据插入表中，向指定表格添加1行或多行数据，本篇文章主要以kingbase介绍insert的一些技巧。

        

文章目录如下

1. 基本语法

2. 实用技巧

2.1. 插入其他表数据

2.2. 快速插入万行数据

2.3. 插入随机数

2.4. 插入随机字符

2.5. 插入随机日期

2.6. 插入随机指定字符

3. 应用案例

---

        

1. 基本语法

insert 的基本语法如下：

INSERT INTO
    表名(列1, 列2...)
VALUES
    (数据1, 数据2...);

        

比如创建一张简单表（3列数据）：

CREATE TABLE t1(
    id int default 1,
    name text,
    avg int
);

【案例一】指定列名插入数据

INSERT INTO
    t1(id, name)
VALUES
    (1, '小李');

如果是全部插入则可以不指定列名（默认全部）

INSERT INTO
    t1
VALUES
    (1, '小李', 18);

        

【案例二】插入多行数据

INSERT INTO
    t1
VALUES
    (1, '小李', 18),
    (2, '小王', 19),
    (3, '小张', 16);

        

【案例三】使用默认值

INSERT INTO
    t1
VALUES
    (1, '小李', DEFAULT),  --使用默认值
    (2, '小王', 19),
    (3, '小张', DEFAULT);  --使用默认值

        

2. 实用技巧

介绍几种实用的技巧，包括快速插入大量数据、随机数、随机字符、随机日期、随机指定字符等。

2.1. 插入其他表数据

查询其他表数据，插入到指定表中

INSERT INTO
    表1(列名1, 列名2)  --插入数据到表1
SELECT                --查询表2的数据，将其插入到表1
    表2列1, 表2列2
FROM
    表2
WHERE
    条件;

【案例一】将 t1 中 id 和 name 数据插入到 t2

INSERT INTO
    t2(id,name)
SELECT
    id,name
FROM
    t1;

• 仅查询t1表2列数据插入，所以另外2列为空

        

【案例二】手动增加另2列数据

INSERT INTO
    t2
SELECT
    id,
    name,
    19,            --手动指定数据
    '2000-01-01'   --手动指定数据
FROM
    t1;

        

【案例三】插入连接查询的数据

INSERT INTO t3 (id, name, dept, id_number)
SELECT * FROM t1,t2;

• 直接查询2张表会将所有数据合并，根据实际情况利用连接查询的方法插入即可。

        

2.2. 快速插入万行数据

使用函数 generate_series 可以快速插入上万行，语法如下：

generate_series(起始大小, 结束大小, 步进)

        

【案例一】插入整数1~10（共10行）

INSERT INTO
    t1
VALUES(
    generate_series(1, 10)
);

        

【案例二】插入整数10~15（共6行）

INSERT INTO
    t1
VALUES(
    generate_series(10, 15)
);

        

【案例三】插入1~10的奇数

INSERT INTO
    t1
VALUES(
    generate_series(1, 10, 2)
);

        

如果存在多列，将该函数带入某列即可

INSERT INTO
    t3
VALUES(
    generate_series(1, 100000),  --插入1~10w
    '小李',
    '502385199003092375',
    '综合部');

        

2.3. 插入随机数

内置函数 random 用于生成一个0~1的随机小数

        

通过 random * N 来实现生成随机数，比如生成100~1000的随机数

random() * 901 + 100

        

如果需要取整则使用 TRUNC 函数

TRUNC (random() * 901 + 100)

        

通过上述的方法，我们可以直接生成6位随机数

TRUNC(random()*1000000)

这种方法不太稳定，如果前面数字为0则会被自动屏蔽，所以需要使用 LPAD 函数

/*将左侧用0填充6位数*/
LPAD(TRUNC(random()*1000000), 6, 0)

        

将该函数应用到 insert

INSERT INTO t1 VALUES(LPAD(TRUNC(random()*1000000), 6, 0));

        

2.4. 插入随机字符

随机字符也通过随机数 random 来生成，将随机数转换为32位的哈希值

MD5(RANDOM())

        

同样可以通过 SUBSTRING 函数来截取前6位

SUBSTRING(MD5(RANDOM()), 1, 6)

        

2.5. 插入随机日期

生成 2020 年之内的随机日期

to_char(
    (date '2020-01-01' + TRUNC(random()*365+1)), 
    'YYYY-MM-DD'
)

• 通过 TRUNC(random()*365+1) 生成一个1~365的随机数；
• 再通过date日期相加(得到一个随机天数偏移量)；
• 最后通过to_char将日期转换为YYYY-MM-DD格式。

        

如果希望插入幅度比较大的随机日期，自己计算天数，修改random*数值即可。或者使用日期相减的方式（生成2020-01-01到2030-12-31的随机日期）

to_char(
    (date '2020-01-01' + trunc(
        random() * (date '2030-12-31' - date '2020-01-01' + 1))), 
    'YYYY-MM-DD'
)

        

生成当前日期使用 now()

INSERT INTO t1 VALUES(now());

        

2.6. 插入随机指定字符

通过 case + random 的方式来生成指定的字符，比如生成 "男" 或 "女"

CASE WHEN
    random() < 0.5 THEN
        '男'
    ELSE
        '女'
END

        

再举一个例子，随机生成10、20、40、100，同样的方法：使用 random * 4 取整得到0~4的整数，再通过这四个数判断

SELECT CASE floor(random() * 4)::int  --取0~4随机值
    WHEN 0 THEN 10  --结果0生成10
    WHEN 1 THEN 20  --结果1生成20
    WHEN 2 THEN 40  --结果2生成40
    ELSE 100        --结果3生成100
END;

        

结合 insert 插入

INSERT INTO
    t1
VALUES
    (CASE WHEN random() < 0.5 THEN '男' ELSE '女' END);

        

3. 应用案例

准备一张员工表

CREATE TABLE emp(
    工号      int            primary key,
    姓名      varchar(128)   not null,
    性别      varchar(2)     default '男',
    年龄      int            not null,
    学历      varchar(32)    not null,
    薪资      decimal(10,2)  not null,
    入职日期  date           default now()
);

插入1w行随机数据

INSERT INTO
    emp
VALUES(
    GENERATE_SERIES(1, 10000),  --生成1~10000的整数
    SUBSTRING(MD5(RANDOM()), 1, 5),  --生成5位随机字符
    CASE WHEN random() < 0.5 THEN '男' ELSE '女' END,  --随机生成男或女
    TRUNC (random() * 27 + 18),    --生成18~45的随机数
    CASE floor(random() * 4)::int WHEN 0 THEN '专科' WHEN 1 THEN '本科' WHEN 2 THEN '硕士' ELSE '博士' END,
    TRUNC (random() * 15000 + 5000),  --生成5000~20000的随机数
    to_char((date '2020-01-01' + TRUNC(random()*730+1)), 'YYYY-MM-DD')  --生成2020后面2年的随机日期
);

结果如下：