暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / DM8达梦数据库数据操纵

DM8达梦数据库数据操纵

原创 达梦 2021-01-31
1516

本章节主要介绍在 DM 数据库中如何进行増、删、改操作。

适用场景

软件 版本
操作系统 Redhat 7 及以上版本
DM 数据库 DM 8.0 及以上版本
CPU 架构 x86、ARM、龙芯、飞腾等国内外主流 CPU

操作方法

插入新行

插入新行，示例语句如下所示：

INSERT INTO dmhr.employee VALUES 
(11146,'Dameng','220103198501166001','whdm@dameng.com','13712346385',
'2020-10-26','52',9500.00,0,11005,1105);

插入多行

创建测试表，示例语句如下所示：

CREATE TABLE dmhr.test
AS
   SELECT employee_id,
          employee_name,
          identity_card,
          salary,
          department_id
     FROM dmhr.employee
    WHERE 1 = 2;

插入多行并查询，示例语句如下所示：

INSERT INTO dmhr.test
     VALUES
(1109, '程东生', '410107197103252999', 4400, 102),
(1110, '王金玉', '410107197103258999', 4300, 102),
(1111, '程东生', '410107197103252999', 4400, 102);
COMMIT;

SELECT * FROM dmhr.test;

输出结果：

多行插入

选择性插入行

可以按指定列插入行，未指定值的列上若定义了默认值，则插入默认值。没有指定默认值，为 NULL，则插入 NULL 值。示例语句如下所示：

//创建测试表
CREATE TABLE dmhr.t1
(
   id         INTEGER PRIMARY KEY,
   name       VARCHAR (12) DEFAULT 'dm2020',
   class_id   INTEGER NOT NULL,
   tp         TIMESTAMP DEFAULT SYSDATE
);

//插入数据
INSERT INTO dmhr.t1 (id, class_id) VALUES (1, 103);

输出结果：

选择性插入行

有缺省值在不列出的情况下，自动填入。

复制表结构

如需快速复制表结构且不需要数据，示例语句如下所示：

CREATE TABLE dmhr.t2 AS SELECT * FROM dmhr.t1 WHERE 1 = 0;
注意

使用 SP_TABLEDEF 过程查看 t2 的结构，所有定义在 t1 列上的约束均没有被新表继承。

需使用如下语句添加各类约束：

ALTER TABLE dmhr.t2  ADD PRIMARY KEY (id);
ALTER TABLE dmhr.t2  ALTER COLUMN name SET DEFAULT 'dm2020';
ALTER TABLE dmhr.t2  ALTER COLUMN class_id SET NOT NULL;
ALTER TABLE dmhr.t2  ALTER COLUMN tp SET DEFAULT SYSDATE;

多表插入

使用上述表 t1 和 t2 演示多表插入。为了方便演示先创建一个序列，示例语句如下所示：

CREATE SEQUENCE dmhr.seq_id START WITH 1 INCREMENT BY 1 MAXVALUE 20000 NOCYCLE;

一次性向两张表中插入多条数据，存在默认值同样可以省略。示例语句如下所示：

INSERT ALL
  INTO dmhr.t1 (id,name,class_id,tp)
  INTO dmhr.t2 (id,name,class_id,tp)
   SELECT dmhr.seq_id.nextval， DBMS_RANDOM.STRING('X',4),
CEIL(DBMS_RANDOM.VALUE(100,106)), SYSDATE
FROM DUAL CONNECT BY LEVEL <10;
注意

使用 managerid=null 查询无效（测试表中没有 null 数据，需插入 null）

MERGE INTO 操作

使用 MERGE INTO 语法可合并 UPDATE 和 INSERT 语句。通过 MERGE 语句，根据一张表（或视图）的连接条件对另外一张表（或视图）进行查询，连接条件匹配上的进行 UPDATE（可能含有 DELETE），无法匹配的执行 INSERT。

使用 MERGE 可以实现记录存在则 update，不存在则 insert 的逻辑。

创建两张表，示例语句如下所示：

CREATE TABLE dmhr.dup_emp
(
   employee_id     INTEGER PRIMARY KEY,
   employee_name   VARCHAR2 (12),
   identity_card   VARCHAR2 (18),
   salary          INTEGER,
   department_id   INTEGER
);

CREATE TABLE dmhr.emp_salary
(
   employee_id   INTEGER PRIMARY KEY,
   new_salary    INTEGER
);

插入数据，示例语句如下所示：

INSERT INTO dmhr.dup_emp
   SELECT employee_id,employee_name,identity_card,salary,department_id
     FROM dmhr.employee
    WHERE department_id = 102 AND salary < 9000;

INSERT INTO dmhr.emp_salary
   SELECT employee_id, salary + 2000 FROM dmhr.dup_emp;

INSERT INTO dmhr.emp_salary
     VALUES (1108, 4100);

测试数据结果如下：

merge 操作

merge 操作

执行 merge 语句，示例语句如下所示：

MERGE INTO dmhr.dup_emp
     USING dmhr.emp_salary
        ON (dmhr.dup_emp.employee_id = dmhr.emp_salary.employee_id)
WHEN MATCHED
THEN
   UPDATE SET dmhr.dup_emp.salary = dmhr.emp_salary.new_salary
WHEN NOT MATCHED
THEN
   INSERT VALUES (dmhr.emp_salary.employee_id,
                      'dm2020',
                      410107197103257999,
                      dmhr.emp_salary.new_salary,
                      102);

得到结果如下：

merge 操作

目标表 dup_emp 中添加了一条数据，其余行的 salary 列被更新成 emp_salary 中对应的值。如果 emp_salary 中有多行和目标表匹配成功，将会报如下错误：没有一组稳定的行。

merge 操作

处理违反参照完整性的记录

该情况出现在有主外键关系的表中。

//环境准备-去掉 employee 表 department_id 列上的外键约束。
ALTER TABLE dmhr.employee DROP CONSTRAINT EMP_DEPT_FK;

例如需更新其中一个员工的部门编号为 999，该编号部门在 dempartment 表中不存在。示例语句如下所示：

UPDATE dmhr.employee SET department_id = 999 WHERE employee_id = 1003;

commit；

再次添加外键约束，报错，结果如下所示：

参照完整性

这种提示在业务操作中经常出现，解决方法：删除子表行或者添加主表行。示例语句如下所示：

DELETE FROM dmhr.employee e
      WHERE NOT EXISTS
               (SELECT 1
                  FROM dmhr.department d
                 WHERE d.department_id = e.department_id);

再次添加外键约束，成功。示例语句如下所示：

ALTER TABLE dmhr.employee ADD CONSTRAINT EMP_DEPT_FK FOREIGN KEY(department_id)
REFERENCES dmhr.department(department_id);

删除重复记录

实际工作中经常遇到表内包含重复数据的情况，下面介绍几种删除重复数据的方法。

//准备数据，使用上面创建的 dup_emp 表，插入重复数据:
INSERT INTO dmhr.dup_emp VALUES
(1109, '程东生', '410107197103252999', 4400, 102),
(1110, '王金玉', '410107197103258999', 4300, 102),
(1111, '程东生', '410107197103252999', 4400, 102);
COMMIT;

输出结果：

删除重复记录

  • 方法一：

通过 group by + having 子句分组查询的方式，查找员工名称相同的记录，示例语句如下所示：

SELECT employee_name, count(*) 
FROM dmhr.dup_emp GROUP BY employee_name HAVING COUNT(*) > 1;

输出结果：

删除重复记录

  • 方法二：

通过 group by + rowid 的方式，查找员工名称重复的记录，示例语句如下所示：

SELECT *
  FROM dmhr.dup_emp
 WHERE ROWID NOT IN (  SELECT MAX (ROWID)
                         FROM dmhr.dup_emp
                     GROUP BY employee_name);

可在查找到重复记录后直接删除，示例语句如下所示：

DELETE FROM dmhr.dup_emp
      WHERE ROWID NOT IN (  SELECT MAX (ROWID)
                              FROM dmhr.dup_emp
                          GROUP BY employee_name);

DELETE FROM dmhr.dup_emp t
      WHERE ROWID <> (SELECT MAX (ROWID)
                        FROM dmhr.dup_emp
                       WHERE employee_name = t.employee_name);

SELECT * FROM dmhr.dup_emp;

输出结果：

删除重复记录

删除后记录由原来的 8 条减少为 5 条。

多表 update 容易犯的错误

场景示例：使用新表中的数据更新源表中的数据，通过编写关联 SQL 语句完成，新建两张表并准备数据。

  • 源表
CREATE TABLE dmhr.test
AS
   SELECT employee_id,
          employee_name,
          identity_card,
          salary,
          department_id
     FROM dmhr.employee
    WHERE 1 = 2;

SELECT * FROM dmhr.test;

INSERT INTO dmhr.test
     VALUES
(1109, '程东', '410107197103252999', 4400, 102),
(1110, '王金玉', '410107197103258999', 5300, 102),
(1111, '陈仙', '410107197103252999', 2400, 102),
(1112, '张晓中', '410107197103252999', 9000, 102),
(1113, '吴迎', '410107197103252999', 7400, 102);
COMMIT;

SELECT * FROM dmhr.test;

源表输出结果：

源表

  • 新表
CREATE TABLE dmhr.test_new
AS
   SELECT employee_id,employee_name,identity_card,salary,department_id
     FROM dmhr.employee
    WHERE 1 = 2;

INSERT INTO dmhr.test_new
     VALUES (1111, '陈仙', '410107197103252999', 8500, 104);
COMMIT;

SELECT * FROM dmhr.test_new;

新表输出结果：

新表

编写关联更新的 SQL 语句，按新表中的工资去更新原表。员工陈仙的工资从 2400 更改为 8500，示例语句如下所示：

UPDATE dmhr.test ot
   SET (salary, department_id) =
          (SELECT nt.salary, nt.department_id
             FROM dmhr.test_new nt
            WHERE ot.employee_id = nt.employee_id);

SELECT * FROM dmhr.test;

这样写看似正确，但却把所有其他的列都更改成了 NULL。

新表

原因是当新表 dmhr.test 中的数据无法和源表 dmhr.test_new 中的行匹配时，子查询选择不出来相应的值，所以用空值对不匹配的行进行了更新，产生了严重的错误。

//回滚
rollback;

正确写法应该再加一层判断，示例语句如下所示：

UPDATE dmhr.test ot
   SET (salary, department_id) =
          (SELECT nt.salary, nt.department_id
             FROM dmhr.test_new nt
            WHERE ot.employee_id = nt.employee_id)
 WHERE EXISTS
          (SELECT 1
             FROM dmhr.test_new nt
            WHERE ot.employee_id = nt.employee_id);

SELECT * FROM dmhr.test;

正确的输出结果：

更新

参考文档

更多 SQL 语言使用说明，请参考《DM_SQL 语言使用手册》，手册位于数据库安装路径 /dmdbms/doc 文件夹下。如有其他问题，请在社区内咨询。

达梦
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论