SQL - 数据操作

上一页
下一页

Oracle 提供数据操纵语言 (DML) 命令来执行数据库中的数据操作。数据操作可以是使用应用程序或业务数据填充数据库表，修改数据以及根据需要从数据库中删除数据。 除了数据操作外，还有一些命令用于控制这些操作。这些命令被分组为事务控制语言 (TCL)。

在逻辑 SQL 事务中涉及三种类型的 DML 语句，即插入、更新、删除和合并。事务是在数据库会话中 DML 操作的逻辑集合。

INSERT 语句

INSERT 命令用于将数据存储到表中。INSERT 命令通常在更高级的编程语言（如 Visual Basic.NET 或 C++）中用作嵌入式 SQL 命令；但是，此命令也可以在命令模式下的 SQL*PLUS 提示符下执行。INSERT 命令有两种不同的形式。如果新行将为行的每一列插入一个值，则使用第一种形式。INSERT 命令的第二种形式用于插入某些列数据未知或从其他业务逻辑中默认的行。此形式的 INSERT 命令要求您指定正在存储数据的列名。

语法

如果表中所有列的值都是确定的和已知的，则可以遵循以下语法。

INSERT INTO table
VALUES (column1 value, column2 value, 
...);

如果表中只有少数几列需要填充值，则可以使用以下语法。其余列可以将它们的值推断为 NULL 或来自不同的业务逻辑。

INSERT INTO table (column1 name, column2 name, . . .)
VALUES (column1 value, column2 value, . . .);

下面的 INSERT 语句在 EMPLOYEES 表中创建一个新的员工记录。请注意，它为主键列 EMPLOYEE_ID、FIRST_NAME、SALARY 和 DEPARTMENT_ID 插入值。

INSERT INTO employees (EMPLOYEE_ID, FIRST_NAME, SALARY, DEPARTMENT_ID)
VALUES (130, 'KEMP', 3800, 10);

否则，如果预先知道值并且必须符合表中列的数据类型和位置，则可以使用以下 INSERT 语句在 EMPLOYEES 表中插入完整的员工数据，而无需指定列列表。

INSERT INTO employees
VALUES (130, 'KEMP','GARNER', '[email protected]', '48309290',TO_DATE ('01-JAN-2012'), 'SALES', 3800, 0, 110, 10);

要插入的值必须与列的数据类型兼容。文字、固定值和特殊值（如函数、SYSDATE、CURRENT_DATE、SEQ.CURRVAL (NEXTVAL) 或 USER）可以用作列值。指定的值必须遵循通用规则。字符串文字和日期值必须用引号括起来。日期值可以使用 DD-MON-RR 或 D-MON-YYYY 格式提供，但首选 YYYY，因为它清楚地指定了世纪，并且不依赖于内部 RR 世纪计算逻辑。

INSERT-AS-SELECT (IAS) 语句

可以使用 INSERT..AS..SELECT (IAS) 操作将数据从源表填充到目标表。这是一个直接路径读取操作。这是一种简单的方法，可以将数据从一个表复制到另一个表，或者创建一个源表操作在线的表的备份副本。

例如，可以将数据从 EMPLOYEES 表复制到 EMP_HISTORY 表。

INSERT INTO EMP_HISTORY
SELECT EMPLOYEE_ID, EMPLOYEE_NAME, SALARY, DEPARTMENT_ID
FROM employees;

UPDATE 语句

UPDATE 命令修改存储在列中的数据。它可以一次更新一行或多行，具体取决于 WHERE 子句中指定的条件过滤的结果集。请注意，更新列与更改列不同。在本章前面，您学习了 ALTER 命令。ALTER 命令更改表结构，但不会影响表数据。UPDATE 命令更改表中的数据，而不是表结构。

语法

UPDATE table
SET column = value [, column = value ...]
[WHERE condition]

从语法来看，

SET column = expression 可以是任何字符、公式或函数的组合，这些字符、公式或函数将更新指定列名中的数据。WHERE 子句是可选的，但如果包含，则它指定将更新哪些行。一次只能使用 UPDATE 命令更新一个表。

下面的 UPDATE 语句将员工 JOHN 的薪水更新为 5000。

UPDATE employees
SET salary = 5000
WHERE UPPER (first_name) = 'JOHN';

虽然 WHERE 谓词是可选的，但必须逻辑地附加，以便仅修改表中的所需行。下面的 UPDATE 语句更新表中所有员工的薪水。

UPDATE employees
SET salary = 5000;

也可以通过在 SET 子句中指定多个用逗号分隔的列来更新多个列。例如，如果 JOHN 的薪水和职位都必须分别更改为 5000 和 SALES，则 UPDATE 语句如下所示：

UPDATE employees
SET	SALARY = 5000,
	JOB_ID = 'SALES'
WHERE UPPER (first_name) = 'JOHN';

1 row updated.

另一种更新同一行多个列的方法显示了子查询的使用。

UPDATE employees
SET (SALARY, JOB_ID) = (SELECT 5000, 'SALES' FROM DUAL)
WHERE UPPER (ENAME) = 'JOHN'

DELETE 语句

DELETE 命令是 SQL 语句中最简单的语句之一。它从表中删除一行或多行。SQL 中不允许多表删除操作。DELETE 命令的语法如下所示。

DELETE FROM table_name
    [WHERE condition];

DELETE 命令删除表中满足可选 WHERE 子句中条件的所有行。由于 WHERE 子句是可选的，因此可以通过省略 WHERE 子句轻松地从表中删除所有行，因为 WHERE 子句限制了 DELETE 操作的范围。

下面的 DELETE 语句将从 EMP 表中删除 EDWIN 的详细信息。

DELETE employees
WHERE UPPER (ENAME) = 'EDWIN'

1 row deleted.

注意：DELETE [TABLE NAME] 和 DELETE FROM [TABLE NAME] 具有相同的含义。

条件删除语句中的 WHERE 条件可以使用子查询，如下所示。

DELETE FROM employees
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID
				    FROM LOCATIONS
				    WHERE LOCATION_CODE = 'SFO')

TRUNCATE

TRUNCATE 是一个 DDL 命令，用于清空表中的所有记录，但保留表结构。它不支持 WHERE 条件来删除选定的记录。

语法

TRUNCATE [table name]

它是自动提交的，即它提交会话中的当前活动事务。截断表不会删除依赖的索引、触发器或表约束。如果表 A 是数据库中表 B 的引用约束的父表，则无法截断表 A。

事务

事务是在数据库中完成的逻辑工作单元。它可以包含：

  • 以 TCL 命令（即 COMMIT 或 ROLLBACK）结尾的多个 DML 命令

  • 一个 DDL 命令

  • 一个 DCL 命令

事务的开始以第一个 DML 命令为标记。它以 TCL、DDL 或 DCL 命令结尾。显式发出 TCL 命令（即 COMMIT 或 ROLLBACK）以结束活动事务。由于其基本行为，如果数据库会话中执行任何 DDL 或 DCL 命令，则会提交会话中正在进行的活动事务。如果数据库实例异常崩溃，则事务将停止。

COMMIT、ROLLBACK 和 SAVEPOINT 是事务控制语言。COMMIT 将数据更改永久应用到数据库，而 ROLLBACK 执行反提交操作。SAVEPOINT 通过在不同事务阶段设置标记来控制事务的系列。用户可以将当前事务回滚到之前设置的所需保存点。

**COMMIT** - Commit 通过将数据更改永久应用到数据库表来结束当前活动事务。COMMIT 是显式结束事务的 TCL 命令。但是，DDL 和 DCL 命令会隐式提交事务。

**SAVEPOINT** - 保存点用于标记会话中当前事务中的特定点。因为它是在事务中的逻辑标记，所以无法在数据字典中查询保存点。

**ROLLBACK** - ROLLBACK 命令用于通过丢弃数据更改来结束整个事务。如果事务包含标记的保存点，则可以使用 ROLLBACK TO SAVEPOINT [name] 将事务回滚到指定的保存点。结果，直到指定保存点的所有数据更改都将被丢弃。

演示

考虑 EMPLOYEES 表，该表每年第一季度都会填充新聘用员工的详细信息。文员员工使用保存点附加每个员工的详细信息，以便在数据馈送活动期间随时回滚任何错误数据。请注意，他保留了与员工姓名相同的保存点名称。

INSERT INTO employees (employee_id, first_name, hire_date, job_id, salary, department_id)
VALUES (105, 'Allen',TO_DATE ('15-JAN-2013','SALES',10000,10);

SAVEPOINT Allen;

INSERT INTO employees (employee_id, first_name, hire_date, job_id, salary, department_id)
VALUES (106, 'Kate',TO_DATE ('15-JAN-2013','PROD',10000,20);

SAVEPOINT Kate;

INSERT INTO employees (employee_id, first_name, hire_date, job_id, salary, department_id)
VALUES (107, 'McMan',TO_DATE ('15-JAN-2013','ADMIN',12000,30);

SAVEPOINT McMan;

假设，数据馈送操作员意识到他错误地输入了“Kate”和“McMan”的薪水。他将活动事务回滚到保存点 Kate，然后重新输入 Kate 和 McMan 的员工详细信息。

ROLLBACK TO SAVEPOINT Kate;

INSERT INTO employees (employee_id, first_name, hire_date, job_id, salary, department_id)
VALUES (106, 'Kate',TO_DATE ('15-JAN-2013','PROD',12500,20);

SAVEPOINT Kate;

INSERT INTO employees (employee_id, first_name, hire_date, job_id, salary, department_id)
VALUES (107, 'McMan',TO_DATE ('15-JAN-2013','ADMIN',13200,30);

SAVEPOINT McMan;

完成数据输入后，他可以通过在当前会话中发出 COMMIT 来提交整个事务。

读取一致性

Oracle 在每个会话中保持用户之间在数据访问和读/写操作方面的一致性。

当对表执行 DML 时，操作更改的原始数据值将记录在数据库撤销记录中。只要事务未提交到数据库，任何稍后查询修改数据的其他会话中的用户都会查看原始数据值。Oracle 使用系统全局区域中的当前信息和撤销记录中的信息来构建查询的表的读取一致性视图。只有当事务提交时，事务的更改才会永久保存。事务是 Oracle 提供读取一致性策略的关键。

读取一致性视图的起点是代表读取器生成的

控制其他数据库事务何时可以看到修改后的数据以进行读取或更新

打印页面
上一页
下一页
广告