使用 Visual Studio Code 创建并运行 Transact SQL 脚本Use Visual Studio Code to create and run Transact-SQL scripts

适用对象:是SQL Server(仅限 Linux)否Azure SQL 数据库 否Azure SQL 数据仓库 否并行数据仓库 APPLIES TO: yesSQL Server (Linux only) noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

本文介绍如何为 Visual Studio Code 使用“mssql”扩展来开发 SQL Server 数据库 。This article shows how to use the mssql extension for Visual Studio Code to develop SQL Server databases. 由于 Visual Studio Code 是跨平台的,因此可以在 Linux、macOS 和 Windows 上使用“mssql”扩展 。Because Visual Studio Code is cross-platform, you can use mssql extension on Linux, macOS, and Windows.

安装并启动 Visual Studio CodeInstall and start Visual Studio Code

Visual Studio Code 是支持扩展的跨平台图形代码编辑器。Visual Studio Code is a cross-platform, graphical code editor that supports extensions.

  1. 在计算机上下载并安装 Visual Studio CodeDownload and install Visual Studio Code on your machine.

  2. 启动 Visual Studio Code。Start Visual Studio Code.

    备注

    如果通过 xrdp 远程桌面会话连接时 Visual Studio Code 无法启动,请参阅使用 XRDP 连接时 VS Code 无法在 Ubuntu 上运行If Visual Studio Code does not start when you are connected through an xrdp remote desktop session, see VS Code not working on Ubuntu when connected using XRDP.

安装 mssql 扩展Install the mssql extension

适用于 Visual Studio Code 的 mssql 扩展允许你连接到 SQL Server,使用 Transact-SQL (T-SQL) 进行查询,并查看结果。The mssql extension for Visual Studio Code lets you connect to a SQL Server, query with Transact-SQL (T-SQL), and view the results.

  1. 在 Visual Studio Code 中,选择“查看” > “命令面板”,或按“Ctrl”+“Shift”+“P”,或按“F1”打开“命令面板” 。In Visual Studio Code, select View > Command Palette, or press Ctrl+Shift+P, or press F1 to open the Command Palette.

  2. 在命令面板中,从下拉列表中选择“扩展 :安装扩展” 。In the Command Palette, select Extensions: Install Extensions from the dropdown.

  3. 在“扩展”窗格中,键入“mssql” 。In the Extensions pane, type mssql.

  4. 选择“SQL Server (mssql)”扩展,然后选择“安装” 。Select the SQL Server (mssql) extension, and then select Install.

    安装 mssql 扩展

  5. 安装完成后,选择“重新加载”以启用扩展 。After the installation completes, select Reload to enable the extension.

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

当语言模式设置为“SQL”时,mssql 扩展将在代码编辑器中启用 mssql 命令和 T-SQL IntelliSense 。The mssql extension enables mssql commands and T-SQL IntelliSense in the code editor when the language mode is set to SQL.

  1. 选择“文件” > “新建文件”或按“Ctrl”+“N” 。Select File > New File or press Ctrl+N. 默认情况下,Visual Studio Code 将打开一个新的“纯文本”文件。Visual Studio Code opens a new Plain Text file by default.

  2. 在下方状态栏上选择“纯文本”,或按“Ctrl”+“K” > “M”,然后从“语言”下拉列表中选择“SQL” 。Select Plain Text on the lower status bar, or press Ctrl+K > M, and select SQL from the languages dropdown.

    SQL 语言模式

    备注

    如果这是你第一次使用该扩展,则该扩展会安装支持性的 SQL Server 工具。If this is the first time you have used the extension, the extension installs supporting SQL Server tools.

如果打开一个文件扩展名为 .sql 的现有文件,语言模式会自动设置为 SQL 。If you open an existing file that has a .sql file extension, the language mode is automatically set to SQL.

连接到 SQL ServerConnect to SQL Server

请按照以下步骤创建连接配置文件并连接到 SQL Server。Follow these steps to create a connection profile and connect to a SQL Server.

  1. 按“Ctrl”+“Shift”+“P”或“F1”打开“命令面板”。 。Press Ctrl+Shift+P or F1 to open the Command Palette.

  2. 键入 sql 以显示 mssql 命令,或键入 sqlcon,然后从下拉列表中选择“MS SQL :连接” 。Type sql to display the mssql commands, or type sqlcon, and then select MS SQL: Connect from the dropdown.

    mssql 命令

    备注

    代码编辑器中的焦点须位于 SQL 文件(例如创建的空 SQL 文件),才能执行 mssql 命令。A SQL file, such as the empty SQL file you created, must have focus in the code editor before you can execute the mssql commands.

  3. 选择“MS SQL:管理连接配置文件”命令 。Select the MS SQL: Manage Connection Profiles command.

  4. 然后选择“创建”为 SQL Server 创建新的连接配置文件 。Then select Create to create a new connection profile for your SQL Server.

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

    连接属性Connection property 描述Description
    服务器名称或 ADO 连接字符串Server name or ADO connection string 指定 SQL Server 实例名称。Specify the SQL Server instance name. 使用 localhost 连接到本地计算机上的 SQL Server 实例 。Use localhost to connect to a SQL Server instance on your local machine. 如果要连接到远程 SQL Server,请输入目标 SQL Server 的名称,或它的 IP 地址。To connect to a remote SQL Server, enter the name of the target SQL Server, or its IP address. 若要连接到 SQL Server 容器,请指定容器主机的 IP 地址。To connect to a SQL Server container, specify the IP address of the container's host machine. 如果需要指定端口,请使用逗号将其与名称分开。If you need to specify a port, use a comma to separate it from the name. 例如,对于侦听端口 1401 的服务器,请输入 <servername or IP>,1401For example, for a server listening on port 1401, enter <servername or IP>,1401.

    或者,可以在此处输入数据库的 ADO 连接字符串。As an alternative, you can enter the ADO connection string for your database here.
    “数据库名称”(可选) Database name (optional) 要使用的数据库。The database that you want to use. 若要连接到默认数据库,请不要在此处指定数据库名称。To connect to the default database, don't specify a database name here.
    身份验证类型Authentication Type 选择“集成”或“SQL 登录” 。Choose either Integrated or SQL Login.
    User nameUser name 如果选择了“SQL 登录”,则输入拥有访问服务器上数据库权限的用户名 。If you selected SQL Login, enter the name of a user with access to a database on the server.
    密码Password 输入指定用户的密码。Enter the password for the specified user.
    保存密码Save Password 按“Enter”选择“是”并保存密码 。Press Enter to select Yes and save the password. 选择“否”,系统将在每次使用连接配置文件时提示输入密码 。Select No to be prompted for the password each time the connection profile is used.
    “配置文件名称”(可选) Profile Name (optional) 键入连接配置文件的名称,例如 localhost 配置文件 。Type a name for the connection profile, such as localhost profile.

    输入所有值并选择“Enter”后,Visual Studio Code 将创建连接配置文件并连接到 SQL Server 。After you enter all values and select Enter, Visual Studio Code creates the connection profile and connects to the SQL Server.

    提示

    如果连接失败,请尝试通过 Visual Studio Code “输出”面板中的错误消息来诊断问题 。If the connection fails, try to diagnose the problem from the error message in the Output panel in Visual Studio Code. 要打开“输出”面板,请选择“查看” > “输出” 。To open the Output panel, select View > Output. 另外,请查看连接故障排除建议Also review the connection troubleshooting recommendations.

  6. 在下方的状态栏中验证连接。Verify your connection in the lower status bar.

    连接状态

作为前面步骤的替代方法,还可以在“用户设置”文件 (settings.json) 中创建和编辑连接配置文件 。As an alternative to the previous steps, you can also create and edit connection profiles in the User Settings file (settings.json). 若要打开设置文件,请选择“文件” > “首选项” > “设置” 。To open the settings file, select File > Preferences > Settings. 有关详细信息,请参阅管理连接配置文件For more information, see Manage connection profiles.

创建 SQL 数据库Create a SQL database

  1. 在先前启动的新 SQL 文件中,键入 sql 以显示可编辑的代码段的列表 。In the new SQL file that you started earlier, type sql to display a list of editable code snippets.

    SQL 代码段

  2. 选择“sqlCreateDatabase” 。Select sqlCreateDatabase.

  3. 在代码段中,键入 TutorialDB 以替换“DatabaseName”:In the snippet, type TutorialDB to replace 'DatabaseName':

    -- Create a new database called 'TutorialDB'
    -- Connect to the 'master' database to run this snippet
    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.

    创建数据库消息

    提示

    可以自定义 mssql 命令的快捷键。You can customize the shortcut keys for the mssql commands. 请参阅自定义快捷键See Customize shortcuts.

创建表Create a table

  1. 删除代码编辑器窗口中的内容。Delete the contents of the code editor window.

  2. 按“Ctrl”+“Shift”+“P”或“F1”打开“命令面板” 。Press Ctrl+Shift+P or F1 to open the Command Palette.

  3. 键入 sql 以显示 mssql 命令,或键入 sqluse,然后选择“MS SQL :使用数据库”命令 。Type sql to display the mssql commands, or type sqluse, and then select the MS SQL: Use Database command.

  4. 选择新的“TutorialDB”数据库 。Select the new TutorialDB database.

    使用数据库

  5. 在代码编辑器中,键入 sql 以显示片段,选择“sqlCreate Table”后按“Enter” 。In the code editor, type sql to display the snippets, select sqlCreateTable, and then press Enter.

  6. 在片段中,键入表名 EmployeesIn the snippet, type Employees for the table name.

  7. 按“Tab”键转到下一个字段,然后键入 dbo 作为架构名称 。Press Tab to get to the next field, and then type dbo for the schema name.

  8. 使用以下列替换列定义:Replace the column definitions with the following columns:

    EmployeesId INT NOT NULL PRIMARY KEY,
    Name [NVARCHAR](50)  NOT NULL,
    Location [NVARCHAR](50)  NOT NULL
    
  9. 按“Ctrl”+“Shift”+“E”可创建表 。Press Ctrl+Shift+E to create the table.

插入和查询Insert and query

  1. 添加下列语句,将四行插入“Employees”表 。Add the following statements to insert four rows into the Employees table.

    -- 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, T-SQL IntelliSense helps you to complete the statements:

    T-SQL IntelliSense

    提示

    mssql 扩展还包含可帮助创建 INSERT 和 SELECT 语句的命令。The mssql extension also has commands to help create INSERT and SELECT statements. 前面的示例中未使用这些命令。These were not used in the previous example.

  2. 按“Ctrl”+“Shift”+“E”执行命令 。Press Ctrl+Shift+E to execute the commands. 将在“结果”窗口中显示两个结果集 。The two result sets display in the Results window.

    结果

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

  1. 选择“视图” > “编辑器布局” > “翻转布局”以切换为垂直拆分或水平拆分的布局 。Select View > Editor Layout > Flip Layout to switch to a vertical or horizontal split layout.

  2. 选择“结果”和“消息”面板标头来折叠和展开面板 。Select the Results and Messages panel headers to collapse and expand the panels.

    切换标头

    提示

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

  3. 选择第二个结果网格上的最大化网格图标以放大这些结果。Select the maximize grid icon on the second result grid to zoom in to those results.

    最大化网格

    备注

    T-SQL 脚本产生两个或多个结果网格时,会出现最大化图标。The maximize icon displays when your T-SQL script produces two or more result grids.

  4. 右键单击网格,打开网格上下文菜单。Open the grid context menu by right-clicking on the grid.

    上下文菜单

  5. 选择“全选” 。Select Select All.

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

  7. 为 JSON 文件指定文件名。Specify a file name for the JSON file.

  8. 验证 JSON 文件是否在 Visual Studio Code 中保存和打开。Verify that the JSON file saves and opens in Visual Studio Code.

    另存为 JSON

如果以后需要保存和运行 SQL 脚本,用于管理用途或更大的开发项目,请使用 .sql 扩展名保存脚本 。If you need to save and run SQL scripts later, for administration or a larger development project, save the scripts with a .sql extension.

后续步骤Next steps

如果不熟悉 T-SQL,请参阅教程:编写 Transact-SQL 语句Transact-SQL 参考(数据库引擎)If you're new to T-SQL, see Tutorial: Write 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.

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