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

SQLÓÅ»¯½éÉÜÒ»

Ò»¡¢Ñ¡Ôñ×îÓÐЧÂʵıíÃû˳Ðò(Ö»ÔÚ»ùÓÚ¹æÔòµÄÓÅ»¯Æ÷ÖÐÓÐЧ)
 
ORACLEµÄ½âÎöÆ÷°´ÕÕ´ÓÓÒµ½×óµÄ˳Ðò´¦Àífrom×Ó¾äÖеıíÃû,Òò´Ëfrom×Ó¾äÖÐдÔÚ×îºóµÄ±í(»ù´¡±í driving table)½«±»×îÏÈ´¦Àí. ÔÚfrom×Ó¾äÖаüº¬¶à¸ö±íµÄÇé¿öÏÂ,Äã±ØÐëÑ¡Ôñ¼Ç¼ÌõÊý×îÉٵıí×÷Ϊ»ù´¡±í.µ±ORACLE´¦Àí¶à¸ö±íʱ, »áÔËÓÃÅÅÐò¼°ºÏ²¢µÄ·½Ê½Á¬½ÓËüÃÇ.Ê×ÏÈ,ɨÃèµÚÒ»¸ö±í(from×Ó¾äÖÐ×îºóµÄÄǸö±í)²¢¶Ô¼Ç¼½øÐÐÅÉÐò,È»ºóɨÃèµÚ¶þ¸ö±í(from×Ó¾äÖÐ×îºóµÚ¶þ¸ö±í),×îºó½«ËùÓдӵڶþ¸ö±íÖмìË÷³öµÄ¼Ç¼ÓëµÚÒ»¸ö±íÖкÏÊʼǼ½øÐкϲ¢.
 
 
ÀýÈç:
 
±í TAB1 16,384 Ìõ¼Ç¼
 
±í TAB2 1 Ìõ¼Ç¼
 
Ñ¡ÔñTAB2×÷Ϊ»ù´¡±í (×îºÃµÄ·½·¨)
 
select count(*) from tab1,tab2 Ö´ÐÐʱ¼ä0.96Ãë
 
Ñ¡ÔñTAB1×÷Ϊ»ù´¡±í (²»¼ÑµÄ·½·¨)
 
select count(*) from tab2,tab1 Ö´ÐÐʱ¼ä26.09Ãë
 
 
Èç¹ûÓÐ3¸öÒÔÉϵıíÁ¬½Ó²éѯ, ÄǾÍÐèҪѡÔñ½»²æ±í(intersection table)×÷Ϊ»ù´¡±í, ½»²æ±íÊÇÖ¸ÄǸö±»ÆäËû±íËùÒýÓõıí.
 
 
ÀýÈç:
 
EMP±íÃèÊöÁËLOCATION±íºÍCATEGORY±íµÄ½»¼¯.
 
SELECT * from LOCATION L , CATEGORY C, EMP E
WHERE E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN
AND E.EMP_NO BETWEEN 1000 AND 2000
 
½«±ÈÏÂÁÐSQL¸üÓÐЧÂÊ
 
SELECT E.CAT_NO from EMP E, LOCATION L , CATEGORY C
WHERE E.EMP_NO BETWEEN 1000 AND 2000
AND E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN
¶þ¡¢WHERE×Ó¾äÖеÄÁ¬½Ó˳Ðò
 
ORACLE²ÉÓÃ×Ô϶øÉϵÄ˳Ðò½âÎöWHERE×Ó¾ä,¸ù¾ÝÕâ¸öÔ­Àí,±íÖ®¼äµÄÁ¬½Ó±ØÐëдÔÚÆäËûWHEREÌõ¼þ֮ǰ, ÄÇЩ¿ÉÒÔ¹ýÂ˵ô×î´óÊýÁ¿¼Ç¼µÄÌõ¼þ±ØÐëдÔÚWHERE×Ó¾äµÄĩβ.
 
ÀýÈç:
 
(µÍЧ,Ö´ÐÐʱ¼ä156.3Ãë)
 
SELECT … from EMP E WHERE SAL > 50000 AND JOB = ‘MANAGER'
AND 25 < (SELECT COUNT(*) from EMP
WHERE MGR=E.EMPNO);
 
(¸ßЧ,Ö´ÐÐʱ¼ä10.6Ãë)
 
SELECT … from EMP E
WHERE 25 < (SELECT COUNT(*) from EMP WHERE MGR=E.EMPNO)
AND SAL > 50000
AND JOB = ‘MANAGER';
 
Èý¡¢SELECT×Ó¾äÖбÜÃâʹÓà ‘ * ‘
 
µ±ÄãÏëÔÚSELECT×Ó¾äÖÐÁгöËùÓеÄCOLUMNʱ,ʹÓö¯Ì¬SQLÁÐÒýÓà ‘*' ÊÇÒ»¸ö·½±ãµÄ·½·¨.²»ÐÒµÄÊÇ,ÕâÊÇÒ»¸ö·Ç³£µÍЧµÄ·½·¨. ʵ¼ÊÉ


Ïà¹ØÎĵµ£º

½ñÌì×°ÁËOracle SQL developer

ÏÂÔغó½âѹËõ¾ÍÄÜÖ±½ÓʹÓÃÁË£¬µ«ÊÇÊÇÖÐÎĵģ¬×ÖÌåÏÔʾµÄ²»ÊǺܺã¬ÓÚÊÇÕÒµ½ÁË»»Ó¢Îĵİ취£º
ÓÉÓÚ1.5°üº¬Á˶àÓïÑÔ½çÃæµÄÖ§³Ö£¬µ«ÊÇËüÊÇͨ¹ýJVMÀ´±æÈÏϵͳÓïÑԵģ¬ËùÒÔµ±ÄãµÄϵͳÓïÑÔΪÖÐÎĵÄʱºò£¬Ëü»áʹÓÃÖÐÎĽçÃæ¡£²»¹ýËüµÄÖÐÎÄ·­Òë²¢²»ÍêÕû£¬¼ÓÉÏĬÈϽçÃæ×ÖÌå̫С£¬Ê¹ÓÃÖÐÎĻῴ×ŷdz£ÄÑÊÜ¡£Èç¹ûÏëʹÓÃÓ¢ÎĽçÃ棬ÐèÒªÐÞ ......

SQLÖÐûÓü¯ºÏµÄminus,ÓÃunionʵÏÖ

ÁгöTableAÖÐÓеĶøTableBÖÐûÓÐ, ÒÔ¼°BÖÐÓжøAÖÐûÓеļǼ£º
ÆäÖÐÁ½¸ö±íµÄ½á¹¹Ïàͬ£¬Ñ¡ÔñµÄKey¿ÉÒÔ¶à¸ö
Select Key from
( select * from TableA
Union select * from TableB
)
group by Key
having count(Key)=1

ÁгöTableAÖÐÓеĶøTableBÖÐûÓеļǼ£º
Select Key from
( (select * from TableA
Un ......

ÐÞ¸Äsql server µÄÁ¬½ÓÊý

 ÈçºÎÐÞ¸ÄSQL ServerµÄÁ¬½ÓÊý
ÎÒ°ÑSQL   Server   7.0µÄÓû§Á¬½ÓÊýÉèΪ1ºó£¬Êý¾Ý¿â¾ÍÔÙÒ²Á¬²»ÉÏÁË£¬ËùÒÔҲû°ì·¨ÐÞ¸ÄÁ¬½ÓÊý  
  ÇëÎÊÓÐʲô°ì·¨ÄÜÐÞ¸ÄÁ¬½ÓÊý£¿
ÔÚserver   µÄÊôÐÔÀïÃæÓиöconnetctions     µÄ  
   
  maximun     concurr ......

sqlÓï¾ä»ù±¾²Ù×÷

1.½¨±íÓï¾ä£ºcreate table
Ó÷¨: create table ±íµÄÃû×Ö (×Ö¶Î1, ×Ö¶Î2,¡£¡£¡£¡£)
¾ÙÀý£ºÀýÈç´´½¨Ò»¸öѧÉú³É¼¨±í£¬°üº¬µÄ×Ö¶ÎÓУ¬Ñ§Éúid£¬ÐÕÃû£¬ÐԱ𣬰༶£¬³É¼¨create table score(
create table score(
 sid nvarchar(10) primary key,
 sname nvarchar(10) not null,
 sex nvarchar(2),
 sc ......

access ·ÖÒ³Óà SQL²éѯÓï¾ä

select top ÿҳÏÔʾµÄ¼Ç¼Êý * from topic where id not in (select top £¨µ±Ç°µÄÒ³Êý-1£©×ÿҳÏÔʾµÄ¼Ç¼Êý id from topic order by id desc) order by id desc
select top ÿҳÏÔʾµÄ¼Ç¼Êý * from topic where id not in (select top £¨µ±Ç°µÄÒ³Êý-1£©×ÿҳÏÔʾµÄ¼Ç¼Êý id from topic order by id desc) ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ