全文参考《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. 无缝对象名称更改：
   如果需要重命名表或其他对象，可以创建一个同义词，以保持现有应用程序的正常运行，而无需修改所有引用。