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 則要求先刪除所有依賴關系。