MySql学习笔记16——数据库操作语言DML(修改语句 update)

nanyue 2025-01-23 20:05:47 技术文章 3 ℃

1.修改单表的记录★

语法:

update 表名
set 列=新值,列=新值, .. .
where 筛选条件;

student表

teacher表

①修改张三的语文成绩为79

update student
set score =79
where name='张三' and subject ='语文'

②修改张三的语文成绩为79且修改其语文教师id为2

update student
set score =79,teacher_id =2
where name='张三' and subject ='语文'

SQL92语法(SQL SERVER 不支持):

update 表1 别名,表2 别名
set 列=值, ...
where 连接条件
and 筛选条件;

案例:

修改张三的语文老师的年龄为50

update student st ,teacher te
set te.age = 50
where st.teacher_id = te.id
and st.name ='张三' and subject ='语文'

SQL99语法(SQL SERVER 不支持):

update 表1 别名
inner | left l right join 表2 别名
on 连接条件
set 列=值, ...
where 筛选条件;

修改张三的语文老师的年龄为50

update student st ,
inner join teacher te
on st.teacher_id = te.id
set te.age = 50
where st.name ='张三' and subject ='语文'

SQL SERVER要实现上述功能:

update teacher
set age = 50
where id = (select teacher_id from student where name ='张三' and subject ='语文')