php mysql数据库查询_PHP

PHP MySQL数据库查询是使用PHP语言与MySQL数据库进行交互的过程。在PHP中，可以使用mysqli或PDO扩展来连接和操作MySQL数据库。通过编写SQL语句并执行，可以实现对数据库的增删改查等操作。

PHP MySQL数据库查询

在Web开发中，PHP和MySQL是一对常见的搭档，它们共同完成了从后端逻辑到数据存储的整个流程，本文将深入探讨如何使用PHP来对MySQL数据库进行查询，包括连接数据库、执行增删改查操作以及预处理和安全性措施等内容，通过不同的实例和解释，帮助开发者掌握PHP与MySQL交互的核心技能。

一、基础连接与查询

在开始数据库查询之前，必须先建立与MySQL数据库的连接，PHP提供了多种方式来实现这一连接，其中最常用的是MySQLi扩展和PDO（PHP Data Objects）扩展。

1、使用MySQLi扩展

连接数据库：使用mysqli_connect()函数，需要提供数据库地址（如localhost）、用户名、密码和数据库名。$conn = mysqli_connect("localhost", "username", "password", "myDB");

检查连接：验证连接是否成功，如果连接失败，显示错误信息并退出。if (!$conn) { die("Connection failed: " . mysqli_connect_error()); }

执行查询：通过mysqli_query()函数执行SQL语句。$result = mysqli_query($conn, "SELECT * FROM MyGuests");

处理结果：如果查询返回了结果，使用循环遍历输出每行数据。

“`php

while ($row = mysqli_fetch_assoc($result)) {

echo "id: " . $row["id"] . " Name: " . $row["firstname"] . " " . $row["lastname"] . "<br>";

}

“`

关闭连接：完成查询后，使用mysqli_close()关闭与数据库的连接。mysqli_close($conn);

2、使用PDO扩展

连接数据库：使用new PDO()创建一个新的PDO实例。

“`php

$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);

$conn>setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

“`

预处理语句：使用prepare()方法创建一个预处理语句对象。$stmt = $conn>prepare("SELECT id, firstname, lastname FROM MyGuests");

执行查询：通过execute()方法执行预处理语句。$stmt>execute();

获取结果：使用setFetchMode()设置结果获取模式，并通过循环获取结果集中的数据。

“`php

$result = $stmt>setFetchMode(PDO::FETCH_ASSOC);

foreach($stmt>fetchAll() as $k=>$v){

echo "id: " . $v[‘id’] . " Name: " . $v[‘firstname’] . " " . $v[‘lastname’] . "<br>";

}

“`

关闭连接：PDO连接在脚本结束时会自动关闭，也可以显式地将连接赋值为null来关闭。$conn = null;

二、增删改操作实现

除了查询数据外，增（Insert）、删（Delete）、改（Update）也是数据库操作的重要组成部分，这些操作都可以通过执行相应的SQL语句来实现。

1、插入数据

MySQLi插入：使用INSERT INTO语句结合mysqli_query()。

“`php

$sql = "INSERT INTO MyGuests (firstname, lastname) VALUES (‘John’, ‘Doe’)";

mysqli_query($conn, $sql);

“`

PDO插入：使用预处理语句的bindParam()方法或直接插入参数。

“`php

$sql = "INSERT INTO MyGuests (firstname, lastname) VALUES (?, ?)";

$stmt = $conn>prepare($sql);

$stmt>execute(array(‘John’, ‘Doe’));

“`

2、更新数据

MySQLi更新：使用UPDATE语句结合mysqli_query()。

“`php

$sql = "UPDATE MyGuests SET lastname=’Doe’ WHERE id=2";

mysqli_query($conn, $sql);

“`

PDO更新：使用预处理语句。

“`php

$sql = "UPDATE MyGuests SET lastname=? WHERE id=?";

$stmt = $conn>prepare($sql);

$stmt>execute(array(‘Doe’, 2));

“`

3、删除数据

MySQLi删除：使用DELETE FROM语句结合mysqli_query()。

“`php

$sql = "DELETE FROM MyGuests WHERE id=2";

mysqli_query($conn, $sql);

“`

PDO删除：使用预处理语句。

“`php

$sql = "DELETE FROM MyGuests WHERE id=?";

$stmt = $conn>prepare($sql);

$stmt>execute(array(2));

“`

4、条件查询

条件查询：在SELECT语句中使用WHERE子句来指定条件，查找lastname为“Doe”的所有记录：

“`php

$sql = "SELECT id, firstname, lastname FROM MyGuests WHERE lastname=’Doe’";

$result = mysqli_query($conn, $sql);

while ($row = mysqli_fetch_assoc($result)) {

echo $row["id"] . ": " . $row["firstname"] . " " . $row["lastname"] . "<br>";

}

“`

模糊查询：使用LIKE子句进行模糊匹配，查找firstname以“J”开头的所有记录：

“`php

$sql = "SELECT id, firstname, lastname FROM MyGuests WHERE firstname LIKE ‘J%’";

$result = mysqli_query($conn, $sql);

while ($row = mysqli_fetch_assoc($result)) {

echo $row["id"] . ": " . $row["firstname"] . " " . $row["lastname"] . "<br>";

}

“`

聚合查询：使用GROUP BY和聚合函数（如COUNT(),SUM(),AVG()等）进行数据聚合，统计每个lastname的人数：

“`php

$sql = "SELECT lastname, COUNT(*) as count FROM MyGuests GROUP BY lastname";

$result = mysqli_query($conn, $sql);

while ($row = mysqli_fetch_assoc($result)) {

echo $row["lastname"] . ": " . $row["count"] . "<br>";

}

“`

5、数据排序

排序查询结果：使用ORDER BY子句对结果集进行排序，按lastname降序排序：

“`php

$sql = "SELECT id, firstname, lastname FROM MyGuests ORDER BY lastname DESC";

$result = mysqli_query($conn, $sql);

while ($row = mysqli_fetch_assoc($result)) {

echo $row["id"] . ": " . $row["firstname"] . " " . $row["lastname"] . "<br>";

}

“`

联合查询：使用UNION操作符合并两个或多个SELECT语句的结果集，从两个表中选取不重复的名字：

“`php

$sql = "SELECT firstname FROM MyGuests UNION SELECT name FROM AnotherTable";

$result = mysqli_query($conn, $sql);

while ($row = mysqli_fetch_assoc($result)) {

echo $row["firstname"] . "<br>";

}

“`

多表连接：使用JOIN语句从多个表中根据某个相关列进行连接查询，从用户表和订单表中选取记录：

“`php

$sql = "SELECT users.id, users.firstname, orders.order_date FROM users INNER JOIN orders ON users.id=orders.user_id";

$result = mysqli_query($conn, $sql);

while ($row = mysqli_fetch_assoc($result)) {

echo $row["id"] . ": " . $row["firstname"] . " " . $row["order_date"] . "<br>";

}

“`

分组和过滤：使用GROUP BY和HAVING子句在聚合查询中进行分组和过滤，选取有多个订单的用户及其订单数：

“`php

$sql = "SELECT users.id, users.firstname, COUNT(orders.id) as order_count FROM users INNER JOIN orders ON users.id=orders.user_id GROUP BY users.id HAVING COUNT(orders.id) > 1";

$result = mysqli_query($conn, $sql);

while ($row = mysqli_fetch_assoc($result)) {

echo $row["id"] . ": " . $row["firstname"] . " " . $row["order_count"] . "<br>";

}

“`

嵌套查询：在一个SQL语句中嵌入另一个完整的SQL语句，查找在特定日期后下过订单的用户：

“`php

$sql = "SELECT id, firstname FROM users WHERE id IN (SELECT user_id FROM orders WHERE order_date > ‘20230101’)";

$result = mysqli_query($conn, $sql);

while ($row = mysqli_fetch_assoc($result)) {

echo $row["id"] . ": " . $row["firstname"] . "<br>";

}

“`

存储过程：在MySQL中创建存储过程并在PHP中调用它，创建一个计算总销售额的过程并调用它：

“`php

$conn>query("CREATE PROCEDURE GetTotalSales() BEGIN SELECT SUM(price) FROM orders; END;");

$result = $conn>query("CALL GetTotalSales()")>fetch();

echo "Total sales: " . $result[0] . "<br>";

“`

视图：创建和使用数据库视图来简化复杂查询，创建一个视图来显示用户的订单详情，然后查询它：

“`php

$conn>query("CREATE VIEW UserOrderDetails AS SELECT users.id, users.firstname, orders.order_date, orders.price FROM users INNER JOIN orders ON users.id=orders.user_id");

$result = $conn>query("SELECT * FROM UserOrderDetails");

while ($row = $result>fetch_assoc()) {

echo $row["id"] . ": " . $row["firstname"] . " " . $row["order_date"] . " " . $row["price"] . "<br>";

}

“`

事务处理：在PDO中使用事务来确保数据的一致性，向余额表中添加一条记录并更新用户表中的余额：

“`php

$conn>beginTransaction();

try {

$conn>query("INSERT INTO balance_log (user_id, amount) VALUES (?, ?)");

$conn>query("UPDATE users SET balance = balance + ? WHERE id = ?");

$conn>commit();

} catch (Exception $e) {

$conn>rollBack();

echo "Transaction failed: " . $e>getMessage();

}

“`

错误处理：使用trycatch块来捕获和处理SQL错误，处理插入数据时的错误：

“`php

try {

$conn>query("INSERT INTO non_existent_table (col1, col2) VALUES (?, ?)");

} catch (PDOException $e) {

echo "Error: " . $e>getMessage();

}

“`

安全性考虑：使用预处理语句和参数化查询来防止SQL注入攻击，安全的登录验证：

“`php

$stmt = $conn>prepare("SELECT password FROM users WHERE username = ?");

$stmt>execute(array($username));

$password_hash = $stmt>fetchColumn();

// Verify the password…

“`

性能优化：使用索引、合理的查询结构和减少不必要的数据访问来优化查询性能，为经常用于搜索的列创建索引：

“`php

$conn>query("CREATE INDEX idx_username ON users (username)");

“`

数据库维护：定期检查和优化数据库表，清理旧数据和维护索引来保持最佳性能，优化所有表：

“`php

$conn>query("OPTIMIZE TABLE users, orders");

“`

备份与恢复：定期备份数据库以防数据丢失，并确保可以快速恢复，导出和导入数据库：

“`php

shell_exec("mysqldump u [username] p[password] [database] > backup.sql"); // Backup

shell_exec("mysql u [username] p[password] [database] < backup.sql"); // Restore

“`

字符编码：确保数据库连接和表使用相同的字符编码来避免乱码问题，设置UTF8编码：

“`php

$conn>query("SET NAMES ‘utf8’"); // For PDO/MySQLi after connection

// OR change the database and table collation to utf8_general_ci in the DB settings

“`

并发控制：在高并发环境下采取措施，如乐观锁或悲观锁，来处理数据竞争问题，使用版本号作为乐观锁：

“`php

$version = $row["version"]; // Assume there is a version column for concurrency control

if ($conn>query("UPDATE items SET price=?, version=version+1 WHERE id=? AND version=?", array($newPrice, $itemId, $version))) {

// Successful update…

} else {

// Concurrent modification happened…

}

“`

要在 PHP 中将 MySQL 数据库查询的结果以介绍的形式展示，你可以遵循以下步骤：

1、连接到 MySQL 数据库。

2、执行 SQL 查询。

3、循环遍历查询结果，将每行数据输出为介绍中的一行（<tr>）。

4、对于每行数据，再循环遍历其各个字段，将每个字段输出为介绍单元格（<td>）。

以下是一个简单的示例代码：

<?php
// 数据库连接信息
$host = 'localhost'; // 服务器地址
$user = 'username';  // 数据库用户名
$pass = 'password';  // 数据库密码
$dbname = 'database_name'; // 数据库名称
// 创建数据库连接
$conn = new mysqli($host, $user, $pass, $dbname);
// 检查连接
if ($conn>connect_error) {
    die("连接失败: " . $conn>connect_error);
}
// SQL 查询语句
$sql = 'SELECT column1, column2, column3 FROM your_table_name';
$result = $conn>query($sql);
// 检查查询是否有结果
if ($result>num_rows > 0) {
    // 开始介绍
    echo '<table border="1">'; // 边框为1像素的介绍，可以根据需要调整
    echo '<tr>'; // 表头行开始
    // 输出表头
    echo '<th>Column1</th>';
    echo '<th>Column2</th>';
    echo '<th>Column3</th>';
    echo '</tr>'; // 表头行结束
    // 输出每行数据
    while($row = $result>fetch_assoc()) {
        echo '<tr>'; // 行开始
        echo '<td>'.$row['column1'].'</td>'; // 输出每个单元格
        echo '<td>'.$row['column2'].'</td>';
        echo '<td>'.$row['column3'].'</td>';
        echo '</tr>'; // 行结束
    }
    echo '</table>'; // 介绍结束
} else {
    echo "0 结果";
}
// 关闭数据库连接
$conn>close();
?>

请确保将$host,$user,$pass,$dbname 和 SQL 查询中的your_table_name 以及表头中的列名替换成你自己的数据库信息。

代码中的<table> 标签用来创建介绍，<tr> 标签定义介绍行，<th> 标签定义表头单元格，而<td> 标签定义普通单元格，你可以使用 CSS 来进一步美化介绍。