SQLServerµÄËø»úÖÆ
×î½üÒòΪҪдһ¸öÊý¾Ý²¢·¢·ÃÎʵĿØÖƳÌÐò£¬ÉÏÍø²éÁËһЩ×ÊÁÏ£¬ÏÖÔÚ¹éÄÉÈçÏ£º ËøµÄ¸ÅÊö Ò». ΪʲôҪÒýÈëËø
¶à¸öÓû§Í¬Ê±¶ÔÊý¾Ý¿âµÄ²¢·¢²Ù×÷ʱ»á´øÀ´ÒÔÏÂÊý¾Ý²»Ò»ÖµÄÎÊÌ⣺
¶ªÊ§¸üÐÂ
A£¬BÁ½¸öÓû§¶ÁͬһÊý¾Ý²¢½øÐÐÐ޸ģ¬ÆäÖÐÒ»¸öÓû§µÄÐ޸Ľá¹ûÆÆ»µÁËÁíÒ»¸öÐ޸ĵĽá¹û£¬±ÈÈ綩Ʊϵͳ
Ôà¶Á
AÓû§ÐÞ¸ÄÁËÊý¾Ý£¬ËæºóBÓû§ÓÖ¶Á³ö¸ÃÊý¾Ý£¬µ«AÓû§ÒòΪijЩÔÒòÈ¡ÏûÁ˶ÔÊý¾ÝµÄÐ޸ģ¬Êý¾Ý»Ö¸´ÔÖµ£¬´ËʱBµÃµ½µÄÊý¾Ý¾ÍÓëÊý¾Ý¿âÄÚµÄÊý¾Ý²úÉúÁ˲»Ò»ÖÂ
²»¿ÉÖظ´¶Á
AÓû§¶ÁÈ¡Êý¾Ý£¬ËæºóBÓû§¶Á³ö¸ÃÊý¾Ý²¢Ð޸ģ¬´ËʱAÓû§ÔÙ¶ÁÈ¡Êý¾Ýʱ·¢ÏÖÇ°ºóÁ½´ÎµÄÖµ²»Ò»ÖÂ
²¢·¢¿ØÖƵÄÖ÷Òª·½·¨ÊÇ·âËø£¬Ëø¾ÍÊÇÔÚÒ»¶Îʱ¼äÄÚ½ûÖ¹Óû§×öijЩ²Ù×÷ÒÔ±ÜÃâ²úÉúÊý¾Ý²»Ò»ÖÂ
¶þ ËøµÄ·ÖÀà
ËøµÄÀà±ðÓÐÁ½ÖÖ·Ö·¨£º
1. ´ÓÊý¾Ý¿âϵͳµÄ½Ç¶ÈÀ´¿´£º·ÖΪ¶ÀÕ¼Ëø£¨¼´ÅÅËüËø£©£¬¹²ÏíËøºÍ¸üÐÂËø
MS SQL Server ʹÓÃÒÔÏÂ×ÊÔ´Ëøģʽ¡£
Ëøģʽ ÃèÊö
¹²Ïí (S) ÓÃÓÚ²»¸ü¸Ä»ò²»¸üÐÂÊý¾ÝµÄ²Ù×÷£¨Ö»¶Á²Ù×÷£©£¬Èç SELECT Óï¾ä¡£
¸üР(U) ÓÃÓڿɸüеÄ×ÊÔ´ÖС£·ÀÖ¹µ±¶à¸ö»á»°ÔÚ¶ÁÈ¡¡¢Ëø¶¨ÒÔ¼°Ëæºó¿ÉÄܽøÐеÄ×ÊÔ´¸üÐÂʱ·¢Éú³£¼ûÐÎʽµÄËÀËø¡£
ÅÅËü (X) ÓÃÓÚÊý¾ÝÐ޸IJÙ×÷£¬ÀýÈç INSERT¡¢UPDATE »ò DELETE¡£È·±£²»»áͬʱͬһ×ÊÔ´½øÐжàÖظüС£
ÒâÏòËø ÓÃÓÚ½¨Á¢ËøµÄ²ã´Î½á¹¹¡£ÒâÏòËøµÄÀàÐÍΪ£ºÒâÏò¹²Ïí (IS)¡¢ÒâÏòÅÅËü (IX) ÒÔ¼°ÓëÒâÏòÅÅËü¹²Ïí (SIX)¡£
¼Ü¹¹Ëø ÔÚÖ´ÐÐÒÀÀµÓÚ±í¼Ü¹¹µÄ²Ù×÷ʱʹÓ᣼ܹ¹ËøµÄÀàÐÍΪ£º¼Ü¹¹ÐÞ¸Ä (Sch-M) ºÍ¼Ü¹¹Îȶ¨ÐÔ (Sch-S)¡£
´óÈÝÁ¿¸üР(BU) Ïò±íÖдóÈÝÁ¿¸´ÖÆÊý¾Ý²¢Ö¸¶¨ÁË TABLOCK ÌáʾʱʹÓá£
¹²ÏíËø
¹²Ïí (S) ËøÔÊÐí²¢·¢ÊÂÎñ¶ÁÈ¡ (SELECT) Ò»¸ö×ÊÔ´¡£×ÊÔ´ÉÏ´æÔÚ¹²Ïí (S) Ëøʱ£¬ÈκÎÆäËüÊÂÎñ¶¼²»ÄÜÐÞ¸ÄÊý¾Ý¡£Ò»µ©ÒѾ¶ÁÈ¡Êý¾Ý£¬±ãÁ¢¼´ÊÍ·Å×ÊÔ´ÉϵĹ²Ïí (S) Ëø£¬³ý·Ç½«ÊÂÎñ¸ôÀ뼶±ðÉèÖÃΪ¿ÉÖظ´¶Á»ò¸ü¸ß¼¶±ð£¬»òÕßÔÚÊÂÎñÉú´æÖÜÆÚÄÚÓÃËø¶¨Ìáʾ±£Áô¹²Ïí (S) Ëø¡£
¸üÐÂËø
¸üР(U) Ëø¿ÉÒÔ·Àֹͨ³£ÐÎʽµÄËÀËø¡£Ò»°ã¸üÐÂģʽÓÉÒ»¸öÊÂÎñ×é³É£¬´ËÊÂÎñ¶ÁÈ¡¼Ç¼£¬»ñÈ¡×ÊÔ´£¨Ò³»òÐУ©µÄ¹²Ïí (S) Ëø£¬È»ºóÐÞ¸ÄÐУ¬´Ë²Ù×÷ÒªÇóËøת»»ÎªÅÅËü (X) Ëø¡£Èç¹ûÁ½¸öÊÂÎñ»ñµÃÁË×ÊÔ´ÉϵĹ²ÏíÄ£
Ïà¹ØÎĵµ£º
INSERT´¥·¢Æ÷
INSERT¼°UPDATE´¥·¢Æ÷¾³£ÓÃÓÚ¼ì²â´¥·¢Æ÷Ëù¼à¿Ø±íµÄÁм°ÆäÊý¾ÝÊÇ·ñ·ûºÏËù¶¨ÒåµÄ¹æÔò¡£ËüÃÇ¿ÉÒÔÔÚÊý¾ÝÊäÈë±í֮ǰ£¬¶ÔÆä½øÐÐÔÚ¶¨ÒåÒýÓÃÍêÕûÐÔʱÎÞ·¨Íê³ÉµÄÔ¼Êø¼ìÑé¡£
ÏÂÃæÒÔѧÉúÊý¾Ý¿âstudentΪÀýÀ´½éÉÜINSERT´¥·¢Æ÷µÄʹÓ᣸ÃÊý¾Ý¿â°üÀ¨Èý¸ö±í£¬·Ö±ðÊÇÃèÊöѧÉúÇ ......
ÉÏ´Î×öÁ˸öÏîÄ¿£¬Éæ¼°µ½Êý¾Ý¿âµÄ»¹ÔºÍ»Ö¸´£¬µ½ÍøÉÏÕÒÁËһϣ¬ÊÇÀûÓÃSQLDMOʵÏֵģ¬Ö»ÒªÌí¼ÓSQLDMOÒýÓþͺÃÁË£¬
È»ºóÀûÓÃϱߵÄÀàµÄ·½·¨¾Í¿ÉÒÔʵÏÖÁË¡£
ÎÒ°ÑÔ×÷ÕßµÄÀàÀ©³äÁËһϣ¬¿ÉÒÔ×Ô¶¯Ê¶±ðweb.configÀï µÄÊý¾Ý¿âÁ¬½Ó×Ö·û´®£¬¿ÉÒÔͨ¹ý±äÁ¿ÉèÖû¹Ô»Ö¸´µÄÐÅÏ¢¡£
ÐèҪעÒâµÄʱ»¹Ô£¬»¹ÔµÄʱºòÎÊÌâ ......
ÒÉÎÊ£º
1£¬ sqlserverÀïÃæÖ´ÐÐ xp_cmdshell
exec @error=xp_cmdshell 'cmdstr......'
·µ»ØÖµÊÇʲôֵ£¿dosÃüÁîµÄ´íÎó£¿Ê²Ã´ÑùµÄ´íÎó¿ÉÒÔÆË×½µ½£¿
±ÈÈç:
DECLARE @error int
EXEC @error=master.dbo.xp_cmdshell 'bcp CM_DWHSend.dbo.WK_CM_DWHSend_Master in "D:\Sales\DWH_Onl\2010042_SENDMEMM.csv" -n -t, - ......
Access¡¢SQLServer¡¢Oracle³£¼ûSQLÓï¾äÓ¦ÓÃÇø±ð
¹Ø¾¢ËÉ PMP
Èç¹ûÒª¼æÈÝAccess¡¢SQL Server¡¢OracleÈý¸öÊý¾Ý¿â°æ±¾£»ÎÒÃÇÔÚ±àдSQLÓï¾äµÄ¹ý³ÌÖУ¬¾¡Á¿Ê¹ÓÃһЩͨÓõÄÓï¾ä£¬µ«»¹ÊÇÓÐЩ·½ÃæÓÐЩÇø±ð±ÜÃâ²»ÁË£¬ÏÖ¼òµ¥×ܽáһϡ£
ÒÔÏÂA´ú±íAccess,S´ú±íSQL Server,O´ú±íOracle
1¡¢È¡µ±Ç°ÏµÍ³Ê±¼ä
A:Select Now()
S:Selec ......
1
.¾ø¶ÔÖµ
S:
select
abs
(
-
1
) value
O:
select
abs
(
-
1
) value
from
dual
¡¡¡¡
2
.È¡Õû(´ó)
S:
select
ceiling
(
-
1.001
) value
O:
select
ceil(
-
1.001
) value
from
d ......