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ªk°Ñ¦Ò¤â¥U

 
µ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 12:59 pm    ¤å³¹¥DÃD: [±ÀÂË]SQL»yªk°Ñ¦Ò¤â¥U ¤Þ¨¥¦^ÂÐ

DB2 ´£¨Ñ¤FÃö³s¦¡¸ê®Æ®wªº¬d¸ß»y¨¥ ¢á¢ß¢Ú (Structured Query Language)¡A¬O¤@ºØ«D±`¤f»y¤Æ¡B¬J©ö¾Ç¤S©öÀ´ªº»yªk¡C¦¹¤@»y¨¥´X¥G¬O¨C­Ó¸ê®Æ®w¨t²Î³£¥²¶·´£¨Ñªº¡A¥Î¥Hªí¥ÜÃö³s¦¡ªº¾Þ§@¡A¥]§t¤F¸ê®Æªº©w¸q¡]¢Ò¢Ò¢Ú¡^¥H¤Î¸ê®Æªº³B²z¡]¢Ò¢Û¢Ú¡^¡CSQL­ì¨Ó«÷¦¨SEQUEL¡A³o»y¨¥ªº­ì«¬¥H¡§¨t²Î R¡§ªº¦W¦r¦b IBM ¸t²ü¦è¹êÅç«Ç§¹¦¨¡A¸g¹LIBM¤º³¡¤Î¨ä¥Lªº³\¦h¨Ï¥Î©Ê¤Î®Ä²v´ú¸Õ¡A¨äµ²ªG¬Û·í¥O¤Hº¡·N¡A¨Ã¨M©w¦b¨t²ÎR ªº§Þ³N°ò¦µo®i¥X¨Ó IBM ªº²£«~¡C¦Ó¥B¬ü°ê°ê®a¼Ð·Ç¾Ç·|¡]ANSI¡^¤Î°ê»Ú¼Ð·Ç¤Æ²Õ´¡]ISO¡^¦b1987¿í´`¤@­Ó´X¥G¬O¥H IBM SQL ¬°°ò¦ªº¼Ð·ÇÃö³s¦¡¸ê®Æ»y¨¥©w¸q¡C ¤@¡B¸ê®Æ©w¸q ¢Ò¢Ò¢Ú¡]Data Definition Language)
¸ê®Æ©w»y¨¥¬O«ü¹ï¸ê®Æªº®æ¦¡©M§ÎºA¤U©w¸qªº»y¨¥¡A¥L¬O¨C­Ó¸ê®Æ®w­n«Ø¥ß®É­Ô®É­º¥ý­n­±¹ïªº¡AÁ|¤Z¸ê®Æ¤À­þ¨Çªí®æÃö«Y¡Bªí®æ¤ºªº¦³¤°麽Äæ¦ì¤¸¥DÁä¡Bªí®æ©Mªí®æ¤§¶¡¤¬¬Û°Ñ¦ÒªºÃö«Yµ¥µ¥¡A³£¬O¦b¶}©lªº®É­Ô©Ò¥²¶·³W¹º¦nªº¡C
¢°¡B«Øªí®æ¡G
CREATE TABLE table_name(
column1 DATATYPE [NOT NULL] [NOT NULL PRIMARY KEY],
column2 DATATYPE [NOT NULL],
...¡^
»¡©ú¡G¡@
DATATYPE --¬O¸ê®Æªº®æ¦¡¡A¸Ô¨£ªí¡C
NUT NULL --¥i¤£¥i¥H¤¹³\¸ê®Æ¦³ªÅªº¡]©|¥¼¦³¸ê®Æ¶ñ¤J¡^¡C
PRIMARY KEY --¬O¥»ªíªº¥DÁä¡C
¢±¡B§ó§ïªí®æ¡@
ALTER TABLE table_name
ADD COLUMN column_name DATATYPE
»¡©ú¡G¼W¥[¤@­ÓÄæ¦ì¡]¨S¦³§R°£¬Y­ÓÄæ¦ì¤¸ªº»yªk¡C
ALTER TABLE table_name
ADD PRIMARY KEY (column_name)
»¡©ú¡G§ó§ïªí±oªº©w¸q§â¬Y­ÓÄæ¦ì³]¬°¥DÁä¡C
ALTER TABLE table_name
DROP PRIMARY KEY (column_name)
»¡©ú¡G§â¥DÁ䪺©w¸q§R°£¡C
¢²¡B«Ø¥ß¯Á¤Þ¡@
CREATE INDEX index_name ON table_name (column_name)
»¡©ú¡G¹ï¬Y­Óªí®æªºÄæ¦ì¤¸«Ø¥ß¯Á¤Þ¥H¼W¥[¬d¸ß®Éªº³t«×¡C
¢³¡B§R°£¡@
DROP table_name
DROP index_name
¤G¡Bªº¸ê®Æ§ÎºA DATATYPEs
smallint
16 ¦ì¤¸¤¸ªº¾ã¼Æ¡C
interger
32 ¦ì¤¸¤¸ªº¾ã¼Æ¡C
decimal(p,s)
p ºë½T­È©M s ¤j¤pªº¤Q¶i¦ì¤¸¾ã¼Æ¡Aºë½T­Èp¬O«ü¥þ³¡¦³´X­Ó¼Æ(digits)¤j¤p­È¡As¬O«ü¤p¼Æ
ÂI«á¦³´X¦ì¼Æ¡C¦pªG¨S¦³¯S§O«ü©w¡A«h¨t²Î·|³]¬° p=5; s=0 ¡C
float
32¦ì¤¸ªº¹ê¼Æ¡C
double
64¦ì¤¸ªº¹ê¼Æ¡C
char(n)
n ªø«×ªº¦r¦ê¡An¤£¯à¶W¹L 254¡C
varchar(n)
ªø«×¤£©T©w¥B¨ä³Ì¤jªø«×¬° n ªº¦r¦ê¡An¤£¯à¶W¹L 4000¡C
graphic(n)
©M char(n) ¤@¼Ë¡A¤£¹L¨ä³æ¦ì¬O¨â­Ó¦r¤¸ double-bytes¡A n¤£¯à¶W¹L127¡C³o­Ó§ÎºA¬O¬°
¤F¤ä´©¨â­Ó¦r¤¸ªø«×ªº¦rÅé¡A¨Ò¦p¤¤¤å¦r¡C
vargraphic(n)
¥iÅܪø«×¥B¨ä³Ì¤jªø«×¬° n ªºÂù¦r¤¸¦r¦ê¡An¤£¯à¶W¹L 2000¡C
date
¥]§t¤F ¦~¥÷¡B¤ë¥÷¡B¤é´Á¡C
time
¥]§t¤F ¤p®É¡B¤ÀÄÁ¡B¬í¡C
timestamp
¥]§t¤F ¦~¡B¤ë¡B¤é¡B®É¡B¤À¡B¬í¡B¤d¤À¤§¤@¬í¡C
¤T¡B¸ê®Æ¾Þ§@ ¢Ò¢Û¢Ú ¡]Data Manipulation Language)
¸ê®Æ©w¸q¦n¤§«á±µ¤U¨Óªº´N¬O¸ê®Æªº¾Þ§@¡C¸ê®Æªº¾Þ§@¤£¥~¥G¼W¥[¸ê®Æ¡]insert)¡B¬d¸ß¸ê®Æ¡]query¡^¡B§ó§ï¸ê®Æ¡]update) ¡B§R°£¸ê®Æ¡]delete¡^¥|ºØ¼Ò¦¡¡A¥H¤U¤À §O¤¶²Ð¥L­Ìªº»yªk¡G
¢°¡B¼W¥[¸ê®Æ¡G
INSERT INTO table_name (column1,column2,...)
valueS ( value1,value2, ...)
»¡©ú¡G
1.­Y¨S¦³«ü©wcolumn ¨t²Î«h·|«öªí®æ¤ºªºÄæ¦ì¤¸¶¶§Ç¶ñ¤J¸ê®Æ¡C
2.Äæ¦ì¤¸ªº¸ê®Æ§ÎºA©M©Ò¶ñ¤Jªº¸ê®Æ¥²¶·§k¦X¡C
3.table_name ¤]¥i¥H¬O´ºÆ[ view_name¡C
INSERT INTO table_name (column1,column2,...)
SELECT columnx,columny,... FROM another_table
»¡©ú¡G¤]¥i¥H¸g¹L¤@­Ó¤l¬d¸ß¡]subquery¡^§â§Oªºªí®æªº¸ê®Æ¶ñ¤J¡C
¢±¡B¬d¸ß¸ê®Æ¡G
°ò¥»¬d¸ß
SELECT column1,columns2,...
FROM table_name
»¡©ú¡G§âtable_name ªº¯S©wÄæ¦ì¤¸¸ê®Æ¥þ³¡¦C¥X¨Ó
SELECT *
FROM table_name
WHERE column1 = xxx
[AND column2 > yyy] [OR column3 <> zzz]
»¡©ú¡G
1.'*'ªí¥Ü¥þ³¡ªºÄæ¦ì¤¸³£¦C¥X¨Ó¡C
2.WHERE ¤§«á¬O±µ±ø¥ó¦¡¡A§â²Å¦X±ø¥óªº¸ê®Æ¦C¥X¨Ó¡C
SELECT column1,column2
FROM table_name
ORDER BY column2 [DESC]
»¡©ú¡GORDER BY ¬O«ü©w¥H¬Y­ÓÄæ¦ì°µ±Æ§Ç¡A[DESC]¬O«ü±q¤j¨ì¤p±Æ¦C¡A­Y¨S¦³«ü©ú¡A«h¬O±q¤p¨ì¤j
±Æ¦C
²Õ¦X¬d¸ß
²Õ¦X¬d¸ß¬O«ü©Ò¬d¸ß±o¸ê®Æ¨Ó·½¨Ã¤£¥u¦³³æ¤@ªºªí®æ¡A¦Ó¬OÁp¦X¤@­Ó¥H¤Wªº
ªí®æ¤~¯à°÷±o¨ìµ²ªGªº¡C
SELECT *
FROM table1,table2
WHERE table1.colum1=table2.column1
»¡©ú¡G
1.¬d¸ß¨â­Óªí®æ¤¤¨ä¤¤ column1 ­È¬Û¦Pªº¸ê®Æ¡C
2.·íµM¨â­Óªí®æ¬Û¤¬¤ñ¸ûªºÄæ¦ì¤¸¡A¨ä¸ê®Æ§ÎºA¥²¶·¬Û¦P¡C
3.¤@­Ó½ÆÂøªº¬d¸ß¨ä°Ê¥Î¨ìªºªí®æ¥i¯à·|«Ü¦h­Ó¡C ¾ã¦X©Êªº¬d¸ß¡G
SELECT COUNT (*)
FROM table_name
WHERE column_name = xxx
»¡©ú¡G
¬d¸ß²Å¦X±ø¥óªº¸ê®Æ¦@¦³´Xµ§¡C
SELECT SUM(column1)
FROM table_name
»¡©ú¡G
1.­pºâ¥XÁ`©M¡A©Ò¿ïªºÄæ¦ì¥²¶·¬O¥i¼Æªº¼Æ¦ì§ÎºA¡C
2.°£¦¹¥H¥~ÁÙ¦³ AVG() ¬O­pºâ¥­§¡¡BMAX()¡BMIN()­pºâ³Ì¤j³Ì¤p­Èªº¾ã¦X©Ê¬d¸ß¡C
SELECT column1,AVG(column2)
FROM table_name
GROUP BY column1
HAVING AVG(column2) > xxx
»¡©ú¡G
1.GROUP BY: ¥Hcolumn1 ¬°¤@²Õ­pºâ column2 ªº¥­§¡­È¥²¶·©M AVG¡BSUMµ¥¾ã¦X©Ê¬d¸ßªºÃöÁä¦r
¤@°_¨Ï¥Î¡C
2.HAVING : ¥²¶·©M GROUP BY ¤@°_¨Ï¥Î§@¬°¾ã¦X©Êªº­­¨î¡C
½Æ¦X©Êªº¬d¸ß
SELECT *
FROM table_name1
WHERE EXISTS (
SELECT *
FROM table_name2
WHERE conditions )
»¡©ú¡G
1.WHERE ªº conditions ¥i¥H¬O¥t¥~¤@­Óªº query¡C
2.EXISTS ¦b¦¹¬O«ü¦s¦b»P§_¡C
SELECT *
FROM table_name1
WHERE column1 IN (
SELECT column1
FROM table_name2
WHERE conditions )
»¡©ú¡G¡@
1. IN «á­±±µªº¬O¤@­Ó¶°¦X¡Aªí¥Ücolumn1 ¦s¦b¶°¦XùØ­±¡C
2. SELECT ¥X¨Óªº¸ê®Æ§ÎºA¥²¶·²Å¦X column1¡C
¨ä¥L¬d¸ß
SELECT *
FROM table_name1
WHERE column1 LIKE 'x%'
»¡©ú¡GLIKE ¥²¶·©M«á­±ªº'x%' ¬Û©IÀ³ªí¥Ü¥H x¬°¶}ÀYªº¦r¦ê¡C
SELECT *
FROM table_name1
WHERE column1 IN ('xxx','yyy',..)
»¡©ú¡GIN «á­±±µªº¬O¤@­Ó¶°¦X¡Aªí¥Ücolumn1 ¦s¦b¶°¦XùØ­±¡C
SELECT *
FROM table_name1
WHERE column1 BETWEEN xx AND yy
»¡©ú¡GBETWEEN ªí¥Ü column1 ªº­È¤¶©ó xx ©M yy ¤§¶¡¡C
¢²¡B§ó§ï¸ê®Æ¡G
UPDATE table_name
SET column1='xxx'
WHERE conditoins
»¡©ú¡G
1.§ó§ï¬Y­ÓÄæ¦ì³]©w¨ä­È¬°'xxx'¡C
2.conditions ¬O©Ò­n²Å¦Xªº±ø¥ó¡B­Y¨S¦³ WHERE «h¾ã­Ó table ªº¨º­ÓÄæ¦ì¤¸³£·|¥þ³¡³Q§ó§ï¡C
¢³¡B§R°£¸ê®Æ¡G
DELETE FROM table_name
WHERE conditions
»¡©ú¡G§R°£²Å¦X±ø¥óªº¸ê®Æ¡C
»¡©ú¡GÃö©óWHERE±ø¥ó«á­±¦pªG¥]§t¦³¤é´Áªº¤ñ¸û¡A¤£¦P¸ê®Æ®w¦³¤£¦Pªº¹Bºâ¦¡¡C¨ãÅé¦p¤U¡G
(1)¦pªG¬OACCESS¸ê®Æ®w¡A«h¬°¡GWHERE mydate>#2000-01-01#
(2)¦pªG¬OORACLE¸ê®Æ®w¡A«h¬°¡GWHERE mydate>cast('2000-01-01' as date)
©Î¡GWHERE mydate>to_date('2000-01-01','yyyy-mm-dd')
¦bDelphi¤¤¼g¦¨¡G
thedate='2000-01-01';
query1.SQL.add('select * from abc where mydate>cast('+''''+thedate+''''+' as date)');
¦pªG¤ñ¸û¤é´Á®É¶¡«¬¡A«h¬°¡G
WHERE mydatetime>to_date('2000-01-01 10:00:01','yyyy-mm-dd hh24:mi:ss')
­ì§@ªÌ¡Gtonny
¨Ó ·½¡GÂà¸ü

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

¾Ç·|VFP¨Ï¥ÎªÌªÀ°Ïªº·j´M,Code¤~·|§ó¦³½ì~
#############################
¦^³»ºÝ
À˵ø·|­û­Ó¤H¸ê®Æ µo°e¨p¤H°T®§
IORI71721



µù¥U®É¶¡: 2009-06-09
¤å³¹: 15


²Ä 2 ¼Ó

µoªíµoªí©ó: ¬P´Á¤G ¤»¤ë 09, 2009 10:57 pm    ¤å³¹¥DÃD: ¤Þ¨¥¦^ÂÐ

·PÁ¤j¤jªº¾ã²z ¨ü¥Î¤£¤Ö
¦^³»ºÝ
À˵ø·|­û­Ó¤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§@