mysql数据库的高级应用索引、视图，触发器的详细介绍

下文主要给大家带来mysql数据库的高级应用索引、视图，触发器的详细介绍，希望这些内容能够带给大家实际用处，这也是我编辑mysql数据库的高级应用索引、视图，触发器的详细介绍这篇文章的主要目的。好了，废话不多说，大家直接看下文吧。

索引优缺点

优点

加快访问速度

加强行的唯一性

缺点

带索引的表在数据库中需要更多的存储空间

操纵数据的命令需要更长的处理时间，因为它们需要对索引进行更新

按照下列标准选择建立索引的列

频繁搜索的列

经常用作查询选择的列

经常排序、分组的列

经常用作联接的列（主键/外键）

请不要使用下面的列创建索引

仅包含几个不同值的列

表中仅包含几行

查询时减少使用*返回全部列，不要返回不需要的列

索引应该尽量小，在字节数小的列上建立索引

WHERE子句中有多个条件表达式时，包含索引列的表达式应置于其他条件表达式之前

避免在ORDER BY子句中使用表达式

根据业务数据发生频率，定期重新生成或重新组织索引，进行碎片整理 

索引的分类

普通索引

唯一索引 ：主键本身就是一钟特殊的唯一索引

全文索引

单列索引

多列索引

空间索引

准备实验环境

批量插入记录：mysql>delimiter $$  //定义分隔符

Mysql>Create procedure autoinsert1()   //创建存储过程

Begin

Declare i int default 1;

While (1<200000)do

Insert into school.t2 values(i,’ccc’); //这个表必须是事先存在的，

Set i=i+1;

End while;

End$$

Mysql>delimiter;//把分隔符还原回来

Mysql>Call autoinsert1();  //调用存储过程

创建索引(创建表时)注意事项：一开始就有索引会有个如果你批量导入数据的时候，因为某个字段上存在索引，所以速度会非常慢。

语法：create table 表名(

字段一 数据类型 [完整性约束条件.....],

字段一 数据类型 [完整性约束条件.....],

[unique|fulltext|spatial] index |key [索引名] (字段名[长度] [asc |desc])

); 

创建普通索引创建表时实例：

Create table department(

Dept_id int primary key,

Dept_name varchar(30),

Commnet varchar(50),

Index index_dept_name (dept_name)

);

查看索引：desc 表名或者show create table表名 \G

创建唯一索引创建表时实例：

Create table department(

Dept_id int primary key,

Dept_name varchar(30),

Commnet varchar(50),

Unique Index index_dept_name (dept_name)

);

创建全文索引创建表时实例：

Create table department(

Dept_id int primary key,

Dept_name varchar(30),

Commnet varchar(50),

fulltext Index index_dept_name (dept_name)

)engin=myisam; 

创建多列索引创建表时实例：

Create table department(

Dept_id int primary key,

Dept_name varchar(30),

Commnet varchar(50),

Index index_dept_name (dept_name,commnet)

);

模拟查询分析查询语法

Explain select * from dept 4 where commnet=’sale’ \G

创建索引(已存在的表create)

复制表的内容和结构  将t2表的所有数据复制到t4表上

Create table t4 select * from t2;

只复制表的结构  将t2表的结构复制到t4表上

Create table t4 select * from t2 where 1=2;

或者create table t5 like t2;

语法：create [unique|fullext|spatial] index 索引名 on 表名(字段名[(长度)][asc|desc]);

创建普通索引实例：

Create index index_dept_name on departmnet (dept_name);

创建唯一索引实例：

Create unique index index_dept_name on departmnet (dept_name);

创建全文索引实例：

Create fulltext index index_dept_name on departmnet (dept_name);

创建多列索引实例：

Create index index_dept_name on departmnet (dept_name,comment);

创建索引(已存在的表alter table)

语法：alter table 表名 add [unique|fullext|spatial] index 索引名 (字段名[(长度)][asc|desc]);

创建普通索引实例：

Alter table department add  index index_dept_name (dept_name); 

创建唯一索引实例：

Alter table department add  unique index index_dept_name (dept_name);

创建全文索引实例：

Alter table department add  fulltext index index_dept_name (dept_name);

创建多列索引实例：

Alter table department add index index_dept_name (dept_name,comment);

管理索引

查看索引

Show create table 表名\G

测试示例

EXPLAIN select * from department where dept_name=’hr’;

删除索引

Drop index 索引名 on 表名

视图简介：安全和简化操作

Mysql视图是一张虚拟表，其内容由查询定义，同真实的表一样，视图包括一系列带有名称的列和行数据，但是视图并不在数据库中以存储的数据值集形式存在，行和列数据来自由定义视图的查询所引用的表，并且在引用视图的动态生成，对其中所引用的基础表来说，mysql视图的作用类似于筛选。定义视图的筛选可以来自当前或其他数据库的一个或多个表，或者其他视图，通过视图进行查询没有任何限制，通过它们进行数据修改时的限制也很少。

视图是存储在数据库中的sql查询语句，它主要出于两种原因，安全原因，视图可以隐藏一些数据，如一些敏感的信息，另一原因是可以使复杂的查询便于理解和使用

创建视图

语法一

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

View 视图名[(字段1,字段2.....)]  as select语句

[with [caseaded|local] check option]; 

语法二

Create view 视图名 as select 语句;

实例：

Create database view; //创建一个数据库，建议创建

Use view

Create view view_user

As select user,host,password from mysql.user;

Select * from view_user;

查看视图

Show tables 查看视图名

Use view;

Show tables;

Show tables status

实例：查看view数据库中视图以及所有表详细信息

Show table status from view\G

实例：查看view数据库中视图名view_user详细信息

Show table status from view like “view_user” \G 

查看视图定义信息

Use view;

Show create view view_user \G

查看视图结构

Use view;

Desc view_user;

修改视图

方法一删除后创建

Use view;

Drop view view_user;

Create view view_user as select user,host from mysqk.user;

Select * from view_user;

方法二：替换原有视图

语法：create or replace view 视图名 as select 语句;

实例：use view;

Create or replace view view_user as select user,password from mysql.user;

方法三：alter 修改视图

语法：alter view 视图名 as select 语句;

实例：use view;

Alter view view_user as select user,password from mysql.user;

通过视图操作基表

查询数据 select    通常都是查询操作

Select * from view_user;

更新数据update

删除数据delect

删除视图

Drop view 视图名

实例：

Use view;

Drop view view_user;

触发器简介

触发器(triggers)是一个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由时间来触发，比如当对一个表进行操作(insert,delect,update)时就会激活它执行,触发器经常用于加强数据的完整约束和业务规则等 

例如：当学生表中增加一个学生的信息时，学生的总数就应该同时改变，因此可以针对学生表创建一个触发器，每次增加一个学生记录时，就执行一次学生总数的计算操作，从而保障学生总数和记录数一致性。 

创建触发器:同一张表最多可以创建六个触发器

语法：create trigger 触发器名称 before|after 触发事件

On 表名 for each row

Begin

 触发器程序体;

End

触发器名称：//最多64字符

for each row //触发器的执行间隔,这句话的意思是指的是针对每一行，如果不加就是针对整张表

Insert|update|delete//触发的事件

On表名//在哪张表上建立触发器

触发器程序体：//触发器所触发的sql语句，语句可以使用顺序，判断，循环等语句，实现一般程序需要的逻辑功能。 

实例：

创建表

Create table student(

Id int unsigned auto_incremnet primary key not null,

Name varchar(50)

);

unsigned //无符号修饰符

往表里插入数据

Insert into student values(1,’jack’);

创建表2总数表

Create table student_total (total int); 

往数表中插入数据

Insert into student_total values(1);

创建触发器实例一student_insert_trigger

Mysql>Delimiter && //定义结束符

Mysql>Create trigger student_insert_trigger after insert 

On student for each now

Begin

Upadte student_total set total=total+1;

End&&

Mysql>Delimiter ; 

创建触发器实例二

Mysql>Delimiter && //定义结束符

Mysql>Create trigger student_insert_trigger after delect

On student for each now

Begin

Upadte student_total set total=total-1;

End&&

Mysql>Delimiter ;

测试效果，往学生表中插入数据，查看总数表结果，

Insert into student values(2,’Alice’);

Select * from student_total ;

查看触发器

方法1通过show triggers语句查看

案例：show triggers\G

方法2通过系统表triggers查看

案例：use information_schema

Select * from triggers\G

SELECT * FROM TRIGGERS WHERE TGIGGER_NAME=’触发器名称’\G

删除触发器

Drop trigger 触发器名称

实例：Drop trigger student_insert_trigger;

触发器的案例

案例一：增加tab1表记录后自动将记录增加到tab2中,能同步的字段的数据类型肯定要必须一致才行。

创建tab1

Drop table if exists tab1;

Create table tab1(

Tab1_id varchar(11)

); 

创建tab2

Drop table if exists tab2;

Create table tab2(

Tab2_id varchar(11)

);

触发器：after_tab1_trigger

作用：增加tab1表记录后自动将记录增加到tab2中

Mysql>Delimiter && //定义结束符

Mysql>drop trigger if exists tab1_after_trigger;

Mysql>Create trigger tab1_after_trigger after insert

On tab1 for each now

Begin

Insert into tab2(tab2_id) values (new tab1_id);  new关键字指的是tab1插入以后的新增值，在删除的时候就应该是用old关键字

End&&

Mysql>Delimiter ; 

案例三：

当我们更新学生表学生信息的时候，我们学生更新表也对应的改变。

创建表

Drop table if exists student;

Create table student(

student_id int auto_incremnet primary key not null,

Student varchar(30) not null,

Student_sex enum(‘m’,’f’) default ‘m’

);

插入数据：

Insert into student values

(1,’jack’,’m’),

(2,’robin’,’m’),

(3,’alice’,’f’); 

创建tab2

Drop table if exists update_student;

Create table update_student(

Update_record int auto_incremnet primary key not null,

Student_id int not null,

Update_date date

);

插入数据：

Insert into update_student values

(1,1,now()),

(2,2,now()),

(3,3,now()); 

创建触发器ustudent_trigger

Mysql>Delimiter && //定义结束符

Mysql>drop trigger if exists student_update_trigger;

Mysql>Create trigger student_update_trigger before update

On studnet for each now

Begin

If new.student_id!=old.student_id then

Update update_student

Set student_id=new.student_id

Where student_id=old.student_id;

End if;

End&&

Mysql>Delimiter ;

改后的值叫new值，改之前叫做old值，这句话表示学生id被修改后，就会把学生id新值也会赋值给update_student表

删除同步操作案例

Mysql>Delimiter && //定义结束符

Mysql>drop trigger if exists student_delete_trigger;

Mysql>Create trigger student_delete_trigger before delete

On studnet for each now

Begin

Delete from update_student

Where student_id=old.student_id;

End&&

Mysql>Delimiter ;

对于以上关于mysql数据库的高级应用索引、视图，触发器的详细介绍，大家是不是觉得非常有帮助。如果需要了解更多内容，请继续关注我们的行业资讯，相信你会喜欢上这些内容的。