单行
SQL> --字符函数 SQL> select lower('Hello World') 转小写,upper('Hello World') 转大写,initcap('hello world') 首字母大写 2 from dual; 转小写 转大写 首字母大写 ----------- ----------- ----------- hello world HELLO WORLD Hello World SQL> --substr(a,b) 从a中，第b位开始取 SQL> select substr('Hello World',3) 子串 from dual; 子串 --------- llo World SQL> --substr(a,b,c) 从a中，第b位开始取,取c位 SQL> select substr('Hello World',3,4) 子串 from dual; 子串 ---- llo SQL> --instr(a,b) SQL> select instr('Hello World','ll') 位置 from dual; 位置 ---------- 3 SQL> --length 字符数 lengthb 字节数 SQL> select length('Hello World') 字符,lengthb('Hello World') 字节 from dual; 字符 字节 ---------- ---------- 11 11 SQL> ed 已写入 file afiedt.buf 1* select length('北京') 字符,lengthb('北京') 字节 from dual SQL> / 字符 字节 ---------- ---------- 2 4 SQL> --lpad 左填充 rpad 右填充 SQL> -- abcd ---> 10 位 SQL> select lpad('abcd',10,'*') 左,rpad('abcd',10,'*') 右 from dual; 左 右 ---------- ---------- ******abcd abcd****** SQL> --trim 去掉前后指定的字符 SQL> select trim('H' from 'Hello WorldH') from dual; TRIM('H'FR ---------- ello World SQL> --replace SQL> select replace('Hello World','l','*') from dual; REPLACE('HE ----------- He**o Wor*d SQL> host cls SQL> --四舍五入 SQL> select round(45.926,2) 一,round(45.926,1) 二,round(45.926,0) 三, 2 round(45.926,-1) 四,round(45.926,-2) 五 3 from dual; 一 二 三 四 五 ---------- ---------- ---------- ---------- ---------- 45.93 45.9 46 50 0 SQL> --截断 SQL> ed 已写入 file afiedt.buf 1 select trunc(45.926,2) 一,trunc(45.926,1) 二,trunc(45.926,0) 三, 2 trunc(45.926,-1) 四,trunc(45.926,-2) 五 3* from dual SQL> / 一 二 三 四 五 ---------- ---------- ---------- ---------- ---------- 45.92 45.9 45 40 0 SQL> --查询当前时间 SQL> select sysdate from dual; SYSDATE -------------- 22-11月-15 SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; TO_CHAR(SYSDATE,'YY ------------------- 2015-11-22 15:05:22 SQL> --昨天 今天 明天 SQL> select (sysdate-1) 昨天,sysdate 今天,(sysdate+1) 明天 from dual; 昨天 今天 明天 -------------- -------------- -------------- 21-11月-15 22-11月-15 23-11月-15 SQL> --计算员工的工龄：天 星期 月 年 SQL> select ename,hiredate, (sysdate-hiredate) 天,(sysdate-hiredate)/7 星期, 2 (sysdate-hiredate)/30 月,(sysdate-hiredate)/365 年 3 from emp; ENAME HIREDATE 天 星期 月 年 ---------- -------------- ---------- ---------- ---------- ---------- SMITH 17-12月-80 12758.6314 1822.66163 425.287713 34.9551545 ALLEN 20-2月 -81 12693.6314 1813.37591 423.121046 34.7770723 WARD 22-2月 -81 12691.6314 1813.0902 423.05438 34.7715928 JONES 02-4月 -81 12652.6314 1807.51877 421.75438 34.6647435 MARTIN 28-9月 -81 12473.6314 1781.94734 415.787713 34.1743326 BLAKE 01-5月 -81 12623.6314 1803.37591 420.787713 34.5852915 CLARK 09-6月 -81 12584.6314 1797.80448 419.487713 34.4784422 SCOTT 19-4月 -87 10444.6314 1492.0902 348.15438 28.6154285 KING 17-11月-81 12423.6314 1774.80448 414.121046 34.0373463 TURNER 08-9月 -81 12493.6314 1784.80448 416.45438 34.2291271 ADAMS 23-5月 -87 10410.6314 1487.23306 347.021046 28.5222778 ENAME HIREDATE 天 星期 月 年 ---------- -------------- ---------- ---------- ---------- ---------- JAMES 03-12月-81 12407.6314 1772.51877 413.587713 33.9935107 FORD 03-12月-81 12407.6314 1772.51877 413.587713 33.9935107 MILLER 23-1月 -82 12356.6314 1765.23306 411.887713 33.8537846 已选择 14 行。 SQL> select sysdate + hiredate from emp; select sysdate + hiredate from emp * 第 1 行出现错误: ORA-00975: 不允许日期 + 日期 SQL> --months_between SQL> select ename,hiredate,(sysdate-hiredate)/30 一,months_between(sysdate,hiredate) 二 2 from emp; ENAME HIREDATE 一 二 ---------- -------------- ---------- ---------- SMITH 17-12月-80 425.287784 419.181726 ALLEN 20-2月 -81 423.121117 417.084952 WARD 22-2月 -81 423.054451 417 JONES 02-4月 -81 421.754451 415.665597 MARTIN 28-9月 -81 415.787784 409.826888 BLAKE 01-5月 -81 420.787784 414.697855 CLARK 09-6月 -81 419.487784 413.439791 SCOTT 19-4月 -87 348.154451 343.11721 KING 17-11月-81 414.121117 408.181726 TURNER 08-9月 -81 416.454451 410.472049 ADAMS 23-5月 -87 347.021117 341.988178 ENAME HIREDATE 一 二 ---------- -------------- ---------- ---------- JAMES 03-12月-81 413.587784 407.633339 FORD 03-12月-81 413.587784 407.633339 MILLER 23-1月 -82 411.887784 405.988178 已选择 14 行。 SQL> --56 个月后 SQL> select add_months(sysdate,56) from dual; ADD_MONTHS(SYS -------------- 22-7月 -20 SQL> select last_day(sysdate) from dual; LAST_DAY(SYSDA -------------- 30-11月-15 SQL> --next_day SQL> --下一个星期日 SQL> select next_day(sysdate,'星期日') from dual; NEXT_DAY(SYSDA -------------- 29-11月-15 SQL> --下一个星期一 SQL> select next_day(sysdate,'星期一') from dual; NEXT_DAY(SYSDA -------------- 23-11月-15 SQL> /* SQL> next_day的应用：每个星期一自动备份数据 SQL> 1. 分布式数据库 SQL> 2. 快照 触发器 SQL> */ SQL> select round(sysdate,'month'),round(sysdate,'year') from dual; ROUND(SYSDATE, ROUND(SYSDATE, -------------- -------------- 01-12月-15 01-1月 -16 SQL> --2015-11-22 15:32:12今天是星期日 SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss今天是day') from dual; select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss今天是day') from dual * 第 1 行出现错误: ORA-01821: 日期格式无法识别 SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss"今天是"day') from dual; TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI ---------------------------------- 2015-11-22 15:33:55今天是星期日 SQL> --查询员工薪水：两位小数 千位符 货币代码 SQL> select to_char(sal,'L9,999.99') from emp; TO_CHAR(SAL,'L9,999 ------------------- ￥800.00 ￥1,600.00 ￥1,250.00 ￥2,975.00 ￥1,250.00 ￥2,850.00 ￥2,450.00 ￥3,000.00 ￥5,000.00 ￥1,500.00 ￥1,100.00 TO_CHAR(SAL,'L9,999 ------------------- ￥950.00 ￥3,000.00 ￥1,300.00 已选择 14 行。 SQL> host cls SQL> --nvl2(a,b,c) 当a=null的时候，返回c；否则返回b SQL> select sal*12+nvl2(comm,comm,0) from emp; SAL*12+NVL2(COMM,COMM,0) ------------------------ 9600 19500 15500 35700 16400 34200 29400 36000 60000 18000 13200 SAL*12+NVL2(COMM,COMM,0) ------------------------ 11400 36000 15600 已选择 14 行。 SQL> --nullif(a,b) 当a=b的时候，返回null；否则返回a SQL> select nullif('abc','abc') 值 from dual; 值 --- SQL> select nullif('abc','abcd') 值 from dual; 值 --- abc SQL> --coalesce 从左到右找到第一个不为null的值 SQL> select comm,sal,coalesce(comm,sal) "第一个不为null的值" 2 from emp; COMM SAL 第一个不为null的值 ---------- ---------- ------------------ 800 800 300 1600 300 500 1250 500 2975 2975 1400 1250 1400 2850 2850 2450 2450 3000 3000 5000 5000 0 1500 0 1100 1100 COMM SAL 第一个不为null的值 ---------- ---------- ------------------ 950 950 3000 3000 1300 1300 已选择 14 行。 SQL> host cls SQL> --涨工资，总裁1000 经理 800 其他400 SQL> set linesize 200 SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-12月-80 800 20 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 7566 JONES MANAGER 7839 02-4月 -81 2975 20 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 7839 KING PRESIDENT 17-11月-81 5000 10 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7900 JAMES CLERK 7698 03-12月-81 950 30 7902 FORD ANALYST 7566 03-12月-81 3000 20 7934 MILLER CLERK 7782 23-1月 -82 1300 10 已选择 14 行。 SQL> select ename,job,sal 涨前, 2 case job when 'PRESIDENT' then sal+1000 3 when 'MANAGER' then sal+800 4 else sal+400 5 end 涨后 6 from emp; ENAME JOB 涨前 涨后 ---------- --------- ---------- ---------- SMITH CLERK 800 1200 ALLEN SALESMAN 1600 2000 WARD SALESMAN 1250 1650 JONES MANAGER 2975 3775 MARTIN SALESMAN 1250 1650 BLAKE MANAGER 2850 3650 CLARK MANAGER 2450 3250 SCOTT ANALYST 3000 3400 KING PRESIDENT 5000 6000 TURNER SALESMAN 1500 1900 ADAMS CLERK 1100 1500 ENAME JOB 涨前 涨后 ---------- --------- ---------- ---------- JAMES CLERK 950 1350 FORD ANALYST 3000 3400 MILLER CLERK 1300 1700 已选择 14 行。 SQL> select ename,job,sal 涨前, 2 decode(job,'PRESIDENT',sal+1000, 3 'MANAGER',sal+800, 4 sal+400) 涨后 5 from emp; ENAME JOB 涨前 涨后 ---------- --------- ---------- ---------- SMITH CLERK 800 1200 ALLEN SALESMAN 1600 2000 WARD SALESMAN 1250 1650 JONES MANAGER 2975 3775 MARTIN SALESMAN 1250 1650 BLAKE MANAGER 2850 3650 CLARK MANAGER 2450 3250 SCOTT ANALYST 3000 3400 KING PRESIDENT 5000 6000 TURNER SALESMAN 1500 1900 ADAMS CLERK 1100 1500 ENAME JOB 涨前 涨后 ---------- --------- ---------- ---------- JAMES CLERK 950 1350 FORD ANALYST 3000 3400 MILLER CLERK 1300 1700 已选择 14 行。 SQL> spool off
多行
SQL> host cls SQL> --工资总额 SQL> select sum(sal) from emp; SUM(SAL) ---------- 29025 SQL> --人数 SQL> select count(*) from emp; COUNT(*) ---------- 14 SQL> --平均工资 SQL> select sum(sal)/count(*) 一,avg(sal) 二 from emp; 一 二 ---------- ---------- 2073.21429 2073.21429 SQL> --平均奖金 SQL> select sum(comm)/count(*) 一,sum(comm)/count(comm) 二,avg(comm) 三 2 from emp; 一 二 三 ---------- ---------- ---------- 157.142857 550 550 SQL> select count(*),count(comm) from emp; COUNT(*) COUNT(COMM) ---------- ----------- 14 4 SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-12月-80 800 20 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 7566 JONES MANAGER 7839 02-4月 -81 2975 20 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 7839 KING PRESIDENT 17-11月-81 5000 10 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7900 JAMES CLERK 7698 03-12月-81 950 30 7902 FORD ANALYST 7566 03-12月-81 3000 20 7934 MILLER CLERK 7782 23-1月 -82 1300 10 已选择 14 行。 SQL> --null值 5.组函数（多行函数）自动滤空 SQL> select count(*),count(nvl(comm,0)) from emp; COUNT(*) COUNT(NVL(COMM,0)) ---------- ------------------ 14 14 SQL> --null值 5.组函数（多行函数）自动滤空;可以嵌套滤空函数 来屏蔽他的滤空功能 SQL> host cls SQL> --求每个部门的平均工资 SQL> select deptno,avg(sal) 2 from emp 3 group by deptno; DEPTNO AVG(SAL) ---------- ---------- 30 1566.66667 20 2175 10 2916.66667 SQL> --多个列的分组 SQL> select deptno,job,sum(sal) 2 from emp 3 group by deptno,job 4 order by 1; DEPTNO JOB SUM(SAL) ---------- --------- ---------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 20 ANALYST 6000 20 CLERK 1900 20 MANAGER 2975 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 5600 已选择 9 行。 SQL> --求平均工资大于2000的部门 SQL> select deptno,avg(sal) 2 from emp 3 group by deptno 4 having avg(sal) > 2000; DEPTNO AVG(SAL) ---------- ---------- 20 2175 10 2916.66667 SQL> --where和having的区别：where后面不能使用多行函数 SQL> --查询10号部门的平均工资 SQL> select deptno,avg(sal) 2 from emp 3 group by deptno 4 having deptno=10; DEPTNO AVG(SAL) ---------- ---------- 10 2916.66667 SQL> ed 已写入 file afiedt.buf 1 select deptno,avg(sal) 2 from emp 3 where deptno=10 4* group by deptno SQL> / DEPTNO AVG(SAL) ---------- ---------- 10 2916.66667 SQL> --SQL 优化原则3. 尽量使用where SQL> host cls SQL> /* SQL> group by的增强 SQL> select deptno,job,sum(sal) from emp group by deptno,job SQL> + SQL> select deptno,sum(sal) from emp group by deptno SQL> + SQL> select sum(sal) from emp SQL> SQL> === SQL> SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job); SQL> SQL> 抽象 SQL> group by rollup(a,b) SQL> == SQL> group by a,b SQL> + SQL> group by a SQL> + SQL> group by null SQL> SQL> */ SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job); DEPTNO JOB SUM(SAL) ---------- --------- ---------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 10 8750 20 CLERK 1900 20 ANALYST 6000 20 MANAGER 2975 20 10875 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 5600 DEPTNO JOB SUM(SAL) ---------- --------- ---------- 30 9400 29025 已选择 13 行。 SQL> break on deptno skip 2 SQL> / DEPTNO JOB SUM(SAL) ---------- --------- ---------- 10 CLERK 1300 MANAGER 2450 PRESIDENT 5000 8750 20 CLERK 1900 ANALYST 6000 MANAGER 2975 10875 DEPTNO JOB SUM(SAL) ---------- --------- ---------- 30 CLERK 950 MANAGER 2850 SALESMAN 5600 9400 29025 已选择 13 行。 SQL> break on null SQL> / DEPTNO JOB SUM(SAL) ---------- --------- ---------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 10 8750 20 CLERK 1900 20 ANALYST 6000 20 MANAGER 2975 20 10875 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 5600 DEPTNO JOB SUM(SAL) ---------- --------- ---------- 30 9400 29025 已选择 13 行。 SQL> spool off