php7实践指南-ch15MySQL数据库的使用

15.1 MySQL数据库基础

MySQL是最流行的关系型数据库管理系统，在Web应用方面常用，且是免费的。关系型数据库中数据以表格的形式出现，每行为各种记录名称，每列为记录名称所对应的数据域。许多的行和列构成一张数据表，许多的表组成一个数据库。MySQL把数据存储在表格中，使用结构化查询语言SQL来访问数据库。MySQL具有以下特点：

（1）MySQL是开源的，所以你不需要支付额外的费用。

（2）MySQL支持大型的数据库，可以处理拥有上千万条记录的大型数据库。

（3）MySQL使用标准的SQL数据语言形式。

（4）MySQL可以允许于多个系统上，并且支持多种语言。这些编程语言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。

（5）MySQL对PHP有很好的支持，PHP是目前最流行的Web开发语言。

（6）MySQL支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持4GB,64位系统支持最大的表文件为8TB。

（7）MySQL是可以定制的，采用GPL协议，可以修改源码来开发自己的MySQL系统。

15.2 操作MySQL数据库

15.3 MySQL数据类型

在MySQL中定义数据字段的类型对的数据库的优化是非常重要的。MySQL支持多种类型，大致可以分为3类：数值、日期/时间和字符串（字符）类型。

15.3.1 数值类型

MySQL支持所有标准SQL数值数据类型。这些类型包括严格数值数据类型（INTEGER、SMALLINT、DECIMAL和NUMERIC）以及近似数值数据类型（FLOAT、REAL和DOUBLE PRECISION）。关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。MySQL数据库支持的数值类型说明如表15-1所示。

 

15.3.2 日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型都有一个有效值范围和一个“零”值，当指定不合法的MySQL不能表示的值时使用“零”值。MySQL中日期和时间数据类型如表15-2所示。

15.3.3 字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。字符串类型如表15-3所示。

CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY和VARBINARY类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串，而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值。

BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同，支持任何数据，如文本、声音和图像等。

有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型，有相同的最大长度和存储需求，但是不能存储二进制文件。

15.4 操作MySQL数据表

操作MySQL数据表是指对表结构进行改变，比如创建一个表，更改表字段类型，设置主键索引等。这些在平常的使用中是很常见的。

15.4.1 创建数据表

在MySQL中创建数据表使用create table语句，语法如下：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

create table语句中的参数说明如表15-4所示。

关于create_definition参数的说明如表15-5所示。

下面演示创建一个名为test_table的表，该表包含id、title、author、content、submit_time、click字段。创建表的SQL语句如下：

CREATE TABLE `test_table`(
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(40) CHARACTER SET utf8  NOT NULL,
`author` CHAR(10) NULL,
`content` VARCHAR(45) NOT NULL COMMENT '文章内容',
`submit_time` VARCHAR(45) NOT NULL,
`click` INT(4) NULL DEFAULT 0,
 PRIMARY KEY (`id`)
)
ENGINE=MyISAM
DEFAULT CHARACTER SET=utf8
COMMENT='文章内容表';

 

在命令行模式下输入SQL语句的运行结果如下图所示

15.4.2 查看数据表结构

创建完一个表后，可以使用show columns或者describe语句查看指定数据表的结构。

show columns语法如下：

show columns显示在一个给定表中各列的信息

也可以使用describe语句查看表结构

也可以使用describe语句查看表结构，describe语法如下：

15.4.3 更改数据表结构

当我们需要修改数据表名或数据表字段名时，需要使用到alter命令。

alter语法如下：

alter table用于更改原有表的结构，可以增加或删减列、创建或取消索引、更改原有列的类型、重新命名列或表，还可以更改表的评注和表的类型。alter table运行时会对原表进行临时复制，在副本上进行更改，然后删除原表，再对新表进行重命名。在执行alter table时，其他用户可以阅读原表，但是对表的更新和修改的操作将被延迟，直到新表生成为止。新表生成后，这些更新和修改信息会自动转移到新表上。

下面介绍几个alter命令经常使用到的场景。

使用alter删除test_table中的click字段，语句如下：

ALTER TABLE test_table drop click

使用alter给表test_table添加字段click_times，语句如下：

ALTER TABLE test_table add click_times int(4)

使用alter修改表test_table中submit_time字段为datetime类型，语句如下：

ALTER TABLE test_table MODIFY submit_time datetime

使用alter修改表test_table表名为table_test，语句如下：

ALTER table test_table rename to table_test

15.4.4 删除数据表

删除数据表使用drop table语句，语法如下：

if exists检查表是否存在，因为在删除一个不存在的表的情况下会报错。例如，删除数据表table_test的语句如下：

DROP table table_test;

15.5 操作MySQL数据

操作MySQL数据是对表中数据进行增删改查，常用的是insert向表中增加数据，update更新数据，delete删除一些数据，select查询所需数据等。编程中用到的数据很多都存在MySQL数据库中，这一节就来介绍如何操作MySQL数据库中的数据。

15.5.1 插入数据

一般通过以下两种方式向数据表中插入数据。语法分别如下：

另一种常用语法如下：

下面通过使用第一种语法向表test_table中插入一行数据，语句如下：

INSERT INTO test_table (title,author,content,submit_time,click) values
('hello','chenxiaolong','hello everyone',now(),10);

接下来演示使用第二种语法向表中插入一行数据，语句如下：

INSERT INTO test_table set title='h1',  author='chendalong',content='hello world', submit_time=now(),click=100;

15.5.2 更新数据

更新数据表使用update语句，语法如下：

set重新设定指定列的值，where子句指定记录中哪些行需要被更改，如果不设置where子句或where子句的值恒成立（如1=1），就将更新所有记录行的数据。下面演示更改表test_table中第一行记录的click值为200。语句如下：

UPDATE test_table SET click=200 where id=1;

15.5.3 删除数据

删除表中的数据使用delete语句，语法如下：

其中，where子句指定哪些行被删除，如果没有设置where子句或设置其恒成立（如where true）将会删除所有记录。下面演示如何删除表test_table中的第一行数据，语句如下：

DELETE FROM test_table where id=3;

MySQL中还提供一个可以清空数据表中所有数据的函数truncate

15.5.4 查询数据

最常用的数据库操作是增、删、改、查，其中查询数据可以说是数据库操作中最常使用到的。MySQL中提供的查询数据的语句非常强大，比如可限定查询数量和查询起始位置、对查询结果进行分组排序、使用函数和表达式查询数据等。

关于select语句参数的说明如表15-6所示。

 

通过应用程序向表test_table中插入一百行数据，表中的数据示例如图15-22所示。

CREATE DEFINER=`root`@`localhost` PROCEDURE `myproc4`()
begin
declare num int ;
set num = 0 ;
while num < 100 do
insert into test_table (title,author,content,submit_time,click) values
(concat("hello", num),'张小龙',concat('hello everyone',num),now(), concat('100',num)); 
set num = num + 1;
end
while;
end

1. distinct去掉查询重复行

使用distinct可去掉查询结果中的重复行。例如，查询test_table表并在结果中去掉字段author中的重复数据，SQL语句如下：

MariaDB [db_test]> SELECT DISTINCT author FROM test_table;
+--------+
| author |
+--------+
| 张小龙 |
| 陈小龙 |
| 张大龙 |
| 李小龙 |
+--------+
4 rows in set (0.001 sec)

2. into outfile将查询结果导出到文件

将从表test_table中查询到的数据导出到test_table.txt，语句如下：

SELECT * INTO OUTFILE 'test_table.txt' FROM test_table;

执行以上SQL语句后将会得到test_table.txt文件，其中存储着从test_table表中查询到的信息，如图15-24所示。

3. where查询子句

可以使用where子句限定查询条件，where子句功能非常强大，通过它可以实现一些复杂的查询，在where子句中常用到的运算符如表15-7所示。

select * from test_table where click>500 and author='张小龙';

4. order by对结果进行排序

MariaDB [db_test]> select * from test_table order by click desc limit 0,10;
+------+----------+--------+-------------------+---------------------+-------+
| id   | title    | author | content           | submit_time         | click |
+------+----------+--------+-------------------+---------------------+-------+
| 1000 | hello999 | 张小龙 | hello everyone999 | 2021-01-25 14:15:55 |  1000 |
|  999 | hello998 | 张小龙 | hello everyone998 | 2021-01-25 14:15:55 |   999 |
|  998 | hello997 | 张小龙 | hello everyone997 | 2021-01-25 14:15:55 |   998 |
|  997 | hello996 | 张小龙 | hello everyone996 | 2021-01-25 14:15:55 |   997 |
|  996 | hello995 | 张小龙 | hello everyone995 | 2021-01-25 14:15:55 |   996 |
|  995 | hello994 | 张小龙 | hello everyone994 | 2021-01-25 14:15:55 |   995 |
|  994 | hello993 | 张小龙 | hello everyone993 | 2021-01-25 14:15:55 |   994 |
|  993 | hello992 | 张小龙 | hello everyone992 | 2021-01-25 14:15:55 |   993 |
|  992 | hello991 | 张小龙 | hello everyone991 | 2021-01-25 14:15:55 |   992 |
|  991 | hello990 | 张小龙 | hello everyone990 | 2021-01-25 14:15:55 |   991 |
+------+----------+--------+-------------------+---------------------+-------+
10 rows in set (0.019 sec)

图15-26 使用order by对查询结果排序

使用like可实现模糊查询，它有两种通配符“%”和“_”。“%”可以匹配一个或多个字符，“—”只匹配一个字符。例如，查询test_table表中author=’陈小龙’和author=’陈大龙’的记录行，语句如下：

select * from test_table where author like ('张_龙') limit 0,10;

15.6 MySQL图形化管理工具

1. phpMyAdmin

2. MySQL Workbench

3. MySQL-Front

4. Navicat