ITPUB论坛-中国专业的IT技术社区

 找回密码
 注册
查看: 47689|回复: 150

[原创] 总结笔记之【开发篇sql】

[复制链接]
论坛徽章:
32
奥运会纪念徽章:摔跤
日期:2012-08-23 11:03:05青年奥林匹克运动会-击剑
日期:2014-09-19 10:58:152014年世界杯参赛球队:巴西
日期:2014-07-07 12:19:232014年世界杯参赛球队: 瑞士
日期:2014-05-19 12:18:36马上有钱
日期:2014-04-08 12:12:232014年新春福章
日期:2014-04-04 14:20:47马上有钱
日期:2014-02-18 16:43:092014年新春福章
日期:2014-02-18 16:43:09红旗
日期:2014-02-14 15:15:55优秀写手
日期:2013-12-18 09:29:16
发表于 2010-5-29 15:32 | 显示全部楼层 |阅读模式
一些自己之前的总结,希望大家指出问题,错误之处,遗漏之处,可深入或者扩展的地方,希望大家支持!!

把文档传上来方便下载,太长了估计没几个人仔细看。。。不过记得,下载的兄弟要顶帖啊,呵呵! 2.1 sql.doc (316.5 KB, 下载次数: 1581)
论坛徽章:
32
奥运会纪念徽章:摔跤
日期:2012-08-23 11:03:05青年奥林匹克运动会-击剑
日期:2014-09-19 10:58:152014年世界杯参赛球队:巴西
日期:2014-07-07 12:19:232014年世界杯参赛球队: 瑞士
日期:2014-05-19 12:18:36马上有钱
日期:2014-04-08 12:12:232014年新春福章
日期:2014-04-04 14:20:47马上有钱
日期:2014-02-18 16:43:092014年新春福章
日期:2014-02-18 16:43:09红旗
日期:2014-02-14 15:15:55优秀写手
日期:2013-12-18 09:29:16
 楼主| 发表于 2010-5-29 15:35 | 显示全部楼层
2,undo和redo
Undo

Oracle将所有的数据更改记录在undo,这些记录信息可以使oracle使用rollback来撤销更改操作。Undo的机制主要有两个目的:一是用来允许读不能阻塞写入,写入不能阻塞读,二是可以使事务能够rollback(回滚)。
事务能够产生undo(回滚),且产生的undo记录数量可以在动态性能视图V$transaction中得到。用一个例子来看看undo的产生:
SQL> set autocommit off;
SQL> insert into t_test_undo values (2,'asd');

1 row created.

SQL>
SQL> select used_urec from v$session s,v$transaction t where
  2  s.audsid = sys_context('userenv','sessionid') and
  3  s.taddr = t.addr;

USED_UREC
----------
         1
上面的操作产生了1条undo记录,再看看一次插入多条记录的情况:
SQL> insert into t_test_undo
  2  select rownum + 50000, object_name from all_objects where rownum < 51;


50 rows created.

SQL> SQL> select used_urec from v$session s, v$transaction t
  2   where s.audsid=sys_context('userenv', 'sessionid') and
  3         s.taddr = t.addr;

USED_UREC
----------
         2
上面的语句也只产生了一条undo。
然后看看update的情形:
SQL> update t_test_undo a set a.b = 'asdsd' where a.a = 1;

1 row updated.

SQL> select used_urec from v$session s, v$transaction t
  2   where s.audsid=sys_context('userenv', 'sessionid') and
  3         s.taddr = t.addr;

USED_UREC
----------
         3
这个update语句也产生了1个undo记录,再看看delete的情形:
SQL> delete from t_test_undo a where a.a <10;

2 rows deleted.

SQL> select used_urec from v$session s, v$transaction t
  2   where s.audsid=sys_context('userenv', 'sessionid') and
  3         s.taddr = t.addr;

USED_UREC
----------
         5
可以看出,delete的每一条记录都产生了一个undo记录。
由此可以大概对比一下不同的语句产生的undo大小。
SQL>
SQL>
SQL> insert into t_test_undo values (10, 'asd');

1 row created.

SQL> select a.XIDUSN, a.UBAREC, a.UBABLK, a.USED_UBLK, a.USED_UREC
  2    from v$transaction a;

    XIDUSN     UBAREC     UBABLK  USED_UBLK  USED_UREC
---------- ---------- ---------- ---------- ----------
         9         17        336          1          1

SQL> commit;

Commit complete.

SQL> select a.XIDUSN, a.UBAREC, a.UBABLK, a.USED_UBLK, a.USED_UREC
  2    from v$transaction a;

no rows selected

SQL> update t_test_undo a set a.b = 'qwe' where a.a = 10;

1 row updated.

SQL> select a.XIDUSN, a.UBAREC, a.UBABLK, a.USED_UBLK, a.USED_UREC
  2    from v$transaction a;

    XIDUSN     UBAREC     UBABLK  USED_UBLK  USED_UREC
---------- ---------- ---------- ---------- ----------
         7          6       2701          1          1

SQL> commit;

Commit complete.

SQL> delete from t_test_undo a where a.a = 10;

1 row deleted.

SQL> select a.XIDUSN, a.UBAREC, a.UBABLK, a.USED_UBLK, a.USED_UREC
  2    from v$transaction a;

    XIDUSN     UBAREC     UBABLK  USED_UBLK  USED_UREC
---------- ---------- ---------- ---------- ----------
         3         25       1057          1          1

SQL> commit;

Commit complete.

SQL> insert into t_test_undo values (10, 'asd');

1 row created.

SQL> update t_test_undo a set a.b = 'qwe' where a.a = 10;

1 row updated.

SQL> delete from t_test_undo a where a.a = 10;

1 row deleted.

SQL> select a.XIDUSN, a.UBAREC, a.UBABLK, a.USED_UBLK, a.USED_UREC
  2    from v$transaction a;

    XIDUSN     UBAREC     UBABLK  USED_UBLK  USED_UREC
---------- ---------- ---------- ---------- ----------
         2         38      10021          1          3

SQL>

一般来说,insert产生了最少的undo,因为insert行的反操作是delete行,只记录插入记录的rowid,delete因为需要把整行的前映像记录到undo,所以产生最多的undo,update需要记录行中被更新的字段部分的前映像,因此一般来说介于insert和delete之间。

回滚段可以说是用来保持数据变化前映像而提供一致性读和保证事务完整性的一段存储区域。当一个事务开始的时候,首先把变化前的数据和变化后的数据先写入日志缓冲区,然后吧变化前的数据写入回滚段,最后才在数据缓冲区中修改。

Undo segment头部包含记录了当前事务使用的undo segment信息的一张表。一系列事务只使用一个undo segment存储所有数据。许多并发的事务可以使用同一个undo segment。

再来看看与undo相关的ora-01555:
SQL> host oerr ora 1555
01555, 00000, "snapshot too old: rollback segment number %s with name \"%s\" too small"
// *Cause: rollback records needed by a reader for consistent read are
//         overwritten by other writers
// *Action: If in Automatic Undo Management mode, increase undo_retention
//          setting. Otherwise, use larger rollback segments

在相对稳定的数据库中,出现这个错误大多数时候是代码需要调整,或者由于业务需要在某一时段进行大批量的业务处理,而对一些较大的查询产生了影响。涉及到的undo的优化或者sql的优化在性能调整一章中详述。


Oracle的segment信息可以在dba_segments或者sys_dba_segs中查看,从下面的输出信息中可以看到几个比较重要的段对象,如table,index,rollback。
SQL> select distinct segment_type from sys_dba_segs;

SEGMENT_TYPE
------------------
LOBINDEX
INDEX PARTITION
TABLE PARTITION
NESTED TABLE
ROLLBACK
LOB PARTITION
LOBSEGMENT
INDEX
TABLE
CLUSTER
TYPE2 UNDO

11 rows selected.

Redo

与undo不同,undo是产生使操作撤销的信息,而redo是产生使操作重做的信息。Oracle的redo有两种:online redo log(联机),archivelog(归档)。
Lgwr循环的写入联机日志,当前日志日满后,lgwr写下一个日志。Lgwr总是在一下条件之一满足之时将缓冲重做日志写入磁盘:
1,每三秒;2,每当满1/3或1MB;3碰到任何事务commit;
在事务commit或者rollback之前往往已经完成了一下工作:
已经在sga中产生了回滚段记录,用于撤销
已经在sga中产生了修改数据块
已经在sga中产生了回滚和修改的重做,是的以上2个操作可以重做
根据lgwr的刷新特点,一部分数据可能已经刷新到了磁盘
已经获得了所有的锁定
当commit时,要做的工作如下:
        为事务产生scn,lgwr写磁盘,释放锁定,访问修改事务块,并清除之。
当rollback时,要做的工作如下:
        使用回滚段的数据来撤销修改,释放锁定。
同样的,dml对redo产生的影响也是能够测定的。
下列语句依次执行:
select a.SID, a.VALUE
  from v$mystat a, v$statname b
where a.STATISTIC# = b.STATISTIC#
   and b.NAME like 'redo_size';
insert into t_test_undo values (12, '123');
commit;
select a.SID, a.VALUE
  from v$mystat a, v$statname b
where a.STATISTIC# = b.STATISTIC#
   and b.NAME like 'redo_size';
update t_test_undo a set a.b = '321' where a = 12;
commit;
select a.SID, a.VALUE
  from v$mystat a, v$statname b
where a.STATISTIC# = b.STATISTIC#
   and b.NAME like 'redo_size';
delete t_test_undo where a.a = 12;
commit;
select a.SID, a.VALUE
  from v$mystat a, v$statname b
where a.STATISTIC# = b.STATISTIC#
   and b.NAME like 'redo_size';

得到的redo size数值如下表:
操作        Sid        Redo size
初始        144        0
Insert 一行        144        548
Update 一行        144        1140
Delete 一行        144        1732

同样可以测试一次插入多行,更新多行和删除多行对redo的产生量,并且可以测试逐条提交和一次提交对redo的产生量。如果不结合起来看undo和redo的影响而来调整应用,很多时候结果往往是不确定的。值得注意的是触发器的不同类型对redo的产生也有影响,比如对update使用after触发器,将不会影响重做。开发过程中,可以大致了解一下重做的数量:估计事务的大小,需要修改的数据量,提交的频率,dml类型占比(考虑到update产生大约2倍的重做,insert和delete大约为1倍)。而以上的表中似乎看不到不同dml对redo的区别,这是因为b的字段类型为varchar2(20),本身的修改量很小,如果吧字段修改为char(2000),则数字规律很明显,有关字符类型的细节在数据库设计一章节中详述。

另外redo是备份和恢复的一个支撑原理。有关联机日志和归档日志的管理和使用在备份恢复一章中详解。

使用道具 举报

回复
论坛徽章:
32
奥运会纪念徽章:摔跤
日期:2012-08-23 11:03:05青年奥林匹克运动会-击剑
日期:2014-09-19 10:58:152014年世界杯参赛球队:巴西
日期:2014-07-07 12:19:232014年世界杯参赛球队: 瑞士
日期:2014-05-19 12:18:36马上有钱
日期:2014-04-08 12:12:232014年新春福章
日期:2014-04-04 14:20:47马上有钱
日期:2014-02-18 16:43:092014年新春福章
日期:2014-02-18 16:43:09红旗
日期:2014-02-14 15:15:55优秀写手
日期:2013-12-18 09:29:16
 楼主| 发表于 2010-5-29 15:35 | 显示全部楼层
3,DDL和DML

Oracle的DDL主要有create,alter,drop,truncate等,DML主要有insert,update,select,delete,merge等。Oracle的DDL一个特点是自动提交,这与其他的一些数据库DDL不同。
对于oracle,ddl执行过程可以用一段伪码示意如下:

begin
   COMMIT;
   do the ddl;
   COMMIT;
exception
   when others then
        ROLLBACK;
        RAISE;
end;

也即在执行ddl之前有一个提交动作,执行完成之后也有一个提交动作,而执行失败时,第一个提交动作已经完成,所以会提交当前事务。用一个例子来看:
SQL> create table t_test as select * from dual;

Table created

SQL> update t_test a set a.dummy = 'z';

1 row updated

SQL> create table t_test as select * from dual;

create table t_test as select * from dual

ORA-00955: 名称已由现有对象使用

SQL> rollback;

Rollback complete

SQL> select * from t_test;

DUMMY
-----
z

对于dml来说也可以设置为AUTOCOMMIT,比如sqlplus中的set autocommit:
SQL> set autocommit on;
SQL> update scott.t_test set dummy = 'v';

1 row updated
Commit complete

或者java的jdbc连接中的SetAutoCommit(boolean isCommit):
使用java.sql.Connection或者oracle.jdbc.OracleConnection建立的一个新的连接默认为auto-commit模式,代码如下:
// Connect to the database
// You can put a database hostname after the @ sign in the connection URL.
   OracleDataSource ods = new OracleDataSource();
   ods.setURL("jdbc:oracle:oci:@");//或者也可以使用瘦客户端连接(thin)
   ods.setUser("scott");
   ods.setPassword("tiger");
   Connection conn = ods.getConnection();
   
// It's faster when auto commit is off
conn.setAutoCommit (false); //一般来讲这里关闭自动提交并不是为了更快,而是为了更好的控制提交的时机。

// Create a Statement
Statement stmt = conn.createStatement ();
...

如果对DDL进行trace,会发现ddl其实就是一系列的dml,这些dml操作了数据字典表。例子如下:

SQL> alter session set events '10046 trace name context forever , level 12';

Session altered.

SQL> create table xxxx (a number);

Table created.

使用tkprof格式化trace文件,可以看到:
Trace file: test1_ora_7449.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

create table xxxx (a number)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.02          0          0          0           0
Execute      1      0.03       0.03          0          1          8           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.04       0.06          0          1          8           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     1       33.73         33.73
********************************************************************************

select dummy
from
dual where  ora_dict_obj_type = 'SYNONYM' AND ora_dict_obj_owner = 'PUBLIC'
.....(以下省略)


看一个具体的DDL create table的完整语句:
SQL> select dbms_metadata.get_ddl('TABLE','T_TEST_UNDO','SYS') from dual;

  CREATE TABLE "SYS"."T_TEST_UNDO"
   (    "A" NUMBER,
        "B" VARCHAR2(20)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSTEM"
注意到以上代码中的一些参数,这些被称作common sql ddl clauses,分别包括了:
Allocate extent clause       
用来分配对象的extent,可以指定size子句,指定数据文件(datafile),指定确切的instance

Constraint

用来定义完整性约束,比如not null ,unique,primary key,foreign key,check等,通常可以通过inline或者out-of-line 两种方式来定义约束(not null除外,只能使用inline),比如:
create table t_test_id(
id     number unique,--inline的unique约束
name   varchar2(20) not null
);

Create table t_test_undo_1(
Id  number not null,--not null 只能inline
S_id number ,
Name  varchar2(20),
  Primary key (s_id),--
  constraint fk__t_test_ud1 foreign key (Id)
  references t_test_id (ID)
);
或者:
Create table t_test_undo_1(
Id  number not null,--
S_id number ,
Name  varchar2(20)
);
alter table t_test_undo_1 add constraint pk_sid Primary key (s_id);
alter table t_test_undo_1 add constraint fk__t_test_ud1 foreign key (Id)
  references t_test_id (ID);

Deallocate unused clause
可以显示的释放在一个数据库段对象结束时未使用的空间,使得空间可以被其他段对象使用。要注意的是不能同时在一个语句中设置deallocate_unused_clause和allocate_extent_clause。

Logging clause
在创建数据库对象时,这个参数设置为logging或者nologging,用来控制是否记录redo log,要注意的是并不能完全nologging,并且在不同的数据库模式下,影响也不同(archived和unarchived)。DDL中允许nologging模式的语句主要有下列:
        Create table ... as select
        alter table ... move
        alter table ... xxx        partition
        Create index
        Alter index ... xxx

Physical attributes clause
可以用来设置table,cluster,index或者materialized的pctfree,pctused,和initans参数。这些参数的原理和作用详见oracle管理基础一章。

Storage clause
Oracle应该如何储存数据库对象的设置。主要的参数如下:
INITIAL 对象的第一个extent的大小
NEXT 下一扩展的extent大小
MINEXTENTS 最小的extents数量
MAXEXTENTS 最大的extents数量
PCTINCREASE 指定extent较前一个的增长的百分比。
需要注意的是,pctincreate值大于0会造成extent不一致,如果next较多的话,可能会造成空间利用率低下,虽然smon会自动合并表空间中的碎片,但是这些空间也不能很好的利用。(oracle建议设置为0,可以减少碎片和避免产生非常大的临时段,而回滚段不能设置pctincrease,默认是0)
FREELISTS 一个空闲链表组内的空闲链表数
FREELIST GROUPS 空闲链表组的数量
以上两个参数只能在create table,cluster,index时使用
BUFFER_POOL 可以用来为对象设置一个默认的缓冲池,同样的,回滚段不能设置。
有关存储参数的详述见oracle管理基础一章。

再看看DML insert的大致语法 :
Insert {direct/parallel/append}
{all/first} into
{schema}[table/view/materialized view]{@dblink}
{partition/subpartition}
{when ..then}
[values/select]
{returning into ..}
{log errors into }
{reject limit {integer/unlmited}}
涉及到的clause有插入路径,方式,schema,是否使用dblink,指定分区或者子分区,插入条件,返回值,错误日志记录,拒绝的限制等等,具体可以参见oracle官方文档《sql reference》。在oracle数据库特性一章中单独对分区,直接路径插入,物化视图等进行了详述。
由此也可以看到oracle的dml扩展相当强,可以满足很多数据操纵的需求,关注这些oracle提供的特性会使得问题的解决有更多的选择。

使用道具 举报

回复
论坛徽章:
32
奥运会纪念徽章:摔跤
日期:2012-08-23 11:03:05青年奥林匹克运动会-击剑
日期:2014-09-19 10:58:152014年世界杯参赛球队:巴西
日期:2014-07-07 12:19:232014年世界杯参赛球队: 瑞士
日期:2014-05-19 12:18:36马上有钱
日期:2014-04-08 12:12:232014年新春福章
日期:2014-04-04 14:20:47马上有钱
日期:2014-02-18 16:43:092014年新春福章
日期:2014-02-18 16:43:09红旗
日期:2014-02-14 15:15:55优秀写手
日期:2013-12-18 09:29:16
 楼主| 发表于 2010-5-29 15:40 | 显示全部楼层
二,条件和表达式
1,数据类型

Varchar2,nvarchar2,char的简单比较
Varchar2与nvarchar2都是可变长度的字符类型,不同的是nvarchar2与国家字符集相关,而char是固定长度的类型。看看他们之间的区别:
create table t_test_chr (
type_char    char(4),
type_varchar2           varchar2(20),
type_nvarchar2          nvarchar2(20)
);

insert into t_test_chr values ('aaaa','aaaa','aaaa');
insert into t_test_chr values ('我','我考','我考');

SQL> select length(type_char),length(type_varchar2),length(type_nvarchar2) from t_test_chr;

LENGTH(TYPE_CHAR) LENGTH(TYPE_VARCHAR2) LENGTH(TYPE_NVARCHAR2)
----------------- --------------------- ----------------------
                4                     4                      4
                2                     2                      2
可以看到对于字符来说三者的长度相同,但对于汉字来说就有区别了,而且不同的字符集也存在差异,一个很常见的问题是汉字乱码问题,这是由于数据库的字符集设置与客户端的差异造成的,一般对于开发人员来说这个不用考虑。另外要注意的,char可以不设置长度,默认为1,而varchar2需要设置长度。例如:
SQL> create table t_testchar (v_char char,v_var varchar2(10));

Table created.

有关字符集的问题详见附录一章。有关在设计时数据类型的选择和特殊字符处理方法在数据库设计一章中详述,有关xmltype在oracle xml一章中详述。有关oracle的基本数据类型,可以参见oracle官方文档的《sql reference》的datatypes一节。

2,数据类型的比较规则

主要看几个常见且比较重要的例子。
Char字符的比较,是比较ascii值的大小:
SQL> select 1 from dual where 'ab' >'ac';

         1
----------

SQL> select 1 from dual where 'ab' <'ac';

         1
----------
         1
SQL> select 1 from dual where 'a' <'b';

         1
----------
         1
字符与数值的比较,有一个隐身转换的过程:
SQL> select 1 from dual where '32' = 32;

         1
----------
         1
但是下面这样的情况并不会将32隐身转换为’32’:
SQL> select 1 from dual where 'a' = 32;

select 1 from dual where 'a' = 32

ORA-01722: invalid number

之所以这里特意提到隐式转换,是因为,往往在sql条件中,由于隐式转换可能会禁用相关的索引,这个也是sql性能的一大隐患,可以简单看一个例子,注意不能使用上面创建的表,drop后新建,插入下列数据:
Insert into t_test_chr values ('1111', '1111', '1111');

SQL> create index t_test_chr_id2 on t_test_chr(type_varchar2);

Index created

SQL> select * from t_test_chr a where a.type_varchar2='aaaa';
Execution Plan
----------------------------------------------------------
Plan hash value: 3150866331

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |    40 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_TEST_CHR     |     1 |    40 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_TEST_CHR_ID2 |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

SQL>  select * from t_test_chr a where a.type_varchar2=1;     

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 217284118

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |     1 |    40 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T_TEST_CHR |     1 |    40 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------
可以看到由于隐式转换使得查询走了不同的执行路径,在很多时候这种变化不是开发者所期待的,所以需要留意数据类型。具体可以参加《sql reference》的Datatype Comparison Rules一节。

3,Null详解

一行的一个cell如果没有值,那么就是null的,注意并不是他的值是null,所以col=null与col is null是完全不同的,null有很多特性,可以简单看几个例子来说明:

SQL> insert into t_test_chr  values ('1','',null);

1 row created.

SQL> select * from t_test_chr where type_char=1;

TYPE TYPE_VARCHAR2        TYPE_NVARCHAR2
---- -------------------- --------------------
1
SQL> select * from t_test_chr where type_varchar2 is null and type_nvarchar2 is null;

TYPE TYPE_VARCHAR2        TYPE_NVARCHAR2
---- -------------------- --------------------
1
可以看出,''与null都是没有值的,可以认为他们相同,但是:
SQL> select * from dual where ''=null;

no rows selected

SQL> select * from dual where '' is null;

D
-
X

同样的情况:
SQL> select * from dual where '' ='';

no rows selected

SQL> select * from dual where null =null;

no rows selected

SQL> select * from dual where 1!=null;

no rows selected

这说明null不等于null,其他任何值与null的比较,结果是未知的。
在一些关联问题中字段包含了null值,通常情况下可以使用外连接来解决null值的影响。
同时也可以利用null的特性来实现一些特定的需求,比如:
SQL> create table t_test_null(id number,send_time date ,recv_time date );

Table created.
SQL> insert into t_test_null
  2    select 1, sysdate, '' from dual;

1 row created.
SQL> insert into t_test_null
  2    select 1, '', sysdate from dual;

1 row created.
插入的两条数据如果是模拟某种成对的操作,现在要知道某个id下面的send和recv时间,并且在一条中显示(往往情况比这个复杂),那么就可以使用:
SQL> select id ,min(send_time),min(recv_time) from t_test_null group by id;

        ID MIN(SEND_ MIN(RECV_
---------- --------- ---------
         1 16-MAY-10 16-MAY-10
或者使用max也可以,因为null的“值”比其他都小,或者都大。

一般为了消除控制字段中的null值出现,可以增加一个 not null约束,增加not null约束之后新插入或者更新后的字段值不允许为null,但是要注意的是,这个约束对增加之前已有的数据不会检查。

使用道具 举报

回复
论坛徽章:
32
奥运会纪念徽章:摔跤
日期:2012-08-23 11:03:05青年奥林匹克运动会-击剑
日期:2014-09-19 10:58:152014年世界杯参赛球队:巴西
日期:2014-07-07 12:19:232014年世界杯参赛球队: 瑞士
日期:2014-05-19 12:18:36马上有钱
日期:2014-04-08 12:12:232014年新春福章
日期:2014-04-04 14:20:47马上有钱
日期:2014-02-18 16:43:092014年新春福章
日期:2014-02-18 16:43:09红旗
日期:2014-02-14 15:15:55优秀写手
日期:2013-12-18 09:29:16
 楼主| 发表于 2010-5-29 15:40 | 显示全部楼层
4,几个常见的伪列

SQL> select ora_rowscn,rowid,rownum from t_test_chr;

ORA_ROWSCN ROWID                  ROWNUM
---------- ------------------ ----------
6.4107E+12 AAAOG6AAFAAAAAWAAA          1
6.4107E+12 AAAOG6AAFAAAAAWAAB          2
其中ORA_rowscn主要用在闪回查询的flashback_query_clause,有关闪回的详细探讨在闪回一章。
Rowid是每一行的唯一地址。Rowid包含了许多信息,诸如:
数据对象的对象编号;
在数据文件中,该行所在的数据;
行在数据块的位置;
行所在的表空间内的数据文件号。
所以rowid是访问单行最快的途径,不过如果是访问多行的话,则稍稍有些不同,这与被访问的行的相对位置有关,也或者叫聚簇因子。具体的影响情况见cluster一章。
可以看一个rowid的使用,例如删除重复行:
truncate table t_test_chr;
insert into t_test_chr
  select 1, '', ''
    from dual
  union all
  select 1, '', '' from dual;
insert into t_test_chr
  select 2, '', ''
    from dual
  union all
  select 2, '', '' from dual;
delete from t_test_chr a where a.rowid in
(select min(rowid) from t_test_chr b where b.type_char = a.type_char);

Rownum则是结果集的临时行标。
很多时候需要取表或者集合内的一部分数据来使用,使用rownum很容易达到这个目的。或者在需要对一部分临时数据编号时使用,如果需要对导入数据库的一部分数据按照导入的次序进行排序编号,那么可以使用rownum的特性:
select type_char,type_varchar2,type_nvarchar2,rownum num from t_test_chr order by rowid asc
或者要获得top n的数据:
select * from
   (select * from employees order by employee_id)
   where rownum < 11;

5,集合运算

Union 与Union all
前者是获取每个查询的所有不重复的行,后者是所有行,包括重复的
Intersect
选择所有不重复的相交行
Minus
第一个查询减去第二个查询所得的结果。

6,Exists, not exsists,in ,not in

先来描述一下oracle的连接和子查询,在很多情况下,连接和子查询可以相互替代,这时候如何选择使用很多是凭个人习惯,不过二者在执行的效率问题上面确并不是完全一样的,往往需要根据具体的数据量的大小结合执行计划来调整。
in和exists同样可以相互替代,也需要根据具体的情况来选择使用。使用in的情况下,oracle有限查询子查询,然后匹配外层查询,exists则刚好相反。
一般来说,后面的子查询结果集如果比较小的话,一般使用in,如果子查询较大,而外层的查询相对较小,则使用exists。
Not in 和not exists则不能完全替换,他们有一些差异。具体先看一个例子:
SQL> select * from t_test_chr a ;

TYPE TYPE_VARCHAR2        TYPE_NVARCHAR2
---- -------------------- --------------------
1111 1111                 1111
1

SQL> select * from t_test_chr a where not exists (select 1 from t_test_chr where type_varchar2 = a.type_varchar2 );

TYPE TYPE_VARCHAR2        TYPE_NVARCHAR2
---- -------------------- --------------------
1
SQL> select * from t_test_chr a where type_varchar2 not in (select type_varchar2 from t_test_chr);

no rows selected

而exists 和in分别的结果是:
SQL> select * from t_test_chr a where exists (select 1 from t_test_chr where type_varchar2 = a.type_varchar2 );

TYPE TYPE_VARCHAR2        TYPE_NVARCHAR2
---- -------------------- --------------------
1111 1111                 1111

SQL> select * from t_test_chr a where type_varchar2 in (select type_varchar2 from t_test_chr);

TYPE TYPE_VARCHAR2        TYPE_NVARCHAR2
---- -------------------- --------------------
1111 1111                 1111

这样区别就一目了然了。

使用道具 举报

回复
论坛徽章:
1088
金色在线徽章
日期:2007-04-25 04:02:08金色在线徽章
日期:2007-06-29 04:02:43金色在线徽章
日期:2007-03-11 04:02:02在线时间
日期:2007-04-11 04:01:02在线时间
日期:2007-04-12 04:01:02在线时间
日期:2007-03-07 04:01:022008版在线时间
日期:2010-05-01 00:01:152008版在线时间
日期:2011-05-01 00:01:342008版在线时间
日期:2008-06-03 11:59:43ITPUB年度最佳技术原创精华奖
日期:2013-03-22 13:18:30
发表于 2010-5-29 15:52 | 显示全部楼层
不错,支持原创!~~~

使用道具 举报

回复
论坛徽章:
32
奥运会纪念徽章:摔跤
日期:2012-08-23 11:03:05青年奥林匹克运动会-击剑
日期:2014-09-19 10:58:152014年世界杯参赛球队:巴西
日期:2014-07-07 12:19:232014年世界杯参赛球队: 瑞士
日期:2014-05-19 12:18:36马上有钱
日期:2014-04-08 12:12:232014年新春福章
日期:2014-04-04 14:20:47马上有钱
日期:2014-02-18 16:43:092014年新春福章
日期:2014-02-18 16:43:09红旗
日期:2014-02-14 15:15:55优秀写手
日期:2013-12-18 09:29:16
 楼主| 发表于 2010-5-29 16:00 | 显示全部楼层
7,decode,case when,connect by 语句
decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)
case when 条件1 then 值1
        when 条件n then 值n
        else 值n+1
        end
很多时候这两个语句是类似的,比如:
SQL> select decode(dummy, 'X', 'xx', 'Y') decode_col,
  2         case
  3           when dummy = 'X' then
  4            'xxxx'
  5           else
  6            'Z'
  7         end case_col
  8    from dual;

DECODE_COL CASE_COL
---------- --------
xx         xxxx

不过一般来说decode用来处理匹配少量数值,且匹配条件比较简单,如果过于复杂的条件则应该使用case语句。
Connect by ..start with通常用来做树形查询。举个简单的树形结构例子,比如全国的机构组织架构,总部设为A,一级分部AXX(AAA,AAB),二级分部AXXXX(AAAAA,AAAAAB,AABAA,AABAB),创建数据如下:
SQL> create table t_test_organ (
  2  id   char(5),
  3  p_id char(5),
  4  name varchar2(40)
  5  );

Table created
SQL> insert into t_test_organ values ('A','','总公司');

1 row inserted
SQL> insert into t_test_organ values ('AAA','A','分公司1');

1 row inserted
SQL> insert into t_test_organ values ('AAB','A','分公司2');

1 row inserted
SQL> insert into t_test_organ values ('AAAAA','AAA','支公司1');

1 row inserted
SQL> insert into t_test_organ values ('AAAAB','AAA','支公司2');

1 row inserted
SQL> insert into t_test_organ values ('AABAA','AAB','支公司3');

1 row inserted
SQL> insert into t_test_organ values ('AABAB','AAB','支公司4');

1 row inserted
SQL> select level, id, name
  2    from t_test_organ
  3  connect by prior id = p_id
  4   start with id = 'A';

     LEVEL ID    NAME
---------- ----- ----------------------------------------
         1 A     总公司
         2 AAA   分公司1
         3 AAAAA 支公司1
         3 AAAAB 支公司2
         2 AAB   分公司2
         3 AABAA 支公司3
         3 AABAB 支公司4

7 rows selected

8,10g 正则表达式
很多时候需要对数据做处理,10g之前通常会自定义一些通用函数来实现一些规则,10g开始提供了几个函数来支持正则表达式:
REGEXP_LIKE
REGEXP_REPLACE
REGEXP_INSTR
REGEXP_SUBSTR

先来看看oracle10g正则表达式定义的运算符。
\        反斜杠有四个不同意义:取本身的值,引用下一个字符,介绍一个运算符,什么也不做
示例:
SQL> select * from dual where  regexp_like('aa111b','\^') ;

DUMMY
-----

SQL> select * from dual where  regexp_like('aa11^1b','\^') ;

DUMMY
-----
X
SQL> select * from dual where  regexp_like('aa11^1b','\a') ;

DUMMY
-----
X

SQL> select * from dual where  regexp_like('aa11^1b','\aa1') ;

DUMMY
-----
X

SQL> select * from dual where  regexp_like('aa11^1b','\aa2') ;

DUMMY
-----

SQL> select * from dual where  regexp_like('aa11^1b','\') ;

DUMMY
-----
X
*        匹配0个或者多个表达式
SQL> select * from dual where  regexp_like('aa11^1b','*') ;

DUMMY
-----
X

SQL> select * from dual where  regexp_like('aa11^1b','a*1') ;

DUMMY
-----
X
+        匹配一个或者多个表达式
SQL> select * from dual where  regexp_like('aa11^1b','a*1+1*1') ;

DUMMY
-----
X
?        匹配0个或者一个表达式
SQL> select * from dual where  regexp_like('aa11^1b','?b') ;

DUMMY
-----
X

SQL> select * from dual where  regexp_like('aa11^1b','?bb') ;

DUMMY
-----

SQL> select * from dual where  regexp_like('aa11^1b','?a1') ;

DUMMY
-----
X
|        指两项之中的选择
SQL> select * from dual where  regexp_like('aa11^1b','?a1|?bb') ;

DUMMY
-----
X
^        匹配开头的字符
SQL> select * from dual where  regexp_like('aa11^1b','^aa1') ;

DUMMY
-----
X

SQL> select * from dual where  regexp_like('aa11^1b','^aa2') ;

DUMMY
-----

$        匹配结尾的字符
SQL> select * from dual where  regexp_like('aa11^1b','1b$') ;

DUMMY
-----
X

SQL> select * from dual where  regexp_like('aa11^1b','11b$') ;

DUMMY
-----

SQL> select * from dual where  regexp_like('aa11^1b','a11$') ;

DUMMY
-----

.        匹配任何字符除了null
SQL> select * from dual where  regexp_like('aa11^1b','\^1.$') ;

DUMMY
-----
X

SQL> select * from dual where  regexp_like('aa11^1b','\^.$') ;

DUMMY
-----
[]        括号表达式
SQL> select * from dual where  regexp_like('aa11^1b','[\^.$]+[\^1.$]') ;

DUMMY
-----
X
()        子表达式的开始和结尾
SQL> select * from dual where  regexp_like('aa11^1b','(^a)+([\^.$]+[\^1.$])') ;

DUMMY
-----

SQL> select * from dual where  regexp_like('aa11^1b','(^a)+(.)+([\^.$]+[\^1.$])') ;

DUMMY
-----
X

SQL> select * from dual where  regexp_like('aa11^1b','(^a)+|([\^.$]+[\^1.$])') ;

DUMMY
-----
X

{m}        精确的匹配次数
{m,}        至少m次匹配
{m,n}        m到n次匹配
SQL> Select REGEXP_REPLACE('aabbcc','(aa){1}', 'xx') FROM dual;

REGEXP_REPLACE('AABBCC','(AA){
------------------------------
xxbbcc

SQL> Select REGEXP_REPLACE('aabbcc','(aa){1,}', 'xx') FROM dual;

REGEXP_REPLACE('AABBCC','(AA){
------------------------------
xxbbcc

SQL> Select REGEXP_REPLACE('aabbcc','(a){1,}', 'xx') FROM dual;

REGEXP_REPLACE('AABBCC','(A){1
------------------------------
xxbbcc

SQL> Select REGEXP_REPLACE('aabbcc','(a){2,}', 'xx') FROM dual;

REGEXP_REPLACE('AABBCC','(A){2
------------------------------
xxbbcc

SQL> Select REGEXP_REPLACE('aabbcc','(ab){2,}', 'xx') FROM dual;

REGEXP_REPLACE('AABBCC','(AB){
------------------------------
aabbcc

SQL> Select REGEXP_REPLACE('aabbcc','(ab){1,}', 'xx') FROM dual;

REGEXP_REPLACE('AABBCC','(AB){
------------------------------
axxbcc

SQL> Select REGEXP_REPLACE('aabbcc','(a){1,2}', 'xx') FROM dual;

REGEXP_REPLACE('AABBCC','(A){1
------------------------------
xxbbcc

SQL> Select REGEXP_REPLACE('aabbcc','(ab){1,2}', 'xx') FROM dual;

REGEXP_REPLACE('AABBCC','(AB){
------------------------------
axxbcc

使用道具 举报

回复
论坛徽章:
32
奥运会纪念徽章:摔跤
日期:2012-08-23 11:03:05青年奥林匹克运动会-击剑
日期:2014-09-19 10:58:152014年世界杯参赛球队:巴西
日期:2014-07-07 12:19:232014年世界杯参赛球队: 瑞士
日期:2014-05-19 12:18:36马上有钱
日期:2014-04-08 12:12:232014年新春福章
日期:2014-04-04 14:20:47马上有钱
日期:2014-02-18 16:43:092014年新春福章
日期:2014-02-18 16:43:09红旗
日期:2014-02-14 15:15:55优秀写手
日期:2013-12-18 09:29:16
 楼主| 发表于 2010-5-29 16:01 | 显示全部楼层
9,group by语句
Group by主要用来分组统计,这个是开发中经常被使用的语句,先来看几个语句的运行情况:
例如求各部门内的最大工钱值,根据部门来分组统计:
SQL> select deptno,max(sal) from scott.emp group by deptno;

    DEPTNO   MAX(SAL)
---------- ----------
        30       2850
        20       3000
        10       5000


Execution Plan
----------------------------------------------------------
Plan hash value: 4067220884

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     3 |    21 |     4  (25)| 00:00:01 |
|   1 |  HASH GROUP BY     |      |     3 |    21 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |    98 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        524  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed
增加一个order by deptno子句
SQL> select deptno,max(sal) from scott.emp group by deptno order by deptno;

    DEPTNO   MAX(SAL)
---------- ----------
        10       5000
        20       3000
        30       2850


Execution Plan
----------------------------------------------------------
Plan hash value: 15469362

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     3 |    21 |     4  (25)| 00:00:01 |
|   1 |  SORT GROUP BY     |      |     3 |    21 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |    98 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        524  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          3  rows processed
增加一个order by max(sal)子句
SQL> select deptno,max(sal) from scott.emp group by deptno order by max(sal);

    DEPTNO   MAX(SAL)
---------- ----------
        30       2850
        20       3000
        10       5000


Execution Plan
----------------------------------------------------------
Plan hash value: 2664716850

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     3 |    21 |     5  (40)| 00:00:01 |
|   1 |  SORT ORDER BY      |      |     3 |    21 |     5  (40)| 00:00:01 |
|   2 |   HASH GROUP BY     |      |     3 |    21 |     5  (40)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP  |    14 |    98 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        524  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          3  rows processed
上述三个语句略有不同,不过都是从全表扫描emp表开始执行,然后在此基础上进行group by或者order by,具体方式有所不同,这里仅仅把执行计划和统计信息给出来,具体在性能调整一章中进行详细测试分析与叙述。
主要来看看group by的增强语句,例如:having 子句,grouping sets,roll up,cube,group_id等
Having 子句
改进上述查询语句,只需要工钱大于等于3000的记录:
SQL>  select deptno,max(sal) from scott.emp group by deptno having max(sal) >=3000 order by max(sal);

    DEPTNO   MAX(SAL)
---------- ----------
        20       3000
        10       5000


Execution Plan
----------------------------------------------------------
Plan hash value: 3611938775

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |     7 |     5  (40)| 00:00:01 |
|   1 |  SORT ORDER BY       |      |     1 |     7 |     5  (40)| 00:00:01 |
|*  2 |   FILTER             |      |       |       |            |          |
|   3 |    HASH GROUP BY     |      |     1 |     7 |     5  (40)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| EMP  |    14 |    98 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Rollup语句
Rollup可以理解为一维多层的统计,往往在需要按层次统计的时候用到。
修改一下需求,现在需要按照部门,分工作职位来统计工钱的总和,并求全公司之和,可以简单的使用rollup来完成:
SQL> select a.deptno, b.job, sum(b.sal)
  2    from scott.dept a, scott.emp b
  3   where a.deptno = b.deptno
  4   group by rollup(a.deptno, b.job);

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

    DEPTNO JOB       SUM(B.SAL)
---------- --------- ----------
        30                 9400
                          28050

13 rows selected.
可以看到使用rollup使得sum增加了聚合的级别,即实现了对多层进行分组统计计算。
现在稍稍修改一下需求,不需要对全公司进行统计,只需要计算各部门的总和和部门下不同工种的求和,那么修改后的语句可以如下:

SQL> select a.deptno, b.job, sum(b.sal)
  2    from scott.dept a, scott.emp b
  3   where a.deptno = b.deptno
  4   group by a.deptno,rollup( b.job);

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

    DEPTNO JOB       SUM(B.SAL)
---------- --------- ----------
        30                 9400

12 rows selected.

再增加一个工头的字段,需要知道不同的工头下面工人的情况:
SQL> select a.deptno, b.job,b.mgr, sum(b.sal)
  2    from scott.dept a, scott.emp b
  3   where a.deptno = b.deptno
  4   group by a.deptno,rollup( b.job,b.mgr);

    DEPTNO JOB              MGR SUM(B.SAL)
---------- --------- ---------- ----------
        10 CLERK           7782       1300
        10 CLERK                      1300
        10 MANAGER         7839       2450
        10 MANAGER                    2450
        10 PRESIDENT                  5000
        10 PRESIDENT                  5000
        10                            8750
        20 CLERK           7788       1100
        20 CLERK           7902        800
        20 CLERK                      1900
        20 ANALYST         7566       6000

    DEPTNO JOB              MGR SUM(B.SAL)
---------- --------- ---------- ----------
        20 ANALYST                    6000
        20 MANAGER         7839       2000
        20 MANAGER                    2000
        20                            9900
        30 CLERK           7698        950
        30 CLERK                       950
        30 MANAGER         7839       2850
        30 MANAGER                    2850
        30 SALESMAN        7698       5600
        30 SALESMAN                   5600
        30                            9400

22 rows selected.
可以这样理解上面rollup语句:
group by col1,rollup(col2,col3)会进行如下计算:
(col1,col2,col3)
(col1,col2)
(col1)

Cube语句
Cube的意思是立方,主要是用来进行多维度的统计的,Cube有时也可以代替rollup,比如group by col1,rollup(col2)就可以用group by col1,cube(col2)来代替,可以用下面的语句带实现前面rollup的实现2:
SQL> select a.deptno, b.job, sum(b.sal)
  2    from scott.dept a, scott.emp b
  3   where a.deptno = b.deptno
  4   group by a.deptno,cube( b.job);

    DEPTNO JOB       SUM(B.SAL)
---------- --------- ----------
        10                 8750
        10 CLERK           1300
        10 MANAGER         2450
        10 PRESIDENT       5000
        20                 9900
        20 CLERK           1900
        20 ANALYST         6000
        20 MANAGER         2000
        30                 9400
        30 CLERK            950
        30 MANAGER         2850

    DEPTNO JOB       SUM(B.SAL)
---------- --------- ----------
        30 SALESMAN        5600

12 rows selected.
可以看到除了结果集的排序不同以外,其他均相同。这时候使用rollup和cube分别进行了如下的计算:
Rollup:
(col1,col2)
(col1)
Cube:
(col1)
(col1,col2)
注意上述表达式的顺序
但是cube的group by col1,cube(col2,col3)与rollup差别较大,事实上它的计算如下:
(col1)
(col1,col3)
(col1,col2)
(col1,col2,col3)
而group by cube(col1,col2,col3)则会计算2的3次方次,即维度为3.计算如下:
(col1),(col2),(col3),(col2,col3),(col1,col2),(col1,col3),(col1,col2,col3),()

Grouping sets语句
如果说rollup和cube是oracle预定义了的计算维度,那么grouping sets则可以理解为可以自己设置计算维度的一个表达式,用下面一个例子来看:
SQL> select a.deptno, b.job,b.mgr, sum(b.sal)
  2    from scott.dept a, scott.emp b
  3   where a.deptno = b.deptno
  4   group by a.deptno,cube( b.job,b.mgr)
  5  minus
  6  select a.deptno, b.job,b.mgr, sum(b.sal)
  7    from scott.dept a, scott.emp b
  8   where a.deptno = b.deptno
  9   group by a.deptno,grouping sets((b.job,b.mgr),(b.job),(b.mgr),());

no rows selected
说明了group by a.deptno,cube( b.job,b.mgr)
与group by a.deptno,grouping sets((b.job,b.mgr),(b.job),(b.mgr),())的结果是完全一样的,根据对cube的理解则grouping sets也很容易理解,不在累述。

Grouping_id和group_id函数
对于使用cube或者rollup合作而后grouping sets的语句,可以使用group_id()或者grouping_id()来获得不同的分组:
SQL> select a.deptno, b.job, sum(b.sal),group_id() g_id
  2    from scott.dept a, scott.emp b
  3   where a.deptno = b.deptno
  4   group by a.deptno,cube( a.deptno,b.job);

    DEPTNO JOB       SUM(B.SAL)       G_ID
---------- --------- ---------- ----------
        10 CLERK           1300          0
        10 MANAGER         2450          0
        10 PRESIDENT       5000          0
        20 CLERK           1900          0
        20 ANALYST         6000          0
        20 MANAGER         2000          0
        30 CLERK            950          0
        30 MANAGER         2850          0
        30 SALESMAN        5600          0
        10 CLERK           1300          1
        10 MANAGER         2450          1

    DEPTNO JOB       SUM(B.SAL)       G_ID
---------- --------- ---------- ----------
        10 PRESIDENT       5000          1
        20 CLERK           1900          1
        20 ANALYST         6000          1
        20 MANAGER         2000          1
        30 CLERK            950          1
        30 MANAGER         2850          1
        30 SALESMAN        5600          1
        10                 8750          0
        20                 9900          0
        30                 9400          0
        10                 8750          1

    DEPTNO JOB       SUM(B.SAL)       G_ID
---------- --------- ---------- ----------
        20                 9900          1
        30                 9400          1

24 rows selected.

SQL> select group_id() gp_id,
  2         grouping_id(a.deptno, b.job,b.mgr) gpp_id,
  3         a.deptno, b.job,b.mgr,sum(b.sal)
  4    from scott.dept a, scott.emp b
  5   where a.deptno = b.deptno
  6   group by a.deptno,grouping sets( a.deptno,b.job,b.mgr);

     GP_ID     GPP_ID     DEPTNO JOB              MGR SUM(B.SAL)
---------- ---------- ---------- --------- ---------- ----------
         0          2         20                 7839       2000
         0          2         10                 7839       2450
         0          2         30                 7698       6550
         0          2         20                 7566       6000
         0          2         10                 7782       1300
         0          2         20                 7902        800
         0          2         10                            5000
         0          2         30                 7839       2850
         0          2         20                 7788       1100
         0          1         10 CLERK                      1300
         0          1         10 MANAGER                    2450

     GP_ID     GPP_ID     DEPTNO JOB              MGR SUM(B.SAL)
---------- ---------- ---------- --------- ---------- ----------
         0          1         10 PRESIDENT                  5000
         0          3         10                            8750
         0          1         20 CLERK                      1900
         0          1         20 ANALYST                    6000
         0          1         20 MANAGER                    2000
         0          3         20                            9900
         0          1         30 CLERK                       950
         0          1         30 MANAGER                    2850
         0          1         30 SALESMAN                   5600
         0          3         30                            9400

21 rows selected.

使用道具 举报

回复
论坛徽章:
32
奥运会纪念徽章:摔跤
日期:2012-08-23 11:03:05青年奥林匹克运动会-击剑
日期:2014-09-19 10:58:152014年世界杯参赛球队:巴西
日期:2014-07-07 12:19:232014年世界杯参赛球队: 瑞士
日期:2014-05-19 12:18:36马上有钱
日期:2014-04-08 12:12:232014年新春福章
日期:2014-04-04 14:20:47马上有钱
日期:2014-02-18 16:43:092014年新春福章
日期:2014-02-18 16:43:09红旗
日期:2014-02-14 15:15:55优秀写手
日期:2013-12-18 09:29:16
 楼主| 发表于 2010-5-29 16:01 | 显示全部楼层
10,dml扩展示例
With as select
先来看个例子,
SQL> with temp as
  2  (select a.deptno,a.job,a.sal,sum(a.sal)over(partition by a.deptno) max_sal from scott.emp a
  3  )
  4  select * from temp
  5  ;

    DEPTNO JOB              SAL    MAX_SAL
---------- --------- ---------- ----------
        10 MANAGER         2450       8750
        10 PRESIDENT       5000       8750
        10 CLERK           1300       8750
        20 MANAGER         2000       9900
        20 ANALYST         3000       9900
        20 CLERK           1100       9900
        20 CLERK            800       9900
        20 ANALYST         3000       9900
        30 SALESMAN        1250       9400
        30 SALESMAN        1500       9400
        30 SALESMAN        1600       9400

    DEPTNO JOB              SAL    MAX_SAL
---------- --------- ---------- ----------
        30 CLERK            950       9400
        30 MANAGER         2850       9400
        30 SALESMAN        1250       9400

14 rows selected.
可以看出with的一般用法,with子句只能用于select,一个比较好的用处是使得代码的逻辑比较清楚。
另外with语句使用系统临时表,一定程度上可以提升性能,因为oracle执行一次WITH子查询,会将结果放到临时表中,如果随后有对子查询的多次访问,那么会从临时表中直接读取数据。有关性能的分析具体给出yangtingkun老师的文章:http://yangtingkun.itpub.net/post/468/202694

Merge into
SQL> select * from test.cust;

   CUST_ID CUST_CITY
---------- --------------------------------------------------
        10 a
        15 b
        20 c
        25 d
        30 e
        35 f
        40 g

7 rows selected
SQL> merge into test.cust a
  2  using (select 10 cust_id from dual ) b
  3  on (a.cust_id = b.cust_id)
  4  when matched then
  5       update set a.cust_city = a.cust_city||'1'
  6  when not matched then
  7       insert (cust_id) values (b.cust_id);

1 row merged.

Insert all:
可以使用insert all select 同时插入不同的表中,也可以使用带条件的insert all语句,增加条件判断语句:when ..then .into。
使用insert first 则下一个条件将自动不考虑上一个条件被选中的行。


带条件的update:
update t_test_bill a
   set a.date =
   (case when a.b_no = '000025661847852' then date '2010-4-13'
         when a.b_no = '000044155978850' then date '2010-3-25'
         else date '2010-4-2' end)
where a.b_no in ('000025661847852', '000028634036852', '000028634143852',
        '000028634250852', '000044155978850');

更新视图:
SQL> update (select a.empno, a.comm,b.dname
  2            from scott.emp a, scott.dept b
  3           where a.deptno = b.deptno
  4           and b.loc in ('NEW YORK', 'DALLAS'))
  5     set comm = 0.1;

8 rows updated
但要注意,不能同时update多个关联的基表:
SQL> update (select a.empno, a.comm,b.dname
  2            from scott.emp a, scott.dept b
  3           where a.deptno = b.deptno
  4           and b.loc in ('NEW YORK', 'DALLAS'))
  5     set comm = 0.1,dname = dname||'';

update (select a.empno, a.comm,b.dname
          from scott.emp a, scott.dept b
         where a.deptno = b.deptno
         and b.loc in ('NEW YORK', 'DALLAS'))
   set comm = 0.1,dname = dname||''

ORA-01776: cannot modify more than one base table through a join view

使用道具 举报

回复
论坛徽章:
1088
金色在线徽章
日期:2007-04-25 04:02:08金色在线徽章
日期:2007-06-29 04:02:43金色在线徽章
日期:2007-03-11 04:02:02在线时间
日期:2007-04-11 04:01:02在线时间
日期:2007-04-12 04:01:02在线时间
日期:2007-03-07 04:01:022008版在线时间
日期:2010-05-01 00:01:152008版在线时间
日期:2011-05-01 00:01:342008版在线时间
日期:2008-06-03 11:59:43ITPUB年度最佳技术原创精华奖
日期:2013-03-22 13:18:30
发表于 2010-5-29 16:14 | 显示全部楼层
好帖子,授予精华,我想大家肯定没有异议,嘿嘿!~期待后续好内容!~

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

TOP技术积分榜 社区积分榜 徽章 电子杂志 团队 统计 虎吧 老博客 知识索引树 读书频道 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 | IT博客
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
京ICP备16024965号 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表