mysql数据库的高级应用索引、视图,触发器的详细介绍
下文主要给大家带来MySQL数据库的高级应用索引、视图,触发器的详细介绍,希望这些内容能够带给大家实际用处,这也是我编辑mysql数据库的高级应用索引、视图,触发器的详细介绍这篇文章的主要目的。好了,废话不多说,大家直接看下文吧。
创新互联是一家专注于做网站、成都网站设计与策划设计,青龙网站建设哪家好?创新互联做网站,专注于网站建设十多年,网设计领域的专业建站公司;建站业务涵盖:青龙等地区。青龙做网站价格咨询:13518219792
索引优缺点
优点
加快访问速度
加强行的唯一性
缺点
带索引的表在数据库中需要更多的存储空间
操纵数据的命令需要更长的处理时间,因为它们需要对索引进行更新
按照下列标准选择建立索引的列
频繁搜索的列
经常用作查询选择的列
经常排序、分组的列
经常用作联接的列(主键/外键)
请不要使用下面的列创建索引
仅包含几个不同值的列
表中仅包含几行
查询时减少使用*返回全部列,不要返回不需要的列
索引应该尽量小,在字节数小的列上建立索引
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),
Indexindex_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 Indexindex_dept_name (dept_name)
);
创建全文索引创建表时实例:
Create table department(
Dept_id int primary key,
Dept_name varchar(30),
Commnet varchar(50),
fulltext Indexindex_dept_name (dept_name)
)engin=myisam;
创建多列索引创建表时实例:
Create table department(
Dept_id int primary key,
Dept_name varchar(30),
Commnet varchar(50),
Indexindex_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_nameon departmnet (dept_name);
创建唯一索引实例:
Create unique index index_dept_nameon departmnet (dept_name);
创建全文索引实例:
Create fulltext index index_dept_nameon departmnet (dept_name);
创建多列索引实例:
Create index index_dept_nameon departmnet (dept_name,comment);
创建索引(已存在的表alter table)
语法:alter table 表名add[unique|fullext|spatial] index索引名(字段名[(长度)][asc|desc]);
创建普通索引实例:
Alter tabledepartment add index index_dept_name(dept_name);
创建唯一索引实例:
Alter tabledepartment add unique index index_dept_name(dept_name);
创建全文索引实例:
Alter tabledepartment add fulltext index index_dept_name(dept_name);
创建多列索引实例:
Alter tabledepartment addindex 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 viewview_user;
Create viewview_user as select user,host from mysqk.user;
Select * from view_user;
方法二:替换原有视图
语法:create or replace view 视图名as select语句;
实例:use view;
Create or replace viewview_user as select user,password from mysql.user;
方法三:alter 修改视图
语法:alter view 视图名as select语句;
实例:use view;
Alter viewview_user as select user,password from mysql.user;
通过视图操作基表
查询数据 select 通常都是查询操作
Select * from view_user;
更新数据update
删除数据delect
删除视图
Drop view 视图名
实例:
Use view;
Drop viewview_user;
触发器简介
触发器(triggers)是一个特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由时间来触发,比如当对一个表进行操作(insert,delect,update)时就会激活它执行,触发器经常用于加强数据的完整约束和业务规则等
例如:当学生表中增加一个学生的信息时,学生的总数就应该同时改变,因此可以针对学生表创建一个触发器,每次增加一个学生记录时,就执行一次学生总数的计算操作,从而保障学生总数和记录数一致性。
创建触发器:同一张表最多可以创建六个触发器
语法:createtrigger 触发器名称 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
Onstudent for each now
Begin
Upadte student_total set total=total+1;
End&&
Mysql>Delimiter ;
创建触发器实例二
Mysql>Delimiter && //定义结束符
Mysql>Create trigger student_insert_trigger after delect
Onstudent 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数据库的高级应用索引、视图,触发器的详细介绍,大家是不是觉得非常有帮助。如果需要了解更多内容,请继续关注我们的行业资讯,相信你会喜欢上这些内容的。
网页题目:mysql数据库的高级应用索引、视图,触发器的详细介绍
浏览地址:http://scyanting.com/article/jhjhid.html