Oracle学习记录1（单行函数，多行函数）

单行函数

1.字符大小写

LOWER       小写

SELECT LOWER('HELLO WORLD') FROM DUAL;//结果：hello world

UPPER       大写

SELECT UPPER('hello world') FROM DUAL;//结果：HELLO WORLD

INITCAP     首字母大写

SELECT INITCAP('hello world') FROM DUAL;//结果：Hello World

2.字符控制

1. substr(a,b) 从a中，第b位开始取(从1开始)

SELECT SUBSTR('ABC',2) FROM DUAL; //结果:BC

2.substr(a,b,c) 从a中，第b位开始取,取后c位（超出选最后一位）

SELECT SUBSTR('ABCDE',2,2) FROM DUAL;//结果：BC

3.length 字符数  lengthb字节数（汉字和字母的区别，汉字2字节，字母1字节）

SELECT LENGTH('ABC') FROM DUAL;//结果：3

SELECT LENGTH('你') FROM DUAL;//结果：1

SELECT LENGTHB('ABC') FROM DUAL;//结果：3

SELECT LENGTHB('你') FROM DUAL;//结果：2

4.instr(a,b)找位置

SELECT INSTR('abc','b') FROM DUAL;//结果：2

5.lpad 左填充 rpad 右填充

select lpad('abcd',10,'*') 左,rpad('abcd',10,'*') 右 from dual;结果： 左******abcd 右abcd******

6.trim 去掉前后指定的字符(区分大小写，对字符串中间字符无效)

select trim('H' from 'Hello hWorldh') from dual;//结果：ello hWorldh

7.replace 替换（区分大小写）

select replace('Hello WOrld','l','*') from dual;//结果：He**o WOr*dL

8.concat连接字符

SELECT CONCAT('Hello',' World' ) FROM DUAL;

SELECT 'Hello'|| ' World' || ' ！！' FROM DUAL;

3.数字函数

1.Round 四舍五入

select Round(12.15,1) from dual;//结果：12.2

select Round(12.15,-1) from dual;//结果：10

2.Trunc 截断

select trunc(12.15,1) from dual;//结果：12.1

select trunc(12.15,-1) from dual;//结果：10

4.日期函数

1.查询默认时间格式

select * from nls_database_parameters;

其中字段NLS_DATE_FORMAT，为默认的日期格式，可以通过修改默认格式，来修改显示格式。

2.当前时间

select sysdate from dual;

3.格式化时间

select to_char(sysdate,'yyyy-mm-ddhh24:mi:ss') from dual;

4.时间添加

select (sysdate-1) 昨天,sysdate 今天,(sysdate+1) 明天 from dual;

5.日期是可以相减，不能相加。相加没有意义。

6.months_between 相差的月数

直接相减，和使用months_between的区别：估计会取整，还没实验。

7.添加月份add_months（可以直接加到下一年）

select add_months(sysdate,53) from dual;

8.last_day本月最后一天。

SELECT  last_day(SYSDATE) FROM DUAL;

9. next_day（下一个星期几，是几号。）

select next_day(sysdate,'星期五') from dual;

next_day应用场景。

备份（异地容灾）

分布式数据库

10.日期四舍五入（都是使用round字段，可以对数据四舍五入，Trunc也可以截断）

select round(sysdate,'month'),round(sysdate,'year') from dual;

5.转换函数

隐式转换：oracle来做

比如yyyy-mm-dd,添加到数据库，会根据默认格式，隐式转成DD-MON-RR

显示转换：我们来做，主要。

 

select to_char(sysdate,'yyyy-mm-ddhh24:mi:ss"今天是"day')from dual;//结果：2018-02-0921:12:53今天是星期五

小数：.

千位符：，

本地货币代码：L

select to_char(sal,'L9,999.99') from emp;//结果：￥1,600.00

6.通用函数

1.NVL(a,b)

如果a为null。那么返回B。

2.nvl2(a,b,c) 

如果a为null的时候，返回c，否则返回b。

3.nullif(a,b) 

如果a=b的时候，返回null，否则返回a。

4.coalesce （字段）

从coalesce中取字段值，从左到右找到第一个不为null的值

select comm,sal,coalesce(comm,sal) "第一个不为null的值" from emp;

7.条件表达式

相当于if elseif else 的写法

1.Oracle写法

SELECT EMPNO,ENAME,SAL 旧,
decode(EMPNO,7369,SAL+1000,
             7499,SAL+200,
             SAL+400) 新
FROM EMP;

2.SQL99写法

SELECT EMPNO,ENAME,SAL 旧,
case EMPNO WHEN 7369 THEN SAL+1000
           WHEN 7499 THEN SAL+200
           ELSE SAL+350
           END 新
FROM EMP;

多行函数

1.AVG( )：平均

2.COUNT( )：数据个数

3.MAX( )：最大值

4.MIN( )：最小值

5.SUM( )：数据求和

Distinct去重，可以在函数里使用，例：

Select Count（distinct deptno）from emp;

分组函数

1. GROUP BY（分组）

SELECT DEPTNO,AVG(SAL)

FROM EMP

GROUP BY DEPTNO;

2. HAVING（过滤）

SELECT DEPTNO,AVG(SAL)

FROM EMP

GROUP BY DEPTNO

HAVING AVG(SAL) > 2000;

sql优化：

1.没有多行函数情况下where和having都可以使用。优先where。

having先分组在过滤。

where先过滤在分组。

2.日期和字符只能在单引号中出现，双引号表示列的别名。