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' => '[email protected]', 'votes' => 0] ); 一次性通过传入多个数组来插入多条记录 DB::table('users')->insert([ ['email' => '[email protected]', 'votes' => 0], ['email' => '[email protected]', 'votes' => 0] ]); 自增ID insertGetId 方法来插入记录并返回ID值： $id = DB::table('users')->insertGetId( ['email' => '[email protected]', '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();