SQL ÈÕÆÚº¯Êý¼¯ÈÕÆÚ´¦Àí·½·¨·¶Àý
--¶¼Ð´ÁË ²Î¿¼Ï°É
ͨ³££¬ÄãÐèÒª»ñµÃµ±Ç°ÈÕÆںͼÆËãһЩÆäËûµÄÈÕÆÚ£¬ÀýÈ磬ÄãµÄ³ÌÐò¿ÉÄÜÐèÒªÅжÏÒ»¸öÔµĵÚÒ»Ìì»òÕß×îºóÒ»Ìì¡£ÄãÃǴ󲿷ÖÈË´ó¸Å¶¼ÖªµÀÔõÑù°ÑÈÕÆÚ½øÐзָÄê¡¢Ô¡¢Èյȣ©£¬È»ºó½ö½öÓ÷ָî³öÀ´µÄÄê¡¢Ô¡¢ÈյȷÅÔÚ¼¸¸öº¯ÊýÖмÆËã³ö×Ô¼ºËùÐèÒªµÄÈÕÆÚ£¡ÔÚÕâƪÎÄÕÂÀÎÒ½«¸æËßÄãÈçºÎʹÓÃDATEADDºÍDATEDIFFº¯ÊýÀ´¼ÆËã³öÔÚÄãµÄ³ÌÐòÖпÉÄÜÄãÒªÓõ½µÄһЩ²»Í¬ÈÕÆÚ¡£
ÔÚʹÓñ¾ÎÄÖеÄÀý×Ó֮ǰ£¬Äã±ØÐë×¢ÒâÒÔϵÄÎÊÌâ¡£´ó²¿·Ö¿ÉÄܲ»ÊÇËùÓÐÀý×ÓÔÚ²»Í¬µÄ»úÆ÷ÉÏÖ´ÐеĽá¹û¿ÉÄܲ»Ò»Ñù£¬ÕâÍêÈ«ÓÉÄÄÒ»ÌìÊÇÒ»¸öÐÇÆڵĵÚÒ»ÌìÕâ¸öÉèÖþö¶¨¡£µÚÒ»Ì죨DATEFIRST£©É趨¾ö¶¨ÁËÄãµÄϵͳʹÓÃÄÄÒ»Ìì×÷ΪһÖܵĵÚÒ»Ìì¡£ËùÓÐÒÔϵÄÀý×Ó¶¼ÊÇÒÔÐÇÆÚÌì×÷ΪһÖܵĵÚÒ»ÌìÀ´½¨Á¢£¬Ò²¾ÍÊǵÚÒ»ÌìÉèÖÃΪ7¡£¼ÙÈçÄãµÄµÚÒ»ÌìÉèÖò»Ò»Ñù£¬Äã¿ÉÄÜÐèÒªµ÷ÕûÕâЩÀý×Ó£¬Ê¹ËüºÍ²»Í¬µÄµÚÒ»ÌìÉèÖÃÏà·ûºÏ¡£Äã¿ÉÒÔͨ¹ý@@DATEFIRSTº¯ÊýÀ´¼ì²éµÚÒ»ÌìÉèÖá£
ΪÁËÀí½âÕâЩÀý×Ó£¬ÎÒÃÇÏȸ´Ï°Ò»ÏÂDATEDIFFºÍDATEADDº¯Êý¡£DATEDIFFº¯Êý¼ÆËãÁ½¸öÈÕÆÚÖ®¼äµÄСʱ¡¢Ìì¡¢ÖÜ¡¢Ô¡¢ÄêµÈʱ¼ä¼ä¸ô×ÜÊý¡£DATEADDº¯Êý¼ÆËãÒ»¸öÈÕÆÚͨ¹ý¸øʱ¼ä¼ä¸ô¼Ó¼õÀ´»ñµÃÒ»¸öеÄÈÕÆÚ¡£ÒªÁ˽â¸ü¶àµÄDATEDIFFºÍDATEADDº¯ÊýÒÔ¼°Ê±¼ä¼ä¸ô¿ÉÒÔÔĶÁ΢ÈíÁª»ú°ïÖú¡£
ʹÓÃDATEDIFFºÍDATEADDº¯ÊýÀ´¼ÆËãÈÕÆÚ£¬ºÍ±¾À´´Óµ±Ç°ÈÕÆÚת»»µ½ÄãÐèÒªµÄÈÕÆڵĿ¼ÂÇ·½·¨Óе㲻ͬ¡£Äã±ØÐë´Óʱ¼ä¼ä¸ôÕâ¸ö·½ÃæÀ´¿¼ÂÇ¡£±ÈÈ磬´Óµ±Ç°ÈÕÆÚµ½ÄãÒªµÃµ½µÄÈÕÆÚÖ®¼äÓжàÉÙʱ¼ä¼ä¸ô£¬»òÕߣ¬´Ó½ñÌ쵽ijһÌ죨±ÈÈç1900-1-1£©Ö®¼äÓжàÉÙʱ¼ä¼ä¸ô£¬µÈµÈ¡£Àí½âÔõÑù×ÅÑÛÓÚʱ¼ä¼ä¸ôÓÐÖúÓÚÄãÇáËɵÄÀí½âÎҵIJ»Í¬µÄÈÕÆÚ¼ÆËãÀý×Ó¡£
Ò»¸öÔµĵÚÒ»Ìì
µÚÒ»¸öÀý×Ó£¬ÎÒ½«¸æËßÄãÈçºÎ´Óµ±Ç°ÈÕÆÚÈ¥Õâ¸öÔµÄ×îºóÒ»Ìì¡£Çë×¢Ò⣺Õâ¸öÀý×ÓÒÔ¼°ÕâƪÎÄÕÂÖеÄÆäËûÀý×Ó¶¼½«Ö»Ê¹ÓÃDATEDIFFºÍDATEADDº¯ÊýÀ´¼ÆËãÎÒÃÇÏëÒªµÄÈÕÆÚ¡£Ã¿Ò»¸öÀý×Ó¶¼½«Í¨¹ý¼ÆË㵫ǰµÄʱ¼ä¼ä¸ô£¬È»ºó½øÐмӼõÀ´µÃµ½ÏëÒª¼ÆËãµÄÈÕÆÚ¡£
ÕâÊǼÆËãÒ»¸öÔµÚÒ»ÌìµÄSQL ½Å±¾£º
SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
&nbs
Ïà¹ØÎĵµ£º
SQLÓÅ»¯µÄÔÔòÊÇ£º½«Ò»´Î²Ù×÷ÐèÒª¶ÁÈ¡µÄBLOCKÊý¼õµ½×îµÍ¡£
µ÷Õû²»Á¼SQLͨ³£¿ÉÒÔ´ÓÒÔϼ¸µãÇÐÈ룺
¼ì²é²»Á¼µÄSQL£¬¿¼ÂÇÆäд·¨ÊÇ·ñ»¹ÓпÉÓÅ»¯ÄÚÈÝ£»
¼ì²é×Ó²éѯ¿¼ÂÇSQL×Ó²éѯÊÇ·ñ¿ÉÒÔÓüòµ¥Á¬½ÓµÄ·½Ê½½øÐÐÖØÐÂÊéд£»
¼ì²éÓÅ»¯Ë÷ÒýµÄʹÓã»
¿¼ÂÇÊý¾Ý¿âµÄÓÅ»¯Æ÷;
²éѯµÄÒ»°ã¹æÔò
Ø ......
Select * from t_user_profile where convert ( varchar ( 21 ),regDate, 120 ) like ' 2008-05-07% ' ±íÃû³Æ£ºt_user_profile ÈÕÆÚ×Ö¶ÎÃû³Æ£ºregDate
Select * from t_user_profile where convert(varchar(21),regDate,120) like '2008-05-07%'< ......
1¡¢ISNULL ( check_expression , replacement_value )
²ÎÊý
check_expression
½«±»¼ì²éÊÇ·ñΪ NULLµÄ±í´ïʽ¡£check_expression ¿ÉÒÔÊÇÈκÎÀàÐ͵ġ£
replacement_valueÔÚ check_expressionΪNULLʱ½«·µ»ØµÄ±í´ïʽ¡£replacement_value ±ØÐëÓë check_expresssion ¾ßÓÐÏàͬµÄÀàÐÍ¡£
·µ»ØÀàÐÍ
·µ»ØÓë check_expression Ï ......
1.³£Á¿
¶¨ÒåÓï·¨¸ñʽ£º
³£Á¿Ãû constant ÀàÐͱêʶ·û [not null]:=Öµ;
È磺PI constant number(9):=3.1415;
2.»ù±¾Êý¾ÝÀàÐͱäÁ¿
»ù±¾Êý¾ÝÀàÐÍ
number Êý×ÖÐÍ
int ÕûÊýÐÍ
pls_integer ÕûÊýÐÍ£¬²úÉúÒç³öʱ³öÏÖ´íÎó
binary_integer ÕûÊýÐÍ£¬±íʾ´ø·ûºÅµÄÕûÊý
char ¶¨³¤×Ö·ûÐÍ£¬×î´ó255¸ö×Ö·û
varchar2 ±ä³¤× ......
Oracle9iÒì³£´¦Àí·ÖΪϵͳԤ¶¨ÒåÒì³£´¦ÀíºÍ×Ô¶¨ÒåÒì³£´¦ÀíÁ½²¿·Ö¡£
×Ô¶¨ÒåÒì³£´¦Àí
1.¶¨ÒåÒì³£´¦Àí
declare Òì³£Ãû exception;
2.´¥·¢Òì³£´¦Àí
raise Òì³£Ãû
3.´¦ÀíÒì³£
exception
when Òì³£Ãû1 then
Òì³£´¦ÀíÓï¾ä¶Î1;
when Òì³£Ãû2 then
Òì³£´¦ÀíÓï¾ä¶Î2;
ʾÀý£º
se ......