XML Technology Research Lab: Microsoft SQL Server 2000 簡介

Microsoft SQL Server 2000 簡介

本教材主要參考由施威銘研究室編著、旗標出版的Microsoft SQL Server 2000 設計實務與管理實務 以及由曾守正與周韻寰編著、儒林出版的資料庫系統應用實務所編寫而成。
Written by: 國立中興大學資管系呂瑞麟 Eric Jui-Lin Lu

請勿轉貼

看其他教材

組成單元

SQL Server 的主要組成單元有：

資料庫： SQL Server 資料庫的副檔名為 .MDF，而存放紀錄（log）的副檔名為 .LDF（可參考 data 路徑內的檔案）。 SQL Server 有一些特定的系統資料庫
1. master 資料庫：這個資料庫記錄有關 SQL Server 的資訊，包括所有的登入帳號、系統的組態、各資料的初始資訊等重要資料。若 master 資料庫毀損了，可使用位於 80\tools\binn 目錄下之 rebuildm.exe 來重建。
2. msdb 資料庫：它是提供 SQL Server Agent 作各類排程作業所用的資料庫。另外，有關 backup/restore 、複寫等資訊也放在這裡。
3. model 資料庫：基本上，它是一個樣板資料庫，所有新增的資料庫的內容都由這個資料庫複製過去。
4. tempdb 資料庫：它是用來存放所有作業過程中所產生的資料用的。
Transact-SQL：除了標準的 ANSI SQL 之外，還包含
1. 流程控制的指令：如 if、while 等。
2. 使用者自訂的資料型態
3. 預儲程序（Stored Procedures）與觸發程序（Triggers）
命令列應用程式： ISQL
視窗應用程式：
1. ISQL/w：即 Query Analyzer
2. SQL 用戶端設定程式
3. SQL Service Manager 可以讓你啟動及結束服務程式
4. MMC (Microsoft Management Console；即 SQL Enterprise Manager) 程式用來管理伺服器
5. SQL Server 的效能監視器，除了可用來監視效能外，也可用來作最佳化的調整。

新增功能

Microsoft SQL 2000 比舊版的 SQL 7.0 和 SQL 6.5 多出許多新的功能，我們在此只對我們常見的部分做簡單的說明：

支援 XML：關聯式資料庫引擎已可使用「延伸式標記語言」(XML) 的文件格式傳回資料。不僅如此，XML 還可以用來插入、更新和刪除資料庫中的值。
可以有多個執行個體：SQL Server 2000 支援在同一部電腦上執行多個關聯式資料庫引擎的執行個體 (Instance)。每個執行個體都有自己專屬的一套系統和使用者資料庫。執行個體主要是套用到資料庫引擎及其支援的元件，而不是套用到用戶端工具。對於預設執行個體而言，服務的名稱仍然是 MSSQLServer 與 SQLServerAgent。對於具名執行個體而言，服務的名稱則變成 MSSQL$instancename 與 SQLAgent$instancename，讓執行個體的啟動及停止與伺服器上的其他執行個體無關。
新的資料類型：SQL Server 2000 引進了三種新的資料型別。bigint 為一種 8 位元組的整數型別。sql_variant 為一種可以儲存多種資料型別資料值的型別。table 則為允許應用程式儲存暫存結果以供稍後使用的型別，這些資料型別可以適用於變數，並可以作為使用者自訂函數的傳回型別。

安裝

註冊 SQL Server 伺服器：你可以在 Enterprise Manager 註冊 local 的或者遠端的 SQL Servers。
更改資料庫管理員（sa）的密碼：
「SQL Server 群組 / <Your Server Name> / 安全性 / 登入」，並選擇「sa」的帳號後，按右鍵來選取「內容」。如果你對資料的使用者想多了解一些，你可以現在先到「使用者權限管理」把"使用者基本觀念"看一看。
開啟 Enterprise Manager 並在本地的 SQL Server 圖示上按右鍵來選取「內容」來更改 SQL Server 的特性。
利用預儲程序瞧一瞧有哪些 metadata？
1. 利用 Enterprise Manager 看看有哪些系統表格（system tables）。
2. 找出有多少資料庫？
```
           sp_databases
       
```
3. 找出有哪些表格？
```
           (1) sp_tables
           (2) sp_tables @TABLE_TYPE = "'TABLE'"
       
```
4. Demo 連結至遠端 SQL Server 的方法
  1. IP is 163.17.9.7, port is 1433.
  2. 利用 Query Analyzer：使用前請先使用用戶端網路公用程式 來設定。
  3. 利用 Enterprise Manager

建立磁碟鏡像

參考由陳玄玲與許皓翔編著、松崗出版的精通 NT Server 4.0。

建立資料庫

建議之管理方式：只有資料庫管理員（Database Administrator; DBA）擁有「Database Creators」的權限，其他資料庫使用者只給予表格的產生、刪除、修改等的權限。至於查詢者，只給予讀取之權限。

利用指令建立資料庫的範例：要特別注意，在下面的範例中路徑 c:\mssql\data 必需已經存在，否則無法正確的產生資料庫。

  CREATE DATABASE Bob
  ON
  ( NAME = 'Bob_dat',
    FILENAME = 'c:\mssql\data\bob_dat.mdf',
    SIZE = 4MB,
    FILEGROWTH = 1MB )
  LOG ON
  (
    NAME = 'Bob_log',
    FILENAME = 'c:\mssql\data\bob_log.ldf',
    SIZE = 2MB,
    FILEGROWTH = 1MB )

利用指令刪除資料庫的範例：要注意目前正在使用 (被使用者開啟來讀取或寫入) 的資料庫不能卸除。
```
  DROP DATABASE Bob         # 刪除資料庫 bob
  DROP DATABASE Bob, Fish   # 同時刪除資料庫 bob 和 fish
  
```
以上的指令可由 Query Analyzer 或 ISQL 輸入，利用 ISQL 輸入指令的方式如下：
1. 開啟 MS-DOS Prompt
2. 輸入 isql -Usa -Sec1
  -S 表示後面的名稱 ec1 為在 Alias Manager 所設定的資料庫名稱；而 -U 表示後面的名稱為你的資料庫帳號。
3. 結束 ISQL，請輸入 quit
若事先指定的硬碟已經滿了，而我仍需要加大我的資料庫，怎麼辦？「SQL Server 群組 / <Your Server Name> / 資料庫」，選擇你的資料庫，並在資料庫上按右鍵然後選擇「內容 / 資料檔案」。

建立表格

（曾3-13）

從其他資料來源（如 Microsoft Access）匯入資料（import data）。可下載 Bob.mdb，一個 Microsoft Access 資料庫作範例。這個資料庫包含三個表格各為 books、bookstores、以及 orders。
1. 點選資料庫（如 dbms）
2. 按右鍵並選「所有工作 / 匯入資料」。
你也可以利用匯出資料（export data）將資料儲存於 Microsoft Access 然後帶回家使用。試試看？
在匯入/匯出的過程中，有很多的設定會不見，那怎麼辦？甚至，當你完成程式開發而必須在另一部機器上安裝，你需要一步一步的重新設定嗎？要是設定的過程中出錯怎麼辦？這時你需要使用 script。
1. Script 是有如 autoexe.bat 的文字檔，資料庫管理系統可以載入該 script 檔並執行它的內容。
2. 將下列範例存入一文字檔（如 1.sql）
```
       select db_name()

       use dbms

       select db_name()
       
```
3. 利用 Query Analyzer 開啟 1.sql，並執行
4. 你可以利用「SQL Server 群組 / <Your Server Name> / 資料庫」，選擇你的資料庫，並在資料庫上按右鍵然後選擇「所有工作 / 產生 SQL 指令碼」來產生所有跟這個資料庫相關的 script，如此一來，你便可以利用這個產生的 script 檔到另一部電腦上快速的建立起你的資料庫！

你可以使用 Enterprise Manager 來產生新的資料表，也可以利用指令來產生資料表，如（曾3-13）

  create table bookstores
  ( no int not null primary key,
    name varchar(10),
    rank int,
    city varchar(8))

資料型態：

資料型態	說明	範例
INT	長度為 4 個 bytes；介於 -2,147,483,648 與 2,147,483,647 間的整數
SMALLINT	長度為 2 個 bytes；介於 -32,768 與 32,767 間的整數
TINYINT	長度為 1 個 bytes；介於 0 與 255 間的整數
BIGINT	長度為 8 個 bytes；介於 -2^63 與 2^63-1 間的整數
FLOAT[(n)]	n 是儲存 float 數字的小數位數，介於 1 與 53 間；若 n 介於 1 與 24 間，儲存大小為 4 個 bytes，有效位數為 7 位數；若 n 介於 25 與 53 間，儲存大小為 8 個 bytes，有效位數為 15 位數	create table p_ex (num1 real, num2 float) insert into p_ex values(4000000.123456789, 4000000.123456789012345) insert into p_ex values(400.123456789, 400.1234567890123456789) select * from p_ex // real 共顯示八位數，float 共顯示 17 位數
REAL	長度為 4 個 bytes；介於 -3.4E-38 與 3.4E+38 間的浮點數；與 FLOAT(24) 相同
DECIMAL[(p[,s])]	使用 2 到 17 個 bytes 來儲存資料，可儲存的值介於 -10³⁸-1 與 10³⁸-1 之間；p 用來定義小數點兩邊可以被儲存的位數總數目，而 s 代表小數點右邊的有效位數（s < p）；p的預設值為 18 而 s 的預設值為0	create table p_ex (num1 numeric(19,9), num2 decimal) insert into p_ex values(4000000.123456789, 4000000.123456789012345) select * from p_ex
NUMERIC[(p[,s])]	與 DECIMAL[(p[,s])] 同
CHAR[(n)]	固定長度為 n 的字元型態，n 必須介於 1 與 8000 之間
VARCHAR[(n)]	與 CHAR 相同，只是若輸入的資料小於 n，資料庫不會自動補空格，因此為變動長度之字串
NCHAR 與 NVARCHAR	與 CHAR 以及 VARCHAR 相同，只是每一個字元為兩個 bytes 的 unicode，且 n 最大為 4000
DATETIME	長度為 8 個 bytes；介於 1/1/1753 與 12/31/9999 間的日期時間
SMALLDATETIME	長度為 4 個 bytes；介於 1/1/1900 與 6/6/2079間的日期時間	create table d_ex (d1 datetime, d2 smalldatetime) insert into d_ex values ('1/1/1753', 'Jan 2 1900') select * from d_ex
MONEY	長度為 8 個 bytes 的整數，小數點的精確度取四位
SMALLMONEY	長度為 4 個 bytes 的整數，小數點的精確度取四位
BIT	只佔用一個位元，且不允許存放 NULL 值
BINARY[(n)]	固定長度為 n+4 個 bytes； n 為 1 到 8000 的值，輸入的值必需符合兩個條件：（1）每一個值皆為 0-9、a-f 的值；（2）每一個值的前面必須有 0X	create table b_ex (x binary(1), y binary(2)) insert into b_ex values(0x0, 0x0) insert into b_ex values(0x1, 0x1) insert into b_ex values(0xff, 0xff) insert into b_ex values(0xfff, 0xfff) insert into b_ex values(0xff, 0xfff) insert into b_ex values(0xffff, 0xffff) insert into b_ex values(0xff, 0xffff) select * from b_ex
VARBINARY[(n)]	與 BINARY 相同，只是若輸入的資料小於 n，資料庫不會自動補 0，因此長度為變動的
TEXT	用來儲存大量的（可高達兩億個位元組）字元資料，儲存空間以 8k 為單位動態增加
NTEXT	和 TEXT 雷同，只是儲存的是 Unicode 資料
IMAGE	和 TEXT 雷同，只是儲存的是影像資料
SQL_VARIANT	此資料型別可儲存 text、ntext、timestamp 與 sql_variant以外的各種 SQL Server 支援的資料型別。	a sql_variant column can contain smallint values for some rows, float values for other rows, and char/nchar values in the remainder. create table b_ex (x int, y sql_variant) insert into b_ex values(5, '五十') insert into b_ex values(6, 60) select * from b_ex

了解 NULL：
表格欄位的 NULL 的特性可以讓你省略掉一欄位中的欄位值，其值被資料庫管理系統解讀為「未定義」或「不存在」，這與空白、零、或 ASCII 的 null 值不同。若一個欄位被定義為 NOT NULL，則表示該欄位不得為空的。
1. 輸入 NULL 值： NULL 與 'NULL' 不同
```
    create table ntable (x int null, y char(10) null)
    insert into ntable values(NULL, null)
    select * from ntable
    
```
2. 輸入部分值
```
    insert into ntable (x) values(5)
    insert into ntable (y) values('5')
    insert into ntable (y) values('null')
    select * from ntable
    
```
3. 可以利用 is null 或者 is not null 來檢查，例如請查出所有 y 非 null 的資料。
```
    SELECT * FROM ntable WHERE y IS NOT NULL
    
```
4. NULL 的處理：在 ORDER BY 、 GROUP BY 、與 DISTINCT 中，所有 NULL 的值被視為相同。
```
    select * from ntable order by y
    
```
5. 設定欄位為 NOT NULL
```
    create table ntable (x int null, y char(10) not null)
    
```
  因此我們無法執行 insert into ntable (x) values(5)。
了解 identity：欄位的特性除了「NULL/NOT NULL」之外，還可以利用 identity 來定義。當一個欄位被定義為 identity 後，你可以省略該欄位的輸入，資料庫系統會自動依照你所定義的遞增值來遞增。identity(1,2) 指定該欄位的值從 1 開始，每一次加 2，identity 的預設方式為 identity(1,1)。要注意的是：你只能把 identity 的特性指定給資料型態為 BIGINT、INT、SMALLINT、TINYINT、DECIMAL(p,0)、NUMERIC(p,0) 的欄位。
```
  create table itable (name char(15), row int identity(1,2))
  insert into itable (name) values ('Bob Smith')
  insert into itable (name) values ('Mary Jones')
  select * from itable
  
```
使用者自訂之資料型態：當一組專案人員在進行資料庫的設計時，對於某些欄位的設計可能有所不同，如對於姓名的設定，有些開發人員定義它為 char(15) 而另外一些開發人員可能定義它為 char(20)，這除了造成定義的不統一外，更可能造成資料在不同的系統轉換成不同的結果。我們可以利用使用者自訂之資料型態並宣告所有的專案設計都遵循此一規格，以降低前述之問題。
1. 利用 Enterprise Manager：展開資料庫（如 Bob），並在展開項目中，選擇「使用者自訂資料型別」來新增與刪除。
2. 新增的語法： sp_addtype typename, system_datatype, null_type
3. 檢查新增的使用者自訂型態： sp_help typename
4. 新增的範例：
```
    sp_addtype TP_names, 'char(15)', 'not null'
    
```
5. 刪除的語法： sp_droptype typename
6. 刪除的範例： sp_droptype TP_names
7. 使用者自訂型態的使用：
```
    create table itable (name TP_names, row int)
    insert into itable values('Bob Smith', 1)
    select * from itable
    
```

主鍵（primary key）的建立

single-attribute PK.

multiple-attribute PK.

  CREATE TABLE ntable
  (num1 int,
   num2 money,
   PRIMARY KEY (num1, num2))

外來鍵（Foreign Key）的建立：
1. 指令：
```
        ALTER TABLE orders ADD CONSTRAINT FK_orders_no
              FOREIGN KEY (no) REFERENCES bookstores (no)
  
```
2. 經由 Enterprise Manager 的「設計資料表」視窗中，點選「資料表與索引屬性」圖示，並選擇「關聯性」標籤。
3. 請將 books 中的 id 也加入為外來鍵。
4. 使用指令來變更屬性後，請檢查表格內「設計資料表 / 資料表和索引屬性 / 關聯性」的變化。
5. 外來鍵的建立可以維護部分資料的一致性，例如，
  1. 若 orders 中存在有 books.id 為 1 的資料時，你無法將 id 為 1 的資料從 books 中刪除。若
```
  delete from books where id = 1
  
```
    你會收到錯誤訊息。
  2. 若 bookstores 中不存在 no 為 6 的資料時, 你無法將 no 為 6 的訂單資料加入 orders 中.

外來鍵的刪除

        ALTER TABLE orders DROP CONSTRAINT FK_orders_no

資料的輸入：
1. 在輸入資料的表格，按右鍵並選擇「開啟資料表 / 傳回所有資料列」，再出現的對話窗中開始輸入資料。
2. SQL 語法：
```
        insert into books values(4,'西遊記','吳承恩',140,'聊齋出版社')
    
```
怎麼做才能 insert 如 'a' （包含單引號）的資料？輸入兩個單引號代表一個單引號，例如
```
        insert into books values(7,'''a''','''b''',140,'''c''')
    
```
外來鍵參考圖：
1. 展開資料庫（如 Bob），並選擇「圖表」
2. 按右鍵並選擇「新增資料庫圖表」
3. 利用資料庫圖表你可以很容易的看出資料表之間的關聯性，其實如果你在之前並未設定任何關聯性，你也可以利用這個圖表所提供的工具來設定資料表之間的關聯。
唯一性的限制（Unique Constraints）是讓我們強制某個欄位中所含的值一定不能有重複的資料，（可以用來設定候選鍵（candidate key）)。例如，我們假設在 books 中的 bookname 為一候選鍵，其設定方式如下：
```
   ALTER TABLE books ADD CONSTRAINT UQ_books_name
   UNIQUE NONCLUSTERED (bookname)
   
```
完成上述操作後，試試看能不能再加入一本由另一個出版社出版的「紅樓夢」。
檢查的限制（Check Constraint）能夠用來限制可以被輸入到一資料庫表格中一個或多個欄位中的值。
1. 表格 books 中的單價必須介於 100 與 500 之間：選擇 books 並按右鍵後執行「設計資料表 / 資料表和索引屬性 / 檢查條件約束」；在「條件約束運算式」中輸入 price > 100 and price < 500 並於「條件約束名稱」內輸入 CK_books_1。
2. 表格 bookstores 中的 rank 只可能有 10、20、30、或 40；其他數字皆不可能。選擇 books 並按右鍵後執行「設計資料表 / 資料表和索引屬性 / 檢查條件約束」；在「條件約束運算式」中輸入 rank in (10, 20, 30, 40) 並於「條件約束名稱」內輸入 CK_bookstores_1。
規則物件 (Rule)
規則物件的功能與檢查的限制（check）非常類似，不過規則物件是獨立儲存的物件，它必須與資料表 bind 之後，才能發揮查核的功能。規則物件的優點是，同一個規則物件可以提供不同資料表的不同欄位使用，但每個欄位最多只能和一個規則物件結合。
1. 建立規則指令
```
    CREATE RULE price_rule AS @price >= 1 AND @price <= 50000
    CREATE RULE gender_rule AS @sex in ('男', '女', NULL)
    
```
2. 利用 enterprise manager，「SQL Server 群組 / <Your Server Name> / 資料庫 /規則」
3. 將規則 bind 到資料表的欄位， sp_bindrule rule_name, 'table_name.column_name'
4. 解除 bind 的關係， sp_unbindrule 'table_name.column_name' 或者利用 enterprise manager，「SQL Server 群組 / <Your Server Name> / 資料庫 /規則」，於選取規則後，按右鍵並點選『內容 / 繫結資料行』，將資料行由繫結中移除。
5. 刪除規則，drop rule rule_name [, ... n]。要特別注意的是在刪除規則以前，使用者必須先解除這項規則與所有資料表的關係。

表格查詢

OUTER JOIN:

列出所有書局的訂單資料（包含未下任何訂單的書局）

  select name, id, quantity
  from bookstores LEFT OUTER JOIN orders
  on bookstores.no = orders.no

列出所有書名的訂購資料（可以將 no 為 1 且 id 為 6 的資料從 orders 中刪除，比較容易看出效果）
```
  select bookname, orders.no, quantity
  from orders RIGHT OUTER JOIN books
  on books.id = orders.id
  
```

以下的範例若改為 RIGHT OUTER JOIN 或者 FULL OUTER JOIN 會產生什麼結果？

  select name, bookname, quantity
  from (bookstores LEFT OUTER JOIN orders
  on bookstores.no = orders.no)
  left outer join books
  on books.id = orders.id

索引

索引（Index）是資料庫管理系統內部將表格裡的資料組織成可以更快速的進行資料存取的一種方式。索引是一個表格，它包含某一表格裡屬性值（可為單一屬性或組合屬性），以及其所對應指向這些值存放在表格中的資料分頁的指標。

建立索引的通則：一般來說，這會因為你的經驗、使用時效能的考量等因素來考量，並沒有一個百戰百勝的方程式。但是，你還是可以從你的表格的存取方式來決定；例如，雖然說學號是學生資料表的主鍵，但是若查詢的大部分時間是利用姓名和科系來決定，那麼你大概就可以新增一個以姓名和科系為主的索引。另外，索引的其他候選者也包含外來鍵以及 ORDER BY 與 GROUP BY 裡面所含的欄位。如果欄位的內容同質性很高，例如性別欄只有男跟女兩種，那麼這個欄位就不適合做索引。
多少個索引才不會造成效能的明顯下降? 雖然索引的確能增快查詢的速度，但是卻會導致新、修改的速度下降，究竟可以建立多少個索引才不會造成效能的明顯下降？依據 Stephen Wynkoop 的說法，SQL Server 的 magic number 是 5 個。
索引的使用：關鍵在於 SQL Server 考慮一索引時，SELECT 敘述的其中一個欄位名稱必須是在索引中的第一欄位，否則不會使用這個索引。在上述的學生例子中，若 SELECT 敘述只用到科系，則該索引不會被使用到，反之，查詢中若包含「姓名」或「姓名與科系」，則該索引會被使用。
欄位的資料型態為 TEXT、NTEXT、IMAGE 者不能成為索引的欄位。每一個索引所使用的欄位最多只能包含 16 個欄位。
索引的建立：
1. Enterprise Manager
  1. 選定表格後（如 books），按右鍵並選「所有工作 / 管理索引」。
  2. 「叢聚於磁碟上」的意思是盡量將資料在邏輯上的順序與實際在磁碟上的順序保持一致；也就是在磁碟中盡量將有某種相關順序的資料擺放在一起，以減少存取資料時在磁碟機中所花的搜尋時間，增進效率。因此，依據某屬性所建立的索引，如果被設定為叢聚時，則稱為「叢聚索引」（Clustering Index）。注意，一個表格只能建立一個叢聚索引。請為 bookstores 之 no 、 books 之 id 、與 orders 之 no 和 id 建立叢聚索引。
  3. 忽略重複值：若你的 index 設定為 unique，可是又不希望已經存在的資料有重複時會造成錯誤，可以設定此選項來忽略。
  4. 填滿因數：在一索引上指定的 FILLFACTOR 值，可以用來告訴 SQL Server 如何將資料打包（pack）到索引資料分頁中。依據 Stephen Wynkoop 的說法，填滿因數應該少用。
  5. 卸除現存的：用來指定索引應被刪除後重建。
2. 當資料表中有設為 UNIQUE 的欄位時，則 SQL Server 會用此欄位自動建立一個非叢集索引的唯一索引。
3. SQL
  1. 語法：
```
      CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]
      INDEX index_name ON table (column [, ... n])
      
```
  2. 範例：
```
      create nonclustered index IDX_bookname on books(bookname)
      
```
顯示索引資訊：
1. 語法：sp_helpindex table_name
2. 範例：sp_helpindex books
刪除索引：
1. 語法：DROP INDEX table_name.index_name
2. 範例：DROP INDEX books.IDX_bookname

預儲程序（Stored Procedures）

預儲程序是一種可以寫在資料庫伺服器端的 SQL 程序，可以在伺服端呼叫，也可以在客戶端呼叫。（曾 7-99）

流程控制敘述：

if ... else:

    if not exists (select * from bookstores where rank = 35)
      print '目前無 rank 為 35 的書局'

begin ... end:

    if exists (select * from bookstores where rank=30)
    begin
      print 'record found'
      select no, name, city from bookstores where rank=30
    end

while:

    /* 變數的宣告是將變數名稱前加上 @ */
    /* 宣告 x 為 int, x 為區域變數 */
    /* 若 x 前加入兩個 @，則 x 為全域變數 */
    declare @x int

    /* 設定 x 的初始值為 1 */
    select @x = 1

    while @x < 6
    begin
      /* + 號為將字串聯起來 */
      /* str() 是將整數轉換為字串 */
      print '書局編號為 ' + str(@x,3) + ' 的訂單資料有'
      select * from orders where no = @x

      /* 故意空出兩行 */
      print ' '
      print ' '

      /* 將 x 加 1 */
      select @x=@x+1
    end

break:

    declare @x int
    select @x = 1

    while @x < 6
    begin
      select * from orders where no = @x
      print ' '
      print ' '

      /* 當 x 為 3 時，跳出迴圈 */
      if @x = 3
        break

      select @x=@x+1
    end

continue:

    declare @x int
    select @x = 1

    while @x < 6
    begin

      /* 當 x 為 1 時，直接進入下一個迴圈 */
      if @x = 1
      begin
        select @x = @x + 1
        continue
      end

      select * from orders where no = @x
      print ' '
      print ' '
      select @x=@x+1
    end

goto:

    declare @x int
    select @x = 1
    select @x = @x + 1
    goto finish
    select @x = @x * 2
  
    finish:
    print @x

case:

    /* 值為相同時 */
    select '名稱'  = name, '排名' =
      case rank
        when 10 then '劣'
        when 20 then '良'
        when 30 then '優'
        else '特優'
      end
    from bookstores

    /* 值為某一區域時 */
    select '名稱'  = name, '排名' =
      case 
        when rank < 20 then '劣'
        when rank = 20 then '良'
        when rank > 20 then '優'
      end
    from bookstores

全域變數：請參考（林 50--51）

全域變數	說明
@@connections	以登入或試著登入系統的總數
@@error	系統最近的錯誤代號，0 為成功（比較重要的錯誤訊息可參見林 53）
@@servername	伺服器的名稱
@@version	SQL Server 的時間與版本

新增基本預儲程序：

語法：

    CREATE PROCEDURE procedure_name 
    AS sql_statements

範例：

    create procedure all_orders
    as select * from orders

    all_orders

程序的參數：

    create procedure all_orders_1 (@n1 int, @n2 int) as
    select * from orders where no=@n1 and id=@n2

    all_orders_1 1, 1

回傳整數結果： RETURN 僅能回傳整數結果。

    create procedure all_orders_2 (@n1 int, @n2 int) as
    declare @c int
    select @c = count(*) from orders where no=@n1 and id=@n2
    return @c

    declare @count int
    exec @count = all_orders_2 1, 1
    print @count

回傳字串結果：

    create procedure all_orders_3 (@n1 int, @n varchar(20) output) as
    select @n = name from bookstores where no=@n1
    return

    declare @n varchar(20)
    exec all_orders_3 1, @n output
    print @n

回傳多個結果：

    create procedure all_orders_4 (@n1 int, @n varchar(20) output, @c varchar(20) output) as
    select @n = name, @c = city from bookstores where no=@n1
    return 

    declare @n varchar(20)
    declare @c varchar(20)
    exec all_orders_4 1, @n output, @c output
    print @n
    print @c

觸發程序（Triggers）

觸發程序可以看成是一種特殊的預儲程序，不過與預儲程序不同的地方是：預儲程序是被動的，它需要被呼叫時才會執行，而觸發程序卻是主動的，當某些預定的條件為真時（即事件產生時），它會主動起來執行。（曾 7-104）

觸發程序與規則以及預設的不同？
SQL Server 會在將資訊寫入資料庫以前，套用系統中所定義的規則和預設。但是觸發程序卻是在資料更動之後才去觸發 Triggers，因此觸發程序可視為維持資料一致性的最後一道防線。

建立觸發程序：當你建立一觸發時，你必須是資料庫的擁有人。

語法：

    CREATE TRIGGER trigger_name
    ON table_name
    FOR {INSERT | UPDATE | DELETE}
    [WITH ENCRYPTION]
    AS sql_statements

範例：廠商所下的訂單數量不得高於 50

    /* 建立觸發程序 orders_insert_note */
    create trigger orders_insert_note
    on orders
    for insert, update
    as
    declare @q int
    select @q = quantity from orders where quantity > 50
    if @q > 50
    begin
      rollback tran

      /* 產生錯誤訊息：
         第一個參數是訊息，第二個參數是嚴重等級，一般會設在 11 至 16 間，
         第三個參數是狀態（state） */
      /* 訊息錯誤碼以及其敘述可以從 sysmessages 中得知（可以執行
         select * from master.dbo.sysmessages） */
      raiserror('The quantity of orders must be less than 50 units',
                 16, 10)
    end

    /* 嘗試執行下列指令，剛剛建立的觸發程序會被自動執行 */
    insert into orders values(3,3,55)

範例：若訂單數量更改，將發 email 通知

    create trigger update_quantity_note
    on orders
    for update
    as

    /* 當欄位 quantity 有任何更動 */
    if update(quantity) and (@@rowcount = 1)
    begin
      declare @mesg varchar(50)
      select @mesg = 'You have updated your orders'

      /* 經由 email 將通知發出去 */
      exec master.dbo.xp_sendmail @recipients = 'jllu@nchu.edu.tw',
                                  @message = @mesg
    end

啟動 SQL Mail：首先請你在你的電腦上安裝 MAPI Enabled 的電子郵件用戶端程式（如 Exchange Client, Mail Client, or Outlook 等，不過要注意的是 Outlook Express 不是 MAPI Enabled 的用戶端程式）
1. 展開伺服器
2. 展開「支援服務」並於「SQL Mail」上按右鍵
3. 於「內容」內給予一 Profile 名稱（如 ec1）
4. 在「SQL Mail」上按右鍵並選擇「Start」

範例：通知取消的訂單

    create trigger delet_quantity_note
    on orders
    for delete
    as
      declare @mesg varchar(50)
      declare @q int

      /* DELETED 內包含剛剛被刪除和更改的資料 */
      /* INSERTED 內含剛剛新增的資料 */
      select @q = D.quantity from DELETED D
      select @mesg = 'You have deleted your orders of ' + str(@q)
                     + ' units'

      exec master.dbo.xp_sendmail @recipients = 'jllu@nchu.edu.tw',
                                  @message = @mesg

練習：請更改以上的範例，使得使用者於更改訂單數量後，會收到更改（含更新前、後的數量）的通知。

顯示觸發資訊：
1. sp_help trigger_name
2. Enterprise Manager
  1. 選取你要處理的表格
  2. 按右鍵並選取「所有工作 / 管理觸發程序」

交易與鎖定

交易的特性在於交易只能全部完成或全部沒完成。在一般資訊系統，需要確定是否交易完成的往往是涉及到兩個以上的更動。例如，如果我們要將 bookstores 中 no 為 1 的資料去除，我們一定要確認 orders 內的所有 no 為 1 的資料全部被刪除，而且 bookstores 中 no 為 1 的資料被刪除。兩者缺一不可。為達成以上的目標，SQL Server 提供了一些指令。

BEGIN TRAN;
DELETE FROM orders WHERE no = 1;
DELETE FROM bookstores WHERE no = 1;
SELECT * FROM orders;
SELECT * FROM bookstores;

由以上的範例中，我們可以了解 no 為 1 的資料已經刪除了，可是交易尚未完成。上述交易不會完成直到「COMMIT TRAN」執行完畢。這個時候我們如果後悔，我們可以使用「ROLLBACK TRAN」來恢復成尚未執行交易前的狀態。另外，我們也可以利用 TRAN 加上 T-SQL 的語法來完成平行調撥的手續。在這個範例中，我們假設 ORDERS 的 QUANTITY 不得小於零並且已經在資料庫設定該限制。假設我們將書店標號 1 對第五本書的訂單數量改給編號為 4 的書店。這個 TRAN 會被設計成如下的指令檔

BEGIN TRAN;
UPDATE orders
SET quantity = quantity + 10
WHERE no = 4 AND id = 5;

IF @@ERROR > 0 AND @@ROWCOUNT <> 1
  GOTO NeedRollBack;

UPDATE orders
SET quantity = quantity - 10
WHERE no = 1 AND id = 5;

NeedRollBack:
IF @@ERROR > 0 AND @@ROWCOUNT <> 1
  ROLLBACK TRAN
ELSE
  COMMIT TRAN;

SELECT * FROM orders WHERE id = 5;

由於書店 1 的訂購數量為 10，因此更改完了以後將會使數量等於零，這並不符合原先訂定的限制。所以，第二個 UPDATE 將出現 ERROR，整個 TRAN 也會被 ROLLBACK。由上述的兩個範例當中，我們知道在交易進行當中，常常會發生兩個以上的使用者需要更改同一筆記錄（如飛機訂位系統），這不但會造成資料的不一致，也會造成商業糾紛。為解決此一問題，資料庫管理系統都會提供鎖定（locking）的功能。一般來說，一個典型的更新交易過程是先從資料庫讀取資料，再對這些資料進行『分享鎖定』，然後進入『排它鎖定』狀態來進行資料的更動。 SQL Server 提供各式的鎖訂方式 --- 從 row level 的鎖定、page level 的鎖定、到整個表格的鎖定。若資料庫管理者或系統開發人員不自行設定鎖定的等級，SQL Server 會自動幫你決定較佳的鎖定方式。

隔離等級（isolation level）
隔離等級主要是用來設定交易在『讀取』資料時的隔離狀態（在修改資料時則一定要做完整鎖定，因此不必設等級）。SQL Server 提供的主要隔離等級有：
1. READ UNCOMMITTED 等級：這是最低的隔離等級，它只能確保實際上已經毀損的資料（physically corrupt data）不會被讀取。不建議使用！執行兩個 sessions，由於在第一個 session 中，表格 orders 已經含有未確認的資料，而且第二個 session 的隔離等級設定為 READ UNCOMMITTED，所以已經更改但是還沒有 commit 的資料也可以讀的到。
  1. session 1:
```
        begin tran
        update orders set quantity=30 where no=4 and id=5
        
```
  2. session 2:
```
        set transaction isolation level read uncommitted

        /* 你可以看到沒 commit 但是已經 update 的資料 */
        select * from orders
        
```
2. READ COMMITTED 等級：這是 SQL Server 預設的等級，在 READ COMMITTED 等級，SQL Server 不允許你存取「dirty」或未確認的（uncommitted）資料。你可以更進一步了解 READ COMMITTED 的運作方式：
  1. 執行兩個 sessions，由於在第一個 session 中，表格 orders 已經含有未確認的資料，因此第二個 session 無法查詢而停在那裡。直到第一個 session rollback 或 commit 後，第二個 session 才會開始執行。
    1. session 1:
      begin tran update orders set quantity=30 where no=4 and id=5
    2. session 2:
      select * from orders
  2. 執行兩個 sessions，第一個 session 對 orders 進行 row 鎖定且更動資料，第二個 session 若希望對未被鎖定的資料進行查詢，它必須下達 readpast 的 locking hint。
    1. session 1:
      begin tran update orders with (rowlock) set quantity=30 where no=4 and id=5
    2. session 2:
      /* 只有非 dirty 的資料顯示出來 */ /* readpast: 略過鎖定的資料列。readpast 提示僅套用於在 READ COMMITTED 隔離等級下運作的交易。僅套用於 SELECT 陳述式。 */ select * from orders with (readpast) /* 依舊無法查詢 */ select * from orders
3. REPEATABLE READ 等級： SQL Server 允許你重複的存取同一筆資料，並保證每一次所讀到的內容都相同。在這個設定下，其他交易不可以對正被讀取的資料進行更改或刪除，但是別人仍然可以新增記錄。
  1. session 1:
```
      /* 保證已經讀取的資料不被更改 */
      set transaction isolation level repeatable read
      begin tran
      select * from orders
      
```
  2. session 2:
```
      /* 預設的 isolation level 為 read committed */
      begin tran

      /* 無法 update */
      update orders set quantity=quantity+5 where no=4 and id=5

      /* 但是可以 insert */
      insert into orders values(5,5,20)
      
```
4. SERIALIZABLE 等級：這是 SQL Server 提供的最高隔離等級，所有的交易全部互相隔離。
  1. 你可以重複之前的範例，只是在 session 1 設定的等級為 serializable
  2. 在 session 2，現在便無法 update and insert。
注意，隔離等級設的越高，對於同時性（concurrency）的負面影響越大。至於需不需要每一筆交易都設定為 SERIALIZABLE 等級，這必須考量系統的需求。例如，一本書的 editor 為書本的每一章邀請作者寫作，每一個作者對這本書的更動僅及於他負責的那一章，因此對這本書的所有交易不必設過高的隔離等級。
（從 online books 的內容） Transactions must be run at an isolation level of repeatable read or higher to prevent lost updates that can occur when two transactions each retrieve the same row, and then later update the row based on the originally retrieved values. If the two transactions update rows using a single UPDATE statement and do not base the update on the previously retrieved values, lost updates cannot occur at the default isolation level of read committed.

設定隔離等級：這個設定會對這一次的連線（current connection）產生效力。設定後，並不會對其他的連線造成影響。

  set transaction isolation level (read uncommitted |
                                   read committed   |
                                   repeatable read  |
                                   serializable)

雖然 SQL Server 會動態的幫你選擇較佳的鎖定方式，不過瞭解鎖定的內涵也是有幫助的。鎖定的意義在於使用者在更改某一筆記錄前先進行鎖定，那麼其他使用者便無法對同一筆記錄作更改，不過需要注意可能會形成死結（deadlock）。 SQL Server 鎖定的主要種類：
1. 共享鎖定（shared lock）：共享鎖定允許多個交易同時讀取（select）某一物件。當某一物件被一個或一個以上的交易以共享鎖定的方式鎖定時，其他的交易無法對這一個物件進行修改。當一個交易讀取完某一物件，其共享鎖定的狀態會被立即釋放除非交易的隔離等級被設定為 REPEATABLE READ 或更高、或者該交易所使用的 locking hint 要求要保留該共享鎖定。當某資料被分享鎖定後，其他交易不得對他做排他鎖定。
2. 排它鎖定（exclusive lock）：排它鎖定不允許其他交易對被鎖定的物件進行任何活動（含 select、update）。
3. 更新鎖定（update lock）：更新鎖定是介於共享鎖定與排它鎖定之間的鎖定等級。當一個交易設定某一物件為更新鎖定時，其他交易無法取得這一物件的更新鎖定；而當這個交易對這個物件進行更動時，這物件的鎖定會自動變成排它鎖定；至於其他操作，則為共享鎖定，這樣可以避免死結。因此，某資料有可能同時被更新鎖定以及共享鎖定，只是在這資料被更新的時候，不能有其他共享鎖定加諸於他。
  1. session 1:
```
         /* REPEATABLEREAD 類似於共享鎖定 */
         begin tran
         select * from orders with (REPEATABLEREAD)
         
```
  2. session 2:
```
         begin tran
         select * from orders with (REPEATABLEREAD)
         
```
  3. 在 session 2 先 ROLLBACK，然後
```
         /* XLOCK 為除他鎖定，結果將是無法鎖定而等待 */
         begin tran
         select * from orders with (XLOCK)
         
```
  4. 在 session 2 先停止 XLOCK，然後
```
         begin tran
         select * from orders with (UPDLOCK)
         
```
明顯的（explicit）取得一鎖定
利用定義在 SELECT、INSERT、 UPDATE、DELETE 的 locking hints。locking hints 的設定會 override 目前的隔離等級設定。注意，依照微軟的文件說明 SQL Server 查詢最佳化器 (Query Optimizer) 會自動做出決定。建議您只有在必要時才使用資料表層級的鎖定提示來變更預設的鎖定行為。不允許某些鎖定層級可能會嚴重影響 concurrency。 SELECT ...... WITH (locking hints)
1. NOLOCK：不發出分享鎖定的要求，也不承諾（honor）排它鎖定（exclusive lock）
2. HOLDLOCK：保有一個共享鎖定（？？好像文件有錯，請看範例）直到一個交易完成，而非在查詢完後便立即釋放鎖定。請試一試這個範例：
  1. session 1:
```
         begin tran
         select * from orders with (HOLDLOCK)
         
```
  2. session 2:
```
         /* 猜一下，sql 2000 對 select 所下的 implicit lock 為何？ */
         select * from orders

         /* 這個的 implicit lock 又為何？ */
         insert into orders values(2,3,40)
         
```
    可以讀取 orders 的內容，但是由於資料已經被鎖定，因此無法進行新增，必須等到交易結束後，新增的指令才會被執行。你可以執行 ROLLBACK TRAN 或 COMMIT TRAN 來結束交易。因為無法新增，可以推測 isolation level 為 serializable。
3. REPEATBLEREAD：保證相當於 REPEATBLE READ 的隔離等級直到一個交易完成。
  1. session 1:
```
         begin tran
         select * from orders with (REPEATABLEREAD)
         
```
  2. session 2:
```
         /* select OK */
         select * from orders

         /* insert OK */
         insert into orders values(2,3,40)

         /* cannot update */
         update orders set quantity=20 where no=2 and id=1
         
```
4. ROWLOCK：鎖定一個 row 而非整個表格或分頁。
  1. session 1:
```
         begin tran

         select * from orders with (ROWLOCK,REPEATABLEREAD)
                  where no=1 and id=1
         
```
  2. session 2:
```
         update orders set quantity=40 where no=1 and id=1
         
```
    由於該 row 已經被鎖定，因此無法進行更新，必須等到交易結束後，更新的指令才會被執行。但是由於其他 row 的資料並未被鎖定，因此下列指令可以照常進行
```
         update orders set quantity=40 where no=1 and id=2
         insert into orders values(2,3,40)
         delete from orders where no=2 and id=3
         
```
5. 有關其他 lockinig hints；如 PAGLOCK（分頁鎖定）、TABLOCK（表格鎖定）等，請參考 online books。
練習題：請寫出一個 stored procedure 並用這個 stored procedure 來訂機票。假設機票只剩下一張，而由兩個同學同時執行這個 stored procedure，成功的人，顯示取得機票，否則就是失敗。答案可以從之前的範例做出來，或者可以看.....
顯示鎖定資訊
1. sp_lock
2. Enterprise Manager
  1. 展開資料庫伺服器
  2. 展開「管理 / 目前活動」

結束鎖定：

於顯示的鎖定程序（Process Info 內）上按右鍵，並選擇「Kill Process」來結束鎖定。

KILL spid

  /* 先利用之前所說明的鎖定進行一 select 鎖定 */
  /* 找出有哪些鎖定 */
  sp_lock

  /* 看一下心目中的程序是哪一個？ */
  /* 本例題中的 549576996 是由 sp_lock 找出的 */
  select object_name(549576996)

  /* 由於 549576996 的 spid 是 8 */
  kill 8

使用者權限管理

使用者的基本觀念：
SQL Server 上的使用者分為兩類：第一種使用者是登入（Login），登入是具有連上 SQL Server 的能力；至於登入後，可以存取哪些資料庫、哪些資料庫表格等，則由使用者（User）的權限（permission）來規範。由於一個使用者可能可以存取多項資料庫的資源，而且可能有多個使用者具有相同的使用權限，所以 SQL Server 提供了角色（role）的做法，使得具有相同的權限的使用者可以擁有相同的角色（類似群組 -- group -- 的觀念），以便於管理。角色又分為伺服器角色（server role）與資料庫角色（database role）；資料庫角色又分為資料庫角色（database role）與應用程式資料庫角色（application database role）。
1. 伺服器角色：是一群由 SQL Server 已經定義好的角色，供資料庫管理員使用。資料庫管理員不能刪除或更改伺服器角色。想了解有哪些伺服器角色，展開資料庫伺服器（如 imsql），然後展開「安全性 / 伺服器角色」。
2. 資料庫角色
  1. 資料庫角色：最常見的角色就是 PUBLIC 這個角色，它是由 SQL Server 自動產生的，而且每一個新增的使用者都屬於這個角色。你也可以自己新增一個角色經由展開資料庫（如 Bob），並選擇「角色」來新增。你可以於新增完該角色後，進行權限的設定。設定的方式是選擇該角色，並按右鍵選取「內容 / 權限」來進行。
  2. 應用程式資料庫角色：它是由應用程式所啟動的。在預設的情形下，應用程式資料庫角色不會被啟動也不會有任何的使用者。當一程式使用某一應用程式資料庫角色登入時，所有身分認證工作都會被忽略掉，而改用應用程式資料庫角色的密碼來確認身分。
範例：新增兩個使用者，一個為 bob，其權限為可以在資料庫 Bob 內產生、修改表格等動作，而另一個使用者為 guest，guest 只能在資料庫 Bob 中瀏覽。
新增登入：產生 bob 與 guest
1. 利用 Enterprise Manager
  1. 展開資料庫伺服器
  2. 展開「安全性」，並選擇「登入」
  3. 按右鍵並選擇「新增登入」
2. 利用 Stored Procedure
```
    sp_addlogin login_id [, password [, defaultdb]]
    
```
新增資料庫角色：產生 developer 與 browse，並使得 bob 與其他開發人員具有 developer 角色，且使得 guest 具有 browse 的角色。
1. 利用 Enterprise Manager
  1. 展開資料庫（如 Bob）
  2. 選擇「角色」並按右鍵來選擇「新增資料庫角色」來產生新角色。
  3. 於新增完 developer 與 browse 後，在其上分別按右鍵並選擇「內容」以進行權限的設定。在「資料庫角色屬性」的對話視窗內，選擇「權限」來進行設定。
  4. 將 browse 設定為可以 select 後，利用 isql 登入，並進行查詢。並試著 insert 一筆記錄到 orders 內，你應該會得到錯誤訊息。
  5. 將 developer 設定為可以 select、insert、update、delete，利用 isql 登入，並進行查詢。並試著 insert 與 delete 一筆記錄到 orders 內。
2. 利用 Stored Procedure：先 use Bob 後
  
  語法範例
  
  sp_adduser login_id sp_adduser 'bob'
  
  sp_addrole role sp_addrole 'developer'
  
  sp_addrolemember role, account sp_addrolemember 'developer','bob'
3. 除了 select、insert、update、delete 外，還有哪些權限？
  1. EXEC：可以讓你執行預儲程序
  2. DRI/REFERENCES (Declared Referential Integrity)：可以讓你為一表格加上外來鍵的限制
  3. ALL：代表所有權限，需要 sysadmin 或者 db_owner 的成員才能設定。
  4. SQL Server 2000 也支援 data-item security，也就是說你可以選擇「資料行」來對某些特定的欄位做 select 以及 update 的權限設定。

權限的授與與取消
可以使用 Enterprise Manager 來授與與取消某些角色或使用者的權限，也可以利用 GRANT 與 REVOKE 的 Transact-SQL 的指令來達成同樣的目標。

語法	範例
GRANT permission_list [fieldname1, fieldname2, ...] ON object_name TO name_list	// 將資料表 orders 內欄位 id 的 select 權力授權給 guest GRANT select (id) ON orders TO guest
	GRANT select,delete,update,insert ON orders TO bob
REVOKE permission_list ON object_name FROM name_list	REVOKE select ON orders FROM guest
REVOKE permission_list ON object_name FROM name_list	REVOKE update,delete ON orders FROM bob

Written by: 國立中興大學資管系呂瑞麟 Eric Jui-Lin Lu

XML Technology Research Lab

2012年9月26日星期三

Microsoft SQL Server 2000 簡介

Microsoft SQL Server 2000 簡介

目錄

組成單元

新增功能

安裝

建立磁碟鏡像

建立資料庫

建立表格

表格查詢

索引

預儲程序（Stored Procedures）

觸發程序（Triggers）

交易與鎖定

使用者權限管理

1 則留言:

語法	範例
sp_adduser login_id	sp_adduser 'bob'
sp_addrole role	sp_addrole 'developer'
sp_addrolemember role, account	sp_addrolemember 'developer','bob'

2012年9月26日 星期三

Microsoft SQL Server 2000 簡介

Microsoft SQL Server 2000 簡介

目錄

組成單元

新增功能

安裝

建立磁碟鏡像

建立資料庫

建立表格

表格查詢

索引

預儲程序（Stored Procedures）

觸發程序（Triggers）

交易與鎖定

使用者權限管理

1 則留言:

2012年9月26日星期三