laravel CURD

检索一个列值列表
DB::table("tablename")->lists('mobile'); //5.3 及以上版本  lists 改为  pluck
返回
[
"13455556666",
"13455556667",
"13455556668",
"13455556669",
]

指定一个自定义的键列返回的数组
\DB::table('tablename')->lists('mobile','email');
返回
[
"aa@sina.com"=>"13022223335",
"bb@sina.com"=>"13022223336",
"cc@sina.com"=>"13022223337",
]

检索表中的所有行

$users = DB::table('users')->get();
foreach ($users as $user)
{
    var_dump($user->name);
}

从表检索单个行

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

检索单行单列--返回指定字段
DB::table('users')->where('name', 'John')->pluck('name');
返回数组 非字符串
["Jone"] 并不是返回 "Jone"

指定一个Select子句

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

where

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

$users = DB::table('users')->where(['votes'=>100,'name'=>'zhangsan'])->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(); 

Where Not Between 

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

Where In With An Array 

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

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

Using Where Null To Find Records With Unset Values 

$users = DB::table('users')->where('parent_id',1)->whereNull('updated_at')->get(); 

Order By, Group By, And Having 

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

Offset & Limit 

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

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

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')->orOn(...);
　　})
     ->get();
//on 多个条件

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

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

聚合
$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');

递增或递减一个列的值
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, array('name' => 'John'));

Inserts

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

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

Updates
DB::table('users')
->where('id', 1)
->update(array('votes' => 1));

Deletes
删除表中的记录

DB::table('users')->where('votes', '<', 100)->delete();
 
删除表中的所有记录

DB::table('users')->delete();

清空一个表

DB::table('users')->truncate();

共享锁
DB::table('users')->where('votes', '>',
100)->sharedLock()->get();

更新“锁”
DB::table('users')->where('votes', '>', 100)->lockForUpdate()->get();

\DB::connection()->getPdo()->exec($sql)

 

事务 使用匿名函数 任何一个异常都会触发回滚

        return \DB::transaction(function() use($user_params, $staff_params) {
            //写管理员进入user表
            $user_id = DB::table(User::TABLE_NAME)
                ->insertGetId($user_params);
            if(!$user_id) throw new Exception("写入user表失败");

            $staff_params[Staff::DB_FIELD_USER_ID] = $user_id;
            $staff_id=DB::table(Staff::TABLE_NAME)
                ->insertGetId($staff_params);
            if(!$staff_id) throw new Exception("写入staff表失败");

            return $staff_id;
        });

 

直接使用语句

$ret = DB::select("select * from tablename where id=123"); //返回一个二维数组
或者

$ret = DB::select("select * from tablename where id=?",[123]); //返回一个二维数组

使用命名绑定来执行查询

$results = DB::select('select * from users where id = :id', ['id' => 1]);

DB::insert('insert into users (id, name) values (?, ?)', [1, 'Dayle']);

$affected = DB::update('update users set votes = 100 where name = ?', ['John']);

$deleted = DB::delete('delete from users');

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

 

//chunk()每次查n条  
$student=DB::table("vipinfo")->chunk(2,function($students){  //每次查2条  
    var_dump($students);  
    if(.......) return false;  //在满足某个条件下使用return就不会再往下查了  
});  

 

打印sql
\DB::connection()->enableQueryLog();#开启log
$aa = \DB::table(tablename)->first();
$log = \DB::getQueryLog();
dd($log);
//打印
array:1 [
  0 => array:3 [
    "query" => "select * from `fuli_xwc_merchant` limit 1"
    "bindings" => []
    "time" => 29.27
  ]
]
        

 

手动使用事务

如果你想要手动开始事务从而对回滚和提交有一个完整的控制，可以使用 DB 门面的beginTransaction 方法：

DB::beginTransaction();
你可以通过 rollBack 方法回滚事务：

DB::rollBack();
最后，你可以通过 commit 方法提交事务：

DB::commit();
注意：使用 DB 门面的事务方法还可以用于控制查询构建器和 Eloquent ORM 的事务。

 like

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

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

$users = DB::table('users')->where([
    ['status', '=', '1'],
    ['subscribed', '<>', '1'],
])->get();
$users = DB::table('users')
                ->where('votes', '>', 100)
                ->orWhere('name', 'John')
                ->get();
$users = DB::table('users')
                ->whereBetween('votes', [1, 100])->get();

　　null

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

　　从一张表中获取一行/一列

$user = DB::table('users')->where('name', 'John')->first();
echo $user->name;//该方法将会返回单个 StdClass 对象：

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

 

DB:raw

\DB::table(\DB::raw("xl_channel_goods as a"))
           ->whereIn(ChannelGood::DB_FIELD_CHANNEL_GOODS_CODE, $codes)
            ->get([
                'a.*',
                \DB::raw("(select goods_name from xl_goods as b where a.goods_sn=b.goods_sn) as goods_name"),
                \DB::raw("(select group_concat(upc)  from xl_goods_upc as c where a.goods_sn=c.goods_sn group by c.goods_sn) as upc")
            ]);