oracle-单表更新和删除

来源：互联网发布：毛少将知乎编辑：程序博客网时间：2024/06/11 12:28
SQL> select * from tbl_student;STU_NO STU_NAME                                                       STU_AGE STU_HEIGHT------ ------------------------------ --------------------------------------- ----------001    mary                                                                13     130.50002    david                                                               14     131.50003    tom                                                                 13006    kent                                                                14     135.50009    jenny                                                               15SQL> commit;Commit complete-- 全记录单字段更新SQL> update tbl_student set stu_age=14;5 rows updatedSQL> select * from tbl_student;STU_NO STU_NAME                                                       STU_AGE STU_HEIGHT------ ------------------------------ --------------------------------------- ----------001    mary                                                                14     130.50002    david                                                               14     131.50003    tom                                                                 14006    kent                                                                14     135.50009    jenny                                                               14-- 通过回滚恢复SQL> rollback;Rollback completeSQL> select * from tbl_student;STU_NO STU_NAME                                                       STU_AGE STU_HEIGHT------ ------------------------------ --------------------------------------- ----------001    mary                                                                13     130.50002    david                                                               14     131.50003    tom                                                                 13006    kent                                                                14     135.50009    jenny                                                               15-- 单字段单条件更新SQL> update tbl_student set stu_age=14 where stu_no='001';1 row updatedSQL> select * from tbl_student;STU_NO STU_NAME                                                       STU_AGE STU_HEIGHT------ ------------------------------ --------------------------------------- ----------001    mary                                                                14     130.50002    david                                                               14     131.50003    tom                                                                 13006    kent                                                                14     135.50009    jenny                                                               15-- 多字段条件更新SQL> update tbl_student set stu_age=15,stu_height=136.5 where stu_no='003';1 row updatedSQL> select * from tbl_student;STU_NO STU_NAME                                                       STU_AGE STU_HEIGHT------ ------------------------------ --------------------------------------- ----------001    mary                                                                14     130.50002    david                                                               14     131.50003    tom                                                                 15     136.50006    kent                                                                14     135.50009    jenny                                                               15-- 单字段更新 (is null操作)SQL> update tbl_student  2  set stu_height=135.5  3  where stu_height is null;1 row updatedSQL> select * from tbl_student;STU_NO STU_NAME                                                       STU_AGE STU_HEIGHT------ ------------------------------ --------------------------------------- ----------001    mary                                                                14     130.50002    david                                                               14     131.50003    tom                                                                 15     136.50006    kent                                                                14     135.50009    jenny                                                               15     135.50-- 相对值更新SQL> update tbl_student set stu_height=stu_height+1;5 rows updatedSQL> select * from tbl_student;STU_NO STU_NAME                                                       STU_AGE STU_HEIGHT------ ------------------------------ --------------------------------------- ----------001    mary                                                                14     131.50002    david                                                               14     132.50003    tom                                                                 15     137.50006    kent                                                                14     136.50009    jenny                                                               15     136.50SQL> commit;Commit complete-- 全表纪录删除SQL> delete from tbl_student;5 rows deletedSQL> select * from tbl_student;STU_NO STU_NAME                                                       STU_AGE STU_HEIGHT------ ------------------------------ --------------------------------------- ----------SQL> rollback;Rollback completeSQL> select * from tbl_student;STU_NO STU_NAME                                                       STU_AGE STU_HEIGHT------ ------------------------------ --------------------------------------- ----------001    mary                                                                14     131.50002    david                                                               14     132.50003    tom                                                                 15     137.50006    kent                                                                14     136.50009    jenny                                                               15     136.50-- 条件删除SQL> delete from tbl_student where stu_no='003';1 row deletedSQL> select * from tbl_student;STU_NO STU_NAME                                                       STU_AGE STU_HEIGHT------ ------------------------------ --------------------------------------- ----------001    mary                                                                14     131.50002    david                                                               14     132.50006    kent                                                                14     136.50009    jenny                                                               15     136.50
0 0