Oracle-DQL 3- 单行函数

单行函数：

--使用函数对表中的数据进行运算和处理，针对每行数据返回一个结果，叫做单行函数
--包括数字函数，字符函数，日期函数，转换函数，其他函数

 

1.数字函数

--round(m,n) 将数字m精确到小数点后n位
SELECT ROUND(123.456,1),ROUND(123.456,2),ROUND(123.456,0) FROM emp;

--oracle 中有一张系统默认的空表 dual ，可以进行辅助的查询或者运算
SELECT * FROM dual;
SELECT ROUND(123.456,1),ROUND(123.456,2),ROUND(123.456,0) FROM dual;

--n 可以不写，默认精确到个位
SELECT ROUND(123.456) FROM dual;

--n 可以为负数，精确到小数点左边n位
SELECT ROUND(126.456,-1),ROUND(173.456,-2),ROUND(823.456,-3) FROM dual;

SELECT ename,job,sal,deptno,ROUND(sal/22,2) rixin FROM emp;

--trunc(m,n) 将数字m截取到小数点后n位，其他与round函数规则一样
SELECT TRUNC(123.456,1),TRUNC(123.456,2),TRUNC(123.456,0) FROM dual;

 

2.字符函数

--uppper(x) 将字符串x转换成大写
--lower(x) 将字符串x转换成小写
SELECT UPPER('abcdefg') FROM dual;
SELECT LOWER(ename),LOWER(job) FROM emp;

SELECT * FROM emp
WHERE LOWER(job) = LOWER('clerk');

--length(x) 返回字符串x的长度
SELECT ename,LENGTH(ename) FROM emp;

SELECT * FROM emp
WHERE LENGTH(ename) = 5;

--substr(x,m,n) 返回从字符串x的第m个字符开始长度为n的子字符串
SELECT SUBSTR('abcdefg',2,3) FROM dual;

--n 可以不写，如果不写默认截取后面所有的字符串
SELECT ename,SUBSTR(ename,3) FROM emp;

--m 可以为负数，从右向左数第m位字符开始截取
SELECT ename,SUBSTR(ename,-1) FROM emp;

SELECT ename,SUBSTR(ename,LENGTH(ename)-1) FROM emp;

 

3.日期函数

--日期对格式敏感，一个日期格式的数据由世纪、年、月、日、小时、分钟、秒组成
--oracle默认的日期格式DD-MON-RR
SELECT * FROM emp;
--sysdate 系统时间
SELECT SYSDATE FROM dual;

--两个日期之间可以做减法运算，表示相差多少天
SELECT ename,hiredate,TRUNC((SYSDATE-hiredate)/365) gongling FROM emp;

--last_day(date) 返回日期date所在月份的最后一天
SELECT LAST_DAY(SYSDATE) FROM dual;
SELECT ename,hiredate,LAST_DAY(hiredate) FROM emp;

--add_months(date,n) 给日期date加上n个月
SELECT ADD_MONTHS(SYSDATE,5) FROM dual;
SELECT ename,hiredate,ADD_MONTHS(hiredate,3) FROM emp;

 

4.转换函数
--oracle中数据类型的转换有2种：明确和隐含的数据类型转换
--隐含的数据类型转换是指系统自动完成的不需要用户特别执行的数据类型转换
--数字和字符之间通常可以进行隐含的数据类型转换
--数字可以直接看作是字符串，具有数字特征的字符串也可以自动转换成数字
SELECT * FROM emp
WHERE deptno = '10';

--明确的数据类型转换就是使用转换函数

--to_char(date,'format') 将日期date转换成格式为format的字符串
--YYYY 表示四位的年份数字，YEAR 表示年份的拼写
--MM 表示两位月份数字，MONTH 表示月份的拼写
--DD 表示两位日期数字，DAY 表示星期的拼写
--分隔符可以自定义（: ; - _ 空格……）

SELECT SYSDATE,TO_CHAR(SYSDATE,'YYYYMMDD') FROM dual;
SELECT hiredate,TO_CHAR(hiredate,'YYYY:MM:DD') FROM emp;

--查找emp表中1981年6月1日之前入职的员工
SELECT * FROM emp
WHERE TO_CHAR(hiredate,'YYYYMMDD') < '19810601';

--年月日的拼写和数字可以混合使用
SELECT hiredate,TO_CHAR(hiredate,'YEAR:MONTH:DAY') FROM emp;

SELECT hiredate,TO_CHAR(hiredate,'YYYY:MONTH:DD') FROM emp;

--年月日的顺序可以自定义,分隔符也可以混合使用
SELECT hiredate,TO_CHAR(hiredate,'MONTH:DD YYYY') FROM emp;

--在format之前加上fm可以去掉无意义的0或者空格
SELECT hiredate,TO_CHAR(hiredate,'fmMONTH:DD YYYY') FROM emp;

--年月日可以不完整，只转换其中的一部分
SELECT hiredate,TO_CHAR(hiredate,'YYYY') FROM emp;
SELECT hiredate,TO_CHAR(hiredate,'MM') FROM emp;

--to_char(n,'format') 将数字n转换成格式为format的字符串
--9 代表一位数字
--0 代表一位数字，如果位数不足时会使用0补齐
-- .  代表小数点，不能出现在开头和结尾
--，代表分隔符，只能出现在整数部分的中间
SELECT sal,TO_CHAR(sal,'99999.99') FROM emp;
SELECT sal,TO_CHAR(sal,'00000.00') FROM emp;

--分隔符惯例是三位一分隔，也可以自定义
SELECT sal,TO_CHAR(sal,'99,999.99') FROM emp;
SELECT sal,TO_CHAR(sal,'9,9,9,9,9.99') FROM emp;

--货币单位$,当地货币L
SELECT sal,TO_CHAR(sal,'$99,999.99') FROM emp;

--to_date(x,'format') 将字符串x转换成日期
SELECT TO_DATE('20170801','YYYYMMDD') FROM dual;

SELECT TO_DATE('20170801','YYYYDDMM') FROM dual;

--年月日可以不完整，系统会自动将日期补齐为完整的格式

--如果不定义年份和月份，会用当前系统事件所在的年份和月份补齐
SELECT TO_DATE('0801','MMDD') FROM dual;
SELECT TO_DATE('201325','YYYYDD') FROM dual;

--如果不定义天数，会用当月第一天补齐
SELECT TO_DATE('201904','YYYYMM') FROM dual;
SELECT TO_DATE('2019','YYYY') FROM dual;

--查找emp表中1981年6月1日之前入职的员工
SELECT * FROM emp
WHERE hiredate < TO_DATE('19810601','YYYYMMDD');

 

5.其他函数

--nvl(x,y) 判断x的值，如果x不为空，则返回x，如果x为空则返回y
SELECT ename,job,sal,comm,12*(sal+NVL(comm,0)) FROM emp;

--x 和 y 的数据类型必须一致
SELECT ename,job,sal,NVL(TO_CHAR(mgr),'BOSS') FROM emp;

--nvl2(x,y,z) 判断x的值，如果x不为空则返回y，如果x为空则返回z
--y 和 z 的数据类型必须一致
SELECT ename,job,sal,mgr,NVL2(mgr,'EMPLOYEE','BOSS') FROM emp;

--decode 针对不同的数据返回不同结果
/*
decode(x,value1,result1,
value2,result2,
...
valueN,resultN,
default)
判断 x 的值，当x = value1 时返回result1
当x = value2 时返回result2
……
当x = valueN 时返回resultN
以上条件都不满足时返回default默认值
*/
--需要对emp表中不同职位的员工涨不同幅度的工资
SELECT ename,job,sal,deptno,
DECODE(job,'CLERK',sal+500,
'SALESMAN',sal+300,
'MANAGER',sal+200,
sal) addsal
FROM emp;

 

【练习】
1.用一个sql语句完成在字符串”hello”左右各添加5个*，
使其最终返回*****hello*****(用2种方法实现)
SELECT RPAD(LPAD('hello',10,'*'),15,'*') FROM dual;
SELECT '*****'||'hello'||'*****' FROM dual;
SELECT CONCAT('*****',CONCAT('hello','*****')) FROM dual;

2.写一条sql语句返回”abcdefg”的后三位字符“efg”,并转换成大写
SELECT UPPER(SUBSTR('abcdefg',-3)) FROM dual;
SELECT UPPER(LTRIM('abcdefg','abcd')) FROM dual;

3.查询emp表，显示删除掉第一个字符后的员工姓名
（如员工ALLEN，显示为LLEN）（2种方法实现）
SELECT ename,SUBSTR(ename,2) FROM emp;
SELECT ename,LTRIM(ename,SUBSTR(ename,1,1)) FROM emp;

4.查询emp表，显示姓名中不包含字符’A’的员工信息
SELECT * FROM emp
WHERE ename NOT LIKE '%A%';

--instr(x,y,m,n) 返回字符串y从字符串x第m位开始出现第n次的位置，如果没有出现则返回0
SELECT INSTR('abcabcabccadbda','a',5,4) FROM dual;
--m和n的默认值都为1
SELECT ename,INSTR(ename,'A') FROM emp;

SELECT * FROM emp
WHERE INSTR(ename,'A') = 0;

5.查询emp表，使用员工姓名的第一个字符将员工姓名从左边补齐到长度为10
SELECT LPAD(ename,10,SUBSTR(ename,1,1)) FROM emp;

6.查询emp表，显示6月份入职的员工信息
SELECT * FROM emp
WHERE TO_CHAR(hiredate,'MM') = 6;

7.查询在某个月倒数第三天入职的员工信息
SELECT * FROM emp
WHERE hiredate = LAST_DAY(hiredate) - 2;

8.写一条sql语句查询员工编号、员工姓名、工资、部门号，
要求当员工在10号部门时，显示’财务部’，20号部门时显示’研发部’，
30号部门时显示’销售部’，其余部门显示’未知部门’
SELECT empno,ename,sal,deptno,
DECODE(deptno,10,'Finance',
20,'Research',
30,'Sales',
'Unknown') deptname
FROM emp;