欢迎光临
感谢一路有你

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 asname => title) { echotitle;
}

组块结果集
一次获取结果集的一小块,然后传递每一小块数据到闭包函数进行处理,
DB::table(‘users’)->orderBy(‘id’)->chunk(100, function(users) { foreach (users as user) { // } return false;//终止组块的运行 }); 聚合函数 count, max, min, avg 和 sumusers = 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(); 左连接leftJoinusers = 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(); 联合unionfirst = 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(); whereBetweenusers = DB::table(‘users’)->whereBetween(‘votes’, [1, 100])->get();

whereNotBetween
users = DB::table(‘users’) ->whereNotBetween(‘votes’, [1, 100]) ->get(); whereInusers = DB::table(‘users’)
->whereIn(‘id’, [1, 2, 3])
->get();

whereNotIn
users = DB::table(‘users’) ->whereNotIn(‘id’, [1, 2, 3]) ->get(); whereNullusers = 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) { returnquery->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();

赞(0) 打赏
未经允许不得转载:王明昌博客 » laravel5.4 | 查询构建器
分享到: 更多 (0)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏

隐藏
变装