Oracle

一、oracle里的单行函数
1、字符函数
(1)lower转小写
select lower(‘Hello Word’) from dual;
(2)upper转大写
select upper(‘Hello Word’) from dual;
(3)initcap每个单词的首字母大写
select initcap(‘hello world’) from dual;
(4)substr截取字符串
* substr(a,b) 从a中，第3位开始取
select substr(‘Hello World’,3) from dual;
* substr(a,b,c) 从a中，第3位开始取,取4位
select substr(‘HelloWorld’,3,4) from dual;
(5)length字符数，lengthb字节数
select length(‘湖南’) 字符数,lengthb(‘湖南’) 字节数 from dual;
(6)instr(a,b) 在a中，查询b，返回找到的开始位置
select instr(‘Hello World’,‘ll’) from dual;
(7)填充指定字符
* lpad左填充
select lpad(‘1’,10,‘0’) from dual;
* rpad右填充
select rpad(‘abcd’,10,’’) from dual;
(8)trim去除前后指定的字符
select trim(‘H’ from ‘Hello WorldH’) from dual;
(9)replace替换
select replace(‘Hello World’,‘l’,’’) from dual;

2、数学函数
(1)round 四舍五入，四舍五入是绝对值的四舍五入 0表示个位，正数表示保留的小数位数，负数表示小数点左边的位数
select round(45.8954,1) 一,round(45.8954,2) 二,round(45.8954,0) 三,round(45.8954,-1) 四,round(45.8954,-2) 五 from dual;
(2)trunc 截断， 0表示个位，正数表示保留的小数位数，负数表示小数点左边的位数
select trunc(45.8954,1) 一,trunc(45.8954,2) 二,trunc(45.8954,0) 三,trunc(45.8954,-1) 四,trunc(45.8954,-2) 五 from dual;
(3)mod 取余
select mod(10,3) from dual;

3、日期函数
(1) sysdate获取当前时间
select sysdate from dual;
(2)日期计算
加减的值是天数
* 昨天、今天、明天
select sysdate-1 昨天,sysdate 今天,sysdate+1 明天 from dual;
* 计算员工工龄
select (sysdate-hiredate) 天,(sysdate-hiredate)/7 周,(sysdate-hiredate)/30 月,(sysdate-hiredate)/365 年 from emp;
* 日期不允许相加日期
select sysdate+hiredate from emp;
(3)months_between两个日期相差的月数
select (sysdate-hiredate)/30 月,months_between(sysdate,hiredate) 月 from emp;
* 单行行数的嵌套使用
select round(months_between(sysdate,hiredate),0) from emp;
(4)add_months增加月数
select add_months(sysdate,12) from dual;
(5)last_day月份的最后一天的日期数据
select last_day(sysdate) from dual;
(6)next_day指定日期的下一个星期几的日期
select next_day(sysdate,‘星期日’) from dual;
(7)round日期四舍五入
select round(sysdate,‘month’),round(sysdate,‘year’),round(sysdate,‘dd’) from dual;
(8)trunc日期截断
select trunc(sysdate,‘month’) from dual;

4、转换函数
转换的前提:被转换对象是可以转换的
(1)to_char 将某种类型的数据转为字符串显示
12小时制显示
select to_char(sysdate,‘YYYY-MM-DD HH12:mi:ss’) from dual;
24小时制显示
select to_char(sysdate,‘YYYY-MM-DD HH24:mi:ss’) from dual;
按照年月日时分秒 星期几
select to_char(sysdate,‘YYYY-MM-DD HH24:MI:SS"今天是"day’) from dual;
(2)to_date 将字符串转为日期
select * from emp where hiredate<to_date(‘2017-1-1’,‘YYYY-MM-DD’);
(3)to_number 将字符串转为数字
select to_number(‘2008’)+1 from dual;
(4)数字转字符串
select 2008||‘年’ from dual;

5、通用函数
(1)、nvl(exp1,exp2)
如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。
select sal*13+nvl(comm,0) from emp;
(2)nvl2(expr1,expr2,expr3)
如果expr1不为空返回expr2,否则返回expr3
select empno,ename,nvl2(comm,‘有奖金’,‘没有奖金’) from emp;
(3)nullif(expr1,expr2)如果expr1和expr2相等返回空，否则返回expr1
select comm,nullif(comm,300) from emp;
(4)coalesce从左到右找到第一个不为null的值
select comm,sal,coalesce(comm,sal) from emp;

6、条件表达式
(1)case 字段
when 条件 then 操作
when 条件 then 操作
else 操作
end
涨工资，总裁1000 经理800 其他400
select ename,job,sal 涨前,
case job when ‘PRESIDENT’ then sal+1000
when ‘MANAGER’ then sal+800
else sal+400
end 涨后
from emp;

(2)decode,oracle特有语法
select ename,job,sal 涨前,
decode(job,‘PRESIDENT’,sal+1000,‘MANAGER’,sal+800,sal+400) 涨后 from emp;

2、oracle中的多行函数
和单行函数相比，oracle提供了丰富的基于组的，多行的函数。
这些函数能在select或select的having子句中使用，
当用于select子串时常常都和GROUP BY一起使用。
(1)sum求总薪水
select sum(sal) from emp;
(2)count统计员工数
select count(*) from emp;
(3)avg求平均工资
select avg(sal) from emp;
(4)求平均工资和平均奖金
select avg(sal),avg(comm) from emp;
null 自动过滤
select avg(sal),avg(comm) from emp;
3、group by分组查询
select列表中，所有没有使用聚合函数的列，都应该包含在group by 中
(1)求部门的平均工资
select avg(sal),deptno from emp
group by deptno;
(2)多个列的分组，求每个部门下按照不同职位的工资情况
按照职位统计工资情况
select deptno,job,sum(sal) from emp group by deptno,job;
(3)使用rollup对group by 增强
一个查询里要显示统计部门里的职位工资+按照部门统计部门工资+统计总工资
效果参照 工资统计效果.png
相当于
按照职位统计工资情况
select deptno,job,sum(sal) from emp group by deptno,job;
+
部门工资情况
select deptno,sum(sal) from emp group by deptno;
+
整体工资之和
select sum(sal) from emp;
使用增强
select deptno,job,sum(sal) from emp group by rollup(deptno,job);
使用sqlplus的报表功能，优化格式
break on deptno skip 2;
break on deptno 表示相同的列名只显示一次
skip 2 表示每个结果跳过两行

三、oracle锁机制
数据库都有并发机制，不过带来的问题就是数据访问的冲突。
为了解决这个问题，大多数数据库用的方法就是数据的锁定。
数据的锁定分为两种方法，第一种叫做悲观锁，第二种叫做乐观锁
1、悲观锁
悲观锁顾名思义，就是对数据的冲突采取一种悲观的态度，
也就是说假设数据肯定会冲突，所以在数据开始读取的时候就把数据锁定住，
没有提交或者回滚事务前都不能操作这些数据
示例用SCOTT.class表
先将SCOTT.class的修改权限赋给xiaoming这个用户
grant update on class to xiaoming;
然后使用for update将id为2的数据锁住
select * from class where classid=2 for update;

重新开一个sqlplus,使用xiaoming登录
修改修改id为2的数据
update scott.class set cname=‘a’ where classid=2;
这时候我们会看到一直卡在这里，操作堵塞了

切换到SCOTT的sqlplus下，使用commit提交事务

这时候xiaoming的update操作才能完成

但是这样的操作会导致所有操作带锁的数据时，都要排队，效率会低下

2、乐观锁
乐观锁就是一开始假设不会造成数据冲突，在最后提交的时候再进行数据冲突检测
像我们后面学习的数据访问层的hibernate框架里就实现了乐观锁
一般做法是在数据表里新增一列，表示版本，在操作之前就获得数据的版本号
最后提交时，拿取到的版本号和数据库现在的版本号对比，如果一致就可以提交操作
否则就是已经被别人改动过了

3、死锁
用户a操作a表，加锁
用户b操作b表，加锁
两个用户都没有提交事务释放锁，这时候
a操作b表，等待b表解锁
b操作a表，等待a表解锁
这就形成了死锁
死锁一般需要在程序设计时就避免掉