编程马·菜鸟教程

Appearance

12.3 查询数据 select ​

SELECT 语句基础 ​

SELECT 语句用于从数据库表中检索数据，是最常用的 SQL 语句之一。

基本查询 ​

1. 查询所有列 ​

php
<?php
require_once 'db.php';
$conn = getDbConnection();

// 查询所有列
$sql = "SELECT * FROM users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"] . " - 用户名: " . $row["username"] . " - 邮箱: " . $row["email"] . "<br>";
    }
} else {
    echo "0 结果";
}

$result->free();
$conn->close();
?>

2. 查询指定列 ​

php
<?php
require_once 'db.php';
$conn = getDbConnection();

// 查询指定列
$sql = "SELECT id, username, email FROM users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"] . " - 用户名: " . $row["username"] . " - 邮箱: " . $row["email"] . "<br>";
    }
} else {
    echo "0 结果";
}

$result->free();
$conn->close();
?>

条件查询 ​

1. WHERE 子句 ​

php
<?php
require_once 'db.php';
$conn = getDbConnection();

// 条件查询
$age = 18;
$sql = "SELECT * FROM users WHERE age > $age";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"] . " - 用户名: " . $row["username"] . " - 年龄: " . $row["age"] . "<br>";
    }
} else {
    echo "0 结果";
}

$result->free();
$conn->close();
?>

2. 使用预处理语句 ​

php
<?php
require_once 'db.php';
$conn = getDbConnection();

// 使用预处理语句
$age = 18;
$stmt = $conn->prepare("SELECT * FROM users WHERE age > ?");
$stmt->bind_param("i", $age);
$stmt->execute();
$result = $stmt->get_result();

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"] . " - 用户名: " . $row["username"] . " - 年龄: " . $row["age"] . "<br>";
    }
} else {
    echo "0 结果";
}

$stmt->close();
$result->free();
$conn->close();
?>

排序查询 ​

php
<?php
require_once 'db.php';
$conn = getDbConnection();

// 排序查询
$sql = "SELECT * FROM users ORDER BY age DESC, username ASC";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"] . " - 用户名: " . $row["username"] . " - 年龄: " . $row["age"] . "<br>";
    }
} else {
    echo "0 结果";
}

$result->free();
$conn->close();
?>

限制查询 ​

php
<?php
require_once 'db.php';
$conn = getDbConnection();

// 限制查询
$sql = "SELECT * FROM users LIMIT 5";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"] . " - 用户名: " . $row["username"] . "<br>";
    }
} else {
    echo "0 结果";
}

// 分页查询
$page = 2;
$perPage = 5;
$offset = ($page - 1) * $perPage;
$sql = "SELECT * FROM users LIMIT $perPage OFFSET $offset";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    echo "<h3>第 $page 页</h3>";
    while ($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"] . " - 用户名: " . $row["username"] . "<br>";
    }
} else {
    echo "0 结果";
}

$result->free();
$conn->close();
?>

聚合函数查询 ​

php
<?php
require_once 'db.php';
$conn = getDbConnection();

// 聚合函数查询
$sql = "SELECT COUNT(*) as total_users, AVG(age) as avg_age, MAX(age) as max_age, MIN(age) as min_age FROM users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    $row = $result->fetch_assoc();
    echo "总用户数: " . $row["total_users"] . "<br>";
    echo "平均年龄: " . $row["avg_age"] . "<br>";
    echo "最大年龄: " . $row["max_age"] . "<br>";
    echo "最小年龄: " . $row["min_age"] . "<br>";
} else {
    echo "0 结果";
}

$result->free();
$conn->close();
?>

分组查询 ​

php
<?php
require_once 'db.php';
$conn = getDbConnection();

// 分组查询
$sql = "SELECT age, COUNT(*) as user_count FROM users GROUP BY age";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "年龄: " . $row["age"] . " - 用户数: " . $row["user_count"] . "<br>";
    }
} else {
    echo "0 结果";
}

$result->free();
$conn->close();
?>

连接查询 ​

1. 内连接 ​

php
<?php
require_once 'db.php';
$conn = getDbConnection();

// 内连接查询
$sql = "SELECT users.username, orders.order_id, orders.amount 
        FROM users 
        INNER JOIN orders ON users.id = orders.user_id";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "用户名: " . $row["username"] . " - 订单ID: " . $row["order_id"] . " - 金额: " . $row["amount"] . "<br>";
    }
} else {
    echo "0 结果";
}

$result->free();
$conn->close();
?>

2. 左连接 ​

php
<?php
require_once 'db.php';
$conn = getDbConnection();

// 左连接查询
$sql = "SELECT users.username, orders.order_id, orders.amount 
        FROM users 
        LEFT JOIN orders ON users.id = orders.user_id";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "用户名: " . $row["username"] . " - 订单ID: " . ($row["order_id"] ?? '无') . " - 金额: " . ($row["amount"] ?? '无') . "<br>";
    }
} else {
    echo "0 结果";
}

$result->free();
$conn->close();
?>

子查询 ​

php
<?php
require_once 'db.php';
$conn = getDbConnection();

// 子查询
$sql = "SELECT * FROM users WHERE age > (SELECT AVG(age) FROM users)";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"] . " - 用户名: " . $row["username"] . " - 年龄: " . $row["age"] . "<br>";
    }
} else {
    echo "0 结果";
}

$result->free();
$conn->close();
?>

模糊查询 ​

php
<?php
require_once 'db.php';
$conn = getDbConnection();

// 模糊查询
$keyword = "a";
$sql = "SELECT * FROM users WHERE username LIKE '%$keyword%'";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"] . " - 用户名: " . $row["username"] . "<br>";
    }
} else {
    echo "0 结果";
}

// 使用预处理语句
$stmt = $conn->prepare("SELECT * FROM users WHERE username LIKE ?");
$searchTerm = "%$keyword%";
$stmt->bind_param("s", $searchTerm);
$stmt->execute();
$result = $stmt->get_result();

if ($result->num_rows > 0) {
    echo "<h3>使用预处理语句的结果</h3>";
    while ($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"] . " - 用户名: " . $row["username"] . "<br>";
    }
} else {
    echo "0 结果";
}

$stmt->close();
$result->free();
$conn->close();
?>

结果集处理 ​

1. 转换为数组 ​

php
<?php
require_once 'db.php';
$conn = getDbConnection();

// 转换为数组
$sql = "SELECT * FROM users";
$result = $conn->query($sql);

// 方法一：手动转换
$users = [];
while ($row = $result->fetch_assoc()) {
    $users[] = $row;
}

// 方法二：使用 fetch_all（PHP 5.3+）
// $users = $result->fetch_all(MYSQLI_ASSOC);

// 输出结果
print_r($users);

// 遍历数组
foreach ($users as $user) {
    echo "id: " . $user["id"] . " - 用户名: " . $user["username"] . "<br>";
}

$result->free();
$conn->close();
?>

2. 分页查询 ​

php
<?php
require_once 'db.php';
$conn = getDbConnection();

// 分页查询
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$perPage = 5;
$offset = ($page - 1) * $perPage;

// 获取总记录数
$countSql = "SELECT COUNT(*) as total FROM users";
$countResult = $conn->query($countSql);
$countRow = $countResult->fetch_assoc();
$total = $countRow['total'];
$totalPages = ceil($total / $perPage);

// 获取当前页数据
$sql = "SELECT * FROM users ORDER BY id DESC LIMIT $perPage OFFSET $offset";
$result = $conn->query($sql);

// 显示数据
if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"] . " - 用户名: " . $row["username"] . "<br>";
    }
} else {
    echo "0 结果";
}

// 显示分页链接
echo "<br>分页: ";
for ($i = 1; $i <= $totalPages; $i++) {
    if ($i == $page) {
        echo "<strong>$i</strong> ";
    } else {
        echo "<a href='?page=$i'>$i</a> ";
    }
}

$countResult->free();
$result->free();
$conn->close();
?>

最佳实践 ​

  1. 使用预处理语句：防止 SQL 注入攻击
  2. 限制查询结果：使用 LIMIT 减少数据传输
  3. 只查询需要的列：减少数据传输和处理时间
  4. 使用索引：为频繁查询的列创建索引
  5. **避免 SELECT **：只查询必要的列
  6. 使用适当的连接类型：根据需求选择内连接或外连接
  7. 优化复杂查询：分解复杂查询，使用子查询或临时表

练习 ​

  1. 查询所有用户信息
  2. 查询年龄大于18的用户
  3. 查询用户名包含特定字符的用户
  4. 按年龄排序查询用户
  5. 分页查询用户
  6. 使用连接查询获取用户及其订单信息
  7. 使用聚合函数统计用户数据

© 2026 编程马·菜鸟教程 版权所有

基于 VitePress 构建