Oracle查询，分页查询，函数

一、oracle中的基本查询
先创建一个class
1、连接符
concat将字符串连接起来，相当于java中的+号
select concat(‘hello’,‘world’) from dual;
dual是一个虚拟表，作用就是满足select的语法要求
||连接字符串
查询员工表中的信息，显示xxx的薪水是xxx
select ename||‘的薪水是’||sal from emp;

2、where 过滤条件
查询部门编号为10的员工
select * from emp where deptno=10;

3、字符和日期要包含在单引号中，字符的大小写敏感，日期格式敏感
select * from emp where ename=‘King’;
是查不出名字为KING的员工的
需要写成select * from emp where ename=‘KING’;
查询入职时间为17-11月-81的员工，默认的时间格式为DD-MM-YY
select * from emp where hiredate=‘17-11月-81’;
但是日期格式改为我们认识的1981-11-17是查询不了的
select * from emp where hiredate=‘1981-11-17’;
修改本次会话的日期格式
alter session set nls_date_format=‘YYYY-MM-DD’;
select * from emp where hiredate=‘1981-11-17’;就可查询出结果了

4、比较运算符
= 等于

大于
< 小于
= 大于等于
<= 小于等于
<> 不等于也可以是!=
between … and … 在两个值之间，包含边界
查询工资在800-2450的员工
select * from emp where sal between 800 and 2450;
in 等于列表中的一个值
not in 不在列表中的值
查询部门编号10和20下的所有员工
select * from emp where deptno in(10,20);
like 模糊查询
查询员工姓名为S开头的
select * from emp where ename like ‘S%’;
查询运功姓名是4个字的员工，一个下划线代表一位
select * from emp where ename like ‘___’;
插入一条语句
insert into emp(empno,ename,sal,deptno) values(10086,‘联_通’,3000,10);
查询姓名带下划线的所有员工，这样是查询不出的
select * from emp where ename like '%%’;
设置转义字符就可以了
select * from emp where ename like ‘%_%’ escape ‘’;
注意：oracle中的事务是手动开启的，如果现在输入rollback;事务会回滚，要插入的数据保存需要手动commit;
is null 是空值
is not null 是非空

5、逻辑运算符
and
or
not

6、排序
order by
asc:升序
desc:降序
order by 语句在select语句的结尾，默认是升序
查询员工号，按照月薪排序
select * from emp order by sal;
order by后可以跟 列名，表达式，别名，序号
根据列名排序
select ename,sal,sal12 as money from emp order by sal;
根据别名排序
select ename,sal,sal12 as money from emp order by money desc;
根据序号排序，3指的是第3个列，只能写有的列
select ename,sal,sal*12 as money from emp order by 3 desc;
写多个排序条件,desc只能作用于离它最近的列
select * from emp order by deptno desc,sal desc;
null值的排序
按照奖金升序排列，null值排在后面，是正常的
select * from emp order by comm;
按照奖金降序排列，oracle中，null值最大，null值排在前面了，需要将null值排在最后
select * from emp order by comm desc nulls last;

二、子查询
子查询要少用
子查询解决不能一步到位的查询
1、查询工资比SCOTT高的员工
SCOTT的工资
select sal from emp where ename=‘SCOTT’
比SCOTT的工资3000高的人
select ename,sal from emp where sal>3000;
使用子查询
select * from emp where sal>(select sal from emp where ename=‘SCOTT’);

2、子查询可以放在列上
select ename,sal,(select job from emp where empno=7839) job from emp;

3、主查询和子查询可以不是同一张表；只要子查询返回的结果主查询可以使用即可
查询部门名称是SALES的员工信息
select * from emp where deptno=(select deptno from dept where dname=‘SALES’);

三、oracle中的伪列
1、rowid
rowid是物理存在的，唯一的，但是其值并未存储在表中，所以不支持增删改操作
select rowid,emp.* from emp;
创建class表，用于模拟重复数据
create table class(
classid number(4),
cname varchar2(20)
);
利用rowid删除重复行数据
分析
* 找到重复的数据
方法1：select distinct * from class;
方法2：select * from class group by classid,cname having count()>1;
只保留一个重复的就可以了，利用rowid的唯一性，将条件改为>=1，保留没有重复的
select max(rowid) from class group by classid,cname having count()>=1;
使用子查询，删除重复的数据
delete from class where rowid not in(select max(rowid) from class group by classid,cname having count(*)>=1);

2、rownum
rownum伪列是Oracle进行查询获取到结果集之后在加上去的一个伪列，
这个伪列对符合条件的结果添加一个从1开始的序列号
select rownum,ename,job,sal from emp;
rownum永远按照默认的顺序生成
select rownum,ename,job,sal from emp order by sal;
如果要按照排序后的效果重新生成rownum，必须使用子查询
select rownum,ename,job,sal from(select * from emp order by sal);

3、利用rownum进行分页查询
oracle中没有top命令，也没有limit命令，分页查询可以通过rownum来实现
rownum不能直接写> >=的条件 >=1是特例，只能使用< <=的条件
select * from emp where rownum>1;
所以不能使用这种来进行分页
select * from emp where rownum<=5 and rownum>=1;这句可以正常返回
但是下一页的数据就取不到了,我们以每页显示5条，取第二页的数据为例
select * from emp where rownum<=10 and rownum>=6
利用子查询，重新给查询的结果来进行生成rowid,考虑到可能会使用到排序
select rownum,ename,sal from emp order by sal;
所以，里面不能直接使用rownum来限制了，变为：
select rownum,e.* from(select * from emp) e where rownum<=10;
这样就设置了结果集的结束位置
rownum不能直接写大于和大于等于条件，但是可以给rownum取别名，将rownum变为一列数据
select rownum r,e.* from (select * from emp) e where rownum<=10;
但是查询出来的r在这里不能作为where条件，所以需要再次套一层子查询，形成最终的分页查询语句
最终的分页查询SQL：
select * from
(select rownum r,e.* from
(select * from emp) e where rownum<=10) where r>=6;
以后只要改起始的记录数和结束的记录数
如果有排序，有where条件，记得加在最里层的select语句后面

四、索引
大量数据的查询优化，第一选择—索引
单列索引:基于单个列建立的索引，如：
create index 索引名 on 表名(列名);
复合索引：基于两列或多列的索引，在同一张表上可以有多个索引，但是要求列的组合必须不同，如：
create index emp_index1 on emp(ename,job);
create index emp_index1 on emp(job,ename);

1、索引使用原则：
(1)在大表上建立索引才有意义
(2)在where子句或是连接条件上经常使用的列上建立索引
(3)索引的层次不超过4层

2、索引缺点：
(1)建立索引，系统要占用大约为表1.2倍硬盘和内存空间来保存索引。
(2)更新数据时，系统必须要额外的时间来同时对索引的更新，以保持数据和索引的一致性。

实践表明：不恰当的索引不但对性能没有优化，反而会降低系统的性能。
因为大量的索引在进行插入、修改和删除时比没有索引花费更多的系统时间。
在很少或从不引用的字段上或逻辑型的字段（如男或女、是或否）等列上建立索引是不恰当的。

一、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表解锁
这就形成了死锁
死锁一般需要在程序设计时就避免掉