Oracle建表语句SQL详解|极客笔记

Oracle建表语句SQL详解

引言

在数据库领域中，表是最基本、最重要的数据存储对象。在Oracle数据库中，创建表的一种常用方式是使用SQL语句。本篇文章将详细解读Oracle建表语句SQL的语法、用法和示例，并通过运行结果展示其实际应用。

基本语法

在Oracle数据库中，使用CREATE TABLE语句来创建表。其基本语法如下：

CREATE TABLE table_name
(
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
    table_constraints
);

其中，table_name为要创建的表名，column1、column2等为创建的列名，datatype为列的数据类型，constraints为列的约束，table_constraints为表的约束。

数据类型

Oracle数据库支持多种数据类型，常用的有以下几种：

NUMBER(p, s)：用于存储数字，p表示总位数，s表示小数位数。
VARCHAR2(size)：用于存储可变长度的字符数据，size表示最大长度。
DATE：用于存储日期和时间。
CLOB：用于存储大文本数据。
BLOB：用于存储二进制大对象。

其它常见的数据类型还包括CHAR、NCHAR、LONG、RAW等，具体使用取决于实际需求。

列约束

列约束主要用于对列的数据进行限制，常见的列约束包括：

NOT NULL：指定列不能为空。
UNIQUE：指定列的值必须唯一。
PRIMARY KEY：指定列作为表的主键，主键是唯一标识表中每一条记录的列。
FOREIGN KEY：指定列为外键，用于建立表与表之间的关系。
CHECK：用于指定列的取值范围。

下面是一个示例代码，演示了如何在创建表时使用列约束：

CREATE TABLE employees
(
    id NUMBER PRIMARY KEY,
    name VARCHAR2(50) NOT NULL,
    gender VARCHAR2(10) CHECK (gender IN ('Male', 'Female')),
    department_id NUMBER,
    FOREIGN KEY (department_id) REFERENCES departments (id)
);

表约束

表约束用于对整个表的数据进行限制，常见的表约束包括：

UNIQUE：指定多个列的值的组合必须唯一。
PRIMARY KEY：指定多个列的组合作为表的主键。
FOREIGN KEY：指定多个列组合作为外键。
CHECK：对表的多个列的取值范围进行限制。

下面是一个示例代码，演示了如何在创建表时使用表约束：

CREATE TABLE departments
(
    id NUMBER PRIMARY KEY,
    name VARCHAR2(50) NOT NULL,
    manager_id NUMBER,
    CONSTRAINT fk_manager FOREIGN KEY (manager_id) REFERENCES employees (id)
);

示例代码

接下来，我们将通过几个示例代码来演示Oracle建表语句SQL的使用方法和效果。

示例一

创建一个包含基本数据类型的表，并使用列约束和表约束：

CREATE TABLE users
(
    id NUMBER PRIMARY KEY,
    username VARCHAR2(50) UNIQUE NOT NULL,
    password VARCHAR2(50) NOT NULL,
    email VARCHAR2(100) CHECK (email LIKE '%@%.%'),
    created_at DATE DEFAULT SYSDATE
);

ALTER TABLE users ADD CONSTRAINT ck_password CHECK (LENGTH(password) >= 6);

示例二

创建一个包含大文本和二进制大对象的表：

CREATE TABLE documents
(
    id NUMBER PRIMARY KEY,
    document_name VARCHAR2(100) NOT NULL,
    content CLOB,
    attachment BLOB
);

示例三

创建一个包含外键关系的表：

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

运行结果

使用以上示例代码创建表后，可以通过以下方式验证其效果。

查询已创建的表：

SELECT table_name FROM user_tables;

运行结果：

users
documents
orders

查询指定表的结构：

DESC users;

运行结果：

Name                  Null     Type
--------------------- -------- ---------------
ID                    NOT NULL NUMBER
USERNAME              NOT NULL VARCHAR2(50)
PASSWORD              NOT NULL VARCHAR2(50)
EMAIL                          VARCHAR2(100)
CREATED_AT                      DATE

插入数据并查询：

INSERT INTO users (id, username, password, email) VALUES (1, 'admin', '123456', 'admin@example.com');
INSERT INTO users (id, username, password, email) VALUES (2, 'user1', 'password1', 'user1@example.com');

SELECT * FROM users;

运行结果：

ID   USERNAME  PASSWORD   EMAIL                CREATED_AT
---- --------- ---------- -------------------- ----------
1    admin     123456     admin@example.com        
2    user1     password1  user1@example.com

结论

本文详细解释了Oracle建表语句SQL的语法、数据类型、列约束和表约束，并提供了示例代码和运行结果，展示了其实际应用。通过灵活运用Oracle建表语句SQL，可以有效地创建和管理数据库中的表，满足不同的需求和约束条件。在实际工作中，可以根据具体情况灵活运用和扩展这些知识，提高数据库开发和管理的效率和性能。