单行函数
如果在安装数据库时没有对scott与hr库进行解锁，也可输入命令进行解锁于加密
SQL> 管理员登录: sqlplus / as sysdba
SQL> 1. 解锁: alter user scott account unlock;
SQL> 2. 改密码: alter user scott idenfitied by 新密码;
字符函数
特点：
l 操作数据对象
l 接受参数返回一个结果
l 只对一行进行变换
l 每行返回一个结果
l 可以转换数据类型
l 可以嵌套
l 参数可以是一列或一个值
例：select lower('Hello WORLd') 转小写,upper('Hello WORLd') 转大写,initcap('hello world') 首字母大写from dual;
转小写 转大写 首字母大写
----------- ----------- -----------
hello world HELLO WORLD Hello World
SQL> --substr(a,b)从a中，第b位开始截取
SQL> select substr('Hello World',3) fromdual;
SUBSTR('H
---------
llo World
SQL> --substr(a,b,c) 从a中，第b位开始取,取c个位
SQL> select substr('Hello World',3,4) fromdual;
SUBS
----
llo
SQL> --length 字符数 lengthb 字节数
SQL> select length('HelloWorld') 字符数,lengthb('HelloWorld') 字节数
2 from dual;
字符数 字节数
---------- ----------
11 11
SQL> --instr 在母串中，查找子串
SQL> select instr('HelloWorld','ll') from dual;
INSTR('HELLOWORLD','LL')
------------------------
3
SQL> --lpad 左填充 rpad 右填充
SQL> select lpad('abcd',10,'*')左,rpad('abcd',10,'*')右from dual;
左 右
---------- ----------
******abcd abcd******
SQL> --trim: 去掉前后指定的字符
SQL> select trim('H'from 'Hello WorldH') from dual;
TRIM('H'FR
----------
ello World
SQL> --replace 替换
SQL> select replace('HelloWorld','l','*') from dual; 将l替换为*
REPLACE('HE
-----------
He**o Wor*d
数值函数
ROUND(a,b) 将a这个数保留b个小数点 b为-1为保留各位 -2为十位以此类推
SQL> select ROUND(45.926,2) 一, ROUND(45.926, 1) 二, ROUND(45.926, 0) 三，
2 ROUND(45.926, -1) 四, ROUND(45.926, -2) 五
3 from dual;
ROUND(45.926, -1) 四, ROUND(45.926, -2) 五
一 二 三 四 五
---------- ---------- ---------- --------------------
45.93 45.9 46 50 0
TRUNC(a,b) 将a保留b个小数点后直接舍去
1 select TRUNC(45.926, 2) 一, TRUNC(45.926, 1) 二, TRUNC(45.926, 0) 三,
2 TRUNC(45.926, -1) 四, TRUNC(45.926, -2) 五 from dual
一 二 三 四 五
---------- ---------- ---------- --------------------
45.92 45.9 45 40 0
mod(a,b) 求a与b的余数
日期函数
当前时间select sysdate from dual;
SYSDATE
--------------
01-4月 -13
SQL> select to_char(sysdate,'yyyy-mm-ddhh24:mi:ss') from dual; 设置日期格式用到to_char函数
例： select (sysdate-1) 昨天, sysdate 今天, (sysdate+1) 明天 from dual;
昨天 今天 明天
-------------- ----------------------------
31-3月 -13 01-4月 -13 02-4月 -13
SQL> --计算员工的工龄
例： selectename,hiredate,(sysdate-hiredate) 天,(sysdate-hiredate)/7 星期,
2 (sysdate-hiredate)/30 月,(sysdate-hiredate)/365 年
3*from emp
例：select sysdate+hiredate from emp; 此语句为错误，因为不允许日期 +日期
SQL> --MONTHS_BETWEEN 计算员工的工龄
MONTHS_BETWEEN(日期一，日期二) 返回两个日期相差的月数
例：selectename,hiredate,(sysdate-hiredate)/30 一,MONTHS_BETWEEN(sysdate,hiredate) 二from emp;
ADD_MONTHS(日期一，日期二) 向指定日期中加上若干月数
例： select ADD_MONTHS(sysdate,83)from dual; 83个月后
NEXT_DAY(日期一，日期二) 指定日期的下一个日期
LAST_DAY(日期一，日期二) 本月的最后一天
ROUND(当前日期,以什么四舍五入) 日期四舍五入
TRUNC(当前日期,以什么截断) 日期截断
例：selecttrunc(sysdate,'month'),trunc(sysdate,'year') from dual; 当前日期以年分割
转换函数
隐士转换：oracle数据库自己将符合日期格式的字符串转为日期叫做隐士转换
显示转换：我们用to_char函数把日期值按照给定的格式显示为字符串
SQL优化： 尽量使用显式转换，省的oracle去检查你这个字符串能不能转换
SQL> --隐式转换的前提：被转换对象是可以转换的 比如“123” 就不能转为abc
SQL> --显式转换
显示转换用到的函数
to_char(日期，‘格式’) 必须包含在单引号中而且大小写敏
SQL> --2013-04-01 14:48:03 今天是星期一
例： selectto_char(sysdate,'yyyy-mm-dd hh24:mi:ss "今天是"day') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:
-----------------------------------
2013-04-01 14:50:04 今天是星期一
日期格式表
SQL> --查询员工的薪水：两位小数 本地货币代码 千位符
例： select to_char(sal,'L9,999.99')from emp;
TO_CHAR(SAL,'L9,999
-------------------
￥800.00
￥1,600.00
￥1,250.00
￥2,975.00
￥1,250.00
￥2,850.00
￥2,450.00
￥3,000.00
￥5,000.00
￥1,500.00
￥1,100.00
TO_CHAR(SAL,'L9,999
-------------------
￥950.00
￥3,000.00
￥1,300.00
通用函数
nvl2(a,b,c) 当a=null时返回c，否则返回b nvl2函数是nvl函数的增强
例：select sal*12+nvl2(comm,comm,0)from emp;
nullif(a,b) 当a=b时 返回null；否则返回a
例： select nullif('abc','abc') fromdual; 返回null
COALESCE (expr1, expr2, ..., exprn) 从左往右找到参数中第一个不为空的值返回
例： select comm,sal,COALESCE(comm,sal) from emp;
CASE 表达式
例：给员工涨工资: 总裁1000 经理800 其他400
SQL> select ename,job,sal 涨前薪水,
2 casejob when 'PRESIDENT' thensal+1000
3 when 'MANAGER'thensal+800
4 else sal+400
5 end 涨后薪水
6 from emp;
DECODE 函数
DECODE(列, 条件一, 结果一 [, 条件二, 结果二,...,] [, 都不符合的结果])
SQL> select ename,job,sal 涨前薪水,
2 decode(job,'PRESIDENT',sal+1000,
3 'MANAGER',sal+800,
4 sal+400) 涨后薪水
5 from emp;
多行函数
多行函数（分组函数）：作用一组数据，并对一组数据返回一个值
员工的工资总额select sum(sal)from emp;
员工人数select count(*)from emp;
平均工资select sum(sal)/count(*) 方式一, avg(sal) 方式二from emp;
平均奖金（奖金有为null的）
select sum(comm)/count(*) 方式一, sum(comm)/count(comm) 方式二, avg(comm) 方式三from emp;
方式一 方式二 方式三
---------- ---------- ----------
157.142857 550 550
count(*)包含了null的数据
null： 5. 分组函数自动滤空
SQL> select count(*), count(nvl(comm,0))from emp; 解除分组函数自动虑空的功能（如果为null就替换成0）
查询部门的平均工资
SQL> select * from emp order by deptno;
例：select deptno,avg(sal) from emp groupby deptno; 给部门分组后求平均值
注意：所有没有包含在组函数中的列，都必须在group by的后面出现，像deptno没出现在平均函数中，所以要在group by中出现
如果group by后面有多列怎么办呢？
例： select deptno,job,avg(sal) fromemp group by deptno,job;
注：但是反过来行，也就是说在group by出现的列可以不在组函数中出现
不能在 WHERE 子句中使用组函数（注意）。可以在 HAVING子句中使用组函数。
where与having的区别在于WHERE子句中不能使用组函数
例：查询平均薪水大于2000的部门
select deptno,avg(sal) from emp group bydeptno having avg(sal)>2000
例：求10号 部门的平均工资
在没有组函数的时候where和having都能使用，这里就涉及到了sql优化的问题，尽量采用where
因为having要先分组再选，where是先选后再分组
写法一：select deptno,avg(sal) from emp group by deptno having deptno=10
写法二：select deptno,avg(sal) from emp wheredeptno=10 group by deptno
group by的增强
rollup(a,b)函数
相当于
SQL> group by a,b
SQL> +
SQL> group by a
SQL> +
SQL> group by null
例：select deptno,job,sum(sal) fromemp group by rollup(deptno,job);
SQL>相当于
SQL> select deptno,job,sum(sal) from empgroup by deptno,job
和
SQL> select deptno,sum(sal) from emp groupby deptno
和
SQL> select sum(sal) from emp;
SQL> --SQLPLUS 报表（了解）
SQL> break on deptno skip 2;
DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
MANAGER 2450
PRESIDENT 5000
8750
20 CLERK 1900
ANALYST 6000
MANAGER 2975
10875
DEPTNO JOB SUM(SAL)
---------- --------- ----------
30 CLERK 950
MANAGER 2850
SALESMAN 5600
9400
29025
扫一扫
2441
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
