• 单行函数的案例


    select * from scott.emp;
    select ename "name"from scott.emp;
    --lower转化小写的用法
    select lower(ename) ename ,sal from scott.emp;
    
    select * from scott.emp where lower(ename)='allen';
    --upper 转化大写
    select upper(ename) "ename" from scott.emp ;
    --INITCAP 首字母大写
    select INITCAP(ename) as ename from scott.emp ;
    --CONCAT 或 ||连接
    select concat(ename,sal )from scott.emp;
    select concat ('wang','feng') ename from scott.emp;
    select ename||'  '||sal  ename from scott.emp;
    select '@'||ename||'@'as ename from scott.emp;
    --SUBSTR
    select SUBSTR(ename ,1,3)from scott.emp;
    select substr(hiredate , 0)from scott.emp;
    --寻找月份
    select* from scott.emp where hiredate = '22-2月-81';
    select* from scott.emp where hiredate > '1-1月-81';
    select * from scott.emp where hiredate between '1-1月-81'and'31-12月- 81';
    --LENGTH 长度
    select length(ename) as chang from scott.emp;
    --INSTR返回第一次出现某字符的位置
    select INSTR(ename,'L')from scott.emp;
    --LPAd 变成一个长度为10的字符,不够用*代替
    select lpad (ename ,10,'*')FROM scott.emp;
    --RPAD 变成一个长度为10的字符串,不够在其后面加*
    select rpad (ename ,10,'*')from scott.emp;
    select rpad (ename ,3)from scott.emp;
    --TRIM 去除字符两端的某个字符
    select trim ('C'from ename)from scott.emp;
    select '['||'    '||ename||'   '||']'from scott.emp;
    select trim (ename)from (select '    ['||'    '||ename||'   '||']    ' ename from scott.emp);
    select trim ('a'from 'aaabbbbaa') from scott.emp;
    --"Contains 'a'?"
    --select from scott.emp;
    --ROUND: 四舍五入
    select round(7850.3854512145,5)from scott.emp;
    select round(7850.38,5)from scott.emp;
    select round(7850.38,-2)from scott.emp;
    --TRUNC:     截断
    select trunc (222222.2222,2)from scott.emp ;
    select trunc (222222,-2)from scott.emp ;
    --日期报错select trunc (hiredate)from scott.emp;
    select trunc (222222.222,0)from scott.emp ;
    --MOD: 求余
    select mod (3,2)from scott.emp;
    --sysdate 返回时间毫秒表示
    select sysdate-hiredate  from scott.emp;
    --substr负数从后面算起
    Select substr('abcde',-4,3) from scott.emp;
    --replace 
    select replace (ename,'A','a')from scott.emp;
    --Months_between()
    select months_between(sysdate,add_months(sysdate,-2)) from scott.emp;
    select sysdate from scott.emp;--返回现在时间
    --add_months
    select add_months(sysdate,1) from scott.emp;
    --select add_days(sysdate,2)from scott.emp;              add-years (),add_day()无效
    --next_day
    select next_day(sysdate,'星期一')from scott.emp;
    --order by
    select * from scott.emp  order by sal,empno asc;
    select * from scott.emp  order by sal,empno desc;
    --Last_day
    select last_day (sysdate)from scott.emp;
    select add_months (sysdate,-2) from scott.emp;
    
    --ROUND 日期
    select round (sysdate,'year')from scott.emp; 
    select round (sysdate ,'month')from scott.emp;
    select round (sysdate,'day')from scott.emp;
    --trunc截取日期
    select trunc (sysdate,'year')from scott.emp;
    select trunc (sysdate,'month')from scott.emp;
    select trunc (sysdate,'day')from dual;
    --转化函数
    -- to_char()对于日期的转化
    select to_char (sysdate,'yyyy')from scott.emp;
    select to_char (hiredate,'yy')from scott.emp;
    select to_char (sysdate ,'yyyy-mm-dd')from scott.emp;
    select to_char (hiredate, 'mm')from scott.emp;
    --生日在5到8月之间
    select *from scott.emp where to_number(to_char (hiredate, 'mm'))between 7 and 9;
    select *from scott.emp where to_number(to_char (hiredate, 'mmdd'))between to_number(to_char (sysdate, 'mmdd')) and  to_number(to_char (add_months(sysdate,2), 'mmdd')); 
    select to_number(to_char (add_months(sysdate,2), 'mm')) from dual;
    select to_char(sysdate,'fmyyyy-mm-dd') from scott.emp;
    select to_char (hiredate,'fmyyyy-mm') from scott.emp;
    select to_char (sysdate,'fmyyyy-mm-dd-hh24-mi-ss')from scott.emp;
    select to_char (sysdate,'fmyyyy/mm/dd/hh/mi/ss')from scott.emp;
    select to_char(sal,'l999,999') from scott.emp;
    select to_char(sal,'$999,999') from scott.emp;
    select to_char (sysdate,'d')from Scott.emp;
    --To_number把字符串变成数字
    select to_number('13')+to_number('14') from dual;
    --to_date
    select to_date ('20090222','yyyymmdd')from dual;
    --NVL()函数
    select nvl(comm,0) from scott.emp;
    select * from scott.emp;
    --nvl2(comm, sal+comm, sal)
    select nvl2(comm, sal+comm, sal) from scott.emp;
    --COALESCE()函数
    select empno, ename, sal, comm, coalesce(sal+comm, sal, 0)as sal from scott.emp;
    --case
    select empno, ename, sal, case
    deptno
    when 10 then '财务部' when
    20 then '研发部' when 30
    then '销售部'
    else '未知部门'
    end 
    from scott.emp;
    --DECODE()函数
    select empno, ename, sal, decode(deptno,
    10, '财务部', 
    20, '研发部',
    30, '销售部', '未知
    部门') 部门
    from scott.emp;
    select * from scott.emp where job != 'CLERK';
    
    select * from scott.emp  order by sal desc ;
    --查询出每个部门的编号、名称、位置、部门人数、平均工资
    select * from scott.emp where deptno = 10;
  • 相关阅读:
    小学生双倍快乐
    暑期随笔
    akm
    设计原型
    测试与优化
    小学生求求你别折磨程序员
    小学生快乐刷题
    枪在手,跟我走

    第一次团队作业
  • 原文地址:https://www.cnblogs.com/fy02223y/p/7198652.html
Copyright © 2020-2023  润新知