課程 1:建立及查詢資料庫物件

適用于:yesSQL Server (所有支援的版本) Yes Azure SQL Database Yes Azure SQL 受控實例 yes 分析平臺系統 (PDW)

這一課會示範如何建立資料庫、在資料庫中建立資料表,然後在資料表中存取和變更資料。 由於本課程是使用 Transact-SQL 的簡介,因此不會使用這些語句使用或描述許多可用的選項。

Transact-SQL 語句可以透過下列方式寫入並提交至 Database Engine:

  • 使用 SQL Server Management Studio。 本教學課程假設您使用 Management Studio,但您也可以使用 Management Studio Express,可從 Microsoft 下載中心免費下載。

  • 使用 sqlcmd 公用程式

  • 從您建立的應用程式連接。

不論您提交程式碼語句的方式為何,程式碼都會以相同方式和相同的許可權在 Database Engine 上執行。

若要在 Management Studio 中執行 Transact-SQL 語句,請開啟 Management Studio 並聯機到 SQL Server Database Engine 的實例。

必要條件

若要完成本教學課程,您需要 SQL Server Management Studio 和 SQL Server 執行個體存取權。

如果您沒有 SQL Server 執行個體,請建立一個。 請從下列連結選取平台來建立 SQL Server 執行個體。 若您選擇 SQL 驗證,請使用您的 SQL Server 登入認證。

建立資料庫

如同許多 Transact-SQL 語句, CREATE DATABASE 語句具有必要的參數:資料庫的名稱。 CREATE DATABASE 也有許多選擇性參數,例如您要放置資料庫檔案的磁片位置。 當您在沒有選擇性參數的情況下執行 CREATE DATABASE 時,SQL Server 會針對其中許多參數使用預設值。

  1. 在 [查詢編輯器] 視窗中,輸入下列程式碼 (但不要執行):

    CREATE DATABASE TestData  
    GO  
    
  2. 使用指標選取 CREATE DATABASE這兩個字,然後按 F1鍵。 這時應該會開啟《SQL Server 線上叢書》中的 CREATE DATABASE 主題。 您可使用此技術來找到 CREATE DATABASE,以及在這個教學課程中所使用的其他陳述式完整語法。

  3. 在 [查詢編輯器] 中按 F5 ,執行陳述式並建立名為 TestData的資料庫。

當您建立資料庫時,SQL Server 會建立 模型 資料庫的複本,並將複本重新命名為資料庫名稱。 除非您在選擇性參數中指定了非常大的資料庫初始大小,否則這項作業應該只需要幾秒鐘的時間。

注意

如果在單一批次中提交了一個以上的陳述式,可用關鍵字 GO 來分隔陳述式; 如果批次中只包含一個陳述式,則 GO 可有可無。

建立資料表

適用于:SQL Server Azure SQL Database Azure Synapse Analytics Analytics Platform System (PDW)

若要建立資料表,您必須提供資料表的名稱,以及資料表中各資料行的名稱和資料類型, 最好也能指出各資料行中是否允許有 Null 值。 若要建立資料表,您必須擁有 CREATE TABLE 權限,以及將包含資料表之結構描述的 ALTER SCHEMA 權限。 db_ddladmin 固定資料庫角色擁有這些權限。

大多數資料表都具有由資料表中一或多個資料行組成的主索引鍵。 主索引鍵一定是唯一的。 Database Engine 會強制限制資料表中無法重複任何主鍵值。

如需每個資料類型和描述的連結清單,請參閱 Transact-SQL) (資料類型

注意

資料庫引擎可以安裝為區分大小寫或不區分大小寫。 如果 Database Engine 安裝為區分大小寫,則物件名稱一律必須有相同的大小寫。 例如,名稱為 OrderData 的資料表與名稱為 ORDERDATA 的資料表會代表不同的資料表。 如果 Database Engine 安裝為不區分大小寫,這兩個數據表名稱會被視為相同的資料表,而且該名稱只能使用一次。

將查詢編輯器連接切換到 TestData 資料庫

在 [查詢編輯器] 視窗中,輸入並執行下列程式碼,將連接變更為 TestData 資料庫。

USE TestData  
GO  

建立資料表

在 [查詢編輯器] 視窗中鍵入並執行下列程式碼,以建立名為 Products 的資料表。 此資料表中的資料行名稱分別為 ProductIDProductNamePriceProductDescriptionProductID 資料行是此資料表的主索引鍵。 intvarchar(25)moneyvarchar(max) 全部都是資料類型。 在插入或變更資料列時,只有 PriceProductionDescription 資料行可以不含任何資料。 這個陳述式包含一個選擇性的元素 (dbo.),稱為「結構描述」。 結構描述就是擁有資料表的資料庫物件。 如果您是系統管理員,則 dbo 是預設的結構描述。 dbo 代表資料庫擁有者。

CREATE TABLE dbo.Products  
   (ProductID int PRIMARY KEY NOT NULL,  
   ProductName varchar(25) NOT NULL,  
   Price money NULL,  
   ProductDescription varchar(max) NULL)  
GO  

在資料表中插入及更新資料

既然您現在建立好 Products 資料表,就可以準備使用 INSERT 陳述式,將資料插入資料表。 在插入資料後,您將使用 UPDATE 陳述式來變更資料列的內容。 您將使用 UPDATE 陳述式的 WHERE 子句,限制對單一資料列進行更新。 接下來所述的四個陳述式將輸入下面資料。

ProductID ProductName 價格 ProductDescription
1 Clamp 12.48 Workbench clamp
50 Screwdriver 3.17 Flat head
75 Tire Bar Tool for changing tires.
3000 3 mm Bracket 0.52

基本語法包括:INSERT、資料表、資料行清單、VALUES 以及要插入的值清單。 程式行前面的兩個連字號表示該程式行是註解,而編譯器會忽略這行文字。 在本案例中,註解說明所允許的語法變化。

將資料插入資料表中

  1. 執行下列陳述式,將資料列插入上一項工作中建立的 Products 資料表。
-- Standard syntax  
INSERT dbo.Products (ProductID, ProductName, Price, ProductDescription)  
    VALUES (1, 'Clamp', 12.48, 'Workbench clamp')  
GO   

注意

如果插入成功,請繼續進行下一個步驟。

如果插入失敗,可能是因為 Product 資料表已經具有資料列,其中包含該產品識別碼。 若要繼續,請刪除資料表中的所有資料列,並重複上述步驟。 TRUNCATE TABLE 刪除資料表中的所有資料列。

執行下列命令來刪除資料表中的所有資料列:

TRUNCATE TABLE TestData.dbo.Products;
GO

截斷資料表後,請重複此步驟中的 INSERT 命令。

  1. 下列陳述式示範如何可以在透過切換欄位清單 (括號內) 和值清單內 ProductIDProductName 的位置所提供的參數中變更順序。
-- Changing the order of the columns  
INSERT dbo.Products (ProductName, ProductID, Price, ProductDescription)  
    VALUES ('Screwdriver', 50, 3.17, 'Flat head')  
GO    
  1. 下列陳述式示範只要依照正確的順序列出值,就可以省略資料行的名稱。 這是常見的語法,但不建議您使用,因為其他使用者可能會很難了解您的程式碼。 NULL 已針對 Price 資料行指定,這是因為此產品的價格不明。
-- Skipping the column list, but keeping the values in order  
INSERT dbo.Products  
    VALUES (75, 'Tire Bar', NULL, 'Tool for changing tires.')  
GO  
  1. 只要是在預設的結構描述中存取及變更資料表,就可以省略結構描述名稱。 因為 ProductDescription 資料行可以接受 Null 值及無值,所以在陳述式中便可以完全省略 ProductDescription 資料行名稱和值。
-- Dropping the optional dbo and dropping the ProductDescription column  
INSERT Products (ProductID, ProductName, Price)  
    VALUES (3000, '3 mm Bracket', 0.52)  
GO  

更新產品資料表

輸入並執行下列 UPDATE 陳述式,將第二個產品的 ProductNameScrewdriver變更為 Flat Head Screwdriver

UPDATE dbo.Products  
    SET ProductName = 'Flat Head Screwdriver'  
    WHERE ProductID = 50  
GO  

從資料表讀取資料

使用 SELECT 陳述式來讀取資料表的資料。 SELECT 語句是其中一個最重要的 Transact-SQL 語句,而且語法有許多變化。 在本教學課程中,您將使用五種簡單的變化樣式。

讀取資料表的資料

  1. 輸入並執行下列陳述式,以讀取 Products 資料表的資料。
-- The basic syntax for reading data from a single table  
SELECT ProductID, ProductName, Price, ProductDescription  
    FROM dbo.Products  
GO  
  1. 您可使用星號 (*) 來選取資料表中的所有資料行。 星號適用於特定查詢。 請在固定程式碼中提供資料行清單,讓陳述式傳回預期的資料行,即使以後將新資料行新增至資料表也一樣。
-- Returns all columns in the table  
-- Does not use the optional schema, dbo  
SELECT * FROM Products  
GO   
  1. 您可以省略不要傳回的資料行。 而且會以資料行所列出的順序來傳回資料行。
-- Returns only two of the columns from the table  
SELECT ProductName, Price  
    FROM dbo.Products  
GO    
  1. 使用 WHERE 子句,限制要傳回給使用者的資料列。
-- Returns only two of the records in the table  
SELECT ProductID, ProductName, Price, ProductDescription  
    FROM dbo.Products  
    WHERE ProductID < 60  
GO    
  1. 您可以處理資料行中所傳回的值。 下列範例會在 Price 資料行上進行數學運算。 除非使用 AS 關鍵字提供名稱,否則以這種方式變更的資料行將不會有名稱。
-- Returns ProductName and the Price including a 7% tax  
-- Provides the name CustomerPays for the calculated column  
SELECT ProductName, Price * 1.07 AS CustomerPays  
    FROM dbo.Products  
GO  

SELECT 陳述式中的實用函數

如需有關某些可在 SELECT 陳述式中用來處理資料之函數的詳細資訊,請參閱下列主題:

建立檢視和預存程序

檢視是預存的 SELECT 語句,而預存程式是一或多個以批次方式執行的 Transact-SQL 語句。

檢視的查詢方式就跟資料表一樣,而且不接受參數。 預存程序就比檢視還要複雜。 預存程序能有輸出和輸入參數,而且還能包含控制程式碼流程的陳述式,如 IF 和 WHILE 陳述式。 對於所有在資料庫中的重複動作,使用預存程序是一個不錯的程式設計方法。

例如,您將使用 CREATE VIEW 建立檢視,其中只選取 Products 資料表中的兩個資料行。 接著,您將使用 CREATE PROCEDURE 建立預存程序,其中接受 price 參數並只傳回成本小於指定參數值的產品。

建立檢視

執行下列陳述式來建立會執行 SELECT 陳述式並將產品名稱及價格傳回給使用者的檢視。

CREATE VIEW vw_Names  
   AS  
   SELECT ProductName, Price FROM Products;  
GO    

測試檢視

檢視的處理方式和資料表一樣。 使用 SELECT 陳述式存取檢視。

SELECT * FROM vw_Names;  
GO   

建立預存程序

下列陳述式會建立預存程序名稱 pr_Names,接受資料類型為 @VarPrice 的輸入參數 (名稱是 money)。 預存程序會列印與輸出參數串連的 Products less than 陳述式,而這個輸出參數會從 money 資料類型變更為 varchar(10) 字元資料類型。 然後,預存程序會執行檢視上的 SELECT 陳述式,將輸出參數當做 WHERE 子句的一部分進行傳遞。 這樣會傳回成本小於輸出參數值的所有產品。

CREATE PROCEDURE pr_Names @VarPrice money  
   AS  
   BEGIN  
      -- The print statement returns text to the user  
      PRINT 'Products less than ' + CAST(@VarPrice AS varchar(10));  
      -- A second statement starts here  
      SELECT ProductName, Price FROM vw_Names  
            WHERE Price < @VarPrice;  
   END  
GO    

測試預存程序

若要測試預存程序,請輸入並執行下列陳述式。 這個程序應該傳回兩個成本小於 Products 的產品名稱,這兩個產品是在第 1 課輸入 10.00資料表而來的。

EXECUTE pr_Names 10.00;  
GO  

後續步驟

下一篇文章會教您如何設定資料庫物件的權限。 在課程 1 中建立的物件也會用於課程 2。

請前往下一篇文章以深入了解: