Laravel 常用数据库操作

除了使用 Laravel Eloquent 的模型查询:https://www.anttoweb.com/kb/laravel-model/
还可以使用查询构造器来实现更加复杂的查询。

增删改

插入数据:

DB::insert('insert into users (id, name) values (?, ?)', array(1, 'Dayle'));
DB::table('users')->insert(array('email' => 'john@example.com', 'votes' => 0));

如果数据表已经有主键了, 使用 insertGetId 方法插入数据,不需要主键字段信息:
同时插入多条数据,同时让主键自增

$id = DB::table('users')->insertGetId(
    array('email' => 'john@example.com', 'votes' => 0));

一次插入多条数据

DB::table('users')->insert(array(
    array('email' => 'taylor@example.com', 'votes' => 0),
    array('email' => 'dayle@example.com', 'votes' => 0),));

删除数据:

DB::delete('delete from users');//删除所有数据
DB::table('users')->where('votes', '<', 100)->delete();//普通删除方式
DB::table('users')->delete();//删除一张表的所有数据
DB::table('users')->truncate();//清空一张表

删除表:

DB::statement('drop table users');

更新:

DB::update('update users set votes = 100 where name = ?', array('John'));
DB::table('users')
            ->where('id', 1)
            ->update(array('votes' => 1));

获取数据

按主键查询

$results = DB::select('select * from users where id = ?', array(1));

// 动态的 Where 字句
$admin = DB::table('users')->whereId(1)->first();
$users = DB::table('users')
      ->whereIn('id', [1, 2, 3])->get();
$users = DB::table('users')
      ->whereNotIn('id', [1, 2, 3])->get();
$users = DB::table('users')->where('votes', '>', 100)->get();

使用Union合并两次查询

$first = DB::table('users')->whereNull('first_name');
$users = DB::table('users')->whereNull('last_name')->union($first)->get();
unionAll 方法也是可用的, 它和 union 方法一样。

缓存一次查询的查询结果

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

获取一张表里的一条数据

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

获取一张表里的满足where条件的第一行数据的指定字段的值

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

以列表形式获取一张表里一个字段的值

$roles = DB::table('roles')->lists('title');

lists方法返回一个包含所有roles表的title字段的值的数组. 可以通过lists的第二个参数为返回的数组自定义键名:

$roles = DB::table('roles')->lists('title', 'name');

指定一个选择字句

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

为已经建立的查询添加筛选

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

使用where条件语句

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

使用or语句

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

在Where语句中使用Between子句

$users = DB::table('users')
       ->whereBetween('votes', array(1, 100))->get();
$users = DB::table('users')
      ->whereNotBetween('votes', [1, 100])->get();

Not Between

$users = DB::table('users')
                    ->whereNotBetween('votes', array(1, 100))->get();

在Where语句中使用In子句,In的内容通过数组传递

$users = DB::table('users')
                    ->whereIn('id', array(1, 2, 3))->get();
$users = DB::table('users')
                    ->whereNotIn('id', array(1, 2, 3))->get();

使用whereNull方法获取未被清除或未被初始化的记录(字段如果没有指定默认值将会是null)

$users = DB::table('users')
      ->whereNull('updated_at')->get();
DB::table('name')->whereNotNull('column')->get();

Order By语句, Group By语句, 和 Having 语句筛选

$users = DB::table('users')
                    ->orderBy('name', 'desc')
                    ->groupBy('count')
                    ->having('count', '>', 100)
                    ->get();

汉字按照首字排序

User::orderBy(DB::raw('convert(`name` using gbk)'))->get()

Offset 和 Limit语句

$users = DB::table('users')->skip(10)->take(5)->get();

关联查询

简单连接语句

DB::table('users')
            ->join('contacts', 'users.id', '=', 'contacts.user_id')
            ->join('orders', 'users.id', '=', 'orders.user_id')
            ->select('users.id', 'contacts.phone', 'orders.price');

左连接(Left Join)语句

DB::table('users')
        ->leftJoin('posts', 'users.id', '=', 'posts.user_id')
        ->get();

指定更多的连接条件:

DB::table('users')
        ->join('contacts', function($join)
        {
            $join->on('users.id', '=', 'contacts.user_id')
                 ->where('contacts.user_id', '>', 5);
        })
        ->get();

高级查询

where条件分组

orWhere用法1

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

*上面的查询将产生如下的 SQL:

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

orWhere用法2

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

*上面的查询将产生如下的 SQL:

select * from `users` where `name` = 'John' and (`votes` > 100 or `title` <> 'Admin')

where中的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
)

when条件
注意:当你的条件为false的时候,when查询的条件会失效,可以判断条件是否有值

$active = $request->input('active');
App\Flight::when($active, function ($query) use ($active) {
      return $query->where('active', '>=', $active);
  })->paginate(20);

$sum = $transaction->selectRaw('business_name,business_id,sum(paid) as account,count(*) as times')
    ->where('membership_openid', '=', $wxOpenId)//当前用户微信ID
    ->where('start_time', '>=', $start_time)//当前月的开始时间
    ->where('start_time', '<=', $end_time)//当前月的最后时间
    ->where('status', '=', 1)//交易类型成功的时候
    ->groupBy('business_name','business_id')//根据商户名称,id分组(商户名称与商户ID一一对应)
    ->when($params['type'] == 1,function ($query){
        $query->orderBy('account', 'desc')->orderBy('times', 'desc');//当type为1的时候    则排序为金额,次数
    },function ($query){
        $query->orderBy('times', 'desc')->orderBy('account', 'desc');//当type为2的时候    则排序为次数,金额
    })
    ->limit(5)
->get();

whereHas条件

// 获取所有至少有一篇评论相似于 foo% 的文章
$posts = Post::whereHas('comments', function ($query) {
    $query->where('content', 'like', 'foo%');
})->get();

$quantity = Purchase::where('purchase_date', 'like', $reportMonth . '%')
            ->where('is_new', 1)
            ->whereHas('hospital.areas.region', function ($query) use ($k) {
                    $query->where('id', $k);
             })->first(
                    array(
                        \DB::raw('SUM(purchase_10mg_quantity) as count10'),
                        \DB::raw('SUM(purchase_25mg_quantity) as count25'),
                        \DB::raw('SUM(mds_quantity) as countMds')
                    )
             )->toArray();

聚合查询

$users = DB::table('users')->count(); 求数量
$price = DB::table('orders')->max('price'); 求最大数
$price = DB::table('orders')->min('price');求最小数
$price = DB::table('orders')->avg('price');求平均数
$total = DB::table('users')->sum('votes');求总数

筛选求和

// 根据spn字段进行分组去重求offer_qty字段的和
$results = Stock::select(DB::raw("sum(offer_qty) as offer_qty_total"))->groupBy('spn');

使用原始查询表达式

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

将数据表中的某个字段+1或-1
DB::table('users')->increment('votes');
DB::table('users')->decrement('votes');

分页

查询构造器分页

每页显示 15 条数据:.

$users = DB::table('users')->paginate(15);

在分页视图中显示简单的「下一页」和「上一页」的链接,即不需要显示每个页码的链接,更推荐使用

simplePaginate 方法来执行更高效的查询,这对于大型数据集非常有用
$users = DB::table('users')->simplePaginate(15);

分页器实例方法

每个分页器实例可以通过以下方法获取额外的分页信息:

$results->count()
$results->currentPage()
$results->firstItem()
$results->hasMorePages()
$results->lastItem()
$results->lastPage() (使用 simplePaginate 时不可用)
$results->nextPageUrl()
$results->perPage()
$results->previousPageUrl()
$results->total() (使用 simplePaginate 时不可用)
$results->url($page)

事务处理:

将需要在事务模式下执行的查询放入 transaction 方法内即可

DB::transaction(function(){
    DB::table('users')->update(array('votes' => 1));
    DB::table('posts')->delete();
});

步骤

DB::beginTransaction();
DB::rollback();
DB::commit();

查询日志:

DB::enableQueryLog();
// Your Code
dd(DB::getQueryLog());

 5,160 total views,  8 views today

Revisions

No comments yet.

发表评论