Oracle Sql Óï¾äÐÔÄܵ÷ÓÅ
ÕâƪÎÄÕÂÊÇÐþ»úÒÝʿΪijÉÏÊй«Ë¾µÄERPϵͳµ÷ÓÅËù׫д±¨¸æµÄ²¿·Öժ¼£¬Ï£ÍûÄܹ»¶Ô´ó¼ÒÓÐËù°ïÖú£º
ÏÂÃæ¸ø³öSQLÓÅ»¯¾³£Óöµ½µÄÇéÐΡ£ËüÃÇÊÇÔںܶàÈ˳¤ÆÚ»ýÀ۵ľÑéÉÏ×ܽá³öÀ´µÄ£¬ÕâЩ¶¼ÊÇһЩһ°ãÐԵĽáÂÛ£¬ÔÚʵ¼Ê¹¤×÷ÖУ¬»¹Ó¦µ±¾ßÌåÇé¿ö¾ßÌå·ÖÎö£¬²¢¼ÓÒÔÁé»îÔËÓá£
(1) ÓÃNOT EXISTSÌæ»»NOT IN ½«¸üÏÔÖøµØÌá¸ßЧÂÊ£¬¾¡Á¿ÓÃNOT EXISTSÌæ´úNOT IN¡£ÔÚ×Ó²éѯÖУ¬NOT IN×Ӿ佫ִÐÐÒ»¸öÄÚ²¿µÄÅÅÐòºÍºÏ²¢. ÎÞÂÛÔÚÄÄÖÖÇé¿öÏ£¬NOT IN¶¼ÊÇ×îµÍЧµÄ (ÒòΪËü¶Ô×Ó²éѯÖеıíÖ´ÐÐÁËÒ»¸öÈ«±í±éÀú). ΪÁ˱ÜÃâʹÓÃNOT IN£¬ÎÒÃÇ¿ÉÒÔ°ÑËü¸Äд³ÉÍâÁ¬½Ó(Outer Joins)»òNOT EXISTS¡£
ÀýÈ磬
SELECT ENMAE
from EMP
WHERE DEPTNO NOT IN
(
SELECT DEPTNO
from DEPT
WHERE LOC='BOSTON'
);
ΪÁËÌá¸ßЧÂÊ£¬¿É¸ÄдΪ£¬
(·½·¨Ò»£º¸ßЧ)
SELECT ENAME
from EMP A,DEPT B
WHERE A.DEPTNO = B.DEPTNO(+)
AND B.DEPTNO IS NULL
AND B.LOC(+) = 'BOSTON'
(·½·¨¶þ£º×î¸ßЧ)
SELECT ENAME
from EMP E
WHERE NOT EXISTS
( SELECT 'X' from DEPT D WHERE
D.DEPTNO = E.DEPTNO AND LOC='BOSTON'
);
³ý´ËÖ®Í⣬ÍƼöʹÓÃNOT EXISTSµÄÒ»¸öÖØÒªµÄÔÒòÊÇNOT EXISTSÔÚµ±×Ó²éѯ¿ÉÄÜ·µ»ØNULLµÄÇé¿öÏ£¬¸ü¼Ó¿É¿¿£¬ÕâÊÇÒòΪÔڱȽÏÁбíÖÐÈç¹û°üº¬NULL£¬NOT INÌõ¼þ½«Îªfalse¡£¿¼ÂÇÏÂÃæµÄÓï¾ä£¬¸ÃÓï¾äÊÔͼÔÚEMP±íÖвéѯ³öÀ´Ã»ÓÐÒ»¸öÏÂÊôµÄÈËÔ±ÁÐ±í£º
SELECT ENAME, JOB from EMP
WHERE EMPNO NOT IN (SELECT MGR from EMP);
ÉÏÃæ²éѯ²»»á·µ»ØÈκνá¹û£¬ÒòΪijЩ¼Ç¼µÄMGR×Ö¶ÎÊÇNULLÖµ¡£ÕýÈ·µÄд·¨ÊÇ£º
SELECT ENAME, JOB from EMP E
WHERE NOT EXISTS (SELECT MGR from EMP WHERE MGR=E.EMPNO);
(2) ¾¡Á¿±ÜÃâÔÚË÷ÒýÁÐÉϽøÐмÆËã¡£
WHERE×Ó¾äÖУ¬Èç¹ûË÷ÒýÁÐÊǺ¯ÊýµÄÒ»²¿·Ö¡£ÓÅ»¯Æ÷½«²»Ê¹ÓÃË÷Òý¶øʹÓÃÈ«±íɨÃè¡£ÀýÈ磺
µÍЧµÄд·¨£º
SELECT …
from DEPT
WHERE SAL * 12 > 25000;
¸ßЧµÄд·¨£º
SELECT …
from DEPT
WHERE SAL > 25000/12;
ÕâÊÇÒ»¸ö·Ç³£ÊµÓõĹæÔò£¬ÇëÎñ±ØÀμǡ£
(3) ÓÃ>=Ìæ´ú>
Èç¹ûDEPTNOÉÏÓÐÒ»¸öË÷Òý£¬
¸ßЧµÄд·¨£º
SELECT *
from EMP
WHERE DEPTNO >=4
µÍЧµÄд·¨£º
Ïà¹ØÎĵµ£º
ÔÚOracleÖн¨¿â£¬Í¨³£ÓÐÁ½ÖÖ·½·¨¡£Ò»ÊÇʹÓÃOracleµÄ½¨¿â¹¤
ÇÒDBCA£¬ÕâÊÇÒ»¸öͼÐνçÃ湤ÇÒ£¬Ê¹ÓÃÆðÀ´·½±ãÇÒºÜÈÝÒ×Àí½â£¬ÒòΪËüµÄ½çÃæÓѺá¢ÃÀ¹Û£¬¶øÇÒÌáʾҲ±È½ÏÆëÈ«¡£ÔÚ£×indowsϵͳÖУ¬Õâ¸ö¹¤¾ß¿ÉÒÔÔÚOracle³ÌÐò×éÖдò¿ª£¨”¿ªÊ¼”—“³ÌÐò”—“ Oracle OraDb10g_home1”&mdash ......
Ò»¡¢ÏµÍ³µÄĬÈÏÓû§
Java´úÂë
sys;//ϵͳ¹ÜÀíÔ±£¬ÓµÓÐ×î¸ßȨÏÞ
system;//±¾µØ¹ÜÀíÔ±£¬´Î¸ßȨÏÞ
scott;//ÆÕͨÓû§£¬ÃÜÂëĬÈÏΪtiger,ĬÈÏδ½âËø
¶þ¡¢µÇ½
Java´úÂë
sqlplus conn / as sysdba;//µÇ½sysÕÊ»§ ......
1. ²éѯÊý¾Ý¿âÏÖÔڵıí¿Õ¼ä
select tablespace_name, file_name, sum(bytes)/1024/1024 table_size from dba_data_files group by tablespace_name,file_name;
2. ½¨Á¢±í¿Õ¼ä
CREATE TABLESPACE data01 DATAFILE '/oracle/ ......
1)½¨Á¢²Ù×÷ϵͳĿ¼e:\test£¬×¼±¸Êý¾ÝÎļþdept.txt²¢ÖÃÓÚe:\testÖ®ÏÂ
"10","ACCOUNTING","NEW
YORK"
"20","RESEARCH","DALLAS"
"30","SALES","CHICAGO"
"40","OPERATIONS","BOSTON"
2)´´ ......
HWMÊÇoracleÖÐblockÓÐûÓÐʹÓõķֽçÏߣ¬Ëü»áËæ×ÅÊý¾ÝµÄinsert¶øÉÏÉý£¬µ«Ëü²¢²»»áËæÊý¾ÝµÄdelete¶øϽµ£¬Òò´ËÈ«±íɨÃèµÄʱ¼ä²¢²»ÒòÊý¾ÝµÄdelete¶ø¼õÉÙ£¬Ïà·´¿ÉÄÜÓÉÓÚ¿éÇå³ý·´¶øÈ«±íɨÃèʱ¼äÔö¼Ó£¬¿ÉÒÔÓÃÏÂÃæ·½·¨µÄÈÎÒ»Ò»ÖÖÀ´½µµÍHWM£º
µÚÒ»ÖÖ£ºshrink
ÊÊÓÃÓÚ10gÒÔºóµÄ°æ±¾£¬Ç°ÌáÊÇÕâ¸ö±í£¬Ë÷Òý£¬ÎﻯÊÓͼ»òÎﻯÊÓͼlogËùÔ ......