tags: Oracle
前言
在Oracle總結的第一篇中，我們已經總結了一些常用的SQL相關的知識點了...那麼本篇主要總結關於Oralce檢視、序列、事務的一些內容...
在資料庫中，我們可以把各種的SQL語句分為四大類...
- （1）DML（資料操縱語言）：select，insert，update，delete
- （2）DDL（資料定義語言）：create table，alter table，drop table，truncate table
- （3）DCL（資料控制語言）：grant select any table to scott/revoke select any table from scott
- （4）TCL（事務控制語言）：commit，rollback，savepoint to 回滾點
批量操作
何為批量操作，就是一次性插入多條資料....在SQL中，我們查詢出來的資料可看成是一張表，那麼我們在插入資料的時候，可以根據查詢出來的資料進行插入...這就可以看成是批量操作...
值得注意的是，如果沒有指定插入哪些欄位的話，那麼查詢出來的全部欄位均會插入表中..
將xxx_emp表中所有20號部門的員工，複製到emp表中，批量插入，insert into 表名 select ...語法
insert into emp
select *
from xxx_emp
where deptno=20;
複製程式碼刪除操作對比
我們的刪除語法有三種：
- delete from
- truncate from
- drop from
我們來對比一下他們的區別：
drop table
- 1)屬於DDL
- 2)不可回滾
- 3)不可帶where
- 4)表內容和結構刪除
- 5)刪除速度快
truncate table
- 1)屬於DDL
- 2)不可回滾
- 3)不可帶where
- 4)表內容刪除
- 5)刪除速度快
delete from
- 1)屬於DML
- 2)可回滾
- 3)可帶where
- 4)表結構在，表內容要看where執行的情況
- 5)刪除速度慢,需要逐行刪除
事務
事務其實我們在JDBC章節中已經講解過了：blog.csdn.net/hon_3y/arti…
再次明確一下：事務就是讓一個不可分割的子操作形成一個整體，該整體要麼全部執行成功，要麼全部執行失敗
我們值得注意的是Oracle中的事務與Mysql中的事務操作是有些不同的：
Oracle的事務開始：
- 第一條DML操作做為事務開始【並不需要手動開啟事務】
Oracle的提交事務
- （1）顯示提交：commit
- （2）隱藏提交：DDL/DCL/exit(sqlplus工具)【注意】
Oracle的回滾事務
- （1）顯示回滾：rollback
- （2）隱藏回滾：關閉視窗(sqlplus工具)，當機，掉電
因為Oracle有例項池這個概念，所以Oracle支援回滾...
Oracle預設支援的隔離級別是：read commited
Mysql預設支援的隔離級別是：reapatable read
Oracle中設定事務隔離級別為serializable
set transaction isolation level serializable;
複製程式碼訪問其他使用者的物件
在上一篇博文已經說了，Oracle將表/使用者都看成是物件...那麼我們怎麼在scott使用者下訪問hr使用者下的表呢？？？
其實，我們只要在訪問表的時候，指定具體的使用者.資料庫表就行了，但是呢，還要看看該使用者有沒有許可權查詢別的使用者的資料表，於是就需要賦予許可權了...
練習：
宣告：scott或hr叫使用者名稱/方案名/空間名
scott--tiger
hr-----lion
查詢當前使用者是誰
show user;
查詢scott自己表空間下的所有物件時，可加，或不加使用者名稱select * from emp;
select * from emp;
或
select * from scott.emp;
以sysdba身份解鎖hr普通帳戶
alter user hr account unlock;
以sysdba身份設定hr普通帳戶的密碼
alter user hr identified by lion;
當scott查詢hr表空間下的所有表時，必須得加使用者名稱
select * from hr.jobs;
在預設情況下，每個使用者只能查詢自已空間下的物件的許可權，不能查詢其它使用者空間下的物件
以sysdba身份角色，授予scott使用者查詢所有使用者空間下的物件許可權
grant select any table to scott;
以sysdba身份，撤銷scott使用者查詢所有使用者空間下的物件許可權
revoke select any table from scott;
scott自已檢視自己所擁有的許可權
select * from user_sys_privs;
從scott使用者空間導航到sysdba使用者空間
conn / as sysdba;
從sysdba使用者空間導航到scott使用者空間
conn scott/tiger;
從scott使用者空間導航到hr使用者空間
conn hr/lion;
查詢hr使用者空間中的所有物件
select * from tab;
從hr使用者空間導航到scott使用者空間
conn scott/tiger;
在scott使用者空間下，查詢hr使用者空間下的jobs表，必須加上hr使用者空間名
select * from hr.jobs;
複製程式碼檢視
檢視是一種基於資料表的一種虛表
- （1）檢視是一種虛表
- （2）檢視建立在已有表的基礎上, 檢視賴以建立的這些表稱為基表
- （3）向檢視提供資料內容的語句為 SELECT 語句,可以將檢視理解為儲存起來的 SELECT 語句
- （4）檢視向使用者提供基表資料的另一種表現形式
- （5）檢視沒有儲存真正的資料，真正的資料還是儲存在基表中
- （6）程式設計師雖然操作的是檢視，但最終檢視還會轉成操作基表
- （7）一個基表可以有0個或多個檢視
為什麼要用到檢視？
有的時候，我們可能只關係一張資料表中的某些欄位，而另外的一些人只關係同一張資料表的某些欄位...
那麼把全部的欄位都都顯示給他們看，這是不合理的。我們應該做到：他們想看到什麼樣的資料，我們就給他們什麼樣的資料...一方面就能夠讓他們只關注自己的資料，另一方面，我們也保證資料表一些保密的資料不會洩露出來...
還有另外一個原因：
我們在查詢資料的時候，常常需要編寫非常長的SQL語句，幾乎每次都要寫很長很長....上面已經說了，檢視就是基於查詢的一種虛表，也就是說，檢視可以將查詢出來的資料進行封裝。。。那麼我們在使用的時候就會變得非常方便...
小總結：
- （1）如果你不想讓使用者看到所有資料（欄位，記錄），只想讓使用者看到某些的資料時，此時可以使用檢視
- （2）當你需要減化SQL查詢語句的編寫時，可以使用檢視，但不提高查詢效率
練習##
基於emp表所有列，建立檢視emp_view_1，create view 檢視名 as select對一張或多張基表的查詢
create view emp_view_1
as
select * from emp;
預設情況下，普通使用者無權建立檢視，得讓sysdba為你分配creare view的許可權
以sysdba身份，授權scott使用者create view許可權
grant create view to scott;
以sysdba身份，撤銷scott使用者create view許可權
revoke create view from scott;
基於emp表指定列，建立檢視emp_view_2，該檢視包含編號/姓名/工資/年薪/年收入（查詢中使用列別名）
create view emp_view_2
as
select empno "編號",ename "姓名",sal "工資",sal*12 "年薪",sal*12+NVL(comm,0) "年收入"
from emp;
基於emp表指定列，建立檢視emp_view_3(a,b,c,d,e)，包含編號/姓名/工資/年薪/年收入（檢視中使用列名）
create view emp_view_3(a,b,c,d,e)
as
select empno "編號",ename "姓名",sal "工資",sal*12 "年薪",sal*12+NVL(comm,0) "年收入"
from emp;
查詢emp_view_3建立檢視的結構
desc emp_view_3;
修改emp_view_3(id,name,salary,annual,income)檢視，create or replace view 檢視名 as 子查詢
create or replace view emp_view_3(id,name,salary,annual,income)
as
select empno "編號",ename "姓名",sal "工資",sal*12 "年薪",sal*12+NVL(comm,0) "年收入"
from emp;
查詢emp表，求出各部門的最低工資，最高工資，平均工資
select min(sal),max(sal),round(avg(sal),0),deptno
from emp
group by deptno;
建立檢視emp_view_4，檢視中包含各部門的最低工資，最高工資，平均工資
create or replace view emp_view_4
as
select deptno "部門號",min(sal) "最低工資",max(sal) "最高工資",round(avg(sal),0) "平均工資"
from emp
group by deptno;
建立檢視emp_view_5，檢視中包含員工編號，姓名，工資，部門名，工資等級
create or replace view emp_view_5
as
select e.empno "編號",e.ename "姓名",e.sal "工資",d.dname "部門名",s.grade "工資等級"
from emp e,dept d,salgrade s
where (e.deptno=d.deptno) and (e.sal between s.losal and s.hisal);
刪除檢視emp_view_1中的7788號員工的記錄，使用delete操作，會影響基表嗎
delete from emp_view_1 where empno=7788;寫法正確，會影響基表
修改emp_view_1為只讀檢視【with read only】，再執行上述delete操作，還行嗎？
create or replace view emp_view_1
as
select * from emp
with read only;
不能進行delete操作了
刪除檢視中的【某條】記錄會影響基表嗎？
會影響基表
將【整個】檢視刪除，會影響表嗎？
不會影響基表
刪除檢視，會進入回收站嗎？
不會進入回收站
刪除基表會影響檢視嗎？
會影響檢視
閃回基表後,檢視有影響嗎？
檢視又可以正常工作了
複製程式碼同義詞
我們在使用多表查詢，或者查詢出來的表欄位意義不清晰的時候，我們就使用別名來替代....當然了，別名只針對列名或表名
現在，我們已經知道的物件有使用者/檢視/表等等其他物件了，Oracle也提供了同義詞【類似於別名】給我們進行使用
同義詞的作用
- （1）縮短物件名字的長度
- （2）方便訪問其它使用者的物件
練習：
建立與salgrade表對應的同義詞，create synonym 同義詞 for 表名/檢視/其它物件
create synonym e for salgrade;
create synonym ev5 for emp_view_5;
以sys身份授予scott普通使用者create synonym許可權
grant create synonym to scott;
以sys身份從scott普通使用者撤銷create synonym許可權
revoke create synonym from scott;
使用同義詞操作salgrade表
select * from s;
刪除同義詞
drop synonym ev5;
刪除同義詞，會影響基表嗎？
不會影響基表
刪除基表，會影響同義詞嗎？
會影響同義詞
複製程式碼序列
Mysql的自動增長可以直接在建立表的時候，在欄位後面跟上auto increament關鍵字就行了。那Oracle 有沒有自動增長策略呢？？？
Oracle使用的是序列這麼一個物件....
- （1）類似於MySQL中的auto_increment自動增長機制，但Oracle中無auto_increment機制
- （2）是oracle提供的一個產生唯一數值型值的機制
- （3）通常用於表的主健值
- （4）序列只能保證唯一，不能保證連續
-
宣告：**oracle中，只有rownum永遠保持從1開始，且繼續** 複製程式碼
-
- （5）序列值，可放於記憶體，取之較快
那oralce中的序列和Mysql中的自動增長有啥區別？？？
- Mysql每張表都會維護一個自動增長的程式...
- Oralce會把序列存放在記憶體中，可以供幾張表使用...
有的同學可能會疑問，我們在分頁的時候用到了rownum這麼一個偽列，為啥不用它來做自動增長的呢？？？
rownum的值雖然是唯一和連續的，但是不能一直唯一標識該記錄...也就是說，一旦該記錄刪除了，那麼rownum的值是會變的。
為什麼要用序列
- （1）以前我們為主健設定值，需要人工設定值，容易出錯
- （2）以前每張表的主健值，是獨立的，不能共享
練習
為emp表的empno欄位，建立序列emp_empno_seq，create sequence 序列名
create sequence emp_empno_seq;
刪除序列emp_empno_seq，drop sequence 序列名
drop sequence emp_empno_seq;
查詢emp_empno_seq序列的當前值currval和下一個值nextval，第一次使用序列時，必須選用：序列名.nextval
select emp_empno_seq.nextval from dual;
select emp_empno_seq.currval from dual;
使用序列，向emp表插入記錄，empno欄位使用序列值
insert into emp(empno) values(emp_empno_seq.nextval);
insert into emp(empno) values(emp_empno_seq.nextval);
insert into emp(empno) values(emp_empno_seq.nextval);
修改emp_empno_seq序列的increment by屬性為20，預設start with是1，alter sequence 序列名
alter sequence emp_empno_seq
increment by 20;
修改修改emp_empno_seq序列的的increment by屬性為5
alter sequence emp_empno_seq
increment by 5;
修改emp_empno_seq序列的start with屬性，行嗎
alter sequence emp_empno_seq
start with 100;
有了序列後，還能為主健手工設定值嗎？
insert into emp(empno) values(9999);
insert into emp(empno) values(7900);
刪除表，會影響序列嗎？
你無法做insert操作,表真正亡，序列亡
刪除序列，會影響表嗎？
不會
在hibernate中，如果是訪問oracle資料庫伺服器，那麼User.hbm.xml對映檔案中關於<id>標籤如何配置呢？
<id name="id" column="id">
<generator class="increment/identity/uuid/【sequence】/【native】"/>
</id>
複製程式碼索引
什麼是索引
什麼是索引【Index】
（1）是一種快速查詢表中內容的機制，類似於新華字典的目錄 （2）運用在表中某個/些欄位上，但儲存時，獨立於表之外
為什麼要用索引
為什麼要用索引
- （1）通過指標加速Oracle伺服器的查詢速度
- （2）通過rowid快速定位資料的方法，減少磁碟I/O
-
rowid是oracle中唯一確定每張表不同記錄的唯一身份證 複製程式碼
-
索引表把資料變成是有序的....
快速定位到硬碟中的資料檔案...
rowid特點
rowid的特點
- （1）位於每個表中，但表面上看不見，例如：desc emp是看不見的
- （2）只有在select中，顯示寫出rowid，方可看見
- （3）它與每個表繫結在一起，表亡，該表的rowid亡，二張表rownum可以相同，但rowid必須是唯一的
- （4）rowid是18位大小寫加數字混雜體，唯一表代該條記錄在DBF檔案中的位置
- （5）rowid可以參與=/like比較時，用''單引號將rowid的值包起來，且區分大小寫
- （6）rowid是聯絡表與DBF檔案的橋樑
索引特點
索引的特點
- （1）索引一旦建立,** Oracle管理系統會對其進行自動維護**, 而且由Oracle管理系統決定何時使用索引
- （2）使用者不用在查詢語句中指定使用哪個索引
- （3）在定義primary key或unique約束後系統自動在相應的列上建立索引
- （4）使用者也能按自己的需求，對指定單個欄位或多個欄位，新增索引
需要注意的是：Oracle是自動幫我們管理索引的，並且如果我們指定了primary key或者unique約束，系統會自動在對應的列上建立索引..
什麼時候【要】建立索引
- （1）表經常進行 SELECT 操作
- （2）表很大(記錄超多)，記錄內容分佈範圍很廣
- （3）列名經常在 WHERE 子句或連線條件中出現
什麼時候【不要】建立索引
- （1）表經常進行 INSERT/UPDATE/DELETE 操作
- （2）表很小(記錄超少)
- （3）列名不經常作為連線條件或出現在 WHERE 子句中
練習
為emp表的empno單個欄位，建立索引emp_empno_idx，叫單列索引，create index 索引名 on 表名(欄位,...)
create index emp_empno_idx
on emp(empno);
為emp表的ename,job多個欄位，建立索引emp_ename_job_idx，多列索引/聯合索引
create index emp_ename_job
on emp(ename,job);
如果在where中只出現job不使用索引
如果在where中只出現ename使用索引
我們提倡同時出現ename和job
注意：索引建立後，只有查詢表有關，和其它（insert/update/delete）無關,解決速度問題
刪除emp_empno_idx和emp_ename_job_idx索引，drop index 索引名
drop index emp_empno_idx;
drop index emp_ename_job_idx;
複製程式碼許可權與使用者
練習
一）使用者
Oracle中的使用者分為二大類
1）Oracle資料庫伺服器建立時，由系統自動建立的使用者，叫系統使用者，如sys。
2）利用系統使用者建立的使用者，叫普通使用者，如scott,hr，c##tiger,zhaojun,...
》用sys登入，查詢當前Oracle資料庫伺服器中已有使用者的名字和狀態
username表示登入名
expired&locked表示帳號過期和鎖定
open表示帳號現在可用
sqlplus / as sysdba;
col username for a30;
col account_status for a30;
set pagesize 100;
select username,account_status from dba_users;
查詢Oracle中有哪些使用者
select * from all_users;
二）建立與刪除普通使用者
可以在Oracle中建立新的普通使用者，建立普通使用者命令是：create user，在建立普通使用者的同時，應該為其分配一個具體的表空間，通常叫users。
》用sys登入，查詢Oracle中有哪些可用儲存空間，所有普通使用者預設為users儲存空間
select * from v$tablespace;
》用sys登入，建立普通使用者c##tiger，密碼為abc，預設使用users儲存空間，即對應硬碟上的一個DBF二進位制檔案
sqlplus / as sysdba;
create user c##tiger identified by abc default tablespace users;
》用sys登入，為c##tiger分配users空間無限制使用，即資料庫中DBF檔案可以無限增加，一個DBF檔案不夠，會建立第二個DBF檔案
sqlplus / as sysdba;
alter user c##tiger quota unlimited on users;
》用c##tiger登入，能進orcl資料庫嗎？
sqlplus c##tiger/abc
進不去orcl資料庫
》用sys登入，刪除普通使用者c##tiger
sqlplus / as sysdba;
drop user c##tiger cascade;
三）瞭解系統使用者
sys是Oracle中一個重要的系統使用者，sys是Oracle中最高許可權使用者，其角色為SYSDBA（系統管理員）
sqlplus / as sysdba
四）許可權
許可權的最終作用於使用者。即所有使用者在資料庫內的操作物件和可執行的動作都是受到限制的。
Oracle中許可權分為二大類：
1）系統許可權
2）物件許可權
五）系統許可權
針對資料庫中特定操作的許可，例如：讓c##tiger能登入到orcl資料庫，能在orcl資料庫中建立表
》用sys登入，獲取系統許可權的相關資訊，例如：select any table表示針對所有表的select許可權
sqlplus / as sysdba;
select distinct privilege from dba_sys_privs;
》用sys登入，為c##tiger分配create session與資料庫建立會話的許可權，即允許該使用者登入
sqlplus / as sysdba;
grant create session to c##tiger;
》用c##tiger登入，能進orcl資料庫嗎？
sqlplus c##tiger/abc
能進去orcl資料庫
》用c##tiger登入，建立一張tiger的表，能建立嗎？
sqlplus c##tiger/abc
create table tiger(
name varchar2(20)
);
這時c##tiger沒有許可權建立表
》用sys登入，為c##tiger分配create table許可權，即允許建立表
sqlplus / as sysdba;
grant create table to c##tiger;
》用c##tiger登入，建立一張tiger的表，能建立嗎？
sqlplus c##tiger/abc
create table tiger(
name varchar2(20)
);
可以建立c##tiger表
》用sys登入，查詢c##tiger所擁有的系統許可權
sqlplus / as sysdba;
select grantee,privilege from dba_sys_privs where lower(grantee) = 'c##tiger';
grantee表示普通使用者名稱
privilege許可權名
》用sys登入，撤銷c##tiger的create table許可權
sqlplus / as sysdba;
revoke create table from c##tiger;
六）物件許可權
使用者對已有物件的操作許可權，包括：
1）select可用於表，檢視和序列
2）insert向表或檢視中插入新的記錄
3）update更新表中資料
4）delete刪除表中資料
5）execute函式，過程的執行
6）index為表建立索引
7）references為表建立外健
8）alter修改表或者序列的屬性
》用sys登入，查詢c##tiger所擁有的物件許可權
sqlplus / as sysdba;
col grantee for a10;
col table_name for a10;
col privilege for a20;
select grantee,table_name,privilege from dba_tab_privs where lower(grantee) = 'c##tiger';
》用sys登入，為c##tiger分配對tiger表的所有許可權，即增刪改查操作
sqlplus / as sysdba;
grant all on c##tiger.tiger to c##tiger;
注意：c##tiger表示空間名
tiger表示該空間下的表名
C##TIGER TIGER FLASHBACK
C##TIGER TIGER DEBUG
C##TIGER TIGER QUERY REWRITE
C##TIGER TIGER ON COMMIT REFRESH
C##TIGER TIGER REFERENCES
C##TIGER TIGER UPDATE
C##TIGER TIGER SELECT
C##TIGER TIGER INSERT
C##TIGER TIGER INDEX
C##TIGER TIGER DELETE
C##TIGER TIGER ALTER
》用c##tiger登入，對tiger表進行增刪改查操作
sqlplus c##tiger/abc;
insert into tiger(name) values('AA');
update tiger set name = 'BB';
delete from tiger where rownum = 1;
select * from tiger;
複製程式碼如果您覺得這篇文章幫助到了您，可以給作者一點鼓勵