DML操作及名称空间

DML操作及名称空间
模式与对象名称空间的关系
模式(schema)是一种逻辑结构，它对应于用户，每建一个用户就有一套模式与之对应。
我们通常说对象的唯一标识符是前缀为模式名加上对象名称，如scott.emp
同一模式下的对象是不可以重名的。比如在scott模式里，表emp是唯一的，不能还有另外对象叫emp。但不同的模式下可以重名。
create table emp1 as select * from emp;
create view emp1 as select 1 as A from dual;
create unique index emp1 on emp1(empno);
alter table emp1 add constraint emp1 unique(empno);
名称空间定义了一组对象类型，同一个名称空间里的不同对象不能同名，而不同的名称空间中的不同对象可以共享相同的名称。
表，视图，序列，同义词是不同类型的对象，但他们属于同一名称空间，因此在同一模式下也是不可以重名的。
索引，约束有自己的名称空间，所以在同一模式下，可以由表A,索引A和约束A共存。




使用子查询创建表
create table as subquery
create table emp1 as select * from emp;
表emp的索引不会被复制给表emp1
表emp中的约束只有not null约束能够带到表emp1里来
表emp中的default也不会被复制到emp1


改变表结构的DDL操作
drop和rename语法上要加column关键字。如drop列建议先使用set unused 然后drop unused column
当想要add一列，并约束该列为not null时，如果该表已经有数据了，则会报错。
alter table emp1 add c number not null
alter table emp1 add  c number default 0 not null


alter table emp1 set unused column c;
select * from emp1
alter table emp1 drop unused column;




如果set unused 某列，该列上有索引，约束，并定义了视图，引用过序列，结果如何？
索引和约束自动删除，序列无关，视图保留定义。
create table test(id int ,name varchar(10));
create index idx_test_id on test(id);
alter table test add constraint unq_test_id unique(id);
create sequence seq_test start with 1 increment by 1;
create view vi_test as select id from test;
insert into test values(seq_test.nextval,'zzl');
commit;


select * from user_indexes where table_name ='TEST';
select * from user_constraints where table_name ='TEST';
select * from user_sequences ;
select * from user_views;


alter table test drop column id;




select * from user_indexes where table_name ='TEST';
select * from user_constraints where table_name ='TEST';
select * from user_sequences ;
select * from user_views;
select * from vi_test




group by 产生统计报告
group by rollup
group by cube
grouping 
grouping_id
grouping set


select job,deptno,sal from emp1;
select job,deptno,sum(sal) from emp1 group by (job,deptno);
select job,deptno,sum(sal) from emp1 group by rollup(job,deptno);
select job,deptno,sum(sal) from emp1 group by cube(job,deptno);


grouping函数可以接受一列，返回0或1，如果列为空，那么grouping返回1，如果列值非空，那么返回0，grouping只能在使用rollup或cube的查询中使用


select job,deptno,sum(sal),grouping(job),grouping(deptno), grouping_id(job,deptno) from emp group by cube(job,deptno);




select deptno,job,mgr, avg(sal) from emp
group by grouping sets((deptno,job),(job,mgr))




select deptno,job,null, avg(sal) from emp
group by deptno,job
union all
select null,job,mgr,avg(sal) from emp
group by job,mgr




oracle分层查询
oracle是一个关系数据库管理系统，它用表的形式组织数据，在某些表中的数据还呈现出树形结构的联系，
例如，emp表，empno和mgr两列，通过这两列反映出来的就是雇员之间领导和被领导的关系。他们之间的这种
关系就是一种树结构。


遍历有两个方向
top-down
down_top




select ...
connect by {prior 列名1=列名2|列名1 = prior 列名2}
[start with];


select level,empno,ename,mgr from emp
connect by prior empno = mgr
start with empno =7839 order by level;




select level,empno,ename,mgr from emp
connect by  empno = prior mgr
start with empno =7369 order by level;




select lpad(level,level*3,' '),empno,ename,mgr
from emp
connect by prior empno = mgr
start with ename ='KING' order by level




节点和分支的裁剪


在对树结构进行查询时，可以去掉表中的某些行，也可以剪掉树中的一个分支，使用where子句来限定
树形结构中的单个节点，以去掉树中的单个节点，但他却不影响后代节点（top_down）或前辈节点(down_top)






select lpad(level,level*3,' '),empno,ename,mgr
from emp
where ename <>'SCOTT'
connect by prior empno = mgr
start with ename ='KING' order by level




select lpad(level,level*3,' '),empno,ename,mgr
from emp
connect by prior empno = mgr
and ename <> 'SCOTT'
start with ename ='KING' order by level




oracle的事务和锁
事务的ACID属性
原子性(atomicity):事务是一个完整的操作，事务的各步操作是不可分的；要么都执行，要么都不执行。
一致性（consistency）：一个查询的结构必须与数据库在查询开始的状态一致（读不等待写，写不等待读）
隔离性（isolation）:对于其他会话来说，未完成的（也就是未提交的）事务必须不可见
持久性（durability）:事务一旦提交完成后，数据库就不可以丢失这个事务的结果，数据库通过日志能够保持事务的持久性。


事务的开始和结束
事务采用隐形的方式，起始于session的第一条dml语句
事务结束于：
commit或rollback
ddl语句被执行（提交）
dcl语句被执行（提交）
用户退出sqlplus（正常退出是提交，非正常退出是回滚）
机器故障或系统崩溃(回滚)
shutdown immediate(回滚)


oracle的事务保存点功能
savepoint 命令允许在事务进行中设置一个标记（保存点），这个标记可以控制rollback的效果，
即在一个事务中回滚掉最近的部分dml语句，保留下保存点之前的dml语句，并使事务本身继续执行，
也就是说回滚到保存点这个动作并不使事务结束。


insert into test values(1,'a'); 
savepoint A; 
insert into test values(2,'b');
savepoint B; 
insert into test values(3,'c');
savepoint C; 
rollback to A; 
commit; 




scn的概念
scn：system change number
它是一个不断增长的整数，相当于oracle内部的一个时钟，只要数据库一有变更，这个scn就会+1
oracle通过scn记录数据库里事务的一致性。scn涉及了实例恢复和介质恢复的核心概念，它几乎无处不在，
控制文件，数据文件，日志文件都有scn。包括block上也有scn。


select current_scn from v$database;
select dbms_flashback.get_system_change_number from dual;




共享锁与排他锁的基本原理
排他锁，排斥其他排他锁和共享锁
共享锁，排斥其他排他锁，但不排斥其他共享锁


oracle数据库锁分类
dml锁（data locks 数据锁）用于保护数据的完整性
ddl锁（dictionary locks 字典锁）用于保护数据对象的结构 如表，索引等的结构定义
内部锁和闩（internal locks and latches）保护数据库的内部结构


当一个用户对某表做dml操作时，也会加ddl锁，这样在事务未结束前，可防止另一用户对该表做ddl操作。初始化参数ddl_lock_timeout可以
设定了ddl锁的等待时间。时间过后如果事务仍未结束，则显示资源正忙。
当一个用户对某表做DDL操作时，也会加DML锁，这样可以防止另一个用户对该表做DML操作


oracleDML锁，包括tm和tx两种
tm是面向对象的锁，它表示锁定了系统中的一个对象，在锁定期间不允许其他人对这个对象做ddl操作。
tx是面向事务的锁，他表示发起了一个事务，是否有事务产生，这是根据是否使用undo段作为评断标准的。




五种tm表锁的含义
row share 行共享(RS)允许其他用户同时更新其他行，允许其他用户同时加共享锁，不允许有独占（排他性质）的锁
row exclusive 行排他（RX） 允许其他用户同时更新其他行，只允许其他用户同时加行共享锁或者行排他锁
share 共享（S） 不允许其他用户同时更新任何行，只允许其他用户同时加共享锁或者行共享锁
share row exclusive(srx) 共享行排他，不允许其他用户同时更新其他行，只允许其他用户同时加行共享锁
exclusive(X)排他，其他用户禁止更新任何行，禁止其他用户同时加任何排他锁


sql语句                                         加锁模式    许可其他用户的加锁模式
selec * from emp1                                  无       RS,RX,S,SRX,X
insert,update,delete                               RX       RS,RX
select * from emp1 for update                      RX       RS,RX
lock table emp1 in row share mode                  RS       RS,RX,S,SRX
lock table emp1 in row exclusive mode              RX       RS,RX
lock table emp1 in share mode                      S        RS，S
lock table emp1 in share row exclusive mode        SRX      RS
lock table emp1 in exclusive mode                  X        无






加锁模式
自动加锁
做DML操作时，如insert,update,delete 以及select ... for update 由oracle自动完成加锁
scott
select * from emp1 for update
sys
update scott.emp1 set ename ='SCOTT' where empno =7788


scott
select * from emp1 where deptno =10 for update
sys
select * from emp1 for update nowait
select * from scott.emp1 for update wait 5
select * from scott.emp1 for update skip locked


对整个表for udpate是不锁insert语句的




死锁和解锁
plsql
update emp1 set ename ='aaa' where empno =7788;
update emp1 set ename = 'bbb' where empno = 7369;


sqlplus
update emp1 set ename = 'bbb' where empno = 7369;
update emp1 set ename ='aaa' where empno =7788;




select sess.sid, 
    sess.serial#, 
    lo.oracle_username, 
    lo.os_user_name, 
    ao.object_name, 
    lo.locked_mode 
    from v$locked_object lo, 
    dba_objects ao, 
    v$session sess 
where ao.object_id = lo.object_id and lo.session_id = sess.sid; 




alter system kill session 'sid,serial#'










用户访问控制
创建和管理数据库用户


查看数据库用户
select * from dba_users;




模式 schema


showuser


用户缺省表空间
select * from v$tablespace
create user zzl identified by bsoft;
缺省表空间


select * from database_properties


alter  database default tablespace tablespace_name




空间配额
配额 (quota)是表空间中为用户的对象使用的空间量
alter user zzl quota 10M on users;
alter user zzl quota
alter user zzl quota 0 on users;




管理概要文件(profile)
作用是对用户访问数据库做一些限制
概要文件（profile）具有两个功能，一个是实施口令限制，另一个是限制会话可以占用的资源
始终要实施口令控制，而对于资源限制，则只有实例参数resoure_limit为true时才会实施，默认是false
系统自动使用概要文件，有一个默认的default profile 限制很松，作用很小
可以使用create profile为用户创建它自己的概要文件，没有说明的参数就从default profile的当前版本中提取


create profile zzl limit failed_login_attempts 2;
alter user zzl1 profile zzl;
select * from dba_users;


zzl1用错误密码尝试登陆3次
drop profile zzl cascade
select * from dba_users;
alter user zzl1 account unlock;


权限的引入
系统安全：用户名和口令，分配给用户的磁盘空间及用户的系统操作，如profile等
数据库安全：对数据库对象的访问及操作


用户具备系统权限才能够访问数据库
具备对象权限才能访问数据库中的对象


系统权限通常由dba授予
典型dba权限
create user
drop user
backup any table
select any table
create any table


典型用户需要的系统权限
create session
create table
create sequence
create view
create procedure


对象权限
对象权限有8种：alter,delete,execute,index,insert,references,select ,udpate


权限的授权
授予系统权限语法
grant sys_privs,[role] to user|role|public [with admin option]
授予对象权限语法
grant object_privs on object to user|role|public [with grant option]


系统权限和对象权限语法格式不同，不能混合使用
grant create table ,select on emp1 to zzl1;




创建和删除角色
create role myrole
drop role myrole




角色的引入：
系统权限太复杂，将系统权限打包成角色，oracle建议通过角色授权权限，目的就是为了简化用户访问管理




权限传递
系统权限级联
create user zzl1 identified by bsoft;
create user zzl2 identified by bsoft;
grant create session to zzl1 with admin option;
--zzl1
grant create session to zzl2;
尝试用zzl2登录


revoke create session from zzl1;


尝试用zzl1和zzl2登录


对象权限级联
grant select on emp1 to zzl1 with grant option;


--zzl1
grant select on emp1 to zzl2;
select * from scott.emp1;


--zzl2
select * from scott.emp1;


--scott
revoke select on emp1 from zzl1;


--zzl1
select * from scott.emp1;


--zzl2
select * from scott.emp1;




与权限有关的数据字典


select * from session_privs;//用户当前会话拥有的系统权限
select * from user_role_privs;//用户被授予的角色
select * from role_sys_privs;//用户当前拥有的角色的系统权限
select * from user_sys_privs;//直接授予用户的系统权限
select * from user_tab_privs;//授予用户的对象权限
select * from role_tab_privs;//授予角色的表的权限










正则表达式
oracle中的支持正则表达式的函数主要有下面四个
regexp_like:与like 的功能相似
regexp_instr：与instr的功能相似
regexp_substr：与substr的功能相似
regexp_replace：与replace的功能相似
使用posix正则表达式代替了老的（%和_）
create table test(id varchar2(4),value varchar2(10));
insert into test values('1','1234560');


--1开头60结尾的7位字符串
select * from test where value like '1____60';
select * from test where regexp_like(value,'1....60');
--1开头60结尾中间为数字的7位字符串
select * from test where regexp_like(value,'1[0-9]{4}60');
select * from test where regexp_like(value,'1[[:digit:]]{4}60');


regexp_replace(x,pattern[,replacestring[,start[,occurence[match_option]]]])
x:待匹配的字符串
pattern匹配模式
replace_string替换字符串
start开始位置
occurence匹配次数
match_option匹配参数
select regexp_replace('hello everybody,welcome to bsoft','b[[:alpha:]]{3}','one',1,1) from dual