数据操纵

SQL 的数据操纵功能通过数据操纵语言（Data Manipulation Language，DML）实 现，用于改变数据库中的数据。数据更新包括插入、删除和修改 3 种操作，对应 INSERT、

DELETE 和 UPDATE 三条语句。在 Oracle 11g 中，DML 除了包括 INSERT、UPDATE 和 DELETE 语句之外，还包括 TRUNCATE、CALL、EXPLAIN PLAN、LOCK TABLE 和 MERGE 等语句。在本节中将对 INSERT、UPDATE、DELETE、TRUNCATE 常用语 句进行介绍。

3.4.1 INSERT 语句

INSERT 语句用于完成各种向数据表中插入数据的功能，既可根据对列赋值一次插 入一条记录，也可根据 SELECT 查询子句获得的结果记录集批量插入指定数据表。

1．一般 INSERT 语句

INSERT 语句主要用于向表中插入数据。INSERT 语句的语法如下：

INSERT INTO [user.]table [@db_link] [(column1[,column2]...)]

VALUES (expressl[,express2]...)

其中，table 表示要插入的表名；db_link 表示数据库链接名；columnl，column2 表 示表的列名；VALUES 表示给出要插入的值列表。

在 INSERT 语句的使用方式中，最为常用的形式是在 INSERT INTO 子句中指定添 加数据的列，并在 VALUES 子句中为各个列提供一个值。

【例 3.34】用 INSERT 语句向 JOBS 表添加一条记录，其结果如图 3.32 所示。

insert into jobs(job_id,job_title,min_salary,max_salary) values('IT_TEST',测试员',3000.00,8000.00)

图 3.32 例 3.34 的运行结果示意图

在向表的所有列添加数据时，也可以省略 INSERT INTO 子句后的列表清单，使用 这种方法时，必须根据表中定义的列的顺序，为所有的列提供数据。可以使用 DESC 命令查看表中定义列的顺序。

【例 3.35】使用 DESC 命令查看 JOBS 表中各列的定义次序，然后省略列表清单向 表中添加一行记录，其结果如图 3.33 所示。

desc jobs

insert into jobs values('IT_DBA','数据库管理员',5000.00,15000.00)；

图 3.33 例 3.35 的运行结果示意图

如果上面示例的 VALUES 子句少指定了一个列的值，则在执行时就会收到如下的 错误信息：

ORA-00947：没有足够的值

注意：如果没有按正确的顺序提供各列的插入值，那么插入操作可能失败；也可能 插入成功，但在表中则添加了一条错误的数据，这种情况造成的危害更大。因此，推荐 使用明确指定插入数据的列名的方式进行 INSERT，它可以有效地避免上述两种错误的 发生。

从上例的 DESC 命令的显示结果可以看出，JOB_ID 和 JOB_TITLE 列不能为 NULL，

而 MIN_SALARY 和 MAX_SALARY 列则可以接受 NULL 值。也就是说，JOB_ID 和

JOB_TITLE 列被定义了 NOT NULL 约束，在添加数据时，必须为这两个字段提供数据，

而其余的两个字段不受此限制。

【例 3.36】建立没有 MAX_SALARY 值的记录，其结果如图 3.34 所示。

insert into jobs(job_id,job_title,min_salary) values('PP_MAN','产品经理',5000.00)：

图 3.34 例 3.36 的运行结果示意图

如果某个列不允许 NULL 值存在，而用户没有为该列提供数据，则会因为违反相 应的约束而插入失败。事实上，在定义表的时候为了数据的完整性，常常会为表添加许 多完整性约束。例如在 JOBS 表中，为了保证表中每条记录的唯一性，为 JOB_ID 列定 义了主键约束。再次尝试将上例运行一次，则因为违反主键约束而失败，运行结果如图 3.35 所示。

insert into jobs(job_id,job_title,min_salary) values('PP_MAN','产品经理',5000.00)：

图 3.35 由于表的完整性约束插入失败

关于为表定义的完整性约束，将在后面的章节中介绍，这里需要记住的是在向表添 加记录时，添加的数据必须符合为表定义的所有完整性约束。

2．批量 INSERT

SQL 提供了一种成批添加数据的方法，即使用 SELECT 语句替换 VALUES 语句，

由 SELECT 语句提供添加的数据，语法如下：

INSERT INTO [user.]table [@db_link] [(column1[,column2]...)] Subquery

其中，Subquery 是子查询语句，可以是任何合法的 SELECT 语句，其所选列的个 数和类型应该与前边的 column 相对应。

【例 3.37】例 3.1 中我们建立了一个名为 IT_EMPLOYEES 的表，下面的示例将从 EMPLOYEES 表提取 department_id 等于“IT”的雇员信息，并保存到 IT_EMPLOYEES 中。

insert into IT_EMPLOYEES(

employee_id,first_name,last_name,email, phone_number,job_id,salary,manager_id)

select em.employee_id,em.first_name,em.1ast_name,em.email, em.phone_number,em.job_id,em.salary,em.manager_id from employees em,departments dep

where em.department_id=dep.department_id and dep.department_name='IT’;

上述语句的运行结果如图 3.36 所示。

图 3.36 例 3.37 的运行结果示意图

从上面的运行结果可以看出，使用 INSERT 和 SELECT 的组合语句一次性为新创 建的表添加了 5 行记录。

注意：在使用 INSERT 和 SELECT 的组合语句成批添加数据时，INSERT INTO 指 定的列名可以与 SELECT 指定的列名不同，但是其数据类型必须相匹配，即 SELECT 返回的数据必须满足表中列的约束。

3.4.2 UPDATE 语句

当需要修改表中一列或多列的值时，可以使用 UPDATE 语句。使用 UPDATE 语句 可以指定要修改的列和修改后的新值，使用 WHERE 子句可以限定被修改的行。使用 UPDATE 语句修改数据的语法形式如下：

UPDATE table_name

SET {column1=express1[,column2=express2]

(column1[,column2])=(select query)}

[WHERE condition]

其中，各选项含义如下：

 UPDATE 子句用于指定要修改的表名称。需要后跟一个或多个要修改的表名 称，这部分是必不可少的。

 SET 子句用于设置要更新的列以及各列的新值。需要后跟一个或多个要修改 的表列，这也是必不可少的。

 WHERE 后跟更新限定条件，为可选项。

【例 3.38】使用 UPDATE 语句为所有程序员提高 15％的薪金，其运行结果如图 3.37 所示。

update employees

set salary = salary * 1.15 where job_id='IT_PROG';

图 3.37 例 3.38 的运行结果示意图

以上使用了 WHERE 子句限定更新薪金的人员为程序员（job_id='IT_PROG'），如果 在使用 UPDATE 语句修改表时，未使用 WHERE 子句限定修改的行，则会更新整个表。

同 INSERT 语句一样，可以使用 SELECT 语句的查询结果来实现更新数据。

【例 3.39】使用 UPDATE 语句更新编号为 104 的雇员薪金，调整后的薪金为 IT 程 序员的平均薪金。

update employees set salary=

(select avg(salary) from employees

where job_id='IT_PROG') where employee_id=104；

运行上述语句后的结果如图 3.38 所示。

图 3.38 例 3.39 的运行结果示意图

注意：在使用 SELECT 语句提供新值时，必须保证 SELECT 语句返回单一的值，

否则将会出现错误。

3.4.3 DELETE 语句

数据库向用户提供了添加数据的功能，那么一定也会向用户提供删除数据的功能。

从数据库中删除记录可以使用 DELETE 语句来完成。就如同 UPDATE 语句一样，用户 也需要规定从中删除记录的表，以及限定表中哪些行将被删除。

DALETE FROM table_name [WHERE condition]

其中，关键字 DELETE FROM 后必须要跟准备从中删除数据的表名。

【例 3.40】一个简单的示例，从 IT_EMPLOYEES 表中删除一条记录。

delete from it_employees where employee_id=107；

上述删除语句的运行结果如图 3.39 所示。

图 3.39 例 3.40 的运行结果示意图

提示：建议使用 DELETE 语句一定要带上 WHERE 子句，否则将会把表中所有数 据全部删除。

3.4.4 TRUNCATE 语句

如果用户确定要删除表中所有的记录 ，则建议使用 TRUNCATE 语句。使用 TRUNCATE 语句删除数据时，通常要比 DELETE 语句快许多。因为使用 TRUNCATE

语句删除数据时，它不会产生回滚信息，因此执行 TRUNCATE 操作也不能被撤销。

【例 3.41】使用 TRUNCATE 语句删除 IT_EMPLOYEES 表中所有的记录。

truncate table it_employees;

select employee_id,last_name from it_employees;

运行上述语句后的结果如图 3.40 所示。

图 3.40 例 41 的运行结果示意图

在 TRUNCATE 语句中还可以使用关键字 REUSE STORAGE，表示删除记录后仍然 保存记录占用的空间；与此相反，也可以使用 DROP STORAGE 关键字，表示删除记录 后立即回收记录占用的空间。TRUNCATE 语句默认为使用 DROP STORAGE 关键字。

使用关键字 REUSE STORAGE 保留删除记录后的空间的 TRUNCATE 语句如下：

truncate table it_employees reuse stoage;

说明：若使用 DELETE FROM TABLE_NAME 语句，则整个表中的所有记录都将 被删除，只剩下一个表格的定义，在这一点上，语句作用的效果和 TRUNCATE TABLE TABLE_NAME 的效果相同。但是 DELETE 语句可以用 ROLLBACK 来恢复数据，而 TRUNCATE 语句则不能。