LARAVEL之DB类操作数据库

查询所有
$data = DB::table('user')->get();

 查询所有，并指定字段 推荐使第一种方式
$data = DB::table('user')->get(['name','age']);

 查询单条数据
$ret = DB::table('member')->where('id', 5)->first();

获取一列数据
$ret = DB::table('member')->pluck('name');

分页操作
$ret = DB::table('member')->orderBy('id','desc')->offset(0)->limit(2)->get();
limit：表示限制输出的条数
offset：从什么地方开始,起始从0开始
排序 以ID来进行倒序排列

增删改查
$ret = DB::table('member')->orderBy('id','desc')->get();

insert() 可以同时添加一条或多条，返回值是布尔类型。
insertGetId() 只能添加一条数据，返回自增的id。
添加多条记录
$ret = DB::table('member')->insert([
['name'=>'AAAA','age'=>20,'email'=>'111@111.com'],
['name'=>'BBBB','age'=>30,'email'=>'222@222.com'],
]);

 
添加数据并得到插入时的ID值

$ret = DB::table('member')->insertGetId([
'name' => '小华',
'age' => 60,
'email' => 'ff@ff.com'
]);

 
修改

$ret = DB::table('member')->where('id', 2)->update([
'name' => '修改一下',
'age'  => 50
]);

 
删除数据
$ret = DB::table('member')->where('id',2)->delete();

    //批量更新
    public function batchUpdateSql($multipleData = [])
    {
            if (empty($multipleData)) {
                return false;
            }
            $firstRow = current($multipleData);
            
            $updateColumn = array_keys($firstRow);
            // 默认以id为条件更新，如果没有ID则以第一个字段为条件
            $referenceColumn = isset($firstRow['id']) ? 'id' : current($updateColumn);
            unset($updateColumn[0]);
            
            // 拼接sql语句
            $updateSql = "UPDATE " . $this->table . " SET ";
            $sets = [];
            $bindings = [];
            foreach ($updateColumn as $uColumn) {
                $setSql = "`" . $uColumn . "` = CASE ";
                foreach ($multipleData as $data) {
                    $setSql .= "WHEN `" . $referenceColumn . "` = ? THEN ? ";
                    $bindings[] = $data[$referenceColumn];
                    $bindings[] = $data[$uColumn];
                }
                $setSql .= "ELSE `" . $uColumn . "` END ";
                $sets[] = $setSql;
            }
            
            $updateSql .= implode(', ', $sets);
            $whereInData = collect($multipleData)->pluck($referenceColumn)->values()->all();
            $whereIn = rtrim(str_repeat('?,', count($whereInData)), ',');
            $updateSql = rtrim($updateSql, ", ") . " WHERE `" . $referenceColumn . "` IN (" . $whereIn . ")";
            $bindings = array_merge($bindings, $whereInData);
            
            // 传入预处理sql语句和对应绑定数据
            return DB::update($updateSql, $bindings);
    }

Laravel left join多个字段关联的写法

DB::table('table1 as t1')
->join('table2 as t2',function($join){
    $join->on('t1.id1','=','t2.id2')
        ->where('t1.type1','=','t2.type2')
        ->where('t2.status','=','OK');
}, null,null,'left')
->get();

//相当于sql语句如下
SELECT * FROM table1 as t1
LEFT JOIN table2 as t2 
ON t1.id1 = t2.id2 
AND t1.type1 = t2.type2 
AND t2.status = 'OK' 
或者另一种写法：

$query = DB::table(self::$_table . ' as rg')
    ->select(DB::raw('rg.id,rg.user_id,rg.name,rg.parent_id, count(ri.id) as res_cnt'))
    ->leftJoin('resource_infos as ri', function($join){
        $join->on('ri.group_id', '=', 'rg.id')->where('ri.is_delete', '=','0');
    })
    ->where('rg.user_id', $userId)
    ->where('rg.type', $type)
    ->where('rg.is_delete', 0)
    ->groupBy('rg.id')
    ->orderBy('rg.id','asc');

    $groupId>0 && $query->where('rg.id', $groupId);
    !empty($name) && $query->where('rg.name', 'like', '%'.$name.'%');

   return  $query->get()->map(function ($value) {
        return (array)$value;
    })->toArray();

另外可以参考

 

 orWhere:

if(!empty($param['customer_id'])){
    $query = $query->where(function ($query) use($param){
        $query->where('c.customer_id', $param['customer_id'])->orWhere('c.full_name', $param['customer_id']);
        });
}

$query=$query->orWhere(function($query) use($chilParam) {
　　if (!empty($chilParam['province_sys_no'])) {
　　　　if (is_array($chilParam['province_sys_no'])) {
　　　　　　$query = $query->whereIn('ci.province_sys_no', $chilParam['province_sys_no']);
　　} else {
　　　　$query = $query->where('ci.province_sys_no', $chilParam['province_sys_no']);
　　}
　　}
});