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/n/246183.html