函数在SQL 中的作用：
Ø 执行数据计算
Ø 修改单独数据项
Ø 操纵一组行的输出
Ø 替换日期的显示格式
Ø 转换列的数据类型
SQL 函数的两种类型：
Ø 单行函数
Ø 多行函数 单行函数： Ø 操作数句对象
Ø 接受函数返回一个结果
Ø 只对一行进行变换
Ø 每行返回一个结果
Ø 可以转换数据类型
Ø 可以嵌套
Ø 参数可以是一列或一个值
function_name [(arg1, arg2,...)]
单行函数分类：
1-1 字符函数
1-1-1 大小写控制函数
LOWER('SQL Course')
UPPER('SQL Course')
INITCAP('SQL Course')
例：
SELECT employee_id, last_name, department_id FROM employees WHERE LOWER(last_name) = 'higgins';
1-1-2 字符控制函数
CONCAT
与’||’
的区别：一个CONCAT
函数只能连接两个字符串
，||
可以一次连接多个。1-2 数字函数
1-2-1 ROUND函数
SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1) FROM DUAL;
DUAL 是一个‘伪表’，可以用来测试函数和表达式1-2-2 TRUNC函数
SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-2) FROM DUAL;
1-2-3 MOD函数
SELECT last_name, salary, MOD(salary, 5000) FROM employees WHERE job_id = 'SA_REP';
1-3 日期函数
1-3-1 日期的数学计算
Ø 在日期上加上或减去一个数字结果仍为日期。
Ø 两个日期相减返回日期之间相差的天数。
Ø 可以用数字除24 来向日期中加上或减去小时。1-4 转换函数 隐式 显式1-4-1 隐式数据类型转换
Oracle
自动完成下列转换：
1-4-2 显式数据类型转换
TO_CHAR 函数对日期的转换
TO_CHAR(date, 'format_model')
TO_CHAR 函数对日期的转换
SELECT last_name, TO_CHAR(hire_date, 'fmDD Month YYYY') AS HIREDATE FROM employees;
TO_CHAR 函数对数字的转换
SELECT TO_CHAR(salary, '$99,999.00') SALARY FROM employees WHERE last_name = 'Ernst';
TO_NUMBER 和 TO_DATE 函数
使用 TO_NUMBER 函数将字符转换成数字: TO_NUMBER(char[, 'format_model']) 使用 TO_DATE 函数将字符转换成日期: TO_DATE(char[, 'format_model'])
1-4-3 通用函数
NVL 假如oracle第一个参数为空那么显示第二个参数的值，假如第一个参数的值不为空，则显示第一个参数本来的值。 NVL2 假如该函数的第一个参数为空那么显示expr3参数的值，假如第一个参数的值不为空，则显示expr2参数的值。 NULLIF 假如exp1和exp2相等则返回空(NULL)，否则返回第一个值。 COALESCE 假如第一个参数为空，则看第二个参数是否是空，否则则显示第一个参数，假如第二个参数是空再看第三个参数是否为空，否则显示第二个参数，依次类推。 相当于NVL的循环使用。
NVL 函数
SELECT last_name, salary, NVL(commission_pct, 0), (salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL FROM employees;
NVL2 函数
SELECT last_name, salary, commission_pct, NVL2(commission_pct, 'SAL+COMM', 'SAL') income FROM employees WHERE department_id IN (50, 80);
NULLIF 函数
SELECT first_name, LENGTH(first_name) "expr1", last_name, LENGTH(last_name) "expr2", NULLIF(LENGTH(first_name), LENGTH(last_name)) result FROM employees;
COALESCE 函数
SELECT last_name, COALESCE(commission_pct, salary, 10) comm FROM employees ORDER BY commission_pct;
1-4-4 嵌套函数
Ø 单行函数可以嵌套。
Ø 嵌套函数的执行顺序是由内到外。
SELECT last_name, NVL(TO_CHAR(manager_id), 'No Manager') FROM employees WHERE manager_id IS NULL;
1-5
条件表达式
Ø CASE 表达式
Ø DECODE 函数1-5-1 case表达式
CASE WHEN condition1 THEN return_result1 WHEN condition2 THEN return_result2 …… WHEN conditionn THEN return_resultn ELSE default_result END
1-5-2 DECODE表达式
DECODE(col|expression, search1, result1 [, search2, result2,...,] [, default])
SELECT last_name, job_id, salary, DECODE(job_id, 'IT_PROG', 1.10*salary, 'ST_CLERK', 1.15*salary, 'SA_REP', 1.20*salary, salary) REVISED_SALARY FROM employees;
1-5-3 行列转换
从格式一到格式二 SELECT a.card_code, SUM(decode(a.q, 1, a.val, 0)) q1, SUM(decode(a.q, 2, a.val, 0)) q2, SUM(decode(a.q, 3, a.val, 0)) q3, SUM(decode(a.q, 4, a.val, 0)) q4 FROM t_change_lc a GROUP BY a.card_code ORDER BY 1;
从格式二到格式一 SELECT t.card_code, t.rn q, decode(t.rn, 1, t.q1, 2, t.q2, 3, t.q3, 4, t.q4) val FROM (SELECT a.*, b.rn FROM t_change_cl a, (SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 4) b) t ORDER BY 1, 2;
分组函数：
分组函数是对一批（一组）数据进行操作（综合）之后返回一个值。这批数据可能是整个表，也可能是按某种条件把该表分成的组。对于管理者或决策者来说综合数据才是最有价值的信息。
SELECT [column,] group_function(column), ... FROM table [WHERE condition] [GROUP BY column] [ORDER BY column];
1-6 组函数类型
Ø AVG （平均值）
Ø COUNT （计数）
Ø MAX （最大值）
Ø MIN （最小值）
Ø SUM （合计）1-6-1 AVG 和 SUM 函数
SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary) FROM employees WHERE job_id LIKE '%REP%';
1-6-2
MIN
和
MAX
函数
可以对任意数据类型的数据使用 MIN 和 MAX 函数
SELECT MIN(hire_date), MAX(hire_date) FROM employees;
1-6-3 count函数
COUNT(*) 返回表中记录总数。
SELECT COUNT(*) FROM employees WHERE department_id = 50;
COUNT(
expr)
返回
expr
不为空的记录总数
SELECT COUNT(commission_pct) FROM employees WHERE department_id = 80
COUNT(distinct expr) 返回 expr非空切不重复 记录总数
SELECT COUNT(DISTINCT department_id) FROM employees;
组函数忽略空值
SELECT AVG(commission_pct) FROM employees;
1-7 分组数据
使用group by子句分组
SELECT column, group_function(column) FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column];
• 将表中的行分成更小的组
• 使用ORDER BY 子句改变缺省的排序方式
• SELECT 列表中的所有非组函数中的列必须在GROUP BY 子句中
• GROUP BY 的列不必出现在SELECT 子句中
• 使用HAVING 子句限制组
• 可使用多个GROUP BY 列
• 由GROUP BY 子句中的列顺序，决定结果的缺省排序顺序
SELECT department_id dept_id, job_id, SUM(salary) FROM employees GROUP BY department_id, job_id ;
不能在 WHERE 子句中使用组函数。
可以在HAVING 子句中使用组函数。
SELECT department_id, AVG(salary) FROM employees WHERE AVG(salary) > 8000 GROUP BY department_id; WHERE AVG(salary) > 8000 * ERROR at line 3: ORA-00934: group function is not allowed here
1-8 过滤分组
使用 HAVING 过滤分组:
1. 行已经被分组。
2. 使用了组函数。
3. 满足HAVING 子句中条件的分组将被显示。
SELECT column, group_function FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column];
SELECT job_id, SUM(salary) PAYROLL FROM employees WHERE job_id NOT LIKE '%REP%' GROUP BY job_id HAVING SUM(salary) > 13000 ORDER BY SUM(salary);
