>[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* 鍵：