DML 语句 语句操作语言
INSERT
顺序
特定列
子查询代替values
子查询数据录入多张表
UPDATE
单个列 多个列
子查询
关联更新（协同更新）
DELETE
where
MERGE
select column_name,DATA_TYPE,DATA_DEFAULT from user_tab_columns where table_name='T1';
INSERT 方法:
按顺序列插入
按指定列插入
default值插入
子查询(代替values)结果插入
替代变量插入
子查询指定列插入(代替表名)
子查询限制条件插入
INSERT INTO (SUB-Q WHERE 条件 with check option) values()
使用with check option 必须保证向条件列插入数据
子查询插入到多张表
insert first|all
when empno <7800 then
into t1 values(empno,ename,sal)
when empno <7900 then
into t2 values(empno,ename,job)
else
into t3 values(empno,ename,hiredate)
select * from emp where deptno=10;
SCOTT@ora10g> create table t1(id number,name varchar2(10));
Table created.
SCOTT@ora10g> create table t1 (id number,name varchar2(10),hiredate date default sysdate);
Table created.
SCOTT@ora10g> insert into t1 values(1,'seker',to_date('2009-09-09','YYYY-MM-DD'));
SCOTT@ora10g> insert into t1 values(1,'SEKER');
1 row created.
SCOTT@ora10g> insert into t1 values(1,'SEKER',default)
SCOTT@ora10g> select * from t1;
ID NAME
---------- --------------------------------------------------
1 SEKER
不使用列列表增加数据 则逐个字段插入
SCOTT@ora10g> insert into t1 (id) values(2);
1 row created.
SCOTT@ora10g> select * from t1;
ID NAME
---------- --------------------------------------------------
1 SEKER
2
SCOTT@ora10g>
特定格式插入日期
SCOTT@ora10g> alter table t1 add (hiredate date);
Table altered.
SCOTT@ora10g> insert into t1 (hiredate) values(to_date('2010-10-10','YYYY-MM-DD'));
1 row created.
SCOTT@ora10g> select * from t1;
ID NAME HIREDATE
---------- -------------------------------------------------- ---------
1 SEKER
2
10-OCT-10
SCOTT@ora10g>
default提供数据
SCOTT@ora10g> alter table t1 modify(hiredate date default sysdate);
Table altered.
SCOTT@ora10g> insert into t1(id,name) values(3,'zorro');
1 row created.
SCOTT@ora10g> select * from t1;
ID NAME HIREDATE
---------- -------------------------------------------------- ---------
1 SEKER
2
10-OCT-10
3 zorro 31-DEC-10
SCOTT@ora10g>
替代变量插入数据
SCOTT@ora10g> ! cat insert1.sql
accept id prompt 'ID: '
accept name prompt 'name: '
accept hiredate prompt 'hiredate: '
insert into t1 values('&id','&name',&hiredate);
SCOTT@ora10g> @ insert1.sql
ID: 99
name: shrek
hiredate: sysdate
old 1: insert into t1 values('&id','&name',&hiredate)
new 1: insert into t1 values('99','shrek',sysdate)
1 row created.
SCOTT@ora10g> select * from t1;
ID NAME HIREDATE
---------- -------------------------------------------------- ---------
1 SEKER
2
10-OCT-10
3 zorro 31-DEC-10
99 shrek 31-DEC-10
SCOTT@ora10g>
子查询插入数据
SCOTT@ora10g> insert into t1(id,name,hiredate) select empno,ename,hiredate from emp where ename='SCOTT';
1 row created.
SCOTT@ora10g> select * from t1;
ID NAME HIREDATE
---------- -------------------------------------------------- ---------
1 SEKER
2
10-OCT-10
3 zorro 31-DEC-10
99 shrek 31-DEC-10
7788 SCOTT 19-APR-87
6 rows selected.
SCOTT@ora10g>
按子查询得到的列进行插入
SCOTT@ora10g> truncate table t1;
Table truncated.
SCOTT@ora10g> insert into t1 values(1,'seker',sysdate);
1 row created.
SCOTT@ora10g> select * from t1;
ID NAME HIREDATE
---------- -------------------------------------------------- ---------
1 seker 31-DEC-10
SCOTT@ora10g> insert into (select id,name from t1) values(2,'zorro');
1 row created.
SCOTT@ora10g> select * from t1;
ID NAME HIREDATE
---------- -------------------------------------------------- ---------
1 seker 31-DEC-10
2 zorro 31-DEC-10
SCOTT@ora10g> insert into (select id from t1) values(3);
1 row created.
SCOTT@ora10g> select * from t1;
ID NAME HIREDATE
---------- -------------------------------------------------- ---------
1 seker 31-DEC-10
2 zorro 31-DEC-10
3 31-DEC-10
SCOTT@ora10g>
子查询限制插入数据 with check option 选项
SCOTT@ora10g> select * from t1;
ID NAME HIREDATE DEPTNO
---------- ---------- --------- ----------
1 seker 31-DEC-10 10
2 zorro 31-DEC-10 20
SCOTT@ora10g> insert into (select id,name,deptno from t1 where deptno=20 with check option) values(3,'shrek',20);
1 row created.
SCOTT@ora10g> insert into (select id,name,deptno from t1 where deptno=20 with check option) values(4,'blues',10);
insert into (select id,name,detpno from t1 where detpno=20 with check option) values(4,'blues',10)
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
SCOTT@ora10g> 不满足where子句的需求 所以没成功
多表插入数据
将一个子句中的数据分别插入到多个表中
insert all
into table values(column,...)
into table values(column,...)
subquery;
SCOTT@ora10g> create table e1 as select ename,sal,hiredate from emp where 9=0;
Table created.
SCOTT@ora10g> create table e2 as select ename,deptno,mgr from emp where 9=0;
Table created.
SCOTT@ora10g> insert all
2 into e1 values(ename,sal,hiredate)
3 into e2 values(ename,deptno,mgr)
4 select ename,sal,hiredate,deptno,mgr from emp where deptno=10;
6 rows created.
SCOTT@ora10g> select * from e1;
ENAME SAL HIREDATE
---------- ---------- ---------
CLARK 2450 09-JUN-81
KING 5000 17-NOV-81
MILLER 1300 23-JAN-82
SCOTT@ora10g> select * from e2;
ENAME DEPTNO MGR
---------- ---------- ----------
CLARK 10 7839
KING 10
MILLER 10 7782
SCOTT@ora10g>
将一张表的数据分散到多个表 first 一行只能给一个表 即使两个表的条件都满足
insert first
when expr then
into table values(column,...)
when expr then
into table values(column,...)
subquery;
SCOTT@ora10g> truncate table e1;
Table truncated.
SCOTT@ora10g> truncate table e2;
Table truncated.
SCOTT@ora10g> insert first
2 when sal>3000 then
3 into e1 values(ename,sal,hiredate)
4 when sal>2000 then
5 into e2 values(ename,deptno,mgr)
6 select ename,sal,hiredate,deptno,mgr from emp;
6 rows created.
SCOTT@ora10g> select * from e1;
ENAME SAL HIREDATE
---------- ---------- ---------
KING 5000 17-NOV-81
SCOTT@ora10g> select * from e2;
ENAME DEPTNO MGR
---------- ---------- ----------
JONES 20 7839
BLAKE 30 7839
CLARK 10 7839
SCOTT 20 7566
FORD 20 7566
SCOTT@ora10g>
*****************************************************************************************
update 更新表中数据
一定要加where子句 否则会应用到所有行
加了where子句时只更新where条件受影响的行
多列同时更新列与列之间用逗号分隔
更新日期列时要避免隐式转换 使用to_date()函数
使用defulat值更新依旧使用default关键字
子查询更新保证等号两边列的数量一致
单行更新
SCOTT@ora10g> select * from t1 where name='shrek';
ID NAME HIREDATE DEPTNO
---------- ---------- --------- ----------
3 shrek 31-DEC-10 20
SCOTT@ora10g> update t1 set deptno=30 where name='shrek';
1 row updated.
SCOTT@ora10g> select * from t1 where name='shrek';
ID NAME HIREDATE DEPTNO
---------- ---------- --------- ----------
3 shrek 31-DEC-10 30
SCOTT@ora10g>
多行更新
SCOTT@ora10g> select * from t1 where deptno=20;
ID NAME HIREDATE DEPTNO
---------- ---------- --------- ----------
2 zorro 31-DEC-10 20
4 blues 31-DEC-10 20
SCOTT@ora10g> update t1 set deptno=40 where deptno=20;
2 rows updated.
SCOTT@ora10g> select * from t1 where deptno=40;
ID NAME HIREDATE DEPTNO
---------- ---------- --------- ----------
2 zorro 31-DEC-10 40
4 blues 31-DEC-10 40
SCOTT@ora10g>
更新日期列
SCOTT@ora10g> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
Session altered.
SCOTT@ora10g> select * from t1 where id=1;
ID NAME HIREDATE DEPTNO
---------- ---------- ------------------- ----------
1 seker 2010-12-31 08:50:20 10
SCOTT@ora10g> update t1 set hiredate=to_date('2005-01-05','YYYY-MM-DD') where id=1;
1 row updated.尽量使用此函数,避免隐式转换.
SCOTT@ora10g> select * from t1 where id=1;
ID NAME HIREDATE DEPTNO
---------- ---------- ------------------- ----------
1 seker 2005-01-05 00:00:00 10
SCOTT@ora10g>
使用默认选项更新列
SCOTT@ora10g> select * from t1 where id=1;
ID NAME HIREDATE DEPTNO
---------- ---------- ------------------- ----------
1 seker 2005-01-05 00:00:00 10
SCOTT@ora10g> update t1 set hiredate=default where id=1;
1 row updated.
SCOTT@ora10g> select * from t1 where id=1;
ID NAME HIREDATE DEPTNO
---------- ---------- ------------------- ----------
1 seker 2010-12-31 09:26:20 10
SCOTT@ora10g>
多列同时更新
SCOTT@ora10g> select * from t1 where id=2;
ID NAME HIREDATE DEPTNO
---------- ---------- ------------------- ----------
2 zorro 2010-12-31 08:50:26 40
SCOTT@ora10g> update t1 set name='pg',deptno=20 where id=2;
1 row updated.
SCOTT@ora10g> select * from t1 where id=2;
ID NAME HIREDATE DEPTNO
---------- ---------- ------------------- ----------
2 pg 2010-12-31 08:50:26 20
SCOTT@ora10g>
使用子查询结果更新数据
SCOTT@ora10g> select * from t1 where id=2;
ID NAME HIREDATE DEPTNO
---------- ---------- ------------------- ----------
2 pg 2010-12-31 08:50:26 20
SCOTT@ora10g>
SCOTT@ora10g> update t1 set (id,name,hiredate,deptno)=(select empno,ename,hiredate,deptno from emp where ename='SCOTT') where id=2;
1 row updated.
SCOTT@ora10g> select * from t1 where id=2;
no rows selected
SCOTT@ora10g> select * from t1 where name='SCOTT';
ID NAME HIREDATE DEPTNO
---------- ---------- ------------------- ----------
7788 SCOTT 1987-04-19 00:00:00 20
SCOTT@ora10g>
关联子查询更新
SQL> create table t77 as select * from emp;
Table created.
SQL> update t77 set sal=100,ename='seker';
14 rows updated.
SQL>
SQL> select empno,ename,sal,deptno from t77 order by 4;
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7782 seker 100 10
7839 seker 100 10
7934 seker 100 10
7566 seker 100 20
7902 seker 100 20
7876 seker 100 20
7369 seker 100 20
7788 seker 100 20
7521 seker 100 30
7844 seker 100 30
7499 seker 100 30
7900 seker 100 30
7698 seker 100 30
7654 seker 100 30
14 rows selected.
SQL> update t77 set (ename,sal)=(select ename,sal from emp where empno=t77.empno) where deptno=10;
3 rows updated.
SQL> select empno,ename,sal,deptno from t77 order by 4;
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7782 CLARK 2450 10
7839 KING 5000 10
7934 MILLER 1300 10
7566 seker 100 20
7902 seker 100 20
7876 seker 100 20
7369 seker 100 20
7788 seker 100 20
7521 seker 100 30
7844 seker 100 30
7499 seker 100 30
7900 seker 100 30
7698 seker 100 30
7654 seker 100 30
14 rows selected.
SQL>
delete 删除表数据
不加where 全部记录删除
加where只删除条件满足行
where条件中也可以使用子查询
删除全部数据
SCOTT@ora10g> select count(*) from t2;
COUNT(*)
----------
4
SCOTT@ora10g> delete t2;
4 rows deleted.
SCOTT@ora10g> select count(*) from t2;
COUNT(*)
----------
0
SCOTT@ora10g>
删除特定条件数据
SCOTT@ora10g> select * from t1;
ID NAME HIREDATE DEPTNO SAL
---------- ---------- ------------------- ---------- ----------
1 seker 2010-12-31 09:26:20 10 200
2 SCOTT 1987-04-19 00:00:00 20 400
3 shrek 2010-12-31 08:51:19 30 600
4 blues 2010-12-31 09:23:51 40 800
SCOTT@ora10g> delete t1 where sal >500;
2 rows deleted.
SCOTT@ora10g> select * from t1;
ID NAME HIREDATE DEPTNO SAL
---------- ---------- ------------------- ---------- ----------
1 seker 2010-12-31 09:26:20 10 200
2 SCOTT 1987-04-19 00:00:00 20 400
SCOTT@ora10g>
也可以使用子查询
SCOTT@ora10g> select * from t1;
ID NAME HIREDATE DEPTNO SAL
---------- ---------- ------------------- ---------- ----------
1 seker 2010-12-31 09:26:20 10 200
2 SCOTT 1987-04-19 00:00:00 20 400
3 shrek 2010-12-31 08:51:19 30 600
4 blues 2010-12-31 09:23:51 40 800
SCOTT@ora10g> select * from t2;
ID NAME HIREDATE DEPTNO SAL
---------- ---------- ------------------- ---------- ----------
1 seker 2010-12-31 09:26:20 10 100
2 SCOTT 1987-04-19 00:00:00 20 300
SCOTT@ora10g> delete t1 where id in (select id from t2);
2 rows deleted.
SCOTT@ora10g> select * from t1;
ID NAME HIREDATE DEPTNO SAL
---------- ---------- ------------------- ---------- ----------
3 shrek 2010-12-31 08:51:19 30 600
4 blues 2010-12-31 09:23:51 40 800
SCOTT@ora10g>
*************************************************************************************************************
MERGE
经常在程序里有这样的业务逻辑
IF SELECT COUNT(*) >0 FROM …WHERE 条件语句
THEN
UPDATE
ELSE
INSERT INTO
END IF;
ORACLE就提供了merge来解决这种选择性执行DML语句
语法:
MERGE INTO table_name table_alias
USING (table|view|sub_query) alias
ON (join condition)
WHEN MATCHED THEN
UPDATE SET
COL1=COL_VAR1,
COL2=COL_VAR2
WHERE expr
DELETE WHERE expr
WEHN NOT MATCHED THEN
INSERT (col1,col2,...)
VALUES (col_var1,col_var2,...);
INTO 被插入或更新的表
USING 参照表 插入或更新的数据从这个表获取
ON 条件
WHEN MATCHED THEN 条件成立判断
UPDATE SET 更新内容(不写表名)
WEHN NOT MATCHED THEN 条件失败判断
INSERT VALUES 插入内容(不写表名)
将emp和myemp中empno相同的行做同步 参照数据在emp表内,被同步数据在myemp中
实验环境建立 myemp 并做修改 但保持empno员工号和emp中一致
SQL> create table myemp as select empno,ename,sal from emp where empno in (7839,7788,7369);
Table created.
SQL> select * from myemp;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7788 SCOTT 3000
7839 KING 5000
SQL> update myemp set ename='ZORRO',sal='999' where empno=7369;
1 row updated.
SQL> update myemp set ename='SEKER',sal='888' where empno=7788;
1 row updated.
SQL> select * from myemp;
EMPNO ENAME SAL
---------- ---------- ----------
7369 ZORRO 999
7788 SEKER 888
7839 KING 5000
参照emp同步
SQL> merge into myemp m
using emp e
on (e.empno=m.empno)
when matched then
update set m.ename=e.ename,m.sal=e.sal;
3 rows merged.
SQL> select * from myemp;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7788 SCOTT 3000
7839 KING 5000
SQL>
将A表中存在 B表中不存在的数据 同步到B表
SQL> create table youremp as select * from myemp where sal in (800,3000);
Table created.
SQL> select * from myemp;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7788 SCOTT 3000
7839 KING 5000
SQL> select * from youremp;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7788 SCOTT 3000
SQL> merge into youremp y
2 using myemp m
3 on(y.empno=m.empno)
4 when not matched then
5 insert values(m.empno,m.ename,m.sal);
1 row merged.
SQL> select * from youremp;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7788 SCOTT 3000
7839 KING 5000
SQL>
对update insert添加条件
只同步KING的工资 并把在myemp缺少的10号部门员工同步
SQL> update emp set sal=3333 where ename='SCOTT';
1 row updated.
SQL> update emp set sal=6666 where ename='KING';
1 row updated.
SQL> select ename,sal from emp where ename IN ('KING','SCOTT');
ENAME SAL
---------- ----------
SCOTT 3333
KING 6666
SQL> select * from myemp;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7788 SCOTT 3000
7839 KING 5000
SQL> select * from emp where deptno=10;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7839 KING PRESIDENT 1981-11-17 00:00:00 6666 10
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
SQL> merge into myemp m
using emp e
on (e.empno=m.empno)
when matched then
update set m.sal=e.sal
where m.ename='KING'
when not matched then
insert values(e.empno,e.ename,e.sal)
where e.deptno=10;
3 rows merged.
SQL> select * from myemp;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7788 SCOTT 3000
7839 KING 6666
7782 CLARK 2450
7934 MILLER 1300
SQL>
练习:
对myemp添加一列deptno
把myemp中的每个用户的部门号从emp中同步过来
SQL> alter table myemp add (deptno number);
Table altered.
SQL>
SQL> select empno,ename,sal,deptno from emp where empno in (7369,7788,7839,7782,7934);
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7369 SMITH 800 20
7782 CLARK 2450 10
7788 SCOTT 3333 20
7839 KING 6666 10
7934 MILLER 1300 10
SQL> select * from myemp;
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7369 SMITH 800
7788 SCOTT 3000
7839 KING 6666
7782 CLARK 2450
7934 MILLER 1300
SQL>
完成后效果
merge into myemp m
using emp e
on (m.empno=e.empno)
when matched then
update set m.deptno=e.deptno;
SQL> select * from myemp;
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7369 SMITH 800 20
7788 SCOTT 3000 20
7839 KING 6666 10
7782 CLARK 2450 10
7934 MILLER 1300 10
SQL>
update后可以使用delete where子句
删除规则: 匹配delete条件并且匹配on条件
SQL> update myemp set empno=9999 where ename='SMITH';
1 row updated.
SQL> update myemp set sal=1111 where ename='MILLER';
1 row updated.
SQL> select * from myemp;
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
9999 SMITH 800 20
7788 SCOTT 3000 20
7839 KING 6666 10
7782 CLARK 2450 10
7934 MILLER 1111 10
SQL> merge into myemp m
using emp e
on (m.empno=e.empno)
when matched then
update set m.sal=e.sal
delete where deptno=20 and m.empno=e.empno
when not matched then
insert values(e.empno,e.ename,e.sal,e.deptno)
where e.deptno=30;
if m.empno=e.empno
then
if deptno=20
then
delete
10 rows merged.
SQL> select * from myemp;
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
9999 SMITH 800 20
7839 KING 6666 10
7782 CLARK 2450 10
7934 MILLER 1300 10
7499 ALLEN 1600 30
7521 WARD 1250 30
7654 MARTIN 1250 30
7698 BLAKE 2850 30
7844 TURNER 1500 30
7900 JAMES 950 30
10 rows selected.
SCOTT@orasid> create table t1 as select empno,ename,sal from emp
where deptno=10 and rownum<=1;
SCOTT@orasid> insert into t1 select empno,ename,sal from emp
where deptno=20 and rownum <=2;
2 rows created.
SCOTT@orasid> insert into t1 select empno,ename,sal from emp
where deptno=30 and rownum <=3;
3 rows created.
SCOTT@orasid> update t1 set ename='AAA',sal=100 where rownum <=4;
4 rows updated.
SCOTT@orasid> alter table t1 add (deptno number);
Table altered.
SCOTT@orasid> select * from t1;
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7782 AAA 100
7369 AAA 100
7566 AAA 100
7499 AAA 100
7521 WARD 1250
7654 MARTIN 1250
6 rows selected.
SCOTT@orasid> 使用merge 将emp里的ename,sal,deptno协同更新到T1里
SCOTT@orasid> 并将emp表里的10号部门中的其他人插入到t1里
SCOTT@orasid>
版权声明：本文为gongxiude原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。