Oracle数据库介绍

Oracle是一种广泛使用的关系数据库管理系统（RDBMS），由甲骨文公司（Oracle Corporation）开发和销售。Oracle数据库在企业级应用中非常流行，主要因为其强大的性能、可靠性、安全性和可扩展性

Oracle数据库的基本概念

• 表（Table）： 存储数据的基本单位，由行和列组成
• 行（Row）： 表中的一条记录
• 列（Column）： 表中的一个字段，所有行都包含该字段
• 主键（Primary Key）： 表中的一个或多个列，唯一标识表中的每一行
• 外键（Foreign Key）： 表中的一列或多列，用于建立与另一表的联系
• 索引（Index）： 提高查询速度的数据结构

Oracle数据库的架构

• 实例（Instance）： Oracle数据库运行的背景进程和内存结构的组合。
• 数据库（Database）： 存储数据文件、控制文件、日志文件等的物理结构。
• 表空间（Tablespace）： Oracle数据库的逻辑存储单元，由一个或多个数据文件组成。
• 数据文件（Datafile）： 存储实际数据的物理文件。
• 控制文件（Control File）： 存储数据库的结构信息和状态信息。
• 重做日志文件（Redo Log File）： 记录所有对数据库所做的更改，用于数据恢复。

基础命令

用户管理

创建用户并设置密码

CREATE USER username IDENTIFIED BY password;

授予权限；所有权限，如果指定权限（GRANT SELECT, INSERT, UPDATE, DELETE）

GRANT ALL PRIVILEGES TO username;

创建角色

CREATE ROLE role_name;

授予角色权限

GRANT CREATE SESSION, CREATE TABLE TO role_name;

将角色授予用户

GRANT role_name TO username;

修改用户密码

ALTER USER username IDENTIFIED BY new_password;

删除用户（如果用户拥有对象，可以使用CASCADE选项）

DROP USER username CASCADE;

查看当前用户

SELECT USER FROM dual;

查看所有用户

SELECT username FROM all_users;

查看用户的角色

SELECT * FROM dba_role_privs WHERE grantee = 'username';

表管理

可创建的字段类型如下

• 数值类型
  NUMBER(p, s): 精确数字类型。p 表示总位数，s 表示小数位数。例如，NUMBER(10, 2) 表示总共10位，其中2位为小数位。
  INTEGER: 整型，等同于 NUMBER(38).
  FLOAT: 浮点型，实际上也是 NUMBER 的一种，主要用于科学计数法。
• 字符串类型
  CHAR(n): 固定长度的字符类型。n 表示字符长度，例如 CHAR(10)。
  VARCHAR2(n): 可变长度的字符类型，n 表示最大长度，例如 VARCHAR2(50)。
  CLOB: 用于存储大文本数据。
• 日期和时间类型
  DATE: 日期和时间类型，精确到秒。
  TIMESTAMP: 日期和时间类型，精确到小数秒。
  TIMESTAMP WITH TIME ZONE: 包含时区的时间戳。
  TIMESTAMP WITH LOCAL TIME ZONE: 在本地时区中存储时间戳，但在数据库中以标准时区存储。
• 二进制类型
  RAW(n): 固定长度的二进制数据。
  LONG RAW: 存储大量二进制数据，较少使用，已被 BLOB 取代。
  BLOB: 大型二进制对象，用于存储大容量二进制数据，如图像和视频。
• 其他类型
  ROWID: 表示行的唯一标识符。
  UROWID: 可以表示索引行标识符或表行标识符。
• 空间数据类型
  SDO_GEOMETRY: 用于存储空间数据

支持的约束如下

• 主键约束 (PRIMARY KEY): 唯一标识每一行，不能重复或为空。
• 唯一约束 (UNIQUE): 确保列中的值唯一，但允许空值。
• 外键约束 (FOREIGN KEY): 确保列的值在另一个表的主键中存在。
• 检查约束 (CHECK): 验证列的值符合指定的条件。
• 非空约束 (NOT NULL): 确保列的值不能为空。
• 默认值约束 (DEFAULT): 为列指定默认值，如果插入时未提供值，则使用默认值。

创建表

CREATE TABLE table_name (
  column1 datatype 约束,
  column2 datatype 约束,
  ...
);

创建带有外键的表

CREATE TABLE orders (
  order_id NUMBER PRIMARY KEY,
  order_date DATE,
  customer_id NUMBER,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

分区 (Partitioning)
分区是一种将大型表或索引划分为多个较小、独立的部分的技术。每个部分称为分区。分区可以提高查询性能、管理效率以及数据可用性。

• 范围分区 (Range Partitioning): 按照列值的范围将数据分区。例如，按照日期范围分区。
  列表分区 (List Partitioning): 根据列值的离散列表将数据分区。例如，按照地区代码分区。
  哈希分区 (Hash Partitioning): 使用哈希函数将数据分布到不同的分区，以平衡数据量。
  复合分区 (Composite Partitioning): 结合多种分区方法。例如，首先按范围分区，再按列表分区。

设有一个销售记录表 sales，按年分区的示例如下

CREATE TABLE sales (
    sale_id NUMBER,
    sale_date DATE,
    amount NUMBER
)
PARTITION BY RANGE (sale_date) (
    PARTITION p_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
    PARTITION p_2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')),
    PARTITION p_2025 VALUES LESS THAN (TO_DATE('2026-01-01', 'YYYY-MM-DD'))
);

在这个例子中，sales 表按 sale_date 列的年份进行分区，每年一个分区。

索引 (Index)
索引是一个数据库对象，用于提高对表中数据的检索速度。它是根据一个或多个列创建的，通常提高查询性能，但可能会增加数据修改的开销。

• B树索引 (B-Tree Index): 默认的索引类型，适用于大多数查询，提供快速的数据检索。
  位图索引 (Bitmap Index): 适用于列值较少的情况，通过位图表示每个值，适合多条件查询。
  唯一索引 (Unique Index): 确保索引列中的值唯一，通常用于保证数据的唯一性。
  反向键索引 (Reverse Key Index): 通过反转键值来分散索引，从而避免热点问题。
  函数索引 (Function-Based Index): 基于列值的函数结果创建索引，用于加速特定类型的查询。

索引示例
B树索引
创建一个普通的 B树 索引以加速对 sales 表中 sale_date 列的查询：

CREATE INDEX idx_sales_date ON sales (sale_date);

唯一索引
假设我们需要确保 sale_id 列中的值唯一：

CREATE UNIQUE INDEX idx_unique_sale_id ON sales (sale_id);

位图索引
如果 sales 表中的 amount 列值种类不多（例如，amount 是 low, medium, high），可以创建一个位图索引：

CREATE BITMAP INDEX idx_sales_amount ON sales (amount);

反向键索引
用于避免热点问题的反向键索引示例：

CREATE INDEX idx_sales_reverse_date ON sales (sale_date) REVERSE;

函数索引
假设我们频繁查询销售记录的年份，可以基于年份创建函数索引：

CREATE INDEX idx_sales_year ON sales (EXTRACT(YEAR FROM sale_date));

修改表

• 添加列

ALTER TABLE table_name ADD (column_name datatype constraint);

• 修改列的数据类型

ALTER TABLE table_name MODIFY (column_name new_datatype);

• 删除列

ALTER TABLE table_name DROP COLUMN column_name;

• 重命名列

ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;

删除表

DROP TABLE table_name;

管理数据

• 插入数据

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

• 更新数据

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

删除数据，可使用where指定条件

DELETE FROM table_name WHERE condition;

清空数据（比delete更快，但是不能指定条件，只能全部清空，会保留字段）

TRUNCATE TABLE table_name;

查看表结构

DESC table_name;

DESCRIBE table_name;

查看表结构（比较全面）column_name和datatype是列名和字段类型，*可查看更多

SELECT column_name,DATA_TYPE
FROM all_tab_columns
WHERE table_name = 'table_name'
AND owner = 'database_name'

查看表数据

SELECT * FROM table_name;

重命名表

ALTER TABLE old_table_name RENAME TO new_table_name;

复制表，连同数据和字段

CREATE TABLE 备份的表名 AS 
  SELECT * FROM 被备份的表名;

复制表，只复制表结构

CREATE TABLE 复制的表名 AS
SELECT * FROM 被复制的表名
WHERE 1=0;