初涉MySQL操作(四)

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 各种存储引擎的特点

特点MyISAMInnoDBMemoryArchine
存储限制256TB64TB
事务安全-支持--
支持索引支持支持支持-
锁颗粒表锁行锁表锁行锁
数据压缩支持--支持
支持外键-支持--

备注:

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

2.5设置存储引擎

  • 通过修改MySQL的配置文件实现

    • default-storage-engine= engine
    • 这里写图片描述
  • 通过创建数据表命令实现

    CREATE TABLE table_name(
    ……
    ……
    )ENGINE = engine;
  • 通过修改数据表命令修改存储引擎

    ALTER TABLE table_name ENGINE [=] engine_name;
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值