SQL SERVER的集合运算

1.集合运算符

（1）.SQL Server Union(并集)

SQL Server UNION 是一组集合操作，用于将两个SELECT语句的结果组合到一个结果集中，该结果集包 括属于 union 中 SELECT 语句的所有行。 以下是SQL Server UNION 的语法：

query_1
UNION
query_2

（2).UNION 与 UNION ALL

默认情况下， UNION 运算符从结果集中删除所有重复的行。 但是，如果要保留重复的行，则需要明确指 定 ALL 关键字，如下所示：

query_1
UNION ALL
query_2

换句话说， UNION 运算符删除重复行，而 UNION ALL 运算符在最终结果集中包含重复行。

UNION 与 JOIN

[INNER JOIN]或[LEFT JOIN]等连接组合了两个表中的列，而 UNION 组合了两个查询中的行。 换句话说， join 会水平附加结果集，而 union 会垂直附加结果集

(3).UNION和UNION ALL示例

以下示例将员工和客户的名称合并到一个列表中：

SELECT
first_name,
last_name
FROM
sales.staffs
UNION
SELECT
first_name,
last_name
FROM
sales.customers;

（4).UNION和ORDER BY示例

要对 UNION 运算符返回的结果集进行排序，请将 ORDER BY 子句放在最后一个查询中，如下所示：

SELECT
select_list
FROM
table_1
UNION
SELECT
select_list
FROM
table_2
ORDER BY
order_list;

2.SQL Server Intersect(交集)

 SQL Server INTERSECT 组合了两个或多个查询的结果集，并返回两个查询输出的不同行。 以下是SQL Server INTERSECT 的语法：

query_1
INTERSECT
query_2

请考虑以下查询语句：

SELECT
city
FROM
sales.customers
INTERSECT
SELECT
city
FROM
sales.stores
ORDER BY
city;

3. SQL Server Except(差积)

SQL Server EXCEPT 比较两个查询的结果集，并返回第一个查询中不是由第二个查询输出的不同行。 换 句话说， EXCEPT 从一个查询结果中减去另一个查询的结果集。 以下是SQL Server EXCEPT 的语法：

query_1
EXCEPT
query_2

 以下示例使用 EXCEPT 运算符查找没有销售的产品：

SELECT
product_id
FROM
production.products
EXCEPT
SELECT
product_id
FROM
sales.order_items;

4.修改数据

（1）.INSERT语句

INSERT 语句向表中添加新行，以下是 INSERT 语句的最基本形式：

INSERT INTO table_name (column_list)
VALUES (value_list);

 首先， table_name 指定要插入的表的名称。 通常，通过模式名称引用表名称，例如 production.products ，其中 production 是模式名称， products 是表名称。

其次， column_list 指定要在其中插入数据的一个或多个列的列表。必须将列列表括在括号中并用逗号 分隔列。

如果列在列列表中没有出现，则SQL Server必须能够提供插入值，否则无法插入行。

SQL Server自动对表中可用的列使用以下值，但不会出现在 INSERT 语句的列列表中：

如果列具有[IDENTITY]属性，则为下一个增量值。

如果列具有指定的默认值，则为默认值。

如果列的数据类型是时间戳数据类型（timestamp），则默认为当前时间戳值。

如果列可以为 NULL 值，则使用 NULL 。

第三，要在 VALUES 子句中提供插入的值列表。 列列表中的每列必须在值列表中具有相应的值。 此外， 必须将值列表括在括号中。

创建一个名为 promotions 的新表：

CREATE TABLE sales.promotions (
promotion_id INT PRIMARY KEY IDENTITY (1, 1),
promotion_name VARCHAR (255) NOT NULL,
discount NUMERIC (3, 2) DEFAULT 0,
start_date DATE NOT NULL,
expired_date DATE NOT NULL
);

(2). 基本INSERT

以下语句将新行插入 promotions 表:

INSERT INTO sales.promotions (
promotion_name,
discount,
start_date,
expired_date
)
VALUES
(
'2020夏季促销',
0.25,
'20200601',
'20200901'
);

在此示例中，为 promotions 表中的四列指定了值。但没有为 promotion_id 列指定值，因为SQL Server会自动为此列提供值。 如果 INSERT 语句成功执行，将返回插入的行数。

 (1 row affected)

要验证插入操作结果

SELECT
*
FROM
sales.promotions;

(2).将显式值插入标识列

 通常，不为标识列指定值，因为SQL Server将自动提供值。但是，在某些情况下，可能希望在标识列中 插入值，例如数据迁移 要为标识列插入显式值，必须首先执行以下语句：

SET IDENTITY_INSERT table_name ON;

 要关闭标识插入，请使用类似的语句：

SET IDENTITY_INSERT table_name OFF;

在 promotion 表中插入标识列的值：

 IDENTITY_INSERT sales.promotions ON;
INSERT INTO sales.promotions (
promotion_id,
promotion_name,
discount,
start_date,
expired_date
)
VALUES
(
'2020春季促销',
0.25,
'20200201',
'20200301'
);
SET IDENTITY_INSERT sales.promotions OFF;

(3).Insert多行

要一次向表中添加多行，请使用以下形式的 INSERT 语句：

INSERT INTO table_name (column_list)
VALUES
(value_list_1),
(value_list_2),
...
(value_list_n);

 (2).INSERT INTO SELECT语句

要将其他表中的数据插入另一个表中，请使用以下SQL Server INSERT INTO SELECT 语句：

INSERT [ TOP ( expression ) [ PERCENT ] ]
INTO target_table (column_list)
select_query

在此语法中，查询语句返回的行将插入 target_table 。

该查询是从任何其他表中检索数据的有效 SELECT 语句。

它必须返回与 column_list 中指定的列对应的数据 为了方便演示，下面创建一张 addresses 表： 

 

CREATE TABLE sales.addresses (
address_id INT IDENTITY PRIMARY KEY,
street VARCHAR (255) NOT NULL,
city VARCHAR (50),
state VARCHAR (25),
zip_code VARCHAR (5)
);

 将表的所有行插入另一张表 以下语句将 customers 表中的所有地址都插入到 addresses 表中：

INSERT INTO sales.addresses (street, city, state, zip_code)
SELECT
street,
city,
state,
zip_code
FROM
sales.customers
ORDER BY
first_name,
last_name;

 要验证插入结果，请使用以下查询：

SELECT
*
FROM
sales.addresses;
1

 

(3).UPDATE语句

要修改表中的现有数据，请使用以下 UPDATE 语句语法

UPDATE table_name
SET c1 = v1, c2 = v2, ... cn = vn
[WHERE condition]

(4). 更新多列示例

 对于最高当地税率为 1% 的州，以下语句将最高当地税率提高 2% ，以及平均税率提高 1% 。

UPDATE sales.taxes
SET max_local_tax_rate += 0.02,
avg_local_tax_rate += 0.01
WHERE
max_local_tax_rate = 0.01;

(3).DELETE语句

要删除表中的现有数据，请使用以下 DELETE 语句语法：

DELETE FROM table_name
[WHERE condition]

在上面语法中 首先，指定要从中删除数据的表的名称。

第三，在 WHERE 子句中指定条件以选择删除的行。W

HERE子句是可选的。 如

果不指定 WHERE 子 句，则表中的所有行都将被删除。 

以sales.taxes为例 删除 sales.taxes 表中 state（州） 为 ‘Alabama’的数据

delete from sales.taxes where state= 'Alabama' ;

(1).删除表中所有数据

delete from sales.taxes ;