1.索引
1.1 索引概述
Mysql官方对索引的定义是：索引(index)是帮助mysql高效获取数据的数据结构(有序)。在数据库之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某些方式引用(指向)数据，这样就可以在数据结构上实现高级算法的查找，这种数据结构就是索引。如下图所示：
左边是数据库，一共有两列七条数据，最左边是数据记录的物理地址，为了加快数据的查找，可以维护一个右边所示的二叉树，每个节点包含索引值和一个指向对应数据记录的物理地址的指针，这样就可以用二叉查找快速定位到相应的数据。
一般来说，索引本身也很大，不可能完全存储在内存中，因此索引往往是以索引文件的形式存储在磁盘上。
1.2 索引优劣势
优势
- 类似于书记的目录索引，提高数据检索效率，降低数据库的IO成本
- 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。
劣势
- 实际上索引也是一张表，该表中保存了主键和索引字段，并指向实体类的记录，所以索引也是占用空间的
- 虽然索引大大提高了数据查询效率，同时也降低了数据的更新速度，比如对表进行INSERT,UPDATE,DELETE，因为更新数据时，mysql不仅要保存数据，还要保存索引文件每次更新添加了索引列的字段。
1.3 索引结构
索引是在MySQL的存储引擎层实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定相同，也不是所有的存储引擎都支持所有的索引类型。MySQL目前提供了以下4中索引：
- BTREE索引：最常见的索引类型，
- HSAH索引：只有memory引擎支持
- R_TREE索引：也叫空间索引，是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
- Full-text索引：全文索引，MyISAM的一个特殊索引类型
我们平常说的索引，如果没有明确指明，都是指B+树。其中聚簇索引、复合索引、前缀索引、唯一索引默认都是使用B+树索引，统称为索引。
1.3.1 MySQL中的B+Tree
B+Tree的数据结构这里就不作介绍了，可以自己去了解下。
MySQL索引的数据结构对经典的B+Tree进行了优化，在原B+Tree的基础上，增加了一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。
MySQL中的B+Tree索引结构示意图：
B+Tree只有叶子节点保存数据。
1.4 索引分类
1.4.1 单值索引
一个索引只包含单个列，一个表可以有多个单列索引
1.4.2 唯一索引
索引列的值必须唯一，但允许有空值
1.4.3 复合索引
一个索引包含多个列
1.4.4 聚簇索引
聚簇索引就是索引结构和数据一起存放的索引。主键索引属于聚簇索引。
在MySQL中，Innodb引擎的表的.ibd文件就包含了该表的索引和数据，对于innodb引擎表来说，该表索引的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。
1.4.5 非聚簇索引
非聚簇索引就是索引结构和数据分开存放的索引。
二级索引属于非聚簇索引。
非聚簇索引的叶子节点并不一定存放数据的指针，因为二级索引的叶子节点存放的是主键，根据主键再回表差数据。
1.4.6 覆盖索引
如果一个索引包含(或者说是覆盖)所有需要查询的字段的值，我们就称之为覆盖索引。
在innodb存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值，最终还是要回表查询，也就是通过主键再查找一次，这样就会比较慢，覆盖索引就是把要查询的列和值放在一起，不用回表查询。
2. 视图
2.1 视图概述
视图(VIEW)是一种虚拟存在的表。视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且在使用视图时动态生成的。通俗的将就是一个select语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这个sql查询语句上。
视图对于普通表的优势主要包括以下几点：
- 简单：使用视图的用户完全不用关心后面对应的表的结构、关联条件和筛选条件，对用户来说，已经是过滤好的复合条件的结果集。
- 安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某行某列，但是通过视图就可以简单是下面你。
- 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响。
3. 存储过程
3.1 存储过程和函数的概述
存储过程和函数是视线经过编译并存储在数据库中的一段sql语句的集合。调用存储过程和函数可以减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。
存储过程和函数的区别在于函数必须有返回值，而存储过程没有。
3.2 创建存储过程
delimiter $
create procedure pro_test1()
begin
select 'Hello Mysql' ;
end$
delimiter ;
3.3 调用存储过程
call procedure_name();
3.4 语法
3.4.1 变量
通过DECLARE可以定义一个局部变量，该变量的作用范围只能用在BEGIN … END块中。
delimiter $
create procedure pro_test02();
begin
declare num int default 5;
select num+10;
end $
delimiter ;
直接赋值使用SET,可以赋值常量或者赋表达式
delimiter $
create procedure pro_test03();
begin
declare name varcahr(20);
set name = 'mysql';
select name;
end $
delimiter ;
也可以通过SET … INTO 方式进行赋值操作；
DELIMITER $
CREATE PROCEDURE pro_test04()
BEGIN
declare countnum int;
select count(*) into countnum from city;
select countnum;
END$
DELIMITER ;
3.4.2 if条件判断
delimiter $
create procedure pro_test05()
begin
declare height int default 175;
declare description varchar(50);
if height >= 180 then
set description ='身材好';
elseif height >=170 and height <180
set description = '标准身材';
else
set description = '一般身材';
end if;
select description;
end $
delimiter ;
3.4.3 传递参数
IN -输入
delimiter $
create procedure pro_test06(in height int)
begin
declare description varchar(50) default '';
if height >= 180 then
set description='身材高挑';
elseif height >= 170 and height < 180 then
set description='标准身材';
else
set description='一般身材';
end if;
select concat('身高 ', height , '对应的身材类型为:',description);
end$
delimiter ;
OUT - 输出
create procedure pro_test07(in height int , out description varchar(100))
begin
if height >= 180 then
set description='身材高挑';
elseif height >= 170 and height < 180 then
set description='标准身材';
else
set description='一般身材';
end if;
end$
调用
call pro_test07(168, @description)$
select @description$
@description : 这种变量要在变量名称前面加上“@”符号，叫做用户会话变量，代表整个会话过程他都是有作用的，这个类似于全局变量一样。
3.4.4 case结构
delimiter $
create procedure pro_test08(month int)
begin
declare result varchar(20);
case
when month >= 1 and month <=3 then
set result = '第一季度';
when month >= 4 and month <=6 then
set result = '第二季度';
when month >= 7 and month <=9 then
set result = '第三季度';
when month >= 10 and month <=12 then
set result = '第四季度';
end case;
select concat('您输入的月份为 :', month , ' , 该月份为 : ' , result) as content ;
end$
delimiter ;
3.4.5 while结构
delimiter $
create procedure pro_test8(n int)
begin
declare total int default 0;
declare num int default 1;
while num<=n do
set total = total + num;
set num = num + 1;
end while;
select total;
end$
delimiter ;
3.4.6 repeat结构
有条件的循环控制语句，当满足条件的时候退出循环。while是满足条件才执行，repeat是满足条件就退出循环。
delimiter $
create procedure pro_test10(n int)
begin
declare total int default 0;
repeat
set total = total + n;
set n = n - 1;
until n=0
end repeat;
select total ;
end$
delimiter ;
3.4.7 loop语句
loop实现简单的循环，退出循环的条件需要使用其他的语句定义，通常可以使用leave语句实现；
delimiter $
create procedure pro_test11(in n int)
begin
declare total int default 0;
ins : loop
if n <= 0 then
leave ins;
end if;
set total =total +n;
set n=n-1;
end loop ins;
select total;
end $
delimiter ;
3.4.8 游标/光标
游标是用来存储查询结果集的数据类型，在存储过程和函数中可以使用光标对结果集进行循环的处理。
声明游标
declare cursor_name CURSOR FOR select_statement;
OPEN游标
OPEN cursor;
FETCH游标
FETCH cursor_name into var_name...
CLOSE游标
CLOSE cursor_name;
初始化脚本：
create table emp(
id int(11) not null auto_increment ,
name varchar(50) not null comment '姓名',
age int(11) comment '年龄',
salary int(11) comment '薪水',
primary key(`id`)
)engine=innodb default charset=utf8 ;
insert into emp(id,name,age,salary) values(null,'金毛狮王',55,3800),(null,'白眉鹰王',60,4000),(null,'青翼蝠王',38,2800),(null,'紫衫龙王',42,1800);
-- 查询emp表中数据, 并逐行获取进行展示
create procedure pro_test11()
begin
declare e_id int(11);
declare e_name varchar(50);
declare e_age int(11);
declare e_salary int(11);
declare emp_result cursor for select * from emp;
open emp_result;
fetch emp_result into e_id,e_name,e_age,e_salary;
select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary);
fetch emp_result into e_id,e_name,e_age,e_salary;
select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary);
fetch emp_result into e_id,e_name,e_age,e_salary;
select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary);
fetch emp_result into e_id,e_name,e_age,e_salary;
select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary);
fetch emp_result into e_id,e_name,e_age,e_salary;
select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary);
close emp_result;
end$
4. 函数
定义一个函数，请求满足条件的总记录数
delimiter $
create function count_city(countryId int)
return int
begin
declare num int;
select count(*) into num from city where country_id = countryId;
return num;
end $
delimiter ;
调用
select count_city(1);
5. 触发器
5.1 介绍
触发器是与表有关的数据库对象，指在insert/update/delete之前或之后，出发并执行触发器中定义的sql语句集合，触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。
使用别名OLD和NEW 来引用触发器中发生变化的记录内容。
5.2 创建触发器
语法结构：
create trigger trigger_name
before/after insert/update/delete
on tab_name
[for each row] -- 行级触发器
begin
trigger_statement;
end;
创建 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 ;
212
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
