oracle-单表更新和删除

SQL> select * from tbl_student;

STU_NO STU_NAME                                                       STU_AGE STU_HEIGHT
------ ------------------------------ --------------------------------------- ----------
001    mary                                                                13     130.50
002    david                                                               14     131.50
003    tom                                                                 13
006    kent                                                                14     135.50
009    jenny                                                               15

SQL> commit;

Commit complete


-- 全记录单字段更新
SQL> update tbl_student set stu_age=14;

5 rows updated

SQL> select * from tbl_student;

STU_NO STU_NAME                                                       STU_AGE STU_HEIGHT
------ ------------------------------ --------------------------------------- ----------
001    mary                                                                14     130.50
002    david                                                               14     131.50
003    tom                                                                 14
006    kent                                                                14     135.50
009    jenny                                                               14

-- 通过回滚恢复
SQL> rollback;

Rollback complete

SQL> select * from tbl_student;

STU_NO STU_NAME                                                       STU_AGE STU_HEIGHT
------ ------------------------------ --------------------------------------- ----------
001    mary                                                                13     130.50
002    david                                                               14     131.50
003    tom                                                                 13
006    kent                                                                14     135.50
009    jenny                                                               15


-- 单字段单条件更新
SQL> update tbl_student set stu_age=14 where stu_no='001';

1 row updated

SQL> select * from tbl_student;

STU_NO STU_NAME                                                       STU_AGE STU_HEIGHT
------ ------------------------------ --------------------------------------- ----------
001    mary                                                                14     130.50
002    david                                                               14     131.50
003    tom                                                                 13
006    kent                                                                14     135.50
009    jenny                                                               15

-- 多字段条件更新
SQL> update tbl_student set stu_age=15,stu_height=136.5 where stu_no='003';

1 row updated

SQL> select * from tbl_student;

STU_NO STU_NAME                                                       STU_AGE STU_HEIGHT
------ ------------------------------ --------------------------------------- ----------
001    mary                                                                14     130.50
002    david                                                               14     131.50
003    tom                                                                 15     136.50
006    kent                                                                14     135.50
009    jenny                                                               15

-- 单字段更新 (is null操作)
SQL> update tbl_student
  2  set stu_height=135.5
  3  where stu_height is null;

1 row updated

SQL> select * from tbl_student;

STU_NO STU_NAME                                                       STU_AGE STU_HEIGHT
------ ------------------------------ --------------------------------------- ----------
001    mary                                                                14     130.50
002    david                                                               14     131.50
003    tom                                                                 15     136.50
006    kent                                                                14     135.50
009    jenny                                                               15     135.50

-- 相对值更新
SQL> update tbl_student set stu_height=stu_height+1;

5 rows updated

SQL> select * from tbl_student;

STU_NO STU_NAME                                                       STU_AGE STU_HEIGHT
------ ------------------------------ --------------------------------------- ----------
001    mary                                                                14     131.50
002    david                                                               14     132.50
003    tom                                                                 15     137.50
006    kent                                                                14     136.50
009    jenny                                                               15     136.50

SQL> commit;

Commit complete


-- 全表纪录删除
SQL> delete from tbl_student;

5 rows deleted

SQL> select * from tbl_student;

STU_NO STU_NAME                                                       STU_AGE STU_HEIGHT
------ ------------------------------ --------------------------------------- ----------

SQL> rollback;

Rollback complete

SQL> select * from tbl_student;

STU_NO STU_NAME                                                       STU_AGE STU_HEIGHT
------ ------------------------------ --------------------------------------- ----------
001    mary                                                                14     131.50
002    david                                                               14     132.50
003    tom                                                                 15     137.50
006    kent                                                                14     136.50
009    jenny                                                               15     136.50

-- 条件删除
SQL> delete from tbl_student where stu_no='003';

1 row deleted

SQL> select * from tbl_student;

STU_NO STU_NAME                                                       STU_AGE STU_HEIGHT
------ ------------------------------ --------------------------------------- ----------
001    mary                                                                14     131.50
002    david                                                               14     132.50
006    kent                                                                14     136.50
009    jenny                                                               15     136.50