上一篇我们学习了如何创建和维护数据库中的表。有了表之后，我们就可以执行一些数据操作了。今天我们就来学习如何对表中的数据进行增加、删除、修改以及合并操作。

增加数据

SQL 主要提供了两种增加数据的方法：

  1. INSERT INTO … VALUES … ;
  2. INSERT INTO … SELECT … ;

首先，第一种形式的语法如下：
INSERT INTO t(col1, col2, …) VALUES (value1, value2, …); 复制
其中，t 是表名；VALUES 子句中提供的值与 INSERT INTO 中的字段数量必须相同，并且数据类型能够兼容。
举例来说：
— 只有 Oracle 需要执行以下 alter 语句 — alter session set nls_date_format = ‘YYYY-MM-DD’; INSERT INTO employee(empid, emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email) VALUES ( 26, ‘张三’, ‘男’, 5, 18, ‘2019-12-25’, 10, 6000, NULL, ‘zhangsan@shuguo.com’); 复制
以上语句为员工表增加了一个员工。如果 VALUES 值列表与表中的字段顺序完全一致，可以省略字段列表；以上示例也可以简写成：
— 只有 Oracle 需要执行以下 alter 语句 — alter session set nlsdate_format = ‘YYYY-MM-DD’; INSERT INTO employee VALUES ( 26, ‘张三’, ‘男’, 5, 18, ‘2019-12-25’, 10, 6000, NULL, ‘zhangsan@shuguo.com’); 复制
如果同时执行了上面的两个插入语句，第 2 次执行时将会产生主键冲突的错误。因为数据库在插入数据之前将会执行完整性检查，对于违反约束的数据提示错误，而不会生成新的数据。以下两个语句分别违反了外键约束和非空约束：
— 违反外键约束，职位不存在 INSERT INTO employee(empid, emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email) VALUES ( 27, ‘李四’, ‘男’, 5, 18, ‘2019-12-25’, 0, 6000, NULL, ‘lisi@shuguo.com’); — 违反非空约束，姓名不能为空_ INSERT INTO employee(emp_id) VALUES ( 28 ); 复制
第二种插入数据的语法通过 SELECT 语句查询出一个结果集，然后将该结果集插入表中。我们先创建一个新表 emp_devp：
CREATE TABLE emp_devp ( emp_id INTEGER NOT NULL PRIMARY KEY , emp_name VARCHAR(50) NOT NULL , sex VARCHAR(10) NOT NULL , dept_id INTEGER NOT NULL , manager INTEGER , hire_date DATE NOT NULL , job_id INTEGER NOT NULL , salary NUMERIC(8,2) NOT NULL , bonus NUMERIC(8,2) , email VARCHAR(100) NOT NULL ) ; 复制
然后通过一个查询语句，将研发部的员工信息复制到 emp_devp 表中：
INSERT INTO emp_devp(emp_id, emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email) SELECT FROM employee WHERE dept_id = (SELECT dept_id FROM department *WHERE dept_name = ‘研发部’); 复制
这种语法通常用于数据仓库中的 ETL（抽取、转换和加载）或者生成测试数据。

修改数据

SQL 使用 UPDATE 语句更新表中的数据，基本语法如下：
UPDATE t SET col1 = expr1, col2 = expr2, … [WHERE condition]; 复制
其中，t 是表名；SET 子句指定了需要更新的列和更新后的值，多个字段使用逗号进行分隔；只有满足 WHERE 条件的数据行才会被更新；如果没有指定条件，将会更新表中所有行。
以下语句将会更新 empdevp 表中“赵云”的月薪和邮箱：
UPDATE emp_devp SET salary = salary + 1000, bonus = 8000 WHERE emp_name = ‘赵云’; SELECT emp_name, salary, bonus FROM emp_devp WHERE emp_name = ‘赵云’; emp_name|salary |bonus | ————|————|———-|_ 赵云 |16000.00|8000.00| 复制
“赵云”的月薪增加了 1000，奖金被修改为 8000。
和插入数据一样，更新数据时也会执行约束校验，确保不会产生违反约束的数据。
除了直接指定数据值之外，也可以通过一个关联子查询获取更新后的值：
UPDATE emp_devp SET email = (SELECT e.email FROM employee e WHERE e.emp_id = emp_devp.emp_id); 复制
以上语句在 SET 子句中使用了一个关联子查询，将员工表中开发部门员工的电子邮箱更新到 emp_devp 表的对应记录中。

删除数据

SQL 中使用 DELETE 语句删除数据：
DELETE FROM t [WHERE conditions]; 复制
只有满足 WHERE 条件的数据才会被删除；如果省略 WHERE 条件，将会删除表中所有的数据。
以下语句将会删除员工表中的“张三”：
— SELECT * DELETE FROM employee WHERE emp_name = ‘张三’; 复制
Oracle 和 SQL Server 中可以省略 FROM 关键字。在删除数据之前，可以使用相应的 SELECT 语句查看要删除的数据。
在上一篇中，我们介绍了 TRUNCATE 语句；它可以用于快速删除表中的所有数据。这两种删除数据的方式存在一些区别：

  • DELETE 语句通过 WHERE 条件从表中删除指定的数据；如果不指定条件将会删除所有的数据。DELETE 属于数据操作语言（DML），删除数据后，可以选择提交或者回滚。如果删除的数据较多时速度比较慢。
  • TRUNCATE 语句用于快速删除表中的所有数据，并且释放表的存储空间。TRUNCATE 属于数据定义语言（DDL），操作默认提交无法回滚。TRUNCATE 相当于删除表并重建，通常执行速度很快。

另外，DROP TABLE 语句用于删除一个表，包括表中的数据。

合并数据

在 SQL:2003 标准中引入了一个新的数据操作语句：MERGE。MERGE 可以同时完成 INSERT 和 UPDATE 的操作，甚至 DELETE 的功能。
目前只有 Oracle 和SQL Server 支持 MERGE 语句，简单的 MERGE 语法如下：
— Oracle 和 SQL Server 实现 MEGRE INTO target_table [AS t_alias] USING source_table [AS s_alias] ON (condition) WHEN MATCHED THEN UPDATE SET column1 = expr_1, column2 = expr_2, … WHEN NOT MATCHED THEN INSERT (column1, column2, …) VALUES (expr_1, expr_2, …); 复制
其中，

  • target_table 是合并操作的目标表；
  • USING 指定了数据的来源，可以是一个表或者查询结果；
  • ON 指定了合并的条件，通常是主键或者唯一键相等；
  • 对于数据源中的每一行，如果在目标表中存在满足条件的记录，执行 WHEN MATCHED THEN 分支更新目标表中对应的记录；如果不存在匹配的记录，执行 WHEN NOT MATCHED THEN 分支在目标表中插入一条新记录。

我们先创建一个用于数据合并的目标表 empmerge：
CREATE TABLE emp_merge ( emp_id INTEGER NOT NULL PRIMARY KEY , emp_name VARCHAR(50) NOT NULL , sex VARCHAR(10) NOT NULL , salary NUMERIC(8,2) NOT NULL , email VARCHAR(100) NOT NULL ); 复制
以下示例使用 MERGE 语句将员工表中女性员工的信息合并到 emp_merge 表中：
— Oracle 和 SQL Server 实现 MERGE INTO emp_merge t USING (SELECT emp_id, emp_name, sex, salary, email FROM employee WHERE sex = ‘女’) s ON (t.emp_id = s.emp_id) WHEN MATCHED THEN UPDATE SET t.emp_name = s.emp_name, t.sex = s.sex, t.salary = s.salary, t.email = s.email WHEN NOT MATCHED THEN INSERT (emp_id, emp_name, sex, salary, email) VALUES (s.emp_id, s.emp_name, s.sex, s.salary, s.email); 复制
其中，合并的条件为 emp_id 是否相同；相同则更新已有的数据，不同则插入数据。第一次运行以上语句时，由于 emp_merge 表中没有任何数据，因此执行的都是 WHEN NOT MATCHED THEN 分支，即插入新的数据。
SELECT *FROM emp_merge; EMP_ID|EMP_NAME|SEX|SALARY|EMAIL | ———|————|—-|———|————————————| 7|孙尚香 |女 | 12000|sunshangxiang@shuguo.com| 8|孙丫鬟 |女 | 6000|sunyahuan@shuguo.com | 12|赵氏 |女 | 6600|zhaoshi@shuguo.com | 复制
大家可以尝试修改 emp_merge 中的某些数据，然后再次运行上面的 MERGE 语句，看看 WHEN MATCHED THEN 分支的更新结果。
MERGE 语句也可以只包含更新或者插入操作，例如：
— Oracle 和 SQL Server 实现 MERGE INTO emp_merge t USING (SELECT emp_id, emp_name, sex, salary, email FROM employee WHERE sex = ‘女’) s ON (t.emp_id = s.emp_id) WHEN NOT MATCHED THEN INSERT (emp_id, emp_name, sex, salary, email) VALUES (s.emp_id, s.emp_name, s.sex, s.salary, s.email); 复制
以上语句只在没有匹配数据时插入新的数据，而不执行更新操作。
另外，MERGE 还支持 DELETE 功能，用于在目标表中找到匹配的数据时删除记录：
— Oracle 实现 MERGE INTO emp_merge t USING (SELECT emp_id, emp_name, sex, salary, email FROM employee WHERE sex = ‘女’) s ON (t.emp_id = s.emp_id) WHEN MATCHED THEN UPDATE SET t.emp_name = s.emp_name, t.sex = s.sex, t.salary = s.salary, t.email = s.email DELETE WHERE t.emp_name = ‘赵氏’ WHEN NOT MATCHED THEN INSERT (emp_id, emp_name, sex, salary, email) VALUES (s.emp_id, s.emp_name, s.sex, s.salary, s.email); — SQL Server 实现 MERGE INTO emp_merge t USING (SELECT emp_id, emp_name, sex, salary, email FROM employee WHERE sex = ‘女’) s ON (t.emp_id = s.emp_id) WHEN MATCHED AND t.emp_name = ‘赵氏’ THEN DELETE WHEN MATCHED THEN UPDATE SET t.emp_name = s.emp_name, t.sex = s.sex, t.salary = s.salary, t.email = s.email WHEN NOT MATCHED THEN INSERT (emp_id, emp_name, sex, salary, email) VALUES (s.emp_id, s.emp_name, s.sex, s.salary, s.email); 复制
Oracle 和 SQL Server 对于 DELETE 的实现略有不同，但都是在 MATCHED 分支增加了一个删除操作。如果运行以上语句，emp_merge 表中姓名为“赵氏”的数据将会被删除。
MySQL 和 PostgreSQL 没有提供 MERGE 语句，而是使用专有的语法格式：
— MySQL 实现 INSERT INTO emp_merge(emp_id, emp_name, sex, salary, email) SELECT emp_id, emp_name, sex, salary, email FROM employee s WHERE sex = ‘女’ ON DUPLICATE KEY UPDATE emp_name = s.emp_name, sex = s.sex, salary = s.salary, email = s.email; — PostgreSQL 实现_ INSERT INTO emp_merge(emp_id, emp_name, sex, salary, email) SELECT emp_id, emp_name, sex, salary, email FROM employee WHERE sex = ‘女’ ON CONFLICT(emp_id) DO UPDATE SET emp_name = EXCLUDED.emp_name, sex = EXCLUDED.sex, salary = EXCLUDED.salary, email = EXCLUDED.email; 复制
MySQL 中 ON DUPLICATE KEY UPDATE 表示主键或者唯一索引出现重复值时执行更新操作；PostgreSQL 中 ON CONFLICT(emp_id) DO UPDATE 表示 emp_id 重复时执行更新操作，EXCLUDED 代表了此时的源数据。这两种语法不支持 DELETE 操作。

小结

数据操作语言（Data Manipulation Language）用于操作表中的数据，包括插入（INSERT）、修改（UPDATE）、删除（DELETE）以及合并（MERGE）。虽然我们使用最多的是查询语句，但生成数据是进行查询分析的前提；应用程序通常也需要使用 DML 语句进行业务数据的操作。
练习题 1：以下语句能不能执行成功，为什么？
— SELECT * DELETE FROM department WHERE dept_id = 5; 复制
如何处理这种情况？
练习题 2：尝试修改 emp_merge 中的某些数据，然后再次运行上面合并数据的示例，看看执行更新分支语句的结果。