Ò׽ؽØͼÈí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

ORACLEµÄһЩ²éѯ


Ò»¡¢ÐèÒª²éѯµÄÊý¾Ý
1
¡¢²éѯ³öÔÂн´óÓÚ
'SMITH'
µÄËùÓÐÔ±¹¤×ÊÁÏ
    select *from emp where
sal>(select sal from emp where name="SMITH");
2
¡¢²éѯËùÓÐÔ±¹¤µÄÐÕÃû¼°Áìµ¼ÐÕÃû
    select e1.ename,e2.ename from
emp e1,emp e2 where e1.mgr=e2.empno(+);
3
¡¢²éѯ³öÈëÖ°ÈÕÆÚÔçÓÚÁìµ¼µÄËùÓÐÔ±¹¤±àºÅ£¬ÐÕÃû£¬²¿ÃÅÃû³Æ¡£
   
ÏȲé³öÔ±¹¤ÐÕÃû£¬Áìµ¼ÐÕÃû£¬ÈëְʱÆÚ
.
   
ÔÚÌâÄ¿
2
µÄ»ù´¡ÉÏ£¬¶àÔö¼ÓÒ»¸öÈëÖ°ÈÕÆÚÌõ¼þ£¬Ôö¼Ó
deptno
±í£¬Ôö¼ÓÈ¥³ýµÑ¿¨¶û»ýµÄ¹ØÁªÌõ¼þ
select
e1.empno,e1.ename,e2.ename,d.dname from emp e1,emp e2,dept d where
e1.mgr=e2.empno(+) and e1.hiredate<e2.hiredate and e1.deptno=d.deptno;
4
¡¢²é³ö²¿ÃÅÃû³ÆºÍ²¿ÃÅÔ±¹¤ÐÅÏ¢
(
ûÓÐÔ±¹¤µÄ²¿ÃÅÒ²ÐèÒªÌåÏÖ
)
select
d.dname,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno
from
dept d,emp e
where
d.deptno=e.deptno(+);
5
¡¢²é³öְλÊÇ“
CLERK
”µÄÐÕÃû¼°Æ䲿ÃÅÃûºÍ²¿ÃÅÈËÊý
select
e.ename,d.dname,t.c
    from emp e,dept d,(select
deptno,count(empno) c from emp where job='CLERK'
    group by deptno) t
    where e.job='CLERK' AND e.deptno=d.deptno
and e.deptno=t.deptno;
6
¡¢²é³ö×îµÍ¹¤×Ê´óÓÚ
1500
µÄ¸÷ÖÖ¹¤×÷¼°Æä´ÓÊÂÕâЩ¹¤×÷µÄÈ«²¿Ô±¹¤ÈËÊý¡£
ÒÑÖªÌõ¼þ£º
min(sal)>1500
 
6.1
¡¢²é´¦Ã¿¸ö²¿ÃÅÖÐ×îµÍ¹¤×ʵÄÔ±¹¤ÐÅÏ¢¡£
select
deptno,min(sal)  from emp group by
deptno;
6.2
¡¢²é³öְλ¹¤×ʵÄ×îµÍ¹¤×Ê´óÓÚ
1500
µÄËùÓй¤×÷ºÍ¹¤×Ê¡£
Select
job,min(sal) from emp group by job having min(sal)>1500
6.3
¡¢²éѯ³öËùÓÐְ룬ÒÔ¼°´ÓÊÂÕâ¸öְλµÄÈËÊý¡£
Select
job,count(empno) from emp group by job;
6.4
ºÏ²¢³ö×îºó½á¹û£º
Select job,min(sal),count(empno) from
emp group by job having min(sal)>1500


Ïà¹ØÎĵµ£º

³£¼ûOracle HINTµÄÓ÷¨

     [×ÊÁÏÀ´×ÔÓÚORACLEƵµÀ http://oracle.chinaitlab.com/induction/398193.html]
  ¡¡1. /*+ALL_ROWS*/
¡¡¡¡±íÃ÷¶ÔÓï¾ä¿éÑ¡Ôñ»ùÓÚ¿ªÏúµÄÓÅ»¯·½·¨,²¢»ñµÃ×î¼ÑÍÌÍÂÁ¿,ʹ×ÊÔ´ÏûºÄ×îС»¯.
¡¡¡¡ÀýÈç:
¡¡¡¡SELECT /*+ALL_ROWS*/ EMP_NO,EMP_NAM,DAT_IN from BSEMPMS WHERE EMP_NO='SCOTT';
¡¡¡ ......

¡¾×ª¡¿oracle´óÊý¾ÝÁ¿µÄµ¼ÈëºÍµ¼³ö

ÔÚoracleÖÐÅúÁ¿Êý¾ÝµÄµ¼³öÊǽèÖúsqlplusµÄspoolÀ´ÊµÏֵġ£ÅúÁ¿Êý¾ÝµÄµ¼ÈëÊÇͨ¹ýsqlloadÀ´ÊµÏֵġ£
´óÁ¿Êý¾ÝµÄµ¼³ö²¿·ÖÈçÏ£º
/***************************
 *  sql½Å±¾²¿·Ö demo.sql begin
 **************************/
/**************************
 * @author meconsea
 * @date 20050 ......

ÔÚWindows 7Ï°²×°Oracle ½â¾ö·½·¨

Ê×ÏÈÕÒµ½Â·¾¶£º
E:\Oracle Enterprise_Standard Edition_11.1.0.7\database\stage\prereq\db\refhost.xml
ÈçϽڵ㣺
  <!--Microsoft Windows Vista-->
     <OPERATING_SYSTEM>
      <VERSION VALUE="6.0"/>
    </OPERAT ......

oracle³£ÓÃÊý¾ÝÀàÐÍ

author£ºskate
time£º2010/03/03
oracle³£ÓÃÊý¾ÝÀàÐÍ
½ñÌìͬÊÂÎÊЩÊý¾ÝÀàÐ͵ÄÎÊÌ⣬ÓеĻ¹ÕæÓеã¼Ç²»ÇåÁË£¬ÓÚÊǾͼòµ¥×ܽáϳ£ÓõÄÊý¾ÝÀàÐÍÒÔ±¸ÈÕºó²éÓÃ
1¡¢Char
¶¨³¤¸ñʽ×Ö·û´®£¬ÔÚÊý¾Ý¿âÖд洢ʱ²»×ãλÊýÌî²¹¿Õ¸ñ£¬ËüµÄÉùÃ÷·½Ê½ÈçÏÂCHAR(L)£¬LΪ×Ö·û´®³¤¶È£¬
ȱʡΪ1£¬×÷Ϊ±äÁ¿×î´ó32767¸ö×Ö·û£¬×÷ΪÊý¾Ý´æ´¢ÔÚORA ......

oracle³éÈ¡º¯Êý extract()

auhtor£ºskate
time£º2010/03/03
oracle³éÈ¡º¯Êý
½ñÌìÅóÓÑÎʸöÎÊÌ⣬ÈçºÎ³éȡʱ¼äµÄ“Сʱ”
oracleµÄ³éÈ¡º¯ÊýÊÇ extrac()Óï·¨:
SELECT EXTRACT(YEAR from SYSDATE) from DUAL; return Current Year
SELECT EXTRACT(MONTH from SYSDATE) from DUAL; return Current Month
SELECT EXTRACT(DAY from SYSDA ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ