数据库概念和MySQL知识

C语言中文网：数据库入门 (biancheng.net)

SQL( Structured Query Language,结构化査询语言)是一种特定目的编程语言，用于管理关系数据库管理系统，或在关系流数据管理系统中进行流处理。

DDL( Data Definition Language)数据定义语言：用于定义或修改数据库中的对象，如：表、索引、视图、序列、用户、角色、表空间等。
DML( Data Manipulation Language)数据操纵语言：用于对数据库表中的数据进行操作，如插入，更新和删除。
DCL( Data Control Language)数据控制语言：用来设置或更改数据库事务、权限操作(用户或角色授权，权限回收)、等。
DQL( Data Query Language)数据査询语言：用来查询数据库内的数据，如单表查询、多表查询。

数据库（Database）指长期存储在计算机内的、有组织的、可共享的数据集合。

数据库管理系统（DBMS）是数据库系统的核心软件之一，是位于用户与操作系统之间的数据管理软件，用于建立，使用和维护数据库。它的主要功能包括数据定义、数据操作、数据库的运行管理、数据库的建立和维护等几个方面。

我们常说 XX 数据库，其实实质上是 XX 数据库管理系统。目前，较为流行的数据库管理系统有 MySQL、SQL Server、Oracle 和 DB2 等。

数据库有两种类型，分别是关系型数据库和非关系型数据库。

关系型数据库

关系型数据库是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。简单说，关系型数据库是由多张能互相连接的表组成的数据库。

优点

都是使用表结构，格式一致，易于维护。
使用通用的 SQL 语言操作，使用方便，可用于复杂查询。
数据存储在磁盘中，安全。

缺点

读写性能比较差，不能满足海量数据的高效率读写。
不节省空间。因为建立在关系模型上，就要遵循某些规则，比如数据中某字段值即使为空仍要分配空间。
固定的表结构，灵活度较低。

常见的关系型数据库有 Oracle、DB2、PostgreSQL、Microsoft SQL Server、Microsoft Access 和 MySQL 等。

非关系型数据库

非关系型数据库又被称为 NoSQL（Not Only SQL )，意为不仅仅是 SQL。通常指数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定。

优点

非关系型数据库存储数据的格式可以是 key-value 形式、文档形式、图片形式等。使用灵活，应用场景广泛，而关系型数据库则只支持基础类型。
速度快，效率高。 NoSQL 可以使用硬盘或者随机存储器作为载体，而关系型数据库只能使用硬盘。
海量数据的维护和处理非常轻松。
非关系型数据库具有扩展简单、高并发、高稳定性、成本低廉的优势。
可以实现数据的分布式处理。

缺点

非关系型数据库暂时不提供 SQL 支持，学习和使用成本较高。
非关系数据库没有事务处理，没有保证数据的完整性和安全性。适合处理海量数据，但是不一定安全。
功能没有关系型数据库完善。

常见的非关系型数据库有 Neo4j、MongoDB、Redis、Memcached、MemcacheDB 和 HBase 等。

数据库系统

数据库系统是指在计算机系统中引入数据库后的系统。完整的数据库系统结构关系如图所示：

由图可知，一个完整的数据库系统一般由数据库、数据库管理系统、应用开发工具、应用系统、数据库管理员和用户组成。

数据库系统主要有以下 3 个组成部分：

数据库：用于存储数据的地方。
数据库管理系统：用于管理数据库的软件。
数据库应用程序：为了提高数据库系统的处理能力所使用的管理数据库库的软件补充。

数据库（DataBase，DB）提供了一个存储空间来存储各种数据，可以将数据库视为一个存储数据的容器。一个数据库可能包含许多文件，一个数据库系统中通常包含许多数据库。

数据库管理系统（Database Management System，DBMS）是用户创建、管理和维护数据库时所使用的软件，位于用户和操作系统之间，对数据库进行统一管理。DBMS 能定义数据存储结构，提供数据的操作机制，维护数据库的安全性、完整性和可靠性。

虽然已经有了 DBMS，但是在很多情况下，DBMS 无法满足对数据管理的要求。

数据库应用程序（DataBase Application）的使用可以满足对数据管理的更高要求，还可以使数据管理过程更加直观和友好。数据库应用程序负责与 DBMS 进行通信、访问和管理 DBMS 中存储的数据，允许用户插入、修改、删除数据库中的数据。

下面再简单介绍一下 DBMS 提供的一些功能，主要包括以下几个方面。

1) 数据定义功能

DBMS 提供数据定义语言（Data Definition Language，DDL），用户通过它可以方便地对数据库中的数据对象进行定义。

2) 数据操纵功能

DBMS 还提供数据操纵语言（Data Manipulation Language，DML），用户可以使用 DML 操作数据，实现对数据库的基本操作，如查询、插入、删除和修改等。

3) 数据库的运行管理

数据库在建立、运用和维护时由数据库管理系统统一管理、统一控制，以保证数据的安全性、完整性、多用户对数据的并发使用及发生故障后的系统恢复。例如：

数据的完整性检查功能保证用户输入的数据应满足相应的约束条件；
数据库的安全保护功能保证只有赋予权限的用户才能访问数据库中的数据；
数据库的并发控制功能使多个用户可以在同一时刻并发地访问数据库的数据；
数据库系统的故障恢复功能使数据库运行出现故障时可以进行数据库恢复，以保证数据库可靠地运行。

4) 提供方便、有效地存取数据库信息的接口和工具

编程人员可通过编程语言与数据库之间的接口进行数据库应用程序的开发。数据库管理员（Database Administrator，DBA）可通过提供的工具对数据库进行管理。

数据库管理员是维护和管理数据库的专门人员。

5) 数据库的建立和维护功能

数据库功能包括数据库初始数据的输入、转换功能，数据库的转储、恢复功能，数据库的重组织功能和性能监控、分析功能等。这些功能通常由一些使用程序来完成。

常用数据库访问接口

不同的程序设计语言会有各自不同的数据库访问接口，程序语言通过这些接口，执行 SQL 语句，进行数据库管理。主要的数据库访问接口主要有 ODBC、JDBC、ADO.NET 和 PDO。

ODBC

ODBC（Open Database Connectivity，开放数据库互连）为访问不同的 SQL 数据库提供了一个共同的接口。ODBC 使用 SQL 作为访问数据的标准。这一接口提供了最大限度的互操作性。一个应用程序可以通过共同的一组代码访问不同的 SQL 数据库管理系统。

一个基于 ODBC 的应用程序对数据库的操作不依赖任何 DBMS，不直接与 DBMS 打交道，所有的数据库操作由对应的 DBMS 的 ODBC 驱动程序完成。也就是说，不论是 MySQL 还是 Oracle 数据库，均可用 ODBC API 进行访问。由此可见，ODBC 的最大优点是能以统一的方式处理所有的数据库。

JDBC

Java Data Base（JDBC，Java 数据库连接）用于 Java 应用程序连接数据库的标准方法，是一种用于执行 SQL 语句的 Java API，可以为多种关系数据库提供统一访问，它由一组用 Java 语言编写的类和接口组成。

ADO.NET

ADO.NET 是微软在 .NET 框架下开发设计的一组用于和数据源进行交互的面向对象类库。ADO.NET 提供了对关系数据、XML 和应用程序的访问，允许和不同类型的数据源以及数据库进行交互。

PDO

PDO（PHP Data Object）为 PHP 访问数据库定义了一个轻量级的、一致性的接口，它提供了一个数据访问抽象层，这样，无论使用什么数据库，都可以通过一致的函数执行查询和获取数据。PDO 是 PHP 5 新加入的一个重大功能。

数据库的种类

DBMS（数据库管理系统）主要通过数据的保存格式进行分类，现阶段主要分为以下几种类型。

层次数据库(HDB)

层次数据库（Hierarchical Database）是最早研制成功的数据库系统，它把数据通过层次结构（树形结构）的方式表现出来。层次数据库曾经是数据库的主流，但随着关系数据库的出现和普及，现在已经很少使用了。

比较具有代表性的层次数据库是 IMS（Information Management System）数据库，由 IBM 公司研制成功。

关系型数据库(RDB)

关系型数据库(Relational Database)是现在应用最广泛的数据库。关系型数据库在 1969 年诞生，可谓历史悠久。和 Excel 工作表一样，关系型数据库也采用由行和列组成的二维表来管理数据，所以简单易懂。同时，它还使用 SQL（Structured Query Language，结构化查询语言）对数据进行操作。

比较具有代表性的关系型数据库有 Oracle Database、SQL Server、DB2、PostgreSQL 和MySQL。

面向文档数据库

文档(Document-Oriented)型数据库的灵感来自于 Lotus Notes 办公软件，和最后一种键值存储数据库类似。文档型数据库可以看作是键值数据库的升级版，允许之间嵌套键值。而且文档型数据库比键值数据库的查询效率更高。

面向文档数据库会将数据以文档的形式存储。每个文档都是一系列数据项的集合。每个数据项都有一个名称与对应的值，值既可以是简单的数据类型，如字符串、数字和日期等；也可以是复杂的类型，如有序列表和关联对象。

数据存储的最小单位是文档，同一个表中存储的文档属性可以是不同的，数据可以使用XML、JSON或者JSONB等多种形式存储。

具有代表性的面向文档数据库有 MongDB 和 CouchDB。

列存储数据库

列存储(Column-oriented)数据库将数据存储存在列族（column family）中，一个列族用来存储经常被一起查询的相关数据。例如，如果有一个 Person 类，我们经常会一起查询他们的姓名和年龄而不是薪资。这种情况下，姓名和年龄就会被放入一个列族中，而薪资则在另一个列族中。

列存储数据库通常用来应对分布式存储的海量数据。具有代表性的列存储数据库有 Cassandra 和 HBase。

XML 数据库(XMLDB)

XML 数据库(XML Database)是一种支持对 XML（标准通用标记语言下的一个应用）格式文档进行存储和查询等操作的数据管理系统。在系统中，开发人员可以对数据库中的 XML 文档进行查询、导出和指定格式的序列化。

键值存储数据库(KVS)

键值存储数据库(Key-Value Store)是用来保存查询所使用的主键（Key）和值（Value）的组合的数据库。具有编程语言知识的读者可以把它想象成关联数组或者散列（hash）。

近年来，随着键值存储数据库被应用到 Google 等需要对大量数据进行超高速查询的 Web 服务当中，它正逐渐为人们所关注。

具有代表性的键值存储数据库有 Redis、Memcached 和 MemcachedDB。

MySQL存储引擎

数据库存储引擎是数据库底层软件组件，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作。简而言之，存储引擎就是指表的类型。数据库的存储引擎决定了表在计算机中的存储方式。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎还可以获得特定的功能。

MySQL 提供了多个不同的存储引擎，包括处理事务安全表的引擎和处理非事务安全表的引擎。在 MySQL 中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。

MySQL 5.7 支持的存储引擎有 InnoDB、MyISAM、Memory、Merge、Archive、CSV、BLACKHOLE 等。可以使用SHOW ENGINES;语句查看系统所支持的引擎类型，mysql8.0结果如图所示：

Support 列的值表示某种引擎是否能使用，YES表示可以使用，NO表示不能使用，DEFAULT表示该引擎为当前默认的存储引擎。

存储引擎	描述
ARCHIVE	用于数据存档的引擎，数据被插入后就不能在修改了，且不支持索引。
CSV	在存储数据时，会以逗号作为数据项之间的分隔符。
BLACKHOLE	会丢弃写操作，该操作会返回空内容。
FEDERATED	将数据存储在远程数据库中，用来访问远程表的存储引擎。
InnoDB	具备外键支持功能的事务处理引擎
MEMORY	置于内存的表
MERGE	用来管理由多个 MyISAM 表构成的表集合
MyISAM	主要的非事务处理存储引擎
NDB	MySQL 集群专用存储引擎

有几种存储引擎的名字还有同义词，例如，MRG_MyISAM 和 NDBCLUSTER 分别是 MERGE 和 NDB 的同义词。存储引擎 MEMORY 和 InnoDB 在早期分别称为 HEAP 和 Innobase。虽然后面两个名字仍能被识别，但是已经被废弃了。

修改默认存储引擎

查看默认存储引擎

1	SHOW VARIABLES LIKE 'default_storage_engine%';

修改数据库临时的默认存储引擎

1	SET default_storage_engine=< 存储引擎名 >

如，将 MySQL 数据库的临时默认存储引擎修改为 MyISAM

重启客户端时，默认存储引擎仍然是 InnoDB

拓展阅读

如果希望修改默认的存储引擎，就需要修改 my.cnf 配置文件。在my.cnf 配置文件的 [mysqld] 后面加入以下语句：

1	default-storage-engine=存储引擎名称

选择存储引擎

特性	MyISAM	InnoDB	MEMORY
存储限制	有	支持	有
事务安全	不支持	支持	不支持
锁机制	表锁	行锁	表锁
B树索引	支持	支持	支持
哈希索引	不支持	不支持	支持
全文索引	支持	不支持	不支持
集群索引	不支持	支持	不支持
数据缓存		支持	支持
索引缓存	支持	支持	支持
数据可压缩	支持	不支持	不支持
空间使用	低	高	N/A
内存使用	低	高	中等
批量插入速度	高	低	高
支持外键	不支持	支持	不支持

1）MyISAM

在 MySQL 5.1 版本及之前的版本，MyISAM 是默认的存储引擎。

MyISAM 存储引擎不支持事务和外键，所以访问速度比较快。如果应用主要以读取和写入为主，只有少量的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择 MyISAM 存储引擎是非常适合的。

MyISAM 是在 Web 数据仓储和其他应用环境下最常使用的存储引擎之一。

2）InnoDB

MySQL 5.5 版本之后默认的事务型引擎修改为 InnoDB。

InnoDB 存储引擎在事务上具有优势，即支持具有提交、回滚和崩溃恢复能力的事务安装，所以比 MyISAM 存储引擎占用更多的磁盘空间。

如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询以外，还包括很多的更新、删除操作，那么 InnoDB 存储引擎是比较合适的选择。

InnoDB 存储引擎除了可以有效地降低由于删除和更新导致的锁定，还可以确保事务的完整提交（Commit）和回滚（Rollback），对于类似计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB 都是合适的选择。

3）MEMORY

MEMORY 存储引擎将所有数据保存在 RAM 中，所以该存储引擎的数据访问速度快，但是安全上没有保障。

MEMORY 对表的大小有限制，太大的表无法缓存在内存中。由于使用 MEMORY 存储引擎没有安全保障，所以要确保数据库异常终止后表中的数据可以恢复。

如果应用中涉及数据比较少，且需要进行快速访问，则适合使用 MEMORY 存储引擎。

修改数据表存储引擎

MySQL 中修改数据表的存储引擎的语法格式如下：

1	ALTER TABLE <表名> ENGINE=<存储引擎名>; # ENGINE 关键字用来指明新的存储引擎

示例：将数据表 student 的存储引擎修改为 MyISAM。

在修改存储引擎之前，先使用 SHOW CREATE TABLE 语句查看 student 表当前的存储引擎，结果如下

mysql> SHOW CREATE TABLE student \G
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `stuId` int(4) DEFAULT NULL,
  `id` int(4) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `stuno` int(11) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `age` int(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

将 student 表的存储引擎修改为 MyISAM 类型：

1	ALTER TABLE student ENGINE=MyISAM;

使用 SHOW CREATE TABLE 语句再次查看 student 表的存储引擎，会发现student 表的存储引擎变成了“MyISAM”。