Oracle之使用DML语言管理表指南_Oracle

前情提要：本篇博客将详细介绍dml语言语法规则，并且有详细示例演示使用dml语言管理表，同时还有详细解析

oracle版本：19c

当你执行dml语句时:

向表中添加新行
修改表中的现有行
从表中删除现有行

事务由构成逻辑工作单元的dml语句的集合组成。

一、insert语句

向表添加新行

1.1 insert语句语法

通过使用insert 语句将新行添加到表中:

insert into	table [(column [, column...])]
values		(value [, value...]);
-- 使用这种语法，一次只能插入一行

1.2 插入新行

注意事项：

插入一个新行，其中包含每一列的值。
以表中列的默认顺序列出值并对应插入。
（可选）列出insert子句中的列。
将字符和日期值括在单引号内。

示例

-- 查看departments表结构
sql> desc departments;

name                                      null?    type
----------------------------------------- -------- ----------------------------
department_id                             not null number(4)
department_name                           not null varchar2(30)
manager_id                                         number(6)
location_id                                        number(4)

-- 向departments表插入一行数据
-- 显示写法，列出所有列的列名
sql> insert into departments(department_id, department_name, manager_id, location_id) values (71, 'public relations', 100, 1700);
1 row created.

-- 隐式写法，如果插入的是完整的一行数据则可以省略列名
insert into departments values (71, 'public relations', 100, 1700);

-- 在oracle数据库中，插入数据后要使用commit语句进行事务的提交，而在mysql数据库中，则不需要commit因为，mysql数据库默认的是自动提交，而oracle数据库默认不自动提交
-- 在oracle数据库中，插入数据后发现数据有问题或者有错误的时候，我们可以使用rollback语句回滚事务使刚刚的插入失效，但是有个前提是这条语句还没有提交生效
-- 并且无论是在oracle数据库还是在mysql数据库插入数据时，都需要去关注，数据库表中该列是否有约束条件，比如说主键约束，唯一键约束，非空约束等

rollback; -- 撤销刚才的插入
commit; -- 提交刚才的插入，使插入生效

1.3 插入具有空行的值

隐式方法: 从列表中省略该列

insert into	departments (department_id, department_name) values (30, 'purchasing');

显示方法：在values子句中指定null关键字

insert into	departments values (100, 'finance', null, null);

示例

-- 创建示例表
sql> create table test(id1 number,id2 number);

-- 插入一行数据
sql> insert into test values (1,1);

sql> select * from test;

       id1        id2
---------- ----------
         1          1

-- 隐式插入空值到id2列
sql> insert into test values (1,null);

-- 隐式插入空值到id1列
sql> insert into test values (null,2);

-- 查看表的数据，可见id2的第二行数据和id1的第三行数据为null
sql> select * from test;

       id1        id2
---------- ----------
         1          1
         1
                    2

-- 显示插入空值到id2列，指定要插入的列
sql> insert into test(id2) values(3);

-- 查看数据
sql> select * from test;

       id1        id2
---------- ----------
         1          1
         1
                    2
                    3

-- 回滚事务使插入失效
rollback
-- 提交事务使插入生效
commit

1.4 插入特殊值

在values子句中除了用户自己指定值以外还可以使用函数来产生结果作为列的值进行插入

示例

使用current_date函数插入系统时间

-- 假如说新入职了一个员工，那么可以在values子句中使用current_date函数直接插入当前时间
insert into employees (employee_id, first_name, last_name, email, phone_number,hire_date, job_id, salary, commission_pct, manager_id,department_id)
values (113, 'louis', 'popp', 'lpopp', '515.124.4567', current_date, 'ac_account', 6900, null, 205, 110);

使用to_date函数插入指定时间

insert into employees values      
			 (114, 
             'den', 'raphealy', 
             'drapheal', '515.127.4561',
             to_date('feb 3, 2003', 'mon dd, yyyy'),	
             'sa_rep', 11000, 0.2, 100, 60);

可以使用替代变量作为values子句的值，让用户自己输入想要插入的内容

示例

-- 查看测试表的结构
sql> desc test;

name                                      null?    type
----------------------------------------- -------- ----------------------------
id1                                                number
id2                                                number

-- 使用替代变量作为values的值
sql> select * from test;
no rows selected

sql> insert into test values(&id1,&id2);
enter value for id1: 11		-- 指定id1为11
enter value for id2: 22		-- 指定id2为22
old  1: insert into test values(&id1,&id2)
new  1: insert into test values(11,22)

1 row created.

-- 查看test表
sql> select * from test;

       id1        id2
---------- ----------
        11         22

1.5 从另一个表复制行插入

用子查询编写insert语句：
不要使用values子句。
要保证insert子句中的列数与子查询中的列数匹配。
要保证插入的表的列的数据类型和子查询的结果的列的数据类型匹配
将子查询返回的所有行插入表中

示例

-- 创建示例表test1
sql> create table test1(id1 number,id2 number);

-- 给示例表插入数据并提交事务
sql> insert into test1 values(1,1);
sql> insert into test1 values(2,2);
sql> commit;

-- 通过子查询查test1然后将结果插入到test中
-- 查询test1表中id1=1的行插入到test表中
sql> insert into test select * from test1 where id1=1;
1 row created.

-- 查看test表
sql> insert into test select * from test1 where id1=1;
1 row created.

sql> select * from test;

       id1        id2
---------- ----------
        11         22
         1          1

-- 提交事务
sql> commit;
commit complete.

二、update语句

修改表中的数据

2.1 update语句的语法

使用update语句修改表中存在的数据:

update		table
set		column = value [, column = value, ...]
[where 		condition];

注意：update语句一定要搭配where条件限制来使用，这样才能保证修改点精确，如果不使用where条件限制则update会默认修改整个表的行

2.2 修改表中的行

如果指定where子句，则将修改一个或多个特定行的值

示例

使用where子句限制修改单行内容

-- 查看test表的内容
sql> select * from test;

       id1        id2
---------- ----------
        11         22
         1          1
-- 为test表添加一些内容
sql> insert into test values(2,2);
sql> insert into test values(3,4);
sql> insert into test values(7,8);
sql> insert into test values(6,6);
sql> select * from test;
       id1        id2
---------- ----------
        11         22
         1          1
         2          2
         3          4
         7          8
         6          6
sql> commit;

-- 使用update更新一行内容
-- 更新id1=11的行的id1列的值为123
sql> update test set id1 = 123 where id1 = 11;

1 row updated.

sql> select * from test;

       id1        id2
---------- ----------
       123         22
         1          1
         2          2
         3          4
         7          8
         6          6

6 rows selected.
-- 提交事务
sql> commit;

commit complete.

使用where限制修改多行的内容

-- 使用update更新多行内容
-- 更新id1=6或者id2=8的行的id1列的值为666
sql> update test set id1=666 where id1=6 or id2=8;

2 rows updated.

sql> select * from test;

       id1        id2
---------- ----------
       123         22
         1          1
         2          2
         3          4
       666          8
       666          6

6 rows selected.
-- 提交事务
sql> commit;

commit complete.

如果省略where子句，则会修改表中所有行的值:

示例：

sql> update test set id1=00;

6 rows updated.

sql> select * from test;

       id1        id2
---------- ----------
         0         22
         0          1
         0          2
         0          4
         0          8
         0          6

6 rows selected.

-- 回滚事务
sql> rollback;

rollback complete.

指定set column_name = null将列值更新为null.

示例：

sql> update test set id1=null where id2=22;

1 row updated.

sql> select * from test;

       id1        id2
---------- ----------
                   22
         1          1
         2          2
         3          4
       666          8
       666          6

6 rows selected.
-- 回滚事务
sql> rollback;

rollback complete.

2.3 使用子查询进行更新

可以用子查询的结果作为指定列的值进行更新

使用子查询更新多列

示例

更新103号员工的工作和工资以匹配205号员工的工作和工资。

-- 先查看两名员工原先的工作和工资
sql> select job_id,salary from employees where employee_id=103 or employee_id=205;

job_id         salary
---------- ----------
it_prog          9000
ac_mgr          12008

-- 通过子查询更新
sql> update employees
  2  set (job_id,salary)=(select job_id,salary from employees where employee_id=205) 
  3	 where employee_id=103;

1 row updated.

-- 再查看两名员工的工作和工资
sql> select job_id,salary from employees where employee_id=103 or employee_id=205;

job_id         salary
---------- ----------
ac_mgr          12008
ac_mgr          12008

-- 回滚事务
sql> rollback;

rollback complete.

使用update语句中的子查询可基于另一个表中的值更新表中的行值:

示例

update  copy_emp
set     department_id  =  (select department_id
                           from employees
                           where employee_id = 100)
where   job_id         =  (select job_id
                           from employees
                           where employee_id = 200);

-- 查看两个子查询的结果
sql> select department_id from employees where employee_id = 100;

department_id
-------------
           90
sql> select job_id from employees where employee_id = 200;

job_id
----------
ad_asst

-- 所以上面的示例等价于
update  copy_emp
set     department_id  = 90
where   job_id = ad_asst;

三、delete | truncate 语句

删除表中存在的数据

3.1 delete 语句的语法

您可以使用delete语句从表中删除现有行：

delete [from]	  table
[where	  condition];

delete语句和update一样一定要用where指定删除范围，若不指定则会直接删除整张表

3.2 通过delete语句删除表中的数据

通过delete语句删除表中的数据

示例

-- 查看示例表
sql> select * from test;

       id1        id2
---------- ----------
       123         22
         1          1
         2          2
         3          4
       666          8
       666          6

6 rows selected.

-- 通过where子句限制删除指定的行
sql> delete from test where id1=666;

2 rows deleted.

-- 查看
sql> select * from test;

       id1        id2
---------- ----------
       123         22
         1          1
         2          2
         3          4

-- 回滚事务
sql> rollback;

rollback complete.

如果省略where子句则删除表中的所有行

sql> select * from test;

       id1        id2
---------- ----------
       123         22
         1          1
         2          2
         3          4
       666          8
       666          6

6 rows selected.

-- 删除时不使用where进行限制
sql> delete from test;

6 rows deleted.

sql> select * from test;
no rows selected

sql> rollback;

rollback complete.

使用delete语句中的子查询根据另一个表中的值从表中删除行:

示例

-- 先查看两张示例表
sql> select * from test;

       id1        id2
---------- ----------
       123         22
         1          1
         2          2
         3          4
       666          8
       666          6

6 rows selected.

sql> select * from test1;

       id1        id2
---------- ----------
         1          1
         2          2
         
-- 将子查询的结果作为delete的where子句的条件
sql> delete from test
  2  where id1 in (select id1 from test1);

2 rows deleted.

sql> select * from test;

       id1        id2
---------- ----------
       123         22
         3          4
       666          8
       666          6

-- 提交事务
sql> commit;

commit complete.

3.3 truncate 语句的语法和示例

从表中删除所有行，使表为空，并保持表结构完整

是数据定义语言（ddl）语句而不是dml语句；不能轻易撤消

语法

truncate table table_name;

示例

-- 查看示例表
sql> select * from test1;

       id1        id2
---------- ----------
         1          1
         2          2

-- 使用truncate删除test1的数据
sql> truncate table test1;

table truncated.

-- 查看test1
sql> select * from test1;
no rows selected

-- 尝试回滚
sql> rollback;

rollback complete.

-- 发现回滚也无法恢复test1的数据
sql> select * from test1;
no rows selected

注意：

truncate删除的数据时物理删除，delete语句是逻辑删除，truncate语句删除后无法使用rollback语句进行回滚，而delete语句删除后可以回滚，并且在删除大表数据的时候truncate语句速度要比delete语句速度快

四、数据库事务控制：commit,rollback,savepoint

4.1 数据库中的事务

在数据库中三种语言是产生事务的：

dml语句产生事务 insert update delete merge /select...for update（特殊）
ddl语句产生事务 cretae alter drop truncate
dcl语句产生事务 grant revoke
tcl commit rollback 虽然他自身是控制事务的，但是在执行这两条语句的时候，在数据库底层也会发生相应的底层事务

数据库事务由以下之一组成

dml构成对数据的一致更改
一个ddl语句
一种数据控制语言（dcl）语句

数据库事务的开始和结束

从第一个dml sql语句执行开始。
以下列事件之一结束:
- 发出commit或rollback语句（提交或回滚）。
- ddl或dcl语句执行（自动提交）。
- 用户正常退出sql developer或sql * plus（自动提交）。
- 系统崩溃（自动回滚）。

4.2 commit rollback savepoint

commit和rollback的优点

确保数据一致性
在永久更改之前预览数据更改
分组逻辑相关的操作

savepoint语句

使用savepoint语句在当前事务中创建一个标记。
使用rollback to savepoint语句回滚到该标记。

update...
savepoint update_done;

insert...
rollback to update_done;

commit之前或rollback之前的数据状态

可以恢复数据的先前状态。
当前会话可以通过使用select语句查看dml操作的结果。
其他会话无法查看当前会话发出的dml语句的结果。
受影响的行被锁定；其他会话无法更改受影响的行中的数据。

commit之后的数据状态

数据更改保存在数据库中。
数据的先前状态将被覆盖。
所有会话都可以查看结果。
受影响行上的锁被释放；这些行可供其他会话操纵。
所有保存点被删除。

rollback之后的数据状态

使用rollback语句丢弃所有未决的更改:

数据更改被撤消。
恢复数据的先前状态。
受影响行上的锁已释放。

回滚级别

如果单个dml语句在执行期间失败，则仅回退该语句。
oracle服务器实现隐式保存点。
保留所有其他更改。
用户应通过执行commit或rollback语句显式终止事务。

五、读取一致性

读取一致性可确保始终保持一致的数据视图。
一个用户所做的更改不会与另一用户所做的更改冲突。

读取一致性可确保在相同数据上:

readers do not wait for writers 读不等写
writers do not wait for readers 写不等读
writers wait for writers 写只等写

5.1 实现读取一致性

六、for update 子句

数据库事务处理的关键机制，用于在多用户环境中实现行级锁定，确保数据的一致性和隔离性。它允许事务在查询数据时锁定特定行，防止其他事务同时修改这些数据，是实现悲观锁的核心手段。
仅当您发出rollback或commit时才释放锁定.
如果select语句尝试锁定被另一个用户锁定的行，则数据库将等待直到该行可用为止，然后返回select语句的结果.

6.1 锁的简单示例

锁是oracle保证数据一致性的重要机制，当会话a对test表的某行进行操作时，会给该表的该行上锁，然后在会话a提交或回滚事务前，别的用户都无法再对该表的该行进行操作。

演示

-- 在sql*plus中操作test表
-- 查看test表
sql> select * from test;

       id1        id2
---------- ----------
       123         22
         3          4
       666          8
       666          6

-- 对test进行操作
sql> update test set id1=11 where id1=666;
2 rows updated.

-- 查看test表，可以看见test表的后两行收到影响
sql> select * from test;

       id1        id2
---------- ----------
       123         22
         3          4
        11          8
        11          6

-- 然后不提交事务并新开一个sql*plus会话
-- 查看test表，可见查询返回的结果是事务提交之前的，这是oracle的默认机制，只能查看已经提交事务的内容，未提交的是无法查看的
sql> select * from test;

       id1        id2
---------- ----------
       123         22
         3          4
       666          8
       666          6

-- 新会话尝试修改后两行
sql> update test set id1=22 where id2=8 or id2=6;

-- 回车后就会发现新会话卡住，无法进行
-- 然后回到旧会话提交事务
sql> commit;

commit complete.

-- 再回到新会话查看
sql> update test set id1=22 where id2=8 or id2=6;

2 rows updated.
-- 在旧会话提交事务后新会话就已经自动update了
-- 可见当某个会话修改某表的某行的时候会给该行上锁，然后别的会话就无法再修改该行了

-- 现在新会话修改了表的后两行，并且没有提交事务
-- 新会话查看test
sql> select * from test;

       id1        id2
---------- ----------
       123         22
         3          4
        22          8
        22          6
        
-- 回到旧会话尝试修改前两行
sql> update test set id2=11 where id1=123;

1 row updated.
sql> select * from test;

       id1        id2
---------- ----------
       123         11
         3          4
        11          8
        11          6
-- 可见不同会话可以同时修改一个表的不同的行，这种锁是行级锁
-- 两个会话均提交事务，释放锁

6.2 for update使用示例

当想要锁定某行以便稍后进行修改时，可以使用for update 语句给该行上锁，但是前提是该行得存在，如果锁定行无数据，那么for update锁定将会自动撤销（失效）

-- 查看test表
sql> select * from test;

       id1        id2
---------- ----------
       123         11
         3          4
        22          8
        22          6

-- 对后两行上锁
-- 虽然返回的是正常的查询结果，但是这两行已经被上锁了，别的会话无法再对这两行进行操作了
sql> select * from test where id1=22 for update;
       id1        id2
---------- ----------
        22          8
        22          6
        
-- 去新会话验证
sql> update test set id2=666 where id1=22;

-- 在新会话回车后就会停住

-- 若要释放锁则使用commit即可

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持代码网。

Oracle之使用DML语言管理表指南