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

³ÌÐò¿ª·¢: Oracle¸÷ÖÖÈÕÆÚ¼ÆËã·½·¨(ÊÕ²Ø)

Ò»¸öÔµĵÚÒ»Ìì
´úÂë (Ë«»÷´úÂë¸´ÖÆµ½Õ³Ìù°å)
SELECT to_date(to_char(SYSDATE,'yyyy-mm')||'-01','yyyy-mm-dd')
from dual

sysdate ΪÊý¾Ý¿â·þÎñÆ÷µÄµ±Ç°ÏµÍ³Ê±¼ä¡£
to_char Êǽ«ÈÕÆÚÐÍתΪ×Ö·ûÐ͵ĺ¯Êý¡£
to_date Êǽ«×Ö·ûÐÍתΪÈÕÆÚÐ͵ĺ¯Êý£¬Ò»°ãʹÓà yyyy-mm-dd hh24:mi:ss¸ñʽ£¬µ±Ã»ÓÐÖ¸¶¨Ê±¼ä²¿·Öʱ£¬ÔòĬÈÏʱ¼äΪ 00:00:00
dual ±íΪsysÓû§µÄ±í£¬Õâ¸ö±í½öÓÐÒ»Ìõ¼Ç¼£¬¿ÉÒÔÓÃÓÚ¼ÆËãһЩ±í´ïʽ£¬Èç¹ûÓкÃÊÂÕßÓà sys Óû§µÇ¼ϵͳ£¬È»ºóÔÚ dual ±íÔö¼ÓÁ˼ǼµÄ»°£¬ÄÇôϵͳ99.999%²»ÄÜʹÓÃÁË¡£ÎªÊ²Ã´Ê¹ÓõÄʱºò²»Óà sys.dual ¸ñÊ½ÄØ£¬ÒòΪ sys ÒѾ­Îª dual ±í½¨Á¢ÁËËùÓÐÓû§¾ù¿ÉʹÓõıðÃû¡£
Ò»ÄêµÄµÚÒ»Ìì
´úÂë (Ë«»÷´úÂë¸´ÖÆµ½Õ³Ìù°å)
SELECT to_date(
to_char(SYSDATE,'yyyy')||'-01-01','yyyy-mm-dd'
)
from dual

¼¾¶ÈµÄµÚÒ»Ìì
´úÂë (Ë«»÷´úÂë¸´ÖÆµ½Õ³Ìù°å)
SELECT to_date(
to_char(SYSDATE,'yyyy-')||
lpad(floor(to_number(to_char(SYSDATE,'mm'))/3)*3+1,2,'0')||
'-01',
'yyyy-mm-dd')
from dual

floor ΪÏòÏÂÈ¡Õû
lpad ΪÏò×óʹÓÃÖ¸¶¨µÄ×Ö·ûÀ©³ä×Ö·û´®£¬Õâ¸öÀ©³ä×Ö·û´®ÖÁ2룬²»×ãµÄ²¹'0'¡£
µ±ÌìµÄ°ëÒ¹
SELECT trunc(SYSDATE)+1-1/24/60/60
from dual
trunc Êǽ« sysdate µÄʱ¼ä²¿·Ö½Øµô£¬¼´Ê±¼ä²¿·Ö±ä³É 00:00:00
OracleÖÐÈÕÆÚ¼Ó¼õÊǰ´ÕÕÌìÊý½øÐеģ¬ËùÒÔ +1-1/24/60/60 ʹʱ¼ä²¿·Ö±ä³ÉÁË 23:59:59¡£
Oracle 8i ÖнöÖ§³Öʱ¼äµ½Ã룬9iÒÔÉÏÔòÖ§³Öµ½ 1/100000000 Ãë¡£
ÉϸöÔµÄ×îºóÒ»Ìì
´úÂë (Ë«»÷´úÂë¸´ÖÆµ½Õ³Ìù°å)
SELECT trunc(last_day(add_months(SYSDATE,-1)))+1-1/24/60/60
from dual

add_months ÊÇÔ·ݼӼõº¯Êý¡£
last_day ÊÇÇó¸ÃÔ·ݵÄ×îºóÒ»ÌìµÄº¯Êý¡£
±¾ÄêµÄ×îºóÒ»Ìì
´úÂë (Ë«»÷´úÂë¸´ÖÆµ½Õ³Ìù°å)
SELECT trunc(
last_day(to_date(to_char(SYSDATE,'yyyy')||'-12-01','yyyy-mm-dd'))
)+1-1/24/60/60
from dual

±¾ÔµÄ×îºóÒ»Ìì
´úÂë (Ë«»÷´úÂë¸´ÖÆµ½Õ³Ìù°å)
select trunc(last_day(sysdate))+1-1/24/60/60
from dual

±¾ÔµĵÚÒ»¸öÐÇÆÚÒ»
´úÂë (Ë«»÷´úÂë¸´ÖÆµ½Õ³Ìù°å)
SELECT next_day(
to_date(to_char(SYSDATE,'yyyy-mm')||'-01','yyyy-mm-dd'),
'ÐÇÆÚÒ»'
)


Ïà¹ØÎĵµ£º

Windows 7Éϳɹ¦°²×°Oracle 10gµÄÒ»µã¾­Ñé

Windows 7ÕæÊÇÈÃÈËÓÖ°®ÓÖºÞ°¡£¡±¾È˵çÄÔ֮ǰÒѰ²×° SQL Server 2005£¬°²×°¹ý³Ì¶¼Ã»Ê²Ã´ÎÊÌ⣬ºÜ¿ì¸ã¶¨£¬¿ÉÊÇ×°Ò»¸öOracle 10gÈ´»¨ÁËÎÒÒ»¸öÍíÉϵÄʱ¼ä£¡²»¹ý×ÜËã°²×°³É¹¦ÁË£¡ËäÈ»»¹Ã»ÓÐÕýʽ¿ªÊ¼Ê¹Ó㬵«ÊÇÐË·ÜÖ®ÓàÆÈ²»¼°´ýÒªºÍ´ó¼Ò·ÖÏíһϾ­Ñ飡
Ê×ÏÈÒªÏÂÔØÖ§³ÖVista°æ±¾µÄOracle 10g£¨ÒÔÏÂÁ´½ÓµØÖ·ÔÚä¯ÀÀÆ÷Öдò¿ªÃ»Ó㬸´ ......

Oracle³£ÓÃÈÕÆÚÊý¾Ý¸ñʽ

Ò»¡¢ ³£ÓÃÈÕÆÚÊý¾Ý¸ñʽ
 
1.Y»òYY»òYYY ÄêµÄ×îºóһ룬Á½Î»»òÈýλ
SQL> Select to_char(sysdate,'Y') from dual;
TO_CHAR(SYSDATE,'Y')
--------------------
7
SQL> Select to_char(sysdate,'YY') from dual;
TO_CHAR(SYSDATE,'YY')
---------------------
07
SQL> Select to_char(sysdate,'YYY ......

oracleÊý¾Ý¿â

¹Ø¼ü×Ö: oracleÊý¾Ý¿â
OracleÊý¾Ý¿âÃüÁ
1¡¢sqlplusÆô¶¯·½Ê½£º
DosÏÂÔËÐÐsqlplus Óû§Ãû/ÃÜÂë  as sysdba
ÀýÈ磺c:>sqlplus sys/password AS sydba(ϵͳÕÊ»§)
»òÕߣºc:>sqlplus scott/password
2¡¢Óû§Á¬½Ó(Çл»)²Ù×÷£º
sql>conn Scott/password(ĬÈÏΪtiger)
×¢£ºconnÓëconnectʹÓ÷½·¨Ò» ......

¹ØÓÚoracleѧϰ,¶ÔÕÒ×ÊÁϽ׶εÄÓÐÒâÒå

ƽʱ½Ó´¥mysql½Ï¶à£¬ÒòΪmysqlÔÚÒ»°ãµÄСÏîÄ¿Öй»ÓÃÁË ¿ªÔ´¶øÇÒÃâ·Ñ¡£
ºÜ¶à¹«Ë¾ÐèÒªoracleά»¤µÄ£¬¿ª·¢oracleÉõÖÁÊǶþ´Î¿ª·¢ÔÚ¹úÄÚÒ²±È½ÏÉٵģ¬Óм׹ÇÎĹ«Ë¾£¬Ë­¸ÒºÍËûÃÇÇÀ·¹Íë°¡ ¹þ¹þ ²»¹ýÈ˼ҵÄÊÕ·ÑÂù¹óµÄ£¬ÄãÈç¹ûÓÐˮƽ£¬³Ôµã²Ð¸þµÄ»ú»áÂù¶à¡£ oracleµÄÅàѵÐÅÏ¢±í´ïÁËÊг¡ÐèÇó¡£
£¨ÎÒÔÚѧУ»·¾³µÄ²ÂÏ룬רҵÈËÊ¿À´ÅÄש ......

oracleÖÐÈ¥ÖØ¸´¼Ç¼,²»ÓÃdistinct

ÓÃdistinct¹Ø¼ü×ÖÖ»ÄܹýÂ˲éѯ×Ö¶ÎÖÐËùÓмǼÏàͬµÄ£¨¼Ç¼¼¯Ïàͬ£©£¬¶øÈç¹ûÒªÖ¸¶¨Ò»¸ö×Ö¶ÎȴûÓÐЧ¹û£¬ÁíÍâdistinct¹Ø¼ü×Ö»áÅÅÐò£¬Ð§Âʺܵ͡£
select distinct name from t1 ÄÜÏû³ýÖØ¸´¼Ç¼£¬µ«Ö»ÄÜȡһ¸ö×ֶΣ¬ÏÖÔÚҪͬʱȡid,nameÕâ2¸ö×ֶεÄÖµ¡£
select distinct id,name from t1 ¿ÉÒÔÈ¡¶à¸ö×ֶΣ¬µ«Ö»ÄÜÏû³ýÕâ2¸ö×Ö¶ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ