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

声明和定义在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;

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

原文链接: https://www.cnblogs.com/-cqq/p/8059729.html

欢迎关注

微信关注下方公众号，第一时间获取干货硬货；公众号内回复【pdf】免费获取数百本计算机经典书籍