oracle sql语句总结,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;