MYSQL分页示例-云BLOG

public function staff()
{
    if (Request::isPost()) {
        $data = Request::post(['py', 'name', 'phone', 'staffid', 'limit', 'page', 'status', 'sdate', 'edate']);
        try {
            validate(StaffValidate::class)->check($data);
        } catch (ValidateException $exception) {
            $judge = $exception->getMessage();
            return $this->show(
                config('status.error'),
                config('status.error'),
                $judge
            );
        }

        // 获取分页数据
        $page = isset($data['page']) ? $data['page'] : 1;
        $limit = isset($data['limit']) ? $data['limit'] : 10;
        $offset = ($page - 1) * $limit;

        $staffData = Db::name('base_staff')
            ->where(function ($query) use ($data) {
                if (!empty($data['py'])) {
                    $query->where('py', 'like', '%' . $data['py'] . '%');
                }
                if (!empty($data['name'])) {
                    $query->where('name', 'like', '%' . $data['name'] . '%');
                }
                if (!empty($data['phone'])) {
                    $query->where('phone', 'like', '%' . $data['phone'] . '%');
                }
                if (!empty($data['staffid'])) {
                    $query->where('staffid', $data['staffid']);
                }
                if (!empty($data['status'])) {
                    $query->where('status', $data['status']);
                }
                if (!empty($data['sdate'])) {
                    $query->where('created_at', '>=', $data['sdate']);
                }
                if (!empty($data['edate'])) {
                    $query->where('created_at', '<=', $data['edate']);
                }
            })
            ->limit($limit)
            ->offset($offset)
            ->select();

        // 获取总记录数
        $total = Db::name('base_staff')
            ->where(function ($query) use ($data) {
                if (!empty($data['py'])) {
                    $query->where('py', 'like', '%' . $data['py'] . '%');
                }
                if (!empty($data['name'])) {
                    $query->where('name', 'like', '%' . $data['name'] . '%');
                }
                if (!empty($data['phone'])) {
                    $query->where('phone', 'like', '%' . $data['phone'] . '%');
                }
                if (!empty($data['staffid'])) {
                    $query->where('staffid', $data['staffid']);
                }
                if (!empty($data['status'])) {
                    $query->where('status', $data['status']);
                }
                if (!empty($data['sdate'])) {
                    $query->where('created_at', '>=', $data['sdate']);
                }
                if (!empty($data['edate'])) {
                    $query->where('created_at', '<=', $data['edate']);
                }
            })
            ->count();

        // 返回结果
        $result = [
            'code' => 0,
            'msg' => '',
            'count' => $total,
            'data' => $staffData
        ];

        return json($result);
    }

    $select = $this->BaseInfo('2');
    $data['url'] = request()->pathinfo();
    View::assign(['data' => $data, 'select' => $select]);
    return View::fetch();
}

连多个表

在MySQL中，你可以使用 JOIN 操作将多个表连接在一起。假设你需要从 base_staff 表中获取员工信息，并且需要关联 base_department 表（部门信息）和 base_position 表（职位信息）。下面是一个示例，展示如何在控制器中实现这个查询。
示例场景
假设我们有三个表：
base_staff：员工表
base_department：部门表
base_position：职位表
我们需要查询所有员工的信息，并且关联他们所属的部门和职位信息。
表结构示例
base_staff 表结构：
id (员工ID)
name (员工姓名)
phone (员工电话)
department_id (部门ID)
position_id (职位ID)
base_department 表结构：
id (部门ID)
name (部门名称)
base_position 表结构：
id (职位ID)
name (职位名称)
SQL 查询示例

SELECT 
    s.id AS staff_id,
    s.name AS staff_name,
    s.phone AS staff_phone,
    d.id AS department_id,
    d.name AS department_name,
    p.id AS position_id,
    p.name AS position_name
FROM 
    base_staff s
LEFT JOIN 
    base_department d ON s.department_id = d.id
LEFT JOIN 
    base_position p ON s.position_id = p.id;

在控制器中实现
在 tempfile_1738758991049.php 控制器中，我们可以实现一个方法来执行这个查询，并返回结果。

public function getStaffDepartmentPositionInfo()
{
    $result = Db::name('base_staff')
        ->alias('s')
        ->leftJoin('base_department d', 's.department_id = d.id')
        ->leftJoin('base_position p', 's.position_id = p.id')
        ->field('s.id as staff_id, s.name as staff_name, s.phone as staff_phone, d.id as department_id, d.name as department_name, p.id as position_id, p.name as position_name')
        ->select();

    return json([
        'code' => 0,
        'msg' => '',
        'data' => $result
    ]);
}

解释
LEFT JOIN：
使用 leftJoin 方法将 base_staff 表与 base_department 表和 base_position 表连接起来。
连接条件分别是 s.department_id = d.id 和 s.position_id = p.id。
字段选择：
使用 field 方法选择需要的字段，并为每个字段指定别名，以便于在结果集中识别。
返回结果：
将查询结果封装到一个数组中，并返回 JSON 格式的数据。
完整示例
以下是完整的控制器方法示例，包括分页查询和获取总记录数：

public function getStaffDepartmentPositionInfo()
{
    if (Request::isPost()) {
        $data = Request::post(['py', 'name', 'phone', 'limit', 'page', 'status', 'sdate', 'edate']);
        try {
            validate(StaffValidate::class)->check($data);
        } catch (ValidateException $exception) {
            $judge = $exception->getMessage();
            return $this->show(
                config('status.error'),
                config('status.error'),
                $judge
            );
        }

        // 获取分页数据
        $page = isset($data['page']) ? $data['page'] : 1;
        $limit = isset($data['limit']) ? $data['limit'] : 10;
        $offset = ($page - 1) * $limit;

        $staffData = Db::name('base_staff')
            ->alias('s')
            ->leftJoin('base_department d', 's.department_id = d.id')
            ->leftJoin('base_position p', 's.position_id = p.id')
            ->where(function ($query) use ($data) {
                if (!empty($data['py'])) {
                    $query->where('s.py', 'like', '%' . $data['py'] . '%');
                }
                if (!empty($data['name'])) {
                    $query->where('s.name', 'like', '%' . $data['name'] . '%');
                }
                if (!empty($data['phone'])) {
                    $query->where('s.phone', 'like', '%' . $data['phone'] . '%');
                }
                if (!empty($data['status'])) {
                    $query->where('s.status', $data['status']);
                }
                if (!empty($data['sdate'])) {
                    $query->where('s.created_at', '>=', $data['sdate']);
                }
                if (!empty($data['edate'])) {
                    $query->where('s.created_at', '<=', $data['edate']);
                }
            })
            ->field('s.id as staff_id, s.name as staff_name, s.phone as staff_phone, d.id as department_id, d.name as department_name, p.id as position_id, p.name as position_name')
            ->limit($limit)
            ->offset($offset)
            ->select();

        // 获取总记录数
        $total = Db::name('base_staff')
            ->alias('s')
            ->leftJoin('base_department d', 's.department_id = d.id')
            ->leftJoin('base_position p', 's.position_id = p.id')
            ->where(function ($query) use ($data) {
                if (!empty($data['py'])) {
                    $query->where('s.py', 'like', '%' . $data['py'] . '%');
                }
                if (!empty($data['name'])) {
                    $query->where('s.name', 'like', '%' . $data['name'] . '%');
                }
                if (!empty($data['phone'])) {
                    $query->where('s.phone', 'like', '%' . $data['phone'] . '%');
                }
                if (!empty($data['status'])) {
                    $query->where('s.status', $data['status']);
                }
                if (!empty($data['sdate'])) {
                    $query->where('s.created_at', '>=', $data['sdate']);
                }
                if (!empty($data['edate'])) {
                    $query->where('s.created_at', '<=', $data['edate']);
                }
            })
            ->count();

        // 返回结果
        $result = [
            'code' => 0,
            'msg' => '',
            'count' => $total,
            'data' => $staffData
        ];

        return json($result);
    }

    $select = $this->BaseInfo('2');
    $data['url'] = request()->pathinfo();
    View::assign(['data' => $data, 'select' => $select]);
    return View::fetch();
}

解释
LEFT JOIN：
使用 leftJoin 方法将 base_staff 表与 base_department 表和 base_position 表连接起来。
连接条件分别是 s.department_id = d.id 和 s.position_id = p.id。
分页查询：
使用 limit 和 offset 方法进行分页查询。
总记录数：
使用 count 方法获取总记录数，确保分页功能的正确性。
返回结果：
将查询结果和总记录数封装到一个数组中，并返回 JSON 格式的数据。
通过这种方式，你可以在控制器中实现带有多个 LEFT JOIN 的分页查询，并获取总记录数。

连表

1. 连表查询公司和供应商信息
假设你想查询公司和供应商的关联信息，可以使用以下代码：

public function getFirmVendorInfo()
{
    $result = Db::name('base_firm')
        ->alias('f')
        ->join('base_vendor v', 'f.id = v.firm_id', 'LEFT')
        ->field('f.name as firm_name, v.name as vendor_name, v.phone')
        ->select();

    return json([
        'code' => 0,
        'msg' => '',
        'data' => $result
    ]);
}

2. 连表查询员工和部门信息
假设你想查询员工及其所属部门的信息，可以使用以下代码

public function getStaffDepartmentInfo()
{
    $result = Db::name('base_staff')
        ->alias('s')
        ->join('base_department d', 's.department_id = d.id', 'LEFT')
        ->field('s.name as staff_name, d.name as department_name, s.phone')
        ->select();

    return json([
        'code' => 0,
        'msg' => '',
        'data' => $result
    ]);
}

3. 连表查询客户和订单信息
假设你想查询客户及其订单信息，可以使用以下代码：

public function getGuestOrderInfo()
{
    $result = Db::name('base_guests')
        ->alias('g')
        ->join('base_order o', 'g.id = o.guest_id', 'LEFT')
        ->field('g.name as guest_name, o.order_number, o.total_amount')
        ->select();

    return json([
        'code' => 0,
        'msg' => '',
        'data' => $result
    ]);
}

4. 多表连表查询商品和供应商信息
假设你想查询商品及其供应商信息，可以使用以下代码：

public function getGoodsVendorInfo()
{
    $result = Db::name('base_goods')
        ->alias('g')
        ->join('base_vendor v', 'g.vendor_id = v.id', 'LEFT')
        ->field('g.name as goods_name, v.name as vendor_name, g.price')
        ->select();

    return json([
        'code' => 0,
        'msg' => '',
        'data' => $result
    ]);
}

注意事项
字段选择：确保选择的字段名称不会冲突，可以通过别名（如 as）来避免冲突。
连接类型：根据业务需求选择合适的连接类型（如 LEFT JOIN, INNER JOIN 等）。
性能优化：对于大数据量的查询，建议添加索引并合理使用分页。
这些示例展示了如何在 ThinkPHP 中进行连表查询，并返回 JSON 格式的数据。你可以根据实际需求调整表名、字段和连接条件。

在MySQL中，LEFT JOIN 是一种常用的连接操作，用于从两个或多个表中检索数据。LEFT JOIN 返回左表中的所有记录，即使右表中没有匹配的记录。如果右表中没有匹配的记录，则结果集中右表的字段将包含 NULL 值。
下面是一个示例，展示如何在控制器中使用 LEFT JOIN 进行查询。假设我们要查询员工信息，并且需要关联员工所属的部门信息。
示例场景
假设我们有两个表：
base_staff：员工表
base_department：部门表
我们需要查询所有员工的信息，并且关联他们所属的部门信息。
表结构示例
base_staff 表结构：
id (员工ID)
name (员工姓名)
department_id (部门ID)
base_department 表结构：
id (部门ID)
name (部门名称)
SQL 查询示例

SELECT 
    s.id AS staff_id,
    s.name AS staff_name,
    d.id AS department_id,
    d.name AS department_name
FROM 
    base_staff s
LEFT JOIN 
    base_department d ON s.department_id = d.id;


在控制器中实现
在 Base.php 控制器中，我们可以实现一个方法来执行这个查询，并返回结果。

public function staffWithDepartment()
{
    if (Request::isPost()) {
        $data = Request::post(['py', 'name', 'phone', 'staffid', 'limit', 'page', 'status', 'sdate', 'edate']);
        try {
            validate(StaffValidate::class)->check($data);
        } catch (ValidateException $exception) {
            $judge = $exception->getMessage();
            return $this->show(
                config('status.error'),
                config('status.error'),
                $judge
            );
        }

        // 获取分页数据
        $page = isset($data['page']) ? $data['page'] : 1;
        $limit = isset($data['limit']) ? $data['limit'] : 10;
        $offset = ($page - 1) * $limit;

        $staffData = Db::name('base_staff')
            ->alias('s')
            ->leftJoin('base_department d', 's.department_id = d.id')
            ->where(function ($query) use ($data) {
                if (!empty($data['py'])) {
                    $query->where('s.py', 'like', '%' . $data['py'] . '%');
                }
                if (!empty($data['name'])) {
                    $query->where('s.name', 'like', '%' . $data['name'] . '%');
                }
                if (!empty($data['phone'])) {
                    $query->where('s.phone', 'like', '%' . $data['phone'] . '%');
                }
                if (!empty($data['staffid'])) {
                    $query->where('s.staffid', $data['staffid']);
                }
                if (!empty($data['status'])) {
                    $query->where('s.status', $data['status']);
                }
                if (!empty($data['sdate'])) {
                    $query->where('s.created_at', '>=', $data['sdate']);
                }
                if (!empty($data['edate'])) {
                    $query->where('s.created_at', '<=', $data['edate']);
                }
            })
            ->limit($limit)
            ->offset($offset)
            ->select();

        // 获取总记录数
        $total = Db::name('base_staff')
            ->alias('s')
            ->leftJoin('base_department d', 's.department_id = d.id')
            ->where(function ($query) use ($data) {
                if (!empty($data['py'])) {
                    $query->where('s.py', 'like', '%' . $data['py'] . '%');
                }
                if (!empty($data['name'])) {
                    $query->where('s.name', 'like', '%' . $data['name'] . '%');
                }
                if (!empty($data['phone'])) {
                    $query->where('s.phone', 'like', '%' . $data['phone'] . '%');
                }
                if (!empty($data['staffid'])) {
                    $query->where('s.staffid', $data['staffid']);
                }
                if (!empty($data['status'])) {
                    $query->where('s.status', $data['status']);
                }
                if (!empty($data['sdate'])) {
                    $query->where('s.created_at', '>=', $data['sdate']);
                }
                if (!empty($data['edate'])) {
                    $query->where('s.created_at', '<=', $data['edate']);
                }
            })
            ->count();

        // 返回结果
        $result = [
            'code' => 0,
            'msg' => '',
            'count' => $total,
            'data' => $staffData
        ];

        return json($result);
    }

    $select = $this->BaseInfo('2');
    $data['url'] = request()->pathinfo();
    View::assign(['data' => $data, 'select' => $select]);
    return View::fetch();
}

解释
LEFT JOIN：
使用 leftJoin 方法将 base_staff 表和 base_department 表连接起来，连接条件是 s.department_id = d.id。
分页查询：
使用 limit 和 offset 方法进行分页查询。
总记录数：
使用 count 方法获取总记录数，确保分页功能的正确性。
返回结果：
将查询结果和总记录数封装到一个数组中，并返回 JSON 格式的数据。
通过这种方式，你可以在控制器中实现带有 LEFT JOIN 的分页查询，并获取总记录数。

目录CONTENT

MYSQL分页示例

评论区