单行函数

--单行函数
/*
  字符函数
*/
--小写转大写 upper()
select upper('hello accp!') from dual ;
--大写转小写 lower()
select lower('HELLO ACCP!') from dual ;
--单词首字母大写,其余小写 initcap()
select initcap('hello accp!') from dual ;
--返回指定字符串的十进制数 ascii()
select ascii('\') ,ascii('n') n from dual ;
--返回指定整数对应的字符串 chr()
select chr(54740) zhao from dual ;
--字符串连接 concat()
select concat('0512-','88889999') from dual ;
--在一个字符串中搜索指定的字符,返回发现指定的字符的位置.INSTR(C1,C2,I,J)
C1 被搜索的字符串
C2 希望搜索的字符串
I 搜索的开始位置,默认为1
J 出现的位置,默认为1
select instr('hello accp welcome to you !','o',6,2) from dual ;//6为查找的起始位置（从第6个位置开始查找），2为字符串出现第几次
--返回字符串的长度 length()
select length('hello accp!') from dual ;

 

 

--粘贴字符 lpad(),rpad() 在左边或右边插入指定字符(根据指定返回结果长度循环插入指定字符)
select lpad(rpad('gao',10,'*'),17,'*') from dual ; --结果 *******gao*******
select rpad('hello',15,'accp') from dual ; --结果 helloaccpaccpac
--删除字符串 ltrim() rtrim() trim()
select '  accp  ' str from dual ;
select ltrim('  accp  ') str from dual ;
select rtrim('  accp  ') str from dual ;
select ltrim(rtrim('  accp  ',' '),' ') str from dual ;
select trim(' ' from '  accp  ') str from dual ;
--截取字符串 substr(str,startIndex,length)
select substr('hello accp!',1,7) str1,substr('hello accp!',0,7) str2 from dual ;
--结果一样都是hello a
--查询雇员姓名的最后三个字母
select substr(ename,-3,3) ename from emp ;
--字符串内容替换 replace()
select replace('  accp  ',' ') str from dual ;      --accp
select replace('  accp  ',' ','*') str from dual ;  --**accp**

/*
       数值函数
      
*/
--返回大于指定值的最小的的整数 ceil()
select ceil(68.49),ceil(-68.49) from dual ; --69,-68
--返回小于指定值的最大整数
select floor(68.49),floor(-68.49) from dual ; --68,-69
--四舍五入 round()
select round(789.536),round(789.536,2),round(789.536,-2) from dual ;  --790,789.54,800
--截断小数位 trunc()
select trunc(789.536),trunc(789.536,2),trunc(789.536,-2) from dual ;  --789,789.53,700
--取余mod()
select mod(10,3) from dual ; --1
--返回一个数值的符合 sign()
select sign(5),sign(0),sign(-5) from dual ;  --1,0,-1

/*
       日期函数
*/
--当前日期 sysdate
select sysdate from dual ;
--查询10部门雇员进入公司的星期数
select floor((sysdate-hiredate)/7) "weeks" from emp ;
--求出给定日期范围的月数 months_between()
select floor(months_between(sysdate,'27-2月 -83')) "months" from dual ;
--在指定的日期上加上指定的月数求出之后的日期
select add_months(sysdate,12) "new_date" from dual ;
--求出下一个指定星期对应的日期
select next_day(sysdate,'星期五') from dual ;
--求出指定日期的最后一天 last_day()
select last_day(sysdate) from dual ;

/*
       转换函数
*/
--转换成字符串 to_char()
/*
YYYY：四位表示的年份
YYY，YY，Y：年份的最后三位、两位或一位，缺省为当前世纪
MM：01~12的月份编号
MONTH：九个字符表示的月份，右边用空格填补
MON：三位字符的月份缩写
WW：一年中的星期    
D：星期中的第几天
DY/DAY:显示星期几
DD：月份中的第几天
DDD：年所中的第几天
DAY：九个字符表示的天的全称，右边用空格补齐
HH，HH12：一天中的第几个小时，12进制表示法
HH24：一天中的第几个小时，取值为00~23
MI：一小时中的分钟
SS：一分钟中的秒
SSSS：从午夜开始过去的秒数

*/
select to_char(hiredate,'yyyy/mm/dd') "date" from emp ;    --格式化日期到字符串
select to_char(hiredate,'fmyyyy/mm/dd') "date" from emp ;  --fm去前导零
select to_char(sysdate,'fmyyyy/mm/dd hh24:mi:ss') "date" from emp ;
select to_char(sal,'L99,999') "sal" from emp ;             --用当地货币表示法
select to_char(sal,'$99,999') "sal" from emp ;             --指定美元货币表示法
/*
to_char函数特殊用法

to_char(sysdate,'d') 每周第几天
to_char(sysdate,'dd') 每月第几天
to_char(sysdate,'ddd') 每年第几天
to_char(sysdate,'ww') 每年第几周
to_char(sysdate,'mm') 每年第几月
to_char(sysdate,'q') 每年第几季
to_char(sysdate,'yyyy') 年
*/
select to_char(sysdate,'ww') from dual ;   --今年第38周
--将字符串转换成日期 to_date(string,'format')
select to_date('2009-9-18','yyyy-mm-dd') from dual ;
--将字符串转换成数字 to_number(string)
select to_number('123')+to_number('123') from dual ;

--1 查询部门30中的所有员工
select * from emp where deptno=30;

--2 列出所有办事员(CLERK)的姓名,编号和部门编号
select ename,empno,deptno from emp where lower(job)='clerk';

--3 找出佣金高于薪金的员工
/*
  truncate table emp ;
  select * from emp;
  insert into emp select * from scott.emp;
*/
select * from emp where comm > sal;

--求出每个雇员的年薪
/*
   nvl(arg,value) 如果前面的arg值为null，那么返回后面的value值   
*/
select (sal+nvl(comm,0))*12 income ,ename from emp;

--4 找出佣金高于薪金的60%的员工
select * from emp where comm>sal*0.6;

--5 找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料
select * from emp
where (deptno=10 and lower(job)='manager') or (deptno=20 and lower(job)='clerk');

--6 找出部门10中所有经理(MANAGER),部门20中的所有办事员(CLEAK),
--  既不是经理又不是办事员但薪金大于或等于2000的所有员工的详细资料
select * from emp
where (deptno=10 and lower(job)='manager') or
      (deptno=20 and lower(job)='clerk') or
      (sal>=2000 and lower(job) not in ('manager','clerk'));

--7 找出收取佣金的员工的不同工作
/*
    null类型数据要用 is null和is not null来判断,其它的任何判断都是错误的
    如=null,<null等.
    select nvl(comm,0) from emp;
    select * from emp where comm is null;
    select * from emp where comm is not null ;
*/
select distinct job from emp
where comm is not null ;

--8 找出不收取佣金或收取佣金低于100的员工
select * from emp
where comm is null or comm<100;

--9 找出各月倒数第3天受雇的所有员工
select * from emp where last_day(hiredate)-2=hiredate ;

--10 找出早于12年前受雇的员工
select * from emp where months_between(sysdate,hiredate)/12 > 12 ;

--11 以首字母大写的方式显示所有员工的姓名
select upper(ename) from emp;

--12 显示正好为5个字符的员工的姓名
select ename from emp where ename like '_____';

--13 显示不带有"R"的员工的姓名
select ename from emp where ename not like '%R%';

--14 显示所有员工的姓名的前三个字符
select substr(ename,1,3) ename from emp ;

--15 显示所有员工的姓名,用"a"代替所有的"A"
select replace(ename,'A','a') ename from emp ;

--16 显示满10年服务年限的员工的姓名和受雇日期
select ename,hiredate from emp where months_between(sysdate,hiredate)/12 > 10 ;

--17 显示员工详细信息,按姓名排序
select * from emp order by ename;

--18 显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在前面
select ename,hiredate from emp order by hiredate asc ;

--19 显示所有员工的姓名,工作和薪金,按工作的降序排序,若工作相同则薪金排序
select ename,job,sal from emp order by job desc,sal;

--20 显示所有员工的姓名,加入公司的年份和月份,按受雇日期所在月排序,若月份相同则将
--最早年份的员工排在前面
select ename,to_char(hiredate,'YYYY') year,to_char(hiredate,'MM') month from emp order by month,year;

--21 显示在一个月为30天的情况,所有员工的日薪金,忽略余数
select round(sal/30) from emp ;

--22 找出在(任何年份的)2月受雇的所有员工
select * from emp where to_char(hiredate,'mm')=2;

--23 对于每个员工,显示其加入公司的天数
select round(sysdate-hiredate) day_num,ename from emp;

--24 显示姓名字段的任何位置包含"A"的所有员工的姓名
select ename from emp where ename like '%A%' ;

--25 以年月日的方式显示所有员工的服务年限(大概)
select  trunc(temp.days/365) years,
        trunc((temp.days-trunc(temp.days/365)*365)/30) months,
        trunc(temp.days-(trunc(temp.days/365)*365+trunc((temp.days-trunc(temp.days/365)*365)/30)*30)) days
     from  (select ceil(sysdate-hiredate) days from emp ) temp ;