Oracle数据库SQL概述

Oracle数据库SQL概述

SQL 简介

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

SQL作用

SQL 提供一个对关系数据库如 Oracle 数据库的接口。SQL 以一种一致的语言统一完成以下任务：
1、创建、 替换、 改变、和删除对象
2、插入、 更新、和删除表行
3、查询数据
4、控制对数据库及其对象的访问
5、保证数据库的一致性和完整性

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

SQL 数据访问

计算机语言派别分为两种： 1）非过程化的声明性语言，它描述应该做什么；2）过程化语言，如 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语句不需要描述一行一行地处理，也不需要知道行是如何被物理存储和检索的。因此，可以简单理解，SQL是以集合的方式操作数据。
所有 SQL 语句都会使用优化器，优化器作为 Oracle 数据库的一部分，它会确定访问指定数据的最有效方式。Oracle 数据库还支持其它多种技术，可以使优化器更好地执行其工作。

SQL 语句概述

在 Oracle 数据库中数据的所有操作都是使用 SQL 语句来执行的。SQL 语句是一种计算机程序或指令，它包含标识符、 参数、 变量、 名称、数据类型、和 SQL 的保留字。
注意：
SQL 保留字在 SQL 中具有特殊的意义，且不用于任何其他目的。例如，SELECT 和 UPDATE 是保留字，不能用作表的名称。

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

SELECT last_name, department_id FROM employees SELECT last_name, department_id FROM employees

SQL 语句分类

1、数据定义 (DDL) 语句
2、数据操作 (DML) 语句，注意"数据查询语句"，包含在数据操作语句中
3、事务控制语句
4、会话控制语句
5、系统控制语句
6、嵌入式SQL语句

数据定义语言 (DDL) 语句

数据定义 (DDL) 语句定义、更改、和删除模式对象。

数据定义 (DDL) 语句功能主要包括如下：
1、创建、 更改、和删除模式对象和其他数据库结构，包括数据库本身和数据库用户。大部分的 DDL 语句以关键字 CREATE、 ALTER、或 DROP 开头。
2、删除模式对象中的所有数据，而不删除这些对象的结构（TRUNCATE）。
注意：与 DELETE 不同， TRUNCATE 将不生成回滚数据，这使它比DELETE 更快。此外，TRUNCATE 不会调用删除触发器。
3、授予和撤消权限和角色 （GRANT, REVOKE）。
4、打开和关闭审核选项 （AUDIT , NOAUDIT） 。
5、将注释添加到数据字典 （COMMENT）。

DDL 能够更改对象的属性，而无需更改访问该对象的应用程序。
**在数据库执行 DDL 语句之前会立即执行一个隐式提交，之后立即执行一个提交或回滚。**这一点很重要！

数据操作 (DML) 语句

数据操纵(DML) 语句查询或操作现有模式对象中的数据。DDL 语句可以更改数据库的结构，而 DML 语句能够查询或更改其内容。

DML 语句是最经常使用的 SQL 语句，主要包括如下：
1、从一个或多个表或视图，检索或获取数据（SELECT）。
2、通过指定一个列值列表，或使用一个子查询，来选择和操作现有数据，以将新的数据行添加到表中（INSERT）。
3、更改表或视图中的现有行中的值（UPDATE）。
4、更新或有条件地将行插入到表或视图 （MERGE）。
5、从表或视图中删除行（DELETE）。
6、查看 SQL 语句的执行计划（EXPLAIN PLAN）。
7、锁定一个表或视图，临时限制其他用户的访问（LOCK TABLE）。

SELECT 语句

查询是一个从表或视图中检索数据的操作。SELECT 是你唯一可用于查询数据的 SQL 语句。

SELECT语句的一般语法结构

select [DISTINCT]...
from ...
where ...
group by...
having...
order by ...

SELECT语句的逻辑执行顺序

逻辑顺序	    语句
5	        SELECT [DISTINCT] <column list>                                   
1	          FROM <source object list>                            
1.1       	 FROM <left source object> <join type> JOIN <right source object> ON <on predicates>
2	         WHERE <where predicates>                              
3	         GROUP BY <group by expression(s)>                     
4	        HAVING <having predicates>                             
6	         ORDER BY <order by list>  

关键字解释

SELECT
1、必须包含的关键字
2、列出最终的查询结果集中需要显示哪些列，可以是一个实际的列、一个表达式(表达式可以是一个或多个值、操作符、和SQL函数的组合，经过计算后最终得到一个值)、或者甚至是一个SELECT语句的结果（效率低效）
3、SELECT 关键字之后 FROM 子句之前的表达式列表，叫做选择列表
4、DISTINCT用来在其他子句执行完毕后，从结果集中去除重复的行

FROM
1、必须包含的关键字
2、指定应从哪些表或视图中检索数据。这个子句可以包含表、视图、物化视图、分区、子分区，或者一个临时的子查询

WHERE
1、不是必须包含的关键字
2、提供一种可以按照条件来限制临时结果集（最终返回结果集）的行数方法
3、每个条件都是以两个值或者表达式相比较的形式出现。比较的结果要么是匹配（TRUE），要么是不匹配（FALSE），或者不知道（UNKNOW,这种情况一般是空值null比较才会出现）
4、限制或者减少结果集

GROUP BY
1、不是必须包含的关键字
2、将FROM和WHERE子句运行后得到的临时结果集进行聚合。查询出来的结果按照group by子句中列出的表达式进行分组，为每一个分组得出一行汇总结果
3、Select 列表中的任何非聚合字段都必须包含在group by表达式中

HAVING
1、不是必须包含的关键字
2、将group by子句分组汇总后的临时结果集刷选为having子句中条件为真的数据行
3、本质上，HAVING子句是在group by子句执行后用来刷选“汇总结果集”的第二个where条件（一般在where第一刷选过后、结果聚合之后，进行第二次条件刷选）

ORDER BY
1、不是必须包含的关键字
2、用来对查询的最终返回结果集进行排序（指定行应以何种顺序显示）。

INSERT插入语句

用来向表添加行
简单案例

insert into tab1 values(4,’小王’);
insert into tab1(name) values(‘无名’);

insert into tab1 select * from tab2;   //将select的查询结果，直接插入表中

UPDATE更新语句

1、改变表中原有行的列值，由UPDATE、SET、WHERE组成
2、UPDATE子句用来指定要更新的表
3、SET子句用来指明哪些列需要改变以及改变后的值
4、WHERE子句用来按条件刷选需要更新的行

简单案例

update tab1  set id=4;   --不加where刷新，更新所有行
update tab1  set name=’小蔡’  where id=1;  

DELETE删除语句

1、用来从表中删除数据行，由DELETE FROM、WHERE关键字组成
2、DELETE FROM用来指定从哪个表中删除数据行
3、WHERE子句提供刷选条件，用于确定哪些数据行需要删除

简单案例

delete  from  tab1;   ---不加where刷选条件，删除表中所有行
delete  from tabl1  where name=’张三’; 

连接

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

简单案例

将employees 表与 departments 表联接(FROM 子句），只选择满足指定条件的行（WHERE 子句），并使用投影从两个列中检索数据（SELECT）。该 SQL 语句的示例输出如下所示。
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

联接类型

内连接

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

外连接

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

笛卡儿积

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

子查询

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

简单案例

示例子查询
SELECT first_name, last_name 
FROM employees
WHERE department_id 
IN (SELECT department_id FROM departments 
WHERE location_id = 1800);
//括号内的子查询是内部查询块。内部的 SELECT 语句检索位置 ID 为 1800 的部门 id。这些部门 id 需要在外部查询块中用到，外部查询基于子查询提供的部门 ID 来检索员工姓名

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

隐式查询

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

简单案例

例如，下面的语句包括一个对 Baer 记录的隐式查询：
statement includes an implicit query for the Baer record:
UPDATE employees 
 SET salary = salary*1.1 
 WHERE last_name = 'Baer';

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

事务控制语句

事务控制语句管理 DML 语句所做的更改，和将多个 DML 语句按事务分组。

事务控制语句功能

1、使一个事务的更改持久化（COMMIT）。
2、撤消在一个事务中自事务开始以来的更改（ROLLBACK），或自一个保存点以来的更改 （ROLLBACK TO SAVEPOINT）。保存点是在一个事务上下文中由用户声明的中间标记。注意：ROLLBACK 命令会结束一个事务，但 ROLLBACK TO SAVEPOINT 不会。
3、设置一个你可以回滚到的点（SAVEPOINT）。
4、设定一个事务的属性（SET TRANSACTION）。
5、指定是在每个 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';

会话控制语句

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

会话控制语句功能

1、通过执行一项专门的功能来改变当前会话，如启用和禁用 SQL 跟踪（ALTER SESSION）。
2、启用和禁用角色，它是一组用于当前会话的权限（SET ROLE） 。

简单案例

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

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

系统控制语句

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

简单案例

下面是系统控制语句的示例：
ALTER SYSTEM SWITCH LOGFILE; 
ALTER SYSTEM KILL SESSION '39, 23';

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

嵌入式的 SQL 语句

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

嵌入式的 SQL 语句功能

1、定义、 分配、和释放游标 （DECLARE CURSOR, OPEN, CLOSE）。
2、指定一个数据库，并连接到该数据库（DECLARE DATABASE, CONNECT）。
3、分配变量名称 （DECLARE STATEMENT）。
4、初始化描述符(DESCRIBE)
5、指定如何处理错误和警告(WHENEVER)。
6、分析并运行 SQL 语句 （PREPARE, EXECUTE, EXECUTE IMMEDIATE). IMMEDIATE）。
7、从数据库中检索数据（FETCH）。