SQL 索引、约束、外键 —/—＜11＞

一、索引

        MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

1、普通索引

1）创建索引

CREATE INDEX indexName ON table_name (column_name)

        其中，index_name是要创建的索引的名称，table_name是要在其中创建索引的表的名称，column1, column2，...是要在索引中包含的列的名称。

2）修改表结构(添加索引)

要在已有的表中添加索引，可以使用ALTER TABLE语句。以下是添加索引的语法：

ALTER table tableName ADD INDEX indexName(columnName)

        其中，table_name是要修改的表的名称，index_name是要添加的索引的名称，column1, column2，...是要在索引中包含的列的名称。

3）创建表的时候直接指定

CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
INDEX [indexName] (username(length))  
);

ID：一个INT类型的列，不能为空（NOT NULL）。

username：一个VARCHAR(16)类型的列，不能为空（NOT NULL）。

同时，还创建了一个名为indexName的索引，该索引基于username列的前length个字符

 注意：索引名称应该是一个有效的标识符，并且在表中的索引名称必须是唯一的。此外，索引的创建可能会占用额外的存储空间，并有一定的性能影响。因此，在创建索引时应该仔细评估其对于查询性能的影响，并进行适当的优化。

4）删除索引的语法

DROP INDEX indexName ON mytable; 

2、唯一索引

        它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

1）创建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length)) 

2）修改表结构

ALTER table mytable ADD UNIQUE [indexName] (username(length))

3）创建表时直接指定

CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
UNIQUE [indexName] (username(length))  
); 

3、使用ALTER 命令添加和删除索引

有四种方式来添加数据表的索引：

1）ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):

该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL，

2）ALTER TABLE tbl_name ADD UNIQUE index_name (column_list):

这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。

3）ALTER TABLE tbl_name ADD INDEX index_name (column_list):

添加普通索引，索引值可出现多次。

4）ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):

该语句指定了索引为 FULLTEXT ，用于全文索引。

4、使用ALTER 命令添加和删除主键

 1）使用 ALTER 命令增加主键       

        主键作用于列上（可以一个列或多个列联合主键），添加主键索引时，你需要确保该主键默认不为空（NOT NULL）。实例如下：

ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

2）使用 ALTER 命令删除主键：

ALTER TABLE testalter_tbl DROP PRIMARY KEY;

删除主键时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名。

5、显示索引信息

        可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 \G 来格式化输出信息。

尝试以下实例:

SHOW INDEX FROM table_name;

二、约束

        对表中的数据进行限定，保证数据的正确性、有效性和完整性。

分类：

        1.主键约束：primary key 包含非空约束和唯一约束

        2.非空约束：not null   值不能为空

        3.唯一约束：unique

        4.外键约束：foreign key

1、非空约束：not null，值不能为null

        1）创建表时添加约束

CREATE TABLE stu(
   id INT,
   NAME VARCHAR(20) NOT NULL  -- name为非空
);

        2）创建表完后，添加非空约束

ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;

        3）删除name的非空约束

ALTER TABLE stu MODIFY NAME VARCHAR(25);

2、唯一约束：unique，值不能重复

        1）创建表时添加唯一约束

CREATE TABLE stu(
   id INT,
   phone_num VARCHAR(20) UNIQUE  -- 添加了唯一的约束
);

注意：mysql中，唯一约束限定的列的值可以有多个null，MySQL默认也会对UNIQUE的列建立索引

        2）删除唯一约束

alter table stu modify phone_num varchar(20);
-- 若无法删除可先将索引删除
ALTER TABLE stu DROP INDEX phone_num;

        3）在创建表后，添加唯一约束

ALTER TABLE stu MODIFY phone_nume VARCHAR(20) UNIQUE;

3、主键约束：primary key

        含义：非空且唯一，主键就是表中记录的唯一标识

        1）在创建表时，添加主键约束

CREATE TABLE stu (
   id INT PRIMARY KEY,  -- 给id添加主键约束
   NAME VARCHAR(20)
);

        2）删除主键

ALTER TABLE stu DROP PRIMARY KEY; -- 去除主键
alter table stu modify id int; -- 移除not null的限约束

        3）创建完表后，添加主键

ALTER TABLE stu MODIFY id INT PRIMARY KEY;

        4）自动增长

         如果某一列时数值类型的，使用auto_increment可以来完成值的自动增长

        在创建表时，添加主键约束，并且完成主键自动增长

CREATE TABLE stu(    id INT PRIMARY KEY AUTO_INCREMENT,  -- 给id添加主键约束,并完成主键自动增长
NAME VARCHAR(20)
);

        5）删除自动增长

ALTER TABLE stu MODIFY id INT;

        6）添加自动增长

ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

4、外键约束：foreign key，让表与表产生关系，从而保证数据的正确性。

        1）在创建表时，可以添加外键

create table 表名(
   ...
   外键列
   constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称) 
);

        2）删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

        3）创建表之后，添加外键

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键列名称) REFERENCES 主表名称(主表列名称) ;

        4）级联操作

        • 添加级联操作

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键列名称) REFERENCES 主表名称（主表列名称） ON UPDATE CASCADE ON DELETE CASCADE;

        • 分类

        级联更新：ON UPDATE CASCADE

        级联删除：ON DELETE CASCADE

级联删除（CASCADE）：当删除主表中的记录时，自动删除相关的从表记录。

级联更新（CASCADE）：当更新主表中的记录时，自动更新相关的从表记录中的外键值。

三、外键

外键使用流程：

1. 在主表（父表）中定义主键：主表中的主键一般是一个唯一标识记录的列。

2. 在从表（子表）中创建外键列：在从表中创建一个列，用于保存对主表中记录的引用。该列通常与主表中的主键列具有相同的数据类型。

3. 创建外键约束：在从表中创建外键约束，指定外键列与主表中主键列之间的关系。外键约束定义了对相关数据的引用规则，可以限制从表中的外键列只能引用主表中已存在的记录。

4. 处理外键关系：在插入、更新或删除数据时，数据库会自动验证外键约束，确保数据的一致性。例如，如果试图在从表中插入一个引用了不存在的主键值的记录，数据库会拒绝插入操作。

更新级联操作：

1. CASCADE：当主表中的主键值发生变化时，从表中的对应外键值也会被更新，保持一致性。

2. SET NULL：当主表中的主键值发生变化时，从表中的对应外键值会被设置为NULL。

3. RESTRICT：如果某个主表记录的主键值被修改，而该记录在从表中有相关的外键值，使用RESTRICT操作会导致更新操作失败，除非从表中不存在任何与该主表记录相关的记录。

4. NO ACTION：当主表中的主键值发生变化时，不做任何操作，需要手动更新从表中的外键值。

使用语法：

ALTER TABLE 从表
ADD CONSTRAINT 外键约束名
FOREIGN KEY (外键列)
REFERENCES 主表 (主键列)
ON UPDATE CASCADE;

删除级联操作：

1. CASCADE：当主表中的主键值被删除时，从表中相关的记录也会被删除。

2. SET NULL：当主表中的主键值被删除时，从表中相关的外键值会被设置为NULL。

3. RESTRICT：如果要删除某个主表记录，而该记录在从表中有相关的外键值，使用RESTRICT操作会导致删除操作失败，除非从表中不存在任何与该主表记录相关的记录。

4. NO ACTION：当主表中的主键值被删除时，不做任何操作，需手动处理从表中的记录。

其语法和和创建外键约束的语法相似：

ALTER TABLE 从表
ADD CONSTRAINT 外键约束名
FOREIGN KEY (外键列)
REFERENCES 主表 (主键列)
ON UPDATE RESTRICT
ON DELETE RESTRICT;