SQL ServerÓÅ»¯¶þ
1.Ó¦¾¡Á¿±ÜÃâÔÚ where ×Ó¾äÖжÔ×ֶνøÐÐ null ÖµÅжϣ¬·ñÔò½«µ¼ÖÂÒýÇæ·ÅÆúʹÓÃË÷Òý¶ø½øÐÐÈ«±íɨÃ裬È磺
select id from t where num is null
¿ÉÒÔÔÚnumÉÏÉèÖÃĬÈÏÖµ0£¬È·±£±íÖÐnumÁÐûÓÐnullÖµ£¬È»ºóÕâÑù²éѯ£º
select id from t where num=0
2.Ó¦¾¡Á¿±ÜÃâÔÚ where ×Ó¾äÖÐʹÓÃ!=»ò<>²Ù×÷·û£¬·ñÔò½«ÒýÇæ·ÅÆúʹÓÃË÷Òý¶ø½øÐÐÈ«±íɨÃè¡£ÓÅ»¯Æ÷½«ÎÞ·¨Í¨¹ýË÷ÒýÀ´È·¶¨½«ÒªÃüÖеÄÐÐÊý,Òò´ËÐèÒªËÑË÷¸Ã±íµÄËùÓÐÐС£
3.Ó¦¾¡Á¿±ÜÃâÔÚ where ×Ó¾äÖÐʹÓà or À´Á¬½ÓÌõ¼þ£¬·ñÔò½«µ¼ÖÂÒýÇæ·ÅÆúʹÓÃË÷Òý¶ø½øÐÐÈ«±íɨÃ裬È磺
select id from t where num=10 or num=20
¿ÉÒÔÕâÑù²éѯ£º
select id from t where num=10
union all
select id from t where num=20
4.in ºÍ not in Ò²ÒªÉ÷Óã¬ÒòΪIN»áʹϵͳÎÞ·¨Ê¹ÓÃË÷Òý,¶øÖ»ÄÜÖ±½ÓËÑË÷±íÖеÄÊý¾Ý¡£È磺
select id from t where num in(1,2,3)
¶ÔÓÚÁ¬ÐøµÄÊýÖµ£¬ÄÜÓà between ¾Í²»ÒªÓà in ÁË£º
select id from t where num between 1 and 3
5.¾¡Á¿±ÜÃâÔÚË÷Òý¹ýµÄ×Ö·ûÊý¾ÝÖУ¬Ê¹Ó÷ǴòÍ·×ÖĸËÑË÷¡£ÕâҲʹµÃÒýÇæÎÞ·¨ÀûÓÃË÷Òý¡£
¼ûÈçÏÂÀý×Ó£º
SELECT * from T1 WHERE NAME LIKE ‘%L%’
SELECT * from T1 WHERE SUBSTING(NAME,2,1)=’L’
SELECT * from T1 WHERE NAME LIKE ‘L%’
¼´Ê¹NAME×ֶν¨ÓÐË÷Òý£¬Ç°Á½¸ö²éѯÒÀÈ»ÎÞ·¨ÀûÓÃË÷ÒýÍê³É¼Ó¿ì²Ù×÷£¬ÒýÇæ²»µÃ²»¶ÔÈ«±íËùÓÐÊý¾ÝÖðÌõ²Ù×÷À´Íê³ÉÈÎÎñ¡£¶øµÚÈý¸ö²éѯÄܹ»Ê¹ÓÃË÷ÒýÀ´¼Ó¿ì²Ù×÷¡£
6.±ØҪʱǿÖƲéѯÓÅ»¯Æ÷ʹÓÃij¸öË÷Òý£¬ÈçÔÚ where ×Ó¾äÖÐʹÓòÎÊý£¬Ò²»áµ¼ÖÂÈ«±íɨÃè¡£ÒòΪSQLÖ»ÓÐÔÚÔËÐÐʱ²Å»á½âÎö¾Ö²¿±äÁ¿£¬µ«ÓÅ»¯³ÌÐò²»Äܽ«·ÃÎʼƻ®µÄÑ¡ÔñÍƳٵ½ÔËÐÐʱ£»Ëü±ØÐëÔÚ±àÒëʱ½øÐÐÑ¡Ôñ¡£È»¶ø£¬Èç¹ûÔÚ±àÒëʱ½¨Á¢·ÃÎʼƻ®£¬±äÁ¿µÄÖµ»¹ÊÇδ֪µÄ£¬Òò¶øÎÞ·¨×÷ΪË÷ÒýÑ¡ÔñµÄÊäÈëÏî¡£ÈçÏÂÃæÓï¾ä½«½øÐÐÈ«±íɨÃ裺
select id from t where num=@num
¿ÉÒÔ¸ÄΪǿÖƲéѯʹÓÃË÷Òý£º
select id from t with(index(Ë÷ÒýÃû)) where num=@num
7.Ó¦¾¡Á¿±ÜÃâÔÚ where ×Ó¾äÖжÔ×ֶνøÐбí´ïʽ²Ù×÷£¬Õ⽫µ¼ÖÂÒýÇæ·ÅÆúʹÓÃË÷Òý¶ø½øÐÐÈ«±íɨÃè¡£È磺
SELECT * from T1 WHERE F1/2=100
Ó¦¸ÄΪ:
SELECT * from T1 WHERE F1=100*2
SELECT * from RECORD WHERE SUBSTRING(CARD_NO,1,4)=’5378’
Ó¦¸ÄΪ:
SELECT * from RECORD WHERE CARD_NO LIKE ‘5378%’
SELECT member_number,
Ïà¹ØÎĵµ£º
¹«¸æ£º±¾²©¿ÍΪ΢ÈíÔƼÆËãÖÐÎIJ©¿ÍµÄ¾µÏñ²©¿Í£¬²¢²»±£Ö¤Á´½Ó¿ÉÓÃÐÔ£¬ÎªÁ˱£Ö¤ÔĶÁÌåÑ飬Çë·ÃÎÊhttp://blogs.msdn.com/azchina¡£
SQL Azure vs. SQL Server
¸ÅÒª
SQL Azure Database ÊÇÒ»¸öÀ´×Ô΢ÈíµÄ£¬»ùÓÚÔƵĹØϵÐÍÊý¾Ý¿â·þÎñ¡£SQL Azure Ìṩ¹ØϵÐÍÊý¾Ý¿â¹¦ÄÜÀ´×÷Ϊ¹«Ó÷þÎñ¡£»ùÓÚÔƵÄÊý¾Ý¿â½â¾ö·½°¸ ......
-- ²é¿´µ±Ç°dbµÄµÇ½
select * from sys.sql_logins
-- ²é¿´ÐÔÄܼÇÊýÆ÷
SELECT * from sys.dm_os_performance_counters
-- Ö´ÐйýµÄÏß³ÌËùÓöµ½µÄËùÓеȴý(²»Êǵ±Ç°ÕýÔÚÔËÐеÄÏß³Ì, Ϊ×ÔÉÏ´ÎÖØÖÃͳ¼ÆÐÅÏ¢»òÆô¶¯·þÎñÆ÷ÒÔÀ´ÀÛ»ýµÄÊý¾Ý),¿É·ÖÎö¿¿Ç°µÄ¼¸¸öµÈ´ý½Ï¸ßµÄʼþ¡£
select * from sys.dm_os_wait_stats order by wa ......
·ÖÀàͳ¼Æ×ÜÊý²¢ÅÅÐò¶à±íÁªºÏ²éѯµÄÁ½ÖÖ·½·¨
Àý:
²éѯµØÇø±íÖи÷Ê¡ÏÂÃæ³ÇÊÐ×ÜÊý,²¢¶Ô²éѯ½á¹ûÈ¡Ç°Ê®Ãû
·¨Ò»:
select b.id,a.[name],b.counts from n_area a,(select top 10 parent_id as id,sum(parent_id) as counts from n_area where parent_id<>0 group by parent_id order by sum(parent_id) desc
) b where ......
×î½üÓöµ½Ò»¸öÏîÄ¿£¬¿Í»§ÔÚ×ܲ¿Â¼ÈëÐÅÏ¢ºó£¬Êý¾ÝÐèҪͬ²½µ½ÏÂÊôµÄÈô¸É¸ö×ÓϵͳÖС£ÏÖÔÚ×ܲ¿µ¥¶ÀʹÓÃÒ»¸öÊý¾Ý¿â£¬ÏÂÊôµ¥Î»¸÷×ÔʹÓÃ×Ô¼ºµÄÊý¾Ý¿â¡£
¶ÔÓÚÕâ¸öÎÊÌ⣬Ïëµ½Á˼¸¸ö½â¾ö·½°¸£º
1¡¢×Ô¼ºÐ´´úÂë½â¾ö¡£¶¨ÒåºÃ·ÃÎÊÿ¸öÊý¾ ......
SQL code
´´½¨½ÇÉ«£¬Óû§£¬È¨ÏÞ
/*--ʾÀý˵Ã÷
ʾÀýÔÚÊý¾Ý¿âpubsÖд´½¨Ò»¸öÓµÓбíjobsµÄËùÓÐȨÏÞ¡¢ÓµÓбítitlesµÄSELECTȨÏ޵ĽÇÉ«r_test
Ëæºó´´½¨ÁËÒ»¸öµÇ¼l_test£¬È»ºóÔÚÊý¾Ý¿âpubsÖÐΪµÇ¼l_test´´½¨ÁËÓû§ÕË»§u_test
ͬʱ½«Óû§ÕË»§u_testÌí¼Óµ½½ÇÉ«r_testÖУ¬Ê¹Æäͨ¹ýȨÏ޼̳лñÈ¡ÁËÓë½ÇÉ«r_t ......