视图

视图
为什么使用视图
1）限制数据的存取
用户只能看到基表的部分信息。方法：赋予用户访问视图对象的权限，而不是表的对象权限
2）使得复杂的查询变得容易
对于多表连接等复杂语句的映射，或内联视图的使用
3）提供数据的独立性
基表的多个独立子集的映射




简单视图
视图与基表的记录一对一，故可以通过视图修改表




复杂视图
视图与基表的记录一对多，无法修改视图


特性          简单视图        复杂视图
表的个数      一个            一个或多个
含函数        无              有
含组函数     无               有
含distinct   无               有
DML操作     可以             不一定




语法
create [or replace] [force|noforce] view view_name
[(alias[,alias],...)]
as subquery
[with check option [constraint constraint]]
[with read only];






create view v1 as select empno,sal,deptno from emp1 where deptno =10;


select * from user_views
create view v2 as select empno,sal,sal+100,deptno from emp1 where deptno =10;
create view v2 as select empno,sal,sal+100 as sal2,deptno from emp1 where deptno =10;
create view v3(a1,a2,a3,a4) as select empno,sal,sal+100 ,deptno from emp1 where deptno =10;


drop view v1;
drop table emp1;
create view v3(a1,a2,a3,a4) as select empno,sal,sal+100 ,deptno from emp1 where deptno =10;
create force view v3(a1,a2,a3,a4) as select empno,sal,sal+100 ,deptno from emp1 where deptno =10;
select * from v3
flashback table emp1 to before drop
select * from v3


update v3 set a4=99
select * from v3;
select * from emp1;


--with check option
create or replace view v3(a1,a2,a3,a4) as select empno,sal,sal+100 ,deptno from emp1 where deptno =10 with check option;
update v3 set a4=99
update emp1 set deptno =10 where deptno =99
update v3 set a4=99
update v3 set a1=a1+100


grant connect,resource to zzl identified by bsoft
grant select on v3 to zzl


--zzl用户
select * from scott.v3
select * from scott.emp1;


复杂视图
drop table emp1;
drop table dept1;
create table emp1 as select * from emp;
create table dept1 as select * from dept;
create or replace view v5 as select empno,dname from emp1,dept1 where emp1.deptno = dept1.deptno and emp1.deptno =10;
select * from v5
update v5 set dname =3030




  
同义词
同义词通常是数据库对象的别名
公有同义词
公有同义词一般有DBA创建，使所有用户都可使用
create user zzl identified by bsoft;
select * from session_privs
grant create session to zzl;
select * from session_privs


创建者需要有create public synonym权限
create public synonym syn1 for scott.v3
--grant select on syn1 to public
--grant public to zzl
zzl用户执行
select * from syn1;
select * from scott.v3;
--revoke public from zzl;
select * from syn1;
select * from scott.v3;


私有同义词
create synonym abc for emp1;
grant select on abc to zzl;
select * from scott.abc
select * from scott.emp1
revoke select on abc from zzl
select * from scott.abc
select * from scott.emp1
grant select on emp1 to zzl
select * from scott.abc
select * from scott.emp1


序列
create sequence seq1
select seq1.currval from dual;
select seq1.nextval from dual;
select seq1.currval from dual;


create sequence seq2 start with 10 increment by 2 maxvalue 20 cycle nocache;
select seq2.nextval from dual;
alter sequence seq2 increment by 1;
select seq2.nextval from dual
alter sequence seq2 start with 19
select * from user_sequences
drop sequence seq2








insert 总结


一次插入一行
create table test(id int,name varchar(10) default 'zzl');


insert into test values(1,'abc');
insert into test values(2,default);
insert into test values(3,null);
insert into test(id) values(4);
insert into (select id from test) values(5);
insert into test values(6,(select dname from dept where deptno =10))




insert with check option
insert into (select id from test where id <10 with check option ) values(10);
insert into (select id from test where id <10 with check option ) values(9);
insert into (select name from test where id <10 with check option ) values('zzl');
insert into (select name from test where id <10  ) values('zzl');
insert into (select id,name from test where id <10 with check option ) values(9,'zzl');
update (select sal from emp1) set sal=(select sal from emp1 where empno =7788);
update (select ename from emp1) set sal=(select sal from emp1 where empno =7788);
delete (select sal from emp1) where sal >3000;




一次插入多行
create table emp1 as select * from emp
insert into emp1 select * from emp where deptno =10;




multiable insert
insert all 


create table test(x number(10),y varchar2(10));
insert into test values(1,'a');
insert into test values(2,'b');
insert into test values(3,'c');
insert into test values(4,'d');
insert into test values(5,'e');
insert into test values(6,'f');
commit;


create table test1 as select * from test where 0=1;
create table test2 as select * from test where 0=1;


insert all into test1 into test2 select * from test;


select * from test1;
select * from test2;






delete from test2;
delete from test3;


insert all when x >= 3 then into test1 when x >=2 then into test2 select * from test;




insert first
delete from test2;
delete from test3;


insert first when x >= 3 then into test1 when x >=2 then into test2 select * from test;




旋转insert
create table test(empno number(10),week_id number(2),
sal_mon number(8,2),sal_tun number(8,2),
sal_wed number(8,2),sal_thur number(8,2),sal_fri number(8,2))


insert into test values(3030,56,1000,2000,3000,4000,5000)


create table test2(empno number(10),week number(2),sales number(8,2))






insert all
into test2 values(empno,week_id,sal_mon)
into test2 values(empno,week_id,sal_tun)
into test2 values(empno,week_id,sal_wed)
into test2 values(empno,week_id,sal_thur)
into test2 values(empno,week_id,sal_fri)
select empno,week_id,sal_mon,sal_tun,sal_wed,sal_thur,sal_fri from test;






merge
create table emp1 as select * from emp where empno =7788
update emp1 set ename = 'aaa'


insert into emp1(empno,ename)
values(7900,'zzl')
select * from emp1


merge into emp1
using emp
on (emp1.empno = emp.empno)
when matched  then
update set emp1.ename = emp.ename
delete where emp1.empno =7900
when not matched then
insert values(emp.empno,emp.ename,emp.job,emp.mgr,emp.hiredate,emp.sal,emp.comm,emp.deptno)




with 语句
我们可以用一个关键字with给一个查询块起一个别名。然后在后面的查询中引用这个查询块的别名。
好处：
1.使用with语句，可以避免在select语句中重复书写相同的语句块
2.with语句将该子句中的语句块执行一次并存储到用户的临时表空间中
3.使用with语句可以避免重复解析，提高查询效率




with
dept_costs as(
select d.dname,sum(e.sal) as dept_total
from emp e,dept d
where e.deptno = d.deptno
group by d.dname),
avg_cost as(
select sum(dept_total)/count(*) as dept_avg from dept_costs)
select * from dept_costs 
where dept_total < (select dept_avg from avg_cost)
order by dname




正则表达式
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




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


查看数据库用户
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)
作用是对用户访问数据库做一些限制