Lesson9 Manipulating Data

Lesson 9
1. 使用子查询可以更新两列
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    =  113;


UPDATE employees
SET (job_id, salary) = (SELECT job_id, salary
                     FROM    employees
                     WHERE   employee_id = 205)
 WHERE    employee_id    =  113;
 
2. DELETE
DELETE[FROM] 
table [WHERE condition];


3. TRUNCATE
- 移除所有行
- 把表清空
- 表恢复成原装
1）如果表是具有参照完整性的父表，则必须先在truncate前disable约束


4. 事务控制语言
A. 事务或由如下组成：
1）多个DML语句
2）一个DDL语句
3）一个DCL语句（数据控制语句）


B.事务启动和结束
启动：当第一个DML语句开始执行
结束：任一如下事件触发：
a. commit/rollback
b. 一个DDL或DCL语句执行（自动commit）
c. 用户退出sqlplus或sql developer
d. 系统崩溃


C.COMMIT和ROLLBACK的好处
1）确保事务一致性
2）在数据变为永久前复查数据的改变
3）分组逻辑相关的操作


D.显式的事务控制语句
COMMIT 结束当前事务，让所有的pending数据变为永久的
SAVEPOINT name 为当前事务标记一个保存点
ROLLBACK TO SAVEPOINT name 让当前事务回滚到某个保存点
【注意】如果你创建了一个跟之前一样名字的SAVEPOINT，前面的就会被删掉


E.隐式的事务处理
自动commit：DDL/DCL,sqlplus正常退出但没有显式的执行commit或rollback操作
自动rollback: 异常退出sqlplus，或系统故障


F.在commit或rollback之前的数据状态
1）之前的数据状态可以被恢复
- 因为数据操作最先影响的是db buffer，所以之前状态的数据可被恢复
2）当前的用户可以用select语句查询DML之后的结果
3）其他用户不可以用select语句查询DML之后的结果
4）受影响的行是被锁住的，其他用户不能改变受影响行的数据


G.在commit之后的数据状态
1）在数据库中的数据变化被保存
2）该数据的前一个状态将被覆盖
3）所有用户都可以看到结果
4）受影响的行锁会被释放；这些行允许其他用户操作
5）所有的savepoint会被清除


H.在rollback之后的数据状态
1）数据变化会被撤销
2）先前的数据状态被恢复
3）受影响的行锁被释放


I.STATEMENT-LEVEL回滚
1）如果单条DML语句在执行期间失败，仅仅那条语句是会被回滚的
2）oracle服务器会实施隐式的savepoint
3）所有其他的改变会被保留


J.读一致性
相同的用户可以登录不同的会话，每个会话都会维护读一致性，及时是相同的用户。
实施读一致性
1）读一致性是自动实施的
2）当做插入删除修改操作前，oracle会做一份数据拷贝到回滚段里
3）在commit前，所有的reader，除了操作数据的会话，其他都是读的回滚段中的snapshot
4）在commit后，数据库的变化可被所有人看到。在回滚段中的old数据空间会被释放重用
5）如果事务rollback，回滚段中原版的数据会写回表中


H.SELECT语句中的FOR UPDATE语句
1）如果加上FOR UPDATE关键字，数据库会自动获取一个‘所有行排他行级锁’exclusive row-levelrow-levellocks
意思是，仅由你改变记录。别人要想改变，只能等你rollback或commit才行。
2）可以在FOR UPDATE后追加NOWAIT关键字，意思是告诉oracle，如果其他人锁住了表，不要等待，直接抢过来。
如果不加NOWAIT，就得等别的用户rollback或commit后才能使表可用。
3）可以将FORUPDATE用于多个表
SELECT e.employee_id, e.salary, e.commission_pct 
FROM employees e JOIN departments d
USING (department_id)
WHERE job_id = 'ST_CLERK'
AND location_id =1500
FOR UPDATE
ORDER BY e.employee_id;
//语句会锁employees表中job_id = 'ST_CLERK'和 location_id =1500的行；
//语句会锁departments表中location_id =1500的行；
4）如果使用FOR UPDATE OF column_name去修饰打算改变的列，那么，仅是指定的表中的那些行被锁住
SELECT e.employee_id, e.salary, e.commission_pct FROM employees e JOIN departments d
USING (department_id)
WHERE job_id = 'ST_CLERK' AND location_id = 1500 FOR UPDATE OF e.salary
ORDER BY e.employee_id;
5）系统等待5秒之后再夺取使用权
SELECT employee_id, salary, commission_pct, job_id FROM employees
WHERE job_id = 'SA_REP'
FOR UPDATE WAIT 5
ORDER BY employee_id;