插入数据
插入单条记录
insert into table_name|view_name
[(column1[,column2…])]
values(value1[,values, …]);
注:向表或视图中插入的数据必须满足表的完整性约束;
如果在into子句中没有指明任何列名,则values子句中列值的个数、顺序、类型必须与表中列的个数、顺序、类型相匹配;
如果在into子句中指定了列名,则values子句中提供的列值的个数、顺序、类型必须与指定列的个数、顺序、类型按位置对应;
字符型和日期型数据在插入时要加单引号。日期类型数据需要按系统默认格式输入,或使用TO_DATE函数进行日期转换;
eg:向emp表中插入一行记录;
SQL> insert into emp(empno,ename,sal,hiredate)
values(1244, 'JOAN',2500, '20-4月-2007');
eg:向dept表中插入一行记录;
SQL> insert dept values(50, 'IM', 'dalian');
利用子查询插入数据
insert /*+append*/ into
table_name|view_name[(column1[,column2,…]
subquery;
注:如果要将大量数据插入表中,可以利用子查询直接装载的方式进行;
由于直接装载数据的操作过程不写入日志文件,因此数据插入操作的速度大大提高;
eg:向emp表中插入一行记录,其员工名为FAN,员工号为1245,其他信息与员工名为SCOTT的员工信息相同;
SQL> insert into emp
select 1245,'FAN',job,mgr,hiredate,sal,comm,deptno
from emp where ename='SCOTT';
eg:复制emp表中empno,ename,deptno三列的值,并插入到new_emp表中;
SQL> insert /*+append*/ into
new_emp(empno,ename,deptno)
select empno,ename,deptno from emp;
向多个表中插入数据
无条件插入:将数据插入所有指定的表中;
insert [all]
into table1 values(column1,column2[,…])
into table2 values(column1,column2[,…])
……
subquery;
有条件插入:将数据插入符合条件的表中;
insert all|first
when condition1 then into table1(column1[,…])
when condition2 then into table2(column1[,…])
……
else into tablen(column1[,…])
subquery;
注:各when子句之间没有任何标点符号;
all:表示一条记录可以同时插入多个满足条件的表中;
first:表示一条记录只插入第一个满足条件的表中;
eg:将emp表中工资高于2000的员工信息查询后分别插入emp_sal和emp_mgr表;
SQL> insert into
into emp_sal values(empno,hiredate,sal)
into emp_mgr values(empno,mgr,sal)
select empno,hiredate,mgr,sal from emp
where sal>2000;
eg:将emp表中的员工信息按不同部门号分别复制到emp10,emp20,emp40和emp_other表中;
SQL> insert first
when deptno=10 then into emp10
when deptno=20 then into emp20
when deptno=40 then into emp40
else into emp_other
select * from emp;
修改数据
update语句
update table_name|view_name
set column1=value1[,column2=value2…]
[where condition];
eg:修改单条记录
SQL> update emp set sal=sal+100,comm=200 where empno=7844;
eg:修改多条记录
SQL> update emp set sal=sal+150 where deptno=20;
eg:利用子查询修改记录
SQL> update emp set sal=400+(select avg(sal) from emp where deptno=10)
where deptno=40;
merge语句
利用merge语句可以同时完成数据的插入与更新操作;
将源表的数据分别与目标表中的数据根据特性条件进行比较(每次只比较一条记录),
如果匹配,则利用源表中的记录更新目标表中的记录,如果不匹配,则将源表中的记录插入目标表中;
使用merge语句操作时,用户需要具有源表的select对象权限以及目标表的insert,update对象权限;
基本语法:
merge into [schema.]target_table [target_alias]
using [schema.]source_table|source_view|source_subquery [source_alias]
on (condition)
when matched then
update set column1=expression1[,column2=expression2 …]
[where_clause][delete where_clause]
when not matched then
insert (column1[,column2…])
values (expresstion1[,expression2…])
[where_clause];
参数说明:
into:指定进行数据更新或插入的目标表;
using:指定用于目标表数据更新或插入的源表或视图或子查询;
on:决定merge语句执行更新操作还是插入操作的条件;
对于目标表中满足条件的记录,则利用源表中的相应记录进行更新;而源表中不满条件的记录将被插入目标表中;
where_clause:只有当该条件为真时才进行数据的更新或插入操作;
delete where_clause:当目标表中更新后的记录满足该条件时,则删除该记录;
eg:创建一个表t_emp,然后利用子查询得到的数据更新t_emp表中存在的员工信息;
如果t_emp表中更新后的工资大于2000,则删除该记录;如果员工不存在插入工资小于4000的信息;
SQL> create table t_emp
as select empno,sal,detpno
from emp where deptno=40;
SQL> merge into t_emp t
using (select * from emp) s
on (t.empno=s.empno)
when matched then
update set t.sal=t.sal+s.sal
delete where (t.sal>2000)
when not matched then
insert (empno,sal,deptno)
values(s.empno,s.sal,s.deptno)
where (s.sal<4000);
删除数据
delete语句
delete from table|view [where condition];
注:如果省略where子句,将把表中所有数据全部删掉;
eg:删除单条记录
SQL> delete from emp where empno=7844;
eg:删除多条记录
SQL> delete from emp where deptno=10;
eg:利用子查询的删除记录
SQL> delete from emp
where sal>(select sal from emp where empno=7900);
truncate语句
truncate table table_name;
注:truncate与delete区别
truncate释放存储空间;delete只标记为unused,不释放空间;
truncate不写入日志文件,因此执行效率较高,但该操作不可回滚;