SQL Server ¾Ñé £¨×ªÔØ£©
SQL Server ¾Ñé £¨×ªÔØ£©
http://www.cnblogs.com/treeyh/archive/2007/08/06/844763.html
Èç¹ûÄãÕýÔÚ¸ºÔðÒ»¸ö»ùÓÚSQL ServerµÄÏîÄ¿£¬»òÕßÄã¸Õ¸Õ½Ó´¥SQL Server£¬Ä㶼ÓпÉÄÜÒªÃæÁÙһЩÊý¾Ý¿âÐÔÄܵÄÎÊÌ⣬ÕâƪÎÄÕ»áΪÄãÌṩһЩÓÐÓõÄÖ¸µ¼£¨ÆäÖдó¶àÊýÒ²¿ÉÒÔÓÃÓÚÆäËüµÄDBMS£©¡£
ÔÚÕâÀÎÒ²»´òËã½éÉÜʹÓÃSQL ServerµÄÇÏÃÅ£¬Ò²²»ÄÜÌṩһ¸ö°üÖΰٲ¡µÄ·½°¸£¬ÎÒËù×öµÄÊÇ×ܽáһЩ¾Ñé----¹ØÓÚÈçºÎÐγÉÒ»¸öºÃµÄÉè¼Æ¡£ÕâЩ¾ÑéÀ´×ÔÎÒ¹ýÈ¥¼¸ÄêÖоÊܵĽÌѵ£¬Ò»Ö±À´£¬ÎÒ¿´µ½Ðí¶àͬÑùµÄÉè¼Æ´íÎó±»Ò»´ÎÓÖÒ»´ÎµÄÖظ´¡£ ¡¡
Ò»¡¢Á˽âÄãÓõŤ¾ß ¡¡¡¡
²»ÒªÇá
ÊÓÕâÒ»µã£¬ÕâÊÇÎÒÔÚÕâƪÎÄÕÂÖн²ÊöµÄ×î¹Ø¼üµÄÒ»Ìõ¡£Ò²ÐíÄãÒ²¿´µ½ÓкܶàµÄSQL Server³ÌÐòԱûÓÐÕÆÎÕÈ«²¿µÄT-SQLÃüÁîºÍSQL
ServerÌṩµÄÄÇЩÓÐÓõŤ¾ß¡£
¡¡¡¡“ʲô£¿ÎÒÒªÀË·ÑÒ»¸öÔµÄʱ¼äÀ´Ñ§Ï°ÄÇЩÎÒÓÀÔ¶Ò²²»»áÓõ½µÄSQLÃüÁ£¿£¿”£¬ÄãÒ²Ðí»áÕâÑù˵¡£¶ÔµÄ£¬Äã²»ÐèÒªÕâÑù×ö¡£µ«ÊÇÄãÓ¦¸ÃÓÃÒ»¸öÖÜÄ©ä¯ÀÀËù
ÓеÄT-SQLÃüÁî¡£ÔÚÕâÀÄãµÄÈÎÎñÊÇÁ˽⣬½«À´£¬µ±ÄãÉè¼ÆÒ»¸ö²éѯʱ£¬Äã»á¼ÇÆðÀ´£º“¶ÔÁË£¬ÕâÀïÓÐÒ»¸öÃüÁî¿ÉÒÔÍêȫʵÏÖÎÒÐèÒªµÄ¹¦ÄÜ”£¬ÓÚÊÇ£¬µ½
MSDN²é¿´Õâ¸öÃüÁîµÄÈ·ÇÐÓï·¨¡£ ¡¡¡¡
¶þ¡¢²»ÒªÊ¹ÓÃÓÎ±ê ¡¡¡¡
ÈÃÎÒÔÙÖظ´Ò»±é£º²»ÒªÊ¹ÓÃÓαꡣÈç¹ûÄãÏëÆÆ»µÕû¸öϵͳµÄÐÔ
ÄܵĻ°£¬ËüÃǵ¹ÊÇÄã×îÓÐЧµÄÊ×Ñ¡°ì·¨¡£´ó¶àÊýµÄ³õѧÕ߶¼Ê¹ÓÃÓα꣬¶øûÓÐÒâʶµ½ËüÃǶÔÐÔÄÜÔì³ÉµÄÓ°Ïì¡£ËüÃÇÕ¼ÓÃÄڴ棬»¹ÓÃËüÃÇÄÇЩ²»¿É˼ÒéµÄ·½Ê½Ëø¶¨±í
£¬
ÁíÍ⣬ËüÃǼòÖ±¾ÍÏñÎÏÅ£¡£¶ø×îÔã¸âµÄÊÇ£¬ËüÃÇ¿ÉÒÔʹÄãµÄDBAËùÄÜ×öµÄÒ»ÇÐÐÔÄÜÓÅ»¯µÈÓÚû×ö¡£²»ÖªÄãÊÇ·ñÖªµÀÿִÐÐÒ»´ÎFETCH¾ÍµÈÓÚÖ´ÐÐÒ»´Î
SELECTÃüÁî
£¿ÕâÒâζ×ÅÈç¹ûÄãµÄÓαêÓÐ10000Ìõ¼Ç¼£¬Ëü½«Ö´ÐÐ10000´ÎSELECT£¡Èç¹ûÄãʹÓÃÒ»×é
SELECT¡¢UPDATE»òÕßDELETEÀ´Íê³ÉÏàÓ¦µÄ¹¤×÷£¬Äǽ«ÓÐЧÂʵĶࡣ ¡¡¡¡
³õѧÕßÒ»°ãÈÏΪʹÓÃÓαêÊÇÒ»ÖֱȽÏÊìϤºÍÊæÊʵıà³Ì·½Ê½£¬¿ÉºÜ²»ÐÒ£¬Õâ»áµ¼ÖÂÔã¸âµÄÐÔÄÜ¡£ÏÔÈ»£¬SQLµÄ×ÜÌåÄ¿µÄÊÇÄãҪʵÏÖʲô£¬¶ø²»ÊÇÔõÑùʵÏÖ¡£
ÎÒÔø¾ÓÃT-SQLÖØдÁËÒ»¸ö»ùÓÚÓαêµÄ´æ´¢¹ý³Ì£¬ÄǸö±íÖ»ÓÐ100,000Ìõ¼Ç¼£¬ÔÀ´µÄ´æ´¢¹ý³ÌÓÃÁË40·ÖÖÓ²ÅÖ´ÐÐÍê±Ï£¬¶øеĴ洢¹ý³ÌÖ»ÓÃÁË10ÃëÖÓ¡£ÔÚÕâÀÎÒÏëÄãÓ¦¸Ã¿ÉÒÔ¿´µ½Ò»¸ö²»³ÆÖ°µÄ³ÌÐòÔ±¾¿¾¹ÔÚ¸ÉÁËʲô£¡£¡£¡ ¡¡¡¡
ÎÒÃÇ¿ÉÒÔдһ¸öС³ÌÐòÀ´È¡µÃºÍ´¦ÀíÊý¾Ý²¢ÇÒ¸üÐÂÊý¾Ý¿â
Ïà¹ØÎĵµ£º
select top Ò³´óС *
from table1
where id>
(select max (id) from
(select top ((Ò³Âë-1)*Ò³´óС) id from table1 order by id) as T) order by id ......
1.¶Ô²éѯ½øÐÐÓÅ»¯£¬Ó¦¾¡Á¿±ÜÃâÈ«±íɨÃ裬Ê×ÏÈÓ¦¿¼ÂÇÔÚ where ¼° order by Éæ¼°µÄÁÐÉϽ¨Á¢Ë÷Òý¡£
2.Ó¦¾¡Á¿±ÜÃâÔÚ where ×Ó¾äÖжÔ×ֶνøÐÐ null ÖµÅжϣ¬·ñÔò½«µ¼ÖÂÒýÇæ
·ÅÆúʹÓÃË÷Òý¶ø½øÐÐÈ«±íɨÃ裬È磺
select id from t where num is null
¿ÉÒÔÔÚnumÉÏÉèÖÃ
ĬÈÏÖµ0£¬È·±£±íÖÐnumÁÐûÓÐnullÖµ£¬È»ºóÕâ
Ñù²éѯ£º
sel ......
¾³£»á¿´¼ûÔÚSQL³ÌÐòµÄ¿ªÍ·ÓÐÕâÑùÒ»¾ä»°
if OBJECT_ID('tb') is not null
drop table tb
º¯ÊýÓï·¨ÊÇÕâÑù£º
int OBJECT_ID('objectname');
×÷ÓÃÊÇ¿´¶ÔÏóobjectnameÊÇ·ñ´æÔÚ¡£
ÆäÖвÎÊýobjectname±íʾҪʹÓõĶÔÏó£¬ÊÇchar»òÕßncharÀàÐÍ¡£
·µ»ØÖµÀàÐÍΪint£¬Èç¹û¶ÔÏó´æÔÚ£¬Ôò·µ»Ø´Ë¶ÔÏóÔÚϵͳÖеı ......
SQL³£ÓÃ×Ö·û´®º¯Êý
Ò»¡¢×Ö·ûת»»º¯Êý
1¡¢ASCII()
·µ»Ø×Ö·û±í´ïʽ×î×ó¶Ë×Ö·ûµÄASCII ÂëÖµ¡£ÔÚASCII£¨£©º¯ÊýÖУ¬´¿Êý×ÖµÄ×Ö·û´®¿É²»ÓÑ’À¨ÆðÀ´£¬µ«º¬ÆäËü×Ö·ûµÄ×Ö·û´®±ØÐëÓÑ’À¨ÆðÀ´Ê¹Ó㬷ñÔò»á³ö´í¡£
2¡¢CHAR()
½«ASCII Âëת»»Îª×Ö·û¡£Èç¹ûûÓÐÊäÈë0 ~ 255 Ö®¼ä ......
Ò»¡¢SQL´æ´¢¹ý³ÌµÄ¸ÅÄÓŵ㼰Óï·¨
¡¡¡¡ÕûÀíÔÚѧϰ³ÌÐò¹ý³Ì֮ǰ£¬ÏÈÁ˽âÏÂʲôÊÇ´æ´¢¹ý³Ì?ΪʲôҪÓô洢¹ý³Ì£¬ËûÓÐÄÇЩÓŵã
¡¡¡¡¶¨Ò壺½«³£ÓõĻòºÜ¸´ÔӵŤ×÷£¬Ô¤ÏÈÓÃSQLÓï¾äдºÃ²¢ÓÃÒ»¸öÖ¸¶¨µÄÃû³Æ´æ´¢ÆðÀ´, ÄÇôÒÔºóÒª½ÐÊý¾Ý¿âÌṩÓëÒѶ¨ÒåºÃµÄ´æ´¢¹ý³ÌµÄ¹¦ÄÜÏàͬµÄ·þÎñʱ,Ö»Ðèµ÷ÓÃexecute,¼´¿É×Ô¶¯Íê³ ......