ORACLE ÔÚnot inÖÐʹÓÃnullµÄÎÊÌâ
ÒÔǰ»¹×¨ÃÅС×ܽá¹ýÒ»ÏÂORACLEÖйØÓÚNULLµÄһЩÎÊÌ⣬ÅöÇɽñÌìÔÚ¿´ÊéµÄ¹ý³ÌÖÐÓÖ¿´µ½ÁËÁíÍâÒ»¸öÒÔǰû·¢ÏÖµÄÐèҪעÒâµÄµØ·½£¬ÄǾÍÊÇÔÚnot inÖÐʹÓÃnullµÄÎÊÌâ¡£
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select deptno
2 from dept
3 where deptno in (10,50,null);
DEPTNO
----------
10
//¿´µ½Ê¹ÓÃinµÄʱºò¼´±ãÓÐnull Ò²ÊÇÕý³£µÄ ÏÂÃæ¿´Ò»ÏÂnot in
SQL> select deptno
2 from dept
3 where deptno not in (10,50);
DEPTNO
----------
20
30
40
//ÕâÀï¿´ÆðÀ´ºÍÎÒÃǵÄÔ¤ÆÚͦ·ûºÏµÄŶ
SQL> select deptno
2 from dept
3 where deptno not in (10,50,null);
no rows selected
//Ôõô»ØÊ Ϊʲô¼ÓÁ˸önull Ç°ÃæµÄ20¡¢30¡¢40ÈýÌõÊý¾Ý¾Í²»ÏÔʾ³öÀ´ÁË
INºÍNOT IN±¾ÖÊÉ϶¼ÊÇORÔËË㣬Òò¶ø¼ÆËãÂß¼ORʱ´¦ÀíNULLµÄ·½Ê½²»Í¬£¬²úÉúµÄ½á¹ûÒ²²»Í¬¡£
ÏÂÃæÎÒÃÇ·ÖÎöÒ»ÏÂÇ°ÃæµÄÈýÌõÓï¾ä
SQL> select deptno
2 from dept
3 where deptno in (10,50,null);
ÕâÀï¿ÉÒԵȼÛÓÚwhere deptno=10 or deptno=50 or deptno=null£¬ÓÉÓÚÊÇorÏàÁ¬½Ó£¬ÄÇôֻҪÓÐÒ»¸öÌõ¼þΪTRUE£¬Õû¸ö¾ÍιTRUEÁË¡£ËùÒÔdeptnoΪ10µÄ¼Ç¼ÏÔʾ³öÀ´ÁË¡£
SQL> select deptno
2 from dept
3 where deptno not in (10,50,null);
ÕâÀïµÈ¼ÛÓÚwhere not (deptno=10 or deptno=50 or deptno=null)£¬ÄÃdeptno=20µÄ¼Ç¼À´¾ÙÀý°É¡£
not (20=10
Ïà¹ØÎĵµ£º
DBAÒª¶¨Ê±¶ÔÊý¾Ý¿âµÄÁ¬½ÓÇé¿ö½øÐмì²é£¬¿´ÓëÊý¾Ý¿â½¨Á¢µÄ»á»°ÊýÄ¿ÊDz»ÊÇÕý³££¬Èç¹û½¨Á¢Á˹ý¶àµÄÁ¬½Ó£¬»áÏûºÄÊý¾Ý¿âµÄ×ÊÔ´¡£Í¬Ê±£¬¶ÔһЩ“¹ÒËÀ”µÄÁ¬½Ó£¬¿ÉÄÜ»áÐèÒªDBAÊÖ¹¤½øÐÐÇåÀí¡£
ÒÔϵÄSQLÓï¾äÁгöµ±Ç°Êý¾Ý¿â½¨Á¢µÄ»á»°Çé¿ö:
select sid,serial#,username,program,machine,status
from v$session;
......
×î¼òµ¥µÄÊÇʹÓÃ
/* Formatted on 2009/12/02 16:01 (Formatter Plus v4.8.8) */
SELECT TO_CHAR (TO_DATE ('2009-12-02', 'YYYY-MM-DD'), 'WW')
from DUAL;
²»¹ýto_char()º¯ÊýÔÚ¼ÆËãÒ»ÄêÖеڼ¸ÖÜÊÇ´Ó¸ÃÄêµÄ1ÔÂ1ÈÕ¿ªÊ¼µÄ¡£
ÒÔÏÂSQL Äܵõ½ÕýÈ·µÄ×ÔÈ»ÖÜÊý.
/* Formatted ......
OracleÄÚÖú¯ÊýSQLCODEºÍSQLERRMÊÇÌØ±ðÓÃÔÚOTHERS´¦ÀíÆ÷ÖУ¬·Ö±ðÓÃÀ´·µ»ØOracleµÄ´íÎó´úÂëºÍ´íÎóÏûÏ¢¡£
¡¡¡¡
¡¡¡¡OTHERS´¦ÀíÆ÷Ó¦¸ÃÊÇÒì³£´¦Àí¿éÖеÄ×îºóµÄÒì³£´¦ÀíÆ÷£¬ÒòΪËüÊÇÓÃÀ´²¶»ñ³ýÁ˱ðµÄÒì³£´¦ÀíÆ÷´¦ÀíÒÔÍâµÄËùÓеÄOracleÒì³££¬ËùÒÔÔÚ³ÌÐòµÄ×îÍâ²ãʹÓÃÒ»¸öOTHERS´¦ÀíÆ÷µÄ»°£¬ ......
SQL> select sal,comm from emp
2 where sal<2000;
SAL COMM
---------- ----------
800
1600 300
1250 500
1250 1400
1500 0
1100
950
1300
8 rows selected.
SQL> select sal as salary,comm as ......
ROLLUPºÍCUBEÓï¾ä¡£
OracleµÄGROUP
BYÓï¾ä³ýÁË×î»ù±¾µÄÓï·¨Í⣬»¹Ö§³ÖROLLUPºÍCUBEÓï¾ä¡£Èç¹ûÊÇROLLUP(A, B, C)µÄ»°£¬Ê×ÏÈ»á¶Ô(A¡¢B¡¢C)½øÐÐGROUP
BY£¬È»ºó¶Ô(A¡¢B)½øÐÐGROUP BY£¬È»ºóÊÇ(A)½øÐÐGROUP BY£¬×îºó¶ÔÈ«±í½øÐÐGROUP BY²Ù×÷¡£Èç¹ûÊÇGROUP BY
CUBE(A, B, C)£¬ÔòÊ×ÏÈ»á¶Ô(A¡¢B¡¢C)½øÐÐGROUP
BY£¬È»ºóÒÀ´ÎÊÇ(A¡ ......