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>
1971
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
