初涉MySQL操作（四）_mysql sql access contains sql reads sql data-CSDN博客

本文链接：https://blog.csdn.net/DXZCZH/article/details/52577449

1 MySQL存储过程

1.1 存储过程简介

当我们成功输入命令后，MySQL的引擎会首先对命令进行分析，所书写的语句是否正确，如果正确的话就会编译成MySQL可以识别的命令，最后开始执行，并将执行的结果返回给客户端
存储过程是SQL语句和控制语句的预编译集合，以一个名称存储并作为一个单元来处理。存储过程存储在数据库内，可以由应用程序调用执行，而且允许用户声明变量以及流程控制，存储过程可以接收参数，可以接收输入类型的参数，也可以接收输出类型的参数，而且可以返回多个值。
存储过程比我们单一的命令执行效率高，原因在于：之前写两个语句，MySQL会对这两个语句逐一进行语法分析，逐一的编译，逐一的执行，而采用了存储过程后，只有第一次才进行语法分析和编译，以后客户端再去调用，直接来调用编译的结果就可以了。
存储过程的优点：
- 增强SQL语句的功能和灵活性，存储过程中可以写控制语句，就有很强的灵活性，可以完成复杂的判断以及较复杂的运算
- 实现较快的执行速度
- 减少了网络流量，通过客户端每个单独发送SQL语句，通过HTTP协议所提交的数据量相对较大

1.2 存储过程语法结构解析

创建存储过程，与创建自定义函数的结构类似
```
CREATE
[DEFINER = user|CURRENT_USER}]
PROCEDURE sp_name ([proc_parameter[,…]])
[characteristic …] routine_body
```
- DEFINER：创建者，不写默认为当前用户
- PROCEDURE：后面是存储过程的名字，括号中可以有0个或多个参数
- proc_parameter格式：
```
[IN | OUT | INOUT] param_name type
```
  - IN：表示该参数的值必须在调用存储过程时指定，这个值是不能被返回的
  - OUT：表示该参数的值可以被存储过程改变，并且可以返回
  - INOUT：表示该参数调用时指定，并且可以被改变或返回
- characteristic
```
LANGUAGE SQL
| [NOT] DETERMINISTIC
|{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}
| SQL SECURITY {DEFINER|INVOKER}
| COMMENT 'string'
```
- LANGUAGE SQL：说明routin_body部分是由SQL语句组成的
- [NOT] DETERMINISTIC：指明存储过程执行的结果是否正确，DETERMINISTIC表示结果是确定的，每次执行，相同的输入会得到相同的输出，默认情况下，系统默认指定NOT
- CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA：指明子程序使用SQL语句的限制，默认情况下，系统指定为CONTAINS SQL
  - CONTAINS SQL：表明子程序包含SQL语句，但是不包含读写数据的语句
  - NO SQL：表明子程序不包含SQL语句
  - READS SQL DATA：说明子程序包含读数据的语句
  - MODIFIES SQL DATA：表明子程序包含写数据的语句
- SQL SECURITY {DEFINER|INVOKER}：指明谁有权限来执行
  - DEFINER表示只有定义者才能执行
  - INVOKER表示拥有权限的调用者可以执行，默认情况下只有定义者才能使用
- COMMENT ‘stirng’：注释信息，可以用来描述存储过程或函数
过程体
- 过程体由合法的SQL语句构成
- 过程体可以是任意（对于记录的增删改查和多表连接）的SQL语句，但不能通过存储过程创建数据库（或表）
- 过程体如果为复合结构则使用BEGIN…END语句
- 复合结构中可以包含声明，循环和控制结构

1.3 创建不带参数的存储过程

创建一个存储过程，用来查看MySQL的版本：

调用存储过程

CALL sp_name([parameter[,...]])

CALL sp_name[()] //没有参数时

这里写图片描述

1.4 创建带有IN类型参数的存储过程

删除指定id的字段，id作为参数传递，在调用存储过程时指定
这里写图片描述
（创建之前先修改定界符，创建之后再修改过来）

但是执行删除id=3的存储过程后，发现数据表为空了，因为传入的参数与数据表中的参数名相同，MySQL认为两个id都是字段，所以将所有记录都删除了，因此，在给参数的时候，参数的名字不能和数据表中字段的名字相同。
修改存储过程无法修改过程体，所以必须删除存储过程然后重建：

//修改存储过程，只能修改一些简单的特性，并不能修改过程体
ALTER PROCEDURE sp_name [characteristic...]

characteristic:
     {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
     | SQL SECURITY { DEFINER | INVOKER }
     | COMMENT 'string'

 //删除存储过程
 DROP PROCEDURE [IF EXISTS] sp_name

这里写图片描述
重新创建存储过程，并删除id号为6的记录：

1.5 创建带有IN和OUT类型参数的存储过程

删除指定id的记录，并返回剩余的记录数：
这里写图片描述
（INTO：将结果放入到userNums中）
调用存储过程，使用“@”声明变量，称为用户变量，只在客户端有效

1.6 创建带有多个OUT类型参数的存储过程

这里写图片描述
创建一个存储过程，根据年龄来删除记录（年龄为IN类型的参数），返回要删除的用户和剩余的用户数

MySQL的内置函数ROW_COUNT()用来返回它前一条命令使数据表跟新修改的记录
创建存储过程
调用存储过程
查看结果

1.7 存储过程中的变量

1.7.1 DECLARE局部变量

DECLARE var_name[,...] type [DEFAULT value]

这个语句被用来声明局部变量，作用范围在它被声明的BEGIN…END快内
可以给变量提供一个默认值，如果没有DEFAULT子句，初始值为NULL
变量的值可以被指定为一个表达式，不需要为一个常数

1.7.2 变量SET语句

SET var_name = expr [,var_name = expr]...

1.8 存储过程与自定义函数的区别

存储过程实现的功能要复杂一些，而函数的针对性更强，实际应用中，函数很少对表来作操作，而存储过程经常对表作操作，
存储过程可以返回多个值，函数只能有一个返回值，且必须有
存储过程一般独立的来执行；而函数可以作为其他SQL语句的一部分出现，和内置函数一样
注意事项：
- 存储过程或者自定义函数时需要通过DELIMITER语句修改定界符
- 如果函数体或过程体有多个语句，需要包含在BEGIN…END语句块中

2 MySQL存储引擎

MySQL可以将数据以不同的技术存储在文件（内存）中，这种技术就称为存储引擎
每种存储引擎使用不同的存储机制、索引技巧、锁定水平，最终提供广泛且不同的功能
MySQL支持的存储引擎
- MyISAM
- InnoDB
- Memory
- CSV
- Archive

2.1 并发处理

当多个连接对记录进行修改时保证记录的一致性和完整性

在处理并发读和并发写时，系统会使用一套锁系统来处理：
- 共享锁（读锁）：在同一时间段内，多个用户可以读取同一个资源，读取过程中数据不会发生任何变化
- 排它锁（写锁）：在任何时候只能有一个用户写入资源，当进行写锁时会堵塞其它的读锁或写锁操作
锁的力度（锁颗粒），即锁定的时候的单位，只需要对修改的数据精确加锁即可，而无需对所有的数据加锁
- 表锁，是一种开销最小的锁策略，当用户对数据表进行操作时，就获得了这张表的写锁权限，写锁会禁止其他用户进行读写的操作，每张表最多有一个锁，所以开销最小
- 行锁，是一种开销最大的锁策略，也是支持最大并发操作的策略。数据表中有多少条记录，都可能对每条记录都有锁，所以开销最大

2.2 事务处理

事务用于保证数据库的完整性，四个特性：原子性（Acomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）

2.3 外键和索引

外键：保证数据一致性的策略
索引：对数据表中一列或多列的值进行排序的一种结构，使用索引可以快速访问数据表中的信息，包括普通索引，唯一索引和全文索引等

2.4 各种存储引擎的特点

特点	MyISAM	InnoDB	Memory	Archine
存储限制	256TB	64TB	有	无
事务安全	-	支持	-	-
支持索引	支持	支持	支持	-
锁颗粒	表锁	行锁	表锁	行锁
数据压缩	支持	-	-	支持
支持外键	-	支持	-	-

备注：

Memory将数据存储在内存中，存储的数据量由内存大小决定
MyISAM：适用于事务的处理不多的情况
InnoDB：适用于事务处理比较多，需要有外键支持的情况

2.5设置存储引擎

通过修改MySQL的配置文件实现
- default-storage-engine= engine

通过创建数据表命令实现

CREATE TABLE table_name(
……
……
）ENGINE = engine；

通过修改数据表命令修改存储引擎

ALTER TABLE table_name ENGINE [=] engine_name;