java Web知識點--資料庫（3）

介紹一下SQL Server的全文索引？

全文索引可以對儲存在SQL Server資料庫中的文字資料執行快速檢索功能。同LIKE謂詞不同，全文索引只對字元模式進行操作，對字和語句執行搜尋功能。全文索引對於查詢非結構化資料非常有效。一般情況下，可以對char、varchar和nvarchar資料型別的列建立全文索引，同時，還可以對二進位制格式的列建立索引，如image和varbinary資料型別列。對於這些二進位制資料，無法使用LIKE謂詞。

為了對錶建立全文索引，表必須包含單個、唯一、非空列。當執行全文檢索的時候，SQL Server搜尋引擎返回匹配搜尋條件的行的鍵值。一般情況，使用sql server中的全文索引，經過大體4個步驟：

l         安裝全文索引服務；

l         為資料表建立全文索引目錄；

l         使全文索引與資料表內容同步；

l         使用全文索引進行查詢。

SQL Server裡面什麼樣的檢視才能建立索引？

在為檢視建立索引前，檢視本身必須滿足以下條件：

l         檢視以及檢視中引用的所有表都必須在同一資料庫中，並具有同一個所有者。

l         索引檢視無需包含要供優化器使用的查詢中引用的所有表。

l         必須先為檢視建立唯一群集索引，然後才可以建立其它索引。

l         建立基表、檢視和索引以及修改基表和檢視中的資料時，必須正確設定某些 SET 選項（在本文件的後文中討論）。另外，如果這些 SET 選項正確，查詢優化器將不考慮索引檢視。

l         檢視必須使用架構繫結建立，檢視中引用的任何使用者定義的函式必須使用 SCHEMABINDING 選項建立。

l         另外，還要求有一定的磁碟空間來存放由索引檢視定義的資料。

介紹一下SQL Server裡面的索引檢視？

複雜報表的場景經常會在資料倉儲應用程式中遇到，它在查詢過程中會對資料庫伺服器產生大量請求。當這些查詢訪問檢視時，因為資料庫將建立檢視結果集所需的邏輯合併到從基本表資料建立完整查詢結果集所需的邏輯中，所以效能將會下降。這一操作的開銷可能會比較大，尤其當檢視涉及到複雜的大量行處理–如大量資料聚合或多表聯結時。因為結果集並不永久存放在資料庫（標準檢視）中，以後對該檢視的訪問可能導致在每次執行查詢時建立結果集的代價。

SQL Server允許為檢視建立獨特的聚集索引，從而讓訪問此類檢視的查詢的效能得到極大地改善。在建立了這樣一個索引後，檢視將被執行，結果集將被存放在資料庫中，存放的方式與帶有聚集索引的表的存放方式相同。這就在資料庫中有效地實現了查詢結果。對於那些在FROM子句中不直接指定檢視名的查詢，SQL Server查詢優化器將使用檢視索引。現有查詢將受益於從索引檢視檢索資料而無需重新編寫程式原碼的高效率。對於某些特定型別的檢視，甚至可以獲得指數級的效能改善。

如果在檢視上建立索引，那麼檢視中的資料會被立即儲存在資料庫中，對索引檢視進行修改，那麼這些修改會立即反映到基礎表中。同理，對基礎表所進行的資料修改也會反映到索引檢視那裡。索引的惟一性大大提高了SQL Server 查詢那些被修改的資料行。

維護索引檢視比維護基礎表的索引更為複雜。所以，如果認為值得以因資料修改而增加系統負擔為代價來提高資料檢索的速度，那麼應該在檢視上建立索引。

什麼是唯一索引？

唯一索引可以確保索引列不包含重複的值。在多列唯一索引的情況下，該索引可以確保索引列中每個值組合都是唯一的。例如，如果在 last_name、first_name 和 middle_initial 列的組合上建立了唯一索引 full_name，則該表中任何兩個人都不可以具有相同的全名。

聚集索引和非聚集索引都可以是唯一的。因此，只要列中的資料是唯一的，就可以在同一個表上建立一個唯一的聚集索引和多個唯一的非聚集索引。

只有當唯一性是資料本身的特徵時，指定唯一索引才有意義。如果必須實施唯一性以確保資料的完整性，則應在列上建立 UNIQUE 或 PRIMARY KEY 約束，而不要建立唯一索引。例如，如果打算經常查詢僱員表（主鍵為 emp_id）中的社會安全號碼 (ssn) 列，並希望確保社會安全號碼的唯一性，則在 ssn 列上建立 UNIQUE 約束。如果使用者為一個以上的僱員輸入了同一個社會安全號碼，則會顯示錯誤。

什麼是聚集索引和非聚集索引？分佈介紹一下

（1）非聚集索引

非聚集索引與課本中的索引類似。資料儲存在一個地方，索引儲存在另一個地方，索引帶有指標指向資料的儲存位置。索引中的專案按索引鍵值的順序儲存，而表中的資訊按另一種順序儲存（這可以由聚集索引規定）。如果在表中未建立聚集索引，則無法保證這些行具有任何特定的順序。

典型的桌面資料庫使用的是非聚集索引。在這類索引中，索引鍵值是有序的，而每個索引節點所指向的資料行是無序的。一個SQL Server表最多可以擁有255個非聚集索引。

非聚集索引與聚集索引一樣有 B-樹結構，但是有兩個重大差別：

l         資料行不按非聚集索引鍵的順序排序和儲存。

l         非聚集索引的葉層不包含資料頁。

相反，葉節點包含索引行。每個索引行包含非聚集鍵值以及一個或多個行定位器，這些行定位器指向有該鍵值的資料行（如果索引不唯一，則可能是多行）。非聚集索引可以在有聚集索引的表、堆集或索引檢視上定義。在 SQL Server中，非聚集索引中的行定位器有兩種形式：

l         如果表是堆集（沒有聚集索引），行定位器就是指向行的指標。該指標用檔案識別符號 (ID)、頁碼和頁上的行數生成。整個指標稱為行 ID。

l         如果表是堆集（沒有聚集索引），行定位器就是指向行的指標。該指標用檔案識別符號 (ID)、頁碼和頁上的行數生成。整個指標稱為行 ID。

由於非聚集索引將聚集索引鍵作為其行指標儲存，因此使聚集索引鍵儘可能小很重要。如果表還有非聚集索引，請不要選擇大的列作為聚集索引的鍵。

在建立非聚集索引之前，應先了解您的資料是如何被訪問的。可考慮將非聚集索引用於：

l         包含大量非重複值的列，如姓氏和名字的組合（如果聚集索引用於其它列）。如果只有很少的非重複值，如只有 1 和 0，則大多數查詢將不使用索引，因為此時表掃描通常更有效。

l         不返回大型結果集的查詢。

l         返回精確匹配的查詢的搜尋條件（WHERE 子句）中經常使用的列。

l         經常需要聯接和分組的決策支援系統應用程式。應在聯接和分組操作中使用的列上建立多個非聚集索引，在任何外來鍵列上建立一個聚集索引。

l         在特定的查詢中覆蓋一個表中的所有列。這將完全消除對錶或聚集索引的訪問。

（2）聚集索引

聚集索引確定表中資料的物理順序。聚集索引類似於電話簿，後者按姓氏排列資料。由於聚集索引規定資料在表中的物理儲存順序，因此一個表只能包含一個聚集索引。但該索引可以包含多個列（組合索引），就像電話簿按姓氏和名字進行組織一樣。

聚集索引在系統資料庫表sysindexes 內有一行，其 indid = 1。資料鏈內的頁和其內的行按聚集索引鍵值排序。所有插入都在所插入行中的鍵值與排序順序相匹配時執行。

SQL Server將索引組織為B-樹。索引內的每一頁包含一個頁首，頁首後面跟著索引行。每個索引行都包含一個鍵值以及一個指向較低階頁或資料行的指標。索引的每個頁稱為索引節點。B-樹的頂端節點稱為根節點。索引的底層節點稱為葉節點。每級索引中的頁連結在雙向連結列表中。在聚集索引內資料頁組成葉節點。根和葉之間的任何索引級統稱為中間級。

對於聚集索引，sysindexes.root 指向它的頂端。SQL Server 沿著聚集索引瀏覽以找到聚集索引鍵對應的行。為找到鍵的範圍，SQL Server 瀏覽索引以找到這個範圍的起始鍵值，然後用向前或向後指標掃描資料頁。為找到資料頁鏈的首頁，SQL Server 從索引的根節點開始沿最左邊的指標進行掃描.

聚集索引對於那些經常要搜尋範圍值的列特別有效。使用聚集索引找到包含第一個值的行後，便可以確保包含後續索引值的行在物理相鄰。例如，如果應用程式執行的一個查詢經常檢索某一日期範圍內的記錄，則使用聚集索引可以迅速找到包含開始日期的行，然後檢索表中所有相鄰的行，直到到達結束日期。這樣有助於提高此類查詢的效能。同樣，如果對從表中檢索的資料進行排序時經常要用到某一列，則可以將該表在該列上聚集（物理排序），避免每次查詢該列時都進行排序，從而節省成本。

對於聚集索引，人們往往有一些錯誤的認識。其中，最常見的錯誤有：

l         聚集索引會降低insert操作的速度，因為必須要向後移動一半的資料來為新插入的行騰出空間。這種認識是錯誤的，因為可以利用填充因子控制填充的百分比，從而在索引頁上為新插入的資料保留空間。如果索引頁填滿了，SQL Server將會進行頁拆分，在這種情況下只有第一個頁才會受到影響。

l         在使用標識列的主鍵上建立聚集索引是一種好的設計方法，它可以使對錶的操作達到最快速度。這種認識是錯誤的，它浪費了建立其它更有效的聚集索引的機會。並且，使用這種方法會把每個新插入的記錄行都儲存到表尾部的同一個的資料頁中，這將導致資料庫的熱點和鎖爭用。筆者曾經見過採用這種方法設計的資料庫，對於每一個新訂單，客戶服務人員都不得不等待數分鐘來加以確認。

l         聚集索引是具有魔力的。如果哪個查詢的速度不夠快，那麼就在該列上建立聚集索引，對於表的操作速度一定會得到提高。這種認識也是錯誤的，聚集索引只是比非聚集索引稍稍快了那麼一點點。因為在每個表上只能建立一個聚集索引，所以它也是一種寶貴的效能資源，只有在那些經常作為條件查詢一組記錄行的列上才應該建立聚集索引。

在建立聚集索引之前，應先了解資料是如何被訪問的。可考慮將聚集索引用於：

l         包含大量非重複值的列。

l         使用下列運算子返回一個範圍值的查詢：BETWEEN、>、>=、< 和 <=。

l         被連續訪問的列。

l         返回大型結果集的查詢。

l         經常被使用聯接或 GROUP BY 子句的查詢訪問的列；一般來說，這些是外來鍵列。對 ORDER BY 或 GROUP BY 子句中指定的列進行索引，可以使 SQL Server 不必對資料進行排序，因為這些行已經排序。這樣可以提高查詢效能。

l         OLTP 型別的應用程式，這些程式要求進行非常快速的單行查詢（一般通過主鍵）。應在主鍵上建立聚集索引。

注意，聚集索引不適用於：

l         頻繁更改的列，這將導致整行移動（因為 SQL Server 必須按物理順序保留行中的資料值）。這一點要特別注意，因為在大資料量事務處理系統中資料是易失的。

l         寬鍵，來自聚集索引的鍵值由所有非聚集索引作為查詢鍵使用，因此儲存在每個非聚集索引的葉條目內。

SQL Server提供的3種恢復模型都是什麼？ 有什麼區別？

SQL Server提供了3種恢復模型，分別是：

l         簡單恢復 ，允許將資料庫恢復到最新的備份。

l         完全恢復，允許將資料庫恢復到故障點狀態。

l         大容量日誌記錄恢復，允許大容量日誌記錄操作。

這些模型中的每個都是針對不同的效能、磁碟和磁帶空間以及保護資料丟失的需要。例如，當選擇恢復模型時，必須考慮下列業務要求之間的權衡：

l         大規模操作的效能（如建立索引或大容量裝載）。

l         資料丟失表現（如已提交的事務丟失）。

l         事務日誌空間損耗

l         備份和恢復過程的簡化。

根據正在執行的操作，可以有多個適合的模型。選擇了恢復模型後，設計所需的備份和恢復過程。表6提供了三種恢復模型的優點和含義的概述。

表 6備份模型之間的比較

恢復模型	優點	工作損失表現	能否恢復到即時點？
簡單	允許高效能大容量複製操作。 收回日誌空間以使空間要求最小。	必須重做自最新的資料庫或差異備份後所發生的更改。	可以恢復到任何備份的結尾處。隨後必須重做更改。
完全	資料檔案丟失或損壞不會導致工作損失。 可以恢復到任意即時點（例如，應用程式或使用者錯誤之前）。	正常情況下沒有。 如果日誌損壞，則必須重做自最新的日誌備份後所發生的更改。	可以恢復到任何即時點。
大容量日誌記錄的	允許高效能大容量複製操作。大容量操作使用最少的日誌空間。	如果日誌損壞，或者自最新的日誌備份後發生了大容量操作，則必須重做自上次備份後所做的更改。否則不丟失任何工作。	可以恢復到任何備份的結尾處。隨後必須重做更改。

簡單恢復所需的管理最少。在簡單恢復模型中，資料只能恢復到最新的完整資料庫備份或差異備份的狀態。不使用事務日誌備份，而使用最小事務日誌空間。一旦不再需要日誌空間從伺服器故障中恢復，日誌空間便可重新使用。與完整模型或大容量日誌記錄模型相比，簡單恢復模型更容易管理，但如果資料檔案損壞，則資料損失表現會更高。

完全恢復和大容量日誌記錄恢復模型為資料提供了最大的保護性。這些模型依靠事務日誌提供完全的可恢復性，並防止最大範圍的故障情形所造成的工作損失。完全恢復模型提供最大的靈活性，可將資料庫恢復到更早的即時點。

大容量日誌記錄模型為某些大規模操作（如建立索引或大容量複製）提供了更高的效能和更低的日誌空間損耗。不過這將犧牲時點恢復的某些靈活性。很多資料庫都要經歷大容量裝載或索引建立的階段，因此可能希望在大容量日誌記錄模型和完全恢復模型之間進行切換。

SQL Server的固定資料庫角色都有哪些？對應的伺服器許可權有哪些？

固定伺服器角色	對應的伺服器級許可權
bulkadmin	授予的許可權： ADMINISTER BULK OPERATIONS
dbcreator	授予的許可權： CREATE DATABASE
diskadmin	授予的許可權： ALTER RESOURCES
processadmin	授予的許可權： ALTER SERVER STATE
processadmin	授予的許可權： ALTER ANY CONNECTION
securityadmin	授予的許可權： ALTER ANY LOGIN
serveradmin	授予的許可權： ALTER SETTINGS
serveradmin	授予的許可權： SHUTDOWN
serveradmin	授予的許可權： CREATE ENDPOINT
serveradmin	授予的許可權： ALTER SERVER STATE
serveradmin	授予的許可權： ALTER ANY ENDPOINT
serveradmin	授予的許可權： ALTER RESOURCES
setupadmin	授予的許可權： ALTER ANY LINKED SERVER
sysadmin	帶GRANT 選項授予的許可權：CONTROL SERVER

在SQL Server中建立資料庫主要有那種方式？

在SQL Server中建立資料庫主要有兩種方法，第一種是在SQL Server Management Studio中使用嚮導建立資料庫；第二種是執行Transact-SQL語句建立資料庫。下面我們分別進行介紹。

（1）使用SQL Server Management Studio建立資料庫

在SQL Server Management Studio中，可以使用嚮導建立資料庫，步驟如下：

1．  開啟SQL Server Management Studio。

2.     右擊“資料庫”物件，在彈出式選單中選擇“新建資料庫”選單項，開啟“新建資料庫”對話方塊

3．  在“常規”頁，可以定義資料庫的名稱為EAMS、資料庫檔案、資料庫的所有者、排序規則、恢復模型，以及是否使用全文索引。

4.    在“選項”頁，可以定義資料庫的一些選項，包括自動選項、遊標選項、混雜選項、恢復選項、行級版本選項和狀態選項

5.    在“檔案組”頁，顯示檔案組和檔案的統計資訊，同時還可以設定是否採用預設值

6．  在“擴充套件屬性”頁，可以定義資料庫的一些擴充套件屬性，如圖5所示。

7．  當完成各個選項的定義之後，單擊【OK】按鈕，SQL Server資料庫引擎會建立所定義的資料庫。

（2）使用Transact-SQL建立資料庫

Transact-SQL提供了建立資料庫的語句：CREATE DATABASE，其語法格式如下：

CREATE DATABASE database_name

[ ON

[ <filespec> [ ,...n ] ]

[ , <filegroup> [ ,...n ] ]

]

[

[ LOG ON { <filespec> [ ,...n ] } ]

[ COLLATE collation_name ]

[ FOR { ATTACH [ WITH <service_broker_option> ]

| ATTACH_REBUILD_LOG } ]

[ WITH <external_access_option> ]

]

[;]

<filespec> ::=

[ PRIMARY ]

(

[ NAME = logical_file_name , ]

FILENAME = ‘os_file_name’

[ , SIZE = size [ KB | MB | GB | TB ] ]

[ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]

[ , FILEGROWTH = growth_increment [ KB | MB | % ] ]

) [ ,...n ]

<filegroup> ::=

FILEGROUP filegroup_name

<filespec> [ ,...n ]

<external_access_option> ::=

DB_CHAINING { ON | OFF }

| TRUSTWORTHY { ON | OFF }

<service_broker_option> ::=

ENABLE_BROKER

| NEW_BROKER

| ERROR_BROKER_CONVERSATIONS

Create a Database Snapshot

CREATE DATABASE database_snapshot_name

ON

(

NAME = logical_file_name,

FILENAME = ‘os_file_name’

) [ ,...n ]

AS SNAPSHOT OF source_database_name

[;]

引數說明如下：

l         database_name，新資料庫的名稱。資料庫名稱在伺服器中必須唯一，並且符合識別符號的規則。database_name 最多可以包含 128 個字元，除非沒有為日誌指定邏輯名。如果沒有指定日誌檔案的邏輯名，則SQL Server會通過向 database_name 追加字尾來生成邏輯名。該操作要求 database_name 在 123 個字元之內，以便生成的日誌檔案邏輯名少於 128 個字元。

l         ON，指定顯式定義用來儲存資料庫資料部分的磁碟檔案（資料檔案）。該關鍵字後跟以逗號分隔的 <filespec> 項列表，<filespec> 項用以定義主檔案組的資料檔案。主檔案組的檔案列表後可跟以逗號分隔的 <filegroup> 項列表（可選），<filegroup> 項用以定義使用者檔案組及其檔案。

l         N，佔位符，表示可以為新資料庫指定多個檔案。

l         LOG ON，指定顯式定義用來儲存資料庫日誌的磁碟檔案（日誌檔案）。該關鍵字後跟以逗號分隔的 <filespec> 項列表，<filespec> 項用以定義日誌檔案。如果沒有指定 LOG ON，將自動建立一個日誌檔案，該檔案使用系統生成的名稱，大小為資料庫中所有資料檔案總大小的 25%。

l         FOR LOAD，支援該子句是為了與早期版本的 SQL Server 相容。資料庫在開啟 dbo use only 資料庫選項的情況下建立，並且將其狀態設定為正在裝載。

l         FOR ATTACH，指定從現有的一組作業系統檔案中附加資料庫。必須有指定第一個主檔案的 <filespec> 條目。至於其它 <filespec> 條目，只需要與第一次建立資料庫或上一次附加資料庫時路徑不同的檔案的那些條目。必須為這些檔案指定 <filespec> 條目。附加的資料庫必須使用與 SQL Server 相同的內碼表和排序次序建立。應使用 sp_attach_db 系統儲存過程，而不要直接使用 CREATE DATABASE FOR ATTACH。只有必須指定 16 個以上的 <filespec> 專案時，才需要使用 CREATE DATABASE FOR ATTACH。 如果將資料庫附加到的伺服器不是該資料庫從中分離的伺服器，並且啟用了分離的資料庫以進行復制，則應該執行 sp_removedbreplication 從資料庫刪除複製。

l         collation_name，指定資料庫的預設排序規則。排序規則名稱既可以是 Windows 排序規則名稱，也可以是 SQL 排序規則名稱。如果沒有指定排序規則，則將 SQL Server 例項的預設排序規則指派為資料庫的排序規則。

l         PRIMARY，指定關聯的 <filespec> 列表定義主檔案。主檔案組包含所有資料庫系統表。還包含所有未指派給使用者檔案組的物件。主檔案組的第一個 <filespec> 條目成為主檔案，該檔案包含資料庫的邏輯起點及其系統表。一個資料庫只能有一個主檔案。如果沒有指定 PRIMARY，那麼 CREATE DATABASE 語句中列出的第一個檔案將成為主檔案。

l         NAME，為由 <filespec> 定義的檔案指定邏輯名稱。如果指定了 FOR ATTACH，則不需要指定 NAME 引數。

l         logical_file_name，用來在建立資料庫後執行的 Transact-SQL 語句中引用檔案的名稱。logical_file_name 在資料庫中必須唯一，並且符合識別符號的規則。該名稱可以是字元或 Unicode 常量，也可以是常規識別符號或定界識別符號。

l         FILENAME，為 <filespec> 定義的檔案指定作業系統檔名。

l         os_file_name，作業系統建立 <filespec> 定義的物理檔案時使用的路徑名和檔名。os_file_name 中的路徑必須指定 SQL Server 例項上的目錄。os_file_name 不能指定壓縮檔案系統中的目錄。如果檔案在原始分割槽上建立，則 os_file_name 必須只指定現有原始分割槽的驅動器字母。每個原始分割槽上只能建立一個檔案。原始分割槽上的檔案不會自動增長；因此，os_file_name 指定原始分割槽時，不需要指定 MAXSIZE 和 FILEGROWTH 引數。

l         SIZE，指定 <filespec> 中定義的檔案的大小。如果主檔案的 <filespec> 中沒有提供 SIZE 引數，那麼 SQL Server 將使用 model 資料庫中的主檔案大小。如果次要檔案或日誌檔案的 <filespec> 中沒有指定 SIZE 引數，則 SQL Server 將使檔案大小為 1 MB。

l         Size，<filespec> 中定義的檔案的初始大小。可以使用千位元組 (KB)、兆位元組 (MB)、千兆位元組 (GB) 或兆兆位元組 (TB) 字尾。預設值為 MB。指定一個整數，不要包含小數位。size 的最小值為 512 KB。如果沒有指定 size，則預設值為 1 MB。為主檔案指定的大小至少應與 model 資料庫的主檔案大小相同。

l         MAXSIZE，指定 <filespec> 中定義的檔案可以增長到的最大大小。

l         max_size，<filespec> 中定義的檔案可以增長到的最大大小。可以使用千位元組 (KB)、兆位元組 (MB)、千兆位元組 (GB) 或兆兆位元組 (TB) 字尾。預設值為 MB。指定一個整數，不要包含小數位。如果沒有指定 max_size，那麼檔案將增長到磁碟變滿為止。

l         UNLIMITED，指定 <filespec> 中定義的檔案將增長到磁碟變滿為止。

l         FILEGROWTH，指定 <filespec> 中定義的檔案的增長增量。檔案的 FILEGROWTH 設定不能超過 MAXSIZE 設定。

l         growth_increment，每次需要新的空間時為檔案新增的空間大小。指定一個整數，不要包含小數位。0 值表示不增長。該值可以 MB、KB、GB、TB 或百分比 (%) 為單位指定。如果未在數量後面指定 MB、KB 或 %，則預設值為 MB。如果指定 %，則增量大小為發生增長時檔案大小的指定百分比。如果沒有指定 FILEGROWTH，則預設值為 10%，最小值為 64 KB。指定的大小舍入為最接近的 64 KB 的倍數。

l         <filegroup>，控制檔案組的屬性。檔案組不能在資料庫快照上定義。

l         FILEGROUP，定義檔案組的邏輯名。

l         filegroup_name，表示在建立資料庫之後，在Transact-SQL語句中引用檔案組的名稱。filegroup_name在資料庫中必須唯一，不能是系統提供的名稱，如PRIMARY和PRIMARY_LOG。名稱必須同識別符號的規則保持一致。

l         default，定義檔案組為特定檔案組型別的預設資料庫檔案組。

l         DB_CHAINING { ON | OFF }，當設定為ON的時候，資料庫可以為交叉資料庫所有者關係鏈中的源或者目標。當設定為OFF的時候，資料庫不能參與交叉資料庫所有者關係鏈，對於使用者資料庫，可以修改這個選項，但是不能修改系統資料庫的該選項。預設值為OFF。

l         TRUSTWORTHY { ON | OFF }，當設定為ON的時候，資料庫模組（如檢視、使用者自定義函式或者儲存過程）允許訪問資料庫外的資源。當設定為OFF的時候，資料庫模組不能訪問資料庫之外的資源。預設值為OFF。

l         <service_broker_options> ，當授予FOR ATTACH子句的時候，才能設定Service Broker選項。

l         ENABLE_BROKER，定義資料庫是否啟用Service Broker。

l         NEW_BROKER，在sys資料庫中和恢復資料庫中建立新的service_broker_guid。

l         ERROR_BROKER_CONVERSATIONS，終止所有發生錯誤的會話。

l         database_snapshot_name，定義新資料庫的快照名。

l         ON (NAME =logical_file_name, FILENAME =’os_file_name’) [ ,... n ] ，對於建立一個資料庫快照，在源資料庫中定義檔案列表。

l         AS SNAPSHOT OF source_database_name，定義建立的資料庫為一個源資料庫的資料庫快照。

幾道資料庫的面試題或筆試題難度適中

1.張表,學生表S,課程C,學生課程表SC,學生可以選修多門課程,一門課程可以被多個學生選修,通過SC表關聯；(SQL)
1)寫出建表語句；
答：建表語句如下（mysql資料庫）：
create table s(id integer primary key, name varchar(20));
create table c(id integer primary key, name varchar(20));
create table sc(
sid integer references s(id),
cid integer references c(id),
primary key(sid,cid)
);
2)寫出SQL語句,查詢選修了所有選修課程的學生；
答：SQL語句如下：
select stu.id, stu.name from s stu
where (select count(*) from sc where sid=stu.id) =
(select count(*) from c);
3)寫出SQL語句,查詢選修了至少5門以上的課程的學生。
答：SQL語句如下：
select stu.id, stu.name from s stu
where (select count(*) from sc where sid=stu.id)>=5;

2.資料庫表(Test)結構如下：(SQL)
IDNAMEAGEMANAGER(所屬主管人ID)
106A30104
109B19104
104C20111
107D35109
112E25120
119F45NULL
要求:列出所有年齡比所屬主管年齡大的人的ID和名字?
答：SQL語句如下：
select employee.name from test employee where employee.age>
(select manager.age from test manager where manager.id=employee.manager);

3.有3個表（15分鐘）：(SQL)
Student 學生表 (學號，姓名，性別，年齡，組織部門)
Course 課程表 (編號，課程名稱)
Sc 選課表 (學號，課程編號，成績)
表結構如下：

1)寫一個SQL語句，查詢選修了’計算機原理’的學生學號和姓名（3分鐘）
答：SQL語句如下：
select stu.sno, stu.sname from Student stu
where (select count(*) from sc where sno=stu.sno and cno =
(select cno from Course where cname=’計算機原理’)) != 0;
2)寫一個SQL語句，查詢’周星馳’同學選修了的課程名字（3分鐘）
答：SQL語句如下：
select cname from Course where cno in (select cno from sc where sno=(select sno from Student where sname=’周星馳’));
3)寫一個SQL語句，查詢選修了5門課程的學生學號和姓名（9分鐘）
答：SQL語句如下：
select stu.sno, stu.sname from student stu
where (select count(*) from sc where sno=stu.sno) = 5;

ORACLE面試測試題目

1．    解釋FUNCTION，PROCEDURE和PACKAGE區別
答：function 和procedure是PL/SQL程式碼的集合，通常為了完成一個任務。procedure 不需要返回任何值而function將返回一個值在另一方面，Package是為了完成一個商業功能的一組function和procedure的集合。

2．    取某個序列的當前值的PL/SQL語句怎麼寫？
答：SELECT 序列名.CURRVAL  FROM  DUAL；

3．    說明ORACLE資料庫例項與ORACLE使用者的關係？
答：例項可以包含多個使用者，一個使用者只能在一個例項下

4．    建立資料庫時，自動建立的tablespace名稱？
答：SYSTEM tablespace

5．    建立使用者時，需要賦予新使用者什麼許可權才能使它連上資料庫？
答：CONNECT

6．    IMPORT和SQL*LOADER這2個工具的不同點？
答：這兩個ORACLE工具都是用來將資料匯入資料庫的。
區別是：IMPORT工具只能處理由另一個ORACLE工具EXPORT生成的資料。而SQL*LOADER可以匯入不同的ASCII格式的資料來源。

7．解釋冷備份和熱備份的不同點以及各自的優點？
答：熱備份針對歸檔模式的資料庫，在資料庫仍舊處於工作狀態時進行備份。而冷備份指在資料庫關閉後，進行備份，適用於所有模式的資料庫。熱備份的優點在於當備份時，資料庫仍舊可以被使用並且可以將資料庫恢復到任意一個時間點。冷備份的優點在於它的備份和恢復操作相當簡單，並且由於冷備份的資料庫可以工作在非歸檔模式下,資料庫效能會比歸檔模式稍好。（因為不必將archive log寫入硬碟）

8．比較truncate和delete命令？
答：兩者都可以用來刪除表中所有的記錄。區別在於：truncate是DDL（data defining language資料定義語言），它移動HWK，不需要rollback segment（處理事務回滾操作）而Delete是DML（data manufacturing language資料操作語言）操作，需要rollback segment（處理事務回滾操作）且花費較長時間。

9．給出資料的相關約束型別？
答：主鍵約束，外來鍵約束，非空約束，唯一約束，檢查約束。

10．說明索引的型別與作用？
答：索引型別上分為聚集索引，非聚集索引其作用是加快查詢速度。

11．解釋歸檔和非歸檔模式之間的不同和它們各自的優缺點
答：歸檔模式是指你可以備份所有的資料庫 transactions並恢復到任意一個時間點。非歸檔模式則相反，不能恢復到任意一個時間點。但是非歸檔模式可以帶來資料庫效能上的少許提高。

12．解釋$ORACLE_HOME和$ORACLE_BASE的區別？
答：ORACLE_BASE是oracle的根目錄，ORACLE_HOME是oracle產品的目錄。

13．獲取某個字元欄位的最後3個字元的函式是什麼？
答：select substr (欄位,(length(欄位)-3)) from 表

14．取當前系統時間點日期（不包括年月）的SQL寫法是怎樣的？
答：Select substr (to_char(sysdate,’YYYYMMDDh24hh:MM:SS’),5) from dual;

15．返回大於等於N的最小整數值？
答：select ceil(N) from dual;

16．將根據字元查詢轉換後結果，規則為：’A’轉換為’男’，’B’轉換為’女’，其他字元轉換為’未知’，請用一個SQL語句寫出。
答：select decode(字元,’A’,’男’,’B’,’女’,’未知’) from dual;

17．如何搜尋出前N條記錄？
答：select * from 表 where  Rownum <= N;

18．如何搜尋出第N～M條記錄？
答：select * from 表 where Rownum <= M
Minus
select * from 表 where Rownum <= N;

19．有一個資料表（TEST），欄位如下：
ID      number
PARENT_ID      number
NAME      Varchar(20)
請使用PL/SQL來按父子層次關係查詢出該表的所有資料
答：Select * from test a, test b Where a.parent_id = b.id;

20．怎樣用SQL語句實現查詢一列中的第N大值？
答：select * from (select * from 表 order by 列名 Desc) where Rownum <= N
Minus
select * from (select * from 表 order by 列名 Desc) where Rownum <= N-1;

騰訊公司的一個sql題

小小+霸霸+王王=小霸王
小=?,霸=?,王=?
用sql求證

參考答案:

declare @data int,@i int,@j int,@l int
set @data=100
while (@data<=999)
begin
   set @i=@data/100
   set @j=@data/10 % 10
   set @l=@data % 10
   if((@i+@j+@l)*11=@data)
   begin
      Select @data data,@i i,@j j,@l l
      break
   end
   set @data=@data+1
end;
分析：
II+JJ+LL=IJL
I*10+I +J*10+J+L*10+L   =   I*100+J*10+L
(I+J+L)*11

1、 一套Oracle面試題筆試題

完成下列操作，寫出相應的SQL語句

1. 建立表空間neuspace，資料檔案命名為neudata.dbf，存放在d:\data目錄下，檔案大小為200MB，設為自動增長，增量5MB，檔案最大為500MB。(8分)

答：create tablespace neuspace datafile ‘d:\data\neudata.dbf’ size 200m auto extend on next 5m maxsize 500m;

2. 假設表空間neuspace已用盡500MB空間，現要求增加一個資料檔案，存放在e:\appdata目錄下，檔名為appneudata，大小為500MB，不自動增長。（5分）

答：alter tablespace neuspace add datafile ‘e:\appdata\appneudata.dbf’ size 500m;

3. 以系統管理員身份登入，建立賬號tom，設定tom的預設表空間為neuspace。為tom分配connect和resource系統角色，獲取基本的系統許可權。然後為tom分配對使用者scott的表emp的select許可權和對SALARY, MGR屬性的update許可權。(8分)

答：create user tom identified by jack default tablespace neuspace;

Grant connect, resource to tom;

Grant select, update(salary, mgr) on scott.emp to tom;

4. 按如下要求建立表class和student。（15分）

屬性	型別(長度)	預設值	約束	含義
CLASSNO	數值 (2)	無	主鍵	班級編號
CNAME	變長字元 (10)	無	非空	班級名稱

 

屬性	型別(長度)	預設值	約束	含義
STUNO	數值 (8)	無	主鍵	學號
SNAME	變長字元 (12)	無	非空	姓名
SEX	字元 (2)	男	無	性別
BIRTHDAY	日期	無	無	生日
EMAIL	變長字元 (20)	無	唯一	電子郵件
SCORE	數值 (5, 2)	無	檢查	成績
CLASSNO	數值 (2)	無	外來鍵，關聯到表CLASS的CLASSNO主鍵	班級編號

答：create table class

(classno number(2) constraint class_classno_pk primary key,

cname varchar2(10) not null);

create table student

(stuno number(8) constraint student_stuno_pk primary key,

sname varchar2(12) not null,

sex char(2) default ‘男’,

birthday date,

email varchar2(20) constraint student_email_uk unique,

score number(5,2) constraint student_score_ck check(score>=0 and score<=100),

classno number(2) constraint student_classno_fk references class(classno)

);

5. 在表student的SNAME屬性上建立索引student_sname_idx（5分）

答：create index student_sname_idx on student(sname);

6. 建立序列stuseq，要求初值為20050001，增量為1，最大值為20059999。（6分）

答：create sequence stuseq increment by 1 start with 20050001 maxvalue 20059999 nocache nocycle;

7. 向表student中插入如下2行。（5分）

STUNO	SNAME	SEX	BIRTHDAY	EMAIL	SCORE	CLASSNO
從stuseq取值	tom	男	1979-2-3 14:30:25	tom@163.net	89.50	1
從stuseq取值	jerry	預設值	空	空	空	2

答：insert into student values(stuseq.nextval, ’tom’, ’男’, to_date(‘1979-2-3

14:30:25’, ’yyyy-mm-dd fmhh24:mi:ss’), ’tom@163.net’, 89.50, 1);

insert into student (stuno, sname, classno) values(stuseq.nextval, ’jerry’, 2);

8. 修改表student的資料，將所有一班的學生成績加10分。（4分）

答：update student set score=score+10 where classno=1;

9. 刪除表student的資料，將所有3班出生日期小於1981年5月12日的記錄刪除。（4分）

答：delete from student where classno=3 and birthday > ’12-5月-81’;

10. 完成以下SQL語句。(40分)

(1) 按班級升序排序，成績降序排序，查詢student表的所有記錄。

答：select * from student order by classno, score desc;

(2) 查詢student表中所有二班的成績大於85.50分且出生日期大於1982-10-31日的男生的記錄。

答：select * from student where classno=2 and score>85.50 and birthday < ’31-10月-82’ and sex=’男’;

(3) 查詢student表中所有三班成績為空的學生記錄。

答：select * from student where classno=3 and score is null;

(4) 表student與class聯合查詢，要求查詢所有學生的學號，姓名，成績，班級名稱。（使用oracle與SQL 99兩種格式）

答：select s.stuno, s.sname, s.score, c.cname from student s, class c where s.classno=c.classno;

(5) 按班級編號分組統計每個班的人數，最高分，最低分，平均分，並按平均分降序排序。

答：select classno, count(*), max(score), min(score), avg(score) from student group by classno order by avg(score) desc;

(6) 查詢一班學生記錄中所有成績高於本班學生平均分的記錄。

答：select * from student where classno=1 and score > (select avg(score) from student where classno=1);

(7) 統計二班學生中所有成績大於所有班級平均分的人數。

答：select count(*) from student where classno=2 and score > all (select avg(socre) from student group by classno);

(8) 查詢平均分最高的班級編號與分數。

答：select classno, avg(score) from student group by classno having avg(score) = (select max(avg(score)) from student group by classno);

(9) 查詢所有學生記錄中成績前十名的學生的學號、姓名、成績、班級編號。

答：select stuno, sname, score, classno from (select * from student order by score desc) where rownum<=10;

(10) 建立檢視stuvu，要求檢視中包含student表中所有一班學生的stuno, sname, score, classno四個屬性，並具有with check option限制。

答：create view stuvu

as

select stuno, sname,score,classno from student where classno=1 with check option;

2、 一道Oracle筆試題

表結構說明：
create table employee(
id number(10) not null, — 員工工號
salary number(10,2) default 0 not null, — 薪水
name varchar2(24) not null — 姓名
);
1.建立序列seq_employee，該序列每次取的時候它會自動增加，從1開始計數，不設最大值，並且一直累加，不迴圈。（10分）
2.寫一個PL/SQL塊，插入表user.employee中100條資料。插入該表中欄位id用序列seq_employee實現，薪水和姓名欄位可以任意填寫。（15分）

 

6.寫一個匿名語句塊，用於執行函式f_employee，並列印執行該函式的結果。（8分）
7.建立儲存過程p_create_emp，用於判斷表employee是否存在，如果存在則刪除該表。（15分）
8.寫一個匿名語句塊，用於執行儲存過程p_create_emp。（7分）
答案如下：
SQL> create table employee(
2 id number(10) not null, — 員工工號
3 salary number(10,2) default 0 not null, — 薪水
4 name varchar2(24) not null — 姓名
5 );
表已建立。
—第一題答案：
SQL> Create sequence seq_employee increment by 1 start with 1 nomaxvalue nocycle;
序列已建立。
—第二題答案：
SQL> declare i number;
2 begin
3 for i in 1 .. 100
4 loop
5 insert into employee
6 values(seq_employee.nextval,1950+i,’王明’||to_char(i));
7 commit;
8 end loop;
9 end;
10 /
PL/SQL 過程已成功完成。
SQL> select * from employee where rownum<11;
ID SALARY NAME
———- ———- ————————
1 1951 王明1
2 1952 王明2
3 1953 王明3
4 1954 王明4
5 1955 王明5
6 1956 王明6
7 1957 王明7
8 1958 王明8
9 1959 王明9
10 1960 王明10
已選擇10行。
3.寫一個語句塊，在語句塊中定義一個顯式遊標，按id升序排列，列印表employee中前十條資料。（15分）
——-第三題答案：
SQL> declare
2 cursor c is select id,salary,name from(select * from employee order by id) where rownum<11;
3 v_record c%rowtype;
4 begin
5 open c;
6 loop
7 fetch c into v_record;
8 exit when c%notfound;
9 dbms_output.put_line(to_char(v_record.id)||’,'||to_char(v_record.salary)||’,'||v_record.name);
10 end loop;
11 close c;
12 end;
13 /
1,1951,王明1
2,1952,王明2
3,1953,王明3
4,1954,王明4
5,1955,王明5
6,1956,王明6
7,1957,王明7
8,1958,王明8
9,1959,王明9
10,1960,王明10
PL/SQL 過程已成功完成。
4.建立儲存過程p_employee，輸入員工薪水範圍，返回員工工號、姓名、薪水結果集，結果集按員工薪水升序排列。（15分）
——-第四題答案
SQL> create or replace procedure p_employee
2 (iminsalary in number,
3 imaxsalary in number)
4 is
5 begin
6 for x in(select id,salary,name from(select * from employee where salary between iminsalary and imaxsalary) order by salary)
7 loop
8 dbms_output.put_line(to_char(x.id)||to_char(x.salary)||x.name);
9 end loop;
10 end;
11 /
過程已建立。
SQL> exec p_employee(2000,2007);
502000王明50
512001王明51
522002王明52
532003王明53
542004王明54
552005王明55
562006王明56
572007王明57
PL/SQL 過程已成功完成。
5.建立函式f_employee實現更新員工薪水的功能，將薪水低於2000且姓wang的員工薪水加5%，其他不變，更新成功則返回0，否則返回1。（15分）
———第五題答案
SQL> create or replace function f_employee return number
is
begin
update employee set salary=salary+salary*0.05 where salary<2000 and name like ‘王%’;
commit;
if sql%rowcount=0 then
return 1;
else
return 0;
end if;
end;
/
函式已建立。
—–第六題答案
SQL> declare a number;
2 begin
3 a:=f_employee();
4 dbms_output.put_line(to_char(a));
5 end;
6 /
0
PL/SQL 過程已成功完成。
SQL> select * from employee where salary<2000 and name like ‘王%’;
未選定行
SQL> select * from employee where rownum<50;
ID SALARY NAME
———- ———- ————————
1 2048.55 王明1
2 2049.6 王明2
3 2050.65 王明3
4 2051.7 王明4
5 2052.75 王明5
6 2053.8 王明6
7 2054.85 王明7
8 2055.9 王明8
9 2056.95 王明9
10 2058 王明10
11 2059.05 王明11
ID SALARY NAME
———- ———- ————————
12 2060.1 王明12
13 2061.15 王明13
14 2062.2 王明14
15 2063.25 王明15
16 2064.3 王明16
17 2065.35 王明17
18 2066.4 王明18
19 2067.45 王明19
20 2068.5 王明20
21 2069.55 王明21
22 2070.6 王明22
ID SALARY NAME
———- ———- ————————
23 2071.65 王明23
24 2072.7 王明24
25 2073.75 王明25
26 2074.8 王明26
27 2075.85 王明27
28 2076.9 王明28
29 2077.95 王明29
30 2079 王明30
31 2080.05 王明31
32 2081.1 王明32
33 2082.15 王明33
ID SALARY NAME
———- ———- ————————
34 2083.2 王明34
35 2084.25 王明35
36 2085.3 王明36
37 2086.35 王明37
38 2087.4 王明38
39 2088.45 王明39
40 2089.5 王明40
41 2090.55 王明41
42 2091.6 王明42
43 2092.65 王明43
44 2093.7 王明44
ID SALARY NAME
———- ———- ————————
45 2094.75 王明45
46 2095.8 王明46
47 2096.85 王明47
48 2097.9 王明48
49 2098.95 王明49
已選擇49行。
—–第七題答案
SQL> create or replace procedure p_create_emp
2 is
3 v_count number;
4 begin
5 select count(*) into v_count from user_tables where table_name=’EMPLOYEE’;
6 if v_count=0 then
7 return;
8 else
9 execute immediate ‘drop table employee’;
10 end if;
11 end;
12 /
過程已建立。

———第八題答案
SQL> exec p_create_emp;
PL/SQL 過程已成功完成。
SQL> select * from employee;
select * from employee
*
ERROR 位於第 1 行:
ORA-00942: 表或檢視不存在

如何查詢Oracle資料庫中已經建立的索引？

查詢資料字典user_indexes和user_ind_columns

例子:

SQL> SELECT ic.index_name, ic.column_name,

2         ic.column_position col_pos,ix.uniqueness

3  FROM    user_indexes ix, user_ind_columns ic

4  WHERE   ic.index_name = ix.index_name

5  AND ic.table_name = ‘S_EMP’;

注意: 資料字典裡存放的字元都是大寫的.

哪些情況下不應該使用索引？

1. 表很小的情況下，沒有必要使用索引

2. 不經常在Where後使用的比較欄位

3. 如果表資料需要頻繁修改，不建議使用索引

4. 如果查詢返回記錄很多，不建議使用索引

5. 如果where後含IS NULL /IS NOT NULL/ like ‘%輸入符%’等條件，不建議使用索引。

SQL裡面如何插入自動增長序列號欄位？

INSERT時如果要用到從1開始自動增長的數字做唯一關鍵字, 應該先建立一個序列號.

CREATE SEQUENCE 序列號的名稱 (最好是表名+序列號標記) INCREMENT BY 1 START WITH 1 MAXVALUE 99999 NOCYCLE NOCACHE;

其中最大的值按欄位的長度來定,比如定義的自動增長的序列NUMBER(6) , 最大值為999999

INSERT 語句插入這個欄位值為: 序列號的名稱.NEXTVAL

例子: SQL> insert into s_dept(id, name, region_id) values (s_dept_id.nextval, ‘finance’, 2);

1 row created.

只有執行了序列號的名稱. nextval後序列號的名稱. currval 才有效才有值.

不用遊標的SQL語句有哪些？

1. 說明性語句
2. 資料定義語句
3. 資料控制語句
4. 查詢結果為單記錄的SELECT語句
5. 非CURRENT形式的UPDATE語句
6. 非CURRENT形式的DELETE語句
7. INSERT語句
所有的說明性語句及資料定義與控制語句都不需要使用遊標，他們是嵌入式SQL中最簡單的一類語句，不需要返回結果資料，也不需要使用主變數。 在主語言中嵌入說明性語句及資料定義與控制語句，只要給語句加上字首EXEC SQL 和預計結束符即可。
INSERT語句也不需要使用遊標，但通常需要使用主變數
SELECT語句，UPDATE語句，DELETE語句則要複雜些。

必須要使用遊標的SQL語句有那些？

必須要使用遊標的SQL語句有：
1. 查詢結果為多結果的SELECT語句
2. CURRENT形式的UPDATE語句
3. CURRENT形式的DELETE語句

查詢優化的一般準則有哪些？

1. 選擇運算應儘可能先做。這是優化策略中最重要最基本的一條
2. 在執行連線前對關係適當的預處理，方法有兩種：在連線屬性上建立索引和對關係排序，然後執行連線，第一種稱為索引連線方法，第二種稱為排序合併連線方法
3. 把投影運算和選擇運算同時進行。
4. 把投影同其前或者其後的雙目運算結合起來，沒有必要為了去掉某些欄位而掃描一下關係
5. 把某些選擇同它前面要執行的笛卡爾積結合起來成為一個連線運算，連線特別是等值連線運算要比同樣關係上的笛卡爾積節省很多時間
6. 找出公共子表示式，先計算出公共子表示式的值才參與運算。

ORACLE二十問

1. Oracle安裝完成後的初始口令?　

internal/oracle
sys/change_on_install
system/manager
scott/tiger
sysman/oem_temp　　

2. ORACLE9IAS WEB CACHE的初始預設使用者和密碼？　　

administrator/administrator　

3. oracle 8.0.5怎麼建立資料庫?　　

用orainst。如果有motif介面，可以用orainst /m　

4. oracle 8.1.7怎麼建立資料庫?

dbassist　

5. oracle 9i 怎麼建立資料庫?　

dbca　　

6. oracle中的裸裝置指的是什麼?　　

裸裝置就是繞過檔案系統直接訪問的儲存空間　

7. oracle如何區分 64-bit/32bit 版本？？？　

$ sqlplus ‘/ AS SYSDBA’
SQL*Plus: Release 9.0.1.0.0 – Production on Mon Jul 14 17:01:09 2003
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.0.1.0.0 – Production
With the Partitioning option
JServer Release 9.0.1.0.0 – Production
SQL> select * from v$version;
BANNER
Oracle9i Enterprise Edition Release 9.0.1.0.0 – Production
PL/SQL Release 9.0.1.0.0 – Production
CORE 9.0.1.0.0 Production
TNS for Solaris: Version 9.0.1.0.0 – Production
NLSRTL Version 9.0.1.0.0 – Production
SQL>　　

8. SVRMGR什麼意思？　

svrmgrl，Server Manager.

9i下沒有，已經改為用SQLPLUS了

sqlplus /nolog
　　變為歸檔日誌型的　

9. 請問如何分辨某個使用者是從哪臺機器登陸ORACLE的?

SELECT machine , terminal FROM V$SESSION;　　

10. 用什麼語句查詢欄位呢？　

desc table_name 可以查詢表的結構
select field_name,… from … 可以查詢欄位的值
select * from all_tables where table_name like ‘%’
select * from all_tab_columns where table_name=’??’　　

11. 怎樣得到觸發器、過程、函式的建立指令碼？　

desc user_source
user_triggers 　　

12. 怎樣計算一個表佔用的空間的大小？　

select owner,table_name,
NUM_ROWS,
BLOCKS*AAA/1024/1024 “Size M”,
EMPTY_BLOCKS,
LAST_ANALYZED
from dba_tables
where table_name=’XXX’;
Here: AAA is the value of db_block_size ;
XXX is the table name you want to check　

14. 如何檢視系統被鎖的事務時間？
select * from v$locked_object ;

15. 如何以archivelog的方式執行oracle。
init.ora
log_archive_start = true
RESTART DATABASE
16. 怎麼獲取有哪些使用者在使用資料庫
select username from v$session;

17. 資料表中的欄位最大數是多少?

　　表或檢視中的最大列數為 1000　　

18. 怎樣查得資料庫的SID ?

select name from v$database;

　　也可以直接檢視 init.ora檔案

19. 如何在Oracle伺服器上通過SQLPLUS檢視本機IP地址 ?

select sys_context(’userenv’,'ip_address’) from dual;

　　如果是登陸本機資料庫，只能返回127.0.0.1，呵呵　　

20. unix 下怎麼調整資料庫的時間？

su -root

date -u 08010000

 

如何高效率的查詢一個月以內的資料？

進行時間比較要儘量避免用sysdate. 比如：如果使用select * from eventtable where eventdate>sysdate-30進行查詢,當資料量小的時候看不出來，資料量大一些就會發現執行很慢，但日期型字 段上也是有索引的，為什麼會慢呢？ 原來是Oracle在進行查詢的時候不斷地去取sysdate這個不斷變化的值，而不是我們想象中的一次產生一個條件語句然後進行查詢。為了加快速度，我 們可以先把當天的日期取出來，然後轉成字串後再用如下語句查，select * from eventtable where eventdate > to_date(’2001-12-1′,’yyyy-mm-dd’)。速度相差幾十倍。

Oracle怎樣計算一個表佔用的空間的大小？

可以使用一下語句來進行計算：

select owner ,
table_name,
NUM_ROWS,
BLOCKS*AAA/1024/1024 “Size M”,
EMPTY_BLOCKS,
LAST_ANALYZED
from dba_tables
where table_name= XXX ;
AAA 是指 db_block_size的值 ;
XXX 是你要查詢的表名

如何在Oracle中檢視各個表、表空間佔用空間的大小？

使用以下語句檢視當前使用者每個表佔用空間的大小：
Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name

使用一下語句檢視每個表空間佔用空間的大小：
Select Tablespace_Name,Sum(bytes)/1024/1024 From Dba_Segments Group By Tablespace_Name

介紹一下Oracle的操作符優化？

IN ：

IN寫出來的SQL比較容易寫及清晰易懂但是效能總是比較低的，從ORACLE執行的步驟來分析用IN的SQL與不用IN的SQL有以下區別：

ORACLE 試圖將IN轉換成多個表的連線，如果轉換不成功會先執行IN裡面的子查詢，再查詢外層的表記錄，如果轉換成功，則直接採用多個表的連線方式查詢。所以用 IN的SQL至少多了一個轉換的過程。一般的SQL都可以轉換成功，但對於含有分組統計等方面的SQL就不能轉換了。

推薦方法：在業務密集的SQL當中儘量不要採用IN操作符。

NOT IN：

不推薦使用，因為NOT IN不能應用表的索引。

推薦方案：用NOT EXISTS 或（外連線+判斷為空）

<> 操作符（不等於）

不等於操作符不會用到索引的，對它的處理只會產生全表掃描。

推薦方案：用其它相同功能的操作運算代替，如 a<>0 改為 a>0 or a<0  a<>’’ 改為 a>’’

IS NULL 或IS NOT NULL操作（判斷欄位是否為空）

判斷欄位是否為空一般不會應用索引，因為B樹索引是不索引空值的。

推薦方案：

用其它相同功能的操作運算代替，如 a is not null 改為 a>0 或a>’’等。

不允許欄位為空，而用一個預設值代替空值，如業擴申請中狀態欄位不允許為空，預設為申請。

建立點陣圖索引（有分割槽的表不能建，點陣圖索引比較難控制，如欄位值太多索引會使效能下降，多人更新操作會增加資料塊鎖的現象）。

> 及 < 操作符（大於或小於操作符）

大於或小於操作符一般不用調整，因為它有索引就會採用索引查詢，但有的情況下可以對它進行優化，如一個表有100萬記錄，一個數值型欄位A， 30萬記錄的A=0，30萬記錄的A=1，39萬記錄的A=2，1萬記錄的A=3。那麼執行A>2與A>=3的效果就有很大的區別了，因為 A>2時ORACLE會先找出為2的記錄索引再進行比較，而A>=3時ORACLE則直接找到=3的記錄索引。

LIKE：

LIKE 操作符可以應用萬用字元查詢，裡面的萬用字元組合可能達到幾乎是任意的查詢，但是如果用得不好則會產生效能上的問題，如LIKE ‘%5400%’ 這種查詢不會引用索引，而LIKE ‘X5400%’則會引用範圍索引。一個實際例子：用YW_YHJBQK表中營業編號後面的戶標識號可來查詢營業編號 YY_BH LIKE ‘%5400%’ 這個條件會產生全表掃描，如果改成YY_BH LIKE ’X5400%’ OR YY_BH LIKE ’B5400%’ 則會利用YY_BH的索引進行兩個範圍的查詢，效能肯定大大提高。

Oracle如何查詢登陸客戶端的機器名？

可以用一下語句查詢登陸客戶端的機器名：

select machine, terminal from V$SESSION

如何提高MySql的安全性？

1.如果MYSQL客戶端和伺服器端的連線需要跨越並通過不可信任的網路，那麼需要使用ssh隧道來加密該連線的通訊。

2.使用set password語句來修改使用者的密碼，先“mysql -u root”登陸資料庫系統，然後“mysql> update mysql.user set password=password(’newpwd’)”，最後執行“flush privileges”就可以了。

3.Mysql需要提防的攻擊有，防偷聽、篡改、回放、拒絕服務等，不涉及可用性和容錯方面。對所有的連線、查詢、其他操作使用基於acl即訪問控制列表的安全措施來完成。也有一些對ssl連線的支援。

4.設定除了root使用者外的其他任何使用者不允許訪問mysql主資料庫中的user表;

加密後存放在user表中的加密後的使用者密碼一旦洩露，其他人可以隨意用該使用者名稱/密碼相應的資料庫;

5.使用grant和revoke語句來進行使用者訪問控制的工作;

6.不要使用明文密碼，而是使用md5()和sha1()等單向的哈系函式來設定密碼;

7.不要選用字典中的字來做密碼;

8.採用防火牆可以去掉50%的外部危險，讓資料庫系統躲在防火牆後面工作，或放置在dmz區域中;

9.從因特網上用nmap來掃描3306埠，也可用telnet server_host 3306的方法測試，不允許從非信任網路中訪問資料庫伺服器的3306號tcp埠，需要在防火牆或路由器上做設定;

10.為了防止被惡意傳入非法引數，例如where id=234，別人卻輸入where id=234 or 1=1導致全部顯示，所以在web的表單中使用”或”"來用字串，在動態url中加入%22代表雙引號、%23代表井號、%27代表單引號;傳遞未檢查過的值給mysql資料庫是非常危險的;

11.在傳遞資料給mysql時檢查一下大小;

12.應用程式需要連線到資料庫應該使用一般的使用者帳號，開放少數必要的許可權給該使用者;

$page_devide$

13.在各程式設計介面(c c++ php perl java jdbc等)中使用特定‘逃脫字元’函式;

在因特網上使用mysql資料庫時一定少用傳輸明文的資料，而用ssl和ssh的加密方式資料來傳輸;

14.學會使用tcpdump和strings工具來檢視傳輸資料的安全性，例如tcpdump -l -i eth0 -w -src or dst port 3306 strings。以普通使用者來啟動mysql資料庫服務;

15.不使用到表的聯結符號，選用的引數 –skip-symbolic-links;

16.確信在mysql目錄中只有啟動資料庫服務的使用者才可以對檔案有讀和寫的許可權;

17.不許將process或super許可權付給非管理使用者，該mysqladmin processlist可以列舉出當前執行的查詢文字;super許可權可用於切斷客戶端連線、改變伺服器執行引數狀態、控制拷貝複製資料庫的伺服器;

18.file許可權不付給管理員以外的使用者，防止出現load data ‘/etc/passwd’到表中再用select 顯示出來的問題;

19.如果不相信dns服務公司的服務，可以在主機名稱允許表中只設定ip數字地址;

20.使用max_user_connections變數來使mysqld服務程式，對一個指定帳戶限定連線數;

21.grant語句也支援資源控制選項;

22.啟動mysqld服務程式的安全選項開關，–local-infile=0或1 若是0則客戶端程式就無法使用local load data了，賦權的一個例子grant insert(user) on mysql.user to ‘user_name’@'host_name’;若使用–skip-grant-tables系統將對任何使用者的訪問不做任何訪問控制，但可以用 mysqladmin flush-privileges或mysqladmin reload來開啟訪問控制;預設情況是show databases語句對所有使用者開放，可以用–skip-show-databases來關閉掉。

23.碰到error 1045(28000) access denied for user ‘root’@'localhost’ (using password:no)錯誤時，你需要重新設定密碼，具體方法是:先用–skip-grant-tables引數啟動mysqld，然後執行 mysql -u root mysql,mysql>update user set password=password(’newpassword’) where user=’root’;mysql>flush privileges;，最後重新啟動mysql就可以了。

什麼是ddl dml和dcl？

DDL :資料定義語言，用於定義和管理 SQL 資料庫中的所有物件的語言
1.CREATE – to create objects in the database   建立資料庫物件
2.ALTER – alters the structure of the database   修改資料庫物件
3.DROP – delete objects from the database   刪除資料庫物件
4.TRUNCATE – remove all records from a table, including all spaces allocated for the records are removed
TRUNCATE TABLE [Table Name]。
下面是對Truncate語句在MSSQLServer2000中用法和原理的說明：
Truncate table 表名 速度快,而且效率高,因為:
TRUNCATE TABLE 在功能上與不帶 WHERE 子句的 DELETE 語句相同：二者均刪除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系統和事務日誌資源少。
DELETE 語句每次刪除一行，並在事務日誌中為所刪除的每行記錄一項。TRUNCATE TABLE 通過釋放儲存表資料所用的資料頁來刪除資料，並且只在事務日誌中記錄頁的釋放。
TRUNCATE TABLE 刪除表中的所有行，但表結構及其列、約束、索引等保持不變。新行標識所用的計數值重置為該列的種子。如果想保留標識計數值，請改用 DELETE。如果要刪除表定義及其資料，請使用 DROP TABLE 語句。
對於由 FOREIGN KEY 約束引用的表，不能使用 TRUNCATE TABLE，而應使用不帶 WHERE 子句的 DELETE 語句。由於 TRUNCATE TABLE 不記錄在日誌中，所以它不能啟用觸發器。
TRUNCATE TABLE 不能用於參與了索引檢視的表。
5.COMMENT – add comments to the data dictionary 註釋
6.GRANT – gives user’s access privileges to database 授權
7.REVOKE – withdraw access privileges given with the GRANT command   收回已經授予的許可權

DML:資料操作語言，SQL中處理資料等操作統稱為資料操縱語言
1.SELECT – retrieve data from the a database           查詢資料
2.INSERT – insert data into a table                    新增資料
3.UPDATE – updates existing data within a table    更新資料
4.DELETE – deletes all records from a table, the space for the records remain   刪除
5.CALL – call a PL/SQL or Java subprogram
6.EXPLAIN PLAN – explain access path to data
Oracle RDBMS執行每一條SQL語句，都必須經過Oracle優化器的評估。所以，瞭解優化器是如何選擇(搜尋)路徑以及索引是如何被使用的，對優化SQL語句有很大的幫助。Explain可以用來迅速方便地查出對於給定SQL語句中的查詢資料是如何得到的即搜尋路徑(我們通常稱為Access Path)。從而使我們選擇最優的查詢方式達到最大的優化效果。
7.LOCK TABLE – control concurrency 鎖，用於控制併發

DCL:資料控制語言，用來授予或回收訪問資料庫的某種特權，並控制資料庫操縱事務發生的時間及效果，對資料庫實行監視等
COMMIT – save work done 提交
SAVEPOINT – identify a point in a transaction to which you can later roll back 儲存點
ROLLBACK – restore database to original since the last COMMIT   回滾
SET TRANSACTION – Change transaction options like what rollback segment to use   設定當前事務的特性，它對後面的事務沒有影響．

什麼是資料庫的約束？

資料庫約束是防止非法記錄的規則， 約束儲存在資料字典(data dictionary)中， 約束可以被定義在列級或者表級。

Oracle中包括一下集中約束：

1. Not Null – 明確一列資料不能包含null值

2. Unique – 強制所有資料行不能有重複值

3. Primary Key – 每一行資料的唯一標示

4. Foreign Key – 強制一列資料與引用表的外來鍵約束關係

5. Check – 檢查，明確規定一個必須為true的condition

Oracle資料庫架構中包括幾層？每層都有什麼元素？

Oracle資料庫包括一個邏輯層和物理層，物理層包括Oracle磁碟上的檔案, 邏輯層用來對映資料和物理層的檔案。

邏輯層包括一下元素：

一個或者多個表空間。

資料庫Schema: 包括表，叢集，索引，檢視，儲存過程，資料庫觸發器和sequences.

介紹一下SQL中union, intersect 和 minus

Union用來返回多個查詢的結果的總和去掉重複的結果

語法：
SELECT column1, column2 FROM tablename1
UNION
SELECT column1, column2 FROM tablename2;

Intersect 用來返回多個查詢中共同的結果，intersect會忽略null值

語法：

SELECT column1, column2 FROM tablename1
INTERSECT
SELECT column1, column2 FROM tablename2;

MUNUS返回出現在第一個查詢結果中但是不出現在第二個查詢結果的結果集。

語法：

SELECT column1, column2 FROM tablename1
MINUS
SELECT column1, column2 FROM tablename2;

主鍵(Primary Key)約束和唯一性(UNIQUE)約束有什麼區別？

一個表只能由一個主鍵，一個表可以有很多個唯一鍵(UNIQUE Key)

主鍵不允許有null值，UNIQUE允許null值

Oracle中delete, truncate 和 drop的區別？

Delete命令用來刪除表的全部或者一部分資料行，執行delete之後，使用者需要提交(commmit)或者回滾(rollback) transaction 來執行刪除或者撤銷刪除， delete命令會觸發這個表上所有的delete觸發器。

Truncate刪除表中的所有資料， 這個操作不能回滾，也不會觸發這個表上的觸發器，TRUNCATE比delete更快，佔用的空間更小。

Drop命令從資料庫中刪除表， 所有的資料行，索引和許可權也會被刪除，所有的DML觸發器也不會被觸發，這個命令也不能回滾。

rowid和rownum有什麼不同？

RowId是一個資料庫內部的概念，表示表的一行，用來快速的訪問某行資料

Rownum是結果集的一個功能， 例如select * from Student where rownum = 2 就是得到結果集的第二行。

檢視的作用是什麼？

資料庫檢視的作用只要有：

1. 資料庫檢視隱藏了資料的複雜性。

2. 資料庫檢視有利於控制使用者對錶中某些列的訪問。

3. 資料庫檢視使使用者查詢變得簡單。

檢視是一個虛擬表，其內容由查詢定義。同真實的表一樣，檢視包含一系列帶有名稱的列和行資料。但是，檢視並不在資料庫中以儲存的資料值集形式存在。行和列資料來自由定義檢視的查詢所引用的表，並且在引用檢視時動態生成。

對其中所引用的基礎表來說，檢視的作用類似於篩選。定義檢視的篩選可以來自當前或其它資料庫的一個或多個表，或者其它檢視。分散式查詢也可用於定義使用多個異類源資料的檢視。如果有幾臺不同的伺服器分別儲存組織中不同地區的資料，而您需要將這些伺服器上相似結構的資料組合起來，這種方式就很有用。

通過檢視進行查詢沒有任何限制，通過它們進行資料修改時的限制也很少。

什麼是Oracle的同義詞(synonym)? 如何建立synonym?

同義詞是相當於別名，是為了簡化一些表名或者資料物件。 當我們以一個使用者登陸而要去訪問另一個使用者建立的資料物件時，我們需要使用使用者名稱.物件名的方法來使用，這樣造成物件名難記，麻煩，使用同義詞可以解決這個問題。

假定我們現在以system的身份的登陸進去，我們需要訪問scott使用者下面的一個表emp;

常規的寫法是:select * from scott.emp;

現在我們可以先建立一個同義詞:
create synonym my_syn for scott.emp;

然後我們這樣來訪問這個表了:
select * from my_syn;

什麼是資料庫鎖？Oracle中都有哪些型別的鎖？

鎖是用來在多個使用者同時訪問同一個表的時候保護資料的。 它是Oracle的一種併發控制技術。鎖使資料庫最大併發的情況下保證資料的完整性。 Oracle會在需要的時候自動的提供鎖的功能。

鎖的種類：

共享鎖： 這個鎖是資料在被viewed的時候放置的。

排他鎖： 這種鎖在Insert, Update, Delete命令執行的時候放置的，每一條記錄同一時間只能有一個排他鎖。

觸發器(trigger)的功能都有哪些？寫出一個觸發器的例子

觸發器是資料庫中由一個時間觸發的特殊的儲存過程，他不是由程式條用也不是手工啟動的。觸發器的執行可以由對一個表的insert,delete, update等操作來觸發，觸發器經常用於加強資料的完整性約束和業務規則等等。

觸發器的功能主要有一下六種：

1、 允許/限制對錶的修改

2、 自動生成派生列，比如自增欄位

3、 強制資料一致性

4、 提供審計和日誌記錄

5、 防止無效的事務處理

6、 啟用複雜的業務邏輯

Create table foo(a number);
Create trigger biud_foo
Before insert or update or delete
On foo
Begin
If user not in (‘DONNY’) then
Raise_application_error(-20001, ‘You don’t have access to modify this table.’);
End if;
End;

如何殺掉ORACLE裡面長期沒有釋放的鎖？

如果一個資料庫insert update delete操作很長時間沒有反應，就可能出現了沒有正常釋放的鎖。

可以用以下SQL語句殺掉沒有正常釋放的鎖：

alter system kill session ’sid,serial#’;

如何檢視當前ORACLE資料庫裡面鎖的情況？

檢視資料庫鎖的情況必須要有DBA許可權，可以使用一下SQL 語句：

select object_id,session_id,locked_mode from v$locked_object;

select t2.username,t2.sid,t2.serial#,t2.logon_time

from v$locked_object t1,v$session t2

where t1.session_id=t2.sid order by t2.logon_time;

長時間出現的鎖可能是沒有正常釋放的鎖。

Oracle的鎖有幾種模式？

Oracle裡面的鎖共有一下幾種模式：

0: none

1:  null 空

2：Row-S 行共享(Row Share RS): 共享表鎖

3：Row-X 行專用(RX): 用於行資料的修改

4：Share 共享鎖(S): 阻止其他DML操作

5：S/Row-X 共享行專用(SRX): 阻止其他事務操作

6：exclusive 專用(X): 獨立訪問使用

以上可以看出，數字越大的鎖影響的操作越多，鎖的級別越高。

一般的查詢語句是小於2的鎖，如select * from *

select … from … for update 是2的鎖，這時候返回集的資料行都將處於行級(Row-X)獨佔式鎖定，其他物件只能對這些資料進行查詢，而不能進行更新或者select for update操作。

insert/update/delete是3的鎖， 在這些操作沒有commit之前插入同樣的記錄會沒有反應，因為3的鎖必須要等到前一個3的鎖執行釋放掉以後才能繼續。 建立索引的時候也會產生3,4級別的鎖。

locked_mod為2,3,4的鎖，不影響DML（insert,delete,update,select）操作，但是DDL（alter,drop）等修改表結構的操作會提示ora-00054錯誤。

當有主外來鍵約束時執行update/delete操作可能會產生4,5的鎖。

DDL語句時是6的鎖。