我的oracle学习笔记（血泪史之五）…

这个笔记是在黑马培训之前看的汤阳光及其他老师的oracle视频，以及赵强老师的oracle培训课程中写的笔记。

自己的sql水平在这个基础上得到一个很大的提升，真多让我看到了清华的大神长啥样，大神看问题的角度和方法都不一样。不多说，直接贴笔记。

关系型数据库 行式数据库 OLTP;事务型;

HBase no_sql 列式数据库 OLAP:分析数据;

oracle数据库服务器;

war包有几个啊;

实例:instance;多个;

RAC:real application cluster;

优点:

1 Fail  Over 失败迁移;

2 Load Balance;负载均衡;

进程:是操作系统概念;

线程:是应用程序的概念;

SGA:System global area;

PGA:Process Global area;

二次提交协议;

flash back:闪回;

优化:

nvl:

空值的总结:

1 和空值进行计算的均为null;

2 null!=null;永远不成立;

3 not in null永远不成立;

4 排序时,null项降序时不符合人们的逻辑;

  修改方式如下;select * from emp  order by comm  desc nulls last 

                                          可能为null;  null空值放到最后来;

5 分组函数会自动滤空,可以添加滤空函数进行屏蔽;

列数相加,行数相乘;--笛卡尔积是多表查询的基础;

优化6：尽量不要使用集合运算;

问题一:

insert into emp values(1002,'JACK','CLERK',7782,'1992-06-20',10000,NULL,10);

和 

insert into emp( empno,ename,job,mgr,hiredate ,sal,comm,deptno) values(1002,'JACK','CLERK',7782,'1992-06-20',10000,NULL,10);

这两个语句性能上有没有差距,为什么之前老师讲jdbc的时候推荐使用下面这种方式,有没有什么特殊的原因;

问题二:

in 和not in语句可以用 or 和and 替代,那种方式效率高,推荐使用哪种方式;

首字母大写--如果两个单词分开的,则分别首字母大写;

如果别名中有特殊字符,要加引号;

最好加上as "";

给列取别名,要么加双引号,要么不加,加单引号是错误的;

select substr('a',3) from dual;从a中的第b个字符开始取,如果a的长度小于b,返回null;

重载形式也是一样的;

coalesce:联合,合并的意思;

在oracle中是从一组值中找到第一个为null的东东;

case 的条件可以是>1000;一定要是 when 'xxx';

group by中 出现的字段必须要在select中出现;

子查询注意的问题;

 1括号

 2 采用合理的书写风格;

 3 可以在主查询的where having select后放子查询;

 4 不可以在group by 后面放置子查询;

 5 强调from 后面的子查询;

 6 主查询和子查询可以不是同一张表;

在做oracle的查询时,心里必须时时有这么一个概念,sql查询语句的顺序是

from  where select group by having order by

不管有多少个查询嵌套都是这样的;

34579273

13488899975

DDL:truncate不可以回滚;因为DDL语句中有隐式提交,提交了就不能回滚,只能使用闪回操作;

DML:delete,可以回滚.

delete会产生碎片,

闪回:提交后发现错了;要闪回,要开行移动功能;

alter table emp enable row movement;

闪回:事务已经提交,不能回滚,只能用闪回;

oracle中的任何操作都是可逆的;

DDL语句隐式提交;事务不能跨越DDL;

宕机;

三种事务隔离级别:

1 读已提交--

2 序列化;--1,2是sql99规范

3 只读(非规范)

普通用户才有回收站;管理员没有;

直接删除了事;

check约束;

主键查询最快;主键其实对应于一个索引;

查询优化,由于sql语句执行是从后往前,因此往往将更有区分度的条件放到后面,从而减少检索量;

视图:简化查询,但是不能提高性能;类似于给名字很长的一个人取个短点好记的名字 ,本质并没有改变;

只是可读性增强了;

不建议通过视图去修改表中的数据;

索引的类型

1 B树索引;****oracle默认;

2 位图索引;

decode

select count(*) total,sum(decode(to_char(hiredate,'RR'),'81',1,0)) "1981" from emp;

select count(*) total,

sum(case to_char(hiredate,'RR') when '80' then 1 else 0 end) "1980",

sum(case to_char(hiredate,'RR') when '81' then 1 else 0 end) "1981",

sum(case to_char(hiredate,'RR') when '82' then 1 else 0 end) "1982",

sum(case to_char(hiredate,'RR') when '87' then 1 else 0 end) "1987"

from emp;

DAO的sql语句应该是调用oracle数据库中的plsql语句;

而不是直接写sql语句,这样更加高效;

说的直接点是调用存储过程或者存储函数;

记录型变量:数组;接收一行的数据;

引用型变量:%type;

光标开启,光标关闭;--类似于jdbc的resultrset;

show parameters cursor;列出和cursor关键字相关的东东;

从第一个开始;

属性; %notfound,

性能:能做到不访问数据库获取到数据就尽量不访问数据库;

因为很耗时;

存储过程和存储函数唯一不同的是前者没有返回值,其他的可以认为完全一样;

但是,out输出可以让两者都有多个返回值;

in,输入参数;out输出参数;

子程序是给别人调用;

不在存储过程中commit谁调用谁提交;

create or replace procedure raiseSal(empno in number,addSal in number)

as 

begin

end;

/

jvm:运行内存最大最小值设置一样;

可以提高性能;

1 java调优;

2 故障诊断;

死锁判断:ThreadDump工具

不能通过程序调用进行垃圾回收;

只能进行请求gc;进行垃圾回收;

select * from session_roles;

session_privs;

参数在定义游标时需要指定类型,类似于函数的形式参数,在打开游标时需要给定对应类型的实参;

使用游标时,记得定义exit when语句,否则出不去;

则一直会停留在最后一个位置;因为每当fetch into一次,cursor指针就往后走一格;

走到尾部就停在那,等待结束标记的执行,退出循环;

关于游标的定义和使用:

格式;

declare

cursor cemp is select ename,sal from emp;--必须是select,因为必须要有结果集,没有记录,抛出No_data_found exception;

v_ename emp.ename%type;

v_sal emp.sal%type;

begin

  open cemp;

loop

fetch cemp into v_ename,v_sal;--标示从当前循环到的项中取出对应的值,赋给两个变量,

      --记住顺序一定要和上面的select语句的取出顺序一样;

exit when cemp%notfound;--notfound是cemp的一个属性,所有的光标都有的,注意定义的这个退出必须好好考虑,一般在fetch后就要写;

--如果在fetch into后写了语句在exit,那么最后一次的该语句会执行两次;

end loop;

  close cemp;

end;

/

游标也可以嵌套,可以带参数;如

cursor cemp(v_empno number) is select sal from emp where empno=v_empno;

//注意游标中的参数类似于java中函数的形式参数,只要给定类型即可,无需给定精度;

//打开游标时传递值进去;

open cemp(7839);

close cemp;

create table t_user(

   id       number

            constraint  PK_USER_ID         primary key,

   name     varchar2(20)

            constraint  NOTNULL_USER_NAME  not null,

   regDate  date

            default sysdate,

   cardnum  varchar2(20)

            constraint  NOTNULL_USER_CNUM  not null

            constraint  UNIQUE_CARDNUM     unique,

   age      number

            constraint  CHECK_AGE  check( age >=0 ),

   depno    number

            constraint  FK_USER_DEPTNO references dept(deptno)

)

约束条件整合;

查看本表中的所有的约束：

select * from user_constraints where table_name='T_USER';

exit:可以退出不执行了;

cursor定义是是is

比如

cursor cemp is select * from emp;

可以带参数的;

cursor cemp(v_empno in number) is select * from emp where empno=v_empno;

oracle数据库命名规范;

group by 要和avg,sum,max,min,count等分组函数一起用;

直接 select ename,deptno from emp group by deptno 是错误的;

同时如果是分组函数;

被选的字段,要么是分组函数的计算值,要么是分组的依据;

连接查询:左连接查询,显示左边的,但是+要出现在右边;

就是需要显示另一张表中没有引用到的数据时,+就要出现在那张表上;

SELECT E.ENAME,D.DNAME    FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO AND E.JOB='CLERK';

写sql语句的时候,按照sql的解析顺序写思路会更加的清晰;

6．列出所有“CLERK”（办事员）的姓名及其部门名称。比如这个sql语句

1 首先明确有几张表,根据这个写出 from 块;

2 然后明确根据什么条件查 写出where块

3 然后明确要从符合条件的记录中取哪些列; 写出select块;

4 然后明确要对取出来的记录列是否需要进行分组;写出group by 块,注意和分组函数一起用;

5 然后明确是否要对满足条件的记录列并且分组后的数据进行进一步的过滤,写出having块;

6 最后明确是否需要对选出来的记录列进行排序,写出order by块;

不管是子查询还是什么都按照这个模式来走,肯定能走通;

实在不行就写个块来实现;

比如这里:

1 就涉及到了两张表,员工表和部门表,因为需要输出员工信息和部门名称;我们习惯给这两张表

取个别名,因此from块是这样的: from emp e,dept d;

2 条件是:由于是多表查询涉及到笛卡尔积问题和连接查询的问题,必须写一个条件去除笛卡尔积,同时加上连接查询;

  然后才是进一步的根据条件进行过滤,进而写出where块;

  where d.deptno=e.deptno(+) and e.job='CLERK';

3 看要选择满足条件的记录中的哪些列或者说字段;

  select e.ename,d.dname

4 这里没有其他的,把上面的拼接起来就是了;

select e.ename,d.dname from emp e,dept d  where d.deptno=e.deptno(+) and e.job='CLERK';

sql语句就写出来了

实际案例:

7．列出最低薪金大于1500的各种工作。

select e1.job,e1.minsal from  (select job,min(sal) as minsal from emp group by job) e1 where minsal>1500;

按照上面的思路:

首先写出from:很明显是从emp表中查,但是又不是所有的记录及字段都是我们想要的,要emp表进行过滤;

    经过分析得到:要求每个工作的最低工资;这又是一个查询,按照之前的思路,写出from emp;

    where条件:没有

    select:最低工资min(job),工作job;

    group by job;得到子查询的语句 select job,min(sal)   from emp group by job;

    将上面的结果当做一个表;即

    from (select job,min(sal) as minsal from emp group by job) e1

     where :最低薪金大于1500; where e1.minsal>1500;

     select : select e1.job,e1.minsal;

 最后得到最终的sql语句;

select e1.job,e1.minsal from (select job,min(sal) as minsal from emp group by job) e1 where e1.minsal>1500;

8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。

1 分析得到,这里涉及到两张表的字段:因此有两张表,则 from emp e,dept d;

2 多表查询去除笛卡尔积: where e.deptno=d.deptno

  其他过滤条件;dname='SALES';组合起来就是 where dname='SALES' and e.deptno=d.deptno

3 select e.ename,d.dname

组合起来就是:

select e.ename,d.dname  from emp e,dept d where dname='SALES' and e.deptno=d.deptno;

9．列出薪金高于公司平均薪金的所有员工

 1 from :只涉及到一张表: from emp

 2 where sal子查询,查询全公司的平均工资;

   select avg(sal) from emp;--组合后就是; where sal>(select avg(sal) from emp);

 3 select * :所有信息;

 组合后就是:

 select * from emp where sal>(select avg(sal) from emp);

10．列出与“SCOTT”从事相同工作的所有员工

  1 from emp;

  2 where 和scott相同,--子查询,得到scott的工作;

  select job from emp where ename='SCOTT';

    组合后就是:where job=(select job from emp where ename='SCOTT');

  3 select: select *

  组合后就是:

 select * from emp where job=(select job from emp where ename='SCOTT');

 11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金--用游标实现下;

  1 from emp:这个很简单;

  2 where sal in(30号部门的薪金)--子查询,

      首先得到30号部门的薪金;--去重复元素;

      select distinct sal from emp where deptno=30;

      组合下:

      where sal in(select distinct sal from emp where deptno=30);

  3 姓名,薪金;select ename,sal;

  组合后的最终语句;

 select ename,sal from emp where sal in(select distinct sal from emp where deptno=30);

 12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。

     在前面的基础上加上 all 或者max;

    select ename,sal from emp where sal> all(select distinct sal from emp where deptno=30);

    --使用max的效率要高点;

    select ename,sal from emp where sal> (select distinct max(sal) from emp where deptno=30);

 13．列出在每个部门工作的员工数量、平均工资和平均服务期限。

     平均服务期限:avg(当前时间-hiredate)

     from emp

     where :没有;

     select count(*),avg(sal),avg(sysdate-hiredate)

     group by deptno;

     组合后的语句为:

    select count(*),avg(sal),avg(sysdate-hiredate) from emp group by deptno;

    trunc函数的最后一个参数不指定,默认为0

 14．列出所有员工的姓名、部门名称和工资。

    这个是最基础的题目了:

     from emp e,dept d

     where :去除笛卡尔积的条件 where e.deptno=d.deptno;

     select e.ename,d.dname,e.sal

    组合到最后就是:

   select e.ename,d.dname,e.sal from emp e,dept d  where e.deptno=d.deptno;

 15．列出所有部门的详细信息和部门人数。

     from emp e,dept d;

     where :去除笛卡尔积的条件,注意连接查询where d.deptno=e.deptno(+);

             连接查询原则是,要查的信息对方表没有,在对应表的字段加+;

     select d.dname,d.loc

   这个明天再整吧;

 (select d.deptno,count(e.empno)as num from emp e,dept d where d.deptno=e.deptno(+) group by d.deptno) d2;

 取出了每个部门的人数;

 这又是一个表;

 从

 select d1.dname,d1.loc,d1.deptno,d2.num 

 from dept d1,

 (select d.deptno,count(e.empno)as num from emp e,dept d where d.deptno=e.deptno(+) group by d.deptno) d2

 where d2.deptno=d1.deptno;

  16 列出各种工作的最低工资

  select distinct job,min(sal) from emp group by job;

  17．列出各个部门的MANAGER（经理）的最低薪金。

      from emp

      where job='MANAGER';

      SELECT deptno,min(sal) as managerminsal

      group by deptno

   

   SELECT deptno,min(sal) as managerminsal from emp where job='MANAGER' group by deptno;

  18．列出所有员工的年工资,按年薪从低到高排序

     from emp

     where:没有

     select empno,ename,(sal+nvl(comm,0))*12 annlsal

     order by annlsal

     组合后就是:

    select empno,ename,(sal+nvl(comm,0))*12 annlsal from emp order by annlsal;

    模糊查询中:使用instr函数取代like查询，可提高效率，在海量数据中效果尤其明显;

statement对象可以动态创建表格;

可否就此断言我也可以造出一个hibernate出来;

无非就是读取你的配置文件中的内容,看看这个对象中有哪些字段'

哪些字段是需要对应数据库表中的字段;

解析出来就创建表格;