ʹÓÃALTER TABLE Óï¾ä£¬Óû§¿ÉÒÔÔö¼Ó£¬É¾³ý»ò¸Ä±äÁлòÓò£¬Ò²¿ÉÒÔÔö¼Ó»òɾ³ýÒ»¸öÔ¼Êø¡£»¹¿ÉÒÔΪij¸öÓòÉ趨ȱʡֵ£¬µ«ÊÇÒ»´ÎÖ»ÄÜÐÞ¸ÄÒ»¸öÓò¡£¼ÙÉèÎÒÃÇÓÐÒ»¸ö¼ÇÕ˵¥µÄÊý¾Ý¿â£¬¶øÎÒÃÇÏëÔڹ˿ÍÊý¾Ý±íÖÐÔö¼ÓÒ»¸öÓò£¬Õâʱ¿ÉÒÔʹÓÃALTER TABLE Óï¾ä£¬ÔÚÆäADD COLUMN ×Ó¾äºóдÉÏÓòµÄÃû³Æ¡¢Êý¾ÝÀàÐͺÍÊý¾ÝµÄ´óС£¨Èç¹ûÐèÒªµÄ»°£©¡£

ALTER TABLE tblCustomers
ADD COLUMN Address TEXT(30)
Òª¸Ä±äÓòµÄÊý¾ÝÀàÐÍ»ò´óС£¬¿ÉÒÔʹÓÃALTER COLUMN×Ӿ䣬ÔÚºóÃæ¼ÓÉÏÆÚÍûµÄÊý¾ÝÀàÐͺÍÊý¾ÝµÄ´óС¡£

ALTER TABLE tblCustomers
ALTER COLUMN Address TEXT(40)
Èç¹ûÐèÒª¸Ä±äÓòµÄÃû³Æ£¬Ôò±ØÐëɾ³ý¸ÃÓò²¢ÖØд´½¨¡£É¾³ýÒ»¸öÓòҪʹÓÃDROP COLUMN ×Ӿ䣬ÔÚÆäºó¸úÉÏÓòµÄÃû³Æ¡£

ALTER TABLE tblCustomers
DROP COLUMN Address
×¢ÒâʹÓÃÕâÖÖ·½·¨½«»áɾ³ý¸ÃÓòµÄÏÖ´æÊý¾Ý¡£Èç¹ûÐèÒª±£´æÕâЩÊý¾Ý£¬ÔòÓû§ÐèÒªÔÚAccessµÄÓû§½çÃæµÄÉè¼ÆģʽÖиıä¸ÃÓòµÄÃû³Æ£¬»òÕß±àд´úÂ뽫ÏÖ´æµÄÊý¾Ý±£´æÔÚÒ»¸öÁÙʱµÄ±íÖÐÈ»ºó½«ÆäÌí¼Óµ½¸ÄÃûºóµÄ±íÖС£

ȱʡֵÊÇÖ¸ÔÚ±íÖÐÔö¼Óмͼ²¢ÇÒûÓÐΪ¸ÃÁи³ÖµÊ±×Ô¶¯Ìî³äµ½¸ÃÓòÖеÄÖµ¡£ÎªÄ³ÓòÉèÖÃȱʡֵ£¬ÒªÔÚ¶¨ÒåÓòµÄÀàÐͺóʹÓÃʹÓÃDEFAULT¹Ø¼ü×Ö£¬²»¹ÜÊÇʹÓÃADD COLUMN»ò ALTER COLUMN ×Ӿ䡣

ALTER TABLE tblCustomers
ALTER COLUMN Address TEXT(40) DEFAULT Unknown
×¢Òâȱʡֵ²¢²»Ê¹Óõ¥ÒýºÅ°üº¬£¬Èç¹ûÓÃÁ˵¥ÒýºÅ£¬ÔòÒýºÅÒ²»á²åÈëµ½¼Ç¼ÖС£ÔÚCREATE TABLEÓï¾äÖÐÒ²¿ÉÒÔʹÓÃDEFAULT¹Ø¼ü×Ö¡£

CREATE TABLE tblCustomers (
CustomerID INTEGER CONSTRAINT PK_tblCustomers
PRIMARY KEY,
[Last Name] TEXT(50) NOT NULL,
[First Name] TEXT(50) NOT NULL,
Phone TEXT(10),
Email TEXT(50),
Address TEXT(40) DEFAULT Unknown)

×¢Ò⣺ DEFAULT Óï¾äÖ»ÓÐÔÚJet OLE DB providerºÍADOÖпÉÒÔÖ´ÐУ¬ÔÚAccess SQL ViewµÄÓû§½çÃæÖÐʹÓý«»á·µ»Ø´íÎóÐÅÏ¢¡£

ÏÂÃæµÄ²¿·Ö½«ÌÖÂÛÈçºÎÔÚALTER TABLEÓï¾äÖÐʹÓÃÔ¼Êø¡£Òª»ñµÃ¸üÏêϸµÄÓйØALTER TABLEµÄ˵Ã÷£¬ÇëÔÚOffice ÖúÊÖÖлòÔÚMicrosoft Access °ïÖúµÄ»Ø´ðÏòµ¼µÄ±êÇ©Ò³ÖÐÊäÈëALTER TABLE £¬È»ºóµ¥»÷²éÕÒ¡£

Ô¼Êø
ÔÚ¡¶Access 2000µÄ»ù´¡Microsoft Jet SQL¡·Ò»ÎÄÖУ¬ÎÒÃÇÌÖÂÛÁ˽¨Á¢±íÖ®¼äµÄÁªÏµµÄÔ¼Êø·½·¨¡£Ô¼ÊøÒ²ÄÜÓÃÓÚ½¨Á¢Ö÷¼üºÍ²Î¿¼ÍêÕûÐÔ£¬À´ÏÞÖƲåÈëµ½Ò»¸öÓòÖеÄÊý¾ÝÖµ¡£Í¨³££¬Ô¼Êø¿ÉÒÔÓÃÓÚ±£³ÖÓû§Êý¾Ý¿âÖеÄÊý¾ÝÍêÕûÐÔºÍÒ»ÖÂÐÔ¡£

¹²ÓÐÁ½ÖÖÀàÐ͵ÄÔ¼Êø£ºµ¥Êý¾ÝÓò£¨»ò³ÆÓò¼¶µÄ£©µÄÔ¼ÊøºÍ¶àÊý¾ÝÓò£¨»ò³Æ±í¼¶µÄ£©µÄÔ¼Êø¡£Á½ÖÖÔ¼Êø¶¼¿ÉÒÔÓÃÔÚCREATE TABLE »ò ALTER TABLE Óï¾äÖС£

µ¥ÓòµÄÔ¼Êø£¬Ò²¾ÍÊÇͨ³£Ëù˵µÄÁ춵ÄÔ¼Êø£¬ÊÇÔÚÓò¼°ÆäÊý¾ÝÀàÐͶ¨ÒåºóÕë¶Ô¸ÃÓò¶¨ÒåµÄ¡£ÏÂÃæÎÒÃÇʹÓÃÓû§±í£¬ÔÚCustomerIDÓòÉú³ÉÒ»¸öµ¥ÓòµÄÖ÷¼ü¡£Ôö¼ÓÔ¼Êøʱ£¬ÔÚÓòÃûºóʹÓÃCONSTRAINT¹Ø¼ü×Ö¡£

ALTER TABLE tblCustomers
ALTER COLUMN CustomerID INTEGER
CONSTRAINT PK_tblCustomers PRIMARY KEY

×¢ÒâÕâÀï¸ø³öÁËÔ¼ÊøµÄÃû³Æ¡£Óû§»¹¿ÉÒÔÔÚ¶¨ÒåÖ÷¼üʱʹÓüò³Æ¶øÊ¡ÂÔCONSTRAINT×Ӿ䡣

ALTER TABLE tblCustomers
ALTER COLUMN CustomerID INTEGER PRIMARY KEY

È»¶ø£¬Ê¹Óüò³ÆµÄ·½·¨½«µ¼ÖÂAccessËæ»úµÄÉú³ÉÔ¼ÊøµÄÃû³Æ£¬´Ó¶øʹµÃÔÚ´úÂëÖÐÄÑÒÔÒýÓá£ËùÒÔ£¬×îºÃ¸øÔ¼ÊøÖƶ¨Ãû³Æ¡£

Ҫɾ³ýÒ»¸öÔ¼Êø£¬¿ÉÒÔÔÚALTER TABLE Óï¾äÖÐʹÓÃDROP CONSTRAINT ×Ӿ䣬²¢¸ø³öÔ¼ÊøµÄÃû³Æ¡£

ALTER TABLE tblCustomers
DROP CONSTRAINT PK_tblCustomers

Ô¼Êø»¹¿ÉÒÔÓÃÀ´¸øÓòÏÞÖÆÔÊÐíÖµ¡£Óû§¿ÉÒÔ½«ÏÞÖÆÖµÉèΪ·Ç¿Õ£¨NOT NULL£©»òΨһ£¨ UNIQUE£©£¬»òÕ߶¨ÒåÒ»¸ö¼ìÑéÐÔµÄÔ¼Êø£¬¸ÃÔ¼ÊøÖ¸Ò»ÖÖ¿ÉÒÔÓ¦ÓÃÓÚij¸öÓòµÄ¹æÔò¡£±ÈÈçÓû§Ï£ÍûÏÞÖÆÐÕºÍÃûµÄÓòÊÇΨһµÄ£¬¾ÍÒâζ×ÅÔÚ±íÖÐÓÀÔ¶²»»áÓÐÁ½¸öÏàͬÐÕÃûµÄ¼Ç¼´æÔÚ¡£ÕâÊÇÒòΪÕâÖÖÔ¼ÊøÊǶàÓòµÄÐԵģ¬ÊÇÔÚ±íµÄ¼¶±ð¶¨ÒåµÄ£¬¶ø·ÇÓòµÄ¼¶±ð¡£Ê¹ÓÃADD CONSTRAINT×Ó¾ä¿ÉÒÔ¶¨ÒåÒ»¸ö¶àÓòµÄÁÐ±í¡£

ALTER TABLE tblCustomers
ADD CONSTRAINT CustomerNames UNIQUE
([Last Name], [First Name])

×¢Ò⣺ ÎÒÃÇÔÚÕâÀïÖ»ÊÇʾ·¶Ò»ÏÂÈçºÎʹÓÃÔ¼Êø£¬¶øÔÚʵ¼ÊµÄÓ¦ÓóÌÐòÖУ¬Óû§¿ÉÄܲ¢²»Ï£Íû³¹µ×ÏÞÖÆÐÕÃûµÄΨһÐÔ¡£

¼ìÑéÐÔÔ¼ÊøÊÇÒ»ÖÖеÄÇ¿ÓÐÁ¦µÄSQLÌØÐÔ£¬Ëüͨ¹ýÒ»¸ö±í´ïʽ´Ó¶øÔÊÐíÓû§ÔÚ±íÖÐÌí¼ÓÊý¾ÝºÏ·¨ÐÔ¼ìÑ飬¸Ã±í´ïʽ¿ÉÒÔÖ¸ÏòÒ»¸öµ¥Óò£¬Ò²¿ÉÒÔÖ¸Ïò¿çÔ½Ò»¸ö»ò¶à¸ö±íµÄ¶à¸öÓò¡£±ÈÈçÓû§Ï£ÍûÈ·¶¨ÊäÈëµ½·¢Æ±¼Ç¼ÖеÄÊýÖµÊÇ·ñ×ÜÊÇ´óÓÚ0£¬Ôò¿ÉÒÔÔÚALTER TABLEÓï¾äµÄADD CONSTRAINT×Ó¾äÖж¨ÒåÒ»¸öCHECK¹Ø¼ü×Ö¡£

ALTER TABLE tblInvoices
ADD CONSTRAINT CheckAmount
CHECK (Amount > 0)

×¢Ò⣺ ¼ìÑéÐÔÔ¼ÊøÓï¾äÖ»ÄÜͨ¹ýJet OLE DB providerºÍADOÀ´Ö´ÐУ¬ÔÚAccess SQL ViewµÄÓû§½çÃæÖÐʹÓý«·µ»Ø´íÎóÐÅÏ¢¡£¶øÇÒ£¬ÒªÉ¾³ýÒ»¸ö¼ìÑéÐÔÔ¼Êø£¬Ò²±ØÐëÔÚJet OLE DB provider ºÍADOÖÐÖ´ÐÐDROP CONSTRAINTÓï¾ä¡£ÁíÍ⣬Èç¹ûÓû§ÒѾ­¶¨ÒåÁËÒ»¸ö¼ìÑéÐÔÔ¼Êø£º (1) ÔÚAccessµÄÓû§½çÃæÖв¢²»»áÏÔʾΪһ¸öºÏ·¨ÐÔ¹æÔò£¬(2) Óû§Ò²²»ÄÜÔڸýçÃæÖж¨ÒåºÏ·¨ÐÔÎı¾µÄÊôÐÔ£¬·ñÔò½«¸ø³öÒ»°ãÐÔ´íÎóÐÅÏ¢£¬(3) ÔÚÓû§Í¨¹ýADOʹÓÃDROP CONSTRAINTÓï¾ä֮ǰ£¬²»Äܹ»Í¨¹ýAccessµÄÓû§½çÃæ»òÕßÔÚ´úÂëÖÐɾ³ýÊý¾Ý±í¡£

ÓÃÀ´¶¨ÒåÒ»¸ö¼ìÑéÐÔÔ¼ÊøµÄ±í´ïʽҲ¿ÉÒÔÓÃÀ´Ö¸Ïòͬһ¸ö±íÖеĶà¸öÓò£¬ÉõÖÁÊÇÆäËû±íÖеÄÓò¡£ÆäÖпÉÒÔʹÓÃÈκÎÔÚMicrosoft Jet SQL ÖкϷ¨µÄ²Ù×÷·û£¬±ÈÈçSELECT Óï¾ä¡¢ÊýѧÔËËã·û¡¢ÒÔ¼°¼¯ºÏº¯ÊýµÈ¡£ÓÃÀ´¶¨Òå¼ìÑéÐÔÔ¼ÊøµÄ±í´ïʽµÄ³¤¶È²»Äܳ¬¹ý64¸ö×Ö·û¡£

ÉèÏëÓû§Ï£ÍûÔÚ½«¹Ë¿Í¼ÓÈëµ½Customers ±í֮ǰ¼ì²éÿ¸ö¹Ë¿ÍµÄÐÅÓöî¶È¡£Ôò¿ÉÒÔʹÓôøÓÐADD COLUMN ºÍCONSTRAINT ×Ó¾äµÄALTER TABLE Óï¾äÉú³ÉÒ»¸öÔ¼Êø£¬¸ÃÔ¼Êø½«²éÕÒÔÚCreditLimit±íÖеÄÖµÀ´ÑéÖ¤¹Ë¿ÍµÄÐÅÓöî¶È¡£ÏÂÃæµÄSQLÓï¾ä½«Éú³ÉÒ»¸ötblCreditLimit ±í£¬È»ºó½«CustomerLimitÓò¼ÓÈëµ½tblCustomers ±íÖУ¬²¢½«¼ìÑéÐÔÔ¼Êø¼Óµ½tblCustomers±í£¬×îºó¶Ô¸Ã¼ìÑéÐÔÔ¼Êø½øÐвâÊÔ¡£

CREATE TABLE tblCreditLimit (
Limit DOUBLE)
INSERT INTO tblCreditLimit
VALUES (100)
ALTER TABLE tblCustomers
ADD COLUMN CustomerLimit DOUBLE
ALTER TABLE tblCustomers
ADD CONSTRAINT LimitRule
CHECK (CustomerLimit <= (SELECT Limit
FROM tblCreditLimit))
UPDATE TABLE tblCustomers
SET CustomerLimit = 200
WHERE CustomerID = 1

×¢Ò⣺µ±Óû§Ö´ÐÐUPDATE TABLEÓï¾äʱ£¬½«±»Ìáʾ¸üÐÂʧ°Ü£¬ÒòΪ¸ÃÓï¾äÎ¥·´Á˼ìÑéÐÔÔ¼Êø¡£Èç¹ûÓû§Ê¹ÓÃСÓÚµÈÓÚ100µÄÖµÀ´¸üÐÂCustomerLimitÓò£¬¾ÍÄܹ»³É¹¦¡£

Ô¼Êø-II
¼¶ÁªÐÔ¸üкÍɾ³ý
Ô¼Êø»¹¿ÉÒÔÓÃÀ´ÔÚÊý¾Ý¿âÖеıí¼ä½¨Á¢²Î¿¼ÍêÕûÐÔ¡£¾ßÓвο¼ÍêÕûÐÔÒâζ×ÅÊý¾ÝÊÇÒ»ÖµĺÍδ±»ÆÆ»µµÄ¡£±ÈÈ磬Èç¹ûɾ³ýÁËÒ»¸ö¹Ë¿ÍµÄ¼Ç¼µ«ÊǸù˿͵ÄÔ˵¥¼Ç¼ÈԾɴæÔÚÊý¾Ý¿âÖУ¬ÕâʱÊý¾Ý¾ÍÊÇ·ÇÒ»Öµģ¬¼´Ö¸Êý¾Ý¿âÖдæÔÚ×ÅÒ»¸ö¹ÂÁ¢µÄ¹Ë¿ÍÔ˵¥¼Ç¼¡£²Î¿¼ÍêÕûÐÔÊÇÔÚÓû§½¨Á¢¸÷¸ö±í¼äµÄ¹Øϵʱ½¨Á¢µÄ¡£³ýÁ˽¨Á¢²Î¿¼ÍêÕûÐÔÖ®Í⣬Óû§»¹¿ÉÒÔʹÓü¶ÁªÐÔ¸üкÍɾ³ýÀ´È·±£Ï໥²Î¿¼µÄ±í±£³Öͬ²½¡£ÀýÈ磬һµ©¶¨ÒåÁ˼¶ÁªÐÔ¸üкÍɾ³ý£¬µ±Óû§É¾³ý¹Ë¿Í¼Ç¼ʱ£¬¸Ã¹Ë¿ÍµÄÔ˵¥¼Ç¼Ҳ½«×Ô¶¯É¾³ý¡£

ҪʹÓü¶ÁªÐÔ¸üкÍɾ³ý£¬Óû§¿ÉÒÔÔÚALTER TABLE Óï¾äµÄCONSTRAINT×Ó¾äÖÐʹÓÃON UPDATE CASCADE ºÍ/»ò ON DELETE CASCADE ¹Ø¼ü×Ö¡£×¢ÒâËûÃǶ¼±ØÐëÊÇÓ¦ÓÃÓÚÍâ¼üµÄ¡£

ALTER TABLE tblShipping
ADD CONSTRAINT FK_tblShipping
FOREIGN KEY (CustomerID) REFERENCES
tblCustomers (CustomerID)
ON UPDATE CASCADE
ON DELETE CASCADE

Íâ¼ü
ÔÚ´¦ÀíÍâ¼üʱ£¬ÓйؿìËÙÍâ¼ü£¨fast foreign key£©µÄ¸ÅÄîÊǺÜÓÐÓõġ£¿ìËÙÍâ¼ü¼´ÊÇÒ»ÖÖûÓÐË÷ÒýµÄÍâ¼ü¡£¾¡¹ÜÕâÕ§ÌýÆðÀ´Óе㲻ºÏÀí£¬µ«È´¿ÉÒԵõ½ºÜºÃµÄ½âÊÍ¡£ÔÚȱʡÇé¿öÏ£¬Ò»µ©¶¨ÒåÁËÒ»¸öÍâ¼ü£¬½«»á×Ô¶¯Éú³ÉÒ»¸ö»ùÓÚ¸ÃÍâ¼üÖÐÁеÄË÷Òý£¬ÕâÔںܶàÇé¿öÏÂÌá¸ßÁËÖ´Ðб£³Ö²Î¿¼ÍêÕûÐԵIJÙ×÷µÄ±íÏÖ¡£È»¶ø£¬Èç¹ûÔÚ¶¨ÒåÍâ¼üµÄÓòÖдæÔÚÐí¶àÖظ´Öµ£¬Íâ¼üË÷Òý½«»áÓ°ÏìÔö¼ÓºÍɾ³ýÊý¾ÝʹµÃЧÂÊ¡£Òª·ÀÖ¹»ùÓÚÍâ¼üµÄË÷ÒýµÄÉú³É£¬ÎÒÃÇ¿ÉÒÔÔÚ¶¨ÒåÍâ¼üÊÇNO INDEX¹Ø¼ü×Ö¡£

ALTER TABLE tblInvoices
ADD CONSTRAINT FK_tblInvoices
FOREIGN KEY NO INDEX (CustomerID) REFERENCES
tblCustomers (CustomerID)
ON UPDATE CASCADE
ON DELETE CASCADE

×¢Òâ: ¿ìËÙÍâ¼üÓï¾äÖ»ÄÜͨ¹ýJet OLE DB provider ºÍ ADOÀ´Ö´ÐС£ÔÚAccess SQL ViewµÄÓû§½çÃæÖÐʹÓý«·µ»Ø´íÎóÐÅÏ¢¡£¶øÇÒ£¬ÒªÉ¾³ýÒ»¸ö¿ìËÙÍâ¼ü£¬Ò²±ØÐëÔÚJet OLE DB provider ºÍADOÖÐÖ´ÐÐDROP CONSTRAINTÓï¾ä¡£

ÔڼǼ°´ÐòÅÅÁеÄÊý¾Ý¿âÓ¦ÓÃÖУ¬¿ìËÙÍâ¼üÒ²½«·¢»Ó×÷ÓᣱÈÈçÕâÀïÓÐÒ»¸öÓÃÀ´±æ±ðËù¸ú×ٵĹ˿ÍÀàÐ굀 CustomerTypesµÄ±í£¬Ò»¸öCustomer ±íºÍÒ»¸öOrders ±í¡£ÔÚCustomerTypes±íÖÐÓÐ10ÐУ¬ÔÚ Customer ±íÖÐÓÐ100000ÐУ¬ÔÚOrders±íÖÐÓÐ350000ÐС£Õâʱ²ÉÓÃÔÚCustomers ±íÖÐÖ¸ÏòCustomerTypes±íÖеÄÖ÷¼üµÄ¿ìËÙÍâ¼ü½«ÊǺܺõÄÑ¡Ôñ£¬ÒòΪÔÚ100000ÐмǼÍâ×î¶àÖ»ÓÐ10ÐÐΨһµÄ¼Ç¼¡£ÕâʱË÷Òý¶ÔÓÚÌáÈ¡Êý¾ÝºÁÎÞÓô¦£¬²¢ÇÒÔÚCustomerTypeÖвåÈ롢ɾ³ýºÍ¸üÐÂÊý¾ÝÊdzÉΪÀÛ׸¡£

¶øÁíÒ»·½Ã棬¿ìËÙÍâ¼üÓ¦ÓÃÓÚOrders ±íÖеÄCustomerID ÁкܿÉÄÜÊÇûÓÐÓô¦µÄ£¬ÒòΪÄÇЩֵÿһ¸ö¶¼´ú±íÁ˲»Í¬µÄ¿Í»§£¬ËùÒÔÒ»°ãÊÇΨһµÄ¡£ÔÚÕâÖÖÇé¿öÏ£¬Ê¹ÓÃͨ³£µÄ´øÓÐË÷ÒýµÄÍâ¼ü½«»áºÜÓÐñÔÒ棬ÕâÊÇÒòΪËüÊÇÓ¦ÓÃÔÚÁ¬½áºÍÆäËû²éÕÒ¹æÔòÉϵġ£

×¢Ò⣺ ¾¡¹ÜÔÚ±¾½ÚµÄ´ó²¿·ÖÀý×ÓÖÐʹÓõÄÊÇALTER TABLEÓï¾ä£¬µ«ÊÇËùÓÐÕâЩ¶¼ÊÇ¿ÉÒÔдÔÚCREATE TABLE Óï¾äÖеġ£

ÒªÁ˽âÓйØCONSTRAINT×Ó¾äµÄ¸ü¶àÐÅÏ¢£¬ÇëÔÚOffice ÖúÊÖÖлòÔÚMicrosoft Access °ïÖúµÄ»Ø´ðÏòµ¼µÄ±êÇ©Ò³ÖÐÊäÈëALTER TABLE £¬È»ºóµ¥»÷²éÕÒ¡£

Êý¾ÝÀàÐÍ
ÔÚŬÁ¦Ê¹»ùJetÊý¾ÝÒýÇæµÄAccessÓ¦ÓóÌÐòÒ×ÓÚÏòÄÇЩ»ùÓÚMicrosoft SQL Server »ò MSDEµÄÓ¦ÓÃת»¯·½Ã棬Jet Êý¾ÝÒýÇæ¸Ä±äÁËһЩÊý¾ÝÀàÐÍ£¬Ôö¼ÓÁËһЩеÄÊý¾ÝÀàÐÍ×Ö¡£ÏÂÃæµÄ²¿·Ö½«ÌÖÂÛÖ÷ÒªµÄÊý¾ÝÀàÐͼ°ÆäÈçºÎʵÏֵġ£

Îı¾£¨TEXT£©Êý¾ÝÀàÐÍ
Îı¾£¨TEXT£©Êý¾ÝÀàÐÍÊÇÓÃÀ´ÉèÖÃÒ»¸ö¿ÉÒÔ´æ´¢´¿Îı¾µÄÓòµÄ£¬Ò²¿ÉÒÔÓÃÀ´´æ´¢Í¬Ê±°üº¬Îı¾ºÍÊý×ÖµÄÄÚÈÝ£¬µ«ÆäÖеÄÊý×Ö²¢²»ÊÇÓÃÓÚ¼ÆËãµÄ£¬±ÈÈçµç»°ºÅÂë»òÕßµç×ÓÓʼþµØÖ·¡£µ±Óû§Í¨¹ýACESSµÄÓû§½çÃæ´´½¨±íʱ£¬Óû§ÓÐÁ½ÖÖ»ù±¾µÄÎı¾ÀàÐÍ£ºÎı¾£¨TEXT£©ºÍ±¸×¢£¨MEMO£©¡£µ«ÊÇͨ¹ýSQLÓï¾äÈçCREATE TABLE»òÕß ALTER TABLE À´´´½¨±íʱ£¬Ôò»¹ÓÐÆäËûÐí¶àµÄTEXT ºÍ MEMOÀàÐ͵ÄͬÒå×ֿɹ©Ñ¡Ôñ¡£

×ܵÄÀ´Ëµ£¬Îı¾Óò×î¶à¿ÉÒÔÓÐ255¸ö×Ö·û£¬¶ø±¸×¢ÓòÔò×î¶à¿ÉÒÔÓÐ65,535 ¸ö×Ö·û£¬µ«ÊÇÈç¹û±¸×¢Óò²»°üº¬Èκζþ½øÖÆÊý¾ÝµÄ»°£¬ÆäΨһµÄÏÞÖƾÍÊÇÕû¸öÊý¾Ý¿âµÄ×î´óÈÝÁ¿£¨´ó¸ÅÊÇ2.14GB »ò 1,070,000,000 Ë«×Ö½Ú×Ö·û£©¡£ÁíÍ⣬ûÓÐÓõ½µÄÎı¾Ó벿·Ö²¢²»»á±£´æÔÚÄÚ´æÖС£

ÏÂÃæµÄ±í¸ñÁÐʾÁË×î»ù±¾µÄJet Îı¾Êý¾ÝÀàÐÍ£¬ËüµÄͬÒå×ÖÒÔ¼°Ëù·ÖÅäµÄ×Ö½ÚÊý¡£

Jet Êý¾ÝÀàÐÍ
ͬÒå×Ö
´æ´¢ÈÝÁ¿

TEXT£¨Îı¾£©
TEXT, TEXT(n), CHAR, CHAR(n), ALPHANUMERIC, ALPHANUMERIC(n), STRING, STRING(n), VARCHAR, VARCHAR(n), NTEXT(n), NCHAR, NCHAR(n), CHAR VARYING, CHAR VARYING(n), CHARACTER VARYING, CHARACTER VARYING(n), NATIONAL CHAR, NATIONAL CHAR(n), NATIONAL CHARACTER, NATIONAL CHARACTER(n), NATIONAL CHAR VARYING, NATIONAL CHAR VARYING(n), NATIONAL CHARACTER VARYING, NATIONAL CHARACTER VARYING(n)
×î¶à¿ÉÒÔÓÐ255¸ö×Ö·û£¬Ã¿¸ö×Ö·ûÁ½¸ö×Ó½Ú£¨Èç¹û²»½øÐÐѹËõ£©¡£

MEMO£¨±¸×¢£©
LONGTEXT, LONGCHAR, ×¢Òâ, NTEXT
65,535 ¸ö×Ö·û£»Èç¹û·Ç¶þ½øÖÆÊý¾Ý¿ÉÒÔÓÐ2.14 GB

ÏÂÃæµÄCREATE TABLEÓï¾äʾ·¶ÁË¿ÉÒÔÓÃÀ´Í¨¹ýAccess SQL ViewÓû§½çÃæ´´½¨±íµÄTEXT ºÍMEMOµÄ²»Í¬ÐÎʽµÄͬÒå´Ê¡£

CREATE TABLE tblUITextDataTypes (
Field1_TEXT TEXT,
Field2_TEXT25 TEXT(25),
Field3_MEMO MEMO,
Field4_CHAR CHAR,
Field5_CHAR25 CHAR(25),
Field6_LONGTEXT LONGTEXT,
Field7_LONGCHAR LONGCHAR,
Field8_ALPHA ALPHANUMERIC,
Field9_ALPHA25 ALPHANUMERIC(25),
Field10_STRING STRING,
Field11_STRING25 STRING(25),
Field12_VARCHAR VARCHAR,
Field13_VARCHAR25 VARCHAR(25),
Field14_NOTE NOTE)

Èç¹ûÓû§Í¨¹ýAccessµÄÓû§½çÃæ²ì¿´ÉÏÃæµÄtblUITextDataTypes±íµÄÉè¼ÆµÄ»°£¬½«»á¿´µ½MEMO¡¢ LONGTEXT¡¢ LONGCHAR¡¢ºÍ×¢ÒâͬÒå×Ö¶¼ÊÇMEMO µÄÊý¾ÝÀàÐÍ£¬ÆäËûµÄËùÓÐͬÒå×Ö¶¼ÊÇTEXTÊý¾ÝÀàÐÍ¡£¶ÔÓÚÄÇЩûÓж¨Ò峤¶ÈµÄTEXT Êý¾ÝÀàÐÍ£¬È±Ê¡µÄ³¤¶ÈΪ255¸ö×Ö·û¡£

Ëä˵ÉÏÃæµÄSQLÓï¾äÒ²¿ÉÒÔͨ¹ýJet OLE DB providerºÍ ADOÀ´Ö´ÐУ¬µ«»¹ÊÇÓÐһЩÆäËûµÄTEXT ºÍMEMO Êý¾ÝÀàÐ͵IJ»Í¬¶¨ÒåÖ»ÄÜͨ¹ýJet OLE DB provider ºÍADOÀ´Ö´ÐС£

CREATE TABLE tblCodeTextDataTypes
Field1_NTEXT NTEXT,
Field2_NTEXT25 NTEXT(25),
Field3_NCHAR NCHAR,
Field4_NCHAR NCHAR(25),
Field5_VARYING CHAR VARYING,
Field6_VARYING CHAR VARYING(25),
Field7_VARYING CHARACTER VARYING,
Field8_VARYING CHARACTER VARYING(25),
Field9_NATIONAL NATIONAL CHAR,
Field10_NATIONAL NATIONAL CHAR(25),
Field11_NATIONAL NATIONAL CHARACTER,
Field12_NATIONAL NATIONAL CHARACTER(25),
Field13_NATIONAL NATIONAL CHAR VARYING,
Field14_NATIONAL NATIONAL CHAR VARYING(25),
Field15_NATIONAL NATIONAL CHARACTER VARYING,
Field16_NATIONAL NATIONAL CHARACTER VARYING(25))

Èç¹ûÓû§Í¨¹ýAccessµÄÓû§½çÃæ²ì¿´ÉÏÃæµÄtblUITextDataTypes±íµÄÉè¼ÆµÄ»°£¬½«»á¿´µ½Ö»ÓÐNCHARÊý¾ÝÀàÐÍÊÇMEMO£¬ÆäËûµÄ¶¼ÊÇTEXTÊý¾ÝÀàÐÍ¡£¶ÔÓÚÄÇЩûÓж¨Ò峤¶ÈµÄTEXT Êý¾ÝÀàÐÍ£¬È±Ê¡µÄ³¤¶ÈΪ255¸ö×Ö·û¡£

×¢Ò⣺ ÔÚÉÏÃæµÄSQLÓï¾äÖÐÁÐʾµÄÕâЩÊý¾ÝÀàÐÍÖ»ÄÜͨ¹ýJet OLE DB provider ºÍADOÀ´Ö´ÐУ¬ÔÚAccess SQL ViewµÄÓû§½çÃæÖÐʹÓý«µ¼Ö´íÎóÐÅÏ¢¡£»¹Òª×¢ÒâÈç¹ûͨ¹ýJet OLE DB providerºÍ ADO´´½¨ÁËÒ»¸öTEXTÊý¾ÝÀàÐ͵ÄÓò£¬Ôòͨ¹ýAccessÓû§½çÃæ¿´µ½µÄ½«ÊÇMEMOµÄÊý¾ÝÀàÐÍ¡£

UnicodeѹËõ

ÏÖÔÚÔÚMicrosoft Jet 4.0 Êý¾ÝÒýÇæÖУ¬ËùÓеÄTEXTÊý¾ÝÀàÐͶ¼ÊÇÒÔÁ½¸ö×Ö½ÚµÄͳһ±àÂëÐÎʽ´æ´¢µÄ¡£ËüÈ¡´úÁËÇ°Ãæ°æ±¾ÖвÉÓõĶà×Ö½Ú×Ö·û¼¯£¨Multi-byte Character Set £¬MBCS£©¸ñʽ¡£ËäȻ˫×ֽڵĸñʽÐèÒª¸ü¶àµÄ¿Õ¼äÀ´´æ´¢Ã¿¸ö×Ö·û£¬µ«¿ÉÒÔ¶¨ÒåʹÓÃTEXTÊý¾ÝÀàÐ͵Ä×Ô¶¯½øÐÐѹËõ¡£

ÔÚÓû§Ê¹ÓÃSQL½¨Á¢µÄTEXTµÄÊý¾ÝÀàÐÍʱ£¬Ë«×Ö½Ú±àÂëµÄѹËõÊôÐÔȱʡÉèÖÃΪNO£¬Èç¹ûÐèÒª½«¸ÃÊôÐÔÉèÖÃΪYes£¬Óû§¿ÉÒÔÔÚ¶¨ÒåÓòµÄʱºòʹÓÃWITHCOMPRESSION £¨»òÕß WITH COMP£©¹Ø¼ü×Ö¡£

ÏÂÃæµÄCREATE TABLE Óï¾ä½«´´½¨Ò»¸öÐÂµÄ¹Ë¿Í±í£¬ÕâÀｫ˫×Ö½Ú±àÂëµÄѹËõÊôÐÔÉèÖÃΪYes¡£

CREATE TABLE tblCompressedCustomers (
CustomerID INTEGER CONSTRAINT
PK_tblCompCustomers PRIMARY KEY,
[Last Name] TEXT(50) WITH COMP NOT NULL,
[First Name] TEXT(50) WITH COMPRESSION NOT NULL,
Phone TEXT(10),
Email TEXT(50),
Address TEXT(40) DEFAULT Unknown)

×¢ÒâWITH COMPRESSION ºÍWITH COMP¹Ø¼ü×ÖÒªÔÚNOT NULL֮ǰ¶¨Òå¡£Óû§Ò²¿ÉÒÔʹÓÃALTER TABLEÓï¾ä¸Ä±äÏÖ´æÓòµÄË«×Ö½Ú±àÂëµÄѹËõÊôÐÔ£¬ÈçÏÂËùʾ£º

ALTER TABLE tblCustomers
ALTER COLUMN [Last Name] TEXT(50) WITH COMPRESSION

×¢Ò⣺ ÔÚÉÏÃæµÄSQLÓï¾äÖÐÁÐʾµÄWITH COMPRESSION and WITH COMP¹Ø¼ü×ÖÖ»ÄÜͨ¹ýJet OLE DB provider ºÍADOÀ´Ö´ÐУ¬ÔÚAccess SQL ViewµÄÓû§½çÃæÖÐʹÓý«µ¼Ö´íÎóÐÅÏ¢¡£

ÔÚÓû§Éè¼ÆÊý¾Ý±íʱ²ÉÈ¡ÄÇÖÖÊý¾ÝÀàÐÍÈ¡¾öÓÚÓû§µÄÓ¦ÓóÌÐòµÄÄ¿µÄ¡£Èç¹û¸ÃÓ¦ÓóÌÐò×ÜÊÇÓ¦ÓÃÔÚJetÊý¾Ý¿âµÄ»ù´¡ÉÏ£¬ÔòÓ¦¸ÃʹÓÃ×îÊÊÒ˵ÄÄÇЩÊý¾ÝÀàÐÍ¡£µ«Èç¹û¸ÃÓ¦ÓóÌÐò×îÖÕÒªÓõ½ODBC-compliantµÄÊý¾Ý¿âÉÏ£¬±ÈÈçSQL Server »ò MSDE£¬ÔòҪʹÓÃÄÇЩÊÇÒ»ÖÂ×î·½±ãµÄÊý¾ÝÀàÐÍ¡£

Êý×Ö£¨ NUMERIC£©Êý¾ÝÀàÐÍ
Êý×Ö£¨NUMERIC£©Êý¾ÝÀàÐÍÊÇÓÃÀ´¶¨Òå´æ´¢¼ÆËãÓÃÊý×ÖµÄÓòµÄ¡£Í¨³££¬½«Ä³ÖÖNUMERICÀàÐÍÓëÆäËûµÄÇø±ð¿ªÀ´µÄÊÇÓÃÀ´´æ´¢Êý¾ÝµÄ×Ö½ÚÊý£¬ËüÒ²Ó°Ïì×ÅËù´æ´¢Êý×ֵľ«¶È¡£Ðí¶àJet SQL µÄÊý¾ÝÀàÐͶ¹ÓÍЭͬÒå×Ö¿ÉÒÔÓÃÀ´½øÐж¨Ò壬µ½µ×ʹÓÃÄÄÒ»¸öÈ¡¾öÓÚ¸ÃÊý¾Ý±íÊǽöÔÚij¸öJetÊý¾Ý¿âÖл¹Êǽ«ÒÆÖ²µ½ÖîÈçMicrosoft SQL ServerµÄÊý¾Ý¿â·þÎñÆ÷ÖС£Èç¹û½«½øÐÐÒÆÖ²£¬Óû§Ó¦Ñ¡ÔñÄÇЩʹµÃÒÆÖ²×îÈÝÒ×½øÐеÄÊý¾ÝÀàÐÍ¡£

ÏÂÃæµÄ±í¸ñÁÐʾÁË»ù±¾µÄJet NUMERICÊý¾ÝÀàÐÍ£¬ËüµÄ¸÷ÖÖͬÒå×ÖÒÔ¼°ÎªÆÚ·ÖÅäµÄ×Ö½ÚÊý¡£

Jet Êý¾ÝÀàÐÍ
ͬÒå×Ö
´æ´¢´óС

TINYINT
INTEGER1, BYTE
1 byte

SMALLINT
SHORT, INTEGER2
2 bytes

INTEGER
LONG, INT, INTEGER4
4 bytes

REAL
SINGLE, FLOAT4, IEEESINGLE
4 bytes

FLOAT
DOUBLE, FLOAT8, IEEEDOUBLE, NUMBER
8 bytes

DECIMAL
NUMERIC, DEC
17 bytes

ÏÂÃæµÄCREATE TABLE Óï¾äʾ·¶Á˸÷ÖÖ¿ÉÒÔÔÚͨ¹ýAccess SQL View µÄÓû§½çÃæ´´½¨±íʱʹÓõÄSNUMERIC Êý¾ÝÀàÐÍ¡£

CREATE TABLE tblUINumericDataTypes (
Field1_INT INT,
Field2_INTEGER INTEGER,
Field3_LONG LONG,
Field4_INTEGER1 INTEGER1,
Field5_BYTE BYTE,
Field6_NUMERIC NUMERIC,
Field7_REAL REAL,
Field8_SINGLE SINGLE,
Field9_FLOAT FLOAT,
Field10_FLOAT4 FLOAT4,
Field11_FLOAT8 FLOAT8,
Field12_DOUBLE DOUBLE,
Field13_IEEESINGLE IEEESINGLE,
Field14_IEEEDOUBLE IEEEDOUBLE,
Field15_NUMBER NUMBER,
Field16_SMALLINT SMALLINT,
Field17_SHORT SHORT,
Field18_INTEGER2 INTEGER2,
Field19_INTEGER4 INTEGER4)

ËäÈ»ÉÏÃæµÄSQL Óï¾äÒ²¿ÉÒÔͨ¹ýJet OLE DB provider ºÍADOÀ´Ö´ÐУ¬ÈÔÓÐһЩÆäËûµÄNUMERIC Êý¾ÝÀàÐ͵ÄÐÎʽֻÄÜͨ¹ýJet OLE DB provider ºÍ ADOÀ´Ö´ÐС£

CREATE TABLE tblCodeNumericDataTypes (
Field1_TINYINT TINYINT,
Field2_DECIMAL DECIMAL,
Field3_DEC DECIMAL,
Field4_DPRECISION DOUBLE PRECISION)

×¢Òâ: ÉÏÃæµÄSQL Óï¾äÖеÄÊý¾ÝÀàÐÍÖ»ÄÜͨ¹ýJet OLE DB provider ºÍ ADOÀ´Ö´ÐУ¬ÔÚAccess SQL ViewµÄÓû§½çÃæÖÐʹÓý«µ¼Ö´íÎóÐÅÏ¢¡£»¹Òª×¢ÒâÈç¹ûͨ¹ýAccess SQL View ½¨Á¢ÁËÒ»¸öNUMERIC Êý¾ÝÀàÐ͵ÄÓò£¬Í¨¹ýAccess Óû§½çÃæ²ì¿´±íµÄÉè¼Æʱ¿´µ½µÄ½«ÊÇDOUBLE Êý¾ÝÀàÐÍ£¬µ«ÊÇÈç¹ûͨ¹ýJet OLE DB provider ºÍ ADO½¨Á¢NUMERICÊý¾ÝÀàÐÍ£¬Í¨¹ýAccess Óû§½çÃæ¿´µ½µÄ½«ÊÇDECIMALÊý¾ÝÀàÐÍ.

ʹÓÃеÄDECIMAL Êý¾ÝÀàÐÍ£¬Óû§¿ÉÒÔÉ趨ÊýÖµµÄ¾«¶ÈºÍСÊýλ¡£¾«¶È¾ÍÊǸÃÓòËùÄÜ°üº¬µÄÊý×ÖµÄ×ÜÊý£¬¶øСÊýλÔò¾ö¶¨ÁËСÊýµãÓÒÃæÄÜÓм¸Î»Êý×Ö£¬¾«¶ÈȱʡֵÊÇ18£¬×î´óµÄÔÊÐíÖµ28£¬¶øСÊýλȱʡµÄÊÇ0£¬×î´óֵʱ28¡£

CREATE TABLE tblDecimalDataTypes (
DefaultType DECIMAL,
SpecificType DECIMAL(10,5))

»õ±Ò£¨CURRENCY£©Êý¾ÝÀàÐÍ
»õ±Ò£¨CURRENCY£©Êý¾ÝÀàÐÍÊÇÓÃÀ´´æ´¢15λÕûÊýºÍ4λСÊýµÄÊý×ÖÖµµÄ£¬ËüʹÓÃ8¸ö×ֽڵĴ洢Á¿£¬ÆäΨһµÄͬÒå×ÖÊÇMONEY¡£

ÏÂÃæµÄCREATE TABLE Óï¾äʾ·¶ÁËCURRENCY Êý¾ÝÀàÐÍÔÚ´´½¨Êý¾Ý±íʱµÄÓ÷¨£¬Ëü¼È¿ÉÒÔÔÚAccess SQL View Óû§½çÃæÖÐÒ²¿ÉÒÔÔÚJet OLE DB provider ºÍ ADOÖÐʹÓá£

CREATE TABLE tblCurrencyDataTypes (
Field1_CURRENCY CURRENCY,
Field2_MONEY MONEY)

ÊÇ·ñ£¨BOOLEAN £©Êý¾ÝÀàÐÍ

ÊÇ·ñ£¨BOOLEAN£©ÊÇÂß¼­Êý¾ÝÀàÐÍ£¬ÆäֵΪÊÇ£¨TRUE£©»ò·ñ(FALSE)¡£ËüÃÇʹÓÃÒ»¸ö×ֽڵĴ洢Á¿£¬Í¬Òå×ÖÓÐBIT¡¢ LOGICAL¡¢ LOGICAL1 ºÍ YESNO¡£ TrueµÄÖµµÈÓÚ¨C1£¬ FalseµÄÖµµÈÓÚ0¡£

ÏÂÃæµÄCREATE TABLE Óï¾äʾ·¶ÁËͨ¹ýJet OLE DB provider ºÍ ADO À´´´½¨BOOLEAN Êý¾ÝÀàÐ͵IJ»Í¬ÐÎʽ¡£

CREATE TABLE tblUIBooleandataTypes (
Field1_BIT BIT,
Field2_LOGICAL LOGICAL,
Field3_LOGICAL1 LOGICAL1,
Field4_YESNO YESNO)

×Ö½Ú£¨BINARY £©Êý¾ÝÀàÐÍ

×Ö½Ú£¨BINARY £©Êý¾ÝÀàÐÍÒÔ¶þ½øÖƵı¾À´ÃæÄ¿´æ´¢Ð¡ÈÝÁ¿µÄÈκÎÀàÐ͵ÄÊý¾Ý¡£Ëü¶ÔÓÚËù´æ´¢µÄÿ¸ö×Ö·ûֻʹÓÃ1¸ö×ֽڵĴ洢Á¿£¬Óû§¿ÉÒÔÖ¸¶¨Ëù·ÖÅäµÄ×Ö½ÚÊý¡£Èç¹ûûÓÐÖƶ¨×Ö½ÚÊý£¬È±Ê¡µÄÖµÊÇ510£¬ÕâÒ²ÊÇËùÄÜÔÊÐíµÄ×î´ó×Ö½ÚÊý¡£ËüµÄͬÒå×ÖÓÐBINARY¡¢ VARBINARYºÍ BINARY VARYING¡£BINARY Êý¾ÝÀàÐÍÔÚAccess Óû§½çÃæÖÐÊDz»¿ÉÓõġ£

ÏÂÃæµÄ CREATE TABLE Óï¾äʾ·¶ÁË¿ÉÒÔÓÃÀ´ÔÚAccess SQL View Óû§½çÃæÖÐʹÓõÄBINARY Êý¾ÝÀàÐ͵IJ»Í¬ÐÎʽ¡£

CREATE TABLE tblUIBinaryDataTypes (
Field1_BINARY BINARY,
Field2_BINARY250 BINARY(250),
Field3_VARBINARY VARBINARY,
Field4_VARBINARY250 VARBINARY(250))

ËäÈ»ÉÏÃæµÄSQLÓï¾äÒ²¿ÉÒÔͨ¹ýJet OLE DB provider ºÍ ADOÖ´ÐУ¬µ«ÈÔÈ»ÓÐЩÆäËûµÄbinary Êý¾ÝÀàÐ͵ÄͬÒå×ÖÖ»ÄÜͨ¹ýJet OLE DB provider ºÍ ADOÀ´Ö´ÐУ¬ÈçÏÂËùʾ£º

CREATE TABLE tblCodeBinaryDataTypes (
Field1_BVARYING BINARY VARYING,
Field2_BVARYING250 BINARY VARYING(250))

OLEOBJECT Êý¾ÝÀàÐÍ
OLEOBJECT Êý¾ÝÀàÐÍÓÃÀ´´æ´¢´óµÄ¶þ½øÖƶÔÏ󣬱ÈÈçWordÎĵµ»òÕßExcel±íµ¥¡£ËüµÄ×Ö½ÚÊý²¢²»È·¶¨£¬×î´ó¿É´ï2.14 GB¡£ÆäͬÒå×ÖÓУºIMAGE¡¢LONGBINARY¡¢GENERAL ºÍ OLEOBJECT

ÏÂÃæµÄ CREATE TABLE Óï¾äչʾÁËͨ¹ýAccess SQL View Óû§½çÃæ»òJet OLE DB providerºÍADO À´´´½¨±íʱʹÓÃOLEOBJECT Êý¾ÝÀàÐÍ¡£

CREATE TABLE tblImageDataTypes (
Field1_IMAGE IMAGE,
Field2_LONGBINARY LONGBINARY,
Field3_GENERAL GENERAL,
Field4_OLEOBJECT OLEOBJECT)

ÈÕÆÚʱ¼ä£¨DATETIME£©Êý¾ÝÀàÐÍ

ÈÕÆÚʱ¼ä£¨DATETIME£©Êý¾ÝÀàÐÍÓÃÀ´´æ´¢ÈÕÆÚ¡¢Ê±¼äÒÔ¼°ÈÕÆÚºÍʱ¼äµÄ½áºÏÖµ£¬ÄêÊý¿ÉÒÔ´Ó100µ½ 9999¡£ËüʹÓÃ8¸ö×ֽڵĴ洢Á¿£¬ÆäͬÒå×ÖÓÐDATE¡¢TIME¡¢DATETIMEºÍ TIMESTAMP

ÏÂÃæµÄ CREATE TABLE Óï¾äչʾÁËͨ¹ýAccess SQL View Óû§½çÃæ»òJet OLE DB provider ºÍ ADO ´´½¨±íʾËùʹÓõÄDATETIME Êý¾ÝÀàÐ͵IJ»Í¬ÐÎʽ¡£

CREATE TABLE tblDateTimeDataTypes (
Field1_DATE DATE,
Field2_TIME TIME,
Field3_DATETIME DATETIME,
Field4_TIMESTAMP TIMESTAMP)

COUNTER Êý¾ÝÀàÐÍ

COUNTER Êý¾ÝÀàÐÍÓÃÀ´´æ´¢³¤ÕûÐÍÊýÖµ£¬¸ÃÊýÖµÔÚ±íÖÐÿÔö¼ÓÒ»ÌõеļͼʱÄܹ»×Ô¶¯Ôö¼Ó¡£Ê¹ÓÃCOUNTER Êý¾ÝÀàÐÍ£¬Óû§¿ÉÒÔÉ趨һ¸öÖÖ×ÓÖµºÍÔö¼ÓÖµ£¬ÖÖ×ÓÖµÊǵ±µÚÒ»¸ö¼Ç¼²åÈëµ½±íÖÐʱ½«ÊäÈëµ½ÓòÖеÄÊýÖµ£¬¶øÔö¼ÓÖµÓÃÀ´¼Óµ½ÉÏÒ»¸ö¼ÇÊýÖµÉÏ×÷ΪÏÂÒ»¸ö¼ÇÊýÖµ¡£Èç¹ûûÓÐÖ¸¶¨ÖÖ×ÓÖµºÍÔö¼ÓÖµ£¬ËüÃǶ¼½«È±Ê¡µÄʹÓÃ1¡£ÔÚÒ»¸ö±íÖÐÖ»ÄÜÓÐÒ»¸öCOUNTERÓò¡£ÆäͬÒå×ÖÓÐ COUNTER¡¢AUTOINCREMENTºÍIDENTITY¡£

ÏÂÃæµÄ CREATE TABLE Óï¾äչʾÁËͨ¹ýAccess SQL View Óû§½çÃæ´´½¨±íʱʹÓõÄCOUNTER Êý¾ÝÀàÐ͵ÄͬÒå×Ö¡£

CREATE TABLE tblUICounterDataTypes (
Field1 COUNTER,
Field2 TEXT(10))

×¢ÒâÕâÀïûÓÐÖ¸¶¨ÖÖ×ÓÖµºÍÔö¼ÓÖµ£¬ËùÓж¼½«²ÉÓÃȱʡֵ1¡£¶¨ÒåCOUNTER Êý¾ÝÀàÐ͵ÄÁíÒ»ÖÖ·½Ê½ÊÇʹÓÃAUTOINCREMENT ¹Ø¼ü×Ö£¬ÈçÏÂËùʾ£º

CREATE TABLE tblUICounterDataTypes (
Field1 AUTOINCREMENT(10,5),
Field2 TEXT(10))

ÕâÒ»´ÎÖÖ×ÓÖµºÍÔö¼ÓÖµ¶¼Ö¸¶¨ÁË£¬Ôò¿ªÊ¼ÖµÊ±10£¬Ã¿´Î½«¼Ó5¡£ÉÏÃæµÄSQL Óï¾äÒ²¿ÉÒÔͨ¹ýJet OLE DB provider ºÍ ADOÖ´ÐУ¬ÁíÍ⻹ÓÐcounter Êý¾ÝÀàÐ͵ÄÁíÒ»ÖÖÐÎʽ£¬Ö»ÄÜͨ¹ýJet OLE DB provider ºÍ ADOÀ´Ö´ÐУ¬¼´IDENTITY ¹Ø¼ü×Ö£¬ËüºÍSQL ServerµÄ IDENTITY Êý¾ÝÀàÐÍÊÇÏà¼æÈݵġ£

CREATE TABLE tblCodeCounterDataTypes
Field1_IDENTITY IDENTITY(10,5),
Field2 TEXT(10))

ÕâÀïµÄÖÖ×ÓÖµºÍÔö¼ÓÖµ¶¼¿ÉÒÔͨ¹ýALTER TABLE Óï¾ä½øÐÐÐ޸ģ¬Ð޸ĺóËùÓÐвåÈëµÄÐн«Ê¹ÓÃеÄÖµ¡£µ«ÊÇ£¬ COUNTER Êý¾ÝÀàÐͳ£³£¶¼ÊÇÓÃÓÚÖ÷¼üµÄ£¬¶øÖ÷¼üÒªÇóÿÁÐΨһ¡£Èç¹ûÄã¸Ä±äÁËÖÖ×ÓÖµ, ½«¿ÉÄܵ¼ÖÂÖ÷¼üÓòµÄÖظ´Öµ£¬´Ó¶ø·¢Éú´íÎó¡£

ALTER TABLE tblUICounterDataTypes
ALTER COLUMN Field1 COUNTER(10,10)
×¢Òâ: Èç¹ûij¸öÏÖ´æµÄÁÐÒѾ­°üº¬ÁËÊý¾ÝµÄ»°£¬Óû§²»ÄÜʹÓÃALTER TABLE Óï¾äÀ´½«¸ÃÁеÄÊý¾ÝÀàÐ͸ıäΪCOUNTER Êý¾ÝÀàÐÍ¡£

ÔÚÒÔÇ°°æ±¾µÄJetÊý¾Ý¿âÖУ¬Ñ¹ËõÊý¾Ý¿âÖ®ºó£¬ÖÖ×ÓÖµ½«±»ÖÃΪ¿ÉÄܵÄ×î´óÖµ¡£ÔÚJet 4.0ÖÐÈÔÈ»Èç´Ë£¬Ö»ÒªÖÖ×ÓÖµºÍÔö¼ÓÖµµÄ²ÉÓõÄÊÇȱʡֵ1µÄ»°¡£Èç¹ûÓû§Ö¸¶¨Á˲»µÈÓÚȱʡֵµÄÖÖ×ÓÖµºÍÔö¼ÓÖµ£¬ÔÚѹËõÊý¾Ý¿âÊDz¢²»»áÖØÖÃÖÖ×ÓÖµ¡£

@@IDENTITY ±äÁ¿

@@IDENTITY ±äÁ¿ÊÇÒ»¸öÈ«¾ÖµÄSQL±äÁ¿£¬Óû§¿ÉÒÔÓÃËüÀ´ÌáȡʹÓÃCOUNTERÊý¾ÝÀàÐ͵ÄÁеÄ×îºóʹÓÃÖµ¡£Óû§ÔÚÌáÈ¡@@IDENTITY ±äÁ¿Ê±£¬²»ÄÜÖ¸¶¨±íµÄÃû³Æ¡£·µ»ØÖµ×ÜÊÇ×î½üµÄͨ¹ý´úÂë²åÈëÁËÊý¾ÝµÄ±íµÄCOUNTERÓò¡£

SELECT @@IDENTITY
Òª¸ø@@IDENTITY Öµ¼ÓÉÏij¸öÖµ£¬Òª½«¸Ã±äÁ¿Ó÷½À¨ºÅÀ¨ÆðÀ´¡£

SELECT [@@IDENTITY] + 1
×¢Òâ ÉÏÃæSQL Óï¾äÖеÄ@@IDENTITY±äÁ¿Ö»ÄÜͨ¹ýJet OLE DB provider ºÍ ADOÖ´ÐÐÌáÈ¡£¬Í¨¹ýAccess SQL View Óû§½çÃæÌáÈ¡µÄÖµ½«ÊÇ0£¬ÁíÍ⣬¸ÃÖµÖ»ÓÐÔÚͨ¹ý´úÂë²åÈë¼Ç¼ʱ²Å»á¸Ä±ä£¬Èç¹ûͨ¹ýÓû§½Ó¿Ú£¬²»¹ÜÊÇÊý¾Ý±íµ¥¡¢´°Ì廹ÊÇAccess SQL View´°¿ÚÖеÄSQLÓï¾ä£¬ @@IDENTITY·µ»ØÖµ¶¼ÊÇ0¡£Òò´Ë£¬ @@IDENTITY µÄÖµÖ»ÓÐÔÚ¸Õ¸Õͨ¹ý´úÂë¼ÓÈëÁ˼ǼÊDzÅÊÇ׼ȷµÄ¡£

Òª»ñµÃ¸üÏêϸµÄÓйØÊý¾ÝÀàÐ͵Ä˵Ã÷£¬ÇëÔÚOffice ÖúÊÖÖлòÔÚMicrosoft Access °ïÖúµÄ»Ø´ðÏòµ¼µÄ±êÇ©Ò³ÖÐÊäÈëALTER TABLE £¬È»ºóµ¥»÷²éÕÒ¡£

--------------------------------------------------------------------------------
Ïà¹ØÎÄÕÂ
ÈçºÎÄÜͨ¹ý´°Ìå·ÃÎÊ±í£¬µ«²»ÄÜÖ±½Ó¶ÁÈ¡±í£¿ 2004-8-27 21:58:17
Êý¾Ý¿âÉè¼ÆÖеÄÃô½Ý·½·¨ 2004-2-9 11:19:16
µ÷ÓÃoutlookÉú³É²¢·¢ËÍÓʼþµÄ´úÂë 2004-1-30 10:32:07
SQL SERVER»ù±¾¸ÅÄîѧϰӡÏó 2004-1-1 21:23:13
Ò»×éSQL ServerÉí·ÝÑéÖ¤¹ÜÀí×Ó³ÌÐò 2003-12-31 16:29:54
Access Êý¾Ý¿âºÍ Access ÏîÄ¿Ö®¼äµÄÊý¾ÝÀàÐ͵ıȽϻòÓ³Éä 2003-12-26 14:20:19
´óÐÍÊý¾Ý¿âÉè¼ÆÔ­Ôò 2003-12-15 12:49:18
ERwin--¼ò»¯µÄÊý¾Ý¿âÉè¼Æ¹¤¾ß 2003-12-15 12:41:40
ERwinÈí¼þ¼ò½é 2003-12-15 12:39:55
¾­µä×Ó²éѯӦÓà 2003-11-17 8:54:51
Á˽â Microsoft Access °²È«ÐÔ 2003-11-13 11:21:08
²»µ±±àдSQLÓï¾äµ¼ÖÂϵͳ²»°²È« 2003-11-13 10:55:26
±ÜÃâAccessºÍSQL ServerµÄ¿ÕÖµ³åÍ» 2003-10-17 20:03:19
±í´ïʽÉú³ÉÆ÷Öеļ¸ÖÖ³£Ó÷ûºÅ 2003-10-17 19:55:56
´´½¨ÍêÃÀ±¨±í 2003-10-17 19:37:17
£Ó£Ñ£Ì Óï·¨²Î¿¼ÊÖ²á 2003-10-17 19:12:40
SQL SERVER ÓëACCESS¡¢EXCELµÄÊý¾Ýת»» 2003-10-17 19:12:00
´æ´¢¹ý³ÌÈëÃÅ 2003-10-17 19:06:48
MS Jet SQL for Access 2000Öм¶Æª(IV) 2003-10-17 18:52:20
MS Jet SQL for Access 2000Öм¶Æª (III) 2003-10-17 18:51:47
MS Jet SQL for Access 2000Öм¶Æª (I) 2003-10-17 18:47:38
ʹÓÃADOÁ¬½Óµ½·À»ðǽºóµÄSQL Server 2003-10-17 18:43:57
ADOÁ¬½ÓÊý¾Ý¿â×Ö·û´®´óÈ« 2003-10-17 18:40:47
ADOÈý´ó¶ÔÏóµÄÊôÐÔ¡¢·½·¨¡¢Ê¼þ¼°³£Êý£¨¶þ£© 2003-10-14 8:45:33
ADOÈý´ó¶ÔÏóµÄÊôÐÔ¡¢·½·¨¡¢Ê¼þ¼°³£Êý£¨Èý£© 2003-10-14 8:44:20
ADOÈý´ó¶ÔÏóµÄÊôÐÔ¡¢·½·¨¡¢Ê¼þ¼°³£Êý£¨Ò»£© 2003-10-14 8:43:26
ACCESSÊý¾Ý·ÃÎÊÒ³ÅäÖÃʵÀý 2003-10-14 8:36:00
ÌáÉýÇ°ºǫ́ģʽ³ÌÐòµÄËÙ¶È 2003-10-10 10:17:08
AccessÆô¶¯ÃüÁîÐÐÑ¡Ïî´óÈ« 2003-10-10 10:02:00
Ò»¸ö¼òµ¥µÄAdoÓ÷¨Ê¾Àý 2003-10-6 16:24:38
Access 2002 ÖеÄÃû³Æ×Ô¶¯¸üÕý˵Ã÷ 2003-10-6 15:16:24
Êý¾Ý¿âÓ¦ÓóÌÐòÐè×¢ÒâµÄÎÊÌâ 2003-10-6 11:11:06