Oracle学习笔记(三)

一, Oracle的体系结构

• 数据库：只有一个数据库
• 实例：后台运行一个进程，一个数据库可以对应多个实例
  
  • 一个数据库对应多个实例是什么意思?
    
    • 1、oracle数据库包括实例和数据库
    • 2、实例由内存和一组后台进程组成
    • 3、数据库包括各种物理文件
    • 4、多实例是一种集群应用，多个实例共享访问同一数据库
• 表空间：逻辑存储单位 (书)
• 数据文件：物理存储单位，体现dbf文件 (纸)
• 用户：面向用户管理，用户管理表空间，向表空间添加数据，最终存储到数据文件中

二, SQL语句的分类

• DDL: 数据库定义语言：create drop , alter
• DML: 数据库操作语言： insert update ,delete
• DCL: 数据库控制语言：grant ，revoke
• DQL: 数据库查询语言：
  
  • select
  • insert into 表名 values();
  • insert into 表名(列名,…) values(值,…)
  • update 表名 set 列= 值, 列=值 where 条件
  • delete from 表 where 条件
  • truncate table 表名; – 删除所有的记录（效率极高），摧毁表结构，重新建表

三，管理表的DDL语句

1. 表空间

   /*
   表空间创建语法
    create tablespace 表空间名称
    datafile '文件路径'
    size 200M -- 指定文件的大小
    autoextend on -- 开启自动扩展 ， off
    next 20M;  -- 每次扩展20M;
   */
   create tablespace lyric
      datafile 'c:/lyric.dbf'
      size 200M
      autoextend on
      next 20M
   -- 表空间的删除的语法（了解）
    --drop tablespace 表空间名称(不能删除数据文件)
    drop tablespace lyric;
    --drop tablespace 表空间名称 including contents and datafiles;
    drop tablespace lyric including contents and datafiles;
   

2. 用户

   /*
       语法:
        create user 用户名 identified by 密码
        default tablespace 表空间名称;
     */
     create user lyric identified by lyric
     default tablespace lyric;
   
   --- 权限管理
      -- connect : 基本权限：create session
      -- resource :开发人员权限，
      -- dba ：管理员权限
      -- 赋予权限的语法
         -- grant  权限列表 to 用户;
      -- 回收权限
         -- revoke 权限列表 from 用户;
   
      grant connect to lyric;
      grant resource to lyric;
      -- 查询当前用户的所有权限
      select * from session_privs;
   
      revoke resource from lyric;
   

四、创建表&约束

-- 数据类型:
    -- 字符串类型
       -- char（10）:固定长度字符串 ,最大长度：4000
       -- varchar（10）：可变长度字符串
       -- varchar2(10):可变长度字符串,由oracle公司定义，承诺向后兼容
       -- long : 存储量是 2G ，用clob取代
    -- 数值类型
       number: 整数 
       number(m,n)：浮点数类型， m是总的位数,n 是小数点后的位数
    -- 日期类型
       -- date :相当于mysql中的datetime
       -- timestamp : 时间戳类型，精确到秒后的9位
    -- 大数据类型
       -- blob: 存储量是4G，字节大数据类型,
       -- clob：存储量是4G, 字符大数据类型,

  -- 创建表的语法
     /*
       create table 表名(
            列名 类型 约束,
            列名 类型 约束,
            列名 类型 约束,
            ....
       )
     */
     create table lyric(
          id number ,
          name varchar2(200)
     );
     -- 修改表的操作
     -- 添加列 add
      -- alter table 表名 add 列名 类型 约束;
      alter table lyric add address varchar2(200);
     -- 删除列  drop
      -- alter table 表名 drop column 列名;
      alter table lyric drop column address;
     -- 修改列属性  modify
      -- alter table 表名 modify 列名 类型;
      alter table lyric modify name varchar2(400);
     -- 重命名列 rename
      -- alter table 表名 rename column 原列名 to 新列名;
      alter table lyric rename column name to iname;
  --- 约束:保证数据的完整性
     -- 主键：唯一，非空
     -- 非空:
     -- 唯一
     -- 默认
     -- 检查 
     drop table lyric;
     create table student(
         id number ,
         primary key(id ),
         sname varchar2(200) not null,
         address varchar2(200) unique,
         age number default 20 check (age between 1 and 120) ,
         sex varchar2(20) check(sex in('男','女'))  
     )
     drop table student;
     -- 外键
     -- 班级表和学生表
     create table clazz(
         cid number primary key ,
         cname varchar2(200)
     )

     create table student(
         sid number primary key ,
         sname varchar2(200),
         cid number,
         -- 设定外键
         --constraint 约束名称 foreign key(外键列) references 主表(主键) on delete cascade
         constraint fk_clazz_student foreign key(sid) references clazz(cid) on delete cascade
      ); 
      -- 级联删除：不推荐使用
      delete from student where cid = 1;
      delete from clazz where cid = 1; 

      -- 物理外键:
      -- 逻辑外键:由java代码所控制
      -- 强制删除主表(不推荐使用)
      drop table clazz cascade constraint;

五, 事务的特性

• 原子性，一致性，隔离性，持久性
• 数据库的隔离级别
  
  • read uncommited:读未提交
  • read comminted :读已提交
  • repeatable read ：重复读
  • serializable : 串行化（序列化）
• mysql ：支持四个隔离级别,默认隔离级别：
  
  • repeatable read
• oracle：支持三个隔离级别
  
  • read commited ,serializable ,read only
  • 默认的是：read commited

六、DML语句

   -- 事务的保存点(了解)
   insert into student values(1,'a',1);
   savepoint s1;
   insert into student values(2,'b',1);
   savepoint s2;
   insert into student values(3,'c',1);
   savepoint s3;
   rollback to s2;
   commit;

七、视图.序列.索引.同义词

1. 视图

   --- 视图: 是一张虚表, 不能存储记录，所有的记录都在基本表中 ,可以对视图进行增删改查
     -- 语句：
        -- create view 视图名 as DQL(表查询);
        grant dba to scott;
     -- 视图可以直接把它作为表查询，修改，删除，添加
        create view emp_view as select * from emp;   
        select * from emp_view;
   
        insert into emp_view(empno ,ename) values(1, 'rose');
   
        select * from emp;
    -- 作用一：可以屏蔽敏感列
        create or replace view employee as 
               select empno,ename ,job ,mgr,deptno from emp;
   
        select * from employee;
    -- 作用二：简化操作
       create or replace view employee as
              select t.* ,rownum rn from (select * from emp order by sal desc) t   
   
       select * from employee where rn between 6 and 9;  
    -- 作用三：可以定义只读的视图
       create or replace view emp_view as select * from emp with read only;      
   
       insert into emp_view(empno ,ename) values(2, 'mike');
   

2. 序列

   -- 序列： sequence , 数列， 从1开始，依次递增，没上上限
       /*
       创建序列的语法
          create sequence 序列名;
       */  
       create sequence emp_seq;
      -- 属性： nextval  ，currval(必须先执行依次nextval，才能使用)
      select emp_seq.nextval from dual;
      select emp_seq.currval from dual;
   
      insert into emp(empno ,ename) values(emp_seq.nextval ,'lili');
   
   
      select * from emp;
   -- 完整的语法（了解）
       /*
       create sequence 序列名
       start with 1  起始值
       increment by 2  自增量
       maxvalue  9999 最大值  ,nomaxvalue
       minvalue 1 最小值 ，nominvalue
       cycle    开启循环
       cache 20 ; 缓存
       */

3. 索引

   -- 索引：提高检索的效率
             -- 前提：百万条记录以上  ， 不经常修改的表
     -- 语法： create index 索引名称 on 表名(列,列,...);
     -- 添加百万条记录
     create table a(
            id number primary key, 
            aname varchar2(100)
      );
      create sequence a_seq; 
   
      select sys_guid()  from dual;
   
     declare
   
     begin
       for i in 1..1000000 loop
           insert into  a values(a_seq.nextval, sys_guid());
       end loop;
     end;
     -- 单列索引
      -- 添加索引前查询某一条记录： 0.453
      select * from a where aname = '10C4668168AE4AECB61E4F8F6B668022';
      -- 添加索引
      create index a_index on a(aname);
      -- 添加索引后查询某一条记录：0.062
      select * from a where aname = 'DCC4B29D2E034BC0A7DEA68604CAAC22';
     -- 复合索引 触发条件 (name ,address)
        -- select * from 表 where name = '' and address = ''可以触发索引
        -- select * from 表 where name = '' or address = '' 不可以触发索引
        -- select * from 表 where name = '' 可以触发索引
        -- select * from 表 where address = '' 不可以触发索引
   

什么是复合索引

用户可以在多个列上建立索引,这种索引叫做复合索引(组合索引); 复合索引在数据库操作期间所需的开销更小,可以代替多个单一索引; 同时有两个概念叫做窄索引和宽索引,窄索引是指索引列为1-2列的索引,宽索引也就是索引列超过2列的索引; 设计索引的一个重要原则就是能用窄索引不用宽索引,因为窄索引往往比组合索引更有效;

复合索引的注意事项

• 1、对于复合索引,在查询使用时,最好将条件顺序按找索引的顺序,这样效率最高;
  　　select * from table1 where col1=A AND col2=B AND col3=D
  　　如果使用 where col2=B AND col1=A 或者 where col2=B 将不会使用索引
• 2、何时是用复合索引
  　　根据where条件建索引是极其重要的一个原则;
  　　注意不要过多用索引,否则对表更新的效率有很大的影响,因为在操作表的时候要化大量时间花在创建索引中

• 3、复合索引会替代单一索引么
  　　如果索引满足窄索引的情况下可以建立复合索引,这样可以节约空间和时间

  1. ##### 同义词

  -- 同义词: 作用一： 跨用户访问 ，作用二：缩短表名
     select * from scott.emp;   
     -- 创建同义词
     --create synonym 同义词名称 for 用户.表;
     create synonym emp for scott.emp;
  
     select * from emp;
     -- 缩短表名 
     create synonym e for scott.emp; 
     select * from e;
  

七、数据库的备份

 -- 数据库的备份：
    grant dba to lyric;
    -- 导出: exp scott/tiger file='c:/scott.ora' [tables=(dept)]
    -- 导入: imp lyric/lyric file='c:/scott.ora'
          --  fromuser=scott touser=lyric [tables=(dept)]

八, 补充

-- 解锁用户

   -- alter user 用户名 account unlock;
   alter user scott account unlock

--- 重置密码

   -- alter user 用户名 identified by 密码;
   alter user scott identified by tiger;