第8章　MySQL数据库基础

（视频讲解：1小时44分钟）

只有与数据库相结合，才能充分发挥动态网页编程语言的魅力，因为网络上的众多应用都是基于数据库的。PHP支持多种数据库，尤其与MySQL被称为黄金搭档。MySQL命令行通过SQL语句对数据库进行操作，本章将详细介绍MySQL数据库的基础知识，通过本章的学习，读者不但可以轻松掌握操作MySQL数据库、数据表的方法，还可以对MySQL数据库进行查询等操作。

学习摘要：

MySQL概述

启动和关闭MySQL服务器

操作MySQL数据库

MySQL数据类型

操作数据表

操作数据表记录

MySQL图形化管理工具

8.1　MySQL概述

MySQL是目前最为流行的开源数据库，是完全网络化的跨平台关系型数据库系统，它是由瑞典的MySQL AB公司开发，由MySQL的初始开发人员David Axmark和Michael Monty Widenius于1995年建立。它的象征符号是一只名为Sakila的海豚，如图8.1所示，代表着MySQL数据库和团队的速度、能力、精确和优秀本质。

除了具有许多其他数据库所不具备的功能和选择之外，MySQL数据库还是一种完全免费的产品，用户可以直接从网上下载使用，而不必支付任何费用。

下面介绍MySQL的特点。

功能强大：MySQL中提供了多种数据库存储引擎，各个引擎各有所长，适用于不同的应用场合，用户可以选择最合适的引擎以得到最高的性能，甚至可以处理每天访问量数亿的高强度Web搜索站点。MySQL支持事务、视图、存储过程和触发器等。

支持跨平台：MySQL支持至少20种以上的开发平台，包括Linux、Windows、FreeBSD、IBM AIX等。这使得在任何平台下编写的程序都可以进行移植，而不需要对程序做任何修改。

运行速度快：高速是MySQL的显著特性。在MySQL中，使用了极快的B树磁盘表（MyISAM）和索引压缩；通过使用优化的单扫描多连接，能够极快地实现连接；SQL函数使用高度优化的类库实现，运行速度极快。

成本低：MySQL数据库是一种完全免费的产品，用户可以直接从网上下载。

支持各种开发语言：MySQL为各种流行的程序设计语言提供支持，为它们提供了很多的API函数，包括PHP、ASP.NET、Java、Eiffel、Python、Ruby、Tcl、C、C++和Perl等。

数据库存储容量大：MySQL数据库的最大有效表尺寸通常是由操作系统对文件大小的限制决定的，而不是由MySQL内部限制决定的。InnoDB存储引擎将InnoDB表保存在一个表空间内，该表空间可由数个文件创建，表空间的最大容量为64TB，可以轻松处理拥有上千万条记录的大型数据库。

8.2　启动和关闭MySQL服务器

8.2.1　启动MySQL服务器

由于我们使用的phpStudy集成开发环境中已经内置了MySQL，所以，读者无须再重复安装MySQL。当启动phpStudy时，MySQL也随着默认启动，如图8.2所示。

8.2.2　连接和断开MySQL服务器

1．连接MySQL服务器

MySQL服务器启动后，下面连接服务器。MySQL通过提供MySQL console命令窗口客户端实现了与MySQL服务器之间的交互。操作步骤如下：

选择“开始”→“运行”命令，在弹出的“运行”窗口中输入cmd命令，如图8.3所示。按Enter键后进入CMD命令行窗口，如图8.4所示。

要使用MySQL命令，首先需要切换到MySQL命令行目录，即D：\phpStudy\MySQL\bin，操作方法如图8.5所示。

在命令提示符下，输入如下命令连接MySQL：

上述命令中，-uroot表示用户名为root，-proot表示密码为root。phpStudy中MySQL的默认账号和密码都是root。

输入完命令语句后，按Enter键即可连接MySQL服务器，如图8.6所示。

2．断开MySQL连接

连接到MySQL服务器后，可以通过在MySQL提示符下输入exit或者quit命令并且按Enter键来断开MySQL连接，如图8.7所示。

3．设置系统的环境变量

每次使用MySQL命令，都要切换到MySQL命令行目录，即D:\phpStudy\MySQL\bin，如果在其他目录下执行MySQL命令，则会提示“‘mysql’不是内部或外部命令”错误信息，如图8.8所示。

通过设置环境变量，可以实现在任何目录下都能使用MySQL命令的功能。下面介绍设置环境变量的方法。其步骤如下。

（1）右击“计算机”图标，在弹出的快捷菜单中选择“属性”命令，在弹出的对话框中选择“高级系统设置”选项，如图8.9所示。在弹出的“系统属性”对话框中单击“环境变量”按钮，如图8.10所示。

（2）在弹出的“环境变量”对话框中选择PATH选项，如图8.11所示。单击“编辑”按钮，将弹出“编辑用户变量”对话框，如图8.12所示。

在“编辑用户变量”对话框中，将MySQL服务器的bin文件夹路径（D:\phpStudy\MySQL\bin）添加到变量值文本框中，注意要使用“;”与其他变量值进行分隔，最后，单击“确定”按钮。环境变量设置完成后，即可在任何目录使用MySQL命令。例如，在“运行”窗口输入cmd命令进入的初始目录中使用MySQL命令，如图8.13所示。

8.3　操作MySQL数据库

针对MySQL数据库的操作可以分为创建、选择、查看和删除4种，下面介绍这4种操作。

8.3.1　创建数据库

在MySQL中，应用create database语句创建数据库。其语法格式如下：

在创建数据库时，数据库的命名要遵循如下规则：

不能与其他数据库重名。

名称可以由任意字母、阿拉伯数字、下画线（_）或者“$”组成，可以使用上述的任意字符开头，但不能使用单独的数字，否则会造成它与数值相混淆。

名称最长可为64个字符组成（还包括表、列和索引的命名），而别名最多可长达256个字符。

不能使用MySQL关键字作为数据库、表名。

默认情况下，Windows下数据库名、表名的字母大小写是不敏感的，而在Linux下数据库名、表名的字母大小写是敏感的。为了便于数据库在平台间进行移植，建议读者采用小写字母来定义数据库名和表名。

下面通过create database语句创建一个名称为db_users的数据库。在创建数据库时，首先连接MySQL服务器，然后编写create database db_users;SQL语句，数据库创建成功。运行结果如图8.14所示。

创建db_users数据库后，MySQL管理系统会自动在D:\phpStudy\MySQL\data目录下创建db_users数据库文件夹及相关文件，实现对该数据库的文件管理。

8.3.2　选择数据库

use语句用于选择一个数据库，使其成为当前默认数据库。其语法格式如下：

例如，选择名称为db_users的数据库，操作命令如图8.15所示。

选择了db_users数据库之后，才可以操作该数据库中的所有对象。

8.3.3　查看数据库

数据库创建完成后，可以使用show databases命令查看MySQL数据库中所有已经存在的数据库。其语法格式如下：

例如，使用show databases命令显示本地MySQL数据库中所有存在的数据库名，如图8.16所示。

8.3.4　删除数据库

删除数据库使用的是drop database语句，其语法格式如下：

例如，在MySQL命令窗口中使用drop database db_users;SQL语句即可删除db_users数据库，如图8.17所示。删除数据库后，MySQL管理系统会自动删除D:\phpStudy\MySQL\data目录下的db_ users目录及相关文件。

8.4　MySQL数据类型

在MySQL数据库中，每一条数据都有其数据类型。MySQL支持的数据类型主要分成3类：数字类型、字符串（字符）类型、日期和时间类型。

8.4.1　数字类型

MySQL支持的数字类型包括准确数字的数据类型（NUMERIC、DECIMAL、INTEGER和SMALLINT），还包括近似数字的数据类型（FLOAT、REAL和DOUBLE PRECISION）。其中，关键字INT是INTEGER的简写，关键字DEC是DECIMAL的简写。

一般来说，数字类型可以分成整型和浮点型两类，详细内容如表8.1和表8.2所示。

8.4.2　字符串类型

字符串类型可以分为3类：普通的文本字符串类型（CHAR和VARCHAR）、可变类型（TEXT和BLOB）和特殊类型（ENUM和SET）。它们之间都有一定的区别，取值的范围不同，应用的地方也不同。

（1）普通的文本字符串类型，即CHAR和VARCHAR类型，CHAR列的长度在创建表时指定，取值在1～255；VARCHAR列的值是变长的字符串，取值和CHAR一样。普通的文本字符串类型如表8.3所示。

（2）TEXT和BLOB类型。它们的大小可以改变，TEXT类型适合存储长文本，而BLOB类型适合存储二进制数据，支持任何数据，如文本、声音和图像等。TEXT和BLOB类型如表8.4所示。

（3）特殊类型ENUM和SET。特殊类型ENUM和SET的介绍如表8.5所示。

8.4.3　日期和时间类型

日期和时间类型包括DATE、TIME、DATETIME、TIMESTAMP和YEAR。其中的每种类型都有其取值的范围，如赋予它一个不合法的值，将会被“0”代替。日期和时间数据类型如表8.6所示。

在MySQL中，日期的顺序是按照标准的ANSI SQL格式进行输入的。

8.5　操作数据表

数据库创建完成后，即可在命令提示符下对数据库中的数据表进行操作，如创建数据表、更改数据表结构以及删除数据表等。

8.5.1　创建数据表

MySQL数据库中，可以使用create table命令创建数据表。其语法格式如下：

create table语句的参数说明如表8.7所示。

下面介绍列属性create_definition的使用方法，每一列具体的定义格式如下：

属性create_definition的参数说明如表8.8所示。

在实际应用中，使用create table命令创建数据表时，只需指定最基本的属性即可，语法格式如下：

例如，在命令提示符下应用create database db_users创建db_users数据库，然后使用create table命令，在数据库db_users中创建一个名为tb_users的数据表，表中包括id、user name、password和createtime等字段，实现过程如图8.18所示。

8.5.2　查看表结构

成功创建数据表后，可以使用show columns命令或describe命令查看指定数据表的表结构。下面分别对这两个语句进行介绍。

1．show columns命令

show columns命令的语法格式如下：

或写成：

例如，应用show columns命令查看数据表tb_users表结构，如图8.19所示。

2．describe命令

describe命令的语法格式如下：

其中，describe可以简写为desc。在查看表结构时，也可以只列出某一列的信息，语法格式如下：

例如，应用describe命令的简写形式查看数据表tb_users的某一列信息，如图8.20所示。

8.5.3　修改表结构

修改表结构采用alter table命令。修改表结构指增加或者删除字段、修改字段名称或者字段类型、设置取消主键外键、设置取消索引以及修改表的注释等。其语法格式如下：

需要注意的是，当指定IGNORE时，如果出现重复关键的行，则只执行一行，其他重复的行被删除。其中，alter_specification子句用于定义要修改的内容，语法如下：

alter table语句允许指定多个动作，动作间使用逗号分隔，每个动作表示对表的一个修改。

例如，向tb_users表中添加一个新的字段address，类型为varchar（60），并且不为空值（not null），将字段username的类型由varchar（30）改为varchar（50），然后再用show columns命令查看修改后的表结构，如图8.21所示。

8.5.4　重命名数据表

重命名数据表采用rename table命令，其语法格式如下：

例如，对数据表tb_users进行重命名，更名后的数据表为tb_member，只需要在MySQL命令窗口中使用rename table tb_users to tb_member；语句即可。此时使用show columns查看tb_users表将输出错误信息，提示“tb_users表不存在”。因为tb_users表已经变成tb_member表，可以使用show columns查看tb_member表，运行结果如图8.22所示。

8.5.5　删除数据表

删除数据表的操作很简单，与删除数据库的操作类似，使用drop table命令即可实现。其语法格式如下：

例如，在MySQL命令窗口中使用drop table tb_member;SQL语句即可删除tb_member数据表。删除数据表后，MySQL管理系统会自动删除D:\phpStudy\MySQL\data\db_users目录下的表文件。

在删除数据表的过程中，如果删除一个不存在的表将会产生错误，这时在删除语句中加入if exists关键字就可以避免出错。其语法格式如下：

例如，先使用drop table语句删除一个tb_users表，查看提示信息，然后使用drop table if exists语句删除tb_users表。运行结果如图8.23所示。

8.6　数据表记录的操作

数据库中包含数据表，而数据表中包含数据。在MySQL与PHP的结合应用中，真正被操作的是数据表中的数据，因此如何更好地操作和使用这些数据才是使用MySQL数据库的根本。

向数据表中插入、修改和删除记录可以在MySQL命令行中使用SQL语句完成。下面介绍如何在MySQL命令行中执行基本的SQL语句。

8.6.1　数据表记录的添加

建立一个空的数据库和数据表时，首先要想到的就是如何向数据表中添加数据。这项操作可以通过insert命令来实现。其语法格式如下：

在MySQL中，一次可以同时插入多行记录，各行记录的值清单在values关键字后以逗号“,”分隔，而标准的SQL语句一次只能插入一行。

例如，向用户信息表tb_member中插入一条数据信息，如图8.24所示。

当向数据表中的所有列添加数据时，insert语句中的字段列表可以省略，例如：

8.6.2　数据表记录的查询

数据表中插入数据后，可以使用select命令来查询数据表中的数据。其语法格式如下：

其中，selection_list是要查找的列名，如果要查询多个列，可以用“,”隔开；如果查询所有列，可以用“*”代替。where子句是可选的，如果给出该子句，将查询出指定记录。

例如，查询tb_member表中所有数据。运行结果如图8.25所示。

8.6.3　数据表记录的修改

要执行数据修改的操作可以使用update命令，其语法格式如下：

其中，set子句指出要修改的列及其给定的值；where子句是可选的，如果给出该子句将指定记录中哪行应该被更新，否则，所有的记录行都将被更新。

例如，将用户信息表tb_member中用户名为mr的管理员密码mrsoft修改为mingrisoft，SQL语句如下：

运行结果如图8.26所示。

8.6.4　数据表记录的删除

在数据库中有些数据已经失去意义或者是错误的，这时就需要将它们删除，此时可以使用delete命令。其语法格式如下：

使用delete命令删除整个表的效率并不高，还可以使用truncate命令，利用它可以快速删除表中所有的内容。

例如，删除用户信息表tb_users中用户名为mr的记录信息，SQL语句如下：

删除后，使用select命令查看结果。运行结果如图8.27所示。

8.7　数据表记录的查询操作

对于数据表的“增删改查”，最常用的就是查询操作。在8.6.2节中，我们只是介绍了最基础的查询操作，实际应用中查询的条件要复杂得多。再来看一下比较复杂的select语法：

下面对它的参数进行详细讲解。

1．selection_list

设置查询内容。如果要查询表中所有列，可以将其设置为“*”；如果要查询表中某一列或多列，则直接输入列名，并以“,”为分隔符。例如，查询tb_mrbook数据表中所有列和查询id、bookname列的代码如下：

2．table_list

指定查询的数据表。既可以从一个数据表中查询，也可以从多个数据表中进行查询，多个数据表之间用“,”进行分隔，并且通过where子句使用连接运算来确定表之间的联系。

例如，从tb_mrbook和tb_bookinfo数据表中查询“bookname='PHP自学视频教程'”的id编号、书名、作者和价格，其代码如下：

在上面的SQL语句中，因为两个表都有id字段和bookname字段，为了告诉服务器要显示的是哪个表中的字段信息，要加上前缀。语法格式如下：

tb_mrbook.bookname=tb_bookinfo.bookname将表tb_mrbook和tb_bookinfo连接起来，叫作等同连接；如果不使用tb_mrbook.bookname=tb_bookinfo.bookname，那么产生的结果将是两个表的笛卡儿积，叫作全连接。

3．where条件语句

在使用查询语句时，如要从很多的记录中查询出想要的记录，就需要一个查询的条件。只有设定了查询的条件，查询才有实际的意义。设定查询条件应用的是where子句。

where子句的功能非常强大，通过它可以实现很多复杂的条件查询。在使用where子句时，需要使用一些比较运算符，常用的比较运算符如表8.9所示。

表8.9中列举的是where子句常用的比较运算符，示例中的id是记录的编号，name是表中的用户名。

例如，应用where子句，查询tb_mrbook表，条件是type（类别）为PHP的所有图书，代码如下：

4．distinct在结果中去除重复行

使用distinct关键字，可以去除结果中重复的行。

例如，查询tb_mrbook表，并在结果中去掉类型字段type中的重复数据，代码如下：

5．order by对结果排序

使用order by可以对查询的结果进行升序和降序（desc）排列，在默认情况下，order by按升序输出结果。如果要按降序排列可以使用desc来实现。

对含有NULL值的列进行排序时，如果是按升序排列，NULL值将出现在最前面，如果是按降序排列，NULL值将出现在最后。例如，查询tb_mrbook表中的所有信息，按照id进行降序排列，并且只显示5条记录。代码如下：

6．like模糊查询

like属于较常用的比较运算符，通过它可以实现模糊查询。它有两种通配符：“%”和“_”。“%”可以匹配一个或多个字符，而“_”只匹配一个字符。例如，查找所有书名（bookname字段）包含PHP的图书，代码如下：

7．concat联合多列

使用concat函数可以联合多个字段，构成一个总的字符串。例如，把tb_mrbook表中的书名（bookname）和价格（price）合并到一起，构成一个新的字符串。代码如下：

其中，合并后的字段名为concat（）函数形成的表达式bookname:price，看上去十分复杂，通过as关键字给合并字段取一个别名，这样看上去就清晰了。如《PHP项目开发实战入门》这本书定价为69.80元，concat（）查询结果中的info字段值则是“PHP项目开发实战入门:69.80”。

8．limit限定结果行数

limit子句可以对查询结果的记录条数进行限定，控制它输出的行数。例如，查询tb_mrbook表，按照图书价格升序排列，显示10条记录，代码如下：

使用limit还可以从查询结果的中间部分取值。首先要定义两个参数，参数1是开始读取的第1条记录的编号（在查询结果中，第1个结果的记录编号是0，而不是1）；参数2是要查询记录的个数。

例如，查询tb_mrbook表，从第3条记录开始，查询6条记录，代码如下：

9．使用函数和表达式

在MySQL中，还可以使用表达式来计算各列的值，作为输出结果。表达式还可以包含一些函数。

例如，计算tb_mrbook表中各类图书的总价格，代码如下：

在对MySQL数据库进行操作时，有时需要对数据库中的记录进行统计，如求平均值、最小值、最大值等，这时可以使用MySQL中的统计函数，其常用的统计函数如表8.10所示。

除了使用函数之外，还可以使用算术运算符、字符串运算符，以及逻辑运算符来构成表达式。例如，可以计算图书打九折之后的价格，代码如下：

10．group by对结果分组

通过group by子句可以将数据划分到不同的组中，实现对记录进行分组查询。在查询时，所查询的列必须包含在分组的列中，目的是使查询到的数据没有矛盾。在与avg（）函数或sum（）函数一起使用时，group by子句能发挥最大作用。例如，查询tb_mrbook表，按照type进行分组，求每类图书的平均价格，代码如下：

11．使用having子句设定第二个查询条件

having子句通常和group by子句一起使用。在对数据结果进行分组查询和统计之后，还可以使用having子句来对查询的结果进行进一步筛选。having子句和where子句都用于指定查询条件，不同的是where子句在分组查询之前应用，而having子句在分组查询之后应用，而且having子句中还可以包含统计函数。例如，计算tb_mrbook表中各类图书的平均价格，并筛选出图书的平均价格大于60元的记录，代码如下：

8.8　MySQL图形化管理工具

在命令提示符下操作MySQL数据库的方式对PHP初学者并不友好，而且需要有专业的SQL语言知识，所以各种MySQL图形化管理工具应运而生。下面只简要介绍phpMyAdmin、Navicat for MySQL这两种工具的使用。

8.8.1　phpMyAdmin简介

phpMyAdmin是众多MySQL图形化管理工具中使用最广泛的一种，是一款使用PHP开发的B/S模式的MySQL客户端软件，该工具是基于Web跨平台的管理程序，并且支持简体中文。通过该管理工具可以对MySQL进行各种操作，如创建数据库、数据表和生成MySQL数据库脚本文件等。

phpStudy集成开发环境中已经安装了phpMyAdmin图形化管理工具，所以用户无须再下载，可以按图8.28所示方式打开。

打开后，输入数据库的用户名root，密码root，如图8.29所示。

单击“执行”按钮，进入phpMyAdmin管理平台，如图8.30所示。

8.8.2　Navicat for MySQL简介

Navicat for MySQL是一个桌面版的MySQL数据库管理和开发工具。和微软SQLServer的管理器很像，易学易用。Navicat使用图形化的用户界面，可以让用户使用和管理更为轻松。官方网址是https://www.navicat.com.cn。

首先下载、安装Navicat for MySQL，然后按照步骤新建MySQL连接，如图8.31所示。

弹出“连接属性”对话框后，输入连接信息，如图8.32所示，其中密码为root。

单击“确定”按钮，创建完成。此时，双击localhost，即进入localhost数据库。

8.9　小结

本章主要介绍MySQL数据库的基本操作，包括创建、查看、选择、删除数据库；创建、修改、更名、删除数据表；插入、浏览、修改、删除记录，这些是程序开发人员必须掌握的内容。如果用户不习惯在命令提示符下管理数据库，可以在可视化的图形工具中轻松操作和管理数据库。另外，本章还介绍了启动、连接和断开MySQL服务器的方法，要求读者熟练掌握。