六天带你玩转Mysql笔记--第六天

1.事务安全

1.1事务操作

1.1.1手动事务操作流程

1.1.2事务操作原理

1.1.3回滚点

1.1.4自动事务处理

1.1.5事务特性

1.1.6锁机制

2.变量

2.1系统变量

2.1.1查看系统变量

2.2修改系统变量

2.2自定义变量

2.2.1自定义变量

3触发器

3.1创建触发器

3.2查看触发器

3.3使用触发器

3.4修改触发器&删除触发器

3.5触发器记录

4.代码执行结构

4.1分支结构

4.2循环结构

5.函数

5.1系统函数

5.2自定义函数

5.2.1创建函数

5.2.2查看函数

5.2.3修改函数&删除函数

5.2.4函数参数

5.2.5作用域

6存储过程

6.1创建过程

6.2查看过程

6.3调用过程

6.4修改过程&删除过程

6.5过程参数

 

1. 事务安全

（1）事务：一系列要发生的连续的操作。

（2）事务安全：一种保护连续操作同时满足（实现）的一种机制。

（3）事务安全的意义：保证数据操作的完整性。

1.1事务操作

事务操作分为两种：自动事务（默认的），手动事务

1.1.1手动事务操作流程

（1）开启时事务：告诉系统以下所有的操作（写）不要直接写入到数据表，先存放到事务日志smart transaction。

--开启事务

start transaction;

（2）进行事务操作：一系列操作

1）李四账户减少1000

2）张三账户增多1000

--事务操作1：李四的账户减少1000

update my_account set money = money - 1000 where id = 2;

--事务操作2：张三账户增加1000

update my_account set money = money + 1000 where id = 1;

 

（3）关闭事务：选择性的将日志文件中操作的结果保存到数据表（同步）或者直接清除事务日志（原来操作全部清空）

1）提交事务：同步数据表（操作成功）：commit

 

2）回滚事务：直接清空日志表（操作失败）：rollback

 

1.1.2事务操作原理

事务开启后，所有的操作都会临时保存到事务日志，事务日志只有得到commit命令才会同步到数据表，其他任何情况都会清空（rollback，断电和断开连接）。

 

 

1.1.3回滚点

（1）定义：在某个成功的操作完成之后，后续的操作有可能成功有可能失败，但是不管成功还是失败，前面的操作都已经成功。可以再在当前成功的位置，设置一个点。可以供后续失败操作返回到该位置，而不是返回所有操作，这个点称之为回滚点。

（2）设置回滚点语法：savepoint回滚点名字;

（3）回到回滚点语法：rollback to 回滚点名字;

 

1.1.4自动事务处理

（1）在mysql中，默认的都是自动事务处理，用户操作完成后会立即同步到数据库。

（2）自动事务：系统通过autocommit变量控制

Show variables like‘autocommit’;

 

（3）关闭事务自动提交

Set autocommit = off/0;

（4）自动关闭后，需要手动来选择处理：commit/rollback

注意：通常开启自动事务。

 

1.1.5事务特性

事务有四大特性：A C I D

1)Atomic：原子性，事务的整个操作就是一个整体，不可分割，要么全部成功，要么全部失败。

2)Consistency：一致性，事务操作的前后，数据表中的数据没有变化。

3)Isolation：隔离性，事务操作时互相隔离不受影响。

4)Durability：持久性，数据一旦提交，永久改变数据表数据。

 

1.1.6锁机制

（1）Innodb默认是行锁，但是如果在事务操作的过程中，没有使用到索引，那么系统会自动全表检索数据，自动升级为表锁。

（2）行锁：只有当前行被锁住，别的用户不能操作

（3）表锁：整张表被锁住

 

 

2. 变量

变量分为：系统变量和自定义变量

 

2.1系统变量

系统定义的变量，大部分的时候用户根本不需要使用系统变量，系统变量时用来控制服务器的表现，如autocommit，auto_increment等

2.1.1查看系统变量

Show  variables; --查看所有系统变量

查看具体变量值：任何一个有数据返回的内容都是有select查看

Select @@系统变量名;

 

2.2修改系统变量

（1）修改系统变量分为两种方式：会话级别和全局级别

（2）会话级别：临时修改，当前客户端当次连接有效

Set变量名 = 值;

Set @@变量名= 值;

（3）全局级别：一次修改，永久生效（对所有客户端都生效）

Set global变量名;

注：如果其他客户端当前已经连接上服务器，那么当次修改无效，要重新登录才会生效。

2.2自定义变量

2.2.1自定义变量

（1）系统为了区分系统变量，规定用户自定义变量必须使用一个@符号

Set @变量名=/:= 值;

（2）自定义变量查看

Select @变量名;

（3）在mysql中，”=”会默认的当做比较符号处理，mysql为了区分比较和赋值的概念增加了一个赋值符号：”:=”

（4）Mysql允许数据表中获取数据，然后赋值给变量

方案1：边赋值，边查看结果

代码：

select @name= name,name from my_student;

 

代码：

select @name := name,name from my_student;

select @name;

 

方案2：只有赋值不看结果（1）要求很严格（2）数据记录最多只允许一条（3）Mysql不支持数组

Select字段列表 from 表名 into 变量列表;

代码：

select name,age from my_student where id =2 into @name,@age;

效果：

 

（5）所有自定义的变量都是会话级别：当前客户端当次连接有效

（6）所有自定义变量不区分数据库（用户级别）

3触发器

（1）触发器(trigger)：事先为了某张表绑定好一段代码，当表中的某些内容发生改变的时候（增删改），系统会自动触发代码执行。

（2）触发器：事情类型，触发时间，触发对象

1）事件类型：增删改，三种类型（insert，delete和update）

2）触发时间：前后（before和after）

3）触发对象：表中的每一条记录（行）

（3）一张表中只能拥有一种触发时间的一种类型的触发器（不重复），即一张表最多能有6个触发器。

 

3.1创建触发器

（1）在mysql高级结构中，没有大括号，都是对应的字符符号代替

（2）触发器基本语法

--临时修改语句结束符

Delimiter自定义符号 --后续代码中只有碰到自定义符号才算结束

Create trigger触发器名字 触发时间 事件类型 on表 for each row

Begin -- 代表左大括号：开始

--里面就是触发器的内容，每行内容都必须使用语句结束符：分号

End   --代表右大括号：结束

自定义符号  --语句结束符

--将临时修改修正过来

Delimiter ;

代码：

--触发器：订单生成一个，商品库存减少一个

delimiter $$

create trigger after_order after insert on my_order for each row

begin

 --触发器开始内容

 update my_goods set inv = inv-1 where id = 2;

 end

 $$

 delimiter ;

 

 

3.2查看触发器

（1）查看所有触发器：show triggers [like‘pattern’];

 

（2）可以查看触发器创建语句

Show create trigger触发器名字;

 

（3）所有的触发器都会保存一张表：information_schema triggers;

 

3.3使用触发器

触发器：不需要手动调用，而是当某种情况发生时回自动触发。

 

3.4修改触发器&删除触发器

Drop trigger触发器名;

 

3.5触发器记录

（1）触发器记录：不管触发器是否触发了，只要当某种操作准备执行，系统就会将“当前要操作的记录的当前状态”和“即将执行之后的新状态”分别保留下来，供触发器使用。其中，要操作的当前状态保存到old中，操作之后的可能形态保存给new。

（2）Old代表的是旧记录，new代表的是新记录

删除的时候是没有new的;

插入的时候是没有old的;

（3）Old和new都是代表记录本身：任何一条记录除了有数据，还有字段名字。

（4）使用方式：old字段名/ new字段名;

--触发器：订单生成，商品库存减少

delimiter $$

create trigger after_order after insert on my_order for each row

begin

 --触发器开始内容

 update my_goods set inv = inv-new.g_number where id = new.g_id;

 end

 $$

 delimiter ;

 

4. 代码执行结构

代码执行三种结构：顺序结构，分支结构和循环结构

 

4.1分支结构

（1）分子结构：事先准备多个代码块，按照条件选择性执行某段代码块

（2）If分支基本语法：

If条件判断 then

   --满足条件执行的代码

Else

--不满足条件执行的代码

End if

 

4.2循环结构

（1）循环结构：某段代码在指定条件执行重复循环

（2）基本语法（while循环，没有for循环）

While条件判断 do

 --满足条件要执行的循环代码

 --变更循环条件

End while;

（3）循环控制：在循环内部进行循环判断和控制

Mysql中没有对应continue和break，但是有替代品

Iterate：迭代，类型continue：后面的代码不执行，循环重新来

Leave：离开，类似break

（4）使用方式：iterate/leave循环名字

--定义循环名字

循环名字：while条件 do

--循环体

--循环控制

Leave/iterate循环名字;

End while;

 

5. 函数

将一段代码块封装到一个结构中，在需要执行代码块的时候，调用结构执行即可（代码复用）

 

5.1系统函数

（1）系统定义好的函数，直接调用即可。

（2）任何函数都有返回值，因此函数的调用时通过select调用。

（3）Mysql中，字符串的基本操作单位（最常见的是字符）

1）Substring字符串截取

set @cn = '世界你好';

set @en = 'hello world';

--字符串截取

select substring(@cn,1,1);

select substring(@en,1,1);

 

2）Char_length：字符长度

Length：字节长度

select char_length(@cn),char_length(@en),length(@cn),length(@en);

 

3）Instr：判断字符串是否在某个具体的字符串中存在，存在返回位置

 

4）Lpad：左填充，将字符串按照某个指定的填充方式，填充到指定长度（字符）

select lpad(@cn,20,'欢迎'),lpad(@en,20,'hello');

 

5）Insert：替换，找到指定长度的字符串，替换成目标字符串

6）Strcmp：compare，字符串比较

 

5.2自定义函数

函数要素：函数名，参数列表（形参和实参），返回值，函数体（作用域）

 

5.2.1创建函数

（1）创建语法：

Create function函数名([参数列表]) returns数据类型 --规定返回数据类型

Begin

--函数体

--返回值：return类型（指定数据类型）

End

（2）自定义函数与系统函数调用方式是一样：select函数名（实参列表）

 

5.2.2查看函数

查看所有函数：show function status [like‘patren’];

查看函数的创建语句：show create function函数名;

 

5.2.3修改函数&删除函数

（1）函数只能先删除后新增，不能修改。

Drop function函数名;

 

5.2.4函数参数

（1）参数分为两种：定义时的参数叫做形参，调用时的参数叫实参（实参可以是数值也可以变量）。形参：要求必须指定数据类型。

Function函数名（形参名字 字段类型）returns数据类型

（2）在函数内部使用@定义的变量在函数外部也可以访问。

 

 

5.2.5作用域

（1）Mysql中的作用域与js中的作用域完全一样

（2）全局变量可以在任何地方使用；局部变量只能在函数内部使用。

（3）全局变量：使用set关键字定义，使用@符号标志

（4）局部变量：使用declare关键字声明，没有@符号；所有的局部变量的声明，必须在函数体的开始之前定义。

delimiter @@

create function display3(int_1 int) returns int

 

begin

     --定义局部变量

declare res int default 0;

declare num int default 1;

--循环判断

while num <= int_1 do

if num%5!=0 then

--相加

set res = res + num;

end if;

--改变循环变量

set num = num + 1;

end while;

return res;

end

@@

delimiter ;

 

 

6存储过程

存储过程简称过程（procedure），是一种用来处理数据的方式。

存储过程是一种没有返回值的函数。

6.1创建过程

Create procedure过程名字（[参数列表]）

Begin

--过程体

End

6.2查看过程

查看所有过程：show procedure status [like‘patern’];

6.3调用过程

Call过程名;

6.4修改过程&删除过程

过程只能先删除，后新增

Drop procedure过程名;

6.5过程参数

（1）函数的参数需要数据类型指定，过程比函数更严格。过程有自己的类型限定：三种类型

1）In：数据只是从外部传入给内部使用（值传递，变量或数值）

2）Out：只允许过程内部使用（不用外部数据），给外部使用的只能是变量（引用传递：外部的数据会被先清空再进入到内部）

3）Inout：外部可以在内部使用，内部修改液可以给外部使用（典型的引用传递：只能传变量）

（2）基本使用

Create procedure过程名（in 形参名字 数据类型，out形参名字 数据类型，inout形参名字 数据类型）

 

（3）调用：out和inout传入的必须是变量

（4）存储过程对于变量的操作（返回）是滞后的：是在存储过程调用结束的时候，才会重新将内部修改的值(传入的变量)赋值给外部传入的全局变量、