Oracle基础知识2 : 单表查询2(内置函数)

-- 1.字符函数 :
SELECT 'The job title for ' || INITCAP(ename) || ' is ' || LOWER(job)
FROM emp;
--首字母大写函数(如果有多个单词,单词之间有空格的话,每个单词都首字母大写) : INITCAP()
--大写显示函数 : UPPER() ; 小写显示函数 : LOWER()
SELECT ename,CONCAT(ename,job),LENGTH(ename),INSTR(ename,'A')
FROM emp
WHERE SUBSTR(job,1,4) = 'SALE';
--使字符串首尾相接显示函数(只能放两个参数) : CONCAT()
--求字符串长度函数 : LENGTH()
--截取长度为 num 得到的新字符串 (num如果不写就截取到最后)
SELECT INSTR('ASDFGDF','D',2,2) FROM DUAL;
--INSTR(str1,str2) : 在 str1 中查找 str , 返回所在位置下标(从1开始)(没找到的话返回0)
--INSTR(str1,str2,n,m) : n表示第几个字符开始,m表示第几次出现
SELECT CONCAT(CONCAT(ename,','),sal)
FROM emp;
--CONCAT()可以套着用~
SELECT ename 
FROM emp
WHERE SUBSTR(ename,-1,1) = 'N';
--SUBSTR(str,start,num) : 返回从 start 开始截取字符串 str ,
--检索姓名最后一个字母是N的雇员姓名(第二个参数如果是负数的话就是从后往前数)
SELECT TRIM('A' FROM 'AANNDDAD') FROM DUAL;
--TRIM(char FROM str) : 从str的左右两边去除char(如果是连续的char那就都删除)
SELECT LTRIM('KLASXLLK','KL') FROM DUAL;
--LTRIM(str1，str2) : 从str1左边开始计数,只要是有str2中的字符就删除,一直到非str2中的字符出现为止
SELECT RTRIM('KLLAALLK','KL') FROM DUAL;
--LTRIM(str1，str2) : 从str1右边开始计数,同LTRIM()理
SELECT LPAD(job,3,'$')
FROM emp;
--LAD(str1,num,str2) : str1不足num位的话,从左用str2补位,如果超出num位的话删除
SELECT RPAD(job,3,'$')
FROM emp;
--RPAD()同LPAD理(从右补位)
SELECT LPAD(ename,10,' ') FROM emp;
--LPAD()/RPAD()一般作用就是右对齐/左对齐 (上面那句就是右对齐)




--2.数字函数 :
SELECT ROUND(45.923,2),ROUND(45.923,0),ROUND(45.923,-1)
FROM DUAL;
--四舍五入函数 : ROUND() 。第二个参数代表保留几位小数(如果为负代表从个位开始舍掉几位精度)
SELECT TRUNC(45.923,2),TRUNC(45.923,0),TRUNC(45.923,-1)
FROM DUAL;
--向下取整函数(总之就是数会被截取 , 变小) : TRUNC() 。同ROUND()理
SELECT ename,sal,comm,MOD(sal,comm)
FROM emp;
--取余函数 : MOD(被除数 , 除数)
SELECT CEIL(290.22) FROM DUAL;
SELECT FLOOR(290.22) FROM DUAL;
--CEIL(num) : 向上取整
--FLOOR(num) : 向下取整




----3.时间函数
--DATE 7个 世纪 年(YYYY/YY) 月(MM) 日(DD) 时(HH24/HH12) 分(MI) 秒(SS)
--TIMESTAMP 精确到纳秒(ns) 前7个字符跟DATE是一毛一样的
SELECT SYSTIMESTAMP FROM DUAL;
--查询系统时间(精确到ns)
SELECT TO_TIMESTAMP('2017-11-16 14:32:12','YYYY-MM-DD HH24:MI:SS') FROM DUAL;
SELECT TO_TIMESTAMP('2017年11月16日 14:32:12','YYYY"年"MM"月"DD"日" HH24:MI:SS') FROM DUAL;
--出现了非英文字母/常用符号时(比如中文),要用双引号引起来,不然系统不认识
SELECT SYSDATE + 100 FROM DUAL;
--日期可以加数字(数字的单位是天),也可以和日期相加减
SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') FROM DUAL;
--将日期按照自己的格式输出
SELECT TO_CHAR(TO_DATE('20-09-09','RR-MM-DD'),'YYYY/MM/DD') FROM DUAL;
--RR的自动转化~(关注世纪)
SELECT LAST_DAY(SYSDATE) FROM DUAL;
--LAST_DAY(date) 返回DATE的月份的最后一天
SELECT ADD_MONTHS(SYSDATE,3) FROM DUAL;
--ADD_MONTHS(date,num) 给date加num个月
SELECT MONTHS_BETWEEN(SYSDATE,hiredate) FROM emp;
--MONTHS_BETWEEN(date1,date2) date1与date2相差多少月份
SELECT NEXT_DAY(SYSDATE,3) FROM DUAL;
--NEXT_DAY(date,n) 下一个n是几号。n代表周几(1代表周日,2代表周一)
SELECT GREATEST(12,11,0,22) FROM DUAL;
--LEAST/GREATEST() 求最小最大值(参数可以有任意多个,参数可以是数字也可以是日期)
SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;
--EXTRACT(元素 FROM date) 从date中提取元素(可以是year,month,day),返回元素值


--4.关于NULL
--Tips : 1.赋值语句中可以用= null赋空值
--       2.但判断语句中用is null表示‘是’空值
SELECT 'JACK'||NULL FROM DUAL;
SELECT 15+NULL FROM DUAL;
--NULL和任何字符串连接 相当于什么也没做
--NULL和任何数字操作 都得NULL
--NVL(a1,a2) 当a1不为空的时候返回a1,当a1为空的时候返回a2

--NVL2(a1,a2,a3) 当a1不为空的时候返回a2,当a1为空的时候返回a3

emp表↑

dept表↑