mysql数据库索引和视图,触发器

  1. 云栖社区>
  2. 博客>
  3. 正文

mysql数据库索引和视图,触发器

技术小牛人 2017-11-06 19:43:00 浏览657
展开阅读全文

引简介  跟存储引擎有很大关系其实就是一种排序,生成一种算法,索引主要用在大数据量的时候使用,数据小根本没必要

索引在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 ;

本文转自    探花无情   51CTO博客,原文链接:http://blog.51cto.com/983865387/1917419


网友评论

登录后评论
0/500
评论
技术小牛人
+ 关注