Mysql高级_索引_视图_存储过程_存储函数_触发器

1.索引

文章目录

1.1 索引概述

索引是一种数据结构

索引分为BTree索引,Hash索引,RTree索引(空间索引),Full-text索引(全文索引)
而Mysql默认为BTree索引,Mysql默认引擎为InnoDB

Btree索引和B+树请看数据结构的详细

这里只是简单的说一下BTree数和B+Tree的区别.
B树由于叶子节点和其他节点都有数据索引存储的数据的量比不上B+树。B+树只有叶子节点放的是数据,而其他的节点放的都是键值和指针

1.2 索引的分类

• 单值索引:即一个索引只包含单个列，一个表可以有多个单列索引
• 复合索引:与上面的单值索引做对比,为几个列形成的索引
• 唯一索引:用唯一的列做的索引,比如id,telephone,身份证号
• 主键索引:一般用主键当的索引.如果一个表不指定索引的话也会有主键索引
• 聚簇索引:数据和索引放在一起的,Innodb默认支持聚簇索引
• 非聚簇索引
• 覆盖索引

1.3 索引的语法

准备环境:

create database demo_01 default charset=utf8mb4;

use demo_01;

CREATE TABLE `city` (
  `city_id` int(11) NOT NULL AUTO_INCREMENT,
  `city_name` varchar(50) NOT NULL,
  `country_id` int(11) NOT NULL,
  PRIMARY KEY (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `country` (
  `country_id` int(11) NOT NULL AUTO_INCREMENT,
  `country_name` varchar(100) NOT NULL,
  PRIMARY KEY (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


insert into `city` (`city_id`, `city_name`, `country_id`) values(1,'西安',1);
insert into `city` (`city_id`, `city_name`, `country_id`) values(2,'NewYork',2);
insert into `city` (`city_id`, `city_name`, `country_id`) values(3,'北京',1);
insert into `city` (`city_id`, `city_name`, `country_id`) values(4,'上海',1);

insert into `country` (`country_id`, `country_name`) values(1,'China');
insert into `country` (`country_id`, `country_name`) values(2,'America');
insert into `country` (`country_id`, `country_name`) values(3,'Japan');
insert into `country` (`country_id`, `country_name`) values(4,'UK');

1.3.1 创建索引:

CREATE  [UNIQUE|FULLTEXT|SPATIAL]  INDEX index_name 
[USING  index_type]
ON tbl_name(index_col_name,...)


index_col_name : column_name[(length)][ASC | DESC]

1.3.2 查看索引:

show index  from  table_name;

1.3.3 删除索引

DROP  INDEX  index_name  ON  tbl_name;

1.3.4 更改索引

1). alter  table  tb_name  add  primary  key(column_list); 

  该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
  
2). alter  table  tb_name  add  unique index_name(column_list);
  
  这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）
  
3). alter  table  tb_name  add  index index_name(column_list);

  添加普通索引， 索引值可以出现多次。
  
4). alter  table  tb_name  add  fulltext  index_name(column_list);
  
  该语句指定了索引为FULLTEXT， 用于全文索引

1.4 索引创建规则

不需要建索引：

1. 数据量小
2. 经常增删改的表
3. where条件里用不到的字段不创建索引

需要:

1. where条件后字段建索引
2. 频率高,数据量大
3. 尽量使用唯一索引,区分度高,查询效率大

索引也是数据结构,多了占空间,经常增删改的表不适合建索引
尽可能建立短索引

2 视图

View
是一种虚拟的表,是一条select语句执行后的结果集。

视图:简单,安全,数据独立

2.1 创建视图

create view view_1 as select a.*, b.country_name from city a inner join country b on a.country_id = b.country_id;

2.2 查看视图

show tables;

2.3 删除视图

drop view view_1;

2.4 利用视图

select * from view_1;

3.存储过程与存储函数

调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

存储过程和函数的区别在于函数必须有返回值，而存储过程没有。

函数 ： 是一个有返回值的过程 ；

过程 ： 是一个没有返回值的函数 ；

3.1 创建存储过程

CREATE PROCEDURE procedure_name ([proc_parameter[,...]])
begin
  -- SQL语句
end ;

比如

delimiter $

create procedure pro_test1()
begin
  select 'Hello Mysql' ;
end$

delimiter ;

3.2 调用存储过程

call procedure_name() ; 

3.3 查看储存过程

-- 查询存储过程的状态信息
show procedure status;

3.4 删除存储过程

DROP PROCEDURE  [IF EXISTS] sp_name ；

4. 存储函数

CREATE FUNCTION function_name([param type ... ]) 
RETURNS type 
BEGIN
  ...
END;

比如

delimiter $

create function count_city(countryId int)
returns int
begin
  declare cnum int ;
  
  select count(*) into cnum from city where country_id = countryId;
  
  return cnum;
end$

delimiter ;

调用

select count_city(1);

select count_city(2);

5.触发器

触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作 。

使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

5.1 创建触发器

create trigger trigger_name 

before/after insert/update/delete

on tbl_name 

[ for each row ]  -- 行级触发器

begin

  trigger_stmt ;

end;

需求:
通过触发器记录 emp 表的数据变更日志 , 包含增加, 修改 , 删除 ;

创建一张日志表

create table emp_logs(
  id int(11) not null auto_increment,
  operation varchar(20) not null comment '操作类型, insert/update/delete',
  operate_time datetime not null comment '操作时间',
  operate_id int(11) not null comment '操作表的ID',
  operate_params varchar(500) comment '操作参数',
  primary key(`id`)
)engine=innodb default charset=utf8;

创建insert触发器

DELIMITER $

create trigger emp_logs_insert_trigger
after insert 
on emp 
for each row 
begin
  insert into emp_logs (id,operation,operate_time,operate_id,operate_params) values(null,'insert',now(),new.id,concat('插入后(id:',new.id,', name:',new.name,', age:',new.age,', salary:',new.salary,')'));  
end $

DELIMITER ;

创建update触发器

DELIMITER $

create trigger emp_logs_update_trigger
after update 
on emp 
for each row 
begin
  insert into emp_logs (id,operation,operate_time,operate_id,operate_params) values(null,'update',now(),new.id,concat('修改前(id:',old.id,', name:',old.name,', age:',old.age,', salary:',old.salary,') , 修改后(id',new.id, 'name:',new.name,', age:',new.age,', salary:',new.salary,')'));                                                                      
end $

DELIMITER ;

创建delete触发器

DELIMITER $

create trigger emp_logs_delete_trigger
after delete 
on emp 
for each row 
begin
  insert into emp_logs (id,operation,operate_time,operate_id,operate_params) values(null,'delete',now(),old.id,concat('删除前(id:',old.id,', name:',old.name,', age:',old.age,', salary:',old.salary,')'));                                                                      
end $

DELIMITER ;

测试

insert into emp(id,name,age,salary) values(null, '光明左使',30,3500);
insert into emp(id,name,age,salary) values(null, '光明右使',33,3200);

update emp set age = 39 where id = 3;

delete from emp where id = 5;

5.2 删除触发器

drop trigger [schema_name.]trigger_name

5.3 查看触发器

show triggers ；