[TOC]
# 查询构造器
## 简介
Laravel 的查询构造器使用 PDO 参数绑定来保护您的应用程序免受 SQL 注入攻击。
## 获取结果
### 从一个数据表中获取所有行
```
use Illuminate\Support\Facades\DB;
$users = DB::table('users')->get();
// 返回包含 Illuminate\Support\Collection 的结果，每个结果都是 StdClass 对象的实例
foreach ($users as $user) {
echo $user->name;
}
```
### 从数据表中获取单行或单列
```
// 返回 StdClass 对象
$user = DB::table('users')->where('name', 'John')->first();
echo $user->name;
// 返回单个字段的值
$email = DB::table('users')->where('name', 'John')->value('email');
```
### 获取一列的值
```
// 返回单列值的集合
$titles = DB::table('roles')->pluck('title');
foreach ($titles as $title) {
echo $title;
}
// 返回的集合中指定字段的自定义键值
$roles = DB::table('roles')->pluck('title', 'name');
foreach ($roles as $name => $title) {
echo $title;
}
```
### 分块结果
```
DB::table('users')->orderBy('id')->chunk(100, function ($users) {
foreach ($users as $user) {
//
}
});
// 终止查询
DB::table('users')->orderBy('id')->chunk(100, function ($users) {
// Process the records...
return false;
});
// 使用 chunkById 方法根据记录的主键自动对结果进行分页，保证查询结果
DB::table('users')->where('active', false)
->chunkById(100, function ($users) {
foreach ($users as $user) {
DB::table('users')
->where('id', $user->id)
->update(['active' => true]);
}
});
```
> 注意：在块的回调里面更新或删除记录时，对主键或外键的任何更改都可能影响块查询。 这可能会导致记录没有包含在分块结果中。
### 聚合
```
// count, max，min，avg，sum
$users = DB::table('users')->count();
$price = DB::table('orders')->max('price');
$price = DB::table('orders')
->where('finalized', 1)
->avg('price');
```
### 判断记录是否存在
```
return DB::table('orders')->where('finalized', 1)->exists();
return DB::table('orders')->where('finalized', 1)->doesntExist();
```
## 查询字段
```
// Select 指定查询字段
$users = DB::table('users')->select('name', 'email as user_email')->get();
$users = DB::table('users')->distinct()->get();
// 加入查询字段
$query = DB::table('users')->select('name');
$users = $query->addSelect('age')->get();
```
## 原生表达式
```
// 使用 DB::raw 创建原生表达式
$users = DB::table('users')
->select(DB::raw('count(*) as user_count, status'))
->where('status', '<>', 1)
->groupBy('status')
->get();
```
>[success] 注意：原生表达式将会被当做字符串注入到查询中，因此你应该小心使用，避免创建 SQL 注入的漏洞。
### 原生方法
```
// selectRaw 方法代替 select(DB::raw(...))。第二个参数是可选项，值为绑定参数的数组
$orders = DB::table('orders')
->selectRaw('price * ? as price_with_tax', [1.0825])
->get();
// whereRaw 和 orWhereRaw 方法将原生的 where 注入到的查询中。
$orders = DB::table('orders')
->whereRaw('price > IF(state = "TX", ?, 100)', [200])
->get();
// havingRaw 和 orHavingRaw 方法用于将原生字符串设置为 having 语句的值
$orders = DB::table('orders')
->select('department', DB::raw('SUM(price) as total_sales'))
->groupBy('department')
->havingRaw('SUM(price) > ?', [2500])
->get();
// orderByRaw 方法用于将原生字符串设置为 order by 子句的值
$orders = DB::table('orders')
->orderByRaw('updated_at - created_at DESC')
->get();
```
## Join
```
// Inner Join Clause
$users = DB::table('users')
->join('contacts', 'users.id', '=', 'contacts.user_id')
->join('orders', 'users.id', '=', 'orders.user_id')
->select('users.*', 'contacts.phone', 'orders.price')
->get();
// Left Join 语句
$users = DB::table('users')
->leftJoin('posts', 'users.id', '=', 'posts.user_id')
->get();
$users = DB::table('users')
->rightJoin('posts', 'users.id', '=', 'posts.user_id')
->get();
// Cross Join 语句
$users = DB::table('sizes')
->crossJoin('colours')
->get();
// 高级 Join 语句
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();
// 子连接查询
// 使用 joinSub，leftJoinSub，rightJoinSub 方法关联一个查询作为子查询。
// 他们每一种方法都会接收三个参数：子查询，表别名和定义关联字段的闭包。
$latestPosts = DB::table('posts')
->select('user_id', DB::raw('MAX(created_at) as last_post_created_at'))
->where('is_published', true)
->groupBy('user_id');
$users = DB::table('users')
->joinSub($latestPosts, 'latest_posts', function($join) {
$join->on('users.id', '=', 'latest_posts.user_id');
})->get();
```
## Union
```
// union，unionAll
$first = DB::table('users')
->whereNull('first_name');
$users = DB::table('users')
->whereNull('last_name')
->union($first)
->get();
```
## Where 语句
### 简单的 Where 语句
```
// 调用 where 最基本的方式是需要传递三个参数：
// 第一个参数是列名，
// 第二个参数是任意一个数据库系统支持的运算符，
// 第三个是该列要比较的值。
$users = DB::table('users')->where('votes', '=', 100)->get();
// 相等比较
$users = DB::table('users')->where('votes', 100)->get();
// 其他的运算符
$users = DB::table('users')
->where('votes', '>=', 100)
->get();
$users = DB::table('users')
->where('votes', '<>', 100)
->get();
$users = DB::table('users')
->where('name', 'like', 'T%')
->get();
// 数组条件
$users = DB::table('users')->where([
['status', '=', '1'],
['subscribed', '<>', '1'],
])->get();
```
### Or 语句
```
$users = DB::table('users')
->where('votes', '>', 100)
->orWhere('name', 'John')
->get();
```
### 其他 Where 语句
```
// whereBetween
$users = DB::table('users')->whereBetween('votes', [1, 100])->get();
// whereNotBetween
$users = DB::table('users')->whereNotBetween('votes', [1, 100])->get();
// whereIn
$users = DB::table('users')->whereIn('id', [1, 2, 3])->get();
// whereNotIn
$users = DB::table('users')->whereNotIn('id', [1, 2, 3])->get();
// whereNull
$users = DB::table('users')->whereNull('updated_at')->get();
// whereNotNull
$users = DB::table('users')->whereNotNull('updated_at')->get();
// whereDate
$users = DB::table('users')->whereDate('created_at', '2018-09-08')->get();
// whereMonth
$users = DB::table('users')->whereMonth('created_at', '9')->get();
// whereDay
$users = DB::table('users')->whereDay('created_at', '8')->get();
// whereYear
$users = DB::table('users')->whereYear('created_at', '2018')->get();
// whereTime
$users = DB::table('users')->whereTime('created_at', '=', '11:20:45')->get();
// whereColumn，比较两个字段的值是否相等
$users = DB::table('users')->whereColumn('first_name', 'last_name')->get();
// 使用比较运算符
$users = DB::table('users')->whereColumn('updated_at', '>', 'created_at')->get();
// 传递数组 用 and 运算符链接
$users = DB::table('users')
->whereColumn([
['first_name', '=', 'last_name'],
['updated_at', '>', 'created_at']
])->get();
```
### 参数分组
```
// 生成SQL：select * from users where name = 'John' and (votes > 100 or title = 'Admin')
DB::table('users')
->where('name', '=', 'John')
->where(function ($query) {
$query->where('votes', '>', 100)
->orWhere('title', '=', 'Admin');
})
->get();
```
### Where Exists 语句
```
DB::table('users')
->whereExists(function ($query) {
$query->select(DB::raw(1))
->from('orders')
->whereRaw('orders.user_id = users.id');
})
->get();
// 生成SQL
select * from users
where exists (
select 1 from orders where orders.user_id = users.id
)
```
### JSON Where 语句
Laravel 也支持查询 JSON 类型的字段（仅在对 JSON 类型支持的数据库上）。目前，本特性仅支持 MySQL 5.7、PostgreSQL、SQL Server 2016 以及 SQLite 3.9.0 (with the[JSON1 extension](https://www.sqlite.org/json1.html))。使用`->`操作符查询 JSON 数据：
```
$users = DB::table('users')
->where('options->language', 'en')
->get();
$users = DB::table('users')
->where('preferences->dining->meal', 'salad')
->get();
// 使用 whereJsonContains 来查询 JSON 数组
$users = DB::table('users')
->whereJsonContains('options->languages', 'en')
->get();
// MySQL 和 PostgreSQL 的 whereJsonContains 可以支持多个值
$users = DB::table('users')
->whereJsonContains('options->languages', ['en', 'de'])
->get();
// 使用 whereJsonLength 来查询 JSON 数组的长度
$users = DB::table('users')
->whereJsonLength('options->languages', 0)
->get();
$users = DB::table('users')
->whereJsonLength('options->languages', '>', 1)
->get();
```
## Ordering, Grouping, Limit, & Offset
### orderBy
```
$users = DB::table('users')
->orderBy('name', 'desc')
->get();
```
### latest / oldest
```
// latest、oldest 方法通过日期排序。
// 默认使用 created_at 列作为排序依据，也可以传递自定义的列名。
$user = DB::table('users')
->latest()
->first();
```
### inRandomOrder
```
// 随机排序，使用此方法随机找到一个用户
$randomUser = DB::table('users')
->inRandomOrder()
->first();
```
### groupBy / having
```
$users = DB::table('users')
->groupBy('account_id')
->having('account_id', '>', 100)
->get();
// 多个 groupBy 参数
$users = DB::table('users')
->groupBy('first_name', 'status')
->having('account_id', '>', 100)
->get();
```
### skip / take
```
// 限制结果的返回数量，或跳过指定数量的结果。
$users = DB::table('users')->skip(10)->take(5)->get();
$users = DB::table('users')
->offset(10)
->limit(5)
->get();
```
## 条件语句
```
// when 方法只有在第一个参数为 true 的时候才执行闭包。
$role = $request->input('role');
$users = DB::table('users')
->when($role, function ($query, $role) {
return $query->where('role_id', $role);
})
->get();
// 第三个参数可选，在第一个参数为 false 的情况下执行。
$sortBy = null;
$users = DB::table('users')
->when($sortBy, function ($query, $sortBy) {
return $query->orderBy($sortBy);
}, function ($query) {
return $query->orderBy('name');
})
->get();
```
## 插入
```
// 插入单条记录
DB::table('users')->insert(
['email' => 'john@example.com', 'votes' => 0]
);
// 插入多条记录
DB::table('users')->insert([
['email' => 'taylor@example.com', 'votes' => 0],
['email' => 'dayle@example.com', 'votes' => 0]
]);
```
### 自增 ID
```
$id = DB::table('users')->insertGetId(
['email' => 'john@example.com', 'votes' => 0]
);
```
> 当使用 PostgreSQL 时，`insertGetId`方法将默认把`id`作为自动递增字段的名称。如果你要从其他「序列」来获取 ID ，则可以将字段名称作为第二个参数传递给`insertGetId`方法。
## 更新
```
DB::table('users')
->where('id', 1)
->update(['votes' => 1]);
```
### 更新或者新增
```
// updateOrInsert 方法将首先尝试使用第一个参数的键和值对来查找匹配的数据库记录。
// 如果记录存在，则使用第二个参数中的值去更新记录。
// 如果找不到记录，将插入一个新记录，更新的数据是两个数组的集合。
DB::table('users')
->updateOrInsert(
['email' => 'john@example.com', 'name' => 'John'],
['votes' => '2']
);
```
### 更新 JSON 字段
```
// 只能支持 MySQL 5.7+
DB::table('users')
->where('id', 1)
->update(['options->enabled' => true]);
```
### 自增与自减
```
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']);
```
## 删除
```
// 删除记录
DB::table('users')->delete();
DB::table('users')->where('votes', '>', 100)->delete();
// 清空表，重置自增 ID 为零
DB::table('users')->truncate();
```
## 悲观锁
```
// 共享锁
DB::table('users')->where('votes', '>', 100)->sharedLock()->get();
// 排它锁
DB::table('users')->where('votes', '>', 100)->lockForUpdate()->get();
```
- 入门指南
- 安装
- 部署
- 基础功能
- 路由
- 中间件
- CSRF 保护
- 控制器
- 请求
- 响应
- 视图
- URL
- Session
- 表单验证
- 错误
- 日志
- 前端开发
- Blade 模板
- 本地化
- 脚手架
- 编译资源 Mix
- 安全相关
- 用户认证
- API 认证
- 综合话题
- 命令行
- 广播
- 缓存
- 集合
- 事件
- 文件存储
- 辅助函数
- 邮件发送
- 消息通知
- 扩展包开发
- 队列
- 任务调度
- 数据库
- 快速入门
- 查询构造器
- 分页
- 数据库迁移
- 数据填充
- Redis
- Eloquent ORM
- 快速入门
- 速查表
- Artisan
- Auth
- Blade
- Cache
- Collection
- Composer
- Config
- Container
- Cookie
- DB
- Environment
- Event
- File
- Helper
- Input
- Lang
- Log
- Model
- Pagination
- Queue
- Redirect
- Request
- Response
- Route
- SSH
- Schema
- Security
- Session
- Storage
- String
- URL
- UnitTest
- Validation
- View