|
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 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Ó¸ê®Æ®wn«Ø¥ß®ÉԮɺ¥ý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¡AY¨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() ¬Opºâ¥§¡¡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ªº±ø¥ó¡BY¨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¤~·|§ó¦³½ì~
############################# |
|
¦^³»ºÝ |
|
|
IORI71721
µù¥U®É¶¡: 2009-06-09 ¤å³¹: 15
²Ä 2 ¼Ó
|
µoªí©ó: ¬P´Á¤G ¤»¤ë 09, 2009 10:57 pm ¤å³¹¥DÃD: |
|
|
·PÁ¤j¤jªº¾ã²z ¨ü¥Î¤£¤Ö |
|
¦^³»ºÝ |
|
|
|
|
±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§@
|