>[danger] ## 3.4.1 创建表 演示如何在数据库的特定模式中创建新表。 表用于在数据库中存储数据。 表在数据库和模式中唯一命名。 每个表包含一个或多个列。 每列都有一个相关的数据类型，用于定义它可以存储的数据类型，例如：数字，字符串和日期。 要创建新表，请使用`CREATE TABLE`语句，如下所示： ~~~sql CREATE TABLE [database_name.][schema_name.]table_name ( pk_column data_type PRIMARY KEY, column_1 data_type NOT NULL, column_2 data_type, ..., table_constraints ); ~~~ 在上面的语法中， * 首先，指定创建表的数据库的名称。 `database_name`必须是现有数据库的名称。 如果未指定，则`database_name`默认为当前数据库。 * 其次，`schema_name`指定新表所属的模式。 * 第三，`table_name`指定新表的名称。 * 第四，每个表应该有一个由一列或多列组成的主键。 通常，首先列出主键列，然后列出其他列。 如果主键只包含一列，则可以在列名后使用`PRIMARY KEY`关键字。 如果主键由两列或更多列组成，则需要将`PRIMARY KEY`约束指定为表约束。 每个列都在语句中的名称后面指定了关联的数据类型。 列可能具有一个或多个列约束，例如：`NOT NULL`和`UNIQUE`。 * 第五，表可能在表约束部分中指定了一些约束，例如：`FOREIGN KEY`，`PRIMARY KEY`，`UNIQUE`和`CHECK`。 请注意，`CREATE TABLE`可以很复杂，并且具有比上述语法更多的选项。 我们将在后续教程中逐步的介绍每个选项。 ## SQL Server CREATE TABLE示例 以下语句创建一个名为`visits`的新表来跟踪客户的店内访问： ~~~sql CREATE TABLE sales.visits ( visit_id INT PRIMARY KEY IDENTITY (1, 1), first_name VARCHAR (50) NOT NULL, last_name VARCHAR (50) NOT NULL, visited_at DATETIME, phone VARCHAR(20), store_id INT NOT NULL, FOREIGN KEY (store_id) REFERENCES sales.stores (store_id) ); ~~~ 在这个示例中： 因为没有明确指定创建表的数据库名称，所以在`bb_stores`数据库中创建了`visits`表。 但是已经明确指定模式，因此，在`sales`模式中创建了`visits`表。 `visits`表中定义了`6`列，下面来看每一列的简介描述： * `visit_id`列是表的主键列。 `IDENTITY(1,1)`指示SQL Server自动生成从`1`开始的列的整数，并为每个新行递增`1`。 * `first_name`和`last_name`列是`VARCHAR`类型的字符串列。 这些列最多可以存储`50`个字符。 * `visited_at`是`DATETIME`数据类型的列，记录客户访问商店的日期和时间。 * `phone`列是一个接受`NULL`的`VARCHAR`字符串列。 * `store_id`列存储标识客户访问商店的标识号。 * 表定义的末尾是`FOREIGN KEY`约束。 此外键确保`visit`表的`store_id`列中的值必须在`stores`表的`store_id`列中可用。可在后续教程中了解有关`FOREIGN KEY`约束的更多信息。 >[danger] ## 3.4.2 标识列 ## SQL Server IDENTITY列简介 要为表创建标识列，请使用`IDENTITY`属性，如下所示： ~~~sql IDENTITY[(seed,increment)] ~~~ 在上面语法中， * `seed`是表中的第一行的值(第一条记录标识列使用的值)。 * `increment`是添加到上一行标识值的增量值。 `seed`和`increment`的默认值是`1`，即`(1,1)`。表示加载到表中的第一行的值为：`1`，第二行的值为：`2`(在上一行：`1`的基础上加`1`)，依此类推。 假设，希望第一行的标识列的值为`10`，增量值为`2`，可使用以下语法： ~~~sql IDENTITY (10,2) ~~~ > 注：第一条记录标识列的值是：10，第二条记录标识列的值是：12，第三条记录标识列的值为：14，依此类推。 请注意，SQL Server允许每个表只有一个标识列。 ## SQL Server IDENTITY示例 下面创建一个名为`hr`的新模式用来练习： ~~~sql CREATE SCHEMA hr; ~~~ 以下语句在个人标识号(`person_id`)列上使用`IDENTITY`属性创建新表： ~~~sql CREATE TABLE hr.person ( person_id INT IDENTITY(1,1) PRIMARY KEY, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, gender CHAR(1) NOT NULL ); ~~~ 首先，向`person`表中插入一个新行： ~~~sql INSERT INTO hr.person(first_name, last_name, gender) OUTPUT inserted.person_id VALUES('Max','Su', 'M'); ~~~ 输出结果如下：  从输出中可以清楚地看到，第一行在`person_id`列中加载的值为：`1`。 接下来，再向`person`表中插入另一行： ~~~sql INSERT INTO hr.person(first_name, last_name, gender) OUTPUT inserted.person_id VALUES('Mini','Su','F'); ~~~ 输出结果如下：  从输出中可以清楚地看到，第二行在`person_id`列中的值为：`2`。 >[danger] ## 3.4.3 向表添加列 以下`ALTER TABLE ADD`语句将新列添加到表中： ~~~sql ALTER TABLE table_name ADD column_name data_type column_constraint; ~~~ 在上面语句中： 首先，`table_name`指定要添加新列的表的名称。 其次，`column_name`指定列的名称，`data_type`表示数据类型，`column_constraint`表示约束(如果适用)。 如果要使用单个`ALTER TABLE`语句一次向表中添加多个列，请使用以下语法： ~~~sql ALTER TABLE table_name ADD column_name_1 data_type_1 column_constraint_1, column_name_2 data_type_2 column_constraint_2, ..., column_name_n data_type_n column_constraint_n; ~~~ 在此语法中，指定要在`ADD`子句之后添加到表中，以逗号分隔的列列表。 ## SQL Server ALTER TABLE ADD列示例 以下语句创建一个名为`sales.quotations`的新表： ~~~sql CREATE TABLE sales.quotations ( quotation_no INT IDENTITY PRIMARY KEY, valid_from DATE NOT NULL, valid_to DATE NOT NULL ); ~~~ 要将名为`description`的新列添加到`sales.quotations`表，请使用以下语句： ~~~sql ALTER TABLE sales.quotations ADD description VARCHAR (255) NOT NULL; ~~~ 以下语句将两个名为`amount`和`customer_name`的新列添加到`sales.quotations`表中： ~~~sql ALTER TABLE sales.quotations ADD amount DECIMAL (10, 2) NOT NULL, customer_name VARCHAR (50) NOT NULL; ~~~ >[danger] ## 3.4.4. [修改列] 演示如何更改表中现有列的定义 SQL Server可对表的列执行以下更改： * 修改数据类型 * 改变大小 * 添加`NOT NULL`约束 #### 1\. 修改列的数据类型 要修改列的数据类型，请使用以下语句： ~~~sql ALTER TABLE table_name ALTER COLUMN column_name new_data_type(size); ~~~ 新数据类型必须与旧数据类型兼容，否则，如果列具有数据且无法转换，则会出现转换错误。 请看下面示例。 首先，创建一个包含只有一列数据类型为`INT`的新表： ~~~sql CREATE TABLE t1 (c INT); ~~~ 其次，在表中插入一些行： ~~~sql INSERT INTO t1 VALUES (1), (2), (3); ~~~ 接下来，将列的数据类型从`INT`修改为`VARCHAR`： ~~~sql ALTER TABLE t1 ALTER COLUMN c VARCHAR (2); ~~~ 第三，插入带有字符串数据的新行： ~~~sql INSERT INTO t1 VALUES ('@'); ~~~ 第四，将列的数据类型从`VARCHAR`修改回`INT`： ~~~sql ALTER TABLE t1 ALTER COLUMN c INT; ~~~ SQL Server发出以下错误： ~~~sql Conversion failed when converting the varchar value '@' to data type int. ~~~ #### 2\. 更改列的大小 以下语句创建一个新表，其中一列的数据类型为`VARCHAR(10)`： ~~~sql CREATE TABLE t2 (c VARCHAR(25)); ~~~ 将一些示例数据插入到`t2`表中： ~~~sql INSERT INTO t2 VALUES ('SQL Server'), ('Yiibai.com'), ('Kaops.com') ~~~ 可以按如下方式增加列的大小，修改为：`50`： ~~~sql ALTER TABLE t2 ALTER COLUMN c VARCHAR (50); ~~~ 但是，当减小列的大小时，SQL Server会检查现有数据以查看它是否可以根据新大小转换数据。 如果转换失败，SQL Server将终止该语句并发出错误消息。 例如，如果将列`c`的大小减小为`5`个字符： ~~~sql ALTER TABLE t2 ALTER COLUMN c VARCHAR (5); ~~~ SQL Server发出以下错误： ~~~sql String or binary data would be truncated. ~~~ #### 3\. 向可空列添加NOT NULL约束 以下语句创建一个具有可为空列的新表： ~~~sql CREATE TABLE t3 (c VARCHAR(50)); ~~~ 以下语句向表中插入一些行： ~~~sql INSERT INTO t3 VALUES ('Nullable column'), (NULL); ~~~ 如果要将`NOT NULL`约束添加到列`c`，则必须首先将`NULL`更新为非null，例如： ~~~sql UPDATE t3 SET c = '' WHERE c IS NULL; ~~~ 然后再添加`NOT NULL`约束： ~~~sql ALTER TABLE t3 ALTER COLUMN c VARCHAR (20) NOT NULL; ~~~ >[danger] ## 3.4.5 [删除列] - 演示如何从表中删除一个或多个列 ## ALTER TABLE DROP COLUMN简介 有时，需要从表中删除一个或多个未使用或过时的列。 那么可使用`ALTER TABLE DROP COLUMN`语句，如下所示： ~~~sql ALTER TABLE table_name DROP column_name; ~~~ 在上面语法中， * 首先，`table_name`指定要从中删除列的表的名称。 * 其次，`column_name`指定要删除的列的名称。 如果要删除的列具有`CHECK`约束，则必须先删除该约束，然后再删除该列。 此外，SQL Server不允许删除具有`PRIMARY KEY`或`FOREIGN KEY`约束的列。 如果要一次删除多个列，请使用以下语法： ~~~sql ALTER TABLE table_name DROP column_name_1, column_name_2,...; ~~~ 在此语法中，将要删除的列指定为`DROP`子句中以逗号分隔列的列表。 ## ALTER TABLE DROP COLUMN示例 下面为了演示，创建一个名为`sales.price_lists`的新表。 ~~~sql CREATE TABLE sales.price_lists( product_id int, valid_from DATE, price DEC(10,2) NOT NULL CONSTRAINT ck_positive_price CHECK(price >= 0), discount DEC(10,2) NOT NULL, surcharge DEC(10,2) NOT NULL, note VARCHAR(255), PRIMARY KEY(product_id, valid_from) ); ~~~ 以下语句用于从`price_lists`表中删除`note`列： ~~~sql ALTER TABLE sales.price_lists DROP COLUMN note; ~~~ `price`列具有`CHECK`约束，因此无法删除它。 如果尝试执行以下语句，则会收到错误消息： ~~~sql ALTER TABLE sales.price_lists DROP COLUMN price; ~~~ 将收到以下错误消息： ~~~sql The object 'ck_positive_price' is dependent on column 'price'. ~~~ 如果要删除`price`列：首先，删除它的`CHECK`约束： ~~~sql ALTER TABLE sales.price_lists DROP CONSTRAINT ck_positive_price; ~~~ 然后，再删除`price`列： ~~~sql ALTER TABLE sales.price_lists DROP COLUMN price; ~~~ 以下示例用于一次删除两列：`discount`和`surcharge`： ~~~sql ALTER TABLE sales.price_lists DROP COLUMN discount, surcharge; ~~~ >[danger] ## 3.4.6[删除表] 演示如何从数据库中删除表 有时，我们希望删除一些不再使用的表。 那么可使用以下`DROP TABLE`语句： ~~~sql DROP TABLE [IF EXISTS] [database_name.][schema_name.]table_name; ~~~ 在上面语法中， * 首先，`database_name`指定要删除的表的名称。 * 其次，指定创建表的数据库的名称以及表所属的模式的名称。数据库名称是可选的。 如果不指定，`DROP TABLE`语句将删除当前数据库中的表。 * 第三，使用`IF EXISTS`子句仅在表存在时才删除表。 自SQL Server 2016 13.x起，支持`IF EXISTS`子句。 如果删除不存在的表，则会出现错误。 如果表已经存在，则`IF EXISTS`子句有条件地删除该表。 当SQL Server删除表时，它还会删除该表的所有数据，触发器，约束和权限。 此外，SQL Server不会显式删除引用已删除表的视图和存储过程。 要显式删除这些对象，必须使用`DROP VIEW`和`DROP PROCEDURE`语句。 SQL Server允许使用单个`DROP TABLE`语句来一次删除多个表，如下所示： ~~~sql DROP TABLE [database_name.][schema_name.]table_name_1, [database_name.][schema_name.]table_name_2, ... [database_name.][schema_name.]table_name_n; ~~~ ## SQL Server DROP TABLE示例 下面来看一些使用SQL Server `DROP TABLE`语句的示例。 #### 1\. 删除不存在的表 以下语句删除`sales`模式中的`revenue`表： ~~~sql DROP TABLE IF EXISTS sales.revenues; ~~~ 在此示例中，`revenue`表不存在。 因为它使用`IF EXISTS`子句，所以语句成功执行，但是没有表可以删除。 #### 2\. 删除单个表示例 以下语句在`sales`模式中创建一个名为`delivery`的新表： ~~~sql CREATE TABLE sales.delivery ( delivery_id INT PRIMARY KEY, delivery_note VARCHAR (255) NOT NULL, delivery_date DATE NOT NULL ); ~~~ 要删除`delivery`表，请使用以下语句： ~~~sql DROP TABLE sales.delivery; ~~~ #### 3\. 删除具有外键约束的表 以下语句在`procurement`模式中创建两个新表：`supplier_groups`和`supplier`： ~~~sql CREATE SCHEMA procurment; GO CREATE TABLE procurment.supplier_groups ( group_id INT IDENTITY PRIMARY KEY, group_name VARCHAR (50) NOT NULL ); CREATE TABLE procurment.suppliers ( supplier_id INT IDENTITY PRIMARY KEY, supplier_name VARCHAR (50) NOT NULL, group_id INT NOT NULL, FOREIGN KEY (group_id) REFERENCES procurement.supplier_groups (group_id) ); ~~~ 下面尝试删除`supplier_groups`表： ~~~sql DROP TABLE procurement.supplier_groups; ~~~ SQL Server发出以下错误： ~~~sql Could not drop object 'procurement.supplier_groups' because it is referenced by a FOREIGN KEY constraint. ~~~ SQL Server不允许删除外部约束引用的表。 要删除此表，必须先删除引用外键约束或引用表。 在这种情况下，必须先删除`supplier`表或`supplier`表中的外键约束，然后再删除`supplier_groups`表。 ~~~sql DROP TABLE procurement.supplier_groups; DROP TABLE procurement.suppliers; ~~~ 如果使用单个`DROP TABLE`语句删除两个表，则必须将引用表放在前面，如下面的查询所示： ~~~sql DROP TABLE procurement.suppliers, procurement.supplier_groups; ~~~ >[danger] ## 3.4.7[截断表] 演示如何更快，更有效地删除表中的所有数据 ## QL Server TRUNCATE TABLE语句简介 有时，希望从表中删除所有行。 在这种情况下，通常使用不带`WHERE`子句的DELETE语句。 以下示例创建一个名为`customer_groups`的新表，并向此表中插入一些行： ~~~sql CREATE TABLE sales.customer_groups ( group_id INT PRIMARY KEY IDENTITY, group_name VARCHAR (50) NOT NULL ); INSERT INTO sales.customer_groups (group_name) VALUES ('公司内部'), ('第三方'), ('一次购买'); ~~~ 要删除`customer_groups`表中的所有行，请使用`DELETE`语句，如下所示： ~~~sql DELETE FROM sales.customer_groups; ~~~ 除使用`DELETE FROM`语句外，还可以使用`TRUNCATE TABLE`语句删除表中的所有行。 以下是`TRUNCATE TABLE`语句的语法： ~~~sql TRUNCATE TABLE [database_name.][schema_name.]table_name; ~~~ 在此语法中，首先，`table_name`指定要从中删除所有行的表的名称。 其次，`database_name`\-数据库名称是创建表的数据库的名称。 数据库名称是可选的。 如果未指定数据名称，该语句将删除当前连接的数据库中的表。 以下语句首先将一些行插入`customer_groups`表，然后使用`TRUNCATE TABLE`语句从中删除所有行： ~~~sql INSERT INTO sales.customer_groups (group_name) VALUES ('公司内部'), ('第三方'), ('一次购买'); TRUNCATE TABLE sales.customer_groups; ~~~ `TRUNCATE TABLE`类似于没有`WHERE`子句的`DELETE`语句。 但是，`TRUNCATE`语句执行得更快，并且使用的系统和事务日志资源更少。 ## TRUNCATE TABLE与DELETE比较 与`DELETE`语句相比，`TRUNCATE TABLE`具有以下优点： #### 1\. 使用较少的事务日志 `DELETE`语句一次删除一行，并在事务日志中为每个删除的行插入一个条目。 另一方面，`TRUNCATE TABLE`语句通过释放用于存储表数据的数据页来删除数据，并仅在事务日志中插入页面解除分配。 #### 2\. 使用更少的锁 使用行锁执行`DELETE`语句时，表中的每一行都被锁定以便删除。 `TRUNCATE TABLE`锁定表和页，而不是每一行。 #### 3\. 标识重置 如果要截断的表具有标识列，则当使用`TRUNCATE TABLE`语句删除数据后，具有标识列的计数器将重置为开始的值(一般是：`1`)。 >[danger] ## 3.4.8[重命名表]演示如何将表重命名为新表的过程 #### 1\. 使用Transact SQL重命名表 SQL Server没有任何直接重命名表的语句。 但是，它提供了一个名为`sp_rename`的存储过程，用于更改表的名称。 下面显示了使用`sp_rename`存储过程更改表名称的语法： ~~~sql EXEC sp_rename 'old_table_name', 'new_table_name' ~~~ 请注意，要更改表的旧名称和新名称必须用单引号括起来。 下面来看一个例子。 首先，创建一个名为`sales.contr`的新表来存储销售合同的数据： ~~~sql CREATE TABLE sales.contr ( contract_no INT IDENTITY PRIMARY KEY, start_date DATE NOT NULL, expired_date DATE, customer_id INT, amount DECIMAL (10, 2) ); ~~~ 然后，使用`sp_rename`存储过程将`sales.contr`表重命名为`sales.contract`： ~~~sql EXEC sp_rename 'sales.contr', 'sales.contracts'; ~~~ #### 2\. SQL Server使用SSMS重命名表 重命名表的另一种方法是使用SQL Server Management Studio提供的功能。 以下示例说明如何将`contr`表重命名为`contracts`。首先，右键单击表名，然后选择“重命名”菜单项：   接下来，填入表的新名称，例如`contract`，然后按*Enter* 键：