在本 PHP 教程中，您将学习有关 PHP 中 MySQL 数据库连接的所有知识。我们还将讨论如何连接数据库、创建数据库、创建表、向表中插入数据、从数据库中获取数据、更新数据、删除数据以及关闭连接。
MySQL 是一个关系型数据库管理系统 (RDBMS)。它是与 PHP 一起使用最广泛的数据库系统。Oracle 公司创建、分发和支持 MySQL。MySQL 是网络上使用最广泛的关系型数据库系统之一。它可以免费下载并且设置简单。
与其他关系型数据库一样，MySQL 以表的形式存储数据。表是相关数据的集合，被组织成行和列。表中的每一行代表一条数据记录，这些记录彼此内在相关，例如关于单个人的信息，而每一列代表一个特定的字段，例如 id、名字、姓氏、电子邮件等。
要存储或访问 MySQL 数据库中的数据，您必须首先连接到 MySQL 数据库服务器。PHP 提供了两种连接到 MySQL 服务器的方法：MySQLi (改进的 MySQL) 和 PDO (PHP 数据对象)。虽然 PDO 扩展更具可移植性，支持超过十二种其他数据库，但 MySQLi 扩展，顾名思义，只支持 MySQL 数据库。另一方面，MySQLi 扩展使得连接到 MySQL 数据库服务器并运行查询变得很容易。PDO 和 MySQLi 都提供面向对象的 API，但 MySQLi 还提供了一个过程式 API，这对新手来说非常容易理解。
PHP 中的 mysqli_connect() 函数使这变得简单。PHP 和 MySQL 数据库服务器之间的所有通信都使用此连接。以下是通过 MySQLi 和 PDO 扩展连接到 MySQL 的基本语法
语法：使用 MySQLi 过程式
$c = mysqli_connect("hostname", "username", "password", "database");
语法：使用 MySQLi 面向对象
$mysqli = new mysqli("hostname", "username", "password", "database");
语法：使用 PDO
$c = new PDO("mysql:host=hostname; dbname=database", "username", "password");
在上述语法中，hostname 参数指定 MySQL 服务器的主机名（例如 localhost）或 IP 地址，而 username 和 password 参数指定访问 MySQL 服务器的凭据，如果提供了 database 参数，则指定执行查询时要使用的默认 MySQL 数据库。
<?php
$host = "localhost";
$username = "root";
$password = "";
$c = mysqli_connect($host, $username, $password);
if (!$c) {
die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>
<?php
$host = "localhost";
$username = "root";
$password = "";
$mysqli = new mysqli($host, $username, $password);
if ($mysqli === false) {
die("ERROR: Could not connect. " . $mysqli->connect_error);
}
echo "Connect Successfully. Host info: " . $mysqli->host_info;
?>
<?php
$host = "localhost";
$username = "root";
$password = "";
try {
$c = new PDO("mysql:host=$host", $username, $password);
$c->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully";
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
?>
在我们从 PHP 脚本连接到 MySQL 数据库后，当我们的工作完成后，我们也应该断开或关闭连接。下面提到了用所有三种方法关闭 MySQL 连接的语法。
语法：使用 MySQLi 过程式
mysqli_close($c);
语法：使用 MySQLi 面向对象
$c->close();
语法：使用 PDO
$c = null;
现在您已经学会了如何连接到 MySQL 数据库服务器。现在我们将看看如何运行 SQL 查询来建立一个数据库。在我们能够保存或访问数据之前，我们必须首先建立一个数据库。在 MySQL 中，`Establish DATABASE` 命令用于创建一个新的数据库。让我们使用 CREATE DATABASE 语句创建一个 SQL 查询，然后通过将其发送到 PHP mysqli_query() 函数来运行它，以最终创建我们的数据库。
<?php
$host = "localhost";
$username = "root";
$password = "";
$c = mysqli_connect($host, $username, $password);
if (!$c) {
die("Connection failed: " . mysqli_connect_error());
}
$sql_query = "CREATE DATABASE db_pro";
if (mysqli_query($c, $sql_query)) {
echo "Database created successfully";
} else {
echo "ERROR: Could not able to execute $sql_query. " . mysqli_error($c);
}
?>
<?php
$host = "localhost";
$username = "root";
$password = "";
$c = new mysqli($host, $username, $password);
if ($c === false) {
die("ERROR: Could not connect. " . $c->connect_error);
}
$sql_query = "CREATE DATABASE db_obj";
if ($c->query($sql_query) === true) {
echo "Database created successfully";
} else {
echo "ERROR: Could not able to execute $sql_query. " . $c->error;
}
?>
<?php
$host = "localhost";
$username = "root";
$password = "";
try {
$c = new PDO("mysql:host=$host", $username, $password);
$c->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
try {
$sql_query = "CREATE DATABASE de_pdo";
$c->exec($sql_query);
echo "Database created successfully";
} catch (PDOException $e) {
die("ERROR: Could not able to execute $sql_query. " . $e->getMessage());
}
?>
我们学习了如何在 MySQL 服务器上构建数据库。现在是时候在数据库中创建表了，我们将在其中实际存储数据。表将数据组织成行和列。要在数据库中构建表，请使用 SQL CREATE TABLE 命令。让我们使用 CREATE TABLE 语句编写一个 SQL 查询，然后通过将其传递给 PHP mysqli_query() 函数来运行它，以最终创建我们的表。
<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "db_pro";
$c = mysqli_connect($host, $username, $password, $database);
if (!$c) {
die("Connection failed: " . mysqli_connect_error());
}
$sql_query = "CREATE TABLE student(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
email VARCHAR(70) NOT NULL UNIQUE
)";
if (mysqli_query($c, $sql_query)) {
echo "Table created successfully.";
} else {
echo "ERROR: Could not able to execute $sql_query. " . mysqli_error($c);
}
?>
<?php
$host = "localhost";
$username = "root";
$password = "";
$c = new mysqli($host, $username, $password, $database);
if ($c === false) {
die("ERROR: Could not connect. " . $c->connect_error);
}
$sql_query = "CREATE TABLE student(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
email VARCHAR(70) NOT NULL UNIQUE
)";
if ($c->query($sql_query) === true) {
echo "Table created successfully.";
} else {
echo "ERROR: Could not able to execute $sql_query. " . $c->error;
}
?>
<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "db_pdo";
try {
$c = new PDO("mysql:host=$host;dbname=$database", $username, $password);
$c->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
try {
$sql_query = "CREATE TABLE student(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
email VARCHAR(70) NOT NULL UNIQUE
)";
$c->exec($sql_query);
echo "Table created successfully.";
} catch (PDOException $e) {
die("ERROR: Could not able to execute $sql_query. " . $e->getMessage());
}
?>
您现在了解了如何在 MySQL 中创建数据库和表。现在我们将看看如何运行 SQL 查询以将记录放入表中。要将新条目插入数据库表，请使用 INSERT INTO 命令。让我们使用 INSERT INTO 语句创建一个带有可接受值的 SQL 查询，然后我们将通过将其发送到 PHP mysqli_query() 函数来运行它，以将数据插入表中。这里是一个通过为名字、姓氏和电子邮件字段提供值来向人员数据库中插入新条目的示例。
<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "db_pro";
$c = mysqli_connect($host, $username, $password, $database);
if (!$c) {
die("Connection failed: " . mysqli_connect_error());
}
$sql_query = "INSERT INTO student (first_name, last_name, email) VALUES ('John', 'Doe', '[email protected]')";
if (mysqli_query($c, $sql_query)) {
echo "Records inserted successfully.";
} else {
echo "ERROR: Could not able to execute $sql_query. " . mysqli_error($c);
}
?>
<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "db_obj";
$c = new mysqli($host, $username, $password, $database);
if ($c === false) {
die("ERROR: Could not connect. " . $c->connect_error);
}
$sql_query = "INSERT INTO student (first_name, last_name, email) VALUES ('John', 'Doe', '[email protected]')";
if ($c->query($sql_query) === true) {
echo "Records inserted successfully.";
} else {
echo "ERROR: Could not able to execute $sql_query. " . $c->error;
}
?>
<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "db_pdo";
try {
$c = new PDO("mysql:host=$host;dbname=$database", $username, $password);
$c->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
try {
$sql_query = "INSERT INTO student (first_name, last_name, email) VALUES ('John', 'Doe', '[email protected]')";
$c->exec($sql_query);
echo "Records inserted successfully.";
} catch (PDOException $e) {
die("ERROR: Could not able to execute $sql_query. " . $e->getMessage());
}
?>
UPDATE 语句用于更改或修改数据库表中的现有记录。此语句通常与 WHERE 子句结合使用，以将修改限制为满足特定条件的记录。让我们使用 UPDATE 语句和 WHERE 子句创建一个 SQL 查询，然后通过将其提供给 PHP mysqli_query() 函数来执行它，以更新表的条目。
<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "db_pro";
$c = mysqli_connect($host, $username, $password, $database);
if (!$c) {
die("Connection failed: " . mysqli_connect_error());
}
$sql_query = "UPDATE student SET email='[email protected]' WHERE id=1";
if (mysqli_query($c, $sql_query)) {
echo "Records were updated successfully.";
} else {
echo "ERROR: Could not able to execute $sql_query. " . mysqli_error($c);
}
?>
<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "db_obj";
$c = new mysqli($host, $username, $password, $database);
if ($c === false) {
die("ERROR: Could not connect. " . $c--->connect_error);
}
$sql_query = "UPDATE student SET email='[email protected]' WHERE id=1";
if ($c->query($sql_query) === true) {
echo "Records were updated successfully.";
} else {
echo "ERROR: Could not able to execute $sql_query. " . $c->error;
}
?>
<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "db_pdo";
try {
$c = new PDO("mysql:host=$host;dbname=$database", $username, $password);
$c->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
try {
$sql_query = "UPDATE student SET email='[email protected]' WHERE id=1";
$c->exec($sql_query);
echo "Records were updated successfully.";
} catch (PDOException $e) {
die("ERROR: Could not able to execute $sql_query. " . $e->getMessage());
}
?>
SQL DELETE 命令用于从表中删除记录，其方式与向表中插入记录相同。它通常与 WHERE 子句结合使用，以仅删除满足指定标准或条件的条目。让我们使用 DELETE 语句和 WHERE 子句创建一个 SQL 查询，然后通过将其提供给 PHP mysqli_query() 函数来执行它，以删除表的记录。
<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "db_pro";
$c = mysqli_connect($host, $username, $password, $database);
if (!$c) {
die("Connection failed: " . mysqli_connect_error());
}
$sql_query = "DELETE FROM student WHERE first_name='Roy'";
if (mysqli_query($c, $sql_query)) {
echo "Records were deleted successfully.";
} else {
echo "ERROR: Could not able to execute $sql_query. " . mysqli_error($c);
}
?>
<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "db_obj"; $c mysqli($host, $username, $password, $database);
if ($c === false) {
die("ERROR: Could not connect. " . $c->connect_error);
}
$sql_query = "DELETE FROM student WHERE first_name='Roy'";
if ($c->query($sql_query) === true) {
echo "Records were deleted successfully.";
} else {
echo "ERROR: Could not able to execute $sql_query. " . $c->error;
}
?>
<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "db_pdo";
try {
$c = new PDO("mysql:host=$host;dbname=$database", $username, $password);
$c->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
try {
$sql_query = "DELETE FROM student WHERE first_name='Roy'";
$c->exec($sql_query);
echo "Records were deleted successfully.";
} catch (PDOException $e) {
die("ERROR: Could not able to execute $sql_query. " . $e->getMessage());
}
?>
到目前为止，您已经学会了如何构建数据库和表，以及如何输入数据。现在是时候恢复在上一个教程中插入到表中的数据了。要从数据库表中获取记录，请使用 SQL SELECT 命令。让我们使用 SELECT 语句创建一个 SQL 查询，然后通过将其提供给 PHP mysqli_query() 函数来执行它，以获取表数据。
<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "db_pro";
$c = mysqli_connect($host, $username, $password, $database);
if (!$c) {
die("Connection failed: " . mysqli_connect_error());
}
$sql_query = "SELECT * FROM student";
if ($result = mysqli_query($c, $sql_query)) {
if (mysqli_num_rows($result) > 0) {
echo "<table>";
echo "<tr>";
echo "<th>id</th>";
echo "<th>First Name</th>";
echo "<th>Last Name</th>";
echo "<th>Email</th>";
echo "</tr>";
while ($row = mysqli_fetch_array($result)) {
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['first_name'] . "</td>";
echo "<td>" . $row['last_name'] . "</td>";
echo "<td>" . $row['email'] . "</td>";
echo "</tr>";
}
echo "</table>";
mysqli_free_result($result);
} else {
echo "No records matching your query were found.";
}
} else {
echo "ERROR: Could not able to execute $sql_query. " . mysqli_error($c);
}
?>
<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "db_obj";
$c = new mysqli($host, $username, $password, $database);
if ($c === false) {
die("ERROR: Could not connect. " . $c->connect_error);
}
$sql_query = "SELECT * FROM student";
if ($result = $c->query($sql_query)) {
if ($result->num_rows > 0) {
echo "<table>";
echo "<tr>";
echo "<th>id</th>";
echo "<th>First Name</th>";
echo "<th>Last Name</th>";
echo "<th>Email</th>";
echo "</tr>";
while ($row = $result->fetch_array()) {
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['first_name'] . "</td>";
echo "<td>" . $row['last_name'] . "</td>";
echo "<td>" . $row['email'] . "</td>";
echo "</tr>";
}
echo "</table>";
$result->free();
} else {
echo "No records matching your query were found.";
}
} else {
echo "ERROR: Could not able to execute $sql_query. " . $c->error;
}
?>
<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "db_pdo";
try {
$c = new PDO("mysql:host=$host;dbname=$database", $username, $password);
$c->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
try {
$sql_query = "SELECT * FROM student";
$result = $c->query($sql_query);
if ($result->rowCount() > 0) {
echo "<table>";
echo "<tr>";
echo "<th>id</th>";
echo "<th>First Name</th>";
echo "<th>Last Name</th>";
echo "<th>Email</th>";
echo "</tr>";
while ($row = $result->fetch()) {
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['first_name'] . "</td>";
echo "<td>" . $row['last_name'] . "</td>";
echo "<td>" . $row['email'] . "</td>";
echo "</tr>";
}
echo "</table>";
unset($result);
} else {
echo "No records matching your query were found.";
}
} catch (PDOException $e) {
die("ERROR: Could not able to execute $sql_query. " . $e->getMessage());
}
?>
