mysql中的索引和约束

目录

一：sql中的索引

二：sql中的约束

---

一：sql中的索引

• 作用：索引是一种数据结构，用于快速查找数据库表中的记录。它类似于书籍的目录，可以加快数据的检索速度。

• 类型：MySQL支持多种类型的索引，包括B-tree索引、哈希索引等。最常用的是B-tree索引，它适用于各种查询类型。

• 创建：可以在表的一个或多个列上创建索引，以便在查询时加快对这些列的搜索速度。可以使用CREATE INDEX语句来创建索引。下面将介绍几个增加索引的方式。

例1：在表上的单个列上增加索引

create index idx_id  on emp (name);

上述代码表示在emp表中的name列增加了一个名为idx_id的索引

例2：在表上的多个列上增加索引

create index idx_id on emp (name , sex );

上述代码表示在emp表上的name和sex列上创建一个复合索引

例3：在已有的表上面增加索引

alter table emp add index idx_id (name);

该语句表示在emp上增加一个名为idx_id的索引用于name列

例4：创立一个全文本索引

create fulltext index idx_id on emp (name);

该语句表示在emp表上的name列创建一个名为idx_id的全文索引

注意：虽然索引可以提高查询性能，但过多地创建索引可能会导致插入、更新和删除操作变慢，并增加存储空间的消耗。

二：sql中的约束

sql中的约束大致有一下几个作用

1. 确保数据的完整性：

   • 约束可以确保数据的完整性，防止在数据库中插入无效或不合规的数据。

   • 例如，主键约束可以确保每一行都有一个唯一的标识符，唯一约束可以确保某列或组合列的值是唯一的，非空约束可以确保某列的值不为空。

2. 维护数据的一致性：

   • 外键约束可以用来维护不同表之间的引用关系，确保引用的数据在被引用表中存在。

   • 通过外键约束，可以实现数据的关联性和一致性，避免了数据之间的不一致。

3. 提高数据的安全性：

   • 约束可以增强数据的安全性，防止恶意或误操作导致数据的破坏或丢失。

   • 例如，检查约束可以限制列中允许的值的范围或条件，从而避免了无效数据的插入。

4. 简化数据操作：

   • 默认约束可以在插入新记录时为列提供默认值，减少了用户在数据操作中的输入工作。

   • 约束的使用可以简化数据操作，使数据库更易于管理和维护。

5. 提高查询性能：

   • 索引是一种特殊的约束，它可以提高查询的性能，加快数据检索的速度。

   • 通过在经常用于查询条件的列上创建索引，可以加速查询操作，提高数据库的性能。

下面来介绍sql中的六大约束并举出简单的示例。

第一：主键约束：主键约束用于唯一标识表中的每一行，并确保每个主键值都是唯一的。主键约束在表中只能有一个，且不允许为空。

示例：创建了一个名为stu的表并且设id这个字段为主键

create table stu (
    id int primary key ,
    name varchar(50),
    age int
);

第二：唯一约束：唯一约束用于确保表中某列或一组列的值是唯一的，但允许空值。可以有多个唯一约束，每个唯一约束可以包含多列。

示例：在emp这个表中表示 email和number这两个字段都是唯一的

create table emp (
    id int ,
    email varchar(255) unique
    number varchar(255) unique
);

第三：外键约束：外键约束用于在两个表之间建立引用关系，确保参考表中的外键值在被引用表中存在。外键约束可以确保数据的一致性和完整性。

示例：该语句表示了orders表和customers表之间有一个外键的约束

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

第四：检查约束：检查约束用于限制列中允许的值的范围或条件。可以在列级别或表级别上定义检查约束。

示例：该语句表明了在这个表中，价格和数量都必须大于等于0

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10, 2),
    quantity INT,
    CHECK (price >= 0 AND quantity >= 0)
);

第五：非空约束：非空约束用于确保列中的值不为空。

示例：

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    phone_number VARCHAR(20) NOT NULL
);

第六：默认约束：默认约束用于在插入新记录时为列提供默认值。如果未提供值，则列将使用默认值。

示例：在这个表中创建的age如果没有给值就默认是18岁

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT DEFAULT 18
);