1 DML

1.1 简介

使用SQL语句操作数据库，除了查询操作之外，还包括完成插入、更新和删除等数据操作。后3种数据操作使用的SQL语言也称为数据操纵语言（Data Manipulation Language，DML），它们分别对应INSERT、DELETE和UPDATE三条语句。

在Oracle 11G中，DML除了包括上面提到的3种语句之外，还包括TRUNCATE、CALL、LOCKTABLE和MERGE等语句。

1.2 插入数据（INSERT语句）

1.2.1 简介

插入数据就是将数据记录添加到已经存在的数据表中，Oracle数据库通过INSERT语句来实现插入数据记录。该语句既可以实现向数据表中一次插入一条记录，也可以使用SELECT子句将查询结果集批量插入数据表。

使用INSERT语句有以下注意事项：

• 当为数字列增加数据时，可以直接提供数字值，或者用单引号引住。
• 当为字符列或日期列增加数据时，必须用单引号引住。
• 当增加数据时，数据必须要满足约束规则，并且必须为主键列和NOT NULL列提供数据。
• 当增加数据时，数据必须与列的个数和顺序保持一致。

1.2.2 单条插入数据

单条插入数据是INSERT语句最基本的用法，其用法格式如下：

INSERT INTO table_name [(column_name1[,column_name2]…)]
    VALUES(express1[,express2]…)

参数介绍：

• table_name：表示要插入的表名。
• column_name1和column_name2：指定表的完全或部分列名称。如果指定多个列，那么列之间用逗号分开。
• express1和express2：表示要插入的值列表。

当使用INSERT语句插入数据时，既可以指定列列表，也可以不指定列列表。如果不指定列列表，那么在VALUES子句中必须为每个列提供数据，并且数据顺序必须与表列顺序完全一致。如果指定列列表，则只需要为相应列提供数据。

1.2.2.1 使用列列表增加数据

在INSERT语句的几种使用方式中，最常用的形式是在INSERT INTO子句中指定添加数据的列，并在VALUES子句中为各个列提供一个值。

insert into users(id, name, age) values(1, 'Jack', 18);

INSERT INTO子句中指定添加数据的列，既可以是数据表的全部列，也可以是部分列。在指定部分列时，需要注意不许为空（NOT NULL）的列必须被指定出来，并且在VALUES子句中的对应赋值也不许为NULL，否则系统显示无法将NULL插入 的错误信息提示。

在使用INSERT INTO子句指定为表的部分列添加数据时，为了避免产生不许为空值的错误，可以使用DESC命令查看数据表中的哪些列不许为空。对于可以为空的列，用户可以不指定其值。

1.2.2.2 不使用列列表增加数据

在向表的所有列添加数据时，也可以省略INSERT INTO子句后面的列表清单，使用这种方法时，必须根据表中定义的列的顺序，为所有的列提供数据。用户可以使用DESC命令来查看表中定义列的顺序。

insert into users values(1, 'JACK', 18, '男');

1.2.2.3 使用特定格式插入日期值

当增加日期数据时，默认情况下日期值必须匹配于日期格式和日期语言；否则在插入数据时会增加错误信息。如果希望使用习惯方式插入日期数据，那么必须使用TO_DATE函数进行转换。

insert into users(id, name, age, birth) values(1, 'JACK', 18, to_date('2019-12-01','yyyy-mm-dd'));

1.2.2.4 使用DEFAULT提供数据

从Oracle Database 9i开始，当增加数据时，可以使用DEFAULT提供数值。当指定DEFAULT时，如果列存在默认值，则会使用其默认值，如果列不存在默认值，则自动使用NULL。

insert into users(id, name, age, birth) values(1, 'JACK', 18, default);

注意：Oracle 中，DEFAULT 关键字和 NULL 在插入操作中有一些区别：

• DEFAULT：如果列定义了默认值，在 INSERT 语句中使用 DEFAULT，系统会插入该列的默认值。
• NULL：如果列允许 NULL，而没有定义默认值，那么插入 NULL 就会将该列的值设为 NULL。如果列有默认值，但插入 NULL，系统会明确地将该列值设为 NULL，不会使用默认值。

示例说明：

假设你的表 users 有如下结构，并且 birth 列有默认值：
CREATE TABLE users (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(50),
    age NUMBER,
    birth DATE DEFAULT SYSDATE
);
使用 DEFAULT：
INSERT INTO users(id, name, age, birth) VALUES (1, 'JACK', 18, DEFAULT);
此时，birth 列会使用默认值（比如当前日期）。

使用 NULL：

INSERT INTO users(id, name, age, birth) VALUES (2, 'JANE', 20, NULL);
此时，birth 列会明确设为 NULL，即使它有默认值。

1.2.2.5 使用替代变量插入数据

如果经常需要给某表插入数据，那么为了避免输入错误，可以将INSERT语句放到SQL脚本，并使用替代变量为表插入数据。如果经常需要为emp表插入数据，那么为了避免输入错误，可以使用SQL脚本插入数据。

1.2.3 批量插入数据

INSERT 语句还有一种强大的用法，就是可以一次向表中添加一组数据，也就是批量插入数据。用户可以使用SELECT语句替换掉原来的VALUES子句，这样由SELECT语句提供添加的数值。

其语法格式如下：

INSERT INTO table_name [(column_name1[,column_name2]…)] selectSubquery

参数说明：

• table_name：表示要插入的表名称。
• column_name1和column_name2：表示指定的列名。
• selectSubquery：任何合法的SELECT语句，其所选列的个数和类型要与语句中的column对应。

insert into users1 
    select * from user2
    where users2.age > 19;

需要注意的是，在使用这种组合语句实现批量插入数据时，INSERT INTO子句指定的列名可以与SELECT子句指定的列名不同，但它们之间的数据类型必须是兼容的，即SELECT语句返回的数据必须满足INSERT INTO表中列的约束。

1.3 更新数据（UPDATE语句）

1.3.1 简介

如果表中的数据不正确或不符合需求，那么就需要对其进行修改。Oracle数据库通过UPDATE语句来实现修改现有的数据记录。 在更新数据时，更新的列数可以由用户自己指定，列与列之间用逗号（,）分隔；更新的条数可以通过WHERE子句来加以限制，使用WHERE子句时，系统只更新符合WHERE条件的记录信息。

UPDATE语句的语法格式如下：

UPDATE table_name
   SET {
  
    (column_name1 = express1),(column_name2 = express2...)} 
 [WHERE condition]
或者
UPDATE table_name
   SET (column_name1, column_name2…) = (selectSubquery)
 [WHERE condition]

参数说明：

• table_name：表示要修改的表名。
• column_name1和column_name2：表示指定要更新的列名。
• selectSubquery：任何合法的SELECT语句，其所选列的个数和类型要与语句中的column对应。
• condition：筛选条件表达式，只有符合筛选条件的记录才被更新。

使用UPDATE语句有以下注意事项：

• 当更新数字列时，可以直接提供数字值，或者用单引号引住。
• 当更新字符列或日期列时，必须用单引号引住。
• 当更新数据时，数据必须要满足约束规则。
• 当更新数据时，数据必须与列的数据类型匹配。

1.3.2 简单操作

1.3.2.1 更新单列数据

当更新单列数据时，set子句后只需要提供一个列。

update users set age = 18 where name = 'jack';

1.3.2.2 更新多列数据

当使用update语句修改表行数据时，既可以修改一列，也可以修改多列。当修改多列时，列之间用逗号分开。

update users set age = 18 where age = '20';

1.3.2.3 更新日期列数据

当更新日期列数据时，数据格式要与日期格式和日期语言匹配；否则会显示错误信息。如果希望使用习惯方式指定日期值，那么可以使用TO_DATE函数进行转换。

update users set birth = to_date('2019-12-01','yyyy/mm/dd') where age = '20';

1.3.2.4 使用DEFAULT选项更新数据

当更新数据时，可以使用DEFAULT选项提供数据。使用此方式时，如果列存在默认值，则会使用默认值更新数据；如果列不存在默认值，则使用NULL。

update users set birth = default where age = '20';

1.3.2.5 使用子查询更新数据

另外，同INSERT语句一样，UPDATE语句也可以与SELECT语句组合使用来达到更新数据的目的。

update users set age = (select avg(age) from user2) where age = '20';

需要注意的是，在将UPDATE语句与SELECT语句组合使用时，必须保证SELECT语句返回单一的值，否则会出现错误提示，导致更新数据失败。

1.3.3 批量更新

Oracle数据库对于大数据量的更新和插入如果一次一行SQL的写的话，显得有点low同时也是效率问题容易让人抓狂，因此在这里记叙下针对Oracle的批量更新和插入
建表：

-- 学生表
create table student (id varchar2(20),name varchar2(20),class varchar2(20));

-- 其他表
create table other (id varchar2(20),name varchar2(20));

1.3.3.1 一般方式

update student s 
set s.name = 
(select o.name from other o where s.id = o.id )
where exists (select 1 from other o where s.id = o.id ) ;

引申问题：
针对上述的sql中的where条件可有可无，如果没了是全表更新，但是更新结果是需要匹配的上才更新，不然不更新，这样不容易看出实际更新了多少行；如果有where条件，就先去查证符合条件的再去更新，这样可以看出来更新了多少行

1.3.3.2 使用游标

点击此处了解 游标相关信息
由于一般的update更新耗时久，5W条数据能耗时12分钟，因此游标方式可以作为考虑

declare
 cursor my_cur is   --声明游标my_cur
    select s.id,o.name from student s,other o where s.id = o.id ;
 count NUMBER;--声明一个number类型的变量
BEGIN count:=0;--初始化变量值为0
  FOR cur IN my_cur LOOP--遍历游标
   UPDATE student s SET s.name=cur.name WHERE s.id=cur.id;
       count:=count+1;--每次循环变量值+1
    IF(count>=1000) THEN
       COMMIT;
       count:=0;--每更新1000行，count值为1000时候，就提交给数据库提交后将变量归零，继续下一个1000行更新
    END IF;
  END LOOP;
   COMMIT;
END;
/

1.4 删除数据

1.4.1 简介

Oracle系统提供了向数据库添加记录的功能，同时也提供了从数据库删除记录的功能。从数据库中删除记录可以使用DELETE语句和TRUNCATE语句，但这两种语句还是有很大区别的

1.4.2 DELETE语句

DELETE语句用来删除数据库中的所有记录和指定范围的记录，若要删除指定范围的记录，同UPDATE语句一样，要通过WHERE子句进行限制.

其语法格式如下：

DELETE FROM table_name [WHERE condition]

参数说明：

• table_name：表示要删除记录的表名。
• condition：筛选条件表达式，是个可选项，当该筛选条件存在时，只有符合筛选条件的记录才被删除掉。
• 删除满足条件的数据：当使用DELETE语句删除数据时，通过指定WHERE子句可以删除满足条件的数据。
• 删除表的所有数据：当使用DELETE删除表的数据时，如果不指定WHERE子句，那么会删除表的所有数据。

1.4.3 TRUNCATE语句

如果用户确定要删除表中的所有记录，那么除了可以使用DELETE语句之外，还可以使用TRUNCATE语句，而且Oracle本身也建议使用TRUNCATE语句。

使用TRUNCATE语句删除表中的所有记录要比DELETE语句快得多。这是因为使用TRUNCATE语句删除数据时，它不会产生回滚记录。当然，执行了TRUNCATE语句的操作也就无法使用ROLLBACK语句撤销。

truncate table users;

在TRUNCATE语句中还可以使用REUSE STORAGE关键字或DROP STORAGE关键字，前者表示删除记录后仍然保存记录所占用的空间，后者表示删除记录后立即回收记录占用的空间。默认情况下TRUNCATE语句使用DROP STORAGE关键字。

1.4.4 PURGE

特点：

• 功能：从数据库中永久删除数据。
• 对象：主要用于回收站（Recycle Bin）中的对象。
• 日志：不记录 Redo 日志，不支持回滚。
• 触发器：不触发任何触发器。
• 占用空间：释放所有数据空间

-- 删除回收站中的表
PURGE TABLE table_name;

-- 删除回收站中的所有对象
PURGE RECYCLEBIN;