函数

  • 单行函数：一个输入一个输出
  • 多行函数：多个输入一个输出
  • lower(a):a转小写
  • upper(a):a转大写
  • substr(a,b):在a中，取从b位开始到结尾的子串
  • initcap():首字母大写
  • length()：字符长度
  • lengthb():字节长度
  • instr(a,b):在a中查找b，找到返回从1开始的下标，找不到返回0
  • lpad('abcd', 10, ''): 在abcd的左填充 ,一共10位，即****abcd,rpad()为右填充
  • trim():去掉前后指定的字符，select trim('H' from 'Hello WorldH') from dual; -- ello World
  • replace('Hello', '', 'l'):用替换‘Hello’中的‘l’
  • round(45.926, 2): 45.93,四舍五入
  • round(45.926, 0): 46
  • round(45.926, -1): 50
  • round(45.926, -2): 0
  • trunc(45.926, 2): 45.92, 舍去
  • mod(1600, 300): 100,取余

日期

  • 系统时间：oracle: sysdate, mysql: now()函数
  • select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;
  • select ename, hiredate, (sysdate - hiredate) 天, (sysdate-hiredate)/7 星期, (sysdate-hiredate)/30 月 from daul; -- 计算员工工龄
  • 日期 +/- 一个数字 => +/- 天数，结果仍为日期
  • 两个日期相减返回两者之间相差的天数，日期之间相加无意义
  • 可用数字除以24，来向日期 +/- 小时数
  • months_between(sysdate, hiredate): 相差月数
  • add_months(): 向指定日期加上若干个月数
  • next_day(): 指定日期的下一个日期
  • last_day(sysdate): 本月的最后一天
  • round(sysdate, 'month'):
  • round(sysdate, 'year'):
  • trunc(sysdate, 'year')
  • select next_day(sysdate, '星期二') from dual; -- 下个星期二的日期

显式类型转换

		 to_number()  <<<                >>>  to_date()  
number <=================>  character  <=================> date
		   to_char()  >>>				 <<<  to_char()
  • select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss "今天是" day ') from dual;
  • select to_char(sal, 'L9,999.99') from dual; -- ￥4.800.00， L本地货币单位 ‘，’表千位分隔符， 9表一位数字， ‘.’表小数点

通用函数

  • nvl(a, b): a为null,返回b,否则返回a
  • nvl2(a, b, c): a为null，返回c,否则返回b
  • select sal*12 + nvl2(comm,comm,0) from emp;
  • nullif(a,b): 当a=b,返回null,否则返回a
  • coalesce(expr1, ... ): 从左到右找到第一个不为null的值
  • select comm,sal, coalesce(comm, sal) from emp;

涨工资

	* select ename,job,sal 涨前,
		case job when 'PRESIDENT' then sal + 1000
				 when 'MANAGER' then sal + 800
				 else sal + 400
		end 涨后
	  from emp;
	* select ename, job, sal 涨前，
		decode(job, 'PRESIDENT', sal + 1000, 
					'MANAGER', sal + 800, 
								sal + 400) 涨后
	  from emp;

多行函数/组函数

  • avg(),count(),max(),min(),sum()
  • 组函数自动滤空，可以嵌套滤空函数来屏蔽自动滤空
  • group by子句：在select列表中所有未包含在组函数中的列都应该出现在group by子句中
  • group by deptno, job: 先按第一列分组，第一列相同在按第二列分组
  • select deptno, count(ename) from emp; 错误的，count为组函数，deptno不在group by子句中，即缺少group by子句
  • 不能在where子句中使用组函数，可以在having子句中使用组函数，其他情况可通用，但where比having效率高
  • group by rollup(a,b) <=> group by a,b + group by a + group by null.
  • break on deptno skip 2 --相同部门号只打印一次，且不同之间跳过2行
  • break on null --取消以上格式
  • select deptno,job,sum(sal) from emp group rollup(deptno,job) -- 做报表
  • wm_concat(varchar2) -- 行转列组函数
  • select deptno,wm_concat(ename) namelist from emp group by deptno;