《Oracle（一）- 基础》

一、Oracle简介

（一）什么是ORACLE

ORACLE 数据库系统是美国 ORACLE 公司（甲骨文）提供的以分布式数据库为核心的一组软件产品，是目前最流行的客户/服务器(CLIENT/SERVER)或B/S 体系结构的数据库之一。

ORACLE 通常应用于大型系统的数据库产品。

ORACLE 数据库是目前世界上使用最为广泛的数据库管理系统，作为一个通用的数据库系统，它具有完整的数据管理功能；作为一个关系数据库，它是一个完备关系的产品；作为分布式数据库它实现了分布式处理功能。

ORACLE 数据库具有以下特点：

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

（2）数据安全性和完整性控制

（3）支持分布式数据处理

（4）可移植性

（二）ORACLE 体系结构

1.数据库

Oracle 数据库是数据的物理存储。这就包括（数据文件 ORA 或者 DBF、控制文件、联机日志、参数文件）。其实 Oracle 数据库的概念和其它数据库不一样，这里的数据库是一个操作系统只有一个库。可以看作是 Oracle 就只有一个大数据库。

2.实例

一个Oracle实例（Oracle Instance）有一系列的后台进程（Backguound Processes)和内存结构（Memory Structures)组成。一个数据库可以有 n 个实例。

3.数据文件（dbf）

数据文件是数据库的物理存储单位。数据库的数据是存储在表空间中的，真正是在某一个或者多个数据文件中。而一个表空间可以由一个或多个数据文件组成，一个数据文件只能属于一个表空间。一旦数据文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数据文件，只能删除其所属于的表空间才行。

4.表空间

表空间是 Oracle 对物理数据库上相关数据文件（ORA 或者 DBF 文件）的逻辑映射。一个数据库在逻辑上被划分成一到若干个表空间，每个表空间包含了在逻辑上相关联的一组结构。每个数据库至少有一个表空间(称之为 system 表空间)。

每个表空间由同一磁盘上的一个或多个文件组成，这些文件叫数据文件(datafile)。一个数据文件只能属于一个表空间。

注：表的数据，是有用户放入某一个表空间的，而这个表空间会随机把这些表数据放到一个或者多个数据文件中。

由于 oracle 的数据库不是普通的概念，oracle 是有用户和表空间对数据进行管理和存放的。但是表不是有表空间去查询的，而是由用户去查的。因为不同用户可以在同一个表空间建立同一个名字的表！这里区分就是用户了！

5.用户

用户是在表空间下建立的。用户登陆后只能看到和操作自己的表, ORACLE的用户与 MYSQL 的数据库类似，每建立一个应用需要创建一个用户。

二、ORACLE 安装与配置

（一）VMware 挂载 windows server 2003

锁频密码：amani

（二）网络配置

（三）安装 ORACLE 数据库

口令：root

cmd输入：sqlplus system/root

SQL：select * from tabs; 查询所有表

（四）SQLPlus 远程连接 ORACLE 数据库

远程连接

远程连接工具：D:\Program Files\Oracle\instantclient\instantclient_21_3

D:\Program Files\Oracle\instantclient\instantclient_21_3>sqlplus system/root@192.168.100.125:1521/orcl

SQL*Plus: Release 12.1.0.1.0 Production on Wed Sep 6 14:58:38 2023

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select * from tabs;

（五）PLSQL Developer 安装与配置

（1）安装 PLSQL Developer 15

（2）配置客户端路径

D:\Program Files\Oracle\instantclient\instantclient_21_3

D:\Program Files\Oracle\instantclient\instantclient_21_3\oci.dll

（3） 编辑 ORA 文件

C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora

复制映射下面路径

D:\Program Files\Oracle\tnsnames.ora

ORCL10 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.125)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

（4） 设置环境变量 TNS_ADMIN

为 D:\Program Files\Oracle( tnsnames.ora 所在目录 )

（5）进入系统

输入用户名 system 密码 root即可进入到软件主界面

问题1：

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

ERROR:ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务
解决1：

ORA-12514 TNS 监听程序当前无法识别连接描述符中请求服务 的解决方法

问题2：

ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Process ID: 0
Session ID: 0 Serial number: 0

解决2：
ORA-01034:ORACLE not available ORA-27101解决方法
解决：ORA-01034: ORACLE not available ORA-27101（推荐，实测有效）

操作步骤：

1 启动监听

lsnrctl start

2 设置实例

set ORACLE_SID=orcl

3 连接

sqlplus  /nolog
conn / as sysdba

4 启动

startup
shutdown immediate
startup

5 测试

select * from user_tables;

总结：

sqlplus /nolog
conn / as sysdba
startup

（6）中文编码设置

select userenv(‘language’) from dual

我实际查到的结果为:AMERICAN_AMERICA.ZHS16GBK

计算机->属性->高级系统设置->环境变量->新建

设置变量名:NLS_LANG,变量值:第 1 步查到的值，

我的是

AMERICAN_AMERICA.ZHS16GBK

三、项目案例：《自来水公司收费系统》

（一）项目介绍与需求分析

（二）表结构设计

（三）创建表空间

-- 创建表空间
create tablespace waterboss
datafile 'C:\oracle\dbf\waterboss.dbf'
size 100m
autoextend on
next 10m;

解释：
waterboss 为表空间名称
datafile 用于设置物理文件名称
size 用于设置表空间的初始大小
autoextend on 用于设置自动增长，如果存储量超过初始大小，则开始自动扩容
next 用于设置扩容的空间大小

（四）创建用户

-- 创建用户 
create user wateruser
identified by amani
default tablespace waterboss;

解释：
wateruser 为创建的用户名
identified by 用于设置用户的密码
default tablesapce 用于指定默认表空间名称

（五）用户赋权

-- 用户赋权
grant dba to wateruser;

给用户 wateruser 赋予 DBA 权限后即可会话登陆

四、表的创建、修改与删除

（一）创建表

语法：

CREATE TABLE 表名称(
字段名 类型(长度) primary key,
字段名 类型(长度),
.......
);

数据类型：

1. 字符型
   （1）CHAR : 固定长度的字符类型，最多存储 2000 个字节
   （2）VARCHAR2 :可变长度的字符类型，最多存储 4000 个字节
   （3）LONG : 大文本类型。最大可以存储 2 个 G
2. 数值型
   NUMBER : 数值类型
   例如：NUMBER(5) 最大可以存的数为 99999
   NUMBER(5,2) 最大可以存的数为 999.99
3. 日期型
   （1）DATE：日期时间型，精确到秒
   （2）TIMESTAMP：精确到秒的小数点后 9 位
4. 二进制型（大数据类型）
   （1）CLOB : 存储字符,最大可以存 4 个 G
   （2）BLOB：存储图像、声音、视频等二进制数据,最多可以存 4 个 G

实例：
创建业主表

-- 创建业主表
create table t_owners
(
id number primary key,
name varchar2(30),
addressid number,
housenumber varchar2(30),
watermeter varchar2(30),
adddate date,
ownertypeid number
);

（二）修改表

1. 增加字段语法:

ALTER TABLE 表名称 ADD(列名 1 类型 [DEFAULT 默认值]，列名 1 类型
[DEFAULT 默认值]...)
为业主表增加两个字段，语句：
--追加字段
ALTER TABLE T_OWNERS ADD
(
REMARK VARCHAR2(20),
OUTDATE DATE
)

2. 修改字段语法：

ALTER TABLE 表名称 MODIFY(列名 1 类型 [DEFAULT 默认值]，列名 1 类型
[DEFAULT 默认值]...)
修改两个字段的类型，语句：
--修改字段
ALTER TABLE T_OWNERS MODIFY
(
REMARK CHAR(20),
OUTDATE TIMESTAMP
)

3. 修改字段名语法：

ALTER TABLE 表名称 RENAME COLUMN 原列名 TO 新列名
语句：
ALTER TABLE T_OWNERS RENAME COLUMN OUTDATE TO EXITDATE

4. 删除字段名

--删除一个字段
ALTER TABLE 表名称 DROP COLUMN 列名
--删除多个字段
ALTER TABLE 表名称 DROP (列名 1,列名 2...)

语句：

--删除字段
ALTER TABLE T_OWNERS DROP COLUMN REMARK

（三）删除表

语法：

DROP TABLE 表名称

五、数据增删改

（一）插入数据

语法：

INSERT INTO 表名[(列名 1，列名 2，...)] VALUES(值 1，值 2，...)

执行 INSERT 后一定要再执行 commit 提交事务

向业主表插入数据：

INSERT INTO T_OWNERS VALUES (1,' 张三丰',1,'2-2','5678',sysdate,1);

语句中的 sysdate 是系统变量用于获取当前日期，点击齿轮的图标后，再点击下
图的绿色图标，此图标为 commit

我们再次录入一条数据，语句如下:

INSERT INTO T_OWNERS VALUES (2,'赵大侃',1,'2-3','9876',sysdate,1);
commit;

（二）修改数据

语法：

UPDATE 表名 SET 列名 1=值 1，列名 2=值 2，....WHERE 修改条件；

执行 UPDATE 后一定要再执行 commit 提交事务

需求：将 ID 为 1 的业主的登记日期更改为三天前的日期

update T_OWNERS set adddate=adddate-3 where id=1;
commit;

（三）删除数据

语法 1：

DELETE FROM 表名 WHERE 删除条件;

执行 DELETE 后一定要再执行 commit 提交事务

需求：删除业主 ID 为 2 的业主信息

delete from T_OWNERS where id=2;
commit;

语法 2：

TRUNCATE TABLE 表名称

比较 truncat 与 delete 实现数据删除？

1. delete 删除的数据可以 rollback
2. delete 删除可能产生碎片，并且不释放空间
3. truncate 是先摧毁表结构，再重构表结构

六、JDBC 连接 ORACLE

（一）创建工程，引入驱动包

在下图目录中可以找到驱动包 ORACLE 的 JDBC 驱动包，拷贝到工程即可使用

C:\oracle\product\10.2.0\db_1\jdbc\lib\ojdbc14.jar

（二）BaseDao

public class BaseDao {

    //加载驱动
    static {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取数据库连接
     */
    public static java.sql.Connection getConnection() throws SQLException {
        return java.sql.DriverManager.getConnection("jdbc:oracle:thin:@192.168.100.125:1521:orcl",
                "wateruser", "amani");
    }

    /**
     * 关闭资源
     */
    public static void closeAll(java.sql.ResultSet rs, java.sql.Statement stmt, java.sql.Connection conn) {
        //关闭结果集
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        //关闭执行对象
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        //关闭执行对象
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

JDBC 驱动为：
oracle.jdbc.OracleDriver
连接字符串( 瘦连接 )：
jdbc:oracle:thin:@虚拟机的 IP:1521:orcl

（三）业主增删改代码编写

1.创建实体类

/**
 * 业主实体类
 */
public class Owners {
	private Long id;//编号
	private String name;//业主名称
	private Long addressid;//地址编号
	private String housenumber;//门牌号
	private String watermeter;//水表编号
	private Date adddate;//登记日期
	private Long ownertypeid;//业主类型ID

	public Long getId() {
		return id;
	}
	public void setId(Long id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public Long getAddressid() {
		return addressid;
	}
	public void setAddressid(Long addressid) {
		this.addressid = addressid;
	}
	public String getHousenumber() {
		return housenumber;
	}
	public void setHousenumber(String housenumber) {
		this.housenumber = housenumber;
	}
	public String getWatermeter() {
		return watermeter;
	}
	public void setWatermeter(String watermeter) {
		this.watermeter = watermeter;
	}
	public Date getAdddate() {
		return adddate;
	}
	public void setAdddate(Date adddate) {
		this.adddate = adddate;
	}
	public Long getOwnertypeid() {
		return ownertypeid;
	}
	public void setOwnertypeid(Long ownertypeid) {
		this.ownertypeid = ownertypeid;
	}
}

2.创建 Dao 类实现增删改

/**
 * 业主数据访问类
 */
public class OwnerDao {

        /**
         * 新增业主
         */
        public static void add(Owners owners) {

            java.sql.Connection conn = null;
            java.sql.PreparedStatement stmt = null;

            try {
                conn = BaseDao.getConnection();
                stmt = conn.prepareStatement("insert into T_OWNERS values(?,?,?,?,?,?,?)");
                stmt.setLong(1, owners.getId());
                stmt.setString(2, owners.getName());
                stmt.setLong(3, owners.getAddressid());
                stmt.setString(4, owners.getHousenumber());
                stmt.setString(5, owners.getWatermeter());
                stmt.setDate(6, new java.sql.Date(owners.getAdddate().getTime()));
                stmt.setLong(7, owners.getOwnertypeid());
                stmt.execute();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                BaseDao.closeAll(null, stmt, conn);
            }
        }

        /**
         * 更新业主信息
         */
        public static void update(Owners owners) {

            java.sql.Connection conn = null;
            java.sql.PreparedStatement stmt = null;

            try {
                conn = BaseDao.getConnection();
                stmt = conn.prepareStatement("update T_OWNERS set name=?,addressid=?,housenumber=?,"
                        + "watermeter=?,adddate=?, ownertypeid=? where id=?");

                stmt.setString(1, owners.getName());
                stmt.setLong(2, owners.getAddressid());
                stmt.setString(3, owners.getHousenumber());
                stmt.setString(4, owners.getWatermeter());
                stmt.setDate(5, new java.sql.Date(owners.getAdddate().getTime()));
                stmt.setLong(6, owners.getOwnertypeid());
                stmt.setLong(7, owners.getId());
                stmt.execute();
            } catch (SQLException e) {

                e.printStackTrace();
            } finally {
                BaseDao.closeAll(null, stmt, conn);
            }
        }

        /**
         * 删除业主信息
         */
        public static void delete(Long id) {

            java.sql.Connection conn = null;
            java.sql.PreparedStatement stmt = null;

            try {
                conn = BaseDao.getConnection();
                stmt = conn.prepareStatement("delete from T_OWNERS where id=?");
                stmt.setLong(1, id);
                stmt.execute();
            } catch (SQLException e) {

                e.printStackTrace();
            } finally {
                BaseDao.closeAll(null, stmt, conn);
            }
        }
}

七、数据导出与导入

当我们使用一个数据库时，总希望数据库的内容是可靠的、正确的，但由于计算机系统的故障（硬件故障、软件故障、网络故障、进程故障和系统故障）影响数据库系统的操作，影响数据库中数据的正确性，甚至破坏数据库，使数据库中全部或部分数据丢失。因此当发生上述故障后，希望能重构这个完整的数据库该处理称为数据库恢复，而要进行数据库的恢复必须要有数据库的备份工作。

（一）整库导出与导入

整库导出命令

exp system/root full=y

添加参数 full=y 就是整库导出

执行命令后会在当前目录下生成一个叫 EXPDAT.DMP，此文件为备份文件。
如果想指定备份文件的名称，则添加 file 参数即可，命令如下

exp system/root file=文件名 full=y

整库导入命令

imp system/root full=y

此命令如果不指定 file 参数，则默认用备份文件 EXPDAT.DMP 进行导入
如果指定 file 参数，则按照 file 指定的备份文件进行恢复

imp system/root full=y file=water.dmp

（二）按用户导出与导入

按用户导出

exp system/root owner=wateruser file=wateruser.dmp

按用户导入

imp system/root file=wateruser.dmp fromuser=wateruser

（三）按表导出与导入

按表导出

exp wateruser/amani file=a.dmp tables=T_OWNERS,T_OWNERSTYPE

用 tables 参数指定需要导出的表，如果有多个表用逗号分割即可
按表导入

imp wateruser/amani file=a.dmp tables=T_OWNERS,T_OWNERSTYPE