1  select deptno,sum(sal)
  2  from emp
  3* group by rollup(deptno)
SQL> /

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

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

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

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

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

grouping ---1表示肯定,0表示没有

SQL> select deptno,sum(sal),grouping(deptno)
  2  from emp
  3  group by detpno;
group by detpno
         *
ERROR at line 3:
ORA-00904: "DETPNO": invalid identifier


SQL> c/detpno/deptno/
  3* group by deptno
SQL> /

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

SQL> c/by deptno/by rollup(deptno/
  3* group by rollup(deptno
SQL> a )
  3* group by rollup(deptno)
SQL> r
  1  select deptno,sum(sal),grouping(deptno)
  2  from emp
  3* group by rollup(deptno)

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


SQL> select deptno,job,sum(sal) ----就是利用grouping sets加上分组列把对应的多个结果并起来,形成总结果
  2  from emp
  3  group by grouping sets(deptno,job);

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

8 rows selected.

SQL> select deptno,job,sum(sal)
  2  from emp
  3  group by deptno,rollup(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

12 rows selected.

高级查询
with

SQL> select addr1,to_char(date1,'yyyy-mm-dd') from iptest;

ADDR1            TO_CHAR(DA
---------------- ----------
192.168.1.0      1981-08-28
192.168.1.8      1981-08-29
192.168.1.28     1982-08-29

1  select substr(to_char(date1,'yyyy-mm-dd'),1,4),count(addr1)
  2  from iptest
  3* group by substr(to_char(date1,'yyyy-mm-dd'),1,4)
SQL> /

SUBS COUNT(ADDR1)
---- ------------
1982            1
1981            2

select e.ename,e.sal,c.avgsal ---得出每个部门工资大于每个部门平均工资的员工信息及部门平均工资
   from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) c  ---from中子查询要给个别名,avg也要取别名
  where
e.deptno=c.deptno and e.sal>c.avgsal



ENAME             SAL     AVGSAL
---------- ---------- ----------
ALLEN            1600 1566.66667
JONES            2975       2175
BLAKE            2850 1566.66667
SCOTT            3000       2175
KING             5000 2916.66667
FORD             3000       2175

6 rows selected.


order by和case也可以用子查询(而且可以是join子查询)

内查询要依赖于外查询,外查询变化一次,内查询也要执行一次,而且还要把内查询的结果返加给外查询,这叫关联子查询
  1  select f.ename,f.sal
  2  from emp f
  3* where sal>(select avg(sal) from emp c where c.deptno=f.deptno) --这就是关联子查询,因为子查询和外查询有关联,通过where
SQL> /

ENAME             SAL
---------- ----------
ALLEN            1600
JONES            2975
BLAKE            2850
SCOTT            3000
KING             5000
FORD             3000

6 rows selected.

高级查询
with

SQL> select addr1,to_char(date1,'yyyy-mm-dd') from iptest;

ADDR1            TO_CHAR(DA
---------------- ----------
192.168.1.0      1981-08-28
192.168.1.8      1981-08-29
192.168.1.28     1982-08-29

1  select substr(to_char(date1,'yyyy-mm-dd'),1,4),count(addr1)
  2  from iptest
  3* group by substr(to_char(date1,'yyyy-mm-dd'),1,4)
SQL> /

SUBS COUNT(ADDR1)
---- ------------
1982            1
1981            2

select level,col ---level为层次
from tab1  
where condition
start with conditions ---指定起始条件
connect by prior condions  ---主外键(父子键),top down (父键和子键),down top(子键和父键)
也有存在某个分去去掉了,加上<>就可以了在connect by prior限制或where都可以

ddl and dml extension
first insert
all insert
uncontional insert
pivoting insert  --reverse position

access external table --stored in os file

insert all  --把表三的结果插入到表一和二中
into table1
into table2
select table3

SQL> desc dept;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
DEPTNO                                    NOT NULL NUMBER(2)
DNAME                                              VARCHAR2(14)
LOC                                                VARCHAR2(13)

SQL> create table dept1 as select * from dept where 1=2;

Table created.

SQL>  create table dept2 as select * from dept where 1=2;

Table created.

SQL> insert all
  2  into dept2
  3  into dept1
  4  select * from dept;

8 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from dept;

  COUNT(*)
----------
         4

insert all --有条件插入when then,可以理解为对不同目标数据源,加上匹配源数据的where条件
when sal>1000 then
into sal values()
when mgr>200 then
  into mgr_history values()
select * from table1 where condtion;

insert first  ---类似于case语句结构
when lastname like 'B%' then
into emp values()
when lastname like 'W%' then
into emp_addr values()
else
into emp_name values()
select * from employees where condtions

外部表:一不可以dml,二不可以建索引
create table externaltab
(empno number,empname char(20),birdate date)
organization external
(type oracle_loader
default directory emp_dir   --对应于create directory emp_dir as 'path',store os file
access parameters
(records delimited by newline  --指定行分隔符,我如此理解
badfile 'bad_emp'
logfile 'log_emp'
fields terminated by ','  ----指定列分隔符
(empno char,
empname char,
birdate char date_format date mask "dd-mon-yyyy"))
location ('emp.txt')  --就是上面定义directory 的存储的实际os file
parallel 5
reject limit 200;


USER is "SCOTT"
SQL> create table new_emp(empid number primary key  --- 在建表时把索引也一样建了,在建表中内含了一个独立的建索引语句
                          using index(create index idx_emp on new_emp(empid)),firstname char(4));

Table created.

SQL> select index_name from user_indexes where table_name='NEW_EMP';

INDEX_NAME
------------------------------
IDX_EMP

031
datafile,redofile.controlfile是必须文件
user process(user开启 sqlplus-)>server process(pga)(oracle会在服务side建立相应的服务器进程)
user process不能直接操作oracle data,必须要通过server process来进行操作oracle data

The PARALLEL clause enables parallel query on the data sources.---use for external table
The REJECT LIMIT clause specifies that there is no limit on the number of errors that
can occur during a query of the external data. For parallel access, this limit applies to
each parallel execution server independently.

alter database open;--真正验证数据文件有效性

NAME  DESCRIPTION                                                           ERROR
----- ---------------------------------------------------------------- ----------
PMON  process cleanup                                                  ##########
DIAG  diagnosibility process                                           ##########
FMON  File Mapping Monitor Process                                     ##########
PSP0  process spawner 0                                                ##########
LMON  global enqueue service monitor                                   ##########
LMD0  global enqueue service daemon 0                                  ##########
LMS0  global cache service process 0                                   ##########
LMS1  global cache service process 1                                   ##########
LMS2  global cache service process 2                                   ##########
LMS3  global cache service process 3                                   ##########
LMS4  global cache service process 4                                   ##########

NAME  DESCRIPTION                                                           ERROR
----- ---------------------------------------------------------------- ----------
LMS5  global cache service process 5                                   ##########
LMS6  global cache service process 6                                   ##########
LMS7  global cache service process 7                                   ##########
LMS8  global cache service process 8                                   ##########
LMS9  global cache service process 9                                   ##########
LMSa  global cache service process 10                                  ##########
LMSb  global cache service process 11                                  ##########
LMSc  global cache service process 12                                  ##########
LMSd  global cache service process 13                                  ##########
LMSe  global cache service process 14                                  ##########
LMSf  global cache service process 15                                  ##########

NAME  DESCRIPTION                                                           ERROR
----- ---------------------------------------------------------------- ----------
LMSg  global cache service process 16                                  ##########
LMSh  global cache service process 17                                  ##########
LMSi  global cache service process 18                                  ##########
LMSj  global cache service process 19                                  ##########
LMSk  global cache service process 20                                  ##########
LMSl  global cache service process 21                                  ##########
LMSm  global cache service process 22                                  ##########
LMSn  global cache service process 23                                  ##########
LMSo  global cache service process 24                                  ##########
LMSp  global cache service process 25                                  ##########
LMSq  global cache service process 26                                  ##########

NAME  DESCRIPTION                                                           ERROR
----- ---------------------------------------------------------------- ----------
LMSr  global cache service process 27                                  ##########
LMSs  global cache service process 28                                  ##########
LMSt  global cache service process 29                                  ##########
LMSu  global cache service process 30                                  ##########
LMSv  global cache service process 31                                  ##########
LMSw  global cache service process 32                                  ##########
LMSx  global cache service process 33                                  ##########
LMSy  global cache service process 34                                  ##########
LMSz  global cache service process 35                                  ##########
MMAN  Memory Manager                                                   ##########
DBW0  db writer process 0                                              ##########

NAME  DESCRIPTION                                                           ERROR
----- ---------------------------------------------------------------- ----------
DBW1  db writer process 1                                              ##########
DBW2  db writer process 2                                              ##########
DBW3  db writer process 3                                              ##########
DBW4  db writer process 4                                              ##########
DBW5  db writer process 5                                              ##########
DBW6  db writer process 6                                              ##########
DBW7  db writer process 7                                              ##########
DBW8  db writer process 8                                              ##########
DBW9  db writer process 9                                              ##########
DBWa  db writer process 10 (a)                                         ##########
DBWb  db writer process 11 (b)                                         ##########

NAME  DESCRIPTION                                                           ERROR
----- ---------------------------------------------------------------- ----------
DBWc  db writer process 12 (c)                                         ##########
DBWd  db writer process 13 (d)                                         ##########
DBWe  db writer process 14 (e)                                         ##########
DBWf  db writer process 15 (f)                                         ##########
DBWg  db writer process 16 (g)                                         ##########
DBWh  db writer process 17 (h)                                         ##########
DBWi  db writer process 18 (i)                                         ##########
DBWj  db writer process 19 (j)                                         ##########
ARC0  Archival Process 0                                               ##########
ARC1  Archival Process 1                                               ##########
ARC2  Archival Process 2                                               ##########

NAME  DESCRIPTION                                                           ERROR
----- ---------------------------------------------------------------- ----------
ARC3  Archival Process 3                                               ##########
ARC4  Archival Process 4                                               ##########
ARC5  Archival Process 5                                               ##########
ARC6  Archival Process 6                                               ##########
ARC7  Archival Process 7                                               ##########
ARC8  Archival Process 8                                               ##########
ARC9  Archival Process 9                                               ##########
ARCa  Archival Process 10                                              ##########
ARCb  Archival Process 11                                              ##########
ARCc  Archival Process 12                                              ##########
ARCd  Archival Process 13                                              ##########

NAME  DESCRIPTION                                                           ERROR
----- ---------------------------------------------------------------- ----------
ARCe  Archival Process 14                                              ##########
ARCf  Archival Process 15                                              ##########
ARCg  Archival Process 16                                              ##########
ARCh  Archival Process 17                                              ##########
ARCi  Archival Process 18                                              ##########
ARCj  Archival Process 19                                              ##########
ARCk  Archival Process 20                                              ##########
ARCl  Archival Process 21                                              ##########
ARCm  Archival Process 22                                              ##########
ARCn  Archival Process 23                                              ##########
ARCo  Archival Process 24                                              ##########

NAME  DESCRIPTION                                                           ERROR
----- ---------------------------------------------------------------- ----------
ARCp  Archival Process 25                                              ##########
ARCq  Archival Process 26                                              ##########
ARCr  Archival Process 27                                              ##########
ARCs  Archival Process 28                                              ##########
ARCt  Archival Process 29                                              ##########
LNS0  Network Server 0                                                 ##########
LNS1  Network Server 1                                                 ##########
LNS2  Network Server 2                                                 ##########
LNS3  Network Server 3                                                 ##########
LNS4  Network Server 4                                                 ##########
LNS5  Network Server 5                                                 ##########

NAME  DESCRIPTION                                                           ERROR
----- ---------------------------------------------------------------- ----------
LNS6  Network Server 6                                                 ##########
LNS7  Network Server 7                                                 ##########
LNS8  Network Server 8                                                 ##########
LNS9  Network Server 9                                                 ##########
LNSa  Network Server 10                                                ##########
LNSb  Network Server 11                                                ##########
LNSc  Network Server 12                                                ##########
LNSd  Network Server 13                                                ##########
LNSe  Network Server 14                                                ##########
LNSf  Network Server 15                                                ##########
LNSg  Network Server 16                                                ##########

NAME  DESCRIPTION                                                           ERROR
----- ---------------------------------------------------------------- ----------
LNSh  Network Server 17                                                ##########
LNSi  Network Server 18                                                ##########
LNSj  Network Server 19                                                ##########
MRP0  Managed Standby Recovery                                         ##########
LGWR  Redo etc.                                                        ##########
LCK0  Lock Process 0                                                   ##########
CKPT  checkpoint                                                       ##########
LSP0  Logical Standby                                                  ##########
LSP1  Dictionary build process for Logical Standby                     ##########
LSP2  Set Guard Standby Information for Logical Standby                ##########
CTWR  Change Tracking Writer                                           ##########

NAME  DESCRIPTION                                                           ERROR
----- ---------------------------------------------------------------- ----------
RVWR  Recovery Writer                                                  ##########
SMON  System Monitor Process                                           ##########
RECO  distributed recovery                                             ##########
CJQ0  Job Queue Coordinator                                            ##########
EMN0  Event Monitor Process 0                                          ##########
QMNC  AQ Coordinator                                                   ##########
DMON  DG Broker Monitor Process                                        ##########
RSM0  Data Guard Broker Resource Guard Process 0                       ##########
RSM1  Data Guard Broker Resource Guard Process 1                       ##########
NSV0  Data Guard Broker NetSlave Process 0                             ##########
NSV1  Data Guard Broker NetSlave Process 1                             ##########

NAME  DESCRIPTION                                                           ERROR
----- ---------------------------------------------------------------- ----------
NSV2  Data Guard Broker NetSlave Process 2                             ##########
NSV3  Data Guard Broker NetSlave Process 3                             ##########
NSV4  Data Guard Broker NetSlave Process 4                             ##########
NSV5  Data Guard Broker NetSlave Process 5                             ##########
NSV6  Data Guard Broker NetSlave Process 6                             ##########
NSV7  Data Guard Broker NetSlave Process 7                             ##########
NSV8  Data Guard Broker NetSlave Process 8                             ##########
NSV9  Data Guard Broker NetSlave Process 9                             ##########
INSV  Data Guard Broker INstance SlaVe Process                         ##########
RBAL  ASM Rebalance master                                             ##########
ARB0  ASM Rebalance 0                                                  ##########

NAME  DESCRIPTION                                                           ERROR
----- ---------------------------------------------------------------- ----------
ARB1  ASM Rebalance 1                                                  ##########
ARB2  ASM Rebalance 2                                                  ##########
ARB3  ASM Rebalance 3                                                  ##########
ARB4  ASM Rebalance 4                                                  ##########
ARB5  ASM Rebalance 5                                                  ##########
ARB6  ASM Rebalance 6                                                  ##########
ARB7  ASM Rebalance 7                                                  ##########
ARB8  ASM Rebalance 8                                                  ##########
ARB9  ASM Rebalance 9                                                  ##########
ARBA  ASM Rebalance 10                                                 ##########
ASMB  ASM Background                                                   ##########

NAME  DESCRIPTION                                                           ERROR
----- ---------------------------------------------------------------- ----------
GMON  diskgroup monitor                                                ##########
MMON  Manageability Monitor Process                                    ##########
MMNL  Manageability Monitor Process 2                                  ##########

db_cache_advice 和db_cache_size有关系
alter session set nls_language=american;--ora 错误就不会出现?
在小于sga_max_size时,可以动态修改sga大小.
shared pool: library cache ---,ratio 99% 以上,(lru)current used sql and plsql execution plan  ,shared sql area and shared plsql area,主要提高代码的共享
             data dictionary cache (row cache) ---user privilege,data object,data object structure info



SQL> show parameter db;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size                    big integer 0
db_2k_cache_size                     big integer 0
db_32k_cache_size                    big integer 0
db_4k_cache_size                     big integer 0
db_8k_cache_size                     big integer 0
db_block_buffers                     integer     0 ---oracle 9i前要配置的,9i后不要配置了,
db_block_checking                    string      FALSE
db_block_checksum                    string      TRUE
db_block_size                        integer     8192
db_cache_advice                      string      ON
db_cache_size                        big integer 0

data buffer cache --是把数据文件镜像放在内存中之区域
oracle使用内存是以块大小为大小,就是你读到一条记录,也就读取一个块
redo 用于恢复,log buffer

large pool用于shared server,store uga

user process   -以一个程序或终端生成,不可以直接和oracle交互
server process ---user create session produce server process,它可以是专用或共享
background process  ---oracle interval process,used for administer oracle itself