Oracle×Ó²éѯ
×Ó²éѯ
µ¥ÐÐ×Ó²éѯ(single-row subqueries)
ʹÓõÄÔËËã·ûºÅ(=,>,<,>=,<=,<>)
¶àÐÐ×Ó²éѯ(multiple-row subqueries)
ʹÓõÄÔËËã·ûºÅ(in,not in,exists,not exits,all,any)
Ïà¹Ø×Ó²éѯ(correlated subqueries)
¸ñʽ select ÁÐÃû,(select Óï¾ä) from ±íÃû
±êÁ¿×Ó²éѯ(scalar subqueries)
×Ó²éѯÊÇ·µ»Øµ¥Ðе¥ÁÐ,¸ñʽͬÉÏ
¶àÁÐ×Ó²éѯ(multiple-column subqueries)
ÔÚDDLÓï¾äÖÐʹÓÃ×Ó²éѯ
ÔÚDMLÓï¾äÖÐʹÓÃ×Ó²éѯ
--------
µ¥ÐÐ×Ó²éѯ
--ÏÔʾ¹¤×Ê×î¸ßµÄ¹ÍÔ±ÐÅÏ¢
Select ename,deptno,sal from emp
Where sal=(select max(sal) from emp);
--------
¶àÐÐ×Ó²éѯ
--ÏÔʾÓ벿ÃűàºÅΪ20µÄ¸ÚλÏàͬµÄ¹ÍÔ±ÐÅÏ¢
Select ename,deptno,sal,job from emp
Where job in (select distinct job from emp where deptno=20);
--ÏÔʾ²»Ó벿ÃűàºÅΪ20µÄ¸ÚλÏàͬµÄ¹ÍÔ±ÐÅÏ¢
Select ename,deptno,sal,job from emp where job not in (select distinct job from emp where deptno=20);
--ÏÔʾ¸ßÓÚ²¿ÃűàºÅΪ20µÄËùÓйÍÔ±µÄ¹¤×ʵĹÍÔ±ÐÅÏ¢
select ename,deptno,sal ,job from emp
where sal>all(select sal from emp where deptno=20);
--ÏÔʾ¸ßÓÚ²¿ÃűàºÅΪ20µÄÈκιÍÔ±µÄ¹¤×ʵĹÍÔ±ÐÅÏ¢
select ename,deptno,sal ,job from emp
where sal>any(select sal from emp where deptno=20);
---------
Ïà¹Ø×Ó²éѯ
--ÏÔʾÿ¸ö²¿ÃŵÄ×î¸ß¹¤×ʵĹÍÔ±ÐÅÏ¢
select deptno,(select max(sal) from emp b where b.deptno=a.deptno) maxsal
from emp a order by deptno;
--Ôö¼Ódistinct
select distinct deptno,(select max(sal) from emp b where b.deptno=a.deptno) maxsal
from emp a order by deptno;
--ÏÔʾ¹¤×÷ÔÚNEW YORKµÄ¹ÍÔ±ÐÅÏ¢
select ename,deptno,sal,job from emp
where exists (select 'x' from dept where dept.deptno=emp.deptno and dept.loc='NEW YORK');
---------
±êÁ¿×Ó²éѯ
--·µ»Øµ¥Ðе¥ÁÐ
Select count(*) from emp;
Select sum(sal)
Ïà¹ØÎĵµ£º
ƽʱ½Ó´¥mysql½Ï¶à£¬ÒòΪmysqlÔÚÒ»°ãµÄСÏîÄ¿Öй»ÓÃÁË ¿ªÔ´¶øÇÒÃâ·Ñ¡£
ºÜ¶à¹«Ë¾ÐèÒªoracleά»¤µÄ£¬¿ª·¢oracleÉõÖÁÊǶþ´Î¿ª·¢ÔÚ¹úÄÚÒ²±È½ÏÉٵģ¬Óм׹ÇÎĹ«Ë¾£¬Ë¸ÒºÍËûÃÇÇÀ·¹Íë°¡ ¹þ¹þ ²»¹ýÈ˼ҵÄÊÕ·ÑÂù¹óµÄ£¬ÄãÈç¹ûÓÐˮƽ£¬³Ôµã²Ð¸þµÄ»ú»áÂù¶à¡£ oracleµÄÅàѵÐÅÏ¢±í´ïÁËÊг¡ÐèÇó¡£
£¨ÎÒÔÚѧУ»·¾³µÄ²ÂÏ룬רҵÈËÊ¿À´ÅÄש ......
ÔµÆðÒ»¸ö±í¿Õ¼äÌ«´ó,ɾ³ýÊý¾ÝºóÓÉÓÚÎļþβ±»ÓÃ,ÎÞ·¨resize,´òËã°ÑËùÓбí¿Õ¼äÉϵĶÔÏómoveµ½Ò»¸öÁÙʱ´æ´¢µÄ±í¿Õ¼ä×öÕûÀí¡£
moveÒ»¸ö±íµ½ÁíÍâÒ»¸ö±í¿Õ¼äʱ,Ë÷Òý²»»á¸ú×ÅÒ»Æðmove£¬¶øÇÒ»áʧЧ¡££¨LOBÀàÐÍÀýÍ⣩±ímove£¬ÎÒÃÇ·ÖΪ£º
*ÆÕͨ±ímove
*·ÖÇø±ímove
*LONG,LOB´ó×Ö¶ÎÀàÐÍmoveÀ´½øÐвâÊÔºÍ˵Ã÷¡£
Ë÷ÒýµÄmove£¬ÎÒÃÇÍ ......
WinXP ÏÂÖØÐÂÉèÖà Oracle ¹ÜÀíÔ±ÃÜÂë
Windows ÏÂÐÞ¸Ä Oracle ¹ÜÀíÔ±ÃÜÂë²Ù×÷²½Öè¡£´Ë²½ÖèÔÚ WinXP5.1¡¢Oracle92 ϲÙ×÷³É¹¦¡£¸ü¸ÄÒÔºóÐèÒªÖØÆô¼ÆËã»úºÍʵÀý·½¿ÉÉúЧ¡£
±³¾°£ºWinXP °æ±¾ 5.1£¨ÄÚ²¿°æ±¾ºÅ 2600.xpsp_sp2_dgr.07022 ......
º¯Êý:
×Ö·ûº¯Êý
ת»¯³ÉСдLOWER(<C>) ת»¯³É´óдUPPER(<C>) select lower('aAbBcC') from dual;
--------
ÈÕÆÚº¯Êý
add_months(D,<I>)·µ»ØÈÕÆÚD¼ÓÉÏi¸öÔºóµÄ½á¹û
select add_month(sysdate,3)from dual;
&nb ......