MySQLday04（剩余窗口函数，其他常用函数，存储引擎，如何选择数据类型，字符集，索引，存储过程，触发器，LOCK TABLES 和 UNLOCK TABLES，事务控制，分布式事务的使用，JDBC）

剩余窗口函数

• NTILE(N)：
• ntile()函数的功能是对一个数据分区中的有序结果集进行划分，将其分为N个组，并为每个小组分配一个唯一的组编号。
• 此函数在数据分析中应用较多，比如由于数据最大，需要将数据分配到N个并行的进程分别计算，此时就可以用NTILE(N)对数据进行分组，由于记录数不一定被N整除，所以每组记录数不一定完全一致，然后将不同组号的数据再分配。 

-- ntile(n) 在组内进行分组
select ename,deptno,ntile(4) over (partition by deptno) '组号' from emp;

　　

• NTH_VALUE(expr,N)：
• NTH_VALUE(expr,N)函数可以返回窗口中第N个expr的值，expr既可以是表达式，也可以是列名。 

-- nth_value(n) 在组内找第n个数
select ename,deptno,nth_value(ename,2) over (PARTITION by deptno order by sal desc) from emp;

　　

• LAG(expr,N)/LEAD(expr,N)：
• LAG(expr,N)和LEAD(expr,N)这两个函数的功能是获取当前数据行按照某种排序规则上的N行（LAG）/下N行（LEAD）数据的某个字段。 

-- lead(expr,n) 显示组内 隐藏从上到下的n个数据 并显示剩余数据
select ename,deptno,lead(ename,2) over (partition by deptno ORDER BY sal desc) from emp;

-- lag(expr,n) 显示组内 隐藏从下到上的n个数据 并显示剩余数据
select ename,deptno,lag(ename,2) over (partition by deptno order by sal desc) from emp;

• FIRST_VALUE(expr)/LAST_VALUE(expr)：
  • FIRST_VALUE(expr)和LAST_VALUE(expr)这两个函数的功能分别是获得滑动窗口范围内参数字段中第一个和最后一个的值。 

　　

-- first_value(expr) 显示组内第一个的expr值
select ename,deptno,sal,first_value(ename) over (partition by deptno ORDER BY sal) from emp;

-- last_value(expr) 显示组内最后一个的expr值
select ename,deptno,sal,last_value(ename) over (partition by deptno ORDER BY sal) from emp;

　　　

• 聚合函数作为窗口函数：
  • 使用各种聚合函数（sum、avg、max、min、count）作为窗口函数来使用。 

其他常用函数

　　

存储引擎

• MySQL5.7支持的存储引擎包括：
  • InnoDB
  • MyISAM
  • MEMORY
  • CSV
  • BLACKHOLE
  • ARCHIVE
  • MERGE
  • FEDERATED
  • EXAMPLE
  • NDE
  • 其中InnoDB和NDB提供事务安全表，其他存储引擎都是非事务安全表。

　　

• MySQL5.5之前默认存储引擎是MyISAM，5.5版本之后改为InnoDB。
• 修改默认存储引擎，在参数文件中设置default_storage_engine。
• 查看当前的默认引擎
  • show variables like 'default_storage_engine'
• 查询当前数据库支持的存储引擎
  • show engines G
  • 其中Support不同值的含义分别为：
    • DEFAULT：支持并启用，并且为默认引擎
    • YES：支持并启用
    • NO：不支持
    • DISABLED：支持，但是数据库启动的时候被禁用

 

• 在创建新表的时候，可以通过增加engine关键字设置新建表的存储引擎。
• 可以使用alter table语句，将一个已经存在的表修改成其他的存储引擎。
  • 注意：修改表的存储引擎需要锁表并复制数据，对于线上环境的表进行这个操作非常危险，除非你非常了解可能造成的影响，否则在线上环境请使用其他方式（借助OSC工具）。

　　常用存储引擎对比

 　　

• MyISAM：
  • MyISAM既不支持事务，也不支持外键，对事务完整性没有要求或者以SELECT、INSERT为主的应用可以使用这个引擎来创建表。

 

• InnoDB：
  • InnoDB提供了具有提交、回滚和崩溃恢复能力的事务安全保障，同时提供了更小粒度和更强的并发能力，拥有自己独立的缓存和日志。
  • 对比MyISAM存储引擎，InnoDB会占用更多的磁盘空间以保留数据和索引。
  • 不同于使用其他存储引擎的表的特点：
    • 自动增长列
    • 外键约束
    • 主键和索引
      • 满足唯一和非空约束；
      • 优先考虑使用最经常被当作查询条件的字段或者自增字段
      • 字段值基本不会被修改
      • 使用尽可能短的字段
      • 存储方式
        • 共享表空间存储
        • 多有空间存储
• MEMORY：
  • MEMORY存储引擎使用存在于内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件，格式是.frm。MEMORY类型的表访问非常地快，因为它的数据是放在内存中的，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失掉。
• MERGE：
  • MERGE存储引擎也被称为MRG_MyISAM，是一组MyISAM表的组合。这些MyISAM表必须结构完全相同，MERGE表本身并没有数据，对MERGE类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行的。
  • 对于MERGE类型表的插入操作，是通过INSERT_METHOD子句定义插入的表
  • 对于MERGE类型表进行DROP操作，只是删除MERGE的定义，对内部的表没有任何影响。
• TokuDB：
  • TokuDB是一个高性能、支持事务处理的存储引擎，具有高扩展性、高压缩率、高效的写入性能，支持大多数在线DDL操作。
    • 使用Fractal树索引保证高效的插入性能；
    • 优秀的压缩特性，比InnoDB高近10倍；
    • Hot Schema Changes特性支持在线创建索引和添加、删除属性列等DDL操作；
    • 使用Bulk Loader达到快速加载大量数据；
    • 提供了主从延迟消除技术；
    • 支持ACID和MVCC。

　如何选择合适的存储引擎

• MyISAM：
  • MySQL5.5之前版本默认的存储引擎。如果应用是以读操作和插入操作为主，只有极少的更新和删除操作，并且对事务的完整性没有要求、没有并发写操作，那么选择这个存储引擎是适合的。OLTP环境一般建议不要再使用MyISAM。
• InnoDB：
  • MySQL5.5之后版本默认的存储引擎，用于事务处理应用程序，支持外键，对于大多数的应用系统，InnoDB都是合适的选择。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性。数据操作除了插入和查询以外，还包括很多的更新、删除操作，那么应该优先选择InnoDB存储引擎。InnoDB存储引擎除了有效地降低由于删除和更新导致的锁定，还可以确保事务的完整提交（Commit）和回滚（Rollback）
• MEMORY：
  • 将所有数据保存在RAM中，在需要快速定位记录和其他类似数据的环境下，可提供极快的访问速度。MEMORY的缺陷是对表的大小有限制，太大的表无法缓存在内存中，其次是确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY表通常用于更新不太频繁的小表，用以快速得到访问结果。
• MERGE：
• 用于将一系列等同的MyISAM表以逻辑方式组合在一起，并作为一个对象引用它们。MERGE表的优点在于可能突破对单个MyISAM表大小的限制，并且通过将不同的表分布在多个磁盘上，可以有效地改善MERGE表的访问效率。这对于诸如数据仓储等VLDB环境十分适合。 

如何选择数据类型

CHAR和VARCHAR

• 固定长度与可变长度的字符类型，下表以CHAR(4)和VARCHAR(4)类型对字符串值保存的差异：

 

• 注意：最后一行的值只适用MySQL运行在非“严格模式”时，如果MySQL运行在严格模式，超过列长度的值将不会保存，并且会出现错误提示。

 

• 在MySQL中，不同的存储引擎对CHAR和VARCHAR的使用原则有所不同，概括如下：
  • MyISAM存储引擎：建议使用固定数据列代替可变长度的数据列。
  • MEMORY存储引擎：目前都使用固定长度的数据行存储，因此无论使用CHAR或VARCHAR列都没有关系，两者都是作为CHAR类型处理。
  • InnoDB存储引擎：建议使用VARCHAR类型。对于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），因此在本质上，使用固定长度的CHAR列不一定比使用可变长度VARCHAR列性能要好。因而，主要的性能因素是数据行存储总量。由于CHAR平均占用的空间多于VARCHAR，因此使用VARCHAR来最小化需要处理的数据行的存储总量和磁盘I/O是比较好的。

 

TEXT与BLOB

• 保存较大文本时，通常会选择使用TEXT或BLOB。二者之间的差异在于：
  • BLOB能用来保存二进制数据（照片）；
  • TEXT只能保存字符数据（文章或日记）；
• BLOB与TEXT存在的一些常见问题：
  • BLOB和TEXT值会引起一些性能问题，特别是在执行大量的删除操作时；
  • 可以使用合成的索引来提高大文本字段的查询性能；
  • 在不必要的时候避免检索大型的BLOB或TEXT值；
  • 把BLOB或TEXT列分离到单独的表中；

注意：尽可能在OLTP环境避免使用BLOB或TEXT类型，优先使用VARCHAR。VARCHAR类型最长可以支持65533字节的长度，已经可以满足绝大多数的需求。

浮点与定点数

• 浮点数：
  • 一般用于表示含有小数部分的数值；如果插入数据的精度超过该列定义的实际精度，则插入值会被四舍五入到实际定义的精度值，然后插入，四舍五入的过程不会报错。
• 定点数：
  • 以字符串形式存放，可以更精确地保存数据；如果实际插入的数值精度大于实际定义的精度，则MySQL会进行警告（默认的SQLMode下），但是数据按照实际精度四舍五入插入；如果SQLMode是在TRADITIONAL（传统模式）下，则系统会报错，导致数据无法插入。
• 关于浮点数和定点数的应用中，用户要考虑到以下几个原则：
  • 浮点数存在误差问题；
  • 对货币等对精度敏感的数据，应该用定点数表示或存储；
  • 在编程中，如果用到浮点数，要特别注意误差问题，并尽量避免做浮点数比较；
  • 要注意浮点数中一些特殊值的处理。

日期类型选择

• MySQL提供的常用日期类型有DATE、TIME、DATETIME和TIMESTAMP，选择日期类型的原则：
  • 根据实际需要选择能够满足应用的最小存储的日期类型。如果应用只需要记录“年份”，那么用1个字节来存储的YEAR类型完全可以满足，而不需要用4个字节来存储的DATE类型，这样不仅仅能节约存储，更能够提高表的操作效率。
  • 如果要记录年月日时分秒，并且记录的年份比较久远，那么最好使用DATETIME，而不要使用TIMESTAMP，因为TIMESTAMP表示的日期范围比DATETIME要短得多。
  • 如果记录的日期需要让不同时区的用户使用，那么最好使用TIMESTAMP，因为日期类型中只有它能够和实际时区相对应。

字符集

• 字符集就是一套文字符号及其编码、比较规则的集合。
  • 20世纪60年代初：ASCII
  • ISO-8859系列
  • GB2312-80
  • GBK
  • BIG 5等

 

• 国际标准化组织（International Organization for Standardization，ISO）
  • 1984 UCS 标准编号：ISO-10646 采用4字节32位编码，简称为UCS-4
  • 1991 Unicode1.0 16位编码
  • UTF（UCS/Unicode Transformation Format）

汉字及一些常见的字符集

• 汉字字符集编码标准
  • GB 2312-80：全称《信息交换用汉字编码字符集 基本集》，收录了6763个常用汉字和682个非汉字图形符号；
  • GB 13000：全称《信息技术 通用多八位编码字符集（UCS）第一部分：体系结构与基本多文种平面》，收录27484个汉字以及一些偏旁部首等；
  • GBK：全称《汉字内码扩展规范》1.0版
  • GB 18030：全称《信息技术信息交换用汉字编码字符集、基本集的扩充》27484

     

怎样选择合适的字符集

• 对数据库来说，字符集更加重要，因为数据库存储的数据大部分都是各种文字，字符集对数据库的存储、处理性能，以及日后系统的移植、推广都会有影响，综上所述，选择合适字符集，主要考虑以下几个因素：
  • 满足应用支持语言的需求，如果应用要处理各种各样的文字，或者将发布到使用不同语言的国家或地区，就应该选择Unicode字符集；
  • 如果应用中涉及已有数据的导入，就要充分考虑数据库字符集对已有数据的兼容性；
  • 如果数据库只需要支持一般中文，数据量很大，性能要求也很高，那就应该选择双字节定长编码的中文字符集，比如GBK；
  • 如果数据库需要做大量的字符运算，如比较、排序等，那么选择定长字符集可能更好，因为定长字符集的处理速度要比变长字符集处理速度快；
  • 如果所有客户端程序都支持相同的字符集，则应该优先选择该字符集作为数据库字符集。这样可以避免因字符集转换带来的性能开销和数据损失。

MySQL支持的字符集简介

• MySQL服务器可以支持多种字符集，在同一台服务器、同一个数据库甚至同一个表的不同字段都可以指定使用不同的字符集。
  • 查看所有可用的字符集命令查看information_schema.character_set，可以显示所有的字符集和该字符集默认的排序规则
    • show character set

       

 　　

• MySQL的字符集包括字符集和排序规则两个概念，字符集和排序规则是一对多的关系
• 每个字符集至少对应一个排序规则。可以用”show collation like '***';”
  • 排序规则命名约定：它们以其相关的字符集名开始，通常包括一个语言名，并且以_ci（大小写不敏感）、_cs（大小写敏感）或_bin（二元，即比较是基于字符编码的值而以language无关）结束。

• MySQL的字符集和排序规则有4个级别的默认设置：服务器级、数据库级、表级和字段级。
• 服务器字符集和排序规则，可以在MySQL服务启动的时候确定：
  • 可以在my.ini中设置
    • character-set-server=utf-8
  • 在启动项中指定
    • mysqld --character-set-server=utf-8
  • 在编译时指定
    • shell > cmake . -DDEFAULT_CHARSET=utf-8

注意：在最新的 MySQL8.0中，默认字符集已经变为utf8mb4。

• 数据库的字符集和排序规则既可以在创建数据库的时候指定，也可以在创建完数据库后通过”alter database“命令进行修改。需要注意的是，如果数据库里已经存在数据，因为修改字符集并不能将已有的数据按照新的字符集进行存放，所以不能通过修改数据库的字符集直接修改数据的内容。
• 设置数据库字符集的规则如下：
  • 如果指定了字符集和排序规则，则使用指定的字符集和排序规则；
  • 如果指定了字符集没有指定排序规则，则使用指定字符集默认排序规则；
  • 如果指定了排序规则但未指定字符集，则字符集使用与该排序规则关联的字符集；
  • 如果没有指定字符集和排序规则，则使用服务器字符集和排序规则作为数据库的字符集和排序规则。
• 要显示当前数据库的字符集和排序规则，可以使用以下的指令：
  • show variables like 'character_set_database'
  • show variables like 'collation_database'

 

 

• 表的字符集和排序规则在创建表的时候指定，可以通过alter table命令进行修改，同样，如果表中已有记录，修改字符集对原有的记录并没有影响，不会按照新的字符集进行存放。表的字段仍然使用原来的字符集。
• 设置表字符集的规则如下：
  • 如果指定了字符集和排序规则，则使用指定的字符集和排序规则；
  • 如果指定了字符集没有指定排序规则，则使用指定字符集默认排序规则；
  • 如果指定了排序规则但未指定字符集，则字符集使用与该排序规则关联的字符集；
  • 如果没有指定字符集和排序规则，则使用数据库字符集和排序规则作为表的字符集和排序规则。
• 要显示当前表的字符集和排序规则，可以使用以下的指令：
  • show create table table_name G

 

• MySQL可以定义列级别的字符集和排序规则，主要是针对相同的表不同字段需要使用不同的字符集的情况。
• 列字符集和排序规则的定义可以创建表时指定，或者在修改表时调整。如果在创建表的时候没有特别指定字符集和排序规则，则默认使用表的字符集和排序规则。

 

• 对于客户端和服务器的交互操作，MySQL提供了3个不同的参数
  • character_set_client（客户端）
  • character_set_connection（连接）
  • character_set_results（返回结果的字符集）

字符集的修改步骤

• 已有记录的字符集调整，需要先将数据导出，经过适当的调整重新导入后才可完成。
  • 导出表结构
  • 手工修改test1.sql中表结构定义中的字符集为新的字符集
  • 确保记录不再更新，导出所有记录
  • 打开data.sql，将set names utf8修改成SET NAMES gbk
  • 使用新的字符集创建新的数据库
  • 创建表，执行createtab.sql
  • 导入数据，执行data.sql

注意：选择目标字符集的时候，要注意最好是源字符集的超集，或者确定比源字符集的字库更大，否则如果目标字符集的字库小于源字符集的字库，那么目标字符集中不支持的字符导入后会变成乱码，丢失一部分数据。

索引

• 索引在创建表的时候可以同时创建，也可以随时增加新的索引。
• 第一种方式：创建表的时候创建索引

　　

　　 

　　

 　　

　　代码演示：

-- 创建索引  create index 索引名 on 表明(索引设置的字段名)
create index wzk on emp(ename);

-- 查看表中的索引 show index from 表 
show index from emp

-- 删除索引  drop index 索引名 on 表 删除表中的
drop index wzk on emp;

设计索引的原则

• 索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效地使用索引。
  • 要在条件列上创建索引，而不是查询列；
  • 尽量使用唯一索引；
  • 使用短索引；
  • 利用最左前缀；
  • 对于InnoDB存储引擎的表，尽量手工指定主键。

索引设计的误区

• 设计索引时，有一些常见的误区，总结如下：
  • 不是所有的表都需要创建索引；
  • 不要过度索引；
  • 谨慎创建低选择度索引。

索引设计的一般步骤

• 设计索引时，一般可以采用下面的步骤：
  • 整理表上的所有SQL，重点包括select、update、delete操作的where条件所用到的列的组合、关联查询的关联条件等；
  • 整理所有查询SQL的预期执行频率；
  • 整理所有涉及的列的选择度，列的不同值相比总非空行数的比例较大，选择度越好，比如全部都是唯一值的主键列选择度最高；
  • 遵照之前提到的设计原则，给表选择合适的主键；
  • 优先给那些执行频率最高的SQL创建索引，执行频率很高的SQL，使用到的索引的效率对整体性能影响也会很大，选择其中选择度最高的列创建索引，如果选择度都不够好，那么应该考虑是否可以使用其他选择度更好的条件，或者选择创建联合索引；
  • 按执行频率排序，依次检查是否需要为每个SQL创建索引；
  • 索引合并，利用复合索引来降低索引的总数，充分利用最左前缀的原则；
  • 上线之后，通过慢查询分析、执行计划分析、索引使用统计，来确定索引的实际使用情况。

BTREE与HASH索引

• MEMORY存储引擎的表可以选择使用BTREE索引或HASH索引，两种不同类型的索引各有其不同的适用范围。HASH索引有一些重要的特征在使用时需特别注意：而对于BTREE索引，当使用>、<、>=、<=、BETWEEN、!=或者<>，或者LIKE 'pattern'（其中'pattern'不以通配符开始）操作符时，都可以使用相关列上的索引。
  • 只用于使用=或<=>操作符的等式比较；
  • 优化器不能使用HASH索引来加速ORDER BY操作；
  • MySQL不能确定在两个值之间大约有多少行。如果将一个MyISAM表改为HASH索引的MEMORY表，会影响一些查询的执行效率；
  • 只能使用整个关键字来搜索一行。
• 下列范围查询适应于BTREE索引和HASH索引：
  • select * from t1 where key_col = 1 or key_col in(15,18,20);
• 下列范围只适用于BTREE索引：当对索引字段进行范围查询的时候，只有BTREE索引可以通过索引访问，而HASH索引实际上是全表扫描的。
  • select * from t1 where key_col > 1 and key_col < 10;
  • select * from t1 where key_col like 'ab%' or key_col between 'lisa' and 'simon';

• 知识点1：不可见索引
• 知识点2：倒序索引
• 所谓不可见，指的对于查询优化器不可见，SQL在执行时自然也就不会选择，但在查看表结构时候索引仍然能看到，也可以通过information_schema.statistics或者show index来查看索引是否可见的状态。
• 索引默认是可见的，可以通过以下方式来创建不可见索引：引用不可见索引的目的：主要是为了减小对于表上的索引进行调整时潜在风险。
  • 在创建索引时指定invisble关键字来创建不可见索引
  • 通过命令单独添加不可见索引
  • 通过alter table命令来修改索引是否可见
• MySQL8.0中，正式增加了对于倒序索引（descending index）的支持。
• 注意：
  • 由于倒序索引的引入，MySQL8.0取消了对于group by操作的隐式排序，如果业务中有依赖于此特性的，在升级数据库版本的时候要谨慎。

　　create index 索引名称on 表(字段名)

视图

• 视图（View）是一种虚拟存在的表，对于使用视图的用户来说基本上是透明的。
• 视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。
• 视图的优势主要包括：
  • 简单
  • 安全
  • 数据独立
• 视图的操作主要包括：视图创建前提条件是：需要有CREATE VIEW权限，并且对于查询涉及的列有SELECT权限。如果使用CREATE OR REPLACE 或者ALTER修改视图，那么还需要该视图的DROP权限。
  • 创建视图
  • 修改视图
  • 删除视图
  • 查看视图定义
• 创建视图语法：

CREATE [ALGORITHM]={UNDEFINED|MERGE|TEMPTABLE}]

VIEW 视图名 [(属性清单)]

       AS SELECT 语句

       [WITH [CASCADED|LOCAL] CHECK OPTION];

• 修改视图语法（ALTER 或者 CREATE OR REPLACE）：

        ALTER|CREATE OR REPLACE [ALGORITHM]={UNDEFINED|MERGE|TEMPTABLE}]

VIEW 视图名 [(属性清单)]

       AS SELECT 语句

       [WITH [CASCADED|LOCAL] CHECK OPTION]; 

 代码演示：

-- 创建视图
create view showOne as (
    select * from (
        select *,(row_number() over (partition by deptno order by sal asc)) eno from emp
    ) t 
    where t.eno = 1
)

-- 查看视图
select * from showOne

-- 查看视图信息 show table status from 数据库名  通过查看数据库来查看其中的表或者视图信息
show table status from scott

-- 删除视图
drop view showOne

• 视图的可更新性和视图中查询的定义有关系，以下类型的视图是不可更新的：
  • 包含以下关键字的SQL语句：聚合函数（SUM、MIN、MAX、COUNT等）、DISTINCT、GROUP BY、HAVING、UNION或者UNION ALL；
  • 常量视图；
  • SELECT中包含子查询；
  • JOIN；
  • FROM一个不能更新的视图；
  • WHERE字句的子查询引用了FROM字句中的表。
• [WITH [CASCADED|LOCAL] CHECK OPTION] 决定了是否允许更新数据使记录不再满足视图的条件。
  • LOCAL只要满足本视图的条件就可以更新；
  • CASCADED则必须满足所有针对该视图的所有视图的条件才可以更新。
  • 注意：如果没有明确LOCAL还是CASCADED，则默认是CASCADED。
• 用户可以一次删除一个或者多个视图，前提是必须有该视图的DROP权限。
  • DROP VIEW [IF EXISTS] view_name [,view_name];
• 可以通过下面的命令显示视图的信息：
  • SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern'];
• 可以通过下面的命令查看视图的定义：
• SHOW CREATE VIEW view_name；

存储过程

　　

• 存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合。
• 优势：存储过程和函数的区别在于函数必须有返回值，而存储过程没有，存储过程的参数可能使用IN、OUT、INOUT类型，而函数的参数只能是IN类型。
  • 简化应用开发人员的工作；
  • 减少数据在数据库和应用服务器之间的传输；
  • 提高数据处理的效率

如果有函数从其他类型的数据库迁移到MySQL，那么就可能因此需要将函数改造成存储过程

• 对存储过程和函数的相关操作时，需要首先确认用户是否具有相应的权限。创建、修改存储过程或者函数语法：
  • 创建存储过程或函数：需要CREATE ROUTINE权限；
  • 修改或者删除存储过程或函数：需要ALTER ROUTINE权限；
  • 执行存储过程或函数：需要EXECUTE权限
• 

 

• 调用过程的语法：CALL sp_name([parameter[,...]])
• MySQL的存储过程和函数中允许包含DDL语句，也允许在存储过程中执行或回滚，但存储过程和函数中不允许执行LOAD DATA INFILE语句。此外，存储过程和函数中可以调用其他的过程或者函数。
• 通常我们在执行创建过程和函数之前，都会通过”DELIMITER $$”命令将语句的结束符从”;“修改成其他符号，这里使用的是”$$”，这样在过程和函数中的”;”就不会被MySQL解释成语句的结束而提示错误。存储过程或函数创建完毕，通过”DELIMITER ;”命令再将结束符修改回成“;”。
• 和视图创建语法稍有不同，存储过程和函数的CREATE语法不支持使用CREATE OR REPLACE对存储过程或函数进行修改，如果需要对已有的存储过程或函数进行修改，需要执行ALTER语法。
• characteristic特征值的部分说明如下：
  • LANGUAGE SQL：说明下面过程的BODY是使用SQL编写，系统默认；
  • [NOT] DETERMINISTIC：DETERMINISTIC确定的，即每次输入一样输出也一样的程序；NOT DETERMINISTIC非确定的，默认是非确定的；
  • {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}：如果没有明确特征，默认使用的值是CONTAINS SQL。
    • CONTAINS SQL：子程序不包含读或写数据的语句
    • NO SQL：子程序不包含SQL语句
    • READS SQL DATA：子程序包含读数据的语句，但不包含写数据语句
    • MODIFIES SQL DATA：子程序包含写数据的语句
  • SQL SECURITY {DEFINER | INVOKER}：可以用来指定子程序该用创建子程序者的许可来执行，还是使用调用者的许可来执行，默认值是：DEFINER 。
  • COMMIT 'string'：存储过程或者函数的注释信息。

    　　 

             

 　　　　

• 一次只能删除一个存储过程或者函数，前提是必须有该存储过程或函数的ALTER ROUTINE权限。
  • DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name;
• 查看存储过程或者函数的状态：
  • SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern'];
• 查看存储过程或者函数的定义：通过查看information_schema.Routines了解存储过程和函数的信息
  • SHOW CREATE {PROCEDURE | FUNCTION} sp_name;
• 变量的定义：
  • DECLARE var_name[,...] type [DEFAULT value];
• 变量的赋值：
  • SET var_name = expr [,var_name=expr]...;
  • 可以通过查询将结果赋值给变量，这要求查询返回的结果必须只有一行。

 　　

• 条件的定义：

 　　

• 条件的处理：

 　　

• handler_type只支持CONTINUE和EXIT两种，前者表示继续                                                                执行语句，后者则表示终止执行。
• 声明光标：
  • DECLARE cursor_name CURSOR FOR select_statement;
• OPEN光标：
  • OPEN cursor_name;
• FETCH光标：
  • FETCH [[NEXT] FROM] cursor_name INTO var_name[,var_name]...
• CLOSE光标：
  • CLOSE cursor_name;

注意：变量、条件、处理程序、光标都是通过DECLARE定义的，它们之间是有先后顺序要求的。变量和条件必须在最前面声明、然后才能是光标的声明，最后才可以是处理程序的声明。

 

• IF语句：
  • IF search_condition THEN statement_list

[ELSEIF search_condition THEN statement_list]...

[ELSE statement_list]

END IF

 　　

 　　

• CASE语句：
  • CASE

WHEN search_condition THEN statement_list

[WHEN search_condition THEN statement_list]...

[ELSE statement_list]

END CASE

 　　

 

• LOOP语句：
  • [begin_label:] LOOP

statement_list

END LOOP [end_label]

• LEAVE语句：
  • LEAVE语句用来从标注的流程构造中退出，通常和BEGIN...END或者循环一起使用。
• ITERATE语句：
  • ITERATE语句必须用在循环中，作用是路过当前循环的剩下的语句，直接进入下一轮循环。
• REPEAT语句：
  • 有条件的循环控制语句，当满足条件的时候退出循环。
  • [begin_label:]  REPEAT
  • statement_list  search_condition END REPEAT [end_label]

 　　

• REPEAT语句：
  • 有条件的循环控制语句，当满足条件的时候退出循环。
  • [begin_label:]  REPEAT

statement_list  

UNTIL search_condition

END REPEAT [end_label]

 　　

 

• WHILE语句：
  • 有条件的循环控制语句，当满足条件的时候执行循环内容。
  • [begin_label:]  WHILE

search_condition DO

statement_list  

END WHILE[end_label]

 　　 

触发器

• 可以将数据库按自定义的时间周期触发某种操作，也可以理解为时间触发器。
• 案例分析：
• 创建测试表
• 创建事件调度器，每隔5秒向测试表插入一条记录
• 查看调度器状态
• 隔几秒后，查看测试表，发现并没有数据插入
• 查看事件调度器状态，发现默认是关闭的
• 通过命令打开调试器，同时show processlist发现新产生一个后台进程
• 隔几秒的，再次查看测试表，发现有数据的插入
• 为了防止表变得很大，创建一个新的调度器，每隔一秒清空一次测试表
• 可以禁止或者删除调度器

• 触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。

　　

• 语法：
  • 注意：触发器只能创建在永久表上，不能对临时表创建触发器。
  • trigger_time：触发时间，可以是BEFORE或者AFTER
  • trigger_event：触发事件，可以是INSERT、UPATE、DELETE。
• 一次可以删除一个触发程序，如果没有指定schema_name，默认为当前数据库，具体语法如下：
• DROP TIGGER [schema_name.]trigger_name；

           

• 触发器执行的语句有以下两个限制：MySQL的触发器是按照BEFORE触发器、行操作、AFTER触发器的顺序执行的，其中任何一步操作发生错误都不会继续执行剩下的操作。如果是对事务表进行的操作，那么会整个作为一个事务被回滚（Rollback）,但是如果是对非事务表进行的操作，那么已经更新的记录将无法回滚，这也是设计触发器的时候需要注意的问题。
  • 触发程序既不能调用将数据返回客户端的存储程序，也不能使用CALL语句的动态SQL语句，但是允许存储程序通过参数将数据返回触发程序。也就是存储过程或者函数通过OUT或者INOUT类型的参数将数据返回触发器是可以的，但是不能调用直接返回数据的过程;
  • 不能在触发器中使用以显式或隐式方式开始或结束事务语句，如START TRANS-ACTION、COMMIT或ROLLBACK。

LOCK TABLES 和 UNLOCK TABLES

• LOCK TABLES可以锁定用于当前线程的表。如果表被其他线程锁定，则当前线程会等待，直到可以获取所有锁定为止。
• UNLOCK TABLES可以释放当前线程获得的任何锁定。当前线程执行另一个LOCK TABLES时，或当服务器的连接被关闭时，所有由当前线程锁定的表被隐含地解锁，具体语法如下：
  • LOCK TABLES

　　table_name [AS alias] {READ [LOCK] | [LOW_PRIORITY] WRITE}

　　[,table_name [AS alias] {READ [LOCK] | [LOW_PRIORITY] WRITE}]..

　　UNLOCK TABLES

• 注意：LOCK TABLES和UNLOCK TABLES有时也写为LOCK TABLE和UNLOCK TABLE，两种写法含义一样。 

事务控制

　　

• MySQL通过SET AUTOCOMMIT、START TRANSACTION、COMMIT、ROLLBACK等语句支持本地事务，具体语法如下：
  • START TRANSACTION | BEGIN [WORK]

　　COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]

　　ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]

　　SET AUTOCOMMIT = {0|1}

• START TRANSACTION或BEGIN语句可以开始一项新的事务；
• COMMIT和ROLLBACK用来提交或者回滚事务；
• CHAIN和RELEASE子句分别用于定义在事务提交或者回滚之后的操作，CHAIN会立即启动一个新事务，并且和刚才的事务具有相同的隔离级别，RELEASE则会断开和客户端的连接；
• SET AUTOCOMMIT可以修改当前连接的提交方式，如果设置了SET AUTOCOMMIT=0，则设置之后的所有事务都需要通过明确的命令进行提交或者回滚。

　　commit;提交事物  之前操作全部进行提交

　　rollback：事物回滚   之前的所有操作全部取消

分布式事务的使用

• MySQL从5.0.3版本起开始支持分布式事务，当前分布式事务只支持InnoDB存储引擎。一个分布式事务会涉及多个行动，这些行动本身是事务性的。所有行动都必须一起成功完成，或者一起回滚。
• 在MySQL中，使用分布式事务的应用程序涉及一个或多个资源管理器和一个事务管理器。要执行一个分布式事务，必须知道这个分布式事务涉及哪些资源管理器，并且把每个资源管理器的事务执行到事务可以被提交或回滚时。
  • 资源管理器（RM）用于提供通向事务资源的途径，数据库服务器是一种资源管理器。该管理器必须可以提交或回滚由RM管理的事务。
  • 事务管理器（TM）用于协调作为一个分布式事务一部分的事务。TM与管理每个事务的RM进行通信。在一个分布式事务中，各个单个事务均是分布式事务的”分支事务“。分布式事务和各分支通过一种命名方法进行标识。
• 用于执行分布式事务的过程使用两阶段提交，发生时间在由分布式事务的各个分支需要进行的行动已经被执行之后。
  • 在第一阶段中，所有的分支被预备好。即它们被TM告知要准备提交。通常，这意味着用于管理分支的每个RM会记录对于被稳定保存的分支的行动。分支指示是否它们可以这么做。这些结果被用于第二阶段。
  • 在第二阶段中，TM告知RM是否要提交或回滚。如果在预备分支时，所有的分支指示它们将能够提交，则所有的分支被告知要提交。如果在预备时，有任何分支指示它将不能提交，则所有分支被告知回滚。
  • 在有些情况下，一个分布式事务可能会使用一阶段提交。

• 分布式事务（XA 事务）的SQL语法如下：分布式的关键在于如何确保分布式事务的完整性，以及在某个分支出现问题时的故障解决。
  • XA {START | BEGIN} xid [JOIN|RESUME]
  • xid值包含1~3个部分：xid: gtrid [,bqual [,formatID ]]
    • gtrid：是一个分布式事务标识符，相同的分布式事务应该使用相同的gtrid，这样可以明确知识XA事务属于哪个分布式事务。
    • bqual：是一个分支限定符，默认值是空串。对于一个分布式事务中的每个分支事务，bqual值必须是唯一的。
    • formatID：是一个数字，用于标识由gtrid和bqual值使用的格式，默认值是1。
• MySQL的分布式事务还存在一些问题，在数据库或者应用异常的情况下，可能会导致分布式事务的完整性或者需要人工介入处理。如果需要使用分布式事务，建议尽量采用MySQL5.7或者更新的版本。

JDBC

　　Java数据库连接技术(Java DataBase Connectivity) 

　　

　　

• JDBC的内容
• JDBC API
  • 定义了一系列的接口和类，集成在java.sql和javax.sql包中
• DriverManager
  • 管理各种不同的JDBC驱动
• JDBC 驱动
• 负责连接不同类型的数据库

             

　　

　　　

 　　

 　　

import java.sql.*;

public class test {
    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        // 第一步：引入jdbc jar包
        // 第二步：加载驱动
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 第三步：通过驱动DriverManager获取conn对象
            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/scott?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC", "root", "123456");
            // 第四步：通过conn对象获取stmt对象
            stmt = conn.createStatement();
            // 第五步：调用stmt的executeQuery方法或者executeUpdate方法 参数为sql语句 来查找数据库中数据
            rs = stmt.executeQuery("select * from emp");
            // 第六步：接受stmt执行sql后的结果并处理
            while (rs.next()){
                System.out.println(rs.getInt("empno") + "*-----*" + rs.getString("ename"));
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            // 第七步：关闭流
            try {
                if(rs!=null)rs.close();
                if(stmt!=null)stmt.close();
                if(conn!=null)conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }
}