查询
select *from emp where ename=UPPER('&inputname');
输入 inputname 的值: smith
原值 1: select *from emp where ename=UPPER('&inputname')
新值 1: select *from emp where ename=UPPER('smith')
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
字符串函数
字符串 UPPER（列|字符串） 将所有字符串变为大写
字符串 LOWER（列|字符串） 将所有字符串变为大写
字符串 INITCAP（列|字符串） 开头字母大写，其余全部小写
数字 LENGTH（列|字符串） 取得字符串长度
字符串 SUBSTR（列|字符串，开始索引，[长度]） 截取字符串，若没有设置长度，则从开始截取到结尾
字符串 REPLACE（列|字符串，旧内容，新内容） 新内容替换旧内容
UPPER
select upper('hello') from dual; //专供查询的dual为虚表
UPPER('HEL
----------
HELLO
LOWER
select lower (ename) from emp;
LOWER(ENAME)
--------------------
smith
allen
ward
jones
martin
blake
clark
scott
king
turner
adams
james
ford
miller
已选择14行。
INITCAP
select ename,initcap(ename) from emp;
ENAME INITCAP(ENAME)
-------------------- --------------------
SMITH Smith
ALLEN Allen
WARD Ward
JONES Jones
MARTIN Martin
BLAKE Blake
CLARK Clark
SCOTT Scott
KING King
TURNER Turner
ADAMS Adams
JAMES James
FORD Ford
MILLER Miller
已选择14行。
LENGTH
select length('djdjjdjdjskskks') from dual;
LENGTH('DJDJJDJDJSKSKKS')
-------------------------
15
-------------查询姓名长度为5的雇员信息-------------
select *from emp where length(ename)=5;
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
7566 JONES MANAGER 7839 02-4月 -81 2975 20
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
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
已选择8行。
SUBSTR（Oracle默认从1开始而不是0）
select substr('helloworld',6) from dual;
SUBSTR('HE
----------
world
---------------------------------------------------------
select substr('helloworld',1,5) from dual;
SUBSTR('HE
----------
hello
---------------------------------------------------------
select ename,substr(ename,1,3) from emp;
ENAME SUBSTR(ENAME,1,3)
-------------------- ------------------------
SMITH SMI
ALLEN ALL
WARD WAR
JONES JON
MARTIN MAR
BLAKE BLA
CLARK CLA
SCOTT SCO
KING KIN
TURNER TUR
ADAMS ADA
JAMES JAM
FORD FOR
MILLER MIL
--------------------从后面截取--------------
select ename,substr(ename,length(ename)-2) from emp;
select ename,substr(ename,-3) from emp;
ENAME SUBSTR(ENAME,LENGTH(ENAME)-2)
-------------------- --------------------------------------------------------------------------------
SMITH ITH
ALLEN LEN
WARD ARD
JONES NES
MARTIN TIN
BLAKE AKE
CLARK ARK
SCOTT OTT
KING ING
TURNER NER
ADAMS AMS
JAMES MES
FORD ORD
MILLER LER
数值函数
数字 ROUND （列 | 数字 [,小数位]） 实现数据的四舍五入，可以设置保留小数位
----------------------------------------------------------------------------------------
数字 TRUNC （列 | 数字 [,小数位]） 实现数据的截取，即不进位
----------------------------------------------------------------------------------------
数字 MOD（列 | 数字 ，列 | 数字） 求模（计算余数）
ROUND(会进位)
select round(789.36544555) from dual;
ROUND(789.36544555)
-------------------
789
---------------------------------------------------------
select round(256.55555555,2) from dual;
ROUND(256.55555555,2)
---------------------
256.56
TRUNC（不会进位）
select trunc(225.658255,2) from dual;
TRUNC(225.658255,2)
-------------------
225.65
---------------------------------------------------------
select trunc(225.658255) from dual;
TRUNC(225.658255)
-----------------
225
MOD
select mod(10,3) from dual;
MOD(10,3)
----------
1
日期函数
伪列： SYSDATE SYSTIMESTAMP
模糊日期操作公式：
日期+数字=日期（若干天之后的日期）
日期-数字=日期（若干天之前的日期）
日期-日期=数字（天数）
select sysdate from dual;
SYSDATE
--------------
04-4月 -17
select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
04-4月 -17 09.34.09.223000 上午 +08:00
日期操作(7天前，120天之后)
select sysdate-7,sysdate+120 from dual;
SYSDATE-7 SYSDATE+120
-------------- --------------
28-3月 -17 02-8月 -17
查询入职日期
select empno,ename,sal,job,sysdate-hiredate from emp;
EMPNO ENAME SAL JOB SYSDATE-HIREDATE
---------- -------------------- ---------- ------------------ ----------------
7369 SMITH 800 CLERK 13257.4062
7499 ALLEN 1600 SALESMAN 13192.4062
7521 WARD 1250 SALESMAN 13190.4062
7566 JONES 2975 MANAGER 13151.4062
7654 MARTIN 1250 SALESMAN 12972.4062
7698 BLAKE 2850 MANAGER 13122.4062
7782 CLARK 2450 MANAGER 13083.4062
7788 SCOTT 3000 ANALYST 10943.4062
7839 KING 5000 PRESIDENT 12922.4062
7844 TURNER 1500 SALESMAN 12992.4062
7876 ADAMS 1100 CLERK 10909.4062
7900 JAMES 950 CLERK 12906.4062
7902 FORD 3000 ANALYST 12906.4062
7934 MILLER 1300 CLERK 12855.4062
准确计算公式
日期 AND_MONTHS(列 | 日期，月数) 在指定日期上增加若干月之后的日期
数字 MONTHS_BETWEEN(列|日期，列|日期) 返回两个日期之间所经历的月数
日期 LAST_DAY(列|日期) 取得所在日期月的最后一天
日期 NEXT_DAY(列|日期，星期X) 返回下一个指定的一周时间数对应的日期
4个月之后
select add_months(sysdate,4) from dual;
ADD_MONTHS(SYS
--------------
04-8月 -17
----------------------------------------------------
select add_months(sysdate,4),add_months(sysdate,20) from dual;
ADD_MONTHS(SYS ADD_MONTHS(SYS
-------------- --------------
04-8月 -17 04-12月-18
MONTHS_BETWEEN
select empno,ename,hiredate,months_between(sysdate,hiredate) from emp;
EMPNO ENAME HIREDATE MONTHS_BETWEEN(SYSDATE,HIREDATE)
---------- -------------------- -------------- --------------------------------
7369 SMITH 17-12月-80 435.59685
7499 ALLEN 20-2月 -81 433.500076
7521 WARD 22-2月 -81 433.43556
7566 JONES 02-4月 -81 432.080721
7654 MARTIN 28-9月 -81 426.242012
7698 BLAKE 01-5月 -81 431.112979
7782 CLARK 09-6月 -81 429.854915
7788 SCOTT 19-4月 -87 359.532334
7839 KING 17-11月-81 424.59685
7844 TURNER 08-9月 -81 426.887173
7876 ADAMS 23-5月 -87 358.403302
7900 JAMES 03-12月-81 424.048463
7902 FORD 03-12月-81 424.048463
7934 MILLER 23-1月 -82 422.403302
LAST_DAY
//查询当前月的最后一天
select last_day(sysdate) from dual;
LAST_DAY(SYSDA
--------------
30-4月 -17
-------------------------------------------------------
//查询所有在雇佣所在月倒数前三天的雇员信息 where 筛选作用
select empno,ename,hiredate,last_day(hiredate)-2 from emp where hiredate=last_day(hiredate)-2;
EMPNO ENAME HIREDATE LAST_DAY(HIRED
---------- -------------------- -------------- --------------
7654 MARTIN 28-9月 -81 28-9月 -81
NEXT_DAY(返回当前日期下周X所对应的日期)
select next_day(sysdate,'星期二') from dual;
NEXT_DAY(SYSDA
--------------
11-4月 -17
以年月日的形式计算出每个雇员到现在的雇佣时间
日期图
select empno,ename,hiredate,
2 trunc(months_between(sysdate,hiredate)/12) years,
3 trunc(mod(months_between(sysdate,hiredate),12)) months,
4 trunc(sysdate-add_months(hiredate,months_between(sysdate,hiredate))) days
5 from emp;
EMPNO ENAME HIREDATE YEARS MONTHS DAYS
---------- -------------------- -------------- ---------- ---------- ----------
7369 SMITH 17-12月-80 36 3 18
7499 ALLEN 20-2月 -81 36 1 15
7521 WARD 22-2月 -81 36 1 13
7566 JONES 02-4月 -81 36 0 2
7654 MARTIN 28-9月 -81 35 6 7
7698 BLAKE 01-5月 -81 35 11 3
7782 CLARK 09-6月 -81 35 9 26
7788 SCOTT 19-4月 -87 29 11 16
7839 KING 17-11月-81 35 4 18
7844 TURNER 08-9月 -81 35 6 27
7876 ADAMS 23-5月 -87 29 10 12
7900 JAMES 03-12月-81 35 4 1
7902 FORD 03-12月-81 35 4 1
7934 MILLER 23-1月 -82 35 2 12
转换函数
字符串 TO_CHAR(列|日期|数字，转换格式) 将日期或数字转换成指定结构的字符串
日期 TO_DATE(列|字符串，转换格式) 按照指定的转换格式编写字符串后将其变为日期型数据
数字 TO_NUMBER(列|字符串) 将字符串变为数字
一、TO_CHAR
转换格式
日期：年（yyyy）、月（mm）、日（dd）；
时间：时（hh、hh24）、分（mi）、秒（ss）；
数字：任意数字（9）、本地货币符号（L）。
转换当前日期
SQL> select to_char(sysdate,'yyyy-mm-dd') from dual;
TO_CHAR(SYSDATE,'YYY
--------------------
2017-04-08
------------------------------------------------
SQL> select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH:MI:SS')
--------------------------------------
2017-04-08 03:36:17
-------------------------------------------------
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS'
--------------------------------------
2017-04-08 15:37:28
日期数据的拆分
SQL> select to_char(sysdate,'yyyy'),to_char(sysdate,'mm'),to_char(sysdate,'dd') from dual;
TO_CHAR( TO_C TO_C
-------- ---- ----
2017 04 08
查询在二月份雇佣的员工
SQL> select *from emp where to_char(hiredate,'mm')='02';
select *from emp where to_char(hiredate,'mm')=2;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
格式化数字
SQL> select to_char(9999999999,'L999,999,999,999,999') from dual;
TO_CHAR(9999999999,'L999,999,999,999,999')
------------------------------------------------------------
￥9,999,999,999
二、TO_DATE
将字符串转为日期
SQL> select to_date('2016-05-15','yyyy-mm-dd') from dual;
TO_DATE('2016-
--------------
15-5月 -16
三、TO_NUMBER
//提供默认转换 select '1'+'2' from dual;与下面一致
SQL> select to_number('1')+to_number('2') from dual;
TO_NUMBER('1')+TO_NUMBER('2')
-----------------------------
3
通用函数
数字 NVL（列|NULL，默认值） 如果传入的内容是NULL，则使用默认数值处理；如果不是NULL则使用原始数据处理；
数据类型 DECODE（列|字符串|数值，比较内容1，显示内容1，比较内容2，显示内容2，...[,默认显示内容]） 设置的内容会与每一个比较内容进行比较，如果内容相同，则会使用显示内容进行输出，如何都不相同，则使用最后的默认信息输出。
一、 NVL 处理NULL函数(任何数与NULL运算都为NULL此时把NULL替换成0，所以要处理NULL值)
SQL> select empno,ename,comm,(sal+nvl(comm,0))*12 from emp;
EMPNO ENAME COMM (SAL+NVL(COMM,0))*12
---------- -------------------- ---------- --------------------
7369 SMITH 9600
7499 ALLEN 300 22800
7521 WARD 500 21000
7566 JONES 35700
7654 MARTIN 1400 31800
7698 BLAKE 34200
7782 CLARK 29400
7788 SCOTT 36000
7839 KING 60000
7844 TURNER 0 18000
7876 ADAMS 13200
7900 JAMES 11400
7902 FORD 36000
7934 MILLER 15600
二、DECODE（比较输出）
将职位信息换成中文(要设置则全部设置，不设置的为NULL，若不设置也可用‘---’表示)
SQL> select ename,job,decode(job,'CLERK','办事员','SALESMAN','销售') from emp;
ENAME JOB DECODE(JOB,'CLERK'
-------------------- ------------------ ------------------
SMITH CLERK 办事员
ALLEN SALESMAN 销售
WARD SALESMAN 销售
JONES MANAGER
MARTIN SALESMAN 销售
BLAKE MANAGER
CLARK MANAGER
SCOTT ANALYST
KING PRESIDENT
TURNER SALESMAN 销售
ADAMS CLERK 办事员
JAMES CLERK 办事员
FORD ANALYST
MILLER CLERK 办事员
-----------------------------------------------------
SQL> select ename,job,decode(job,'CLERK','办事员','SALESMAN','销售','---') from emp;
ENAME JOB DECODE(JOB,'CLERK'
-------------------- ------------------ ------------------
SMITH CLERK 办事员
ALLEN SALESMAN 销售
WARD SALESMAN 销售
JONES MANAGER ---
MARTIN SALESMAN 销售
BLAKE MANAGER ---
CLARK MANAGER ---
SCOTT ANALYST ---
KING PRESIDENT ---
TURNER SALESMAN 销售
ADAMS CLERK 办事员
JAMES CLERK 办事员
FORD ANALYST ---
MILLER CLERK 办事员
