SQL Server創建表

SQL Server是Microsoft開發的一個關係型資料庫管理系統，無論是開發小型還是大型應用它都有很好的表現。本文從多個方面介紹SQL Server創建表的方法和技巧。

一、表的基礎結構

在SQL Server中，創建表需要定義表的基礎結構。基礎結構包括表的名稱、列名稱、數據類型和列約束等。


CREATE TABLE [dbo].[Employee](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [First Name] [varchar](50) NOT NULL,
    [Last Name] [varchar](50) NULL,
    [Email] [varchar](50) NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

上述代碼示例為創建名為Employee的表，並定義了四列：id（自增型整數）、First Name（非空的字元串型）、Last Name（字元串型，可以為空）和Email（字元串型，可以為空）。其中，id列被指定為主鍵，且使用IDENTITY屬性進行自增。在創建表時，需要使用CREATE TABLE語句，同時定義表的名稱和列的屬性。

二、主鍵和外鍵

主鍵和外鍵是SQL Server中非常重要的概念，它們用於建立表與表之間的關係。主鍵用於唯一標識表中的每一行數據，而外鍵則用於建立表之間的聯繫。

可以使用以下代碼來創建包含主鍵和外鍵的表：


CREATE TABLE [dbo].[Employee](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [First Name] [varchar](50) NOT NULL,
    [Last Name] [varchar](50) NULL,
    [Email] [varchar](50) NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Orders](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [EmployeeId] [int] NOT NULL,
    [OrderDate] [date] NOT NULL,
 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Orders_Employee] FOREIGN KEY([EmployeeId])
REFERENCES [dbo].[Employee] ([id])
GO
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Employee]
GO

上述代碼示例創建了兩個表：Employee和Orders。Employee表中的id列被指定為主鍵，而Orders表中的EmployeeId列則被指定為外鍵，用於與Employee表建立聯繫。

三、列屬性的約束

在SQL Server中，可以使用列的屬性約束來限制列的取值範圍、驗證輸入數據等。下面介紹幾種常用的列屬性約束：

1. 非空約束

非空約束用於限制該列的值不能為空。當使用非空約束時，插入一條沒有該列的值的數據時，SQL Server將會返回一個錯誤。


CREATE TABLE [dbo].[Employee](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [First Name] [varchar](50) NOT NULL,
    [Last Name] [varchar](50) NULL,
    [Email] [varchar](50) NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

2. 唯一約束

唯一約束用於限制該列的值不能重複。當使用唯一約束時，插入一條重複的數據時，SQL Server將會返回一個錯誤。


CREATE TABLE [dbo].[Employee](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [First Name] [varchar](50) NOT NULL,
    [Last Name] [varchar](50) NULL,
    [Email] [varchar](50) NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UQ_Email] UNIQUE NONCLUSTERED 
(
    [Email] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

3. 默認值約束

默認值約束用於在插入數據時設置該列的默認值。當插入數據時，如果該列沒有輸入值，將會使用默認值。


CREATE TABLE [dbo].[Employee](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [First Name] [varchar](50) NOT NULL,
    [Last Name] [varchar](50) NULL,
    [Email] [varchar](50) NULL DEFAULT 'test@test.com',
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

4. 檢查約束

檢查約束用於限制該列的值必須符合指定條件。當插入數據時，如果該列輸入的值不符合指定條件，SQL Server將會返回一個錯誤。


CREATE TABLE [dbo].[Employee](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [First Name] [varchar](50) NOT NULL,
    [Last Name] [varchar](50) NULL,
    [Email] [varchar](50) NULL,
    [Salary] [money] NOT NULL CHECK (([Salary]>(0))),
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

四、表的索引

索引是一個非常強大的工具，它可以加速對錶的查詢和更新操作。在SQL Server中，可以使用CREATE INDEX語句創建索引。

下面代碼示例展示了如何在Employee表的LastName列上創建索引：


CREATE NONCLUSTERED INDEX [IX_Employee_LastName] ON [dbo].[Employee]
(
    [Last Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

五、表的分區

表的分區是一種高級技術，它可以將一個表中的數據分成多個分區進行管理。在SQL Server中，可以使用CREATE PARTITION FUNCTION和CREATE PARTITION SCHEME語句來實現表的分區。

下面代碼示例展示了如何使用CREATE PARTITION FUNCTION和CREATE PARTITION SCHEME語句來創建一個Employee表的分區：


CREATE PARTITION FUNCTION EmployeePF1 (int)
AS RANGE LEFT FOR VALUES (100, 200, 300)

CREATE PARTITION SCHEME EmployeePS1
AS PARTITION EmployeePF1
TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])

CREATE TABLE [dbo].[Employee](
    [id] [int] NOT NULL,
    [First Name] [varchar](50) NOT NULL,
    [Last Name] [varchar](50) NULL,
    [Email] [varchar](50) NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON EmployeePS1(id)

六、總結

本文介紹了SQL Server中創建表的基本結構和常用技巧，包括創建主鍵和外鍵、列屬性約束、表的索引和分區等。在實際開發中，需要根據具體的需求選擇不同的技術，以提高資料庫系統的處理效率和數據管理能力。

原創文章，作者：小藍，如若轉載，請註明出處：https://www.506064.com/zh-tw/n/246183.html