laravel数据库: 查询构造器

流火如夏xx

已于 2022-08-01 10:00:56 修改

阅读量680

点赞数 1

分类专栏： laravel 文章标签： laravel

于 2022-08-01 09:12:44 首次发布

本文链接：https://blog.csdn.net/m0_37873390/article/details/126059761

版权

laravel 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

数据库: 查询构造器

获取结果

从表中检索所有行

get

use Illuminate\Support\Facades\DB;

$users = DB::table('users')->get();
//可以将列作为对象的属性来访问每列的值
foreach ($users as $user) {
    echo $user->name;
}

从表中检索单行或单列

first ：检索单行

$user = DB::table('users')->where('name', 'John')->first();
return $user->email;

value ：提取单个值

$email = DB::table('users')->where('name', 'John')->value('email');

find：通过 id 字段值获取单行数据

//（）必填
$user = DB::table('users')->find(3);

获取某一列的值

pluck

包含单列值的集合

$titles = DB::table('users')->pluck('title');

foreach ($titles as $title) {
    echo $title;
}

第二个参数来指定结果集中要作为键的列

$titles = DB::table('users')->pluck('title', 'name');

foreach ($titles as $name => $title) {
    echo $title;
}

聚合

count, max, min, avg
判断记录是否存在

DB::table('orders')->where('finalized', 1)->count()
DB::table('orders')->where('finalized', 1)->exists()
DB::table('orders')->where('finalized', 1)->doesntExist()

Select 说明

自定义一个「select」查询语句来查询指定的字段

select

$users = DB::table('users')
            ->select('name', 'email as user_email')
            ->get();

distinct

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

selectRaw : 代替 addSelect(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();

groupByRaw : 将原生字符串设置为 group by 语句的值

$orders = DB::table('orders')
                ->select('city', 'state')
                ->groupByRaw('city, state')
                ->get();

Joins

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

Cross Join 语句

$sizes = DB::table('sizes')
            ->crossJoin('colors')
            ->get();

高级 Join 语句
子连接查询

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

Unions

$first = DB::table('users')
            ->whereNull('first_name');

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

Where语句

条件查询语句

where('votes', '=', 100)
where('votes', 100)

其他操作符

where('votes', '>=', 100)
where('votes', '<>', 100)
where('name', 'like', 'T%')

条件数组

where([
    ['status', '=', '1'],
    ['subscribed', '<>', '1'],
])

Or Where 语句

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

select * from users where votes > 100 or (name = ‘Abigail’ and votes > 50)

$users = DB::table('users')
            ->where('votes', '>', 100)
            ->orWhere(function($query) {
                $query->where('name', 'Abigail')
                      ->where('votes', '>', 50);
            })
            ->get();

Where Not 语句

“whereNot”和“orWhereNot”方法可用于否定给定的一组查询约束

    $products = DB::table('products')
                    ->whereNot(function ($query) {
                        $query->where('clearance', true)
                              ->orWhere('price', '<', 10);
                    })
                    ->get();

其他 Where 语句

whereBetween / orWhereBetween / whereNotBetween / orWhereNotBetween

whereBetween('votes', [1, 100])

whereIn / whereNotIn / orWhereIn / orWhereNotIn

whereIn('id', [1, 2, 3])

whereNull / whereNotNull / orWhereNull / orWhereNotNull

whereNull('updated_at')

whereDate / whereMonth / whereDay / whereYear / whereTime

whereDate('created_at', '2016-12-31')
whereMonth('created_at', '12')
whereDay('created_at', '31')
whereYear('created_at', '2016')
whereTime('created_at', '=', '11:20:45')

whereColumn / orWhereColumn

whereColumn('first_name', 'last_name')
whereColumn('updated_at', '>', 'created_at')
whereColumn([
	['first_name', '=', 'last_name'],
	['updated_at', '>', 'created_at'],
])

逻辑分组

select * from users where name = ‘John’ and (votes > 100 or title = ‘Admin’)

$users = DB::table('users')
           ->where('name', '=', 'John')
           ->where(function ($query) {
               $query->where('votes', '>', 100)
                     ->orWhere('title', '=', 'Admin');
           })
           ->get();

高级 Where 语句

WhereExists语句

$users = DB::table('users')
           ->whereExists(function ($query) {
               $query->select(DB::raw(1))
                     ->from('orders')
                     ->whereColumn('orders.user_id', 'users.id');
           })
           ->get();
//select * from users
//where exists (
//    select 1
//    from orders
//    where orders.user_id = users.id
//)

子查询 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();
//构建一个“where”子句，将列与子查询的结果进行比较
$incomes = Income::where('amount', '<', function ($query) {
    $query->selectRaw('avg(i.amount)')->from('incomes as i');
})->get();

Ordering, Grouping, Limit & Offset

orderBy 方法

//要按多列排序，您可以根据需要多次调用 orderBy
orderBy('name', 'desc')

latest 和 oldest 方法 : 默认根据数据表的 created_at 字段进行排序

$user = DB::table('users')
                ->latest()
                ->first();

随机排序

$randomUser = DB::table('users')
                ->inRandomOrder()
                ->first();

移除已存在的排序

$query = DB::table('users')->orderBy('name');

$unorderedUsers = $query->reorder()->get();
//传递一个列名和排序方式去重新排序整个查询:
$usersOrderedByEmail = $query->reorder('email', 'desc')->get();

分组

groupBy 和 having 方法

groupBy('account_id')
groupBy('first_name', 'status')
having('account_id', '>', 100)
havingBetween('number_of_orders', [5, 15])