使用 Visual Studio Code 创建和运行 SQL Server 的 TRANSACT-SQL 脚本Use Visual Studio Code to create and run Transact-SQL scripts for SQL Server

本主题适用于: 是SQL Server (仅限 Linux)没有Azure SQL 数据库没有Azure SQL 数据仓库没有并行数据仓库 THIS TOPIC APPLIES TO: yesSQL Server (Linux only)noAzure SQL DatabasenoAzure SQL Data WarehousenoParallel Data Warehouse

这篇文章演示如何使用mssql Visual Studio Code (VS Code) 要开发 SQL Server 数据库的扩展。This article shows how to use the mssql extension for Visual Studio Code (VS Code) to develop SQL Server databases.

Visual Studio Code 是一款适用于 Linux、macOS 和 Windows 的图形代码编辑器,支持扩展。Visual Studio Code is a graphical code editor for Linux, macOS, and Windows that supports extensions. Mssql VS Code 的扩展使您能够连接到 SQL Server,使用 TRANSACT-SQL (T-SQL),查询并查看结果。The mssql extension for VS Code enables you to connect to SQL Server, query with Transact-SQL (T-SQL), and view the results.

安装 VS CodeInstall VS Code

  1. 如果尚未安装 VS Code 中,下载并安装 VS Code在您的计算机上。If you have not already installed VS Code, Download and install VS Code on your machine.

  2. 启动 VS Code。Start VS Code.

安装 mssql 扩展Install the mssql extension

以下步骤说明了如何安装 mssql 扩展。The following steps explain how to install the mssql extension.

  1. CTRL + SHIFT + P (或F1) 以打开在 VS Code 的命令控制板。Press CTRL+SHIFT+P (or F1) to open the Command Palette in VS Code.

  2. 选择安装扩展和类型mssqlSelect Install Extension and type mssql.

    提示

    有关 macOS, CMD密钥相当于CTRL Linux 和 Windows 上的密钥。For macOS, the CMD key is equivalent to CTRL key on Linux and Windows.

  3. 单击安装mssqlClick install mssql.

    Install the extension

  4. Mssql扩展采用一分钟的时间安装。The mssql extension takes up to one minute to install. 请等待通知安装成功的提示。Wait for the prompt that tells you it successfully installed.

    Installation success notification

    备注

    对于 macOS,则必须安装 OpenSSL。For macOS, you must install OpenSSL. 这是 mssql 扩展使用 .Net Core 的先决条件。This is a pre-requisite for .Net Core used by the mssql extension. 请按照安装必备中的步骤.Net 核心说明Follow the install pre-requisite steps in the .Net Core instructions. 也可在 macOS 终端运行以下命令。Or, you can run the following commands in your macOS Terminal.

    brew update
    brew install openssl
    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/
    

    备注

    对于 Windows 8.1、 Windows Server 2012 或更低版本,你必须下载并安装Windows 10 通用 C 运行时For Windows 8.1, Windows Server 2012 or lower versions, you must download and install the Windows 10 Universal C Runtime. 下载并打开 zip 文件。Download and open the zip file. 然后根据当前 OS 配置运行安装程序(.msu 文件)。Then run the installer (.msu file) targeting your current OS configuration.

创建或打开 SQL 文件Create or open a SQL file

Mssql扩展使 mssql 命令和 T-SQL 的 intellisense 功能在编辑器中的语言模式设置为时SQLThe mssql extension enables mssql commands and T-SQL IntelliSense in the editor when the language mode is set to SQL.

  1. CTRL + NPress CTRL+N. 默认情况下,Visual Studio Code 将打开一个新的“纯文本”文件。Visual Studio Code opens a new 'Plain Text' file by default.

  2. CTRL + K、 M并更改到语言模式SQLPress CTRL+K,M and change the language mode to SQL.

    SQL language mode

  3. 也可使用 .sql 文件扩展打开现有文件。Alternatively, open an existing file with .sql file extension. 语言模式,则自动SQL扩展名为.sql 的文件。The language mode is automatically SQL for files that have the .sql extension.

连接到 SQL ServerConnect to SQL Server

以下步骤演示了如何使用 VS Code 连接到 SQL Server。The following steps show how to connect to SQL Server with VS Code.

  1. 在 VS Code 中,按 CTRL+SHIFT+P(或 F1)打开命令面板。In VS Code, press CTRL+SHIFT+P (or F1) to open the Command Palette.

  2. 类型sql以显示 mssql 命令。Type sql to display the mssql commands.

    mssql commands

  3. 选择MS SQL: 连接命令。Select the MS SQL: Connect command. 你可以只需键入sqlconENTERYou can simply type sqlcon and press ENTER.

  4. 选择创建连接配置文件Select Create Connection Profile. 这将为 SQL Server 实例创建连接配置文件。This creates a connection profile for your SQL Server instance.

  5. 按照提示为新连接配置文件指定连接属性。Follow the prompts to specify the connection properties for the new connection profile. 指定每个值后,按 ENTER 继续。After specifying each value, press ENTER to continue.

    下表描述了连接配置文件的属性。The following table describes the Connection Profile properties.

    设置Setting DescriptionDescription
    服务器名称Server name SQL Server 实例名称。The SQL Server instance name. 对于本教程中,使用localhost连接到您的计算机上的本地 SQL Server 实例。For this tutorial, use localhost to connect to the local SQL Server instance on your machine. 如果要连接到远程 SQL Server,请输入目标 SQL Server 计算机的名称,或它的 IP 地址。If connecting to a remote SQL Server, enter the name of the target SQL Server machine or its IP address. 如果你需要指定 SQL Server 实例的端口,使用逗号分隔的名称。If you need to specify a port for your SQL Server instance, use a comma to separate it from the name. 例如对于本地服务器在端口 1401年上运行你将输入localhost,1401年For example for a local server running on port 1401 you would enter localhost,1401.
    [可选]数据库名称[Optional] Database name 要使用的数据库。The database that you want to use. 对于此教程的目的,不指定数据库和按ENTER以继续。For purposes of this tutorial, don't specify a database and press ENTER to continue.
    用户名User name 输入拥有访问服务器上数据库权限的用户名。Enter the name of a user with access to a database on the server. 对于本教程中,使用默认SA SQL Server 安装过程中创建的帐户。For this tutorial, use the default SA account created during the SQL Server setup.
    密码(SQL 登录名)Password (SQL Login) 输入指定用户的密码。Enter the password for the specified user.
    是否保存密码?Save Password? 类型保存密码。Type Yes to save the password. 否则,请键入,在每次使用时连接配置文件的密码会提示您。Otherwise, type No to be prompted for the password each time the Connection Profile is used.
    [可选]输入此配置文件的名称[Optional] Enter a name for this profile 连接配置文件名称。The Connection Profile name. 例如,无法将该配置文件localhost 配置文件For example, you could name the profile localhost profile.

    提示

    可在用户设置文件 (settings.json) 中创建和编辑连接配置文件。You can create and edit connection profiles in User Settings file (settings.json). 通过选择打开设置文件首选项然后用户设置VS Code 菜单中。Open the settings file by selecting Preference and then User Settings in the VS Code menu. 有关详细信息,请参阅管理连接配置文件For more information, see manage connection profiles.

  6. 按 ESC 键关闭提示配置文件已创建并连接的提示消息。Press the ESC key to close the info message that informs you that the profile is created and connected.

    提示

    如果你获取连接失败,请首先尝试诊断中的错误消息从问题输出在 VS Code 的面板 (选择输出视图菜单)。If you get a connection failure, first attempt to diagnose the problem from the error message in the Output panel in VS Code (select Output on the View menu). 然后查看连接故障排除建议Then review the connection troubleshooting recommendations.

  7. 在状态栏中验证连接。Verify your connection in the status bar.

    Connection status

创建数据库Create a database

  1. 在编辑器中,键入sql弹出的可编辑的代码段的列表。In the editor, type sql to bring up a list of editable code snippets.

    SQL snippets

  2. 选择sqlCreateDatabaseSelect sqlCreateDatabase.

  3. 在代码段中,键入TutorialDB数据库名称。In the snippet, type TutorialDB for the database name.

    USE master
    GO
    IF NOT EXISTS (
       SELECT name
       FROM sys.databases
       WHERE name = N'TutorialDB'
    )
    CREATE DATABASE [TutorialDB]
    GO
    
  4. CTRL + SHIFT + E执行 TRANSACT-SQL 命令。Press CTRL+SHIFT+E to execute the Transact-SQL commands. 在查询窗口中查看结果。View the results in the query window.

    create database messages

    提示

    可以自定义为 mssql 扩展命令绑定的快捷键。You can customize shortcut key bindings for the mssql extension commands. 请参阅自定义快捷键See customize shortcuts.

创建表Create a table

  1. 删除编辑器窗口中的内容。Remove the contents of the editor window.

  2. F1以显示命令控制板。Press F1 to display the Command Palette.

  3. 类型sql中要显示的 SQL 命令或类型的命令调色板sqluseMS SQL:Use 数据库命令。Type sql in the Command Palette to display the SQL commands or type sqluse for MS SQL:Use Database command.

  4. 单击MS SQL:Use 数据库,然后选择TutorialDB数据库。Click MS SQL:Use Database, and select the TutorialDB database. 此操作会将上下文更改为上一节中创建的新数据库。This changes the context to the new database created in the previous section.

    use database

  5. 在编辑器中,键入sql以显示这些代码段,然后选择sqlCreateTable输入In the editor, type sql to display the snippets, and then select sqlCreateTable and press enter.

  6. 在代码段中,键入员工为表名。In the snippet, type Employees for the table name.

  7. 选项卡,然后键入dbo架构名称。Press Tab, and then type dbo for the schema name.

    备注

    添加片段之后,必须键入表名和架构名,而无效从 VS Code 编辑器中更改焦点。After adding the snippet, you must type the table and schema names without changing focus away from the VS Code editor.

  8. 更改的列名称Column1名称Column2位置Change the column name for Column1 to Name and Column2 to Location.

    -- Create a new table called 'Employees' in schema 'dbo'
    -- Drop the table if it already exists
    IF OBJECT_ID('dbo.Employees', 'U') IS NOT NULL
    DROP TABLE dbo.Employees
    GO
    -- Create the table in the specified schema
    CREATE TABLE dbo.Employees
    (
       EmployeesId        INT    NOT NULL   PRIMARY KEY, -- primary key column
       Name      [NVARCHAR](50)  NOT NULL,
       Location   [NVARCHAR](50)  NOT NULL
    );
    GO
    
  9. CTRL + SHIFT + E以创建的表。Press CTRL+SHIFT+E to create the table.

插入和查询Insert and query

  1. 添加以下语句插入到四个行员工表。Add the following statements to insert four rows into the Employees table. 然后选择所有行。Then select all the rows.

    -- Insert rows into table 'Employees'
    INSERT INTO Employees
       ([EmployeesId],[Name],[Location])
    VALUES
       ( 1, N'Jared', N'Australia'),
       ( 2, N'Nikita', N'India'),
       ( 3, N'Tom', N'Germany'),
       ( 4, N'Jake', N'United States')   
    GO   
    -- Query the total count of employees
    SELECT COUNT(*) as EmployeeCount FROM dbo.Employees;
    -- Query all employee information
    SELECT e.EmployeesId, e.Name, e.Location 
    FROM dbo.Employees as e
    GO
    

    提示

    键入时,可使用 T-SQL IntelliSense 协助。While you type, use the assistance of the T-SQL IntelliSense. TSQL IntelliSense

  2. CTRL + SHIFT + E执行命令。Press CTRL+SHIFT+E to execute the commands. 这两个结果集显示在结果窗口。The two result sets display in the Results window.

    Results

查看并保存结果View and save the result

  1. 视图菜单上,选择切换编辑器组布局以切换到垂直或水平拆分布局。On the View menu, select Toggle Editor Group Layout to switch to vertical or horizontal split layout.

    Vertical split

  2. 单击结果消息面板标头以折叠和展开面板。Click the Results and Messages panel header to collapse and expand the panel.

    Toggle Messages

    提示

    可以自定义 mssql 扩展的默认行为。You can customize the default behavior of the mssql extension. 请参阅自定义扩展选项See customize extension options.

  3. 单击第二个结果网格上的最大化网格图标放大网格。Click the maximize grid icon on the second result grid to zoom in.

    Maximize grid

    备注

    T-SQL 脚本具有两个或多个结果网格时,会显示最大化图标。The maximize icon displays when your T-SQL script has two or more result grids.

  4. 使用网格上的鼠标右键打开网格上下文菜单。Open the grid context menu with the right mouse button on a grid.

    Context menu

  5. 选择选择所有Select Select All.

  6. 打开网格上下文菜单,然后选择将另存为 JSON将结果保存到的.json 文件。Open the grid context menu and select Save as JSON to save the result to a .json file.

  7. 为 JSON 文件指定文件名。Specify a file name for the JSON file. 对于本教程中,键入employees.jsonFor this tutorial, type employees.json.

  8. 验证 JSON 文件是否已保存,是否已在 VS Code 中打开。Verify that the JSON file is saved and opened in VS Code.

    Save as Json

后续步骤Next steps

在实际情况中,你可能会创建一个稍后要保存和运行的脚本(用于管理或作为大型开发项目的一部分)。In a real-world scenario, you might create a script that you need to save and run later (either for administration or as part of a larger development project). 在这种情况下,你可以将使用脚本保存.sql扩展。In this case, you can save the script with a .sql extension.

如果你不熟悉 T-SQL 的请参阅教程: 编写 TRANSACT-SQL 语句TRANSACT-SQL 参考 (数据库引擎)If you're new to T-SQL, see Tutorial: Writing Transact-SQL Statements and the Transact-SQL Reference (Database Engine).

使用或导致 mssql 扩展的详细信息,请参阅mssql 扩展项目 wikiFor more information on using or contributing to the mssql extension, see the mssql extension project wiki.

使用 VS Code 的详细信息,请参阅Visual Studio Code 文档For more information on using VS Code, see the Visual Studio Code documentation.