索引、视图、存储过程、函数、游标
声明和定义在mysql中语句的不同：
声明：declare 名称 类型（int / cursor / handler / condition等）
定义：create 类型（view / index /procedure /function等） 名称
①索引：数据库管理系统中用于排序的数据结构，用于实现快速查询更新
myisam 和innodb默认是BTREE索引，前缀索引：当字符列为text.blob,比较长的varchar时，索引会变得比较慢，这时就需要使用前缀索引，对开始部分字符进行索引。
create [fulltext\unique\spatial] index 索引名 on 表名(列名(长度))
drop index 索引名 on 表名
设计索引的准则：
a 最适合索引的列是出现在where 语句中的列
b 当存储引擎是innodb时，记录默认会按照一定的顺序保存，当存在主键时，按照主键的顺序保存，没有主键但存在唯一索引时，按照唯一索引保存，又没有主键又没有唯一索引
时，会创建一个内部列，按照内部列的顺序保存。按照主键和内部列顺序保存的访问是最快的。innodb的普通索引会自动保存主键的键值，所以主键需要选择较短的数据类型
hash索引和btree索引
hash索引只能用于=或<=>的比较，而btree索引可以用于<=, >= ,<>, between, like操作符
优化器不能使用hash索引来加速order By操作
hash索引中只能使用整个关键字来搜索一行
Key即键值，是关系模型理论中的一部份，比如有主键（Primary Key)，外键（Foreign Key）等，用于数据完整性检否与唯一性约束等。
而Index则处于实现层面，比如可以对表的任意列建立索引，那么当建立索引的列处于SQL语句中的Where条件中时，就可以得到快速的数据定位，从而快速检索。
②视图 需要create view, select, drop 权限
虚拟的表，并不是数据库中实际存在的，行列的数据是视图中的查询中使用的表，并且是在使用视图时动态生成的。
优点：简单，是过滤好的复合条件的结果集，不需要关心对应的表结构，关联条件和筛选条件
安全，只能访问到被允许查询的结果集
数据独立，可以屏蔽表结构变化对视图的影响
创建视图：create or replace view 视图名as 查询语句 [with cascade\local check option] //local只要满足本视图的条件即可更新，cascade需要满足所有针对该视图的所有视图的条 件才可以更新
修改视图：alter view 视图名 as 查询语句 [with cascade\local check option]
注：mysql的视图中where 语句中不允许出现子查询
create or replace view view_text as select s.stu_name,d.dept_name from stu as s,dept as d where s.dept_no=d.dept_no and s.dept_no<3;
1 create or replace view view_text2 as select * from view_text where dept_no>1;
删除视图：drop view 视图名[if exists] //一次可以同时删除多个视图
查看视图：show tables;//不存在show views，show tables 既可以查看表又可以查看视图
show table status like '视图名'
show create view 视图名
select * from information_schema.VIEWS;
③存储过程 create routine, alter routine, execute 权限
存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合，可以减少数据在数据库和服务器之间的传输，提高效率
存储过程和函数的区别：
1）函数必须有返回值，而存储过程没有，存储过程的参数可以使用in out inout，而函数的参数只能使用in，如果有函数需要从其他类型的数据库转移到mysql数据库，需要将函数转换成存储过程。
2）对于存储过程来说可以返回参数，而函数只能返回值或者表对象。
3）存储过程一般是采用call语句进行调用，而函数可以作为查询语句的一部分，当函数的返回值是一个表对象是还可以作为查询语句中from后面的表对象。
4）存储过程和函数执行时先到procedure cash（execution plan）中去取，如果没有就会对存储过程和函数进行编译。
创建存储过程：delimiter $$ //将分隔符改成$$
create procedure 存储过程名(in/ out/ inout 参数名 类型)
begin
sql security definer / sql security invoker 分别表示以创建者的权限执行和以调用者的权限执行，当以调用者的权限执行时，如果调用者是其他用户，而系统赋予的 其他用户的权限不够，则不能执行
...
end
delimiter ;
调用存储过程：call 存储过程名（参数）
use db; delimiter $$ create procedure pro (in s_no int ,in d_no int,out num int) sql security definer begin select * from stu where stu_no<s_no and dept_no<d_no; select found_rows() into num; end $$ delimiter ;
call pro(3,3,@i);
select @i;
删除存储过程：drop procedure 存储过程名
查看存储过程或函数：show procedure/ function status like ''
查看存储过程或函数的定义：show create procedure/ function 存储过程名
select * from information_schema.routines where routine_name='存储过程名'
select * from information_schema.ROUTINES where ROUTINE_NAME='pro'
④函数
delimiter $$
create function 函数名(参数名 参数类型) //在function中的参数只能是输入型参数
returns 参数类型
begin
......
return 返回值的参数名
end $$
delimiter ;
调用函数用 select 函数名(参数);
use db; delimiter $$ create function fun_test(s_no int) returns varchar(10) begin declare name_temp varchar(10); select stu_name into name_temp from stu where stu_no=s_no; return name_temp; end $$ delimiter ;
use db; select fun_test(1);
定义局部变量 ：declare @ 变量名
set 变量名=‘’
⑤定义条件和处理
定义：declare 条件名 condition for 条件值
处理：declare 处理类型 handler for 条件值
条件类型：continue //继续执行
exit //退出
条件值： sqlstate [sql状态值]
条件名
sqlwarning //sql警告
not found //没有找到相关的记录
sqlexception //sql异常
use db; delimiter $$ create procedure pro_text1() begin declare continue handler for sqlexception set @i=1; set @a=1; insert into stu values(1,'lucy',2); set @a=2; insert into stu values(4,'luci',2); end $$ delimiter ;
⑥游标
declare 游标名 cursor for 查询语句（查询特定表中的一列或几列作为游标的标志列，一般是后面语句中的where语句中的列名）
open 游标名
fetch 游标名 into 定义的变量名
close 游标名
use db; drop procedure if exists proc_cursor; delimiter % create procedure proc_cursor() begin declare num int; declare str varchar(10) default '+'; declare cur cursor for select dept_no from dept; declare exit handler for not found close cur; open cur; repeat fetch cur into num; update dept set dept_name=concat(dept_name,str) where dept_no=num; set str=concat(str,'+'); until 0 end repeat; close cur; end% delimiter ;
注：变量、条件、处理程序、游标的声明都是通过declare 实现的，声明是有先后顺序的，先后顺序是：变量、条件的声明 ， 游标的声明 ， 处理程序的声明在最后
⑦流程控制
1、 if 语句：
if 条件
then 操作
elseif 条件
then 操作
else
操作
end if;
2、 case 语句：
case
when 条件(a=b) then 操作;
when 条件(a=c) then 操作;
else 操作
end case ;
或：
case a
when 值(b) then 操作;
when 值(c) then 操作;
else 操作
end case ;
在select 语句中使用case语句
use db; select stu_name, case when stu_no>2 then 'after' else 'before' end stage from stu;
结果：
3、loop语句
[begin_label:] loop
操作
end loop[end_label]
loop语句需要在循环体中自定义退出循环的语句，否则该循环将变成一个死循环
4、leave语句 相当于C++里面的break语句
注：在procedure 中声明变量用declare ,当声明varchar 变量时初始化，当变量用declare声明后，在后续使用该变量时不需要用@变量名，而是直接使用变量名
use db; delimiter % drop procedure if exists proc_leave% create procedure proc_leave() begin declare str varchar(10) default ''; declare i int default 0; declare total int default 0; declare num int; select count(*) into num from dept; ins:loop if i>3 then leave ins; end if; set i=i+1; set total=i+num; set str=concat(str,'a'); insert into dept values (total,str); end loop ins; end% delimiter ;
5、iterate 语句 相当于C++中的continue语句，跳出本层循环
use db; drop procedure if exists proc_iterate; delimiter @ create procedure proc_iterate() begin declare num int; declare cur cursor for select dept_no from dept; declare exit handler for not found close cur; open cur; rp:repeat fetch cur into num; if num%2=0 then update dept set dept_name=concat(dept_name,'@') where dept_no=num; else iterate rp; end if; until 0 end repeat; close cur; end@
6、repeat 语句
repeat
操作
until 条件 end repeat
use db; drop procedure if exists proc_iterate; delimiter @ create procedure proc_iterate() begin declare num int; declare flag int default 1; declare cur cursor for select dept_no from dept; declare exit handler for not found close cur; open cur; rp:repeat fetch cur into num; if num%2=1 then update dept set dept_name=concat(dept_name,'!') where dept_no=num; set flag=0; else iterate rp; end if; until flag=0 end repeat; close cur; end@
7、while语句
while 条件
do
操作
end while
⑦事件调度器 有点像定时器timer()定时执行一个操作
可以将数据库按照自定义的时间周期触发某种操作
create event 事件名
on schedule 时间发生的时间或频次
do 执行的操作
例1：在当前时间的一段时间后执行某个操作：
create event event_test1 on schedule at current_timestamp+interval 1 minute do insert into date_test values(now());
在事件创建后，并没有执行
查看时间调度器的状态： show events;
show variables like '%schedule%';
此时时间时刻表并没有打开，需要在超级用户的权限下将event_scheduler设置为1
打开时间调度器：set global event_scheduler=1;
例2 每个一段时间定时进行某个操作
create event event_test1 on schedule every 1 minute do insert into date_test values(now());
在执行一段时间后，该数据表将变得很大，创建一个新的时间调度器去定时删除某些数据
create event event_truncate_test on schedule every 5 minute do truncate table date_test;
禁用和删除事件：
alter event 事件名 disable;
drop event 事件名;
