laravel5.4 | 查询构建器

DB

支持:
MySQL
Postgres
SQLite
SQL Server

读写分离read/host
'mysql' => [
'read/' => [
'host' => '192.168.1.1',
],
'write' => [
'host' => '196.168.1.2'
],
'driver' => 'mysql',
'database' => 'database',
'username' => 'root',
'password' => '',
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
],

多数据库连接connection
传递给 connection 方法的 name 对应配置文件 config/database.php 中列出的某个连接
$users = DB::connection('foo')->select(...);

使用连接实例上的 getPdo 方法访问底层原生的 PDO 实例
$pdo = DB::connection()->getPdo();

原生sql查询
select, update, insert, delete 和 statement。

DB::select('select * from users where active = ?', [1]);
DB::insert('insert into users (id, name) values (?, ?)', [1, 'Dayle']);
DB::update('update users set votes = 100 where name = ?', ['John']);//受影响的行数
$deleted = DB::delete('delete from users');//返回删除的行数

运行一个通用语句
有些数据库语句不返回任何值，对于这种类型的操作
DB::statement('drop table users');

事务
事务transaction
DB::transaction(function () {
DB::table('users')->update(['votes' => 1]);
DB::table('posts')->delete();
});

处理死锁(transaction的第二参数)
第二个参数，用于定义死锁发生时事务的最大重试次数。如果尝试次数超出指定值，会抛出异常
DB::transaction(function () {
DB::table('users')->update(['votes' => 1]);

DB::table('posts')->delete();
}, 5);

手动使用事务
如果你想要手动开始事务从而对回滚和提交有一个完整的控制，可以使用 DB 门面的beginTransaction 方法：
DB::beginTransaction();
你可以通过 rollBack 方法回滚事务：
DB::rollBack();
最后，你可以通过 commit 方法提交事务：
DB::commit();

查询构建器
get 从一张表中取出所有行
$users = DB::table('users')->get();
first 从一张表中获取一行
$user = DB::table('users')->where('name', 'John')->first();
value 从一张表中获取一列
取出部分值
$email = DB::table('users')->where('name', 'John')->value('email');

获取数据列值列表
$titles = DB::table('roles')->pluck('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) {
//
}

return false;//终止组块的运行
});

聚合函数
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');

查询select

select 查询指定自定义的字段
$users = DB::table('users')->select('name', 'email as user_email')->get();

distinct 方法允许你强制查询返回不重复的结果集：
$users = DB::table('users')->distinct()->get();

addSelect 查询的结果做条件
已经有了一个查询构建器实例并且希望添加一个查询列到已存在的 select 子句
$query = DB::table('users')->select('name');
$users = $query->addSelect('age')->get();

原生表达式DB::raw 避免sql注入
$users = DB::table('users')
->select(DB::raw('count(*) as user_count, status'))
->where('status', '<>', 1)
->groupBy('status')
->get();

连接join
内连接(等值连接)
$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();

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

交叉连接crossJoin
交叉连接在第一张表和被连接表之间生成一个 a*b
$users = DB::table('sizes')
->crossJoin('colours')
->get();

高级连接语句
你还可以指定更多的高级连接子句，传递一个闭包到 join 方法作为该方法的第二个参数，该闭包将会返回允许你指定 join 子句约束的 JoinClause 对象
DB::table('users')
->join('contacts', function ($join) {
$join->on('users.id', '=', 'contacts.user_id')->orOn(...);
})
->get();

如果你想要在连接中使用“where”风格的子句，可以在查询中使用 where 和orWhere 方法。这些方法将会将列和值进行比较而不是列和列进行比较：

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

联合union
$first = DB::table('users')
->whereNull('first_name');
$users = DB::table('users')
->whereNull('last_name')
->union($first)
->get();

unionAll 方法也是有效的，并且和 union 有同样的使用方式。

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();

orwhere
$users = DB::table('users')
->where('votes', '>', 100)
->orWhere('name', 'John')
->get();

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', '2016-10-10')
->get();

whereMonth 方法用于比较字段值和一年中的指定月份：
$users = DB::table('users')
->whereMonth('created_at', '10')
->get();

whereDay 方法用于比较字段值和一月中的制定天：
$users = DB::table('users')
->whereDay('created_at', '10')
->get();

whereYear 方法用于比较字段值和指定年：
$users = DB::table('users')
->whereYear('created_at', '2016')
->get();

whereColumn
whereColumn 方法用于验证两个字段是否相等：
$users = DB::table('users')
->whereColumn('first_name', 'last_name')
->get();

还可以传递一个比较运算符到该方法：
$users = DB::table('users')
->whereColumn('updated_at', '>', 'created_at')
->get();

还可以传递多条件数组到 whereColumn 方法，这些条件通过 and 操作符进行连接：
$users = DB::table('users')
->whereColumn([
['first_name', '=', 'last_name'],
['updated_at', '>', 'created_at']
])->get();

参数分组
DB::table('users')
->where('name', '=', 'John')
->orWhere(function ($query) {
$query->where('votes', '>', 100)
->where('title', '<>', 'Admin');
})
->get();
等价于 select * from users where name = 'John' or (votes > 100 and title <> 'Admin')

where exists子句
whereExists 方法允许你编写 where exists SQL子句，whereExists 方法接收一个闭包参数，该闭包获取一个查询构建器实例从而允许你定义放置在“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 字段类型的数据库（目前是 MySQL 5.7 和 Postgres）上使用操作符 -> 查询 JSON 字段类型：

$users = DB::table('users')
->where('options->language', 'en')
->get();

$users = DB::table('users')
->where('preferences->dining->meal', 'salad')
->get();

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

latest / oldest

latest 和 oldest 方法允许你通过日期对结果进行排序，默认情况下，结果集通过 created_at 字段进行排序，或者，你可以你想要排序的字段作为字段名传入：
$user = DB::table('users')
->latest()
->first();
inRandomOrder
inRandomOrder 方法可用于对查询结果集进行随机排序
$randomUser = DB::table('users')
->inRandomOrder()
->first();

groupBy / having / havingRaw
groupBy 和 having 方法用于对结果集进行分组，having 方法和 where 方法的用法类似：
$users = DB::table('users')
->groupBy('account_id')
->having('account_id', '>', 100)
->get();
havingRaw 方法可用于设置原生字符串作为 having 子句的值，例如，我们可以这样找到所有售价大于 $2,500 的部分：
$users = DB::table('orders')
->select('department', DB::raw('SUM(price) as total_sales'))
->groupBy('department')
->havingRaw('SUM(price) > 2500')
->get();
skip / take
想要限定查询返回的结果集的数目，或者在查询中跳过给定数目的结果，可以使用skip 和 take 方法：
$users = DB::table('users')->skip(10)->take(5)->get();
作为替代方法，还可以使用 limit 和offset 方法：
$users = DB::table('users')
->offset(10)
->limit(5)
->get();

条件子句
某些条件为 true 的时候才将条件子句应用到查询。例如，你可能只想给定值在请求中存在的情况下才应用where 语句，这可以通过 when 方法实现：
$role = $request->input('role');
$users = DB::table('users')
->when($role, function ($query) use ($role) {
return $query->where('role_id', $role);
})
->get();
when 方法只有在第一个参数为 true 的时候才执行给定闭包，如果第一个参数为 false，则闭包不执行。
你可以传递另一个闭包作为 when 方法的第三个参数，该闭包会在第一个参数为false 的情况下执行。为了演示这个特性如何使用，我们来配置一个查询的默认排序：
$sortBy = null;
$users = DB::table('users')
->when($sortBy, function ($query) use ($sortBy) {
return $query->orderBy($sortBy);
}, function ($query) {
return $query->orderBy('name');
})
->get();

插入insert
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 insertGetId 方法来插入记录并返回ID值：
$id = DB::table('users')->insertGetId(
['email' => 'john@example.com', 'votes' => 0]
);
注：当使用 PostgresSQL 时insertGetId 方法默认自增列被命名为 id，如果你想要从其他“序列”获取ID，可以将序列名作为第二个参数传递到insertGetId 方法。

更新（Update）
DB::table('users')
->where('id', 1)
->update(['votes' => 1]);

更新JSON字段
更新 JSON 字段的时候，需要使用 -> 语法访问 JSON 对象上相应的值，该操作只能用于支持 JSON 字段类型的数据库：

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']);

删除（Delete）
DB::table('users')->delete();
DB::table('users')->where('votes', '>', 100)->delete();

删除所有列并将自增ID置为0，可以使用 truncate 方法：
DB::table('users')->truncate();

悲观锁
查询构建器还提供了一些方法帮助你在select 语句中实现“悲观锁”。可以在查询中使用 sharedLock 方法从而在运行语句时带一把”共享锁“。共享锁可以避免被选择的行被修改直到事务提交：

DB::table('users')->where('votes', '>', 100)->sharedLock()->get();
此外你还可以使用 lockForUpdate 方法。“for update”锁避免选择行被其它共享锁修改或删除：
DB::table('users')->where('votes', '>', 100)->lockForUpdate()->get();