Oracle数据库

结构

数据库

和其他数据库不一样，这里的数据库是一个操作系统只有一个库，可以看作Oracle就只有一个大数据库

实例

一个Oracle实例有一系列的后台进程和内存结构组成，一个数据库可以有n个实例。其实也是数据库不过是软件上的

用户

用户是在实例下建立的不同实例可以有同名用户。

注：表是由用户管理的，这与Mysql数据库不同。

表空间

对数据库进行了逻辑的划分

创建表空间

create tablespace itheima
//保存路径
datafile '路径'
//容量
size 100m //容量为100m
//内存不够时自动扩展
autoextend on
next 10m; //每次扩展10m

//删除表空间
drop tablespace itheima

创建用户

create user itheima(用户名)
identifie by itheima(密码)
//默认表空间
default tablespace ithima（表空间名）;

oracle数据库常用角色
connect //连接角色，基本jues 没这个角色将无法使用

resource //开发角色

dba //超级管理员

//给用户授予角色
grant dba to itheima

数据类型

varchaar 和 varchar2 表示一个字符串 ，更常用varchar2

number（n）表示一个整数长度为n

number（m，n）表示一个小数，总长度为m，小数是n整数时m-n

data 表示日期

clob大对象 表示大文本数据类型可存4g

blob 大对象，表示二进制数据可存4g

表的创建和修改

创建表

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;

增删改

Oracle的数据库的增删改必须进行commit

增和改于MySQL一样
但删除有所不同

//删除全部记录
delete from person;

//删除表结构
drop table person;

//先删除表，在创建表。效果等同于删除表中全部记录
对于有大量数据的表，采取此方法会更快
truncate table person;

序列

默认从1开始，一次递增，主要用来给主键赋值使用。

创建序列

create sequence s_person（名称）；

方法

s_person.currval 查看当前索引

s_person.nextval 获得下一个索引

Oracle的查询必须需要表就是from后必须加一个表不能省略。但没有表时可以使用dual作为虚拟表使用。

dual只是补全语法的，没实际意义

scott用户

是Oracle自带的用户，用来给用户学习

解锁scott

必须有超级管理员

alter user scott account unlock;

解锁密码

alter user scott identified by tiger(可以修改别的密码)

函数

单行函数

字符函数

upper()  //大写
lower() //小写

数值函数

round('',m) //四舍五入 m表示保留几位，负数为往前保留 如-1为保留10位数。

trunc('',m)//直接截取，不看后边的数字

mod('',m) //求余数

日期函数

sysdate-表的日期//距离现在多少天

sysdate+1 //明天此刻

months_between(sysdate,表的日期)//距离现在几月

months_between(sysdate,表的日期)/12 //距离现在几年

(sysdate-表的日期)/7 //距离现在几周

转换函数

to_char(sysdate,'yyyy-mm-dd hh:mi:ss')//日期转字符串

to_char('yyyy-mm-dd hh:mi:ss',sysdate)//字符串转日期

通用函数

nvl('',m)//如果值位null则替换位m

条件表达式

从查找出的结果进行判断

select
    e.ename
    case e.ename
        when 'SMITH' then '曹贼'
        when 'ALLEN' then '大耳贼'
        else '无名'
        end
from 
    emp e;
    

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qADTgTd5-1595168191213)(8A35A4F79C0944A18A5E9C7A86D96F4D)]

select
    e.sal
    case 
    when e.sal > 3000 then '高收入'
    when e.sal > 1500 then '中等收入'
    else '低收入'
    end
from
emp e；
    

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TwS7ixSu-1595168191218)(BA4D6D817CAF4493B0F6E08E5EF04E2B)]

oracle专用表达式

select
    e.ename
    decode( e.ename,
         'SMITH' , '曹贼',
         'ALLEN' , '大耳贼',
         '无名')"别名"
        
from 
    emp e;

多行函数

count()//查询总数
sum()//总和
max()//最大值
min()//最小值
avg()//平均

分组查询

分组查询中，出现在group by后面的原始列，才能出现在select后面。没有出现在group by后面的列，想在select后面，必须加上聚合函数

所有条件都不能使用别名来判断

group by 列明 //进行分组

where是过滤分组前的数据，having是过滤分组后的数据

多表查询

Oracle专用用法

select
    *
form
    emp e ,dept d
where
    e.deptno = d.petno(+)

当哪个表用(+)表示时它的令一个表的数据会全部显示，上边的语句等同于e表和d表进行左连接，e表的数据全部显示

分页查询

rownum 是行号，在查询时加上可以显示行号，但进行排序时行号会错乱。

可以先将查询结果当作子表，在进行输出可以解决

select 
    rownum, t.*
from
    (select
        rownum, e.*
     from 
        emp e
    order by
        e.asl desc
    ) t;

可以使用rownum作为分页查询

但rownum不能写大于一个正数

 select * from(
    select rownum rn, e.* from(
        select * from emp order by sal desc
    )e where rownum < 11
 )where rn > 5
 
 将 rownum起个别名可以实现让rownum大于一个正数

视图

视图提供一个查询的窗口，所有数据来源原表，创建视图必须有dba权限

create table emp as select * from scott.emp;

//创建视图
create view v_emp as select ename, jb from emp
//创建只读视图
create view v_emp as select ename, jb from emp
with read only;

视图作用

1. 屏蔽一些敏感字段
2. 保证总部和分组数据及时统一。

索引

在表的列上构建一个二叉树，大幅度提高查询效率的目的，但是索引会影响增删改效率。

单列索引

触发规则，条件必须是索引中的原始值，单行函数，模糊查询，都会影响索引的触发

create index idx_ename on emp(ename);

//会触发单列索引
select * from emp where ename = 'SCOTT'

复合索引

复合索引中第一列位优先检索列。如果要触发复合索引，必须包含有优先检索列中的原始值

create index idx_enamejob on emp(ename,job);

//会触发复合索引
select * from emp where ename = 'SCOTT' and job='xx'

//不触发
select * from emp where ename = 'SCOTT' or job='xx'

//会触发单列索引
select * from emp where ename = 'SCOTT'

pl/sql编程语言

是对sql语言的扩展，使得sql语言具有过程化编程的特性。主要用来编写存储过程和存储函数等

声明方法

declare
    i number(2) := 10;
    s varchar2(10) := 'xiaom';
    //引用型变量
    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;
    select * me into emprow from emp where empno = 7788;
end;

if判断

declare
    i number(3) := ⅈ
begin
    if i<18 then
        dbms_output.put_line('未成年');
    elseif i<40 then
        dbms_output.put_line('中年');
    else
        dbms_output.put_line('老年');
    end if;
end

循环

//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

游标

类似Java的集合，可以放多个对象，多行记录

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);
        end loop;
    close c1;

存储过程

存储过程就是提前编译好的一段pl/sql语言，放置在数据库可以直接被调用，一般是固定步骤的业务。

create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]
AS | is
begin
    
end;

in和out，凡是涉及into查询语句赋值或者：=赋值操作的参数都必须使用out来修饰，其余都用in。不写默认为in

or repalce 代表可以直接修改
例如: 
create or replace procedure pl(eno emp.empno%type)
is

begin
    update emp set sal = sal+100 where empno = eno;
    commit;
end;

存储函数

存储过程和存储函数参数都不能带长度

存储函数的返回值类型不能带长度

create [or replace] FUNCTION 函数名(参数名  数据类型，参数名  数据类型)return 数据类型
AS | is
结果变量 数据类型；
begin
    
    return（结果变量）
end;



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

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

1.存储函数有返回值，而存储过程没有返回值。而如果存储过程想实现返回值，就必须使用out类型参数。即使是存储过程使用了out类型参数，本质也不是真的有了返回值，而是存储过程内部给out类型参数赋值，在执行完毕后直接拿去

触发器

触发器是制定一个规则，在我们做增删改操作时会自动触发，查询不会触发

触发器有两类，语句级触发器，行级触发器。当包含for each row就是行级触发器

语句级触发器

create or replace trigger t1
after
insert
on person
declare

begin
    dbms_output.put_line('入职')
end;

行级触发器

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

给主键列赋值

create or replace trigger auid
before
insert
on person
for each row
daclare

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