MySQL day7 (2019.5.14)

一、回顾

视图
外键
事务
数据管理
用户管理

二、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编程总结

通过函数，触发器以及存储过程实现。
在数据库服务器内部执行，距离数据最近，执行效率高。
能够实现代码重用。
能够提高安全，传输的数据越少越安全，同时事务用于大量的金融业务。

数据库的资源消耗比较高。