您的位置:

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