oracle_sql 基础 （2）

+++++++++++++++++++++++++++++++
从多表中查询数据

当一个连接条件无效或被遗漏时，其结果是一个笛卡尔乘积 (Cartesian product)，其中所有行的组合都被显示。第一个表中的所有行连接到第二个表中的所有行。
一个笛卡尔乘积会产生大量的行，其结果没有什么用。你应该在 WHERE 子句中始终包含一个有效的连接条件，除非你有特殊的需求，需要从所有表中组合所有的行。
对于一些测试笛卡尔乘积是有用的，例如你需要产生大量的行来模拟一个相当大的数据量。

ORACLE SQL的连接类型有下面几种：EQUJION等值连接，NON-EQUJION 非等值连接，Outer join 外连接，Self join 自连接。外连接分为：左外连接，右外连接，全外连接。
在写一个连接表的 SELECT 语句时，在列名前面用表名可以使语义清楚，并且加快数据库访问。如果相同的列名出现在多个表中，列名必须前缀表名。
为了连接 n 个表在一起，你最少需要 n-1 个连接条件。例如，为了连接 4 个表，最少需要 3 个连接条件。如果表中有一个连接主键，该规则可能不适用，其中可能有多行用来唯一地标识每一行。

使用表前缀可以改善性能，因为你确切地告诉 Oracle 服务器在那里找到列。
表别名有助于保持 SQL 代码较小，因此使用的存储器也少。
一个非等值连接是一种不同于等值操作的连接条件。
Select e.Last_Name, e.Salary, j.Job_Title
From Employees e, Jobs j
Where e.Salary Between j.Min_Salary And j.Max_Salary;

普通的连接被称为内连接。
内连接的特点是如果一个行不满足连接条件，该行将不出现在查询结果中。
使用外连接，不满足连接条件的行也会在输出结果中。

自连接
自连接就是连接的两个表均来自于同一个列
自连接是一种使用很少的连接形式
自连接可以是等值或非等值的连接
一个自连接示例：
Select w.Last_Name || ' works for ' || m.Last_Name From Employees w, Employees m Where w.Manager_Id = m.Employee_Id;

交叉连接：
Select Last_Name, Department_Name From Employees Cross Join Departments;
等价于笛卡儿积。

自然连接：
Select Department_Id, Department_Name, Location_Id, City
From Departments Natural
Join Locations;
等价于等值连接，前提是两个表有相同名称的列。如果没有，则为交叉连接。
如果列名称相同，但是列类型不同，会报错。可以使用USING子句来处理这种情况。自然连接(Natural joins) 用具有相匹配的名字和数据类型的所有列来连接表。
USING 子句可以被用来指定那些将被用语一个等值连接的列中的唯一列。在USING 子句中引用的列不应该在SQL 语句的任何地方用表名或表别名限制(前缀)。
如下：
Select l.City, d.Department_Name
From Locations l
Join Departments d
Using (Location_Id)
Where Location_Id = 1400;
对于自然连接的连接条件，基本上是带有相同名字的所有列的等值连接，为了指定任意条件，或者指定要连接的列，可以使用ON 子句连接条件从另一个搜索条件中被分开，ON 子句使得代码易懂。
Select e.Employee_Id,
e.Last_Name,
e.Department_Id,
d.Department_Id,
d.Location_Id
From Employees e
Join Departments d On (e.Department_Id = d.Department_Id);

+++++++++++++++++++++++++++++++++++++++
用组函数合计数据

用GROUP BY 子句分组数据，用HAVING 子句包含或排除分组的行。
使用组函数的原则：
DISTINCT 使得函数只考虑不重复的值；ALL 使得函数考虑每个值，包括重复值。默认值是 ALL ，因此不需要指定。
用于函数的参数的数据类型可以是 CHAR、VARCHAR2、NUMBER 或 DATE。
所有组函数忽略空值。为了用一个值代替空值，用 NVL、NVL2 或 COALESCE 函数。
当使用 GROUP BY 子句时，Oracle 服务器隐式以升序排序结果集。为了覆盖该默认顺序，DESC 可以被用于 ORDER BY 子句。

AVG([distinct|all]n) 求取记录集中的平均值。

SUM([distinct|all] col) 取指定列所在分组序列的值的和并返回

MAX (col) OVER (analytic_clause) 获取分组序列中的最大值。
MIN (col) OVER (analytic_clause) 获取分组序列中的最小值。

COUNT([distinct|all]col) 该函数返回查询涉及到的记录行数。COUNT 函数有三中格式： COUNT(*)、COUNT(expr)、COUNT(DISTINCT expr)
COUNT(*) 返回表中满足 SELECT 语句标准的行数，包括重复行，包括有空值列的行。如果 WHERE 子句包括在 SELECT 语句中，COUNT(*) 返回满足 WHERE 子句条件的行数。
对比，COUNT(expr) 返回在列中的由 expr 指定的非空值的数。COUNT (DISTINCT expr) 返回在列中的由 expr 指定的唯一的非空值的数。

COUNT(DISTINCT expr) 返回对于表达式expr 非空并且值不相同的行数，使用 DISTINCT 关键字禁止计算在一列中的重复值。

所有组函数忽略列中的空值。在下面的例子中，平均值只基于表中的那些COMMISSION_PCT 列的值有效的行的计算。
NVL 函数强制组函数包括空值。在例子中，平均值被基于所有表中的行来计算，不管 COMMISSION_PCT 列是否为空。

所有组函数是将表作为一个大的信息组进行处理，有时，你需要将表的信息划分为较小的组，可以用GROUP BY 子句实现。
你可以用 GROUP BY 子句把表中的行划分为组。然后你可以用组函数返回每一组的摘要信息。
原则
如果在 SELECT 子句中包含了组函数，就不能选择单独的结果，除非单独的列出现在 GROUP BY 子句中。如果你未能在 GROUP BY 子句中包含一个字段列表，你会收到一个错误信息。
使用 WHERE 子句，你可以在划分行成组以前过滤行。
在 GROUP BY 子句中必须包含列。
在 GROUP BY 子句中你不能用列别名。
默认情况下，行以包含在 GROUP BY 列表中的字段的升序排序。你可以用 ORDER BY 子句覆盖这个默认值。
当使用 GROUP BY 子句时，确保在 SELECT 列表中的所有没有包括在组函数中的列必须在 GROUP BY 子句中。
分组结果被以分组列隐式排序，可以用ORDER BY 指定不同的排序顺序，但只能用组函数或分组列。
GROUP BY 列不必在 SELECT 子句中。分组可以包含多个列。
在SELECT 列表中的任何列或表达式（非计算列）必须在GROUP BY 子句中，在GROUP BY 子句中的列或表达式不必在SELECT 列表中。
不能使用WHERE 子句来约束分组，可以使用HAVING 子句来约束分组结果，在WHERE 子句中不能使用组函数作为条件，只能用非计算列

HAVING 子句
约束分组结果: HAVING 子句，行被分组，应用组函数，匹配HAVING 子句的组被显示。使用 HAVING 子句指定哪个组将被显示，并且进一步基于聚集信息约束分组。
 group_condition 限制行分组，返回那些指定条件为 true 的组
当你使用 HAVING 子句时，Oracle 服务器执行下面的步骤：
1. 行被分组。
2. 组函数被用于分组。
3. 匹配 HAVING 子句的标准的组被显示。
HAVING 子句可以优先于 GROUP BY 子句，但建议你先用 GROUP BY 子句，因为这样更合逻辑。在 HAVING 子句被用于 SELECT 列表的组之前，分组已形成，并且组函数已被计算。

+++++++++++++++++++++++++++++++++++++++++++++
子查询
使用子查询你可以用简单的语句构建功能强大的语句。当你需要从表中用依赖于表本身的数据选择行时它们是非常有用的。
可以将子查询放在许多的 SQL 子句中，包括：
WHERE 子句，HAVING 子句，FROM 子句，CREATE VIEW 语句中、CREATE TABLE 语句、UPDATE 语句、INSERT 语句的 INTO 子句和 UPDATE 语句的 SET 子句中。
一个子查询必须放在圆括号中。
将子查询放在比较条件的右边以增加可读性。
Oracle 服务器没有强制限制子查询的数目；限制只与查询所需的缓冲区大小有关。

子查询分类
单行子查询：从内 SELECT 语句只返回一行的查询
多行子查询：从内 SELECT 语句返回多行的查询
多列子查询：从内SELECTY语句返回多列(单行、多行)的查询。

带子查询的HAVING 子句示例：
Select Department_Id, Min(Salary)
From Employees
Group By Department_Id
Having Min(Salary) > (Select Min(Salary)
From Employees
Where Department_Id = 50);

使用子查询的一个常见的错误是单行子查询返回了多行。如下例子：
Select Employee_Id, Last_Name
From Employees
Where Salary = (Select Min(Salary) From Employees Group By Department_Id);
如果是普通的查询，为了查询出结果可以将“＝”改为“IN”
如果是系统报错，就要查看原因了。
通常还会产生的一个错误是子查询并没有返回预期的行：
这种情况，有两种原因：第一就是子查询没有返回值，第二就是子查询返回了值，但是在外查询中无法查询到。

多行子查询返回多行，需要使用多行比较符：
IN 等于列表中的任何成员
ANY 比较子查询返回的每个值
ALL 比较子查询返回的全部值
在多行子查询中使用ANY、ALL运算符示例：
Select Employee_Id, Last_Name, Job_Id, Salary
From Employees
Where Salary < Any (Select Salary From Employees Where Job_Id = 'IT_PROG') And Job_Id <> 'IT_PROG';

多行子查询也会出现空值的现象，需要注意。

+++++++++++++++++++++++++
操作数据

每一个表都有其关联的INSERT、UPDATE和DELETE权限，这些权限被自动地授予表的创建者，但在通常情况下这些权限必须被明确地授予其它用户。

因为你能够插入一个包含每个列的新行，因此在INSERT子句中字段列表不是必须的，可是，如果你不用字段列表，值必须按照表中字段的默认顺序排列，并且必须为每个列提供一个值。
为使语句更清楚，在INSERT子句中使用字段列表。字符和日期值应放在单引号中；数字值不需要。
数字值不应放在单引号中，因为对于指定为NUMBER数据类型的字段，如果使用了单引号，可能会发生数字值的隐式转换。

INSERT的时候可能出现如下的错误：
对于NOT NULL列缺少强制的值，重复值违反了唯一性约束，违反外键约束，违反CHECK约束，数据类型不匹配，值的宽度超过了列的限制。

在插入行到表中时，你也可以用USER函数，USER函数记录当前用户名。

插入可输入变量
Insert Into Departments (Department_Id, Department_Name, Location_Id)
Values (&Department_Id, '&department_name', &Location);
在执行该语句之后，会提示我们依次输入变量的值，利用输入变量的值运行该脚本，这样一来，大家就可以多次输入不同的值来执行该语句。

可以用INSERT语句添加行到表中，插入的值来自已存在的表，在VALUES子句的位置用一个子查询。
语法：INSERT INTO table [column (, column) ] subquery;
table 是表名
column 是表中的列名
subquery 是返回行的子查询
在INSERT子句的字段列表中列的数目和它们的数据类型必须与子查询中的值的数目及其数据类型相匹配。为了创建一个表的行的拷贝，在子查询中用SELECT * 。
INSERT INTO copy_emp SELECT * FROM employees;
从另外的一个表复制分两种情况。
一是从相关的一个表或者多个表查询需要插入的数据，如下：
Insert Into Sales_Reps
(Id, Name, Salary, Commission_Pct)
Select Employee_Id, Last_Name, Salary, Commission_Pct
From Employees
Where Job_Id Like '%REP%';
第二种是从DUAL表查询特定的数据、子查询，取得插入的数据。如下：
Insert Into Departments
Select 999 'TEST_PART', (Select 400 From Dual), 4422 From Dual;

可以在INSERT语句的INTO子句中用一个子查询代替表名，该子查询的选择列表必须与VALUES子句的字段列表有相同的字段数，基表的列上的所有规则必须遵循INSERT语句的顺序。
例如，你不可能插入一个重复的employee_id，也不能遗漏强制为非空列的值。子查询用于为INSERT语句标识所要操作的表。
Insert Into
(Select Employee_Id,
Last_Name, Email, Hire_Date,
Job_Id, Salary, Department_Id
From Employees
Where Department_Id = 50)
Values
(99999, 'Taylor',
'DTAYLOR', To_Date('07-6月-1999', 'DD-MON-RR'),
'ST_CLERK', 5000, 50);

UPDATE
在一条UPDATE语句中同时更新很多值
UPDATE的语法：
UPDATE table
SET column= value[, column = value, ...]
[WHERE condition];
切记：通常，用主键标识一个单个的行，如果用其他列，可能会出乎意料的引起另一些行被更新。
例如，在EMPLOYEES表中用名字标识一个单个的行是危险的，因为不同的雇员可能有相同的名字。

用子查询更新两列
Update Employees set Job_Id = (Select Job_Id
From Employees
Where Employee_Id = 205),
Salary = (Select Salary From Employees Where Employee_Id = 205)
Where Employee_Id = 114;

更新基于另一个表的行
Update Copy_Emp set Department_Id = (Select Department_Id
From Employees
Where Employee_Id = 100)
Where Job_Id = (Select Job_Id From Employees Where Employee_Id = 200);

更新导致完整性约束报错
主键是该表中的唯一约束，不能重复，外键是该表中的字段与另外一个表的主键字段名相同的情况下设置的一种约束，外键约束不满足的时候，数据也无法更新或者插入。

delete
TRUNCATE语句是数据定义语言 (statement is a data definition language DDL) 语句，不产生回退信息，TRUNCATE语句在子查询课程中讲述。
截断表不触发表的删除触发器。
如果表是引用完整性约束的父表，你不能截断该表，在发布TRUNCATE语句之前先禁用约束。
切记：在提交DELETE之前，多看一眼，自己到底有没有加条件，条件是否是唯一约束，是否真的是要删除条件约束的数据。

删除基于另一个表的行
Delete From Employees
Where Department_Id =
(Select Department_Id
From Departments
Where Department_Name Like '%Public%');

删除数据导致的完整性约束
不能删除包含主键的行，该主键被用做另一个表的外键。
如果你尝试删除一条记录，该记录中的值依赖一个完整性约束，一个错误被返回。
如果使用了引用完整性约束，当你试图删除一行时，你可能收到一个Oracle服务器错误信息。
但是，如果引用完整性约束包含了ON DELETE CASCADE选项，那么，可以删除行，并且所有相关的子表记录都被删除。

显式默认值
Insert Into Departments
(Department_Id, Department_Name, Manager_Id)
Values
(300, 'Engineering', Default);
Update Departments Set Manager_Id = Default Where Department_Id = 10;

merge
SQL的扩展包括了MERGE语句，使用该语句，你可以有条件地更新或插入行到表中，这样能避免多重UPDATE语句。是执行对目的表的更新操作还是执行对目的表的插入操作，取决于基于ON子句中的条件。
由于MERGE命令组合了INSERT和UPDATE命令，你需要有对目的表的INSERT和UPDATE权限，以及对源表的SELECT权限。
MERGE语句确定性的。在同一个MERGE语句中，你不能多次更新目的表中相同的行。
一个可供选择近似方法是用PL/SQL循环和多重DML语句，然而，MERGE语句易于使用，并且作为一条单个的SQL语句会更简单。
MERGE语句在许多数据仓库应用中是适用的。例如，在一个数据仓库应用程序中，你可能需要用来自多个源的数据工作，其中的一些可能是完全相同的。用MERGE语句，你可以有条件地添加或修改行。
MERGE有以下特点：避免分散更新，增进性能和易用性，在数据仓库应用中有用。
MERGE的语法：
MERGE INTO table_nametable_alias
USING (table|view|sub_query) alias
ON (join condition)
WHEN MATCHED THEN
UPDATE SET
col1 = col_val1,
col2 = col2_val
WHEN NOT MATCHED THEN
INSERT (column_list)
VALUES (column_values);
INTO子句 指定你正在更新或插入的目的表。
USING子句 指定数据源要被更新或插入的数据的源；可以是一个表、视图或者字查询。
ON 子句 是一个条件，在此条件上MERGE操作即可以更新也可以插入。
WHEN MATCHED | 通知服务器怎样响应连接条件的结果。
在数据仓库环境中，你可能有一个大的事实表和一个较小维数的表，小表中的行需要有条件地插入到大的事实表中。在这种情况下，MERGE语句是有用的。

数据库事务处理
在一个事务结束以后，下一个可执行的SQL语句自动开始下一个事务。一个DDL语句或者一个DCL语句自动提交，并且因此一个事务隐式结束。
用COMMIT和ROLLBACK语句，你能够：确保数据的一致性，在数据永久改变之前进行预览，分组逻辑相关的操作。
在事务中所做的每一个数据改变在事务被提交之前都是临时的。
COMMIT或ROLLBACK之前数据的状态，数据操纵操作首先影响数据库缓冲区，因此，数据以前的状态可以被恢复。
当前用户可以查询表观察到数据操纵操作的结果。
其他用户不能观察到当前用户所做的数据操纵操作的结果。Oracle服务器用读一致性来确保每个用户看到的数据和上次提交时相同。
受影响的行被锁定；其他用户不能改变受影响的行中的数据。
就Oracle服务器来说，数据的改变在事务被提交之前可能实际上已被写入数据库文件，但他们仍然是临时的。
如果许多用户同时对相同的表作了修改，那么，直到用户提交他们的修改之前，每个用户只能看到他自己的修改。
默认情况下，Oracle服务器有行级(row-level locking)。改变默认的锁机制是可能的。
用COMMIT语句使得未决的改变永久化，在COMMIT语句执行后：
数据的改变被写到数据库中。数据以前状态永久地丢失。所有用户都可以观察到事务的结果。受影响的行上的所被释放；其他用户现在可以对行进行新的数据改变。所有保存点被释放。
用ROLLBACK语句放弃所有未决的改变，在一个ROLLBACK语句执行后：
数据的改变被还原。数据以前的状态被恢复。受影响的行上的锁被释放。
如果一个语句的执行错误被发现，一个事务的一部分可以用隐式的回退丢弃。
如果一个单个的DML语句在一个事务的执行期间失败，它的影响被一个语句级的回退撤消，但在事务中的以前已经由DML语句完成的改变不能丢弃，他们可以由用户显示地提交或回滚。
Oracle在任何数据定义语言(data definition language DDL) 语句之前和之后发布一个隐式的提交。所以，即使你的DDL语句执行不成功，你也不能回退前面的语句，因为服务器已经发布了提交命令。
执行COMMIT或ROLLBACK语句来明确地结束事务。
Oracle服务器在数据上实现锁以防止对数据库中数据的并发操作，当某些事件发生时(例如系统故障) 或当事务完成时，那些锁被释放。
当一个DML语句成功执行时，数据库上的隐式锁被获得，默认情况下，Oracle服务器在尽可能的最低级别锁定数据。
执行带FOR UPDATE子句的LOCK TABLE语句或SELECT语句可以手动获得数据库表上的锁。从Oracle9i开始，DBA有管理回退段的选择，或让Oracle自动管理在回退表空间中的回退数据。

事务的控制
显式控制语句：
语句                        说明
COMMIT                        结束当前事务，使得所有未决的数据永久改变。
SAVEPOINT name                在当前事务中标记保存点。
ROLLBACK                     结束当前事务，丢弃所有未决的数据改变。
ROLLBACK TO SAVEPOINT name    回滚当前事务到指定的保存点，从而丢弃保存点创建后的任何改变。如果忽略了 TO SAVEPOINT 子句，ROLLBACK 语句回滚整个事务。
                            由于保存点是逻辑的，因此，没有办法列出已经创建的保存点。
                            
                            
事务的控制示例
你能够在当前事务中用SAVEPOINT语句创建一个标记，它把事务分为较小的部分。你可以用ROLLBACK TO SAVEPOINT语句丢弃未决的改变到该标记。
如果你用与前面的保存点相同的名字创建了另一个保存点，哪个早一点时间创建的保存点就被删除了。
回退到一个标记：用SAVEPOINT语句在当前事务中创建一个标记，用ROLLBACK TOSAVEPOINT语句回退到该标记，如下：
UPDATE...SAVEPOINT update_done;
INSERT...
ROLLBACK TO update_done;

隐式事务处理
在下面的情况下，一个自动提交发生：DDL 语句被发送，DCL 语句被发送，正常退出iSQL*Plus，没有明确地发送COMMIT或ROLLBACK语句。
当iSQL*Plus 非正常退出时，或者发生系统故障时，一个自动回退发生。
当一个事务被系统故障中断时，整个事务被自动回滚。该回滚防止不必要的数据改变错误发生，并且返回表到他们上一次提交时的状态，以这种方式，Oracle服务器保护表的完整性。

读一致性
数据库用户用两种方法访问数据库：读操作 (SELECT 语句)，写操作(插入、更新、删除语句)。
数据库读一致性的作用是：
数据库读者和写者被确保对数据观察的一致性。读者不能观察正在变化过程中的数据。写者被确保对数据库的改变以一致的方式进行。一个写者所做的改变不破坏另一个写者所做的改变或与其冲突。
读一致性的目的是确保每个用户看到的数据和他最后一次提交，并且在一个DML操作开始之前的数据一样。
读一致性是一种自动的执行，该操作在回退段保持一个数据库的局部的拷贝。
在对数据库进行一个插入、更新或者删除时，Oracle服务器在数据改变之前获得相关数据的拷贝，并且将这些数据写到一个回退段(undo segment)。
所有读数据者，除了发布修改命令的用户，看到的数据还是改变之前的状态；他们看到的数据是回退段中的数据快照(snapshot)。
在改变被提交到数据库之前，只有正在修改数据的用户能看见数据库的改变；除他之外的任何人看到的是回退段中的快照，这样就确保数据的读者读到一致的数据，而不是当前正在被修改的数据。
当一个DML语句被提交时，对数据库所做的改变对任何执行SELECT语句的人成为可见的。在回退段中文件中被旧 数据占用的空间被释放以重新使用。
如果事务被回滚，修改就被回退，在回退段中原来的，更旧的数据版本被写回到表中。所有用户看到的数据库就像事务开始之前那样。
当你提交一个事务时，Oracle服务器释放回滚信息，但并不立即销毁它，该信息保留在回退段中用来为事务提交之前就已经启动的查询创建相应数据的读一致查看。

锁定
锁是防止访问相同资源的事务之间的破坏性交互的机制
隐式锁定：这是Oracle中使用最多的锁。通常用户不必声明要对谁加锁，Oracle自动可以为操作的对象加锁，这就是隐式锁定。
显示锁定：用户可以使用命令明确的要求对某一对象加锁。显示锁定很少使用。
锁的作用：在并发事务之间防止破坏性的交互作用，不需要用户的动作，自动使用最低的限制级别，在事务处理期间保持。

显式锁定
lock table table_name in row share mode
lock table table_name in row exclusive mode
lock table table_name in share mode
lock table table_name in share row exclusive mode
lock table table_name in exclusive mode

隐式锁定
两种锁模式，独占锁：不允许其他用户访问，对于用DML语句修改的每一行，独占锁被自动获得。独占锁在本事务被提交或被回滚之前防止行被其他事务修改。该锁确保无其他用户能够在相同的时间修改相同的行，并且覆盖另一个用户还没有提交的改变。
共享所：允许其他用户访问，共享所是在表级在DML操作期间自动获得的。用共享锁模式，几个事务可以在相同的资源上获得共享锁。
高级数据并发操作：DML: 表共享，行独占
查询: 不需要锁
DDL: 保护对象定义
锁保持直到commit 或rollback。

按照对象分类锁定
DML lock（data locks，数据锁）：用于保护数据的完整性；
DDL lock（dictionary locks，字典锁）：用于保护数据库对象的结构（例如表、视图、索引的结构定义）；
internal locks 和l a t c h es（内部锁与闩）：保护内部数据库结构；
distributed locks（分布式锁）：用于OPS（并行服务器）中；
PCM locks（并行高速缓存管理锁）：用于OPS（并行服务器）中。