索引、视图、存储过程、函数、游标

声明和定义在mysql中语句的不同：

声明：declare 名称 类型（int / cursor / handler / condition等）

定义：create 类型（view / index /procedure /function等） 名称

①索引：数据库管理系统中用于排序的数据结构，用于实现快速查询更新

   myisam 和innodb默认是BTREE索引，前缀索引：当字符列为text.blob,比较长的varchar时，索引会变得比较慢，这时就需要使用前缀索引，对开始部分字符进行索引。

   create [fulltextuniquespatial] 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 cascadelocal check option]   //local只要满足本视图的条件即可更新，cascade需要满足所有针对该视图的所有视图的条              件才可以更新

修改视图：alter view 视图名 as 查询语句 [with cascadelocal 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;

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 事件名;