教程:使用 SSMS 在 Azure SQL 数据库的单一数据库中设计关系数据库Tutorial: Design a relational database in a single database within Azure SQL Database using SSMS

Azure SQL 数据库是云 (Azure) 中的关系数据库即服务 (DBaaS)。Azure SQL database is a relational database-as-a-service (DBaaS) in the Cloud (Azure). 本教程介绍如何使用 Azure 门户和 SQL Server Management Studio (SSMS) 执行以下操作:In this tutorial, you learn how to use the Azure portal and SQL Server Management Studio (SSMS) to:

  • 使用 Azure 门户创建单一数据库*Create a single database using the Azure portal*
  • 通过 Azure 门户设置服务器级 IP 防火墙规则Set up a server-level IP firewall rule using the Azure portal
  • 使用 SSMS 连接到数据库Connect to the database with SSMS
  • 使用 SSMS 创建表Create tables with SSMS
  • 使用 BCP 大容量加载数据Bulk load data with BCP
  • 使用 SSMS 查询数据Query data with SSMS

如果没有 Azure 订阅,请在开始前创建一个试用帐户If you don't have an Azure subscription, create a trial account before you begin.

Note

本教程使用单一数据库。For the purpose of this tutorial, we are using a single database. 还可以在弹性池中创建共用数据库。You could also use a pooled database in an elastic pool.

先决条件Prerequisites

要完成本教程,请确保已安装:To complete this tutorial, make sure you've installed:

登录到 Azure 门户Sign in to the Azure portal

登录到 Azure 门户Sign in to the Azure portal.

创建空的单一数据库Create a blank single database

创建 Azure SQL 数据库中的单一数据库时,会使用定义好的一组计算和存储资源。A single database in Azure SQL Database is created with a defined set of compute and storage resources. 数据库在 Azure 资源组中创建,使用数据库服务器进行托管。The database is created within an Azure resource group and is managed using an database server.

遵循以下步骤创建空白的单一数据库。Follow these steps to create a blank single database.

  1. 在 Azure 门户的左上角单击“创建资源”。Click Create a resource in the upper left-hand corner of the Azure portal.

  2. 在“新建”页上的“Azure 市场”部分中选择“数据库”,然后在“特别推荐”部分中单击“SQL 数据库”。On the New page, select Databases in the Azure Marketplace section, and then click SQL Database in the Featured section.

    创建空数据库

  3. 如上图所示,在“SQL 数据库”表单中填写以下信息:Fill out the SQL Database form with the following information, as shown on the preceding image:

    设置Setting       建议的值Suggested value 说明Description
    数据库名称Database name yourDatabaseyourDatabase 如需有效的数据库名称,请参阅数据库标识符For valid database names, see Database identifiers.
    订阅Subscription yourSubscriptionyourSubscription 有关订阅的详细信息,请参阅订阅For details about your subscriptions, see Subscriptions.
    资源组Resource group yourResourceGroupyourResourceGroup 有关有效的资源组名称,请参阅 Naming rules and restrictions(命名规则和限制)。For valid resource group names, see Naming rules and restrictions.
    选择源Select source 空白数据库Blank database 指定应创建空白数据库。Specifies that a blank database should be created.
  4. 单击“服务器”以使用现有的数据库服务器,或者创建并配置新的数据库服务器。Click Server to use an existing database server or create and configure a new database server. 选择现有服务器或单击“创建新服务器”,然后在“新建服务器”窗体中填写以下信息:Either select an existing server or click Create a new server and fill out the New server form with the following information:

    设置Setting       建议的值Suggested value 说明Description
    服务器名称Server name 任何全局唯一名称Any globally unique name 如需有效的服务器名称,请参阅 Naming rules and restrictions(命名规则和限制)。For valid server names, see Naming rules and restrictions.
    服务器管理员登录名Server admin login 任何有效的名称Any valid name 如需有效的登录名,请参阅Database Identifiers(数据库标识符)。For valid login names, see Database identifiers.
    密码Password 任何有效的密码Any valid password 密码必须至少有八个字符,且必须使用以下类别中的三个类别的字符:大写字符、小写字符、数字以及非字母数字字符。Your password must have at least eight characters and must use characters from three of the following categories: upper case characters, lower case characters, numbers, and non-alphanumeric characters.
    位置Location 任何有效的位置Any valid location 中国东部、中国东部 2、中国北部、中国北部 2China East,China East 2,China North,China North 2

    创建数据库 - 服务器

  5. 单击“选择”。Click Select.

  6. 单击“定价层”,指定服务层级、DTU 或 vCore 数,以及存储量。Click Pricing tier to specify the service tier, the number of DTUs or vCores, and the amount of storage. 可以浏览相关选项,了解每个服务层级可提供的 DTU/vCore 数和存储。You may explore the options for the number of DTUs/vCores and storage that is available to you for each service tier.

    选择服务层、DTU 数或 vCore 数以及存储量后,然后单击“应用”。After selecting the service tier, the number of DTUs or vCores, and the amount of storage, click Apply.

  7. 输入空白数据库的“排序规则”(就本教程来说,请使用默认值)。Enter a Collation for the blank database (for this tutorial, use the default value). 有关排序规则的详细信息,请参阅 Collations(排序规则)For more information about collations, see Collations

  8. 填写“SQL 数据库”窗体后,单击“创建”以预配单一数据库。Now that you've completed the SQL Database form, click Create to provision the single database. 这个步骤可能需要几分钟的时间。This step may take a few minutes.

  9. 在工具栏上,单击“通知”可监视部署过程。On the toolbar, click Notifications to monitor the deployment process.

    通知

创建服务器级 IP 防火墙规则Create a server-level IP firewall rule

SQL 数据库服务在服务器级别创建 IP 防火墙。The SQL Database service creates an IP firewall at the server-level. 此防火墙阻止外部应用程序和工具连接到服务器和服务器上的任何数据库,除非防火墙规则允许其 IP 通过防火墙。This firewall prevents external applications and tools from connecting to the server and any databases on the server unless a firewall rule allows their IP through the firewall. 若要启用与单一数据库的外部连接,必须首先为 IP 地址(或 IP 地址范围)添加 IP 防火墙规则。To enable external connectivity to your single database, you must first add an IP firewall rule for your IP address (or IP address range). 遵循这些步骤创建 SQL 数据库服务器级 IP 防火墙规则Follow these steps to create a SQL Database server-level IP firewall rule.

Important

SQL 数据库服务通过端口 1433 进行通信。The SQL Database service communicates over port 1433. 如果尝试从企业网络内部连接到此服务,则该网络的防火墙可能不允许经端口 1433 的出站流量。If you are trying to connect to this service from within a corporate network, outbound traffic over port 1433 may not be allowed by your network's firewall. 如果是这样,则无法连接到单一数据库,除非管理员打开端口 1433。If so, you cannot connect to your single database unless your administrator opens port 1433.

  1. 部署完成后,在左侧菜单中单击“SQL 数据库”,然后在“SQL 数据库”页上单击“yourDatabase”。After the deployment completes, click SQL databases from the left-hand menu and then click yourDatabase on the SQL databases page. 此时会打开数据库的概览页,显示完全限定的服务器名称(例如 yourserver.database.chinacloudapi.cn),并且会提供进行进一步配置所需的选项。The overview page for your database opens, showing you the fully qualified Server name (such as yourserver.database.chinacloudapi.cn) and provides options for further configuration.

  2. 复制此完全限定的服务器名称,将其用于从 SQL Server Management Studio 连接到服务器和数据库。Copy this fully qualified server name for use to connect to your server and databases from SQL Server Management Studio.

    服务器名称

  3. 单击工具栏上的“设置服务器防火墙”。Click Set server firewall on the toolbar. 此时会打开 SQL 数据库服务器的“防火墙设置”页。The Firewall settings page for the SQL Database server opens.

    服务器级别 IP 防火墙规则

  4. 在工具栏上单击“添加客户端 IP”,将当前的 IP 地址添加到新的 IP 防火墙规则。Click Add client IP on the toolbar to add your current IP address to a new IP firewall rule. IP 防火墙规则可以针对单个 IP 地址或一系列 IP 地址打开端口 1433。An IP firewall rule can open port 1433 for a single IP address or a range of IP addresses.

  5. 单击“保存” 。Click Save. 此时会针对当前的 IP 地址创建服务器级 IP 防火墙规则,在 SQL 数据库服务器上打开端口 1433。A server-level IP firewall rule is created for your current IP address opening port 1433 on the SQL Database server.

  6. 单击“确定”,并关闭“防火墙设置”页。Click OK and then close the Firewall settings page.

你的 IP 地址现在可以通过 IP 防火墙。Your IP address can now pass through the IP firewall. 现在可以使用 SQL Server Management Studio 或其他所选工具连接到单一数据库。You can now connect to your single database using SQL Server Management Studio or another tool of your choice. 确保使用之前创建的服务器管理员帐户。Be sure to use the server admin account you created previously.

Important

默认情况下,所有 Azure 服务都允许通过 SQL 数据库 IP 防火墙进行访问。By default, access through the SQL Database IP firewall is enabled for all Azure services. 在此页上单击“关”即可对所有 Azure 服务执行禁用操作。Click OFF on this page to disable for all Azure services.

连接到数据库Connect to the database

使用 SQL Server Management Studio 来与单一数据库建立连接。Use SQL Server Management Studio to establish a connection to your single database.

  1. 打开 SQL Server Management Studio。Open SQL Server Management Studio.

  2. 在“连接到服务器”对话框中,输入以下信息:In the Connect to Server dialog box, enter the following information:

    设置Setting       建议的值Suggested value 说明Description
    服务器类型Server type 数据库引擎Database engine 此值是必需的。This value is required.
    服务器名称Server name 完全限定的服务器名称The fully qualified server name 例如,yourserver.database.chinacloudapi.cn。For example, yourserver.database.chinacloudapi.cn.
    身份验证Authentication SQL Server 身份验证SQL Server Authentication SQL 身份验证是本教程中配置的唯一身份验证类型。SQL Authentication is the only authentication type that we've configured in this tutorial.
    登录名Login 服务器管理员帐户The server admin account 在创建服务器时指定的帐户。The account that you specified when you created the server.
    密码Password 服务器管理员帐户的密码The password for your server admin account 创建服务器时指定的密码。The password that you specified when you created the server.

    连接到服务器

  3. 单击“连接到服务器”对话框中的“选项”。Click Options in the Connect to server dialog box. 在“连接到数据库”部分输入 yourDatabase,以连接到此数据库。In the Connect to database section, enter yourDatabase to connect to this database.

    连接到服务器上的 DB

  4. 单击“连接” 。Click Connect. 此时会在 SSMS 中打开“对象资源管理器”窗口。The Object Explorer window opens in SSMS.

  5. 在对象资源管理器中展开“数据库”,然后展开 yourDatabase,查看示例数据库中的对象。In Object Explorer, expand Databases and then expand yourDatabase to view the objects in the sample database.

    数据库对象

在数据库中创建表Create tables in your database

使用 Transact-SQL 创建具有 4 个表格的数据库架构,这些表格是大专院校的学生管理系统的模型:Create a database schema with four tables that model a student management system for universities using Transact-SQL:

  • 人员Person
  • 课程Course
  • 学生Student
  • 额度Credit

以下关系图显示了这些表的相互关系。The following diagram shows how these tables are related to each other. 其中一些表引用其他表中的列。Some of these tables reference columns in other tables. 例如,“学生”表引用“人员”表的 PersonId 列。For example, the Student table references the PersonId column of the Person table. 请研究此关系图,了解本教程中各种表的相互关系。Study the diagram to understand how the tables in this tutorial are related to one another. 若要深入了解如何创建有效的数据库表,请参阅 Create effective database tables(创建有效的数据库表)。For an in-depth look at how to create effective database tables, see Create effective database tables. 有关如何选择数据类型的信息,请参阅 Data types(数据类型)。For information about choosing data types, see Data types.

Note

还可以使用 SQL Server Management Studio 中的表设计器来创建和设计表。You can also use the table designer in SQL Server Management Studio to create and design your tables.

表关系

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

  2. 在查询窗口中执行以下查询,在数据库中创建 4 个表:In the query window, execute the following query to create four tables in your database:

    -- Create Person table
    CREATE TABLE Person
    (
        PersonId INT IDENTITY PRIMARY KEY,
        FirstName NVARCHAR(128) NOT NULL,
        MiddelInitial NVARCHAR(10),
        LastName NVARCHAR(128) NOT NULL,
        DateOfBirth DATE NOT NULL
    )
    
    -- Create Student table
    CREATE TABLE Student
    (
        StudentId INT IDENTITY PRIMARY KEY,
        PersonId INT REFERENCES Person (PersonId),
        Email NVARCHAR(256)
    )
    
    -- Create Course table
    CREATE TABLE Course
    (
        CourseId INT IDENTITY PRIMARY KEY,
        Name NVARCHAR(50) NOT NULL,
        Teacher NVARCHAR(256) NOT NULL
    )
    
    -- Create Credit table
    CREATE TABLE Credit
    (
        StudentId INT REFERENCES Student (StudentId),
        CourseId INT REFERENCES Course (CourseId),
        Grade DECIMAL(5,2) CHECK (Grade <= 100.00),
        Attempt TINYINT,
        CONSTRAINT [UQ_studentgrades] UNIQUE CLUSTERED
        (
            StudentId, CourseId, Grade, Attempt
        )
    )
    

    创建表

  3. 展开“对象资源管理器”中 yourDatabase 下的“表”节点以查看创建的表。Expand the Tables node under yourDatabase in the Object Explorer to see the tables you created.

    创建的 ssms 表

将数据加载到表Load data into the tables

  1. 在“下载”文件夹中创建名为 sampleData 的文件夹,为数据库存储示例数据。Create a folder called sampleData in your Downloads folder to store sample data for your database.

  2. 右键单击以下链接并将它们保存到 sampleData 文件夹。Right-click the following links and save them into the sampleData folder.

  3. 打开命令提示符窗口并导航到 sampleData 文件夹。Open a command prompt window and navigate to the sampleData folder.

  4. 执行以下命令,将示例数据插入表,使用环境值替换“服务器”、“数据库”、“用户”和“密码”的值。Execute the following commands to insert sample data into the tables replacing the values for server, database, user, and password with the values for your environment.

    bcp Course in SampleCourseData.txt -S <server>.database.chinacloudapi.cn -d <database> -U <user> -P <password> -q -c -t ","
    bcp Person in SamplePersonData.txt -S <server>.database.chinacloudapi.cn -d <database> -U <user> -P <password> -q -c -t ","
    bcp Student in SampleStudentData.txt -S <server>.database.chinacloudapi.cn -d <database> -U <user> -P <password> -q -c -t ","
    bcp Credit in SampleCreditData.txt -S <server>.database.chinacloudapi.cn -d <database> -U <user> -P <password> -q -c -t ","
    

现已将示例数据加载到了之前创建的表中。You have now loaded sample data into the tables you created earlier.

查询数据Query data

执行以下查询,从数据库表中检索信息。Execute the following queries to retrieve information from the database tables. 有关写入 SQL 查询的详细信息,请参阅写入 SQL 查询See Write SQL queries to learn more about writing SQL queries. 第一个查询将联接所有 4 个表,以查找由“Dominick Pope”授课的分数高于 75% 的学生。The first query joins all four tables to find the students taught by 'Dominick Pope' who have a grade higher than 75%. 第二个查询将联接所有 4 个表,以查找“Noe Coleman”注册过的课程。The second query joins all four tables and finds the courses in which 'Noe Coleman' has ever enrolled.

  1. 在 SQL Server Management Studio 查询窗口中,执行以下查询:In a SQL Server Management Studio query window, execute the following query:

    -- Find the students taught by Dominick Pope who have a grade higher than 75%
    SELECT  person.FirstName, person.LastName, course.Name, credit.Grade
    FROM  Person AS person
        INNER JOIN Student AS student ON person.PersonId = student.PersonId
        INNER JOIN Credit AS credit ON student.StudentId = credit.StudentId
        INNER JOIN Course AS course ON credit.CourseId = course.courseId
    WHERE course.Teacher = 'Dominick Pope'
        AND Grade > 75
    
  2. 在查询窗口中执行以下查询:In a query window, execute the following query:

    -- Find all the courses in which Noe Coleman has ever enrolled
    SELECT  course.Name, course.Teacher, credit.Grade
    FROM  Course AS course
        INNER JOIN Credit AS credit ON credit.CourseId = course.CourseId
        INNER JOIN Student AS student ON student.StudentId = credit.StudentId
        INNER JOIN Person AS person ON person.PersonId = student.PersonId
    WHERE person.FirstName = 'Noe'
        AND person.LastName = 'Coleman'
    

后续步骤Next steps

本教程介绍了许多基本数据库任务。In this tutorial, you learned many basic database tasks. 你已了解如何:You learned how to:

  • 创建单一数据库Create a single database
  • 设置服务器级 IP 防火墙规则Set up a server-level IP firewall rule
  • 使用 SQL Server Management Studio (SSMS) 连接到该数据库Connect to the database with SQL Server Management Studio (SSMS)
  • 创建表Create tables
  • 批量加载数据Bulk load data
  • 查询该数据Query that data

转向下一教程,了解如何使用 Visual Studio 和 C# 设计数据库。Advance to the next tutorial to learn about designing a database using Visual Studio and C#.