教程:使用 SQL Server Management Studio (SSMS) 连接和查询 SQL Server 实例Tutorial: Connect to and query a SQL Server instance by using SQL Server Management Studio (SSMS)

本教程将指导如何使用 SQL Server Management Studio (SSMS) 连接到 SQL Server 实例以及运行一些基本的 Transact-SQL (T-SQL) 命令。This tutorial teaches you how to use SQL Server Management Studio (SSMS) to connect to your SQL Server instance and run some basic Transact-SQL (T-SQL) commands. 本文展示了如何按照以下步骤操作:The article demonstrates how to follow the below steps:

  • 连接到 SQL Server 实例Connect to a SQL Server instance
  • 创建数据库 ("TutorialDB")Create a database ("TutorialDB")
  • 在新数据库中创建表(“客户”)Create a table ("Customers") in your new database
  • 在新表中插入行Insert rows into your new table
  • 查询新表并查看结果Query the new table and view the results
  • 使用查询窗口表验证连接属性Use the query window table to verify your connection properties
  • 更改查询窗口连接到的服务器Change the server that your query window is connected to

先决条件Prerequisites

若要完成本教程,需要 SQL Server Management Studio 以及针对 SQL Server 实例的访问权限。To complete this tutorial, you need SQL Server Management Studio and access to a SQL Server instance.

如果不能访问 SQL Server 实例,请从以下链接选择平台。If you don't have access to a SQL Server instance, select your platform from the following links. 如果选择 SQL 身份验证,请使用 SQL Server 登录凭据。If you choose SQL Authentication, use your SQL Server login credentials.

连接到 SQL Server 实例Connect to a SQL Server instance

  1. 启动 SQL Server Management Studio。Start SQL Server Management Studio. 首次运行 SSMS 时,系统将打开“连接到服务器”窗口 。The first time you run SSMS, the Connect to Server window opens. 如未打开,可以选择“对象资源管理器” > “连接” > “数据库引擎” ,将其手动打开。If it doesn't open, you can open it manually by selecting Object Explorer > Connect > Database Engine.

    对象资源管理器中的“连接”链接

  2. 在“连接到服务器”窗口中,按照下表进行操作 :In the Connect to Server window, follow the list below:

    • 对于“服务器类型” ,选择“数据库引擎” (通常的默认选项)。For Server type, select Database Engine (usually the default option).

    • 对于“服务器名称”,输入 SQL Server 实例的名称 。For Server name, enter the name of your SQL Server instance. (本文使用主机名 NODE5 [NODE5\SQL2016ST] 上的实例名称 SQL2016ST。)如果不知道如何确定 SQL Server 实例的名称,请参阅使用 SSMS 的其他提示和技巧(This article uses the instance name SQL2016ST on the hostname NODE5 [NODE5\SQL2016ST].) If you're unsure how to determine your SQL Server instance name, see Additional tips and tricks for using SSMS.

    • 对于“身份验证”,选择“Windows 身份验证” 。For Authentication, select Windows Authentication. 本文使用 Windows 身份验证,但也支持 SQL Server 登录。This article uses Windows Authentication, but SQL Server login is also supported. 如果选择“SQL 登录” ,便会看到输入用户名和密码的提示。If you select SQL Login, you're prompted for a username and password. 有关身份验证类型的详细信息,请参阅连接到服务器(数据库引擎)For more information about authentication types, see Connect to the server (database engine).

    “服务器名称”字段与使用 SQL Server 实例的选项

    也可以通过选择“选项”来修改其他连接选项 。You can also modify additional connection options by selecting Options. 连接选项的示例包括你要连接到的数据库、连接超时值和网络协议。Examples of connection options are the database you're connecting to, the connection timeout value, and the network protocol. 本文对所有选项使用默认值。This article uses the default values for all the options.

  3. 完成所有字段后,选择“连接” 。After you've completed all the fields, select Connect.

成功连接的示例Examples of successful connections

可通过展开和浏览“对象资源管理器”中的对象,验证 SQL Server 连接是否成功 。To verify that your SQL Server connection succeeded, expand and explore the objects within Object Explorer. 这些对象因选择连接的服务器类型而异。These objects are different depending on the type of server you choose to connect.

  • 连接到本地 SQL Server,示例中为 NODE5\SQL2016ST:连接到本地服务器Connecting to an on-premises SQL server - in this case NODE5\SQL2016ST: Connecting to an on-premises server

  • 连接到 SQL Azure DB,示例中为 msftestserver.database.windows.net:连接到 SQL Azure DBConnecting to SQL Azure DB - in this case msftestserver.database.windows.net: Connecting to a SQL Azure DB

    备注

    在本教程中,之前已使用 Windows 身份验证连接到本地 SQL Server,但此方法不支持连接到 SQL Azure DB 。In this tutorial, you previously used Windows Authentication to connect to your on-premises SQL server, but this method is not supported for SQL Azure DB. 因此,此图像显示使用 SQL 身份验证连接到 SQL Azure DB。As such, this image shows using SQL Authentication to connect to the SQL Azure DB. 有关详细信息,请参阅 SQL 本地身份验证SQL Azure 身份验证For more information, see SQL on-premises authentication and SQL Azure authentication.

创建数据库Create a database

按照以下步骤,创建一个名为 TutorialDB 的数据库:Create a database named TutorialDB by following the below steps:

  1. 在“对象资源管理器”中右键单击服务器实例,然后选择“新建查询” :Right-click your server instance in Object Explorer, and then select New Query:

    “新建查询”链接

  2. 将以下 T-SQL 代码片段粘贴到查询窗口:Into the query window, paste the following T-SQL code snippet:

    USE master
    GO
    IF NOT EXISTS (
       SELECT name
       FROM sys.databases
       WHERE name = N'TutorialDB'
    )
    CREATE DATABASE [TutorialDB]
    GO
    
  3. 若要执行查询,请选择“执行”(或选择键盘上的 F5) 。To execute the query, select Execute (or select F5 on your keyboard).

    “执行”命令

    查询完成后,新的 TutorialDB 数据库会显示在“对象资源管理器”内的数据库列表中。After the query is complete, the new TutorialDB database appears in the list of databases in Object Explorer. 如未显示,请右键单击“数据库”节点,然后选择“刷新” 。If it isn't displayed, right-click the Databases node, and then select Refresh.

在新数据库中创建表Create a table in the new database

本部分中将在新创建的 TutorialDB 数据库中创建一个表。In this section, you create a table in the newly created TutorialDB database. 由于查询编辑器仍处于 master 数据库的上下文中,因此请按以下步骤操作,将连接上下文切换到 TutorialDB 数据库 :Because the query editor is still in the context of the master database, switch the connection context to the TutorialDB database by doing the following steps:

  1. 在数据库下拉列表中,选择所需数据库,如下所示:In the database drop-down list, select the database that you want, as shown here:

    更改数据库

  2. 将以下 T-SQL 代码片段粘贴到查询窗口,选择它,再选择“执行”(或选择键盘上的 F5)。 Paste the following T-SQL code snippet into the query window, select it, and then select Execute (or select F5 on your keyboard).
    可在查询窗口中替换现有文本或将其追加到末尾。You can either replace the existing text in the query window or append it to the end. 若要在查询窗口中执行所有文本,请选择“执行” 。To execute everything in the query window, select Execute. 如果已追加文本,不妨只执行部分文本,因此请突出显示相应部分,然后选择“执行” 。If you've appended the text, you want to execute just the portion of the text, so highlight that portion, and then select Execute.

    USE [TutorialDB]
    -- Create a new table called 'Customers' in schema 'dbo'
    -- Drop the table if it already exists
    IF OBJECT_ID('dbo.Customers', 'U') IS NOT NULL
    DROP TABLE dbo.Customers
    GO
    -- Create the table in the specified schema
    CREATE TABLE dbo.Customers
    (
       CustomerId        INT    NOT NULL   PRIMARY KEY, -- primary key column
       Name      [NVARCHAR](50)  NOT NULL,
       Location  [NVARCHAR](50)  NOT NULL,
       Email     [NVARCHAR](50)  NOT NULL
    );
    GO
    

查询完成后,新的“客户”表会显示在对象资源管理器内的表列表中。After the query is complete, the new Customers table is displayed in the list of tables in Object Explorer. 如果表未显示,请右键单击“对象资源管理器”中的“TutorialDB” > “表” 节点,并选择“刷新” 。If the table isn't displayed, right-click the TutorialDB > Tables node in Object Explorer, and then select Refresh.

将行插入新表Insert rows into the new table

将一些行插入前面创建的“客户”表。Insert some rows into the Customers table that you created previously. 将以下 T-SQL 代码片段粘贴到查询窗口并选择“执行”来完成此操作 :To do so, paste the following T-SQL code snippet into the query window, and then select Execute:

-- Insert rows into table 'Customers'
INSERT INTO dbo.Customers
   ([CustomerId],[Name],[Location],[Email])
VALUES
   ( 1, N'Orlando', N'Australia', N''),
   ( 2, N'Keith', N'India', N'keith0@adventure-works.com'),
   ( 3, N'Donna', N'Germany', N'donna0@adventure-works.com'),
   ( 4, N'Janet', N'United States', N'janet1@adventure-works.com')
GO

查询表并查看结果Query the table and view the results

查询结果在查询文本窗口下可见。The results of a query are visible below the query text window. 要查询客户表和查看以前插入的行,请按照以下步骤操作:To query the Customers table and view the rows that were previously inserted, follow these steps:

  1. 将以下 T-SQL 代码片段粘贴到查询窗口并选择“执行” :Paste the following T-SQL code snippet into the query window, and then select Execute:

    -- Select rows from table 'Customers'
    SELECT * FROM dbo.Customers;
    

    查询结果显示在输入文本的区域下:The results of the query are displayed under the area where the text was entered:

    “结果”列表

  2. 可以通过选择以下选项之一来修改结果的显示方式:Modify the way results are presented by selecting one of the following options:

    用于显示查询结果的三个选项

    • 中间的按钮采用“网格视图”显示结果,这是默认选项 。The middle button displays the results in Grid View, which is the default option.
    • 第一个按钮将在“文本视图”中显示结果,如下一部分中的图像所示 。The first button displays the results in Text View, as shown in the image in the next section.
    • 第三个按钮可将结果保存为默认扩展名是 .rpt 的文件。The third button lets you save the results to a file whose extension is .rpt by default.

使用查询窗口表验证连接属性Verify your connection properties by using the query window table

在查询结果下,可以找到有关连接属性的信息。You can find information about the connection properties under the results of your query. 在运行前一步骤中的上述查询后,查看查询窗口底部的连接属性。After you run the previously mentioned query in the preceding step, review the connection properties at the bottom of the query window.

  • 可以确定连接到的服务器和数据库,以及使用的用户名。You can determine which server and database you're connected to, and the username that you use.

  • 此外,还可以查看查询持续时间和之前执行的查询所返回的行数。You can also view the query duration and the number of rows that are returned by the previously executed query.

    连接属性

    备注

    在此图像中,结果显示在“文本视图”中 。In the image, the results are displayed in Text View.

根据查询窗口更改服务器Change the server based on the query window

通过执行以下步骤,可以更改当前查询窗口连接到的服务器:You can change the server that your current query window is connected to by following the steps below:

  1. 右键单击查询窗口,然后选择 “连接” > “更改连接”。Right-click in the query window, and then select Connection > Change connection. “连接到服务器” 窗口将再次打开。The Connect to Server window opens again.

  2. 更改查询使用的服务器。Change the server that your query uses.

    “更改连接”命令

    备注

    此操作仅更改查询窗口连接到的服务器,而不更改对象资源管理器使用的服务器。This action changes only the server that the query window is connected to, not the server that Object Explorer uses.

Azure Data StudioAzure Data Studio

也可以使用 Azure Data Studio 连接和查询 SQL ServerAzure SQL 数据库Azure SQL 数据仓库You can also connect and query SQL Server, an Azure SQL Database, and Azure SQL data warehouses using Azure Data Studio.

后续步骤Next steps

熟悉 SSMS 的最好方式是进行实践演练。The best way to get acquainted with SSMS is through hands-on practice. 这些文章可帮助你使用 SSMS 的各种功能。These articles help you with various features available within SSMS. 这些文章教你如何管理 SSMS 组件,以及如何查找常用功能。These articles teach you how to manage the components of SSMS and how to find the features that you use regularly.