详解SQL数据定义功能

1. 数据库模式（Schema）的定义与删除

定义模式

CREATE SCHEMA schema_name;

• 示例：

  CREATE SCHEMA my_schema;
  

  • 说明：创建名为 my_schema 的模式。

删除模式

DROP SCHEMA schema_name [CASCADE | RESTRICT];

• 示例：

  DROP SCHEMA my_schema CASCADE;
  

  • 说明：
    • CASCADE：删除模式时，同时删除模式中的所有对象。
    • RESTRICT：如果模式中存在对象，则无法删除。

---

2. 基本表的定义、修改与删除

定义表

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

• 示例：

  CREATE TABLE employees (employee_id INT PRIMARY KEY,name VARCHAR(100) NOT NULL,department_id INT,hire_date DATE,salary DECIMAL(10, 2)
  );
  

  • 说明：创建名为 employees 的表，包含以下字段：
    • employee_id：主键，不允许为空。
    • name：员工姓名，不允许为空。
    • department_id：部门编号，允许为空。
    • hire_date：雇佣日期。
    • salary：薪资，精确到小数点后两位。

约束

1. NOT NULL 约束

• 作用：保证列的值不能为 NULL，即必须提供一个值。
• 语法：

  column_name data_type NOT NULL
  

• 示例：

  CREATE TABLE employees (employee_id INT NOT NULL,name VARCHAR(100) NOT NULL,hire_date DATE NOT NULL
  );
  

  • 说明：
    • employee_id、name 和 hire_date 列都必须有值，不能为 NULL。

2. DEFAULT 约束

• 作用：为列设置一个默认值，当插入数据时未提供值时使用默认值。
• 语法：

  column_name data_type DEFAULT default_value
  

• 示例：

  CREATE TABLE employees (employee_id INT NOT NULL,name VARCHAR(100) NOT NULL,hire_date DATE DEFAULT CURRENT_DATE,salary DECIMAL(10, 2) DEFAULT 3000.00
  );
  

  • 说明：
    • hire_date 默认值是当前日期。
    • salary 默认值是 3000.00，如果插入数据时未提供 salary 值，将使用默认值。

3. UNIQUE 约束

• 作用：保证列中的值唯一，不能重复。
• 语法：

  column_name data_type UNIQUE
  

• 示例：

  CREATE TABLE employees (employee_id INT NOT NULL UNIQUE,email VARCHAR(255) UNIQUE
  );
  

  • 说明：
    • employee_id 和 email 列中的值不能重复。
    • UNIQUE 可以保证唯一性，但不同于主键，它允许列中存在 NULL 值。

4. PRIMARY KEY 约束

• 作用：用于标识表中的唯一记录，必须唯一且不能为 NULL。
• 语法：

  column_name data_type PRIMARY KEY
  

• 或者在多列上定义主键：

  PRIMARY KEY (column1, column2, ...)
  

• 示例：

  CREATE TABLE employees (employee_id INT PRIMARY KEY,name VARCHAR(100),department_id INT,hire_date DATE
  );
  

  • 说明：
    • employee_id 是表的主键，值必须唯一且不能为 NULL。

多列主键示例：

CREATE TABLE order_items (order_id INT,item_id INT,quantity INT,PRIMARY KEY (order_id, item_id)
);

• 说明：order_id 和 item_id 组合在一起作为主键，确保每个订单中每个商品的记录唯一。

5. FOREIGN KEY 约束

• 作用：用于建立两张表之间的关系，确保外键列的值必须在另一张表中存在。
• 语法：

  column_name data_type REFERENCES parent_table (parent_column)
  

• 或者：

  FOREIGN KEY (column_name) REFERENCES parent_table (parent_column)
  

• 示例：

  CREATE TABLE departments (department_id INT PRIMARY KEY,department_name VARCHAR(100)
  );CREATE TABLE employees (employee_id INT PRIMARY KEY,name VARCHAR(100),department_id INT,FOREIGN KEY (department_id) REFERENCES departments(department_id)
  );
  

  • 说明：
    • department_id 是 employees 表中的外键，引用 departments 表中的 department_id。

6. CHECK 约束

• 作用：限制列的值必须满足某个条件。
• 语法：

  column_name data_type CHECK (condition)
  

• 或者：

  CHECK (condition)
  

• 示例：

  CREATE TABLE employees (employee_id INT PRIMARY KEY,name VARCHAR(100) NOT NULL,salary DECIMAL(10, 2) CHECK (salary >= 0),age INT CHECK (age BETWEEN 18 AND 65)
  );
  

  • 说明：
    • salary 列的值必须大于或等于 0。
    • age 列的值必须在 18 和 65 之间。

7. AUTO_INCREMENT (MySQL 特有)

• 作用：为列自动生成唯一的递增值，通常用于主键。
• 语法：

  column_name data_type AUTO_INCREMENT
  

• 示例：

  CREATE TABLE employees (employee_id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(100) NOT NULL
  );
  

  • 说明：
    • employee_id 列的值将自动生成，初始值为 1，每次插入新行时递增。

综合示例：定义一张复杂表

CREATE TABLE employees (employee_id INT AUTO_INCREMENT PRIMARY KEY, -- 主键，自动递增name VARCHAR(100) NOT NULL,                -- 员工姓名，不能为空email VARCHAR(255) UNIQUE,                 -- 邮箱必须唯一department_id INT,                         -- 部门编号hire_date DATE DEFAULT CURRENT_DATE,       -- 默认值为当前日期salary DECIMAL(10, 2) CHECK (salary >= 0), -- 薪资必须大于或等于 0FOREIGN KEY (department_id) REFERENCES departments(department_id) -- 外键约束
);

• 详细解读：
  • employee_id 是主键，自动递增，唯一且不能为空。
  • name 是员工姓名，不能为空。
  • email 必须唯一，避免重复。
  • department_id 是外键，必须引用 departments 表中的 department_id。
  • hire_date 默认值是当前日期。
  • salary 的值必须大于或等于 0。

修改表结构

1. 添加列

   ALTER TABLE table_name ADD column_name datatype [constraints];
   

   • 示例：

     ALTER TABLE employees ADD email VARCHAR(255);
     

     • 说明：为 employees 表添加 email 列。

2. 修改列类型

   ALTER TABLE table_name ALTER COLUMN column_name TYPE new_datatype;
   

   • 示例：

     ALTER TABLE employees ALTER COLUMN salary TYPE DECIMAL(12, 2);
     

     • 说明：将 employees 表中的 salary 列的精度修改为 12 位（小数点后两位）。

3. 删除列

   ALTER TABLE table_name DROP COLUMN column_name [CASCADE | RESTRICT];
   

   • 示例：

     ALTER TABLE employees DROP COLUMN email;
     

     • 说明：删除 employees 表中的 email 列。

删除表

DROP TABLE table_name [CASCADE | RESTRICT];

• 示例：

  DROP TABLE employees CASCADE;
  

  • 说明：删除 employees 表及其依赖对象。

---

3. 视图（View）的定义与删除

创建视图

视图是基于一个或多个表的查询结果集，定义一个视图后可以像查询表一样查询视图。

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

• 示例：

  CREATE VIEW employee_salary AS
  SELECT employee_id, name, salary
  FROM employees
  WHERE salary > 50000;
  

  • 说明：
    • 创建一个名为 employee_salary 的视图，该视图包含 employees 表中薪资大于 50000 的员工的 employee_id、name 和 salary。

删除视图

DROP VIEW view_name;

• 示例：

  DROP VIEW employee_salary;
  

  • 说明：删除 employee_salary 视图。

---

4. 索引的建立、修改与删除

创建索引

1. 普通索引

   CREATE INDEX index_name ON table_name (column1, column2, ...);
   

   • 示例：

     CREATE INDEX idx_department_id ON employees(department_id);
     

     • 说明：为 employees 表的 department_id 列创建普通索引。

2. 唯一索引

   CREATE UNIQUE INDEX index_name ON table_name (column1, column2, ...);
   

   • 示例：

     CREATE UNIQUE INDEX idx_employee_email ON employees(email);
     

     • 说明：为 employees 表的 email 列创建唯一索引。

修改索引

索引本身无法直接修改，需要删除后重新创建。

删除索引

DROP INDEX index_name;

• 示例：

  DROP INDEX idx_department_id;
  

  • 说明：删除名为 idx_department_id 的索引。

---

总结

• CREATE 用于创建模式、表和索引。
• ALTER 用于修改表结构。
• DROP 用于删除模式、表和索引。
• 使用 CASCADE 会删除对象及其依赖关系，RESTRICT 则要求先删除所有依赖关系。