揭秘数据库的组成：从数据表到索引的一切

数据库的组成

数据库系统由多个部分组成，每个部分都有其特定的功能和作用。

表

表是数据库中最基本的存储结构，用于存储数据。每个表包含行和列，行表示记录，列表示字段。

• 示例：

  CREATE TABLE employees (
      id INT PRIMARY KEY AUTO_INCREMENT,
      name VARCHAR(50),
      dept VARCHAR(50),
      salary DECIMAL(10, 2)
  );
  
  

employees
表包含四个字段：id
、name
、dept
和 salary
，其中 id
是主键，并且自动递增。

视图

视图是基于表创建的虚拟表，它可以包含表的某些列或行。视图用于简化复杂查询和提高数据访问的安全性。

• 示例：

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

high_salary_employees
只包含 employees
表中工资大于 50000 的员工的名字和工资。

存储过程和函数

存储过程和函数是预编译的 SQL 代码块，可以在数据库中保存和执行。存储过程用于执行特定任务，而函数用于返回单个值。

• 示例存储过程：

  CREATE PROCEDURE get_employee_salary(IN emp_id INT, OUT emp_salary DECIMAL(10, 2))
  BEGIN
      SELECT salary INTO emp_salary
      FROM employees
      WHERE id = emp_id;
  END;
  
  

• 示例函数：

  CREATE FUNCTION calculate_bonus(emp_id INT) RETURNS DECIMAL(10, 2)
  BEGIN
      DECLARE bonus DECIMAL(10, 2);
      SELECT salary * 0.1 INTO bonus
      FROM employees
      WHERE id = emp_id;
      RETURN bonus;
  END;
  
  

索引和约束

索引用于加速数据库查询，而约束用于保证数据完整性和一致性。

• 示例索引：

  CREATE INDEX idx_emp_dept ON employees(dept);
  
  

• 示例约束：

  ALTER TABLE employees
  ADD CONSTRAINT chk_salary CHECK (salary > 0);
  
  

idx_emp_dept
加快了基于 dept
列的查询，约束 chk_salary
确保 salary
列的值大于 0。

触发器

触发器是在特定事件（如插入、更新或删除）发生时自动执行的存储过程。

• 示例触发器：

  CREATE TRIGGER update_salary_history
  AFTER UPDATE ON employees
  FOR EACH ROW
  BEGIN
      INSERT INTO salary_history(emp_id, old_salary, new_salary, change_date)
      VALUES(OLD.id, OLD.salary, NEW.salary, NOW());
  END;
  
  

update_salary_history
在 employees
表的 salary
列更新时，将旧值和新值记录到 salary_history
表中。

存储引擎

存储引擎是数据库存储、检索和更新数据的底层软件模块。MySQL 支持多种存储引擎，如 InnoDB、MyISAM 等。

• 示例：

  CREATE TABLE orders (
      id INT PRIMARY KEY AUTO_INCREMENT,
      item_id INT,
      amount INT,
      unit_price DECIMAL(12, 4)
  ) ENGINE=InnoDB;
  
  

orders
使用 InnoDB 存储引擎，支持事务和外键。