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ƵµÀ 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ÖÐÅúÁ¿Êý¾ÝµÄµ¼³öÊǽèÖúsqlplusµÄspoolÀ´ÊµÏֵġ£ÅúÁ¿Êý¾ÝµÄµ¼ÈëÊÇͨ¹ýsqlloadÀ´ÊµÏֵġ£
´óÁ¿Êý¾ÝµÄµ¼³ö²¿·ÖÈçÏ£º
/***************************
* sql½Å±¾²¿·Ö demo.sql begin
**************************/
/**************************
* @author meconsea
* @date 20050 ......
Ê×ÏÈÕÒµ½Â·¾¶£º
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 ......
author£ºskate
time£º2010/03/03
oracle³£ÓÃÊý¾ÝÀàÐÍ
½ñÌìͬÊÂÎÊЩÊý¾ÝÀàÐ͵ÄÎÊÌ⣬ÓеĻ¹ÕæÓеã¼Ç²»ÇåÁË£¬ÓÚÊǾͼòµ¥×ܽáϳ£ÓõÄÊý¾ÝÀàÐÍÒÔ±¸ÈÕºó²éÓÃ
1¡¢Char
¶¨³¤¸ñʽ×Ö·û´®£¬ÔÚÊý¾Ý¿âÖд洢ʱ²»×ãλÊýÌî²¹¿Õ¸ñ£¬ËüµÄÉùÃ÷·½Ê½ÈçÏÂCHAR(L)£¬LΪ×Ö·û´®³¤¶È£¬
ȱʡΪ1£¬×÷Ϊ±äÁ¿×î´ó32767¸ö×Ö·û£¬×÷ΪÊý¾Ý´æ´¢ÔÚORA ......
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 ......