插入、删除和更新操作使用的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关键字。
领取专属 10元无门槛券
私享最新 技术干货