oracle學習筆記（十四） 資料庫物件 索引 檢視 序列 同義詞

one發表於2019-05-23
Oracle筆記資料庫物件索引

資料庫物件

使用者模式：指資料庫使用者所建立和儲存資料物件的統稱。在訪問其它使用者模式的資料庫物件時需加上使用者模式。
如：scott.emp, scott.dept等。
資料庫物件包括：表、檢視、索引、序列、目錄、同義詞、資料庫使用者、儲存過程、函式、觸發器等。

同義詞

同義詞是現有資料庫物件的一個別名。

  1. 簡化SQL語句
  2. 隱藏物件的名稱和所有者

同義詞分為私有和公有的

--建立同義詞得通過sys進行授權
grant create [any] synonym to $username$; --授權，建立私有同義詞
grant create public synonym to $username$;--授權，建立公有同義詞

私有：只有當前使用者才能使用

--建立私有同義詞
create [or replace] synonym 同義詞名稱 for 物件（如：表,檢視等）; 
drop synonym  $同義詞名$; --刪除同義詞

create or replace synonym emp for employee;

公有：全部的使用者都能使用

--建立公有同義詞
create [or replace] public synonym 同義詞名稱 for 物件（如：表）;
drop public synonym $同義詞名$;--刪除同義詞

create or replace public synonym emp for employee;

序列

序列是用於生成唯一、連續序號的物件，如：1，2，3.......
作用：
序列常用於生成表的主鍵值、唯一鍵的值以及需要連續序號的場合。
補充：
序列可以是升序的，也可以是降序的。
一個序列產生的值可以同時供多個表使用，如果有這個需要的話。
建立序列：

--語法
CREATE SEQUNCE 序列名
  START WITH 初始值 
  【INCREMNT BY 步長】 --步長為負數，則是降序
  【MAXVALUE  最大值】 
  【MINVALUE 最小值】
  【CYCLE/NOCYCLE】
  【CACHE/NOCACH】預設oracle快取20個序列值
create sequence emp_seq
start with 1;

select emp_seq.nextval from dual;
--先執行，序列才會有資料,從1開始，步長預設為1，執行一次，序列的當前數值就會自加1

slect emp_seq.currval from dual;--返回當前的數值

--建立序列
create sequence emp_seq 
start with 8001; --沒加increment by 預設步長為1

--最初建立序列時初始值是沒有的，第一次訪問序列必須先訪問它的nextval屬性
select emp_seq.nextval from dual;
select emp_seq.nextval,emp_seq.currval from dual;

--修改序列，不能修改初始值(start with x)
select max(empno) from emp;

--刪除序列
drop sequence emp_seq;

--在插入時使用序列來生成主鍵值
insert into employee(empno,ename,job,sal) values(emp_seq.NEXTVAL, 'james','CLERK',3999);

--生成序列的下一個值
select emp_seq.nextval from dual;

--訪問序列的當前值
select 'XYZ-'||emp_seq.currval from dual;

檢視

檢視，被稱為虛擬表，可以簡化select語句
作用：

  • 提供了另外一種級別的表安全性
  • 隱藏的資料的複雜性
  • 簡化的使用者的SQL語句
  • 隔離基表（建立檢視時用到的表）結構的改變
  • 通過重新命名列，從另一個角度提供資料
  • 資料獨立性

和之前的序列一樣，需要sys進行授權

grant create view to $username$
--相當於複製一個表
create view emp_view as select * from employee
--隱藏了其他的屬性，只顯示員工編號，姓名以及部門
create view emp_view as select empno,ename,deptno from employee
--複雜查詢的結果當成一個表
--列出薪金高於公司平均薪金的所有員工，所在部門，上級領導，和工資等級
--如果需要使用當前的結果，使用select語句就簡單許多
create or replace view emp_sal_vw
as 
  select e.empno,
    e.ename,
    d.dname dept_name,
    m.empno as mgrNo,
    m.ename as manager,
    s.grade
  from employee e,
       employee m, 
       department d,
       salgrade s
  where e.mgr=m.empno(+)
    AND e.deptno=d.deptno
    AND e.sal>(select avg(sal) from employee)
    AND e.sal BETWEEN s.losal AND s.hisal;

索引

查詢時候資料量大的時候，通過索引可以提高查詢的效率（百萬條資料級別），在常用的列名（欄位）建立索引

--建立索引
create [unique] index $indexName$ on $tablename$(列名1,列名2...)

唯一索引和普通索引

索引有唯一索引和普通索引，如在多個列上建立索引則成為組合索引
唯一索引一般在創表的時候，把某列設定某個主鍵，系統就會自動為該列建立唯一索引

--普通索引
create index hiredate_idx on employee(hiredate);
--組合索引
create index emp_comp on employee(mgr,deptno);
--組合索引的順序可以是任意的，不過，此順序會影響查詢的時候是否會啟用索引查詢

--下面兩條是會啟用索進行查詢
select mgr,deptno from employee;
select mgr from employee;

--下面兩條是會不會啟用索進行查詢
select deptno from employee;
select deptno,mgr from employee;

函式索引

create index emp_job_fun on employee(lower(job));

--不啟用索引查詢
select * from employee where job='CLERK';

--啟用索引查詢
select * from employee where lower(job)='clerk';

相關文章