oracle sql语句笔记

select employee_id,last_name,salary,department_id,department_name

from employees left join departments using(department_id)

(department_id)

select employee_id,last_name,salary,department_id,department_name

from employees right join departments using(department_id)

(department_id)

select employee_id,last_name,salary,department_id,department_name

from employees full outer join departments using(department_id)

(department_id)

select?from table

where?operator (select?from table);

;

Top-Norder by?Select * from emp where sal>(select sal from emp where empno=8888);//8888select * from emp where sal>(select sal from emp where empno=7566);

select * from emp where sal>(select avg(sal) from emp group by deptno);//Desc emploree//emploreeinAnyAllSelect * from emp where sal>any(select avg(sal) from emp group by deptno);

Select * from emp where sal>all(select avg(sal) from emp group by deptno);

Select * from emp where job in(select job from emp ename='MARTIN' ?or ename='SMITH');

ORACLETop Nselect * from (select * from emp order by sal desc) where rownum<=5;

RownumrownumSelect * from employee where rownum<=5//rownumSelect rownum,a.* from(select * from emp order by sal desc) a;//rownumSelect * from

(select rownum myno,a.* from(select * emp order by sal desc) a)

Where myno >=5 and myno <=10;

1. 第章1insertNull.

   insertInsert into dept1(id,name) select deptno,dname from dept;

   valuesinsertwherewhereUpdate emp set sal=sal+99;

   Update emp set sal=sal+99 where empno=7599;

   Update student2

   Set age=nvl(age,o)+1;

   DeletewhereDelete from table

   Delete from table where empno=8888;

   MergeMergeCreate table test1 (eid number(10),name varchar);

   Insert into test1 values(1001,'''20-570',2300);

   Insert into test1 values(1002,'''16-570',2600);

   Select * from test1;

   Create table test2(eid number(10),name varchar2(20),birth date,salary number(8,2));

   Select * from test2;

   Merge into test2

   Using test1

   On (test1.eid=test2.eid)

   When matched then

   Update set name=test1.name,birth=test1.birth,salary=test1.salary

   When not matched then

   Insert(eid,name,birth,salary) values(test1.eid,test1.name,test1.birth,test1,salary);

   Select * from test2;

   ACIDAtomicity,Consistency,Isolation,Durabilitycommit;Sql PlusSQLshow autocommit;

   Autocommit OFF;

   Autocommit IMMEDIATE

   Set autocommit on;

   Set autocommit off;

   DML(commit)DMLselect.

   savepointsInsert into dept values(55,'Adv','Beijing');

   Insert into dept values(56,'Seev','Beijing');

   Savepoint p1;

   Insert into dept values(57,'Acc','Tianjin');

   Select * from dept;

   Rollback to p1;

   Select * from dept;

   commitsavepointCreate table scott.test(

   Eid number(10),

   Name varchar2(20),

   Hiredate date default sysdate,

   Salary number(8,2) default 0

   );

   OracleCreate table student(

   Sid number(3) unique;

   Name varchar2(20)

   );

   Create table student(

   Sid number(3),

   Name varchar2(20),

   Constraint studnet_sid_un unique(sid)

   );

   Create table record(

   Sid number(3),

   Subject_name varchar2(20),

   Record number(4),

   Constraint record1_sid_subName_un unique(sid,subject_name)

   ); ???--sidsubject_nameCreate table record(

   Student_id number(3),

   Subject_id varchar2(20),

   Record number(3),

   Constraint record_stuId_subId_pk primary key(student_id,subject_id)

   );

   check

   Currval,nextval,level,rownumSysdate,uid,user,userevnCreate table test1(

   Name varchar2(20),

   Age number(3) check(age>=0 and age<=120)

   );

   1. 第章create viewCreate or replace view myview1() as select empno,ename,job,sal from emp where deptno=20;

      Desc myview1;?drop view myview1;

      Create or replace ???--Create or replace view v1 as select empno,ename,sal*12?,from emp where deptno=30;

      Desc v1; ???--?forcecreate or replace force view myview2 as select empno,ename,job,sal from emp2 where deptno=20;

      Create or replace view v_sal(deptno,maxsal,minsal,avgsal) as select deptno,max(sal),min(sal),avg(sal) from emp group by deptno;

      DMLgroup bydistinctrownuminsertDMLInsert,update,delete

      with read onlyCreate or replace force view myview2

      As select empno,ename,job,sal from emp2 where deptno=20 with read only;

      SQLSelect rownum,a.* from (select * from emp order by sal) a where rownum<=5 ??--TOP 5

      I/Ooraclecreate indexCreate index myindex on emp(ename);

      drop indexdropindexdrop index myindex;

      Create index emp_idx1 on emp(ename);

      Select * from emp where ename='KING'; ??--?where2%-4%

      where2%-4%SQLCreate index myindex on emp(lower(ename));

      select * from emp where lower(ename)='king';

      Create sequence sequence_name

      Increment by n ???--Start with n ???--Maxvalue n | nomaxvalue ???--Minvalue n | nominvalue ???--Cycle | nocycle ??--Cache n | cache ??--Order | noorder ?--Create sequence mysequence1

      Increment by 1

      Start with 1

      Nomaxvalue nocycle;

      Create sequence mysequence2;

      user_sequencesNextval/currvalNextvalCurrvalSelect mysequence1.currval from dual;

      Select mysequence1.nextval from dual;

      Insert into test1 values(mysequence1.nextval,'Tom');

      cache nnocachenoorderAlter sequence sequence

      Increment by n

      Maxvalue n | nomaxvalue

      Minvalue n | nomin value

      Cycle | nocycle

      Cache n | nocache

      Order | noorder

      altermaxvaluedrop sequencedropCreate synonym gt1 for emp; ???--empgt1

      Select * from gt1; ??--Drop synonym gt1; ??--Create public synonym gt2 for scott.emp; //scott

      ?