联合查询：

1.等值连接:（内连接，自然连接）

SQL> select d.deptno, d.dname, e.empno, e.ename, e.sal

  2  from dept d, emp e

  3  where d.deptno=e.deptno;

13 rows selected.

2.非等值连接:

SQL> select e.ename, e.sal, s.grade

  2  from emp e, salgrade s

  3* where e.sal between s.losal and s.hisal

3.自连接:

SQL> select e.ename, m.ename

  2  from emp e, emp m

  3  where e.mgr=m.empno;

4.外连接:

sql99(sql2003) standard:

SQL> select e.empno, e.ename, d.dname

  2  from emp e left outer join dept d

  3  on e.deptno = d.deptno;

14 rows selected.

oracle standard:

SQL> select e.empno, e.ename, d.dname

  2  from emp e, dept d

  3  where e.deptno=d.deptno(+); 

14 rows selected.

oracle can't but sql99 can:

SQL> select e.empno, e.ename, d.dname

  2  from emp e full outer join dept d

  3  on e.deptno=d.deptno;

15 rows selected.

5.笛卡尔乘积：

SQL> select d.deptno, d.dname, e.empno, e.ename, e.sal

  2  from dept d, emp e

56 rows selected.(14 * 4)

子查询:

SQL> select empno, ename, sal        

  2  from emp

  3  where sal > (select sal                     

  4               from emp

  5               where ename='BLAKE');

null值处理：

SQL> select empno, ename, sal

  2  from emp

  3  where empno not in (select mgr

  4                      from emp);

no rows selected. (And results to now result).

SQL> select empno, ename, sal

  2  from emp

  3  where empno in (select mgr

  4                  from emp);

EMPNO ENAME    SAL

----- ------ -----

 7566 JONES   2975

 7698 BLAKE   2850

 7782 CLARK   2450

 7788 SCOTT   3000

 7839 KING    5000

 7902 FORD    3000

6 rows selected. (Or results to records).

SQL> select empno, ename, sal

  2  from emp

  3* where empno not in (select mgr

                         from emp

                         where mgr is not null)

EMPNO ENAME    SAL

----- ------ -----

 7369 SMITH    800

 7499 ALLEN   1600

 7521 WARD    1250

 7654 MARTIN  1250

 7844 TURNER  1500

 7876 ADAMS   1100

 7900 JAMES    950

 7934 MILLER  1300

8 rows selected. (Remove null value results to records).

Top N分析：

SQL> select * from (select empno, ename, sal from emp order by sal desc) where rownum < 4 order by rownum;

EMPNO ENAME    SAL

----- ------ -----

 7839 KING    5000

 7788 SCOTT   3000

 7902 FORD    3000

Union/Union All/Intersect/Minus：

SQL> break on deptno skip 1;

SQL> select deptno, job, sum(sal)

  2  from emp

  3  group by deptno, job

  4  union

  5  select deptno, to_char(null), sum(sal)

  6  from emp

  7  group by deptno

  8  union

  9  select to_number(null), to_char(null), sum(sal)

 10  from emp;

SQL> select deptno, job, sum(sal) tsal

  2  from emp

  3  group by rollup(deptno,job);

Insert/Update:

SQL> create table test as

  2  select empno, ename, sal

  3  from emp   

  4  where 1=2;

SQL> insert into test values(100, 'tom', null);

SQL> insert into test(empno, ename) values (101, 'mike');

SQL> truncate table test;

SQL> insert into test select empno, ename, sal from emp;

14 rows created.

SQL> show feedback;

FEEDBACK ON for 6 or more rows

SQL> set feedback off;

SQL> set feedback on;

SQL> set feedback 6;

SQL> update test set sal = (select sal from emp where empno=7499) where empno=7369;

Truncate/Delete:

DML(Insert, Update, Delete, Merge)

DDL(Create, Alter, Drop, Rename, Truncate, Comment)

DCL(Grant, Revoke)

Transaction Control(Commit, Rollback, Savepoint)

显示提交：rollback, commit

隐式提交：DDL/DCL,正常退出

          begin

              commit;

              DDL/DCL;

              commit;

          end

隐式回滚：异常退出

SQL> savepoint A;

Savepoint created.

SQL> delete from test where empno=7369;

1 row deleted.

SQL> rollback to savepoint A;

Create Table, View, Sequence, Index, Synonyms(同义词)

varchar2(10)/varchar2(10 char)

number/number(8, 2)

CLOB(text)/BLOB(Binary, media, exe)

BFile: Binary data stored in an external file

Constraint:

not null, unique, primary key, foreign key,check

SQL> purge recyclebin;

SQL> drop table test purge;

Table:

SQL> create table test(id number, name varchar2(10), create_date date default sysdate);

SQL> insert into test values(100, 'mike', default);

SQL> create table et(eno number,

  2                  ename varchar2(10),

  3                  constraint en_u_eno unique(eno));

SQL> create table dt(dno number(4) primary key,

  2                  dname varchar2(10));

SQL> create table d as select deptno dno, dname, loc from dept;    

SQL> create table e as select empno, ename, sal, deptno as dno from emp;

SQL> alter table d add constraint d_pk primary key (dno);

SQL> alter table e add constraint e_fk foreign key (dno) references d(dno);

SQL> drop table e purge;

SQL> create table e(eno number,

  2                 ename varchar2(10),

  3                 dno number references d(dno) on delete set null);

SQL> create table e1(eno number,

  2                  ename varchar2(10),

  3                  dno number references d(dno) on delete cascade);

SQL> alter table e1 read only;

SQL> create table e2 as select * from emp;

SQL> alter table e2 add constraint ck_e2 check ((sal > 0) and (sal < 10000));

View:

SQL> conn / as sysdba;

SQL> grant create view to scott;

SQL> conn scott/tiger;

SQL> create or replace view v1 as select empno, ename, sal from emp with check option;

SQL> create or replace view v2 as select empno, ename, sal from emp with read only;

Index:

{TODO:}

Sequence:

{TODO:}

Synonym:

SQL> conn / as sysdba;

SQL> grant create synonym to scott;

SQL> conn scott/tiger;

SQL> create synonym sg for salgrade;