SQLÓï·¨ÓëÃüÁî
SQLÓï·¨ÓëÃüÁî
SELECT Óï¾ä
SELECT[predicate]{*|table.*|[table.]field [,[table.]field2[,...]]} [AS alias1
[,alias2[,...]]]
from tableexpression [,...][IN externaldatabase]
[WHERE...]
[GROUP BY...]
[HAVING...]
[ORDER BY...]
[WITH OWNERACCESS OPTION]
SELECT Óï¾ä°üÀ¨ÏÂÃæ¼¸¸ö²¿·Ö
predicate
ÈçÇ°ÃæËùÊö£¬°üÀ¨ÁËALL£¬DISTINCT,DISTINCTROW,ÓëTOP
ÎÒÃÇ¿ÉÒÔÀûÓÃÕâÑùµÄÓï¾äÈ¥ÏÞÖÆ²éѯºóËùµÃµÄ½á¹û¡£
*
´ÓÖ¸¶¨±í¸ñÖÐÖ¸¶¨ËùÓеÄ×ֶΡ£
table
Õë¶Ô±»Ñ¡Ôñ³öµÄ¼Ç¼µÄ×ֶΣ¬ËùÖ¸¶¨±í¸ñµÄÃû³Æ¡£
field1,field2
ÏëÒª¶ÁÈ¡Êý¾ÝµÄ×Ö¶ÎÃû³Æ£¬Èç¹û°üº¬ÁËÒ»¸öÒÔÉϵÄ×ֶΣ¬»áÒÀÕÕÁгöµÄ˳ÐòÀ´¶ÁÈ¡Êý¾Ý¡£
alias1,alias2
ÓÃÀ´Ìæ´úÔÚ±í¸ñʵ¼Ê×Ö¶ÎÃû³ÆµÄ»¯Ãû¡£
tableexpression
±í¸ñÃû³Æ»ò°üº¬ÎÒÃÇËùÏëÒªµÄÊý¾ÝµÄ±í¸ñ¡£
externaldatabase
ÈôʹÓõ½²»ÊÇĿǰµÄÊý¾Ý¿âÔò½«ÆäÃû×Ö¶¨ÒåÔÚexternaldatabaseµ±ÖС£
ALL,DISTINCT,DISTINCTROW,TOPÊôÐÔ´ÊÓ÷¨
SELECT [ALL|DISTINCT|DISTINCTROW|[TOP n[PERCENT]]] from table
ALL
ÈôÊÇÄú²»Ö¸¶¨ÈκεÄ×Ö¶ÎÊý¾Ý£¬ÔòMicrosoft JetÊý¾Ý¿âÒýÇæ£¨database
engine£©½«»áÑ¡ÔñËùÓеÄ×ֶΣ¬²¢ÒÀ¾ÝËù¶¨µÄÌõ¼þ²éѯ³öÐèÇóÊý¾Ý¼¯¡£
ÀýÈçÏÂÃæÕâÁ½¸öÀý×Ó½«»á¾ßÓÐÏàͬµÄЧ¹û£¬¶¼»á´ÓÖ°Ô±±í¸ñÖзµ»ØËùÓÐ×ֶεÄÊý¾Ý¡£
ÀýÈ磺
ÈôÊÇÎÒÃÇÒª²éѯ³öÖ°Ô±±í¸ñÖеÄËùÓмǼ£¬¿ÉÒÔͨ¹ýÏÂÃæµÄÓï¾äÀ´Íê³É¡£
SELECT ALL*
from Ö°Ô±±í¸ñ;
DISTINCT
¶Ôij¸ö±í¸ñËùÑ¡ÔñµÄ×Ö¶ÎÊý¾Ý£¬ÂÔ¹ýÖØ¸´µÄÇé¿ö£¬Ò²¾ÍÊÇ˵£¬Õë¶Ôij¸ö×ֶβéѯ³öÀ´µÄ¼Ç¼½á¹ûÊÇΨһµÄ¡£ÀýÈçÓÐÐí¶à´æ·ÅÔÚÖ°Ô±±í¸ñµÄÖ°Ô±
Êý¾Ý£¬Ò²Ðí»á¾ßÓÐÏàͬµÄÐÕÃû£¬ËùÒÔÈôÊÇÎÒÃÇÓÃSQLÓï¾äÖеÄSELECT
DISTINCT£¬Ôò²éѯ³öÀ´µÄ½á¹û½«»áÕë¶Ô²»Ò»ÑùµÄÐÕÃû¼ÓÒÔɸѡ¡£ÈôÊÇÄú°Ñ
DISTINCT ¼ÓÒÔÊ¡ÂÔ£¬ÔòÕâÑùµÄ²éѯ»áÏÔʾËùÓеļǼ¡£
DISTINCTROW
½«ÕûÌõ¼ÇÂ¼ÖØ¸´µÄ¼Ç¼ºöÂÔµô£¬¶ø²»ÊÇÖ»ÓÐÕë¶Ôijһ¸ö×ֶεÄÊý¾Ý¡£
table
Ö¸¶¨²éѯ¼Ç¼ËùÐèÒªµÄ±í¸ñ¡£
ÀýÈ磺
SELECT DISTINCTROW ¹«Ë¾Ãû³Æ
from ¹Ë¿Í±í¸ñ INNER JOIN ¶©µ¥±í¸ñ
ON ¹Ë¿Í±í¸ñ.¹Ë¿ÍID£½¶©µ¥±í¸ñ.¹Ë¿ÍID
ORDER BY ¹«Ë¾Ãû³Æ;
Èç¹ûÄúºöÂÔ DISTINCTROW
Ôò»á¶Ôÿ¸ö¹«Ë¾²úÉúÒ»ÐÐÒÔÏµĶ©µ¥Êý¾Ý¡£´ËÍ⣬ÈôÊÇDISTINCTROWÖ»ÓÐÓÃÔÚÒ»¸ö±í¸ñµ±ÖУ¬Ôò»á±»Ê¡ÂÔµô¡£
TOP
´ÓµÚÒ»Ìõ»ò×îºóÒ»Ìõ¿ªÊ¼£¨ÀûÓÃORDER BYÌõ¼þ×Ӿ䣩£¬·µ»ØÌض¨ÌõÊýµÄÊý¾Ý¡£
ÀýÈ磺
µ±ÄúÏëÒªÖªµÀÔÚ2000Ä꣬°àÉÏǰ25ÃûµÄѧÉúÐÕ
Ïà¹ØÎĵµ£º
ÒÔǰÔÚ°²×°sqlµÄʱºò£¬Èç´ËÌáʾ£¬ÎÒÖ»ÒªÖØÐÂÆô¶¯¼´¿É£¬¿ÉÊǽñÌìÖØÐÂÆô¶¯ÁËN´Î¼ÆËã»ú£¬ÎÊÌâÈ´Ë¿ºÁûÓнâ¾ö£¬ÒÀÈ»ÌáʾÕâÑùµÄ»°¡£“ÒÔǰµÄij¸ö³ÌÐò°²×°ÒÑÔÚ°²×°¼ÆËã»úÉÏ´´½¨¹ÒÆðµÄÎļþ²Ù×÷¡£ÔËÐа²×°³ÌÐò֮ǰ±ØÐëÖØÐÂÆô¶¯¼ÆËã»ú¡£” Ö»ºÃgoogleÒÔÏ£¬×îÖÕµÃÖªÊǰ²×°³ÌÐòÔÚÏÈǰµÄ°²×°¹ý³ÌÖÐÔÚϵͳע²á±íÁôÏÂijЩÐÅÏ¢£ ......
--×Ô¼ººÜÓÞ´ÀµÄ×ö·¨£¬ÒÔΪֱ½Óɾ³ýÊý¾Ý¿âÓû§Ãû£¬ÔÙ´Óд´½¨Óû§µÇ¼ÃûºÍÊý¾Ý¿âÓû§Ãû¡£
--×Ô¼ºÒÔΪ¿ÉÒÔÖ±½Ó´´½¨ÓëÖ®ÔÏÈÒ»ÑùµÄÊý¾Ý¿âµÇ¼Ãû¾Í¿ÉÒÔ
--µ«ÕâЩ²Ù×÷ÊDz»Æð×÷ÓõÄ
--ÔÒò
--µ±Ó³Éä¹ÂÁ¢Óû§Ê±£¬Ö÷´ÓÊý¾Ý¿âÖеÄSID½«·ÖÅ䏸¹ÂÁ¢Óû§£¬ËùÒÔÿ´ÎÒ»¸öÊý¾Ý¿â±¸¸½¼Ó»òÕß»¹Ôʱ£¬--SIDÔÚSQL Server µÇ¼ÃûºÍÊý¾Ý¿âÓû ......
group byÖ÷ÒªÊÇÓÃÀ´·Ö×éµÄ£¬Ôõô¸ö·Ö×éÄØ£¿
ÒÔÏÂÓÃÁ½¸öÀý×Ó˵Ã÷Á½¸öʹÓ÷½Ã棬1ÊǺÏÀíµÄ·µ»ØºÏ¼ÆÖµ£¨·ÀÖ¹µÑ¿¨¶û»ýÏÖÏ󣩣¬2ÊÇÓ÷Ö×éÀ´ÕÒ³öÖØ¸´µÄ¼Ç¼
====================================================================
¡ï¡ï¡ïÀý×Ó1£º¼ÙÈçÓÐÕâôһ¸ö±í£ºtab_1£¬ËüÓÐÁ½¸ö×ֶΣºxm¡¢gzlb¡¢je£¨ÐÕÃû¡¢¹¤×ÊÀà±ð¡¢½ð¶î£© ......
Èç¹ûÄãµÄÊý¾Ý¿âÔËÐÐÔÚÍêÕû»òÊÇÅúÁ¿ÈÕÖ¾»Ö¸´Ä£Ê½Ï£¬ÄÇôÄã¾ÍÐèҪʹÓÃ×÷Òµ(job)À´¶¨ÆÚ±¸·ÝÊÂÎñÈÕÖ¾£¬±£³ÖÄãµÄÊÂÎñÎļþ´óС´¦ÔÚÒ»¸ö¿É¹ÜÀíµÄ·¶Î§¡£µ±ÄãÐèÒª»¹ÔÊÂÎñÈÕ־ʱ£¬Äã¾ÍÐèÒª°´ÕÕ´´½¨ÊÂÎñÈÕÖ¾µÄ˳ÐòÀ´»Ö¸´ËüÃÇ¡£Äã¿ÉÒԲο¼´æÔÚmsdb..backupset±íÖеÄÐÅÏ¢À´È·¶¨»¹ÔÎļþµÄ˳Ðò£¬Ê¹ÓÃFirstLSNºÍLastLSNÁеÄÖµ×÷²Î¿¼¡£µ±Ä ......
ÈÕÖ¾ÐòÁбàºÅ(LSN)ÊÇÊÂÎñÈÕÖ¾ÀïÃæÃ¿Ìõ¼Ç¼µÄ±àºÅ¡£
µ±ÄãÖ´ÐÐÒ»´Î±¸·Ýʱ£¬Ò»Ð©LSNÖµ¾Í±»Í¬Ê±´æ´¢ÔÚÎļþ±¾Éí¼°msdb..backupset±íÖС£Äã¿ÉÒÔʹÓÃRESTORE HEADERONLYÓï·¨À´´Ó±¸·ÝÎļþÖлñÈ¡LSNÖµ¡£
×¢Ò⣺ÔÚSQL Server 2000ÖУ¬ÓÐÒ»ÁнÐ×öDifferentialBaseLSN¡£µ«ÔÚSQL Server 2005ÖУ¬ÏàͬµÄÁÐÃû³Æ±ä³ÉÁËData ......