3.3 表的创建、修改和删除
3.3.1 表的创建
在创建表时,需要确定表的结构,也就是确定表中有几个字段、每个字段的字段名以及每个字段的数据类型等。只有设计好表的结构,系统才会在磁盘上开辟相应的空间,用户才能向表中填写数据。
在SQL Server 2014中,创建表有两种方式:一种是通过SQL Server Management Studio创建,另一种是通过Transact-SQL语句创建。
1.使用SQL Server Management Studio创建表结构
【例3.5】 为sales数据库创建订单表Orders,表中包含4列,每列的名称及类型等属性如表3-1所示。
通过SQL Server Management Studio创建表是最便捷的方式,具体操作步骤如下:
1)启动SQL Server Management Studio,在“对象资源管理器”窗口中,展开“数据库”→“sales”数据库节点。右键单击“表”,从弹出的快捷菜单中选择“新建”→“表”命令,如图3-8所示。
打开如图3-9所示“表设计”窗口(表设计器)。
2)在“表设计”窗口的上半部分输入列的基本属性,包括列的名称、列的数据类型、以及该列是否允许为空值(打勾说明允许为空值,空白说明不允许为空值,默认允许为空值)。
例如,输入第一列的名称OrderID,类型为int,不允许为空值。
3)在表设计器下半部分的“列属性”选项卡中指定列的详细属性,包括是否为标识列、是否使用默认值、为数据类型指定长度等。
如果新列具有默认值,可在“默认值或绑定”栏内设置。当向表插入数据时,如果用户没有提供该列的值,SQL Server自动用默认值填充该列。OrderID这一列没有默认值,因此无须设置该项。
如果想定义某一列为标识列,把“标识范围”属性中的“是标识”设为“是”,并设置“标识种子”和“标识增量”即可。“标识列”是指当一个新的数据行被插入表中时,SQL Server为标识列提供一个唯一的、递增的数值。例如,设置OrderID为标识列,标识种子(标识列的起始值)为10248,标识递增量(标识列的增值)为1,如图3-10所示。那么,当用户向表中添加数据时,该列的值由系统自动填充,依次为10248、10249、10250……
只能指定数据类型是整型的列为标识列,并且每张表中只能有一个标识列。
按照如上方法依次添加CustomerID列、SaleID列以及OrderDate列。OrderDate列带有默认值,需在“列属性”选项卡中的“默认值或绑定”栏中输入默认值,用getdate()系统函数获取系统当前的日期和时间,如图3-11所示。
4)保存表格,单击工具栏上的“保存”按钮,弹出“选择名称”对话框,如图3-12所示,输入表名,单击“确定”按钮即可完成表的建立。如果在该数据库中已经有同名的表存在,系统会弹出警告对话框,用户可以改名重新保存。
至此完成Orders表的创建,在“对象资源管理器”中刷新sales数据库下的表节点,会看到新建的Orders表,如图3-13所示。
2.使用Transact-SQL语句创建表结构
使用Transact-SQL语句创建表格的语法格式为:
CREATE TABLE [database_name.[schema_name].|schema_name.]table_name ( column_name1 data_type [ DEFAULT constant_expression] [ IDENTITY ( SEED, INCREMENT )] [ NULL | NOT NULL ] [ ,…n] ) [ON { filegroup | DEFAULT } ]
其中:
database_name:在其中创建表的数据库的名称。database_name必须指定现有数据库的名称。如果未指定,则database_name默认为当前数据库。
schema_name:新表所属架构的名称。
table_name:新表的名称。表名必须遵循数据库对象的命名规则。除了本地临时表名不能超过116个字符外,table_name最多可包含128个字符。
column_name1:表中列的名称。列名在表中必须唯一。
data_type:是对应列数据所采用的数据类型,可以是数据库管理系统支持的任何数据类型。
DEFAULT constant_expression:指定所定义的列的默认值,默认值由常量表达式确定。
IDENTITY:定义该列是一个标识列。在定义标识列时,必须同时定义标识种子和标识增量。
SEED:标识种子,即标识列的起始值。
INCREMENT:标识列的增量。
NULL| NOT NULL:指出该列是否允许为空,默认为NULL。
ON filegroup | DEFAULT:指定在哪个文件组上创建表。DEFAULT表示将表存储在默认文件组中。
【例3.6】 为sales数据库创建一个销售人员表Seller,包含销售员编号(SaleID)、姓名(SaleName)、性别(Sex)、出生日期(Birthday)、雇用日期(HireDate)、地址(Address)、电话(Telephone)和备注(Notes)字段,其中SaleID、SaleName字段不允许为空,Sex字段带有默认值“男”,HireDate字段用当前系统日期作默认值。
CREATE TABLE Seller ( SaleID char(3) NOT NULL,--销售员编号,不允许为空值 SaleName char(8) NOT NULL,--销售员姓名,不允许为空值 Sex char(2) DEFAULT '男',--销售员性别,有默认值'男' Birthday datetime, HireDate datetime DEFAULT getDate(), Address char(60), Telephone char(13), Notes char(200) )
单击工具栏上的“新建查询”按钮,新建一个使用当前连接的查询,在打开的查询编辑器中输入上面的代码,如图3-14所示。
单击工具栏中的“执行”按钮,执行该CREATE TABLE命令。执行成功之后,刷新sales数据库下的表节点,即可看到新建名称为Seller的数据表。
【例3.7】 为sales数据库在文件组USER1上创建种类Category表。
CREATE TABLE Category ( CategoryID int IDENTITY(1,1), CategoryName nvarchar(15), Description nvarchar(200) ) ON USER1
表Category包含3个字段,其中字段CategoryID是标识列,标识列的起始值和增量均为1。
注意
文件组USER1必须已经定义。在例3.6中,因为没有使用ON关键字指出文件组,所以表存储在默认的文件组中。
表是数据库的组成对象,在进行创建表的操作之前,先要通过命令USE sales打开要操作的数据库。
【例3.8】 为sales数据库创建Customer表,该表包含客户编码(CustomerID)字段、公司名称(CompanyName)字段、联系人姓名(ConnectName)字段、地址(Address)字段和备注(Notes)字段。
CREATE TABLE Customer ( CustomerID char(3) not null, CompanyName varchar(30) not null, ConnectName varchar(8) not null, Address varchar(100), Notes char(200) )