SQL语句（2）--- 函数
2020-11-17 17:17 EniNiemand 阅读(92) 评论(0) 编辑 收藏 举报- 单行函数：用了一个函数（是什么函数？不用管），最终返回的结果是1:1。也就是说用了这个函数后，原来表中有多少行，结果显示的就有多少行，这就叫1:1
- 字符处理函数 11个 必须掌握
- LOWER ：将指定的字符串内的字符变成小写
- UPPER： 将指定的字符串内的字符变成大写
- INITCAP： 将指定的字符串内的第一个字符变成大写，其他的转换成小写
- CONCAT：连接字符串，类似于管道符 ||，单个concat函数中，最多只能拼接两项
- SUBSTR： 截取指定长度的字符串。
- substr(c1,n1,n2)---c1 待截取字符串，n1 从第几位开始截取，n2 要截取几位
- substr(exp1,exp2) exp1 待截取字符串 ， exp2 从第几位开始截取，默认截取到最后
- LENGTH：返回字符串的长度
- ·INSTR：返回要查找的字符在整个字符串中的位置，（返回的是绝对位置） instr(exp1,exp2,exp3) --- exp1 待处理的字符串，exp2 要查找的字符/字符串，exp3 从第几位开始查
- LPAD/RPAD：返回指定长度的字符串，长度不够进行左/右填充
- TRIM：去除前后的空格
- REPLACE：替换字符串里指定的内容
- 数字函数
1. ROUND：四舍五入
语法
例子： select round(123.123,1) from dual; 返回结果为：123.1
select round (123.123,2) from dual; 返回结果为：123.12
2. TRUNC：截断函数，不进行四舍五入
3. MOD：取余
转换函数
隐式转换---尽量避免
显式转换：强制完成类型转换，大类型要长的像
数字转字符：to_char
日期转字符：to_char
字符转数字：to_numer
字符转日期：to_date
嵌套函数
单行函数可以嵌套任意层
多行函数最多只能嵌套两层
通用函数
NVL（exp1,exp2）将空值转换成实际值 ----- exp1 待处理的字段， exp2 替换的值， 如果exp1字段存在 null，则将其替换成exp2，如果不存在，则返回本身值 exp1和exp2 类型可以是任意类型
NVL2（exp1,exp2,exp3）---- 如果exp1 存在null值，返回exp3的值，如果exp1不为null,则返回exp2的值 exp2和exp3的类型可以是除了LONG以外的任何类型
COLAESCE：返回第一个不为空的值
条件表达式
DECODE：Oracle独有的，几种用法如下：
第一种用法：
DECODE(条件，值1，返回值1，值2，返回值2，.....值n，返回值n，缺省值）
含义解释：
IF 条件=值1 THEN
RETURN 值1
ELSEIF 条件=值2 THEN
RETURN 值2
......
ELSEIF 条件=值n THEN
RETURN 值n
ELSE
RETURN 缺省值
END IF
SQL> select ename,sal,decode(ename,'SMITH',sal+2000,'SCOTT',sal-1000,sal) as newsal from test;
ENAME SAL NEWSAL
-------------------- ---------- ----------
SMITH 800 2800
ALLEN 1600 1600
WARD 1250 1250
JONES 2975 2975
MARTIN 1250 1250
BLAKE 2850 2850
CLARK 2450 2450
SCOTT 3000 2000
KING 5000 5000
TURNER 1500 1500
ADAMS 1100 1100
JAMES 950 950
FORD 3000 3000
MILLER 1300 1300
第二种用法：
DECODE（字段或者字段运算，值1，值2，值3）
含义解释：
当字段或者字段运算的结果等于值1时，则函数返回值2，否则返回值3
SQL> create table test as select decode(mod(rownum,2),0,'1','2') as a from all_objects;
Table created.
SQL> select * from test where rownum<5;
A
--
2
1
2
1
SQL>
- CASE：SQL委员会指定的
1 第一种CASE用法 2 SQL> select empno,ename,sal,case ename when 'SCOTT' then sal+1000 3 2 when 'KING' then sal+200 4 3 when 'JAMES' then sal+3000 5 4 else sal end as newsal 6 5 from test; 7 8 EMPNO ENAME SAL NEWSAL 9 ---------- -------------------- ---------- ---------- 10 7369 SMITH 800 800 11 7499 ALLEN 1600 1600 12 7521 WARD 1250 1250 13 7566 JONES 2975 2975 14 7654 MARTIN 1250 1250 15 7698 BLAKE 2850 2850 16 7782 CLARK 2450 2450 17 7788 SCOTT 3000 4000 18 7839 KING 5000 5200 19 7844 TURNER 1500 1500 20 7876 ADAMS 1100 1100 21 7900 JAMES 950 3950 22 7902 FORD 3000 3000 23 7934 MILLER 1300 1300 24 25 14 rows selected. 26 27 SQL> 28 第二种CASE用法 29 SQL> select empno,ename,sal,case when ename='SCOTT' and sal<5000 then sal+1000 30 2 when ename='KING' and sal>4000 then sal-1000 31 3 else sal end as newsal 32 4 from test; 33 34 EMPNO ENAME SAL NEWSAL 35 ---------- -------------------- ---------- ---------- 36 7369 SMITH 800 800 37 7499 ALLEN 1600 1600 38 7521 WARD 1250 1250 39 7566 JONES 2975 2975 40 7654 MARTIN 1250 1250 41 7698 BLAKE 2850 2850 42 7782 CLARK 2450 2450 43 7788 SCOTT 3000 4000 44 7839 KING 5000 4000 45 7844 TURNER 1500 1500 46 7876 ADAMS 1100 1100 47 7900 JAMES 950 950 48 7902 FORD 3000 3000 49 7934 MILLER 1300 1300 50 51 14 rows selected. 52 53 SQL> 54
- 多行函数：用了一个函数（是什么函数？不用管），最终返回的结果是针对每一个结果集返回一行。
- AVG / SUM / MIN / MAX / COUNT / COUNT(DISTINCT)----求平均 / 求和 / 最小 / 最大 / 行数 / 返回不重复的非NULL值
- GROUP BY 分组函数
- select 后面没有跟的字段都可以用于group by 后面的字段，也就是group by可以使用select后面没有跟的任何字段
- select 后面的字段除聚合函数以外的其他字段，都必须在group by 后面的字段里
- group by 后面的字段是不能使用别名的，但是order by后面的字段是可以使用别名的
- having 后面的字段也是不能使用别名
- having 子句在order by 子句之前
- 举例：求出每个部门中工资最高的人（只有一张emp表--ename||deptno||sal）
1 SQL> select testemp.deptno,ename,sal from testemp, 2 (select deptno,max(sal) max_sal from testemp group by deptno) t where testemp.deptno=t.deptnoand sal=t.max_sal order by deptno; 3 4 DEPTNO ENAME SAL 5 ---------- -------------------- ---------- 6 10 KING 5000 7 20 FORD 3000 8 20 SCOTT 3000 9 30 BLAKE 2850
