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名雇员（重点掌握），效率高BBS
select 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;