快速入門:使用 Visual Studio Code 連接及查詢 Azure SQL DatabaseQuickstart: Use Visual Studio Code to connect and query an Azure SQL Database

Visual Studio Code 是 Linux、macOS 及 Windows 適用的圖形化程式碼編輯器。Visual Studio Code is a graphical code editor for Linux, macOS, and Windows. 它支援擴充功能,包括可供查詢 Microsoft SQL Server、Azure SQL Database 和 SQL 資料倉儲的 mssql 擴充功能It supports extensions, including the mssql extension for querying Microsoft SQL Server, Azure SQL Database, and SQL Data Warehouse. 在此快速入門中,您將使用 Visual Studio Code 連線至 Azure SQL 資料庫,然後執行 Transact-SQL 陳述式來查詢、插入、更新和刪除資料。In this quickstart, you'll use Visual Studio Code to connect to an Azure SQL database and then run Transact-SQL statements to query, insert, update, and delete data.

必要條件Prerequisites

安裝 Visual Studio CodeInstall Visual Studio Code

請確定您已安裝最新版的 Visual Studio Code 並已載入 mssql 擴充功能Make sure you have installed the latest Visual Studio Code and loaded the mssql extension. 如需 mssql 擴充功能的安裝指引,請參閱安裝 VS Code適用於 Visual Studio Code 的 mssqlFor guidance on installing the mssql extension, see Install VS Code and mssql for Visual Studio Code .

設定 Visual Studio CodeConfigure Visual Studio Code

Mac OSMac OS

對於 macOS,您必須安裝 OpenSSL,這是 mssql 擴充功能所用 .NET Core 的必要條件。For macOS, you need to install OpenSSL, which is a prerequisite for .NET Core that mssql extension uses. 開啟您的終端機,並輸入下列命令以安裝 brewOpenSSLOpen your terminal and enter the following commands to install brew and OpenSSL.

ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
brew update
brew install openssl
mkdir -p /usr/local/lib
ln -s /usr/local/opt/openssl/lib/libcrypto.1.0.0.dylib /usr/local/lib/
ln -s /usr/local/opt/openssl/lib/libssl.1.0.0.dylib /usr/local/lib/

Linux (Ubuntu)Linux (Ubuntu)

不需要特別設定。No special configuration needed.

WindowsWindows

不需要特別設定。No special configuration needed.

取得 SQL Server 連線資訊Get SQL server connection information

取得連線到 Azure SQL 資料庫所需的連線資訊。Get the connection information you need to connect to the Azure SQL database. 在後續程序中,您將需要完整的伺服器名稱或主機名稱、資料庫名稱和登入資訊。You'll need the fully qualified server name or host name, database name, and login information for the upcoming procedures.

  1. 登入 Azure 入口網站Sign in to the Azure portal.

  2. 瀏覽至 [SQL 資料庫] 或 [SQL 受控執行個體] 頁面。Navigate to the SQL databases or SQL managed instances page.

  3. 在 [概觀] 頁面上,針對單一資料庫檢閱 [伺服器名稱] 旁的完整伺服器名稱,若為受控執行個體,則檢閱 [主機] 旁的完整伺服器名稱。On the Overview page, review the fully qualified server name next to Server name for a single database or the fully qualified server name next to Host for a managed instance. 若要複製伺服器名稱或主機名稱,請將滑鼠暫留在其上方,然後選取 [複製] 圖示。To copy the server name or host name, hover over it and select the Copy icon.

將語言模式設定為 SQLSet language mode to SQL

在 Visual Studio Code 中將語言模式設定為 SQL,以啟用 mssql 命令和 T-SQL IntelliSense。In Visual Studio Code, set the language mode to SQL to enable mssql commands and T-SQL IntelliSense.

  1. 開啟新的 Visual Studio Code 視窗。Open a new Visual Studio Code window.

  2. Ctrl+NPress Ctrl+N. 此時會開啟新的純文字檔。A new plain text file opens.

  3. 選取狀態列右下角的 [純文字] 。Select Plain Text in the status bar's lower right-hand corner.

  4. 在開啟的 [選取語言模式] 下拉式功能表中,選取 [SQL] 。In the Select language mode drop-down menu that opens, select SQL.

連接到您的資料庫Connect to your database

使用 Visual Studio Code 建立對 Azure SQL Database 伺服器的連線。Use Visual Studio Code to establish a connection to your Azure SQL Database server.

重要

在繼續之前,確定您已備妥伺服器和登入資訊。Before continuing, make sure that you have your server and sign in information ready. 開始輸入連線設定檔資訊後,如果您的焦點變換自 Visual Studio Code,則必須重新開始建立設定檔。Once you begin entering the connection profile information, if you change your focus from Visual Studio Code, you have to restart creating the profile.

  1. 在 Visual Studio Code 中,按 Ctrl+Shift+P (或 F1) 以開啟命令選擇區。In Visual Studio Code, press Ctrl+Shift+P (or F1) to open the Command Palette.

  2. 選取 [MS SQL: 連線] 然後選擇 EnterSelect MS SQL:Connect and choose Enter.

  3. 選取 [建立連線設定檔] 。Select Create Connection Profile.

  4. 請依照提示指定新設定檔的連線屬性。Follow the prompts to specify the new profile's connection properties. 指定每個值之後,選擇 ENTER 繼續。After specifying each value, choose Enter to continue.

    屬性Property       建議的值Suggested value 說明Description
    伺服器名稱Server name 完整伺服器名稱The fully qualified server name 應該類似這樣︰mynewserver20170313.database.windows.netSomething like: mynewserver20170313.database.windows.net.
    資料庫名稱Database name mySampleDatabasemySampleDatabase 要連線的資料庫。The database to connect to.
    驗證Authentication SQL 登入SQL Login 本教學課程使用 SQL 驗證。This tutorial uses SQL Authentication.
    使用者名稱User name 使用者名稱User name 建立伺服器時所使用伺服器系統管理員帳戶的使用者名稱。The user name of the server admin account used to create the server.
    密碼 (SQL 登入)Password (SQL Login) 密碼Password 建立伺服器時所使用伺服器系統管理員帳戶的密碼。The password of the server admin account used to create the server.
    儲存密碼?Save Password? 是或否Yes or No 如果您不希望每次都要輸入密碼,請選取 [是] 。Select Yes if you do not want to enter the password each time.
    輸入這個設定檔的名稱Enter a name for this profile 設定檔名稱,例如 mySampleProfileA profile name, such as mySampleProfile 儲存設定檔可讓您在後續登入時加快連線速度。A saved profile speeds your connection on subsequent logins.

    如果成功會顯示通知,指出您的設定檔已建立並連線。If successful, a notification appears saying your profile is created and connected.

查詢資料Query data

執行以下 SELECT Transact-SQL 陳述式來依照類別查詢前 20 項產品。Run the following SELECT Transact-SQL statement to query for the top 20 products by category.

  1. 在 [編輯器] 視窗中,貼上下列 SQL 查詢。In the editor window, paste the following SQL query.

    SELECT pc.Name as CategoryName, p.name as ProductName
    FROM [SalesLT].[ProductCategory] pc
    JOIN [SalesLT].[Product] p
    ON pc.productcategoryid = p.productcategoryid;
    
  2. Ctrl+Shift+E 執行查詢並顯示來自 ProductProductCategory 資料表的結果。Press Ctrl+Shift+E to run the query and display results from the Product and ProductCategory tables.

    從 2 個資料表擷取資料的查詢

插入資料Insert data

執行以下 INSERT Transact-SQL 陳述式在 SalesLT.Product 資料表中加入新產品。Run the following INSERT Transact-SQL statement to add a new product into the SalesLT.Product table.

  1. 使用此查詢取代先前的查詢。Replace the previous query with this one.

    INSERT INTO [SalesLT].[Product]
         ( [Name]
         , [ProductNumber]
         , [Color]
         , [ProductCategoryID]
         , [StandardCost]
         , [ListPrice]
         , [SellStartDate]
         )
      VALUES
         ('myNewProduct'
         ,123456789
         ,'NewColor'
         ,1
          ,100
          ,100
          ,GETDATE() );
    
  2. 按下 Ctrl+Shift+EProduct 資料表中插入新資料列。Press Ctrl+Shift+E to insert a new row in the Product table.

更新資料Update data

執行以下 UPDATE Transact-SQL 陳述式更新加入的產品。Run the following UPDATE Transact-SQL statement to update the added product.

  1. 使用此查詢取代先前的查詢:Replace the previous query with this one:

    UPDATE [SalesLT].[Product]
    SET [ListPrice] = 125
    WHERE Name = 'myNewProduct';
    
  2. Ctrl+Shift+E 以在 Product 資料表中更新指定的資料列。Press Ctrl+Shift+E to update the specified row in the Product table.

刪除資料Delete data

執行以下 DELETE Transact-SQL 陳述式移除新產品。Run the following DELETE Transact-SQL statement to remove the new product.

  1. 使用此查詢取代先前的查詢:Replace the previous query with this one:

    DELETE FROM [SalesLT].[Product]
    WHERE Name = 'myNewProduct';
    
  2. Ctrl+Shift+E 以在 Product 資料表中刪除指定的資料列。Press Ctrl+Shift+E to delete the specified row in the Product table.

後續步驟Next steps