Oracle 11g数据库常用对象创建与管理方法详解
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">引言</a></li><li><a href="#_label1">一、表（Table）：数据的基石</a></li><ul class="second_class_ul"><li><a href="#_lab2_1_0">1. 创建表</a></li><li><a href="#_lab2_1_1">2. 管理表</a></li></ul><li><a href="#_label2">二、视图（View）：虚拟的逻辑窗口</a></li><ul class="second_class_ul"><li><a href="#_lab2_2_2">1. 创建视图</a></li><li><a href="#_lab2_2_3">2. 管理视图</a></li></ul><li><a href="#_label3">三、序列（Sequence）：自动编号发生器</a></li><ul class="second_class_ul"><li><a href="#_lab2_3_4">1. 创建序列</a></li><li><a href="#_lab2_3_5">2. 使用序列</a></li><li><a href="#_lab2_3_6">3. 管理序列</a></li></ul><li><a href="#_label4">四、索引（Index）：加速查询的引擎</a></li><ul class="second_class_ul"><li><a href="#_lab2_4_7">1. 创建索引</a></li><li><a href="#_lab2_4_8">2. 管理索引</a></li></ul><li><a href="#_label5">五，作业</a></li><ul class="second_class_ul"><li><a href="#_lab2_5_9">1.Views表：</a></li><li><a href="#_lab2_5_10">2、表：Tweets</a></li><li><a href="#_lab2_5_11">3、表：Visits</a></li></ul><li><a href="#_label6">总结 </a></li><ul class="second_class_ul"></ul></ul></div><p class="maodian"><a name="_label0"></a></p><h2>引言</h2><p>在Oracle数据库的浩瀚世界里，数据本身固然重要，但如何高效地组织、访问和管理这些数据，才是发挥其强大威力的关键。这一切都离不开数据库对象。无论是初入行的DBA还是后端开发人员，熟练掌握Oracle常用对象的创建与管理都是一项核心技能。</p>
<p>本文将带您系统地了解Oracle 11g中几种最常用的数据库对象，包括<strong>表、视图、序列、索引和同义词</strong>。我们将通过清晰的语法示例和实用的管理技巧，助您夯实基础，提升数据库操作能力。</p>
<p class="maodian"><a name="_label1"></a></p><h2>一、表（Table）：数据的基石</h2>
<p>表是数据库中存储数据的基本单位，由行和列组成。设计良好的表结构是高效数据库系统的前提。</p>
<p class="maodian"><a name="_lab2_1_0"></a></p><h3>1. 创建表</h3>
<p>使用 <code>CREATE TABLE</code> 语句，你需要定义列名、数据类型和约束。</p>
<div class="jb51code"><pre class="brush:sql;">CREATE TABLE employees (
employee_id NUMBER(6) PRIMARY KEY,
first_name VARCHAR2(20),
last_name VARCHAR2(25)NOT NULL,
email VARCHAR2(25)NOT NULL UNIQUE,
hire_date DATE DEFAULT SYSDATE NOT NULL,
salary NUMBER(8,2),
department_idNUMBER(4),
-- 定义外键约束，关联到部门表
CONSTRAINT fk_dept_id
FOREIGN KEY (department_id)
REFERENCES departments(department_id)
);</pre></div>
<p><strong>关键点：</strong></p>
<ul><li><p><strong>数据类型：</strong> <code>NUMBER</code>, <code>VARCHAR2</code>, <code>DATE</code>, <code>CLOB</code>, <code>BLOB</code> 等。</p></li><li><p><strong>约束：</strong> <code>PRIMARY KEY</code>, <code>FOREIGN KEY</code>, <code>NOT NULL</code>, <code>UNIQUE</code>, <code>CHECK</code>。约束保证了数据的完整性和一致性。</p></li></ul>
<p class="maodian"><a name="_lab2_1_1"></a></p><h3>2. 管理表</h3>
<ul><li><p><strong>修改表（ALTER TABLE）：</strong> 用于添加、修改或删除列，以及添加或删除约束。</p></li></ul>
<div class="jb51code"><pre class="brush:sql;">-- 添加新列
ALTER TABLE employees ADD (phone_number VARCHAR2(15));
-- 修改列数据类型
ALTER TABLE employees MODIFY (salary NUMBER(9,2));
-- 删除列
ALTER TABLE employees DROP COLUMN phone_number;
-- 添加约束
ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary > 0);</pre></div>
<ul><li><strong> 删除表（DROP TABLE）：</strong></li></ul>
<div class="jb51code"><pre class="brush:sql;">DROP TABLE employees;
-- 谨慎使用！会删除表结构和所有数据。
DROP TABLE employees CASCADE CONSTRAINTS;
-- 同时删除与之相关的引用完整性约束</pre></div>
<ul><li><strong>重命名表（RENAME）：</strong></li></ul>
<div class="jb51code"><pre class="brush:sql;">RENAME employees TO emp_backup;
</pre></div>
<ul><li><strong>截断表（TRUNCATE TABLE）：</strong> 快速删除表中所有数据，不可回滚，并释放表空间。</li></ul>
<div class="jb51code"><pre class="brush:sql;">TRUNCATE TABLE emp_backup;</pre></div>
<p class="maodian"><a name="_label2"></a></p><h2>二、视图（View）：虚拟的逻辑窗口</h2>
<p>视图是基于一个或多个表的查询结果集。它本身不存储数据，像一个预定义的查询窗口，简化了复杂查询，增强了数据安全性。</p>
<p class="maodian"><a name="_lab2_2_2"></a></p><h3>1. 创建视图</h3>
<div class="jb51code"><pre class="brush:sql;">CREATE OR REPLACE VIEW vw_emp_dept AS
SELECT e.employee_id,
e.first_name || ' ' || e.last_name AS full_name,
e.salary,
d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHEREe.salary > 10000;
</pre></div>
<p class="maodian"><a name="_lab2_2_3"></a></p><h3>2. 管理视图</h3>
<ul><li><p><strong>查询视图：</strong> 像查询普通表一样。</p></li></ul>
<div class="jb51code"><pre class="brush:sql;">SELECT * FROM vw_emp_dept;</pre></div>
<ul><li><strong>删除视图：</strong></li></ul>
<div class="jb51code"><pre class="brush:sql;">DROP VIEW vw_emp_dept;</pre></div>
<p><strong>优点：</strong></p>
<ul><li><p><strong>简化操作：</strong> 将复杂的多表查询封装成一个简单的视图。</p></li><li><p><strong>安全性：</strong> 可以只暴露视图中的特定列给用户，隐藏敏感数据。</p></li><li><p><strong>逻辑独立性：</strong> 即使底层表结构发生变化，只需修改视图定义，而不影响应用程序。</p></li></ul>
<p class="maodian"><a name="_label3"></a></p><h2>三、序列（Sequence）：自动编号发生器</h2>
<p>序列是一个数据库对象，用于生成唯一的、连续的整数编号，通常为主键字段提供值。</p>
<p class="maodian"><a name="_lab2_3_4"></a></p><h3>1. 创建序列</h3>
<div class="jb51code"><pre class="brush:sql;">CREATE SEQUENCE seq_emp_id
INCREMENT BY 1 -- 每次增加1
START WITH 1000 -- 从1000开始
NOMAXVALUE -- 无最大值（或 MAXVALUE 9999）
NOCYCLE -- 不循环
CACHE 20; -- 缓存20个序列值以提高性能</pre></div>
<p class="maodian"><a name="_lab2_3_5"></a></p><h3>2. 使用序列</h3>
<ul><li><p><code>NEXTVAL</code>: 获取序列的下一个值。</p></li><li><p><code>CURRVAL</code>: 获取序列的当前值（必须先使用 <code>NEXTVAL</code>）。</p></li></ul>
<div class="jb51code"><pre class="brush:sql;">INSERT INTO employees (employee_id, first_name, last_name, email)
VALUES (seq_emp_id.NEXTVAL, '张', '三', 'zhangsan@example.com');
SELECT seq_emp_id.CURRVAL FROM dual;
</pre></div>
<p class="maodian"><a name="_lab2_3_6"></a></p><h3>3. 管理序列</h3>
<div class="jb51code"><pre class="brush:sql;">-- 修改序列（不能修改START WITH，通常用于修改增量、缓存值等）
ALTER SEQUENCE seq_emp_id INCREMENT BY 2;
-- 删除序列
DROP SEQUENCE seq_emp_id;</pre></div>
<p class="maodian"><a name="_label4"></a></p><h2>四、索引（Index）：加速查询的引擎</h2>
<p>索引是一种提高数据检索速度的数据库结构，类似于书的目录。</p>
<p class="maodian"><a name="_lab2_4_7"></a></p><h3>1. 创建索引</h3>
<ul><li><p><strong>单列索引：</strong></p></li></ul>
<div class="jb51code"><pre class="brush:sql;">CREATE INDEX idx_emp_last_name ON employees(last_name);</pre></div>
<ul><li><strong>复合索引：</strong></li></ul>
<div class="jb51code"><pre class="brush:sql;">CREATE INDEX idx_emp_dept_salary ON employees(department_id, salary DESC);</pre></div>
<ul><li><strong>唯一索引：</strong>（通常由UNIQUE或PRIMARY KEY约束自动创建，也可手动）</li></ul>
<div class="jb51code"><pre class="brush:sql;">CREATE UNIQUE INDEX idx_emp_email ON employees(email);</pre></div>
<p class="maodian"><a name="_lab2_4_8"></a></p><h3>2. 管理索引</h3>
<div class="jb51code"><pre class="brush:sql;">-- 重建索引（优化索引性能）
ALTER INDEX idx_emp_last_name REBUILD;
-- 删除索引
DROP INDEX idx_emp_last_name;</pre></div>
<p><strong>索引使用场景：</strong></p>
<ul><li><p>经常出现在 <code>WHERE</code>、<code>JOIN</code>、<code>ORDER BY</code> 子句中的列。</p></li><li><p>表的数据量很大。</p></li></ul>
<p><strong>注意事项：</strong></p>
<ul><li><p>索引会占用存储空间。</p></li><li><p>会降低 <code>INSERT</code>, <code>UPDATE</code>, <code>DELETE</code> 数据的速度，因为索引也需要维护。</p></li></ul>
<p class="maodian"><a name="_label5"></a></p><h2>五，作业</h2>
<p class="maodian"><a name="_lab2_5_9"></a></p><h3>1.Views表：</h3>
<table border="1" cellpadding="1" cellspacing="1"><tbody><tr><td>Column Name</td><td>Type</td></tr><tr><td>article_id</td><td>int</td></tr><tr><td>author_id</td><td>int</td></tr><tr><td>viewer_id </td><td>int</td></tr><tr><td>view_date</td><td>data</td></tr></tbody></table>
<p>此表可能会存在重复行。（换句话说，在 SQL 中这个表没有主键）</p>
<p>此表的每一行都表示某人在某天浏览了某位作者的某篇文章。</p>
<p>请注意，同一人的 author_id 和 viewer_id 是相同的。</p>
<p>请查询出所有浏览过自己文章的作者。</p>
<p>结果按照作者的 id 升序排列。</p>
<p>查询结果的格式如下所示：</p>
<p>输入：</p>
<div class="jb51code"><pre class="brush:sql;">-- 创建 Views 表
CREATE TABLE Views (
article_id INT,
author_id INT,
viewer_id INT,
view_date DATE
);
-- 插入示例数据
INSERT INTO Views (article_id, author_id, viewer_id, view_date) VALUES (1, 3, 5, DATE '2019-08-01');
INSERT INTO Views (article_id, author_id, viewer_id, view_date) VALUES (1, 3, 6, DATE '2019-08-02');
INSERT INTO Views (article_id, author_id, viewer_id, view_date) VALUES (2, 7, 7, DATE '2019-08-01');
INSERT INTO Views (article_id, author_id, viewer_id, view_date) VALUES (2, 7, 6, DATE '2019-08-02');
INSERT INTO Views (article_id, author_id, viewer_id, view_date) VALUES (4, 7, 1, DATE '2019-07-22');
INSERT INTO Views (article_id, author_id, viewer_id, view_date) VALUES (3, 4, 4, DATE '2019-07-21');
INSERT INTO Views (article_id, author_id, viewer_id, view_date) VALUES (3, 4, 4, DATE '2019-07-21');
-- 查询验证
SELECT * FROM Views;</pre></div>
<p>输入结果：</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202511/2025110511272732.png" /></p>
<p>输出：</p>
<div class="jb51code"><pre class="brush:sql;">SELECT DISTINCT author_id AS id
FROM Views
WHERE author_id = viewer_id
ORDER BY author_id;</pre></div>
<p>输出结果：</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202511/2025110511272769.png" /></p>
<p class="maodian"><a name="_lab2_5_10"></a></p><h3>2、表：Tweets</h3>
<table border="1" cellpadding="1" cellspacing="1"><tbody><tr><td>Column Name</td><td> Type</td></tr><tr><td>tweet_id</td><td>int</td></tr><tr><td>content</td><td> varchar</td></tr></tbody></table>
<p>在 SQL 中，tweet_id 是这个表的主键。</p>
<p>content 只包含字母数字字符，'!'，' '，不包含其它特殊字符。</p>
<p>这个表包含某社交媒体 App 中所有的推文。</p>
<p>查询所有无效推文的编号（ID）。当推文内容中的字符数严格大于 15 时，该推文是无效的。</p>
<p>以任意顺序返回结果表。</p>
<p>查询结果格式如下所示：</p>
<p>输入：</p>
<div class="jb51code"><pre class="brush:sql;">-- 创建 Tweets 表
CREATE TABLE Tweets (
tweet_id INT PRIMARY KEY,
content VARCHAR2(4000)
);
-- 插入示例数据
INSERT INTO Tweets (tweet_id, content) VALUES (1, 'Vote for Biden');
INSERT INTO Tweets (tweet_id, content) VALUES (2, 'Let us make America great again!');
-- 查询验证
SELECT * FROM Tweets;</pre></div>
<p>输入结果：</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202511/2025110511272732.png" /></p>
<p>输出：</p>
<div class="jb51code"><pre class="brush:sql;">SELECT tweet_id
FROM Tweets
WHERE LENGTH(content) > 15;</pre></div>
<p>输出结果：</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202511/2025110511272732.png" /></p>
<p class="maodian"><a name="_lab2_5_11"></a></p><h3>3、表：Visits</h3>
<table border="1" cellpadding="1" cellspacing="1"><tbody><tr><td>Column Name</td><td>Type</td></tr><tr><td>visit_id</td><td>int</td></tr><tr><td> customer_id</td><td>int</td></tr></tbody></table>
<p>visit_id 是该表中具有唯一值的列。</p>
<p>该表包含有关光临过购物中心的顾客的信息</p>
<table border="1" cellpadding="1" cellspacing="1"><tbody><tr><td>Column Name</td><td>Type</td></tr><tr><td>transaction_id</td><td>int</td></tr><tr><td>visit_id </td><td>int</td></tr><tr><td>amount</td><td>int</td></tr></tbody></table>
<p>transaction_id 是该表中具有唯一值的列。</p>
<p>此表包含 visit_id 期间进行的交易的信息。</p>
<p>有一些顾客可能光顾了购物中心但没有进行交易。请你编写一个解决方案，来查找这些顾客的 ID ，以及他们只光顾不交易的次数。</p>
<p>返回以 任何顺序 排序的结果表。</p>
<p>返回结果格式如下例所示。</p>
<p>输入：</p>
<div class="jb51code"><pre class="brush:sql;">-- 创建 Visits 表
CREATE TABLE Visits (
visit_id INT PRIMARY KEY,
customer_id INT
);
-- 创建 Transactions 表
CREATE TABLE Transactions (
transaction_id INT PRIMARY KEY,
visit_id INT,
amount INT
);
-- 插入 Visits 示例数据
INSERT INTO Visits (visit_id, customer_id) VALUES (1, 23);
INSERT INTO Visits (visit_id, customer_id) VALUES (2, 9);
INSERT INTO Visits (visit_id, customer_id) VALUES (4, 30);
INSERT INTO Visits (visit_id, customer_id) VALUES (5, 54);
INSERT INTO Visits (visit_id, customer_id) VALUES (6, 96);
INSERT INTO Visits (visit_id, customer_id) VALUES (7, 54);
INSERT INTO Visits (visit_id, customer_id) VALUES (8, 54);
-- 插入 Transactions 示例数据
INSERT INTO Transactions (transaction_id, visit_id, amount) VALUES (2, 5, 310);
INSERT INTO Transactions (transaction_id, visit_id, amount) VALUES (3, 5, 300);
INSERT INTO Transactions (transaction_id, visit_id, amount) VALUES (9, 5, 200);
INSERT INTO Transactions (transaction_id, visit_id, amount) VALUES (12, 1, 910);
INSERT INTO Transactions (transaction_id, visit_id, amount) VALUES (13, 2, 970);
-- 查询验证
SELECT * FROM Visits;
SELECT * FROM Transactions;</pre></div>
<p>输入结果：</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202511/2025110511272727.png" /></p>
<p>输出：</p>
<div class="jb51code"><pre class="brush:sql;">SELECT v.customer_id, COUNT(*) AS count_no_trans
FROM Visits v
LEFT JOIN Transactions t ON v.visit_id = t.visit_id
WHERE t.transaction_id IS NULL
GROUP BY v.customer_id
ORDER BY count_no_trans DESC, v.customer_id;</pre></div>
<p>输出结果：</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202511/2025110511272726.png" /></p>
<p class="maodian"><a name="_label6"></a></p><h2>总结 </h2>
頁:
[1]