Ò׽ؽØͼÈí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

SQL ServerÖд洢¹ý³Ì±ÈÖ±½ÓÔËÐÐSQLÓï¾äÂýµÄÔ­Òò

ÔںܶàµÄ×ÊÁÏÖж¼ÃèÊö˵SQLSERVERµÄ´æ´¢¹ý³Ì½ÏÆÕͨµÄSQLÓï¾äÓÐÒÔÏÂÓŵ㣺
1.       ´æ´¢¹ý³ÌÖ»ÔÚ´´Ôìʱ½øÐбàÒë¼´¿É£¬ÒÔºóÿ´ÎÖ´Ðд洢¹ý³Ì¶¼²»ÐèÔÙÖØбàÒ룬¶øÎÒÃÇͨ³£Ê¹ÓõÄSQLÓï¾äÿִÐÐÒ»´Î¾Í±àÒëÒ»´Î,ËùÒÔʹÓô洢¹ý³Ì¿ÉÌá¸ßÊý¾Ý¿âÖ´ÐÐËٶȡ£
2.       ¾­³£»áÓöµ½¸´ÔÓµÄÒµÎñÂß¼­ºÍ¶ÔÊý¾Ý¿âµÄ²Ù×÷£¬Õâ¸öʱºò¾Í»áÓÃSPÀ´·â×°Êý¾Ý¿â²Ù×÷¡£µ±¶ÔÊý¾Ý¿â½øÐи´ÔÓ²Ù×÷ʱ(Èç¶Ô¶à¸ö±í½øÐÐ Update,Insert,Query,Deleteʱ£©£¬¿É½«´Ë¸´ÔÓ²Ù×÷Óô洢¹ý³Ì·â×°ÆðÀ´ÓëÊý¾Ý¿âÌṩµÄÊÂÎñ´¦Àí½áºÏÒ»ÆðʹÓ᣿ÉÒÔ¼«´óµÄÌá¸ßÊý¾Ý¿âµÄʹÓÃЧÂÊ£¬¼õÉÙ³ÌÐòµÄÖ´ÐÐʱ¼ä£¬ÕâÒ»µãÔڽϴóÊý¾ÝÁ¿µÄÊý¾Ý¿âµÄ²Ù×÷ÖÐÊǷdz£ÖØÒªµÄ¡£ÔÚ´úÂëÉÏ¿´£¬SQLÓï¾äºÍ³ÌÐò´úÂëÓï¾äµÄ·ÖÀ룬¿ÉÒÔÌá¸ß³ÌÐò´úÂëµÄ¿É¶ÁÐÔ¡£
3.       ´æ´¢¹ý³Ì¿ÉÒÔÉèÖòÎÊý£¬¿ÉÒÔ¸ù¾Ý´«Èë²ÎÊýµÄ²»Í¬Öظ´Ê¹ÓÃͬһ¸ö´æ´¢¹ý³Ì£¬´Ó¶ø¸ßЧµÄÌá¸ß´úÂëµÄÓÅ»¯ÂʺͿɶÁÐÔ¡£
4.       °²È«ÐÔ¸ß,¿ÉÉ趨ֻÓÐij´ËÓû§²Å¾ßÓжÔÖ¸¶¨´æ´¢¹ý³ÌµÄʹÓÃȨ´æ´¢¹ý³ÌµÄÖÖÀࣺ
A.       ϵͳ´æ´¢¹ý³Ì£ºÒÔsp_¿ªÍ·,ÓÃÀ´½øÐÐϵͳµÄ¸÷ÏîÉ趨.È¡µÃÐÅÏ¢.Ïà¹Ø¹ÜÀí¹¤×÷,Èç sp_help¾ÍÊÇÈ¡µÃÖ¸¶¨¶ÔÏóµÄÏà¹ØÐÅÏ¢¡£
B.       À©Õ¹´æ´¢¹ý³Ì ÒÔXP_¿ªÍ·,ÓÃÀ´µ÷ÓòÙ×÷ϵͳÌṩµÄ¹¦ÄÜ
exec master..xp_cmdshell 'ping 10.8.16.1'
C.       Óû§×Ô¶¨ÒåµÄ´æ´¢¹ý³Ì,ÕâÊÇÎÒÃÇËùÖ¸µÄ´æ´¢¹ý³Ì³£Óøñʽ
    Ä£°æ£ºCreate procedure procedue_name [@parameter data_type][output]
    [with]{recompile|encryption} as sql_statement
    ½âÊÍ£ºoutput£º±íʾ´Ë²ÎÊýÊÇ¿É´«»ØµÄ
with {recompile|encryption} recompile:±íʾÿ´ÎÖ´Ðд˴洢¹ý³Ìʱ¶¼ÖØбàÒëÒ»´Î;encryption:Ëù´´½¨µÄ´æ´¢¹ý³ÌµÄÄÚÈݻᱻ¼ÓÃÜ¡£
 
   µ«ÊÇ×î½üÎÒÃÇÏîÄ¿×éÖÐÓÐÈËдÁËÒ»¸ö´æ´¢¹ý³Ì,Æä¼ÆËãʱ¼äΪ1¸öСʱ47·ÖÖÓ,¶øÓеÄʱºòÔËÐÐʱ¼ä¶¼³¬¹ýÁËÁ½¸öСʱ,ͬÊÂÃèÊö˵Èç¹û½«´æ´¢¹ý³ÌÖеÄÓï¾äÄóöÀ´Ö±½ÓÔËÐÐÒ²¾Í10·ÖÖÓ×óÓÒ¾ÍÔËÐÐÍê±Ï£¬ÎÒûµ±»ØÊ£¬µ«ÊǽñÌìÎÒ×Ô¼ºÐ´µÄ´æ´¢¹ý³ÌÒ²Óöµ½ÁËÕâ¸öÎÊÌ⣬ÔÚ²éÕÒ×ÊÁϺóÔ­ÒòÖÕÓÚÕÒµ½ÁËÔ­Òò£¬Ô­À´ÊÇParameter sniffingÎÊÌâ¡£
    ÏÂÃæ¿´ÎÒÊÇÈçºÎ½«


Ïà¹ØÎĵµ£º

sqlµÄ INNER JOIN, left join,right joinÓï·¨

inner join(µÈÖµÁ¬½Ó) Ö»·µ»ØÁ½¸ö±íÖÐÁª½á×Ö¶ÎÏàµÈµÄÐÐ
left join(×óÁª½Ó) ·µ»Ø°üÀ¨×ó±íÖеÄËùÓмǼºÍÓÒ±íÖÐÁª½á×Ö¶ÎÏàµÈµÄ¼Ç¼
right join(ÓÒÁª½Ó) ·µ»Ø°üÀ¨ÓÒ±íÖеÄËùÓмǼºÍ×ó±íÖÐÁª½á×Ö¶ÎÏàµÈµÄ¼Ç¼
INNER JOIN Óï·¨£º
INNER JOIN Á¬½ÓÁ½¸öÊý¾Ý±íµÄÓ÷¨£º
SELECT * from ±í1 INNER JOIN ±í2 ON ±í1.×ֶκÅ=±í2 ......

Sql½á´æÓà

select ÐÕÃû,סַ,ÆÚ³õÓà¶î=isnull(ÆÚ³õÔö¼Ó,0)-isnull(ÆÚ³õ¼õÉÙ,0),±¾ÆÚÔö¼Ó,±¾ÆÚ¼õÉÙ,
±¾ÆÚ½áÓà=(isnull(ÆÚ³õÔö¼Ó,0)-isnull(ÆÚ³õ¼õÉÙ,0)+isnull(±¾ÆÚÔö¼Ó,0)-isnull(±¾ÆÚ¼õÉÙ,0)) from (
select ÐÕÃû,סַ,
ÆÚ³õÔö¼Ó=(select ÆÚ³õÔö¼Ó=sum(Ôö¼Ó»ý·Ö) from b where ·¢ÉúÈÕÆÚ<'2006-5-1' and ¿¨ºÅ=a.¿¨ºÅ),
ÆÚ³õ¼õÉ ......

SQL×Ô¶¯É¾³ý±¸·ÝÎļþ

DECLARE  @sql varchar(8000)  
--ÏÂÃæÊÇɾ³ý15ÌìÇ°±¸·ÝÎļþµÄ´¦Àí  
set @sql='Del E:\Databasebackup\'  
@sql=@sql +rtrim(convert(varchar,getdate()-15,112))+'.bak'
Exec  master..xp_cmdshell   @sql  
DECLARE @DelFile Varchar(256)
set ......

case when then...else end sql Óï¾äÓ÷¨

select f.AGENTID as 'ID',
          f.AGENTNAME as 'ÐÕÃû',
          f.COMNAME as '¹«Ë¾¼ò³Æ',
          c.REGISTDATE as '×¢²áʱ¼ä',
     ......

Web°²È«¿ª·¢£ºSQL×¢Èë¹¥»÷ºÍÍøÒ³¹ÒÂí

ÉêÃ÷¡£ÎÄÕ½ö´ú±í¸öÈ˹۵㣬ÓëËùÔÚ¹«Ë¾ÎÞÈκÎÁªÏµ¡£
1.     ¸ÅÊö
ÍøÒ³¹ÒÂíÕâ¸ö»°ÌâÏëÀ´´ó¼Ò²¢²»Ä°Éú¡£ÎªÊ²Ã´ÓÐÕâô¶àµÄÍøÒ³ÉÏ´æÔÚ×ÅľÂíÈ¥¹¥»÷ÆÕͨÓû§£¿²»¿É·ñÈÏ£¬Ï൱һ²¿·ÖÍøÒ³Ô­±¾¾ÍÊǶñÒâµÄ£ºÍøÒ³µÄ×÷Õß¹ÊÒâÔÚÉÏÃæ·ÅÉÏľÂí£¬È»ºóͨ¹ý¸÷ÖÖÊÖ¶ÎÒýÓÕÓû§È¥ä¯ÀÀ¡£µ«ÊǾø´ó¶àÊý±»¹ÒÂíµÄÍøÒ³Ô­±¾ÊÇÕý³ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ