DM8数据操纵语言(DML)

 INSERT语句详解

INSERT语句用于向表中插入新记录。

-- 基本INSERT语法
/*
INSERT INTO table_name [(column1, column2, ..., columnN)]
VALUES (value1, value2, ..., valueN);
*/

-- 完整列名列表的INSERT
INSERT INTO employees (
employee_id,
name,
email,
hire_date,
job_id,
salary,
department_id
) VALUES (
1001,
'张三',
'zhangsan@example.com',
TO_DATE('2023-01-15', 'YYYY-MM-DD'),
'IT_PROG',
8000,
90
);

-- 省略列名列表(必须为所有列提供值)
INSERT INTO departments
VALUES (300, '研发部', 100, 1700);

-- 多行INSERT(批量插入)
INSERT ALL
INTO employees VALUES (1002, '李四', 'lisi@example.com', SYSDATE, 'SA_REP', 6000, NULL, 80)
INTO employees VALUES (1003, '王五', 'wangwu@example.com', SYSDATE, 'IT_PROG', 7500, NULL, 90)
INTO employees VALUES (1004, '赵六', 'zhaoliu@example.com', SYSDATE, 'HR_REP', 6500, NULL, 70)
SELECT 1 FROM DUAL;

-- 从其他表插入数据
INSERT INTO employee_history
SELECT
employee_id,
name,
salary,
department_id,
SYSDATE
FROM
employees
WHERE
hire_date > TO_DATE('2022-01-01', 'YYYY-MM-DD');

UPDATE语句详解

UPDATE语句用于修改表中的现有记录。

-- 基本UPDATE语法
/*
UPDATE table_name
SET column1 = value1, column2 = value2, ..., columnN = valueN
[WHERE condition];
*/

-- 简单UPDATE示例
UPDATE employees
SET
salary = salary * 1.1, -- 薪资增长10%
last_raise_date = SYSDATE
WHERE
department_id = 80
AND hire_date < TO_DATE('2020-01-01', 'YYYY-MM-DD');

-- 使用子查询更新
UPDATE employees e
SET
salary = (
SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id
) * 1.2,
manager_id = (
SELECT manager_id
FROM departments
WHERE department_id = e.department_id
)
WHERE
department_id IN (
SELECT department_id
FROM departments
WHERE location_id = 1700
);

-- 使用JOIN更新(达梦特有语法)
UPDATE
(SELECT e.salary, d.avg_sal
FROM employees e
JOIN (SELECT department_id, AVG(salary) avg_sal
FROM employees GROUP BY department_id) d
ON e.department_id = d.department_id)
SET
salary = avg_sal
WHERE
salary < avg_sal;

DELETE语句详解

DELETE语句用于从表中删除记录。

-- 基本DELETE语法
/*
DELETE FROM table_name
[WHERE condition];
*/

-- 简单DELETE示例
DELETE FROM employees
WHERE
employee_id = 1001;

-- 使用子查询删除
DELETE FROM employees
WHERE
department_id IN (
SELECT department_id
FROM departments
WHERE location_id = 1700
);

-- 删除所有记录(慎用)
DELETE FROM temp_table;

-- 使用JOIN删除(达梦特有语法)
DELETE FROM employees e
WHERE EXISTS (
SELECT 1
FROM departments d
WHERE d.department_id = e.department_id
AND d.location_id = 1700
);

MERGE语句详解

MERGE语句可以同时执行INSERT和UPDATE操作，非常适合数据同步场景。

-- MERGE语法结构
/*
MERGE INTO target_table t
USING source_table s
ON (t.key = s.key)
WHEN MATCHED THEN
UPDATE SET t.column1 = s.column1, t.column2 = s.column2, ...
WHEN NOT MATCHED THEN
INSERT (t.column1, t.column2, ...)
VALUES (s.column1, s.column2, ...);
*/

-- MERGE实际示例
MERGE INTO employee_target t
USING employee_source s
ON (t.employee_id = s.employee_id)
WHEN MATCHED THEN
UPDATE SET
t.name = s.name,
t.salary = s.salary,
t.department_id = s.department_id,
t.last_update = SYSDATE
WHEN NOT MATCHED THEN
INSERT (
employee_id,
name,
salary,
department_id,
create_date,
last_update
) VALUES (
s.employee_id,
s.name,
s.salary,
s.department_id,
SYSDATE,
SYSDATE
);

-- 带条件的MERGE
MERGE INTO products p
USING new_products np
ON (p.product_id = np.product_id)
WHEN MATCHED THEN
UPDATE SET
p.price = np.price,
p.stock = p.stock + np.stock
WHERE
np.price > 0
WHEN NOT MATCHED THEN
INSERT (
product_id,
product_name,
price,
stock
) VALUES (
np.product_id,
np.product_name,
np.price,
np.stock
)
WHERE
np.price > 0;