快速入门:使用 Visual Studio Code 进行连接和查询Quickstart: Use Visual Studio Code to connect and query

适用于:是 Azure SQL 数据库 是Azure SQL 托管实例 APPLIES TO: yesAzure SQL Database yesAzure SQL Managed Instance

Visual Studio Code 是一种图形代码编辑器,适用于 Linux、macOS 和 Windows。Visual Studio Code is a graphical code editor for Linux, macOS, and Windows. 它支持各种扩展,其中包括 mssql 扩展(用于查询 SQL Server 实例、Azure SQL 数据库、Azure SQL 托管实例和 Azure Synapse Analytics 中的数据库)。It supports extensions, including the mssql extension for querying a SQL Server instance, Azure SQL Database, an Azure SQL Managed Instance, and a database in Azure Synapse Analytics. 在本快速入门中,使用 Visual Studio Code 连接到 Azure SQL 数据库或 Azure SQL 托管实例,然后运行 Transact-SQL 语句以查询、插入、更新和删除数据。In this quickstart, you'll use Visual Studio Code to connect to Azure SQL Database or Azure SQL Managed Instance 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 扩展的安装指南,请参阅安装 Visual Studio Code适用于 Visual Studio Code 的 mssqlFor guidance on installing the mssql extension, see Install Visual Studio Code and mssql for Visual Studio Code .

配置 Visual Studio CodeConfigure Visual Studio Code

macOSmacOS

对于 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.

获取服务器连接信息Get server connection information

获取连接到 Azure SQL 数据库所需的连接信息。Get the connection information you need to connect to 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. 在“概览”页中,查看 SQL 数据库的“服务器名称”旁边的完全限定的服务器名称,或者 SQL 托管实例的“主机”旁边的完全限定的服务器名称 。On the Overview page, review the fully qualified server name next to Server name for SQL Database or the fully qualified server name next to Host for a SQL 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 建立到服务器的连接。Use Visual Studio Code to establish a connection to your 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:Connect”,然后选择 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.chinacloudapi.cnSomething like: mynewserver20170313.database.chinacloudapi.cn.
    数据库名称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+E 在 Product 表中插入新行 。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