VFP ·R¥ÎªÌªÀ°Ï ­º­¶ VFP ·R¥ÎªÌªÀ°Ï
¥»°Q½×°Ï¬° Visual Foxpro ·R¥ÎªÌ¸gÅç¥æ¬yªº¦a¤è, ½Ð¦h¦h§Q¥Î"·j´M"ªº¥\¯à, ¥ý¬d¬Ý¬Ý¦³µL«e¨Ò¥i´`, ¦pªGÁÙ¦³¤£À´ªº¦Aµo°Ý. ³¡¥÷¥DÃD¦³ªþ¥[ÀÉ®×, ¶·¥ýµù¥U¦¨¬°ªÀ°Ï©~¥Á¤~¥i¥H¤U¸ü.
 
 ±`¨£°ÝÃD±`¨£°ÝÃD   ·j´M·j´M   ·|­û¦Cªí·|­û¦Cªí   ·|­û¸s²Õ·|­û¸s²Õ   ·|­ûµù¥U·|­ûµù¥U 
 ­Ó¤H¸ê®Æ­Ó¤H¸ê®Æ   µn¤JÀˬd±zªº¨p¤H°T®§µn¤JÀˬd±zªº¨p¤H°T®§   µn¤Jµn¤J

[±ÀÂË]SQL »y¥y©Ê¯à½Õ¾ã­ì«h

 
µoªí·s¥DÃD   ¦^ÂÐ¥DÃD    VFP ·R¥ÎªÌªÀ°Ï ­º­¶ -> SQL °Q½×°Ï
¤W¤@½g¥DÃD :: ¤U¤@½g¥DÃD  
µoªí¤H ¤º®e
Ruey



µù¥U®É¶¡: 2003-03-12
¤å³¹: 1698
¨Ó¦Û: tunglo

²Ä 1 ¼Ó

µoªíµoªí©ó: ¬P´Á¤» ¤G¤ë 14, 2004 1:04 pm    ¤å³¹¥DÃD: [±ÀÂË]SQL »y¥y©Ê¯à½Õ¾ã­ì«h ¤Þ¨¥¦^ÂÐ

§@ªÌ¡G¥ÛÅ»ñ´
¤@¡B°ÝÃDªº´£¥X
¦bÀ³¥Î¨t²Î¶}µoªì´Á¡A¥Ñ©ó¶}µo¸ê®Æ®w¸ê®Æ¤ñ¸û¤Ö¡A¹ï©ó¬d¸ßSQL»y¥y¡A½ÆÂøµø¹Ïªºªº½s¼gµ¥Åé·|¤£¥XSQL»y¥y¦UºØ¼gªkªº©Ê¯àÀu¦H¡A¦ý¬O¦pªG±NÀ³¥Î¨t²Î´£¥æ¹ê»ÚÀ³¥Î«á¡AÀHµÛ¸ê®Æ®w¤¤¸ê®Æªº¼W¥[¡A¨t²Îªº¦^À³³t«×´N¦¨¬°¥Ø«e¨t²Î»Ý­n¸Ñ¨Mªº³Ì¥D­nªº°ÝÃD¤§¤@¡C¨t²ÎÀu¤Æ¤¤¤@­Ó«Ü­«­nªº¤è­±´N¬OSQL»y¥yªºÀu¤Æ¡C¹ï©ó®ü¶q¸ê®Æ¡A¦H½èSQL»y¥y©MÀu½èSQL»y¥y¤§¶¡ªº³t«×®t§O¥i¥H¹F¨ì¤W¦Ê­¿¡A¥i¨£¹ï©ó¤@­Ó¨t²Î¤£¬O²³æ¦a¯à¹ê²{¨ä¥\¯à´N¥i¡A¦Ó¬O­n¼g¥X°ª½è¶qªºSQL»y¥y¡A´£°ª¨t²Îªº¥i¥Î©Ê¡C
¦b¦h¼Æ±¡ªp¤U¡AOracle¨Ï¥Î¯Á¤Þ¨Ó§ó§Ö¦a¹M¾äªí¡AÀu¤Æ¾¹¥D­n®Ú¾Ú©w¸qªº¯Á¤Þ¨Ó´£°ª©Ê¯à¡C¦ý¬O¡A¦pªG¦bSQL»y¥yªºwhere¤l¥y¤¤¼gªºSQL¥N½X¤£¦X²z¡A´N·|³y¦¨Àu¤Æ¾¹§R¥h¯Á¤Þ¦Ó¨Ï¥Î¥þªí±½´y¡A¤@¯ë´N³oºØSQL»y¥y´N¬O©Ò¿×ªº¦H½èSQL»y¥y¡C¦b½s¼gSQL»y¥y®É§Ú­ÌÀ³²M·¡Àu¤Æ¾¹®Ú¾Ú¦óºØ­ì«h¨Ó§R°£¯Á¤Þ¡A³o¦³§U©ó¼g¥X°ª©Ê¯àªºSQL»y¥y¡C
¤G¡BSQL»y¥y½s¼gª`·N°ÝÃD
¤U­±´N¬Y¨ÇSQL»y¥yªºwhere¤l¥y½s¼g¤¤»Ý­nª`·Nªº°ÝÃD§@¸Ô²Ó¤¶²Ð¡C¦b³o¨Çwhere¤l¥y¤¤¡A§Y¨Ï¬Y¨Ç¦C¦s¦b¯Á¤Þ¡A¦ý¬O¥Ñ©ó½s¼g¤F¦H½èªºSQL¡A¨t²Î¦b¹B¦æ¸ÓSQL»y¥y®É¤]¤£¯à¨Ï¥Î¸Ó¯Á¤Þ¡A¦Ó¦P¼Ë¨Ï¥Î¥þªí±½´y¡A³o´N³y¦¨¤F¦^À³³t«×ªº·¥¤j­°§C¡C
1. IS NULL »P IS NOT NULL
¤£¯à¥Înull§@¯Á¤Þ¡A¥ô¦ó¥]§tnull­Èªº¦C³£±N¤£·|³Q¥]§t¦b¯Á¤Þ¤¤¡C§Y¨Ï¯Á¤Þ¦³¦h¦C³o¼Ëªº±¡ªp¤U¡A¥u­n³o¨Ç¦C¤¤¦³¤@¦C§t¦³null¡A¸Ó¦C´N·|±q¯Á¤Þ¤¤±Æ°£¡C¤]´N¬O»¡¦pªG¬Y¦C¦s¦bªÅ­È¡A§Y¨Ï¹ï¸Ó¦C«Ø¯Á¤Þ¤]¤£·|´£°ª©Ê¯à¡C
¥ô¦ó¦bwhere¤l¥y¤¤¨Ï¥Îis null©Îis not nullªº»y¥yÀu¤Æ¾¹¬O¤£¤¹³\¨Ï¥Î¯Á¤Þªº¡C
2. Áp±µ¦C
¹ï©ó¦³Áp±µªº¦C¡A§Y¨Ï³Ì«áªºÁp±µ­È¬°¤@­ÓÀRºA­È¡AÀu¤Æ¾¹¬O¤£·|¨Ï¥Î¯Á¤Þªº¡C§Ú­Ì¤@°_¨Ó¬Ý¤@­Ó¨Ò¤l¡A°²©w¦³¤@­Ó¾¤uªí¡]employee¡^¡A¹ï©ó¤@­Ó¾¤uªº©m©M¦W¤À¦¨¨â¦C¦s©ñ¡]FIRST_NAME©MLAST_NAME¡^¡A²{¦b­n¬d¸ß¤@­Ó¥s¤ñº¸.§JªL¹y¡]Bill Cliton¡^ªºÂ¾¤u¡C
¤U­±¬O¤@­Ó±Ä¥ÎÁp±µ¬d¸ßªºSQL»y¥y¡A
select * from employss
where
first_name||''||last_name ='Beill Cliton';
¤W­±³o±ø»y¥y§¹¥þ¥i¥H¬d¸ß¥X¬O§_¦³Bill Cliton³o­Ó­û¤u¡A¦ý¬O³oùػݭnª`·N¡A¨t²ÎÀu¤Æ¾¹¹ï°ò©ólast_name³Ð«Øªº¯Á¤Þ¨S¦³¨Ï¥Î¡C
·í±Ä¥Î¤U­±³oºØSQL»y¥yªº½s¼g¡AOracle¨t²Î´N¥i¥H±Ä¥Î°ò©ólast_name³Ð«Øªº¯Á¤Þ¡C
Select * from employee
where
first_name ='Beill' and last_name ='Cliton';
¹J¨ì¤U­±³oºØ±¡ªp¤S¦p¦ó³B²z©O¡H¦pªG¤@­ÓÅܼơ]name¡^¤¤¦s©ñµÛBill Cliton³o­Ó­û¤uªº©m¦W¡A¹ï©ó³oºØ±¡ªp§Ú­Ì¤S¦p¦óÁקK¥þµ{¹M¾ä¡A¨Ï¥Î¯Á¤Þ©O¡H¥i¥H¨Ï¥Î¤@­Ó¨ç¼Æ¡A±NÅܼÆname¤¤ªº©m©M¦W¤À¶}´N¥i¥H¤F¡A¦ý¬O¦³¤@ÂI»Ý­nª`·N¡A³o­Ó¨ç¼Æ¬O¤£¯à§@¥Î¦b¯Á¤Þ¦C¤W¡C¤UÄѬOSQL¬d¸ß¸}¥»¡G
select * from employee
where
first_name = SUBSTR('&&name',1,INSTR('&&name',' ')-1)
and
last_name = SUBSTR('&&name',INSTR('&&name¡¦,' ')+1)
3. ±a³q°t²Å¡]%¡^ªºlike»y¥y
¦P¼Ë¥H¤W­±ªº¨Ò¤l¨Ó¬Ý³oºØ±¡ªp¡C¥Ø«eªº»Ý¨D¬O³o¼Ëªº¡A­n¨D¦b¾¤uªí¤¤¬d¸ß¦W¦r¤¤¥]§tclitonªº¤H¡C¥i¥H±Ä¥Î¦p¤Uªº¬d¸ßSQL»y¥y¡G
select * from employee where last_name like '%cliton%';
³oùإѩó³q°t²Å¡]%¡^¦b·j´Mµü­º¥X²{¡A©Ò¥HOracle¨t²Î¤£¨Ï¥Îlast_nameªº¯Á¤Þ¡C¦b«Ü¦h±¡ªp¤U¥i¯àµLªkÁקK³oºØ±¡ªp¡A¦ý¬O¤@©w­n¤ß¤¤¦³©³¡A³q°t²Å¦p¦¹¨Ï¥Î·|­°§C¬d¸ß³t«×¡CµM¦Ó·í³q°t²Å¥X²{¦b¦r¦ê¨ä¥L¦ì¸m®É¡AÀu¤Æ¾¹´N¯à§Q¥Î¯Á¤Þ¡C¦b¤U­±ªº¬d¸ß¤¤¯Á¤Þ±o¨ì¤F¨Ï¥Î¡G
select * from employee where last_name like 'c%';
4. Order by»y¥y
ORDER BY»y¥y¨M©w¤FOracle¦p¦ó±Nªð¦^ªº¬d¸ßµ²ªG±Æ§Ç¡COrder by»y¥y¹ï­n±Æ§Çªº¦C¨S¦³¤°»ò¯S§Oªº­­¨î¡A¤]¥i¥H±N¨ç¼Æ¥[¤J¦C¤¤¡]¶HÁp±µ©ÎªÌªþ¥[µ¥¡^¡C¥ô¦ó¦bOrder by»y¥yªº«D¯Á¤Þ¶µ©ÎªÌ¦³­pºâ¹Bºâ¦¡³£±N­°§C¬d¸ß³t«×¡C
¥J²ÓÀˬdorder by»y¥y¥H§ä¥X«D¯Á¤Þ¶µ©ÎªÌ¹Bºâ¦¡¡A¥¦­Ì·|­°§C©Ê¯à¡C¸Ñ¨M³o­Ó°ÝÃDªº¿ìªk´N¬O­«¼gorder by»y¥y¥H¨Ï¥Î¯Á¤Þ¡A¤]¥i¥H¬°©Ò¨Ï¥Îªº¦C«Ø¥ß¥t¥~¤@­Ó¯Á¤Þ¡A¦P®ÉÀ³µ´¹ïÁקK¦border by¤l¥y¤¤¨Ï¥Î¹Bºâ¦¡¡C
5. NOT
§Ú­Ì¦b¬d¸ß®É¸g±`¦bwhere¤l¥y¨Ï¥Î¤@¨ÇÅÞ¿è¹Bºâ¦¡¡A¦p¤j©ó¡B¤p©ó¡Bµ¥©ó¥H¤Î¤£µ¥©óµ¥µ¥¡A¤]¥i¥H¨Ï¥Îand¡]»P¡^¡Bor¡]©Î¡^¥H¤Înot¡]«D¡^¡CNOT¥i¥Î¨Ó¹ï¥ô¦óÅÞ¿è¹Bºâ²Å¸¹¨ú¤Ï¡C¤U­±¬O¤@­ÓNOT¤l¥yªº¨Ò¤l¡G
... where not (status ='VALID')
¦pªG­n¨Ï¥ÎNOT¡A«hÀ³¦b¨ú¤Ïªºµu»y«e­±¥[¤W¬A©·¡A¨Ã¦bµu»y«e­±¥[¤WNOT¹Bºâ²Å¡CNOT¹Bºâ²Å¥]§t¦b¥t¥~¤@­ÓÅÞ¿è¹Bºâ²Å¤¤¡A³o´N¬O¤£µ¥©ó¡]<>¡^¹Bºâ²Å¡C´«¥y¸Ü»¡¡A§Y¨Ï¤£¦b¬d¸ßwhere¤l¥y¤¤Å㦡¦a¥[¤JNOTµü¡ANOT¤´¦b¹Bºâ²Å¤¤¡A¨£¤U¨Ò¡G
... where status <>'INVALID';
¦A¬Ý¤U­±³o­Ó¨Ò¤l¡G
select * from employee where salary<>3000;
¹ï³o­Ó¬d¸ß¡A¥i¥H§ï¼g¬°¤£¨Ï¥ÎNOT¡G
select * from employee where salary<3000 or salary>3000;
ÁöµM³o¨âºØ¬d¸ßªºµ²ªG¤@¼Ë¡A¦ý¬O²Ä¤GºØ¬d¸ß¤è®×·|¤ñ²Ä¤@ºØ¬d¸ß¤è®×§ó§Ö¨Ç¡C²Ä¤GºØ¬d¸ß¤¹³\Oracle¹ïsalary¦C¨Ï¥Î¯Á¤Þ¡A¦Ó²Ä¤@ºØ¬d¸ß«h¤£¯à¨Ï¥Î¯Á¤Þ¡C
6. IN©MEXISTS
¦³®É­Ô·|±N¤@¦C©M¤@¨t¦C­È¬Û¤ñ¸û¡C³Ì²³æªº¿ìªk´N¬O¦bwhere¤l¥y¤¤¨Ï¥Î¤l¬d¸ß¡C¦bwhere¤l¥y¤¤¥i¥H¨Ï¥Î¨âºØ®æ¦¡ªº¤l¬d¸ß¡C
²Ä¤@ºØ®æ¦¡¬O¨Ï¥ÎIN¾Þ§@²Å¡G
... where column in(select * from ... where ...);
²Ä¤GºØ®æ¦¡¬O¨Ï¥ÎEXIST¾Þ§@²Å¡G
... where exists (select 'X' from ...where ...);
§Ú¬Û«Hµ´¤j¦h¼Æ¤H·|¨Ï¥Î²Ä¤@ºØ®æ¦¡¡A¦]¬°¥¦¤ñ¸û®e©ö½s¼g¡A¦Ó¹ê»Ú¤W²Ä¤GºØ®æ¦¡­n»·¤ñ²Ä¤@ºØ®æ¦¡ªº®Ä²v°ª¡C¦bOracle¤¤¥i¥H´X¥G±N©Ò¦³ªºIN¾Þ§@²Å¤l¬d¸ß§ï¼g¬°¨Ï¥ÎEXISTSªº¤l¬d¸ß¡C
²Ä¤GºØ®æ¦¡¤¤¡A¤l¬d¸ß¥H¡¥select 'X'¶}©l¡C¹B¥ÎEXISTS¤l¥y¤£ºÞ¤l¬d¸ß±qªí¤¤©â¨ú¤°»ò¸ê®Æ¥¦¥u¬d¬Ýwhere¤l¥y¡C³o¼ËÀu¤Æ¾¹´N¤£¥²¹M¾ä¾ã­Óªí¦Ó¶È®Ú¾Ú¯Á¤Þ´N¥i§¹¦¨¤u§@¡]³oùØ°²©w¦bwhere»y¥y¤¤¨Ï¥Îªº¦C¦s¦b¯Á¤Þ¡^¡C¬Û¹ï©óIN¤l¥y¨Ó»¡¡AEXISTS¨Ï¥Î¬Û³s¤l¬d¸ß¡Aºc³y°_¨Ó­n¤ñIN¤l¬d¸ß§xÃø¤@¨Ç¡C
³q¹L¨Ï¥ÎEXIST¡AOracle¨t²Î·|­º¥ýÀˬd¥D¬d¸ß¡AµM«á¹B¦æ¤l¬d¸ßª½¨ì¥¦§ä¨ì²Ä¤@­Ó¤Ç°t¶µ¡A³o´N¸`¬Ù¤F®É¶¡¡COracle¨t²Î¦b°õ¦æIN¤l¬d¸ß®É¡A­º¥ý°õ¦æ¤l¬d¸ß¡A¨Ã±NÀò±oªºµ²ªG¦Cªí¦s©ñ¦b¦b¤@­Ó¥[¤F¯Á¤ÞªºÁ{®Éªí¤¤¡C¦b°õ¦æ¤l¬d¸ß¤§«e¡A¨t²Î¥ý±N¥D¬d¸ß±¾°_¡A«Ý¤l¬d¸ß°õ¦æ§¹²¦¡A¦s©ñ¦bÁ{®Éªí¤¤¥H«á¦A°õ¦æ¥D¬d¸ß¡C³o¤]´N¬O¨Ï¥ÎEXISTS¤ñ¨Ï¥ÎIN³q±`¬d¸ß³t«×§Öªº­ì¦]¡C
¦P®ÉÀ³ºÉ¥i¯à¨Ï¥ÎNOT EXISTS¨Ó¥N´ÀNOT IN¡A¾¨ºÞ¤GªÌ³£¨Ï¥Î¤FNOT¡]¤£¯à¨Ï¥Î¯Á¤Þ¦Ó­°§C³t«×¡^¡ANOT EXISTS­n¤ñNOT IN¬d¸ß®Ä²v§ó°ª¡C

_________________
#############################
§Ö¼Ö¶ý«}¨t¦C©¯ºÖ¦v°t,³Ü¤Q¥þÂû´ö~­ì¨Ó©¯ºÖ¨º»ò²³æ!!

¾Ç·|VFP¨Ï¥ÎªÌªÀ°Ïªº·j´M,Code¤~·|§ó¦³½ì~
#############################
¦^³»ºÝ
À˵ø·|­û­Ó¤H¸ê®Æ µo°e¨p¤H°T®§
±q¤§«eªº¤å³¹¶}©lÅã¥Ü:   
µoªí·s¥DÃD   ¦^ÂÐ¥DÃD    VFP ·R¥ÎªÌªÀ°Ï ­º­¶ -> SQL °Q½×°Ï ©Ò¦³ªº®É¶¡§¡¬° ¥x¥_®É¶¡ (GMT + 8 ¤p®É)
²Ä1­¶(¦@1­¶)

 
«e©¹:  
±z µLªk ¦b³o­Óª©­±µoªí¤å³¹
±z µLªk ¦b³o­Óª©­±¦^ÂФ峹
±z µLªk ¦b³o­Óª©­±½s¿è¤å³¹
±z µLªk ¦b³o­Óª©­±§R°£¤å³¹
±z µLªk ¦b³o­Óª©­±¶i¦æ§ë²¼
±z µLªk ¦b³o­Óª©­±ªþ¥[ÀÉ®×
±z µLªk ¦b³o­Óª©­±¤U¸üÀÉ®×


Powered by phpBB © 2001, 2005 phpBB Group
¥¿Å餤¤å»y¨t¥Ñ phpbb-tw ºûÅ@»s§@