PHP 网站常用 SQL 语句示例

PHP 网站常用 SQL 语句示例

以下是 PHP 网站开发中常用的 SQL 语句分类整理，包含基本 CRUD 操作和安全实践建议。

1. 数据库连接（PDO 示例）

php

try { $pdo = new PDO( "mysql:host=localhost;dbname=mydb;charset=utf8mb4", "username", "password", [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_EMULATE_PREPARES => false ] );} catch (PDOException $e) { die("数据库连接失败: " . $e->getMessage());}

2. 查询操作 (SELECT)

基本查询

php

// 查询所有用户$stmt = $pdo->query("");$users = $stmt->fetchAll();// 带条件的查询$stmt = $pdo->prepare("SELECT * FROM products WHERE price > ? AND stock > 0");$stmt->execute([100]);$products = $stmt->fetchAll();

关联查询

php

// 内连接查询$sql = "SELECT u.username, o.order_date, o.amount FROM users u JOIN orders o ON u.id = o.user_id WHERE u.id = ?";$stmt = $pdo->prepare($sql);$stmt->execute([$userId]);$orders = $stmt->fetchAll();

3. 插入数据 (INSERT)

基本插入

php

$sql = "INSERT INTO users (username, email, password_hash, created_at) VALUES (?, ?, ?, NOW())";$stmt = $pdo->prepare($sql);$stmt->execute([$username, $email, $passwordHash]);$userId = $pdo->lastInsertId(); // 获取自增ID

批量插入

php

$data = [ ['Product A', 19.99], ['Product B', 29.99], ['Product C', 39.99]];$sql = "INSERT INTO products (name, price) VALUES (?, ?)";$stmt = $pdo->prepare($sql);foreach ($data as $row) { $stmt->execute($row);}

4. 更新数据 (UPDATE)

php

// 更新用户信息$sql = "UPDATE users SET email = ?, updated_at = NOW() WHERE id = ?";$stmt = $pdo->prepare($sql);$stmt->execute([$newEmail, $userId]);// 带条件更新$sql = "UPDATE products SET stock = stock - ? WHERE id = ? AND stock >= ?";$stmt = $pdo->prepare($sql);$stmt->execute([$quantity, $productId, $quantity]);

5. 删除数据 (DELETE)

php

// 删除记录$sql = "DELETE FROM sessions WHERE expires_at < NOW()";$pdo->exec($sql); // 直接执行，无需参数// 安全删除（带条件）$sql = "DELETE FROM cart WHERE user_id = ? AND product_id = ?";$stmt = $pdo->prepare($sql);$stmt->execute([$userId, $productId]);

6. 事务处理

php

try { $pdo->beginTransaction(); // 操作1: 扣除库存 $stmt = $pdo->prepare("UPDATE products SET stock = stock - ? WHERE id = ?"); $stmt->execute([$quantity, $productId]); // 操作2: 添加订单 $stmt = $pdo->prepare("INSERT INTO orders (user_id, product_id, quantity) VALUES (?, ?, ?)"); $stmt->execute([$userId, $productId, $quantity]); $pdo->commit();} catch (Exception $e) { $pdo->rollBack(); throw $e;}

7. 安全最佳实践

1. 始终使用预处理语句防止 SQL 注入
2. php
3. // 错误方式（易受攻击）$pdo->query("SELECT * FROM users WHERE username = '$username'");// 正确方式$stmt = $pdo->prepare("SELECT * FROM users WHERE username = ?");$stmt->execute([$username]);
4. 限制查询结果数量
5. php
6. $stmt = $pdo->prepare("SELECT * FROM logs WHERE user_id = ? LIMIT 100");
7. 使用参数化分页
8. php
9. $page = $_GET['page'] ?? 1;$perPage = 20;$offset = ($page - 1) * $perPage;$stmt = $pdo->prepare("SELECT * FROM articles LIMIT ? OFFSET ?");$stmt->execute([$perPage, $offset]);
10. 敏感数据使用事务
11. php
12. $pdo->beginTransaction();try { // 多个相关操作 $pdo->commit();} catch (Exception $e) { $pdo->rollBack();}

8. 实用查询示例

检查记录是否存在

php

$stmt = $pdo->prepare("SELECT 1 FROM users WHERE email = ? LIMIT 1");$stmt->execute([$email]);$exists = $stmt->fetchColumn();

统计记录数

php

$stmt = $pdo->prepare("SELECT COUNT(*) FROM products WHERE category_id = ?");$stmt->execute([$categoryId]);$count = $stmt->fetchColumn();

搜索功能

php

$search = "%" . trim($_GET['q']) . "%";$stmt = $pdo->prepare("SELECT * FROM articles WHERE title LIKE ? OR content LIKE ? LIMIT 20");$stmt->execute([$search, $search]);

这些 SQL 示例涵盖了 PHP 网站开发中最常见的数据库操作场景，遵循这些模式可以有效提高代码安全性和可维护性。