查询构建器 #

一、查询构建器概述 #

1.1 什么是查询构建器 #

Laravel查询构建器提供了流畅的接口来构建数据库查询，支持链式调用，可以执行各种数据库操作。

php 
use Illuminate\Support\Facades\DB;

$users = DB::table('users')
    ->where('active', 1)
    ->orderBy('name')
    ->get();

1.2 获取结果 #

php 
// 获取所有结果
$users = DB::table('users')->get();

// 获取单行
$user = DB::table('users')->where('id', 1)->first();

// 获取单个值
$name = DB::table('users')->where('id', 1)->value('name');

// 获取列值
$names = DB::table('users')->pluck('name');

// 获取键值对
$names = DB::table('users')->pluck('name', 'id');

二、基础查询 #

2.1 SELECT查询 #

php 
// 选择所有列
$users = DB::table('users')->get();

// 选择指定列
$users = DB::table('users')->select('name', 'email')->get();

// 添加列
$users = DB::table('users')
    ->select('name')
    ->addSelect('email')
    ->get();

// 使用表达式
$users = DB::table('users')
    ->select(DB::raw('COUNT(*) as count'))
    ->get();

// 去重
$users = DB::table('users')->distinct()->get();

2.2 WHERE条件 #

php 
// 基本WHERE
$users = DB::table('users')->where('active', 1)->get();

// 比较运算符
$users = DB::table('users')->where('votes', '>=', 100)->get();
$users = DB::table('users')->where('votes', '<>', 100)->get();
$users = DB::table('users')->where('name', 'like', 'J%')->get();

// WHERE数组
$users = DB::table('users')->where([
    ['active', 1],
    ['votes', '>=', 100],
])->get();

// OR条件
$users = DB::table('users')
    ->where('active', 1)
    ->orWhere('votes', '>=', 100)
    ->get();

// WHERE分组
$users = DB::table('users')
    ->where('active', 1)
    ->where(function ($query) {
        $query->where('votes', '>', 100)
              ->orWhere('name', 'Admin');
    })
    ->get();
// SQL: WHERE active = 1 AND (votes > 100 OR name = 'Admin')

2.3 其他WHERE方法 #

php 
// WHERE BETWEEN
$users = DB::table('users')
    ->whereBetween('votes', [1, 100])
    ->get();

// WHERE NOT BETWEEN
$users = DB::table('users')
    ->whereNotBetween('votes', [1, 100])
    ->get();

// WHERE IN
$users = DB::table('users')
    ->whereIn('id', [1, 2, 3])
    ->get();

// WHERE NOT IN
$users = DB::table('users')
    ->whereNotIn('id', [1, 2, 3])
    ->get();

// WHERE NULL
$users = DB::table('users')
    ->whereNull('deleted_at')
    ->get();

// WHERE NOT NULL
$users = DB::table('users')
    ->whereNotNull('email_verified_at')
    ->get();

// WHERE DATE
$users = DB::table('users')
    ->whereDate('created_at', '2024-01-01')
    ->get();

// WHERE YEAR/MONTH/DAY
$users = DB::table('users')->whereYear('created_at', 2024)->get();
$users = DB::table('users')->whereMonth('created_at', 1)->get();
$users = DB::table('users')->whereDay('created_at', 1)->get();
$users = DB::table('users')->whereTime('created_at', '12:00:00')->get();

// WHERE COLUMN
$users = DB::table('users')
    ->whereColumn('updated_at', '>', 'created_at')
    ->get();

// WHERE EXISTS
$users = DB::table('users')
    ->whereExists(function ($query) {
        $query->select(DB::raw(1))
              ->from('orders')
              ->whereColumn('orders.user_id', 'users.id');
    })
    ->get();

2.4 JSON查询 #

php 
// JSON字段查询（MySQL 5.7+ / PostgreSQL）
$users = DB::table('users')
    ->where('options->language', 'zh')
    ->get();

// JSON包含
$users = DB::table('users')
    ->whereJsonContains('options->languages', 'en')
    ->get();

// JSON长度
$users = DB::table('users')
    ->whereJsonLength('options->languages', 3)
    ->get();

三、排序、分组、限制 #

3.1 排序 #

php 
// ORDER BY
$users = DB::table('users')
    ->orderBy('name')
    ->get();

// 降序
$users = DB::table('users')
    ->orderBy('name', 'desc')
    ->get();

// 多列排序
$users = DB::table('users')
    ->orderBy('name')
    ->orderBy('votes', 'desc')
    ->get();

// 最新/最旧
$users = DB::table('users')->latest()->get();
$users = DB::table('users')->oldest()->get();

// 随机排序
$users = DB::table('users')->inRandomOrder()->get();

// 字段存在排序
$users = DB::table('users')
    ->orderByDesc('email_verified_at')
    ->get();

3.2 分组 #

php 
// GROUP BY
$users = DB::table('users')
    ->select('department', DB::raw('COUNT(*) as count'))
    ->groupBy('department')
    ->get();

// HAVING
$users = DB::table('users')
    ->select('department', DB::raw('COUNT(*) as count'))
    ->groupBy('department')
    ->having('count', '>', 5)
    ->get();

// HAVING RAW
$users = DB::table('users')
    ->select('department', DB::raw('COUNT(*) as count'))
    ->groupBy('department')
    ->havingRaw('count > ?', [5])
    ->get();

3.3 限制和偏移 #

php 
// LIMIT
$users = DB::table('users')->take(10)->get();
// 或
$users = DB::table('users')->limit(10)->get();

// OFFSET
$users = DB::table('users')->skip(10)->take(10)->get();
// 或
$users = DB::table('users')->offset(10)->limit(10)->get();

// 分页
$page = 2;
$perPage = 10;
$users = DB::table('users')
    ->offset(($page - 1) * $perPage)
    ->limit($perPage)
    ->get();

四、聚合函数 #

4.1 基本聚合 #

php 
// COUNT
$count = DB::table('users')->count();

// MAX
$max = DB::table('users')->max('votes');

// MIN
$min = DB::table('users')->min('votes');

// SUM
$sum = DB::table('users')->sum('votes');

// AVG
$avg = DB::table('users')->avg('votes');

4.2 条件聚合 #

php 
$count = DB::table('users')
    ->where('active', 1)
    ->count();

4.3 存在检查 #

php 
// exists
$exists = DB::table('users')->where('email', 'john@example.com')->exists();

// doesntExist
$exists = DB::table('users')->where('email', 'john@example.com')->doesntExist();

五、JOIN查询 #

5.1 INNER JOIN #

php 
$users = DB::table('users')
    ->join('posts', 'users.id', '=', 'posts.user_id')
    ->select('users.*', 'posts.title')
    ->get();

5.2 LEFT JOIN #

php 
$users = DB::table('users')
    ->leftJoin('posts', 'users.id', '=', 'posts.user_id')
    ->get();

5.3 RIGHT JOIN #

php 
$users = DB::table('users')
    ->rightJoin('posts', 'users.id', '=', 'posts.user_id')
    ->get();

5.4 CROSS JOIN #

php 
$users = DB::table('users')
    ->crossJoin('posts')
    ->get();

5.5 高级JOIN #

php 
$users = DB::table('users')
    ->join('posts', function ($join) {
        $join->on('users.id', '=', 'posts.user_id')
             ->where('posts.published', 1);
    })
    ->get();

5.6 子查询JOIN #

php 
$users = DB::table('users')
    ->joinSub(
        DB::table('posts')->select('user_id', DB::raw('COUNT(*) as post_count'))->groupBy('user_id'),
        'post_counts',
        'users.id',
        '=',
        'post_counts.user_id'
    )
    ->get();

六、UNION查询 #

php 
$first = DB::table('users')
    ->whereNull('deleted_at');

$users = DB::table('users')
    ->whereNotNull('deleted_at')
    ->union($first)
    ->get();

七、插入数据 #

7.1 单条插入 #

php 
DB::table('users')->insert([
    'name' => 'John',
    'email' => 'john@example.com',
    'created_at' => now(),
]);

7.2 批量插入 #

php 
DB::table('users')->insert([
    ['name' => 'John', 'email' => 'john@example.com'],
    ['name' => 'Jane', 'email' => 'jane@example.com'],
]);

7.3 插入并获取ID #

php 
$id = DB::table('users')->insertGetId([
    'name' => 'John',
    'email' => 'john@example.com',
]);

7.4 插入或忽略 #

php 
DB::table('users')->insertOrIgnore([
    ['name' => 'John', 'email' => 'john@example.com'],
]);

7.5 插入或更新 #

php 
DB::table('users')->upsert([
    ['id' => 1, 'name' => 'John', 'email' => 'john@example.com'],
    ['id' => 2, 'name' => 'Jane', 'email' => 'jane@example.com'],
], ['id'], ['name', 'email']);

八、更新数据 #

8.1 基本更新 #

php 
$affected = DB::table('users')
    ->where('id', 1)
    ->update(['votes' => 1]);

8.2 更新JSON字段 #

php 
DB::table('users')
    ->where('id', 1)
    ->update(['options->language' => 'zh']);

8.3 增减数值 #

php 
// 增加
DB::table('users')->increment('votes');
DB::table('users')->increment('votes', 5);

// 减少
DB::table('users')->decrement('votes');
DB::table('users')->decrement('votes', 5);

// 同时更新其他字段
DB::table('users')->increment('votes', 1, ['name' => 'John']);

九、删除数据 #

9.1 基本删除 #

php 
$deleted = DB::table('users')->where('votes', '<', 100)->delete();

9.2 删除所有数据 #

php 
DB::table('users')->delete();

// 清空表（重置自增ID）
DB::table('users')->truncate();

十、分页 #

10.1 简单分页 #

php 
$users = DB::table('users')->paginate(15);

// 在视图中显示分页链接
{{ $users->links() }}

10.2 简单分页（无总数） #

php 
$users = DB::table('users')->simplePaginate(15);

10.3 游标分页 #

php 
$users = DB::table('users')->cursorPaginate(15);

10.4 分页属性 #

php 
$users = DB::table('users')->paginate(15);

$users->count();           // 当前页数量
$users->total();           // 总数量
$users->currentPage();     // 当前页
$users->lastPage();        // 最后一页
$users->perPage();         // 每页数量
$users->hasMorePages();    // 是否有更多页
$users->items();           // 数据项

十一、子查询 #

11.1 SELECT子查询 #

php 
$users = DB::table('users')
    ->addSelect([
        'last_post' => DB::table('posts')
            ->select('title')
            ->whereColumn('user_id', 'users.id')
            ->latest()
            ->limit(1),
    ])
    ->get();

11.2 WHERE子查询 #

php 
$users = DB::table('users')
    ->where('votes', '>', function ($query) {
        $query->selectRaw('AVG(votes)')->from('users');
    })
    ->get();

十二、调试 #

12.1 获取SQL #

php 
// 获取SQL语句
$sql = DB::table('users')->where('active', 1)->toSql();

// 获取带绑定的SQL
$sql = DB::table('users')->where('active', 1)->dd();

12.2 记录查询 #

php 
DB::listen(function ($query) {
    logger($query->sql, $query->bindings);
});

十三、总结 #

13.1 核心方法 #

方法 说明
select() 选择列
where() 条件
join() 连接表
orderBy() 排序
groupBy() 分组
paginate() 分页
insert() 插入
update() 更新
delete() 删除

13.2 下一步 #

掌握了查询构建器后，让我们继续学习 Eloquent ORM，了解Laravel强大的对象关系映射！

最后更新：2026-03-28