ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[±Ð¾Ç] (PHP+IIS+MsSQL±Ð¾Ç²Ä30½g) »{ÃÑDML«ü¥O ¤W½g

[±Ð¾Ç] (PHP+IIS+MsSQL±Ð¾Ç²Ä30½g) »{ÃÑDML«ü¥O ¤W½g

¨C¤@ºØ¸ê®Æ®w³£¦³¤@ºØºÞ²zºûÅ@ªºµ{¦¡»y¨¥¡A¥Ø«e¥«­±¤Wªº¸ê®Æ®w©Ò¨Ï¥Îªºµ{¦¡»y¨¥°ò¦´X¥G³£¨Ó¦ÛSQL¡GStructured Query Language¡]µ²ºc¤Æ¬d¸ß»y¨¥¡^¡C

SQL»y¨¥³Ìªì¬O¥ÑIBM©ó1970¦~¥Nªì´Á©Ò¶}µoªº¡A¥Î©óÃö³s¦¡¸ê®Æ®w ¡]Relational Databases¡^¤¤ªº¤@ºØ¸ê®Æ®w¬d¸ß»y¨¥¡A§Q¥Î SQL ¥i¥H¥Î¨Ó©w¸q¸ê®Æ®wµ²ºc¡B«Ø¥ß¸ê®Æªí¡B«ü©wÄæ¦ì«¬ºA¡Bªø«×»P·s¼W¸ê®Æ¡B­×§ï¸ê®Æ¡B§R°£¸ê®Æ¡B¬d¸ß¸ê®Æ¡A¬O¤@ºØ¸ê®Æ®wÀ³¥Îªº¼Ð·Ç»y¨¥¡C

¼Ð·ÇªºSQL»y¨¥³W®æ¬O¥ÑANSI¡]¬ü°ê°ê®a¼Ð·Ç¾Ç·|¡^»PISO¡]°ê»Ú¼Ð·Ç²Õ´¡^³o¨â­Ó²Õ´©Ò±À°Ê¨î­qªº¡A³Ìªì¬O¦b1986¦~¥ÑANSI¨î©w¨ä¼Ð·Ç¤Æ³W®æ¡AÀH«á¦b1992¦~®É¦A«×±À¥X·sªºª©¥»¡A¤]´N¬O±`Å¥¨ì¤H®a»¡ªº¡uSQL92¡v¡C

¥Ø«e¥«­±¤Wªº¸ê®Æ®w©Ò¨Ï¥Îªºµ{¦¡»y¨¥ÁöµM³£¥HSQL¬°¥D¡A¦ý¬O«o¨S¦³¥ô¦ó¤@®a¼t°Óªº¸ê®Æ®w§¹¾ãªº¤ä´©©ÎÀ³¥ÎSQL¡C¨C®a¸ê®Æ®w¼t°Ó¬°¤F°Ó·~©Î²£«~À³¥Îªº¦Ò¶q¡A³£¦³¤Ö³¡¥÷ªº¥\¯à¤£¤ä´©¡A©Î¦Û¦æ¼W¥[¤Ö³¡¤ÀªºSQL©µ¦ù¥\¯à¡A¨Ò¦p¡AMicrosoft SQL Server´N¤£ºâ¬O¨Ï¥Î¼Ð·ÇªºSQL¡A Microsoft ¹ï¼Ð·ÇªºSQL°µ¤F³\¦hªº­×§ï»P¥\¯à©µ¦ù¡A¦]¦¹¡A§Ú­ÌMicrosoft SQL ServerªºÀ³¬°Transact-SQL¡A¦b¥»³¹¤¤¡A§Ú­Ì¤´¥H¡uSQL¡vºÙ¤§¡C

¾¨ºÞ¨C®a¸ê®Æ®w¼t°Óªº¸ê®Æ®wºÞ²zºûÅ@»y¨¥¤£ºÉ¬Û¦P¡A¦ý¬O¡A¥¦­ÌÁÙ¬O·½¦Û¼Ð·ÇªºSQL¡A©Ò¥H¦bSQLªº°ò¦¨Ï¥Î¤´¬O¾A¥Î©ó¦U®a¼t°Ó¸ê®Æ®w¡C

¥»½g¤º®e¬Ò¥HSQL¸ê®Æ®w¬°¨Ï¥Î¹ï¶H¡A¥D¸ê®ÆÀÉleave.mdf¡B°O¿ýÀÉleave.ldf¡A½Ð±z±NÀɮ׽ƻs¨ì±zªºµwºÐ¸Ì¨Ãªþ¥[¨ìSQL Server¤¤¡A¥H¤è«K±z¾Þ§@½d¨Ò®É¨Ï¥Î¡C
LEAVE.rar (92.64 KB)

¤p»x±NSQLªº±Ô­z²Õ¦¨­n¯À·§¤À¦p¤U¡G

  • ¸ê®Æ©w¸q»y¨¥¡GData Definition Language¡]DDL¡^¡A¥Î¨Ó«Ø¥ß»P§R°£¸ê®Æ®w¡B¸ê®Æªí¡Kµ¥ªºSQL«ü¥O¡C
  • ¸ê®Æ³B²z»y¨¥¡GData Manipulation Language¡]DML¡^¡A¥Î¨Ó·s¼W¡B§R°£¡B­×§ï»P¬d¸ß¸ê®Æ®w¤º¸ê®Æ°O¿ýªºSQL«ü¥O¡C
  • ¸ê®ÆºÞ²z»y¨¥¡GData Control Language¡]DML¡^¡A¥Î¨Ó³]©w©ÎÅܧó¸ê®Æ®w¨Ï¥ÎªÌ¡]¨¤¦â¡^ªºÅv­­¡C
  • ¹w¦sµ{§Ç¡GStored Procedure¡ASQL Server¤¤¹w¥ý³]¥ßªºSQL«ü¥O¡C
  • ¨ä¥L²Õ¦¨­n¯À



»{ÃÑDML«ü¥O
DML¡G¸ê®Æ³B²z»y¨¥¡A¬°SQL¤¤ªº¸ê®Æ³B²z«ü¥O¸s¡A¥]¬ASelect¡]¬d¸ß¡^¡BInsert¡]·s¼W¡^¡BDelete¡]§R°£¡^¡BUpdate¡]­×§ï¡^µ¥«ü¥O¡C


Select«ü¥O
Select«ü¥O±z¤£­¯¥Í§a¡I¦b¤W´X½g¤¤¡A¤p»x´N°µ¤F«Ü¦hªº¤¶²Ð¡A²{¦b¤p»x­n¤¶²Ðªº¬OSelect«ü¥Oªº¶i¶¥À³¥Î¡G


¸ó¸ê®Æªí¬d¸ß
¤p»x²{¦bÁ|¤@­Ó¹ê¨Ò¨Ó»¡©ú¡G¦bLEAVE¸ê®Æ®w¤¤§t¦³¡u­û¤u¦W³æ¡v¡B¡u¥ð°²°O¿ý¡v³o¨â­Ó¸ê®Æªí¡G


²{¦b§Ú­n±q¡u­û¤u¦W³æ¡v¸ê®Æªí¤¤¬D¥X¡u¥ð°²°O¿ý¡v¸ê®Æªí¤º¥ð°²¡u¤Ñ¼Æ¡v¤j©ó¤T¤Ñªº­û¤u¸ê®Æ¡A¦pªG³æ¯Âªº¨Ï¥Î¤@­ÓSelect«ü¥O¬OµLªk¹F¨ì³o­Ó­n¨Dªº¡A¦]¦¹§Ú­Ì¥²¶·¨Ï¥Î¨â­ÓSelect«ü¥O¡I­º¥ý§Ú­Ì¥ý§Q¥Î¤U¦C±Ô­z§ä¥X¡u¥ð°²°O¿ý¡v¸ê®Æªí¤º¥ð°²¡u¤Ñ¼Æ¡v¤j©ó¤T¤Ñªº­û¤u¸ê®Æ¡G
  1. Select ©m¦W  From  ¥ð°²°O¿ý Where  ¤Ñ¼Æ>3
½Æ»s¥N½X
³o¼Ëªº¬d¸ßµ²ªG´N¦¨¬°¤@­Ó¡uµêÀÀ¡v¸ê®Æªí¡]¶°¡^¡A±µµÛ§Ú­Ì­n§Q¥Î³o­Ó¡uµêÀÀ¡v¸ê®Æªí¡]¶°¡^ªº¸ê®Æ¬°¶i¶¥¿z¿ï±ø¥ó­È¡A¿z¿ï¥X¡u­û¤u¦W³æ¡v¸ê®Æªí¤¤³QÂI¦W¥ð°²¶W¹L3¤Ñªº­û¤u¸ê®Æ¡G
  1. Select  *  From  ­û¤u¦W³æ where ©m¦W In
  2. (Select ©m¦W From ¥ð°²°O¿ý Where ¤Ñ¼Æ>3)
½Æ»s¥N½X
¦p¦¹¡A´N¥i¥H±q¡u­û¤u¦W³æ¡v¸ê®Æªí¤¤¬D¥X¡u¥ð°²°O¿ý¡v¸ê®Æªí¤º¥ð°²¡u¤Ñ¼Æ¡v¤j©ó¤T¤Ñªº­û¤u¸ê®Æ¡I



¤W¹Ï¤¤¡G¥¼¥[®Ø³¡¤ÀªºSelect«ü¥O±Ô­z¤¤§t¦³¥t¤@¬q³Q®Ø½u®Ø°_¨ÓªºSelect«ü¥O±Ô­z¡A³oºØÂùSelect«ü¥O²Õ¦Xªº±Ô­zºÙ¤§¬°¡uSubQuery¡v¡I
·í§Ú­Ì±N³o¡u¶i¶¥¬d¸ß«ü¥O¡vÀ³¥Î©óµ{¦¡¤¤±N¥i±o¨ì¹Ï9-3ªºµ²ªG¡A³Q±ø¦C¥X¨Óªº­û¤u³£¬O¥ð°²¤Ñ¼Æ¶W¹L¤T¤ÑªÌ¡C
  1. <?
  2.     $conn = mssql_connect("127.0.0.1", "sa", "12345");
  3.     if(@mssql_select_db("LEAVE", $conn))
  4.      {
  5.       //¸ê®Æ®w¦s¦b,«Ø¥ßSQL©R¥O¦r¦ê
  6.       $SQL="Select  *  From  ­û¤u¦W³æ where ©m¦W In" .
  7.             "(Select ©m¦W From ¥ð°²°O¿ý Where ¤Ñ¼Æ>3)";
  8.       //±N¦^¶Çµ²ªG¦s©ñ©óÅܼƤ¤
  9.       $datalist=mssql_query($SQL);
  10.       //¨ú±oÄæ¦ì¼Æ¶q
  11.       $fieldnum=mssql_num_fields($datalist);
  12. ?>
  13. <html>
  14. <head>
  15. <title>½d¨Òex09_01</title>
  16. </head>
  17. <center><table border=1 width=100%>
  18. <?
  19. /**************¦C¥XÄæ¦ì©ïÀY***************/
  20. echo "<tr>";     
  21.       for ($x=0 ;$x<$fieldnum;$x++)
  22.          {
  23.           echo "<td>" . mssql_field_name($datalist,$x) . "</td>";
  24.          }
  25. echo "</tr>";   
  26. /**************¦C¥XÄæ¦ì¸ê®Æ***************/
  27.       //±N¸ê®Æ¿ýÂà´«¬°Äæ¦ì°}¦C¶°¦X
  28.       while ($fielddatas=mssql_fetch_array($datalist))
  29.           {
  30.              //¿é¥XÄæ¦ì¸ê®Æ
  31.              echo "<tr>";
  32.              for ($x=0;$x<$fieldnum;$x++)
  33.                 {
  34.                  echo "<td>" . $fielddatas[$x] . "</td>";
  35.                 }
  36.              echo "</tr>";  
  37.           }
  38. ?>
  39. <table></body>
  40. </html>
  41. <?
  42.      }
  43.     else
  44.      {
  45.       echo "¸ê®Æ®w¤£¦s¦b";
  46.      }
  47. ?>
½Æ»s¥N½X
¤ß¦w¡A¥­¦w   
µoªí¥ô¦óµL·N¸qªº·PÁ©ÎÄé¤ô¤å³¹¤@«ß¬å,¬Ã±¤±b¸¹,½Ð¸Ô¾\ª©³W!!

¤À²Õ²Î­p Group By
¦b¡u¥ð°²°O¿ý¡v¸ê®Æªí¤º¡A§Ú­Ì¥i¥Hµo²{­û¤u¥ð°²ªº¦¸¼Æ¥i¯à¤£¥u¤@¦¸¡A¤]³\½Ð¤F¤@¦¸¯f°²¡A¹L¤F´X¤Ñ¤S½Ð¤F¨Æ°²¡A¦]¦¹¥ð°²ªº°O¿ý´N¤£¥u¤@¦¸¡G


¦pªG§Ú­Ì§Æ±æ±oª¾­û¤uªºÁ`¥ð°²¤Ñ¼Æ¨º§Ú­Ì¥i¥H¨Ï¥Î¡uSUM¡v¨ç¼Æ¨Ó²Î­p¡G
  1. Select Sum(¤Ñ¼Æ) AS ­û¤u¥ð°²Á`¤Ñ¼Æ From  ¥ð°²°O¿ý
½Æ»s¥N½X
¥i¬O§Ú­Ì±o¨ìªº¬O¥þ³¡­û¤uªº¥ð°²¤Ñ¼Æ£«¡A¨º§Ú§Æ±æ¤À§O±oª¾¨C¤@¦ì­û¤uªºÁ`¥ð°²¤Ñ¼Æ¡A¨º¤S§ï¦p¦ó³B²z¡H³o­Ó®É­Ô§Ú­Ì´N¥i¥H§Q¥Î¡uGroup By¡v«ü¥O¨Ó¶i¦æ¤À²Õ²Î­p¡G
  1. Select ©m¦W,Sum(¤Ñ¼Æ) AS ¥ð°²Á`¤Ñ¼Æ From  ¥ð°²°O¿ý Group By ©m¦W
½Æ»s¥N½X
°õ¦æ¤W¦Cªº«ü¥O±Ô­z«á¡Aµ²ªG¦p¤U¹Ï¡G


¤À²Õ²Î­p¨Ã¤£­­©w¨Ï¥Î©ó³æ¤@Äæ¦ì¡A¸ê®Æªí¤¤ªº¨C¤@­ÓÄæ¦ì³£¥i¦¨¬°¤À²Õªº¨Ì¾Ú¡A¦P®É¡A§Ú­Ì¤]¥i¥H¨Ï¥Î¦h­ÓÄæ¦ì¨Ó§@¬°¤À²Õªº¨Ì¾Ú¡A¨Ò¦p¤U¦Cªº¤À²Õ«ü¥O±Ô­z¡G


¤ß¦w¡A¥­¦w   
µoªí¥ô¦óµL·N¸qªº·PÁ©ÎÄé¤ô¤å³¹¤@«ß¬å,¬Ã±¤±b¸¹,½Ð¸Ô¾\ª©³W!!

TOP

¤À²Õ¿z¿ï
Group By«ü¥O¤F¥i¥H¥Î¨Ó±Æ§Ç¥~¡A¤]¥i¥H¦P®É¥[¤W±Æ§Ç«ü¥O¨Ó¶i¦æ¸ê®Æ±Æ§Ç¡A¨Ò¦p¡G§Ú­Ì¥H¡u°²§O¡v¬°¤À²Õ¨Ì¾Ú¡A§Q¥ÎOrder By«ü¥O°w¹ï¥ð°²¤Ñ¼Æ¨Ó±Æ§Ç¡G
  1. Select°²§O,Sum(¤Ñ¼Æ) AS ¥ð°²Á`¤Ñ¼Æ From  ¥ð°²°O¿ý Group By°²§O Order By Sum(¤Ñ¼Æ)
½Æ»s¥N½X
°£¤F±Æ§Ç¤§¥~¡A§Ú­ÌÁÙ¥i¥[¤WWhere«ü¥O¥ý¶i¦æ¸ê®Æ¿z¿ï«á¦A¶i¦æ¤À²Õ»P±Æ§Ç¡G
  1. Select°²§O,Sum(¤Ñ¼Æ) AS ¥ð°²Á`¤Ñ¼Æ From  ¥ð°²°O¿ý  Where ¦~=86 Group By°²§O Order By Sum(¤Ñ¼Æ)
½Æ»s¥N½X
¤W¨â¦¡ªº±Ô­z°õ¦æµ²ªG¦p¤U¹Ï¡A­n¯S§Oª`·N¨ì¤@ÂI¡G¨Ï¥ÎWhere«ü¥O¶i¦æ¿z¿ï¡A¥u¯à°÷¦b­ì©l¸ê®Æªº±¡ªp¤U¶i¦æ¿z¿ïªº°Ê§@¡A¤]´N¬O»¡¡A¿z¿ï¸ê®Æªº°Ê§@¥²¶·µo¥Í©ó¤À²Õ°Ê§@¤§«e¡A¤£¯à¦b¤À²Õ°Ê§@§¹¦¨¤§«á¤~¨Ï¥ÎWhere«ü¥O¶i¦æ¤À²Õµ²ªGªº¿z¿ï¡I

¹Ï¥ª¬°¤À²Õ»P±Æ§Ç¡F¹Ï¥k¬°¸ê®Æ¿z¿ï«á¦A¶i¦æ¤À²Õ»P±Æ§Ç¡C


¦b¤À²Õ°Ê§@§¹¦¨¤§«á¡A§Ú­ÌÁÙ­n¹ï¤À²Õµ²ªGªº¸ê®Æ¶i¦æ¿z¿ï®É¡A§Ú­Ì¸Ó¦p¦ó³B²z¡H¤Á°O¡G¤£¯à¨Ï¥ÎWhere«ü¥O¡I§_«h±z·|¨£¨ì¤U¦Cªº¿ù»~°T®§¡G



¦pªG¦b¤À²Õ°Ê§@§¹¦¨¤§«á¤~¶i¦æ¸ê®Æ¿z¿ïªº°Ê§@¥²¶·¨Ï¥Î¡uHaving¡v«ü¥O¡A¨Ò¦p¤U¨Ò¡G
  1.       //¸ê®Æ®w¦s¦b,«Ø¥ßSQL©R¥O¦r¦ê
  2.       $SQL="Select ¦~,©m¦W,°²§O,sum(¤Ñ¼Æ) AS ¦~¥ð°²¼Æ From ¥ð°²°O¿ý " .
  3.      "Group By ¦~,©m¦W,°²§O HAVING ¦~=86";
  4.       //±N¦^¶Çµ²ªG¦s©ñ©óÅܼƤ¤
  5.       $datalist=mssql_query($SQL);
  6.       //¨ú±oÄæ¦ì¼Æ¶q
  7.       $fieldnum=mssql_num_fields($datalist);
½Æ»s¥N½X



¨º§Ú¥i¤£¥i¥H§ó§ï¿z¿ïªº±ø¥ó­È¡H¥i¥H¡A¤£¹L±z¤£¥i¨Ï¥Î¡uµêÀÀ¡vÄæ¦ì¬°¿z¿ïªº®Ú¾Ú¡A¦p¤U¨Ò¡G
  1. Select ¦~,©m¦W,°²§O,sum(¤Ñ¼Æ) AS ¦~¥ð°²¼Æ  From ¥ð°²°O¿ý
  2. Group By ¦~,©m¦W,°²§O Having ¦~¥ð°²¼Æ>2  
½Æ»s¥N½X
¤W¦¡¤¤ªº¡u¦~¥ð°²¼Æ¡vÁöµM¬O¡uSum(¤Ñ¼Æ)¡vªº­pºâµ²ªG¡A¦ý¥¦¬O¤@­Ó¡uµêÀÀ¡vÄæ¦ì¡A¤£¬O¸ê®Æ®w¤¤ªº¯u¹êÄæ¦ì¡A©Ò¥H¤£¯à¥Î¨Ó°µ¬°¿z¿ïªº®Ú¾Ú¡C



¦pªG±z­n¨Ï¥Î¡u¦~¥ð°²¼Æ¡vÄæ¦ì°µ¬°¿z¿ï®Ú¾Ú¡A¥²¶·¼¶¼g¦¨¤U¦¡¡G
  1. SQL="Select ¦~,©m¦W,°²§O,sum(¤Ñ¼Æ) AS ¦~¥ð°²¼Æ From ¥ð°²°O¿ý " &_
  2.      "Group By ¦~,©m¦W,°²§O Having sum(¤Ñ¼Æ)>2"
  3. CMD.CommandText=SQL
½Æ»s¥N½X
°õ¦æµ²ªG¦p¤U¹Ï¡G
¤ß¦w¡A¥­¦w   
µoªí¥ô¦óµL·N¸qªº·PÁ©ÎÄé¤ô¤å³¹¤@«ß¬å,¬Ã±¤±b¸¹,½Ð¸Ô¾\ª©³W!!

TOP

        ÀR«ä¦Û¦b : ¥¬¬I¦p¼½ºØ¡A¥HÅw³ß¤ß´þ¼íºØ¤l¡A¤~·|µoªÞ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD