教學課程:使用 SQL Server Management Studio (SSMS) 連線至 SQL Server 執行個體並進行查詢Tutorial: Connect to and query a SQL Server instance by using SQL Server Management Studio (SSMS)

本教學課程將教導您如何使用 SQL Server Management Studio (SSMS) 連線到 SQL Server 執行個體,並執行一些基本的 Transact-SQL (T-SQL) 命令。This tutorial teaches you how to use SQL Server Management Studio (SSMS) to connect to your SQL Server instance and run some basic Transact-SQL (T-SQL) commands. 此文章示範如何遵循下列步驟:The article demonstrates how to follow the below steps:

  • 連接到 SQL Server 執行個體Connect to a SQL Server instance
  • 建立資料庫 ("TutorialDB")Create a database ("TutorialDB")
  • 在新的資料庫中建立資料表 ("Customers")Create a table ("Customers") in your new database
  • 在新的資料表內插入資料列Insert rows into your new table
  • 查詢新的資料表並檢視結果Query the new table and view the results
  • 使用查詢視窗資料表來驗證您的連線屬性Use the query window table to verify your connection properties
  • 變更您查詢視窗所連線的伺服器Change the server that your query window is connected to

PrerequisitesPrerequisites

若要完成本教學課程,您需要 SQL Server Management Studio 和 SQL Server 執行個體存取權。To complete this tutorial, you need SQL Server Management Studio and access to a SQL Server instance.

若您沒有 SQL Server 執行個體存取權,請從下列連結選取您的平台。If you don't have access to a SQL Server instance, select your platform from the following links. 若您選擇 SQL 驗證,請使用您的 SQL Server 登入認證。If you choose SQL Authentication, use your SQL Server login credentials.

連接到 SQL Server 執行個體Connect to a SQL Server instance

  1. 啟動 SQL Server Management Studio。Start SQL Server Management Studio. 首次執行 SSMS 時,會開啟 [連線至伺服器] 視窗。The first time you run SSMS, the Connect to Server window opens. 若該視窗未開啟,您可透過選取 [物件總管] > [連線] > [資料庫引擎] 手動加以開啟。If it doesn't open, you can open it manually by selecting Object Explorer > Connect > Database Engine.

    物件總管中的連線連結

  2. 在 [連線至伺服器] 視窗中,執行下列清單的動作:In the Connect to Server window, follow the list below:

    • 針對伺服器類型,選取 [資料庫引擎] (通常為預設選項)。For Server type, select Database Engine (usually the default option).

    • 針對伺服器名稱,輸入您 SQL Server 執行個體的名稱。For Server name, enter the name of your SQL Server instance. (本文使用了主機名稱 NODE5 [NODE5\SQL2016ST] 上的執行個體名稱 SQL2016ST)。若您不確定如何判斷 SQL Server 執行個體名稱,請參閱使用 SSMS 的其他提示與祕訣(This article uses the instance name SQL2016ST on the hostname NODE5 [NODE5\SQL2016ST].) If you're unsure how to determine your SQL Server instance name, see Additional tips and tricks for using SSMS.

    • 針對驗證,請選取 [Windows 驗證]。For Authentication, select Windows Authentication. 本文使用 Windows 驗證,但 SQL Server 登入亦受支援。This article uses Windows Authentication, but SQL Server login is also supported. 若您選取 [SQL 登入],系統會提示您輸入使用者名稱與密碼。If you select SQL Login, you're prompted for a username and password. 如需驗證類型的詳細資訊,請參閱連線至伺服器 (資料庫引擎)For more information about authentication types, see Connect to the server (database engine).

    [伺服器名稱] 欄位與使用 SQL Server 執行個體的選項

    您也可透過選取 [選項] 修改其他連線設定。You can also modify additional connection options by selecting Options. 連線選項的範例為您所連線的資料庫、連線逾時值以及網路通訊協定。Examples of connection options are the database you're connecting to, the connection timeout value, and the network protocol. 本文會為所有選項使用預設值。This article uses the default values for all the options.

  3. 當您填完所有欄位後,請選取 [連線]。After you've completed all the fields, select Connect.

成功連線的範例Examples of successful connections

若要驗證您的 SQL Server 連線成功,請展開並瀏覽 [物件總管] 中的物件。To verify that your SQL Server connection succeeded, expand and explore the objects within Object Explorer. 這些物件會根據您選擇連線的伺服器類型而有所不同。These objects are different depending on the type of server you choose to connect.

  • 連線到內部部署 SQL Server - 在此案例中為 NODE5\SQL2016ST:連線到內部部署伺服器Connecting to an on-premises SQL server - in this case NODE5\SQL2016ST: Connecting to an on-premises server

  • 連線到 SQL Azure 資料庫 - 在此案例中為 msftestserver.database.windows.net:連線到 SQL Azure 資料庫Connecting to SQL Azure DB - in this case msftestserver.database.windows.net: Connecting to a SQL Azure DB

    注意

    在本教學課程中,您之前使用「Windows 驗證」連線到您的內部部署 SQL Server,但 SQL Azure 資料庫不支援這個方法。In this tutorial, you previously used Windows Authentication to connect to your on-premises SQL server, but this method is not supported for SQL Azure DB. 因此,此影像顯示使用 SQL 驗證連線到 SQL Azure 資料庫。As such, this image shows using SQL Authentication to connect to the SQL Azure DB. 如需詳細資訊,請參閱 SQL 內部部署驗證SQL Azure 驗證For more information, see SQL on-premises authentication and SQL Azure authentication.

建立資料庫Create a database

請遵循下列步驟,建立名為 TutorialDB 的資料庫:Create a database named TutorialDB by following the below steps:

  1. 在物件總管中以滑鼠右鍵按一下您的伺服器執行個體,然後選取 [新增查詢]:Right-click your server instance in Object Explorer, and then select New Query:

    新增查詢連結

  2. 在查詢視窗中,貼上以下 T-SQL 程式碼片段:Into the query window, paste the following T-SQL code snippet:

    USE master
    GO
    IF NOT EXISTS (
       SELECT name
       FROM sys.databases
       WHERE name = N'TutorialDB'
    )
    CREATE DATABASE [TutorialDB]
    GO
    
  3. 若要執行查詢,請選取 [執行] (或在鍵盤上選取 F5)。To execute the query, select Execute (or select F5 on your keyboard).

    執行命令

    查詢完成後,新的 TutorialDB 資料庫會顯示在物件總管的資料庫清單中。After the query is complete, the new TutorialDB database appears in the list of databases in Object Explorer. 若其未顯示,請以滑鼠右鍵按一下 [資料庫] 節點,然後選取 [重新整理]。If it isn't displayed, right-click the Databases node, and then select Refresh.

在新的資料庫中建立資料表Create a table in the new database

在本節中,您會在新建立的 TutorialDB 資料庫中建立資料表。In this section, you create a table in the newly created TutorialDB database. 因為查詢編輯器仍在 master 資料庫的內容中,請執行下列步驟將連線內容切換至 TutorialDB 資料庫:Because the query editor is still in the context of the master database, switch the connection context to the TutorialDB database by doing the following steps:

  1. 在資料庫下拉式清單中,選取您想要的資料庫,如此處所示:In the database drop-down list, select the database that you want, as shown here:

    變更資料庫

  2. 在查詢視窗中貼上以下 T-SQL 程式碼片段,然後選取 [執行] (或選取鍵盤上的 F5)。Paste the following T-SQL code snippet into the query window, select it, and then select Execute (or select F5 on your keyboard).
    您可以替換查詢視窗中的現有文字,或將其附加至結尾。You can either replace the existing text in the query window or append it to the end. 若要在查詢視窗中執行所有項目,請選取 [執行]。To execute everything in the query window, select Execute. 若要執行部分文字,請將該部分反白,並選取 [執行]。To execute a portion of the text, highlight that portion, and then select Execute.

    -- Create a new table called 'Customers' in schema 'dbo'
    -- Drop the table if it already exists
    IF OBJECT_ID('dbo.Customers', 'U') IS NOT NULL
    DROP TABLE dbo.Customers
    GO
    -- Create the table in the specified schema
    CREATE TABLE dbo.Customers
    (
       CustomerId        INT    NOT NULL   PRIMARY KEY, -- primary key column
       Name      [NVARCHAR](50)  NOT NULL,
       Location  [NVARCHAR](50)  NOT NULL,
       Email     [NVARCHAR](50)  NOT NULL
    );
    GO
    

查詢完成後,新的 [客戶] 資料表會顯示在物件總管的資料表清單中。After the query is complete, the new Customers table is displayed in the list of tables in Object Explorer. 若資料表未顯示,請在物件總管中以滑鼠右鍵按一下 TutorialDB > Tables 節點,然後選取 [重新整理]。If the table isn't displayed, right-click the TutorialDB > Tables node in Object Explorer, and then select Refresh.

在新的資料表插入資料列Insert rows into the new table

在您先前建立的 [客戶] 資料表插入一些資料列。Insert some rows into the Customers table that you created previously. 若要這麼做,請在查詢視窗貼上以下 T-SQL 程式碼片段,然後選取 [執行]:To do so, paste the following T-SQL code snippet into the query window, and then select Execute:

-- Insert rows into table 'Customers'
INSERT INTO dbo.Customers
   ([CustomerId],[Name],[Location],[Email])
VALUES
   ( 1, N'Orlando', N'Australia', N''),
   ( 2, N'Keith', N'India', N'keith0@adventure-works.com'),
   ( 3, N'Donna', N'Germany', N'donna0@adventure-works.com'),
   ( 4, N'Janet', N'United States', N'janet1@adventure-works.com')
GO

查詢資料表並檢視結果Query the table and view the results

查詢結果會顯示在查詢文字視窗下方。The results of a query are visible below the query text window. 若要查詢 Customers 資料表並檢視先前插入的資料列,請遵循下列步驟:To query the Customers table and view the rows that were previously inserted, follow these steps:

  1. 在查詢視窗中貼上以下 T-SQL 程式碼片段,然後選取 [執行]:Paste the following T-SQL code snippet into the query window, and then select Execute:

    -- Select rows from table 'Customers'
    SELECT * FROM dbo.Customers;
    

    查詢結果會顯示在輸入文字的區域下:The results of the query are displayed under the area where the text was entered:

    結果清單

  2. 您可透過選取下列其中一個選項來修改結果的呈現方式:Modify the way results are presented by selecting one of the following options:

    三個顯示查詢結果的選項

    • 中間的按鈕會在 [格線檢視] 中顯示結果,這是預設選項。The middle button displays the results in Grid View, which is the default option.
    • 第一個按鈕會在 [文字檢視] 中顯示結果,如下一節中的影像所示。The first button displays the results in Text View, as shown in the image in the next section.
    • 第三個按鈕可讓您將結果儲存至檔案,其副檔名預設為 .rpt。The third button lets you save the results to a file whose extension is .rpt by default.

您可透過使用查詢視窗資料表來驗證連線屬性Verify your connection properties by using the query window table

您可以在查詢結果下找到連線屬性的相關資訊。You can find information about the connection properties under the results of your query. 當您執行在上一個步驟提到的查詢後,請檢閱查詢視窗底部的連線屬性。After you run the previously mentioned query in the preceding step, review the connection properties at the bottom of the query window.

  • 您可以判斷您連線至哪一部伺服器與哪個資料庫,以及您使用的使用者名稱。You can determine which server and database you're connected to, and the username that you use.

  • 您也可以檢視上一個執行之查詢所傳回的查詢持續時間與資料列數。You can also view the query duration and the number of rows that are returned by the previously executed query.

    連線內容

    注意

    在影像中,結果會顯示在 [文字檢視] 中。In the image, the results are displayed in Text View.

根據查詢視窗變更伺服器Change the server based on the query window

您可以遵循下列步驟,變更目前查詢視窗所連線的伺服器:You can change the server that your current query window is connected to by following the steps below:

  1. 以滑鼠右鍵按一下查詢視窗,然後選取 [連線] > [變更連線]。Right-click in the query window, and then select Connection > Change connection. [連線至伺服器] 視窗會再次開啟。The Connect to Server window opens again.

  2. 變更您查詢所使用的伺服器。Change the server that your query uses.

    變更連線命令

    注意

    此動作只會變更查詢視窗所連線的伺服器,而不會變更 [物件總管] 所使用的伺服器。This action changes only the server that the query window is connected to, not the server that Object Explorer uses.

後續步驟Next steps

熟悉 SSMS 的最佳方式是實際練習。The best way to get acquainted with SSMS is through hands-on practice. 這些文章可協助您使用 SSMS 內所提供的各種功能。These articles help you with various features available within SSMS. 這些文章會告訴您如何管理 SSMS 的元件及如何尋找您經常使用的功能。These articles teach you how to manage the components of SSMS and how to find the features that you use regularly.