在这篇文章中,我们将学习CREATE TABLE语句。这篇文章整合了各种情况。我已经涵盖了以下几种情况。
- 如何在数据库的一个特定模式中创建一个表
- 如何创建一个全局和局部的临时表
- 如何创建一个有约束的表
- 如何在一个不同的文件组中创建一个表
- 如何创建一个系统版本的临时表
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] |
在语法上。
- sch_name:指定你要创建表的模式的名称。你可以阅读这篇文章来了解更多关于SQL Server模式的信息。
- tbl_name: 指定所需的表名。
- col_name: 指定你要添加到表中的列名。
- 数据类型。指定该列的数据类型。
- length(长度)。指定该列的长度。
- contrsint_Name。指定约束条件的名称。
- 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] |
屏幕截图
上面的命令在Patients模式中创建了一个名为tblPatient的表。
创建一个有约束的表
现在,让我们了解一下如何创建一个带有约束条件的表。一个表可以有以下约束。
- NOT NULL:这个约束确保查询不会在创建了约束的列上插入NULL值。
- UNIQUE:这个约束确保查询在创建了约束的列中只插入唯一的值。
- CHECK:当我们在表中插入一条记录时,检查约束确保我们在列中插入的值必须满足CHECK约束中指定的条件。
- DEFAULT:DEFAULT约束会插入一个默认值。如果我们没有指定默认约束的列的值,它将插入DEFAULT约束中指定的默认值。
- 主键(PRIMARY KEY)。主键约束是用来唯一地识别每条记录的。以下是主键的特点
- 主键由一个或多个列组成。
- 主键包含唯一的值,不能包含NULL值。
- 我们可以在一个表中创建一个主键。
- 当我们创建一个主键时,一个聚类索引将被自动创建。
- 外键(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失败。
屏幕截图
正如你所看到的,该查询返回了一个错误。
在二级文件组中创建一个表
要在二级文件组中创建一个表,我们必须在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 |
屏幕截图
表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 |
查询输出
正如你所看到的,在FG_Patients文件组中已经创建了tblpatient。
创建一个临时表
临时表是针对会话的表,用于临时存储数据。在执行复杂的字符串或算术函数时,创建和存储所需的数据集临时表并进行复杂的操作是一个好的做法。临时表是在TempDB数据库中创建的。有两种类型的临时表
- 本地临时表
- 全局临时表。
普通表和临时表的语法是一样的。唯一的区别是我们在表名前使用的前缀。要创建本地临时表,我们必须在表的名称前使用**#。要创建一个全局临时表,我们必须在表名前使用##**。例如,我们想创建一个名为#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].[表]。 |
查询输出
创建一个系统版本的时态表
系统版本时态表是在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_date和to_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 |
输出
在历史表上运行以下查询。
1 2 3 4 5 | 使用 VSDatabase 去 选择 * from tblPatient_History |
输出
正如你所看到的,记录被插入到带有日期和时间的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 |
输出
在更新数据后,周期列的日期和时间被改变。让我们看一下历史表。在tblPatient_History表中运行SELECT查询。
查询
1 2 3 4 5 | 使用 VSDatabase 去 选择 * from tblPatient_History |
查询输出
正如你所看到的,tblPatient的旧值已经被插入到历史表中。
有时,时间表可以用来恢复由UPDATE或DELETE查询所做的改变。我将另外写一篇文章来讨论这个问题。
总结
这篇文章让我们了解了SQL Server中的CREATE TABLE语句。我们学习了可以用来创建表的各种用例。我们学到了。
- 如何在数据库的特定模式中创建一个表。
- 如何创建一个全局和局部的临时表。
- 如何创建一个有约束条件的表。
- 如何在一个不同的文件组中创建一个表。
- 如何创建一个系统版本的临时表。