摘要:前言在總結(jié)的第一篇中,我們已經(jīng)總結(jié)了一些常用的相關(guān)的知識點了那么本篇主要總結(jié)關(guān)于視圖序列事務(wù)的一些內(nèi)容在數(shù)據(jù)庫中,我們可以把各種的語句分為四大類數(shù)據(jù)操縱語言,,,數(shù)據(jù)定義語言,,,數(shù)據(jù)控制語言事務(wù)控制語言,,回滾點批量操作何為批量操作,就是
前言
在Oracle總結(jié)的第一篇中,我們已經(jīng)總結(jié)了一些常用的SQL相關(guān)的知識點了...那么本篇主要總結(jié)關(guān)于Oralce視圖、序列、事務(wù)的一些內(nèi)容...
在數(shù)據(jù)庫中,我們可以把各種的SQL語句分為四大類...
(1)DML(數(shù)據(jù)操縱語言):select,insert,update,delete
(2)DDL(數(shù)據(jù)定義語言):create table,alter table,drop table,truncate table
(3)DCL(數(shù)據(jù)控制語言):grant select any table to scott/revoke select any table from scott
(4)TCL(事務(wù)控制語言):commit,rollback,savepoint to 回滾點
批量操作何為批量操作,就是一次性插入多條數(shù)據(jù)....在SQL中,我們查詢出來的數(shù)據(jù)可看成是一張表,那么我們在插入數(shù)據(jù)的時候,可以根據(jù)查詢出來的數(shù)據(jù)進行插入...這就可以看成是批量操作...
值得注意的是,如果沒有指定插入哪些字段的話,那么查詢出來的全部字段均會插入表中..
將xxx_emp表中所有20號部門的員工,復(fù)制到emp表中,批量插入,insert into 表名 select ...語法 insert into emp select * from xxx_emp where deptno=20;刪除操作對比
我們的刪除語法有三種:
delete from
truncate from
drop from
我們來對比一下他們的區(qū)別:
drop table
1)屬于DDL
2)不可回滾
3)不可帶where
4)表內(nèi)容和結(jié)構(gòu)刪除
5)刪除速度快
truncate table
1)屬于DDL
2)不可回滾
3)不可帶where
4)表內(nèi)容刪除
5)刪除速度快
delete from
1)屬于DML
2)可回滾
3)可帶where
4)表結(jié)構(gòu)在,表內(nèi)容要看where執(zhí)行的情況
5)刪除速度慢,需要逐行刪除
事務(wù)其實我們在JDBC章節(jié)中已經(jīng)講解過了,詳情可查看我JDBC的博文。
再次明確一下:事務(wù)就是讓一個不可分割的子操作形成一個整體,該整體要么全部執(zhí)行成功,要么全部執(zhí)行失敗
我們值得注意的是Oracle中的事務(wù)與Mysql中的事務(wù)操作是有些不同的:
Oracle的事務(wù)開始:
第一條DML操作做為事務(wù)開始【并不需要手動開啟事務(wù)】
Oracle的提交事務(wù)
(1)顯示提交:commit
(2)隱藏提交:DDL/DCL/exit(sqlplus工具)【注意】
Oracle的回滾事務(wù)
(1)顯示回滾:rollback
(2)隱藏回滾:關(guān)閉窗口(sqlplus工具),死機,掉電
因為Oracle有實例池這個概念,所以O(shè)racle支持回滾...
Oracle默認(rèn)支持的隔離級別是:read commited
Mysql默認(rèn)支持的隔離級別是:reapatable read
Oracle中設(shè)置事務(wù)隔離級別為serializable set transaction isolation level serializable;
在上一篇博文已經(jīng)說了,Oracle將表/用戶都看成是對象...那么我們怎么在scott用戶下訪問hr用戶下的表呢???
其實,我們只要在訪問表的時候,指定具體的用戶.數(shù)據(jù)庫表就行了,但是呢,還要看看該用戶有沒有權(quán)限查詢別的用戶的數(shù)據(jù)表,于是就需要賦予權(quán)限了...
聲明:scott或hr叫用戶名/方案名/空間名 scott--tiger hr-----lion 查詢當(dāng)前用戶是誰 show user; 查詢scott自己表空間下的所有對象時,可加,或不加用戶名select * from emp; select * from emp; 或 select * from scott.emp; 以sysdba身份解鎖hr普通帳戶 alter user hr account unlock; 以sysdba身份設(shè)置hr普通帳戶的密碼 alter user hr identified by lion; 當(dāng)scott查詢hr表空間下的所有表時,必須得加用戶名 select * from hr.jobs; 在默認(rèn)情況下,每個用戶只能查詢自已空間下的對象的權(quán)限,不能查詢其它用戶空間下的對象 以sysdba身份角色,授予scott用戶查詢所有用戶空間下的對象權(quán)限 grant select any table to scott; 以sysdba身份,撤銷scott用戶查詢所有用戶空間下的對象權(quán)限 revoke select any table from scott; scott自已查看自己所擁有的權(quán)限 select * from user_sys_privs; 從scott用戶空間導(dǎo)航到sysdba用戶空間 conn / as sysdba; 從sysdba用戶空間導(dǎo)航到scott用戶空間 conn scott/tiger; 從scott用戶空間導(dǎo)航到hr用戶空間 conn hr/lion; 查詢hr用戶空間中的所有對象 select * from tab; 從hr用戶空間導(dǎo)航到scott用戶空間 conn scott/tiger; 在scott用戶空間下,查詢hr用戶空間下的jobs表,必須加上hr用戶空間名 select * from hr.jobs;
視圖是一種基于數(shù)據(jù)表的一種虛表
(1)視圖是一種虛表
(2)視圖建立在已有表的基礎(chǔ)上, 視圖賴以建立的這些表稱為基表
(3)向視圖提供數(shù)據(jù)內(nèi)容的語句為 SELECT 語句,可以將視圖理解為存儲起來的 SELECT 語句
(4)視圖向用戶提供基表數(shù)據(jù)的另一種表現(xiàn)形式
(5)視圖沒有存儲真正的數(shù)據(jù),真正的數(shù)據(jù)還是存儲在基表中
(6)程序員雖然操作的是視圖,但最終視圖還會轉(zhuǎn)成操作基表
(7)一個基表可以有0個或多個視圖
為什么要用到視圖?有的時候,我們可能只關(guān)系一張數(shù)據(jù)表中的某些字段,而另外的一些人只關(guān)系同一張數(shù)據(jù)表的某些字段...
那么把全部的字段都都顯示給他們看,這是不合理的。我們應(yīng)該做到:他們想看到什么樣的數(shù)據(jù),我們就給他們什么樣的數(shù)據(jù)...一方面就能夠讓他們只關(guān)注自己的數(shù)據(jù),另一方面,我們也保證數(shù)據(jù)表一些保密的數(shù)據(jù)不會泄露出來...
還有另外一個原因:
我們在查詢數(shù)據(jù)的時候,常常需要編寫非常長的SQL語句,幾乎每次都要寫很長很長....上面已經(jīng)說了,視圖就是基于查詢的一種虛表,也就是說,視圖可以將查詢出來的數(shù)據(jù)進行封裝。。。那么我們在使用的時候就會變得非常方便...
小總結(jié):
(1)如果你不想讓用戶看到所有數(shù)據(jù)(字段,記錄),只想讓用戶看到某些的數(shù)據(jù)時,此時可以使用視圖
(2)當(dāng)你需要減化SQL查詢語句的編寫時,可以使用視圖,但不提高查詢效率
基于emp表所有列,創(chuàng)建視圖emp_view_1,create view 視圖名 as select對一張或多張基表的查詢 create view emp_view_1 as select * from emp; 默認(rèn)情況下,普通用戶無權(quán)創(chuàng)建視圖,得讓sysdba為你分配creare view的權(quán)限 以sysdba身份,授權(quán)scott用戶create view權(quán)限 grant create view to scott; 以sysdba身份,撤銷scott用戶create view權(quán)限 revoke create view from scott; 基于emp表指定列,創(chuàng)建視圖emp_view_2,該視圖包含編號/姓名/工資/年薪/年收入(查詢中使用列別名) create view emp_view_2 as select empno "編號",ename "姓名",sal "工資",sal*12 "年薪",sal*12+NVL(comm,0) "年收入" from emp; 基于emp表指定列,創(chuàng)建視圖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創(chuàng)建視圖的結(jié)構(gòu) 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; 創(chuàng)建視圖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; 創(chuàng)建視圖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】,再執(zhí)行上述delete操作,還行嗎? create or replace view emp_view_1 as select * from emp with read only; 不能進行delete操作了 刪除視圖中的【某條】記錄會影響基表嗎? 會影響基表 將【整個】視圖刪除,會影響表嗎? 不會影響基表 刪除視圖,會進入回收站嗎? 不會進入回收站 刪除基表會影響視圖嗎? 會影響視圖 閃回基表后,視圖有影響嗎? 視圖又可以正常工作了同義詞
我們在使用多表查詢,或者查詢出來的表字段意義不清晰的時候,我們就使用別名來替代....當(dāng)然了,別名只針對列名或表名
現(xiàn)在,我們已經(jīng)知道的對象有用戶/視圖/表等等其他對象了,Oracle也提供了同義詞【類似于別名】給我們進行使用
同義詞的作用
(1)縮短對象名字的長度
(2)方便訪問其它用戶的對象
創(chuàng)建與salgrade表對應(yīng)的同義詞,create synonym 同義詞 for 表名/視圖/其它對象 create synonym e for salgrade; create synonym ev5 for emp_view_5; 以sys身份授予scott普通用戶create synonym權(quán)限 grant create synonym to scott; 以sys身份從scott普通用戶撤銷create synonym權(quán)限 revoke create synonym from scott; 使用同義詞操作salgrade表 select * from s; 刪除同義詞 drop synonym ev5; 刪除同義詞,會影響基表嗎? 不會影響基表 刪除基表,會影響同義詞嗎? 會影響同義詞序列
Mysql的自動增長可以直接在創(chuàng)建表的時候,在字段后面跟上auto increament關(guān)鍵字就行了。那Oracle 有沒有自動增長策略呢???
Oracle使用的是序列這么一個對象....
(1)類似于MySQL中的auto_increment自動增長機制,但Oracle中無auto_increment機制
(2)是oracle提供的一個產(chǎn)生唯一數(shù)值型值的機制
(3)通常用于表的主健值
(4)序列只能保證唯一,不能保證連續(xù)
聲明:oracle中,只有rownum永遠保持從1開始,且繼續(xù)
(5)序列值,可放于內(nèi)存,取之較快
那oralce中的序列和Mysql中的自動增長有啥區(qū)別???
Mysql每張表都會維護一個自動增長的程序...
Oralce會把序列存放在內(nèi)存中,可以供幾張表使用...
有的同學(xué)可能會疑問,我們在分頁的時候用到了rownum這么一個偽列,為啥不用它來做自動增長的呢???
rownum的值雖然是唯一和連續(xù)的,但是不能一直唯一標(biāo)識該記錄...也就是說,一旦該記錄刪除了,那么rownum的值是會變的。
為什么要用序列
(1)以前我們?yōu)橹鹘≡O(shè)置值,需要人工設(shè)置值,容易出錯
(2)以前每張表的主健值,是獨立的,不能共享
為emp表的empno字段,創(chuàng)建序列emp_empno_seq,create sequence 序列名 create sequence emp_empno_seq; 刪除序列emp_empno_seq,drop sequence 序列名 drop sequence emp_empno_seq; 查詢emp_empno_seq序列的當(dāng)前值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,默認(rèn)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; 有了序列后,還能為主健手工設(shè)置值嗎? insert into emp(empno) values(9999); insert into emp(empno) values(7900); 刪除表,會影響序列嗎? 你無法做insert操作,表真正亡,序列亡 刪除序列,會影響表嗎? 不會 在hibernate中,如果是訪問oracle數(shù)據(jù)庫服務(wù)器,那么User.hbm.xml映射文件中關(guān)于標(biāo)簽如何配置呢?
什么是索引【Index】
(1)是一種快速查詢表中內(nèi)容的機制,類似于新華字典的目錄
(2)運用在表中某個/些字段上,但存儲時,獨立于表之外
為什么要用索引
(1)通過指針加速Oracle服務(wù)器的查詢速度
(2)通過rowid快速定位數(shù)據(jù)的方法,減少磁盤I/O
rowid是oracle中唯一確定每張表不同記錄的唯一身份證
索引表把數(shù)據(jù)變成是有序的....
快速定位到硬盤中的數(shù)據(jù)文件...
rowid的特點
(1)位于每個表中,但表面上看不見,例如:desc emp是看不見的
(2)只有在select中,顯示寫出rowid,方可看見
(3)它與每個表綁定在一起,表亡,該表的rowid亡,二張表rownum可以相同,但rowid必須是唯一的
(4)rowid是18位大小寫加數(shù)字混雜體,唯一表代該條記錄在DBF文件中的位置
(5)rowid可以參與=/like比較時,用""單引號將rowid的值包起來,且區(qū)分大小寫
(6)rowid是聯(lián)系表與DBF文件的橋梁
索引特點索引的特點
(1)索引一旦建立, Oracle管理系統(tǒng)會對其進行自動維護, 而且由Oracle管理系統(tǒng)決定何時使用索引
(2)用戶不用在查詢語句中指定使用哪個索引
(3)在定義primary key或unique約束后系統(tǒng)自動在相應(yīng)的列上創(chuàng)建索引
(4)用戶也能按自己的需求,對指定單個字段或多個字段,添加索引
需要注意的是:Oracle是自動幫我們管理索引的,并且如果我們指定了primary key或者unique約束,系統(tǒng)會自動在對應(yīng)的列上創(chuàng)建索引..
什么時候【要】創(chuàng)建索引
(1)表經(jīng)常進行 SELECT 操作
(2)表很大(記錄超多),記錄內(nèi)容分布范圍很廣
(3)列名經(jīng)常在 WHERE 子句或連接條件中出現(xiàn)
什么時候【不要】創(chuàng)建索引
(1)表經(jīng)常進行 INSERT/UPDATE/DELETE 操作
(2)表很小(記錄超少)
(3)列名不經(jīng)常作為連接條件或出現(xiàn)在 WHERE 子句中
為emp表的empno單個字段,創(chuàng)建索引emp_empno_idx,叫單列索引,create index 索引名 on 表名(字段,...) create index emp_empno_idx on emp(empno); 為emp表的ename,job多個字段,創(chuàng)建索引emp_ename_job_idx,多列索引/聯(lián)合索引 create index emp_ename_job on emp(ename,job); 如果在where中只出現(xiàn)job不使用索引 如果在where中只出現(xiàn)ename使用索引 我們提倡同時出現(xiàn)ename和job 注意:索引創(chuàng)建后,只有查詢表有關(guān),和其它(insert/update/delete)無關(guān),解決速度問題 刪除emp_empno_idx和emp_ename_job_idx索引,drop index 索引名 drop index emp_empno_idx; drop index emp_ename_job_idx;權(quán)限與用戶
一)用戶 Oracle中的用戶分為二大類 1)Oracle數(shù)據(jù)庫服務(wù)器創(chuàng)建時,由系統(tǒng)自動創(chuàng)建的用戶,叫系統(tǒng)用戶,如sys。 2)利用系統(tǒng)用戶創(chuàng)建的用戶,叫普通用戶,如scott,hr,c##tiger,zhaojun,... 》用sys登錄,查詢當(dāng)前Oracle數(shù)據(jù)庫服務(wù)器中已有用戶的名字和狀態(tài) username表示登錄名 expired&locked表示帳號過期和鎖定 open表示帳號現(xiàn)在可用 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; 二)創(chuàng)建與刪除普通用戶 可以在Oracle中創(chuàng)建新的普通用戶,創(chuàng)建普通用戶命令是:create user,在創(chuàng)建普通用戶的同時,應(yīng)該為其分配一個具體的表空間,通常叫users。 》用sys登錄,查詢Oracle中有哪些可用存儲空間,所有普通用戶默認(rèn)為users存儲空間 select * from v$tablespace; 》用sys登錄,創(chuàng)建普通用戶c##tiger,密碼為abc,默認(rèn)使用users存儲空間,即對應(yīng)硬盤上的一個DBF二進制文件 sqlplus / as sysdba; create user c##tiger identified by abc default tablespace users; 》用sys登錄,為c##tiger分配users空間無限制使用,即數(shù)據(jù)庫中DBF文件可以無限增加,一個DBF文件不夠,會創(chuàng)建第二個DBF文件 sqlplus / as sysdba; alter user c##tiger quota unlimited on users; 》用c##tiger登錄,能進orcl數(shù)據(jù)庫嗎? sqlplus c##tiger/abc 進不去orcl數(shù)據(jù)庫 》用sys登錄,刪除普通用戶c##tiger sqlplus / as sysdba; drop user c##tiger cascade; 三)了解系統(tǒng)用戶 sys是Oracle中一個重要的系統(tǒng)用戶,sys是Oracle中最高權(quán)限用戶,其角色為SYSDBA(系統(tǒng)管理員) sqlplus / as sysdba 四)權(quán)限 權(quán)限的最終作用于用戶。即所有用戶在數(shù)據(jù)庫內(nèi)的操作對象和可執(zhí)行的動作都是受到限制的。 Oracle中權(quán)限分為二大類: 1)系統(tǒng)權(quán)限 2)對象權(quán)限 五)系統(tǒng)權(quán)限 針對數(shù)據(jù)庫中特定操作的許可,例如:讓c##tiger能登錄到orcl數(shù)據(jù)庫,能在orcl數(shù)據(jù)庫中創(chuàng)建表 》用sys登錄,獲取系統(tǒng)權(quán)限的相關(guān)信息,例如:select any table表示針對所有表的select權(quán)限 sqlplus / as sysdba; select distinct privilege from dba_sys_privs; 》用sys登錄,為c##tiger分配create session與數(shù)據(jù)庫建立會話的權(quán)限,即允許該用戶登錄 sqlplus / as sysdba; grant create session to c##tiger; 》用c##tiger登錄,能進orcl數(shù)據(jù)庫嗎? sqlplus c##tiger/abc 能進去orcl數(shù)據(jù)庫 》用c##tiger登錄,創(chuàng)建一張tiger的表,能創(chuàng)建嗎? sqlplus c##tiger/abc create table tiger( name varchar2(20) ); 這時c##tiger沒有權(quán)限創(chuàng)建表 》用sys登錄,為c##tiger分配create table權(quán)限,即允許創(chuàng)建表 sqlplus / as sysdba; grant create table to c##tiger; 》用c##tiger登錄,創(chuàng)建一張tiger的表,能創(chuàng)建嗎? sqlplus c##tiger/abc create table tiger( name varchar2(20) ); 可以創(chuàng)建c##tiger表 》用sys登錄,查詢c##tiger所擁有的系統(tǒng)權(quán)限 sqlplus / as sysdba; select grantee,privilege from dba_sys_privs where lower(grantee) = "c##tiger"; grantee表示普通用戶名 privilege權(quán)限名 》用sys登錄,撤銷c##tiger的create table權(quán)限 sqlplus / as sysdba; revoke create table from c##tiger; 六)對象權(quán)限 用戶對已有對象的操作權(quán)限,包括: 1)select可用于表,視圖和序列 2)insert向表或視圖中插入新的記錄 3)update更新表中數(shù)據(jù) 4)delete刪除表中數(shù)據(jù) 5)execute函數(shù),過程的執(zhí)行 6)index為表創(chuàng)建索引 7)references為表創(chuàng)建外健 8)alter修改表或者序列的屬性 》用sys登錄,查詢c##tiger所擁有的對象權(quán)限 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表的所有權(quán)限,即增刪改查操作 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;
如果文章有錯的地方歡迎指正,大家互相交流。習(xí)慣在微信看技術(shù)文章,想要獲取更多的Java資源的同學(xué),可以關(guān)注微信公眾號:Java3y
文章版權(quán)歸作者所有,未經(jīng)允許請勿轉(zhuǎn)載,若此文章存在違規(guī)行為,您可以聯(lián)系管理員刪除。
轉(zhuǎn)載請注明本文地址:http://m.specialneedsforspecialkids.com/yun/17703.html
閱讀 2261·2023-04-26 01:50
閱讀 718·2021-09-22 15:20
閱讀 2597·2019-08-30 15:53
閱讀 1600·2019-08-30 12:49
閱讀 1714·2019-08-26 14:05
閱讀 2714·2019-08-26 11:42
閱讀 2310·2019-08-26 10:40
閱讀 2604·2019-08-26 10:38