教學課程:使用 SSMS 在 Azure SQL Database 的單一資料庫中設計關聯式資料庫Tutorial: Design a relational database in a single database within Azure SQL Database using SSMS

Azure SQL 資料庫是 Microsoft Cloud (Azure) 中的關聯式資料庫即服務 (DBaaS)。Azure SQL database is a relational database-as-a-service (DBaaS) in the Microsoft 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 free account before you begin.

注意

基於本教學課程的目的,我們是使用單一資料庫。For the purpose of this tutorial, we are using a single database. 您也可以使用彈性集區中的集區資料庫或受控執行個體中的執行個體資料庫。You could also use a pooled database in an elastic pool or an instance database in a managed instance. 如需連線至受控執行個體,請參閱以下受控執行個體快速入門:快速入門:設定 Azure VM 以連線到 Azure SQL Database 受控執行個體快速入門:設定從內部部署連線至 Azure SQL Database 受控執行個體的點對站連線For connectivity to a managed instance, see these managed instance quickstarts: Quickstart: Configure Azure VM to connect to an Azure SQL Database Managed Instance and Quickstart: Configure a point-to-site connection to an Azure SQL Database Managed Instance from on-premises.

必要條件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 Database 的單一資料庫。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 Marketplace] 區段中的 [資料庫] ,然後按一下 [精選] 區段中的 [SQL Database] 。On the New page, select Databases in the Azure Marketplace section, and then click SQL Database in the Featured section.

    建立空白資料庫

  3. 使用下列資訊填寫 SQL Database 表單,如上圖所示︰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 如需有效的資源群組名稱,請參閱命名規則和限制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 如需有效的伺服器名稱,請參閱命名規則和限制For valid server names, see Naming rules and restrictions.
    伺服器管理員登入Server admin login 任何有效名稱Any valid name 如需有效的登入名稱,請參閱資料庫識別碼For valid login names, see Database identifiers.
    密碼Password 任何有效密碼Any valid password 您的密碼至少要有 8 個字元,而且必須使用下列幾種字元的其中三種︰大寫字元、小寫字元、數字和非英數字元。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 如需區域的相關資訊,請參閱 Azure 區域For information about regions, see Azure Regions.

    建立資料庫伺服器

  5. 按一下 [選取] 。Click Select.

  6. 按一下 [定價層] 可指定服務層、DTU 或虛擬核心的數目,以及儲存體數量。Click Pricing tier to specify the service tier, the number of DTUs or vCores, and the amount of storage. 您可以瀏覽 DTU/虛擬核心數目的選項,以及可供您每個服務層級使用的儲存體。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). 如需定序的詳細資訊,請參閱定序For more information about collations, see Collations

  8. 您現在已完成 SQL Database 表單,請按一下 [建立] 來佈建單一資料庫。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

QL Database 服務會在伺服器層級建立 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 Database 伺服器層級的 IP 防火牆規則Follow these steps to create a SQL Database server-level IP firewall rule.

重要

SQL Database 服務會透過連接埠 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.windows.net),並提供進一步的組態選項。The overview page for your database opens, showing you the fully qualified Server name (such as yourserver.database.windows.net) 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 Database 伺服器的 [防火牆設定] 頁面隨即開啟。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 Database 伺服器上開啟連接埠 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.

重要

根據預設,已對所有 Azure 服務啟用透過 SQL Database 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.windows.netFor example, yourserver.database.windows.net.
    驗證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 建立大學的學生管理系統模型:Create a database schema with four tables that model a student management system for universities using Transact-SQL:

  • PersonPerson
  • 課程Course
  • 學生Student
  • 功勞Credit

下圖顯示這些資料表是如何彼此相互關聯。The following diagram shows how these tables are related to each other. 在這當中有部分資料表會參考其他資料表的資料欄。Some of these tables reference columns in other tables. 例如,Student 資料表會參考 Person 資料表的 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. 如需深入了解建立有效資料庫資料表的方式,請參閱建立有效資料庫資料表 For an in-depth look at how to create effective database tables, see Create effective database tables. 如需選擇資料類型的相關資訊,請參閱資料類型 (英文)For information about choosing data types, see Data types.

注意

您亦可使用 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. 在查詢視窗中,執行下列查詢以在資料庫中建立四個資料表︰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 之下的 Tables 節點,以查看您建立的資料表。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. 執行下列命令,將範例資料插入資料表中,並以適用於您環境的值取代 serverdatabaseuser, 和 password 的值。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 -S <server>.database.windows.net -d <database> -U <user> -P <password> -q -c -t ","
    bcp Person in SamplePersonData -S <server>.database.windows.net -d <database> -U <user> -P <password> -q -c -t ","
    bcp Student in SampleStudentData -S <server>.database.windows.net -d <database> -U <user> -P <password> -q -c -t ","
    bcp Credit in SampleCreditData -S <server>.database.windows.net -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. 第一個查詢會聯結所有四個資料表,以尋找由「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%. 第二個查詢會連結所有四個資料表,以尋找「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#.