第二章 Oracle的函数（字符函数 ，数字函数，日期函数 练习）

/ascii/
–只转一个字段数据中的第一个字符

/连接/
select concat(CONCAT(e.empno,e.empname),e.job) ,
concat(e.empno||e.empname,e.job) ,
e.empno||e.empname||e.job
from emp2 e

/INSTR(x, str [,start] [,n]) /
–返回str中第一个字符第一次出现的位置
select INSTR(e.empname,’ES’) ,e.empname
from emp2 e
–如果要带次数的参数，必须带位置参数
select INSTR(‘ABCDAYTAOA’,’A’,2,2) ,e.empname
from emp2 e
/长度：一个汉字是一个长度、一个字符也算是一个长度/
select LENGTH(e.empname) ,e.empname
from emp2 e
/针对字母变换大小写/
select upper(LOWER(e.empname)) ,upper(e.empname) ,e.empname
from emp2 e

/trim的用法/
select LTRIM(e.stuname,’n’) ,
rTRIM(e.stuname,’n’) ,
TRIM(‘n’ from e.stuname) ,
e.stuname
from infos3 e;

SELECT LTRIM(‘== =HELLO===’, ‘=’)
FROM DUAl;

select ltrim(‘ABASS ASA====’,’AB’)
from dual;

/REPLACE/
select replace(e.empname,’DA’,’a’),e.empname
from emp2 e

/子字符串/
select SUBSTR(e.empname,2,2),e.empname
from emp2 e

–特殊用法
SELECT EMPNAME
FROM emp2
WHERE empname = upper(‘&empname’)

===============================================================
–上入整数
select ceil(1.88) from dual;
select ceil(-1.88) from dual;
select ceil(-1) from dual;
–下舍整数
select floor(1.88) from dual;
select floor(-1.88) from dual;
select floor(-1) from dual;

–x为底y的对数
select LOG(2,16) from dual;
–平方
select SQRT(28) from dual;
–求幂运算
select POWER(4,3) from dual;
–取余
select mod(20,3) from dual;

–x在第y位四舍五入
select round(19.8888888888888) from dual;
–x在第y位截断
select trunc(19.8888888888888) from dual;

–x在第y位四舍五入
select round(19.8888888888888,1) from dual;
–x在第y位截断
select trunc(19.8888888888888,1) from dual;
–x在第y位四舍五入
select round(1988888888888.88,-2) from dual;
–x在第y位截断
select trunc(1988888888888.88,-2) from dual;

===============================================================
1、在emp表中查询出姓名的第二个字母为A的记录。
select * from emp2
where empname like ‘_A%’;

select * from emp2
where instr(empname,’A’ ) = 2;

select * from emp2
where SUBSTR(empname,2, 1) = ‘A’;

2、显示员工姓名正好为5个字符的员工。
select * from emp2
where LENGTH(empname) = 5;
3、显示所有员工姓名的前三个字符。
select SUBSTR(empname,1,3) from emp2;
4、显示所有员工的姓名，用 a 替换A。
update emp2 set empname = REPLACE(empname,’A’,’a’);
select REPLACE(empname,’A’,’a’), empname from emp2;

5、显示所有员工的日薪金，忽略余数。每个月的天数都以30天计。
–显示所有员工的日薪金，忽略余数,四舍五入。
–每个月的天数都以30天计。
select trunc(sal/30),round(sal/30), empname
from emp2;

6、列出在部门SALES(销售部)工作的员工的姓名，假定不知道销售部的部门编号。
–列出在部门SALES(销售部)工作的员工的姓名，
–假定不知道销售部的部门编号】
select *
from emp2
WHERE DEPTNO = (select DEPTNO from dept where dname = ‘SALES’);

select *
from emp2 E,
DEPT D
WHERE D.DEPTNO = E.DEPTNO
AND D.DNAME = ‘SALES’

===============================================================

–add_month(注意一下2月28/29日)
select empname, hiredate,add_months(hiredate,6)
from emp2 ;
select empname, hiredate,add_months(hiredate,-6)
from emp2 ;

–last_day
select hiredate,
add_months(hiredate,1),
to_char(add_months(hiredate,1),’dd’),
add_months(hiredate,1)-to_char(add_months(hiredate,1),’dd’)
from emp2;

select add_months(trunc(sysdate,’mm’),1)-1 ,
add_months(trunc(sysdate,’mm’),1),
trunc(sysdate,’month’),
sysdate
from dual;

–ROUND
select hiredate,
ROUND(HIREDATE,’YEAR’),
ROUND(HIREDATE,’MONTH’),
ROUND(HIREDATE,’DDD’),
ROUND(HIREDATE,’DAY’)
from emp2;

–TRUNC
select hiredate,
TRUNC(HIREDATE,’YEAR’),
TRUNC(HIREDATE,’MONTH’),
TRUNC(HIREDATE,’DDD’),
TRUNC(HIREDATE,’DAY’)
from emp2;

–EXTRACT
select hiredate,
EXTRACT (YEAR FROM hiredate),
EXTRACT (MONTH FROM hiredate),
EXTRACT (DAY FROM hiredate)
from emp2;

–MONTHS_BETWEEN
select hiredate,
MONTHS_BETWEEN(SYSDATE,HIREDATE)
from emp2;

==============================================================