Skip to content

7. 使用 SGBD MySql

我们将编写使用 MySQL 数据库的 PHP 脚本：

SGBD MySQL 包含在 WampServer 软件包中（参见第 2.1.1 节）。 我们将演示如何创建数据库以及用户 MySQL。

  • 启动后，可通过任务栏右下角的 [1] 图标管理 WampServer。
  • 在 [2] 中，启动 MySQL 的管理工具

创建 [dbpersonnes] 数据库：

Image

创建用户 [admpersonnes]，密码为 [nobody]：

  • 将用户名
  • 在 [2] 中，SGBD 机器上授予其权限
  • 在 [3] 中，其密码
  • 在 [4] 中，同上
  • 在 [5] 中，不向该用户授予任何权限
  • 在 [6] 中，创建该用户
  • 在 [7]，返回 phpMyAdmin 的首页
  • 在 [8] 中，使用该页面的 [Privileges] 链接去修改用户 [admpersonnes] 和 [9] 的信息。

1011

Image

  • 在 [10] 中，指定要授予用户 [admpersonnes] 对数据库 [dbpersonnes] 的权限
  • 在 [11] 中，确认该选择
  • 通过链接 [12] 进行确认[Tout cocher]，授予用户 [admpersonnes] 对数据库 [dbpersonnes] 的所有权限[13]
  • 在 [14] 中确认

现在我们有：

  • 一个数据库 MySQL [dbpersonnes]
  • 一个用户 [admpersonnes / nobody]，该用户对该数据库拥有所有权限

我们将编写脚本 PHP 来利用该数据库。PHP 提供了多种用于管理数据库的库。 我们将使用 PDO 库（PHP 数据对象），该库在 PHP 代码与 SGBD 之间起到接口作用：

PDO库使PHP脚本能够忽略所用SGBD的具体类型。 因此，如上所述，SGBD MySQL 可以替换为 SGBD Postgres，对 PHP 脚本代码的影响最小。该库默认不可用。 可通过以下方式检查其 可用性：

  • 1：从 WampServer 的管理图标中，选择 [PHP / PHP extensions] 选项
  • 2：查看可用的各种 PDO 扩展及其当前激活状态：[PHP_pdo_mysql] 对应 SGBD MySQL， [PHP_pdo_sqlite] 对应 SGBD，SQL 对应 Lite 版本。要启用某个扩展，只需点击它即可。随后 PHP 解释器将重新启动，并启用新的扩展。

7.1. 连接至 MySQL 数据库 – 1 (mysql_01)

连接 SGBD 需通过创建 PDO 对象实现。构造函数支持以下参数：

$dbh=new PDO($dsn,$user,$passwd,$driver_options)

各参数的含义如下：

$dsn
(数据源名称) 是一个字符串，用于指定 SGBD 的类型及其在互联网上的位置。字符串
“mysql:host=localhost”表示这是一个在本地服务器上运行的 SGBD MySQL。该字符串
可能包含其他参数，特别是 SGBD 的监听端口以及
："mysql:host=localhost:port=3306:dbname=dbpersonnes"。
$user
登录用户的用户名
$passwd
其密码
$driver_options
SGBD驱动程序的选项数组

只有第一个参数是必填的。由此构建的对象将作为对已连接数据库进行所有操作的载体。如果无法构建对象 PDO，则会抛出类型为 PDOException 的异常。

以下是一个连接示例：


<?php

// 连接到数据库 MySql
// 用户身份为 (admpersonnes,nobody)
$ID = "admpersonnes";
$PWD = "nobody";
$HOTE = "localhost";

try {
  // 登录
  $dbh = new PDO("mysql:host=$HOTE", $ID, $PWD);
  print "Connexion réussie\n";
  // 关闭
  $dbh = null;
} catch (PDOException $e) {
  print "Erreur : " . $e->getMessage() . "\n";
  exit();
}

结果：

Connexion réussie

注释

  • 第 11 行：通过创建 PDO 对象来连接 SGBD。此处的构造函数使用了以下参数：
    • 一个字符串，用于说明SGBD的性质及其在互联网上的位置。字符串“mysql:host=localhost”表明，这是一个在本地服务器上运行的SGBD MySQL。 端口未指定，因此默认使用3306端口。数据库名称也未指定，此时将连接到SGBD MySQL，具体数据库的选择将在稍后进行。
    • 用户名
    • 其密码
  • 第 14 行：通过销毁最初创建的 PDO 对象来关闭连接。
  • 第 15 行：连接 SGBD 可能失败。在此情况下，将抛出类型为 PDOException 的异常。

7.2. 创建表 MySQL (mysql_02)


<?php

// 连接到数据库 MySql
// 用户身份
$ID = "admpersonnes";
$PWD = "nobody";
// 数据库标识
$DSN = "mysql:host=localhost;dbname=dbpersonnes";

// 连接
list($erreur, $connexion) = connecte($DSN, $ID, $PWD);
if ($erreur) {
  print "Erreur lors de la connexion à la base [$DSN] sous l'identité ($ID,$PWD) : $erreur\n";
  exit;
}

// 若存在，则删除人员表
$requête = "drop table personnes";
$erreur = exécuteRequête($connexion, $requête);
//是否发生错误？
if ($erreur)
  print "$requête : Erreur ($erreur)\n";
else
  print "$requête: Exécution réussie\n";
// 创建人员表
$requête = "create table personnes (prenom varchar(30) NOT NULL, nom varchar(30) NOT NULL, age integer NOT NULL, primary key(nom,prenom))";
$erreur = exécuteRequête($connexion, $requête);
//是否发生错误？
if ($erreur)
  print "$requête : Erreur ($erreur)\n";
else
  print "$requête: Exécution réussie\n";
// 断开连接并退出
déconnecte($connexion);
exit;

// ---------------------------------------------------------------------------------
function connecte($dsn, $login, $pwd) {
  // 连接（$login,$pwd）至数据库 $dsn
  // 返回连接ID及错误代码
  try {
    // 连接
    $dbh = new PDO($dsn, $login, $pwd);
    // 无错误返回
    return array("", $dbh);
  } catch (PDOException $e) {
    // 返回错误
    return array($e->getMessage(), null);
  }
}

// ---------------------------------------------------------------------------------
function déconnecte($connexion) {
  // 关闭由 $connexion 标识的连接
  $connexion = null;
}

// ---------------------------------------------------------------------------------
function exécuteRequête($connexion, $sql) {
  // 在连接 $connexion 上执行请求 $sql
  try {
    $connexion->exec($sql);
    // 返回，无错误
    return "";
  } catch (PDOException $e) {
    // 返回错误
    return $e->getMessage();
  }
}

结果：

drop table personnes: Exécution réussie
create table personnes (prenom varchar(30) NOT NULL, nom varchar(30) NOT NULL, age integer NOT NULL, primary key(nom,prenom)): Exécution réussie

在 PHPMyAdmin 中，可以看到该表的存在：

 

注释

  • 第 38-50 行：函数 connecte 建立与 SGBD 的连接。 该函数返回一个数组（$erreur、$connexion），其中 $connexion 表示已建立的连接，若连接建立失败则返回 null。 在后一种情况下，$erreur 是一个错误消息。
  • 第 53-56 行：函数 déconnecte 关闭连接
  • 第 59 行：函数 exécuteRequête 用于在连接上执行命令 SQL。该连接是一个 PDO 对象。 在 PDO 对象上执行 SQL 命令所使用的方法是 exec 方法（第 63 行）。 执行该查询可能会触发 PDOException。因此，该情况也被处理。如果发生错误，该函数会返回一条错误消息；否则返回空字符串。

7.3. 填充人员表（mysql_03）

以下脚本执行来自文本文件 [creation.txt] 中的 SQL 命令：

drop table personnes
create table personnes (prenom varchar(30) not null, nom varchar(30) not null, age integer not null, primary key (nom,prenom))
insert into personnes values('Paul','Langevin',48)
insert into personnes values ('Sylvie','Lefur',70)
insert into personnes values ('Pierre','Nicazou',35)
insert into personnes values ('Geraldine','Colou',26)
insert into personnes values ('Paulette','Girond',56)


<?php

// 连接到数据库 MySql
// 用户身份
$ID = "admpersonnes";
$PWD = "nobody";
// 数据库标识
$DSN = "mysql:host=localhost;dbname=dbpersonnes";
// 待执行的 SQL 命令文本文件的标识
$TEXTE = "creation.txt";

// 连接
list($erreur, $connexion) = connecte($DSN, $ID, $PWD);
if ($erreur) {
  print "Erreur lors de la connexion à la base [$DSN] sous l'identité ($ID,$PWD) : $erreur\n";
  exit;
}

// 创建并填充表
$erreurs = exécuterCommandes($connexion, $TEXTE, 1, 0);
//显示错误数量
print "il y a eu $erreurs[0] erreurs\n";
for ($i = 1; $i < count($erreurs); $i++)
  print "$erreurs[$i]\n";

// 断开连接并退出
déconnecte($connexion);
exit;

// ---------------------------------------------------------------------------------
function connecte($dsn, $login, $pwd) {
...
}

// ---------------------------------------------------------------------------------
function déconnecte($connexion) {
 ...
}

// ---------------------------------------------------------------------------------
function exécuteRequête($connexion, $sql) {
  // 在连接 $connexion 上执行查询 $sql
  // 若发生错误则返回1条错误消息，否则返回空字符串
  ...
}

// ---------------------------------------------------------------------------------
function exécuterCommandes($connexion, $SQL, $suivi=0, $arrêt=1) {
  // 使用连接 $connexion
  // 执行文本文件 $SQL 中包含的 SQL 命令
  // 该文件是一个命令文件，需按每行一条命令的顺序执行
  // 如果 $suivi=1，则每次执行 SQL 命令时，都会显示该命令是否成功或失败
  // 若 $arrêt=1，则函数在遇到第一个错误时停止；否则执行所有 SQL 命令
  // 该函数返回一个数组（错误数量、错误1、错误2、...）
  
  // 检查文件 $SQL 是否存在
  if (! file_exists($SQL))
    return array(1, "Le fichier $SQL n'existe pas");

  // 执行 $SQL 中包含的 SQL 查询
  // 将其放入数组中
  $requêtes = file($SQL);
  // 执行这些查询——起初没有错误
  $erreurs = array(0);
  for ($i = 0; $i < count($requêtes); $i++) {
    //是否存在空查询
    if (preg_match("/^\s*$/", $requêtes[$i]))
      continue;
    // 执行查询 $i
    $erreur = exécuteRequête($connexion, $requêtes[$i]);
    //是否发生了错误？
    if ($erreur) {
      // 又一个错误
      $erreurs[0]++;
      // 错误消息
      $msg = "$requêtes[$i] : Erreur ($erreur)\n";
      $erreurs[] = $msg;
      // 是否显示屏幕？
      if ($suivi)
        print "$msg\n";
      // 停止吗？
      if ($arrêt)
        return $erreurs;
    } else
    if ($suivi)
      print "$requêtes[$i] : Exécution réussie\n";
  }//用于
  // 返回
  return $erreurs;
}

屏幕显示结果：

drop table personnes : Exécution réussie
create table personnes (prenom varchar(30) not null, nom varchar(30) not null, age integer not null, primary key (nom,prenom)) : Exécution réussie
insert into personnes values('Paul','Langevin',48) : Exécution réussie
insert into personnes values ('Sylvie','Lefur',70) : Exécution réussie
insert into personnes values ('Pierre','Nicazou',35) : Exécution réussie
insert into personnes values ('Geraldine','Colou',26) : Exécution réussie
insert into personnes values ('Paulette','Girond',56) : Exécution réussie
il y a eu 0 erreurs

插入的内容可通过 PhpMyAdmin 查看：

 

注释

新功能体现在第48至90行的exécuterCommandes函数中。 该函数在连接 $connexion 上执行文本文件 $SQL 中找到的 SQL 命令。 它返回一个错误数组（$nbErreurs、$msg1、$msg2、……），其中 $nbErreurs 表示错误数量， $msgi 表示第 i. 条错误信息。若无错误，则返回的数组为 array(0)。

7.4. 执行任意查询（mysql_04）

以下脚本演示了如何执行来自文本文件 [sql.txt] 中的命令 SQL：

select * from personnes
select nom,prenom from personnes order by nom asc, prenom desc
select * from personnes where age between 20 and 40 order by age desc, nom asc, prenom asc
insert into personnes values('Josette','Bruneau',46)
update personnes set age=47 where nom='Bruneau'
select * from personnes where nom='Bruneau'
delete from personnes where nom='Bruneau'
select * from personnes where nom='Bruneau'
xselect * from personnes where nom='Bruneau'

在这些 SQL 命令中，有用于从数据库中检索结果的 select 命令，有不返回结果但会修改数据库的 insert、update、delete 命令，以及最后一个（xselect）等错误命令。


<?php

// 连接数据库MySql
// 用户身份
$ID = "admpersonnes";
$PWD = "nobody";
// 数据库标识
$DSN = "mysql:host=localhost;dbname=dbpersonnes";
// 待执行的 SQL 命令文本文件的标识
$TEXTE = "sql.txt";

// 连接
list($erreur, $connexion) = connecte($DSN, $ID, $PWD);
if ($erreur) {
  print "Erreur lors de la connexion à la base [$DSN] sous l'identité ($ID,$PWD) : $erreur\n";
  exit;
}

// 执行命令 SQL
$erreurs = exécuterCommandes($connexion, $TEXTE, 1, 0);
//显示错误数量
print "il y a eu $erreurs[0] erreur(s)\n";
for ($i = 1; $i < count($erreurs); $i++)
  print "$erreurs[$i]\n";

// 断开连接并退出
déconnecte($connexion);
exit;

// ---------------------------------------------------------------------------------
function connecte($dsn, $login, $pwd) {
  // 将 ($login,$pwd) 连接到数据库 $dsn
  // 返回连接 ID 及一条错误消息
  ...
}

// ---------------------------------------------------------------------------------
function déconnecte($connexion) {
  ...
}

// ---------------------------------------------------------------------------------
function exécuteRequête($connexion, $sql) {
  // 在连接 $connexion 上执行查询 $sql
  // 返回一个包含 2 个元素的数组（$erreur,$résultat）
  
  // 判断是否为 select 语句
  $commande = "";
  if (preg_match("/^\s*(\S+)/", $sql, $champs)) {
    $commande = $champs[0];
  }
  // 执行命令
  try {
    if (strtolower($commande) == "select") {
      $res = $connexion->query($sql);
    } else {
      $res = $connexion->exec($sql);
      if($res===FALSE){
        $info=$connexion->errorInfo();
        return array($info[2],null);
      }
    }
    // 无错误返回
    return array("", $res);
  } catch (PDOException $e) {
    // 返回错误
    return array($e->getMessage(), null);
  }
}

// ---------------------------------------------------------------------------------
function exécuterCommandes($connexion, $SQL, $suivi=0, $arrêt=1) {
  // 使用连接 $connexion
  // 执行文本文件 $SQL 中包含的命令 SQL
  // 该文件是一个命令文件，其中每行包含一个待执行的 SQL 命令
  // 如果 $suivi=1，则每次执行 SQL 命令时，都会显示该命令是否成功或失败
  // 若 $arrêt=1，则函数在遇到第一个错误时停止；否则执行所有 SQL 命令
  // 该函数返回一个数组（错误数量、错误1、错误2、...）
  // 检查文件 $SQL 是否存在
  if (!file_exists($SQL))
    return array(1, "Le fichier $SQL n'existe pas");

  // 执行 $TEXTE 中包含的 SQL 查询
  // 将其放入数组中
  $requêtes = file($SQL);
  // 正在执行——起初没有错误
  $erreurs = array(0);
  for ($i = 0; $i < count($requêtes); $i++) {
    //是否存在空查询
    if (preg_match("/^\s*$/", $requêtes[$i]))
      continue;
    // 正在执行查询 $i
    list($erreur, $res) = exécuteRequête($connexion, $requêtes[$i]);
    //是否发生了错误？
    if ($erreur) {
      // 又一个错误
      $erreurs[0]++;
      // 错误信息
      $msg = "$requêtes[$i] : Erreur ($erreur)\n";
      $erreurs[] = $msg;
      // 是否进行屏幕跟踪？
      if ($suivi)
        print "$msg\n";
      // 停止吗？
      if ($arrêt)
        return $erreurs;
    } else
    if ($suivi) {
      print "$requêtes[$i] : Exécution réussie\n";
      // 关于已执行查询结果的信息
      afficherInfos($res);
    }
  }//用于
  // 返回
  return $erreurs;
}

// ---------------------------------------------------------------------------------
function afficherInfos($résultat) {
  // 显示 SQL 查询的结果 $résultat
  // 这是个 SELECT 语句吗？
  if ($résultat instanceof PDOStatement) {
    // 显示字段名称
    $titre = "";
    $nbColonnes = $résultat->columnCount();
    for ($i = 0; $i < $nbColonnes; $i++) {
      $infos = $résultat->getColumnMeta($i);
      $titre.=$infos['name'] . ",";
    }
    // 移除最后一个字符 ,
    $titre = substr($titre, 0, strlen($titre) - 1);
    // 显示字段列表
    print "$titre\n";
    // 分隔行
    $séparateurs = "";
    for ($i = 0; $i < strlen($titre); $i++) {
      $séparateurs.="-";
    }
    print "$séparateurs\n";
    // 数据
    foreach ($résultat as $ligne) {
      $data = "";
      for ($i = 0; $i < $nbColonnes; $i++) {
        $data.=$ligne[$i] . ",";
      }

      // 删除最后一个字符 ,
      $data = substr($data, 0, strlen($data) - 1);
      // 显示
      print "$data\n";
    }
  } else {
    // 这不是一个下拉列表
    print " $résultat lignes(s) a (ont) été modifiée(s)\n";
  }
}

屏幕显示结果：

select * from personnes
 : Exécution réussie
prenom,nom,age
--------------
Geraldine,Colou,26
Paulette,Girond,56
Paul,Langevin,48
Sylvie,Lefur,70
Pierre,Nicazou,35
select nom,prenom from personnes order by nom asc, prenom desc : Exécution réussie
nom,prenom
----------
Colou,Geraldine
Girond,Paulette
Langevin,Paul
Lefur,Sylvie
Nicazou,Pierre
select * from personnes where age between 20 and 40 order by age desc, nom asc, prenom asc : Exécution réussie
prenom,nom,age
--------------
Pierre,Nicazou,35
Geraldine,Colou,26
insert into personnes values('Josette','Bruneau',46) : Exécution réussie
 1 lignes(s) a (ont) été modifiée(s)
update personnes set age=47 where nom='Bruneau' : Exécution réussie
 1 lignes(s) a (ont) été modifiée(s)
select * from personnes where nom='Bruneau' : Exécution réussie
prenom,nom,age
--------------
Josette,Bruneau,47
delete from personnes where nom='Bruneau' : Exécution réussie
 1 lignes(s) a (ont) été modifiée(s)
select * from personnes where nom='Bruneau' : Exécution réussie
prenom,nom,age
--------------
xselect * from personnes where nom='Bruneau' : Erreur (You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xselect * from personnes where nom='Bruneau'' at line 1)

il y a eu 1 erreur(s)
xselect * from personnes where nom='Bruneau' : Erreur (You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xselect * from personnes where nom='Bruneau'' at line 1)

注释

文本文件 [sql.txt] 中的每个命令均由第 43 行中的函数 exécuteRequête 执行。

  • 第 43 行：该函数的两个参数分别是用于执行 SQL 命令的连接（$connexion）以及待执行的 SQL 命令（$sql）。 该函数返回一个包含两个值的数组（$erreur,$résultat），其中
    • $erreur 是一条错误消息，若未发生错误，该字段可能为空
    • $résultat：执行命令 SQL 所返回的结果。 该结果因指令类型而异：若为 select 指令，则结果与 insert、update、delete 指令的结果不同。
  • 第 48-51 行：提取订单 SQL 的第一个元素，以判断当前处理的是订单 select，还是订单 insert、 update、delete。
  • 第 55 行：对于 select 命令，其通过 [PDO]->query("select 命令") 方法执行。返回的结果是一个 PDOStatement 类型的对象。
  • 第 57 行：对于 insert、update、delete 语句，将使用方法 [PDO]->exec("语句 SQL") 来执行该语句。 返回结果为由命令 SQL 修改的行数。 因此，如果命令 SQL delete 删除了两行，则返回结果为整数 2。如果执行时发生错误，则返回结果为布尔值 false。 在这种情况下，方法 [PDO]->errorinfo() 会以数组形式返回错误信息。该数组的索引为 2 的元素即为错误消息。
  • 第 58-60 行：处理操作 [PDO]->exec("命令 SQL") 可能出现的错误。
  • 第 65-68 行：处理可能出现的异常
  • 第 72 行：函数 exécuterCommandes 在连接 $connexion 上执行存储在文本文件 $SQL 中的命令 SQL。 这段代码我们之前已经见过，只是有一个细节不同：第111行。
  • 第111行：函数exécuteRequête返回了一个数组 （$erreur,$résultat）或 $résultat 是执行命令 SQL 的结果。 该结果因 SQL 命令的类型而异：若其为 select 命令，则结果不同；若其为 insert、update 或 delete 命令，结果亦不同。 函数 afficherInfos 会显示有关该结果的信息。
  • 第 122 行：如果命令 SQL 是一个 select 命令，则结果类型为 PDOStatement。该类型表示由行和列组成的表。
  • 第 125 行：方法 [PDOStatement]->getColumnCount() 返回 select 结果表的列数
  • 第 127 行：方法 [PDOStatement]->getMeta(i) 方法返回一个字典，其中包含关于 select. 结果表中第 i 列的信息。在此字典中， 与键 'name' 关联的值即为该列的名称。
  • 第 127-129 行：select 结果表中的列名被拼接成一个字符串。
  • 第 141-145 行：可以通过 foreach 循环遍历 PDOStatement 类型的对象。 每次迭代中，获得的元素是 select 结果表中的一行，其形式为一个值数组，该数组表示该行各列的值。使用 for 循环显示所有这些值。
  • 第 154 行：执行 insert、update、delete 命令的结果是该命令修改的行数。
https://stahe.github.io
本教程由 Serge Tahé 编写，根据 知识共享 署名-非商业性使用- ShareAlike 3.0 Unported.