MySQL学习记录（10）

学习内容：索引的创建与应用、触发器的创建与应用

---

一、认识索引

1.索引的概念

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可提高数据库中特定数据的查询速度。
索引是一个单独的、存储在磁盘上的数据库结构，包含着对数据表里所有记录的引用指针。

2.索引的分类

（1）普通索引和唯一索引
普通索引：允许在定义索引的列中插入重复值和空值。
唯一索引：索引值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。主键索引是一种特殊的唯一索引，不允许有空值。
（2）单列索引和组合索引
单列索引：一个索引只包含单个列，一个表可以有多个单列索引。
组合索引：在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合。
（3）全文索引
全文索引类型为 FULLTEXT ,允许在这些索引列中插入重复值和空值。
（4）空间索引
对空间数据类型的字段建立的索引。MySQL中的空间数据类型有四种，分别是：GEOMETRY、POINT、LINESTRING、POLYGON。创建空间索引的列，必须将其申明为NOT NULL。

二、创建索引

1.创建数据表时创建索引

语法格式：

CREATE TABLE 表名 [col_name data_type]
[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name [length]) [ASC|DESC];

• UNIQUE：唯一索引
• FULLTEXT：全文索引
• SPATIAL：空间索引
• INDEX与KEY：为同义词，两者作用相同，用来指定创建索引。
• col_name：需要创建索引的字段列。
• index_name：指定索引的名称，若不指定，默认col_name为索引名称。
• length：索引长度，只有字符串类型的字段才能指定索引长度。
• ASC或DESC：指定升序或者降序的索引值存储。

2.在存在的表上创建索引

语法格式：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX [index_name]
ON table_name (col_name [length]) [ASC|DESC];

3.使用ALTER TABLE 语句创建索引

语法格式：

ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX [index_name]
(col_name [length]) [ASC|DESC];

三、删除索引

1.使用ALTER TABLE 语句创建索引

语法格式：

ALTER TABLE table_name DROP INDEX index_name;

2.使用DROP INDEX 语句删除索引

语法格式：

DROP INDEX index_name ON table_name;

---

一、认识触发器

当对表执行插入、删除或更新操作时，触发器会自动执行以检查表的数据完整性和约束性。
触发器最重要的作用时能够确保数据的完整性，每一个数据操作只能设置一个触发器。
触发器建立在触发事件上。

二、创建触发器

语法格式：

CREATE TRIGGER trigger_name trigger_time trigger_event
ON table_name FOR EACH ROW trigger_stmt;

• trigger_name：触发器名称。
• trigger_time ：触发时间，可以指定为before 或after。
• trigger_event：触发事件，包括INSERT,UPDATE,DELETE。
• trigger_stmt：触发器程序体，触发器程序可以使用begin和end作为开始和结束，中间包含多条语句。

1.只有一条执行语句

例如：

DELIMITER //
--在这之后的语句，以分号结束，解释器不会有什么反应，只有遇到了//，才认为是语句结束。
CREATE TRIGGER name_student
 BEFORE INSERT
 ON students
FOR EACH ROW
BEGIN
  IF new.id=3 THEN
   set new.name='小林';
END IF;
END //

2.多条执行语句

例如：

DELIMITER //
CREATE TRIGGER tri_mu BEFORE INSERT ON test1
FOR EACH ROW
BEGIN
INSERT INTO test2 SET a2=new.a1+10;
INSERT INTO test3 SET a3=new.a1+20;
END //

三、查看触发器

1.使用 SHOW TRIGGERS 语句查看

只能查询所有触发器，无法指定查询。

SHOW TRIGGERS;

2.使用INFORMATION_SCHEMA 查看

SELECT *FROM INFORMATION_SCHEMA.TRIGGERS
[WHERE TRIGGER_NAME='trigger_name'];

四、删除触发器

DROP TRIGGER [schema_name.][IF EXISTS] trigger_name;

• schema_name：数据库名称。