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中创建表的基本结构和常用技巧,包括创建主键和外键、列属性约束、表的索引和分区等。在实际开发中,需要根据具体的需求选择不同的技术,以提高数据库系统的处理效率和数据管理能力。