数据库上机1

数据库上机报告

实验一 表的定义

实验目标

• 创建一个包含完整性约束的表；
• 确定表命名的规则；
• 描述可以使用的数据类型；
• 利用约束自动创建索引；
• 利用其它表的数据行创建新表。

步骤

创建表格 student_info:

格式: <column_name> <data_type> <constraint_type>(可选)

CREATE TABLE student_info (
    student_id NUMBER(10) PRIMARY KEY,          -- 学号，主键约束
    name VARCHAR2(50) NOT NULL,                 -- 姓名，非空约束
    phone_number VARCHAR2(20) UNIQUE,           -- 电话，唯一约束
    email VARCHAR2(100) UNIQUE,                 -- 邮箱，唯一约束
    enrollment_date DATE DEFAULT SYSDATE,       -- 入学日期，默认当前日期
    CONSTRAINT chk_phone_number CHECK (REGEXP_LIKE(phone_number, '^\d{10,20}$')) -- 检查电话格式为10到20位数字
);

创建含外键约束的表:

CREATE TABLE course_enrollment (
    enrollment_id NUMBER(10) PRIMARY KEY,       -- 注册 ID，主键
    student_id NUMBER(10),                       -- 学号，外键
    course_name VARCHAR2(100) NOT NULL,         -- 课程名称，非空约束
    enrollment_date DATE DEFAULT SYSDATE,       -- 注册日期，默认当前日期
    CONSTRAINT fk_student FOREIGN KEY (student_id) REFERENCES student_info(student_id)  -- 外键约束
);

查看当前表格:

DESC student_info

外键约束的表:

确定命名规则:

• 以字母开始
• 包含 1-30 个字符长
• Z, a–z, 0–9, _, $, #
• 不能与本用户的其他对象同名
• 不能是保留字

描述可以使用的数据类型:

在创建表格时进行约束

利用约束自动创建索引

• 在创建表的时候已经创建约束
• 自动将唯一约束以及主键约束创建索引
• 可自行创建索引:CREATE INDEX <索引名> ON <表名>(<列名>);

约束的语法

• 列级约束

column [CONSTRAINT constraint_name] constraint_type,

• 表级约束

column,...
[CONSTRAINT constraint_name] constraint_type (column, ...),

约束的类型

• 非空约束（NOT NULL）

  • 强制某列不能为空
  • 不允许使用表达式
  • 是列级约束

• 唯一键（UNIQUE KEY）

  • 指明列或列组合的值唯一
  • 当单一列有唯一约束时，可以为空值
  • 可以定义为表级或列级约束
  • 系统会自动建立唯一性索引

• 主键（PRIMARY KEY）

  • 主键约束所表示的列值，其值是唯一识别表中的一条记录
  • 每个表都应该有一个主键，且只能有一个主键
  • 指定列值唯一
  • 主键不可以为空
  • 是表级或列级约束
  • 系统会自动建立唯一性索引

• 外键（FOREIGN KEY）

  • 外键用于连接包括相关信息的两个表，他们大多使用在父子关系中
  • 可以定义在单列或多列上
  • 会同本表或其他表的主键或唯一键建立连接关系
  • 是表级或列级约束
  • 插入值必须同父表匹配或为 NULL
  • FOREIGN KEY：指明子表中的列
  • REFERENCES：指明父级表及相关列
  • ON DELETE CASCADE：允许父表中的删除操作，并且删除子表中的相关记录

  创建一个 course_enrollment 表，记录学生的课程注册信息，其中 student_id 列将作为外键引用 student_info 表中的 student_id。

  创建 course_enrollment 表

  以下是创建 course_enrollment 表的 SQL 语句示例：

CREATE TABLE course_enrollment (
      enrollment_id NUMBER(10) PRIMARY KEY,       -- 注册 ID，主键
      student_id NUMBER(10),                       -- 学号，外键
      course_name VARCHAR2(100) NOT NULL,         -- 课程名称，非空约束
      enrollment_date DATE DEFAULT SYSDATE,       -- 注册日期，默认当前日期
      CONSTRAINT fk_student FOREIGN KEY (student_id) REFERENCES student_info(student_id)  -- 外键约束
  );

说明：

1. enrollment_id ：主键，唯一标识每条课程注册记录。
2. student_id ：外键，引用 student_info 表中的 student_id，确保每条课程注册记录都对应一个有效的学生。
3. course_name ：课程名称，使用 VARCHAR2 类型并设置为非空。
4. enrollment_date ：注册日期，默认设置为当前日期。
5. 外键约束 ：通过 CONSTRAINT fk_student FOREIGN KEY (student_id) REFERENCES student_info(student_id) 来定义外键约束，确保 course_enrollment 表中的 student_id 必须在 student_info 表中存在。

示例插入数据

插入数据时，确保 `student_info` 表中有相应的 `student_id` 记录。以下是示例插入操作：

-- 假设 student_info 表中已经有一条记录
INSERT INTO student_info (student_id, name, phone_number, email)
VALUES (1, 'Alice', '1234567890', '[email protected]');

-- 向 course_enrollment 表插入记录
INSERT INTO course_enrollment (enrollment_id, student_id, course_name)
VALUES (1, 1, 'Database Systems');  -- 这里的 student_id 为 1，必须存在于 student_info 表中

重要性

外键约束确保数据的一致性和完整性，防止不合法的记录插入到 course_enrollment 表中。这意味着，只有在 student_info 表中存在的学生 ID 才能用于注册课程，从而维护数据的关联性。

• CHECK(制定每条记录必须满足的条件)

  • 不允许使用表达式
  • 参照伪列 CURRVAL, NEXTVAL, LEVEL, 或 ROWNUM
  • 调用 SYSDATE, UID, USER, 或 USERENV 函数
  • 与其它行中其它值有关的查询值
  • 是表级或列级约束

使用子查询创建表

使用 CREATE TABLE 和 AS 子查询创建表并且插入记录

CREATE TABLE table [ column(, column...)] AS sub-query;

• 指定列应与子查询中的列相匹配
• 为列定义了列名、缺省值和完整性约束
  例：创建一个学生的联系方式表:

CREATE TABLE student_contact_info AS
SELECT student_id, name, phone_number
FROM student_info
WHERE phone_number IS NOT NULL;

利用数据字典查看约束条件

SELECT constraint_name, constraint_type, status, search_condition
FROM user_constraints
WHERE table_name = 'STUDENT_INFO';

改变表中列的名字

ALTER TABLE student_info RENAME COLUMN phone_number TO contact_number;

删除表中的列

ALTER TABLE student_info DROP COLUMN email;

改变一个字段初始定义的 Check 范围

-- 删除旧的
ALTER TABLE student_info DROP CONSTRAINT chk_phone_number;

-- 添加新的
ALTER TABLE student_info 
ALTER TABLE student_info ADD CONSTRAINT chk_phone_number CHECK (REGEXP_LIKE(contact_number, '^\d+$'));