Oracle 19C入门到精通之SQL数据操纵语言DML

插入、删除和更新操作使用的SQL语言，称为数据操纵语言(data manipulation language，DML)，它们分别对应INSERT、DELETE和UPDATE这3种语句。在Oracle中，DML除了包括上述提到的3种语句，还包括TRUNCATE、CALL、LOCKTABLE和MERGE等语句。

1. 插入数据（INSERT语句）

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

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

当为数字列增加数据时，可以直接提供数字值，或者用单引号引住。

当为字符列或日期列增加数据时，必须用单引号引住。

当增加数据时，数据必须要满足约束规则，并且必须为主键列和NOT NULL列提供数据。

当增加数据时，数据必须与列的个数和顺序保持一致。

1.1. 插入单条数据

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

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

table_name：表示要插入的表名。

column_name1和column_name2：指定表的完全或部分列名称，如果指定多个列，那么列之间用逗号分开。

express1和express2：表示要插入的值列表。

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

1.1.1. 指定列增加数据

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

--向dept表中的deptno,dname两列插入数据

insert into dept(deptno,dname) values(90,'abc');

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

1.1.2. 不指定列增加数据

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

--不指定列，向dept中插入数据

insert into dept values(88,'design','beijing');

在SQL * Plus中使用desc dept命令查看dept的表结构和列的顺序，可以看到只有deptno、dname、loc三列，所以上述insert into语句的values给定了三个值；

1.1.3. 使用特定格式插入日期值

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

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

insert into emp (empno,ename,job,hiredate)

values(1356, 'MARY','CLERK',to_date('1983-10-20', 'YYYY-MM-DD'));

1.1.4. 使用DEFAULT提供数据

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

--使用DEFAUT插入数据

insert into dept values(60, 'MARKET',DEFAULT);

--查询deptno=60的部门信息

select * from dept where deptno = 60;

可以看到LOC列没有默认值，DEFAULT自动使用了NULL空值；

1.2. 批量插入数据

可以使用SELECT语句替换原来的VALUES子句，这样由SELECT语句提供添加的数值，通过INSERT向表中添加一组数据。其语法格式如下：

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

table_name：表示要插入的表名称。

column_name1和column_name2：表示指定的列名。

selectSubquery：任何合法的SELECT语句，其所选列的个数和类型要与语句中的column对应。

--创建一个与EMP表结构一样的EMP_TEMP表

create table EMP_TEMP

(

empno    NUMBER(4) not null,

ename    VARCHAR2(10),

job      VARCHAR2(9),

mgr      NUMBER(4),

hiredate DATE,

sal      NUMBER(7,2),

comm     NUMBER(7,2),

deptno   NUMBER(2)

)

--将emp表中sal大于等于3000的数据插入emp_temp表

insert into emp_temp select * from emp where sal >= 3000

INSERT INTO子句指定的列名可以与SELECT子句指定的列名不同，但它们之间的数据类型必须是兼容的，即SELECT语句返回的数据必须满足INSERT INTO表中列的约束。

2. 更新数据（UPDATE语句）

在更新数据时，更新的列数可以由用户自己指定，列与列之间用逗号(,)分隔；更新的条数可以通过WHERE子句来加以限制，使用WHERE子句时，系统只更新符合WHERE条件的记录信息。UPDATE语句的语法格式如下：

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

table_name：表示要修改的表名。

column_name1和column_name2：表示指定要更新的列名。

selectSubquery：任何合法的SELECT语句，其所选列的个数和类型要与语句中的column对应。

condition：筛选条件表达式，只有符合筛选条件的记录才被更新。

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

更新数字列时，可以直接提供数字值，或者用单引号引住。

更新字符列或日期列时，必须用单引号引住。

更新数据时，数据必须要满足约束规则。

更新数据时，数据必须与列的数据类型匹配。

2.1. 更新单列数据

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

--将emp表中员工名称ename为SCOTT的工资sal调整为6000

update emp set sal = 6000 where ename='SCOTT';

2.2. 更新多列数据

当修改多列时，列之间用逗号分开。

--将emp表中员工名称ename为JONES的工资sal上调20%，部门编号deptno调整为30

update emp set sal = sal*1.2,deptno=30 where ename='JONES';

2.3. 更新日期列数据

当更新日期列数据时，数据格式要与日期格式和日期语言匹配，否则会显示错误信息，可以使用TO_DATE函数进行日期格式转换；

--将员工编号7788的入职时间hiredate修改为1986/01/01

update emp set hiredate = TO_DATE('1986/01/01', 'YYYY/MM/DD') where empno=7788;

2.4. 使用DEFAULT选项更新数据

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

--更新员工姓名为SCOTT的岗位为默认值

update emp set job = DEFAULT where ename = 'SCOTT';

2.5. 使用子查询更新数据

UPDATE语句也可以与SELECT语句组合使用来达到更新数据的目的。

--将工资sal低于2000的员工工资调整为管理者的平均工资水平

update emp set sal = (select avg(sal) from emp where job = 'MANAGER')

where sal 

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

3. 删除数据（DELETE语句和TRUNCATE语句）

从数据库中删除记录可以使用DELETE语句和TRUNCATE语句，但这两种语句还是有很大区别的，下面分别进行讲解。

3.1. DELETE语句

DELETE语句用来删除数据库中的所有记录和指定范围的记录。

DELETE FROM table_name [WHERE condition]

table_name：表示要删除记录的表名。

condition：筛选条件表达式，是一个可选项。当该筛选条件存在时，只有符合筛选条件的记录才会被删除。

--删除员工姓名ename为SCOTT的员工信息

delete from emp where ename='SCOTT';

--删除emp表中的所有数据

delete from emp；

使用DELETE语句删除数据时，Oracle系统会产生回滚记录，所以这种操作可以使用ROLLBACK语句来撤销。

3.2. TRUNCATE语句

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

--删除emp_temp表的所有数据

truncate table emp_temp;

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