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)
基本查询
// 查询所有用户$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. 安全最佳实践
- 始终使用预处理语句防止 SQL 注入
- php
- // 错误方式（易受攻击）$pdo->query("SELECT * FROM users WHERE username = '$username'");// 正确方式$stmt = $pdo->prepare("SELECT * FROM users WHERE username = ?");$stmt->execute([$username]);
- 限制查询结果数量
- php
- $stmt = $pdo->prepare("SELECT * FROM logs WHERE user_id = ? LIMIT 100");
- 使用参数化分页
- php
- $page = $_GET['page'] ?? 1;$perPage = 20;$offset = ($page - 1) * $perPage;$stmt = $pdo->prepare("SELECT * FROM articles LIMIT ? OFFSET ?");$stmt->execute([$perPage, $offset]);
- 敏感数据使用事务
- php
- $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 网站开发中最常见的数据库操作场景，遵循这些模式可以有效提高代码安全性和可维护性。
热门跟贴