单行函数

  • 字符函数

  • select * from emp;

  • select INITCAP(ename) from emp;–initcap函数将首字母大写

  • select lower(ename)from emp;–lower 字母小写

  • select replace(ename,'S','M') from emp;–replace 替换

函数 功能 示例 结果
INITCAP(char) 首字母大写 initcap(‘hello’) Hello
LOWER(char) 转换为小写 lower(‘FUN’) fun
UPPER(chaor) 转换为大写 upper(‘sun’) SUN
LTRIM(char,set) 左裁剪 ltrim(‘xyzadams’,’xyz’) adams
RTRIM(char,set) 右裁剪 rtrim(‘xyzadams’,’ams’) xyzad
TRANSLATE(char,from,to) 按字符翻译 translate(‘jack’,’abcd’,’1234’) j13k
REPLACE(char,search_str,replace_str) 字符串替换 replace(‘jack and jue’,’j’,’bl’) black and blue
INSTR(char,substr[,pos]) 查找子串的位置 instr(‘worldwide’,’d’) 5
SUBSTR(char,pos,len) 取字符串 substr(‘abcdefg’,3,2) cd
CONCAT(char1,char2) 连接字符串 concat(‘Hello’,’world’) Helloworld

  • 数值函数

  • 伪表：dual

  • select * from dual

  • select abs(-3) 绝对值，ceil(3.1415926)向上取整,floor(3.1415926)向下取整,power(2,3)幂,round(3.4)四舍五入 from dual

函数 功能 示例 结果
ABS(n) 取绝对值 abs(-15) 15
CEIL(n) 向上取整 ceil(44.778) 45
SIN(n) 正弦 sin(1.571) .999999979
COS(n) 余弦 cos(0) 1
SIGN(n) 取符号 sign(-32) -1
FLOOR(n) 向下取整 floor(100.2) 100
POWER(m,n) m的n次幂 power(4,2) 16
MOD(m,n) 取余数 mod(10,3) 1
ROUND(m,n) 四舍五入 round(100.256,2) 100.26
TRUNC(m,n) 截断 trunc(100.256,2) 100.25
SQRT(n) 平方根 sqrt(4) 2
  • 日期函数
  • select months_between('13-12月-2016','13-10月-2016') from dual–months_between两个日期之间的月份数
函数 功能 示例 结果
MONTHS_BETWEEN 返回两个日期间的月份 months_between(‘04-11月-05’,’11-1月-01’) 57.7741935
ADD_MONTHS 返回把月份数加到日期上的新日期 add_months(‘06-2月-03’,1) add_months(‘06-2月-03’,-1) 06-3月-03 06-1月-03
NEXT_DAY 返回指定日期后的星期对应的新日期 next_day(‘06-2月-03，’星期一’) 10-2月-03
LAST_DAY 返回指定日期所在月的最后一天 last—_day(‘06-2月-03’) 28-月-03
ROUND 按指定格式对日期进行四舍五入 round(to_date(‘13-2月-03’),’YEAR’) 01-1月-03
TRUNC 对日期按指定方式进行截断 trunc(to_date(‘06-2月-03’)，’YEAR’) 01-1月-03

多行函数

  • max: max(字段名) 返回此字段的最大值
  • min：min(字段名) 返回此字段的最小值
  • avg：avg(字段名) 返回平均值
  • sum：sum（字段名） 返回字段的和
  • count：
    • count(*)，用来查询表中有多少条记录
    • count（字段名），用来查询某个字段有值的个数
    • count(distinct 字段名),可以先去除重复再计数
  • 注意：
    • 多行函数不能和普通字段直接出现在查询语句中，除非group by
    • 多行函数和单行函数不能直接出现在查询语句中，除非group by
  • 实例
    • 查看员工的最高工资
      select max(sal),ename from emp–多行函数不能和普通字段直接出现在查询语句中，除非group by
      select max(sal)，lower(ename)from emp–多行函数和单行函数不能直接出现在查询语句中，除非group by select ename from emp
    • 查看员工的最低工资
      select min(sal) from emp
    • 查看员工的平均工资
      select avg(sal) from emp
    • 查看所有的员工工资之和
      select sum(sal) from emp
    • 查询公司有多少员工
      select * from emp
      select count(*) from emp–使用count(*)来查看一张表中有多少条记录
    • 查询有津贴的员工人数
      select count(comm) from emp–使用count(字段名),查询该字段有值的记录数
      select count(ename) from emp
    • 查询公司有多少工作种类
      select count(job) from emp
      select count(distinct job) from emp

转换函数

转换函数:在转换的时候改变的是数据的类型，数据内容不会改变，可以指定格式。

  • to_number：将数字字符转换为数字类型的数值，to_number(数字字符)
  • to_char：将数字转换为字符类型，将日期转换为字符类型 to_char(数字/日期)
  • to_date：将字符类型的日期转换为日期类型： to_date(char)
  • 实例
    • number–>char 转换的时候使用的是默认格式，
      select to_char(123) from dual–将数字转换为字符类型，使用to_char(number)
      select sal, to_char(sal) from emp
    • number–>char 使用指定的格式
      to_char(number,’格式’)
      select to_char(sal),to_char(sal,'L999,999,999') from emp
      select to_char(sal),to_char(sal,'L0000.00') from emp
      $代表美元符，9代码数字占位。例如，L999,999,999表示三位一组的显示方式L代表人民币符号，0代表保留数字个数，不足使用0补充。
    • char—>number
      to_number(数字字符)
      select to_number('123') from dual
    • char—>date，转换的字符必须是日期格式的字符串，默认格式 dd-mm-yyyy
    • 注意：因为字符串有很多，所以在字符转换为日期的时候，需要指定格式，因为日期是具备一定格式的字符组合。字符转换为日期的时候，指定的格式为字符的日期顺序，无需指定间隔符。yyyy：表示年 mm:表示月 dd表示日
      select to_date('05-12月-2016') from dual;
      select to_date('2016-05-12','yyyy-mm-dd') from dual–使用指定的格式将指定的日期字符串转换为日期
      select to_date('12-05-2016','mm/dd/yyyy') from dual
    • date—>char
    • 注意：因为日期本身就具备一定的格式在不是指定格式的情况下会默认使用dd-mm-yyyy格式显示数据。指定的格式会作为日期转换为字符串类型的显示格式存在。例如：
      yyyy-mm-dd’ ‘2016-12-05’
      yyyy/mm/dd’ ‘2016/12/05’
      yyyy”年”mm”月”dd”日”‘ 2016年12月05日
      select hiredate,to_char(hiredate) from emp–日期转换为字符的时候，不指定格式使用默认格式：dd-mm-yyyy
      select hiredate,to_char(hiredate,'yyyy-mm-dd') from emp;–使用指定格式将日期转换为字符串类型
      select hiredate,to_char(hiredate,'yyyy/mm/dd') from emp;–使用指定格式将日期转换为字符串类型
      select hiredate,to_char(hiredate,'yyyy"年"mm"月"dd"日"') from emp;–使用指定格式将日期转换为字符串类型
    • 查询入职日期在81年10月20日后的员工信息
      • 自动转型
        select * from emp where hiredate>'20-10月-1981'
      • 将日期转换为字符串
        select * from emp where to_char(hiredate,'yyyy-mm-dd')>'1981-10-20'
      • 将日期转换为按指定格式显示的日期格式
        select * from emp where hiredate>to_date('1981-10-20','yyyy/mm/dd')

其他函数

  • nvl():nvl(字段名，执行)–相当于java中的if条件判断
  • nvl2():nvl2(字段名，值，值)–相当于java中的If(){}else{}判断
  • decode():decode(字段名，条件1，执行内容1，条件2，执行内容2，条件3，执行内容3，默认执行内容)–相当于java中if(){}else if(){}else if(){}…else{}
  • 实例
    • 查询所有员工的月薪及姓名和工作
      select * from emp
      select ename,sal 基本工资,comm 绩效,sal+comm 月薪 from emp–问题：当comm为空时null，sal+comm会显示为空，而不能正常的显示月薪。
      select ename,sal 基本工资,comm 绩效,sal+nvl(comm,0)月薪 from emp
      如果comm不为空时，则sal+comm。如果comm为空时，则sal+0。
    • 查询所有员工的月薪及姓名和工作
      select ename,sal 基本工资,comm 绩效,nvl2(comm,sal+comm,sal)月薪 from emp
      如果comm不为空null，则sal+comm。如果为空null，则sal。
    • 显示员工的职称
      select ename,job,decode(job,'MANAGER','经理','SALESMAN','销售人员','普通员工') from emp
      在job字段名中，如果job为MANAGER则显示为经理，job为SALESMAN则显示为销售人员，如果不为这两个中的任意一个，默认显示为普通人员