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

适用于:是Azure SQL 数据库 APPLIES TO: yesAzure SQL Database

查询编辑器是 Azure 门户中的一个工具,用于针对 Azure SQL 数据库中的数据库或 Azure SQL 数据仓库中的数据仓库运行 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 SQL Data Warehouse.

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

先决条件Prerequisites

需要 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 quickly creates one:

配置网络设置Configure 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, or A connection to the server could not be established. This might indicate an issue with your local firewall configuration or your network proxy settings, the following important information should help resolve:

重要

查询编辑器使用端口 443 和 1443 进行通信。The query editor uses ports 443 and 1443 to communicate. 确保已在这些端口上启用出站 HTTPS 流量。Ensure you have enabled outbound HTTPS traffic on these ports. 还需要将出站 IP 地址添加到服务器的允许防火墙规则中,以访问数据库和数据仓库。You also need to add your outbound IP address to the server's allowed firewall rules to access your databases and data warehouses.

打开 SQL 数据库查询编辑器Open the SQL Database 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

配置一个 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 连接到数据库,请遵循以下步骤配置 SQL Server 实例的 Azure AD 管理员。To connect to your database using Azure AD, follow the steps below to configure an Azure AD admin for your SQL Server instance.

备注

  • 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 doesn't work with accounts that have 2-factor authentication enabled.

为服务器设置 Active Directory 管理员Set an Active Directory admin for the server

  1. 在 Azure 门户中选择你的 SQL Server 实例。In the Azure portal, select your SQL Server instance.

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

  3. 在 SQL Server 的“Active Directory 管理员”页工具栏中选择“设置管理员”,然后选择充当 Azure AD 管理员的用户或组。 On the SQL Server Active Directory admin page toolbar, select Set admin and choose the user or group as your Azure AD 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.

连接到数据库Connect to the database

  1. 在“SQL Server”菜单中,选择“SQL 数据库”,然后选择数据库 。In the SQL server menu, select SQL databases, and then select your database.

  2. 在“SQL 数据库”菜单中,选择“查询编辑器(预览)” 。In the SQL database menu, select Query editor (preview). 在“登录”页面中的“Active Directory 身份验证”标签下,将显示一条消息,说明你已登录(如果你是 Azure AD 管理员) 。然后选择“以 <your user or group ID> 继续”按钮。In the Login page, under the Active Directory authentication label, a message appears saying you have been signed in if you're an Azure AD admin. 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.

查询编辑器注意事项Query editor considerations

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

  • 查询编辑器使用端口 443 和 1443 进行通信。The query editor uses ports 443 and 1443 to communicate. 确保已在这些端口上启用出站 HTTPS 流量。Ensure you have enabled outbound HTTPS traffic on these ports. 还需要将出站 IP 地址添加到服务器的允许防火墙规则中,以访问你的数据库和数据仓库。You will also need to add your outbound IP address to the server's allowed firewall rules to access your databases and data warehouses.

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

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

  • 查询执行的超时为 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.