Oracle培训笔记20060413

创建一个表，并向其中添加数据
SQL> create table emp_bk as select * from emp;

UPDATE语句

update 表名 set 列名1=值1.....
[where....]

练习：修改smith的工资和分成，分别为原来的1.1倍和1.5倍
update emp_bk set set sal=sal*1.1,comm=comm*1.5 where ename='SMITH';
练习：假设新的工资政策是CLERK增加10%，MANAGER增加5%，其他人不变，请修改所有人的工资。
SQL> update emp_bk set sal=case job when 'CLERK' then 1.1
  2  when 'MANAGER' then 1.05
  3  else 1
  4  end*sal;
update 表名字[别名] set (列名列表)=(子查询)
[where 条件]
练习：修改所有职工的工资为其所在部门的平均工资。
update emp_bk A
 set sal=(select avg(B.sal)
  from emp_bk B
  where A.deptno=B.deptno);
完成后进行一下查询：
select empno,ename,deptno,sal from empp
order by deptno;

练习：将empp表的SAL和COMM的数据恢复成和emp表相同 

数据库表的删除与重建
drop table emp;
drop table dept;
drop user scott cascade;//强制删除
c:\oraacle\ora\rdbms\admin\scott.sql
需要将里面有两条插入语句里面写的是"JUY"，需要改为"7"月

INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,to_date('13-7月-87')-85,3000,NULL,20);

INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('13-7月-87')-51,1100,NULL,20);

执行脚本：在管理员帐户下：
@c:\oracle\ora\rdbms\admin\scott.sql或者
@%oracle_home%\rdbms\admin\scott.sql
连接SYSDBA功能屏蔽：开始--运行--Oracle - OraHome92---Configuration and Migration Tools---net manager
概要文件----ORACLE高级选项---安全性里面将NTS增加或者删除
删除后将提示：
SQL> conn /as sysdba;
ERROR:
ORA-01031: insufficient privileges
警告: 您不再连接到 ORACLE。

然后在SQL PLUS中修改管理员密码：
SQL> alter user sys identified by zhangzhongliang;

用户已更改。

然后将NTS帐户取消。

DELETE语句
删除表中数据：
DELETE [FROM] 表名
 [WHERE条件]

***************事务：
执行DML语句时的几个主要现象：

现象一：一个会话执行DML，它所修改的数据，如果不提交，起他会话就看不到它所做的修改。
打开两个SQL*PLUS会话（建立了两个会话）
在第一个会话里UPDATE EMP SET SAL=NULL
在进行SELECT * FROM EMP
则显示SAL全部被设置成空值

而在另一个会话里面，却依然能够查询到以前（未更新的SAL值）
只有在COMMIT提交之前，在另一个会话里才可以看得到新的数据值。

现象二：一个会话内所有执行的DML，它所修改的数据在提交前，可以撤消
多次ROLLBACK语句

现象三：一个DML修改某一行数据之前，先要获得这个行上的锁，这个锁是独占锁

如果这个锁，已经被其他会话获得，则当前会话发生等待
测试：在一个会话里面
UPDATE EMP SET SAL=SAL WHERE EMPNO=7566;
锁的封锁时间为直到它提交
在另一个会话里执行
UPDATE EMP SET SAL=SAL WHERE EMPNO=7566;
这时将等待（不是死掉）
直到占用锁的会话提交（COMMIT）或者撤消（ROLLBACK）
死锁：共享资源的彼此等待。
在第一个会话里更新empno=7566;
在第二个会话里更新empno=7866;
然后第一个会话需要更新empno=7866;
而第二个会话需要更新empno=7566;
这时发生死锁。
ORACLE将发现死锁。提示等待资源时检测到死锁
这时解决的方法是ROLLBACK
现象四：一个语句一但开始执行，不能看到在它执行过程中任何会话修改了的语句，看到的是语句开始时刻的旧数据。
练习：修改所有职工的工资为其所在部门的平均工资。
update emp_bk A
 set sal=(select avg(B.sal)
  from emp_bk B
  where A.deptno=B.deptno);
001:100 --->200
002:200 --->233.3333
003:300 --->244

上面的四个现象都和事务有关系：
下面介绍一下事务：（TRANSACTIONS）执行的一系列SQL语句序列。
 原子性：要么全做，要么全不做。
事务的起点与终点：
 事务的起点：一个事务第一个执行的SQL语句

第一个执行的SQL语句：（1）登陆后的第一次执行
                     （2）上一个事务结束后的第一次执行
 事务的终点：
（1）COMMIT
（2）ROLLBACK
（3）DDL语句：CREATE,ALTER,DROP,GRANT,REVOKE
 隐含的提交当前事务，并且开始一个自动提交的事务
（4）主动终止会话：主动停止会话，不提交也不撤消就退出了：这种情况有可能自动提交或者自动撤消（SQL*PLUS是自动提交的）
（5）被动终止会话：中毒，硬件...自动撤消事务，在下次重起的时候会自动撤消（只要能重新启动的话）

*************切换帐户就等于切断一个会话************************
INSERT  ----事务一开始
UPDATE 
DELETE 
ROLLBACK  ----事务一结束
INSERT ----事务二开始
COMMIT  ----事务二结束 

INSERT   事务一开始
UPDATE
CREATE 事务一结束，事务二开始，事务二结束（即使CREATE语句执行错误，也同样是一个事务）
DELETE 事务三开始
ROLLBACK 事务三结束
UPDATE 事务四开始
COMMIT 事务四结束
CREATE  ----事务一开始与结束
INSERT ----事务二开始
UPDATE
DELETE
CONN SCOTT/.... ---事务二结束
INSERT  ---事务三开始
COMMIT  --事务三结束
UPDATE  ---事务四开始 
ROLLBACK ---事务四结束
SELECT   --事务1开始并结束
   UPDATE   --事务2开始
   DELETE
   SELECT
   INSERT
   COMMIT   --事务2结束
SELECT语句的特殊处：如果SELECT语句是第一个语句，则独自成为一个事务，如果不是第一个语句，则合并到当前事务中
一个完善的事务应该具有以下四个特性：
A:automacity原子性
C:Consistency持续性
I:Isolation隔离性 让并行的事情看起来是串行的
D:Duirablity永久性 写到介质上，不是内存，CPU，高速缓存，而是硬盘文件等

并行事务间需要避免的三个现象：
读污染 ( dirty read )
一个事务读取了另一个事务写的但是尚未提交的数据

不可重复读 ( non-repeatable read )
一个事务重新读取前面(同一个事务内)读取过的数据,发现已经被其他事务修改了(并提交)

幻象读取 ( phantom read )
事务查询返回一套符合条件的行,发现其中包含了其他事务插入并提交的行.

ISOLATION LEVEL隔离级别：
<level of isolation>::=READ UNCOMMITED
                              | READ COMMITED   ---默认的
                              | REPEATABLE READ
                              | SERIALIZABLE
SET TRANSACTION ISOLATION LEVEL
      [READ COMMITED| SERIALIZABLE]
     (ORACLE)

oracle里现象四：一个语句一但开始执行，不能看到在它执行过程中任何会话修改了的语句，看到的是语句开始时刻的旧数据。但是这只对一个语句起作用，假如结余=收入-支出，但是收入和支出需要使用两个语句进行
12：00开始查询收入：        1000    500
12：30查询收入结束，开始查支出     3000    1500 
13：00查询支出结束                  5000    2000
最后查出来是1000-1500=500

这时需要提升隔离级别：
当事务第一句执行
 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
   上面的语句必须是事务中的第一句话
 其影响,仅限于当前事务,对其他事务没有影响
 当前事务结束,影响消除
Create table as (select * from tableName);

alter table 表名字 ADD 列名 数据类型（宽度）;

alter table 表名字 modify 列名 新数据类型 (新宽度);

alter table 表名字 rename column 旧列名 TO 新列名;

alter table 表名字 drop column 列名;

alter table 表名字 set unused column 列名;
从定义里面去掉，而不释放空间，这样会效率提高

alter table 表名字 drop unused Columns;
释放空间，比较慢
删除了一半，而系统崩溃时，在下次启动，继续删除需要：
alter table 表名字 drop columns continue;

TRUNCATE TABLE 表名字;
 删除表中所有的数据，自动提交（DDL），并且释放空间
与DELETE的区别：有where条件，可以提交也可以撤消，不释放空间

临时表：表中数据是临时的，表的定义不是临时的
 事务内临时表（事务结束，表就失去），会话内临时表（会话结束，表失去）
事务内临时表：
CREATE GLOBAL TEMPORARY TABLE 表名字(列名列表);
事务结束，数据消失
eg.
CREATE GLOBAL TEMPORARY TABLE TTT(A NUMBER(4),B NUMBER(4));
COMMIT;
SELECT * FROM TTT;
未选定行

练习：创建一个事务内临时表，试验一下效果。

会话内临时表：存在于会话内，事务结束，依然存在

create global temporary table_s_test(A number(10),B number(10))
 on commit preserve rows;
只有在切断会话时才失去。
SQL> create global temporary table zzl(A number(4),B number(4))
  2  on commit preserve rows;
create global temporary table zzl(A number(4),B number(4))
                              *
ERROR 位于第 1 行:
ORA-00955: 名称已由现有对象使用

上面事务的临时表的定义依然存在。 

视图：VIEW，给了我们一个看数据的方法。
视图是一个写好了的查询，怎么查找都已经编写好了，但是同时限制了我们用其他的方法去看数据
CREATE OR REPLACE VIEW 视图名 AS 查询;

CREATE OR REPLACE VIEW V_EMP as select empno,ename from emp;
视图是一个虚表。
select * from v_emp;
相当于
select * from (select * from emp);
练习：
创建一个视图，查询所有部门的编号，名称，人数，平均工资，最高工资。
SQL> create or replace view zzl as
  2  select B.deptno,B.dname,count(A.empno),avg(A.sal),max(A.sal)
  3  from emp A,dept B
  4  where A.deptno(+)=B.deptno
  5  group by B.deptno,B.dname;
select B.deptno,B.dname,count(A.empno),avg(A.sal),max(A.sal)
                        *
ERROR 位于第 2 行:
ORA-00998: 必须使用列别名命名此表达式

注释：由于视图是以一个虚表的形式进行存在的，所以不能以组函数做为列名，必须起一个别名
SQL>  create or replace view zzl as
  2   select B.deptno,B.dname,count(A.empno) eee,avg(A.sal) fff,max(A.sal) ggg
  3  from emp A,dept B
  4  where A.deptno(+)=B.deptno
  5  group by B.deptno,B.dname;

视图已建立。

SQL> select * from zzl;

    DEPTNO DNAME                 EEE        FFF        GGG
---------- -------------- ---------- ---------- ----------
        10 ACCOUNTING              3 2916.66667       5000
        20 RESEARCH                5       2175       3000
        30 SALES                   6 1566.66667       2850
        40 OPERATIONS              0
限制对行进行的访问
CREATE OR REPLACE VIEW V_VIEW_10
 AS SELECT * FROM EMP WHERE DEPTNO=10;

数据字典视图
 在数据字典表上建立的视图
 数据字典的数据重要，对用户进行限制。
 做了一定的加密。
 适合普通人员去查看。
在名字上有三大类：
USER_****:当前帐户拥有的****的信息。eg. user_tables
ALL_****:当前帐户可以访问的****的信息。
DBA_****:系统内所有的****的信息。