一、回顾
视图
外键
事务
数据管理
用户管理
二、MySQL变量
MySQL的变量有两种，一种是系统变量，另一种是用户自定义变量。
用户自定义变量又分为全局变量与局部变量。
1．系统变量
系统变量是系统已经定义好的变量，用户一般不会使用到系统的变量，系统的变量控制着整个系统的操作属性以及表现。
比如说 ： autocommit , auto_increment_increment , auto_increment_offset.
1)查看系统变量：
show variables;
2)查看具体的变量值：
//只记得变量的一部分
show variables like ‘%auto_increment%’;
//完整的记着变量的名称
select @@变量名称；
MySQL系统为了区分系统变量与用户自定义的全局变量，如果是系统的变量的话，需要在变量前面加两个@，如果是 用户自定义的全局变量，只需要加一个@，如果是自定义的局部变量不需要加@。
image.png
3)修改系统变量
修改会话级别的系统变量：
只会对当前用户的本次通话起作用
set names gbk;
set 变量名=变量值；
set @@变量名=变量值；
image.png
image.png
全局修改：
对所有的而客户端一次修改永久生效。
set global 变量名=值；
set @@global.变量 = 值；
注意：使用global的时候不需要添加@@符号。
image.png
注意：names 本质上不是一个系统变量，而是三个系统的一个集合操作。
注意：全局修改需要重启客户端才能生效。
2．全局变量：
全局变量使用一个@。
1)定义全局变量
set @变量名 = 值； //指定默认值。
image.png
必须要有默认值。
image.png
2)查看全局变量
select @变量名
image.png
3)全局变量赋值
set @变量名 = 值；
image.png
MySQL允许从数据表中获取数据赋值给变量：两种方式：
方案一：边赋值边看结果
select @name:=name from 数据源.
image.png
image.png
注意：在MySQL中=是赋值符号，同时也是逻辑判断相等的符号，在这里产生了歧义。所以在这里创建了一个新的符号 （：=），以后再使用到赋值的时候尽量使用这个符号。
方案二：只赋值，不显示结果，一次只能取出一条，不会后来覆盖。
select name,money from my_account into @name,@money;
image.png
看图说话：
select可以查询多个变量，每一个变量之间使用逗号隔开。
3．局部变量
局部变量的定义
基本语法：
declare 变量名 类型 【default】 默认值；
局部变量在函数的内部声明，通常在begin 之后，定义好之后只能在函数的内部使用，不能再函数外部使用。
省略括号语法：
image.png
在MySQL函数中，如果函数体只有一句话的话，可以不写begin和end，相当于括号的省略、
局部变量赋值：
set 变量名 = 值;
三、MySQL的流程控制
1)分支结构
if 条件 then
语句1；
else if 条件 then
语句2；
else
语句n;
end if;
2)循环结构
标签名：while 条件 do
循环语句；
变量递增
end while；
3)循环结束与跳转
iterate 标签名； 跳转到下次循环，相当于continue
leave 标签名 ； 结束循环，相当于break
四、MySQL函数
MySQL函数分为系统函数与自定义函数
1．系统函数
复习：count ，max ，min，avg, sum , now
函数的调用方式：
任何函数都是具有返回值的，所以函数的调用是使用select实现。
1)字符串系列函数
char_length(字符串):字符串的长度
image.png
image.png
length（字符串）：字节长度
image.png
image.png
substring(字符串，开始位置，长度):字符串截取。
image.png
image.png
看图说话：
第二个参数，截取的位置是从1开始的，但是0位置还是占用一个字节的。
第三个参数，截取的长度是按照字节截取的。
instr（父字符串，查抄的内容）：判断某个在字符串是否存在另一个字符串当中。
image.png
成功的话返回字符串的位置。未找到返回0。
image.png
说明位置是按照字节显示的。但是有个区别，数据库字符串存储是从0开始的，返回的位置是下一个字符的起始位置。0不作为字符串查询位置的返回值。
lpad(字符串，长度，内容):从字符串的左侧进行字符串填充。
image.png
第一个参数是原始字符串。
第二个参数是字符串的最终长度。
第三个是填充的内容。如果内容的长度不足以一次填充完全，应该重复填充，同时超出的时候应该截取。
insert（str,start,length,content）：替换。查找到字符串当中的某个位置替换指定的内容。
image.png
image.png
字符填充位置也是字节位。从位置替换之后后面的按照长度截取。
image.png
看图说话：我们发现@name 并没有发生任何变化，说明上面所有对字符串@name 进行操作的函数都是复制的变量的值，而不是直接改变变量，产生的返回值都是使用自己的变量保存的。
strcmp():字符串比较
image.png
image.png
image.png
看图说话：
当第一个字符串大的时候返回的是 1，两个相等的时候，返回的是0，当第二个比较大的时候返回的是-1.
concat(str1,str2):字符串连接函数。
image.png
LTrim()：去除左边的空格.RTrim()：去除右边的空格
Upper()：返回大写字符。PHP中为strtoupper . Lower()：返回小写字符，Strtolower
image.png
Left(str,len) ：返回串左边指定数目的字符
image.png
image.png
Right(str,len)：返回串右边指定数目的字符
2)日期时间函数
Year()：返回一个日期的年份部分
image.png
date():返回指定时间的日期部分。直接返回当前时间的日期使用curdate().
image.png
date():返回指定时间的日期部分。直接返回当前时间的日期使用curdate().
image.png
time():返回指定时间的时间部分，直接返回当前时间的时间部分使用curtime();
image.png
image.png
datediff():求两个日期的时间差。
image.png
now():获取当前时间日期
3)数学函数
abs():取绝对值。
image.png
ceil():向上取整
image.png
floor:向下取整。
image.png
round():四舍五入
image.png
rand()：随机数函数
image.png
4)其他函数
MD5（）：32位加密函数：
image.png
version（）：当前数据库版本
image.png
database():当前数据库名
image.png
uuid():使用开源软件生成的唯一识别码。保证在同一时空是唯一的。分布式的概念
image.png
2．自定义函数
数学函数的三要素：定义域，值域，对应关系
编程函数的三要素：参数，返回值，函数体。
1)定义函数
基本语法：
create function （参数 参数类型） returns 返回值类型
begin
函数体
返回值 ：指定的类型
end
当函数的函数体只有一句的时候可以省略开始于结束语句。
create function fun() returns int
return 100;
image.png
1)自定义函数的调用
select 函数名（）；
image.png
自定义函数调用与系统函数调用一致，都是使用select，需要注意的是，函数是必须具有返回值的，也就是说函数至少要有一句return ，而在只有一句函数体的时候可以省略begin 和 end 。也就是说只有一句return的时候可以使用省略。
2)查看函数
show function status;
image.png
查看函数的创建语句：
show create function 名称;
image.png
3)修改函数&删除函数
函数不能修改，只能先删除后新建。
drop function 函数名;
image.png
4)函数的参数
形参：形式参数 函数定义时候的传入参数变量叫做形式参数。形参可以有指定的默认值，但是必须要制定变量。
实参：实际参数 函数调用时候传入的参数叫做实际参数。实参可以使数值也可以是变量。
实例：
计算输入的数字n到开始数字1之间所有数字的和。1-n的和
delimiter $$
create function msum(int_total int) returns int
begin
set @i := 1 ;
set @sum :=0;
while @i<int_total do
set @sum := @sum+@i;
set @i :=@i+1;
end while;
return @sum;
end
$$
delimiter ;
image.png
看图说话：
在MySQL中每一句话在最后使用一个分号作为结束。但是一个函数的定义里面是少不了完整SQL执行过程的，也就是说分号会打断函数的正常定义过程。
于是数据产生了这样的语法。在函数开启之前将结束符号换掉，执行完成之后再换回来。
image.png
image.png
image.png
看图说话：
一个@表示的变量属于全局变量，而全局变量能够使用在任何地方，不管是函数内部还是函数外部，都能够使用全局变量。
5)作用域
MySQL中的作用域与js中的作用域完全一样：
全局变量可以在任何地方使用，局部变量只能在函数内部使用。
全局变量：使用set关键字定义，使用@作为标志。这种方式的都是全局的变量。
局部变量：使用declare关键字声明。不使用@标记。必须是在函数体开始之前声明。
例子：求一下1-n之间所有不是5的倍数的数字的和。
delimiter $$
create function fun2(total int) returns int
begin
declare i int default 1;
declare sum int default 0;
while1:while i<=total do
if i%5=0 then
set i:=i+1;
iterate while1;
end if;
set sum :=sum+i;
set i :=i+1;
end while;
return sum;
end
$$
delimiter ;
image.png
五、存储过程
procedure：是一种数据处理的方式。可以理解为一种函数。简称过程。
存储过程只能返回一个值，并且必须是通过return。
- 存储过程只有在创建语句的时候进行编译，以后的执行在不需要进行编译，而一般的SQL语句是每次执行都需要编译。
- 存储过程可以接受参数、输出参数、返回单个或多个结果集以及返回值，可以向程序返回错误原因。但函数只能返回一个特定类型的值。
1．创建过程
基本语法：
create procedure name([参数列表]) -- in/out/inout 参数 类型
begin
过程语句；
end;
image.png
2．查看过程
存储过程的查看类似于函数的查看。
查看所有的存储过程：
show procedure status;
image.png
查看创建语句：
show create procedure 存储过程名称；
image.png
3．调用过程
call 存储过程的名称；
image.png
4．修改&删除
存储过程与函数类似，没法进行直接修改，只能删除之后再添加。
删除的基本语法：
drop procedure 名称；
image.png
5．参数要求
函数的参数需要制定参数的类型，存储过程的参数比函数更加严格。
过程还有自己的类型限定：三种类型：
in:传进去，全局变量的值传递给存储过程内部使用，在内部修改该变量的值，在外部查看不会发生变化。这种方式类似于函数传参。in是默认的关键字，可以省略。
out:传出去，外部只需要对内部提供一个变量名，内部会自动实现清空变量=null，然后内部对变量进行操作最后反映到外部的变量身上。也就是外部变量也会发生变化，类似于引用传参。
inout:传进去传出来。在外部的全局变量可以传递到存储过程的内部，在内部对变量的操作可以反映到外部。相当于in与out的结合。
delimiter $$
create procedure pro(in int_1 int ,out int_2 int , inout int_3 int)
begin
select int_1,int_2,int_3;
end
$$
delimiter ;
image.png
image.png
image.png
实例：
delimiter $$
create procedure pro1(in int_1 int,out int_2 int , inout int_3 int)
begin
select int_1,int_2,int_3;
select @int_1,@int_2,@int_3;
set int_1 = 10;
set int_2 = 100;
set int_3 =1000;
select int_1,int_2,int_3;
select @int_1,@int_2,@int_3;
end
$$
delimiter ;
select @int_1,@int_2,@int_3;
image.png
image.png
全局变量的值域局部变量的值相互独立，互不影响，在存储过程中单独占据一块空间。
image.png
存储过程运行结束之后，才会将out与inout类型的值返回到外面来。
6．存储过程与MySQL函数的区别
1.调用的方法不一样
函数使用select调用，存储过程使用call调用。
2.返回值不同
函数的返回值一定是使用return进行返回的，存储过程可以使用out 或者inout来实现返回。
3.参数不同
函数的参数可以使用具体的值，存储过程的值在out或者inout的时候必须是使用变量传递。
4.使用场景不同
函数具有return的返回值，所以函数执行结果可以作为查询的依据，或者作为查询的数据来源。存储过程可以使用out或者inout返回。存储过程只编译一次，效率比较高。但是会占用大量数据库资源。
六、触发器 trigger
需求：有两张表，一张商品表，一张订单表，每生成一个订单，商品数量就会修改。
触发器非常类似于JS的事件触发。只有在触发某个事件之后这才会执行。所以触发器是一种特殊的存储过程，只是在调用的时候不需要使用call，而是自动执行的。
触发器的要素： 触发类型 ：写操作（增删改） ，触发时机（before | after ），触发的前提： 表的每一行 触发的动作：一连串的操作
一张表同一类型的同一触发事件只能有一个，这样的话，一张表中最多只能有 2 * 3 = 6个触发器。
image.png
1．创建触发器
基本语法：
delimiter 自定义分号
create trigger name 触发时机 触发类型 on 表名称 for each row
begin --代表左大括号
end --代表右大括号
自定义符号 --语句结束
delimiter ； --将分号修改回来
实例：每生成一个订单商品表减去对应的商品数量。（先考虑一个订单减去一件商品）
//创建商品表
create table goods (
id int not null primary key auto_increment,
name varchar(50) not null,
price decimal(10,2),
num int
);
//创建订单表
create table my_order (
id int not null primary key auto_increment,
userid int not null,
goodsid int not null,
num int
)
//插入数据
insert into goods values(1,’maotai’,1080,10);
//创建触发器
delimiter $$
create trigger trg after insert on my_order for each row
begin
update goods set num=num-1 where id=1;
end
$$
delimiter ;
image.png
2．查看触发器
1)查看所有触发器
show triggers;
image.png
2)查看指定触发器创建语句
show create trigger 名称；
image.png
所有的触发器都保存在一张表中：information_schema.triggers
image.png
image.png
3．修改&删除
触发器也不能直接修改，只能先删除再新增。
删除：
drop trigger 名称；
image.png
4．触发器记录
不管触发器是否触发，只要当某种操作准备执行的时候，系统就会将当前操作的记录的当前状态和最终执行后的状态保存下，以供触发器使用。其中要操作当前状态保存在old中，操作之后的结果保存在new中。
image.png
old代表旧的记录，new代表新的记录。所以插入的时候没有old记录，删除的时候没new记录。
old和new都本身代表的是一条记录，所对应的除了数据还有字段。所以可以使用字段去除记录中的数据，使用old.字段或者new.字段就行。
完善实例：
delimiter $$
create trigger tg after insert on my_order for each row
begin
update goods set num=num-new.num where id=new.goodsid;
end
$$
delimiter ;
image.png
七、MySQL编程总结
通过函数，触发器以及存储过程实现。
在数据库服务器内部执行，距离数据最近，执行效率高。
能够实现代码重用。
能够提高安全，传输的数据越少越安全，同时事务用于大量的金融业务。
数据库的资源消耗比较高。
网友评论