1.建表
create table test_updata(
id int primary key,
name varchar(10),
gender int,
math_score float,
chin_score float
)CHARACTER SET utf8;
注:若不设置主键,则执行的是插入操作,不可以执行更新!
2.准备数据
insert into test_updata(id,name,gender,math_score,chin_score)values(1,'石昊',1,99,99);
insert into test_updata(id,name,gender,math_score,chin_score)values(2,'清漪',1,99,99);
insert into test_updata(id,name,gender,math_score,chin_score)values(3,'雨生',1,99,99);
insert into test_updata(id,name,gender,math_score,chin_score)values(4,'月婵',1,99,99);
insert into test_updata(id,name,gender,math_score,chin_score)values(5,'兔子',1,99,99);
insert into test_updata(id,name,gender,math_score,chin_score)values(6,'石毅',1,99,99);
3.执行结果
1)初始查询结果(对比使用)
select * from test_updata;
2)执行如下语句,即可执行更新操作
insert into test_updata(id,name,gender,math_score,chin_score)values(6,'石毅',1,99,98)
on duplicate key update
math_score = values(math_score),
chin_score = values(chin_score);
结果如图:
4.避免插入重复数据
insert ignore into test_updata(id,name,gender,math_score,chin_score)values(1,'石昊',1,99,99);
执行结果为(不会报错):
注:数据库的数据同3.1(此处不再截图,可自行演示查看),不会出现重复数据