SQL中的UPDATE命令有什么用途？

UPDATE命令是用于编辑表记录的数据操作命令。它可以被用于根据用户给出的条件更新单行、所有行或一组行。

它与SET子句一起使用，在操作上，WHERE子句可被用来匹配条件 −

阅读更多：MySQL 教程

示例1

下面给出了使用UPDATE命令的示例−

update table student set name=’sneha’ where branch=’CSE’;

示例2

下面是另一个使用UPDATE命令的示例−

create table employee(ename varchar(30),department varchar(20));
insert into employee values('pinky','CSE');
insert into employee values('priya','ECE');
insert into employee values('hari','EEE');
select * from employee;
update employee set ename='sneha' where department='CSE';
select * from employee;

输出

您将获得以下输出 −

pinky|CSE
priya|ECE
hari|EEE
sneha|CSE
priya|ECE
hari|EEE

更新列的值

下面是一个示例，将employee表的年龄加了1：

create table employee(ename varchar(30),department varchar(20), age number(30));
insert into employee values('ram','projectmanager',40);
insert into employee values('priya','assistant director',45);
insert into employee values('hari','developer',46);
select * from employee;
update employee set age=age+2;
select * from employee;

输出

您将获得以下输出 −

ram|projectmanager|40
priya|assistant director|45
hari|developer|46
ram|projectmanager|42
priya|assistant director|47
hari|developer|48

在一条语句中更新多列

下面是一个示例，将employee表的薪水进行更新 −

• bonus=bonus+5000
• basic=basic+(0.2*bonus)

示例

create table employee(ename varchar(30),department varchar(20), age number(30), salary number(20));
insert into employee values('ram','projectmanager',40,50000);
insert into employee values('priya','assistant director',45,45000);
insert into employee values('hari','developer',46,30000);
select * from employee;

update employee set age=age+2,
   salary= salary+5000;

select * from employee;

输出

您将获得以下输出 −

ram  |projectmanager    |40|50000
priya|assistant director|45|45000
hari |developer         |46|30000
ram  |projectmanager    |42|55000
priya|assistant director|47|50000
hari |developer         |48|35000