## **简介**
Laravel 数据库功能的核心就是提供流式接口与数据库进行交互的查询构建器（Query Builder），支持 MySQL、Postgres、SQLite 和 SQL Server 等常见的数据库管理系统。
> 注：关于流式接口可以查看[流接口模式](https://xueyuanjun.com/post/2828.html)了解明细。
## **使用 DB 门面执行原生 SQL 语句**
我们可以直接通过`DB`门面提供的方法执行原生的 SQL 语句（注意安全问题），`DB`门面既可以用于构建查询构建器方法链，也可以用于原生语句的执行。
~~~
//返回包含所有查询结果的`stdClass`对象数组
$users = DB::select('select * from `users`');
//当指定查询条件时，可借助PDO的参数绑定来防范SQL注入
$name = '学院君';
$users = DB::select('select * from `users` where `name` = ?', [$name]);
$name = str_random(10);
$email = str_random(10) . '@163.com';
$password = bcrypt('secret');
//插入成功，返回`true`，插入失败，则抛出`QueryException`异常
$flag = DB::insert('insert into `users` (`name`, `email`, `password`) values (?, ?, ?)', [$name, $email, $password]);
$name = str_random(8);
$id = 8;
//更新成功，返回受影响行数，更新出错，则抛出`QueryException`异常
$affectedRows = DB::update('update `users` set `name` = ? where id = ?', [$name, $id]);
$id = 8;
//删除成功，返回受影响行数，更新出错，则抛出`QueryException`异常
$affectedRows = DB::delete('delete from `users` where id = ?', [$id]);
~~~
## **使用查询构建器进行增删改查**
Laravel 数据库功能的核心组件 —— 查询构建器（说是核心，是因为 Eloquent 模型的底层也是基于这个查询构建器），是我们与数据库的交互的基础（需要直接或间接通过它来完成）。
查询构建器也是基于`DB`门面的，只不过需要调用其提供的`table`方法构建一个基于指定数据表的查询构建器。下面我们就通过查询构建器来依次实现上面通过`DB`门面执行原生 SQL 语句完成的增删改查功能。
~~~
$users = DB::table('users')->get();
//使用查询构建器进行查询，无需手动设置参数绑定来规避 SQL 注入攻击，因为 Laravel 底层会帮助我们自动实现参数绑定
$name = '学院君';
$users = DB::table('users')->where('name', $name)->get();
//默认返回所有字段，要指定查询的字段，可以通过`select`方法来实现
$user = DB::table('users')->select('id', 'name', 'email')->where('name', $name)->first();
$flag = DB::table('users')->insert([
'name' => str_random(10),
'email' => str_random(8) . '@163.com',
'password' => bcrypt('secret')
]);
//如果想要在插入之后获取对应记录的主键 ID，将`insert`方法改为调用`insertGetId`方法
$id = 11;
$affectedRows = DB::table('users')->where('id', '>', $id)->update(['name' => str_random(8)]);
$id = 11;
$affectedRows = DB::table('users')->where('id', '>=', $id)->delete();
~~~
## **查询小技巧**
我们先来介绍几个 Laravel 自带的语法糖，可以帮助我们快速获取期望的查询结果，提高编码效率：
- 获取指定字段的值，而不是一行或几行记录：
~~~
$name = '学院君';
$email = DB::table('users')->where('name', $name)->value('email');
//返回指定字段的值，无需做额外的判断和提取操作
~~~
- 判断某个字段值在数据库中是否存在对应记录，可以通过`exists`方法快速实现：
~~~
$exists = DB::table('users')->where('name', $name)->exists();
//如果存在，返回`true`，否则返回`false`。该方法还有一个与之相对的方法`doesntExist()`
~~~
- 获取查询结果以某个字段值为值构建关联数组，可通过调用`pluck`方法快速实现：
~~~
$users = DB::table('users')->where('id', '<', 10)->pluck('name', 'id');
//在传递参数到`pluck`方法的时候，键对应的字段在后面，值对应的字段在前面
~~~
- 当数据库返回结果集较大时，可以借助`chunk`方法将其分割成多个的组块依次返回进行处理：
~~~
$names = [];
DB::table('users')->orderBy('id')->chunk(5, function ($users) use (&$names) {
foreach ($users as $user) {
$names[] = $user->name;
}
});
//将获取的结果集每次返回5个进行处理——用户名依次放到`$names`数组中
~~~
## **一些复杂的查询语句**
在日常开发中，往往会涉及到一些较复杂的查询语句，比如连接查询、子查询、排序、分页、聚合查询等等，这里我们将围绕这些内容展开探讨。
### **聚合函数**
在开发后台管理系统时，经常需要对数据进行统计、求和、计算平均值、最小值、最大值等，对应的方法名分别是`count`、`sum`、`avg`、`min`、`max`：
~~~
$num = DB::table('users')->count(); # 计数 9
$sum = DB::table('users')->sum('id'); # 求和 45
$avg = DB::table('users')->avg('id'); # 平均值 5
$min = DB::table('users')->min('id'); # 最小值 1
$max = DB::table('users')->max('id'); # 最大值 9
~~~
### **高级 Where 查询**
- like查询
~~~
DB::table('posts')->where('title', 'like', 'Laravel学院%')->get();
~~~
- and查询
~~~
DB::table('posts')->where('id', '<', 10)->where('views', '>', 0)->get();
//亦可以通过传入数组参数的方式实现上述代码同样的功能
DB::table('posts')->where([
['id', '<', 10],
['views', '>', 0]
])->get();
~~~
- or查询
~~~
DB::table('posts')->where('id', '<', 10)->orWhere('views', '>', 0)->get();
//多个and查询可以通过多个where方法连接，同理多个or查询也可以通过多个orWhere方法连接
~~~
- between查询
~~~
DB::table('posts')->whereBetween('views', [10, 100])->get();
//与之相对的还有一个whereNotBeween方法
~~~
- in查询
~~~
DB::table('posts')->whereIn('user_id', [1, 3, 5, 7, 9])->get();
//使用该方法时第二个参数不能是空数组，与之相对的还有一个whereNotIn方法
~~~
- null查询
~~~
DB::table('users')->whereNull('email_verified_at')->get();
//与之相对的还有一个whereNotNull方法
~~~
- 字段本身之间比较查询
~~~
DB::table('posts')->whereColumn('updated_at', '>', 'created_at')->get(); //where updated_at > created_at
~~~
- JSON查询
从 MySQL 5.7 开始，数据库字段原生支持 JSON 类型，对于 JSON 字段的查询，和普通 where 查询并无区别，只是支持对指定 JSON 属性的查询：
~~~
DB::table('users')
->where('options->language', 'en')
->get();
//如果属性字段是个数组，还支持通过`whereJsonContains`方法对数组进行包含查询
~~~
- 参数分组，考虑下面这个 SQL 语句：
~~~
select * from posts where id <= 10 or (views > 0 and created_at < '2018-11-28 14:00');
~~~
貌似我们通过前面学到的方法解决不了这个查询语句的构造，所以我们需要引入更复杂的构建方式，那就是引入匿名函数的方式（和连接查询中构建复杂的连接条件类似）：
~~~
DB::table('posts')->where('id', '<=', 10)->orWhere(function ($query) {
$query->where('views', '>', 0)
->whereDate('created_at', '<', '2018-11-28')
->whereTime('created_at', '<', '14:00');
})->get();
~~~
在这个匿名函数中传入的`$query`变量也是一个查询构建器的实例。这一查询构建方式叫做「参数分组」，在带括号的复杂 WHERE 查询子句中都可以参考这种方式来构建查询语句。
### **连接查询**
在查询构建器中我们通过`join`方法来实现内连接（包含等值连接和不等连接），通过`leftJoin`方法实现左(外)连接，通过`rightJoin`方法实现右(外)连接，举个例子：
~~~
$posts = DB::table('posts')
->join('users', 'users.id', '=', 'posts.user_id')
->select('posts.*', 'users.name', 'users.email')
->get();
//对应的SQL语句为：select posts.*, users.name, users.email from posts inner join users on users.id = posts.user_id;
~~~
有时候，你的连接查询条件可能比较复杂，比如下面这种：
~~~
select posts.*, users.name, users.email from posts inner join users on users.id = posts.user_id and users.email_verified_at is not null where posts.views > 0;
~~~
这个时候，我们可以通过匿名函数来组装连接查询的条件来构建上面的查询语句：
~~~
$posts = DB::table('posts')
->join('users', function ($join) {
$join->on('users.id', '=', 'posts.user_id')
->whereNotNull('users.email_verified_at');
})
->select('posts.*', 'users.name', 'users.email')
->where('posts.views', '>', 0)
->get();
~~~
我们可以在匿名函数的`$join`实例上调用所有 Where 查询子句，以组装我们需要的连接查询条件。
### **联合查询**
查询构建器还支持通过`union`方法合并多个查询结果：
~~~
$posts_a = DB::table('posts')->where('views', 0);
$posts_b = DB::table('posts')->where('id', '<=', 10)->union($posts_a)->get();
对应的SQL语句为：
(select * from `posts` where `id` <= 10) union (select * from `posts` where `views` = 0)
~~~
通过上面这段代码，我们将`views = 0`和`id <= 10`这两个查询结果合并到了一起。
### **分组**
查询构建器还提供了`groupBy`方法用于对结果集进行分组：
~~~
$posts = DB::table('posts')
->groupBy('user_id')
->selectRaw('user_id, sum(views) as total_views')
->get();
//对应的SQL语句为：select user_id, sum(views) as total_views from `posts` group by `user_id`;
~~~
如果我们想要进一步对分组结果进行过滤，可以使用`having`方法，比如，要从上述分组结果中过滤出总浏览数大于等于`10`的记录，可以这么做：
~~~
$posts = DB::table('posts')
->groupBy('user_id')
->selectRaw('user_id, sum(views) as total_views')
->having('total_views', '>=', 10)
->get();
//对应的SQL语句为：select user_id, sum(views) as total_views from `posts` group by `user_id` having `total_views` >= 10;
~~~
### **分页**
在日常开发中，最常见的查询场景就是分页查询了，在查询构建器中提供了两种方式来进行分页查询：
- 通过`skip`方法和`take`方法组合进行分页，`skip`方法传入的参数表示从第几条记录开始，`take`传入的参数表示一次获取多少条记录：
~~~
$posts = DB::table('posts')->orderBy('created_at', 'desc')
->where('views', '>', 0)
->skip(10)->take(5)
->get();
//对应的SQL语句为：select * from `posts` where `views` > 0 order by `created_at` desc limit 5 offset 10;
~~~
- 通过`offset`方法和`limit`方法组合进行分页查询，`offset`表示从第几条记录开始，`limit`表示一次获取多少条记录，使用方式和`skip`和`take`类似：
~~~
$posts = DB::table('posts')->orderBy('created_at', 'desc')
->where('views', '>', 0)
->offset(10)->limit(5)
->get();
//对应的SQL语句为：
select * from `posts` where `views` > 0 order by `created_at` desc limit 5 offset 10;
~~~
