{"rsdb":{"rid":"301420","subhead":"","postdate":"0","aid":"216722","fid":"57","uid":"1","topic":"1","content":"
\u5b57\u7b26\u4e32\u51fd\u6570<\/strong><\/p> 1\u3001 \u5927\u5c0f\u5199\u8f6c\u6362\u51fd\u6570\uff1a 2\u3001 \u9996\u5b57\u6bcd\u5927\u5199\uff0c\u5176\u4f59\u5b57\u6bcd\u5c0f\u5199\uff1a\u5b57\u7b26\u4e32INITCAP(\u5b57\u7b26\u4e32 | \u5217)<\/p> 3\u3001 \u53d6\u5f97\u5b57\u7b26\u4e32\u957f\u5ea6\uff1a\u6570\u5b57 LENGTH(\u5b57\u7b26\u4e32 |<\/p> 4\u3001 \u66ff\u6362\u6307\u5b9a\u5b57\u7b26\u4e32\u7684\u5185\u5bb9\uff1a\u5b57\u7b26\u4e32 REPLACE(\u5b57\u7b26\u4e32 | \u5217\uff0c\u8981\u66ff\u6362\u7684\u5185\u5bb9\uff0c\u65b0\u7684\u5185\u5bb9)<\/p> 5\u3001 \u5b57\u7b26\u4e32\u622a\u53d6\u64cd\u4f5c\uff1a \u5b57\u7b26\u4e32\u7684\u7d22\u5f15\u4e0b\u6807\u662f\u4ece 1 \u5f00\u59cb\u7684<\/p> SUBSTR()\u51fd\u6570\u8fd8\u53ef\u4ee5\u8bbe\u7f6e\u4e3a\u8d1f\u6570\uff0c\u8868\u793a\u7531\u540e\u7684\u6307\u5b9a\u4f4d\u7f6e\u5f00\u59cb<\/p> 6\u3001 \u53bb\u6389\u5de6\u53f3\u7a7a\u683c\u51fd\u6570\uff1a\u5b57\u7b26\u4e32 TRIM(\u5b57\u7b26\u4e32 | \u5217)<\/p> \u6570\u5b57\u51fd\u6570<\/strong><\/p> 1\u3001 \u56db\u820d\u4e94\u5165\u51fd\u6570\uff1a\u6570\u5b57 ROUND(\u6570\u5b57 | \u5217 [\uff0c\u4fdd\u7559\u5c0f\u6570\u4f4d])<\/p> 2\u3001 \u622a\u53d6\u5c0f\u6570\u51fd\u6570\uff1a\u6570\u5b57 TRUNC(\u6570\u5b57 | \u5217 [\uff0c\u4fdd\u7559\u5c0f\u6570\u4f4d])<\/p> \u3000\u3000\u6b63\u6570\u5411\u540e\u622a\u53d6\uff0c\u8d1f\u6570\u5411\u524d\u622a\u53d6<\/p> select trunc(sysdate, 'yy') from dual;--\u5f53\u5e74\u7b2c\u4e00\u5929<\/p> select trunc(sysdate, 'q') from dual;--\u5f53\u524d\u65f6\u95f4\u6240\u5728\u7684\u5b63\u5ea6\u7684\u7b2c\u4e00\u5929<\/p> select trunc(sysdate, 'mm') from dual;--\u5f53\u6708\u7b2c\u4e00\u5929<\/p> select trunc(sysdate, 'd') from dual;--\u8fd4\u56de\u672c\u5468\u7684\u7b2c\u4e00\u5929\uff08\u5468\u65e5\u4e3a\u7b2c\u4e00\u5929\uff09<\/p> 3\u3001 \u6c42\u6a21\uff1a\u6570\u5b57 MOD(\u6570\u5b57 1 | \u5217 1\uff0c\u6570\u5b57 2 | \u5217 2)<\/p> \u65e5\u671f\u51fd\u6570<\/strong><\/p> \u5f53\u524d\u7684\u7cfb\u7edf\u65e5\u671f\u65f6\u95f4\uff0c\u4f2a\u5217\uff1aSYSDATE\u3001SYSTIMESTAMP\u3002<\/p> · \u65e5\u671f + \u6570\u5b57 = \u65e5\u671f\uff0c\u8868\u793a\u82e5\u5e72\u5929\u4e4b\u540e\u7684\u65e5\u671f\uff1b 1\u3001 \u8ba1\u7b97\u4e24\u4e2a\u65e5\u671f\u4e4b\u95f4\u6240\u7ecf\u5386\u7684\u6708\u6570\uff1a\u6570\u5b57 MONTHS_BETWEEN(\u65e5\u671f 1 | \u5217 1\uff0c\u65e5\u671f 2 | \u5217 2)<\/p> 2\u3001 \u52a0\u4e0a\u6307\u5b9a\u6708\u4e4b\u540e\u7684\u65e5\u671f\uff1a\u65e5\u671f ADD_MONTHS(\u65e5\u671f | \u5217 \uff0c\u6708\u6570)<\/p> \u601d\u8003\u9898\uff1a\u8981\u6c42\u8ba1\u7b97\u51fa\u6bcf\u4e00\u4f4d\u96c7\u5458\u5230\u4eca\u5929\u4e3a\u6b62\u96c7\u4f63\u7684\u5e74\u9650 SELECT ename,hiredate, 3\u3001 \u6c42\u51fa\u6307\u5b9a\u7684\u4e0b\u4e00\u4e2a\u4e00\u5468\u65f6\u95f4\u6570\u7684\u65e5\u671f\uff1a\u65e5\u671f NEXT_DAY(\u65e5\u671f | \u5217\uff0c\u4e00\u5468\u65f6\u95f4\u6570)<\/p> 4\u3001 \u6c42\u51fa\u6307\u5b9a\u65e5\u671f\u6240\u5728\u6708\u7684\u6700\u540e\u4e00\u5929\u65e5\u671f\uff1a\u65e5\u671f LAST_DAY(\u65e5\u671f | \u5217)<\/p> \u8f6c\u6362\u51fd\u6570<\/strong><\/p> 1\u3001 \u8f6c\u5b57\u7b26\u4e32\u6570\u636e\uff1a\u5b57\u7b26\u4e32 TO_CHAR(\u6570\u5b57 | \u65e5\u671f | \u5217\uff0c\u8f6c\u6362\u683c\u5f0f) SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss') FROM dual ; 2\u3001 \u8f6c\u65e5\u671f\u6570\u636e\uff1a\u65e5\u671f TO_DATE(\u5b57\u7b26\u4e32\uff0c\u8f6c\u6362\u683c\u5f0f)<\/p> \u8303\u4f8b\uff1a\u5c06\u5b57\u7b26\u4e32\u53d8\u4e3a\u65e5\u671f 3\u3001 \u8f6c\u6570\u5b57\u7c7b\u578b\uff1a\u6570\u5b57 TO_NUMBER(\u5b57\u7b26\u4e32\uff0c\u8f6c\u6362\u683c\u5f0f)<\/p> \u8303\u4f8b\uff1a\u5c06\u5b57\u7b26\u4e32\u53d8\u4e3a\u6570\u5b57 \u901a\u7528\u51fd\u6570<\/strong><\/p> 1\u3001 \u5904\u7406 null \u6570\u636e\uff1a\u6570\u5b57 NVL(\u5217 | \u6570\u636e\uff0c\u9ed8\u8ba4\u503c)<\/p> 2\u3001 \u591a\u6570\u636e\u5224\u65ad\uff1a\u6570\u636e DECODE(\u5b57\u6bb5 | \u6570\u636e\uff0c\u5224\u65ad\u6570\u636e 1\uff0c\u663e\u793a\u6570\u636e 1\uff0c\u5224\u65ad\u6570\u636e 2\uff0c\u663e\u793a\u6570\u636e 2\uff0c\u2026 [\u9ed8\u8ba4\u663e\u793a])<\/p> SELECT ename,job , \u548c\u5355\u884c\u51fd\u6570\u76f8\u6bd4\uff0coracle\u63d0\u4f9b\u4e86\u4e30\u5bcc\u7684\u57fa\u4e8e\u7ec4\u7684\uff0c\u591a\u884c\u7684\u51fd\u6570\u3002\u8fd9\u4e9b\u51fd\u6570\u80fd\u5728select\u6216select\u7684having\u5b50\u53e5\u4e2d\u4f7f\u7528\uff0c\u5f53\u7528\u4e8eselect\u5b50\u4e32\u65f6\u5e38\u5e38\u90fd\u548cGROUP BY\u4e00\u8d77\u4f7f\u7528\u3002\u591a\u884c\u51fd\u6570\u5206\u4e3a\u63a5\u6536\u591a\u4e2a\u8f93\u5165\uff0c\u8fd4\u56de\u4e00\u4e2a\u8f93\u51fa\u3002<\/p> \u7ec4\u51fd\u6570\uff1a<\/p> --\u6c42\u5458\u5de5\u7684\u5de5\u8d44\u603b\u548c
· \u8f6c\u5927\u5199\uff1a\u5b57\u7b26\u4e32 UPPER(\u5b57\u7b26\u4e32 | \u6570\u636e\u5217)\uff1b
· \u8f6c\u5c0f\u5199\uff1a\u5b57\u7b26\u4e32 LOWER(\u5b57\u7b26\u4e32 | \u6570\u636e\u5217)\u3002<\/p>
· \u7531\u6307\u5b9a\u4f4d\u7f6e\u622a\u53d6\u5230\u7ed3\u5c3e\uff1a\u5b57\u7b26\u4e32 SUBSTR(\u5b57\u7b26\u4e32 | \u6570\u636e\u5217\uff0c\u622a\u53d6\u5f00\u59cb\u7d22\u5f15)\uff1b
· \u6307\u5b9a\u622a\u53d6\u7684\u5f00\u59cb\u548c\u7ed3\u675f\u4f4d\u7f6e\uff1a\u5b57\u7b26\u4e32 SUBSTR(\u5b57\u7b26\u4e32 | \u6570\u636e\u5217\uff0c\u622a\u53d6\u5f00\u59cb\u7d22\u5f15\uff0c\u622a\u53d6\u7ed3\u675f\u7d22\u5f15)\uff1b<\/p>
· \u65e5\u671f \u2013 \u6570\u5b57 = \u65e5\u671f\uff0c\u8868\u793a\u82e5\u5e72\u5929\u4e4b\u524d\u7684\u65e5\u671f\uff1b
· \u65e5\u671f \u2013 \u65e5\u671f = \u6570\u5b57\uff0c\u8868\u793a\u4e24\u4e2a\u65e5\u671f\u4e4b\u95f4\u7684\u95f4\u9694\u5929\u6570\u3002<\/p>
A\u3001 \u4f8b\u5982\uff1a\u4eca\u5929\u662f 2014 \u5e74 08 \u6708 12 \u65e5\uff0cCLARK \u7684\u96c7\u4f63\u65e5\u671f\u662f\uff1a1981 \u5e74 06 \u6708 09 \u65e5
B\u3001 \u5230\u4eca\u5929 CLARK \u5df2\u7ecf\u5728\u516c\u53f8\u670d\u52a1\u4e86\uff1a33 \u5e74\u300102 \u6708\u300103 \u5929<\/p>
TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)\/12) year ,
TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,hiredate),12)) months ,
TRUNC(SYSDATE - ADD_MONTHS(hiredate,MONTHS_BETWEEN(SYSDATE,hiredate))) day
FROM emp ;<\/p>
\u5728\u8fdb\u884c\u8f6c\u6362\u683c\u5f0f\u8bbe\u7f6e\u7684\u65f6\u5019\u8981\u6839\u636e\u4e0d\u540c\u7684\u6570\u636e\u7c7b\u578b\u8fdb\u884c\u683c\u5f0f\u6807\u8bb0\u7684\u5b9a\u4e49\uff1a
· \u65e5\u671f\uff1a\u5e74\uff08yyyy\uff09\u3001\u6708\uff08mm\uff09\u3001\u65e5\uff08dd\uff09\u3001\u65f6\uff08HH\u3001HH24\uff09\u3001\u5206\uff08mi\uff09\u3001\u79d2\uff08ss\uff09\uff1b
· \u6570\u5b57\uff1a\u4e00\u4f4d\u4efb\u610f\u6570\u5b57\uff089\uff09\u3001\u672c\u5730\u8d27\u5e01\uff08L\uff09\u3002<\/p>
\u8303\u4f8b\uff1a\u62c6\u5206\u65e5\u671f\u6570\u636e
SELECT TO_CHAR(SYSDATE,'yyyy') year ,
TO_CHAR(SYSDATE,'mm') months ,
TO_CHAR(SYSDATE,'dd') day
FROM dual ;
\u8303\u4f8b\uff1a\u683c\u5f0f\u5316\u6570\u5b57\u663e\u793aSELECT TOCHAR(234789234798,'L999,999,999,999,999') FROM dual<\/p>
SELECT '1981-09-15',TO_DATE('1981-09-15','yyyy-mm-dd') FROM dual ;
\u4f46\u662f\u5728 Oracle \u4e4b\u4e2d\u63d0\u4f9b\u6709\u81ea\u52a8\u7684\u8f6c\u6362\u65b9\u5f0f\uff0c\u5982\u679c\u5b57\u7b26\u4e32\u6309\u7167\u65e5\u671f\u7684\u683c\u5f0f\u7f16\u5199\uff0c\u90a3\u4e48\u53ef\u4ee5\u81ea\u52a8\u7531\u5b57\u7b26\u4e32\u53d8\u4e3a\u65e5\u671f\u3002<\/p>
SELECT TO_NUMBER('1') + TO_NUMBER('2') FROM dual ;
SELECT '1' + '2' FROM dual ;
\u5728 Oracle \u91cc\u9762\u8fd9\u4e9b\u81ea\u52a8\u7684\u6570\u636e\u7c7b\u578b\u8f6c\u6362\u529f\u80fd\u662f\u975e\u5e38\u65b9\u4fbf\u7684\uff0c\u5305\u62ec\u65e5\u671f\u548c\u5b57\u7b26\u4e32\u3001\u5b57\u7b26\u4e32\u548c\u6570\u5b57\u3002<\/p>
DECODE(job,'CLERK','\u529e\u4e8b\u5458','SALESMAN','\u9500\u552e','MANAGER','\u7ecf\u7406','ANALYST','\u5206\u6790\u5458','PRESIDENT','\u603b\u88c1','\u672a
\u5904\u7406')<\/p>
select sum(sal) from emp;
--\u6c42\u4e2a\u6570
select count(*) from emp;
--\u6c42\u5e73\u5747\u5de5\u8d44
select sum(sal)\/count(*) \u65b9\u5f0f\u4e00, avg(sal) \u65b9\u5f0f\u4e8c from emp;
--\u5173\u4e8e\u7a7a\u503c\uff1a\u7ec4\u51fd\u6570\u4f1a\u81ea\u52a8\u6ee4\u7a7a
select count(*), count(comm) from emp;
--max\u548cmin\uff1a\u6c42\u6700\u9ad8\u5de5\u8d44\u548c\u6700\u4f4e\u5de5\u8d44
select max(sal) \u6700\u9ad8\u5de5\u8d44,min(sal) \u6700\u4f4e\u5de5\u8d44 from emp;
--\u5206\u7ec4\u6570\u636e\uff1a\u6c42\u5404\u4e2a\u90e8\u95e8\u7684\u5e73\u5747\u5de5\u8d44
select deptno,avg(sal) from emp group by deptno;
--group by\u4f5c\u7528\u4e8e\u591a\u5217\uff1a \u6309\u90e8\u95e8\uff0c\u4e0d\u540c\u7684\u5de5\u79cd\uff0c\u7edf\u8ba1\u5e73\u5747\u5de5\u8d44
--group by\u4f5c\u7528\u4e8e\u591a\u5217\uff1a\u5148\u6309\u7167\u7b2c\u4e00\u5217\u5206\u7ec4\uff1b\u5982\u679c\u76f8\u540c\uff0c\u518d\u6309\u7167\u7b2c\u4e8c\u5217\u5206\u7ec4
select deptno,job,avg(sal) from emp group by deptno,job;
--\uff1a\u6c42\u90e8\u95e8\u7684\u5e73\u5747\u5de5\u8d44\u5927\u4e8e2000\u7684\u90e8\u95e8
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
--group by\u7684\u589e\u5f3a
select deptno,job,sum(sal) from emp group by rollup(deptno,job);
--\u4e0d\u540c\u7684deptno\u7a7a\u4e24\u884c\/\u53d6\u6d88\u8bbe\u7f6e
break on deptno skip 2\/break on null<\/p>
","orderid":"0","title":"\u5173\u4e8eOracle\u5355\u884c\u51fd\u6570\u4e0e\u591a\u884c\u51fd\u6570","smalltitle":"","mid":"0","fname":"\u6570\u636e\u5e93\u7f16\u7a0b","special_id":"0","bak_id":"0","info":"0","hits":"106","pages":"1","comments":"0","posttime":"2019-04-01 00:09:02","list":"1554048542","username":"admin","author":"","copyfrom":"","copyfromurl":"","titlecolor":"","fonttype":"0","titleicon":"0","picurl":"https:\/\/www.cppentry.com\/upload_files\/","ispic":"0","yz":"1","yzer":"","yztime":"0","levels":"0","levelstime":"0","keywords":"\u5173\u4e8e<\/A> Oracle<\/A> \u5355\u884c<\/A> \u51fd\u6570<\/A>","jumpurl":"","iframeurl":"","style":"","template":"a:3:{s:4:\"head\";s:0:\"\";s:4:\"foot\";s:0:\"\";s:8:\"bencandy\";s:0:\"\";}","target":"0","ip":"47.106.78.186","lastfid":"0","money":"0","buyuser":"","passwd":"","allowdown":"","allowview":"","editer":"","edittime":"0","begintime":"0","endtime":"0","description":"\u5173\u4e8eOracle\u5355\u884c\u51fd\u6570\u4e0e\u591a\u884c\u51fd\u6570","lastview":"1702555907","digg_num":"0","digg_time":"0","forbidcomment":"0","ifvote":"0","heart":"","htmlname":"","city_id":"0"},"page":"1"}