快速入门:使用 SQL Server Management Studio 连接和查询 Azure SQL 数据库Quickstart: Use SQL Server Management Studio to connect and query an Azure SQL database

在本快速入门中,将使用 SQL Server Management Studio (SSMS) 连接到 Azure SQL 数据库。In this quickstart, you'll use SQL Server Management Studio (SSMS) to connect to an Azure SQL database. 然后,将运行 Transact-SQL 语句来查询、插入、更新和删除数据。You'll then run Transact-SQL statements to query, insert, update, and delete data. 可以使用 SSMS 管理任何 SQL 基础结构(从适用于 Microsoft Windows 的 SQL Server 到 SQL 数据库,不一而足)。You can use SSMS to manage any SQL infrastructure, from SQL Server to SQL Database for Microsoft Windows.

先决条件Prerequisites

Azure SQL 数据库。An Azure SQL database. 可以根据下述快速入门之一,在 Azure SQL 数据库中创建数据库,然后对其进行配置:You can use one of these quickstarts to create and then configure a database in Azure SQL Database:

单一数据库Single database 托管实例Managed instance
创建Create PortalPortal PortalPortal
CLICLI CLICLI
PowerShellPowerShell PowerShellPowerShell
配置Configure 服务器级别 IP 防火墙规则Server-level IP firewall rule 从 VM 进行连接Connectivity from a VM
从现场进行连接Connectivity from on-site
加载数据Load data 根据快速入门加载的 Adventure WorksAdventure Works loaded per quickstart 还原 Wide World ImportersRestore Wide World Importers
GitHub 所提供的 BACPAC 文件还原或导入 Adventure WorksRestore or import Adventure Works from BACPAC file from GitHub

重要

本文中脚本的编写目的是使用 Adventure Works 数据库。The scripts in this article are written to use the Adventure Works database. 使用托管实例时,必须将 Adventure Works 数据库导入一个实例数据库,或者修改本文中的脚本,以便使用 Wide World Importers 数据库。With a managed instance, you must either import the Adventure Works database into an instance database or modify the scripts in this article to use the Wide World Importers database.

安装最新的 SSMSInstall the latest SSMS

在开始之前,请确保已安装最新的 SSMSBefore you start, make sure you've installed the latest SSMS.

获取 SQL Server 连接信息Get SQL server connection information

获取连接到 Azure SQL 数据库所需的连接信息。Get the connection information you need to connect to the 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. 在“概览”页中,查看单一数据库的“服务器名称”旁边的完全限定的服务器名称,或者托管实例的“主机”旁边的完全限定的服务器名称 。On the Overview page, review the fully qualified server name next to Server name for a single database or the fully qualified server name next to Host for a managed instance. 若要复制服务器名称或主机名称,请将鼠标悬停在其上方,然后选择“复制”图标 。To copy the server name or host name, hover over it and select the Copy icon.

连接到数据库Connect to your database

在 SMSS 中,连接到 Azure SQL 数据库服务器。In SMSS, connect to your Azure SQL Database server.

重要

Azure SQL 数据库服务器在端口 1433 上进行侦听。An Azure SQL Database server listens on port 1433. 若要从公司防火墙后连接到 SQL 数据库服务器,该防火墙必须打开此端口。To connect to a SQL Database server from behind a corporate firewall, the firewall must have this port open.

  1. 打开 SSMS。Open SSMS. 此时会显示“连接到服务器” 对话框。The Connect to Server dialog box appears.

  2. 输入以下信息:Enter the following information:

    设置Setting     建议的值Suggested value 说明Description
    服务器类型Server type 数据库引擎Database engine 所需的值。Required value.
    服务器名称Server name 完全限定的服务器名称The fully qualified server name 类似于:mynewserver20170313.database.chinacloudapi.cnSomething like: mynewserver20170313.database.chinacloudapi.cn.
    身份验证Authentication SQL Server 身份验证SQL Server Authentication 本教程使用 SQL 身份验证。This tutorial uses SQL Authentication.
    登录名Login 服务器管理员帐户用户 IDServer admin account user ID 用于创建服务器的服务器管理员帐户的用户 ID。The user ID from the server admin account used to create the server.
    密码Password 服务器管理员帐户密码Server admin account password 用于创建服务器的服务器管理员帐户的密码。The password from the server admin account used to create the server.

    连接到服务器

  3. 选择“连接到服务器”对话框中的“选项” 。Select Options in the Connect to Server dialog box. 在“连接到数据库”下拉菜单中,选择 mySampleDatabase。如果将下拉菜单保留为默认值,则将连接到主数据库 。In the Connect to database drop-down menu, select mySampleDatabase.If you leave the drop down to default, the connection is made to master database.

    连接到服务器上的 DB

  4. 选择“连接” 。Select Connect. 此时会打开“对象资源管理器”窗口。The Object Explorer window opens.

  5. 要查看数据库的对象,请展开“数据库”,然后展开“mySampleDatabase” 。To view the database's objects, expand Databases and then expand mySampleDatabase.

    mySampleDatabase 对象

查询数据Query data

运行此 SELECT Transact-SQL 代码,按类别查询前 20 个产品。Run this SELECT Transact-SQL code to query for the top 20 products by category.

  1. 在对象资源管理器中,右键单击“mySampleDatabase”,并选择“新建查询” 。In Object Explorer, right-click mySampleDatabase and select New Query. 此时会打开一个连接到数据库的新查询窗口。A new query window connected to your database opens.

  2. 在查询窗口中,粘贴此 SQL 查询。In the query window, paste this SQL query.

    SELECT pc.Name as CategoryName, p.name as ProductName
    FROM [SalesLT].[ProductCategory] pc
    JOIN [SalesLT].[Product] p
    ON pc.productcategoryid = p.productcategoryid;
    
  3. 在工具栏上,选择“执行”,以便从 ProductProductCategory 表检索数据 。On the toolbar, select Execute to retrieve data from the Product and ProductCategory tables.

    从表 Product 和 ProductCategory 中检索数据的查询

插入数据Insert data

运行此 INSERT Transact-SQL 代码,在 SalesLT.Product 表中创建新产品。Run this INSERT Transact-SQL code to create 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 Execute to insert a new row in the Product table. “消息”面板显示“(受影响的 1 行)” 。The Messages pane displays (1 row affected).

查看结果View the result

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

    SELECT * FROM [SalesLT].[Product]
    WHERE Name='myNewProduct'
    
  2. 选择“执行” 。Select Execute. 将显示以下结果。The following result appears.

    Product 表查询的结果

更新数据Update data

运行此 UPDATE Transact-SQL 代码以修改新产品。Run this UPDATE Transact-SQL code 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 Execute to update the specified row in the Product table. “消息”面板显示“(受影响的 1 行)” 。The Messages pane displays (1 row affected).

删除数据Delete data

运行此 DELETE Transact-SQL 代码以删除新产品。Run this DELETE Transact-SQL code to remove your new product.

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

    DELETE FROM [SalesLT].[Product]
    WHERE Name = 'myNewProduct';
    
  2. 选择“执行”删除 Product 表中的指定行 。Select Execute to delete the specified row in the Product table. “消息”面板显示“(受影响的 1 行)” 。The Messages pane displays (1 row affected).

后续步骤Next steps