Oracle基础知识总结

知识点梳理

 

数据块

• 是oracle逻辑存储结构中最小的逻辑单位。

数据区

SQL特点

• 综合统一
  • 集数据定义语言，数据操纵语言，数据控制语言
  • 可以独立完成数据库生命周期中的全部活动。
  • 用户数据投入运行后，可根据需要随时逐步修改模式，不影响数据的运行
  • 数据操作符统一
• 高度非过程化
• 面向集合的操作方式
• 以同一种语法结构提供多种使用方式
• 语言简洁易学易用

SQL语言编写规则

• 关键字不区分大小写
• 对象名列名不区分大小写
• 字符区分大小写

模式和模式对象

• 模式是一个数据库对象的集合
• 模式对象是由用户创建的逻辑结构，用以存储或引用数据

事务

• 事务是一系列的数据库操作，由一条或多条相关的SQL语句构成，是数据库应用程序的基本逻辑单位。
• 事务包含四种属性acid：原子性一致性隔离性持久性
• 事务的处理结果只有两种：全部回滚，全部提交

数据表

• 主要的数据存储容器
• 数据库最基本的单位
• 表示二维的
• 表来自现实世界，对现实需求的描述

视图

• 视图是一个虚拟表，它由存储的查询结果构成

索引

• 为了能够快速找到数据，表中的每一行均用一个索引来标识，提供快速查找记录的功能。
• B树索引
• 位图索引
• 反向键索引
• 给予函数的索引

另一种区分方法

• 唯一性索引
• 非唯一性索引

在另一种区分方法

• 单列索引
• 复合索引

注意：

• 索引建立在where频繁使用的表列
• 限制索引的数量
• 指定索引块空间的使用参数
• 将表和索引部署在相同的变空间
• 建立建立连接索引
• 不要再小表建立索引

序列

• 提供用于生成一系列唯一数字的数据库对象，序列会自动生成递增的序列号，实现自动提供唯一的主键值。

同义词

• 是表、索引、视图等模式对象的一个别名，可以隐藏对象的实际名称和所有者信息，提供一定安全性保护。

表空间和数据文件的关系

• 一个数据库包含多个表空间
• 一个表空间包含多个数据文件
• 表空间看做数据库逻辑结构
• 数据文件看做数据库物理结构

默认表空间

• example 样例数据
• sysaux system辅助空间
• system 存放数据局字典，表视图存储结构
• temp 放SQL语句和索引信息
• undotbs1 撤销表
• users 数据库对象

安全流程

• 用户向数据库提供身份识别
• 用户证明他们所给出的身份标识信息是有效的
• 确定用户权限

身份验证的三种方法

• 密码验证 最基础常用
• 外部验证
• 全局验证

权限简介

• 系统权限：在系统级对数据库进行存取和操作
• 对象权限：某一个用户对其他用户的表进行操作

角色

-是一组相关权限的命名集合，使用角色最主要的目的是简化权限管理。

 

常用指令

• 数据库的启动和关闭

• 启动

start up + nomount //启动实例不加载数据库
start up + mount //启动实例 加载数据库并保持数据库的关闭状态
start up + open //启动实例、加载并打开数据库 默认选项

• 关闭

shutdown + normal // 正常方式关闭数据库
shutdown + transactional // 在当前所有活动事务被提交完毕后关闭数据库
shutdown + immediate // 立即关闭数据库
shutdown + abort //终止方式关闭数据库

• SQL PLUS命令

• 时间

ti[me] //控制当前日期显示
timi[ng] //控制时间统计的显示

• 页面显示设置

set pagesize + number// 设置从顶部标题至页结束之间的行数
set newpage + number // 一页中空行的数量
set linesize + number // 一行中的最多字符总数

• 帮助

help + value //获得相关英文提示

• 查询指定数据对象的组成结构

desc[ribe] + 表名

• 将查询结果输出到指定文件中

spool + 路径 //默认覆盖
spool + 路径 + apend //追加
spool off //关闭

• 更改用户模式

进入 system 用户下

alter user scott(userName) identified by 123(password) account unlock

• 检索

//基础
select //选择数据表、视图的列
into //插入新表
from //指定数据源
where // 数据筛选
group by //检索结果分组显示
having //在 group by 后的筛选数据行
order by + 列名 + asc/desc 升/倒序
as //更改列名
like //字符匹配
_ 代表一个字符
% 代表任意数量字符
in //测试一个数据值是否匹配一组目标值中的一个
between + val + and + val  //包括两端的值
is NULL //是否为空

• 多表关联查询

//内连接
select table1.row1 , table2.row2
from table1 join table2
on table1.row = table2.row;

//外连接 会扩展一部分不匹配的列

from  tb1 left join tb2//左连接显示左表的值

from tb1 right join tb2//右连接显示右表的值
啥连接啥边的表全显示

full join//完全外连接 执行一个完整的左外连接和右外连接查询，然后将查询结果合并消除重复行

join //自然连接 自动匹配

//自连接 记住设别名

• 插入删除更新

insert into tb1
values();


update tb1
set 更新操作
where 条件


delete from 表
where 条件

• PL/SQL编程

delcare

方式一 名字+类型
方式二 名字+表列名%type   名字+表%rowtype
方式三 type 结构体名字 is record (
        方式一,方式二
        ); 
        声明结构体名字  结构体名字    
    
begin
    select 列名
    into 刚才声明的变量
    from 表
    where 条件
    dbms_output.put_line(''||'');//输出语句
    
    
    //选择语句
    if 条件 then
    操作
    end if;

    if 条件 then
    操作
    else
    操作
    end if;

    if 条件 then
    操作
    elsif 条件 then
    操作
    else
    操作
    end if;
    
    
    case 变量
    when 条件 then
    when 条件 then
    else
    end case;
    
    
    
    //循环语句
    //loop
    loop
    操作
    exit when 条件;
    end loop;
    
    
    //while
    while 终止条件 loop
        操作
    end loop;
    
    
    //for
    for 变量 in 初始值..结束值 loop
    操作
    end loop;
end;
/




//游标

declare
    cursor 游标名
    is select 列名
    from 表
    where 条件
begin
    open 有标名
    fetch 有标名 into 声明的变量
    while 有标名%found loop
    %rowcount 影响的行数
    %isopen 是否打开
    操作
    fetch 有标名 into 声明的变量
    end loop;
    


    for 原始变量 in 声明变量 loop
    操作
    end loop;
    
exception
    when  then
    操作
end;
/

• 存储过程

//声明过程
create or replace procedure procedure_name(
    param_name1 in/out param_type1,
    param_name1 in/out param_type1,
    param_name1 in/out param_type1
)
is/as
begin
sql语句
除select都需要commit语句
exception
	when no_data_found then sql语句
end procedure_name;
/

//调用过程
set serveroutput on;
    variable  paran_name1 param_type1;
    exce procedure_name(param_name1,:param_name2);
    print param_name2;


set serveroutput on;
declare
    paran_name1 param_type1;
    paran_name2 param_type2;
begin
    procedure_name(param_name1,:param_name2);
    dbms_output.put_line();
end;
/

drop procedure procedure_name;

• 函数

    create or replace function function_name (
        param_name1 param_type1,
        param_name2 param_type2
    )return param_type is
        param_name param_type;
    begin
        sql语句
        return param_name;
    exception
	    when no_data_found then 
	    return param_name;
	end;
	/
	
	
	//调用
    set serveroutput on;
    declare
        paran_name1 param_type1;
        paran_name2 param_type2;
    begin
        param_name1  :=  function_name(param_name1,:param_name2);
        dbms_output.put_line();
    end;
    /
    
    drop function function_name;

• 表语句

    create table table_name(
        param_name param_type param_constraint,
    
    )tablespace users;



//增删改查字段
    alter table table_name add(param_name param_type param_con);
    
    alter table table_name drop column param_name;

    alter table table_name drop (param_name1,param_name1);
    
    //改表字段
    alter table table_name modify  param_name param_type;
    
    //改名字
    alter table table_old_name rename to table_new_name;
    
    //改表空间
    alter table table_name move tablespace tablespace_name;
    
    //删除表
    drop table table_name cascade constraints;
    
    //改变表的状态
    alter table table_name read only(read write);
    
    //约束
    not null 非空约束
    primary key 主键约束
    unique 唯一性约束
    foreign key 外键约束
    
    //禁用约束
    alter table tablename disable constraint constraint_name;
    //删除
    alter table tablename drop constraint constraint_name;

• 视图

    create or replace view view_name as
    select param_name
    from table_name
    where condition
    with read only；只读
    
    //查看视图代码
    select text from user_views;
    
    //替换
    or replace
    //删除
    drop view view_name;
    
    

• 索引

b树索引
create index index_name on table_name(param_name)


位图索引
create bitmap index index_name on table_name(param_name)


删除索引
drop index index_name

查询索引
select index_name.index_type
from dba_indexes
where owner = ''

• 序列

create sequence sequence_name 
maxvalue 9999
start with 1
increment by 10
cache 5

调用
select sequence_name.nextval from dual;

修改
alter sequence sequence_name
删除
drop sequence sequence_name

• 创建表空间

create  (smallfile/bigfile) tablespace tablespace_name
datafile tablespace_path
size num (k/m) reuse
autoextend on/off next num(k/m)
maxsize  unlimited/num (k/m)
permanent/temporary //永久临时 默认永久
logging/nologging
extend management local/dictionary  autoallocate/uniform size //指定表空间扩展方式本地还是字典  是自动指定还是等同大小1m

//维护表空间
alter database default tempary tablespace tablespace_name
alter database default tablespace tablespace_name
alter tablespace tablespace_name read only/read write
alter tablespace tablespace_oldname rename to tablespace_newname

drop tablespace tablespace_name including contents cascade constraints;

alter tablespace tablespace_name add datafile datafile_path 
    size autoextend next extend management local/dictionary  autoallocate/uniform size
    
alter tavlespace tablesoace_name drop datafile datafile_name

select *  from dba_data_file

• 用户

create user user_name identified by pass_word
default tablespace tablespace_name //默认user表，不是system表
temporary tablespace temp_tablespace_name
quota num(k/m) on tablespace_name // quota 0 on tablespace_name 禁止用户使用某个表
account unlock/lock
alter user user_name identified by / quota on  /account unlock
drop user user_name  cascade

• 权限

grant  sys_power /roles_power to user/role/public(代表系统所有用户)with admin option


revoke  sys_power /roles_power from user/role/public(代表系统所有用户)

//注 系统权限传递不受影响


grant select,update,delete,insert on scott.emp to user_name

revoke something on something from user_name
//注：用户权限传递受影响

select * from dba_role_privs

• 角色

create role