MySQL数据库编程基础入门1

[TOC]

0x00 快速入门

问:什么是数据库（Database）?

• 1.数据库就是一个文件系统，需要利用特定的命令SQL语句来进行操作;
• 2.数据库是按照数据结构来组织、存储和管理数据库的建立在计算机存储设备上的仓库（数据库是数据的结构化集合）;
• 3.数据库赛长期存储在PC中，有组织、可共享的数据集合，数据库中的数据指的是以一定的数据模型组织、描述和存储在一起、尽可能小的冗余度，较高的数据独立性和易扩展性的特点并可在一定的范围内为多个用户共享;
• 4.具有对象（例如数据库，表，视图，行和列）的逻辑模型提供了灵活的编程环境

问:数据库功能作用?

1. 存储数据:数据仓库持久化存储;
2. 访问权限:限制不同的角色访问不同的数据库以及表字段;
3. 查询数据:数据查询方便、快捷;

(1) SQL介绍

描述:Structured Query Language 结构化查询语言（非过程性语言）:SQL是用来存取关系数据库的语言，具有查询、操纵、定义和控制关系型数据库的四方面功能; 为加强SQL的语言能力，各厂商增强了过程性语言的特征如PL/SQL 过程性处理能力，SQL Server、Sybase的T-SQL;

(2) MySQL介绍

描述:MySQL是最流行的开源SQL数据库管理系统(关系型数据库/NoSQL数据库)，MySQL以联合创始人Monty Widenius的女儿My命名，现在已经被收购所以由 Corporation开发，分发和支持。有免费的社区版本和收费的EnterPrise版本存在有闭源的风险，但是它的开发者又采用收购前的分支开发出它的兄弟Mariadb数据库两则有一定的异同，并且在CentOS6/7发型版本中默认已不再是MySQL;

MySQL软件提供了一个非常快速的多线程,多用户、健壮的SQL（结构化查询语言）数据库,MySQL服务器用于任务关键型、高负载 生产系统，以及嵌入到大规模部署;

• 1.MySQL是一个数据库管理系统。
• 2.MySQL数据库是关系型的。
• 3.MySQL软件是开源的:软件使用GPL（GNU通用公共许可证）
• 4.MySQL数据库服务器非常快速，可靠，可扩展且易于使用。
• 5.MySQL-Server在客户端/服务器或嵌入式系统中运行。
• 6.MySQL相关支持组件，比如采用PHP和JAVA通过通用数据访问对象进行对数据库进行操作;

MySQL的主要功能:

• 内部结构和可移植性
  • 1.用C和C ++编写适用于不同的编译器和各个系统平台;
  • 2.使用具有独立模块的多层服务器设计，设计为使用内核线程完全多线程，可以轻松使用多个CPU（如果有）。
  • 3.使用MyISAM索引压缩非常快的B树磁盘表（），使用非常快速的基于线程的内存分配系统。
  • 4.使用优化的嵌套循环联接执行非常快速的联接，实现内存中的哈希表，用作临时表。
  • 5.提供事务性和非事务性存储引擎
  • 6.使用高度优化的类库来实现SQL函数，该类库应尽可能快。
  • 7.提供服务器作为在客户端/服务器网络环境中使用的单独程序，以及作为可嵌入（链接）到独立应用程序中的库
• 繁多的数据类型:
  • 1.(有符号/无符号)整型 / 浮点型 / 字符型 / 文本类型 / 二进制类型 / 日期时间类型和开放GIS空间类型
  • 2.固定长度和可变长度的字符串类型
• 声明和函数
  • 1.SQL的DQL中支持完全运算符计算和函数支持,如WHERE id % 2 = 0;.
  • 2.字符类函数CONNCAT()，统计类函数COUNT()，时间类函数TIMESTAMP()，编码加密类函数md5();
  • 3.支持标准的SQL语法和ODBC语法，比如:GROUP BY和 ORDER BY子句
  • 4.支持左右连接，交叉连接，全连接:LEFT OUTER JOIN和 支持RIGHT OUTER JOIN标准SQL
  • 5.函数名称与表或列名称的独立性。例如，ABS是一个有效的列名。唯一的限制是对于函数调用，函数名称和其后的“ (”之间不允许有空格
• 安全
  • 1.连接服务器时，通过对所有密码通信进行加密来实现密码安全。连接验证非常灵活和安全，并且可以进行基于主机的验证。
• 可扩展性和限制
  • 1.支持大型数据库:包含5000万条记录的数据库,每个有200,000个表和大约5,000,000,000行,MySQL对数据库的数量本身赛没有限制，但是基础文件系统可能对目录数量有所限制。
  • 2.每个表最多支持64个索引，每个索引可以包含1到16列或部分列。InnoDB表的最大索引宽度为767字节或3072字节
• 连接协议
  • 客户端可以使用多种协议连接到MySQL Server,使用TCP / IP套接字进行连接,在Windows系统上使用命名管道进行连接, Windows服务器还支持共享内存连接, Unix系统上客户端可以使用Unix域套接字文件进行连接
  • MySQL客户端程序可以用多种语言编写:C，C ++，Eiffel，Java，Perl，PHP，Python，Ruby和Tcl的API
  • Connector / ODBC（MyODBC）接口为使用ODBC（开放数据库连接）连接的客户端程序提供MySQL支持
  • Connector / J 接口为使用JDBC连接的Java客户端程序提供MySQL支持
  • Connector / NET 使开发人员可以轻松创建需要与MySQL安全，高性能数据连接的.NET应用程序。它实现了必需的ADO.NET接口，并集成到ADO.NET感知工具中。
• 本土化定位
  • 1.支持不同的字符集可以在编译时和运行时指定它们，可以设置多种语言项客户端提供错误信息;
  • 2.排序和比较是根据默认字符集和排序规则完成的。
  • 3.可以指定时区以及服务器时区的动态修改。
• 客户端和工具
  • 1.命令行: mysqldump | mysqladmin
  • 2.图形化程序: MySQL Workbench
  • 3.检查优化以及表修复: mysqlcheck
  • 4.MyISAM引擎操作工具 myisamchk

常见的关系化数据库:

• 商业化数据库
  • 微软的SQLServer: http://www.microsoft.com/SQL/default.mspx
  • IBM 的DB2
  • Sybase : http://www.sybase.com/
• 开源的数据库
  • MySQL:http://www.mysql.com/
  • SQLite:http://www.sqlite.org/
  • HSQLDB 纯Java小型数据库:http://www.hsqldb.org/
  • PostgreSQL:http://www.postgresql.org/

问:什么是关系型数据库? 答:主要是用来描述实体entity与实体之间的关系，比如学生和班级；我们常用E-R关系图来进行数据库表字段设计,E-R()关系图是一个DBA必须需要了解并且掌握的:

问:什么是E-R图? 答:E-R图也称实体-联系图(Entity Relationship Diagram)，提供了表示实体类型、属性和联系的方法，用来描述现实世界的概念模型。

• 实体(Entity):方框
• 属性(Property):椭圆
• 关系(Relation):菱型

WeiyiGeek.

WeiyiGeek.

问:什么是非关系型数据库(NoSQL)? 答:它是一个Key:Value类型的数据库;

(3) 版本特性

MySQL发行版本：

• MySQL Community Server: 社区Community版本免费遵循GPL协议
• MySQL Enterprise Server: 企业版本商业用途;

MySQL Community 版本：

• 5.7.x 系列 (当前版本 5.7.28)
• 8.0.x 系列 (当前版本 8.0.20)

问:MySQL 5.7.x 与 8.0.x 版本有什么区别?

• 1.速度:MySQL 8.0 的速度要比 MySQL 5.7 快 2 倍 (光这一个理由即可)
• 2.性能:读/写工作负载、IO 密集型工作负载、以及高竞争（”hot spot”热点竞争问题）工作负载
• 3.NoSQL:MySQL 从 5.7 版本开始提供 NoSQL 存储功能,但是8.0系列版本得到了更大的改进该项功能消除了对独立的 NoSQL 文档数据库的需求，而 MySQL 文档存储也为 schema-less 模式的 JSON 文档提供了多文档事务支持和完整的 ACID 合规性。
• 4.窗口函数(Window Functions):8.0版本新增概念，它可以用来实现若干新的查询方式；它与 SUM()、COUNT() 这种集合函数类似，但它不会将多行查询结果合并为一行，而是将结果放回多行当中，即窗口函数不需要 GROUP BY。
• 5.隐藏索引:8.0支持索引可以被“隐藏”和“显示”，当对索引进行隐藏时，它不会被查询优化器所使用，可以使用这个特性用于性能调试；
  • 例如:我们先隐藏一个索引，然后观察其对数据库的影响。如果数据库性能有所下降，说明这个索引是有用的，然后将其“恢复显示”即可；如果数据库性能看不出变化，说明这个索引是多余的可以考虑删掉。
• 6.降序索引:为索引提供按降序方式进行排序的支持，在这种索引中的值也会按降序的方式进行排序;
• 7.UTF-8 编码:从 MySQL 8 开始，使用 utf8mb4 作为 MySQL 的默认字符集。
• 8.JSON:MySQL 8 大幅改进了对 JSON 的支持，添加了基于路径查询参数从 JSON 字段中抽取数据的 JSON_EXTRACT() 函数，以及用于将数据分别组合到 JSON 数组和对象中的JSON_ARRAYAGG() 和 JSON_OBJECTAGG()聚合函数(重点关注) 。
• 9.可靠性:InnoDB 现在支持表 DDL 的原子性(InnoDB 表上的 DDL 也可以实现事务完整性了)，要么失败回滚要么成功提交，不至于出现 DDL 时部分成功的问题，此外还支持 crash-safe 特性，元数据存储在单个事务数据字典中(重点关注)。
• 10.安全性:对 OpenSSL 的改进、新的默认身份验证、SQL角色、密码强度、授权。
• 11.高可用性(High Availability):InnoDB 集群为您的数据库提供集成的原生 HA 解决方案。
• 12.通用表表达式(Common Table Expressions CTE):在复杂的查询中使用嵌入式表时，使用 CTE 使得查询语句更清晰。
• 13.MySQL 8.0不支持通过在 数据目录下手动创建目录（例如，使用mkdir）来创建数据库目录 。

参考资料:

• MySQL 8.0 Reference Manual:https://dev.mysql.com/doc/refman/8.0/en/
• 详细更新说明：https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-11.html
• 官方发布说明
• MySQL 8 正式版的新增功能：https://mysqlserverteam.com/whats-new-in-mysql-8-0-generally-available/

0x01 数据库架构

描述:了解了MySQL数据库架构有助于我们深入学习MySQL,以及后期的性能调优;

MySQL的逻辑架构主要分为三个层次:

• 服务层:为客户端请求做连接处理，授权认证，安全等;
• 核心层:查询解析，分析，优化，缓存，所有内建函数，存储过程，触发器，视图;
• 存储引擎层:存储和提取数据以及事务处理;

WeiyiGeek.逻辑架构

WeiyiGeek.逻辑架构

名词解释:

• 客户端:MySQL客户端并不是单单只指界面或者命令行的MySQL数据库连接软件,它是一种复合概念包含了不同的编程语言编写的后端查询存储应用程序以及所调用的API接口;
  • 比如:MySQL-Client命令行,Native 数据库连接软件,以及PHP的PDO扩展和JAVA的JDBC数据库连接驱动等;

1.服务层 描述:MySQL数据库是一个单进程多线程的应用程序;

WeiyiGeek.服务层

WeiyiGeek.服务层

2.核心层 描述:主要针对于SQL语句进行解析优化,并且查看是否存在缓存记录以提高快速数据查询的效率;

WeiyiGeek.核心层

WeiyiGeek.核心层

3.存储引擎层 描述:有了改层不同的存储引擎,可以根据多个应用场景进行选择不同的存储引擎,他们之间的算法以及IO执行效率都是不同需要根据场景选择。并且他们完美支持WINDOWS/LINUX的文件系统，后面再学习优化的时候重点了解；

WeiyiGeek.存储引擎层

WeiyiGeek.存储引擎层

WeiyiGeek.MySQL架构总图

WeiyiGeek.MySQL架构总图

0x02 库设计标准和原理

描述:MySQL数据库服务器、数据库和表的关系:

• 所谓安装数据库服务器，只是在机器上装了一个数据库管理程序，这个管理程序可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。
• 为保存应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体的数据。
• 数据在数据库中的存储方式:表的一行称之为一条记录;

WeiyiGeek.DB数据库和表的关系

WeiyiGeek.DB数据库和表的关系

（1）MySQL数据库设计标准(重要):

• 1.数据库：采用小写为首加_下划线连接，例如database_project;
• 2.数据表：采用小写为首加_下划线连接，例如table_project;
• 3.数据列：驼峰式命名法第一个单词小写，后面首字母大写，例如displayName;
• 4.主键(Primary Key):每一个表都应设立主键(快速定位，保证唯一性),主键字段Values一般用整数或短字符串
• 5.索引(Index):为了能加快某列查询的速度就需要将某列字段添加索引，例如为了加快按日期查询的速度，可以建立生日的索引;
• 6.连接(JOIN):连接不同表的字段内外连接，左右连接，全连接，交叉链接;
• 7.外键(FOREIGN KEY):作为一种约束条件用于维护数据的完整性（不让用户乱删、错改）。

（2）数据库默认库介绍 描述:我们通过show databases命令可以看见数据库默认的库，下面我们介绍一下这些库的具体应用;

MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
7 rows in set (0.16 sec)

• 1.information_schema库: 简称数据字典表示数据的数据里面保存了mysql服务器所有数据库的信息比如数据库的名、数据库的表、访问权限、数据库表的数据类型，数据库索引的信息，其中表是以视图表进行构建的;
• 2.mysql库: MySQL的核心数据库(类似于sql server中的master表)是数据库用户表、授权表、以及相关库配置表,例如时区ZONE等;
• 3.performance_schema库: 主要用于收集数据库服务器性能参数，可用于监控服务器在一个较低级别的运行过程中的资源消耗、资源等待等情况;
• 4.sys库: 库中所有的数据源来自performance_schema。目标是把performance_schema的把复杂度降低，让DBA能更好的阅读这个库里的内容,让DBA更快的了解DB的运行情况

（3）数据库中表 什么是表? 答:正如库是存放表的容器，表是存放数据的一种容器。表有row和col组成；在数据库中我们定义列的属性，随着数据一条条的增加，行业不断的增加。所以说列是结构而行是信息;

为什么是表？ 答:表是关系型数据库的重要基础。数据越大存储管理越麻烦，非常容易导致数据出现问题;

• 不完整
• 不准确
• 太多冗余

因此关系型数据库中通过表与表之间的关系解决此问题,在表的设计上满足三范式，一共分为6种范式但是往往只是使用前三范式;

1.第一范式:数据库表中的字段都是单一的属性的不可以再分，这个属性有基本类型构造，对于数据的属性能分就分分到不能再分未至，即一个列就是一个原子;

#比如下面案例前者是不满足第一范式，后者是满足的
#序号 地址
1 北京市海淀区南大街

#编号 城市 曲线 街道
1 北京市 海淀区 南大街

2.第二范式:满足第一范式的基础上，数据库表中不存在非关键字段对任意一候选关键字段的部分函数依赖(部分函数依赖只存在组合关键字中某些字段决定非关键字的情况)，不能存在组合关键字；

• 简单的说需要满足各管各的别相互影响，别把所有的东西都放在一个表里;
• 比如下面的例子我们可以将下列数据存放为三个表学生表、课程表、成绩表等;

WeiyiGeek.第二范式

WeiyiGeek.第二范式

• 3.第三范式:在第二范式的基础上，数据表中如果不存在非关键字段对任意候选关键字段的传递函数依赖则符合第三范式，决定某个字段值必须是主键。
  • 所谓的传递函数依赖，指的是如果存在A -> B -> C的决定关系，则C传递函数依赖于A，也就是说表中的字段和主键直接对应不同依靠的其他的中间字段;
  • 比如下面的列子中我们可以将以下数据存放为学生表、学院表，其中学院存在于两个表之中;

WeiyiGeek.第三范式

WeiyiGeek.第三范式

简单的说建表原则: 1) 一对多建表原则

• 比如:分类和商品
• 原则:在商品表中添加一个外键指向分类表中ID主键;

WeiyiGeek.

WeiyiGeek.

2) 多对多建表原则

• 比如:老师和学生，学生和课程
• 原则:多建一张中间表，将多对多的关系拆成一对多的关键，并且中间表一般至少有两个外键分别指向来源表;

WeiyiGeek.

WeiyiGeek.

(3) 一对一建表原则

• 比如:公民和身份证,班级和班长
• 原则:两表行一一对应,可以采用内连接方式直接连接或者采用唯一约束UNIQUE和FOREIGN进行表的绑定主键;
• 实际用途：可以进行拆表操作，将个人常用信息和不常用信息进行拆分，减少表结构的臃肿;

WeiyiGeek.

WeiyiGeek.

（4）表索引Index 什么是索引? 答:Index索引是帮助MySQL高效获取数据的数据结构。索引在存储引擎中实现，每种存储引擎的索引都不一定完全相同，每种存储引擎也不一定支撑所有的索引类型;

默认数据库查询数据是全表扫描在数据量小的情况下查询效率可以忽略不计，但是对于数据量大的表查询的效率便会大大降低; 在创建表的同时创建索引，当对数据库查询的数据建立索引时候采用各类算法以提高查询效率，常规的算法是二叉树算法优化查询,简单的说类似「索引就像书的目录， 通过书的目录就准确的定位到了书籍具体的内容]，只不过这里把引入到数据的物理地址=值;

索引原理 1.采用数据结构「平衡树」(非二叉)，也就是b tree或者b+ tree，重要的事情说三遍：“平衡树，平衡树，平衡树”，主流的RDBMS都是把平衡树当做数据表默认的索引数据结构的，有的数据库也使用哈希桶作用索引的数据结构。 2.我们平时建表的时候都会为表加上主键， 在某些关系数据库中， 如果建表时不指定主键，数据库会拒绝建表的语句执行。 事实上， 一个加了主键的表，并不能被称之为「表」。一个没加主键的表，它的数据无序的放置在磁盘存储器上，一行一行的排列的很整齐， 跟我认知中的「表」很接近。如果给表上了主键，那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构，也就是上面说的「平衡树」结构，换句话说，就是整个表就变成了一个索引。没错， 再说一遍， 整个表变成了一个索引，也就是所谓的「聚集索引」。 这就是为什么一个表只能有一个主键， 一个表只能有一个「聚集索引」，因为主键的作用就是把「表」的数据格式转换成「索引（平衡树）」的格式放置。

WeiyiGeek.平衡树

WeiyiGeek.平衡树

3.其中树的所有结点（底部除外）的数据都是由主键字段中的数据构成，也就是通常我们指定主键的id字段。最下面部分是真正表中的数据。 假如我们执行一个SQL语句：select * from table where id > 89;

WeiyiGeek.表索引

WeiyiGeek.表索引

4.假如一张表有一亿条数据 ，需要查找其中某一条数据，按照常规逻辑， 一条一条的去匹配的话， 最坏的情况下需要匹配一亿次才能得到结果，用大O标记法就是O(n)最坏时间复杂度，这是无法接受的，而且这一亿条数据显然不能一次性读入内存供程序使用， 因此这一亿次匹配在不经缓存优化的情况下就是一亿次IO开销,得出结果时候会非常的长;如果把这张表转换成平衡树结构（一棵非常茂盛和节点非常多的树），假设这棵树有10层，那么只需要10次IO开销就能查找到所需要的数据， 速度以指数级别提升，用大O标记法就是O(log n)，n是记录总树，底数是树的分叉数，结果就是树的层次数。换言之，查找次数是以树的分叉数为底，记录总数的对数，用公式来表示就是

WeiyiGeek.Log对数计算查询次数

WeiyiGeek.Log对数计算查询次数

用程序来表示就是Math.Log(100000000,10)(log以a为底b的对数=n,那么a的n次方=b)，100000000是记录数，10是树的分叉数（真实环境下分叉数远不止10）， 结果就是查找次数，这里的结果从亿降到了个位数。因此利用索引会使数据库查询有惊人的性能提升。

5.非聚集索引是我们平时经常提起和使用的常规索引,非聚集索引和聚集索引一样， 同样是采用平衡树作为索引的数据结构。 索引树结构中各节点的值来自于表中的索引字段， 假如给user表的name字段加上索引 ， 那么索引就是由name字段中的值构成，在数据改变时DBMS需要一直维护索引结构的正确性。如果给表中多个字段加上索引 ， 那么就会出现多个独立的索引结构，每个索引（非聚集索引）互相之间不存在关联。每次给字段建一个新索引， 字段中的数据就会被复制一份出来， 用于生成索引。 因此给表添加索引，会增加表的体积， 占用磁盘存储空间。

WeiyiGeek.非聚集索引

WeiyiGeek.非聚集索引

6.非聚集索引和聚集索引的区别在于， 通过聚集索引可以查到需要查找的数据， 而通过非聚集索引可以查到记录对应的主键值 ， 再使用主键的值通过聚集索引查找到需要的数据,不管以任何方式查询表， 最终都会利用主键通过聚集索引来定位到数据， 聚集索引（主键）是通往真实数据所在的唯一路径。

WeiyiGeek.非聚集索引和聚集索引的区别

WeiyiGeek.非聚集索引和聚集索引的区别

7.覆盖索引是一种例外可以不使用聚集索引就能查询出所需要的数据，也就是平时所说的复合索引或者多字段索引查询。 文章上面的内容已经指出当为字段建立索引以后， 字段中的内容会被同步到索引之中，如果为一个索引指定两个字段，那么这个两个字段的内容都会被同步至索引之中。

-- SQL语句建立索引
create index index_birthday on user_info(birthday);
-- 查询生日在1991年11月1日出生用户的用户名
select user_name from user_info where birthday = '1991-11-1'

SQL语句的执行过程如下:

• 首先，通过非聚集索引index_birthday查找birthday等于1991-11-1的所有记录的主键ID值
• 然后，通过得到的主键ID值执行聚集索引查找，找到主键ID值对就的真实数据（数据行）存储的位置
• 最后， 从得到的真实数据中取得user_name字段的值返回，也就是取得最终的结果我们把birthday字段上的索引改成双字段的覆盖索引(联合|组合索引):create index index_birthday_and_user_name on user_info(birthday, user_name);

通过非聚集索引index_birthday_and_user_name查找birthday等于1991-11-1的叶节点的内容，然而， 叶节点中除了有user_name表主键ID的值以外， user_name字段的值也在里面， 因此不需要通过主键ID值的查找数据行的真实所在， 直接取得叶节点中user_name的值返回即可。 通过这种覆盖索引直接查找的方式，可以省略不使用覆盖索引查找的后面两个步骤，大大的提高了查询性能，

WeiyiGeek.覆盖索引(联合索引)

WeiyiGeek.覆盖索引(联合索引)

索引的分类:

• 1.普通索引:是MySQL的基本索引类型
• 2.唯一索引:唯一索引对应列的值必须唯一但允许空值；
  • 2.1 唯一组合索引则列值组合必须是唯一的;
  • 2.2 主键索引是一种特殊的唯一索引它不允许空值;
• 3.单列索引:指只包含一个列的索引，一个表中可以有多个单列索引;
• 4.组合索引:指表中的多个字段组合建立索引（遵循做前缀集合），8.X 版本组合索引字段随机适配使用都会走索引;
• 5.全文索引:FULLTEXT类型索引，可以在CHAR/VARCHAR或者TEXT类型的列上创建，注意仅MYISAM支持;
• 6.空间索引:对空间数据类型的字段建立索引;

依据那些类别进行索引设置原则:

• 1.索引并非越多越好，需要根据实际的应用来设置
• 2.数据量不多不需要建立索引
• 3.列中的值变化不多也不需要建立索引
• 4.经常排序和分组的数据列要建立索引
• 5.唯一性约束对应使用唯一性索引

基础示例:

-- 普通索引
CREATE TABLE t1 (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    INDEX name_in (name) -- 可以设置索引别名值也可以不设置
);

-- 唯一索引
CREATE TABLE t2 (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    UNIQUE INDEX id_ind (id) -- 可以设置索引别名值也可以不设置
);

-- 单列索引
CREATE TABLE t3 (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    INDEX name_ind (name(10)) -- 可以设置索引别名值也可以不设置
);

-- 组合索引
CREATE TABLE t4 (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    age INT NOT NULL,
    INDEX multi_ind (id,name,age) -- 只有在查询中id + name 或者 id  + age 或者 id + name + age 才会走索引查询，在8.x是不存在这样的问题，下面演示;
);

-- 全文索引
CREATE TABLE t4 (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    age INT NOT NULL,
    info TEXT,
    FULLTEXT INDEX (info) -- 只有在文本类型字符串下使用;
);

-- 显示创建数据表的定义
MySQL [dd]> show create table t2\G;

索引测试 描述:在插入一百万数据后进行数据性能测试;

• 1.分别在有索引和没有索引的情况下执行查询;
• 2.分别在有索引和没有索引的情况下新增导入数据;

#测试表1
CREATE TABLE indexDemo (
  id int,
  num int,
  pass VARCHAR(50)
);

#Linux
for((i = 1; i <= 100; i++))
do 
  `mysql -h127.0.0.1 -P9001 -uroot -ppassword-e "INSERT INTO Demo.indexDemo VALUES ($i,floor($i+rand()*$i),md5($i));"`;
done

#Windows
for /l %%i in (1 1 10000000) do mysql -h127.0.0.1 -P9001 -uroot -ppassword-e "INSERT INTO Demo.indexDemo VALUES (%%i,floor(%%i+rand()*%%i),md5(%%i));"

验证1:有无索引对查询的影响

-- (1) 利用无索引的ID进行查询
MySQL [Demo]> SELECT * FROM indexDemo WHERE id > 500 AND id < 520;
19 rows in set (0.08 sec);

-- 有索引表
CREATE TABLE indexDemo1 (
  id int,
  num int,
  pass VARCHAR(50),
  index id_ind (id)
);


-- (2)对于没有索引表进行导入 (0.10 sec)
MySQL [Demo]> CREATE TABLE indexDemo2 like indexDemo;
Query OK, 0 rows affected (0.11 sec)
MySQL [Demo]> INSERT INTO indexDemo2 SELECT * FROM indexDemo;
Query OK, 1657 rows affected (0.10 sec)
Records: 1657  Duplicates: 0  Warnings: 0


-- (3)对于添加了索引表进行导入 (0.11 sec)
MySQL [Demo]> INSERT INTO indexDemo1 SELECT * FROM indexDemo;
Query OK, 1657 rows affected (0.11 sec)


-- (4) 利用有索引的ID进行查询 0.06 sec
MySQL [Demo]> EXPLAIN SELECT * FROM indexDemo1 WHERE id > 1500 AND id < 1520\G;
           id: 1
  select_type: SIMPLE
        table: indexDemo1
   partitions: NULL
         type: range
possible_keys: id_ind
          key: id_ind
      key_len: 5
          ref: NULL
         rows: 19
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.06 sec)

注意事项:

1.在MySQL 5.7 版本下创建组合索引，只有在使用最左侧字段索引值加其他字段则走索引，否则不走索引比如下图所示，但是在MySQL 8.0不存在该情况;

MySQL [dd]> INSERT INTO t4 VALUES
    -> (1,'aaa',10),
    -> (2,'bbb',13),
    -> (3,'ccc',18),
    -> (4,'ddd',24),
    -> (5,'eee',80);

-- 查看查询是否走索引(后面优化常常用到)
MySQL [dd]> explain SELECT name,age FROM t4 WHERE id > 3 AND age < 80\G;
-- id: 1
-- select_type: SIMPLE
-- table: t4
-- partitions: NULL
-- type: range
-- possible_keys: PRIMARY,multi_ind -- 关键点
-- key: multi_ind
-- key_len: 4
-- ref: NULL
-- rows: 2
-- filtered: 33.33 -- 关键点
-- Extra: Using where; Using index -- 关键点

-- 不加id为查询条件的时候就可以看见异同由于此处是在MySQL8.x版本执行所以只是没有走主键但是任然走的是索引； 
MySQL [dd]> explain SELECT name,age FROM t4 WHERE age < 80\G;
-- id: 1
-- select_type: SIMPLE
-- table: t4
-- partitions: NULL
-- type: index
-- possible_keys: multi_ind
-- key: multi_ind
-- key_len: 1031
-- ref: NULL
-- rows: 5
-- filtered: 33.33
-- Extra: Using where; Using index
-- 1 row in set, 1 warning (0.11 sec)

WeiyiGeek.5.7.28组合索引的影响

WeiyiGeek.5.7.28组合索引的影响

2.RESET QUERY CACHE 刷新查询缓存命令在5.7及以前的版本存在MySQL 8.x 被丢弃，以防止测试数据不准确;

3.对于没有添加索引的表导入数据相比较于有索引的表要快一点，但是在MySQL 8.X 版本其差别不是怎么明显;

总结:

• 1.在关系型数据库中突出强调的就是利用数据之间的关系设计表，用表作为数据的容器;
• 2.表的设计要综合考虑很多因素，参考三范式进行合理规划;
• 3.索引的本质是一种数据结构，牢记索引的设计原则，FULLTEXT索引仅仅能在MYISAM上使用，数据量巨大的表要慎重操作索引(多选择需要查询的列),索引的管理可以使用多种不同的方法。

0x03 库管理工具

1.phpMyAdmin 安装环境：CentOS Linux release 7.6.1810 (Core) / httpd / php 官网：https://www.phpmyadmin.net/

流程步骤：

#php版本
php -v
# PHP 7.3.6 (cli) (built: May 28 2019 09:32:59) ( NTS )
# Copyright (c) 1997-2018 The PHP Group
# Zend Engine v3.3.6, Copyright (c) 1998-2018 Zend Technologies

#我也安装php73的httpd解析php模块
$ ll /etc/httpd/modules/
总用量 6940
-rwxr-xr-x. 1 root root 4588224 10月 31 2018 libphp7.so

#下载phpmyadmin并解压到/var/html/www/中
wget https://files.phpmyadmin.net/phpMyAdmin/4.8.5/phpMyAdmin-4.8.5-all-languages.zip
unzip /opt/phpMyAdmin-4.8.5-all-languages.zip -d /var/www/html/
mv phpMyAdmin-4.8.5-all-languages/ phpMyAdmin/

#调整防火墙
[root@amoabe html]# firewall-cmd --add-port=80/tcp --permanent
success
[root@amoabe html]# firewall-cmd --reload
success

#修改配置使其可以连接到其他的机器上：
vim /var/www/html/phpMyAdmin/libraries/config.default.php
$cfg['AllowArbitraryServer'] = true; #修改为true

查看phpmyadmin后台：

WeiyiGeek.phpmyadmin

WeiyiGeek.phpmyadmin

登录成功后：

WeiyiGeek.phpmyadmin后台

WeiyiGeek.phpmyadmin后台

入坑解决 问题1：phpMyAdmin - 错误缺少 mysqli 扩展

WeiyiGeek.错误

WeiyiGeek.错误

原因：由于phpmyadmin需要用到mysqli扩展,而默认php7.3未安装
解决方法：yum --enablerepo=remi-php73 install -y php php-mysqli

问题2：phpMyAdmin配置文件现在需要一个短语密码的解决方法;

#在phpMyAdmin目录中找到“config.inc.php”，然后用编辑器打开 config.inc.php （如果没有，那就找到另一个文件“config.sample.inc.php”，重命名为“config.inc.php”）搜索下面一行代码：
phpMyAdmin/config.sample.inc.php
phpMyAdmin/libraries/config.default.php
$cfg['blowfish_secret'] = '12312321312312312@thisisasecret@2019';

问题3:变量 $cfg[‘TempDir’] （./tmp/）无法访问， phpMyAdmin无法缓存模板文件，所以会运行缓慢。

[root@amoabe phpMyAdmin]# mkdir tmp
[root@amoabe phpMyAdmin]# chmod 777 tmp

2.Adminer 描述:Adminer（原phpMinAdmin）是用PHP编写的一个功能完备的数据库管理工具,它由一个单一的文件准备部署到目标服务器。 Adminer可用于 MySQL, MariaDB, PostgreSQL, SQLite, MS SQL, Oracle, Firebird, SimpleDB, Elasticsearch and MongoDB.

官网地址:https://www.adminer.org/en/ Adminer主题文件:https://raw.githubusercontent.com/vrana/adminer/master/designs/flat/adminer.css

Adminer特点：

• 1.安全
• 2.用户体验
• 3.性能
• 4.功能设置
• 5.大小

Admier功能：

• 基础SQL语句数据库增删改查,在表中列出搜索、聚合、排序和限制结果的数据
• 列出表的字段、索引、外键和触发器
• 添加和删除表和列
• 更改表的名称、引擎、排序规则、auto_increment和注释
• 更改列的名称、类型、排序规则、注释和默认值
• 创建，修改，删除和搜索索引包括全文 、外键、视图、存储过程和函数、触发器；
• 支持通过文件传输的所有数据类型，SQL语句执行导出表结构，数据，视图，例程，数据库到SQL或CSV；
• 管理事件和表分区(MySQL 5.1)

WeiyiGeek.demo

WeiyiGeek.demo

Adminer安装：

#方式1:采用XMAPP集成环境进行安装然后将其拖入webRoot中
#方式2:采用Docker容器运行它
docker pull adminer #默认lastest版本及最新版本
docker run --restart=always -P 80:8080 adminer

3.SQLyou 描述:无代理和经济有效的MySQL客户端用于管理(SQLyog)和监视性能的工具(SQLDiagnostic Managerfor MySQL (formerly Monyog)) 官网地址:https://www.webyog.com/ 下载地址:https://static.webyog.com/downloads/SQLyog-13.1.5-0.x64Trial.exe

0x04 数据类型

描述:什么是数据类型?为什么要出现数据类型? 答:定义数据类型的本质是在定义列，因为不同的数据类型可以存储并且高效处理各类数据，并且进行数据处理的方式算法也是有所不同的;

数据分类的意义：

• 分配合适得存储空间,不同的数据类型其存储空间是不一致的;
• 采用不同的操作方法,不同的数据类型其数据操作处理是不一致的;
• 对应不同的需求,不同的应用对于数据类型的需求是不一致的;

MySQL数据类型大致分为以下几类:

• 整型:TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT
• 浮点型:FLOAT、DOUBLE、DECIMAL(M,D)
• 布尔型:BOOL、BOOLEAN
• 文本型:CHAR(M)、VARCHAR(M),TINYTEXT,MEDIUMTEXT,TEXT,LONGTEXT
• 二进制型:TINYBLOB、MEDIUMBLOB、BLOB、LONGBLOB
• 日期时间型:YEAR、TIME、DATE、DATETIME、TIMESTAMP
• 枚举类型:SET、ENUM

整型 MySQL中提供多种对于数值的数据类型,不同的数据类型取值范围是不同的，其取值范围越大需要的空间也就越多(注意有无符号数据取值范围); 类型如下:

#查看帮助 help decimal
TINYINT   微小     1B  0-255     -128~127      年龄(age)
SMALLINT  小       2B  0-65535   -32768~32767  技能(skills),员工(id)
MEDIUMINT 中等大小 3B  0-2^24-1  -2^23~2^23-1  行数多(rownum)建议自增长
INT       普通大小 4B  0-2^32-1  -2^31~2^31-1  金钱(money)
BIGINT    大       8B  0-2^64-1  -2^63~2^63-1  人口(Population)

#定义关键字
UNSIGNED:有符号的数据类型
ZEROFILL:长度不够填充置0

基础示例:

-- 整型无符号与有符号定义
CREATE TABLE tinyintsigned(
  col1 TINYINT   -- 默认是有符号
);

CREATE TABLE tinyintUnsigned(
  col1 TINYINT UNSIGNED  -- 定义无符号
);

-- 设置显示位数一般没有作用,只有在设置填充时候
CREATE TABLE intbit(
  col1 INT(3) ZEROFILL -- 长度不够则填充0
);
INSERT INTO intbit VALUES (123),(12),(1);
SELECT * FROM intbit;
-- 执行结果：
-- col1
-- 123
-- 012
-- 001

浮点型 描述:主要为了存储带小数的类型,常常用浮点数和定点数类型用来存储带小数,并且使用(M,D)定点数方式设定M精度和D标度(小数位数);

类型如下:

FLOAT  单精度  4B
DOUBLE 双精度  8B
DECIMAL 定点数  M>D(M+2),M<D(D+2) 其默认值(10,0) [UNSIGNED|ZEROFILL]

基础示例:

-- 浮点数的案例
CREATE TABLE floatDemo(
    col1 FLOAT(3,2) -- 定点数方式
); 
INSERT INTO floatDemo VALUES (3.3),(3.33),(3.333),(3.335);
SELECT * FROM floatDemo;
-- 执行效果:
-- col1
-- 3.30 位数不够采用0补齐
-- 3.33 
-- 3.33 超出了精度范围，只留下两位小数
-- 3.34 四舍五入第三位小数

-- 注意事项:插入数值小数点前的数值长度大于精度长度而还需要满足小数点后面的两位则会查询出错超出定义的范围;
INSERT INTO floatDemo VALUES (33.33)
INSERT INTO floatDemo VALUES (33.3)
查询出错 (1264): Out of range value for column 'col1' at row 1 


-- 定点数案例
CREATE TABLE decimalDemo(
  col1 DECIMAL(5,3) ZEROFILL-- 表示 2位整数，3位小数
);
INSERT INTO decimalDemo VALUES (79.55),(79.555),(70.554),(79.5556);
SELECT * FROM decimalDemo;
-- 执行结果：col1
-- 79.550
-- 79.555
-- 70.554
-- 79.556  也是四舍五入

文本类型 描述:字符串类型是一个统称它可以包含多种不同的数据类型,分为文本字符串类型和二进制字符串类型,它不仅可以存储字符串还可以存储其他的数据类型; 基础类型:

CHAR(M)     定长字符串   0~255B    1<= M <= 255
VARCHAR(M)  变长字符串   0~65535B  L+1B L <= M 

#TEXT类型及其子类型用于存储比较长的非二进制字符串,例如文章或者评论可将TEXT当做VARCHAR加长增强版;
TINYTEXT   0~255B 短文本字符串           < 2^8
TEXT       0-65535B 长文本数据           < 2^16
MEDIUMTEXT 0-16777215B 中等长度文本数据  < 2^24
LONGTEXT   0~4294967295B 极大文本数据    < 2^32

基础示例:

-- CAHR 与 VARCHAR 类型异同
CREATE TABLE chardemo (
    col1 CHAR(4),  -- 非常注意定义存储长度
    col2 VARCHAR(4)
);
INSERT INTO chardemo VALUES ('ab  ','ab  '),('abc','abc'),('abcd','abcd');;
SELECT concat(col1,'!'),concat(col2,'!') FROM chardemo;
-- 执行结果:
-- concat(col1,'!')	concat(col2,'!')
-- ab!	ab  !   前者char后者varchar

SELECT col1,length(col1),col2,length(col2) FROM chardemo;
-- 不同类型存入长度结果
-- col1	length(col1)	col2	length(col2)
-- ab	2	ab  	4    区别之处在于补空格占位前者不算，而后者是存入了空格
-- abc	3	abc	3
-- abcd	4	abcd	4

-- TEXT类型与其子类型案例演示
CREATE TABLE textdemo(
    col1 TEXT
);
INSERT INTO textdemo VALUES ('abc'),('ABC');
SELECT * FROM textdemo WHERE col1 like 'ab'; -- 关键点:模糊查询 MySQL 8.0 Text文本类型是区分大小写的;
SELECT * FROM textdemo WHERE col1 like '%'; -- 关键点:模糊查询;
SELECT * FROM textdemo WHERE col1 like 'a%'
-- 执行结果 col1
-- abc

注意事项:

1.字符串可以区分或者不区分大小写(TEXT类型-MySQL 5.7以下)的串比较匹配查询,并且支持进行模式匹配查询。

2.CHAR类型需要补空格占位,而VARCHAR则是不需要的存入是什么样的显示就是什么样的;

3.在插入数据不满足设定的值长度示进行截断,但是需要依赖sql_mode变量参数的值为空就可不报错插入否则不满足则执行严格的模式;

SHOW variables like 'sql_mode';
SET sql_mode='';
INSERT INTO chardemo VALUES ('ABCDEFG'，'ABCDEFG'); -- 修改sql_mode后可以根据值设置进行截断插入
SELECT * FROM chardemo;
SHOW warnings;

-- 执行结果：
-- Variable_name	Value
-- sql_mode	ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
-- col1	col2
-- ABCD	ABCD

二进制类型 描述:二进制类型及其子类型是用于存储二进制数据的类型(no character set)以bytes为单位存储和比较，是文本类型的另外一种存储类型，可以存储声音/图像以及视频的数据;

基础类型:

TINYBLOB     0~255B 不超过255个字符的二进制字符串    < 2^8
BLOB         0~65535  二进制形式的长文本数据        < 2^16
MEDIUMBLOB   0-16777215B 二进制形式中等长文本数据  < 2^24
BIGBLOB      0-4294967295B 二进制形式极大文本数据  < 2^32

日期时间类型 描述:提供多种用于存储日期和时间的类型，根据需求在开发时候进行选择并且注意格式;

YEAR(M) YY|YYYY(M代表2|4)  1B  1901~2155(4位)/1970~2069(2位) MySQL5.X
TIME    HH:MM:SS           3B  -838:59:59~838:59:59
DATE    YYYY:MM:DD         3B  1000-01-01 ~ 9999-12-31
DATETIME  年月日时分秒     8B  1000-01-01 00:00:00 ~ 9999-12-31 23:59:59         #实际开发中不建议使用此种类型
TIMESTAMP  年月日时分秒     4B  1970-01-01 00:00:00 UTC ~ 2038-01-19 03:14:07 UTC #会受到系统时区影响

基础示例:

-- 特殊类型插入值演示
CREATE TABLE yeardemo(
    col1 YEAR(4),
    col2 YEAR -- MySQL 8.0 没有YEAR(2),默认同上
);
INSERT INTO yeardemo VALUE (2020,20),(0,0),('0','0'); -- 注意此处的‘’
SELECT * FROM yeardemo;
-- 执行结果:
-- col1	col2
-- 2020	2020
-- 0000	0000
-- 2000	2000  关键点


-- TIME类型演示
CREATE TABLE timedemo(
    col1 TIME
);
INSERT INTO timedemo VALUES ('10:59:59'),(1122),('1122'),('2 10:10'),('2 10'),('10'); -- 注意单引号和不加单引号的区别
SELECT * FROM timedemo;
-- 执行结果：
-- col1
-- 10:59:59
-- 00:11:22
-- 00:11:22
-- 58:10:00   表示两天+10小时零10分钟，最右边的数字开头是从小时开始计算
-- 58:00:00
-- 00:00:10  不加:符号时候默认右侧是秒

-- 插入值不能转换成为时间时候会报错(不正确的时间值)
INSERT INTO timedemo VALUES (6565);
查询出错 (1292): Incorrect time value: '6565' for column 'col1' at row 4 


-- DATE类型演示
CREATE TABLE datedemo(
  col1 DATE
);
INSERT INTO datedemo VALUES ('2020-2-2'),('20-02-02'),('20-2-2'); -- 注意引号
SELECT * FROM datedemo; -- 执行结果: col1 都是 2020-02-02


-- DATETIME 类型演示:在实际开发中不建议使用此种类型由于其每次插入都多占2子节相对比与date 、 TIME 类型
CREATE TABLE datetimedemo(
    col1 DATETIME
);
INSERT INTO datetimedemo VALUES ('2020-02-2'),('20-2-2 10:00:59'),('10$10$10 23:59:59'); -- 注意此处可以不采用-作为日期分割符号
SELECT * FROM datetimedemo;
-- 执行结果col1:
-- 2020-02-02 00:00:00
-- 2020-02-02 10:00:59
-- 2010-10-10 23:59:59


-- TIMESTMP 类型演示:该值受到系统时区的影响
SHOW variables like 'time_zone'
SET time_zone='+8:00' -- 设置当前时区加8小时
-- Variable_name	Value
-- time_zone	SYSTEM

CREATE TABLE IF NOT EXISTS timestampdemo(
    col1 TIMESTAMP
);
INSERT INTO timestampdemo VALUES (NOW());
SELECT * FROM timestampdemo;
SET time_zone='+8:00';  -- 会将时间+8小时
SELECT * FROM timestampdemo;
-- 执行结果:
-- col1 2020-02-02 06:44:04
-- col1 2020-02-02 14:44:04 直接在源数据上+8小时

注意事项:

• 在年份中使用’’单引号的时候,会将其中数值当做年份的最右侧的值，比如’0’默认值为2000年,MySQL默认匹配更多的值;
• 由于MySQL支持不严格的语句匹配的特性所以输入日期类型值是非常灵活的;
• 在实际开发中建议使用DATE+TIME或者TIMESTAMP代替DATETIME类型;

枚举类型 描述:其本质是文本字符串类型的一种延伸类型；

• 1.ENUM类型:存储的数据好比是单项选择题，其存储的值为表创建定义的枚举中的值选取一个,输入其他值则会报错;
• 2.SET类型:存储的数据好比是多项选择题，其存储的值也必须是表创建定义的一系列的值，但是不同于ENUM类型的是它可以存入单个或者多个定义的枚举值;

ENUM 枚举类型 1~2B 存储需求1 or 2 bytes
SET 设置类型  1~8B 存储需求取决于集合成员数量;

实际案例:

-- 1.ENUM类型
CREATE TABLE enumDemo(
  sex ENUM('F','M','UN') -- 插入的值只能是其中之一
);

-- 2.SET类型
CREATE TABLE setDemo(
  kemu SET('a','b','c') -- 可以插入单个或者多个枚举值;
);
INSERT INTO setDemo VALUES ('a');
INSERT INTO setDemo VALUES ('a,b'); -- 关键点不是需要包含在一起

0x05 类型选择

(1) 字符串类型的选择

• 1.不同的数据引擎选择倾向不同;
  • MYISAM:读多写少的场景 char性能 > varchar性能;
  • InnoDB:两种性能差不大varchar更节省空间;
  • MEMORY:内存IO效率高但是费用贵，一般也选择varchar类型
• 2.不同的用途选择倾向不同;
  • CHAR存放经常变更的短字符串,VARCHAR则恰恰相反但是会产生数据空洞,如果是在磁盘上存在这样的问题则会导致磁盘碎片增多磁盘IO效率变低;

(2) 浮点数类型的选择

• 1.精度要求不同选择也不同;
• 2.相比较而言定点数的计算代价要昂贵很多，使用最多是在科学计算和金钱汇率相关的应用中;

(3) 大数据类型的选择

• 1.能不用尽量不用TEXT与BLOB有很多替代选择,如果文件确实很大一般采用静态化为文本文件,数据库只记录其路径读取时候采用系统API读取即可效率会更改;
• 2.如果存在经常的删除和更新，建议定期使用optimize table命令进行数据库层面的碎片整理;

(4) 日期类型的选择

• 1.建议选择DATE和TIME来替代DATETIME数据类型;
• 2.也可以采用TIMESTAMP类型来替代DATETIME类型但是需要注意各个机器的时区问题;

总结

• 1.定义数据类型就是定义列，数据类型决定了数据的特性;
• 2.数据类型主要分为字符串类型，整数类型，浮点数、定点数类型、日期类型、二进制类型以及枚举类型;
• 3.根据需求以及空间占用和性能等多个方面来考虑;
• 4.数据类型在不同的存储引擎上表现和处理的方式都是不同的;

0x06 事务处理

描述:什么是事务? 事务是由一组SQL语句组成的逻辑处理单元,我们常常进行简读为ACID;

• 原子性(Atommicity):事务是一个原子操作单元,对其数据的修改要么全部执行要么全都不执行;
• 一致性(Consistent):在事务开始和结束时,数据修改都必须保持一致状态;
• 隔离性(Isolation):数据库系统提供一定的隔离机制,保证事务处理在不受外部并发操作时候影响它是独立执行的,意味着事务处理的过程中的中间状态对外部是不可见反之亦然;
• 持久性(Durable):事务完成之后对于数据的修改是永久性，即使出现系统故障;

事务处理例子: 假如A/B用户汇款事件,A汇款1000给B用户在数据库中对应了两条UPDATE语句一个-1000另外一个则加1000,需要将这两个操作属于一个事务;否则会出现A账号钱被扣了,然后B用户并未收到1000,这可能是由于网络环境和物理环境共同作用的结果;

事物操作使用InnoDB数据引擎的表支持事物操作，默认情况下MySQL开启了自动提交;

BEGIN:开启事务

ROLLBACK:回滚事务

COMMIT:提交一个事务

#查看自动提交功能是否开启
MySQL [Demo]> SHOW variables LIKE '%commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| autocommit                     | ON    |
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
2 rows in set (0.07 sec)

#关闭自动提交功能 ：0 或者 off;
MySQL [Demo]> SET autocommit=0;

基础示例:

-- 事务提交
MySQL [Demo]> begin; -- 打开一个事务
MySQL [Demo]> INSERT INTO user_log VALUE (NULL,'admin',now(),sha('123456'));
MySQL [Demo]> SELECT * FROM user_log
--  5 | admin     | 2020-02-10 12:06:47 | 7c4a8d09ca3762af61e59520943dc26494f8941
MySQL [Demo]> INSERT INTO user_log VALUE (NULL,'weiyigeek',now(),sha('123456'));
MySQL [Demo]> COMMIT;

-- 事务提交回滚-- 事务提交
MySQL [Demo]> begin;
MySQL [Demo]> INSERT INTO user_log VALUE (NULL,'admin',now(),sha('123456'));
MySQL [Demo]> ROLLBACK; --回滚操作:将插入的数据进行取消;
MySQL [Demo]> COMMIT;

注意事项:

• 1.在进行大数据量导入或者有很多的单行导入的时候，需要将自动提交关闭以提高效率性能;