Oracle概念与基本操作

 一<1>.数据管理方式的发展 

 

   1手工管理阶段 

 

       数据不被保存，还没有文件的概念。一组数据与一个程序直接对应 

   2文件管理阶段（ROM） 

       数据以文件形式存放。一个应用对应一组数据，应用之间不能共享数据。 

   3数据管理阶段（RAM） 

       多用户、多应用要共享数据。需要专门的数据管理系统。 

<2> 数据库的发展史

萌芽阶段—文件系统

初级阶段—第一代数据库

网状型数据库：记录是数据的存储单位。记录包含若干数据项。

层次型数据库：按记录来存储数据，但记录之间有父子关系，树状。

中级阶段—第二代数据库

关系型数据库和结构化查询语言(SQL)

70年代IBM E.F.Codd博士发表《大型共享数据银行的关系模型》

关系数据库是一组已经被组织为表结构的相关信息的集合。每个表包许多行（记录），这些行又被组织为列（字段）。这些表在数据库中都被存储在称为方案（Schema）的结构中。

高级阶段—新一代数据库

对象型数据库

 二<1>.基本概念

数据

所谓数据(Data)，就是描述事物的符号。在我们的日常生活中数据无所不在，数字、文字、图表、图像、声音等都是数据。人们通过数据来认识世界，交流信息

数据库

数据库(DB 即 Database) ，顾名思义，就是数据存放的仓库。在计算机中，数据库是数据和数据库对象的集合。

数据库对象是指表(Table)、视图(View)、索引(Index)、存储过程(Stored Procedure)、触发器(Trigger)等。

数据库管理系统

数据库管理系统DBMS(即Database Management System)，是用于管理数据库的一套软件。用户通过DBMS查询和操作数据库，DBMS维护数据的安全性和完整性，以及进行多用户下的并发控制和恢复数据库。

<2>数据库的基本概念

概念模型：基于客户的想法和观点所形成的认识和抽象

实体(Entity)：客观存在的、可以被描述的事物。如员工、部门。

属性(Attribute)：用于描述实体所具有的特性或特征。如使用编号、姓名、部门、工资等属性来描述员工的特征。

关系(Relationship)：实体之间的联系。如：部门和员工之间有一对多的关系。

数据模型：也叫关系模型，是实体、属性、关系在数据库中的具体体现。

关系数据库：用于存储各种类型数据的“仓库”，是二维表的集合。

表：实体的映射。

行和列：行代表一个具体的实体的数据。也叫一条记录。列是属性的映射，用于描述实体的

主键和外键：

数据库管理系统(DBMS)

<3>.Oracle---神谕，预言

1979年：Oracle公司成立。董事长兼CEO:拉里·艾利森

最新推出基于SQL标准的关系 数据库产品。

1983年：Oracle3   Miner和Scott用C语言重写。

SQL语句和事务处理的"原子性"

1985年：Oracle5   首批C/S模式RDBMS产品。

分布式查询

1988年：Oracle6   行级锁、联机备份。

1992年：Oracle7   协同服务器数据库。

分布式事务处理功能

1998年：Oracle8i  互联网数据库。全方位Java支持。

2001年：Oracle9i  集群

2003年：Oracle10g 网格计算

2007年：Oracle11g  信息生命周期管理

<4>Oracle数据库特点

Oracle数据库的主要特点

支持多用户、大事务量的事务处理

数据安全性和完整性控制

支持分布式数据处理

可移植性

二. ORACLE的体系结构 

 

    ORACLE SERVER 

 

                ---INSTANCE 

                    ---内存结构 

                         ---SGA（系统全局区，共享池（sql语句），java池（java程序），数据缓冲区（未保存的数据），日志缓冲区） 

                        ---PGA（程序全局区 连接的用户占用的内存） 

                    ---进程 

                         ---后台进程（用于操作数据 PMON,SMON,DBWR,LGWR具体参考《ORACLE体系结构》 

                        ---用户进程 （用户连接用户） 

 

                 ---DATABASE 

                    1 DBF数据文件  

                     2 CTL控制文件 【引导文件】 

                     3 LOG重做日志文件  【恢复文件】 

                     4  密码文件(口令文件)，初始化文件（参数文件），dump文件（DBA操作） 

 

三.sql语言操作分类： 

 

   1 数据定义语言DDL  （定义，操作数据的结构） 【-->java的变量定义】 

       CREATE : 在数据库中创建新的数据对象 

       ALTER : 修改数据库中对象的数据结构 

       DROP : 删除数据库中的对象 

       DISABLE/ENABLE TRIGGER : 修改触发器的状态 

       UPDATE STATISTIC : 更新表/视图统计信息 

       TRUNCATE TABLE : 清空表中数据 

       COMMENT : 给数据对象添加注释 

       RENAME : 更改数据对象名称 

     2数据操作语言DML 

        DML(Data Manipulation Language)(CRUD)，用于添加/修改/查询数据库中数据。 

       DML包含以下语句： 

       INSERT ：将数据插入到表或视图 

       DELETE ：从表或视图删除数据 

       select ：从表或视图中获取数据 

       UPDATE ：更新表或视图中的数据 

       MERGE ： 对数据进行合并操作(插入/更新/删除) 

    3数据控制语言DCL 

 

        DCL(Data Control Language)用来向用户赋予/取消对数据对象的控制权限。 

       DCL包含以下语句： 

       GRANT : 赋予用户某种控制权限 

       REVOKE ：取消用户某种控制权限 

 

   4. 事务控制语言(TCL) 

        TCL(Transaction Control Language)用来对事务进行管理。 

       TCL包含以下语句： 

       COMMIT : 保存已完成事务动作结果 

       SAVEPOINT : 保存事务相关数据和状态用以可能的回滚操作 

       ROLLBACK : 恢复事务相关数据至上一次COMMIT操作之后 

       SET TRANSACTION : 设置事务选项 

四. 卸载或者重装数据库 

 

  卸载： 

 

     1停止所有以oracle开始的服务，找到 UI（Universal Installer） 打开卸载界面 点击卸载产品按钮  选择所有的组件 点击删除按钮 

 

     2删除服务 使用命令  sc delete 服务名称 删除所有的服务 

 

     3删除注册表 开始->运行->输入 regedit命令打开注册表 找到 HKLM/SofeWare下的oracle 全部删除 

     4找到oracle的安装目录 删除 如果无法删除使用软件强制删除 

 重装： 

 

     1 如果服务能够启动（ORCL和Listener结尾） 首先sqlplus /nolog 和conn /as sysdba连接  出现字符 历程启动  需要使用命令 startup 

 

     2 服务不能启动  通过杀毒软件 去恢复oracle被删除的部分 

 

     3 如果是Listener结尾启动不了使用netca删掉前面创建的监听 去重新创建一个 

 

     4 如果是ORCL结尾启动不了 使用dbca 删除掉前面创建的orcl的数据库  在重新创建 一个新的 

 

     5 dbca和netca无法打开 点击oracle安装文件点击setup。exe重装数据库 

 

     6 setup.exe无法启动或者多次安装都失败，重装系统  

五.1 oracle版本和下载
   oracle 11g release 2
   http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html


2 oracle的结构
   oracle univsal installer (OUI) 用于卸载和重装oracle （这个工具一般都无法卸载和重装）
   database configuation assistant (dbca) 用于管理数据库的维护
   network configuation assistant (netca) 用于管理网络连接

3. oracle内部的组成结构
    介绍下环境变量
       ORACLE_BASE=C:\app\dell
       ORACLE_PRODUCT=%ORACLE_BASE%\product
       ORACLE_HOME=%ORACLE_PRODUCT%\dbhome_1
    (1 数据库
       数据库 本身就是一个数据的集合 它的数据是存在文件中
       包含以下几类文件
          1》数据文件 位于%ORACLE_BASE%\oradata\orcl
               dbf结尾叫做 数据文件（存放数据的）
               log结尾叫做 重做日志文件（备份和恢复）
               ctrl结尾叫做 控制文件 （控制数据库的启动参数）
          2》参数文件【重点】
               %ORACLE_HOME%/NETWORK/admin目录
                listener.ora 监听文件
                tnsnames.ora 客户端连接文件
    (2 数据库实例
          oracle启动的主程序 （线程）
             pmon 用于管理后台进程
             smon 用于监控系统进程
             dbwr 数据写入
             lgwr 日志写入
             SGA(SYSTEM GLOBAL AREA) 系统全局区
                共享池  fixed size
                数据缓冲区  database buffers
                日志缓冲区  redo buffers
    (3 客户端实例
             PGA(PROGRAM GLBAL AREA) 程序全局区 （当客户端连接的时候会产生的内存）

4. 系统默认的账号
    sys （超级管理员） 超级管理员登陆一定要加上  as sysdba
        sqlplus sys/你的密码  as sysdba
        sqlplus / as sysbda (本机拥有的数据最高权限 不需要输入密码)


    system （普通管理员）
        sqlplus system/你的密码
    scott （初学者演示账号） scott被锁定了 默认未开启


5. oracle中常用的服务 
    OracleServiceORCL  【oracle的主服务 必须要 启动该服务器 数据库才能启动】
       
    OracleOraDb11g_home1TNSListener 【监听服务 用于等待其他的客户端连接】

6. 常用的客户端
      sqlplus  (dos客户端)
      plsql developer （C/S客户端）【100%必用】

六 ORACLE管理命令 

      1 启动sqlplus工具 （ORACLE dba管理数据库的工具） 
 
         sqlplus /nolog  (无授权信息登录) 
 
              conn /as sysdba(使用本地系统登录 本地用于加入了ORA_DBA组) 

           sqlplus 用户名/密码@连接描述符 
 
           sqlplus 用户名/密码  （默认本地ORCL） 
 
      2 oracle退出命令 
 
           exit(quit)  直接退出到dos命令 
 
           disconnect(disconn|disco) 退出连接 在sql命令中 
 
           orapwd（orapwd file=<fname> password=<password>） 重置密码文件 
 
           password(passw) 修改密码 （必须登录之后） 
 
      3 操作上一次执行的sql (sql缓冲区中 缓冲区中只能存储一条sql) 
 
           list(l) 显示上一次缓冲区中sql 
 
           run(r|/)执行上一条缓冲区中的sql 
 
           clear buffer(cl buff)清空缓冲区中的sql 
           get sql文件的路径  将文件的内容读取到缓冲区 可以使用 list或者run去查看或者运行 

           save(sav) sql文件的路径 将缓冲区的sql写入到文件中 
 
           start(sta|@) sql文件的路径 将文件的内容读取到缓冲区后 并执行 (get sql路径;run) 
 
           edit(ed) 使用ed 文件路径修改文件的内容 或者使用ed命令修改缓冲区中的内容 
 
           clear scr（ dos清屏cls） 在sql命令下清除屏幕上的文字 
 
           spool 文件路径   记录当前用户的所有操作以及输出   spool off（终止） 
 
     4 启动和关闭 
 

               启动数据库orcl服务： 
 
              sqlplus /nolog, 

               conn /as sysdba 
 
               startup 
 
                   nomount  数据库的instance已经打开  数据库没有打开 
 
                  mount    数据的控制文件已经打开 并且和instance连接 但是不能远程连接 
 
                  open     数据库已经打开并且可以远程连接 
 
                        关闭数据库 
 
              shutdown 
 
                启动或者关闭监听服务 
 
           dos命令下  lsnrctl start|stop 
 
                        检查连接描述符是否能连接  tnsping 描述名称 

 七.数据库网络编程(简单操作)

配置文件

CS  (Client/Server)   BS (Browser/Server)

oracle 连接命令 (socket编程)

服务器 (ServerSocket[ip+端口])

   关于网络编程主目录 （C:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN）

   listener.ora（ip+端口）

     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.14.101)(PORT = 1521))

     ServerScoket ss=new ServerSocket(1521)

    测试 telnet ip 端口

   tnsnames.ora (连接数据库)

    

ORCL36 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.14.36)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

//show user 查看当前用户

 

1. 客户端sqlplus命令

 as sysdba 如果某个用户是超级管理员（dba） 普通用户 不需要添加as sysdba

 (

   create user 用户名 identified by密码//创建用户

   grant connect to 用户名

 )

 sqlplus / as sysdba

 sqlplus 用户名/密码@链接描述符  as sysdba

  oracle默认的用户名是sys密码123456

 

开发客户端 plsqldevleoper

2. oracle管理工具

 dbca 用于管理数据库的维护  每一个数据库都存在名称 默认 orcl

 netca 用于管理网络连接   listener.ora和tnsnames.ora建议手工编辑

3. oracle常用的服务(services.msc)

   OracleServiceORCL （主服务） 必须开启的服务

     net start|stop 服务名

   OracleOraDb11g_home1TNSListener （监听服务 用于远程连接）

     lsnrctl stop|start|reload

4. 注册表（regedit）

   备份注册表 HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE

   备份 %ORACLE_HOME%/bin

5. oracle卸载和重装

   OUI(卸载工具)

  一 关闭服务

    net  stop oracleserviceorcl

    lsnrctl stop

 二 删除开始菜单

 三 删除注册表

 四 删除服务

   sc delete 服务名

 五 删除目录

   C:\Program Files\Oracle

八.子查询

1.子查询可以出现在任意的位置

包括：SELECT子句、FROM子句、WHERE子句、GROUP BY、HAVING、ORDER BY等。
2.子查询注意

一般来说子查询的效率低于连接查询。
表连接查询都可以用子查询替换，但反过来说却不一定。

3.集合操作符：合并多个查询结果

UNION ALL：将多个查询结果合并到一个结果中，有重复行
UNION：将多个查询结果合并到一个结果中，没有重复行（并集）
INTERSECT：返回两个查询结果中共有的行 (交集)
MINUS：返回从第一个查询结果中减去第二个查询结果中相同的行之后剩余的行(差集)

九.事务

1.数据库事务：一组相关的数据改变SQL语句，这组SQL语句对数据的改变要么同时成功，要么同时失败。
事务的ACID属性：
原子性(Atomic)：
指整个数据库事务是不可分割的工作单元。原子性确保在事务中的所有操作要么都发生，要么都不发生。
一致性(Consistency)：
一旦一个事务结束了(不管成功与否)，系统所处的状态和它的业务规则是一致的。即数据应当不会被破坏。
隔离性(Isolation)：
指多个事务同时操作同一数据时，每个事务都有各自的完整数据空间。
持久性(Durability)：
一旦事务完成，事务的结果应该持久化。

2.隐式事务控制：

自动隐式提交事务

自动隐式回滚事务：
3.显式事务控制：（建议使用）
提交和结束事务：COMMIT;
回滚事务：ROLLBACK [TO 回滚点];
设置事务回滚点：SAVEPOINT 回滚点;

4.事务隔离级别

一个事务对数据库的修改与并行的另外一个事务的隔离程度。

多个事务同时访问数据库中相同的数据时，如果没有采取必要的隔离机制，就可能会发生如下并发问题：
第一类丢失更新：撤消一个事务时，把其它事务已提交的更新数据也撤消了。

两个事务都更新同一个行，而另一个事务异常回滚，导致两处变化都丢失。
脏读：一个事务读到另一个事务未提交的更新数据。
可重复读：一个事务读到另一个事务已经提交的更改数据。
第二类丢失更新：一个事务覆盖另一个事务已经提交的更新数据。
幻读：一个事务读到另一个事务未提交的新增或已删除数据。

一个事务前后执行一个查询两次，在第二个结果集中包括第一个结果集中不可见的行，或者包括已经删除的行
5.Oracle的事务隔离级别

SQL标准定义了四种隔离级别：

READ UNCOMMITTED： 读未提交数据。脏读、不可重复读、幻读都可能发生。它的事务隔离性最低。
READ COMMITTED：读已提交数据。不允许脏读。
REPEATABLE READ：可重复读。不允许不可重复读，脏读。
SERIALIZABLE：串行化。不允许任何并发事务问题。最严格的事务隔离性。
Oracle只支持READ COMMITTED和SERIALIZABLE。
默认为READ COMMITTED
设置一个事务的隔离级别
set transaction isolation level serializable;
设置整个会话的隔离级别 
alter session set isolation_level=serializable;

十.用户管理

1.创建用户的语法：
CREATE USER 用户名 IDENTIFIED BY 密码;
修改用户密码：
ALTER USER 用户名 IDENTIFIED BY 新密码;
锁定用户：
ALTER USER 用户名 ACCOUNT LOCK;
解锁用户：
ALTER USER 用户名 ACCOUNT UNLOCK;
删除用户：
DROP USER 用户名;

2.关于Schema

Schema(方案)：数据库对象的集合
Oracle的主要数据库对象有：表、索引、视图、同义词、存储过程、函数、触发器等。
一个用户对应一个方案(schema)，该用户的方案名等于用户名，并作为该用户缺省方案。
创建一个用户时，同名的方案也被自动创建。Oracle数据库中不能单独新创建一个schema。
用户可以直接访问其缺省方案中的对象。
用户要访问其它方案中的对象，必须拥有此对象的访问权限。而且必须使用“方案名.对象名”这种完整写法。
如：用户sys要访问scott方案的emp表，应该使用以下语句：
SELECT * FROM scott.emp;

3.角色管理

角色(role)：一组权限的集合。可分配给用户或其它角色。
Oracle10g内置的常用角色：
CONNECT：连接到Oracle的权限
SELECT PRIVILEGE FROM dba_sys_privs WHERE grantee = 'CONNECT';
RESOURCE：创建各种数据库对象的权限。
SELECT PRIVILEGE FROM dba_sys_privs WHERE grantee = 'RESOURCE';
DBA：数据库管理员所有权限
EXP_FULL_DATABASE：可把数据库整个备份输出的权限
IMP_FULL_DATABASE ：可把数据库整个备份恢复输入的权限
4.授予与撤销

将角色授予用户：
GRANT 角色名[,角色名2,...] TO 用户名;
示例：GRANT CONNECT,RESOURCE TO test;
用户可以通过user_role_privs表查询自己所拥有的角色。
撤销用户的角色：
REVOKE 角色名[,角色名2,...] FROM 用户名;

5.加速查询的索引
索引是数据库中提高查询性能的最常用工具。
任何单个查询要检索的行少于或等于整个表行数的10%时，索引就是非常有用
索引候选列需要存储很大范围的值——“B-树”索引
索引候选列只包含很少范围的值——“位图”索引
Oracle数据库会为定义了PRIMARY KEY和UNIQUE约束的列自动创建一个对应的“B-树”唯一索引。
在查询语句中针对主键的查询效率更高
6.索引
创建“B-树”索引：
CREATE [UNIQUE] INDEX 索引名 ON 表名(列名[,列名]…);
创建“位图”索引：
CREATE BITMAP INDEX 索引名 ON 表名(列名[,列名]…);
创建基于函数的索引：示例
CREATE INDEX i_emp_ename ON emp(lower(ename));
获取有关索引的信息：
从user_indexes视图中可以获取有关索引的信息
删除索引：
DROP INDEX 索引名;

7.简化查询的视图
视图(VIEW)：一个或多个基表上的预定义查询。
视图是虚表,它在存储时只存储视图的定义,而没有存储对应的数据 
视图只在使用时才从基表中搜集数据,再展现给用户
使用视图优点：
安全：防止未经许可的用户访问敏感数据
数据独立：降低数据库的复杂程度
视图可以间接对表进行更新，视图中数据的更新就是表数据的更新

8.同义词
同义词：数据库对象的一个别名，用来简化对数据库对象的访问。
创建同义词语法：
CREATE SYNONYM 同义词名 FOR 方案名.对象名;
删除同义词：
DROP SYNONYM 同义词名;
示例：
当A用户要访问B用户方案中的C表时，就需要使用如下格式：
SELECT * FROM B.C;
可以通过创建同义词来避免这样的问题：
CREATE SYNONYM C FOR B.C;
这样就是可以直接使用SELECT * FROM C;了。
总结

表管理
CREATE、ALTER、DROP、TRUNCATE
约束管理
分类：NOT NULL、UNIQUE、CHECK、PRIMARY KEY、FOREIGY KEY
管理：ADD CONSTRAINT、 DROP CONSTRAINT
序列管理
CREATE SEQUENCE、DROP SEQUENCE
索引管理
CREATE INDEX 、DROP INDEX
视图管理
CREATE OR REPLACE VIEW 、DROP VIEW
同义词
CREATE SYNONYM、 DROP SYNONYM