全文參考《SQL Server Basics》

數據定義 (DDL)	查詢語句	說明	數據定義 (DDL)	查詢語句	說明
創建數據庫	CREATE DATABASE	在SQL Server實例中創建新數據庫。	刪除數據庫	DROP DATABASE	刪除現有數據庫。
創建架構	CREATE SCHEMA	在數據庫中創建新架構。	修改架構	ALTER SCHEMA	在同一數據庫內將安全對象從一個架構轉移到另一個架構。
刪除架構	DROP SCHEMA	從數據庫中刪除架構。	創建表	CREATE TABLE	在數據庫特定架構中創建新表。
身份列	Identity column	使用IDENTITY屬性為表創建身份列。	序列	Sequence	根據規范生成一系列數值。
添加列	ALTER TABLE ADD column	向表中添加一個或多個列。	修改列定義	ALTER TABLE ALTER COLUMN	修改表中現有列的定義。
刪除列	ALTER TABLE DROP COLUMN	從表中刪除一個或多個列。	計算列	Computed columns	在多個查詢中重用計算邏輯。
刪除表	DROP TABLE	從數據庫中刪除表。	清空表	TRUNCATE TABLE	快速刪除表中所有數據。
查詢創建表	SELECT INTO	創建表并插入查詢結果。	重命名表	Rename a table	將表重命名為新的名稱。
臨時表	Temporary tables	用于存儲過程或數據庫會話中臨時數據。	同義詞	Synonym	為數據庫對象創建同義詞。

一、數據庫與架構

1.1 創建與刪除數據庫

1.1.1 使用CREATE DATABASE語句創建數據庫

CREATE DATABASE語句是SQL Server中用于創建新數據庫的核心命令，其基本語法如下：

CREATE DATABASE database_name;

• database_name：數據庫名稱，必須是唯一的，且需符合SQL Server標識符的規則，通常最大長度為128個字符。

例如，以下語句創建了一個名為TestDb的新數據庫：

CREATE DATABASE TestDb;

執行該語句后，新創建的數據庫將出現在SQL Server的對象資源管理器中。如果未顯示，可以通過點擊刷新按鈕或按F5鍵來更新對象列表。

此外，可以通過以下查詢語句查看SQL Server中的所有數據庫：

SELECTname
FROMmaster.sys.databases
ORDER BYname;

或者執行存儲過程sp_databases：

EXEC sp_databases;

1.1.2 使用DROP DATABASE語句刪除數據庫

DROP DATABASE語句用于從SQL Server實例中刪除現有的數據庫。其語法如下：

DROP DATABASE [IF EXISTS] database_name[,database_name2,...];

• database_name：數據庫名稱，以逗號進行分隔

• IF EXISTS：可選。僅當數據庫存在時才執行刪除操作（從SQL Server 2016（13.x）開始）。如果嘗試刪除一個不存在的數據庫且未指定IF EXISTS選項，SQL Server將拋出錯誤。

比如刪除TestDb數據庫：

DROP DATABASE IF EXISTS TestDb;

在刪除數據庫之前，需要確保以下重要事項：

• 提前備份：DROP DATABASE語句會刪除數據庫及其物理磁盤文件。因此，如果需要在未來恢復數據庫，應提前備份數據庫。

• 刪除當前正在使用的數據庫將導致以下錯誤：

  Cannot drop database "database_name" because it is currently in use.
  

1.1.3 使用SSMS創建數據庫

1. 在SSMS中，右鍵單擊數據庫，選擇新建數據庫… 菜單項。
   

2. 在彈出的對話框中輸入數據庫名稱（例如SampleDb），然后點擊確定按鈕。
   

3. 新創建的數據庫將出現在對象資源管理器中。
   

1.1.4 使用SSMS刪除數據庫

1. 在SSMS中，右鍵單擊要刪除的數據庫名稱，選擇刪除菜單項。
   

2. 在彈出的對話框中，取消選中刪除備份和還原歷史信息復選框，選中關閉現有連接復選框，然后點擊確定按鈕刪除數據庫。
   

3. 從對象資源管理器中驗證數據庫是否已被刪除。
   

1.2 CREATE SCHEMA：創建新架構

1.2.1 Schema簡介

在SQL Server中，架構（Schema）是一個重要的概念，用于組織和管理數據庫中的對象。以下是關于架構的詳細說明：

1. 架構的定義：架構是一個數據庫對象的集合，包括表、視圖、觸發器、存儲過程、索引等。這些對象通過架構進行邏輯上的分組和管理，使得數據庫的結構更加清晰和易于維護。此外，還提供了以下重要功能：

   • 安全性：通過架構可以限制用戶對某些對象的訪問權限，增強數據庫的安全性。
   • 邏輯分組：架構可以將相關的數據庫對象分組，便于管理和維護。
   • 避免命名沖突：通過架構名稱限定對象名稱，可以避免不同對象之間的命名沖突。

2. 架構的所有權：每個架構都與一個用戶名相關聯，該用戶稱為架構所有者（Schema Owner）。架構所有者是邏輯上相關的數據庫對象的所有者，負責管理這些對象的權限和操作。

3. 架構與數據庫的關系：架構總是屬于一個數據庫，而一個數據庫可以包含一個或多個架構。例如，在BikeStores示例數據庫中，存在sales和production兩個架構，分別用于管理銷售相關的對象和生產相關的對象。

4. 對象命名；架構中的對象可以通過schema_name.object_name的格式進行限定，這種命名方式可以避免命名沖突。例如，sales.orders和production.orders分別表示sales架構和production架構中的orders表。

5. 內置架構
   SQL Server提供了一些預定義的架構，其名稱與內置數據庫用戶和角色相同。這些內置架構包括：

   • dbo：默認的架構，由數據庫所有者（dbo）擁有。大多數用戶創建的對象默認屬于dbo架構，由dbo用戶賬戶擁有。用戶可以通過顯式指定架構名稱來創建對象，也可以通過設置用戶的默認架構來改變其對象的默認存儲位置。
   • guest：用于允許數據庫中的匿名訪問。
   • sys：用于存儲系統級別的對象和元數據。用戶無法在sys架構中創建或刪除對象。
   • INFORMATION_SCHEMA：用于存儲數據庫的元數據信息，例如表、列、約束等。用戶同樣無法在INFORMATION_SCHEMA架構中創建或刪除對象。

通過合理使用架構，可以提高數據庫的可維護性和可擴展性，同時增強數據的安全性和一致性。

sys和INFORMATION_SCHEMA架構被SQL Server保留用于系統對象，因此無法在這些架構中創建或刪除任何對象。

1.2.2 使用CREATE SCHEMA語句創建架構

CREATE SCHEMA語句允許在當前數據庫中創建新的架構，其簡化語法如下：

CREATE SCHEMA schema_name[AUTHORIZATION owner_name];

• schema_name：指定的架構名稱。
• owner_name：架構所有者名稱，指定在AUTHORIZATION關鍵字之后。

以下示例創建名為customer_services的架構：

CREATE SCHEMA customer_services;
GO

GO命令指示SQL Server Management Studio將GO之前的SQL語句發送到服務器執行。執行該語句后，可以在數據庫的安全性 > 架構下找到新創建的架構。

如果需要列出當前數據庫中的所有架構，可以查詢sys.schemas視圖：

SELECTs.name AS schema_name,u.name AS schema_owner
FROMsys.schemas s
INNER JOIN sys.sysusers u ON u.uid = s.principal_id
ORDER BYs.name;

1.2.3 在架構中創建對象

創建架構后，可以在架構中創建對象。例如，以下語句在customer_services架構中創建了一個名為jobs的新表：

CREATE TABLE customer_services.jobs(job_id INT PRIMARY KEY IDENTITY,customer_id INT NOT NULL,description VARCHAR(200),created_at DATETIME2 NOT NULL
);

1.3 ALTER SCHEMA：更改對象的架構歸屬

1.3.1 ALTER關鍵字

ALTER 是 SQL 中用于修改數據庫對象結構的關鍵字。它允許你在不刪除現有對象的情況下，動態調整其定義或屬性，包括：

1. 修改表結構（ALTER TABLE ）
   • 添加、刪除或修改表中的列。
   • 添加、刪除或修改表的約束（如主鍵、外鍵、唯一約束等）。
2. 修改索引：添加、刪除或修改索引的屬性。
3. 修改視圖
4. 修改存儲過程、函數或觸發器

操作類型	語法	說明
添加列	ALTER TABLE table_name ADD column_name data_type [column_constraint];	向表中添加一個新列，可以指定數據類型和約束。
刪除列	ALTER TABLE table_name DROP COLUMN column_name;	刪除表中的指定列。
修改列	ALTER TABLE table_name ALTER COLUMN column_name new_data_type;	修改列的數據類型或約束。
添加主鍵約束	ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column_name);	為表添加主鍵約束。
刪除主鍵約束	ALTER TABLE table_name DROP CONSTRAINT constraint_name;	刪除表中的主鍵約束。

1.3.2 ALTER SCHEMA基本語法

??ALTER SCHEMA語句允許在同一個數據庫內，將一個可保護對象從一個架構轉移到另一個架構。可保護對象是指數據庫引擎授權系統控制訪問的資源，例如表、視圖、存儲過程等。ALTER SCHEMA語句的基本語法如下：

ALTER SCHEMA target_schema_nameTRANSFER [ entity_type :: ] securable_name;

• target_schema_name：目標架構的名稱，即要將對象移動到的架構名稱。注意，目標架構不能是SYS或INFORMATION_SCHEMA。
• entity_type：可選參數，可以是Object、Type或XML Schema Collection，默認值為Object。它表示要更改所有權的實體類別。
• securable_name：要移動的可保護對象的名稱。

注意事項：

1. 在移動存儲過程、函數、視圖或觸發器時，建議直接在新架構中重新創建這些對象，而不是使用ALTER SCHEMA，因為SQL Server不會自動更新這些對象的架構名稱。
2. 移動表或同義詞后，需要手動更新對這些對象的引用，確保它們指向新的架構名稱，以避免錯誤（SQL Server不會自動更新對這些對象的引用）。

1.3.3 簡單示例

1. 創建表和存儲過程：首先，在dbo架構中創建一個名為offices的表，并插入一些數據

   CREATE TABLE dbo.offices
   (office_id      INT PRIMARY KEY IDENTITY,office_name    NVARCHAR(40) NOT NULL,office_address NVARCHAR(255) NOT NULL,phone          VARCHAR(20)
   );
   

   INSERT INTO dbo.offices (office_name, office_address)
   VALUES('Silicon Valley', '400 North 1st Street, San Jose, CA 95130'),('Sacramento', '1070 River Dr., Sacramento, CA 95820');
   

   接下來，創建一個存儲過程usp_get_office_by_id，用于根據ID查找office：

   CREATE PROC usp_get_office_by_id (@id INT
   ) AS
   BEGINSELECT *FROM dbo.officesWHERE office_id = @id;
   END;
   

2. 移動表到新架構：使用ALTER SCHEMA語句將dbo.offices表移動到sales架構：

   ALTER SCHEMA sales TRANSFER OBJECT::dbo.offices;
   

3. 處理存儲過程中的引用問題：
   移動表后，執行存儲過程usp_get_office_by_id會報錯，因為存儲過程仍然引用了dbo.offices表。

   Msg 208, Level 16, State 1, Procedure usp_get_office_by_id, Line 5 [Batch Start Line 30]
   Invalid object name 'dbo.offices'.
   

   此時需要手動修改存儲過程中的表名，以反映新的架構名稱：

   ALTER PROC usp_get_office_by_id (@id INT
   ) AS
   BEGINSELECT *FROM sales.officesWHERE office_id = @id;
   END;
   

1.4DROP SCHEMA：刪除架構

1.4.1 語法

DROP SCHEMA語句允許從數據庫中刪除一個架構。其語法如下：

DROP SCHEMA [IF EXISTS] schema_name;

• schema_name：指定要刪除的架構名稱。刪除之前，必須確保架構中沒有任何對象（如表、視圖、存儲過程等），否則刪除操作將失敗
• IF EXISTS：可選參數，使用IF EXISTS選項可以避免因嘗試刪除不存在的架構而導致的錯誤。
• 手動更新引用 ：如果架構中的對象被其他數據庫對象（如存儲過程、視圖等）引用，刪除架構前需要手動更新這些引用，或者先刪除這些引用對象。

1.4.2 簡單示例

1. 創建架構和表 ：創建一個名為logistics的新架構，然后，在logistics架構中創建一個名為deliveries的新表：

   CREATE SCHEMA logistics;
   GOCREATE TABLE logistics.deliveries
   (order_id        INT PRIMARY KEY,delivery_date   DATE NOT NULL,delivery_status TINYINT NOT NULL
   );
   

2. 嘗試刪除架構

   DROP SCHEMA logistics;
   

   由于logistics架構中包含deliveries表，SQL Server會拋出以下錯誤：

   Msg 3729, Level 16, State 1, Line 1
   Cannot drop schema 'logistics' because it is being referenced by object 'deliveries'.
   

3. 刪除架構中的對象
   在刪除架構之前，必須先刪除架構中的所有對象。因此，先刪除logistics.deliveries表：

   DROP TABLE logistics.deliveries;
   

4. 再次刪除架構
   確保架構中沒有對象后，再次執行DROP SCHEMA語句。這次操作將成功刪除logistics架構。

   DROP SCHEMA IF EXISTS logistics;
   

二、表創建

2.1 CREATE TABLE ：創建新表

2.1.1 基礎語法

??在 SQL Server 中，CREATE TABLE 語句是用于創建新表。表是數據庫中存儲數據的基本結構，每個表都有一個唯一的名稱，并且屬于特定的數據庫和架構（schema）。表由一個或多個列組成，每列都有一個數據類型，用于定義該列可以存儲的數據種類（如數字、字符串或時間數據）。以下是 CREATE TABLE 語句的基本語法：

在 SQL 中，CREATE TABLE 語句用于創建一個新的表：

CREATE TABLE [database_name.][schema_name.]table_name (column_name data_type [CONSTRAINTS] [DEFAULT default_value] [IDENTITY],pk_column data_type PRIMARY KEY,column_1 data_type NOT NULL,column_2 data_type,...,table_constraints
);

1. database_name：可選參數，用于指定表所屬的數據庫。如果未明確指定，表將被創建在當前數據庫中。

2. schema_name：可選參數，用于指定表所屬的架構。如果未指定，表將被創建在當前用戶的默認架構中，通常為 dbo。

3. table_name：必填參數，用于指定新表的名稱。表名在所屬架構中必須是唯一的。

4. 列定義：每列都有一個數據類型，用于指定該列可以存儲的數據類型，比如INT（整型）、VARCHAR(n)（可變長度的字符串類型）、DATETIME（日期時間類型）。

5. 約束（CONSTRAINTS）：約束是應用于表或列的規則，用于限制可以存儲在數據庫中的數據。它們充當數據驗證器，確保所有數據修改操作（INSERT、UPDATE、DELETE）都符合預定義的業務規則。

2.1.2 表創建示例

以下是一個創建表的示例，該表用于跟蹤客戶在商店的訪問記錄：

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)
);

在這個示例中：

• visit_id 列：作為表的主鍵，使用 IDENTITY(1,1) 自動為每一行生成一個唯一的整數值，從 1 開始，每次遞增 1。
• first_name 和 last_name 列：使用 VARCHAR(50) 數據類型，可以存儲最多 50 個字符的字符串，并且不允許為空。
• visited_at 列：使用 DATETIME 數據類型，用于記錄客戶訪問商店的日期和時間。
• phone 列：使用 VARCHAR(20) 數據類型，允許為空。
• store_id 列：存儲商店的標識符，該列不允許為空。
• 外鍵約束：FOREIGN KEY 約束確保 visits 表中的 store_id 列的值必須存在于 stores 表的 store_id 列中。

2.1.3 列約束與表約束

列約束	關鍵字	作用描述	是否允許 NULL	是否自動 創建索引	示例
主鍵約束	PRIMARY KEY	唯一標識表中的每一行，每個表只能有一個主鍵 主鍵列的值必須唯一且不允許為空。	不允許	是	EmployeeID INT PRIMARY KEY
非空約束	NOT NULL	列值不能為NULL	不允許	否	Name VARCHAR(50) NOT NULL
唯一約束	UNIQUE	列值必須唯一，但允許多個NULL值	允許	是	Email VARCHAR(100) UNIQUE
檢查約束	CHECK	限制列值的范圍或格式	允許	否	Age INT CHECK (Age >= 18)
默認約束	DEFAULT	當插入數據未指定值時，自動填充預設值	允許	否	LogTime DATETIME DEFAULT GETDATE()
自增屬性	IDENTITY	自動生成遞增的數值（SQL Server特有）	不允許	否	CustomerID INT IDENTITY(1,1)

表級約束單獨定義在列定義之后，適用于復雜規則和多列約束：

1. 復合主鍵：多列組合才能唯一標識行時使用。

   CREATE TABLE OrderDetails (OrderID INT,ProductID INT,Quantity INT,CONSTRAINT PK_OrderDetails PRIMARY KEY (OrderID, ProductID)  -- 兩列組合主鍵
   );
   

2. 外鍵約束：用于建立兩個表之間的關系。外鍵列的值必須存在于引用表的對應列中。

   CREATE TABLE Orders (OrderID INT PRIMARY KEY,CustomerID INT NOT NULL,CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
   );
   

3. 檢查約束（多列）：

   CREATE TABLE EmployeeSalaries (EmployeeID INT PRIMARY KEY,BaseSalary DECIMAL(10,2),Bonus DECIMAL(10,2),CONSTRAINT CHK_Salary CHECK (BaseSalary > 0 AND Bonus >= 0 AND Bonus <= BaseSalary * 0.3)
   );
   

4. 唯一約束（多列）

   CREATE TABLE ClassRegistrations (StudentID INT,CourseID INT,Semester VARCHAR(10),CONSTRAINT UQ_Registration UNIQUE (StudentID, CourseID, Semester)  -- 防止重復注冊
   );
   

??列約束可匿名（系統自動生成名稱，如PK__Employe__7AD04FF10EA330E9），表約束必須顯式命名。匿名難以識別約束類型和目的，推薦按如下約定進行命名：

約束類型	命名模式	示例
主鍵	PK_表名	PK_Employees
外鍵	FK_子表_主表	FK_Orders_Customers
唯一	UQ_表名_列名	UQ_Users_Email
檢查	CHK_表名_描述	CHK_Products_PricePositive

查看現有約束：

-- 查看表的所有約束
SELECT * FROM sys.key_constraints
WHERE parent_object_id = OBJECT_ID('表名');-- 查看檢查約束
SELECT * FROM sys.check_constraints;

主鍵和唯一約束會自動創建索引，外鍵列建議手動添加索引以提高連接性能，過多檢查約束可能影響寫入性能。

2.2 IDENTITY：創建自增列

• 使用 SQL Server 的 IDENTITY 屬性可以為表創建自增列。 IDENTITY 列的值會自動遞增，無需手動插入。
• SQL Server 不會重用 IDENTITY 值，事務回滾后會導致值的間隙。可以使用 DBCC CHECKIDENT 命令重置 IDENTITY 列的值。

??IDENTITY 屬性用于為表創建一個自增列，自動為每一行生成一個唯一的值，無需手動插入（通常用于主鍵列）。 IDENTITY 語法如下：

IDENTITY[(seed, increment)]

• seed：第一行的初始值，默認為 1。
• increment：每次遞增的值，默認為 1。

2.2.1 基礎示例

以下是一個創建帶有 IDENTITY 屬性的表的示例：

CREATE SCHEMA hr;

CREATE TABLE hr.person (-- 設置person_id列為主鍵列，也是自增列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
);

插入數據時，無需顯式指定 IDENTITY 列的值，這一列的值會由SQL Server 自動生成。

INSERT INTO hr.person (first_name, last_name, gender)
OUTPUT inserted.person_id
VALUES ('John', 'Doe', 'M');

輸出結果：

person_id
-----------
1

再次插入一行：

INSERT INTO hr.person (first_name, last_name, gender)
OUTPUT inserted.person_id
VALUES ('Jane', 'Doe', 'F');

輸出結果：

person_id
-----------
2

可以看到，person_id 的值自動遞增。

2.2.2 IDENTITY 特性：不重用值

??SQL Server 不會重用 IDENTITY 值。如果插入操作失敗或被回滾，已使用的 IDENTITY 值將丟失，不會再次生成。這可能導致 IDENTITY 列中出現值的間隙。

1. 創建position和person_position表

   CREATE TABLE hr.position (position_id INT IDENTITY (1, 1) PRIMARY KEY,position_name VARCHAR (255) NOT NULL,);CREATE TABLE hr.person_position (person_id INT,position_id INT,PRIMARY KEY (person_id, position_id),FOREIGN KEY (person_id) REFERENCES hr.person (person_id),FOREIGN KEY (position_id) REFERENCES hr. POSITION (position_id)
   );
   

2. 使用以事務嘗試插入一條記錄并分配職位：

   BEGIN TRANSACTION
   BEGIN TRY-- 插入新人員INSERT INTO hr.person (first_name, last_name, gender)VALUES ('Joan', 'Smith', 'F');-- 分配職位INSERT INTO hr.person_position (person_id, position_id)VALUES (@@IDENTITY, 1);
   END TRY
   BEGIN CATCHIF @@TRANCOUNT > 0ROLLBACK TRANSACTION;
   END CATCHIF @@TRANCOUNT > 0COMMIT TRANSACTION;
   GO
   

   如果第二個 INSERT 語句失敗（例如，position_id 不存在），整個事務將回滾。此時，第一個 INSERT 消耗的 IDENTITY 值（例如 3）將丟失，下一個 IDENTITY 值將是 4。

2.2.3 重置 IDENTITY

如果需要重置 IDENTITY 列的值，可以使用 DBCC CHECKIDENT 命令：

DBCC CHECKIDENT ('[TableName]', RESEED, 0);
GO

例如，刪除 hr.person 表中的所有行后，重置 IDENTITY 列的值：

DELETE FROM hr.person;DBCC CHECKIDENT ('hr.person', RESEED, 0);
GO

執行后，插入新行時，IDENTITY 列的值將從 1 開始：

INSERT INTO hr.person (first_name, last_name, gender)
OUTPUT inserted.person_id
VALUES ('Jhoan', 'Smith', 'F');

輸出結果：

person_id
-----------
1

2.3 PRIMARY KEY 約束

??在 SQL Server 中，PRIMARY KEY 約束用于為表創建主鍵。主鍵是表中的一列或多列，用于唯一標識表中的每一行。每個表只能包含一個主鍵，且所有參與主鍵的列都必須定義為 NOT NULL，其語法為：

-- 創建單列主鍵
CREATE TABLE table_name (pk_column data_type PRIMARY KEY,...
);

-- 創建多列主鍵
CREATE TABLE table_name (pk_column_1 data_type,pk_column_2 data type,...PRIMARY KEY (pk_column_1, pk_column_2)
);

• SQL Server 會自動為所有主鍵列設置 NOT NULL 約束，即使沒有顯式指定。
• SQL Server 會在創建主鍵時自動創建一個唯一聚集索引（或非聚集索引，如果指定了）。

1. 創建單列主鍵：如果主鍵只包含一列，可以使用列約束來定義 PRIMARY KEY 約束。

   CREATE TABLE sales.activities (activity_id INT PRIMARY KEY IDENTITY,activity_name VARCHAR(255) NOT NULL,activity_date DATE NOT NULL
   );
   

   在這個例子中，activity_id 列是主鍵列，列中的每個值都是唯一的。IDENTITY 屬性用于自動為 activity_id 列生成唯一的整數值。

2. 創建多列主鍵：如果主鍵包含兩列或多列，則必須使用表約束來定義 PRIMARY KEY 約束。

   CREATE TABLE sales.participants(activity_id int,customer_id int,PRIMARY KEY(activity_id, customer_id)
   );
   

   在這個例子中，activity_id 或 customer_id 列中的值可以重復，但這兩列的值組合必須是唯一的。

3. 為現有表添加主鍵
   通常，表在創建時會定義主鍵，如果沒有，可以使用 ALTER TABLE 語句為表添加主鍵。例如，以下代碼創建了一個沒有主鍵的表：

   CREATE TABLE sales.events(event_id INT NOT NULL,event_name VARCHAR(255),start_date DATE NOT NULL,duration DEC(5,2)
   );
   

   要將 event_id 列設置為主鍵，可以使用以下 ALTER TABLE 語句：

   ALTER TABLE sales.events
   ADD PRIMARY KEY(event_id);
   

   需要注意的是，如果 sales.events 表中已經有數據，在將 event_id 列提升為主鍵之前，必須確保 event_id 列中的值是唯一的。

2.4 FOREIGN KEY 約束：創建表之間的關系

在 SQL Server 中，FOREIGN KEY 約束用于在兩個表之間建立關聯，確保數據的引用完整性。例如，考慮以下 vendor_groups 和 vendors 表：

CREATE TABLE procurement.vendor_groups (group_id INT IDENTITY PRIMARY KEY,group_name VARCHAR(100) NOT NULL
);CREATE TABLE procurement.vendors (vendor_id INT IDENTITY PRIMARY KEY,vendor_name VARCHAR(100) NOT NULL,group_id INT NOT NULL,
);

??在這個例子中，vendors 表和 vendor_groups 表之間存在一對多的關系，即每個供應商屬于一個供應商組，而每個供應商組可以包含多個供應商。然而，當前的表結構存在一個問題：可以在沒有對應供應商組的情況下插入供應商，或者刪除供應商組而不更新或刪除對應的供應商，這會導致孤立的行出現。

??為了解決這個問題，我們需要在 vendors 表中建立一個外鍵。外鍵是一個表中的一列或多列，用于唯一標識另一表中的一行（也可以是同一表的自引用）。通過建立外鍵，可以確保 vendors 表中的數據與 vendor_groups 表中的數據保持一致，避免孤立的行出現。

2.4.1 創建 FOREIGN KEY 約束

創建外鍵約束的語法如下：

CONSTRAINT fk_constraint_name
FOREIGN KEY (column_1, column_2,...)
REFERENCES parent_table_name(column_1, column_2,...)

• CONSTRAINT fk_constraint_name：指定外鍵約束的名稱。這是可選的，如果不指定，SQL Server 將自動生成一個名稱。
• FOREIGN KEY (column_1, column_2,...)：指定子表中的外鍵列。
• REFERENCES parent_table_name(column_1, column_2,...)：指定父表及其對應的列。

以下代碼刪除了現有的 vendors 表，并重新創建了一個帶有 FOREIGN KEY 約束的表：

DROP TABLE procurement.vendors;CREATE TABLE procurement.vendors (vendor_id INT IDENTITY PRIMARY KEY,vendor_name VARCHAR(100) NOT NULL,group_id INT NOT NULL,CONSTRAINT fk_group FOREIGN KEY (group_id)REFERENCES procurement.vendor_groups(group_id)
);

在上述代碼中，vendor_groups 表被稱為父表，即外鍵約束所引用的表。而 vendors 表被稱為子表，即應用外鍵約束的表。

2.4.2 應用FOREIGN KEY 約束

首先，向 vendor_groups 表中插入一些數據：

INSERT INTO procurement.vendor_groups(group_name)
VALUES('Third-Party Vendors'),('Interco Vendors'),('One-time Vendors');

然后，向 vendors 表中插入一個供應商及其所屬的供應商組：

INSERT INTO procurement.vendors(vendor_name, group_id)
VALUES('ABC Corp', 1);

這條語句按預期執行。

接下來，嘗試插入一個不存在于 vendor_groups 表中的供應商組的供應商：

INSERT INTO procurement.vendors(vendor_name, group_id)
VALUES('XYZ Corp', 4);

SQL Server 拋出了以下錯誤：

The INSERT statement conflicted with the FOREIGN KEY constraint "fk_group". The conflict occurred in database "BikeStores", table "procurement.vendor_groups", column 'group_id'.

在這個例子中，由于外鍵約束的存在，SQL Server 拒絕了插入操作并拋出了錯誤。

2.4.3 參考動作

外鍵約束確保了引用完整性，即只有在父表中存在對應的行時，才能在子表中插入行。此外，外鍵約束還允許定義當父表中的行被更新或刪除時的參考動作：

FOREIGN KEY (foreign_key_columns)REFERENCES parent_table(parent_key_columns)ON UPDATE actionON DELETE action;

• ON UPDATE 和 ON DELETE：指定當父表中的行被更新或刪除時，應執行的動作。允許的動作有 NO ACTION、CASCADE、SET NULL 和 SET DEFAULT。

1. 刪除父表中的行：當刪除父表中的一行或多行時，可以設置以下動作：

   • ON DELETE NO ACTION：SQL Server 拋出錯誤并回滾父表中的刪除操作。這是默認行為。
   • ON DELETE CASCADE：SQL Server 刪除子表中對應的行。
   • ON DELETE SET NULL：SQL Server 將子表中的行設置為 NULL，但外鍵列必須允許 NULL。
   • ON DELETE SET DEFAULT：SQL Server 將子表中的行設置為默認值，但外鍵列必須有默認值定義。

2. 更新父表中的行：當更新父表中的一行或多行時，可以設置以下動作：

   • ON UPDATE NO ACTION：SQL Server 拋出錯誤并回滾父表中的更新操作。
   • ON UPDATE CASCADE：SQL Server 更新子表中對應的行。
   • ON UPDATE SET NULL：SQL Server 將子表中的行設置為 NULL，但外鍵列必須允許 NULL。
   • ON UPDATE SET DEFAULT：SQL Server 將子表中的行設置為默認值。

2.5 CHECK 約束

2.5.1 單列CHECK 約束

??SQL Server 的 CHECK 約束用于確保表中某一列的值滿足特定的條件。它通過一個布爾表達式來限制列中可以接受的值。例如，為了確保產品單價為正數，可以使用以下代碼：

CREATE SCHEMA test;
GOCREATE TABLE test.products(product_id INT IDENTITY PRIMARY KEY,product_name VARCHAR(255) NOT NULL,unit_price DEC(10,2) CHECK(unit_price > 0)
);

1. 檢查約束的作用： CHECK 約束定義位于 data type 之后，它由關鍵字 CHECK 和括號中的邏輯表達式組成。CHECK(unit_price > 0) 確保 unit_price 列的值為正。如果嘗試插入不符合條件的數據，SQL Server 將拋出錯誤。

2. 命名約束：您還可以使用 CONSTRAINT 關鍵字為約束分配單獨的名稱（比如positive_price ）。約束命名有助于在錯誤消息中明確標識約束，并在需要修改約束時方便引用。不指定則SQL Server 將自動生成一個名稱。

   unit_price DEC(10,2) CONSTRAINT positive_price CHECK(unit_price > 0)
   

   此時插入負數會報錯：

   The INSERT statement conflicted with the CHECK constraint "positive_price". The conflict occurred in database "BikeStores", table "test.products", column 'unit_price'.
   

3. NULL值處理：CHECK 約束不會阻止 NULL 值的插入，因為 NULL 在布爾表達式中被視為 UNKNOWN。例如：

   INSERT INTO test.products(product_name, unit_price)
   VALUES ('Another Awesome Bike', NULL);
   

   這條語句可以成功執行，因為 unit_price 的值為 NULL，不會違反 CHECK 約束。如果需要阻止 NULL 值，可以結合使用 NOT NULL 約束。

2.5.2 多列 CHECK 約束

CHECK 約束可以引用多個列。例如，假設 test.products 表中存儲了常規價格和折扣價格，需要確保折扣價格總是低于常規價格：

CREATE TABLE test.products(product_id INT IDENTITY PRIMARY KEY,product_name VARCHAR(255) NOT NULL,unit_price DEC(10,2) CHECK(unit_price > 0),discounted_price DEC(10,2) CHECK(discounted_price > 0),CONSTRAINT valid_prices CHECK(discounted_price > unit_price)
);

在這個例子中，第三個 CHECK 約束是一個表約束，它確保 discounted_price 總是小于 unit_price。

您還可以將列約束編寫為 table 約束。但是，不能將表約束編寫為列約束。例如，您可以按如下方式重寫上述語句：

CREATE TABLE test.products(product_id INT IDENTITY PRIMARY KEY,product_name VARCHAR(255) NOT NULL,unit_price DEC(10,2),discounted_price DEC(10,2),CHECK(unit_price > 0),CHECK(discounted_price > 0),CHECK(discounted_price > unit_price)
);

或者是：

...CHECK(unit_price > 0),CHECK(discounted_price > 0 AND discounted_price > unit_price)
);

2.5.3 為現有表添加 CHECK 約束

可以使用 ALTER TABLE ADD CONSTRAINT 語句為現有表添加 CHECK 約束。例如：

ALTER TABLE test.products
ADD CONSTRAINT positive_price CHECK(unit_price > 0);

也可以為現有表添加新列并同時添加 CHECK 約束：

ALTER TABLE test.products
ADD discounted_price DEC(10,2)
CHECK(discounted_price > 0);

2.5.4 刪除 CHECK 約束

要刪除 CHECK 約束，可以使用 ALTER TABLE DROP CONSTRAINT 語句。例如：

ALTER TABLE test.products
DROP CONSTRAINT positive_price;

如果未為約束指定名稱，可以通過 sp_help 存儲過程查找約束名稱：

EXEC sp_help 'test.products';

此語句發出了很多信息，包括 constraint names：

2.5.5 禁用 CHECK 約束

要禁用 CHECK 約束，可以使用 ALTER TABLE NOCHECK CONSTRAINT 語句。例如：

ALTER TABLE test.products
NO CHECK CONSTRAINT valid_price;

2.6 UNIQUE 約束

UNIQUE 約束用于確保表中某一列或一組列中的數據是唯一的，其語法為如下。這里使用了表約束，并用逗號分隔列名。

CREATE TABLE table_name (key_column data_type PRIMARY KEY,column1 data_type,column2 data_type,column3 data_type,...,UNIQUE (column1,column2)
);

??盡管 UNIQUE 和 PRIMARY KEY 約束都強制數據的唯一性，但由于每個表只能有一個主鍵列，所以其它列的唯一性應使用 UNIQUE 約束。與 PRIMARY KEY 約束不同，UNIQUE 約束允許 NULL 值，但只允許每列有一個 NULL 值。

??SQL Server 沒有直接修改 UNIQUE 約束的語句，因此，如果您想更改約束，需要先刪除該約束，然后重新創建它。

2.6.1 創建約束

以下查詢中 email 列的數據在 hr.persons 表中是唯一的：

CREATE SCHEMA hr;
GOCREATE TABLE hr.persons(person_id INT IDENTITY PRIMARY KEY,first_name VARCHAR(255) NOT NULL,last_name VARCHAR(255) NOT NULL,email VARCHAR(255) UNIQUE
);

也可以使用表約束重寫以上代碼：

...email VARCHAR(255),UNIQUE(email)
);

??違反約束：當您在 SQL Server 中設置 UNIQUE 約束時，系統會自動創建一個 UNIQUE 索引，以確保相關列中的數據值不會重復。如果嘗試插入重復的數據，操作會被阻止，并且會收到一條錯誤消息，提示 UNIQUE 約束被違反。

Violation of UNIQUE KEY constraint 'UQ__persons__AB6E616417240E4E'. Cannot insert duplicate key in object 'hr.persons'. The duplicate key value is (j.doe@bike.stores).

??約束命名：如果沒有為 UNIQUE 約束指定單獨的名稱，SQL Server 將自動為其生成一個名稱。在此示例中，約束名稱為 UQ__persons__AB6E616417240E4E，可讀性差。可使用 CONSTRAINT 關鍵字為其命名：

...email VARCHAR(255),CONSTRAINT unique_email UNIQUE(email)
);

2.6.2 為多列定義 UNIQUE 約束

以下示例創建了一個由兩列 person_id 和 skill_id 組成的 UNIQUE 約束：

CREATE TABLE hr.person_skills (id INT IDENTITY PRIMARY KEY,person_id int,skill_id int,updated_at DATETIME,UNIQUE (person_id, skill_id)
);

2.6.3 為現有列添加 UNIQUE 約束

當您向表中的現有列或一組列添加 UNIQUE 約束時，SQL Server 會首先檢查這些列中的現有數據，以確保所有值都是唯一的。如果發現重復值，SQL Server 將返回錯誤并拒絕添加 UNIQUE 約束。

以下是向表添加 UNIQUE 約束的語法：

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
UNIQUE(column1, column2,...);

以下語句為 email 列添加 UNIQUE 約束：

ALTER TABLE hr.persons
ADD CONSTRAINT unique_email UNIQUE(email);

2.6.4 刪除 UNIQUE 約束

要刪除 UNIQUE 約束，可以使用 ALTER TABLE DROP CONSTRAINT 語句：

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

以下語句從 hr.persons 表中刪除 unique_phone 約束：

ALTER TABLE hr.persons
DROP CONSTRAINT unique_phone;

2.7 NOT NULL 約束

2.7.1 創建NOT NULL 約束

SQL Server 的 NOT NULL 約束用于確保表中的某一列不能包含 NULL 值。例如，以下代碼創建了一個表，其中 first_name、last_name 和 email 列都被設置為 NOT NULL：

CREATE SCHEMA hr;
GOCREATE TABLE hr.persons(person_id INT IDENTITY PRIMARY KEY,first_name VARCHAR(255) NOT NULL,last_name VARCHAR(255) NOT NULL,email VARCHAR(255) NOT NULL,phone VARCHAR(20)
);

2.7.2 為現有列添加 NOT NULL 約束

要為現有列添加 NOT NULL 約束，需要先確保該列中沒有 NULL 值。可以按照以下步驟操作：

1. 更新表，確保列中沒有 NULL 值：

   UPDATE table_name
   SET column_name = <value>
   WHERE column_name IS NULL;
   

2. 修改表，更改列的屬性：

   ALTER TABLE table_name
   ALTER COLUMN column_name data_type NOT NULL;
   

例如，要為 hr.persons 表中的 phone 列添加 NOT NULL 約束，可以使用以下語句：

1. 如果某個人沒有電話號碼，則將其更新為公司電話號碼，例如 (408) 123 4567：

   UPDATE hr.persons
   SET phone = "(408) 123 4567"
   WHERE phone IS NULL;
   

2. 修改 phone 列的屬性：

   ALTER TABLE hr.persons
   ALTER COLUMN phone VARCHAR(20) NOT NULL;
   

2.7.3 刪除 NOT NULL 約束

要從列中刪除 NOT NULL 約束，可以使用 ALTER TABLE ALTER COLUMN 語句：

ALTER TABLE table_name
ALTER COLUMN column_name data_type NULL;

例如，要從 phone 列中刪除 NOT NULL 約束，可以使用以下語句：

ALTER TABLE hr.persons
ALTER COLUMN phone VARCHAR(20) NULL;

三、表操作

3.1 SEQUENCE：序列

3.1.1 創建序列

??在 SQL 中，序列是一個數據庫對象，專門用來生成一串有序的數字。這些數值可以是遞增或遞減的，并且可以指定步長和范圍。序列還可以設置為循環（即達到最大值后重新從最小值開始）。以下是序列生成的基本語法：

CREATE SEQUENCE [schema_name.] sequence_name[ AS integer_type ][ START WITH start_value ][ INCREMENT BY increment_value ][ { MINVALUE [ min_value ] } | { NO MINVALUE } ][ { MAXVALUE [ max_value ] } | { NO MAXVALUE } ][ CYCLE | { NO CYCLE } ][ { CACHE [ cache_size ] } | { NO CACHE } ];

• sequence_name：序列的名稱，必須在當前數據庫中唯一。
• AS integer_type：指定序列的數據類型，如 TINYINT、SMALLINT、INT、BIGINT 或 DECIMAL。默認為 BIGINT。
• START WITH start_value：指定序列的起始值。默認為數據類型的最小值（遞增）或最大值（遞減）。
• INCREMENT BY increment_value：指定序列的增量值。如果為負數，則序列遞減；否則遞增。增量值不能為零。
• MINVALUE min_value：指定序列的最小值。默認為數據類型的最小值。
• MAXVALUE max_value：指定序列的最大值。默認為數據類型的最大值。
• CYCLE：當序列達到最大值或最小值時，是否重新開始。默認為 NO CYCLE。
• CACHE cache_size：指定緩存的序列值數量，以提高性能。默認為 NO CACHE。

以下示例創建了一個名為 item_counter 的序列，從 10 開始，每次遞增 10：

CREATE SEQUENCE item_counterAS INTSTART WITH 10INCREMENT BY 10;

使用 NEXT VALUE FOR 函數獲取序列的當前值：

SELECT NEXT VALUE FOR item_counter;

輸出結果：

Current_value
-------------
10

每次執行該語句時，序列值會遞增：

SELECT NEXT VALUE FOR item_counter;

輸出結果：

Current_value
-------------
20

可以通過查詢 sys.sequences 視圖來獲取序列的詳細信息：

SELECT *
FROM sys.sequences;

3.1.2 在單個表中使用序列

以下示例創建了一個名為 procurement.purchase_orders 的表，并使用序列生成 order_id：

CREATE SCHEMA procurement;
GOCREATE TABLE procurement.purchase_orders (order_id INT PRIMARY KEY,vendor_id INT NOT NULL,order_date DATE NOT NULL
);CREATE SEQUENCE procurement.order_numberAS INTSTART WITH 1INCREMENT BY 1;INSERT INTO procurement.purchase_orders (order_id, vendor_id, order_date)
VALUES (NEXT VALUE FOR procurement.order_number, 1, '2019-04-30');INSERT INTO procurement.purchase_orders (order_id, vendor_id, order_date)
VALUES (NEXT VALUE FOR procurement.order_number, 2, '2019-05-01');INSERT INTO procurement.purchase_orders (order_id, vendor_id, order_date)
VALUES (NEXT VALUE FOR procurement.order_number, 3, '2019-05-02');

查詢表內容：

SELECT order_id, vendor_id, order_date
FROM procurement.purchase_orders;

3.1.3 在多個表中使用序列

以下示例創建了一個名為 procurement.receipt_no 的序列，并在兩個表中共享該序列：

CREATE SEQUENCE procurement.receipt_noSTART WITH 1INCREMENT BY 1;CREATE TABLE procurement.goods_receipts (receipt_id INT PRIMARY KEY DEFAULT (NEXT VALUE FOR procurement.receipt_no),order_id INT NOT NULL,full_receipt BIT NOT NULL,receipt_date DATE NOT NULL,note NVARCHAR(100)
);CREATE TABLE procurement.invoice_receipts (receipt_id INT PRIMARY KEY DEFAULT (NEXT VALUE FOR procurement.receipt_no),order_id INT NOT NULL,is_late BIT NOT NULL,receipt_date DATE NOT NULL,note NVARCHAR(100)
);INSERT INTO procurement.goods_receipts (order_id, full_receipt, receipt_date, note)
VALUES (1, 1, '2019-05-12', 'Goods receipt completed at warehouse');INSERT INTO procurement.invoice_receipts (order_id, is_late, receipt_date, note)
VALUES (1, 0, '2019-05-13', 'Invoice duly received');

查詢兩個表的內容：

SELECT * FROM procurement.goods_receipts;
SELECT * FROM procurement.invoice_receipts;

3.1.4 SEQUENCEVS IDENTITY

序列和 IDENTITY 都可以生成唯一的數值，但它們有以下主要區別：

特性/功能	IDENTITY	序列對象
允許指定最小值和最大值	不支持	支持
允許重置數值	不支持	支持
允許緩存數值生成	不支持	支持
允許指定起始值	支持	支持
允許指定步長	支持	支持
允許在多個表中使用	不支持	支持

當需要以下功能時，建議使用序列而不是 IDENTITY：

• 應用程序需要在插入數據之前獲取一個數字。
• 應用程序需要在多個表或同一表的多個列中共享一個數字序列。
• 應用程序需要在達到指定值時重新開始數字序列。
• 應用程序需要一次性分配多個數字（可以通過調用 sp_sequence_get_range 存儲過程實現）。
• 應用程序需要動態調整序列的規格（如最大值）。

3.2 DROP TABLE ：刪除表數據

3.2.1 基本語法

DROP TABLE [IF EXISTS] [database_name.][schema_name.]table_name_1,[schema_name.]table_name_2, …[schema_name.]table_name_n;

• table_name：指定要刪除的表的名稱。
• database_name 和 schema_name：分別指定表所屬的數據庫和架構名稱。如果省略數據庫名稱，則默認在當前連接的數據庫中執行刪除操作。
• IF EXISTS：從 SQL Server 2016（13.x）開始支持，避免因表不存在而導致的錯誤。

??使用 DROP TABLE 語句時，SQL Server 會刪除表中的所有數據、觸發器、約束和權限，但不會自動刪除依賴于該表的視圖和存儲過程，需要手動使用 DROP VIEW 和 DROP PROCEDURE 句語來刪除。例如：

DROP TABLE IF EXISTS sales.revenues;

??如果表不存在，但語句中使用了 IF EXISTS 子句，因此語句會成功執行，但不會刪除任何表。如果表存在，sales.revenues表及其所有數據和相關結構將被刪除。

3.2.2 刪除具有外鍵約束的表

當表之間存在外鍵約束時，刪除表的操作會受到限制。例如，我們創建了兩個表 procurement.supplier_groups 和 procurement.suppliers，其中 suppliers 表通過外鍵引用了 supplier_groups 表：

CREATE SCHEMA procurement;
GOCREATE TABLE procurement.supplier_groups (group_id INT IDENTITY PRIMARY KEY,group_name VARCHAR(50) NOT NULL
);CREATE TABLE procurement.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 表：

DROP TABLE procurement.supplier_groups;

SQL Server 會報錯，提示該表被外鍵約束引用：

Could not drop object 'procurement.supplier_groups' because it is referenced by a FOREIGN KEY constraint.

要刪除 supplier_groups 表，必須先刪除引用它的外鍵約束或整個 suppliers 表。可以使用以下語句：

DROP TABLE procurement.suppliers, procurement.supplier_groups;

• 被引用的表（Referenced Table）：supplier_groups。這是包含主鍵的表，其他表通過外鍵引用它的主鍵。
• 引用表（Referencing Table）：suppliers。這是包含外鍵的表，它通過外鍵列 group_id 引用了 supplier_groups 表的 group_id 列。

當使用單個 DROP TABLE 語句刪除多個表時，必須先刪除引用表（suppliers），再刪除被引用表（supplier_groups），以避免外鍵約束錯誤。

3.3 TRUNCATE TABLE : 清空表數據

在數據庫管理中，有時需要刪除表中的所有數據，但保留表的結構。SQL Server 提供了 TRUNCATE TABLE 語句，用于快速高效地刪除表中的所有行，其基本語法為：

TRUNCATE TABLE [database_name.][schema_name.]table_name;

• table_name：指定要清空數據的表的名稱。
• database_name 和 schema_name：分別指定表所屬的數據庫和架構名稱。如果省略數據庫名稱，則默認在當前連接的數據庫中執行操作。

TRUNCATE TABLE 與 DELETE 的區別：

1. 事務日志的使用

   • DELETE 語句：逐行刪除數據，并為每一行刪除操作在事務日志中記錄一個條目。這意味著 DELETE 語句會生成大量的事務日志，尤其是在處理大量數據時。
   • TRUNCATE TABLE 語句：通過釋放存儲表數據的數據頁來刪除數據，并且只在事務日志中記錄數據頁的釋放操作。因此，TRUNCATE TABLE 的事務日志開銷要小得多。

2. 鎖的使用

   • DELETE 語句：在執行時會鎖定每一行，逐行刪除數據。這可能導致較高的鎖開銷，尤其是在處理大型表時。
   • TRUNCATE TABLE 語句：鎖定整個表和數據頁，而不是每一行。這使得 TRUNCATE TABLE 在刪除大量數據時更加高效。

3. 自增列的重置

   • DELETE 語句：刪除數據后，表中的自增列（IDENTITY）的計數器不會重置。
   • TRUNCATE TABLE 語句：刪除數據后，自增列的計數器會重置為初始值（種子值）。

4. 使用場景

   • TRUNCATE TABLE：適用于需要快速清空表中所有數據的場景，尤其是當表中包含大量數據時。它執行速度快，資源消耗少。
   • DELETE 語句：適用于需要根據條件刪除部分數據的場景，或者需要在刪除數據后保持自增列計數器不變的場景。

3.4 重命名表（Rename Table）

3.4.1 使用 Transact-SQL 重命名表

SQL Server 沒有直接的 RENAME TABLE 語句，但它提供了一個名為 sp_rename 的存儲過程，用于更改表的名稱。以下是使用 sp_rename 的語法：

EXEC sp_rename 'old_table_name', 'new_table_name';

• old_table_name：當前表的名稱，包括架構名（如 schema_name.old_table_name）。
• new_table_name：新表的名稱。
• 舊表名和新表名都必須用單引號括起來。

比如要將表 sales.contr 重命名為 contracts，可以使用以下語句：

EXEC sp_rename 'sales.contr', 'contracts';

執行后，SQL Server 會返回以下警告信息：

Caution: Changing any part of an object name could break scripts and stored procedures.

這意味著重命名表可能會導致依賴該表名的腳本和存儲過程無法正常工作。因此，在重命名表之前：

1. 檢查依賴項：檢查是否有存儲過程、視圖、函數或腳本依賴于該表。如果存在依賴項，需要同步更新這些對象的定義。
2. 權限要求：需要 ALTER 權限或更高的權限。
3. 事務日志備份：建議備份事務日志，以防止意外操作導致數據丟失。
4. 測試環境驗證：在生產環境中重命名表之前，建議先在測試環境中驗證操作的正確性，以避免對業務造成影響。

3.4.2 使用 SSMS 重命名表

1. 打開 SSMS 并連接到數據庫。
2. 找到目標表：在對象資源管理器中，找到 product_history 表。
3. 右鍵單擊表名：選擇 “Rename” 菜單項。
4. 輸入新表名：在表名上直接輸入新名稱 product_archive，然后按 Enter 鍵。

3.5 ADD COLUMN：添加新列

ALTER TABLE ADD 語句用于向表中添加新的列。以下是其基本語法：

ALTER TABLE table_name
ADDcolumn_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;

• table_name：指定要修改的表的名稱。
• column_name：指定要添加的新列的名稱。
• data_type：指定新列的數據類型，例如 INT、VARCHAR、DATE 等。
• column_constraint：可選的列約束，例如 NOT NULL、DEFAULT 等。

??在這種語法中，ADD 子句后面是一個逗號分隔的列列表，用于指定要添加的所有列。需要注意的是，SQL Server 不支持像 MySQL 那樣指定新列插入到現有列之后的語法。以下示例向 sales.quotations 表中添加 amount 和 customer_name 兩個新列：

ALTER TABLE sales.quotations
ADDamount DECIMAL(10, 2) NOT NULL,customer_name VARCHAR(50) NOT NULL;

3.6 ALTER COLUMN ：修改列屬性

在 SQL Server 中，ALTER TABLE ALTER COLUMN 語句用于修改表中現有列的屬性，例如數據類型、長度或約束

3.6.1 修改列的數據類型

可以使用以下語法修改列的數據類型，新數據類型必須與現有數據兼容。

ALTER TABLE table_name
ALTER COLUMN column_name new_data_type(size);

假設表 t1 中的列 c 是 INT 類型，現在要將其修改為 VARCHAR 類型：

CREATE TABLE t1 (c INT);
INSERT INTO t1 VALUES (1), (2), (3);ALTER TABLE t1 ALTER COLUMN c VARCHAR(2);

注意事項：如果列中有數據無法轉換為新數據類型，SQL Server 會報錯：

Conversion failed when converting ...

3.6.2 修改列的大小

修改列的大小（如 VARCHAR 或 NVARCHAR 類型）的語法如下。

ALTER TABLE table_name
ALTER COLUMN column_name new_data_type(new_size);

假設表 t2 中的列 c 是 VARCHAR(10) 類型，現在要將其大小增加到 VARCHAR(50)：

CREATE TABLE t2 (c VARCHAR(10));
INSERT INTO t2 VALUES ('SQL Server'), ('Modify'), ('Column');ALTER TABLE t2 ALTER COLUMN c VARCHAR(50);

注意事項：如果減小列的大小，必須確保現有數據不會被截斷。否則，SQL Server 會報錯。

String or binary data would be truncated.

3.6.3 添加 NOT NULL 約束

如果列當前允許 NULL 值，可以使用以下語法將其修改為不允許 NULL 值：

ALTER TABLE table_name
ALTER COLUMN column_name new_data_type(size) NOT NULL;

假設表 t3 中的列 c 允許 NULL 值，現在要將其修改為不允許 NULL 值：

CREATE TABLE t3 (c VARCHAR(50));
INSERT INTO t3 VALUES ('Nullable column'), (NULL);UPDATE t3 SET c = '' WHERE c IS NULL;ALTER TABLE t3 ALTER COLUMN c VARCHAR(20) NOT NULL;

注意事項：在添加 NOT NULL 約束之前，必須確保列中沒有 NULL 值。可以通過 UPDATE 語句將 NULL 值更新為默認值。

好的！以下是更簡潔的版本：

3.7 DROP COLUMN ：刪除列

在 SQL Server 中，ALTER TABLE DROP COLUMN 語句用于從表中刪除一個或多個列，其語法為：

ALTER TABLE table_name
DROP COLUMN column_name_1, column_name_2,...;

• table_name：要修改的表名。
• column_name：要刪除的列名。

假設有一個表 sales.price_lists：

CREATE TABLE sales.price_lists (product_id INT,-- 添加price列，數據類型為DEC(10, 2)，并添加檢查約束（值非負）和非空約束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)
);

1. 刪除多個列

   ALTER TABLE sales.price_lists
   DROP COLUMN discount, surcharge;
   

2. 刪除帶有約束的列：sales.price_lists表中的price 列有一個名為 ck_positive_price 的 CHECK 約束。直接刪除列會導致錯誤：

   ALTER TABLE sales.price_lists
   DROP COLUMN price;
   

   The object 'ck_positive_price' is dependent on column 'price'.
   

   此時需先刪除約束，再刪除該列：

   ALTER TABLE sales.price_lists
   DROP CONSTRAINT ck_positive_price;ALTER TABLE sales.price_lists
   DROP COLUMN price;
   

注意事項：

1. 約束依賴：刪除列前，需先刪除其上的約束（如 PRIMARY KEY、FOREIGN KEY、CHECK 等）。
2. 數據丟失：刪除列后，該列數據將永久丟失，操作前請確認或備份數據。
3. 依賴對象：如果列被視圖、存儲過程等引用，需先更新或刪除這些對象。

3.8 計算列（Computed Columns）

??在 SQL Server 中，計算列是一種強大的功能，允許在表中添加一個虛擬列，其值基于表中其他列的值動態計算。計算列可以簡化查詢邏輯，避免在多個查詢中重復相同的計算邏輯。

計算列的值是基于表中其他列的值動態計算的，不需要手動插入或更新這些列的值。

1. 在現有表中創建計算列：

   ALTER TABLE table_name
   ADD column_name AS expression [PERSISTED];
   

   其中，PERSISTED是可選參數，用于將表達式確定的計算列持久化到磁盤上，下同。

2. 在創建表時定義計算列：

   CREATE TABLE table_name (...,column_name AS expression [PERSISTED],...
   );
   

3.8.1 創建計算列

假設有一個表 persons：

CREATE TABLE persons (person_id INT PRIMARY KEY IDENTITY,first_name NVARCHAR(100) NOT NULL,last_name NVARCHAR(100) NOT NULL,dob DATE
);

插入一些數據：

INSERT INTO persons(first_name, last_name, dob)
VALUES('John','Doe','1990-05-01'),('Jane','Doe','1995-03-01');

如果我們需要查詢每個人的全名，通常會使用 CONCAT 函數或 + 運算符：

SELECTperson_id,first_name + ' ' + last_name AS full_name,dob
FROMpersons
ORDER BYfull_name;

為了簡化查詢，可以添加一個計算列 full_name，直接存儲全名：

ALTER TABLE persons
ADD full_name AS (first_name + ' ' + last_name);

展開persons表，可以直接看到此full_name列：

現在，查詢時可以直接使用 full_name 列：

SELECT person_id, full_name, dob
FROM persons
ORDER BY full_name;

3.8.2 持久化確定性計算列

持久化計算列會將計算結果物理存儲在磁盤上，查詢時不需要重新計算，從而提高性能。

1. 刪除之前的 full_name 列：

   ALTER TABLE persons
   DROP COLUMN full_name;
   

2. 添加一個持久化計算列：

   ALTER TABLE persons
   ADD full_name AS (first_name + ' ' + last_name) PERSISTED;
   

注意事項：

1. 確定性表達式：只有當計算列的表達式是確定性的（即對于相同的輸入總是返回相同的結果）時，才能將其設置為持久化，非確定性表達式則不能。

2. 索引支持：可以在持久化計算列上創建索引，以進一步提高查詢性能。詳見《 function-based indexes of Oracle》 、《 indexes on expressions of PostgreSQL》。

3.8.3 持久化非確定性計算列

假設我們想添加一個計算列 age_in_years，表示每個人的年齡：

ALTER TABLE persons
ADD age_in_years AS (CONVERT(INT, CONVERT(CHAR(8), GETDATE(), 112)) - CONVERT(CHAR(8), dob, 112)) / 10000 PERSISTED;

由于 GETDATE() 是非確定性的，因此不能將其設置為持久化，執行后會報錯：

Computed column 'age_in_years' in table 'persons' cannot be persisted because the column is non-deterministic.

3.9 臨時表（Temporary Tables）

??臨時表用于存儲多次訪問的即時結果集，通常在復雜的查詢或存儲過程中使用。SQL Server 提供了兩種創建臨時表的方法： SELECT INTO 語句和 CREATE TABLE 語句。另外，臨時表分為兩種類型：

1. 局部臨時表：僅在創建它們的會話中可見，以單個 # 符號開頭。
2. 全局臨時表：在多個會話中可見（可跨連接訪問），以雙 ## 符號開頭。

??臨時表在創建它們的會話結束時自動刪除。對于全局臨時表，當創建它的會話結束且其他會話中的查詢完成后，也會自動刪除。如果需要手動刪除臨時表，可以使用 DROP TABLE 語句：

DROP TABLE #haro_products;
DROP TABLE ##heller_products;

??臨時表存儲在 tempdb 數據庫中，可能會對性能產生影響，尤其是在高并發環境下。對于簡單的臨時數據存儲，可以考慮使用表變量（@table_variable）。

3.9.1 使用 SELECT INTO 創建臨時表

??創建臨時表的第一種方法是使用 SELECT INTO 語句，該方法直接從一個查詢結果創建臨時表，并插入數據，不需要單獨的 INSERT INTO 語句。這種方式適合快速創建臨時表并填充數據：

SELECT columns
INTO #temp_table
FROM source_table
WHERE conditions;
....

以下查詢創建了一個名為 #trek_products 的臨時表，并將 production.products 表中的兩列數據填充到臨時表中：

SELECTproduct_name,list_price
INTO #trek_products
FROMproduction.products
WHEREbrand_id = 9;

??執行該語句后，您可以找到在名為 tempdb 的系統數據庫中創建的臨時表名，可以在SSMS中使用路徑 System Databases > tempdb > Temporary Tables訪問該表：

??由于多個數據庫可以創建具有相同名稱的臨時表，因此 SQL Server 會自動在臨時表名的末尾附加唯一編號（上圖中的數字后綴），以區分臨時表。

3.9.2 使用 CREATE TABLE 創建臨時表

??創建臨時表的第二種方法是使用 CREATE TABLE 語句，先定義臨時表的結構，再使用 INSERT INTO 語句插入數據。這種方式適合需要精確控制表結構的場景。創建臨時表的語法與創建常規表相同。

CREATE TABLE #temp_table (column1 datatype,column2 datatype,...
);
INSERT INTO #temp_table
SELECT columns
FROM source_table
WHERE conditions;

例如：

CREATE TABLE #haro_products (product_name VARCHAR(MAX),list_price DEC(10,2)
);

創建臨時表后，您可以將數據以常規表插入的方式，插入到此表中 ：

INSERT INTO #haro_products
SELECTproduct_name,list_price
FROMproduction.products
WHEREbrand_id = 2;

您可以在當前會話中進行查詢：

SELECT*
FROM#haro_products;

但是，如果您打開另一個連接并嘗試上述查詢，您將收到錯誤，這是因為臨時表只能在創建它們的會話中訪問。

Invalid object name '#haro_products'.

如果使用##創建全局臨時表，則可以從任何會話訪問 ##haro_products 表。

3.10 SYNONYM：同義詞使用指南

??在 SQL Server 中，同義詞（Synonym）是一種別名或替代名稱，用于為數據庫對象（如表、視圖、存儲過程、用戶定義函數和序列）創建一個簡單的別名，以簡化其引用，提高代碼的可讀性和可維護性。創建同義詞的語法如下：

CREATE SYNONYM [schema_name_1.] synonym_name
FOR object;

• synonym_name：在 CREATE SYNONYM 關鍵字后提供同義詞同義詞的名稱，架構名稱為可選。

• object：在FOR子句中分配目標對象的完整路徑，可以是表、視圖、存儲過程、用戶定義函數或序列。目標對象的完整路徑格式如下：

  [server_name.[database_name].][schema_name_2].object_name
  

3.10.1 在同一個數據庫中創建同義詞

假設有一個名為 sales.orders 的表，可以為其創建一個同義詞 orders：

CREATE SYNONYM orders FOR sales.orders;

創建后，可以通過同義詞 orders 引用 sales.orders 表：

SELECT * FROM orders;

3.10.2 為另一個數據庫中的表創建同義詞

??假設你當前在數據庫 BikeStores 中，但需要頻繁訪問數據庫 test 中的 purchasing.suppliers 表。通過創建一個同義詞 suppliers，你可以在 BikeStores 數據庫中直接使用 suppliers 來訪問 test.purchasing.suppliers 表，而無需每次都指定完整的路徑。

1. 創建一個名為 test 的新數據庫 ，并將當前數據庫設置為 test

   CREATE DATABASE test;
   GOUSE test;
   GO
   

2. 在 test 數據庫中創建一個名為 purchasing 的新架構

   CREATE SCHEMA purchasing;
   GO
   

3. 在purchasing 架構中創建新表

   CREATE TABLE purchasing.suppliers
   (supplier_id   INTPRIMARY KEY IDENTITY, supplier_name NVARCHAR(100) NOT NULL
   );
   

4. 在BikeStores 數據庫中，為 test 數據庫中的 purchasing.suppliers 表創建一個同義詞：

   CREATE SYNONYM suppliers 
   FOR test.purchasing.suppliers;
   

5. 創建后，可以通過同義詞 suppliers 引用 test.purchasing.suppliers 表：

   SELECT * FROM suppliers;
   

3.10.3 查看當前數據庫的所有同義詞

1. 使用 Transact-SQL 查詢查看

   SELECTname,base_object_name,type
   FROMsys.synonyms
   ORDER BYname;
   

   

2. 使用 SSMS查看：通過以下路徑 Databases > [數據庫名稱] > Programmability > Synonyms 查看。
   

3.10.4 刪除同義詞

刪除同義詞的語法如下：

DROP SYNONYM [IF EXISTS] [schema.] synonym_name;

• IF EXISTS：可選，如果同義詞存在，則刪除；如果不存在，則不報錯。

比如：

DROP SYNONYM IF EXISTS orders;

3.10.5 同義詞的優點和使用場景

同義詞的優點：

• 提供抽象層：為底層對象提供一層抽象，減少直接引用。
• 簡化長名稱：將復雜的對象名稱簡化為更短的別名。
• 保持向后兼容性：在重命名對象（如表、視圖、存儲過程、用戶定義的函數和序列）時，通過創建同義詞保持現有應用程序的兼容性。

使用場景：

1. 簡化對象名稱：
   如果需要引用另一個數據庫中的對象，可以創建一個同義詞，使其看起來像是本地對象。

2. 無縫對象名稱更改：
   如果需要重命名表或其他對象，可以創建一個同義詞，以保持現有應用程序的正常運行，而無需修改所有引用。