oracle知识点

--oracle基础知识点
1. oracle数据库中的物理文件类型
   a. dbf (database file) 数据库的数据文件
   b. log (logging file)  日志文件
      日志文件记录了某个时间段对数据库的所有修改操作（增加，删除，修改）。
   c. ctl (controller files) 控制文件
      控制文件记录了数据库当前的各种各样的状态。
      控制文件很重要，如果损坏或者丢失，将导致数据库无法被加载。
      instance去读取控制文件，然后加载数据库的。

      一般来说，为了避免灾难，控制文件都需要多备份。   


2。oracle数据库的逻辑结构

   1. 表空间 (tablespace)
      表空间是数据存放的场所，一个oracle数据库可以有多个表空间。
      从物理上说，一个或者多个dbf文件构成了表空间。
      从逻辑上说，一个或多个segment构成了表空间。

   2. 段 (segment)
      段可以跨越DBF文件。
      创建一张表，就在某个表空间中创建了一个表段。

   3. 区 (extent)
      区是段的逻辑构成，一个段由多个区构成。
      区不能跨越DBF文件

   几个命令：

   --查看表空间的情况
   select tablespace_name,status,contents from dba_tablespaces;
   select file_name,file_id,tablespace_name from dba_data_files order by tablespace_name;
   select file_name,file_id,tablespace_name from dba_data_files;  --查看表空间中包含的数据文件

   --查看用户TEST1所拥有的区
   select owner,sgement_name,sgement_type,tablespace_name from dba_segments where owner='TEST1';

   select distinct owner from dba_segments;


3. 数据字典
   oracle中提供了一些专用数据表，以方便DBA对数据库状态进行查看。


4. 创建用户并授权
   create user uuu identified by uuu123
   account unlock
   default tablespace testtbs
   temporary tablespace temp;

   grant create session,create table,create view,create sequence to uuu;

   alter user uuu quota 100m on testtbs;

   select table_name,tablespace_name from user_tables;

   drop user uuu cascade;

5. schema


=====================================================================
一. 数据库的分类 （按规模）
    1. 微型 
       access (桌面型数据库)
    2. 小型
       foxpro (visual foxpro)
       mysql (www.mysql.com)  (C/C++,免费开源, unix平台，java)
    3. 中型
       sybase (www.sybase.com) (powerbuilder[pb],powerdesigner[pd], sybase)
       MS SQL SERVER (6.0,6.5,7.0,2000,2005,2008)
    4. 大型
       informix
       oracle  (www.oracle.com)
    5. 巨型
       db2

二. Oracle的版本
    9i
    10g (grid computing)
    11g (unix...windows)

    生产系统  （product mode)
    开发系统   (development mode)


三. Oracle的系统架构

    Oracle服务器＝Oracle Instance + Oracle database

    客户端与oracle instance进行交互，由instance负责对db的读取和写入数据，最终的数据由instance转交给

client.

    一个instance在一个单位时间内，只能挂接一个数据库，实现对该数据库的读写。
    多个instance可以同时访问oracle数据库，以实现负载均恒。


四。如何访问oracle实例？

    > 要确定oracle实例的所在位置。
    一般来说，网络上的某个应用的所在位置，包含两个部分：
      1) 应用所在的设备的IP
      2) 获得该应用所占用的端口。
         (一台电脑中往往由多个应用，大家都共享一个IP，为了区分不同的应用，系统为每个应用都做了个数字

标志，叫port)
         绑定(bind)   xxx程序绑定了某个IP的某个端口。

      oracle instance id
      一个instance唯一标志. (sid)

    ip:192.168.0.2
    port:1521
    sid: orcl

    -> 192.168.0.2:1521:orcl

    传输协议：
    TCP, TCPS (TCP OVER SECURITY)

    test account: test/test

    > 了解数据库的名字
    全局数据库名  global database name

    global database name: orcl

    > 创建TNS NAME
      uuu=192.168.0.2:1521:orcl
      connect uuu


    > 用sqlplus连接数据库
      sqlplus test/[email protected]

五。oracle数据库的启动和关闭

    sqlplus /nolog
    conn sys/syspwd as sysdba

    sqlplus sys/[email protected] as sysdba  (tnsname tcp login)

    sqlplus sys/syspwd as sysdba          (ipc)


    1.关闭
      shutdown immediate;
    2.启动
      oracle数据库的启动状态
      1)nomount  只启动instance, 但没有加载数据库，该状态适合调整instance参数时候使用。
      2)mount    启动instance,并加载数据库，但不对外开放。 该状态适合内部调试时使用。
      3)open     启动instance,加载数据库，并对外开放，允许外界访问。

      startup  (startup open)
      startup nomount;
      alter database mount;
      alter database open;
      conn test/test
      conn sys/[email protected] as sysdba


六。数据库操作的普通话－结构化查询语言（SQL=Structured Query Language)

    select * from ourstudent where stu_age>22;

    1) 列输出限制部分   select *  （输出所有的列）
    2) 行输出限制部分   where age>22


    sql语句不区分大小写

    sql语句的分类：
    1) DDL (Data Definition Language)  (创建，修改，删除数据库对象的语句)   
       create table, drop table

       DDL语句运行后自动commit(提交), DDL语句不支持rollback(回滚).
       DDL语句不支持事务(transaction).


    2) DML (Data Manipulation Language) (对记录进行CRUD操作 (create,Read,Update,Delete))
       insert into sometable values(.....)
       update sometable set someColumn=someValue where .....
       delete from sometable where .....
       select somecolumn from sometable where ......

    3) DCL
       grant ..... to someuser....


    > 表的关联
    一张表表达的内容有限，有时候需要多张表关联以提供更加丰富的信息。

    SQL> create table ourclass(
    2    cid int primary key,
    3    cname varchar2(30) not null
    4  );

    SQL> create table student(
    2    sno int,
    3    sname varchar2(20) not null,
    4    sage int not null,
    5    cid int not null, -- cid在本表中是普通字段, 但其定义在class表中则是主键。
                         -- 对于本表来说，cid就是一个外来的主键，外键。

    6    constraint PK_STU PRIMARY KEY (sno),  --声明一个主键，主键的名字叫PK_STU, 主键对应字段是

SNO.
    7    constraint FK_STU_CLS FOREIGN KEY (scid) references class(cid)
    8  );

    表已创建。

    学生和班级之间的关系   N:1 


    图书类别和图书  1:N
    歌星和fans      1:N

    两者的关系，有主控的一方，非主控方记住关系，则非主控方保持外键，外键就是两张表之间的关系。

    在外键引用中：
    子表不能增加主表所没有的字段值。
    而子表如果引用了某字段值，主表拥有该字段值的记录不能删除。

    案例：
    求david所在的班级的名字：

    通过联结(inner join)求解
    select sname,cname
    from student
    inner join class
    on scid=cid
    where sname='david'

    通过子查询(subquery)求解
    1) 先在student这张表中，通过学生姓名david找到其所在的班级编号
    2) 拿着这个编号到ourclass表中找到符合该编号的记录，最后找到cname.

    select cname 
    from ourclass
    where cid=(
       select scid
       from student
       where sname='david'
    );

    select cname 
    from ourclass
    where cid <> (
       select scid
       from student
       where sname='david'
    );


    select cname 
    from ourclass
    where cid in (
       select scid
       from student
    );

    select cname 
    from ourclass
    where cid not in (
       select scid
       from student
    );

   > 分组查询

SQL> create table goods(
  2    g_id int primary key,
  3    g_name varchar2(20) not null
  4  );

SQL> create table sale_log(
  2     sid int primary key,
  3     smount int not null,
  4     g_id int not null,
  6     constraint FK_SALELOG_GOODS FOREIGN KEY (g_id) references goods(g_id)
  7  );

SQL> select g_id,sum(smount)
  2  from sale_log
  3  group by g_id
  4  ;

select a.g_id,a.g_name,b.mount
from goods a
inner join
(
 select g_id,sum(smount) mount
 from sale_log
 group by g_id
) b
on a.g_id=b.g_id

   > 聚合函数
     sum,count,min,max,avg
     (这些函数主要用在分组分析的场合,如果没有指明group by，那么默认所有记录为一个组。)

     groupby 环境下的语句限制
     select部分，只能出现被groupby的字段，以及被聚合函数修饰的字段。


select * 
from sale_log
order by g_id

select count(*) from sale_log

select g_id,count(*)
from sale_log
group by g_id

select g_id,max(smount)
from sale_log
group by g_id

select g_id,min(smount)
from sale_log
group by g_id

select g_id,avg(smount)
from sale_log
group by g_id

select g_id,avg(smount)
from sale_log
group by g_id


select a.g_id,a.g_name,b.smount
from goods a
inner join
(
 select g_id,sum(smount) smount
 from sale_log
 group by g_id
) b
on a.g_id=b.g_id


> 字段的完全说明路径
     username.tablename.columnname
     test.goods.g_id    (命名空间 namespace)