|
第9节.修改数据1. insert语句
insert语句向表中添加新行，以下是 insert语句的最基本形式：
1INSERT INTO table_name (column_list)
2VALUES (value_list);
3
4
首先， table_name 指定要插入的表的名称。 通常，通过模式名称引用表名称，
例如 production.products ，其中 production 是模式名称， products 是表名称。
其次， column_list 指定要在其中插入数据的一个或多个列的列表。必须将列列表括在括号中并用逗号 分隔列。
如果列在列列表中没有出现，则SQL Server必须能够提供插入值，否则无法插入行。
SQL Server自动对表中可用的列使用以下值，但不会出现在 INSERT 语句的列列表中：
第三，要在 VALUES 子句中提供插入的值列表。 列列表中的每列必须在值列表中具有相应的值。 此外，必须将值列表括在括号中。
创建一个名为 promotions 的新表：
1CREATE TABLE sales.promotions (
2 promotion_id INT PRIMARY KEY IDENTITY (1, 1),
3 promotion_name VARCHAR (255) NOT NULL,
4 discount NUMERIC (3, 2) DEFAULT 0,
5 start_date DATE NOT NULL,
6 expired_date DATE NOT NULL
7);
8在sales模式中创建了一个名为promotions的新表。 promotions表有五列，包括:促销标识号
9(promotion_id)，名称(name)，折扣(discount)，开始日期(start_date)和过期日期
10(expired_date)。
11promotion_id是标识列，因此当向表中添加新行时，SQL Server会自动填充其值。
12
13
1.基本INSERT
以下语句将新行插入 promotions 表：
1INSERT INTO sales.promotions (
2promotion_name,
3discount,
4start_date,
5expired_date
6)
7VALUES
8(
9'2020夏季促销',
100.25,
11'20200601',
12'20200901'
13);
14
15
在此示例中mssql 在表中插入，为 promotions 表中的四列指定了值。但没有为 promotion_id 列指定值，因为SQL Server会自动为此列提供值。
如果 INSERT 语句成功执行，将返回插入的行数。
1 (1 row affected)
2
3
要验证插入操作结果
1SELECT
2*
3FROM
4sales.promotions;
5
6
2.将显式值插入标识列
通常，不为标识列指定值，因为SQL Server将自动提供值。但是，在某些情况下，可能希望在标识列中插入值，例如数据迁移要为标识列插入显式值，必须首先执行以下语句：
1 SET IDENTITY_INSERT table_name ON;
2
3
要关闭标识插入，请使用类似的语句：
1SET IDENTITY_INSERT table_name OFF;
2
3
在 promotion 表中插入标识列的值：
1SET IDENTITY_INSERT sales.promotions ON;
2INSERT INTO sales.promotions (
3promotion_id,
4promotion_name,
5discount,
6start_date,
7expired_date
8)
9VALUES
10(
11 2,
12'2020春季促销',
130.25,
14'20200201',
15'20200301'
16);
17SET IDENTITY_INSERT sales.promotions OFF;
18
19
3.Insert多行
要一次向表中添加多行，请使用以下形式的 INSERT 语句：
1INSERT INTO table_name (column_list)
2VALUES
3(value_list_1),
4(value_list_2),
5...
6(value_list_n);
7
8
在此语法中，不使用单个值列表，而是使用多个以逗号分隔的值列表进行插入。
使用此形式的 INSERT 语句，一次可以插入的行数为 1000 行
如果尚未创建 promotions 表，则可以使用以下 CREATE TABLE 语句：
1drop table if exists sales.promotions;
2CREATE TABLE sales.promotions (
3 promotion_id INT PRIMARY KEY IDENTITY (1, 1),
4 promotion_name VARCHAR (255) NOT NULL,
5 discount NUMERIC (3, 2) DEFAULT 0,
6 start_date DATE NOT NULL,
7 expired_date DATE NOT NULL
8);
9
10
以下语句将多个行添加到 promotions 表：
1-- 先删除表中的所有记录
2DELETE FROM sales.promotions;
3-- 开始插入
4INSERT INTO sales.promotions (
5promotion_name,
6discount,
7start_date,
8expired_date
9)
10VALUES
11(
12'2020夏季促销',
130.15,
14'20200601',
15'20200901'
16),
17(
18'2020秋季促销',
190.20,
20 '20201001',
21'20201101'
22),
23(
24'2020冬季促销',
250.25,
26'20201201',
27'20210101'
28);
29
30
2. insert into select语句
要将其他表中的数据插入另一个表中，请使用以下SQL Server INSERT INTO SELECT 语句：
1INSERT [ TOP ( expression ) [ PERCENT ] ]
2INTO target_table (column_list)
3select_query
4
5
在此语法中，查询语句返回的行将插入 target_table 。
该查询是从任何其他表中检索数据的有效 SELECT 语句。
它必须返回与 column_list 中指定的列对应的数据
为了方便演示，下面创建一张 addresses 表：
1CREATE TABLE sales.addresses (
2address_id INT IDENTITY PRIMARY KEY,
3street VARCHAR (255) NOT NULL,
4city VARCHAR (50),
5state VARCHAR (25),
6zip_code VARCHAR (5)
7);
8
9
1.将表的所有行插入另一张表
以下语句将 customers 表中的所有地址都插入到 addresses 表中：
1INSERT INTO sales.addresses (street, city, state, zip_code)
2SELECT
3street,
4city,
5state,
6zip_code
7FROM
8sales.customers
9ORDER BY
10first_name,
11last_name;
12
13
要验证插入结果，请使用以下查询：
1SELECT
2*
3FROM
4sales.addresses;
5
6
3.update语句
要修改表中的现有数据，请使用以下 UPDATE 语句语法：
1UPDATE table_name
2SET c1 = v1, c2 = v2, ... cn = vn
3[WHERE condition]
4
5
在上面语法中，
创建一个名为 taxes 的新表。
1drop table if exists sales.taxes;
2CREATE TABLE sales.taxes (
3tax_id INT PRIMARY KEY IDENTITY (1, 1),
4state VARCHAR (50) NOT NULL UNIQUE,
5state_tax_rate DEC(3, 2),
6avg_local_tax_rate DEC(3, 2),
7combined_rate AS state_tax_rate + avg_local_tax_rate,
8max_local_tax_rate DEC (3, 2),
9updated_at datetime
10);
11
12
执行以下语句将数据插入 taxes 表：
1INSERT INTO
2sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate)
3VALUES('Alabama',0.04,0.05,0.07);
4INSERT INTO
5sales.taxes(state,state_tax_rate,avg_local_tax_rate,max_local_tax_rate)
6VALUES('Alaska',0,0.01,0.07);
7
8
1.更新所有行
以下语句更新 taxs 表中所有行的单个列：
1UPDATE sales.taxes
2SET updated_at = GETDATE();
3
4
在此示例中，语句将 updated_at 列中的值更改为 GETDATE() 函数返回的系统日期时间。
SQL Server发出以下消息：
1 (2 rows affected)
2
3
这表示有 2 行记录已成功更新。
下面通过以下查询来验证更新结果：
1SELECT
2*
3FROM
4sales.taxes;
5
6
从输出中可以清楚地看到， updated_at 列已使用当前日期值进行更新。
2.更新多列示例
对于最高当地税率为 1% 的州，以下语句将最高当地税率提高 2% ，以及平均税率提高 1% 。
1UPDATE sales.taxes
2SET max_local_tax_rate += 0.02,
3avg_local_tax_rate += 0.01
4WHERE
5max_local_tax_rate = 0.01;
6
7
4. delete语句
要删除表中的现有数据，请使用以下 DELETE 语句语法：
1DELETE FROM table_name
2[WHERE condition]
3
4
在上面语法中
以sales.taxes为例
删除 sales.taxes 表中 state（州） 为 ‘Alabama’的数据
1delete from sales.taxes where state= 'Alabama' ;
2
3----------
4
5 (1 行受影响)
6
7
删除表中所有数
1delete from sales.taxes ;
2
3
5.Merge语句
假设有两个表名为： source 表和 target 表，并且需要根据 source 表中匹配的值更新 target 表。
有 三种情况：
下图说明了 source 表和 target 表以及相应的操作：插入，更新和删除：
如果单独使用 INSERT ， UPDATE 和 DELETE 语句，则必须构造三个单独的语句，以使用 source 表中的 匹配行将数据更新到 target 表。
但是，SQL Server提供 MERGE 语句以用于同时执行三个操作。 以下是 MERGE 语句的语法：
1MERGE target_table USING source_table
2ON merge_condition
3WHEN MATCHED
4THEN update_statement
5WHEN NOT MATCHED
6THEN insert_statement
7WHEN NOT MATCHED BY SOURCE
8THEN DELETE;
9
10
首先，在 MERGE 子句中指定 source_table 表和 target_table 表。
其次， merge_condition 确定 source_table 表中的行如何与 target_table 表中的行匹配。 它类似 于 join 子句中的 join 条件。 通常，使用主键或唯一键的键列进行匹配。
第三， merge_condition 有三种状态： MATCHED ， NOT MATCHED 和 NOT MATCHED BY SOURCE 。
1. SQL Server MERGE语句示例
假设有两个表： sales.category 和 sales.category_staging ，它们按产品类别存储销售额。
参考以 下创建语句：
1CREATE TABLE sales.category (
2category_id INT PRIMARY KEY,
3category_name VARCHAR(255) NOT NULL,
4amount DECIMAL(10 , 2 )
5);
6INSERT INTO sales.category(category_id, category_name, amount)
7VALUES(1,'Children Bicycles',15000),
8(2,'Comfort Bicycles',25000),
9(3,'Cruisers Bicycles',13000),
10(4,'Cyclocross Bicycles',10000);
11CREATE TABLE sales.category_staging (
12category_id INT PRIMARY KEY,
13category_name VARCHAR(255) NOT NULL,
14amount DECIMAL(10 , 2 )
15);
16INSERT INTO sales.category_staging(category_id, category_name, amount)
17VALUES(1,'Children Bicycles',15000),
18(3,'Cruisers Bicycles',13000),
19(4,'Cyclocross Bicycles',20000),
20(5,'Electric Bikes',10000),
21(6,'Mountain Bikes',10000);
22
23
要使用 sales.category_staging (源表)中的值将数据更新到 sales.category (目标表)，请使用以下 MERGE 语句：
1merge sales.category t using sales.category_staging s
2on s.category_id = t.category_id
3when matched -- 匹配到的数据，按照新表的数据进行更新
4then update set t.category_name =s.category_name,t.amount = s.amount
5when not matched by target -- 目标表中匹配不到的数据，在这插入
6then insert (category_id,category_name,amount)-- 不可以写表的别名
7values (s.category_id,s.category_name,s.amount)
8when not matched by source -- 源表中找不到的数据，在这里删除
9then delete;
10
11
执行过程如下图所示
在此示例中，使用两个表中 category_id 列中的值作为合并条件。
在合并的结果中， sales.category 表中的数据与 sales.category_staging 表中的数据完全同步
（编辑：92站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
