Oracle学习总结

1、基本概念

Oracle数据库： 是一个文件系统，是物理概念
实例： 在Oracle的数据库中可有多个实例，通常我们只用一个实例（orcl）
用户： 一个实例下有多个用户
表空间： 一个实例下有多个表空间，表空间是逻辑概念，一个表空间对应一个或者多个物理存储文件（.dbf、.ora）
用户和表空间的关系： 一个用户有一个默认的表空间，一个表空间可以为多个用户作为默认表空间，用户和用户之间的数据是隔离的，数据不会混

Oracle中用户的概念相当于MySQL中database的概念，另外我认为Oracle是由Java编写的，很多思想和Java的编程思想差不多。

2、命令行常见操作

（1）登陆
       sqlplus 用户名/密码 【as sysdba】

（2）查看当前数据库用户
       show user

（3）用户的切换
       conn 用户名/密码 【as sysdba】

（4）查看用户下所有的表
       select * from tab;

（5）查看用户下所有的表
       select * from tab;

（6）在sys用户查询scott用户下的dpet表（sys的权限必须很大才能跨用户查询）
       select * from scott.dpet;

3、常见操作

   很多用法和MySQL差不多

（1）数字与日期函数
       select round(12.789, 2) from dual; --四舍五入取小数点后面两位
       select trunc(12.789, 2) from dual; --取小数点后面2位
       select mod(10, 3) from dual; --取余数

       select ename, trunc((sysdate - hiredate) / 7) from emp; --现在到入职的周数
       select ename, trunc(months_between(sysdate, hiredate)) from emp; --现在到入职的月数

（2）转换函数
       select to_char(sysdate, ‘yyyy-mm-dd HH24:mi:ss’) from dual; --时间转换
       select to_char(sysdate, ‘fmyyyy-mm-dd’) from dual; --时间转换,去掉前导零
       select to_date(‘1994-01-09’, ‘yyyy-mm-dd’) from dual;–字符串转时间

       select to_char(sal, ‘$99,999’) from emp; --数字格式化

（3）通用函数
       select ename, sal*12+ nvl(comm, 0) from emp; --nvl处理null值

       select ename, decode(job, ‘ANALYST’, ‘分析员’,
                                                   ‘MANAGER’, ‘管理员’,
                                                   ‘其他人员’)
                       from emp; --类似于java中的switch…case…，前三个参数是必须的

       select ename, case when job = ‘ANALYST’ then ‘分析员’
                                        when job = ‘MANAGER’ then ‘管理员’
                                        else ‘其他人员’
                               end
                        from emp; --和decode功能差不多

（4）外连接（左右连接）
     两张表做连接查询时其中一张表要查询全量数据（不会因为另一张表的数据的关联而筛选掉）
     在两张表关联的时候，非全量表的字段后面加上 (+) 就可以做外连接查询
        select * from dept d, emp e where d.deptno = e.deptno(+); --全量表在左端，叫左连接，反之亦然

    --表连接（起别名不加引号或者用双引号）
    select e1.ename as 职员, e2.ename as 上级, e1.sal as 薪资, s.grade as 薪资 from     dept d, emp e1, emp e2, salgrade s
        where e1.deptno = d.deptno(+)
            and e1.mgr = e2.empno(+)
            and e1.sal between s.losal and s.hisal;

    select * from emp e join dept d on e.deptno = d.deptno;

     – sql1999的外连接查询（重点）
        select * from emp e left join dept d on e.deptno = d.deptno;
        select * from emp e right join dept d on e.deptno = d.deptno;
        select * from emp e inner join dept d on e.deptno = d.deptno;

（3）分组
     select job, count(*) c
     from emp
     where ename <> null
     group by job
     having c > 3
     order by c desc

（5）子查询
     in 的执行效率比较低，可以用 exists() 代替，exists子查询一般要和外侧查询关联的
     select * from dept t where exists (select * from emp e where e.deptno = t.deptno); --查询有员工的部门

（6）union 与 union all
     union: 两个集合合并时会去重
     union: 把两个集合做并集时不会去重

4、表空间、用户、表的操作

–1、创建临时表空间，默认是temp
create temporary tablespace JASPER_TEMP
tempfile ‘C:\app\Administrator\xcm\JASPER_TEMP.DBF’
size 100m
autoextend on
next 10m;

–2、创建数据表空间，默认是system
create tablespace JASPER_DATA
logging
datafile ‘C:\app\Administrator\xcm\JASPER_DATA.DBF’
size 100m
autoextend on
next 10m;

–3、创建用户
create user jasper
identified by 123456
default tablespace JASPER_DATA
temporary tablespace JASPER_TEMP;

–4、给用户授权
----oracle数据库常用角色
----connect 连接角色，基本角色
----resource 卡法角色
----dba 超级管理员角色
grant dba to jasper; --给jasper用户授予超级管理员角色

–5、删除表空间和用户
----删除表空间[表空间segments, 文件datafiles, 与该空间完整的约束条件]
drop tablespace JASPER including 【contents】 and 【datafiles】 【cascade constraint】】;
drop tablespace JASPER_DATA including contents and datafiles;

----删除用户 [cascade表示该用户下的所有对象（如table,view等等）]
drop user jasper 【cascade】;

–6、表的相关操作
----创建表
create table person(
pid number(20),
pname varchar2(10)
);
----修改表结构
alter table person add (gender number(1)); --添加一列
alter table person modify gender char(1); --修改列类型
alter table person rename column gender to sex; --修改列名称
alter table person drop column sex; --删除一列

–三个删除
----删除表中的全部数据
drop from person;
----删除表结构
delete table person;
----先删除表，再创建表。效果等同于删除表中的全部记录
----在数据量大的情况下，尤其是带有索引的情况下，该操作效率高
----索引可以提供查询效率，但是会影响增删改效率
truncate table person;

–创建序列(两个方法：nextval, currval)
create sequence s_person;

–解锁scott用户
alter user scott account unlock;
–解锁scott用户的密码【也可以用来重置密码】
alter user scott identified by tiger;

5、分页查询

–oracle中的分页
—rownum行号：在做查询操作的时候，每查询出一条记录就会加上一个行号
—行号从一开始，依次递增，不能跳着走
–每页5条记录，查询第二页
select * from (
        select rownum rn, tt.* from (
                 select * from emp order by sal desc
        ) tt where rownum < 11
) where rn > 5

6、视图

–创建表
create table emp as select * from scott.emp

–视图的概念：视图就是提供一个查询的窗口
–创建视图【必须有dba权限】，这里创建的是只读视图
create view v_emp as select ename, job from emp with read only;
–查询视图
select * from v_emp;
–视图的作用？
—第一：屏蔽掉一些敏感字段
—第二：保证总部和分部的数据及时统一

7、索引

----单列索引
----单列索引的触发规则，条件必须索引列中的原始值，单行函数，模糊查询，都会影响索引的触发
create index idx_ename on emp(ename);

----复合索引
----符合索引的触发规则，符合索引的第一列为优先触发列，如果要触发复合索引，必须包含优先检索列中的原始值
create index idx_enamejob on emp(ename, job);
select * from emp where ename=“SCOTT” and job=“xxx”; --触发复合索引
select * from emp where ename=“SCOTT” or job=“xxx”; --不触发索引，or相当于两个查询的并集
select * from emp where ename=“SCOTT”; --触发单行索引

8、pl/sql编程

      pl/sql语言是对sql语言的扩展，使得sql语言具有过程化编程的特性，一般用来编写存储函数和存储过程
（1）基本语法
赋值

--声明方法
----赋值操作
declare
  i number(2) := 10;
  s varchar2(10) := 'Jasper';
  ena emp.ename%type; --引用型变量（引用emp表中ename字段类型作为变量类型）
  emprow emp%rowtype; --记录型变量（将emp表的记录作为变量类型（有点像Java中的Bean））
begin
  dbms_output.put_line(i);
  dbms_output.put_line(s);
  select ename into ena from emp where empno='7788';
  dbms_output.put_line(ena);
  select * into emprow from emp where empno='7788';
  dbms_output.put_line(emprow.ename||'的工作：'||emprow.job);
end;

判断

----if判断
declare
  i number(3) := ⅈ
begin
  if i<18 then
    dbms_output.put_line('未成年');
  elsif i<48 then
    dbms_output.put_line('年轻人');
  else
    dbms_output.put_line('老年人');
  end if;
end;

循环

----pl/sql中的loop循环
----while循环
declare
  i number(2) := 1;
begin
  while i<11 loop
    dbms_output.put_line(i);
    i := i+1;
  end loop;
end;

----exit循环
declare
  i number(2) := 1;
begin
  loop
    exit when i>10;
      dbms_output.put_line(i);
      i := i+1;
  end loop;
end;

----for循环
declare

begin
  for i in 1..10 loop
     dbms_output.put_line(i); 
  end loop;
end;

（2）游标

--游标：可以存放多个对象，多行记录
declare
  cursor c1 is select * from emp;
  emprow emp%rowtype;
begin
  open c1;
    loop
      fetch c1 into emprow;
      exit when c1%notfound;
      dbms_output.put_line(emprow.ename);
    end loop;
  close c1;
end;


declare
  cursor c2(eno emp.deptno%type) is select empno from emp where deptno=eno;
  en emp.empno%type;
begin
  open c2(10);
    loop
      fetch c2 into en;
      exit when c2%notfound;
      update emp set sal=sal+100 where empno=en;
      commit;
    end loop;
  close c2;
end;

select * from emp where deptno=10;

（3）存储过程与存储函数
存储过程
----存储过程和存储函数的参数都不能带长度
----存储函数的返回值类型不能带长度

--存储过程：一段已经提前编译好的pl/sql语言，放置在数据库，可以被直接调用
---一般都是固定的业务步骤
create [or replace] procedure 过程名(参数名 in/out 数据类型)
as
begin
  PLSQL子程序体；
end;

--创建存储过程
create or replace procedure p_yearsal(eno emp.empno%type)
is
begin
  update emp set sal=sal+100 where empno=eno;
  commit;
end;

select * from emp where empno=7788;

--调用存储函数
declare
begin
  p_yearsal(7788);
end;

------------------------------------------------------------------------

--计算年薪
create or replace procedure p_yearsal(eno emp.empno%type, yealsal out number)
is
  s number(10);
  c emp.comm%type;
begin
  select sal*12, nvl(comm, 0) into s, c from emp where empno=eno;  
  yealsal := s+c;
end;

--调用存储函数
declare
  yearsal number(10);
begin
  p_yearsal(7788, yearsal);
  dbms_output.put_line(yearsal);
end;

存储函数

--函数
----存储过程和存储函数的参数都不能带长度
----存储函数的返回值类型不能带长度
create [or replace] function 函数名(参数名 in/out 数据类型)return 数据类型
is
  结果变量 数据类型;
begin
  PLSQL子程序体；
end;

--计算年薪
create or replace function f_yearsal(eno emp.empno%type) return number
is
  s number(10);
begin
  select sal*12+nvl(comm, 0) into s from emp where empno= eno;
  return s;
end;

--调用函数
declare
  s number(10);
begin
  s := f_yearsal(7788);
  dbms_output.put_line(s);
end;

存储函数与存储函数的本质区别：
存储函数有返回值；存储过程没有返回值，只能通过out返回值

in 和 out 的区别：
凡是涉及到 into 查询语句赋值或者 := 赋值操作的参数，都必须用out 修饰；in一般可以省略不写。

（4）触发器
      触发器就是创建一个规则，在我们做增删改操作的时候，只要满足改规则，自动触发，无需调用

分类：
–语句级触发器：
–行级触发器：包含for each row，加 for each row是为了使用 :old 或者 :new 对象或者一行记录。

--插入一条记录，打印
create or replace trigger t1
after
insert
on person
declare
begin
  dmns_output.put_line('欢迎一个新员工入职');
end;

--行级别的触发器
----raise_application_error(-20001到-20999之间， ‘错误提示信息’)
create or replace trigger t2
before
update
on emp
for each row
declare

begin
  if :old.sal > :new.sal then
    raise_application_error(-20001, '不能给员工降薪');
  end if;
end;

--使用触发器实现主键自增
--在插入之前操作之前拿到数据，再给该数据中的主键赋值