书名：MySQL数据库应用实战教程（慕课版）pdf/doc/txt格式电子书下载

推荐语：

作者：刘刚,苑超影

出版社：人民邮电出版社

出版时间：2019-07-01

书籍编号：30512948

ISBN：9787115489005

正文语种：中文

字数：396522

版次：1

所属分类：教材教辅-中职/高职

版权信息

书名：MySQL数据库应用实战教程（慕课版）

作者：刘刚　苑超影

出版社：人民邮电出版社

出版日期：2019-07-01

ISBN：9787115489005

版权所有 · 侵权必究

前言

Foreword

MySQL 数据库是当前最主流的关系型数据库之一，由瑞典MySQL AB公司开发，目前属于Oracle公司。它免费、开源、体积小、速度快，功能齐全，使用便捷，可运行于Windows和Linux操作系统中，搭配PHP和Apache可组成良好的开发环境。因此，一般中小型网站的开发常选择MySQL作为网站数据库。

本书通过92个实战演练，详细讲解了MySQL数据库的知识和使用方法，配套慕课资源，由一线程序员小刚老师详细讲解，手把手教学。慕课资源可扫描书中二维码移动观看，也可登录人邮学院进行系统化学习。

小刚老师简介

一线项目研发、设计、管理工程师，高级项目管理师、项目监理师，负责纪检监察廉政监督监管平台、国家邮政局项目、政务大数据等多个国家级项目的设计与开发

极客学院、北风网金牌讲师

畅销书《微信小程序开发图解案例教程（附精讲视频）》《小程序实战视频课：微信小程序开发全案精讲》《Axure RP8原型设计图解微课视频教程 （Web+App）》作者

全部案例源代码、素材、最终文件、电子教案可登录人邮教育社区（www.ryjiaoyu.com.cn）下载使用。

编著者

2019年4月

第1章 MySQL概述

本章要点

MySQL的发展历史 ■

了解关系数据库 ■

MySQL的工作原理 ■

MySQL数据库存储引擎 ■

实战：安装MySQL数据库 ■

小结 ■

■ MySQL是采用客户端/服务器的关系型SQL数据库管理系统，它具有跨平台性和可移植性，可以轻松、简单地运行在多种操作系统上，如Windows、Linux操作系统等。本章介绍MySQL的基础知识，并带大家安装MySQL数据库。

MySQL数据库是开放源码的，允许有兴趣的爱好者去查看和维护源码，大公司或者有能力的公司还可以继续对其进行优化，做成适合自己公司的数据库。最重要的一点是，相较于Oracle数据库的商用收费， MySQL允许各大公司免费使用，并且在被甲骨文公司收购后，不断地进行优化，性能提升接近30%，已成为小公司或者创业型公司首选的数据库，市场占有率也逐渐扩大，如图1.1所示。

图1.1 2017年11月数据库占比

1.1 MySQL的发展历史

精讲视频

MySQL的发展历史

MySQL最初是由一个名叫蒙蒂·维德纽斯（Monty Widenius）的小职员为一个叫Tc X的公司设计的一款底层面向报表的存储引擎工具——Unireg。

在1985年，Monty和几个志同道合的朋友在瑞典成立了一家公司，也就是MySQL AB的前身。该公司最初也不是致力于做数据库产品的，只是因为工作过程中需要一个数据库，可是又没有合适的数据库选择，就决定自己开发一个数据库。为了满足瑞典的一些大型零售商不断增长的数据服务需求，并为复杂的系统提供数据仓库服务，他们自己设计了一个索引顺序存取数据算法，开发了高查询性能的数据引擎，也就是ISAM数据存储引擎。

1990年，有些用户要求提供SQL支持，于是Monty想将m SQL（mini SQL，即单用户数据库管理系统）代码集成到ISAM存储引擎中，但效果并不好。于是，Monty毅然决定自己重写一个SQL支持。

1996年，MySQL 1.0正式发布，提供的功能非常简单，只有表数据的INSERT（插入）、UPDATE （更新）、DELETE（删除）和SELECT（查询）操作。不过，它采用的许可策略却与众不同，允许免费商用，前提是不能捆绑MySQL一起发布，这为它的后续发展打下了良好的基础。随着MySQL 3.11.1版本的发布，MySQL不仅提供基本的SQL支持，还提供了复杂的查询优化器，尽管如此，MySQL依旧不支持事务、视图、存储过程等特性。

1999～2000年，Monty团队成立了MySQL AB公司，与Sleepcat合作开发了Berkeley DB引擎， MySQL从此支持事务处理。

2000年，MySQL公布了源码，并采用了通用公共许可协议（GNU General Public License，GPL）；4月，MySQL对旧的存储引擎进行了整理，命名为MyISAM，同时支持全文搜索。

2001年，Heikki Tuuri提议集成自己的InnoDB引擎到MySQL中，这个存储引擎不仅支持行级锁，而且还支持事务处理，MySQL和InnoDB正式结合版本为4.0。

2004年，MySQL发了4.1版本，新增了子查询。

2005年10月，MySQL发布了5.0版本，新版本加入了存储过程、触发器、视图等，MySQL逐渐向高性能数据库方向发展。

2008年1月，MySQL被Sun公司收购。

2009年4月，Oracle公司收购了Sun公司，MySQL转入Oracle旗下。

2010年，MySQL 5.5发布，新特性包括半同步的复制以及对SIGNAL/RESIGNAL异常处理功能的支持，同时InnoDB引擎变为MySQL的默认存储引擎，还加强了在企业应用方面的特性。令人庆幸的是， Oracle承诺MySQL 5.5和未来的版本仍然是采用GPL协议的开源数据库。

在 MySQL 5.5 发布两年后，Oracle 宣布 MySQL 5.6 正式版发布，首个正式版版本号为 5.6.10。在MySQL 5.5 中使用的是 InnoDB 作为默认的存储引擎，而MySQL 5.6则对InnoDB 引擎进行了改造，提供全文索引能力，使 InnoDB 适合各种应用场景。

2015 年，MySQL 5.7 GA 重磅发布，新特性包括160 万只读 QPS，动态修改 Buffer Pool，提高运维效率，优化器 Server 层改进、InnoDB 层优化等。

注意

自MySQL被收购后，Monty Widenius觉得依靠Sun/Oracle来发展MySQL存在问题，于是决定另起分支，于2009年成立了Maria DB项目。

1.2 了解关系数据库

精讲视频

了解关系数据库

MySQL作为最流行的关系型数据库管理系统之一，具备了容易理解、使用方便、易于维护等特性。那么，什么是关系数据库管理系统？什么是关系模型？关系型数据库有哪些优点呢？

1.2.1 什么是关系数据库

数据库（Database）是以特定数据结构组织，在计算机设备上存储和管理数据的“仓库”。在日常生活和工作中，有许多数据集，需要将它们归档到这样一个仓库中，以便进行数据统计和查询等相关管理。数据库有很多类型，通常根据不同数据组织类型分为层次式数据库、网络式数据库、关系数据库和面向对象的数据库4种，下面主要讲解关系数据库。

说明

层次结构数据库是通过一种有根节点的定向有序树结构（类似于一个倒挂的树）模型建立的数据库，如IMS（Information Management System）；网络式数据库是按照网状数据结构建立的数据库管理系统，记录中允许多层次记录关系；面向对象的数据库是一种新的程序设计方法，将世界中的一切事物都视为对象，整个世界都由相互关联的对象组织而成，如果感兴趣可以查询相关资料。

关系数据库管理系统（Relational Database Management System， RDBMS），是指通过关系模型来组织数据的数据库。关系数据库把世界看成由实体和联系组成。关系模型最初在1970年由IBM的研究员E.F.Codd博士提出，在之后的几十年中，关系模型的概念得到了很好的发展，并且逐渐成为主流数据库架构模型。可以简单地把关系数据库理解为由二维表格建立的数据组织（类似Excel，由行和列组织数据）和二维表格之间的联系构成的数据关联。下面详细解释下关系模型。

1.2.2 关系模型

关系模型是用二维表的形式表示实体以及实体之间联系的数据模型。数据都是以表格的形式存在的，每行对应一个实体的记录，每列对应实体的某种属性，若干行和列构成了整个表数据。

注意

所谓实体就是现实世界中客观存在的，可以是有形的、无形的、具体的或者抽象的事物。

说到实体，不得不说实体关系模型。实体关系模型是能直观表示实体、属性以及和实体间联系的模型，可以通过实体关系图（Entity Relationship Diagram， E-R图）来表示，是用来理解现实生活中的实体关系、建立概念模型非常有效的工具。

例如，某个社交网站和用户之间的关系如图1.2所示。

图1.2 社交网站用户关系图

在图1.2的E-R图中，社交网站和用户代表实体，用矩形表示，社交网站拥有属性：企业性质、网站名、上市，用椭圆形表示；用户拥有属性：姓名、手机号、生日，也用椭圆表示；实体社交网站和实体用户之间的联系，用菱形表示。在实体关系模型中，联系（也称关系）有3种类型：一对一关系（1:1），如用户有一个会员编号，一个会员编号能确定唯一一个用户，他们之间是一一对应的；一对多关系（1:N），如用户可以注册多个登录账号，而注册账号只能被当前的用户使用；多对多关系（N:N），如社交网站可以有多个用户，而用户同样可以拥有多个社交网站。

1.2.3 常用的关系型数据库

比较常用的关系型数据库有Oracle、MySQL、SQL Server、 IBM DB2、Sybase、Access等，Oracle是收费商用的数据库，提供很好的维护与支持，适用于业务逻辑较复杂、数据量大的大中型项目；MySQL数据库由于体积小、速度快、总体拥有成本低、开放源码，受到很多中小型公司的青睐；SQL Server数据库的功能比较全面、效率高，适用于中型企业或单位的数据库平台。

1.2.4 SQL结构化查询语言

SQL（Structured Query Language）意为结构化查询语言，是一种用于数据库查询和程序设计的语言，可用于操作数据库的查询、存取更新数据以及管理数据库，是通用的数据库查询语言，它具有极大的灵活性和很多强大的功能。虽然SQL是国际标准的关系型数据库管理语言，但事实上，很多流行的数据库都对SQL规范做了些修改扩充，所以，不同的数据库之间不能完全地互通，如MySQL没有top命令，SQL Server没有limit命令等。

1.3 MySQL的工作原理

精讲视频

MySQL简介

了解了关系数据库的基础知识，下面我们开始讲解MySQL。

为了理解MySQL的工作原理，我们先看一张经典的架构图，如图1.3所示。

MySQL的内部架构由以下几个部分组成。

（1）编程语言交互接口（Connectors）：指的是不同语言与SQL的交互接口，如Java的JDBC、.Netframework的ODBC。

（2）系统管理和控制工具集合（Management Services & Utilities）：提供管理配置服务、备份还原、安全复制等功能。

图1.3 MySQL的内部架构

（3）连接池（Connection Pool） ：接受客户端的请求，缓存请求，检查内存可利用情况，如果没有可用线程，就创建线程执行任务，有可用线程就重复利用。

（4）解析器（Parser）：解析验证SQL语法，分解SQL成相应的数据结构，以备后面处理。

（5）查询优化器（Optimizer）：对SQL语句进行优化处理，优化执行路径，生成执行树，最终数据库会选择认为最优的方案执行并返回结果。

（6）SQL接口（SQL Interface）：接受用户的SQL命令，并返回结果。

（7）查询缓存（Cache & Buffer）：缓存查询结果。如果SQL查询中命中查询结果，将直接从缓存中返回结果，不再执行SQL分析等操作；没有命中，才会进行后续的解析、查询优化、执行SQL接口，返回结果，同时将结果加入缓存中。

（8）存储引擎（Pluggable Storage Engines）：是MySQL中具体的与文件打交道的子系统，可以看到它是以插件形式存在的，意味着可以自定义存储引擎，这是MySQL很特别的地方。MySQL提供了很多存储引擎，其优势各不一样，有的查询效率高、有的支持事务等，最常用的有MyISAM、InnoDB、BDB。

（9）文件系统（File System）：是存放数据库表数据以及相关配置的地方。

下面举例来说明MySQL的查询过程，如用户要查询具体用户的详情（SELECT * FROM T_USER WHERE ID =‘ID’）。

（1）客户端先发送这条查询命令给MySQL服务器。

（2）服务器会先检查缓存，如果缓存命中，则立即返回缓存中的数据；否则，服务器进行SQL解析、预处理，再通过优化器生成执行计划。

（3）服务器根据生成的执行计划，调用对应引擎的API来执行查询。

（4）将结果返回客户端。

1.4 MySQL数据库存储引擎

精讲视频

MySQL数据库存储引擎

MySQL中的数据可以采用不同的技术存储在文件（或内存）中。这些技术都使用不同的存储机制、索引技巧、锁定水平，并且最终提供广泛的、不同的功能和能力。通过选择不同的技术，我们能够获得额外的速度或者功能，从而改善应用的整体功能，每种技术以及配套的相关功能就可以看成是一种数据库存储引擎，MySQL默认配置了许多不同的存储引擎，这些存储引擎可以预先设置或者在MySQL服务器中启用。

例如，银行转账交易需要一个支持事务处理的数据库，以确保事务处理不成功时数据的回退能力，这时就不能选用MyISAM存储引擎，因为它是非事务性存储引擎，可以采用InnoDB引擎，这样才能保证银行转账正常进行。

MySQL自身提供的存储引擎有InnoDB（5.5版本后为默认存储引擎）、MyISAM、MEMORY、CSV、ARCHIVE、BLACKHOLE等。

1.4.1 InnoDB引擎

作为默认存储引擎，InnoDB具备以下主要优势。

（1）数据操纵语言（Data Manipulation Language，DML）操作遵循事务的4个特性——原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability），并通过commit、rollback、crash-recovery保障数据的安全。具体来说，crash-recovery就是指如果服务器因为硬件或软件问题而崩溃，不管当时数据是怎样的状态，在重启MySQL后，InnoDB会自动恢复到发生崩溃之前的状态，并回到用户离开的地方。另外，如果数据在磁盘或者内存中损坏，校验机制会提醒当前数据为虚假数据。

（2）具有行级锁和Oracle风格的读一致性，通过一种更改缓存机制对新增、更新和删除进行优化，增加了对用户并发读写操作和性能。

（3）对表进行基于主键的优化查询，每张表都有一个基于主键的聚集索引，以此达到减少磁盘I/O，进而提高了搜索效率和性能。

（4）支持外键约束，检查外键、插入、更新和删除，以确保数据的完整性。

（5）InnoDB引擎提供了专门的缓存池，在内存中缓存了表和索引的数据，常用的数据可以直接从内存中处理，比直接从磁盘获取数据处理速度快。

（6）可以压缩表和相关索引，创建和删除索引以达到提高性能的目的。

（7）快速压缩表空间，并能释放磁盘空间，保证系统能够重用，而不仅仅是腾出空间给InnoDB复用。

（8）创建表时可以通过语句显示指定表引擎：

CREATE TABLE TABLE_NAME (I INT) ENGINE = INNODB;

1.4.2 MyISAM引擎

MyISAM引擎不支持事务、外键，不过访问速度非常快，表的存储分为以下3个文件。

（1）frm文件：存储表定义。

（2）MYD（MYData）文件：存储数据。

（3）MYI（MYIndex）文件：存储索引。

它有以下特点。

（1）所有数值类型键值都是以高字节存储的，以便于更好地索引压缩。

（2）每张MyISAM表最多支持（232）2（1.844E+19）行。

（3）每张MyISAM表支持的最大索引数是64，每个索引最多16列。

（4）当表字段是通过自增长（auto_increment）的，索引树节点只会包含一个键，这样可以提高索引的空间利用率。

（5）在INSERT、UPDATE时，MyISAM有内部处理会自动更新auto_increment字段，这使auto_increment处理更快（至少10%）。

（6）当进行混合操作（删除、更新、插入同时进行）时，MyISAM通过自动合并和扩展删除块，减少了行碎片。

（7）MyISAM支持并发插入数据：如果一个表中的数据文件中没有空闲块，则可以在插入数据的同时通过其他线程读取表数据。空闲块是由删除或更新操作时数据长度超过当前行内容长度引起的。

（8）可以通过将数据文件（MYD）和索引文件（MYI）放在不同物理设备的不同目录上，来更快地创建表。

（9）BLOB和TEXT可以被索引，索引列中允许NULL值，不过需要占0～1个字节。

（10）创建表时可以通过语句显示指定表引擎：

CREATE TABLE TABLE_NAME (I INT) ENGINE = MYISAM;

1.4.3 MEMORY引擎

MEMORY引擎又称为HEAP引擎，用来创建特殊用途的表，且内容存储在内存中。将数据存储在内存中，能够实现快速访问和低延迟。

因此，使用MEMORY引擎，在出现数据崩溃、硬件故障等问题时，数据极易丢失；它适合临时态和非关键数据（如会管理或缓存等）的操作。

创建表时可以通过语句显示指定表引擎：

CREATE TABLE TABLE_NAME (I INT) ENGINE = MEMORY;

1.4.4 CSV引擎

当用户创建一个CSV引擎的表时，服务器会在数据库目录中创建一个“表名.frm”的格式文件，同时还会创建一个“表名.csv”的数据文件，该数据文件中的数据是以逗号分隔保存的，它主要用于CSV报表格式的数据存储，应用面比较窄。

缺点：CSV引擎不支持索引，也不支持分区，并且所有列必须指明为NOT NULL。

创建表时可以通过语句显示指定表引擎：

CREATE TABLE TABLE_NAME (I INT) ENGINE = CSV;

1.4.5 ARCHIVE引擎

ARCHIVE引擎用于数据归档，它的压缩比例非常高，适合存储历史数据（前提是不做查询操作），所占的存储空间不到InnoDB引擎的1/10；它支持行级锁实现并发插入操作，却不支持事务，其设计目的在于提供高速插入和压缩功能；另外，它不支持索引。

创建表时可以通过语句显示指定表引擎：

CREATE TABLE TABLE_NAME (I INT) ENGINE = ARCHIVE;

1.4.6 BLACKHOLE引擎

BLACKHOLE引擎是很特别的一种引擎，它的表不存储任何数据，就像是“黑洞”一样。它主要用于充当伪服务器、日志服务器、增量备份服务器等。

创建表时可以通过语句显示指定表引擎：

CREATE TABLE TABLE_NAME (I INT) ENGINE = ARCHIVE;

MySQL还支持其他引擎：MERGE引擎、FEDERATED引擎、EXAMPLE引擎等，可以查看相关文档。

1.4.7 存储引擎特点对比

MySQL在5.5版本之后默认的存储引擎是InnoDB存储引擎，下面对比一下MySQL 5.7版本存储引擎的特点，如表1.1所示。

表1.1 MySQL 5.7版本存储引擎的特点

选择存储引擎的建议如下。

（1）MySQL的存储引擎很多，不同的库、不同的表可以选择不同的存储引擎，推荐同一个库用同一种存储引擎，因为不同存储引擎的表之间join操作比较慢。

（2）InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全表，如果需要事务处理、ACID事务支持，则选择InnoDB存储引擎。

（3）Memory将所有数据保存在缓存RAM中，可以提供极快的访问速度。

（4）尽量不要选择MyISAM存储引擎，它只能用单个CPU，内存只能用到4GB，内存里只有索引，而且并发能力差。

1.5 实战：安装MySQL数据库

精讲视频

实战：安装MySQL数据库

首先，通过官网下载MySQL，如图1.4所示。

可以选择Windows、Linux、Mac OS等不同的操作系统以及32位或64位系统，和不同版本进行下载，这里使用的是Windows 7系统。本书以Windows MySQL 5.7.20版本为例，后面的所有例子也是基于这个版本。

图1.4 MySQL下载页面

1.5.1 在Windows环境下安装MySQL

（1）从官网下载mysql-5.7.20-winx64 （即Windows 64位系统）版本，将下载的压缩文件放到目标安装目录中，如D:\\Program Files\\MySQL，开始安装免安装5.7.20版本的MySQL。

（2）如果计算机中有以前的MySQL版本，如果是安装版的，则可以在控制面板中卸载，还需要删除以前MySQL留下来的残留文件；如果是免安装版本，则用mysqld -remove命令删除即可。

（3）解压安装包到当前目标安装目录，解压后的目录如D:\\Program Files\\MySQL\\mysql-5.7.20-winx64。

（4）配置环境变量，选择“我的电脑（计算机）”→“属性”→“高级系统设置”→“环境变量”命令，如图1.5所示 ，打开“环境变量”窗口。

（5）在弹出的“环境变量”窗口中找到Path，单击“编辑”按钮，添加MySQL文件路径到系统变量Path中，在Path变量值中追加“; D:\\Program Files\\MySQL\\mysql-5.7.20-winx64\\bin”，分号不能少，它是用于分割目录的，如图1.6所示。

图1.5 打开“环境变量”窗口

图1.6 添加MySQL路径到系统变量中

（6）添加配置文件，在D:\\Program Files\\MySQL\\mysql-5.7.20-winx64目录下面新建一个记事本文件，修改成my.ini文件格式，打开my.ini文件，添加如下配置。

[client]
port=3306
[mysql]
default-character-set=utf8
[mysqld]
port=3306
basedir= D:\\Program Files\\MySQL\\mysql-5.7.20-winx64
datadir= D:\\Program Files\\MySQL\\mysql-5.7.20-winx64\\data
character-set-server=utf8
default-storage-engine=innodb
max_connections=512
innodb_flush_log_at_trx_commit=0
innodb_log_file_size　　　　　 =1G
innodb_log_files_in_group　　　=3
innodb_max_dirty_pages_pct　 =50
innodb_lock_wait_timeout　　　=120
sync_binlog=1000
innodb_log_buffer_size=16M
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_purge_threads = 8
innodb_page_cleaners = 8
innodb_open_files = 65535
innodb_buffer_pool_size=8G
#DISTINCT关键字经常在MySQL中使用，在MySQL5.7以前的版本中一般没有什么问题，但是在5.7以后的版本中会遇到这样的错误
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
max_connect_errors = 1000
#数据库隔离级别
transaction_isolation = READ-COMMITTED
#MySQL在完成某些join（连接）需求的时候，为了减少参与join的“被驱动表”的读取次数以提高性能
join_buffer_size = 128M
tmp_table_size = 64M
#该值设置过小将导致单个记录超过限制后

....