【学习目标】

知识目标

了解关系型数据库。

认识MySQL和Oracle。

了解MySQL的优势。

掌握MySQL的安装、部署、连接。

掌握MySQL数据库和表的创建、修改和删除。

掌握MySQL的用户管理和查询操作。

掌握MySQL的基本状态监控方法。

掌握数据库备份恢复的方法。

了解数据库的性能监控和优化方法。

技能目标

安装、配置和连接MySQL。

设计数据库和创建、修改MySQL数据库结构。

创建用户和赋予权限。

插入测试数据、修改、删除和查询MySQL数据库。

备份和恢复MySQL数据库。

监控和调试MySQL数据库。

【认证考点】

了解MySQL和Oracle数据库各自的特点。

认识MySQL数据库。

掌握MySQL的安装、配置和连接。

掌握MySQL数据库的创建、修改和删除。

掌握MySQL数据库的数据的基本操作。

能够备份和恢复MySQL数据库。

能够对进行MySQL用户及权限进行基本管理。

能够对MySQL数据库数据导入导出。

能够对MySQL进行状态的监控。

了解数据库的性能监控与优化。

项目引导：论坛数据库部署与实现

【项目描述】

论坛是一种能够提供人们进行网络交流的平台，用户通过发表观点和评论来实现用户和用户间、企业和企业间的信息交互，利用网络经济又快捷地与外界进行各种信息沟通。本项目的目的实现基于B/S结构的企业产品论坛系统的数据库的部署与实现。主要内容包括：设计论坛系统的数据库模型，并使用MySQL建立数据库及表结构、插入测试数据、对数据库进行基本的查询和修改，在数据库运行的过程中，对数据库的用户和权限进行基本管理、备份和恢复数据库、对数据库的状态进行监控、监控数据库性能、优化数据库。

1.需求分析

网络论坛是一个网络交流空间，用户不受时间和空间的约束，发表自己的观点。一个论坛要求具有以下几个基本功能：

（1）用户管理

① 能够对用户名和密码简单验证；

② 用户注册，能够判断用户注册的用户名是否已存在；

③ 能够识别用户类型为管理员用户或普通用户。

（2）浏览帖子和帖子管理

① 用户可以浏览和发表帖子；

② 用户还可以再编辑和删除帖子；

③ 管理员可以对帖子进行审核；

④ 帖子被用户每浏览1次，帖子的浏览量增1；

⑤ 管理员可以设置热门帖子。

（3）论坛版块管理

① 一个论坛通常包括很多版块，一个板块下有很多帖子；

② 只有管理员、版主和成员能看到这个版块下的内容。

（4）发表评论和评论管理

① 用户可以发表和查看评论；

②版主和管理员可以对版内的评论进行管理。

2.数据库实现机制

整个系统采用Apache作为应用服务器，MySQL作为数据库服务器管理软件。根据需求分析可以规划出该数据库的实体有：用户实体、版块实体、帖子实体、评论实体。数据库的设计和实现参照任务1，任务1包括数据库的设计和实现，这部分内容包括论坛数据库的概念结构设计和逻辑结构设计、数据库的实现。数据库管理参照任务2，任务2实现了数据库的权限管理、备份与恢复、状态监控、性能监控与优化等内容。

知识储备

5.1 关系型数据库管理系统概述

数据库（Database，DB）是长期存储在计算机内、有组织的、统一管理的、可共享的相关数据集合。这种数据集合具有如下特点：尽可能不重复，以最优方式为某个特定组织的多种应用服务，其数据结构独立于使用它的应用程序，对数据的增、删、改、查由统一软件进行管理和控制。数据库管理系统（Database Management System，DBMS）就是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库。

5.1.1 关系型数据库简介

数据的组织形式可以是表的形式、树的形式以及图的形式等，相对应的数据模型称为关系模型、层次模型和网状模型，这些也是比较经典的数据模型。关系型数据库指的是经过数学理论验证可以保存现实生活中的各种关系数据，数据库中存储数据以二维表为单位。

关系数据模型中，一个关系（Relation）也就是一张表（Table）。关系数据模型由数据结构、完整性约束规则和关系运算三部分构成。数据库则是由有相互关联关系的Table组成。Table中描述的是一批有相互关联关系的数据，表（Table）也就是关系（Recaltion）。表名和表的标题（格式）一起称作关系模式。表中的一行数据，称作行/元组/记录（Row/Tuple/Record）。表中的一列数据，称作列/属性/字段/数据项（Column/Attribute/Field/Data Item）。

能够对关系型数据库进行管理的数据管理系统可以被称为关系型数据库管理系统（Relational Database Management System，RDBMS）。RDBMS通常具有以下功能：

（1）能够提供方法使用户能够对数据库对象进行建立、修改，完整性约束和保密限制等约束进行定义和描述，对数据追加、删除、更新和查询操作。

（2）需保证数据库系统的正常运行，包括多用户环境下的并发控制、安全性检查和存取限制控制、完整性检查和执行、运行日志的组织管理、事务的管理和自动恢复，即保证事务的原子性。

（3）还要对数据组织、存储、管理和维护等。

（4）由于RDBMS运行在操作系统之上，所以RDBMS还具有与操作系统的联机处理、分时系统及远程作业输入的相关接口，负责处理数据的传送。

（5）网络环境下的数据库管理系统，还应该具有与网络中其他软件系统的通信功能以及数据库之间的互操作功能。

目前，市场上开源和非开源的主流关系型数据库管理系统有MySQL、Oracle、SQL Server、PostgreSQL、MariaDB等RDBMS。

5.1.2 MySQL简介

MySQL是一个开放源代码的RDBMS，它是由瑞典MySQL AB公司开发、发布并支持。MySQL是最流行的关系型数据库管理系统之一，在WEB应用方面，MySQL是最好的 RDBMS之一。

MySQL因为其速度、可靠性和适应性而备受关注。大多数人都认为在不需要事务化处理的情况下，MySQL是管理内容最好的选择。

Linux作为操作系统，Apache 或Nginx作为 Web 服务器，MySQL 作为数据库，PHP/Perl/Python作为服务器端脚本解释器。由于这四个软件都是免费或开放源码软件，因此使用这种方式可以建立起一个稳定、免费的网站系统，被业界称为“LAMP”或“LNMP”组合。

5.1.3 Oracle简介

ORACLE数据库系统是美国ORACLE（甲骨文）提供的以分布式数据库为核心的一组软件产品，它在数据库领域一直处于领先地位。作为一个通用的数据库系统，它具有完整的数据管理功能；作为一个关系数据库，它是一个完备关系的产品；作为分布式数据库它实现了分布式处理功能。

它具有系统可移植性好、使用方便、功能强等特点，适用于各类大、中、小、微机环境。它是一种高效率、可靠性好的、适应高吞吐量的数据库方案。

5.1.4 MySQL的优势

与其他的大型数据库，例如 Oracle、DB2、SQL Server等相比，MySQL 自有它的不足之处，但是这丝毫也没有减少它受欢迎的程度。对于一般的个人使用者和中小型企业来说，MySQL提供的功能已经绰绰有余，而且由于 MySQL是开放源码软件，因此可以大大降低总体拥有成本。

与Oracle相比，MySQL的主要优势如下。

（1）体积小、速度快、总体拥有成本低，开源；

（2）支持多种操作系统；

（3）开源数据库，提供的接口支持多种语言连接操作；

（4）数据库的安装配置都非常简单、性能出色；

（5）数据库的插入和查询性能都非常的高效；

（6）通过MySQL的简单复制功能，可以很好的将数据从一台主机复制到另外一台。

项目实施

采用MySQL实现论坛系统数据库，需要分析论坛系统需求、建立数据库模型。

需要完成的任务

• 安装与配置MySQL
• 创建数据库、插入测试数据
• 管理MySQL数据库

5.2 任务1：数据库的设计和实现

网络论坛系统主要是一个基于Web的应用，后台系统的开发拟采用PHP语言，由于MySQL数据库的安装配置非常简单，使用过程中的维护也不像很多大型商业数据库管理系统那么复杂，而且性能出色等原因，本项目选用MySQL作为网络论坛系统的数据库管理系统。

5.2.1 论坛数据库的设计

1.数据库概念结构设计

数据库的概念结构可以以一组实体-关系（Entity-Relationship，E-R）图形式表示。概念结构设计侧重于数据内容的分析和抽象，以用户的观点描述应用中的实体以及实体间的联系。实体是一个具有相同属性的实体集合，由一个实体型名字和一组属性来定义，也称为实体模式。实体与实体间的对应关系。联系分为以下3种类型：

①一对一联系（1:1）

如果实体集A中每个实体之多和实体集B中一个实体有联系，反之亦然，则称实体集A和实体集B为一对一的联系，记作1:1。

②一对多联系（1:N）

如果实体集A中每个实体可以和实体集B中任意个（零个或者多个）有联系，而实体集B中每个实体之多和实体集A中一个实体有联系，则称实体集A和实体集B为一对多的关系，记作1:N。

③多对多联系（M:N）

如果实体集A中每个实体可以与实体集B中任意个（零个或者多个）实体有联系，反之亦然，则称实体集A和实体集B为多对多的联系，记作M:N。

通过对论坛基本功能分析，可以建立一个基本概念模型，确定实体有用户、版块、帖子和评论。

其中用户实体E-R图如图5-1所示，其属性包括用户ID、昵称、账户、密码、性别、邮箱、注册时间、头像、电话及用户类型。

版块实体E-R图如5-2图所示，其属性包括版块ID、版块名称、版块描述和版主ID。

帖子实体E-R图如图5-3所示，其属性包括帖子ID、帖子标题、帖子内容、修改时间、发表时间、所属版块、发帖用户、浏览次数、是否为热门贴等属性。

评论实体E-R图如图5-4所示，其属性包括评论帖子的ID、评论用户ID、发表时间按、修改时间、评论内容、评论ID。

实体之间的关系E-R模型如图5-5所示，用户和帖子之间是1:N的关系，一个用户可以发表多个帖子。帖子和评论是1:N的关系，一个帖子下可以有多条评论。一个用户可以发表多条评论。一个版块的版主只能有一个，一个用户可以是多个版块的版主。一个帖子也只能属于一个版块。

2.数据库逻辑结构设计

数据库的逻辑结构设计是将数据库概念结构转换能成用二维表形式表示的逻辑结构，用二维表来表示用户、版块、帖子和评论实体，表名分别为users、boards、posts和comments。

本系统采用MySQL作为数据库管理软件。MySQL基本的数据类型包括数值型、字符串型、时间日期类型、复合型、二进制型。

① 数值型又分为整数型和小数型。

MySQL支持的整数类型包括tinyint、smallint、mediumint、int和bigint，其支持的大小分别为1字节、2字节、3字节、4字节和8字节。unsigned修饰符可规定字段只保存正值。

MySQL支持的三个小数类型是float、double和decimal类型。float用于表示单精度浮点数值；double用于表示双精度浮点数值；decimal用于精度要求较高的计算中。

② 字符串型

MySQL 提供了char、varchar、tinyblob、tinytext、blob、text字符串类型，存储的范围从简单的一个字符到巨大的文本块或二进制字符串数据的字符串类型

③ 日期时间型

MySQL提供日期和时间的类型分别有date、time、datetime、timestamp。它们可以被分成简单的日期、时间类型，和混合日期、时间类型。

④ 复合类型

MySQL 还支持两种复合数据类型 ENUM 和 SET，它们扩展了 SQL 规范。虽然这些类型在技术上是字符串类型，但是可以被视为不同的数据类型。一个 ENUM 类型只允许从一个集合中取得一个值；而 SET 类型允许从一个集合中取得任意多个值。

⑤ 二进制类型

二进制类型是在数据库中存储二进制数据的数据类型。二进制类型包括BINARY、VARBINARY、BIT、TINYBLOB、BLOG、MEDIUMBLOB和LONGBLOG。

选择合适的数据类型应遵循以下原则：在符合应用要求（取值范围、精度）的前提下，尽量使用“短”数据类型；数据类型越简单越好；尽量采用精确小数类型（例如decimal），而不采用浮点数类型；在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储日期和时间。

在表结构设计的过程中，还不得不考虑字段的约束和表约束，下列是关于MySQL几种字段约束及表约束的介绍。

① 非空约束（NOT NULL）

非空性是指字段的值不能为空值（NULL）。非空约束将保证所有记录中该字段都有值。如果用户新插入的记录中，该字段为空值，则数据库系统会报错。

② 主键约束（PRIMARY KEY）

一个表通常可以通过一个字段（或多个字段组合）的数据来惟一标识表中的每一行，这个字段（或字段组合）被称为表的主键（Primary key）。主键可以为表级约束也可以为列级约束。主键约束通过不允许一个字段（或多个字段组合）输入重复的值来保证一个表中所有行的惟一性，使所有行都是可以区分的；一个表只能有一个主键，且构成主键的字段的数据不能为空（NULL）值。

③ 唯一约束（UNIQUE）

唯一性是指所有记录中该字段的值不能重复出现。唯一性约束将保证所有记录中该字段的值不能重复出现。

④ 自增约束（AUTO_INCREMENT）

AUTO_INCREMENT是MySQL数据库中一个特殊的约束条件。其主要用于为表中插入的新记录自动生成惟一的ID。一个表只能有一个字段使用AUTO_INCREMENT约束，且该字段必须为主键的一部分。AUTO_INCREMENT约束的字段可以是任何整数类型（TINYINT、SMALLINT、INT、BIGINT等）。

⑤ 默认值约束（DEFAULT）

在创建表时可以指定表中字段的默认值。如果插入一条新的记录时没有为这个字段赋值，那么数据库系统会自动为这个字段插入默认值。

⑥ 外键约束（FOREIGN KEY）

如果表A的主关键字是表B中的字段，则该字段称为表B的外键，表A称为主表，表B称为从表。创建外键应满足的几个条件：数据类型匹配；长度相等；位于同一数据库；主表有主键约束或唯一性约束。

根据论坛系统的概念结构对数据的逻辑结构进行设计，用户表、版块表、帖子表和评论表分别如表5-1、5-2、5-3和5-4所示。

5.2.2 MySQL的安装

MySQL可运行在不同的操作系统下，本项目采用CentOS系统的服务器，在CentOS 7系统中默认的数据库是MariaDB，通过yum命令安装MySQL前，需要从官网上下载yum资源包。

1.使用yum命令安装MySQL

（1）使用wget下载工具从MySQL官方网站下载Yum资源包，具体的命令如下。

wget http://repo.mysql.com/mysql-community-release-el7.rpm

（2）用rpm套件管理方式来安装资源包，具体安装的命令如下。

rpm -ivh mysql-community-release-el7.rpm

（3）使用下列命令更新yum源。

yum update

（4）使用yum命令安装mysql-server，安装配置默认，具体命令如下。

yum –y install mysql-server

（5）通常情况下需要启动MySQL服务，并配置MySQL服务开机自启动，具体命令和注释如下。

[root@localhost ~]#systemctl start mysqld
[root@localhost ~]#systemctl enable mysqld
#停止MySQL服务：systemctl stop mysqld

（6）连接MySQL服务器root账户的初始密码可以通过下列命令进行查看。本例命令执行显示结果如5-6图所示，初始密码为gQNQld(jh7TX。

grep “password” /var/log/mysqld.log

（7）MySQL安装完成后，需要使用mysql_secure_installation命令修改MySQL服务器root账户的密码，具体的命令和配置过程如下。

mysql_secure_installation
Enter current password for root (enter for none): # 此处输入数据库超级管理员root的密码(注意不是系统root的密码)，第一次进入还没有设置密码则直接回车
Set root password? [Y/n] 	# 是否设置密码，输入y
New password: 	# 输入新密码
Re-enter new password: 	# 再次输入确认密码
Remove anonymous users? [Y/n] 	# 是否移除匿名用户
Disallow root login remotely? [Y/n] 	# 是否拒绝root远程登录
Remove test database and access to it? [Y/n] 	#是否删除test数据库
Reload privilege tables now? [Y/n] 	#是否重新加载权限表

2.验证MySQL安装

（1）使用mysqladmin命令来检查服务器的版本，如果该命令执行后输出该系统的版本信息，说明MySQL安装成功；如果未输出信息，说明MySQL未安装成功，具体的命令如下。

mysqladmin --version

（2）CentOS 7系统中安装MySQL，配置文件是默认位于/etc/下的my.cnf文件。在生产环境中，因此要对一些参数进行调整优化使用cat命令查看，具体的命令如下。

cat /etc/my.cnf

（3）默认的配置文件信息及注释如下。

[mysqld]  	# 服务端基本配置
datadir=/var/lib/mysql	#默认的数据存储目录
socket=/var/lib/mysql.sock 	#客户端程序和服务器之间通讯的套接字
log_error = /data/mysql/mysql.err	 #记录错误日志文件
pid-file = /data/mysql/mysql.pid 	 #pid所在的目录

5.2.3 MySQL的基本管理

MySQL配置好后，用户需要使用账号、密码、主机地址等信息连接到MySQL服务器后才可以进行数据库的管理。

1.连接MySQL服务器

连接MySQL实例需要使用mysql工具，它提供连接MySQL实例的命令，命令的基本格式如下。

mysql –h<主机地址> -u<用户名> -p<用户密码> –P< 端口>

（1）默认配置下连接本机的MySQL服务，-h参数为localhost和-P为3306，具体连接到本主机的MySQL服务的命令如下。

mysql –hlocalhost –uroot –pnew_password –P3306

（2）连接成功后，输出显示如图5-7所示，“mysql >”表示MySQL准备好接受命令。一条SQL命令默认以分号结束。若执行结果显示error，表明SQL命令执行未成功，请检查拼写、英文符号等错误，修改后重新执行。

2.MySQL的基本操作

MySQL命令通常由SQL语句组成，以分号结束。回车执行命令，显示执行结果后，执行结果由行和列组成的表显示，最后一行显示返回此命令影响的行数以及执行的时间。不必全在一行给出一个命令，可以输入到多行中，系统见到“;”开始执行，使用exit命令可退出连接。

（1）通过命令查询服务器版本号，具体的查询命令如下。

mysql > select version();           #查看MySQL的版本信息

（2）查看当前系统时间，具体的查询命令如下。

mysql > select now();              #查看系统当前时间

（3）查看当前系统当中的数据库，具体的命令和显示结果如下。

mysql> show databases;
+--------------------+
| Database             |
+--------------------+
| information_schema |
| mysql                 |
| performance_schema |
| sys                    |
+--------------------+

①information_schema是信息数据库，其中保存着关于服务器所维护的所有其他数据库的信息。

②mysql数据库主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息。

③performance_shema数据库主要用于收集数据库服务器性能参数。可用于监控服务器在一个较低级别的运行过程中的资源消耗、资源等待等情况。

④sys数据库库中所有的数据源来自performance_schema数据库，其存在的目的是把performance_schema的把复杂度降低，让数据库管理员能更好的阅读这个库里的内容，更快的了解数据库的运行情况。

5.2.4 数据库和表的创建

（1）为建立数据库存放表及其对象，创建数据通常可使用CREATE DATABASE或CREATE SCHEMA，该语句的基本语法格式如下。

CREATE { DATABASE | SCHEMA} [IF NOT EXISTS] db_name;

其中，db_name表示数据库名；IF NOT EXISTS表示在建数据库前进行判断，只有该数据库目前尚不存在时才执行创建数据库的操作用，此选项可以避免出现数据库已经存在而再新建的错误。

（2）通过下列语句可查看当前存在的数据库。

SHOW DATABASES;

（3）若需要查看某数据库db_name详细情况，其基本语法格式如下。

SHOW CREATE DATABASE db_name;

（4）删除数据库是指在数据库系统中删除已经存在的数据库。删除数据库之后，原来分配的空间将被收回。值得注意的是，删除数据库会删除该数据库中所有的表和所有数据，删除数据库的基本语法格式如下。

DROP DATABASE [IF EXISTS] db_name ;

其中，db_name为数据库名；IF EXISTS表示在删除数据库之前，系统会先判断数据库是否存在，只有数据库存在的情况下才会执行删除数据库的操作，用此选项可以避免出现数据库不存在时，SQL语句执行返回错误。

（5）选中数据库，系统当中可能存在多个数据库，接下来的命令操作是需要对哪个数据库进行操作，需要在执行SQL语句之前对数据库进行选中，其基本语法格式如下。

USE db_name ;

（6）创建数据表可以使用CREATE TABLE语句，其基本语法格式如下。

CREATE  [TEMPORARY] TABLE [IF NOT EXISTS] table_name
[  (  [ column_definition ], … |  [ index_definition ]  ) ]
[table_option]; 

其中，如果选择TEMPORARY参数，表示该表为临时表，临时表将在连接MySQL期间存在，当断开连接时，MySQL将自动删除表并释放所用的空间；如果选择IF NOT EXISTS参数，表示在建数据库前进行判断，只有该数据库目前尚不存在时才执行创建数据库的操作；table_name表示创建表的表名；column_definition表示列的定义，包括列名，数据类型，列级别的约束条件和默认值等。index_definition表示索引的定义；table_option表选项可以对表的引擎，字符集，字符序，描述等选项进行设置。

（7）查看表结构。查看表结构是指查看数据库中已存在的表的定义。DESCRIBE语句的语法形式如下。

DESCRIBE | DESC table_name;

（8）SHOW CREATE TABLE语句可以查看表的详细定义, 该语句可以查看表的字段名、字段的数据类型、完整性约束条件等信息。除此之外，还可以查看表默认的存储引擎和字符编码。SHOW CREATE TABLE语句的语法形式如下。

SHOW CREATE TABLE table_name;

（9）修改表结构的基本语法格式如下。

ALTER  TABLE  table_name 
ADD  [COLMUN]  column_definition  [ FIRST | AFTER col_name]
[RENAME  [TO]  new_table_name]
[CHANGE  column_name  new_column_name]
[MODFIY  column_name]
[DROP  column_name]

ADD表示增加字段，column_definition是列的定义，通过FIRST | AFTER选项可以指定插入的字段的位置；RENAME可对表明进行更改；CHANGE和MODIFY可以修改字段的名字及其定义；DROP则可以删除字段。

（10）删除表

删除表是指删除数据库中已存在的表。删除表时，会删除表中的所有数据。因此，在删除表时要特别注意。MySQL中通过DROP TABLE语句来删除表，其具体的语法格式如下。

DROP TABLE [IF EXISTS] table_name1,table_name2,...;

创建网络论坛系统数据库forum及个表的SQL语句集合如下所示，本例将其保存为文件forum.sql。InnoDB表示的是一种MySQL存储引擎，InnoDB存储引擎是支持事务，并且支持外键（Foreign key）。

数据库及表结构创建的SQL语句集合 forum.sql

CREATE DATABASE forum; 	#创建数据库forum
USE forum;	#选中数据库forum
CREATE TABLE users(	#创建users表
        user_id INT PRIMARY KEY AUTO_INCREMENT,	#用户ID字段
        user_nickname VARCHAR(50) NOT NULL UNIQUE,	#用户昵称字段
        user_account VARCHAR(50) NOT NULL UNIQUE,	#用户账户字段
        user_password VARCHAR(50) NOT NULL,	#用户密码字段
        user_sex VARCHAR(10) NOT NULL,	#用户性别字段
        user_face VARCHAR(255) DEFAULT NULL,	#用户头像字段
        user_regtime DATETIME DEFAULT  CURRENT_TIMESTAMP NOT NULL,#用户注册时间字段
        user_email VARCHAR(20) DEFAULT NULL UNIQUE,	#用户Email字段
        user_mobile VARCHAR(20) DEFAULT NULL UNIQUE,	 #用户电话字段
        user_type TINYINT NOT NULL	#用户类型字段
); 

CREATE TABLE boards(	#创建boards表
        board_id INT PRIMARY KEY AUTO_INCREMENT,	#版块ID字段
        board_name VARCHAR(50) NOT NULL UNIQUE,	#版块名称字段
        board_description VARCHAR(255),	#版块描述字段
        moderatorid INT NOT NULL,	#版主ID字段
        FOREIGN KEY(moderatorid) REFERENCES users(user_id)	#版主ID字段参照用户表中的user_id字段
    ); 

CREATE TABLE posts(	#创建posts表
        post_id INT PRIMARY KEY AUTO_INCREMENT,	#帖子ID字段
        post_title VARCHAR(50) NOT NULL,	#帖子标题字段
        post_content TEXT NOT NULL,	#帖子内容字段
        post_mtime DATETIME DEFAULT NULL,	#帖子修改时间字段
        post_ptime DATETIME DEFAULT  CURRENT_TIMESTAMP NOT NULL, #帖子发表时间字段
        post_browsenum INT DEFAULT 0 NOT NULL,	 #帖子浏览次数字段
        post_ishot INT DEFAULT 0 NOT NULL,	#帖子是否为热门帖
        boardid INT NOT NULL, 	#帖子所在版块ID
        userid INT NOT NULL,	#发帖用户的ID
        FOREIGN KEY(boardid) REFERENCES boards(board_id),#帖子所在版块ID参照boards表中的board_id字段
        FOREIGN KEY(userid) REFERENCES users(user_id)	#发帖用户userid字段参照users表中的user_id字段
);

CREATE TABLE comments(	#创建评论表
        comment_id INT PRIMARY KEY AUTO_INCREMENT,	#评论ID字段
        comment_mtime DATETIME DEFAULT NULL,	#评论修改时间字段
        comment_ptime DATETIME DEFAULT  CURRENT_TIMESTAMP NOT NULL,	#评论发表时间字段
        comment_content TEXT NOT NULL,	#评论内容字段
        postid INT NOT NULL, 	#评论对应的帖子ID
        userid INT NOT NULL,	#评论所属用户ID
        FOREIGN KEY(postid) REFERENCES posts(post_id),#评论对应的postid参照posts表的post_id字段
        FOREIGN KEY(userid) REFERENCES users(user_id)	#评论所属用户的ID参照users表中的user_id字段
);

直接将以上SQL语句通过复制粘贴到MySQL连接会中执行。

数据库创建好后，数据库管理员如发现有错误或者需要更改表结构，可以使用ALTER TABLE对表结构进行更改。

例如修改posts表中post_ishot字段名为post_is_host，具体的实现过程如下。

mysql> ALTER TABLE posts CHANGE post_ishot post_is_hot tinyint 
-> DEFAULT 0 NOT NULL;

修改user_sex字段的数据类型为ENUM类型，并指定选项为f和m，具体的实现过程如下。

mysql> ALTER TABLE users MODIFY user_sex ENUM('f','m') NOT NULL;

5.2.5 测试数据的插入

​ 数据库和表结构创建完成，日常对数据进行的操作包括数据的增删该查。

1.插入数据

INSERT用于向一个已有的表中插入新行。INSERT…VALUES语句根据明确指定的值插入行。

（1）向表中插入数据的时候，可以不指定具体的字段名，具体的INSERT基本语法格式如下。

INSERT INTO table_name VALUES(value1，value2，…，valuen) ;

其中，table_name表示表名；value1、value2、valuen为插入对应列的值。如果表中包含n个字段，那么INSERT语句中对应的列值也应该是n个。

（2）INSERT语句还可以选择字段插入对应的数据，基本语法格式如下。

INSERT INTO table_name(col_name1,col_name2,col_namen)  
   VALUES(value1，value2，…，valuen);

其中，col_name1、col_name2、col_namen表示指定插入哪些字段，用如果需要插入多个字段，字段名之间用逗号隔开。

如果表的字段比较多，用第二种方法就比较麻烦。但是第二种方法比较灵活，可以随意的设置字段的顺序，不需要按照表定义时字段的顺序，当然，值的顺序也必须随着字段顺序的改变而改变。

（3）向MySQL的某个表中插入多条记录时，可以使用多个INSERT语句逐条插入记录，也可以使用一个INSERT语句插入多条记录。同时插入多条记录的INSERT基本语法格式如下。

INSERT INTO table_name[(col_namelist)]                                         VALUES(valuelist)，(valuelist)，…(valuelist)；

其中，table_name表示表名；col_namelist表示字段名列表，多个字段名使用逗号隔开；valuelist表示需要插入的对应col_namelist中字段名的字段值，即一行记录，多个valuelist同时插入，只需要用逗号隔开。如果插入的数据很多时，一个INSERT语句插入多条记录的方式速度会比较快。

由于在定义表结构的时候，对字段有PRIMARY KEY、NOT NULL、UNIQUE、AUTO_INCREMENT等约束，所以在插入的数据的时候和这些约束冲突，MySQL会提示插入失败，通常情况下：

① 对于AUTO_INCREMENT字段插入NULL值，让系统自动去维护。

② 对于默认值字段，如果插入DEFAULT，即表示插入该字段的默认值。

③ 如果表示该值未知，通常插入NULL值而不是0或者空字符串，对于字段约束不能为空的字段，则不能插入NULL值。

④ 对于具有外键约束的字段，例如帖子表posts中userid字段的值必须是用户表users中user_id这一列存在的值。

2.数据的修改

UPDATE的功能是更新表中的数据。它的基本语法给是和INSERT的第二种用法相似。必须提供表名以及SET表达式，在后面可以加WHERE以限制更新的记录范围。

UPDATE table_name SET column_name1=value1,column_name2=value2,…
[WHERE条件表达式];

其中，table_name表示表名称，column_name表示需要修改的字段名，value表示需要修改的字段名对应的字段值。UPDATE可以同时更新多个字段的数据，以逗号隔开。如果使用WHERE子句，则UPDATE只会修改满足WHERE关键字后面的条件表达式结果为TRUE的那些行对应的字段值。

3.数据的删除

删除数据是删除表中已经存在的记录。在MySQL中，通过DELETE语句来删除行记录。

DELETE FROM table_name [WHERE条件表达式];

其中，table_name表示待删除记录的所在的表名；同UPDATE语句一样，DELETE语句同样可以选择使用WHERE子句，如果使用WHERE子句，则DELETE只会删除满足WHERE关键字后面的条件表达式结果为TRUE的那些行对应的记录。DELETE语句中如果不加上“WHERE条件表达式”，数据库系统会删除指定表中的所有数据。

4.插入测试数据

向users表插入3个用户admin、user1和user2，具体的SQL语句如下，SET names utf8表示设置默认字符集为utf8，字符集用来定义MySQL存储字符串的方式。

SET names utf8;
USE forum;
INSERT INTO users VALUES
(NULL,'admin','admin',md5('admin'),'m', NULL,DEFAULT, NULL, NULL,1),
(NULL,'user1','user1',md5('user1'),'m', NULL,DEFAULT, NULL, NULL,2),
(NULL,'user2','user2',md5('user2'),'f', NULL,DEFAULT, NULL, NULL,2);

向boards表插入测试数据，具体的SQL语句如下。

USE forum;
INSERT INTO boards VALUES
(1,'Technology',NULL,1),
(2,'News',NULL,1),
(3,'Entertainment',NULL,1);

向posts表插入测试数据，具体的SQL语句如下。

USE forum;
INSERT INTO posts(post_title,post_content,boardid,userid) VALUES
('testtitle1', 'testcontent1',1,2),
('testtitle2', 'testcontent2',2,2),
('testtitle3', 'testcontent3',3,3);

向Comment表插入测试数据，具体的SQL语句如下。

USE forum;
INSERT INTO comments(comment_ptime,comment_content,postid,userid) VALUES
(DEFAULT, 'testcomment1',1,3),
(DEFAULT, 'testcomment2',2,2),
(DEFAULT, 'testcomment2',3,2);

帖子的浏览次数可以使用以下的语句实现。

UPDATE posts SET post_browsenum=post_browsenum+1 where post_id=1;

5.2.6 数据的查询

数据的查询使用SELECT语句，其基本语法格式如下，SELECT语句后可以是表达式，也可以是函数。

SELECT  [ALL | DISTINCT]  <目标列表达式> 
FROM <表名或视图名> 
[WHERE <条件表达式>]
[GROUP BY <列名> ]
[HAVING <条件表达式> ]
[ORDER BY <列名2> [ ASC| DESC ]] 
[LIMIT 子句] 

1.查询所有字段

查询所有字段是指查询表中的所有字段的数据可以使用通配符“*”来查询，SQL语句基本格式如下，其中table_name为要查询表的表名。

SELECT * FROM table_name

2.查询指定字段

虽然通过SELECT语句可以查询所有字段，但有些时候，并不需要将表中的所有字段都显示出来，只需要查询需要的字段就可以了。SQL语句基本格式如下，其中table_name为要查询表的表名，table_name表示表名，colum_name_list表示要查询的字段，如果需要查询多个字段的数据，则用逗号将列名隔开。

SELECT column_name[,column_name…] FROM table_name

使用第2种方式只要列出该表所有的字段，也可以查询该表所有字段。通过第1种方式查询所有字段比较简单，尤其是数据库表中的字段很多时，这种方式更加明显。但是从显示结果顺序的角度来讲，使用通配符*不够灵活。如果要改变显示字段的顺序，可以选择使用第2种方式。

3.DISTINCT避免重复数据

DISTINCT关键字可以去除重复的查询记录。和DISTINCT相对的是ALL关键字，即显示所有的记录（包括重复的），而ALL关键字是系统默认的。使用DISTINCT去除重复的记录的查询语句基本格式如下。

SELECT DISTINCT(column_name) FROM table_name

4.为表或字段取别名

有时为了显示结果更加直观，需要一个更加直观的名字来表示这一列，而不是用数据库中的字段名。可以使用AS关键字为列字段取别名，其中column_name为字段名，column_alias表示字段别名，table_alias表示表别名，SELECT基本语法格式如下。

SELECT column_name AS column_alias FROM table_name AS table_alias

5.WHERE子句

WHERE子句通常是条件表达式，条件表达式的左边操作数和右边操作数进行比较表达式，比较结果为真返回1，为假返回0，不确定返回NULL。SELECT语句配合WHERE子句使用，可查询出满足WHERE条件表达式结果为真的数据行，基本语法格式如下，conditional_expresssion为条件表达式。

SELECT … FROM … WHERE conditional_expresssion

6.使用集合函数查询

MySQL提供一些集合函数简单分析、统计查询数据，这些函数包括COUNT()、SUM()、 AVG()、MAX()和MIN()。这些集合函数的功能为：

① COUNT()函数功能为统计记录的条数；

② SUM()函数功能为计算字段的值的总和；

③ AVG()函数功能为计算字段的值的平均值；

④ MAX()函数功能为查询字段的最大值；

⑤ MIN()函数功能为查询字段的最小值。

7.使用GROUP BY分组查询

分组查询是对数据按照某个或某多个字段进行分组，MySQL中使用GROUP BY关键自对数据进行分组，其基本语法格式如下。

GROUP BY 字段 [HAVING <条件表达式>]

GROUP还可以和集合函数一起使用。GROUP BY和HAVING可以一起限定显示记录所需满足的条件，只有满足条件的分组才会被显示。HAVING在数据分组之后进行过滤来选择分组，而WHERE在分组之前用来选择记录，WHERE排除的记录不再包括再分组中。

8.ORDER BY

MySQL可以通过再SELECT语句中使用ORDER BY子句，对查询结果进行排序，其基本语法格式如下。

ORDER BY column_name [ASC|DESC]

默认情况下，查询数据按升序（ASC）进行排序，若是用关键字DESC，可以让查询数据按降序进行排序。

9.LIMIT限制查询结果的数量

使用SELECT返回匹配的行时，如果只需要返回第一行或者前几行，可以使用LIMIT关键字，基本语法格式如下。

LIMIT [offset,]rows

offset表示位置偏移量，表示数据从哪一行开始显示，是一个可选参数。如果不指定offset，将会从表中的第1行记录开始显示（第1行记录的位置偏移量为0）；rows表示返回的记录的条数。

10.JOIN基于多表的查询

当FROM子句指定两张或两张以上的表时，叫做表的联接（JOIN），进行比较的两列叫做联接条件。所有的属性都可以指定其来自于那个表，这可以提高查询的可读性，其基本语法格式如下。

SELECT column_name_list 
FROM table_name1 JOIN table_name2
ON table_name1.column_name=table_name2.column_name

查询浏览量大于0的帖子内容且按浏览量的降序进行排序。

mysql> SELECT * FROM posts WHERE post_browsenum>0 ORDER BY  
-> post_browsenum\G
*************************** 1. row ***************************
       post_id: 1
    post_title: testtitle1
  post_content: testcontent1
    post_mtime: NULL
    post_ptime: 2020-05-15 08:26:40
post_browsenum: 1
   post_is_hot: 0
       boardid: 1
        userid: 2
1 row in set (0.00 sec)

查询热门帖子，即post_is_host为1的帖子标题和内容。

mysql> SELECT DISTINCT(comment_content) FROM comments;
+-----------------+
| comment_content |
+-----------------+
| testcomment1    |
| testcomment2    |
+-----------------+
2 rows in set (0.00 sec)

查询评论信息并去除查询结果中的重复评论。

mysql> SELECT post_title,post_content FROM posts WHERE 
-> post_is_host=1;

查询post_id为2的前两条评论

mysql> SELECT * FROM comments WHERE postid=1 LIMIT 0,2\G
*************************** 1. row ***************************
     comment_id: 1
  comment_mtime: NULL
  comment_ptime: 2020-05-15 08:26:44
comment_content: testcomment1
         postid: 1
         userid: 3
1 row in set (0.00 sec)

查询每个用户发表的帖子数大于等于2的用户昵称，性别及其发贴数。

mysql> SELECT user_nickname,user_sex,COUNT(*) AS 发帖数 FROM 
-> users JOIN posts 
-> ON users.user_id=posts.userid 
-> GROUP BY users.user_id HAVING COUNT(*)>=2;
+------------------+-----------+----------------+
| user_nickname     | user_sex  | 发帖数            |
+------------------+-----------+----------------+
| user1              | m           |                2  |
+------------------+-----------+----------------+
1 rows in set (0.00 sec)

5.3 任务2：数据库的管理

5.3.1 数据库权限管理

MySQL服务通过权限表来控制用户对数据库的访问，权限表存放在系统数据库mysql中。MySQL提供了许多语句来管理用户帐号，包括登录和退出MySQL服务、创建用户、删除用户和权限管理等内容。

1.用户管理

在MySQL的日常管理中，通常需要创建一系列具有适当权限的用户而尽可能的避免恶意用户使用root来操作控制数据库。

（1）使用CREATE USER语句创建新用户

CREATE USER语法格式如下。

CREATE USER ‘user’@‘host’ IDENTIFIED BY 'auth_string'

① user表示创建的用户的名称。

② host表示允许登录的主机名称。

③ IDENTIFIED BY表示用来设置用户的密码。

使用CREATE USER语句创建用户后，在mysql数据库的user表中会新增一条记录，但是新建的用户没有任何权限，如果添加的用户已经存在，CREATE USER语句也会返回一个错误。密码如果不满足复杂性要求，也会返回创建用户失败。也可以使用IDENTFIED BY RANDOM PASSWORD的方式创建随机密码，如图5-8所示，语句执行成功后返回系统生成的随机密码go:CP5tYy@vj>DcyaTIh。

（2）使用DROP USER语句删除用户

在MySQL数据库中，可以使用DROP USER语句删除用户，其语法格式如下。

DROP USER ‘user’@‘host’

DROP USER语句可以删除一个或多个MySQL用户。

（3）root用户修改用户的密码

root用户默认拥有最高权限，可以修改其他用户的密码，基本语法如下。

SET PASSWORD FOR ‘user’@‘host’ = ‘new_password’

（4）root和普通用户修改自己的密码

用户连接到MySQL服务以后，可以通过SET语句设置自己的密码，具体的基本语法如下。

SET PASSWORD = ‘new_password’

其中，new_password是设置的新密码。

2.MySQL权限

MySQL判断该用户可以正常登录后，需要再对该拥护的权限进行验证。所有用户的权限都存储在MySQL的权限表中中。为了避免给MySQL服务器带来安全隐患，数据库管理员需要对用户的权限进行合理规划。MySQL的权限系统主要是验证连接到一台给定主机的用户，并且赋予该用户在数据库上的SELECT、INSERT、UPDATE和DELETE等权限，常用的权限、在权限表中对应的列及权限范围的说明如表5-5所示。

① CREATE权限。可以创建新数据库、表或索引，如果将CREATE权限授予某个用户，则该用户可以创建数据库、表或索引。

② DROP权限。可以创建数据库、表或视图。

③ SELECT、INSERT、UPDATE和DELETE权限分别允许在一个数据库现有的表上实施查询、添加、更新和删除操作。

④ ALTER权限。可以使用ALTER TABLE更改表结构和重新命名表。

⑤ GRANT权限。允许用户将权限授权给其他用户。

除此以外，MySQL还有很多的关于视图、存储过程等权限限制以及用于限制用户的管理性操作的权限。

MySQL的这些权限在授予和收回的时候可以分为多个层级。

① 全局层级

全局层级权限适用于一个给定服务器的所有数据库，这些权限存储在mysql数据库的user表中。

② 数据库层级

数据库层级权限适用于一个给定数据库中的所有目标，这些权限存储在mysql数据库的db表中。

③ 表层级

表层级权限适用于一个给定表中的所有列，这些权限存储在mysql数据库的tables_priv表中。

④ 列层级

列层级权限适用于一个给定表中的单一列，这些权限存储在mysql数据库的columns_priv表中。

⑤ 子程序层级

子程序层级权限适用于已存储的子程序。这些权限可以被授予全局层级和数据库层级，这些权限存储在mysql数据库的procs_priv表中。

在MySQL中，拥有GRANT权限的用户可以为用户授予权限，REVOKE收回权限、SHOW GRANTS查看权限，合理的权限可以保证数据库的安全。

（1）使用GRANT语句为用户赋予权限

使用GRANT语句为用户赋予权限的基本语法结构如下。

GRANT priv_type [(column_list)][, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user [, user] ...
    [WITH GRANT OPTION]

其中：

① priv_type表示权限类型。

② column_list表示权限作用于哪些列上，如果不指定，表示作用于整个表。

③ object_type表示指定授权作用的对象类型，包括TABLE、FUNCTION、PROCEDURE，分别代表表、函数、存储过程。

④ priv_level表示指定授予权限的数据库和表，可以是以下某种形式。

*.*	#所有库和所有表
db_name.*	#数据库db_name下的所有表
db_name.tbl_name	#数据库db_name下的tbl_name表

⑤ user表示用户帐户，由用户名和主机名（或IP地址）构成，形式如下。

‘username’@’localhost’

⑥ WITH GRANT OPTION表示允许将自己的权限赋予其他用户。

（2）使用REVOKE语句为收回用户权限

收回权限即取消已经赋予用户的权限。REVOKE有两种语法格式，第一种方式可以收回指定的权限，其基本语法如下：

REVOKE
    priv_type [(column_list)]
    [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user [, user] ...

第二种是收回所有用户的所有权限，此语法可以取消用户的所有全局层级、数据库层级、表层级和列层级的权限，其语法格式如下：

REVOKE ALL [PRIVILEGES], GRANT OPTION
    FROM user [, user] ...

其中，FROM语句可以指明需要收回权限的用户。

（3）使用SHOW GRANTS语句查看权限

SHOW GRANTS语句可以显示指定用户的权限信息，适用该语句查看用户权限信息的基本语法格式如下。

SHOW GRANTS FOR ‘user’@’host’;

3.管理数据库forum的用户权限

实际情况并不希望每个用户都可以执行所有的数据库操作，当MySQL允许一个用户执行各种操作时，它将首先核实该用户向MySQL服务器发送的连接请求，然后确认拥护的操作请求是否被允许。

（1）创建一个新用户admin，密码为随机密码，允许其从本主机访问MySQL，创建过程及返回结果如下。

mysql> CREATE USER 'admin'@'localhost' IDENTIFIED BY RANDOM PASSWORD;
+-------+-----------+----------------------+
| user  | host        | generated password   |
+-------+-----------+----------------------+
| admin | localhost | *AG2J,YxDKUDeAteLqgH |
+-------+-----------+----------------------+

（2）创建一个用户user1，密码为Forum@123,./，具体创建的命令如下。

mysql> CREATE USER 'user1'@'%' IDENTIFIED BY 'Forum@123,./';

（3）查看admin用户的权限信息，查询语句及返回结果如下，admin用户创建成功，只具有连接服务器的权限（USAGE）。

mysql> SHOW GRANTS FOR 'admin'@'localhost';
+-------------------------------------------+
| Grants for admin@localhost                   |
+-------------------------------------------+
| GRANT USAGE ON *.* TO `admin`@`localhost` |
+-------------------------------------------+

（4）赋予admin用户对于数据库forum中所有表的所有权限，并指定其可以将自己的权限赋予给其他用户，具体的命令如下。

mysql> GRANT ALL PRIVILEGES
    -> ON forum.*
    -> TO 'admin'@'localhost'
    -> WITH GRANT OPTION;

（5）查看admin用户的权限信息，查询命令及返回结果如下。

mysql> SHOW GRANTS FOR 'admin'@'localhost';
+---------------------------------------------------+
| Grants for admin@localhost                             |
+---------------------------------------------------+
| GRANT USAGE ON *.* TO `admin`@`loca lhost`           |
| GRANT ALL PRIVILEGES ON `forum`.* TO 
      `admin`@`localhost` WITH GRANT OPTION             |
+----------------------------------------------------+

（6）使用admin用户连接MySQL服务器，连接成功。

mysql> exit
Bye
[root@localhost ~]# mysql -hlocalhost -uadmin -p
Enter password:*******

（7）用GRANT语句赋予user1用户对数据库forum中所有表查看的权限，赋予其对forum数据库users表中user_face字段更新的权限，具体命令如下。

mysql> GRANT SELECT
    -> ON forum.*
    -> TO 'user1'@'%';
mysql> GRANT UPDATE(user_face)
    -> ON forum.users
    -> TO 'user1'@'%';

（8）使用user1用户连接MySQL服务器，连接成功。

mysql -hlocalhost -uuser1 -pForum@123,./

（9）修改forum数据库users表中所有记录的user_face字段值为/image/default.jpg，具体查询命令及返回结果如下。

mysql> SELECT user_nickname,user_face FROM forum.users;
+---------------+--------------------+
| user_nickname | user_face            |
+---------------+--------------------+
| admin           | /image/default.jpg |
| user1           | /image/default.jpg |
| user2           | /image/default.jpg |
+---------------+--------------------+

（10）使用root用户连接到MySQL服务器。

mysql> exit
Bye
[root@localhost ~]# mysql -hlocalhost -uroot -p
Enter password:*******

（11）收回user1用户UPDATE权限，具体命令如下。

mysql> REVOKE UPDATE
    -> ON forum.users
    -> FROM 'user1'@'%';

（12）收回admin用户的所有权限，具体命令如下。

mysql> REVOKE ALL
    -> ON forum.*
    -> FROM 'admin'@'localhost';

（13）删除admin和user1用户，具体命令如下。

mysql> DROP USER 'admin'@'localhost','user1'@'%';

（14）使用SELECT语句查看mysql数据库中的user表，显示用户已经被删除，具体查询命令及返回结果如下。

mysql> SELECT user,host FROM mysql.user;
+------------------+-----------+
| user                | host       |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session     | localhost |
| mysql.sys          | localhost |
| root                | localhost |
+------------------+-----------+

5.3.2 数据库备份与恢复

系统以为崩溃或者硬件的损坏可能会导致数据丢失，因此数据库管理员应该定期的备份数据，使得在意外情况出现时，尽可能的减少损失。

1.使用mysqldump工具备份

mysqldump工具是一个MySQL数据库自带的一个备份工具，此工具提供数据库备份命令。它支持数据库全备也可以指定库进行备份，它备份的文件以文件形式备份并且文件内容都是SQL语句。

mysqldump备份数据库语句的基本语法格式如下：

mysqldump [options] -u user -h host -p dbname[tbname[, tbname…]] 
> filename.sql

其中，user表示用户名；host表示主机名；password为登陆密码；dbname为需要备份的数据库名称；tbname为dbname数据库中需要备份的数据库表，可以指定多个表，用逗号隔开；>表示将备份数据表的定义和数据写入备份文件；filename.sql为备份文件的名称。 常见的option选项及含义如下。

① --databases，表示指定备份多个数据库。

② --all-databases，表示备份所有数据库。

③ --force，表示当出现错误时仍然继续备份操作。

④ --default-character-set，表示指定默认字符集。

⑤ --add-locks，表示备份数据库表时锁定数据库表。

2.通过直接复制数据库数据文件进行备份

因为MySQL数据库结构、表结构及数据等是以文件行的形式存储在MySQL服务器或指定的存储路径中。在Linux平台下，数据目录的位置通常为/var/lib/mysql/。因此，直接复制MySQL数据库的存储目录及文件也是一种备份方式，这种方式虽然简单，但是对于InnoDB存储引擎的表不太是适用，将备份文件复制到不同版本的MySQL服务器，可能会不兼容。数据库目录可以通过查看配置文件找到，具体查询命令如下，查询执行的结果如图5-9所示，datadir一行就定义了数据存储的路径。

cat /var/lib/mysql

3.使用mysql命令进行还原

对于使用mysqldump工具备份的情况，使用mysql工具将备份导出的sql文件导入MySQL就能实现数据库还原，具体mysql工具命令格式如下：

mysql -u user -p [dbname] < filename.sql

其中，user是可以连接到MySQL并执行脚本的用户名；-p表示用户密码；dbname是数据库名，可以省略。

4.使用SELECT INTO OUTFILE语句备份数据

使用SELECT INTO OUTFILE语句备份数据，这种方法只能导出数据的内容，不包括表的结构，如果表的结构文件损坏，必须要先恢复原来表的结构。

SELECT INTO OUTFILE的语法格式如下：

SELECT * INTO OUTFILE ‘file_name’
[CHARACTER SET charset_name] export_options
| INTO DUMPFILE ‘file_name’
FROM table_name

其中，export_options的格式如下：

FIELDS
[TERMINATED BY ‘string’]
[[OPTIONALLY] ENCLOSED BY ‘char’]
[ESCAPED BY ‘char’]]
[LINES TERMINATED BY ‘string’]

① file_name表示导出文件的名称。

② charset_name表示导出文件的字符级编码。

③ FILEDS子句：如果指定了FILEDS子句，可以通过指定TERIMINED BY，ENCLOSED BY和ESCAPED BY参数来指定导出文档中的数据字段值之间的符号，包裹文件中字符值的符号和转义字符。

④ LINES子句：在LINES子句中可以使用TERMINATED BY来指定一个数据行结束的标志。

⑤ DUMPFILE表示导出备份文件中所有的数据行都会彼此紧挨着放置，即值和行之间没有任何表记。

⑥ table_name表示需要导出数据的表的名字。

5.使用LOAD DATA方式导入文本文件

MySQL允许将数据导出到尾部文件，也支持从外部文件导入数据。MySQL提供了一些导入数据的工具，例如LOAD DATA命令。LOAD DATA工具用于高速地从一个文本文件中读取行，并装入一个表中。

导入恢复语句LOAD DATA…INFILE的语法格式如下：

LOAD DATA INFILE file_name INTO TABLE tablename [OPTIONS] [IGNORE 
number LINES] export_options

① file_name表示待导入的数据库备份文件的名称。

② table_name表示需要导入的数据表的表名。

③ replace表示当导入文件中出现与数据库中原有行相同的唯一关键字值时，输入行会替换原有行。

④ ignore表示把与原有行相同的唯一关键字的输入行跳过。

⑤ export_options的选项和说明参照SELECT INTO OUTFILE命令的该选项的说明。

6.备份和恢复forum数据库

（1）使用mysqldump将forum数据库中的posts表，备份到文件/forum_backup.sql，具体备份命令如下。

mysqldump -uroot -p forum > /forum_backup.sql

（2）以上命令执行结束之后，使用cat命令可查看forum_backup.sql内的具体内容，查看命令如下。

cat /forum_backup.sql

（3）为了验证数据还原结果，在当前数据库先删除forum数据库。登录MySQL后输入下列命令删除forum。

Drop database forum;

（4）通过mysql命令使用forum_backup.sql文件还原数据库，具体的mysql命令如下。

mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+

5.3.3 数据库状态监控

在CentOS7系统中使用以下命令查看MySQL服务的运行状态。

systemctl status mysqld

连接到MySQL后，查看MySQL运行状态也可以使用下列语句实现。

SHOW GLOBAL STATUS;

在不连接MySQL的情况下，可以使用下列mysqladmin命令查看MySQL的运行状态。

mysqladmin -uroot -p extended-stat vus

其中extended-status可以用ext进行缩写。

5.3.4 数据库性能监控与优化

MySQL的服务器状态信息，比如查看当前MySQL启动后的运行时间，MySQL的客户端会话连接数，MySQL服务器执行的慢查询数，MySQL执行了多少SELECT/UPDATE/DELETE/INSERT语句等统计信息。查看MySQL运行状态，优化MySQL运行效率，就需要使用SHOW STATUS命令。

SHOW STATUS LIKE ‘value’

其中，value是要查询的参数值，常见查询的value值如下。

① Connetcions表示连接MySQL服务器的次数。

② Uptime表示MySQL服务器上线的时间。

③ Slow_queries表示慢查询的次数。慢查询是指MySQL通过慢查询日志的方式记录了所有执行超过long_query_time参数设定的时间阈值的查询，此参数可以在my.cnf文件内设置，单位为秒，前提是需要MySQL开启慢查询日志。

④ Com_select表示查询操作的次数。

⑤ Com_insert表示插入操作的次数。

⑥ Com_update表示更新操作的次数。

⑦ Com_delete表示删除操作的次数。

⑧ Com_commit表示执行的事务提交次数。

⑨ Com_rollback表示执行的事务回滚次数。

⑩ Qcache_hits表示查询缓存命中次数。

通过这些监控这些性能参数，可以计算出例如每秒查询量（Queries Per Second，QPS）、每秒事务量（Transactions Per Second，TPS）、查询命中率等性能指标。

MySQL的性能优化包括服务器硬件优化、MySQL配置优化、数据库结构优化、查询优化等。优化的原则是减少系统瓶颈、减少资源的占用从而提高数据库整体性能，并通过合理设计结构和调整参数以提高用户操作的相应速度。

1.服务器硬件优化

服务器的硬件性能直接决定了MySQL数据库的性能，提高硬件配置可以提高数据库的查询、更新的速度。可通过提升硬件设备，例如选择高频率的内存、提升网络带宽、使用SSD高速磁盘、提升CPU性能等方法来优化MySQL服务器。其中，对于数据库并发比较高的场景，CPU的数量比频率重要；对于CPU密集型场景和频繁执行复杂SQL的场景，CPU的频率越高越好。

2.MySQL配置优化

优化MySQL的配置可以提高资源利用率，通过修改MySQL的参数配置，可以在一定程度上提高MySQL服务器的性能。配置优化的方法主要是通过在/etc/my.cnf文件mysqld组中添加或修改参数，下列是几个对MySQL服务器性能影响较大的几个参数。

（1）innodb_buffer_pool_size

此参数设置的是InnoDB类型的表和索引的最大缓存，其单位为字节，这个值越大，查询的速度越快，但是太大也会影响操作系统的性能。

（2）innodb_flush_log_at_trx_commit

此参数设置的是何时将缓冲区的数据写入日志文件，并将日志文件写入磁盘中。该参数有三个值分别为0、1和2。

该参数的值为0时表示每隔1秒中将数据写入日志文件并将日志文件写入磁盘；

该参数的值为1时表示每次提交事务时将数据写入日志文件并将日志写入磁盘，1也是该参数的默认值；

该参数的值为2时表示每次提交事务时将数据写入日志文件，每隔1秒将日志文件写入磁盘。

（3）key_buffer_size

此参数设置的是索引缓冲区的大小。增加缓冲区可以得到更好的索引效果，但是值太大会降低操作系统的性能。

（4）table_cache

此参数设置的是同时打开的表的个数，该参数值越大表示能够同时打开的表的个数越多，但是打开的表太多会影响操作系统的性能。

（5）long_qurey_time

此参数设置的是慢查询日志的阈值设置，单位秒。

（6）interactive_timeout

此参数设置的是服务器关闭连接前等待行动的秒数。

（7）wait_timeout

此参数设置的是在关闭一个连接时等待行动的秒数。

（8）max_connections

此参数设置的是数据库的最大连接数，即此参数的值越大，MySQL支持的连接数越大，但是过多的连接可能会导致MySQL服务器僵死。调高该参数则应降低interactive_timeout、wait_timeout的值。

3.数据库结构优化

数据库结构对于数据库的性能起着关键性的作用，合理的数据库结构可以减小磁盘空间占用率，而且能够加快查询速度。数据库结构的优化有以下常见的方法。

（1）将字段较多的表分解成多个表。尽管在数据库设计的时候要考虑数据的尽可能不多余，但是由于存在使用率较低的字段会导致查询速度变慢。因此，对于字段较多的表而言，如果有些字段的使用率较低，那么这个表的结构就可以做出一些改变，例如将这些字段利用率较低的字段分离出来形成一个新的表。

（2）增加中间表。对于经常需要进行多表连接查询的表，可以建立一个中间表，并将原来联合查询的表的数据插入中间表，并将原来联合查询的数据插入中间表，从而将原理需要对多表进行连接的查询改为直接对中间表查询。

（3）增加冗余字段。对于哪些经常要连接另外的表进行的查询，然而需要查询的字段又只是另外一个表的个别字段而不是所有字段，可以通过增加冗余字段的方式连优化查询速度。

（4）优化插入的记录的速度。影响插入速度的主要是索引、唯一性校验、一次性插入记录速度的条数等。对于INNODB引擎常见的优化方法有。

① 在执行插入记录前禁用唯一性检查，插入数据后开启，具体的操作方法如下。

SET unique_checks=0                #禁用唯一性检查
SET unique_checks=1                #开启唯一性检查

② 在执行插入记录前禁用外键检查，插入数据后开启，具体的操作方法如下。

SET foreign_key_checks=0              #禁用外键检查
SET foreign_key_checks=1              #开启外键检查

③ 在执行插入记录前禁止事务的自动提交，在导入完成之后，再恢复自动提交操作。具体的操作方法如下。

SET autocommit=0                #禁止事务的自动提交
SET autocommit =1               #恢复事务的自动提交

（5）分析表、检查表和优化表

MySQL中提供分析表、检查表和优化表的方法，分析表主要的目的是分析表关键字的分布、检查表主要是检查表是否存储在错误、优化表的目的主要是消除删除或者更新造成的空间浪费。

① MySQL中使用ANALYZE TABLE语句来分析表，该语句的基本语法如下。

ANALYZE TABLE table_name [,tablename…] 

分析表的结果以二维表的形式显示Table、Op、Msg_type和Msg_text等4字段的信息。其中，Table表示表的名称；Op表示执行的操作，该字段值有ANALYZE、CHECK和OPTIMIZE，ANALYZE表示进行分析操作，CHECK表示进行检查查找，OPTIMIZE表示进行优化操作；Msg_type表示信息类型，其显示的值通常是状态、警告、错误和信息这四者之一；Msg_text显示信息。

② MySQL中使用CHECK TABLE语句来检查表。CHECK TABLE语句能够检查表是否存在错误。而且，该语句还可以检查视图是否存在错误。该语句的基本语法如下：

CHECK TABLE table_name [,table_name…] 

③ MySQL中使用OPTIMIZE TABLE语句来优化表。但是，OPTILMIZE TABLE语句只能优化表中的VARCHAR、BLOB或TEXT类型的字段。OPTILMIZE TABLE语句的基本语法如下：

OPTIMIZE TABLE table_name[,table_name…]

④ 通过OPTIMIZE TABLE语句可以消除删除和更新造成的磁盘碎片，从而减少空间的浪费。如果一个表使用了TEXT或者BLOB这样的数据类型，那么更新、删除等操作就会造成磁盘空间的浪费。因为，更新和删除操作后，以前分配的磁盘空间不会自动收回。使用OPTIMIZE TABLE语句就可以将这些磁盘碎片整理出来，以便以后再利用。

4.查询优化

通过对查询语句的分析，可以了解查询语句的执行情况，找出查询语句执行的瓶颈，使用索引等方式对查询进行优化。

在MySQL中，可以使用EXPLAIN语句和DESCRIBE语句来分析查询语句。

（1）分析查询语句

EXPLAIN语句的基本语法如下（DESCRIBE语法一致，DESCRIBE可简写为DESC）。

EXPLAIN SELECT select_options

EXPLAIN分析的结果以二维表的形式显示Id、Selecttype、Table、Type、Possible_keys、Key、Key_len、Ref、Rows和Extra等字段的信息。其中，ID字段表示SELECT语句的编号。Select_type表示SELECT语句的类型，该字段有几个常用的取值：SIMPLE表示简单查询，其中不包括连接查询和子查询；PRIMARY表示主查询，或者是最外层的查询语句；UNION表示连接查询的第二个或后面的查询语句。Table表示查询的表的表名。Type表示表的连接类型，该字段值有几个常用的取值：const表示表中有多条记录，但只从表中查询一条记录；eq_ref 表示多表连接时，后面的表使用了UNIQUE或者PRIMARY KEY；ref表示多表查询时，后面的表使用了普通索引；unique subquery表示子查询中使用了UNIQUE或者PRIMARY KEY；index_ subquery表示子查询中使用了普通索引；range表示查询语句中给出了查询范围；index表示对表中的索引进行了完整的扫描；all表示此次查询进行了全表扫描。possible_keys表示查询中可能使用的索引，如果备选的数量大于3会导致选择索引而损耗性能，所以建表时字段最好精简，同时也要建立联合索引，避免无效的单列索引。key表示查询使用到的索引。key_len表示索引字段的一长度。ref表示使用哪个列或常数与索引一起来查询记录。rows表示查询的行数，这个值反映 出SQL执行所需要扫描的行数，因此这个值越小越好。Extra表示查询过程的附件信息。

（2）使用索引来优化查询

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

创建一个普通索引可以使用CREATE INDEX语句，其基本语法格式如下。

CREATE INDEX index_Name ON table_name(column_name(length));

使用SHOW INDEX语句可以查看表的索引信息，其基本语法格式如下。

SHOW INDEX FROM table_name

通过SHOW INDEX查看users表发现系统默认为user_id、user_nickname、user_account、user_email、user_mobile这些主键或UNIQUE字段创建了索引。下面使用EXPLAIN来分析3种查询语句来查询user_nickname为“user2”的用户的user_email、user_mobile信息，找出优化的查询语句。

第一种方式执行语句和结果如下：

mysql> explain select user_nickname,user_email,user_mobile from 
-> users where user_nickname like '%er2'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 33.33
        Extra: Using where

第二种方式执行语句和结果如下：

mysql> explain select user_nickname,user_email,user_mobile from 
-> users where user_nickname like 'user2'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
   partitions: NULL
         type: range
possible_keys: user_nickname
          key: user_nickname
      key_len: 152
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition

对比两种查询方式的分析结果，第一种方式的Rows字段值为3，key字段值为NULL；第二种方式的Rows字段值为1，key字段值为user_nickname。说明第一种方式在查询的时候扫描了3行，没有使用索引，第二种方式扫描了1行，使用了索引，即第二种方式查询效果更好，也说明了索引在一定程度上对查询速度有积极的影响。

此外，数据库管理员还可以对forum数据库表结构进行优化，例如users表中有些字段不常用，可以将users表分解成2个表，表users和users_detail，users表管理用户的登录人证信息，包括user_id、user_account、user_password、user_type字段，而users_detail包括user_id、user_nickname、user_sex、user_face、user_regtime、user_email、user_mobile字段。具体的实现过程如下。

（1）选中数据库，导出表users中user_id、user_account、user_password、user_type等字段的数据到文本文件usersdata中，具体的SQL语句如下。

mysql> use forum;
mysql> select user_id,user_account,user_password,user_type
	-> from users into outfile "/var/lib/mysql-files/usersdata";

（2）导出表users中user_id、user_account、user_password、user_type等字段的数据到文本文件usersdetaildata中，具体的SQL语句如下。

mysql> select user_id,user_nickname,user_sex,user_face,
	-> user_regtime,user_email,user_mobile from users
	-> into outfile "/var/lib/mysql-files/usersdetaildata";

（3）创建一个新表users_detail，其创建出来的新表包含源表的完整表结构和索引信息与users表一样，具体的SQL语句如下。

mysql> create table users_detail like users;

（4）禁用唯一性检查和外键检查，具体的SQL语句如下。

mysql> SET unique_checks=0;
mysql> SET foreign_key_checks=0;

（5）删除users表和users_detail表中的数据，具体的SQL语句如下。

mysql> delete from users;
mysql> delete from users_detail;

（6）删除users表中的user_nickname、user_sex、user_face、user_reqtime、user_email、user_mobile字段，具体的SQL语句如下。

mysql> alter table users drop column user_nickname;
mysql> alter table users drop column user_sex;
mysql> alter table users drop column user_face;
mysql> alter table users drop column user_regtime;
mysql> alter table users drop column user_email;
mysql> alter table users drop column user_mobile;

（7）删除users_detail表中的user_account、user_password、user_type字段，具体的SQL语句如下。

mysql> alter table users_detail drop column user_account;
mysql> alter table users_detail drop column user_password;
mysql> alter table users_detail drop column user_type;

（8）分别将第（1）步和第（2）步中导出的数据，导入到users表和users_details表中，具体的SQL语句如下。

mysql> load data infile '/var/lib/mysql-files/usersdata' into 
	-> table forum.users;
mysql> load data infile '/var/lib/mysql-files/usersdetaildata' 
	-> into table forum.users_detail;

（9）开启唯一性检查和外键检查，具体的SQL语句如下。

mysql> SET unique_checks=1;
mysql> SET foreign_key_checks=1;

本章小结

本章以网络论坛系统数据库为引导，介绍了关系型数据库、关系型数据库管理系统、MySQL的安装和管理、数据库模型的建立、数据库的创建、数据的操作、数据库权限管理、数据库的备份恢复、数据库的状态监控、数据库的性能监控与优化，重点介绍了论坛系统的数据库设计、实现和管理。通过本章的学习，读者应能安装MySQL、创建数据库和表、实现数据的基本操作和管理。

本章习题

一、单项选择题

1.关系型数据库指的是经过数学理论验证可以保存现实生活中的各种关系数据，数据库中存储数据以（ ）为单位。

A.图

B.二维表

C.键值对

D.列

2.在员工信息管理系统中，员工ID字段是唯一能识别员工的，那么此字段在设计的时候最好采用哪一种约束（ ）。

A.UNIQUE

B．NOT NULL

C.PRIMARY KEY

D.DEFAULT

3.需要查询的字段涉及两张及以上的表，可以使用下列哪种方法实现基于多表的查询（ ）。

A.LIMIT

B.WHERE

C.JOIN

D.以上三种都不能实现

4.在网络论坛系统中，帖子表如需增加一个是否为精华帖字段，该字段的值只能取0或1，0表示不是精华帖，1代表是精华帖，以下哪种MySQL数据类型最适合该字段。（ ）

A.Int

B.Tinyint

C.Varchar(50)

D.Set

二、判断题

1.索引创建的越多越好。（ ）   

2.如果表A中的某字段作为外键参照了表B中的某字段，直接使用DROP语句删除B表将会删除失败。 （ ）

3.使用mysqldump工具备份数据库导出的仅仅是表中的数据。 （ ）