Oracle

1.oracle数据库的体系结构

1.1数据库

1.2实例

1.3用户

1.4表空间

1.5数据文件

2.创建表空间

 

-- 创建表空间
create tablespace itheima
datafile 'e:itheima.dbf'
size 100m   -- 后定义的是表空间的初始大小
autoextend on   --自动增长 ，当表空间存储都占满时，自动增长
next 10m;  --后指定的是一次自动增长的大小

-- 删除表空间
drop tablespace itheima;

3.用户

3.1创建用户

-- 创建用户
create user itheima
identified by itheima
default tablespace itheima;

3.2用户授权

-- 给用户授权
-- oracle数据库中常用授权
connect -- 连接角色，基本角色
resource -- 开发者角色
dba -- 超级管理员角色
-- 给itheima用户授予dba角色
grant dba to itheima;

 

 

4.oracle数据类型

5.表的管理

5.1建表

-- 创建一个person表
create table person(
       pid number(20),
       pname varchar2(10)
);

5.2修改表结构

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

5.3数据的增删改查

---- 查看表中记录
select * from person;
---- 添加一条记录
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;

6.序列

-- 序列不真的属于任何一张表，但是可以逻辑和表做绑定
-- 序列：默认从1开始，依次递增，主要用来给主键赋值使用
-- dual：虚表，只是为了补全语法，没有任何意义。
create sequence s_person;
select s_person.nextval from dual;
select s_person.currval from dual;

insert into person(pid,pname) values (s_person.nextval,'小明');

7.单行函数

-- 单行函数
---- 字符函数
select upper('yes') from dual; -- YES
select lower('YES') from dual; -- yes
---- 数值函数
select round(26.18,1) from dual; -- 26.2,四舍五入
select trunc(26.18,1) from dual; -- 26.1直接截取
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.hiredate)/7 ) from emp  e;

-- 转换函数
---- 日期转字符串
select to_char(sysdate, 'yyyy-mm-dd hh:mi:ss') from dual;--2020-04-05 11:58:20
select to_char(sysdate, 'fm yyyy-mm-dd hh:mi:ss') from dual; -- 2020-4-5 11:58:48
select to_char(sysdate, 'fm yyyy-mm-dd hh24:mi:ss') from dual; --2020-4-5 11:59:15,24小时制
---- 字符串转日期
select to_date('2020-4-5 11:59:15' , 'fm yyyy-mm-dd hh24:mi:ss') from dual; --2020/4/5 11:59:15日期类型

--通用函数
----算出emp表中所有员工的年薪
----奖金里面有null值
----如果null值和任意数字做运算，结果都是null
select  e.sal*12+nvl(e.comm,0) from emp e; -- nvl(a,b)如果a是null，则取b的值；否则，取a的值

8.条件表达式

--条件表达式
----条件表达式的通用写法，mysql和oracle通用
----给emp表中员工起别名
select e.ename,
       case e.ename
            when 'SMITH' then '小s'
                 when 'ALLEN' then '小a'
                      when 'WARD' then   '小w'
                           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',  '小s',
                  'ALLEN' , '小a',
                       'WARD' ,   '小w',
                            '无名' ) 中文名 -- 中文名3个字可以不加引号，或者加双引号
from emp e;

9.分组查询

---- 分组查询
----查询出每个部门的平均工资
----分组查询中出现在group by后面的原始列，才能出现在select后面；
----没有出现在group by后面的列，想在select后面，必须加上聚合函数。
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;
--having asal > 2000; 所有查询条件都不能使用别名来判断
----查询出每个部门高于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;

10.多表查询

----等值连接
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;
----查询所有员工信息，以及员工所属部门
select * 
from emp e , dept d
where e.deptno = d.deptno(+);

11.自连接

----等值连接
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;
----查询所有员工信息，以及员工所属部门
select * 
from emp e , dept d
where e.deptno = d.deptno(+);

select * from emp;
----查询出员工姓名，员工领导姓名
----自连接：自连接其实就是站在不同的角度，把一张表看成多张表
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;

12.子查询

----子查询返回一个值
--------查询出工资和SCOTT一样的员工信息
select * from emp where sal in
(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;

13.分页查询

---oracle中的分页
---rownum行号：当我们做select操作的时候
---每查询出一行记录，就会在该行上加一个行号
---行号从1开始，依次递增，不能跳着走
----排序操作会影响rownum的顺序
select * from emp e order by e.sal desc
---emp表工资倒叙排列后，每页5条记录，查询第二页
select * from(
select rownum rn, e.* from( 
               select * from emp order by sal desc
               ) e where rownum < 6 
) where rn >3;

14.plsql编程语言定义变量

----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);
end;

15.plsql中的if判断

----pl/sql中的if判断
----输入小于18的数字，输出未成年
----输入大于18小于40的数字，输出中年人
----输入大于40的数字，输出老年人
declare
      i number(3):=ⅈ----输入数字
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;

 

16.pl/sql中的循环

----pl/sql中的循环
--用三种方式输出1到10
--1.while循环
declare
      i number(2) := 1;
begin
      while i<11 loop
            dbms_output.put_line(i);
            i := i+1;
      end loop;
end;
--2.exit循环
declare
      i number(2) :=1;
begin
  loop
    exit when i>10;
     dbms_output.put_line(i);
     i := i+1;
  end loop;
end;
----3.for循环
declare

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

17.plsql中的游标

----游标：可以存放多个对象，多行记录。
-----输出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;
----查询10号部门员工信息
select * from emp where deptno = 10;

18.存储过程

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

select * from emp where empno=7788;
----测试p1
declare

begin
  p1(7788);
end;

19.存储函数

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

20.out类型参数如何使用

-----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;
       yearsal := s+c;
end;

----测试p_yearsal
declare
       yearsal number(10);
begin
       p_yearsal(7788, yearsal);
       dbms_output.put_line(yearsal);
end;

----in和out类型参数的区别是什么？
----凡是涉及到 into查询语句赋值或者:=赋值操作的参数，都必须使用out来修饰。

21.存储过程和存储函数的区别

----存储过程和存储函数的区别
--------语法区别：关键字不一样
------------存储函数比存储过程多了两个return
--------本质区别：存储函数有返回值，而存储过程没有返回值
------------即便是存储过程使用了out类型的参数，其本质也不是真的有了返回值，
------------而是在存储过程内部给out类型参数赋值，在执行完毕后，我们直接拿到输出类型参数的值

----我们可以使用存储函数有返回值的特性，来自定义函数。
----而存储过程不能用来自定义函数。
--------案例需求：查询出员工姓名，员工所在部门的名称。
----使用传统方式来实现案例需求
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;

 

22.触发器的概念和分类

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

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

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

select * from person;

插入一条记录之后，控制台将输出

-----行级触发器
-----不能给员工降薪
------  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;

select * from emp where empno = 7788;

更新员工的工资后将抛出异常提示

23.触发器实现主键自增

------触发器实现主键自增
--------分析：在用户插入操作之前，拿到即将插入的数据，给该数据中的主键列赋值。
create sequence s_person;----创建一个序列
create or replace trigger auid
before
insert
on person
for each row
declare

begin
    select s_person.nextval into :new.pid  from dual;
end;

select * from person;

-----使用auid实现主键自增
insert into person (pname) values('a');
commit;
insert into person (pname) values('b');
commit;

24.java连接oracle

oracle10g-----------------ojdbc14.jar

oracle11g-----------------ojdbc6.jar

package oracle_learning;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class TestDemo {
    public static void main(String[] args) throws Exception {
        //加载数据库驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //得到Connection连接
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "tiger");
        //得到预编译的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();
    }
}

25.java调用存储过程

package oracle_learning;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import oracle.jdbc.OracleTypes;

/**
 * java调用存储过程
 * {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}   调用存储函数使用
 * {call <procedure-name>[(<arg1>,<arg2>, ...)]}     调用存储过程使用
 */
public class JavaCallProcedure {
    public static void main(String[] args) throws Exception {
        //加载数据库驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //得到Connection连接
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "tiger");
        //得到预编译的Statement对象
        CallableStatement cstm = connection.prepareCall("{call p_yearsal(?, ?)}");
        //给参数赋值
        cstm.setObject(1, 7788);
        cstm.registerOutParameter(2, OracleTypes.NUMBER);
        //执行数据库查询操作
        cstm.execute();
        //输出结果[第二个参数]
        System.out.println(cstm.getObject(2));
        cstm.close();
        connection.close();
    }
}

26.java调用存储函数

package oracle_learning;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import oracle.jdbc.OracleTypes;

/**
 * java调用存储函数
 * {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}   调用存储函数使用
 * {call <procedure-name>[(<arg1>,<arg2>, ...)]}     调用存储过程使用
 */
public class JavaCallFunction {
    public static void main(String[] args) throws Exception {
        //加载数据库驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //得到Connection连接
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "tiger");
        //得到预编译的Statement对象
        CallableStatement cstm = connection.prepareCall("{?= call f_yearsal(?)}");
        //给参数赋值
        cstm.setObject(2, 7788);
        cstm.registerOutParameter(1, OracleTypes.NUMBER);
        //执行数据库查询操作
        cstm.execute();
        //输出结果[第二个参数]
        System.out.println(cstm.getObject(1));
        cstm.close();
        connection.close();
    }
}