1.Merge语句
假设有两个表名为: source 表和 target 表,
并且需要根据 source 表中匹配的值更新 target 表。
有 三种情况: source 表有一些 target 表中不存在的行。
在这种情况下,需要将 source 表中的行插入到 target 表中。
target 表有一些 source 表中不存在的行。
在这种情况下,需要从 target 表中删除行。 source 表的某些行具有与 target 表中的行相同的键。 但是,这些行在非键列中具有不同的值。 在这种情况下,需要使用来自 source 表的值更新 target 表中的行。
如果单独使用 INSERT , UPDATE 和 DELETE 语句,则必须构造三个单独的语句,以使用 source 表中的 匹配行将数据更新到 target 表。
但是,SQL Server提供 MERGE 语句以用于同时执行三个操作。
以下是 MERGE 语句的语法:
MERGE target_table USING source_table
ON merge_condition
WHEN MATCHED
THEN update_statement
WHEN NOT MATCHED
THEN insert_statement
WHEN NOT MATCHED BY SOURCE
THEN DELETE;
首先,在 MERGE 子句中指定 source_table 表和 target_table 表。
其次, merge_condition 确定 source_table 表中的行如何与 target_table 表中的行匹配。
它类似 于 join 子句中的 join 条件。
通常,使用主键或唯一键的键列进行匹配。 第三, merge_condition 有三种状态: MATCHED , NOT MATCHED 和 NOT MATCHED BY SOURCE 。
MATCHED :这些是与合并条件匹配的行。 在图中,它们显示为蓝色。 对于匹配的行,需要使用 source_table 表中的值更新 target_table 表中的行列。 NOT MATCHED
:这些是 source_table 表中的行, target_table 表中没有任何匹配的行。
在图 中,它们显示为橙色。 在这种情况下,需要将 source_table 表中的行添加到 target_table 表
。 NOT MATCHED BY TARGET 也称为目标不匹配。 NO MATCHED BY SOURCE :
这些是 target_table 表中与 source_table 表中的任何行都不匹配的 行。
它们在图中显示为绿色。 如果要将 target_table 表与 source_table 表中的数据同步,则 需要使用此匹配条件从 target_table 表中删除行。
要使用 sales.category_staging (源表)中的值将数据更新到 sales.category (目标表),请使用以下 MERGE 语句:
merge sales.category t using sales.category_staging s
on s.category_id = t.category_id
when matched
then update set t.category_name =s.category_name,t.amount = s.amount
when not matched by target
then insert (category_id,category_name,amount)
values (s.category_id,s.category_name,s.amount)
when not matched by source
then delete;
在此示例中,使用两个表中 category_id 列中的值作为合并条件。
首先, sales.category_staging 表中 id 值为 1 , 3 , 4 的行与目标表中的行匹配,
因此, MERGE 语句更新 sales.category 表中类别名称和 amount 列中的值。
其次, sales.category_staging 表中 id 值为 5 和 6 的行在 sales.category 表中不存在,
因此 MERGE 语句将这些行插入到目标表中。
第三, sales.sales_staging 表中不存在 sales.category 表中具有 id 值为 2 的行
,因此, MERGE 语句将删除此行。 在合并的结果中, sales.category 表中的数据与 sales.category_staging 表中的数据完全同步
2.SQL Server数据类型
数据类型 | 描述 | 存储 |
char(n) | 固定长度的字符串。最多 8,000 个字符。 | |
varchar(n) | 可变长度的字符串。最多 8,000 个字符。 | |
varchar(max) | 可变长度的字符串。最多 1,073,741,824 个字符。 | |
text | 可变长度的字符串。最多 2GB 字符数据。 |
Unicode 字符串:
数据类型 | 描述 | 存储 |
nchar(n) | 固定长度的 Unicode 数据。最多 4,000 个字符。 | |
nvarchar(n) | 可变长度的 Unicode 数据。最多 4,000 个字符。 | |
nvarchar(max) | 可变长度的 Unicode 数据。最多 536,870,912 个字符。 | |
ntext | 可变长度的 Unicode 数据。最多 2GB 字符数据。 |
VARCHAR与NVARCHAR比较
比较 项 | VARCHAR | NVARCHAR |
字符 数据 类型 | 可变长度的非Unicode字符 | 可变长度,Unicode和非Unicode字符, 如:中文,日语和韩语。 |
最大 长度 | 最多 8000 个字符 | 最多 4000 个字符 |
字符 大小 | 每个字符占用 1 个字节 | 每个Unicode/非Unicode字符占用 2 个字节 |
使用 | 当数据长度为可变或可变长度列且 实 际数据始终小于容量时使用 | 仅限存储,仅在需要Unicode支持时使用, 例如:汉字,日语或韩文字符。 |
Binary 类型:
数据类型 | 描述 | 存储 |
bit | 允许 0、1 或 NULL | |
binary(n) | 固定长度的二进制数据。最多 8,000 字节。 | |
varbinary(n) | 可变长度的二进制数据。最多 8,000 字节。 | |
varbinary(max) | 可变长度的二进制数据。最多 2GB 字节。 | |
image | 可变长度的二进制数据。最多 2GB。 |
Number 数字类型:
数据类型 | 描述 | 存 储 |
tinyint | 允许从 0 到 255 的所有数字。 | 1 字 节 |
smallint | 允许从 -32,768 到 32,767 的所有数字。 | 2 字 节 |
int | 允许从 -2,147,483,648 到 2,147,483,647 的所有数字。 | 4 字 节 |
bigint | 允许介于 -9,223,372,036,854,775,808 和 9,223,372,036,854,775,807 之间的所有数字。 | 8 字 节 |
decimal(p,s) | 固定精度和比例的数字。允许从 -10^38 +1 到 10^38 -1 之间的数字。p 参数指示可以存储的最大位数(小数点左侧和右侧)。p 必须是 1 到 38 之间的值。默认是 18。s 参数指示小数点右侧存储的最大位数。s 必须 是 0 到 p 之间的值。默认是 0。 | 5- 17 字 节 |
numeric(p,s) | 固定精度和比例的数字。允许从 -10^38 +1 到 10^38 -1 之间的数字。p 参数指示可以存储的最大位数(小数点左侧和右侧)。p 必须是 1 到 38 之间的值。默认是 18。s 参数指示小数点右侧存储的最大位数。s 必须 是 0 到 p 之间的值。默认是 0。 | 5- 17 字 节 |
smallmoney | 介于 -214,748.3648 和 214,748.3647 之间的货币数据。 | 4 字 节 |
money | 介于 -922,337,203,685,477.5808 和 922,337,203,685,477.5807 之间的 货币数据。 | 8 字 节 |
float(n) | 从 -1.79E + 308 到 1.79E + 308 的浮动精度数字数据。 参数 n 指示该字 段保存 4 字节还是 8 字节。float(24) 保存 4 字节,而 float(53) 保存 8 字 节。n 的默认值是 53。 | 4 或 8 字 节 |
real | 从 -3.40E + 38 到 3.40E + 38 的浮动精度数字数据。 | 4 字 节 |
3.SQL Server创建表
CREATE TABLE语句简介 表用于在数据库中存储数据。
表在数据库和模式中唯一命名。 每个表包含一个或多个列。
每列都有一个相关的数据类型,用于定义它可以存储的数据类型,例如:数字,字符串和日期。 要创建新表,请使用 CREATE TABLE 语句,如下所示:
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 的新表来跟踪客户的店内访问:
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 约束的更多信息。
(1).SQL Server复制表
SQL Server中,如果目标表存在:
insert into 目标表 [column] select * from 原表;
SQL Server中,,如果目标表不存在:
select * into 目标表 from 原表
SQL Server标识列
要为表创建标识列,请使用 IDENTITY 属性,如下所示:
IDENTITY[(seed,increment)]