Sql Certificate 简明教程

SQL - Using Manipulating Data

Oracle 提供数据操作语言命令来执行数据库中的数据操作。数据操作可以随时使用应用程序或业务数据填充数据库表、修改数据以及从数据库中删除数据。除了数据操作之外，还有一组用于控制这些操作的命令。这些命令被归类为事务控制语言。

Oracle provide Data Manipulation Language commands to exercise data operations in the database.Data operations can be populating the database tables with the application or business data,modifying the data and removing the data from the database,whenever required. Besides the data operations,there are set of commands which are used to control these operations.These commands are grouped as Transaction Control Language.

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

There are three types of DML statements involved in a logical SQL transaction namely, Insert, Update, Delete and Merge.A transaction is the logical collection of DML actions within a database session.

INSERT statement

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

The INSERT command is used to store data in tables. The INSERT command is often used in higher-level programming languages such as Visual Basic.NET or C++ as an embedded SQL command; however,this command can also be executed at the SQL*PLUS prompt in command mode.There are two different forms of the INSERT command. The first form is used if a new row will have a value inserted into each column of the row. The second form of the INSERT command is used to insert rows where some of the column data is unknown or defaulted from another business logic.This form of the INSERT command requires that you specify column names for which data are being stored.

Syntax:

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

The below syntax can be followed if the values for all the columns in the table is definite and known.

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

如果只要表中的一些列需要填充某个值，则可以使用下面的语法。其余的列可以推断出它们的值为NULL或根据不同的业务逻辑推断出它们的值。

The below syntax can be used if only few columns from the table have to be populated with a value. Rest of the columns can deduce their values either as NULL or from a different business logic.

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

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

The INSERT statement below creates a new employee record in the EMPLOYEES table. Note that it inserts the values for the primary columns EMPLOYEE_ID, FIRST_NAME, SALARY and DEPARTMENT_ID.

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

否则，可以不使用列列表在EMPLOYEES表中插入完整的雇员数据，前提是事先知道这些值并且这些值必须遵守表中列的数据类型和位置。

Otherwise, complete employee data can be inserted in the EMPLOYEES table without specifying the column list using the below INSERT statement - provided the values are known beforehand and must be in compliance with the data type and position of columns in the table.

INSERT INTO employees
VALUES (130, 'KEMP','GARNER', 'kemp.garner@xxx.com', '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世纪计算逻辑。

Values to be inserted must be compatible with the data type of the column. Literals, fixed values and special values like functions, SYSDATE, CURRENT_DATE, SEQ.CURRVAL (NEXTVAL), or USER can be used as column values. Values specified must follow the generic rules. String literals and date values must be enclosed within quotes. Date value can be supplied in DD-MON-RR or D-MON-YYYY format, but YYYY is preferred since it clearly specifies the century and does not depend on internal RR century calculation logic.

INSERT-AS-SELECT (IAS) statement

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

Data can be populated into the target table from the source table using INSERT..AS..SELECT (IAS) operation. Its a direct path read operation.Its a simple way of creating copy of the data from one table to another or creating a backup copy of the table which the source table operations are online.

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

For example, data can be copied from EMPLOYEES table to EMP_HISTORY table.

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

UPDATE statement

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

The UPDATE command modifies the data stored in a column.It can update single or multiple rows at a time depending on the result set filtered by conditions specified in WHERE clause. Note that Updating columns is different from altering columns. Earlier in this chapter, you studied the ALTER command.The ALTER command changes the table structure, but leaves the table data unaffected.The UPDATE command changes data in the table, not the table structure.

Syntax:

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

从语法中：

From the syntax,

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

The SET column = expression can be any combination of characters, formulas, or functions that will update data in the specified column name.The WHERE clause is optional, but if it is included, it specifies which rows will be updated.Only one table can be updated at a time with an UPDATE command.

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

The UPDATE statement below updates the salary of employee JOHN to 5000.

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

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

Though WHERE predicates are optional, but must be logically appended so as to modify only the required row in the table. The UPDATE statement below updates the salaries of all the employees in the table.

UPDATE employees
SET salary = 5000;

还可以通过在SET子句中使用多个逗号分隔的列来更新多列。例如，如果必须将约翰的薪水和工作角色分别更改为5000和SALES，则UPDATE语句如下所示：

Multiple columns can also be updated by specifying multiple columns in SET clause separated by a comma. For example, if both salary and job role has to be changed to 5000 and SALES respectively for JOHN, the UPDATE statement looks like,

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

1 row updated.

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

Another way of updating multiple columns of the same row shows the usage of subquery.

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

DELETE statement

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

The DELETE command is one of the simplest of the SQL statements. It removes one or more rows from a table. Multiple table delete operations are not allowed in SQL.The syntax of the DELETE command is as below.

DELETE FROM table_name
    [WHERE condition];

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

The DELETE command deletes all rows in the table that satisfy the condition in the optional WHERE clause. Since the WHERE clause is optional, one can easily delete all rows from a table by omitting a WHERE clause since the WHERE clause limits the scope of the DELETE operation.

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

The below DELETE statement would remove EDWIN’s details from EMP table.

DELETE employees
WHERE UPPER (ENAME) = 'EDWIN'

1 row deleted.

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

Note: DELETE [TABLE NAME] and DELETE FROM [TABLE NAME] hold the same meaning.

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

The WHERE condition in the conditional delete statements can make use of subquery as shown below.

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

TRUNCATE

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

Truncate is a DDL command which is used to flush out all records from a table but retaining the table structure. It does not supports WHERE condition to remove the selected records.

Syntax:

TRUNCATE [table name]

它是自动提交，即提交会话中的当前活动事务。截断表不会删除从属索引、触发器或表约束。如果表A在数据库中是表B的引用约束的父级，那么表A将不能被截断。

It is Auto Commit i.e. it commits the current active transaction in the session. Truncating the table does not drops dependent indexes, triggers or table constraints. If a table A is parent of a reference constraint of a table B in the database, the table A could not be truncated.

Transaction

数据库中的一个事务是一个逻辑工作单元。它可以包含以下内容 -

A transaction is a logical unit of work done in database. It can either contain -

Multiple DML commands ending with a TCL command i.e. COMMIT or ROLLBACK
One DDL command
One DCL command

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

Beginning of a transaction is marked with the first DML command. It ends with a TCL, DDL or DCL command. A TCL command i.e. COMMIT or ROLLBACK is issues explicitly to end an active transaction. By virtue of their basic behavior, if any of DDL or DCL commands get executed in a database session, commit the ongoing active transaction in the session. If the database instance crashes abnormally, the transaction is stopped.

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

COMMIT, ROLLBACK and SAVEPOINT are the transaction control language. COMMIT applies the data changes permanently into the database while ROLLBACK does anti-commit operation. SAVEPOINT controls the series of a transaction by setting markers at different transaction stages. User can roll back the current transaction to the desired save point, which was set earlier.

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

COMMIT - Commit ends the current active transaction by applying the data changes permanently into the database tables. COMMIT is a TCL command which explicitly ends the transaction. However, the DDL and DCL command implicitly commit the transaction.

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

SAVEPOINT - Savepoint is used to mark a specific point in the current transaction in the session. Since it is logical marker in the transaction, savepoints cannot be queried in the data dictionaries.

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

ROLLBACK - The ROLLBACK command is used to end the entire transaction by discarding the data changes. If the transaction contains marked savepoints, ROLLBACK TO SAVEPOINT [name] can be used to rollback the transaction upto the specified savepoint only. As a result, all the data changes upto the specified savepoint will be discarded.

Demonstration

考虑 EMPLOYEES 表，其在每年的第一季度都会填充新雇员的详细信息。事务员用保存点追加每个雇员的详细信息，以便在数据输入活动期间随时回滚任何错误数据。请注意，他将保存点名称保留与雇员姓名相同。

Consider the EMPLOYEES table which gets populated with newly hired employee details during first quarter of every year. The clerical staff appends each employee detail with a savepoint, so as to rollback any faulty data at any moment during the data feeding activity. Note that he keeps the savepoint names same as the employee names.

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 的员工详细信息。

Suppose, the data feeding operator realises that he has wrongly entered the salary of 'Kate' and 'McMan'. He rolls back the active transaction to the savepoint Kate and re-enters the employee details for Kate and 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 来提交整个事务。

Once he is done with the data entry, he can commit the entire transaction by issuing COMMIT in the current session.

Read Consistency

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

Oracle maintains consistency among the users in each session in terms of data access and read/write actions.

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

When a DML occurs on a table, the original data values changed by the action are recorded in the database undo records. As long as transaction is not committed into database, any user in other session that later queries the modified data views the original data values. Oracle uses current information in the system global area and information in the undo records to construct a read-consistent view of a table’s data for a query. Only when a transaction is committed, the changes of the transaction made permanent. The transaction is the key to Oracle’s strategy for providing read consistency.

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

Start point for read-consistent views is generated on behalf of readers

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

Controls when modified data can be seen by other transactions of the database for reading or updating