<commit_before># 第七章 MySQL数据库的设计与查询

古人有云：“路漫漫其修远兮，吾将上下而求索”。这句话形容数据库的学习最为贴切，因为数据库开发的学习和编程语言的学习有着重要的区别，对于编程语言而言，只要软件稍有规模，例如大几千行或者上万行时，就可以通过不断的实验各种先进的编程思想和体系结构对软件系统进行不断的重构，从而使软件更具可读性和可扩展性，但是数据库则不同，在不同的用户环境下，相同的数据库设计可能带来截然不同的结果，例如在只有几十人或上百人使用的系统中，对于目前的数据库系统来说不需要任何的优化就可以实现很好的性能，但是对于上万人或几十万用户的系统来说，如果不对数据库系统进行任何的优化，那么将很难得到想要的性能，从而大幅降低系统的用户体验，而对于向双十一、春运等这类高并发应用来说，除了对现有数据库系统进行优化之外，可能还需要使用如分布式等更高级的数据库技术才能很好的实现用户体验。因此数据库的学习从来都不是一蹴而就，也不是通过看几本书就可以学会的，而是需要在有一定基础的情况下通过长时间的不断实践才能完成。

目前市场上常用的数据库主要包含IBM的DB2、甲骨文的Oracle、微软的SQL Server、加州大学伯克利分校计算机系开发的PostgreSQL，以及被Oracle收购的MySQL，其中DB2、Oracle、SQL Server都是商用软件，需要支付较高的费用，但也提供良好的服务。而PostgreSQL和MySQL都属于开源软件，同时其开源协议决定了这两个数据库软件都可以免费进行商用，虽然从性能上来说PostgreSQL和MySQL都兼顾了高性能和高稳定性，甚至在某些对稳定性要求非常高的应用中（如微信支付），PostgreSQL还略胜一筹，但是由于历史中的诸多巧合，以及MySQL迎合了市场快速开发的需求，因此MySQL成为目前中小型企业系统开发的首选。本章也将以MySQL为出发点，讲述MySQL中的各种常见的使用方法，从帮助读者从整体上了解MySQL的使用方法，为后续的进一步实践搭建良好的基础。

![MySQL-Logo](Screenshot/MySQL-Logo.png)

图7-1 MySQL的Logo

#### 本章内容

> * 数据存储的发展和技术构成
> * MySQL的数据类型与数据库、数据表的操作
> * 常用查询语句的使用
> * 数据项的插入、更新、删除，以及函数使用
> * 深入了解触发器和存储过程的使用

## 7.1 数据存储的发展和技术构成

对于普通用户来说，几乎平时接触不到数据库，以及相关概念，但是普通用户却会在电脑中经常保存一些重要的资料或者文档，随着电脑使用的时间越来越长，各种资料和文档也就越来越多，那么此时如果想要查找其中的某一个文档就显得十分困难，一台个人电脑尚且如此，如果是要在几十人或者上百人的人电脑里查找文件，那就像大海捞针一般困难。数据库的出现就是为了解决这个问题，提高数据检索的速度，提高整个系统的运行效率。本节将从应用角度介绍数据存储的发展，以及数据库的必要性，同时介绍构成数据库的几个最重要的技术。

### 7.1.1 数据存储的发展

在计算机出现以前，数据存储主要以文字的方式纪录在各种简单的物理介质中，如纸张、龟壳、竹简等。但是，随着整个社会的不断发展，要存储的数据越来越多，而这些采用传统保存数据保存下来的信息不仅容量有限，而且极易丢失和损坏，显然已经不能应对社会发展的要求。于是在1884年美国统计学家赫尔曼·何乐礼（制表机器公司创始人，IBM前身）根据1725年法国纺织专家巴索·魯修发明的用于保存印染布图案的卡片发明了“打孔卡片制表机”。通过在纸带上打上很小的圆孔来确认数据是0还是1，从而实现了数据的存储。虽然穿孔纸带所能存储的数据与今天相比小了很多，但是对于当时的存储方式已经有了质的飞跃，如图7-2所示。

![aperture-card](Screenshot/aperture-card.png)

图7-2 穿孔卡片

到了20世纪50年代，IBM公司第一次把盘式磁带用于数据存储，此时的一盘盘式磁带所携带的信息量是当时打孔卡片的1万倍，大大提高的存储容量。随后就出现了存储容量更大的磁鼓、软盘、硬盘和光盘，这些存储介质所能存储的数据甚至超越了全世界图书馆中存储文献之和。但是，随着信息存储内容的越来越多，数据的管理却越来越不方便，特别是对数据的移植、检索、修改、删除、增加等操作都已经无法靠人力来完成。因此，从20世纪50年代到60年代开始出现了各种数据处理系统，这些各式各样的系统其目的就是在把计算机中所存储的数据进行有机的组织，通过简单的文件名对文件进行检索、存、取操作，再利用存储器读写设备对存储在系统中数据进行修改，如图7-3所示，Linux的文件系统结构。

![Linux-FileSystem](Screenshot/Linux-FileSystem.png)

图7-3 Linux的文件系统结构

当时的文件系统已经和现在文件系统相类似，能够满足基本对数据操作的要求，但是要在这些数据中精确的找出某一个想要的数据还是相当困难。特别是随着计算机CPU的不断发展，其计算速度也在不断提高，相应的对于数据处理速度的要求和处理数据的规模都在不断提高，而此时的文件系统已经不能够满足这一要求。因此，在20世纪60年代后期，由于美国阿波罗计划的运行，需要进行大量航空数据的存储在计算机内部，于是IBM公司就着手开展对于“大型共享数据库的关系模型”的研究，由此出现了数据库（即Database）和关系模型的概念，并且数据库的雏形也逐渐显现，由此诞生了后续影响全世界的MySQL、Oracle等众多优秀的关系型数据库，如图7-4所示。虽然当时的数据库还只是雏形，但是其设计理念已经与文件系统大不相同，它不再针对某一个特定数据或者应用，而是针对所有的数据类型，全面的对各类数据进行组织和整理，使得数据更加具有结构性，从而减少数据的冗余，提高数据的复用性。同时，把后台数据和前台程序进行分离，使得数据库系统能够更加专注于数据的组织和管理，从而提高对数据进行增、删、改、查的效率，而开发人员不需要关心数据如何存储，只需要通过简单接口实现数据的访问即可，从而省去了原先复杂的业务逻辑带来前端应用和后端数据的高耦合性，影响了整个系统的移植性和高效性。

### 7.1.2 数据库范式与技术构成

自数据库诞生以来就伴随着各种技术的发展，不论是数据库的实现方式，还是数据库的应用领域都在变化，但是其中最核心的却是两个部分，一个是建立数据库的规则，即范式，另一个则是建立数据库所需的相关技术，如数据库引擎、SQL、数据库访问技术等。

**1、数据库范式的基本概念**

1970年，由美国著名的科学哲学家托马斯·库恩在其出版的专著《科学革命的结构》中提出了“范式”的概念，这个概念后来运用于多个领域，例如社会科学、计算机科学等。所谓范式，就是指常规科学赖以运作的理论基础和实践规范，是从事某一科学领域的研究者群体所共同遵从的世界观和行为方式，是一种公认的模型或者模式。同年，英国科学家埃德加·弗兰克·科德根据这一概念，提出了关系数据库模型，并对关系数据库的设计进行总结，从而提出了关系型数据库理论的理论基础，以及在设计数据库时必须要遵循的规则，即数据库范式。

目前数据库范式一共有八种，分别是第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第五范式（5NF）、DK范式（DKNF）、第六范式（6NF）。通常情况下，使用最多的就是1NF、2NF和3NF，下面对这三种范式进行详细说明。

1、第一范式（1NF）：所谓第一范式就是数据表中的每一个字段都是不可分割的原子。例如数据表中存储了电话字段，并且这个电话包括了公司电话和家庭电话，那么这个数据表就不符合第一范式，需要把电话字段再分为公司电话和家庭电话。需要注意的是，数据库设计只有满足了第一范式才能称为关系型数据库，否则就不能称为关系型数据库。

2、第二范式（2NF）：所谓第二范式，首先必须满足第一范式，然后为每张数据表添加一个主键，同时数据表中每个字段都依赖于主键。例如，对于主键为订单ID的数据表【订单详情】（订单ID，产品ID，单价，折扣，数量，产品名称）来说，虽然产品ID和订单ID共同构成了一个唯一的订单，但是单价，折扣，数量，产品名称等信息并不依赖于订单ID，而是依赖于产品ID。因此，这样的设计就不符合第二范式，而是应该把单价，折扣，数量，产品名称等信息单独列出一张表，通过产品ID作为主键和外键的方式进行关联。

3、第三范式（3NF）：所谓第三范式，首先必须满足第二范式，然后每张表中所有非主键字段都依赖于主键，并且不存在非主键字段依赖于非主键字段的问题。例如，对于主键为订单ID的数据表【订单表】（订单ID，下单日期，客户ID，客户名称，客户地址，客户所在城市）来说，虽然客户ID确实依赖于订单ID，从而实现客户名称、客户地址等信息依赖于订单ID，但是客户名称、客户地址等信息却不是直接依赖于主键订单ID，而是通过客户ID进行依赖传递，这样的数据表就不符合第三范式，而是应该把客户ID、客户名称、客户地址等信息单独拉成一张表，通过把客户ID作为主键和外键的方式进行两张表的关联。

**2、数据库存储引擎技术**

由于MySQL数据库是开源数据库，虽然背后有企业进行支撑，但主要还是由本地开发人员进行维护，而SQL Server和Oracle数据库由于是纯商业数据库，用户需要支付一定的费用才能获取软件产品，以及相应的服务，所以SQL Server和Oracle数据库的维护通常由对应的企业来完成，也因为这个原因MySQL中对于存储引擎的使用采用更加开放的态度，即开发人员可以根据项目的实际要求选择多个存储引擎，可以是某一个数据库的存储引擎，也可以是某一个数据表的存储引擎。所谓存储引擎，其主要作用是把各种数据通过不同的技术存储到文件或者内存中。目前MySQL中的存储引擎主要有三种，分别是InnoDB存储引擎、MyISAM存储引擎，以及Memory存储引擎，具体如表7-1所示。

表7-1 存储引擎的比较

| 名称 | 优点 | 缺点 |
| :-: | :- | :- |
| InnoDB存储引擎 | 支持事务处理和外键支持 | 运行速度慢，并且占用资源高 |
| MyISAM存储引擎 | 支持数据索引，以及高并发的读写操作 | 不支持事务操作 |
| Memory存储引擎 | 内存存储，性能优异 | 数据稳定性不高，并且掉电就会丢失所有数据 |


**3、数据库脚本技术SQL**

SQL是应用于数据库中的编程语言，与面向对象和面向过程的语言相比，这种语言的特点就是结构化。所谓结构化语言，其特点在于通过既定的规则或结构来实现对数据库操作，类似于数学公式的套用。SQL语句最早由IBM公司在上世纪70年代进行研发，用于最早的关系型数据库System R中进行数据的操作。到了80年代，美国国家标准局（ANSI）、国际标准化组织（ISO）先后发布了数据库语言美国标准和国际标准，也标志这SQL正式称为一个通用的数据库操作语言。SQL语句主要包含五个部分，分别是数据查询语句、数据操作语句、事务处理语句、数据定义语句、指针控制语句，具体如下：

1、数据查询语句：通过SELECT、WHERE、ORDER BY、GROUP BY和HAVING这些关键字来实现对数据库中数据的查询。

2、数据操作语句：通过INSERT、UPDATE和DELETE这些关键字来实现对数据库中数据的插入、升级和删除。

3、事务处理语句：通过BEGIN TRANSACTION、COMMIT和ROLLBACK这些关键字来实现对数据库中事件的处理。

4、数据定义语句：通过CREATE和DROP关键字实现数据库中数据表的创建和删除，以及索引等内容的加入。

5、指针控制语句：通过DECLARE CURSOR、FETCH INTO和UPDATE WHERE CURRENT来实现对一个或多个数据表的单独操作。

此外，SQL语言还提供了五种数据类型，分别是字符型（char或varchar）、文本型（text）、数值型（int、numeric、money）、布尔型（bit），以及日期型（datetime、smalldatetime）。其中字符型用于存放最长255个字符的字符串，文本型用于存放超长文本，数值型用于存放整型、浮点型、钱币类型，布尔型用于存放0或1的逻辑值，而日期型则用于存放日期数据类型。

**4、数据库访问库**

在应用系统中，除了需要有存储数据所需的数据库，还需要有访问数据库数据的访问接口，而针对不同的编程语言，其背后的支持公司都提供了不同的数据库访问库。目前主流的编程语言有五种，其中具有完整开发环境和商业公司支持的有微软的.Net技术和Oracle的Java技术，此外还有轻量级的PHP技术、Python技术，以及Node.js技术，其数据库访问库也有相应的五种，分别是：

1、.Net的ADO.NET组件：ADO.NET是微软针对.Net技术提供的访问各类关系型数据库的第三方软件库，默认情况下可以访问微软的SQL Server，而如果需要访问其他数据库，则需要下载对应的数据库驱动程序，例如要访问MySQL数据库，就需要在MySQL官网下载数据库驱动程序Connector/ODBC。

2、Java的JDBC组件：JDBC是Oracle提供访问关系型数据库的标准Java API程序库，但是由于该接口没有和任何数据库进行绑定，因此除了在程序中引入该库之外，还需要下载并安装对应数据库的驱动程序，例如访问MySQL就需要下载Connector/J。

3、PHP的PDO组件：PDO（PHP Database Object）插件是PHP中面向对象数据库组件，底层提供了几乎所有数据库的访问支持，而对于上层则采用统一的API进行访问和操作，也就是说开发人员只需要会使用PDO访问一种数据库，那么其他数据库也都会进行访问和操作。此外，由于和PHP联系最紧密的数据库就是MySQL，因此PHP还提供了针对MySQL的链接库MySQLi，通过该库可以实现对MySQL数据库的任意操作，但是缺点是该库只能应用于MySQL，而不能用于其他数据库。

4、Python的DB-API：DB-API与JDBC类似，提供了Python访问数据库标准接口，但是这些接口没有和特定的数据库进行绑定。因此，除了引入该API模块之外，还需要下载对应数据库的Python模块。

5、Node.js的数据库驱动：Node.js没有像其他语言一样提供统一的访问接口，而是需要根据不同的数据库下载并安装不同的操作库。

## 7.2 MySQL的数据类型与数据库、数据表的操作

为了更好的利用数据服务器中的资源，减少数据存储过程中无用数据的冗余，MySQL数据库对常用的数据类型进行抽象得到了MySQL中的四种数据类型，分别是数值型、时间型、字符串型，以及二进制型，并且数据库开发人员需要在保存数据时指定相应的数据类型，从而使得MySQL数据库能对这些数据进行优化。此外，由于MySQL是关系型数据库，因此在使用该数据库时，数据表是整个数据库的基础，数据库就是由各种类型的数据表组合而成，并且通过设置表和表之间的关系，从而实现整个系统的逻辑顺序。本节将详细分析数据库的四种数据类型所应用的场合，以及如何通过指令实现数据库和数据表的操作。

### 7.2.1 MySQL中的的数据类型

MySQL中的数据类型可以分为四大类，分别是数值型、时间型、字符串型，以及二进制型，其中数值型用于存储各类整型和浮点型的数据，时间型用于存储日期和时间的数据，字符串型则用于保存文本型的数据，而二进制类型则通常用于保存一些多媒体文件，但是由于二进制文件相较于字符串、数值类型的数据来说体积相对较大，因此对于二进制文件的保存和检索都比较慢，所以保存二进制数据的常规做法并不是把数据保存在数据库中，而是采用字符串的形式保存二进制数据在本地的存储路径，然后通过读取数据库中二进制数据的路径来对这些信息进行存取。

**1、数值类型**

数值类型是MySQL中最基本的数据类型，主要包括整数型和浮点数型，其中整数型根据数据长度的不同又可以分为微整型（TINYINT）、小整型（SMALLINT）、中整型（MEDIUMINT）、整型（INT），以及超大整型（BIGINT），读者可以根据自己项目的需求选择不同的数据类型，从而减少保存数据时的资源浪费，具体如表7-2所示。

表7-2 整数型的数据类型

| 类型名 | 字节数 | 有符号的取值范围 | 无符号的取值范围 |
| :-: | :-: | :-: | :-: |
| 微整型（TINYINT） | 1字节 | 0 ~ 2^8 - 1 | -2^7 ~ 2^7 - 1 |
| 小整型（SMALLINT） | 2字节 | 0 ~ 2^16 - 1 | -2^15 ~ 2^15 - 1 |
| 中整型（MEDIUMINT） | 3字节 | 0 ~ 2^24 - 1 | -2^23 ~ 2^23 - 1 |
| 整型（INT） | 4字节 | 0 ~ 2^32 - 1 | -2^31 ~ 2^31 - 1 |
| 超大整型（BIGINT） | 8字节 | 0 ~ 2^64 - 1 | -2^63 ~ 2^63 - 1 |

除了整数类型外，MySQL还有浮点数据类型，即带小数的数据类型，包括单精度浮点型（FLOAT）、双精度浮点型（DOUBLE），以及定点浮点型（DECIMAL），其中单精度浮点型占用4字节，而双精度浮点型占用8字节。特别要说明的是定点浮点型，该数据类型由两个数据类型参数构成，分别是表示数据总长度的参数M和表示小数点位数的参数D。当传入数据的小数点位数比设定的位数长时，那么数据就会进行四舍五入操作，而当传入的数据位数比设定的位数小时，则全部保留。此外，由于定点浮点数在保存数据时，其本质是使用字符串对数据进行保存，因此在数据保存的准确性上会比单精度浮点型和双精度浮点型要高，也因为这个原因，当对于数据精度要求比较高时，通常都会采用定点浮点型进行保存。具体的数据类型的定义格式如下：

```sql
DECIMAL(M, D)
```

**2、时间类型**

为了方便进行时间信息的管理，MySQL提供了五种与时间相关的数据类型，分别是年份类型（YEAR）、日期类型（DATE）、时间类型（TIME）、日期时间类型（DATETIME），以及时间戳（TIMESTAMP），其中年份类型（YEAR）用于存放年份、日期类型（DATE）用于存放年月日、时间类型（TIME）用于存放时分秒、日期时间类型（DATETIME）可以同时存放年月日和时分秒，而时间戳（TIMESTAMP）与日期时间类型相似，但是时间戳特别之处在于具有时区之间转换的功能。



### 7.2.2 MySQL的时间类型



### 7.2.3 MySQL的字符串类型<commit_msg>完成时间类型<commit_after># 第七章 MySQL数据库的设计与查询

古人有云：“路漫漫其修远兮，吾将上下而求索”。这句话形容数据库的学习最为贴切，因为数据库开发的学习和编程语言的学习有着重要的区别，对于编程语言而言，只要软件稍有规模，例如大几千行或者上万行时，就可以通过不断的实验各种先进的编程思想和体系结构对软件系统进行不断的重构，从而使软件更具可读性和可扩展性，但是数据库则不同，在不同的用户环境下，相同的数据库设计可能带来截然不同的结果，例如在只有几十人或上百人使用的系统中，对于目前的数据库系统来说不需要任何的优化就可以实现很好的性能，但是对于上万人或几十万用户的系统来说，如果不对数据库系统进行任何的优化，那么将很难得到想要的性能，从而大幅降低系统的用户体验，而对于向双十一、春运等这类高并发应用来说，除了对现有数据库系统进行优化之外，可能还需要使用如分布式等更高级的数据库技术才能很好的实现用户体验。因此数据库的学习从来都不是一蹴而就，也不是通过看几本书就可以学会的，而是需要在有一定基础的情况下通过长时间的不断实践才能完成。

目前市场上常用的数据库主要包含IBM的DB2、甲骨文的Oracle、微软的SQL Server、加州大学伯克利分校计算机系开发的PostgreSQL，以及被Oracle收购的MySQL，其中DB2、Oracle、SQL Server都是商用软件，需要支付较高的费用，但也提供良好的服务。而PostgreSQL和MySQL都属于开源软件，同时其开源协议决定了这两个数据库软件都可以免费进行商用，虽然从性能上来说PostgreSQL和MySQL都兼顾了高性能和高稳定性，甚至在某些对稳定性要求非常高的应用中（如微信支付），PostgreSQL还略胜一筹，但是由于历史中的诸多巧合，以及MySQL迎合了市场快速开发的需求，因此MySQL成为目前中小型企业系统开发的首选。本章也将以MySQL为出发点，讲述MySQL中的各种常见的使用方法，从帮助读者从整体上了解MySQL的使用方法，为后续的进一步实践搭建良好的基础。

![MySQL-Logo](Screenshot/MySQL-Logo.png)

图7-1 MySQL的Logo

#### 本章内容

> * 数据存储的发展和技术构成
> * MySQL的数据类型与数据库、数据表的操作
> * 常用查询语句的使用
> * 数据项的插入、更新、删除，以及函数使用
> * 深入了解触发器和存储过程的使用

## 7.1 数据存储的发展和技术构成

对于普通用户来说，几乎平时接触不到数据库，以及相关概念，但是普通用户却会在电脑中经常保存一些重要的资料或者文档，随着电脑使用的时间越来越长，各种资料和文档也就越来越多，那么此时如果想要查找其中的某一个文档就显得十分困难，一台个人电脑尚且如此，如果是要在几十人或者上百人的人电脑里查找文件，那就像大海捞针一般困难。数据库的出现就是为了解决这个问题，提高数据检索的速度，提高整个系统的运行效率。本节将从应用角度介绍数据存储的发展，以及数据库的必要性，同时介绍构成数据库的几个最重要的技术。

### 7.1.1 数据存储的发展

在计算机出现以前，数据存储主要以文字的方式纪录在各种简单的物理介质中，如纸张、龟壳、竹简等。但是，随着整个社会的不断发展，要存储的数据越来越多，而这些采用传统保存数据保存下来的信息不仅容量有限，而且极易丢失和损坏，显然已经不能应对社会发展的要求。于是在1884年美国统计学家赫尔曼·何乐礼（制表机器公司创始人，IBM前身）根据1725年法国纺织专家巴索·魯修发明的用于保存印染布图案的卡片发明了“打孔卡片制表机”。通过在纸带上打上很小的圆孔来确认数据是0还是1，从而实现了数据的存储。虽然穿孔纸带所能存储的数据与今天相比小了很多，但是对于当时的存储方式已经有了质的飞跃，如图7-2所示。

![aperture-card](Screenshot/aperture-card.png)

图7-2 穿孔卡片

到了20世纪50年代，IBM公司第一次把盘式磁带用于数据存储，此时的一盘盘式磁带所携带的信息量是当时打孔卡片的1万倍，大大提高的存储容量。随后就出现了存储容量更大的磁鼓、软盘、硬盘和光盘，这些存储介质所能存储的数据甚至超越了全世界图书馆中存储文献之和。但是，随着信息存储内容的越来越多，数据的管理却越来越不方便，特别是对数据的移植、检索、修改、删除、增加等操作都已经无法靠人力来完成。因此，从20世纪50年代到60年代开始出现了各种数据处理系统，这些各式各样的系统其目的就是在把计算机中所存储的数据进行有机的组织，通过简单的文件名对文件进行检索、存、取操作，再利用存储器读写设备对存储在系统中数据进行修改，如图7-3所示，Linux的文件系统结构。

![Linux-FileSystem](Screenshot/Linux-FileSystem.png)

图7-3 Linux的文件系统结构

当时的文件系统已经和现在文件系统相类似，能够满足基本对数据操作的要求，但是要在这些数据中精确的找出某一个想要的数据还是相当困难。特别是随着计算机CPU的不断发展，其计算速度也在不断提高，相应的对于数据处理速度的要求和处理数据的规模都在不断提高，而此时的文件系统已经不能够满足这一要求。因此，在20世纪60年代后期，由于美国阿波罗计划的运行，需要进行大量航空数据的存储在计算机内部，于是IBM公司就着手开展对于“大型共享数据库的关系模型”的研究，由此出现了数据库（即Database）和关系模型的概念，并且数据库的雏形也逐渐显现，由此诞生了后续影响全世界的MySQL、Oracle等众多优秀的关系型数据库，如图7-4所示。虽然当时的数据库还只是雏形，但是其设计理念已经与文件系统大不相同，它不再针对某一个特定数据或者应用，而是针对所有的数据类型，全面的对各类数据进行组织和整理，使得数据更加具有结构性，从而减少数据的冗余，提高数据的复用性。同时，把后台数据和前台程序进行分离，使得数据库系统能够更加专注于数据的组织和管理，从而提高对数据进行增、删、改、查的效率，而开发人员不需要关心数据如何存储，只需要通过简单接口实现数据的访问即可，从而省去了原先复杂的业务逻辑带来前端应用和后端数据的高耦合性，影响了整个系统的移植性和高效性。

### 7.1.2 数据库范式与技术构成

自数据库诞生以来就伴随着各种技术的发展，不论是数据库的实现方式，还是数据库的应用领域都在变化，但是其中最核心的却是两个部分，一个是建立数据库的规则，即范式，另一个则是建立数据库所需的相关技术，如数据库引擎、SQL、数据库访问技术等。

**1、数据库范式的基本概念**

1970年，由美国著名的科学哲学家托马斯·库恩在其出版的专著《科学革命的结构》中提出了“范式”的概念，这个概念后来运用于多个领域，例如社会科学、计算机科学等。所谓范式，就是指常规科学赖以运作的理论基础和实践规范，是从事某一科学领域的研究者群体所共同遵从的世界观和行为方式，是一种公认的模型或者模式。同年，英国科学家埃德加·弗兰克·科德根据这一概念，提出了关系数据库模型，并对关系数据库的设计进行总结，从而提出了关系型数据库理论的理论基础，以及在设计数据库时必须要遵循的规则，即数据库范式。

目前数据库范式一共有八种，分别是第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第五范式（5NF）、DK范式（DKNF）、第六范式（6NF）。通常情况下，使用最多的就是1NF、2NF和3NF，下面对这三种范式进行详细说明。

1、第一范式（1NF）：所谓第一范式就是数据表中的每一个字段都是不可分割的原子。例如数据表中存储了电话字段，并且这个电话包括了公司电话和家庭电话，那么这个数据表就不符合第一范式，需要把电话字段再分为公司电话和家庭电话。需要注意的是，数据库设计只有满足了第一范式才能称为关系型数据库，否则就不能称为关系型数据库。

2、第二范式（2NF）：所谓第二范式，首先必须满足第一范式，然后为每张数据表添加一个主键，同时数据表中每个字段都依赖于主键。例如，对于主键为订单ID的数据表【订单详情】（订单ID，产品ID，单价，折扣，数量，产品名称）来说，虽然产品ID和订单ID共同构成了一个唯一的订单，但是单价，折扣，数量，产品名称等信息并不依赖于订单ID，而是依赖于产品ID。因此，这样的设计就不符合第二范式，而是应该把单价，折扣，数量，产品名称等信息单独列出一张表，通过产品ID作为主键和外键的方式进行关联。

3、第三范式（3NF）：所谓第三范式，首先必须满足第二范式，然后每张表中所有非主键字段都依赖于主键，并且不存在非主键字段依赖于非主键字段的问题。例如，对于主键为订单ID的数据表【订单表】（订单ID，下单日期，客户ID，客户名称，客户地址，客户所在城市）来说，虽然客户ID确实依赖于订单ID，从而实现客户名称、客户地址等信息依赖于订单ID，但是客户名称、客户地址等信息却不是直接依赖于主键订单ID，而是通过客户ID进行依赖传递，这样的数据表就不符合第三范式，而是应该把客户ID、客户名称、客户地址等信息单独拉成一张表，通过把客户ID作为主键和外键的方式进行两张表的关联。

**2、数据库存储引擎技术**

由于MySQL数据库是开源数据库，虽然背后有企业进行支撑，但主要还是由本地开发人员进行维护，而SQL Server和Oracle数据库由于是纯商业数据库，用户需要支付一定的费用才能获取软件产品，以及相应的服务，所以SQL Server和Oracle数据库的维护通常由对应的企业来完成，也因为这个原因MySQL中对于存储引擎的使用采用更加开放的态度，即开发人员可以根据项目的实际要求选择多个存储引擎，可以是某一个数据库的存储引擎，也可以是某一个数据表的存储引擎。所谓存储引擎，其主要作用是把各种数据通过不同的技术存储到文件或者内存中。目前MySQL中的存储引擎主要有三种，分别是InnoDB存储引擎、MyISAM存储引擎，以及Memory存储引擎，具体如表7-1所示。

表7-1 存储引擎的比较

| 名称 | 优点 | 缺点 |
| :-: | :- | :- |
| InnoDB存储引擎 | 支持事务处理和外键支持 | 运行速度慢，并且占用资源高 |
| MyISAM存储引擎 | 支持数据索引，以及高并发的读写操作 | 不支持事务操作 |
| Memory存储引擎 | 内存存储，性能优异 | 数据稳定性不高，并且掉电就会丢失所有数据 |


**3、数据库脚本技术SQL**

SQL是应用于数据库中的编程语言，与面向对象和面向过程的语言相比，这种语言的特点就是结构化。所谓结构化语言，其特点在于通过既定的规则或结构来实现对数据库操作，类似于数学公式的套用。SQL语句最早由IBM公司在上世纪70年代进行研发，用于最早的关系型数据库System R中进行数据的操作。到了80年代，美国国家标准局（ANSI）、国际标准化组织（ISO）先后发布了数据库语言美国标准和国际标准，也标志这SQL正式称为一个通用的数据库操作语言。SQL语句主要包含五个部分，分别是数据查询语句、数据操作语句、事务处理语句、数据定义语句、指针控制语句，具体如下：

1、数据查询语句：通过SELECT、WHERE、ORDER BY、GROUP BY和HAVING这些关键字来实现对数据库中数据的查询。

2、数据操作语句：通过INSERT、UPDATE和DELETE这些关键字来实现对数据库中数据的插入、升级和删除。

3、事务处理语句：通过BEGIN TRANSACTION、COMMIT和ROLLBACK这些关键字来实现对数据库中事件的处理。

4、数据定义语句：通过CREATE和DROP关键字实现数据库中数据表的创建和删除，以及索引等内容的加入。

5、指针控制语句：通过DECLARE CURSOR、FETCH INTO和UPDATE WHERE CURRENT来实现对一个或多个数据表的单独操作。

此外，SQL语言还提供了五种数据类型，分别是字符型（char或varchar）、文本型（text）、数值型（int、numeric、money）、布尔型（bit），以及日期型（datetime、smalldatetime）。其中字符型用于存放最长255个字符的字符串，文本型用于存放超长文本，数值型用于存放整型、浮点型、钱币类型，布尔型用于存放0或1的逻辑值，而日期型则用于存放日期数据类型。

**4、数据库访问库**

在应用系统中，除了需要有存储数据所需的数据库，还需要有访问数据库数据的访问接口，而针对不同的编程语言，其背后的支持公司都提供了不同的数据库访问库。目前主流的编程语言有五种，其中具有完整开发环境和商业公司支持的有微软的.Net技术和Oracle的Java技术，此外还有轻量级的PHP技术、Python技术，以及Node.js技术，其数据库访问库也有相应的五种，分别是：

1、.Net的ADO.NET组件：ADO.NET是微软针对.Net技术提供的访问各类关系型数据库的第三方软件库，默认情况下可以访问微软的SQL Server，而如果需要访问其他数据库，则需要下载对应的数据库驱动程序，例如要访问MySQL数据库，就需要在MySQL官网下载数据库驱动程序Connector/ODBC。

2、Java的JDBC组件：JDBC是Oracle提供访问关系型数据库的标准Java API程序库，但是由于该接口没有和任何数据库进行绑定，因此除了在程序中引入该库之外，还需要下载并安装对应数据库的驱动程序，例如访问MySQL就需要下载Connector/J。

3、PHP的PDO组件：PDO（PHP Database Object）插件是PHP中面向对象数据库组件，底层提供了几乎所有数据库的访问支持，而对于上层则采用统一的API进行访问和操作，也就是说开发人员只需要会使用PDO访问一种数据库，那么其他数据库也都会进行访问和操作。此外，由于和PHP联系最紧密的数据库就是MySQL，因此PHP还提供了针对MySQL的链接库MySQLi，通过该库可以实现对MySQL数据库的任意操作，但是缺点是该库只能应用于MySQL，而不能用于其他数据库。

4、Python的DB-API：DB-API与JDBC类似，提供了Python访问数据库标准接口，但是这些接口没有和特定的数据库进行绑定。因此，除了引入该API模块之外，还需要下载对应数据库的Python模块。

5、Node.js的数据库驱动：Node.js没有像其他语言一样提供统一的访问接口，而是需要根据不同的数据库下载并安装不同的操作库。

## 7.2 MySQL的数据类型与数据库、数据表的操作

为了更好的利用数据服务器中的资源，减少数据存储过程中无用数据的冗余，MySQL数据库对常用的数据类型进行抽象得到了MySQL中的四种数据类型，分别是数值型、时间型、字符串型，以及二进制型，并且数据库开发人员需要在保存数据时指定相应的数据类型，从而使得MySQL数据库能对这些数据进行优化。此外，由于MySQL是关系型数据库，因此在使用该数据库时，数据表是整个数据库的基础，数据库就是由各种类型的数据表组合而成，并且通过设置表和表之间的关系，从而实现整个系统的逻辑顺序。本节将详细分析数据库的四种数据类型所应用的场合，以及如何通过指令实现数据库和数据表的操作。

### 7.2.1 MySQL中的的数据类型

MySQL中的数据类型可以分为四大类，分别是数值型、时间型、字符串型，以及二进制型，其中数值型用于存储各类整型和浮点型的数据，时间型用于存储日期和时间的数据，字符串型则用于保存文本型的数据，而二进制类型则通常用于保存一些多媒体文件，但是由于二进制文件相较于字符串、数值类型的数据来说体积相对较大，因此对于二进制文件的保存和检索都比较慢，所以保存二进制数据的常规做法并不是把数据保存在数据库中，而是采用字符串的形式保存二进制数据在本地的存储路径，然后通过读取数据库中二进制数据的路径来对这些信息进行存取。

**1、数值类型**

数值类型是MySQL中最基本的数据类型，主要包括整数型和浮点数型，其中整数型根据数据长度的不同又可以分为微整型（TINYINT）、小整型（SMALLINT）、中整型（MEDIUMINT）、整型（INT），以及超大整型（BIGINT），读者可以根据自己项目的需求选择不同的数据类型，从而减少保存数据时的资源浪费，具体如表7-2所示。

表7-2 整数型的数据类型

| 类型名 | 字节数 | 有符号的取值范围 | 无符号的取值范围 |
| :-: | :-: | :-: | :-: |
| 微整型（TINYINT） | 1字节 | 0 ~ 2^8 - 1 | -2^7 ~ 2^7 - 1 |
| 小整型（SMALLINT） | 2字节 | 0 ~ 2^16 - 1 | -2^15 ~ 2^15 - 1 |
| 中整型（MEDIUMINT） | 3字节 | 0 ~ 2^24 - 1 | -2^23 ~ 2^23 - 1 |
| 整型（INT） | 4字节 | 0 ~ 2^32 - 1 | -2^31 ~ 2^31 - 1 |
| 超大整型（BIGINT） | 8字节 | 0 ~ 2^64 - 1 | -2^63 ~ 2^63 - 1 |

除了整数类型外，MySQL还有浮点数据类型，即带小数的数据类型，包括单精度浮点型（FLOAT）、双精度浮点型（DOUBLE），以及定点浮点型（DECIMAL），其中单精度浮点型占用4字节，而双精度浮点型占用8字节。特别要说明的是定点浮点型，该数据类型由两个数据类型参数构成，分别是表示数据总长度的参数M和表示小数点位数的参数D。当传入数据的小数点位数比设定的位数长时，那么数据就会进行四舍五入操作，而当传入的数据位数比设定的位数小时，则全部保留。此外，由于定点浮点数在保存数据时，其本质是使用字符串对数据进行保存，因此在数据保存的准确性上会比单精度浮点型和双精度浮点型要高，也因为这个原因，当对于数据精度要求比较高时，通常都会采用定点浮点型进行保存。具体的数据类型的定义格式如下：

```sql
DECIMAL(M, D)
```

**2、时间类型**

为了方便进行时间信息的管理，MySQL提供了五种与日期和时间相关的数据类型，分别是年份类型（YEAR）、日期类型（DATE）、时间类型（TIME）、日期时间类型（DATETIME），以及时间戳（TIMESTAMP），并且这五种数据均采用字符串的方式进行保存，其中年份类型（YEAR）用于存放年份、日期类型（DATE）用于存放年月日、时间类型（TIME）用于存放时分秒、日期时间类型（DATETIME）可以同时存放年月日和时分秒，而时间戳（TIMESTAMP）与日期时间类型相似，但是时间戳特别之处在于具有时区之间转换的功能。

>* 年份类型（YEAR）：年份类型在MySQL数据中占用1个字节，并且MySQL提供了两个种存储方式，一种是使用四位数字或字符串进行存储的完整年份，即“YYYY”的形式，采用这种形式存储的年份其最小值为1901年，最大值为2155年，而当超过这个范围时则会保存为默认值“0000”。另外一种则是使用两位字符串进行存储，即年份数据的后两位，当传入的值在1-69之间，那么MySQL会自动把年份转化为2001-2069年，而当传入的值在70-99之间，那么MySQL会自动把年份转化为1970-1999，而当传入数字00时，MySQL却不会自动转化为2000年，而是转化为0000年。因此，如果需要添加2000年，那么就需要传入字符串“0”。

>* 日期类型（DATE）：日期类型在MySQL数据库中占用4个字节，并且MySQL提供了四种存储日期类型的方式，分别是“YYYY-MM-DD”、“YY-MM-DD”、“YYYYMMDD”和“YYMMDD”，从形式上可以明显看到其实质就是两大类，一种是带分隔符的日期表示，还有一种是不带分隔符的日期表示，同时每种表示方式又可以再分两类，一种是采用完整年份进行保存，还有一种是采用缩略年份进行保存，当采用缩略年份时，其特性与年份类型（YEAR）的规则相同。此外，当传递的月份和日期不足两位时，MySQL会自动在前面补0，例如传入的日期为“2018-5-4”，那么保存在MySQL中会变为“2018-05-04”。

>* 时间类型（TIME）：时间类型在MySQL数据库中占用3个字节，并且MySQL提供了两种存储时间类型的方式，分别是“HH:MM:SS”和“HHMMSS”，从形式上可以发现和日期类型相似，即一个带分隔符，还有一个不带分隔符。另外这两种方式都可以采用非严格方式进行存储，例如采用HH:MM:SS方式传入“11:20”，那么存入数据库时就会变为“11:20:00”，如果采用HHMMSS方式传入“1120”，那么存入数据库时就会变为“00:11:20”。由此可以看出，不论采用哪种方式进行存储，当传入的参数不足时会在其缺省的位置不足0。

>* 日期时间类型（DATETIME）：日期时间类型在MySQL数据库中占用8个字节，是日期类型和时间类型的组合，因此其存储方式也有四种，分别是“YYYY-MM-DD HH:MM:SS”、“YY-MM-DD HH:MM:SS”和“YYYYMMDDHHMMSS”、“YYMMDDHHMMSS”，从上面的存储方式可以看出，采用日期时间类型进行数据存储时，如果日期采用分隔符方式，那么时间也要采用分隔符方式，而如果日期采用非分隔符方式，那么时间也需要采用非分隔符方式。

>* 时间戳（TIMESTAMP）：时间戳在MySQL数据库中占用4个字节，时间戳的使用和日期时间类型非常相似，但是由于时间戳所表示的是从格林威治时间1970年01月01日00时00分00秒起到当前所经过的秒数。因此采用时间戳保存的数据具有时区的概念，通过时区的转换可以把时间戳转化为不同时区所对应的时间。此外，时间戳的表示范围较小，最大时间为2038-01-19 11:14:07，当超过这个值时就会显示错误，因此在一般应用时通常采用日期时间类型，而不采用时间戳，只有在需要进行时区转化时才使用时间戳进行数据保存。

### 7.2.2 MySQL的时间类型



### 7.2.3 MySQL的字符串类型
{'fraction_non_alphanumeric': 0.10490439604875512, 'fraction_numerical': 0.026861533129224194, 'mean_word_length': 20.831021437578816, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 18, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '6673331', 'n_tokens_mistral': 15422, 'n_tokens_neox': 17994, 'n_words': 303}