Oracle DML SQL语句总结

desc dept;--描述这张表

DML = Data Manipulation Language 数据操纵语言

insert into dept values (50, 'game', 'bj');rollback;

备份表

create table emp2 as select * from emp;create table dept2 as select * from dept;create table salgrade2 as select * from salgrade;create table emp3 as select * from emp;

insert

insert into dept2 values (50, 'game', 'bj');insert into dept2 (deptno,dname) values (60, 'game2');insert into dept2 select * from dept;--结构一样

rownum

select empno,ename from emp;select empno,ename from emp where rownum < 5;select empno,ename from emp where rownum <= 5;--ronum只能与<和<=连用，不能与>和=连用select empno,ename from emp where rownum > 10;select empno,ename from emp where rownum = 10;select rownum r,ename from emp;select ename from (select rownum r,ename from emp) where r > 10;select ename,sal from emp order by sal desc;select ename,sal from emp where rownum <=5 order by sal desc;--求薪水最高的前5名雇员select ename,sal from  (select ename,sal from emp order by sal desc) where rownum <=5;--薪水最高的第6到10名雇员（重点掌握），效率高BBSselect ename,sal from   (    select ename,sal,rownum r from       (select ename,sal from emp order by sal desc))where r >= 6 and r <=10;

update

update emp2 set sal = sal*2,ename=ename||'-' where deptno = 10;select ename,sal from emp2 where deptno = 10;

delete

delete from emp2;delete from dept2 where deptno < 25;