Laravel 数据库交互 - 查询构造器
获取结果
从表中检索所有行
$articles = DB::table('article')->get();
foreach ($articles as $article) {
echo $article->title.PHP_EOL;
}从表中检索单行或单列
#通过id字段获取一行
$article = DB::table('article')->find(3);
#获取一行
$article = DB::table('article')->where('title', '339911y')->first();
echo $article->content.PHP_EOL;
#从纪录中提取单个值
echo DB::table('article')->where('title', '339911y')->value('content').PHP_EOL;获取某一列的值
$titles = DB::table('article')->pluck('title');
foreach ($titles as $title) {
echo $title.PHP_EOL;
}
#从表中检索单行或单列
$regions = DB::connection('mysql2')->table('regions')->pluck('name', 'code');
foreach ($regions as $code => $name) {
echo $code.' => '.$name.PHP_EOL;
}分块结果
#以一次 1000 条记录的块为单位检索整个 regions 表。
DB::connection('mysql2')->table('regions')->orderBy('id')->chunk(1000, function ($regions) {
foreach ($regions as $region) {
echo $region->code.' => '.$region->name.PHP_EOL;
}
//您可以通过从闭包中返回 false 来停止处理其余的块
//return false;
});
#如果您打算在分块时更新检索到的记录，最好使用 chunkById 方法
DB::connection('mysql2')->table('regions')->chunkById(100, function ($regions) {
foreach ($regions as $region) {
// echo $region->code.' => '.$region->name.PHP_EOL;
DB::table('users')->where('id', $region->id)->update(['views' => 1]);
}
});Lazily 流式传输结果
DB::table('article')->orderBy('id')->lazy()->each(function ($article) {
echo $article->title.PHP_EOL;
});
#如果您打算在迭代它们时更新检索到的记录，最好使用 lazyById 或 lazyByIdDesc 方法。
DB::table('users')->where('active', false)->lazyById()->each(function ($user) {
DB::table('users')
->where('id', $user->id)
->update(['active' => true]);
});聚合函数
$users = DB::table('users')->count();
$price = DB::table('orders')->max('price');
$price = DB::table('orders')->where('finalized', 1)->avg('price');判断记录是否存在
if (DB::table('orders')->where('finalized', 1)->exists()) {
// ...
}
if (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();
#addSelect
$query = DB::table('users')->select('name');
$users = $query->addSelect('age')->get();原生表达式
$users = DB::table('users')
->select(DB::raw('count(*) as user_count, status'))
->where('status', '<>', 1)
->groupBy('status')
->get();
#可以使用以下方法代替 DB::raw
#selectRaw
#whereRaw / orWhereRaw
#havingRaw / orHavingRaw
#orderByRaw
#groupByRawJoins
#Inner 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();
#Left Join / Right 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();Where 语句
$users = DB::table('users')
->where('votes', '=', 100)
->where('age', '>', 35)
->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 Where 语句
$users = DB::table('users')
->where('votes', '>', 100)
->orWhere('name', 'John')
->get();
#JSON Where 语句
$users = DB::table('users')
->where('preferences->dining->meal', 'salad')
->get();
#whereBetween / orWhereBetween
$users = DB::table('users')
->whereBetween('votes', [1, 100])
->get();
#whereNotBetween / orWhereNotBetween
$users = DB::table('users')
->whereNotBetween('votes', [1, 100])
->get();
#whereIn / whereNotIn / orWhereIn / orWhereNotIn
$users = DB::table('users')
->whereIn('id', [1, 2, 3])
->get();
$users = DB::table('users')
->whereNotIn('id', [1, 2, 3])
->get();
#whereNull / whereNotNull / orWhereNull / orWhereNotNull
$users = DB::table('users')
->whereNull('updated_at')
->get();
$users = DB::table('users')
->whereNotNull('updated_at')
->get();
#whereDate / whereMonth / whereDay / whereYear / whereTime
$users = DB::table('users')
->whereDate('created_at', '2016-12-31')
->get();
$users = DB::table('users')
->whereMonth('created_at', '12')
->get();
$users = DB::table('users')
->whereDay('created_at', '31')
->get();
$users = DB::table('users')
->whereYear('created_at', '2016')
->get();
$users = DB::table('users')
->whereTime('created_at', '=', '11:20:45')
->get();
#whereColumn / orWhereColumn
$users = DB::table('users')
->whereColumn('first_name', 'last_name')
->get();
$users = DB::table('users')
->whereColumn('updated_at', '>', 'created_at')
->get();
$users = DB::table('users')
->whereColumn([
['first_name', '=', 'last_name'],
['updated_at', '>', 'created_at'],
])->get();
#逻辑分组
$users = DB::table('users')
->where('name', '=', 'John')
->where(function ($query) {
$query->where('votes', '>', 100)
->orWhere('title', '=', 'Admin');
})
->get();
#子查询 Where 语句
$users = User::where(function ($query) {
$query->select('type')
->from('membership')
->whereColumn('membership.user_id', 'users.id')
->orderByDesc('membership.start_date')
->limit(1);
}, 'Pro')->get();Ordering, Grouping, Limit & Offset
#排序
$users = DB::table('users')
->orderBy('name', 'desc')
->get();
$users = DB::table('users')
->orderBy('name', 'desc')
->orderBy('email', 'asc')
->get();
#latest 和 oldest 方法可以方便让你把结果根据日期排序。查询结果默认根据数据表的 created_at 字段进行排序 。或者，你可以传一个你想要排序的列名
$user = DB::table('users')
->latest()
->first();
#随机排序
$randomUser = DB::table('users')
->inRandomOrder()
->first();
#groupBy 和 having 方法
$users = DB::table('users')
->groupBy('account_id')
->having('account_id', '>', 100)
->get();
$report = DB::table('orders')
->selectRaw('count(id) as number_of_orders, customer_id')
->groupBy('customer_id')
->havingBetween('number_of_orders', [5, 15])
->get();
$users = DB::table('users')
->groupBy('first_name', 'status')
->having('account_id', '>', 100)
->get();
#Limit 和 Offset
$users = DB::table('users')->skip(10)->take(5)->get();
#或者，你可以使用 limit 和 offset 方法。这些方法在功能上等同于 take 和 skip 方法，如下:
$users = DB::table('users')->offset(10)->limit(5)->get();条件语句 when
#when 方法只有当第一个参数为 true 的时候才执行给定的闭包
$role=1;
$users = DB::table('users')
->when($role, function ($query, $role) {
return $query->where('role_id', $role);
})
->get();
#只有当第一个参数的计算结果为 false 时，这个闭包才会执行
$sortByVotes = 0;
$users = DB::table('users')
->when($sortByVotes, function ($query, $sortByVotes) {
return $query->orderBy('votes');
}, function ($query) {
return $query->orderBy('name');
})
->get();插入语句
DB::table('users')->insert([
'email' => 'kayla@example.com',
'votes' => 0
]);
DB::table('users')->insert([
['email' => 'picard@example.com', 'votes' => 0],
['email' => 'janeway@example.com', 'votes' => 0],
]);
#自增 IDs
$id = DB::table('users')->insertGetId(
['email' => 'john@example.com', 'votes' => 0]
);Update 语句
$affected = DB::table('users')
->where('id', 1)
->update(['votes' => 1]);
DB::table('users')
->updateOrInsert(
['email' => 'john@example.com', 'name' => 'John'],
['votes' => '2']
);
#更新 JSON 字段
$affected = 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);删除语句
$deleted = DB::table('users')->where('votes', '>', 100)->delete();悲观锁
DB::table('users')
->where('votes', '>', 100)
->sharedLock()
->get();
DB::table('users')
->where('votes', '>', 100)
->lockForUpdate()
->get();调试
DB::table('users')->where('votes', '>', 100)->dd();
DB::table('users')->where('votes', '>', 100)->dump();参考
本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。
如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
腾讯云开发者
