SQL

SQL简介

SQL (发音为sequel) 是基于集合的高级别声明性计算机语言，所有程序和用户都使用它来访问 Oracle 数据库中的数据。虽然某些 Oracle 工具和应用程序掩盖了对SQL的使用，但实际上所有的数据库操作都是使用 SQL 执行的。任何其他数据访问方法，绕过了内置于数据库的安全机制，并可能危及数据安全和完整性。

SQL 提供一个对关系数据库如 Oracle 数据库的接口。SQL 以一种一致的语言统一了以下任务：

 创建、 替换、 改变、和删除对象

 插入、 更新、和删除表行

 查询数据

 控制对数据库及其对象的访问

 保证数据库的一致性和完整性

SQL 可以交互地使用，即将语句手动地输入到程序。SQL 语句也可以嵌入到用不同的语言（如 C 或 Java）编写的程序内。

SQL数据访问

有两大计算机语言派别： 非过程化的声明性语言，它描述应该做什么，和过程化语言，如 C + + 和 Java，它描述如何做。SQL 是声明性的，即是说用户可以指定他们想要的结果，而不必说明如何得到它。SQL 语言编译器的工作是，生成一个过程，以操作数据库，并执行所需的任务。

SQL 使您能够在逻辑层面使用数据。仅当您想要处理该数据时，您才需要关注其实现细节。例如，下面的语句查询姓氏以K开头的雇员记录

SELECT last_name, first_name
FROM hr.employees
WHERE last_name LIKE 'K%'
ORDER BY last_name, first_name;

数据库用一个单一步骤检索满足WHERE 条件（也称“谓词”）的所有行。这些行可以作为一个整体传递给用户、另一个 SQL 语句、或应用程序。您不需要一行一行地处理，您也不需要知道行是如何被物理存储和检索的。

所有 SQL 语句都使用优化器，作为Oracle 数据库的一部分，它会确定访问指定数据的最有效方式。Oracle 数据库还支持其它多种技术，可以使优化器更好地执行其工作。

SQL语句概述

在Oracle 数据库中的信息上执行的所有操作都是使用 SQL 语句来执行的。SQL 语句是一种计算机程序或指令，它包含标识符、 参数、 变量、 名称、 数据类型、和 SQL 的保留字。

注意：

SQL 保留字在 SQL 中具有特殊的意义，且不用于任何其他目的。例如，SELECT和UPDATE是保留字，不能用作表的名称。

SQL 语句必须等价于一个完整的 SQL 句子，例如：

SELECT last_name, department_id FROM employees

Oracle 数据库仅运行完整的SQL 语句。像下面这样的片段将生成一个错误，指示需要更多的文本：

SELECT last_name;

数据定义语言 (DDL) 语句

数据定义语言 (DDL) 语句定义、更改、和删除模式对象。例如，DDL 语句使您能够：

 创建、 更改、和删除模式对象和其他数据库结构，包括数据库本身和数据库用户。大部分的 DDL 语句以关键字CREATE、 ALTER、或 DROP开头。

 删除模式对象中的所有数据，而不删除这些对象的结构（TRUNCATE）。

注意：

与DELETE不同， TRUNCATE将不生成回滚数据，这使它比DELETE更快。此外，TRUNCATE不会调用删除触发器。

 授予和撤消权限和角色 （GRANT, REVOKE）。

 打开和关闭审核选项 （AUDIT , NOAUDIT） 。

 将注释添加到数据字典 （COMMENT）。

DDL 使您能够更改对象的属性，而无需更改访问该对象的应用程序。例如，可以将某个列添加到由人力资源应用程序访问的一个表，而无需重写应用程序。您还可以在当数据库用户正在数据库中执行工作时，使用 DDL 更改对象的结构。

示例7-1 使用 DDL 语句来创建plants表，然后使用DML在该表中插入两行。然后，该示例使用 DDL 更改表结构、 授予和撤消一个用户在该表上的权限，然后删除表。

示例 7-1 DDL 语句

CREATE TABLE plants
( plant_id NUMBER PRIMARY KEY,
common_name VARCHAR2(15) );
INSERT INTO plants VALUES (1, 'African Violet'); # DML statement
INSERT INTO plants VALUES (2, 'Amaryllis'); # DML statement
ALTER TABLE plants ADD
( latin_name VARCHAR2(40) );
GRANT SELECT ON plants TO scott;
REVOKE SELECT ON plants FROM scott;
DROP TABLE plants;

在数据库执行 DDL 语句之前会立即执行一个隐式提交，之后立即执行一个 提交或回滚。在示例 7-1中，两个 INSERT 语句后面跟了一个 ALTER TABLE 语句，因此数据库会提交这两个 INSERT 语句。如果 ALTER TABLE 语句成功，则数据库提交此语句 ；否则，数据库回滚此语句。在任一情况下，两个 INSERT 语句都是已经提交的。

数据操作语言 (DML) 语句

数据操纵语言 (DML) 语句查询或操作现有模式对象中的数据。DDL 语句使您可以更改数据库的结构，而 DML 语句使您能够查询或更改其内容。例如，ALTER TABLE更改一个的表的结构，而 INSERT向表中添加一个或多个行。

DML 语句是最经常使用的 SQL 语句，并使您能够：

 从一个或多个表或视图，检索或获取数据（SELECT）。

 通过指定一个列值列表，或使用一个子查询，来选择和操作现有数据，以将新的数据行添加到表中（INSERT）。

 更改表或视图中的现有行中的值（UPDATE）。

 更新或有条件地将行插入到表或视图 （MERGE）。

 从表或视图中删除行（DELETE）。

 查看SQL 语句的执行计划（EXPLAIN PLAN）。

 锁定一个表或视图，临时限制其他用户的访问（LOCK TABLE）。

下面的示例使用DML来查询employees表。该示例使用 DML 往employees表中插入一行、 又更新此行、然后将其删除：

SELECT * FROM employees;
INSERT INTO employees (employee_id, last_name, email, job_id, hire_date, salary)
VALUES (1234, 'Mascis', 'JMASCIS', 'IT_PROG', '14-FEB-2008', 9000);
UPDATE employees SET salary=9100 WHERE employee_id=1234;
DELETE FROM employees WHERE employee_id=1234;

形成一个逻辑工作单元的DML 语句的集合，称为一个事务。例如，一个转账事务可能涉及三个独立的操作： 减少储蓄帐户余额、 增加支票帐户余额、和在一个帐户历史记录表中记录转账日志。与 DDL 的语句不同的是， DML 语句不会隐式提交当前事务。

SELECT语句

查询是一个从表或视图中检索数据的操作。SELECT是你唯一可用于查询数据的 SQL 语句。从执行 SELECT 语句检索到的数据集被称为结果集。

表 7-1 显示了在一个 SELECT 语句中两个必需的关键字，和两个常见的关键字。该表也说明了这些关键字在SELECT 语句中的功能。

连接

连接是合并两个或多个表、 视图、或物化视图中的行的查询。示例 7-2 将employees表与departments表联接(FROM 子句），只选择满足指定条件的行（WHERE 子句），并使用投影从两个列中检索数据（SELECT）。该SQL 语句的示例输出如下所示。

示例7-2 连接的例子

SELECT email, department_name
FROM employees JOIN departments
ON employees.department_id = departments.department_id
WHERE employee_id IN (100,103)
ORDER BY email;
EMAIL DEPARTMENT_NAME
------------------------- ------------------------------
AHUNOLD IT
SKING Executive

大多数连接都至少有一个连接条件，要么在 FROM 子句中，要么在 WHERE 子句中，比较来自不同表的两个列。数据库合并行对，每个行对包含来自不同表、且连接条件计算结果为 TRUE 的一行。基于连接条件、 索引、和任何可用的表统计信息，由优化器确定数据库中联接表的顺序。

联接类型包括：

 内连接

内连接是两个或更多表的联接，只返回满足连接条件的行。例如，如果连接条件是employees.department_id = departments.department_id ，则不满足此条件的行不被返回。

 外连接

外连接返回所有满足联接条件的行，也返回一个表中不满足与另一个表的连接条件的行。例如，雇员表和部门表的左外连接，会检索雇员表中的所有行，即使在部门表中没有相应匹配的部门。右外连接会检索部门表中的所有行，即使在雇员表中没有匹配的雇员。

 笛卡儿积

如果两个表联接查询中的没有联接条件，则数据库返回其笛卡儿积。一个表中的每一行与另一个表中的每一行联合。例如，如果employees表有 107 行，而departments表有 27行，则其笛卡儿积包含 107 x 27 行。笛卡儿积是很少用到的。

子查询和隐式查询

子查询是嵌套在另一个 SQL 语句中的 SELECT 语句。在您必须执行多个查询以解决一个问题时，子查询非常有用。

一个语句每个查询部分称为一个查询块。在示例 7-3 中，括号内的子查询是内部查询块。内部的 SELECT 语句检索位置 ID为 1800 的部门 id。这些部门 id 需要在外部查询块中用到，外部查询基于子查询提供的部门ID来检索员工姓名。

示例 7-3 子查询

SELECT first_name, last_name
FROM employees
WHERE department_id
IN (SELECT department_id FROM departments WHERE location_id = 1800);

SQL 语句的结构不会强制数据库必须首先执行内部查询。例如，数据库可能会重写整个查询，将employees表和departments表连接，这样，子查询本身永远不会单独执行。再举一个例子，虚拟专用数据库 (VPD) 功能可能会使用一个 WHERE 子句来限制对员工的查询，这样，数据库可能决定首先查询雇员表，然后获取部门 id。由优化器确定用于检索请求行的最佳步骤序列。

隐式查询是DML 语句的一部分，它并不使用子查询来检索数据。一个没有显式包含SELECT 语句的更新、 删除、或合并语句，会使用一个隐式查询来检索要修改的行。例如，下面的语句包括一个对Baer 记录的隐式查询：

UPDATE employees
SET salary = salary*1.1
WHERE last_name = 'Baer';

唯一不需要包括一个查询组件的 DML 语句，是带 VALUES 子句的INSERT 语句。例如，INSERT INTO TABLE mytable VALUES (1)语句在插入行之前不会检索行。

事务控制语句

事务控制语句管理 DML 语句所做的更改，和将多个DML 语句按事务分组。这些语句使您能够：

 使一个事务的更改持久化（COMMIT）。

 撤消在一个事务中自事务开始以来的更改（ROLLBACK），或自一个保存点以来的更改 （ROLLBACK TO SAVEPOINT）。保存点是在一个事务上下文中由用户声明的中间标记。

注意：

ROLLBACK命令会结束一个事务，但ROLLBACK TO SAVEPOINT不会。

 设置一个你可以回滚到的点（SAVEPOINT）。

 设定一个事务的属性（SET TRANSACTION）。

 指定是在每个 DML 语句之后、还是在事务时提交后，执行可延迟完整性约束检查（SET CONSTRAINT）。

下面的示例启动一个名为Update salaries的事务。该示例创建一个保存点，更新一个雇员的工资，然后回滚事务到保存点。该示例又更新一个不同的薪金值，然后提交。

SET TRANSACTION NAME 'Update salaries';
SAVEPOINT before_salary_update;
UPDATE employees SET salary=9100 WHERE employee_id=1234 # DML
ROLLBACK TO SAVEPOINT before_salary_update;
UPDATE employees SET salary=9200 WHERE employee_id=1234 # DML
COMMIT COMMENT 'Updated salaries';

会话控制语句

会话控制语句动态地管理用户会话的属性。会话是数据库实例内存中的一个逻辑实体，表示登录到数据库中的一个当前用户的状态。会话从该用户通过数据库验证开始，一直持续到用户断开连接或退出数据库应用程序。

会话控制语句使您能够：

 通过执行一项专门的功能来改变当前会话，如启用和禁用 SQL 跟踪 （ALTER SESSION）。

 启用和禁用角色，它是一组用于当前会话的权限（SET ROLE） 。

下面的示例为当前会话打开 SQL 跟踪，然后在当前会话中启用除 dw_manager 之外的所有已授予的角色：

alter session set sql_trace = true;
set role all except dw_manager;

会话控制语句不会隐式提交当前事务。

系统控制语句

系统控制语句更改数据库实例的属性。唯一的系统控制语句是ALTER SYSTEM。它使您能够更改系统设置，例如，共享服务器的最小数目、 终止一个会话、和执行其他系统级任务。

下面是系统控制语句的示例：

alter system switch logfile;
alter system kill session '11, 11';

ALTER SYSTEM语句不会隐式提交当前事务。

嵌入式的 SQL 语句

嵌入式的 SQL 语句将DDL、 DML 、和事务控制语句混入过程化语言程序中。他们和Oracle 预编译器一起使用。嵌入式的 SQL 是一种在您的过程化语言应用程序中纳入SQL的方法。另一种方法是使用一个程序 API，如开放式数据库连接 (ODBC)或Java 数据库连接 (JDBC) 。

嵌入式的 SQL 语句，使您可以：

 定义、 分配、和释放游标 （DECLARE CURSOR, OPEN, CLOSE）。

 指定一个数据库，并连接到该数据库（DECLARE DATABASE, CONNECT）。

 分配变量名称 （DECLARE STATEMENT）。

 初始化描述符(DESCRIBE)

 指定如何处理错误和警告(WHENEVER)。

 分析并运行 SQL 语句 （PREPARE, EXECUTE, EXECUTE IMMEDIATE）。

 从数据库中检索数据（FETCH）。