Oracle数据库期末总结

目录

• 基础内容-服务相关
  • Oracle体系结构:
  • 表空间与数据文件之间的关系：
  • 内存结构（SGA ,PGA)
  • 表空间（大题）(tablespace)
  • 数据文件（大题）(.dbf)
    • 控制文件,记录和维护数据库的物理结构 ()
    • 重做日志文件（选择）(redo.log)
    • 约束(constraint)
    • ※不同点：
    • 索引（选择）(index)
  • 分区表（大题）
    • 数据查询（基本查询，分组查询，多表查询，无关子查询）
  • pl/sql语句（两道）
    • 游标（定义，打开，检索，关闭）（必考）
    • 存储过程(Procedure)，函数(Function)，触发器(Trigger)（2题） （定义）

基础内容-服务相关

1. Sql分为

   1. DDL:(Data Definition Language) 修改数据库对象,create,alter,drop,grant,revoke..
   2. DML:(Data Manipulation Language)数据操纵,insert,update,delete
   3. DQL:(Data Query Language)数据检索,select
   4. 事务控制:commit,rollback,savepoint

2. SID(Oracle服务标识符):orcl

3. 重要的服务：OracleserviceORCL(必开服务)/OracleOraDb11g_home1TNSListener(监听)

4. 网络配置：服务器端监听：listener.ora 客户端本地网络配置tnsnames.ora

5. 登录sqlplus /切换conn

6. 文件命令：save(保存缓冲)/spool(保存屏幕内容)

7. 环境变量：linesize,pagesize,show linesize;set linesize 300 pagesize 15;

8. 显示表结构：desc

9. Oracle体系结构:

   图1

10. 物理结构：数据文件，重做日志文件，控制文件

11. 逻辑结构：表空间>段>区>数据块

12. 数据块->Oracle最小逻辑存储单元，数据库执行输入输出的最小单位，有一个或多个操作系统块组成，块默认8k

13. 区->Oracle存储空间分配的最小单位，默认64k

14. 表空间与数据文件之间的关系：

    图二

15. 内存结构（SGA ,PGA)

    1. SGA 高速缓存区工作过程 图三
    2. 重做日志缓冲区：循环使用
    3. 共享池：库缓存和数据字典缓存区

16. Oracle后台进程

    1. （用户进程，服务器进程，后台进程）
    2. 后台进程：DBWR(数据写),LGWR(日志写),CKPT,SMON,PMON,ARCH(日志归档)

17. 数据字典

    1. 存于system表空间，属于sys模式
    2. 静态数据字典表和视图（dba_tables,dba_data_files,user_tables)
    3. 动态(v$datafile,v$database,v$instance)

18. 表空间概念：

    1. 数据库可划分为若干逻辑存储单元称为表空间
    2. 每个数据库必有SYSTEM（系统表空间） && SYSAUX（辅助表空间）

19. 表空间（大题）(tablespace)

    1. 创建

       Create  tablespace T_NAME Datafile 'locationF_NAME.dbf' size ?M 
       Extent Management Local  Uniform size ?M //区定制分配
       Segment Space Management Manual; //段手动
       

    2. 修改

       1. 添加数据文件：

          Alter tablespace T_NAME Add Datafile 'F_NAME' size ?M;

       2. 自动拓展:

          Alter Database Datafile 'F_NAME' Autoextend On Next ?M Maxsize Unlimited;

       3. 修改数据文件大小:

          Alter Database Datafile 'F_NAME' Resize ?M;

    3. 删除，查询

       1. drop tablespace T_NAME including contents and datafiles;
       2. v$tablespace,dba_tablespaces

20. 数据文件（大题）(.dbf)

    1. 改变数据文件名称和内容（4步)

       1. 修改表空间可用性(脱机)：Alter tablespace T_NAME Offline;

       2. 操作系统修改文件名称或位置：host copy

       3. 重命名：

          Alter tablespace T_NAME Rename Datafile 'F_NAME1' to 'F_NAME2';

       4. 表空间联机 Alter tablespace T_NAME online;

    2. 查询数据文件：v$datafile,dba_data_files

    3. 数据文件可用性：alter database datafile 'F_NAME' offline|online

21. 控制文件,记录和维护数据库的物理结构 ()

    1. 多路复用控制文件（4）
       1. 逻辑上增加控制文件alter system set control_files=.. scope=spfile
       2. shutdwon immediate
       3. host copy
       4. startup
    2. Oracle数据库启动（startup）步骤：
       1. shutdown
       2. nomount:读初始化参数文件(init.ora),启动实例(instance)SGA和后台进程启动
       3. mount:打开控制文件，可执行日志归档，数据文件联机脱机，数据库介质恢复
       4. open:所有文件打开

22. 重做日志文件（选择）(redo.log)

    1. 默认三组，至少需2组，每组至少一个文件，组内文件不同盘，大小一致

    2. 查询：v$log，v$logfile

    3. 重做日志文件切换 alter system switch logfile

    4. 创建重做日志组

       alter database add logfile group 4 ('redo1.log','redo2.log') size 10m;

    5. 创建重做日志文件

       alter database add logfile member 'redo.log' to group 4;

23. 归档文件（选择）

    1. archivelog/noarchivelog（非归档状态）

24. 模式(schema)

    1. 概念:是指一系列逻辑数据结构或对象的集合，在创建用户时会生成一个同名模式。
    2. 在数据库中一个对象的完整名称为schema.object

25. 创建表(table)

    create table table_name( rowname type key )

26. 约束(constraint)

    1. primary key(主键约束),check(检查约束),unique(唯一性约束)，null/not null（空约束）

    2. primarykey,unique区别？

       1. primary key:

          1. 定义主键,起惟一标识作用,其值不能为NULL,也不能重复；
          2. 一个表只能定义一个主键约束

       2. unique:

          1. 定义为惟一性约束的某一列或多个列的组合的取值必须惟一；
          2. 如果某一列或多个列仅定义惟一性约束，而没有定义非空约束，则该约束列可以包含多个空值；

       3. 相同点：

          1. 建立同时在该列上建立一个唯一性索引
          2. 可以是列级也可以是表级约束

       4. ※不同点：

          1. 在一个基本表中只能定义一个PRIMARY KEY约束，但可定义多个UNIQUE约束；
          2. 对于指定为PRIMARY KEY的一个列或多个列的组合，其中任何一个列都不能出现空值，而对于UNIQUE所约束的唯一键，则允许为空。
          3. 不能为同一个列或一组列既定义UNIQUE约束，又定义PRIMARY KEY约束。

27. 修改表 alter

    1. alter table sub_emp2 add(phone varchar2(10))
    2. alter table sub_emp2 add(hiredate date default sysdate not null)
    3. alter table player add constraint p_ck1 check(sage between 20 and 30)

28. 索引（选择）(index)

    1. 索引作用：加快行的检索，减慢更新
    2. 平衡树索引和位图索引
       1. 平衡树索引(B-Tree):高基数，重复率低
       2. 位图索引:适合索引值取值范围小，重复率高，如性别。
    3. 创建索引：create index emp_name_index on emp(ename);

29. 视图

    1. 虚表，来自一个或多个基表
    2. 创建:create view emp_base_info_view (empno ... ) as select empno from emp;

30. 序列（概念）：产生唯一序号的数据库对象,生成不重复连续整数

31. 分区表（大题）

    1. 范围分区

       Create Table student_range(
       sno number primary key
       score number )
       Partition by Range(score)
       ( Partition p1 values less than(60) tablespace tbs1
         partition p2 values less than(80) tablespace tbs2
        partition p3 values less than(MAXVALUE) tablespace tbs3
       );
       

    2. 列表分区

       Create table student_list(
          sno number primary key
          sex char(2) check(sex in('M','F')))
       Partition by List(sex) 
       ( Partition stu_male values('M') tablespace tbs1
         Partition stu_female values('F') tablespace tbs2
       );
       

    3. 散列（了解一哈）by Hash(sno) (partition p1 tablespace tbs1)

32. DML 增删改

    1. insert
    2. update
    3. delete

33. 事务处理：commit,rollback,savepoint

34. 数据查询（基本查询，分组查询，多表查询，无关子查询）

    1. 基本查询：
       1. 空值判断：select * from s where sno is (not) null
       2. 模糊查询：where ename like '%s%' //含s
       3. 排序：order by
       4. 统计：select count(*) from s group by sno;
    2. 分组查询：group by having
    3. 多表查询：select from table1 a,table2 b where a.sno = b.sno;
    4. 子查询（无关子查询）：select from where sno in (select sno from where)

35. pl/sql语句（两道）

    1. 执行部分必需，声明与异常可选

    2. select ..into..（只能查一条记录！）

    3. 控制结构：循环选择(小题)

       1. if

          if condition1 then statement1
          elsif condition2 then statement2
          else statement3
          end if;
          

       2. case

          case
          when condition1 then state1
          else state2
          end case;
          

       3. Loop

          Loop
          statement
          EXIT when condition  --Exit
          end loop;
          

       4. While

          while condition loop 
          statement
          end loop;
          

       5. For

          for i in [reverse] 1..10 loop
          statement
          end loop;  
          

36. 游标（定义，打开，检索，关闭）（必考）

    1. 定义：cursor c_emp IS select empno,sal from emp;

    2. 打开：open c_emp;

    3. 检索：

       loop
       fetch c_emp into v_emp;   -- v_emp c_emp%ROWTYPE
       exit when c_emp%NOTFOUND
       do sth.
       end loop;
       

       4. 关闭：close c_emp;

    4. 显示游标的属性（%isopen,%fount,%notfount,%rowcount)

       1. exit when c_emp%notfound

    5. 异常 exception

       

       raise user_define_exception

37. 存储过程(Procedure)，函数(Function)，触发器(Trigger)（2题） （定义）

    1. DML触发器（！！）

       Create or replace Trigger trigger_name [before|After]
       trigger_event[insert|update|delete] or trigger_event 
       on table_name
       [for each row]
       Begin --Trigger body
       if to_char(sysdate,'DY','NLS_DATE_LANGUAGE=AMERIVAN') 
       in ('SAT','SUN') then
       Raise application_error(-20001,'error');
       END if;
       END trigger_name;
       

    2. CREATE OR REPLACE TRIGGER trg_emp_update_row
       BEFORE UPDATE OF sal 
       ON emp
       FOR EACH ROW  -- get your attention here
       WHEN(new.sal<=old.sal)
       BEGIN
         RAISE_APPLICATION_ERROR(-20001,'The salary is lower! ');
       END trg_emp_update_row;
       /* old.field 修改前, new.field 修改后*/
       

    3. 语句级触发器

38. 安全过程

    1. 用户管理

       1. 创建 :Create User user_name identified by user_passwd account lock;
       2. 修改:Alter User user_name identified by new_passwd account unlock;

    2. 权限管理

       1. 系统权限 grant creat session,create table to user_name;
       2. 对象权限 grant update,select on scott.emp to user_name;
       3. 回收权限 revoke create table from user_name;

    3. 概要文件管理

       1. 创建

          create profile lock_account limit failed_login_attempts 3 password_lock_time 2;

          create profile password_1 limit password_life_time 10 password_grace_time 2 password_reuse_time 10;

       2. 分配

          Alter user user_name profile lock_acount;

          Create User user_name identified by password profile password_1;