Oracle数据库入门知识点汇集_oracle知识点

第一章

一、ORACLE简介

（一）什么是ORACLE

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

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

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

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安装与配置

这里主要是在Window10上安装Oracle，Linux系统稍复杂不在这里阐

（一）Oracle数据库的下载

这里下载的版本为Oracle 11g 这里推荐网盘下载，官网下载非常慢
百度网盘下载： 下载地址

（二）Oracle数据库的安装

这里推荐一下我在B站看到的一个Oracle安装视频
数据库安装视频链接：点击观看

（三）PLSQL的安装

plsql指的是plsqldeveloper 这个Oracle数据库客户端连接工具（百度网盘已提供plsql的最新版）
软件安装视频链接：点击观看
软件配置视频链接：点击观看

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

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

  XXX 市自来水公司为更好地对自来水收费进行规范化管理，决定委托科技公司开发《自来水公司收费系统》。考虑到自来水业务数量庞大， 数据并发量高，决定数据库采用 ORACLE 数据库。主要功能包括：
1.、基础信息管理：
（1）业主类型设置
（2）价格设置
（3）区域设置
（4）收费员设置
（5）地址设置
2、业主信息管理：
（1）业主信息维护
（2）业主信息查询
3、收费管理：
（1）抄表登记
（2）收费登记
（3）收费记录查询
（4）欠费用户清单
4、统计分析：
（1）收费日报单
（2）收费月报表
…

（二）表结构设计

1.业主类型表（T_OWNERTYPE)

2.价格表（T_PRICETABLE)

3.区域表（T_AREA)

4.收费员表（T_OPERATOR)

5.地址表（T_ADDRESS)

6.业主表（T_OWNERS)

7.收费台账（T_ACCOUNT）

上述 7 张表的物理模型如下：

（三）创建表空间

create tablespace waterboss
datafile 'D:\app\oracleData\waterboss.dbf' 
size 100m
autoextend on
next 10m
1
2
3
4
5

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

运行后在D:\app\oracleData的目录下能看到生成的waterboss.dbf文件，大小为100m

（四）创建用户

create user wateruser
identified by 123456
default tablespace waterboss
1
2
3

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

运行后可以看到Users中新增加了一个用户wateruser

（五）用户赋权

grant dba to wateruser
1

解释：
给用户wateruser赋予DBA权限后即可登录

  使用新创建的wateruser用户来登录，弹出错误信息，显示没有赋予用户创建会话的权限，原因是因为我们在创建完用户后没有给用户赋予权限。


赋予权限后，重新登录，显示正在登录即成功，进入界面后显示当前用户为wateruser

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

（一）创建表

语法：

create table 表名称(
  字段名 类型(长度) primary key,
  字段名 类型(长度)，
	......
);
1
2
3
4
5

数据类型：
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）BLOG：存储图像、声音、视频等二进制数据，最多可以存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
2
3
4
5
6
7
8
9
10

创建业主类型表:

create table T_OWNERTYPE
(
  id number primary key,
  name varchar2(30)
);
1
2
3
4
5

创建价格表：

create table T_PRICETABLE
(
  id number primary key,
  price varchar2(30),
  ownertypeid number,
  minnum number(10,2),
  maxnum number(10,2)
);

1
2
3
4
5
6
7
8
9

创建区域表：

create table T_AREA
(
  id number primary key,
  name varchar2(30)
)
1
2
3
4
5

创建收费员表：

create table T_OPERATOR
(
  id number primary key,
  name varchar2(30)
);

1
2
3
4
5
6

创建地址表：

create table T_ADDRESS
(
  id number primary key,
  name varchar2(30),
  areaid number,
  operatorid number
);

1
2
3
4
5
6
7
8

创建收费台账表：

create table T_ACCOUNT
(
  id number primary key,
  owneruuid number,
  ownertype number,
  areaid number,
  year char(4),
  month char(2),
  num0 number,
  num1 number,
  usenum number,
  meteruser number,
  meterdate date,
  money number(10,2),
  isfee char(1),
  feedate date,
  feeuser number
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

（二）修改表

1.增加字段语法

ALTER TABLE 表名称 ADD(列名  类型 [DEFAULT 默认值], 列名  类型 
                    [DEFAULT 默认值] ...)
1
2

实例：
为业主表增加两个字段，语句：

ALTER TABLE T_OWNERS ADD
(
  REMARK VARCHAR2(20),
  OUTDATE DATE
)
1
2
3
4
5

2.修改字段语法

ALTER TABLE 表名称 MODIFY(列名  类型 [DEFAULT 默认值], 列名  类型 
                    [DEFAULT 默认值] ...)
1
2

实例：
修改两个字段语法：

ALTER TABLE T_OWNERS MODIFY
(
REMARK CHAR(20),
OUTDATE TIMESTAMP
)
1
2
3
4
5

3.修改字段名语法

ALTER TABLE 表名称 MODIFY(列名  类型 [DEFAULT 默认值]，列名  类型
									[DEFAULT 默认值]...)
1
2

实例：
修改表的一个字段名

ALTER TABLE T_OWNERS RENAME COLUMN OUTDATE TO EXITDATE
1

4.删除字段名语法

-- 删除一个字段
ALTER TABLE 表名称 DROP COLUMN 列名 

-- 删除多个字段
ALTER TABLE 表名称 DROP (列名1 ，列名2...) 
1
2
3
4
5

实例：
删除表的两个字段

ALTER TABLE T_OWNERS DROP (REMARK,EXITDATE)
1

（三）删除表

语法：

DROP TABLE 表名称
1

实例：

DROP TABLE T_OWNERS
1

五、数据增删改

（一）插入数据

语法：

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

实例：
向业主表插入数据：

INSERT INTO T_OWNERS VALUES(1,'吴彦祖',1,'1-1','5678',sysdate,1)

-- 提交事务
commit;
1
2
3
4

解释：
语句中的sysdate是系统变量用于获取当前日期，以下三个图标从左到右依次为**运行、提交事务、回滚事务，**点击运行后需要点击提交事务

注意：执行INSERT后一定要在执行commit提交事务

（二）修改数据

语法：

UPDATE 表名 SET 列名1=值1,列名2=值2,....WHERE 修改条件;
1

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

UPDATE T_OWNERS SET adddate=adddate-3 WHERE id=1;
1

（三）删除数据

语法1：

DELETE FROM 表名 WHERE 删除条件
1

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

实例：
删除业主ID为2的业主信息

DELETE FROM T_OWNERS WHERE ID=2;
1

语法2：

TRUNCATE TABLE 表名称
1

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

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

六、JDBC连接ORACLE

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

  这边由于是在window系统的电脑上安装的ORACLE数据库，所以在下图目录中库中可以找到驱动包ORACLE的JDBC驱动包，拷贝到工程即可使用。

  使用IDEA创建java项目Oracle，建立lib文件夹，将ojdbc5.jar拷贝到此文件夹，然后右键jar包选择add到库，具体项目架构如下：

（二）创建BaseDao

我们通常编写 BaseDao 负责加载驱动，获取数据库连接，关闭资源，代码如下：

package com.rich.dao;

import java.sql.SQLException;
/**
 * 基本数据访问类
 * @author Administrator
 *
 */
public class BaseDao {
    //加载驱动
    static {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

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

    /**
     * 关闭资源
     *
     * @param rs
     * @param stmt
     * @param conn
     */
    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();
            }
        }
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67

解释：
JDBC驱动为：
oracle.jdbc.OraclerDriver
连接字符串(瘦连接):
jdbc:oracle:thin:@IP:1521:orcl

注意：如何查看IP？
控制台获取oracle数据库IP地址和端口号**(我的是安装在本地，所以是127.0.0.1**）

  在瘦连接中，使用的是纯Java编写的数据库访问接口，不需要额外安装Oracle客户端，只需在classpath中包含相应的JDBC驱动程序的jar包即可。这种连接方式可以通过TCP/IP协议与数据库服务器通信，适用于较为简单的数据库操作和有限的资源环境。
  而在胖连接中，使用的是Oracle客户端提供的接口（OCI），需要在客户端安装和配置Oracle客户端软件。胖连接利用本机的Oracle客户端与数据库进行通信，具有更高的执行效率和更丰富的功能，适用于复杂的数据处理和高性能要求的场景。

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

1. 创建实体类

package com.rich.pojo;

import java.util.Date;
/**
 * 业主实体类
 * @author Administrator
 *
 */
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;
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60

2. 创建Dao类实现增删改

package com.rich.dao;

import com.rich.pojo.Owners;

import java.sql.SQLException;


/**
 * 业主数据访问类
 * @author Administrator
 *
 */
public class OwnersDao {
    /**
     * 新增业主
     * @param owners
     */
    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);
        }
    }
    /**
     * 修改业主
     * @param owners
     */
    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);
        }
    }

    /**
     * 删除业主
     * @param id
     */
    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);
        }
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85

3. 测试（这里采用不太正式的写法）

新建一个testAdd类来测试添加业主

package com.rich.test;

import com.rich.dao.OwnersDao;
import com.rich.pojo.Owners;

import java.util.Date;

public class testAdd {
    public static void main(String[] args) {
        Owners owners = new Owners();
        owners.setId(2L);
        owners.setName("rich哥");
        owners.setAddressid(2L);
        owners.setHousenumber("2-3");
        owners.setWatermeter("23");
        owners.setAdddate(new Date());
        owners.setOwnertypeid(2L);

        OwnersDao.add(owners);
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

新建一个testUpdate类来测试更新业主

package com.rich.test;

import com.rich.dao.OwnersDao;
import com.rich.pojo.Owners;

import java.util.Date;

public class testUpdate {
    public static void main(String[] args) {
        Owners owners = new Owners();
        owners.setName("rich哥");
        owners.setAddressid(2L);
        owners.setHousenumber("6-6");
        owners.setWatermeter("23");
        owners.setAdddate(new Date());
        owners.setOwnertypeid(2L);
        owners.setId(2L);

        OwnersDao.update(owners);
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

新建一个testDel类来测试删除新添加的业主

package com.rich.test;

import com.rich.dao.OwnersDao;

public class testDel {
    public static void main(String[] args) {
        OwnersDao.delete(2L);
    }
}
1
2
3
4
5
6
7
8
9

七：数据导入与导出

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

（一）整库导出与导入

整库导出命令

exp 用户名/密码 full=y
1

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

执行命令后会在当前目录下生产一个叫EXPDAT.DMP，此文件为备份文件。

如果想指定备份文件的名称，则添加file参数即可，命令如下

exp 用户名/密码 file=文件名 full=y
1

整库导入命令

imp 用户名/密码 full=y
1

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

imp 用户名/密码 full=y file=water.dmp
1

（二）按用户导出与导入

按用户导出

exp 用户名/密码 owner=wateruser file=wateruser.dmp
1

按用户导入

imp 用户名/密码 file=wateruser.dmp formuser=wateruser
1

（三）按表导出与导入

按表导出

exp 用户名/密码 file=a.dmp tables=t_account,a_area
1

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

按表导入

imp 用户名/密码 file=a.dmp tables=t_account,a_area
1