Laravel - MySQL数据库的使用详解2（Query Builder用法1：查询操作）

作者：hangge | 2019-05-20 08:10

Laravel 拥有两个强大的功能来执行数据库操作：Query Builder（查询构造器）和 Eloquent ORM。在前面的文章中，我们都是通过 Query Builder 使用原生的 SQL 语句来操作数据库。其实 Query Builder 还提供了许多强大且易用的交互方式，下面通过样例进行演示。

三、Query Builder 介绍

（1）Query Builder 为执行数据库查询提供了一个干净简单的接口。它可以用来进行各种数据库操作，例如：

Retrieving records：检索记录
Inserting new records：插入记录
Deleting records：删除记录
Updating records：更新记录
Performing "Join" queries：执行 JOIN
Executing raw SQL queries：执行「原生」 SQL 语句
Filtering, grouping and sorting of records：过滤、分组和排序记录

（2）从 CURD 到排序和过滤，Query Builder 提供了方便的操作符来处理数据库中的数据。这些操作符大多数可以组合在一起，以充分利用单个查询。下面是 Query Builder 的常用操作符：

insert(array(...))：接收包含字段名和值的数组，插入数据至数据库
find($id)：检索一个主键 id 等于给定参数的记录
update(array(...))：接收含有字段名和值的数组，更新已存在的记录
delete()：删除一条记录
get()：返回一个 Illuminate\Support\Collection 结果，其中每个结果都是一个 PHP StdClass 对象的实例，实例中包含每行记录中的列名及其值
take($number)：限制查询结果数量

四、查询操作

1，简单查询

（1）返回某张表的所有记录（所有列）

$users = DB::table('user')->get();
//foreach ($users as $user) {
  //var_dump($user->username);
//}

（2）返回某张表的所有记录（指定列）

$users = DB::table('user')->get(['id','username']);

（3）返回第一条记录

$users = DB::table('user')->first();

2，根据主键 id 查询数据

使用 find 操作符号可以检索一个主键 id 等于给定参数的记录。

// 下面相当于 select * from `user` where `id` = 2
$users = DB::table('user')->find(2);

3，取得单个列数据集合

使用 pluck 操作符可从数据表中取得单一数据列的单一字段（返回结果是一个数组）

$users = DB::table('user')
            ->pluck('username');

4，avg()、sum()、count()、min()、max() 等聚集函数

// 获取平均值
$result = DB::table('user')
            ->avg('id');
            
// 获取总和
$result = DB::table('user')
            ->sum('id');

// 获取总数
$result = DB::table('user')
            ->count();
            
// 获取最小值
$result = DB::table('user')
            ->min('id');
            
// 获取最大值
$result = DB::table('user')
            ->min('id');

5，分页

（1）下面查询前 10 条记录

$users = DB::table('user')->take(10)->get();

（2）下面查询第 11 到 20 条记录

$users = DB::table('user')->skip(10)->take(10)->get();

6，排序

（1）Query Builder 的 orderBy 操作符提供了一种简单的方法来对从数据库检索的数据进行排序。

$users = DB::table('user')
            ->orderBy('id', 'desc')
            ->get();

（2）orderBy 操作符是可链接的，可以组合多个 orderBy 以获取需要实现的排序结果。

$users = DB::table('user')
            ->orderBy('id', 'desc')
            ->orderBy('username', 'asc')
            ->get();

7，分组

（1）groupBy 操作符类似于 SQL 中 GROUP BY 子句, 它只接受一个参数：用于对记录进行分组的列。

$users = DB::table('user')
            ->groupBy('id')
            ->get();

（2）可以结合 having 操作符使用：

$users = DB::table('user')
            ->groupBy('id')
            ->having('id', '>=', 2)
            ->get();

8，过滤

（1）where 的查询由提供用于过滤数据的三个参数组成：

用于比较的列名
用于比较的运算符
用于比较的值

支持的运算符如下：

=：等于
<：小于
>：大于
<=：小于等于
>=：大于等于
!=：不等于
like：模糊查询
not like：模糊查询

// 多个 where 链接表示 and 过滤
$users = DB::table('user')
            ->where('username', 'like','l%')
            ->where('id','<' ,50)
            ->get();

（2）orwhere 操作符表示 or 过滤。

$users = DB::table('user')
            ->where('username', 'like','l%')
            ->orwhere('id','<' ,50)
            ->get();

（3）whereBetween 操作符可以指定范围进行过滤，即数据在 [min, max] 范围中（包括 min 和 max）

$users = DB::table('user')
            ->whereBetween('id', [1,2])
            ->get();

（4）whereNotBetween 与上面刚好相反，即数据不在 [min, max] 范围中

$users = DB::table('user')
            ->whereNotBetween('id', [1,2])
            ->get();

（5）whereIn 与 whereNotIn 操作符表示数据是否在指定的数组集合中。

$users = DB::table('user')
            ->whereIn('id', array(1, 2, 3))
            ->get();
            
$users = DB::table('user')
            ->whereNotIn('id', array(1, 2, 3))
            ->get();

（6）whereNull 操作符可以过滤出某字段为空的记录。

$users = DB::table('user')
            ->whereNull('password')
            ->get();

（7）通过嵌套参数分组实现更复杂的 where 语句。

DB::table('users') 
    ->where('name', '=', 'John') 
    ->orWhere(function($query) { 
      $query->where('votes', '>', 100) 
            ->where('title', '<>', 'Admin'); 
    })
     ->get();

9，指定一个 Select 子句

// 只返回 username、password 这两个字段数据 
$users = DB::table('user')->select('username', 'password')->get();

// 只返回 username 字段数据，且别名变成 name
$users = DB::table('user')->select('username as name')->get();

// 只返回不重复的记录
$users = DB::table('user')->distinct()->get();

10，多表连接

（1）使用 join 操作符可以进行多表内连接：

// 连接 users、contacts、orders 这三个表
DB::table('users')
　　->join('contacts', 'users.id', '=', 'contacts.user_id')
　　->join('orders', 'users.id', '=', 'orders.user_id')
　　->select('users.id', 'contacts.phone', 'orders.price')
　　->get();

（2）使 leftJoin 操作符可以进行左连接：

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

（3）通过嵌套参数分组实现更复杂的连接语句。

DB::table('users')
　　->join('contacts', function($join)
　　{
　 　$join->on('users.id', '=', 'contacts.user_id')->orOn(...);
　　})
　　->get();
 
DB::table('users')
　　->join('contacts', function($join)
　　{
　 　$join->on('users.id', '=', 'contacts.user_id')
　      　->where('contacts.user_id', '>', 5);
　　})
　　->get();

其他