6.2 SQL Server更新数据

6.2 SQL Server更新数据

 

SQL Server UPDATE

 

目录

• SQL Server UPDATE
  • 简介
  • 示例
  • UPDATE JOIN
    • UPDATE JOIN示例
      • A)SQL Server UPDATE INNER JOIN示例
      • B)SQL Server UPDATE LEFT JOIN示例

 

简介

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

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

语法中:

• 首先，指定要从中更新数据的表的名称。
• 其次，指定要更新的列c1、c2、…、cn和值v1、v2、…、vn的列表。
• 第三，在WHERE子句中指定用于选择要更新的行的条件。WHERE子句是可选的。如果跳过WHERE子句，表中的所有行都将更新。

示例

创建如下UserInfo(用户信息)表：

	create table dbo.UserInfo
	(
	Id int primary key identity(2,2), -- 每添加一条数据，Id从2开始，每次自增2
	UserName nvarchar(15), -- 用户名
	Account nvarchar(15),--账号
	[Password] nvarchar(20),--密码
	DeptId int , -- 学院Id
	Sex nchar(2),--性别
	Age int, -- 年龄
	RoleId nvarchar(50),
	UpdateTime datetime2 -- 更新时间
	);

假设表中已经有一些数据。
修改数据：

	-- 1.将密码修改为 666666
	-- 修改的语法结构：update 表 set 要修改的字段1= 字段1值，字段2 = 字段2值
	update UserInfo set [Password]='666666' -- 这样修改会把所有的数据都修改了
	-- 现在将 编号为3，2 的用户姓名 分别修改为 张三，李四
	-- 语法结构： update 表 set 要修改的字段1= 字段1值，字段2 = 字段2值 where 条件字段=条件值
	update UserInfo set UserName='张三' where Id=3
	update UserInfo set UserName='李四' where Id=2
	-- 将密码为 666666 并且姓张的同学的 年龄 修改为 25
	-- %:0-n 个任意字符
	update UserInfo set Age=25 where [Password]='666666' and UserName like '张%'
	-- 将性别为男并且姓黄的同学的角色 修改为 超级管理员
	-- 0：男 1：女 2:未知 3：保密
	update UserInfo set RoleId='853d469f-7906-4eb6-9034-e4e1314db331' where Sex=0 and UserName like '黄%'
	-- 将 账号包含了 admin 并且 年龄大于30岁的同学的 密码修改为 1qaz2wsx 年龄 修改为 25岁
	update UserInfo set [Password]='1qaz2wsx',Age=25 where Account like '%admin%' and Age>30

UPDATE JOIN

要从相关表中查询数据，通常使用join子句，可以是内部联接，也可以是左联接。在SQL Server中，可以在UPDATE语句中使用这些join子句执行跨表更新。

UPDATE JOIN句式如下：

	UPDATE
	t1
	SET
	t1.c1 = t2.c2,
	t1.c2 = expression,
	...
	FROM
	t1
	[INNER | LEFT] JOIN t2 ON join_predicate
	WHERE
	where_predicate;

这个语法中：

• 首先，在update子句中指定要更新的表（t1）的名称。
• 接下来，为更新表的每列指定新值。
• 然后，再次在from子句中指定要更新的表。
• 然后，使用内部联接或左联接，使用在ON关键字后指定的联接谓词联接到另一个表（t2）。
• 最后，添加一个可选的WHERE子句来指定要更新的行。

UPDATE JOIN示例

让我们看一下使用UPDATE JOIN语句的一些示例。
1）设置示例表
首先，创建一个名为sales.targets的新表。存储销售的目标：

	DROP TABLE IF EXISTS sales.targets;
	CREATE TABLE sales.targets
	(
	target_id INT PRIMARY KEY, --目标id
	percentage DECIMAL(4, 2) NOT NULL DEFAULT 0--提成比率
	);
	INSERT INTO
	sales.targets(target_id, percentage)
	VALUES
	(1,0.2),
	(2,0.3),
	(3,0.5),
	(4,0.6),
	(5,0.8);

如果销售人员达到目标1，他们将获得0.2%或20%的销售提成，依此类推。
其次，创建另一个名为sales.commissions的表。用于存储销售的提成：

	CREATE TABLE sales.commissions
	(
	staff_id INT PRIMARY KEY, --员工Id
	target_id INT, --目标id
	base_amount DECIMAL(10, 2) NOT NULL DEFAULT 0, --基础金额
	commission DECIMAL(10, 2) NOT NULL DEFAULT 0, --提成金额
	FOREIGN KEY(target_id) REFERENCES sales.targets(target_id), --关联到目标表
	FOREIGN KEY(staff_id) REFERENCES sales.staffs(staff_id),--关联到员工表
	);
	INSERT INTO
	sales.commissions(staff_id, base_amount, target_id)
	VALUES
	(1,100000,2),
	(2,120000,1),
	(3,80000,3),
	(4,900000,4),
	(5,950000,5);

sales.commissions表存储员工的唯一标识（staff_id）、目标id（target_id）、基础金额（base_amount）、提成（commission），此表通过target_id链接到sale.targets表。

我们的目标是根据销售目标计算所有销售人员的提成金额。

A)SQL Server UPDATE INNER JOIN示例

以下语句使用UPDATE INNER JOIN计算所有销售人员的销售佣金：

	UPDATE
	sales.commissions
	SET
	sales.commissions.commission = c.base_amount * t.percentage
	FROM
	sales.commissions c
	INNER JOIN sales.targets t ON c.target_id = t.target_id;

执行成功！

如果您查询sales.commissions表中，您将再次看到commission列中的值已更新：

	SELECT
	*
	FROM
	sales.commissions;

结果：

B)SQL Server UPDATE LEFT JOIN示例

假设我们又有两名刚加入的新销售人员，他们还没有任何目标：

	INSERT INTO
	sales.commissions(staff_id, base_amount, target_id)
	VALUES
	(6,100000,NULL),
	(7,120000,NULL);

我们假设新销售人员的佣金为0.1或10%，我们可以使用UPDATE LEFT JOIN更新所有销售人员的薪酬，如下所示：

	UPDATE
	sales.commissions
	SET
	sales.commissions.commission = c.base_amount * COALESCE(t.percentage,0.1)
	FROM
	sales.commissions c
	LEFT JOIN sales.targets t ON c.target_id = t.target_id;

在本例中，如果百分比为空，我们使用COALESCE()返回0.1。
执行成功！
再次查看sales.commissions表

	SELECT
	*
	FROM
	sales.commissions;

 

分类: SQL Server