在满足对某张表中数据的增、删、改的情况下，自动触发的功能称之为触发器。视图就是通过查询得到一张虚拟表，然后将标结果保存下来，下次直接使用即可。索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。

 

触发器

触发程序（trigger）是一种特殊的存储过程，它的执行不是由程序调用，也不是手工启 动，而是通过事件进行触发来被执行的，当对一个表进行操作（insert，delete，update）时就会激活它并执行。触发程序经常用于加强数据的完整性约束和业务规则 等。触发程序类似于约束，但比约束更灵活，具有更精细和更强大的数据控制能力。触发 程序的优点如下。

⑴触发程序的执行是自动的。当对触发程序相关表的数据做出相应的修改后立即执行。

⑵触发程序可以通过数据库中相关的表进行层叠修改另外的表。

⑶触发程序可以实施比 foreignkey 约束、check 约束更为复杂的检查和操作。

创建

create trigger < 触发程序名称>

 

{before | after}

{insert | update | delete}

on < 表名> for each row

< 触发程序 SQL 语句>

详细说明:

⚫ ⑴表的拥有者即创建表的用户可以在表上创建触发程序，而且一个表上可以创建多个触发程序。

⚫ ⑵create trigger<触发程序名称>：创建一个新触发程序，并指定触发程序的名称。

⚫ ⑶{before | after}：用于指定在INSERT、UPDATE或 DELETE语句执行前触发还是在语句执行后触发。

⚫ ⑷{insert | update | delete}。

◼ INSERT：将新行插入表时激活触发程序，例如，通过 INSERT、LOADDATA 和 REPLACE语句。

◼ UPDATE：更改某一行时激活触发程序，例如，通过 UPDATE 语句。

◼ DELETE：从表中删除某一行时激活触发程序，例如，通过 DELETE 和REPLACE语句。

⚫ ⑸on<表名>：用于指定响应该触发程序的表名。必须引用永久性表，不能将触发程序与 TEMPORARY 表或视图关联起

来。

⚫ ⑹for each row：触发程序的执行间隔，for each row 通知触发程序每隔一行执行一次动作，而不是对整个表执行一次。

⚫ ⑺<触发程序 SQL 语句>：触发程序要执行的 SQL语句，如果该触发程序要执行多条 SQL 语句，要将多条语句放在

BEGIN…END 块中。

⚫ ⑻触发程序名称存在于方案的名称空间内，这意味着在 1 个方案中，所有的触发程序必须具有唯一的名称，位于不同方

案中的触发程序可以具有相同的名称。

注意

对于具有相同触发程序动作时间和事件的给定表，不能有两个触发程序。例如，对于 某一表，不能有两个 before update 触发程序。但可以有 1个 before update 触发程序 和 1个 before insert触发程序，或 1个 before update 触发程序和 1个 after update 触发程序。

示例：基于学生表和班级表

添加一个学生,数量班级的学生数量自动增加

 

限制学生的年龄段

 

删除班级后将班级下面所有的学生删除

 

查看

show triggers 

show triggers [from db_ name] [like expr]

在系统表中查看

已定义好的触发程序的信息都存储在 INFORMATION_SCHEMA 库中的 TRIGGERS 表中，可 以通过查看该表中的信息获取某个触发程序的信息

 

删除

MySQL删除触发程序的语法如下。

drop {database | schema} [if exists] trigger_ name

视图

视图（View）是一个由查询语句定义数据内容的表，表中的数据内容就是 SQL查询语句的 结果集，行和列的数据均来自 SQL查询语句中使用的数据表。但之所以说视图是虚拟的 表，是因为视图并不在数据库中真实存在，而是在引用视图时动态生成的。

优势

⚫ 使用视图简单，操作视图和操作数据表完全是两个概念，用户不用理清数据表之间复杂的逻辑关系，而且将经常使用的

SQL 数据查询语句定义为视图，可以有效地避免代码重复减少工作量。

⚫ 使用视图安全，用户只访问到视图给定的内容集合，这些都是数据表的某些行和列，避免用户直接操作数据表引发的一

系列错误。

 

⚫ 使用视图相对独立，应用程序访问是通过视图访问数据表，从而程序和数据表之间被视图分离。如果数据表有变化，完

全不用去修改 SQL 语句，只需要调整视图的定义内容，不用调整应用程序代码。

⚫ 复杂的查询需求。可以进行问题分解，然后将创建多个视图获取数据，再将视图联合起来就能得到需要的结果了

假如因为某种需要，a 表与 b表需要进行合并以组成一个新的表 c，最后 a 表与b表都不 存在了。而由于原来程序中编写 SQL分别是基于 a 表与 b表查询的，这就意味着需要重新 编写大量的 SQL(改成向 c 表去操作数据)，而通过视图就可以不用修改 SQL。定义两个视图 名字还是原来的表名 a 和 b。a、b视图完成从 c 表中取出内容。需要说明的是，使用这样 的解决方式，基于对视图的细节了解越详细越好。因为使用视图与使用表在语法上没区 别。比如视图名 a，那么查询还是“select*froma”。

视图的工作机制:

当调用视图的时候，才会执行视图中的 SQL，进行取数据操作。视图的内容没有存 储，而是在视图被引用的时候才派生出数据。这样不会占用空间，由于是即时引用，视图 的内容与真实表的内容总是一致的。视图这样设计最主要的好处就是比较节省空间，当数 据内容总是一样时，就不需要维护视图的内容，反维护好真实表的内容，就可以保证视图 的完整性了。

创建

语法:

create [algorithm={ undefined| merge| temptable}]

view view_ name as

select column_ name( s) from table_ name

[with [cascaded| local] check option];

其中，ALGORITHM 为可选参数，表示视图选择的算法。

◼ UNDEFINED表示 MySQL将自动选择所要使用的算法(默认的)；

◼ MERGE 表示将视图的语句与视图定义合并起来，使得视图定义的某一部分取代语 句的对应部分；

◼ TEMPTABLE 表示将视图的结果存入临时表，然后使用临时表执行语句。

WITH CHECK OPTION 为可选参数，表示更新视图时要保证在视图的权限范围内。

◼ CASCADED表示更新视图时要满足所有相关视图和表的条件才进行更新(默认)；

◼ LOCAL 表示更新视图时，要满足该视图本身定义的条件即可更新。

示例:

 

表和视图共享数据库中相同的名称空间，因此，数据库不能包含具有相同名称的表和视 图。视图必须具有唯一的列名，不得有重复，就像基表那样。

修改

create or replace [algorithm={ undefined| merge| temptable}]

view 视图

as select 语句

[with [cascaded| local] check option];

删除

因为视图本身只是一个虚拟表，没有物理文件存在，所以视图的删除并不会删除数据，只 是删除掉视图的结构定义。

drop view [if exists] view_ name [, view_ name1, view_ name2...]

索引

索引是在存储引擎中实现的，因此每种存储引擎的索引都不一定完全相同，并且每种存储 引擎也不一定支持所有索引类型。根据存储引擎定义每个表的最大索引数和最大索引长 度。所有存储引擎支持每个表至少 16 个索引，总索引长度至少为 256 字节。大多数存储 引擎有更高的限制。MYSQL中索引的存储类型有两种：BTREE 和 HASH，具体和表的存储 引擎相关；MYISAM 和 InnoDB 存储引擎只支持 BTREE索引。

索引的优点

1、通过创建唯一索引，保证数据库表每行数据的唯一性

2、大大加快数据查询速度

3、在使用分组和排序进行数据查询时，可以显著减少查询中分组和排序的时间

索引的缺点

1、维护索引需要耗费数据库资源

2、索引需要占用磁盘空间，索引文件可能比数据文件更快达到最大文件尺寸

3、当对表的数据进行增删改的时候，因为要维护索引，速度会受到影响

索引的分类

1、普通索引和唯一索引

主键索引是一种特殊的唯一索引，不允许有空值

2、单列索引和复合索引

单列索引只包含单个列

复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字 段，索引才会被使用。使用复合索引时遵循最左前缀集合

3、全文索引

全文索引类型为 FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列 中插入重复值和空值。全文索引可以在 CHAR、VARCHAR、TEXT类型列上创建。MYSQL 只有 MYISAM 存储引擎支持全文索引

4、空间索引

空间索引只能在存储引擎为 MYISAM 的表中创建

创建索引

我们通常都是在创建好的表上面增加索引

利用 alter 语句

alter table table_name add [unique|fulltext|spatial][index|key]

[index_name](col_name[length],...) [asc|desc]

如:给学生表的姓名字段加上索引

 

利用 create 语句

create [unique|fulltext|spatial] index index_name

on table_name(col_name[length],...) [asc|desc]

普通索引

 

唯一索引

 

复合索引

 

删除索引

alter

alter table table_name drop index index_name

drop

drop index index_name on table_name

Explain

 

explain 语句输出结果的各个行的解释如下：

⚫ select_type： 表示查询中每个select 子句的类型（简单 or 复杂）

⚫ type：表示 mysql 在表中找到所需行的方式，又称“访问类型”:[all, index, range, ref, eq_ref, const, system, null]（从左

到右，性能从差到好）

ALL：Full Table Scan， MySQL 将遍历全表以找到匹配的行

index: Full Index Scan，index 与ALL 区别为 index 类型只遍历索引树

range:只检索给定范围的行，使用一个索引来选择行

ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

eq_ref: 类似 ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接

中使用 primary key 或者 unique key 作为关联条件

const、system: 当 MySQL 对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于 where列表中，

MySQL 就能将该查询转换为一个常量,system 是const 类型的特例，当查询的表只有一行的情况下，使用system

NULL: MySQL 在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查

找完成

⚫ possible_keys ：指出 MySQL 能使用哪个索引在表中找到行，查询涉及到的字段上若存在索引，则该索引将被列出，但

不一定被查询使用

⚫ key： 显示 MySQL 在查询中实际使用的索引，若没有使用索引，显示为 NULL

⚫ key_len ：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度

⚫ ref ：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

⚫ rows ：表示 MySQL 根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

⚫ Extra ：包含不适合在其他列中显示但十分重要的额外信息 如 using where，using index

关于索引长度问题

对于 char和 varchar列，只用一列的一部分就可创建索引。创建索引时，使用 col_name(length)语法，对前缀编制索引。前缀包括每列值的前length 个字符。blob 和 text 列也可以编制索引，但是必须给出前缀长度。

使用列的一部分创建索引可以使索引文件大大减小，从而节省了大量的磁盘空间，有可能 提高 insert操作的速度

今天我的分享就到这里，大家有没有什么好的学习方法呢？欢迎来留言评论，和我们一起交流。如果喜欢我的文章，也欢迎大家关注、点赞、转发。我是丫丫，一个专注分享项目实战技能的IT从业者。