MySQL数据库基础

一、什么是数据库？

数据库是结构化信息或数据的有组织的集合，通常以电子方式存储在计算机系统中。数据库通常由数据库管理系统（DBMS）控制。数据和DBMS，以及它们相关联的应用程序一起被称为数据库系统，通常简称为数据库。

存储数据用文件就可以了，为什么需要数据库呢？

虽然可以用文件存储数据，但是文件存储数据会有以下缺点：

• 文件的安全性问题：数据误操作之后无法进行回滚。
• 不利于数据查询和管理：文件中的数据没有用某种数据结构组织起来。
• 文件不利于存储海量数据：数据量越大，管理和操控数据的成本越高，文件存储不适合。
• 文件在程序中控制不方便：在文件中查找数据成本较高。

为了解决文件存储数据的不足，设计了更加利于管理数据的数据库，数据库能够更高效的管理数据。

MySQL的认识：

MySQL 是数据库中的其中一种，它是运行在服务器上的数据库系统，适用于小型和大型应用程序。MySQL 数据库中的数据以表的形式存储。表示相关数据的集合，由列和行组成。

数据库是分为数据库服务器和数据库客户端的，其底层应用的是 TCP 协议。当我们安装好 MySQL 之后，客户端和服务端就一起安装了。如下所示：

mysqld 是 MySQL 的服务器端。我们使用的 mysql 命令实际上就是连接 mysqld 服务。使用命令 netstat 命令可以查看 TCP 协议的网络连接：

mysqld（mysql daemon） 其实是 SQL 后台程序（也就是 MySQL 服务器），它是关于服务器端的一个程序，它在后台运行，监听 3306 端口。当你使用客户端程序时，这个程序一定会在后台运行，因为客户端是通过连接服务器来访问数据库的。只有启动了 mysqld.exe ，MySQL 数据库才可以进行工作。

MySQL 是一个客户端软件，可以对任何主机的 MySQL 服务（即后台运行的 mysqld）发起连接，mysql 自带的客户端程序一般在 cmd 或者终端下进行操作。

数据库的存储介质：

MySQL 的数据存储结构主要分两个方面：物理存储结构与内存存储结构，作为数据库，所有的数据最后一定要落到磁盘上，才能完成持久化的存储。内存结构为了实现提升数据库整体性能，主要用于存储临时数据和日志的缓冲。

二、主流数据库

• SQL Sever： 微软的产品，.Net程序员的最爱，中大型项目。
• Oracle： 甲骨文产品，适合大型项目，复杂的业务逻辑，并发一般来说不如MySQL。
• MySQL：世界上最受欢迎的数据库，属于甲骨文，并发性好，不适合做复杂的业务。主要用在电商，SNS，论坛。对简单的SQL处理效果好。
• PostgreSQL :加州大学伯克利分校计算机系开发的关系型数据库，不管是私用，商用，还是学术研究使用，可以免费使用，修改和分发。
• SQLite： 是一款轻型的数据库，是遵守ACID的关系型数据库管理系统，它包含在一个相对小的C库中。它的设计目标是嵌入式的，而且目前已经在很多嵌入式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了。
• H2： 是一个用Java开发的嵌入式数据库，它本身只是一个类库，可以直接嵌入到应用项目中。

三、MySQL的基本使用

3.1 连接服务器

连接 MySQL 服务器端的指令：

mysql -h [ip地址] -P [端口号] -u [用户] -p

• -h ：表示要连接的 MySQL 服务器所在的主机，127.0.0.1 表示本主机
• -P ：连接的 MySQL 服务器对应的端口号，一般默认的端口号是 3306
• -u ：选择用哪一个用户连接 MySQL 服务器
• -p ：可以直接将对应登录用户的密码跟在后面，也可以回车确认后再输入

连接 MySQL 服务器：

连接本地 MySQL 服务器：

当连接到 MySQL 服务器后，输入指令 quit 或 exit 就可以退出 mysql 了。

注意：

• 如果没有写 -h 127.0.0.1 默认是本地连接。
• 如果没有写 -P 3306 默认是连接 3306 端口号。

3.2 服务器管理

Windows 下：

1. 鼠标右键点击 “此电脑” ，选中 管理 选项。
   
2. 打开服务和应用程序，选择 服务 选项。

3. 通过下图左侧的三个选项：停止、暂停、重启动 来进行服务管理。

Linux下：

启动 MySQL 服务 :

输入指令 systemctl start mysqld 或 service mysqld start 可以启动 MySQL 服务 ：

停止 MySQL 服务 :

输入指令 systemctl stop mysqld 或 service mysqld stop 可以停止 MySQL 服务：

重启 MySQL 服务：

输入指令 systemctl restart mysqld 或 service mysqld restart 可以重启 MySQL 服务 ：

3.3 数据库、服务器、表关系

所谓安装数据库服务器，只是在机器上安装了一个数据库管理系统程序，这个管理程序可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。为了保持应用实体中的数据，一般会在数据库中创建多个表，以保存程序中实体的数据。

数据库服务器、数据库和表的关系如下所示：

3.4 MySQL的基本使用

数据库的存储路径：

我们使用 MySQL 创建的数据库和各种表结构，都会以文件的形式存储，可以通过查看 MySQL 配置文件中的 datadir 来得知数据库和各种表的存储路径。

MySQL 配置文件的路径在 /etc/my.cnf，可以通过指令 vim /etc/my.cnf 来查看或者修改 MySQL 的配置文件，如下所示，datadir=/var/lib/mysql ，因此我们可以通过此路径来查看数据库的各种文件和表结构 ：

接下来，我们使用 MySQL 创建的数据库文件都会存储在该目录下，如下 ：

创建数据库：

create databases [if not exists] t1; 
// t1表示创建表的名称,[]中的内容可以省略，if not exists 表示该名为t1的数据库不存在则创建该数据库

示例：

当我们创建成功一个数据库时，在路径 /var/bin/mysql 下就会生成一个与创建数据库同名称的目录，cd 进入该目录，可以看到一个名为 db.opt 的文件，该文件中指明了该数据库的默认字符编码和字符校验规则。

在 MySQL 中创建一个数据库，实际上就是在 MySQL 的数据存储路径下新建一个目录，在数据库中创建一个表实际上就是在该目录下创建一个文件。因此，在 MySQL 数据存储的路径下删除掉某个目录，实际上就是删除了该数据库。

选择数据库：

use t1; // 在使用数据库之前，需要使用use选择要使用的数据库

选择你要使用的数据库，相当于命令行中的 cd ，进入数据库对应的目录。

创建数据库表：

create table [if not exists] student(
 id int,
 name varchar(32),
 gender varchar(2)
);

下列 student.frm 是表结构文件，student.ibd 是表数据索引文件。

如下所示，使用 show create table 表名 \G 可以查看数据库表的结构，在没有指定的情况下，默认字符编码和字符校验规则都是配置文件指定的。

表中插入数据：

insert into student (id, name, gender) values (01, '张三', '男');
insert into student (id, name, gender) values (02, '李四', '女');
insert into student (id, name, gender) values (03, '王五', '男')

示例：

查询表中数据：

select * from student; // student表示要显示的表的名称 

四、SQL分类

我们都知道结构化查询语言（SQL）是一种数据库语言，通过它可以对现有的数据库进行某些操作，也可以使用这些语言来创建数据库。SQL 使用某些命令，如：创建、删除、插入等来执行所需的任务。

这些 SQL 指令主要分为五类：

• DDL（Data Definition Language）：数据定义语言，用来维护存储数据的结构。【代表指令：create、drop、alter】
• DML（Data Manipulation Language）：数据操作语言，用来对数据进行操作。【代表指令：insert、delete、update】
• DQL（Data Query Language）：数据查询语言。【代表指令：select】
• DCL（Data Control Language）：数据控制语言，主要负责权限管理和事务。【代表指令：grant、revoke】
• TCL（Transaction Control Language）：事务控制语言。【代表指令：commit、savepoint】

五、MySQL架构

MySQL 是一个可移植的数据库，几乎能在所有的操作系统上运行，如 unix/linux、Windows、Mac 和 Solaris。各种系统在底层实现方面各有不同，但是 MySQL 基本上能保证在各个平台的物理体系结构的一致性。

MySQL 体系结构描述了 MySQL 系统中不同组件之间的关系。MySQL 遵循客户端服务器架构。它的设计目的是让客户端可以使用各种网络服务访问资源。MySQL 的架构包含以下主要层：

• 客户端
• 服务器
• 存储层

MySQL 的体系架构图如下 ：

从 MySQL 的架构图，可以看出 MySQL 的架构从上向下大致可以分为：网络连接层、数据库服务层、存储引擎层和系统文件层四大部分。

MySQL 体系架构主要可分为以下四层：

连接层：所包含的服务并不是 MySQL 独有的技术。他们都是服务于 C/S 程序或者这些程序所需要的。主要完成一些连接处理，身份验证，安全性等。

核心服务层：这是 MySQL 的核心部分。通常叫做 SQL Layer。MySQL 数据库系统处理底层数据之前的工作都是在这一层完成的，包括权限判断、SQL接口、SQL解析、分析优化、缓存查询的处理以及部分内置函数执行等。各个引擎提供的功能都在这一层，如存储过程、触发器、视图等。

存储引擎层：通常叫做 StorEngine Layer ，也就是底层数据存取操作实现部分，由多种存储引擎共同组成。负责存储和获取所有存储在 MySQL 中的数据。每个存储引擎都有自己的优缺点。服务器是通过存储引擎 API 来与它们交互。存储引擎不能解析 SQL，互相之间也不能通信。仅仅是简单的响应服务器的请求。

数据存储层：主要是将数据存储在运行于裸设备的文件系统上，并完成与存储引擎的交互。

六、存储引擎

存储引擎就是数据库管理系统如何存储数据，如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。MySQL 的核心就是插件式存储引擎，支持多种存储引擎。

可以通过指令 show engines; 来查看MySQL 支持的存储引擎 ：

从上图可看出，MySQL 底层默认使用的存储引擎是 InnoDB。

各个存储引擎之间的对比：