13

13. T-SQL 與 SQL Query Analyzer

什麼是SQL？

什麼是 T-SQL？

介紹 T-SQL 的新特色

如何使用 T-SQL

本章總結

在本章中我們將介紹結構化查詢語言〈Structured Query Language，SQL〉與Transact-SQL〈T-SQL〉一些基本的觀念以及這兩種語言之間的差異。本章解釋了資料定義語言〈DDL〉與 資料操作語言 ，並且包含了範例。我們也將簡單介紹 Microsoft SQL Server 2000 中 T-SQL的新功能。您將學習如何使用不同的 SQL Server 公用程式來建立和管理資料庫物件，包括命令列 T-SQL 和 SQL Query Analyzer。您也可以學到建立 T-SQL 指令碼的相關知識。

什麼是SQL？

SQL 是一種資料庫查詢和程式設計語言，用於存取資料以及查詢、更新和管理關聯式資料庫系統。美國國家標準局（ANSI）與國際標準化組織（ISO）已經制定了 SQL 標準。ANSI 是一個美國工業和商業集團組織，發展美國的商務和通訊標準。ANSI 同時也是 ISO 和 International Electrotechnical Commission（IEC）的成員之一。ANSI 發佈與國際標準組織相應的美國標準。1992年，ISO 和 IEC 發佈了 SQL 的國際標準，稱為 SQL-92。ANSI 隨之發佈的相應標準是 ANSI SQL-92。ANSI SQL-92 有時被稱為 ANSI SQL。儘管不同的關聯式資料庫使用的 SQL 版本有一些差異，但大多數都遵循 ANSI SQL 標準。SQL Server 使用 ANSI SQL-92 的擴展集，稱為 T-SQL，其遵循 ANSI 制定的 SQL-92 標準。

SQL 語言包括兩種主要程式設計語言類別的陳述式： 資料定義語言 （DDL）與 資料操作語言 （DML）。下面我們將介紹這兩類語言。

DDL

DDL 用於定義和管理物件，例如資料庫、資料表以及檢視表（

第18章 將會解釋何謂檢視表）。DDL 陳述式通常包括每個物件的CREATE、ALTER 以及 DROP 命令。舉例來說，CREATE TABLE、ALTER TABLE 以及 DROP TABLE 這些陳述式便可以用來建立新資料表、修改其屬性（如新增或刪除資料行）、刪除資料表等，下面我們會一一介紹。

CREATE TABLE 陳述式

使用 DDL 在 MyDB 資料庫建立一個名為 Customer_Data 的範例資料表，本章後面的例子我們會使用到這個資料表。如前所述，CREATE TABLE 陳述式可以用來建立資料表。這個範例資料表被定義成四個資料行，如下所示：

    Use MyDB
    CREATE TABLE Customer_Data
    (customer_id smallint,
    first_name char(20),
    last_name char(20),
    phone char(10))
    GO

這個陳述式能產生 Customer_Data 資料表，這個資料表會一直是空的直到資料被填入資料表內。關於建立資料庫資料表的細節，請參閱 第10章 。

ALTER TABLE 陳述式

ALTER TABLE 陳述式用來變更資料表的定義與屬性。在下面的例子中，我們利用 ALTER TABLE 在已經存在的 Customer_Data 資料表中新增 middle_initial 資料行。

    ALTER TABLE Customer_Data
    ADD middle_initial char(1)
    GO

現在資料表的定義包括了五個資料行，而不是之前的四個資料行。關於使用ALTER TABLE 的更多細節，請參閱 第15章 。

DROP TABLE 陳述式

DROP TABLE 陳述式用來刪除資料表定義以及所有的資料、索引、觸發程序、條件約束以及資料表的權限。要刪除我們的 Customer_Data 資料表，可利用下列命令：

    DROP TABLE Customer_Data
    GO

關於 DROP TABLE 陳述式的詳細內容，請參閱 第15章 。

DML

DML 利用 INSERT、SELECT、UPDATE 及 DELETE 等陳述式來操作資料庫物件所包含的資料。

INSERT 陳述式

INSERT 陳述式用來在資料表或檢視表中插入一列資料。例如，如果要在Customer_Data 資料表中新增一個客戶，可使用類似以下的 INSERT 陳述式：

    INSERT INTO Customer_Data
    (customer_id, first_name, last_name, phone)
    VALUES (777, "Frankie", "Stein", "4895873900")

請注意 SQL 陳述式中第二行的資料行名稱清單，清單上資料行名稱的次序決定了資料數值將被放在哪個資料行。舉例來說，第一個資料數值將被放在清單列出的第一個資料行 customer_id、第二個資料數值放在第二個資料行，依此類推。由於我們在建立資料表時，定義資料資料行填入數值的次序與現在相同，因此我們不必特意指定欄位名稱。我們可以用以下的 INSERT 陳述式代替：

    INSERT INTO Customer_Data
    VALUES (777, "Frankie", "Stein", "4895873900")

注意

如果使用這種形式的 INSERT 陳述式，但被插入的數值次序上與建立資料表時不同，數值將被放入錯誤的資料行。如果資料的型別與定義不符，則會收到一個錯誤訊息。

SELECT 陳述式

SELECT 陳述式用來檢索資料表中的資料，而哪些資料被檢索由列出的資料行與陳述式中的 WHERE 子句決定。例如，要從之前建立的 Customer_Data 資料表中檢索 customer_id 以及 first_name 資料行的資料，並且只想取出每列中 first_name 資料行值為 Frankie 的資料，那麼可以利用以下的 SELECT 陳述式：

    SELECT customer_id, first_name FROM Customer_Data
    WHERE first_name = "Frankie"

如果有一列符合 SELECT 陳述式中的標準，則結果將顯示如下：

    customer_id     first_name
    -------------    ------------
    777              Frankie

UPDATE 陳述式

UPDATE 陳述式用來更新或改變一列或多列中的值。例如，一位名稱為 Frankie Stein 的客戶想要在記錄中改變他的姓氏為 Franklin，可使用以下 UPDATE 陳述式：

    UPDATE Customer_Data
    SET first_name = "Franklin"
    WHERE last_name = "Stein" and customer_id= 777

我們在 WHERE 子句中加入 customer_id 的項目來確定其他名稱為 Stein 的客戶不會被影響－只有customer_id為777的客戶，姓氏會有所改變。

說明

當您使用 UPDATE 陳述式時，要確定在 WHERE 子句提供充份的篩選條件，如此才不會不經意地改變了一些不該改變的資料。

DELETE 陳述式

DELETE 陳述式用來刪除資料表中一列或多列的資料，您也可以刪除資料表中的所有資料列。要從 Customer_Data 資料表中刪除所有的列，您可以利用下列陳述式：

    DELETE FROM Customer_Data
    或
    DELETE Customer_Data

資料表名稱前的 FROM 關鍵字在 DELETE 陳述式中是選擇性的。除此之外，這兩個陳述式完全相同。

要從 Customer_Data 資料表中刪除 customer_id 資料行的值小於100的列，可利用下列陳述式：

    DELETE FROM Customer_Data
    WHERE customer_id < 100

現在我們已經快速瀏覽了 SQL 提供的 DDL 與 DML 陳述式，接著，下面將介紹 T-SQL。

什麼是 T-SQL？

T-SQL 是標準 SQL 程式設計語言的增強版，它是用來讓應用程式與 SQL Server 溝通的主要語言。T-SQL 提供標準 SQL的DDL 和 DML 功能，加上延伸的函數、系統預存程序以及程式設計結構(例如 IF 和 WHILE)讓程式設計更有彈性。T-SQL 的功能隨著新版的 SQL Server 而持續成長，讓我們來看一下 T-SQL 最新增加的特色。

介紹 T-SQL 的新特色

在 Microsoft SQL Server 7.0 中 T-SQL 有著為數不少的新增功能，包括新的預存程序、系統資料表、函數、資料型別、陳述式以及現存陳述式中的選項。這些都被保留在 SQL Server 2000 中，因此我們在這裡要先回顧 (特別是在您還不很熟悉 SQL Server 7.0 中 T-SQL 的功能，這個溫習就很重要了)。因為有太多的新功能需要討論，在此我們僅為每個類別舉少數例子來加以介紹。

相關資訊

要找尋這些功能的完整清單，可以參考 SQL Server《線上叢書》中 「Transact-SQL新增與加強的功能」 主題。

系統預存程序

系統預存程序是 SQL Server 提供來執行管理與其他工作，包括了系統資料表更新以及從系統資料表中檢索資料等。系統預存程序是和 SQL Server 一起安裝，名稱以 sp_ (系統預存程序) 或 xp_ (延伸的預存程序)開頭。這些預存程序儲存在 master 資料庫中，權限歸系統管理者所有。不過，許多程序可以從使用者自訂的資料庫執行，用來在特定資料庫中從系統資料表檢索資訊。當您執行一個系統預存程序，它是在目前資料庫的系統資料表中展開作業。

相關資訊

關於這類型程序的更多相關資訊，請參考《線上叢書》中 「延伸預存程序」 主題。

在 SQL Server 7 中新增了許多系統預存程序，如今在 SQL Server 2000 中同樣可以利用。表13-1列出了一些可能對您有所幫助的系統預存程序。

表13-1 Procedures Introduced....

系統預存程序 | 描述
--- | ---
sp_cycle_errorlog | 關閉目前的錯誤日誌檔案，重新命名為errorlog.1(以及，如果需要的話，將舊的errorlog.1重新命名為errorlog.2，依此類推)，以及開始一個新的錯誤日誌檔案。
sp_helpfile | 傳回與目前資料庫相關之檔案的名稱與屬性。
sp_helpfilegroup | 傳回與目前資料庫相關之檔案群組的名稱與屬性。
sp_helprole | 傳回與目前資料庫相關中角色的資訊。
sp_help_alert | 報告伺服器定義的警示資訊。
sp_start_job | 指示SQL Server代理程式開始執行一個工作。

並不是只有這些預存程序提供立即的資訊，不過它們在使用您的使用者資料庫時會相當有用。例如，當執行 T-SQL 指令碼並將輸出結果儲存到一個檔案時，提供使用者資訊的程序就會相當有幫助。您可以將 sp_helpfile、sp_helpfilegroup以及 sp_helpdb 的執行結果儲存在特定的資料庫中，畢竟當您需要重建資料庫時有必要了解檔案、檔案群組、資料庫選項在起初建立時的原始設定。在 SQL Server 7.0 新增並保留 SQL Server 2000 中的系統預存程序清單，可以在《線上叢書》「Transact-SQL新增與加強的功能」這個主題裡找到。

系統資料表

系統資料表用來儲存 SQL Server 中所有資料庫的設定資訊，以及物件、使用者和使用者存取權限的定義。每個使用者資料庫有各自的系統資料表，其中保存資料庫的資訊。只有在 master 資料庫的系統資料表才握有伺服器層級的設定資訊。您應該利用系統預存程序來存取系統資料表而不是直接存取系統資料表。關於新的 SQL Server 7.0 新增的系統資料表清單可在《線上叢書》裡的「Transact-SQL新增與加強的功能」這個主題裡找到。底下列出一些有趣的新系統資料表：

- backupfile這個資料表存放在 msdb 資料庫中。它記錄了所有記錄檔備份或資料庫檔案備份的相關資訊。這些資訊包括檔案識別碼、檔案歸屬的檔案群組，以及檔案存在的實體磁碟或資料分割的名稱。
- restorehistory這個資料表存放在 msdb 資料庫中。它包含對應於各還原作業相關資訊的資料列，不論是檔案的還原或是資料庫的還原。這些資訊包括還原的日期與時間、目的資料庫、資料回復的時間點以及還原的類型。
- sysfiles此資料表為一虛擬資料表，亦即無法直接更新或修改。它包含了每個資料庫檔案的相關資訊，諸如實體裝置及邏輯的檔案名稱、檔案大小及最大檔案大小以及資料庫增長大小（如果資料庫有所增長的話）。

注意

請永遠使用系統預存程序來存取系統資料表，系統預存程序提供一層隔離以確保您不會更動到不可更改的資料。如果您以手動方式存取系統資料表，可能會有變更重要系統資訊的風險。

函數

SQL Server 內建的函數提供了一個快速、簡單的方法來完成某些特定的工作。SQL Server 7.0 新增了幾個有用的函數，並且也包含在 SQL Server 2000 中。了解哪些函數是可用的，SQL Server 應用程式的設計工作可以變得稍為簡單。在《線上叢書》中的「Transact-SQL 新增與加強的功能」這個主題可以找到新增函數的完整清單。此處僅介紹一些對您可能很有幫助的函數：

- NEWID 建立一個 uniqueidentifier 資料型別的 全域唯一識別項 （GUID）。您需要使用這個函數將值分派給該資料型別的資料行，使用方式為 NEWID() 。（此函數不需引數）
- YEAR 傳回指定日期年份部分的完整整數。使用方式為 YEAR(date) 。例如：以下陳述式 SELECT YEAR('07/11/01') 將會傳回值 2001。
- MONTH 傳回指定日期月份部分的整數。使用方式為 MONTH(date) 。例如：以下陳述式 SELECT MONTH('07/11/01') 將會傳回值7。
- DAY 傳回指定日期日期部分的整數。使用方式為 DAY(date) 。例如：以下陳述式 SELECT DAY('07/11/01') 將會傳回值 11。
- FILE_NAME 傳回檔案識別碼（ID）的邏輯檔案名稱。使用方式為 FILE_NAME (file_id_number)。例如：陳述式 SELECT FILE_NAME(4) 檔案識別碼為4的檔案邏輯名稱。如果資料庫中找不到有此識別碼的檔案，則傳回 NULL。

  資料型別

  在 SQL Server 7 中新增了幾種新的資料型別，一些已經有的資料型別的尺寸也有延伸。此外，SQL Server 2000 新增了三種資料型別，這些資料別多數在

第10章 已經討論過。此處僅列出 SQL Server 7 有改變，並且也包含在 SQL Server 2000 中的資料型別：
- 資料指標變數新增了一個 cursor 資料型別。關於指標的相關訊息，請參考《線上叢書》裡「Cursors」這個主題。
- 新增了三種新的 Unicode 資料型別－nchar、nvarchar 以及 ntext。每一個 Unicode 字元使用兩個位元組，並支援所有國際字元。
- 新增了一種 uniqueidentifier 資料型別，用於儲存通用唯一識別碼（GUID）。
- 字元資料和二進位字串的最大長度擴展到8,000位元組。這一長度適用於 char、varchar、binary 以及 varbinary 等型別。

SQL Server 2000 則新增了下列資料型別：

- bigint儲存8個位元組長度的整數。
- sql_variant允許不同資料型別的值儲存於同一個資料行。此型別的資料行儲存資料本身的值並且加以描述－它的基本型別、小數位數、精確性、最大大小以及定序（collation）。
- table運作方式與暫存資料表類似；其宣告包含資料行清單與資料型別。此資料型別可用來定義一個區域變數或是一個使用者自訂函數的傳回值。

  陳述式

  SQL Server 7.0 包括了許多新的 T-SQL 陳述式和已經存在陳述式的新選項。同樣的，這些也保留在S QL 2000 之中。這些陳述式對應 SQL Server 7.0 的一些新功能。例如，ALTER DATABASE 陳述式包含了下列一些針對檔案與檔案群組的新選項：MODIFY FILE、ADD FILEGROUP、MODIFY FILEGROUP、REMOVE FILE 以及 REMOVE FILEGROUP。特別是檔案群組，新的 DBCC CHECKFILEGROUP 陳述式會檢查檔案群組中所有資料表的分配和結構的完整性。

  SQL Server 7.0 與 SQL Server 2000 包括兩個額外的 DBCC 陳述式、DBCC SHRINKFILE 與 DBCC SHRINKDATABASE。前者能縮減資料檔案的大小，後者可縮減資料庫的所有資料檔案並釋放未使用的磁碟空間。

  SQL Server 7 與 SQL Server 2000 支援一種改進的備份和還原結構。新的BACKUP 陳述式允許全部或部份的資料庫備份和記錄檔備份。新的 RESTORE 陳述式允許使用全部或部份資料庫備份和記錄檔備份來還原。這些代替了 SQL Server 較早版本中的 DUMP 和 LOAD 陳述式。關於 SQL Server 7.0 與 SQL Server 2000 新增陳述式與選項的完整清單，請見《線上叢書》裡「Transact-SQL 新增與加強的功能」這個主題。

  如何使用 T-SQL

  除了使用 T-SQL 設計您的應用程式(這個主題已經超過本書的討論範圍)之外，您可以利用以下三個 SQL Server 的工具－ISQL、OSQL 或 Query Analyzer 其中之一來執行 T-SQL 陳述式，或是建立與執行 T-SQL 指令碼。

  ISQL 公用程式

  ISQL 公用程式與 SQL Server 之間是透過 DB-Library 來溝通，它可以讓您執行 T-SQL 陳述式、預存程序與指令碼。由於 DB-Library 保留了 SQL Server 6.5 版的功能，因此 ISQL 應用程式可能無法支援部份 SQL Server 2000 的新功能。比方說，ISQL 就不能檢索 Unicode ntext資料型別的資料。

  OSQL公用程式

  OSQL 公用程式是在 SQL Server 7.0 中新增並保留至 SQL Server 2000 的新工具，主要用來取代 ISQL。這兩個公用程式基本上並沒有很大差異，不過 OSQL 是利用 開放式資料庫連結介面 （Open Database Connectivity，ODBC）與 SQL Server 溝通，而不是 DB-Library，並且 OSQL 支援 SQL Server 2000 所有的功能。OSQL 與 ISQL 的其他功能都相類似，SQL Server 2000 對兩者也都支援，不過，您應該使用 OSQL 來取代 ISQL，以避免之前提到的那些問題。

  要從命令提示字元視窗（MS-DOS提示視窗）執行 OSQL，您可以利用下列適當的參數來執行 OSQL.EXE 程式：

      osql -U username -P password -S servername

  當 OSQL 與 SQL Server 連接後，會出現下面這個數字提示字元：

      1>

  在這一提示字元下可鍵入 T-SQL 陳述式，

      1>    sp_helpdb master
      2>    go

  這項陳述式將列出有關 master 資料庫的資訊。關鍵字 GO 並不是一個 T-SQL陳述式，它是一個ISQL、OSQL 以及 Query Analyzer 均能識別的命令，主要用來作為一段 T-SQL 陳述式的結束標記（視為批次操作）。類似這種互動式查詢的結果將顯示在命令提示字元視窗內。

  如果在 OSQL 中輸入錯誤，可以利用 RESET 這個 OSQL 命令來重回 1> 提示字元，

      1>    sp_helpbd
      2>    reset
      1>    sp_helpdb
      2>    go

  要停止 OSQL，可輸入 QUIT 或 EXIT。您也可以同時按 Ctrl+C，在不退出OSQL 公用程式的情況下結束一個命令或中斷查詢動作。

  OSQL 公用程式可識別的參數不只有 -U、-P 和 -S。關於 OSQL 公用程式可用的參數及 OSQL 的相關訊息，請參考線上指南的 osql Utility 主題。

  Query Analyzer

  Query Analyzer 可讓您在 GUI 介面中執行 T-SQL 陳述式或指令碼，並將結果輸出至視窗。您也可以利用這個工具來執行某些索引與查詢分析的工作。有些人較喜歡利用 Query Analyzer 來執行陳述式而非 MS-DOS 提示字元視窗。要執行 Query Analyzer，可依下列步驟：

  1. 利用下列三種方法之一來啟動 Query Analyzer：
     - 在命令提示字元後輸入『 isqlw 』。
     - 開啟 Enterprise Manager，在 工具 功能表選擇 SQL Query Analyzer。
     - 從 開始 / 程式集 / Microsoft SQL Server / Query Analyzer

     出現 連線到SQL Server 對話方塊（除非您已連線到伺服器），如圖13-1所示。

     圖13-1 連線到 SQL Server對話方塊

  2. 從 SQL Server 下拉式選單中選擇您要連線的伺服器名稱。輸入您的登入資訊，如果 SQL Server 目前尚未啟動而您希望它自動啟動服務，請在選項下的核取方塊打勾。按一下 確定 。SQL Query Analyzer 將會出現，如圖13-2所示。

     圖13-2 SQL Query Analyzer

  3. 在查詢視窗輸入任何 T-SQL 陳述式或呼叫預存程序，如圖13-3所示。注意查詢視窗已經放到最大，佔據了整個 SQL Query Analyzer 視窗。

     圖13-3 在 Query Analyzer 的查詢視窗中呼叫一個預存程序

  4. 要執行這個陳述式，按一下工具列上的 執行查詢 按鈕(綠色指向右的三角箭頭)或同時按Ctrl+E。結果將顯示於結果窗格內，如圖13-4所示。

     圖13-4 在 Query Analyzer 中顯示的查詢結果

  5. 要在 Query Analyzer 中載入並執行您預先建立的 T-SQL 指令碼，按一下工具列上 載入SQL指令碼 按鈕（黃色資料夾符號），或是從 檔案 / 開啟舊檔 中瀏覽並找出您的檔案。指令碼將顯示於上方的窗格。按一下 執行查詢 按鈕來執行指令碼。

     說明

     SQL Query Analyzer 尚有許多其他的選項，包括 SQL Server 2000 新增的一些功能，請參閱

  第35章 以獲得更多詳細說明。

T-SQL 指令碼

當您需要多次執行一些 T-SQL 陳述式或預存程序，建立您自己的指令碼是一個相當不錯的方法。 指令碼 （Script）是內含要執行之 T-SQL 的簡單檔案，其中可以是一條陳述式或是一系列連續的陳述式。建議您在建立 T-SQL 指令碼時，檔案副檔名均定為 .sql，如此您可以很快地辨識出它們。

下面的程式碼是一個簡單的指令碼範例。這個指令碼呼叫了幾個系統預存程序，可以輸出關於 MyDB 資料庫與其檔案、檔案群組及資料表(Customer_Data和 Product_Info)的種種資訊。我們假設這個指令碼已經預先儲存至名為 My_DB.sql 的檔案中。

    use MyDB
    go
    sp_helpdb MyDB
    go
    sp_helpfilegroup
    go
    sp_spaceused Customer_Data
    go
    sp_spaceused Product_Info
    go

要透過命令列來執行這個指令碼，您可以使用 OSQL 的 -i 選項與 -o 選項。 -i 選項後填入要輸入的指令碼檔名， -o 選項後填入執行結果打算輸出至哪個檔案的檔名－本例中，檔名為 MyDB_info.out。我們也可以使用 -e 選項讓原始 T-SQL 陳述式顯示在輸出檔案中，讓結果更為清楚。舉例而言，要以系統管理員身份執行上述指令碼，在命令提示字元後輸入下列程式碼：

    osql -Usa -P -i MyDB_info.sql -o MyDB_info.out -e

檢查輸出檔案以確定指令碼是否如您預期一般正確執行。以這種方式使用T-SQL 執行檔可以讓您將結果儲存於輸出檔案，便於稍後檢視。尤其當您在資料庫變動前與變動後，想比較指令碼執行的輸出結果時，這個方法就能符合您的期望。此外，指令碼在您需要執行一些陳述式一次以上時，也顯得很有幫助。

另一個讓您可以不用每次都要輸入一堆 OSQL 命令來執行指令碼的方法，是利用 OSQL 命令來建立一個 . cmd 檔案。本例中，我們可以命名一個 MyDB_info.cmd 檔案，其中含有我們剛剛鍵入的 OSQL 命令。如果輸入與輸出的檔案與 .cmd 檔案不在同一個目錄裡，要確定已經指出它們的路徑。現在您可以在命令提示字元下直接執行 MyDB_info.cmd 或在 Microsoft Windows Explorer 中按兩下 MyDB_info.cmd 檔案名稱。

您也可以在 Query Analyzer 中執行 T-SQL 指令碼。要執行我們的指令碼 My_Dbinfo.sql，從 檔案 功能表中選擇 開啟舊檔 ，瀏覽並選擇打算執行的檔案。按一下 開啟 後指令碼的程式碼將會顯示在上方的窗格。按一下 執行查詢 按鈕或同時按 Ctrl+E 來執行這些陳述式。每個陳述式的執行結果將依序出現，就如圖13-5所示。注意最上端的兩個結果資料表，分別來自指令碼中前兩個 sp_helpdb 預存程序。

圖13-5 指令碼在 Query Analyzer 中的執行結果

本章總結

在本章中，您已學習到 SQL 與 T-SQL 的基本觀念，並且也已看到 DDL 與DML 陳述式的一些簡單範例。我們也介紹了執行 T-SQL 陳述式的不同方法－包括 ISQL、OSQL、SQL Query Analyzer 以及 T-SQL 指令碼。在接下來兩章以及

第25章 中，會發現更多使用 T-SQL 的相關訊息。