drop table t_name;
show recyclebin;
flashback table table_name to before drop;
drop table t_name purge;
after we drop table a table we can flash it back from recyclebin ,except SYS schema of the table.
view
user_views
create or replace FORCE view vw_force as select * from aa; --force to create a view when aa table doesn't exist.
sequence
user_sequences
index
user_ind_columns
user_indexes
set autotrace on;
synonym
user_synonyms
dba_synonyms --see the pulic synonym
dictionary
dict --contains all the dictionary tables
user_tab_comments
user_col_comments
user_views
user_indexes
user_ind_columns
user_constraints
user_cons_columns
user_sequences
privileges
user_tab_privs_made
alter user u1 quota unlimited on tablespace_name; --specified specific tablespace of unlimited tablespace for the user
system_privilege_map --all the system privileges
table_privileges --all the object privileges
session_roles --current session roles
session_privs --current session privs
user_role_privs --all roles
alter user username default role role2;
external table
create directory
create table ( )

create table dir(id number,name1 varchar2(10),name2 varchar2(10))
organization external
(type oracle_loader
default directory dir
access parameters
(records delimited by newline
fields terminated by ','
(id,name1,name2))
location ('dir_table'));

create table dir(id number,name1 varchar2(10),name2 varchar2(10))
organization external
(type data_pump
default directory dir
access parameters
(records delimited by newline
fields terminated by ','
(id,name1,name2))
location ('dir_table'));

多表insert操作
insert all/first
when sal>10000 then
into table_name values()
when sal>100 then
into table_name2 values()
select * from table_name

SQL> insert all when sal>2000 then
2 into e1 values(ename,sal,deptno)
3 else
4 into e2 values(ename,sal,job)
5 select * from emp;

14 rows created.

SQL> select * from e1;

ENAME SAL DEPTNO
---------- ---------- ----------
JONES 2975 20
BLAKE 2850 30
CLARK 2450 10
SCOTT 3000 20
KING 5000 10
FORD 3000 20

6 rows selected.

SQL> select * from e2;

ENAME SAL JOB
---------- ---------- ---------
SMITH 800 CLERK
ALLEN 1600 SALESMAN
WARD 1250 SALESMAN
MARTIN 1250 SALESMAN
TURNER 1500 SALESMAN
ADAMS 1100 CLERK
JAMES 950 CLERK
MILLER 1300 CLERK

8 rows selected.

--数据变化的历史版本
versions_startscn,versions_starttime
select * from emp versions between scn minvalue and maxvalue where empno=7844;
高级分组
rollup
cube
grouping 0参与分组,1不参与分组
grouping sets
select deptno,sum(sal) from emp group by rollup(deptno)
select deptno,sum(sal) from emp group by cube(deptno)
SQL> select deptno,sum(sal) from emp group by rollup(deptno);

DEPTNO SUM(SAL)
---------- ----------
10 8750
20 10875
30 9400
29025

SQL> select deptno,sum(sal) from emp group by cube(deptno);

DEPTNO SUM(SAL)
---------- ----------
29025
10 8750
20 10875
30 9400
select deptno,job,sum(sal) from emp group by rollup(deptno,job)
select deptno,job,sum(sal) from emp group by cube(deptno,job)
SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);

DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
20 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600

DEPTNO JOB SUM(SAL)
---------- --------- ----------
30 9400
29025

13 rows selected.

SQL> select deptno,job,sum(sal) from emp group by cube(deptno,job);

DEPTNO JOB SUM(SAL)
---------- --------- ----------
29025
CLERK 4150
ANALYST 6000
MANAGER 8275
SALESMAN 5600
PRESIDENT 5000
10 8750
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 10875

DEPTNO JOB SUM(SAL)
---------- --------- ----------
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
30 9400
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600

18 rows selected.

select deptno,job,sum(sal),grouping(deptno),grouping(job) from emp group by rollup(deptno,job)
select deptno,job,sum(sal) from emp group by cube(deptno,job)
select deptno,job,sum(sal),grouping(deptno),grouping(job) from emp group by grouping sets(deptno,job);
SQL> select deptno,job,sum(sal),grouping(deptno),grouping(job) from emp group by grouping sets(deptno,job);

DEPTNO JOB SUM(SAL) GROUPING(DEPTNO) GROUPING(JOB)
---------- --------- ---------- ---------------- -------------
CLERK 4150 1 0
SALESMAN 5600 1 0
PRESIDENT 5000 1 0
MANAGER 8275 1 0
ANALYST 6000 1 0
30 9400 0 1
20 10875 0 1
10 8750 0 1

8 rows selected.
--分析函数
row_number()
over()
SQL> c/emp/
1* select ename,sal , row_number() over(partition by deptno order by sal desc) from emp
SQL> /

ENAME SAL ROW_NUMBER()OVER(PARTITIONBYDEPTNOORDERBYSALDESC)
---------- ---------- -------------------------------------------------
KING 5000 1
CLARK 2450 2
MILLER 1300 3
SCOTT 3000 1
FORD 3000 2
JONES 2975 3
ADAMS 1100 4
SMITH 800 5
BLAKE 2850 1
ALLEN 1600 2
TURNER 1500 3

ENAME SAL ROW_NUMBER()OVER(PARTITIONBYDEPTNOORDERBYSALDESC)
---------- ---------- -------------------------------------------------
MARTIN 1250 4
WARD 1250 5
JAMES 950 6

14 rows selected.

select * from (select ename,deptno,sal , row_number() over(partition by deptno order by sal desc) rw from emp) a where a.rw<4;
每个部门的薪水的前三名？
SQL> select * from (select ename,deptno,sal , row_number() over(partition by deptno order by sal desc) rw from emp) a where a.rw<4;

ENAME DEPTNO SAL RW
---------- ---------- ---------- ----------
KING 10 5000 1
CLARK 10 2450 2
MILLER 10 1300 3
SCOTT 20 3000 1
FORD 20 3000 2
JONES 20 2975 3
BLAKE 30 2850 1
ALLEN 30 1600 2
TURNER 30 1500 3

9 rows selected.[@more@]

ITPub博客

高级分组、外部表

最新文章