mysql存储结构是什么 mysql的存储程序包括

1. 存储程序

# 1.存储程序
# 1.1 存储程序：保存和运行在mysql服务中的一个程序
# 1.2 存储程序：
#     1）存储过程
#     2）存储函数
#     3）触发器

# 2.存储过程
# 2.1 存储过程：保存在mysql服务中一段sql语句
# 2.2 创建存储过程：
#          create procedure 存储过程名字(可选的参数列表)
#			   begin 
#              sql语句
#          end;
# 2.3 存储过程中参数分成：输入参数(in 默认) 输出参数(out) 输入输出参数(inout)

# 示例1：固定查询id为2的学生的名字，把该操作定义为存储过程
# 定义无参的存储过程
create procedure getNameById() 
begin 
	select name from stu_info where id=2;
end;
#调用存储过程
call getNameById();

# 示例2：根据学生的id查询学生的名字，把该操作定义为存储过程
# 定义有参的存储过程（输入参数）
create procedure getNameById2(pid int)
begin 
	select name from stu_info where id=pid;
end;
#调用存储过程
call getNameById2(7);

# 示例3：根据学生的id查询学生的名字并且把名字传出来，把该操作定义为存储过程
# 定义有参的存储过程（输入参数，输出参数）
create procedure getNameById3(pid int, out pname varchar(10))
begin 
	#把查询的name通过into关键字存入输出参数中
	select name into pname from stu_info where id=pid;
end;
#调用存储过程
# 说明：@用于在存储过程外面定义变量
call getNameById3(3,@sname);
# 说明：需要在存储过程的外面查询输出参数中的数据
select @sname;

# 示例4：根据学生的id查询学生的年龄并且把年龄传出来，把该操作定义成为存储过程
# 说明：因为id输入参数和age输出参数的类型一致，所以可以考虑用一个inout参数定义
#       定义inout参数的时候，要保证输入参数和输出参数的类型一致
create procedure getAgeById(inout id_age int)
begin 
	select age into id_age from stu_info where id=id_age;
end;
#调用存储过程
set @pid=4;#在存储过程外面给自定义的变量赋值
call getAgeById(@pid);
select @pid;

# 2.4 在存储过程中使用if分支
# 分支结构的语法：

# if 条件 then 
#		分支代码块1
# elseif 条件 then 
#   分支代码块2
# ... ...
# else 
#   分支代码块n
# end if;

# 示例1：根据输入的季节编号输出季节的名称
create procedure seaon(num int)
begin 
	declare sname varchar(5); # 在存储过程中自定义变量
	if num=1 then 
		set sname='春天'; # 给变量赋值
	elseif num=2 then 
    set sname='夏天';
	elseif num=3 then 
    set sname='秋天';
	else 
		set sname='冬天';
	end if;
  #在分支运行完以后查询季节名
  select sname;
end;
# 调用存储过程
call seaon(3);

# 2.5 在存储过程中使用循环结构
# 2.5.1 分类：while  loop  repeat 

# while循环
# 语法：
#		while 条件 do 
#     循环体
#   end while;
# 示例1：创建存储过程实现指定整数以内的数字的输出
create procedure printNum1(num int)
begin 
	declare i int;
	set i=0;
	while i<num do 
		select i;
		set i=i+1;
  end while;
end;
#调用存储过程
call printNum1(5);

# loop循环
# 语法：

#    [自定义的循环名:]loop 
#       循环体
#       (想结束循环用leave 循环名;)
#    end loop;
#
# 示例2：创建存储过程实现指定整数以内的数字的输出
create procedure printNum2(num int)
begin 
	declare i int;
	set i=0;
	myloop:loop 
		select i;
		set i=i+1;
		if i>=num then
			leave myloop;
		end if;
  end loop;
end;
#调用存储过程
call printNum2(5);

# repeat循环
# 语法：

#   repeat 
#			循环体
#     until 循环结束的条件
#   end repeat;

# 示例3：创建存储过程实现指定整数以内的数字的输出
create procedure printNum3(num int)
begin 
	declare i int;
	set i=0;
	repeat 
		select i;
		set i=i+1;
		until i>=num # 此处没有;
  end repeat;
end;
#调用存储过程
call printNum3(5);


# 3.存储函数
# 3.1 存储函数：是保存和运行在mysql服务中的一段程序
#               （可以理解成为能够定义返回值的存储过程）
# 3.2 语法：

#   create function 存储函数名字(形参列表)
#   returns 返回值类型
#   begin 
#        函数体
#        return 返回值;
#   end;

# 示例1：根据学生的id查询学生的名字并且返回
create function findNameById(pid int)
returns varchar(10) # 定义函数的返回值类型
begin 
	declare sname varchar(10);
	select name into sname from stu_info where id=pid;
  return sname; # 返回数据
end;
# 调用存储存储函数
select findNameById(6);

# 4.存储过程和存储函数的区别：
# 1）存储过程不能定义返回值，只能通过输出参数传出数据
# 2）存储函数，只能通过返回值传出数据，并且必须要有返回值
# 3）存储过程就是定义一个功能的实现过程的，所以直接可以调用
# 4）存储函数是用来返回一个结果的，所以存储函数一定要用到sql语句中
# 5）定义的关键字不一样，一个是procedure，一个是function

# 5.存储过程和存储函数的相同点：
# 1）都是保存和运行在mysql服务中的一段程序
# 2）都是可以用来把一些常用的数据库操作事先定义出来，方便后续的使用

# 6.触发器
# 6.1 触发器：监测表的操作的，当表中发生了对应的操作，触发器就会执行。
# 示例1：实现银行系统备份注销用户的信息
# 语法：
#     create trigger 触发器名
#     触发器执行时机 事件类型
#     on 表名 for each row 
#     begin 
#        触发器体
#     end;

# 语法解释：
#      触发器执行时机：before after
#      事件类型：insert update delete
#      for each row ：给表中的每一行添加上监测

# 复制bank表的结构，用于存储备份的用户信息
create table bank_tmp select * from bank where 1>10;
# 创建触发器
create trigger tri_bank
after delete 
on bank for each row
begin 
  #把删除的数据保存到备份表中
  #当bank原表中发生了删除事件之后，删除的数据会被触发器拿到，用old对象传入
	insert into bank_tmp values(old.id,old.name,old.pwd,old.money);
end;
# 删除bank表中的一条记录(产生事件)
delete from bank where id=3;

7.	存储程序的优缺点：
   优点：保存和运行在mysql服务中的，所以运行的效率比较高
   缺点：保存于服务中，不便于维护和拓展
         会占据服务的资源，影响系统的运行效率

2. 视图

# 1. 视图
# 1.1 视图：是一张虚拟的数据库表，并不实际存在该张表。
# 1.2 视图的特点：在操作视图的时候，其实是动态的通过操作原表来实现功能。
# 1.3 视图创建的语法：create view 视图名 as 一个sql语句;
# 1.4 通过视图实现crud操作：跟在原表中实现crud的操作一样
# 1.5 限制视图数据的可用性：with check option(通过视图做的修改，必须还能通过视图看到)
# 1.6 删除视图：drop view 视图名;
# 1.7 为什么使用视图：
#       1)使用视图可以保证数据的安全性(不提供银行余额)
#       2)简化开发，提升开发效率(把常用的表连接事先写好，用视图管理)
#       3)定制化数据(可以根据需要，任意的组织数据库中的表形成数据)


# 示例1：查询stu_info表中的所有数据，并创建一个视图管理。
create view stuview as select * from stu_info;

# 示例2：查询部门编号为30的员工的名字和薪水以及部门编号，并创建视图管理
create view empview as select ename,sal,deptno from emp_copy where deptno=30;

# 示例3：通过empview视图，查询'james'的薪水（通过视图查数据，只能查视图管理的数据）
select sal from empview where ename='james';
select sal from empview where ename='king';

# 示例4：通过empview视图，更新'james'的薪水为2000（通过视图修改数据，数据的修改其实是发生在原表）
update empview set sal=2000 where ename='james';

# 示例5： 通过empview视图，插入新员工信息（通过视图插入数据，数据的修改其实是发生在原表）
# 插不进去，原因：视图想要添加的数据，不符合原表的结构要求，(不符合主键不能为null的约束)
insert into empview(ename,sal,deptno) values('kat',3000,20);
# 插不进去，原因：视图管理的字段跟想插入的数据的个数不一致
insert into empview values(76123,'kat','clerk',7566,'1981-02-20 00:00:00',3000,0,20);
# 可以插入(还是插入了原表)
insert into stuview values(9,1808,'kat',21,16785,167576);

# 示例6：通过stuview删除'kat'的信息（通过视图删除数据，还是操作原表）
delete from stuview where name='kat';

# 示例7：通过empview视图把视图中管理的数据的而部门编号改为10
# 没有限制视图的数据的可用性的
update empview set deptno=10; #（修改完以后，empview视图中就没有可管理的数据了）

# 示例8：通过with check option限制视图数据的可用性
# 查询部门编号为30的员工的名字和薪水以及部门编号，并创建视图管理,且保证视图数据的可用性
create view empview1 as select ename,sal,deptno from emp where deptno=30 with check option;
# 限制完以后，在修改部门的编号
update empview1 set deptno=10;# （限制完以后，不让修改，但是通过原表是可以改的）

# 示例9：删除empview视图
drop view empview;

3. 索引

# 1. 索引
# 1.1 索引：用于提升对于表中的数据的查询的效率而设计的一种结构
# 1.2 分类：(根据底层算法原理)
#     1) B-Tree索引
#     2) 全文索引
#     3) 位图索引
# 1.3 B-Tree索引：其实是一中采用B-Tree查找算法实现的索引。
#      查找算法：顺序查找算法(逐一比较)  
#                二分查找(先对数据进行排序，然后把数据按照中间值分成两个区间)
#      基于二分查找算法演化出了一种数据结构---二叉树(任何一个节点的值要大于左子节点，小于右子节点)
#      二叉树(普通二叉树，平衡二叉树)
#      平衡二叉树由普通二叉树衍生的，要求是任何一节点的左子树和右子树的高度差不能超过1
#      平衡二叉树的查询效率要高于普通的二叉树
#      B-tree是基于平衡二叉树衍生的，称为多路平衡二叉树。
#     (MySql数据库使用B+Tree算法实现索引的创建)

# 1.4 查看sql语句的查询效率
explain select * from stu_info where name='tom';

# 1.5 如何添加索引
# create index 索引名 on 表名(字段名);
create index name_index on stu_info(name);
# 验证添加完索引以后的查询效率
explain select * from stu_info where name='tom';

# 1.6 mysql中的索引的分类：普通索引，唯一索引，全文索引，空间索引
# 表中的主键其实默认就添加了唯一索引
explain select * from stu_info where id=6;

# 1.7 删除索引： drop index 索引名;

# 1.8 使用索引需要注意：
#  1）并不是把表中的所有的字段都添加索引。
#     (因为索引在文件中存着，添加数据需要改索引结构，索引太多影响插入，更新，删除的效率)
#  2）如果一个表的插入，更新，删除的频率高于查询，就不太适合添加索引。
#  3）如果表的数据量很小，也不适合添加索引。
#  4）字段的数据量比较小的，该字段也不适合添加索引。

# MyISAM存储引擎：一张表用以下的3个文件存储
#       .frm  表的结构
#       .myi  表的索引
#       .myd  表的数据

#InnoDB存储引擎：一张表用以下的2个文件存储
#       .frm  表的结构
#       .ibd  表的数据和表的索引