|
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¸ü.
|
¤W¤@½g¥DÃD :: ¤U¤@½g¥DÃD |
µoªí¤H |
¤º®e |
Ruey
µù¥U®É¶¡: 2003-03-12 ¤å³¹: 1698 ¨Ó¦Û: tunglo
²Ä 1 ¼Ó
|
µ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ªº³Ì¥Dnªº°ÝÃ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¦Ó¬On¼g¥X°ª½è¶qªºSQL»y¥y¡A´£°ª¨t²Îªº¥i¥Î©Ê¡C
¦b¦h¼Æ±¡ªp¤U¡AOracle¨Ï¥Î¯Á¤Þ¨Ó§ó§Ö¦a¹M¾äªí¡AÀu¤Æ¾¹¥Dn®Ú¾Ú©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¥un³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²{¦bn¬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¼Ëªº¡An¨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¤@©wn¤ß¤¤¦³©³¡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ªGn¨Ï¥Î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 EXISTSn¤ñNOT IN¬d¸ß®Ä²v§ó°ª¡C _________________ #############################
§Ö¼Ö¶ý«}¨t¦C©¯ºÖ¦v°t,³Ü¤Q¥þÂû´ö~ì¨Ó©¯ºÖ¨º»ò²³æ!!
¾Ç·|VFP¨Ï¥ÎªÌªÀ°Ïªº·j´M,Code¤~·|§ó¦³½ì~
############################# |
|
¦^³»ºÝ |
|
|
|
|
±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§@
|