在SQL Server中的CREATE TABLE语句

264 阅读8分钟

在这篇文章中,我们将学习CREATE TABLE语句。这篇文章整合了各种情况。我已经涵盖了以下几种情况。

  1. 如何在数据库的一个特定模式中创建一个表
  2. 如何创建一个全局和局部的临时表
  3. 如何创建一个有约束的表
  4. 如何在一个不同的文件组中创建一个表
  5. 如何创建一个系统版本的临时表

CREATE TABLE语句用于在数据库中创建一个新表。CREATE TABLE语句的语法如下。

1
2
3
4
5
6
7
8
创建 TABLE [Sch_Name].[tbl_Name](
[Col_Name_1] [Datatype](Length),
[Col_Name_2] [Datatype](Length) [Constraint_Name]
[Col_Name_3] [Datatype](Length),
.... . . . .
) ON [FilegroupName]

在语法上。

  1. sch_name:指定你要创建表的模式的名称。你可以阅读篇文章来了解更多关于SQL Server模式的信息。
  2. tbl_name: 指定所需的表名。
  3. col_name: 指定你要添加到表中的列名。
  4. 数据类型。指定该列的数据类型。
  5. length(长度)。指定该列的长度。
  6. contrsint_Name。指定约束条件的名称。
  7. filegroupName。指定你要创建表的文件组名称。你可以阅读篇文章来了解更多关于SQL Server文件组的信息。

让我们了解在数据库中创建表的各种用例。

在一个特定的模式中创建表

要在特定模式中创建表,我们必须使用两部分的名称。你创建表的模式必须存在于数据库中。例如,你想在Patients模式中创建一个名为tblPatient的表,创建表的定义将如下。

1
2
3
4
5
6
7
8
9
10
CREATE TABLE [Patients].[tblPatient](
[Patient_ID] [bigint] IDENTITY(1,1),
[Patient_code] [varchar](50),
[Patient_name] [varchar](50),
[地址] [varchar](25),
[城市] [varchar](50),
[AppointmentDate] [datetime],
) on [primary]

屏幕截图

Table in different schema

上面的命令在Patients模式中创建了一个名为tblPatient的表。

创建一个有约束的表

现在,让我们了解一下如何创建一个带有约束条件的表。一个表可以有以下约束。

  1. NOT NULL:这个约束确保查询不会在创建了约束的列上插入NULL值。
  2. UNIQUE:这个约束确保查询在创建了约束的列中只插入唯一的值。
  3. CHECK:当我们在表中插入一条记录时,检查约束确保我们在列中插入的值必须满足CHECK约束中指定的条件。
  4. DEFAULT:DEFAULT约束会插入一个默认值。如果我们没有指定默认约束的列的值,它将插入DEFAULT约束中指定的默认值。
  5. 主键(PRIMARY KEY)。主键约束是用来唯一地识别每条记录的。以下是主键的特点
    1. 主键由一个或多个列组成。
    2. 主键包含唯一的值,不能包含NULL值。
    3. 我们可以在一个表中创建一个主键。
    4. 当我们创建一个主键时,一个聚类索引将被自动创建。
  6. 外键(FOREIGN KEY)。外键是一个在两个表之间建立联系的列。任何表的外键都是指另一个表的主键。一个表可以有一个或多个外键。外键约束可以防止UPDATE和DELETE语句破坏两个表之间的联系。

现在,让我们创建一个名为tblPatient的具有NOT NULL约束的表。

1
2
3
4
5
6
7
8
9
10
创建 [tblPatient](
[Patient_ID] [bigint] IDENTITY(1,1),
[Patient_code] [varchar](50) NOT NULL,
[Patient_name] [varchar](50),
[地址] [varchar](25),
[城市] [varchar](50),
[AppointmentDate] [datetime],
) on [primary]

现在,运行下面的查询,插入一个具有NULL值的记录。

1
2
3
4
5
6
insert into tblPatient
([Patient_code],[Patient_name],[Address],[City],[AppointmentDate])
价值
(NULL, 'Nisarg U', 'D5, Sector 15', 'Ahmedabad', getdate())

查询结果

Msg 515, Level 16, State 2, Line 12
Cannot insert the value NULL into column 'Patient_code', table 'VSDatabase.dbo.tblPatient';
column does not allow nulls.INSERT失败。

屏幕截图

NOT NULL constrint failed

正如你所看到的,该查询返回了一个错误。

在二级文件组中创建一个表

要在二级文件组中创建一个表,我们必须在CREATE TABLE语句中指定文件组名称。确保你要创建表的文件组必须存在于数据库中。当你想把经常访问的表放在一个单独的磁盘上时,在二级文件组中创建表是很有用的。

假设我们想在一个名为FG_Patient的二级文件组中创建表tblPatient。CREATE TABLE语句如下。

1
2
3
4
5
6
7
8
9
10
11
12
创建 [tblPatient](
[Patient_ID] [bigint] IDENTITY(1,1)
[Patient_code] [varchar](50),
[Patient_name] [varchar](50),
[地址] [varchar](25),
[城市] [varchar](50),
[预约日期] [数据时间]
)
ON [FG_Patients]
GO

屏幕截图

Table created in secondary filegroup

tblpatient已经被创建。现在,为了查看在FG_Patient文件组中创建的表,运行以下查询。

1
2
3
4
5
6
7
8
9
10
SELECT OBJECT_SCHEMA_NAME(tbl.object_id) AS schema_name
,tbl.name AS table_name
,fg.name AS filegroup_name
FROM sys.tables tbl
INNER JOIN sys.indexes indx ON tbl.object_id=indx.object_id
INNER JOIN sys.filegroups fg ON indx.data_space_id=fg.data_space_id
and tbl.name not like 'sys%'
ORDER BY tbl.name, indx.index_id

查询输出

View table name and filegroup

正如你所看到的,在FG_Patients文件组中已经创建了tblpatient

创建一个临时表

临时表是针对会话的表,用于临时存储数据。在执行复杂的字符串或算术函数时,创建和存储所需的数据集临时表并进行复杂的操作是一个好的做法。临时表是在TempDB数据库中创建的。有两种类型的临时表

  1. 本地临时表
  2. 全局临时表。

普通表和临时表的语法是一样的。唯一的区别是我们在表名前使用的前缀。要创建本地临时表,我们必须在表的名称前使用**#。要创建一个全局临时表,我们必须在表名前使用##**。例如,我们想创建一个名为#tblpatient的本地临时表,CREATE TABLE语句写如下。

1
2
3
4
5
6
7
8
9
10
11
创建 [#tblPatient](
[Patient_ID] [bigint] IDENTITY(1,1)
[Patient_code] [varchar](50),
[Patient_name] [varchar](50),
[地址] [varchar](25),
[城市] [varchar](50),
[预约日期] [数据时间]
)
GO

如果你想创建一个名为**##tblpatient的**全局临时表,CREATE TABLE语句写如下。

1
2
3
4
5
6
7
8
9
10
11
创建 [##tblPatient](
[Patient_ID] [bigint] IDENTITY(1,1)
[Patient_code] [varchar](50),
[Patient_name] [varchar](50),
[地址] [varchar](25),
[城市] [varchar](50),
[预约日期] [数据时间]
)
GO

要查看临时表,运行以下查询。

1
2
3
select name, create_date from [tempdb].[sys].[]

查询输出

View temp table

创建一个系统版本的时态表

系统版本时态表是在SQL Server 2016中引入的。这些表是特殊类型的表,用于保存数据修改的历史。你可以用它们来分析特定时间点上的数据分析。

当我们创建一个时间表时,我们必须指定两个具有datetime2数据类型的周期列。当表中的数据发生变化时,系统会在周期表中记录数据变化的日期时间。与周期列一起,我们必须指定历史表,它存储了关于数据变化的信息。

创建一个名为的时间表的语法为 tblPatient语法如下。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
创建 [tblPatient](
[Patient_ID] [bigint] IDENTITY(1,1) Primary key clusterstered,
[Patient_code] [varchar](50) NOT NULL,
[Patient_name] [varchar](50),
[地址] [varchar](25),
[城市] [varchar](50),
[AppointmentDate] [datetime],
[Frm_date] datetime2 GENERATED ALWAYS AS ROW START
, [To_date] datetime2 GENERATED ALWAYS AS ROW END
, PERIOD FOR SYSTEM_TIME (Frm_date, To_date)
) on [primary]
(SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.tblPatient_History))

在语法上,frm_dateto_date是周期列。frm_date和to_date的数据类型是datetime2。这些数据变化将被记录在tblPatient_History表中。现在,让我们插入一些假数据来分析其行为。

1
2
3
4
5
6
insert into tblPatient ([Patient_code],[Patient_name],[Address],[City],[AppointmentDate]) values
('OPD0001', 'Nisarg U', 'D5, Sector 15', 'Ahmedabad', getdate(),
('OPD0002', 'Nirali U', 'D5, Sector 15', 'Ahmedabad', getdate())
('OPD0003', 'Dixit U', 'D5, Sector 15', 'Ahmedabad', getdate())

一旦记录被插入,在tblPatient表中运行SELECT查询。

1
2
3
4
5
使用 VSDatabase
选择 * from tblPatient

输出

View data of temporal table

在历史表上运行以下查询。

1
2
3
4
5
使用 VSDatabase
选择 * from tblPatient_History

输出

View data of history table

正如你所看到的,记录被插入到带有日期和时间的tblPatient中。没有记录被插入到tblPatient_history表中。现在,运行一个UPDATE查询来改变一个特定病人的数据。

查询

1
2
3
4
5
使用 VSDatabase
update tblPatient set Patient_code='OPD0005' where Patient_code='OPD0003'

tblPatient表中运行选择查询。

1
2
3
4
5
使用 VSDatabase
选择 * from tblPatient_History

输出

Updated data in temporal table

在更新数据后,周期列的日期和时间被改变。让我们看一下历史表。在tblPatient_History表中运行SELECT查询。

查询

1
2
3
4
5
使用 VSDatabase
选择 * from tblPatient_History

查询输出

Data in history table

正如你所看到的,tblPatient的旧值已经被插入到历史表中。

有时,时间表可以用来恢复由UPDATE或DELETE查询所做的改变。我将另外写一篇文章来讨论这个问题。

总结

这篇文章让我们了解了SQL Server中的CREATE TABLE语句。我们学习了可以用来创建表的各种用例。我们学到了。

  • 如何在数据库的特定模式中创建一个表。
  • 如何创建一个全局和局部的临时表。
  • 如何创建一个有约束条件的表。
  • 如何在一个不同的文件组中创建一个表。
  • 如何创建一个系统版本的临时表。