Oralce 中的其他对象.docx
《Oralce 中的其他对象.docx》由会员分享,可在线阅读,更多相关《Oralce 中的其他对象.docx(21页珍藏版)》请在冰点文库上搜索。
![Oralce 中的其他对象.docx](https://file1.bingdoc.com/fileroot1/2023-8/16/860ecefe-5614-4c09-a38b-4ef1a2bc5269/860ecefe-5614-4c09-a38b-4ef1a2bc52691.gif)
Oralce中的其他对象
Oralce中的其他对象:
●视图:
⏹概念:
视图是从一个或几个实体表(或视图)导出的表,它与实体表不同,视图本身是一个不包含任何真实的数据的虚拟表,数据库中只存放视图的定义,而不存放视图对应的数据,这些数据任然存放在原来的实体表中,从这个意义上讲,视图就像一个窗口,通过它可以看到数据库中自己感兴趣的数据及其变化:
◆案例:
1:
创建一张基本工资大于2000的雇员信息的视图:
SQL>createorreplaceviewv_emp
2as
3selectempno,ename,sal,hiredatefromt_emp
4where
5sal>2000;
视图已创建。
Createorreplace表示如果有这张视图则替换,没有则创建:
2:
就可通过视图来直接查询结果:
视图就相当于给某个查询结果定义一个别名:
SQL>select*fromv_emp;
EMPNOENAMESALHIREDATE
--------------------------------------------
7566JONES297502-4月-81
7698BLAKE285001-5月-81
7782CLARK245009-6月-81
7788SCOTT300019-4月-87
7839KING500017-11月-81
7902FORD300003-12月-81
已选择6行。
⏹但是在Oraclette10gRa这个版本之后,默认情况下scott用户没有创建视图的权限:
所以要进行如下操作:
Connsys/change_no_installassysdba;
Grantcreateviewtoc##scott;
⏹现在只要查询视图就可以实现与被封装的sql语句同样的功能:
⏹在Oracle中,针对视图也提供一个数据字典:
user_views;
⏹视图也相当于一张表:
◆案例:
2:
创建一张只包含20部门雇员信息的视图:
SQL>createorreplaceviewv_emp
2as
3selectempno,ename,sal,deptnofromt_emp
4where
5deptno=20;
视图已创建。
然后再进行查询:
SQL>select*fromv_emp;
EMPNOENAMESALDEPTNO
----------------------------------------
7369SMITH80020
7566JONES297520
7788SCOTT300020
7876ADAMS110020
7902FORD300020
⏹当然视图也可以进行增,删,改,查,但不推荐进行对视图数据的更改:
⏹通过视图实现了查询的方便:
创建视图的时候注意使用别名:
createorreplaceviewv_emp
as
select
d.deptno"部门编号",d.dname"部门名称",
d.loc"部门位置",count(e.ename)"总人数",
avg(e.sal)"平均工资",max(e.sal)"最高工资",
min(e.sal)"最低工资"
fromt_deptd,t_empe
where
e.deptno(+)=d.deptno
groupbyd.deptno,
d.dname,d.loc;
进行查询:
SQL>select*fromv_emp;
部门编号部门名称部门位置总人数平均工资最高工资
-------------------------------------------------------------------
最低工资
----------
20RESEARCHDALLAS521753000
800
40OPERATIONSBOSTON0
10ACCOUNTINGNEWYORK32916.666675000
1300
部门编号部门名称部门位置总人数平均工资最高工资
-------------------------------------------------------------------
最低工资
----------
30SALESCHICAGO61566.666672850
950
⏹还有要注意的是:
◆无法通过视图修改多个基表:
◆也无法更改经过合计后的数据:
◆只能更改基本数据:
因此不推荐:
◆Withcheckoption字句:
在创建视图的时候需要使用一些where字句做一些条件的的限制,但是默认情况下的视图创建完成之后,是可以通过视图去修改在where字句之中的字段内容的,而在此时就需要通过withcheckoption字句来保证视图的创建条件不被更新
SQL>createorreplaceviewv_emp
2as
3select*fromt_empwheredeptno=20
4withcheckoptionconstraintv_emp_vk;
SQL>updatev_empsetdeptno=40wheredeptno=20;
updatev_empsetdeptno=40wheredeptno=20
第1行出现错误:
ORA-01402:
视图WITHCHECKOPTIDNwhere子句违规
需要注意的是,虽然使用了withcheckoption后在where后的条件不能改变了,但是其他字段仍然可以改变:
但是视图不是真实数据,它属于映射数据,应该在基表之中更新才合适:
◆Withreadonly:
◆让视图中所有字段不可更新,则可以通过withreadonly字句控制:
◆案例:
createorreplaceviewv_emp
as
select*fromt_empwheredeptno=30
withreadonly;
SQL>updatev_empsetdeptno=20wheredeptno=30;
updatev_empsetdeptno=20wheredeptno=30
*
第1行出现错误:
ORA-01733:
此处不允许虚拟列
◆删除视图:
●Dropview视图名称:
●序列:
(sequence)
⏹概念:
在许多数据库之中都会为用户提供一个钟自动增长列的操作,也就是说序列可以自动的按照既定的规则实现数据的编号操作:
⏹完整序列的创建语法:
Createsequence序列名称:
[incrementby步长]
[startwith开始值]
[maxvalue最大值|nomaxvalue]
[cycle|nocycle]
[cache缓存大小|nocache]
⏹默认序列:
◆最小值0最大值是无限大
◆Nextval:
获取下一个值,(nextvalue)
◆Currval:
获取当前一个值(currentvalue)
◆Cache保存的是一个缓存的个数,而不是一个值:
◆创建时得注意:
CACHE值必须小于CYCLE值
◆CACHE默认的缓存个数是20;cycle表示表示循环的种子数的个数,例如从1开始,步长为2,最大值是9,则cycle表示的值为5(1,3,5,7,9)因此cache●案例:
SQL>createsequencemysequcnce4
2startwith1
3incrementby5
4maxvalue21
5cycle
6cache4;
序列已创建。
从上面的序列可以看出cycle为5(1,6,11,16,21),cache为4:
满足cache因此可以创建成功:
◆修改序列:
◆Oracle12C新特性自动增长列:
◆
●同义词:
⏹就是给一张表取一个别名:
⏹比如dual;就是sys用户下创建的一张同义词表,因此任何用户都可以访问:
创建一个公共同义词就可以被任何用户使用:
Createpublicsynonymsynonyn_namefortabler_name;
●伪劣:
在oracle数据库之中为了实现完整的关系数据库的功能,专门为用户提供了许多的伪劣,像之前在讲解序列的时候所使用的nextval和currval就是两个默认提供的操作伪劣,同时之前在使用的sysdate与systimestamp也属于伪劣(而此昂查询使用的dual称为伪表,)这些数据伪列帮助用户建立的,用户只需要按照要求使用即可,但是除了nextval和currval连个伪列之外,还提供了两个重要的伪列rownum,rowid
1:
rownum的使用:
SQL>selectempno,ename,sal,rownumfromt_emp;
EMPNOENAMESALROWNUM
----------------------------------------
7369SMITH8001
7499ALLEN16002
7521WARD12503
7566JONES29754
7654MARTIN12505
7698BLAKE28506
7782CLARK24507
7788SCOTT30008
7839KING50009
7844TURNER150010
7876ADAMS110011
EMPNOENAMESALROWNUM
----------------------------------------
7900JAMES95012
7902FORD300013
7934MILLER130014
2:
rowid的使用:
SQL>selectempno,ename,sal,rowidfromt_emp;
EMPNOENAMESALROWID
------------------------------------------------
7369SMITH800AAAM3jAAEAAAAGsAAA
7499ALLEN1600AAAM3jAAEAAAAGsAAB
7521WARD1250AAAM3jAAEAAAAGsAAC
7566JONES2975AAAM3jAAEAAAAGsAAD
7654MARTIN1250AAAM3jAAEAAAAGsAAE
7698BLAKE2850AAAM3jAAEAAAAGsAAF
7782CLARK2450AAAM3jAAEAAAAGsAAG
7788SCOTT3000AAAM3jAAEAAAAGsAAH
7839KING5000AAAM3jAAEAAAAGsAAI
7844TURNER1500AAAM3jAAEAAAAGsAAJ
7876ADAMS1100AAAM3jAAEAAAAGsAAK
EMPNOENAMESALROWID
------------------------------------------------
7900JAMES950AAAM3jAAEAAAAGsAAL
7902FORD3000AAAM3jAAEAAAAGsAAM
7934MILLER1300AAAM3jAAEAAAAGsAAN
已选择14行。
⏹案例:
删除添加在t_dept中的重复数据,只保留不重复数据:
◆那么就要找到min(rowed)在t_dept中
原数据为:
添加后为:
在现在的表中找min(rowed)这也就是最早的数据:
删除重复的数据:
◆Min(rowed):
查询是按什么字段分组就能得到不重复的字段,比如deptno重复,但是查询时是按dname分组,如果dname不重复就不会被删除,根据min(rowed)只可以删除重复在按什么字段分组的情况:
●案例1:
在上面的情况中,deptno是重复的,但是查min(rowid)是根据dname查询的,那么只要dname没有重复,就会被显示出来,删除的时候也是一样,只要dname没有重复,尽管deptno有重复,但是查询min(rowid)没有按照deptno字段分组查询,也是不会被删除掉的:
●案例2:
删除上面重复的行:
分析:
由于是因为deptno重复,删除时只能根据deptno分组:
●案例3:
删除上面重复的字段:
●总之:
可以根据min(rowed)删除各字段上重复的字段:
◆Rownum(重点):
关系到分页查询:
分页查询案例:
第一次显示前5条,
第二次显示5到第十条:
2:
也可以把显示伪列的当成是一张表,然后机型多表查询
◆
●索引:
在数据库中索引是一种专门用于数据库查询操作性能的一种手段,在Oracle之中为了维护这种查询特性,需要对某一类数据进行指定结构的排列。
但是在Oracle之中,针对于不同的情况会有不同的索引使用:
⏹B树索引,降序索引,位图索引,函数索引:
⏹1:
B树索引:
(又为:
B*Tree)是最为基本的索引姐都,在Oracle之中默认尽力的索引就是此类型的所以。
一般B树在检索高基数数列(该列上的重复内容较少或是没有)的时候可以提供高性能的检索操作:
◆案例
1:
查询工资大于1500的全部雇员:
◆跟踪机制,也就是说要找到这个满足sal>1500的雇员信息需要进行逐行扫描,如果信心多了就会影响程序的性能:
Setautotraceon;(打开跟踪机制)
B数索引:
SQL>createindexemp_salont_emp(sal);
索引已创建。
--------------------------------------------------------------------------------
-------
|0|SELECTSTATEMENT||12|444|2(0)|00:
00:
01|
|1|TABLEACCESSBYINDEXROWID|T_EMP|12|444|2(0)|00:
00:
01|
|*2|INDEXRANGESCAN|EMP_SAL|12||1(0)|00:
00:
01|
--------------------------------------------------------------------------------
-------
◆降序索引:
SQL>createindexemp_hiredate_ind_descont_emp(hiredate);
索引已创建。
SQL>
然后查看跟踪:
●函数索引:
◆