Oracle資料庫習題整理

閱讀目錄

• 1.資料庫核心
• 2.關系型資料庫
• 3.權限
• 4.邏輯結構
• 5.執行個體
• 6.表空間
• 7.解鎖/加鎖文法
• 8.日志檔案 控制檔案
• 9.檢視表結構
• 10.配置檔案注釋#
• 11.資料庫參數檔案
• 12.sqlplus登入
• 13.單引号 雙引号 不加引号
• 14.sql文法
• 15.oracle函數
• 16.索引index
• 17.視圖view
• 18.序列sequence
• 19.限制 完整性限制
• 20.存儲過程 pl/sql塊
• 21.觸發器

1.資料庫核心

資料庫的核心是資料庫管理系統，資料庫的核心任務是資料管理。

2.關系型資料庫

主流的關系型資料庫有：Oracle、SQL Server、MySql、DB2、Sybase、MicroSoft Access、Postgresql、SQLite

主流的非關系型資料庫有：、Redis、Memcaced

列存儲資料庫：Hbase

面向文檔資料庫：Mongodb

圖形資料庫：Neo4J、InfoGrid

搜尋引擎資料庫：Solr、Elasticsearch

oracle 11g也是Oracle，關系型

3.權限

使用者：

sys是Oracle最進階管理者，system是Oracle普通管理者。scott是普通使用者。

權限：

系統權限：

DBA：全部權限。隻有DBA才可以建立資料庫結構

RESOURCE：可以建立實體。

CONNECT：隻能連接配接。

對于普通使用者授予後兩個，對于dba使用者授予三個。

系統使用者隻能由sys、system（擁有dba權限）使用者授出。

對象權限：使用者對于表、視圖、序列、過程的操作權限。

對象權限：使用者對于表、視圖、序列、過程的操作權限。

角色：一些權限的集合

标準角色：CONNECT, RESOURCE, DBA

dba是一種角色，也是權限，但sysdba不是角色，隻是一種身份。

4.邏輯結構

Oracle邏輯結構：表空間、段、區、資料塊

表空間：資料庫的邏輯劃分。多個表的邏輯存儲空間。表空間在實體上由一個或多個磁盤檔案組成。每個資料庫至少有一個表空間。

表空間分為：系統表空間（每個Oracle資料庫必須具備的）、SYSAUX表空間、臨時表空間、撤銷表空間（復原表空間）、使用者表空間

段（Segment）：多個資料區構成。段内的資料區可以不連續，可以跨越多個檔案。段是為了儲存特定對象（表段、索引段、復原段、臨時段）配置設定的一系列資料區。

區（Extent）：是一組連續的資料塊。區不能跨越多個資料檔案。一個Oracle對象至少包括一個資料區。

資料塊（Data Blocks）：資料塊是Oracle最小的存儲機關，資料存放在塊中，每次存取也是以塊為機關。Oracle塊一般是作業系統塊的整數倍。

塊由塊頭、表目錄、行目錄、剩餘空間、行資料組成

塊的标準大小由初始化參數DB_BLOCK_SIZE指定。大小一樣的叫标準塊，不一樣的叫非标準塊。DB_BLOCK_SIZE有4K,8K,16K…等等。

模式：模式是一個資料庫使用者擁有的。使用者下所有資料庫對象都會歸屬到資料庫模式。一般與使用者同名。

使用者：用于認證登入，連接配接和通路資料庫。

5.執行個體

測試使用者：Scott tiger

Oracle預設端口号：1521

資料庫：oracle隻有一個大資料庫。是資料的實體存儲，包括資料檔案、控制檔案、日志檔案等

執行個體：一個Oracle執行個體包括一系列的背景程序和記憶體結構。一個資料庫可以由多個執行個體構成。記憶體又分為SGA（system global area）和PGA（Process global area），SGA是執行個體的基本組成部分，會在執行個體啟動時配置設定。SGA由共享池、資料緩沖池、日志緩沖池組成。

使用者：使用者是在執行個體下建成的。不同執行個體可以有同名使用者。

表空間：管理資料的邏輯概念，表空間隻和資料檔案産生關系。一個表空間可以有多個資料檔案，但是一個資料檔案隻能隸屬于一個表空間。删除一個資料檔案，隻能删除它所在的表空間。

執行個體名：instance_name，也稱為system identifier(sid)。

預設sid是orcl。

區分： oracle_sid就是一個環境變量。

程序：常見Oracle程序有：使用者程序、服務程序、背景程序、其他可選程序

使用者程序：在使用者連接配接資料庫時産生，請求Oracle伺服器連接配接。必須要先建立一個連接配接，不會直接和伺服器連接配接。

服務程序：在連接配接執行個體并建立使用者會話時産生。當用戶端處理請求緩沖時，伺服器程序會搜尋緩沖緩存以獲得緩沖。如果資料庫在記憶體中發現緩沖器，則會出現緩存命中。資料讀寫由服務程序完成。

背景程序：維持實體與記憶體之間的聯系，維護資料庫讀寫。使用者程序和服務程序通過背景程序進行資料交換。當Oracle執行個體被啟動時，啟動相關的背景程序。

服務：OracleOraHome81TNSListener、OracleServiceORCL需要開啟。

SYS和SYSTEM 是每個ORACLE 資料庫系統預設安裝的兩個帳戶。SYS 是所有内部資料庫表、結構、過程包、等擁有者，此外它還擁有 V$ 和資料字典視圖，并建立所有封裝的資料庫角色(DBA，CONNECT，RESOURCE)。

sys是一個唯一能通路特定内部資料字典的使用者。 System 也是在安裝ORACLE 時建立的使用者，用于 DBA 任務的管理。

sys模式存儲資料基表和視圖。

TNS是Oracle net的一部分，全稱是Transparent Network Substrate。就是監聽的服務OracleOraDb11g_home1TNSListener。預設使用1521端口号。

TNS的配置檔案是tnsnames.ora，可以修改主機号、端口号等。

使用Oracle連接配接JDBC，有三種方式：SERVICE_NAME、SID和TNSName。

SERVICE_NAME方式：jdbc:oracle:thin:@//:/<SERVICE_NAME>

SID連接配接方式：jdbc:oracle:thin:@::

或：jdbc:oracle:thin:@:/

TNSName連接配接方式：jdbc:oracle:thin:@

6.表空間

Oracle10之前是給未指定default tablespace的使用者配置設定system，10開始是配置設定users。

7.解鎖/加鎖文法

解鎖賬戶：alter user 使用者名稱 account unlock;

加鎖賬戶：alter user 使用者名稱 account lock;

8.日志檔案 控制檔案

日志檔案作用：資料在塊在資料緩沖區被修改，單不會立即寫入到實體檔案中，Oracle先将資料塊的變動情況寫入日志檔案中去，再寫入表的實體檔案。

日志檔案分為重做日志檔案和歸檔日志檔案兩種。重做日志檔案是oracle正常運作不可缺少的一部分，主要記錄了資料庫操作過程，也就是資料的所有更新操作。

控制檔案作用：控制檔案是資料庫的一個二進制檔案，它主要記錄資料庫的名稱、資料庫的資料檔案存放位置等資訊。一個控制檔案隻能屬于一個資料庫。如果控制檔案丢失，這資料庫就無法操作。

9.檢視表結構

DESC：用于檢視表結構、列名和資料類型

show tables/show databases都是mysql中的文法。

show user是SQLPlus的寫法，oracle中是select user from dual

10.配置檔案注釋#

參數檔案中#表示注釋。

11.資料庫參數檔案

init.ora參數檔案的作用：得到資料庫名和控制檔案位置

手工建立新的資料庫時，需要：

1.建立相關目錄

2.建立一個新的初始化檔案（拷貝init.ora）

3.配置環境變量oracle_sid

4.建立執行個體oradim –new –sid book

5.建立密碼檔案

C:>orapwd file=D:\oracle\product\10.1.0\db_1\database\pwdbook.ora password=bookstore entries=2

6.啟動資料庫到nomount(執行個體)狀态

7.執行建庫腳本

8.執行catalog腳步本建立資料字典

9. 執行catproc建立package包

10. 執行pupbld

11. 由初始化參數檔案建立spfile檔案

12. 執行scott腳本建立scott模式

13. SQL>alter database open

資料庫啟動分三步：

1 startup nomount

2 alter database mount;

3 alter database open;

startup nomount選項：（讀初始化參數檔案，啟動執行個體）

startup mount 選項：（打開控制檔案）

startup open 選項：（打開資料檔案，日志檔案）

資料庫從init.ora中得知控制檔案的位置。一個資料庫至少一個控制檔案

建立控制檔案的時候，一定要處于no mount狀态。

12.sqlplus登入

cmd指令：SQLPLUS username [/password] [@connect_identifier] [AS SYSOPER|SYSDBA]

登陸時可以使用IP+端口+服務名或主機名+端口+服務名

SQL>conn bys/[email protected]:1521/orcl

13.單引号 雙引号 不加引号

雙引号：對字段、對象名的引用

單引号：常量值

不加引号：會報沒有逗号的錯誤

單引号的轉義：’’’’ 第一個和第四個單引号表示引用常量，第二個是對第三個單引号的轉義

initcap(“”)會将一串由字母、空格、數字、下劃線的字元串，單詞首字母大寫，其他字母小寫傳回。

14.sql文法

執行順序：from where group by having select order by

右外和左外互換：左右表也換

行：表中的一行

組：group by 後的一行

where隻能篩選分組前，是以是限制行。having隻能篩選分組後，是以是限制組。

單行子查詢：查詢結果隻傳回一行

多行子查詢：查詢結果傳回多行

單行子查詢可以使用單行比較運算符（大于小于等于不等于…），多行子查詢可以使用多行比較運算符Ø+（in any all）

count(*)和count(1)都不忽略null，但是count(colname)會忽略空值。

移動平均數是分析時序資料的值。用分組函數實作。

排它鎖：寫鎖

共享鎖：讀鎖

行級排他鎖？

針對行資料修改，事務占有的排他鎖，稱作行級排它鎖（或排他的行鎖，或粒度為行的排它鎖

行資料修改的修改不是指資料内容的修改，它是指新增行，删除行，修改行内容。

for update會在一行或多行放置排它鎖

oracle 集合運算符：intersect A交B union A并B minus A-B

直接用逗号連接配接兩個表，是笛卡爾積運算。

DDL：create drop alter truncate comment rename

DML：update insert delete merge call (select 廣義上也是DML) lock table explain plan

DQL：select

DCL：grant revoke

TCL：commit rollback savepoint set transaction

可以join自己，是以是一張表

union在合并後會排序去重，union all不去重。是以sql優化應該選擇union all，不去重效率高。

count(*)不可能，因為空表傳回0。

max(col1)空表傳回0，concat()隻要參數有一個是null就會傳回null。

需要傳回全部學生，不需要所有宿舍，是以是左外連接配接。

分組函數如果求一列也不需要去重，可以不加group by。

delete from 表名 ，沒有 *。

因為使用了單行比較運算符>，是以是單行子查詢。

一個為null，全部為null

15.oracle函數

round(數字,3)：千位四舍五入

round(數字,2)：百位四舍五入

round(數字,1)：十位四舍五入

round(數字,0)：個位四舍五入

round(數字,-1)：小數第一位四舍五入

round(數字,-2)：小數第二位位四舍五入

round(數字,-3)：小數第三位位四舍五入

trunc(數字,3)：個位、十位、百位為0，其他整數位不變

trunc(數字,2)：個位、十位為0，其他整數位不變

trunc(數字,1)：個位為0，其他整數位不變

trunc(數字,0)：保留到整數

trunc(數字,-1)：保留到小數點後一位

trunc(數字,-2)：保留到小數點後兩位

trunc(數字,-3)：保留到小數點後三位

SELECT ADD_MONTHS(MAX(hire_Date), 6)
FROM EMP111;--2022/2/16 19:29:23

SELECT ROUND(hire_date)
FROM EMP111;--2021/8/16

SELECT sysdate-hire_date
FROM EMP111;--1.00097222222222

SELECT TO_NUMBER(hire_date +7)
FROM EMP111;--報錯 無效數字
           

add_months 有兩個參數，第一個參數是日期，第二個參數是對日期進行加減的數字(以月為機關的)

A選項是将最大的日期，加了6個月

round()日期函數，如果不指定，預設為四舍五入到天。可以指定第二個參數為’year’,‘month’,‘day’

日期相減，傳回小數

to_number()函數不能操作日期

oracle沒有create or replace文法，建立表如果已經存在隻能先drop 後 create

drop user 使用者名; drop user 使用者名 cascade;

sysdate函數傳回系統日期+時間

D選項，substr也有兩種格式，可以指定長度或者不指定

floor()向負數方向取整，-98

month_between()表示兩個日期之間相差的月份，前-後，結果可能為小數

round()和trunc()函數并不會占位，是以無法保留沒有的小數位

oracle字元串：char（定長），varchar2（變長）

CHAR的效率比VARCHAR2的效率稍高，varchar2更節省空間

VARCHAR2字段類型，最大值為4000位元組。varchar是varchar2的同義詞

char最大長度為2000位元組

LOB對象中，BLOB是位元組流對象，CLOB是字元流對象。是以大文本是CLOB。

number()預設精度為38，預設scale為0

由于truncate不能復原，不使用UNDO表空間，是以在大表删除的時候truncate效率會更高

建立臨時表格式：

create global temporary table xx () on commit preserve(delete) rows;

臨時表會在會話結束時消失，不同session之間看不到。delete會在復原時删除行。

根據已有表建立表：

create table tab_new as select col1,col2… from tab_old

to_char()和extract()都可以

extract(month|day|year|hour from date);

日期預設格式是數字，存儲的是毫秒整數。

16.索引index

索引占用存儲空間。

普通索引:沒有任何限制

唯一索引:不允許建立索引的列有重複的值，但可以有空值

主索引:特殊的唯一索引，不允許有空值

候選索引:也要求唯一性，一個表中可以有多個候選索引

B樹索引：不能有null；模糊查詢詞首不能是通配符；不适合鍵值較少（重複值較多）的列

Hash索引：不适合模糊查詢、不适合排序、不适合複合查詢、也不适合鍵值較少（重複值較多）的列 優點是精确查找（in <> = ）非常快，比b樹還快

位圖索引：不适合鍵值很多（重複值很少）的列、不适合增删改頻繁的列，代價很高 優點：适合決策支援系統、select count(val1)可以直接根據一個位圖得出結果、and\or時可以使用位圖運算

表達式索引：對字段進行某種運算之後的結果建立索引，可以使用一些單行函數

唯一限制和主鍵限制都會自動建立索引。

外模式：視圖

模式：基本表

内模式：存儲檔案

資料并發會丢失更新

優化SQL查詢速度

1.避免使用*

2.減少表之間的關聯

3.表的關聯使用别名

4.連接配接查詢關聯條件寫在where左邊

5.建立索引

6.避免使用模糊查詢，詞首為通配符

7.優化SQL

8.使用exists代替in

表連接配接有三種算法，有hash，nested loop，merge。

對于nested loop，使用索引可以在第二個表中進行索引查詢，加速表與表之間的連接配接。

利用索引的唯一性來控制記錄的唯一性

rowid不需要查詢，最快，主鍵和唯一索引都有索引，比全表掃描快

索引可以建在表、視圖、聚簇上。

索引對使用者是透明的

BCD

主鍵和外鍵建立索引是因為相對的這兩個值比較能确定一些資料，是以比較适合建立索引；

where條件中的字段适合建立索引是因為要在查詢過程中減少資料檢索，需要使用索引；

而select中的字段是否建立索引本質是沒有意義的，沒什麼必要；

建立索引的文法 create index index_name on table_name(colname1,…)

索引占有實體空間，提高查詢速度，降低DML速度

索引不是盡量少建立

17.視圖view

ACD都是視圖的優點

視圖和基本表都可以建構視圖

視圖不允許更新的限制：

1.視圖是由兩個及以上的表導出的

2.若視圖的字段來自字段表達式或常數，則不允許對此視圖執行INSERT和UPDATE操作，但允許執行DELETE操作

3.若視圖的字段來自聚集函數，則此視圖不允許更新。

4. 若視圖定義中含有GROUP BY子句/distinct關鍵字，則此視圖不允許更新。

5. 嵌套查詢，且子表也是導出視圖的基本表

6. 不允許更新的視圖導出的視圖

視圖不支援order by

是以可以建立但功能不符合

視圖是虛表，不占有實體空間，可以隐藏原始表的一些列

違反了視圖的檢查限制，提示違反檢查錯誤。

18.序列sequence

建立序列

create sequence seq_name increment by n

start with n

maxvalue/ minvalue n | nomaxvalue

cycle/nocycle

cache/nocache

修改序列：

1.必須是序列的擁有者或對序列有 ALTER any sequence權限

2.隻有将來的序列值會被改變

3.改變序列的初始值隻能通過删除序列之後重建序列的方法實作

alter sequence seq_name 值名

序列是屬于特定模式，為該模式下的所有使用者共享

序列并不能自動連結到哪個表，需要手動連結

序列不能提供給所有使用者，隻能提供給有權限的使用者

dba可以查詢所有序列，但是有權限的使用者就可以使用序列

insert和update需要使用序列.nextval才能使用序列

19.限制 完整性限制

會自動删除子表中所有相關記錄

在父表連接配接字段上建立主索引，在子表連接配接字段上建立普通索引

外鍵是參照完整性

語句執行，限制生效。

資料字典視圖有三種：

user_:有關使用者所擁有的對象的資訊，即使用者自己建立的對象的資訊

all_:有關使用者可以通路的對象的資訊，即使用者自己建立的對象資訊加上使用者可以有權限通路的對象的資訊

dba_*:有關整個資料庫對象的資訊

*可以是tables,index,sequence等等

20.存儲過程 pl/sql塊

注意rollback;所有操作都復原了

varhchar2在pl/sql中是32767位元組 在sql中是4000位元組

oracle中沒有boolean類型，pl/sql中有

%type取得是某一字段類型

遊标有%found，%notfound，%rowcount，%isopen四個屬性。

更新和删除操作需要在遊标定義後面加上for update子句

執行部分

stats_exist_exception是未定義的變量，需要定義為exception類型

21.觸發器

觸發器可以用于表和視圖

觸發器分為4種：DML、DDL、替代觸發器（instance of）、DB觸發器

觸發器禁用/可用：

alter trigger trigger_name disable;

alter trigger trigger_name enable;

觸發器是一種特殊的存儲過程

DML觸發器

語句級觸發器，會生效1次

？

全部正确