Oracle基础知识

01、oracle简介
数据存储量大、处理速度快、安全性高、容错性强

02、oracle安装  
安装完PL/SQL Developer在对应Tools修改配置，整合连接oracle官方的软件（instantclient_12_1）及oci.dll
查询服务器IP地址：cmd-ipconfig
oracle端口号：1521
默认数据库名称：orcl
配置对应的环境变量来指定连接oracle的信息

03、oracle体系结构
数据库：oracle数据库是数据的物理存储
实例：一个oracle实例有一系列的后台进程和内存结构组成，一个数据库可以有n个实例
用户：用户是在实例下建立的。不同实例可以建相同名字的用户（管理表的基本单位）
表空间：是oracle对物理数据库上相关数据文件（ORA或者DBF文件）的逻辑映射
数据文件：（dbf、ora）

04、创建表空间、用户及用户授权
--创建表空间
create tablespace itheima
datafile 'c:\itheima.dbf'
size 100m
autoextend on
next 10m;
--删除表空间
drop tablespace itheima;
--创建用户
create user itheima--用户名
identified by itheima--密码
default tablespace itheima;
--给用户授权
--oracle数据库常用角色
connect--连接角色，基本角色
resource--开发者角色
dba--超级管理员角色
--给itheima用户授予dba角色
grant dba to itheima;

05、数据类型和表创建
varchar、varchar2(10)--表示字符串,常用varchar2，可变长度
number(n)--表示一个整数，长度是n
number(m,n)--表示一个小数，总长度是m，小数是n，整数是m-n
data--日期类型
clob--大对象，大文本数据类型（文本）可存4G
blob--大对象，二进制数据（视频）可存4G
--创建一个person表
create table person(
    pid number(20),
    pname varchar2(10)
);

06、修改表结构
--添加一列
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;

07、数据的增删改查
--查询表中的记录
select * from person;
--添加一条记录(在数据库操作增删改需要提交事物，否则为脏数据，spring操作不需要提交，默认已添加事物)
insert into person (pid, pname) values (1, '小明');
commit;
--修改一条记录
update person set pname = '小红' where pid = 1;
commit;
--三个删除
--删除表中的全部记录
delete from person;
--删除表结构
drop table person;
--先删除表，再次创建表。效果等同于删除表中的全部记录
--在数据量大的情况下，尤其在表中带有索引的情况下，该操作效率高
--索引可以提高查询效率，但是会影响增删改的效率
truncate table person;

08、序列的使用
--序列不是真的属于任何一张表,但是可以逻辑和表做绑定。
--序列：默认1开始，依次递增，主要用来给主键赋值使用
--dual:虚表，只是为了不全语法，没有任何意义
create sequence s_person;
select s_person.currval from dual; (当前值)
select s_person.nextval from dual;（下一个值）
--添加一条带序列的记录
insert into person (pid, pname) values (s_person.nextval, '小明');
commit;

09、scott用户介绍（入门Oracle练习使用）
--scott用户，密码tiger
--解锁scott用户
alter user scott account unlock;
--解锁scott用户的密码【也可用来重置密码】
alter user scott identified by tiger;
--切换到scott用户下
Session-Log off-All；Session-Log on-xxx；然后Username=scott、Password=tiger；

10、单行函数
--单行函数：作用于一行，返回一个值
--字符函数
select upper ('yes') from dual; --YES 小写变大写
select lower ('YES') from dual; --yes 大写变小写
--数值函数
select round (26.16, 1) from dual; --26.1 四舍五入，后面的参数表示保留的位数
select trunc (26.16, -1) from dual; --20 直接截取，不在看后面位数的数字是否大于5 
select mod (10, 3) from dual; ---1 求余
--日期函数
--查询出emp表所有员工入职距离现在几天
select sysdate-e.hiredate from emp e; 
--算出明天此刻(当前时间加一天)
select sysdate+1 from dual;
--查询出emp表所有员工入职距离现在几个月
select months_between (sysdate, e.hiredate) from emp e;
--查询出emp表所有员工入职距离现在几年
select months_between (sysdate, e.hiredate)/12 from emp e;
--查询出emp表所有员工入职距离现在几周
select round((sysdate-e.firedate)/7) from emp e;
--转换函数
--日期转字符串
select to_char(sysdate, 'yyyy-mm-dd hh:mi:ss') from dual;      --2019-09-29 10:28:56
select to_char(sysdate, 'fm yyyy-mm-dd hh:mi:ss') from dual;   --2019-9-29 10:28:56
select to_char(sysdate, 'fm yyyy-mm-dd hh24:mi:ss') from dual; --2019-9-29 22:28:56
--字符串转日期
select to_char('2019-09-29 22:28:56', 'fm yyyy-mm-dd hh24:mi:ss') from dual;
--通用函数
算出emp表中所有员工的年薪
--奖金里面有null值,如果null值和任意数字做算术运算，结果都是null，可以使用nvl(xxx, 0)
select e.sal*12+nvl(e.comm, 0) from emp e;

11、条件表达式
--条件表达式的通用写法，mysql和oracle使用（推荐使用通用写法）
--给emp表中的员工起中文名
select e.ename
  case e.ename
    when  'SMITH' then '张三' 
      when 'ALLEN' then '李四'
        else '无名'
          end
from emp e;
--判断emp表员工工资，高于3000显示高收入，高于1500低于3000显示中等收入，其余显示低收入
select e.sal
  case 
    when  e.sal>3000  then '高收入' 
      when e.sal>1500 then '中等收入'
        else '低收入'
          end
from emp e;
--oracle中除了起别名（用双引号），其他都用单引号
--oracle专用条件表达式
select e.ename
  decode(e.ename,
    'SMITH', '张三' ,
      'ALLEN', '李四',
        '无名') "中文名"
from emp e;

12、多行函数
--多行函数【聚合函数】：作用于多行，返回一个值
select count(1）from emp; --查询总数量
select sum(sal）from emp; --查询工资总和
select max(sal）from emp; --查询最大工资
select min(sal）from emp; --查询最低工资
select avg(sal）from emp; --查询平均工资

13、分组查询
--查询出每个部门的平均工资
select e.deptno, avg(e.sal) from emp e group by e.deptno;
--查询平均工资高于2000的部门信息
select e.deptno, avg(e.sal) asal from emp e group by e.deptno having avg(e.sal)>2000;
select e.deptno, avg(e.sal) asal from emp e group by e.deptno having asal >2000;--错误的，所有条件都不能使用别名来判断（先执行条件在执行select...）
--查询出每个部门工资高于800的员工的平均工资
select e.deptno, avg(e.sal) asal from emp e where e.sal>800 group by e.deptno;
--where是过滤分组前的数据，having是过滤分组后的数据
--表现形式:where必须在group by之前,having是在group by之后
--查询出每个部门工资高于800的员工的平均工资
--然后再查询每个平均工资高于2000的部门
select e.deptno, avg(e.sal) asal from emp e where e.sal>800 group by e.deptno having avg(e.sal)>2000;

14、多表查询
--笛卡尔积
select * from emp e, dept d;
--等值连接
select * from emp e,dept d where e.deptno=d.deptno;
--内连接
select * from emp e inner 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 left join dept d on e.deptno=d.deptno;
--oracle中专用外连接
select * from emp e,dept d where e.deptno(+) = d.deptno;

15、自连接
--查询员工姓名，员工领导姓名
--自连接其实站在不同的角度把一张表看成多张表
select e1.ename, e2.ename
from emp e1, emp e2
where e1.mgr = e2.empno;
--查询员工姓名，员工部门名称，员工领导姓名，员工领导部门名称
select e1.ename, d1.dname, e2.ename, d2.dname
from emp e1, emp e2, dept d1, dept d2
where e1.mgr = e2.empno
and e1.deptno = d1.deptno
and e2.deptno = d2.deptno;

16、子查询
--子查询返回一个值
--查询出工资和SCOTT一样的员工信息
--当自条件查询结果不为一条则出现问题
select * from emp where sal =
(select sal from emp where ename = 'SCOTT')
--子查询返回一个集合
--查询出工资和10号部门任意员工一样的员工信息
select * from emp where sal in 
(select sal from emp where deptno = 10);
--自查询返回一张表
--查询每个部门的最低工资，和最低工资员工的姓名，和该员工所在部门名称
--1、先查询出每个部门最低工资
select deptno, min(sal) msal from emp group by deptno
2、三表联查，得到最终结果
select t.deptno, t,msal, e.ename, d.dname
from （select deptno, min(sal) msal from emp group by deptno）t,emp e, dept d
where t.deptno = e.deptno
and t.msal = e.sal
and e.deptno =d.deptno

17、分页查询
--rownum行号：每查询一行记录，就会在该行上加一个和行号，从1开始依次递增，不能跳着走
--排序操作会影响rownum的顺序
select rownum, e.* from emp e order by e.sal desc
--如果涉及排序，但是要使用rownum的话，可以使用再次嵌套查询
select rownum, t.* from(
select rownum, e.* from emp e order by e.sal desc) t;
--emp表工资倒序排列后，每页五条记录，查询第二页
--rownum行号不能写上大于一个正数
select * from(
  select rownum rn, tt.* from (
    select * from emp order by sal desc
  ) tt where rownum<11
) where rn>5
--第二种写法（局限不能做order by操作）
select * from
(select rownum rn, * from emp) b
where b.rn >5 and b.rn <11

18、视图
--概念：视图就是提供一个查询的窗口，所有数据来自于原表
--查询语句创建表
create table emp as select * from scott.emp;
--创建视图必须有dba权限
create view v_emp as select ename, job from emp;
--查询视图
select * from v_emp;
--修改视图【不推荐】
update v_emp set job='CLERK' where ename='ALLEN';
commit;
--创建只读视图
create view v_emp1 as select ename, job from emp with read only;
--视图的作用：1、可以屏蔽敏感字段2、保证总部和分部的数据及时统一（总部查表，分部查视图）

19、索引
--概念：索引就是在表的列上构建一个二叉树（相当于书的目录）
--达到大幅度的提高查询效率的目的，但是索引会影响增删改的效率
--单列索引
--创建单列索引
create index idx_ename on emp(ename);
--单列索引触发规则，条件必须是索引列的原始值
--单行函数，模糊查询，都会影响索引的触发
select * from emp where ename='SCOTT';
-复合索引
--创建复合索引
create index idx_enamejob on emp(ename, job);
--复合索引中第一列为优先检索列（ename）
--如果要触发复合索引，必须包含优先检索列中的原始值
select * from emp where ename='SCOTT' and job='xx';--触发复合索引
select * from emp where ename='SCOTT' or job='xx';--不触发索引
select * from emp where ename='SCOTT';--有单列索引和复合索引，触发单列索引

20、pl/sql编程语言
--pl/sql编程语言是对sql语言的扩展，使得sql语言具有过程化的编程特性
--pl/sql编程语言比一般的过程化编程语言更加灵活高效
--pl/sql编程语言主要用来编写存储过程和存储函数等

--声明方法
--赋值操作可以使用:=也可以使用into查询语句赋值
declare 
  i number(2) := 10;
  s varchar2(10) := '小明';
  ena emp.ename%type;--引用型变量
  emprow emp%rowtype;--记录型变量
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);--oracle数据库连接使用||
end;

21、pl/sql的if判断
--输入小于18的数字，输出未成年
--输入大于18小于40的数字，输出中年人
--输入大于40的数字，输入老年人
declare 
  i number(3) := &ii;
begin 
  if i<18 then
    dbms_output.put_line('未成年');
  elsif i<40 then
    dbms_output.put_line('中年人');
  else
    dbms_output.put_line('老年人');
  end if;
end;

22、pl/sql循环
--三种方式输出1到10是个数字
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;

23、pl/sql的游标
--游标：可以存放多个对象，多行记录
--输出emp表中所有员工的姓名
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

24、存储过程
--存储过程：就是提前已经编译好的一段pl/sql语言，放置在数据库端，可以被直接调用（pl/sql一般都是固定步骤的业务）
--给指定员工涨100块钱
create or replace procedure p1(eno emp.empno%type)
is

begin
  update emp set sal=sal+100 where empno = eno;
  commit;
end;
--测试p1
declare 
begin
  p1(7788);
end;

25、存储函数
--通过存储函数实现计算指定员工的年薪
--存储过程和存储函数的参数都不能带长度（eno empno%type、return number）
--存储函数的返回值类型不能带长度
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;

--测试f_yearsal
declare 
  s number(10);
begin
  s :=f_yearsal(7788);
  dbms_output.put_line(s);
end;

26、out类型参数使用
--使用存储过程来算年薪
create or replace procedure p_yearsal(eno emp.empno%type, yearsal 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;
  yaersal := s+c;
end;
--测试p_yearsal
declare 
  yearsal number(10);
begin
  p_yearsal(7788, yearsal);
  dbms_output.put_line(yearsal);
end;
--in和out类型参数的区别是什么？
--凡是涉及into查询语句赋值或者:=赋值操作的参数，都必须使用out来修饰

27、存储过程和存储函数的区别
--语法区别：关键字不一样
--存储含糊比存储过程多了两个return
--本质区别：存储函数有返回值，而存储过程没有返回值。
--如果存储过程想实现有返回值的业务，我们必须使用out类型的参数
--即使存储过程使用了out类型的参数，其本质也不是真的有了返回值
--在存储过程内部给out类型参数赋值，在执行完毕，我们直接拿到输出类型参数的值

--使用存储函数有返回值的特性，来自定义函数
--而存储过程不能使用自定义函数
--案例需求：查询员工姓名，员工所在部门名称
--案例准备工作：把scott用户的dept表复制到当前用户下
create table dept as select * from scott.dept;
--使用传统方式来实现案例需求
select e.ename, d.dname
from emp e, dept d
where e.deptno=d.deptno;
--使用存储函数来实现提供一个部门编号，输出一个部门名称
create or replace function fdna(dno dept.deptno%type) return dept.dname%type
is
  dna dept.dname%type;
begin
  select dname into dna from dept where deptno = dno;
  return dna;
end;
--使用fdna存储函数来实现案例需求：查询出员工姓名，员工所在部门名称
select e.ename,fdna(e.deptno) from emp e;

28、触发器
--制定一个规则，在我们做增删改操作的时候，只要满足改规则，自动触发，无需调用。
--语句级触发器：不包含for each row
--行级触发器：包含for each row的就是行级触发器
--加for each row是为了使用:old或者:new对象或者一行记录

29、触发器案例
--语句触发器
--插入一条记录，输出一个新员工入职
create or replace trigger t1
after
insert
on person
declare

begin
  dbms_output.put_line('一个新员工入职');
end;
--触发t1
insert into person values(1, '小红');
commit;

--行级触发器
--不能给员工降薪
-- 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;
--触发t2
update emp set sal=sal-1 where empno = 7788;
commit;

30、触发器实现主键自增【行级触发器】
--分析：在用户做插入操作之前，拿到即将插入的数据
--给该数据中的主键列赋值
create or replace trigger auid
before
insert
on person
for each row
declare

begin
  select s_person.nextval into :new.pid from dual;
end;
--使用auid实现主键自增
insert into person (pname) values('a');
commit;

31、java调用存储过程环境准备
--oracle10g  ojdbc14.jar
--oracle11g  ojdbc6.jar

32、环境测试
--加载数据库驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
--得到Connection连接
Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.88.6:1521:orcl", "itheima", "itheima");
--得到预编译的Statement对象
PreparedStatement pstm = connection.prepareStatement("select * from emp where empno = ?");
--给参数赋值
pstm.setObject(1, 7788);
--执行数据库查询操作
ResultSet rs = pstm.executeQuery();
--输出结果
while(rs.next()){
  System.out.println(rs.getString("ename"));
}
--释放资源
rs.close();
pstm.close();
connection.close();

33、调用存储过程和存储函数
--java调用存储过程
@Test
public void javaCallProcedure() throws Exception {
  --加载数据库驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
--得到Connection连接
Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.88.6:1521:orcl", "itheima", "itheima");
--得到预编译的Statement对象
CallableStatement pstm = connection.prepareCall("{call p_yearsal(?, ?)}");
--给参数赋值
pstm.setObject(1, 7788);
pstm.registerOutParameter(2, OracleTypes.NUMBER);
--执行数据库查询操作
pstm.execute();
--输出结果[第二个参数]
System.out.println(pstm.getObject(2));
--释放资源
pstm.close();
connection.close();
}
--java调用函数
@Test
public void javaCallFunction() throws Exception {
  --加载数据库驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
--得到Connection连接
Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.88.6:1521:orcl", "itheima", "itheima");
--得到预编译的Statement对象
CallableStatement pstm = connection.prepareCall("{?= call f_yearsal(?)}");
--给参数赋值
pstm.setObject(2, 7788);
pstm.registerOutParameter(1, OracleTypes.NUMBER);
--执行数据库查询操作
pstm.execute();
--输出结果[第一个参数]
System.out.println(pstm.getObject(1));
--释放资源
pstm.close();
connection.close();
}