数据库: 查询构造器

获取结果

1. Retrieve all rows from the table

• get

use Illuminate\Support\Facades\DB;

$users = DB::table('users')->get();
//You can access the value of each column as a property of the object
foreach ($users as $user) {
    
    echo $user->name;
}

2. Retrieve a single row or column from a table

• first ：检索单行

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

• value ：提取单个值

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

• find：通过 id Field value gets a single row of data

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

3. 获取某一列的值

pluck

• A collection containing a single column of values

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

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

• The second parameter to specify the column in the result set to be the key

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

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

聚合

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

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

Select 说明

自定义一个 「select」 Query statement to query the specified field

1. select

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

2. distinct

$users = DB::table('users')->distinct()->get();

3. addSelect

$query = DB::table('users')->select('name');
$users = $query->addSelect('age')->get();

原生表达式

1. raw

$users = DB::table('users')
             ->select(DB::raw('count(*) as user_count, status'))
             ->where('status', '<>', 1)
             ->groupBy('status')
             ->get();

2. selectRaw : 代替 addSelect(DB::raw(…))

$orders = DB::table('orders')
                ->selectRaw('price * ? as price_with_tax', [1.0825])
                ->get();

3. whereRaw / orWhereRaw : 将原生的 「where」 inject into your query

$orders = DB::table('orders')
                ->whereRaw('price > IF(state = "TX", ?, 100)', [200])
                ->get();

4. havingRaw / orHavingRaw : Take the native string as 「having」 语句的值

$orders = DB::table('orders')
                ->select('department', DB::raw('SUM(price) as total_sales'))
                ->groupBy('department')
                ->havingRaw('SUM(price) > ?', [2500])
                ->get();

5. orderByRaw : Set the native string to 「order by」 语句的值

$orders = DB::table('orders')
                ->orderByRaw('updated_at - created_at DESC')
                ->get();

6. groupByRaw : Set the native string to group by 语句的值

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

Joins

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

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

3. Cross Join 语句

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

4. 高级 Join 语句
5. 子连接查询

$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语句

条件查询语句

1. =

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

2. 其他操作符

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

3. 条件数组

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”method can be used to negate a given set of query constraints

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

其他 Where 语句

1. whereBetween / orWhereBetween / whereNotBetween / orWhereNotBetween

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

2. whereIn / whereNotIn / orWhereIn / orWhereNotIn

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

3. whereNull / whereNotNull / orWhereNull / orWhereNotNull

whereNull('updated_at')

4. 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')

5. 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 语句

//The result of the subquery is compared with the given value
$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”子句,Compare the column with the result of the subquery
$incomes = Income::where('amount', '<', function ($query) {
    
    $query->selectRaw('avg(i.amount)')->from('incomes as i');
})->get();

Ordering, Grouping, Limit & Offset

1. orderBy 方法

//To sort by multiple columns,You can call it as many times as you want orderBy
orderBy('name', 'desc')

2. latest 和 oldest 方法 : Default according to the datasheet created_at 字段进行排序

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

3. 随机排序

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

4. Remove an existing sort

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

$unorderedUsers = $query->reorder()->get();
//Pass a column name and sort to reorder the entire query:
$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])