快速入门:使用 Azure 门户的查询编辑器(预览)查询 Azure SQL 数据库Quickstart: Use the Azure portal's query editor (preview) to query an Azure SQL Database

适用于: Azure SQL 数据库

查询编辑器是 Azure 门户中的一个工具,用于针对 Azure SQL 数据库中的数据库或 Azure Synapse Analytics 中的数据仓库运行 SQL 查询。The query editor is a tool in the Azure portal for running SQL queries against your database in Azure SQL Database or data warehouse in Azure Synapse Analytics.

在本快速入门中,你将使用查询编辑器针对数据库运行 Transact-SQL (T-SQL) 查询。In this quickstart, you'll use the query editor to run Transact-SQL (T-SQL) queries against a database.

先决条件Prerequisites

使用示例数据创建数据库Create a database with sample data

需要 AdventureWorksLT 示例数据库才能完成本快速入门。Completing this quickstart requires the AdventureWorksLT sample database. 如果在 SQL 数据库中没有 AdventureWorksLT 示例数据库的有效副本,请参阅以下快速入门来快速创建一个:If you don't have a working copy of the AdventureWorksLT sample database in SQL Database, the following quickstart helps you quickly create one:

快速入门:使用 Azure 门户、PowerShell 或 Azure CLI 创建 Azure SQL 数据库中的数据库Quickstart: Create a database in Azure SQL Database using the Azure portal, PowerShell, or Azure CLI

为服务器设置一个 Azure Active Directory 管理员(可选)Set an Azure Active Directory admin for the server (optional)

配置一个 Azure Active Directory (Azure AD) 管理员即可使用单个标识登录到 Azure 门户和数据库。Configuring an Azure Active Directory (Azure AD) administrator enables you to use a single identity to sign in to the Azure portal and your database. 如果要使用 Azure AD 连接到查询编辑器,请执行以下步骤。If you would like to use Azure AD to connect to query editor, follow the below steps.

此过程是可选的,你可以改用 SQL 身份验证来连接到查询编辑器。This process is optional, you can instead use SQL authentication to connect to the query editor.

备注

  • Azure AD 管理员尚不支持电子邮件帐户(例如,outlook.com、gmail.com、yahoo.com 等)。Email accounts (for example, outlook.com, gmail.com, yahoo.com, and so on) aren't yet supported as Azure AD admins. 请确保选择在 Azure AD 中本机创建或联合到 Azure AD 中的用户。Make sure to choose a user created either natively in the Azure AD or federated into the Azure AD.
  • Azure AD 管理员登录使用那些启用了双重身份验证的帐户,但查询编辑器不支持双重身份验证。Azure AD admin sign in works with accounts that have 2-factor authentication enabled, but the query editor does not support 2-factor authentication.
  1. 在 Azure 门户中,导航到 SQL 数据库服务器。In the Azure portal, navigate to your SQL database server.

  2. 在“SQL Server”菜单中选择“Active Directory 管理员”。 On the SQL server menu, select Active Directory admin.

  3. 在 SQL Server 的“Active Directory 管理员”页工具栏上,选择“设置管理员” 。On the SQL Server Active Directory admin page toolbar, select Set admin.

    选择 active directory

  4. 在“添加管理员”页上的搜索框中,输入要查找的用户或组,选择其作为管理员,然后选择“选择”按钮。 On the Add admin page, in the search box, enter a user or group to find, select it as an admin, and then choose the Select button.

  5. 返回 SQL Server“Active Directory 管理员”页工具栏中,选择“保存” 。Back in the SQL Server Active Directory admin page toolbar, select Save.

使用 SQL 查询编辑器Using SQL Query Editor

  1. 登录到 Azure 门户并选择要查询的数据库。Sign in to the Azure portal and select the database you want to query.

  2. 在“SQL 数据库”菜单中,选择“查询编辑器(预览)” 。In the SQL database menu, select Query editor (preview).

    查找查询编辑器

与数据库建立连接Establish a connection to the database

即使已登录到门户,也仍需提供凭据才能访问数据库。Even though you're signed into the portal, you still need to provide credentials to access the database. 可以使用 SQL 身份验证或 Azure Active Directory 连接到数据库。You can connect using SQL authentication or Azure Active Directory to connect to your database.

使用 SQL 身份验证进行连接Connect using SQL Authentication

  1. 在“登录”页上的“SQL Server 身份验证”下,输入有权访问数据库的用户的 登录名密码In the Login page, under SQL server authentication, enter a Login and Password for a user that has access to the database. 如果你不确定,请使用数据库服务器的服务器管理员的登录名和密码。If you're not sure, use the login and password for the Server admin of the database's server.

    登录

  2. 选择“确定” 。Select OK.

使用 Azure Active Directory 进行连接Connect using Azure Active Directory

在“查询编辑器(预览)”中,查看“登录”页上的“Active Directory 身份验证”部分。In the Query editor (preview), look at the Login page at the Active Directory authentication section. 将会自动进行身份验证。因此,如果你是数据库的 Azure AD 管理员,则会看到一条消息,指出你已登录。Authentication will happen automatically, so if you are an Azure AD admin to the database you will see a message appear saying you have been signed in. 然后选择“以 <your user or group ID> 继续”按钮。Then select the Continue as <your user or group ID> button. 如果页面中指出你未成功登录,可能需要刷新页面。If the page indicates that you have not successfully logged in, you may need to refresh the page.

在 SQL 数据库中查询数据库Query a database in SQL Database

以下示例查询应该针对 AdventureWorksLT 示例数据库成功运行。The following example queries should run successfully against the AdventureWorksLT sample database.

运行 SELECT 查询Run a SELECT query

  1. 将以下查询粘贴到查询编辑器中:Paste the following query into the query editor:

     SELECT TOP 20 pc.Name as CategoryName, p.name as ProductName
     FROM SalesLT.ProductCategory pc
     JOIN SalesLT.Product p
     ON pc.productcategoryid = p.productcategoryid;
    
  2. 选择“运行”,然后查看“结果”窗格中的输出。 Select Run and then review the output in the Results pane.

    查询编辑器结果

  3. (可选)可将查询另存为 .sql 文件,或者将返回的数据导出为 .json、.csv 或 .xml 文件。Optionally, you can save the query as a .sql file, or export the returned data as a .json, .csv, or .xml file.

运行 INSERT 查询Run an INSERT query

运行以下 INSERT T-SQL 语句,以将新产品添加到 SalesLT.Product 表中。Run the following INSERT T-SQL statement to add a new product in 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. 选择“运行”在 Product 表中插入新行。Select Run to insert a new row in the Product table. “消息”窗格显示“查询已成功:受影响的行:1” The Messages pane displays Query succeeded: Affected rows: 1.

运行 UPDATE 查询Run an UPDATE query

运行以下 UPDATE T-SQL 语句以修改新产品。Run the following UPDATE T-SQL statement to modify your new product.

  1. 将之前的查询替换为此查询。Replace the previous query with this one.

    UPDATE [SalesLT].[Product]
    SET [ListPrice] = 125
    WHERE Name = 'myNewProduct';
    
  2. 选择“运行”更新 Product 表中的指定行。Select Run to update the specified row in the Product table. “消息”窗格显示“查询已成功:受影响的行:1” The Messages pane displays Query succeeded: Affected rows: 1.

运行 DELETE 查询Run a DELETE query

运行以下 DELETE T-SQL 语句以删除新产品。Run the following DELETE T-SQL statement to remove your new product.

  1. 将之前的查询替换为此查询:Replace the previous query with this one:

    DELETE FROM [SalesLT].[Product]
    WHERE Name = 'myNewProduct';
    
  2. 选择“运行”删除 Product 表中的指定行。Select Run to delete the specified row in the Product table. “消息”窗格显示“查询已成功:受影响的行:1” The Messages pane displays Query succeeded: Affected rows: 1.

疑难解答和注意事项Troubleshooting and considerations

使用查询编辑器时,需要注意以下要点。There are a few things to know when working with the query editor.

配置本地网络设置Configure local network settings

如果查询编辑器中出现以下错误之一:If you get one of the following errors in the query editor:

  • “本地网络设置可能正在阻止查询编辑器发出查询。请单击此处了解有关如何配置网络设置的说明”Your local network settings might be preventing the Query Editor from issuing queries. Please click here for instructions on how to configure your network settings
  • “无法与服务器建立连接。这可能表示本地防火墙配置或网络代理设置存在问题”A connection to the server could not be established. This might indicate an issue with your local firewall configuration or your network proxy settings

这是因为查询编辑器使用端口 443 和 1443 进行通信。This is because the query editor uses port 443 and 1443 to communicate. 你需要确保已在这些端口上启用出站 HTTPS 流量。You will need to ensure you have enabled outbound HTTPS traffic on these ports. 下面的说明会指导你完成此操作,具体取决于你的操作系统。The instructions below will walk you through how to do this, depending on your Operating System. 你可能需要与公司 IT 人员协作,以便授予在本地网络上打开此连接的权限。You might need to work with your corporate IT to grant approval to open this connection on your local network.

适用于 Windows 的步骤Steps for Windows

  1. 打开 Windows Defender 防火墙Open Windows Defender Firewall
  2. 在左侧菜单上,选择“高级设置”On the left-side menu, select Advanced settings
  3. 在“高级安全 Windows Defender 防火墙”中的左侧菜单上,选择“出站规则”。 In Windows Defender Firewall with Advanced Security, select Outbound rules on the left-side menu.
  4. 选择右侧菜单上的“新建规则...”Select New Rule... on the right-side menu

在“新建出站规则向导”中,执行以下步骤:In the New outbound rule wizard follow these steps:

  1. 选择“端口”作为要创建的规则的类型。Select port as the type of rule you want to create. 选择“下一步”Select Next
  2. 选择“TCP”Select TCP
  3. 选择“特定远程端口”,并输入“443, 1443”。Select Specific remote ports and enter "443, 1443". 然后,选择“下一步”Then select Next
  4. 选择“仅允许安全的连接”Select "Allow the connection if it is secure"
  5. 选择“下一步”,然后再次选择“下一步” Select Next then select Next again
  6. 让“域”、“专用”和“公用”都保持选定状态Keep "Domain", "Private", and "Public" all selected
  7. 为规则提供一个名称,例如“访问 Azure SQL 查询编辑器”,并根据需要提供说明。Give the rule a name, for example "Access Azure SQL query editor" and optionally a description. 然后选择“完成”Then select Finish

适用于 Mac 的步骤Steps for Mac

  1. 打开“系统首选项”(Apple 菜单 >“系统首选项”)。Open System Preferences (Apple menu > System Preferences).
  2. 单击“安全和隐私”。Click Security & Privacy.
  3. 单击“防火墙”。Click Firewall.
  4. 如果防火墙处于禁用状态,请选择底部的“单击此锁进行更改”,然后选择“启用防火墙”If Firewall is off, select Click the lock to make changes at the bottom and select Turn on Firewall
  5. 单击“防火墙选项”。Click Firewall Options.
  6. 在“安全和隐私”窗口中选择此选项:“自动允许已签名的软件接收传入连接。”In the Security & Privacy window select this option: "Automatically allow signed software to receive incoming connections."

适用于 Linux 的步骤Steps for Linux

运行以下命令以更新 iptablesRun these commands to update iptables

sudo iptables -A OUTPUT -p tcp --dport 443 -j ACCEPT
sudo iptables -A OUTPUT -p tcp --dport 1443 -j ACCEPT

连接注意事项Connection considerations

  • 对于与查询编辑器的公共连接,你需要将出站 IP 地址添加到服务器已允许的防火墙规则中,以访问数据库和数据仓库。For public connections to query editor, you need to add your outbound IP address to the server's allowed firewall rules to access your databases and data warehouses.

  • 如果你在服务器上设置了专用链接连接,并且是从专用虚拟网络中的 IP 连接到查询编辑器,则查询编辑器将正常工作,无需你将客户端 IP 地址添加到 SQL 数据库服务器防火墙规则中。If you have a Private Link connection set up on the server and you are connecting to query editor from an IP in the private Virtual Network, the Query Editor works without needing to add the Client IP address into the SQL database server firewall rules.

  • 使用查询编辑器所需的最基本 RBAC 权限是对服务器和数据库的“读取”访问权限。The most basic RBAC permissions needed to use query editor are Read access to the server and database. 具有此级别访问权限的任何人都可以访问查询编辑器功能。Anyone with this level of access can access the query editor feature. 若要限制特定用户的访问,必须阻止他们使用 Azure Active Directory 或 SQL 身份验证凭据登录到查询编辑器。To limit access to particular users, you must prevent them from being able to sign in to the query editor with Azure Active Directory or SQL authentication credentials. 如果他们无法将自己分配为服务器的 AAD 管理员,或者无法访问/添加 SQL 管理员帐户,则他们将不能使用查询编辑器。If they cannot assign themselves as the AAD admin for the server or access/add a SQL administrator account, they should not be able to use query editor.

  • 查询编辑器不支持连接到 master 数据库。Query editor doesn't support connecting to the master database.

  • 查询编辑器无法使用 ApplicationIntent=ReadOnly 连接到副本数据库Query editor cannot connect to a replica database with ApplicationIntent=ReadOnly

  • 如果看到了“无法验证 X-CSRF-Signature 标头”错误消息,请执行以下操作来解决问题:If you saw this error message "The X-CSRF-Signature header could not be validated", take the following action to resolve the issue:

    • 请确保将计算机的时钟设置为正确的时间和时区。Make sure your computer's clock is set to the right time and time zone. 你还可以尝试通过搜索实例位置所在的时区,将计算机的时区与 Azure 匹配。You can also try to match your computer's time zone with Azure by searching for the time zone for the location of your instance.
    • 如果使用的是代理网络,请确保未修改或删除请求标头“X-CSRF-Signature”。If you are on a proxy network, make sure that the request header "X-CSRF-Signature" is not being modified or dropped.

其他注意事项Other considerations

  • F5 刷新查询编辑器页,任何正在处理的查询都将丢失。Pressing F5 refreshes the query editor page and any query being worked on is lost.

  • 查询执行的超时为 5 分钟。There's a 5-minute timeout for query execution.

  • 查询编辑器仅支持地理数据类型的柱面投影。The query editor only supports cylindrical projection for geography data types.

  • 数据库表和视图不支持 IntelliSense,但编辑器支持自动补全已键入的名称。There's no support for IntelliSense for database tables and views, but the editor does support autocomplete on names that have already been typed.

后续步骤Next steps

若要详细了解 Azure SQL 数据库支持的 Transact-SQL (T-SQL),请参阅解决迁移到 SQL 数据库过程中的 Transact-SQL 差异To learn more about the Transact-SQL (T-SQL) supported in Azure SQL Database, see Resolving Transact-SQL differences during migration to SQL Database.