数据库访问类链式查询方法

数据库访问类链式查询方法

一、查询构造器

1.first()：查询一条记录

控制器：Home.php

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
Class Home extends Controller{
    //链式操作
    public function chain(){
        //链式调用
        $res2 = DB::table('staff')->where('id',1002)->first(['id','name']);
        echo '<pre>';
        print_r($res2);
    }
}

路由：web.php

<?php
use Illuminate\Support\Facades\Route;
Route::get('/home/chain','Home@chain');

---

---

2.get():查询多条记录

控制器：Home.php

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
Class Home extends Controller{
    //链式操作
    //查询多条记录
    public function lists(){
        $res = DB::table('staff')->where('sex',2)->get();
        $data = $res->toArray();
        echo '<pre>';
        print_r($res);
        echo '<hr>';
        print_r($data);
    }
}

路由：web.php

<?php
use Illuminate\Support\Facades\Route;
Route::get('/home/list','Home@lists');

---

---

3.value():查询某一列的当前值

控制器：Home.php

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
Class Home extends Controller{
    //链式操作
    //查询某一列的当前值
    Public function val(){
        $res = DB::table('staff')->where('id',1002)->first(['name']);
        $res2 = DB::table('staff')->where('id',1002)->value('name');
        echo '<pre>';
        print_r($res);
        print_r($res2);
    }
}

路由：web.php

<?php
use Illuminate\Support\Facades\Route;
Route::get('/home/val','Home@val');

• first() 返回对象的键和值

• value()返回值

---

---

4.piuck():返回指定字段组成的集合

控制器：Home.php

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
Class Home extends Controller{
    //链式操作
    //pluck():返回指定字段组成的集合
    public function pluck(){
        $res = DB::table('staff')->pluck('name');
        $res2 = DB::table('staff')->get('name');
        $res3 = DB::table('staff')->pluck('name','id');
        echo '<pre>';
    print_r($res);
    print_r($res2);
    print_r($res3);
    }
}

路由：web.php

<?php
use Illuminate\Support\Facades\Route;
Route::get('/home/pluck','Home@pluck');

• pluck(value) ： 返回指定key的值组成的集合

• pluck(value, key) : 返回指定value,key的值组成的集合

---

---

二、链式操作的增删改查

1.新增操作：insert()

控制器：Home.php

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
Class Home extends Controller{
    //链式操作
    //新增记录
    public function insert2(){
        $data = [
            ["name"=>"诸葛亮","sex"=>1,"age"=>40,"hiredate"=>"2020-09-17"],
            ["name"=>"貂蝉","sex"=>0,"age"=>28,"hiredate"=>"2020-09-17"],
            ["name"=>"吕布","sex"=>1,"age"=>33,"hiredate"=>"2020-09-17"],
            ["name"=>"西施","sex"=>0,"age"=>30,"hiredate"=>"2020-09-17"],
        ];
        $res = DB::table('staff')->insert($data);
        var_dump($res);
    }
}

路由：web.php

<?php
use Illuminate\Support\Facades\Route;
Route::get('/home/insert2','Home@insert2');

---

---

---

---

1.更新操作：update()

控制器：Home.php

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
Class Home extends Controller{
    //链式操作
    //更新记录
    public function update2(){
        $res = DB::table('staff')->where('sex',1)->update(['sex'=>2]);
        var_dump($res);
    }
}

路由：web.php

<?php
use Illuminate\Support\Facades\Route;
Route::get('/home/update2','Home@update2');

---

---

---

---

3.删除操作：delete()

控制器：Home.php

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
Class Home extends Controller{
    //链式操作
    //删除记录
    public function delete2(){
        $res = DB::table('staff')->where('id',1048)->delete();
        var_dump($res);
    }
}

路由：web.php

<?php
use Illuminate\Support\Facades\Route;
Route::get('/home/delete2','Home@delete2');

---

---

三、聚合查询

1.max():最大值

控制器：Home.php

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
Class Home extends Controller{
    //链式操作
    //聚合查询
public function juhe(){
    //最大值
   $res = DB::table('staff')->max('salary');
   var_dump($res);
    }
}

路由：web.php

<?php
use Illuminate\Support\Facades\Route;
Route::get('/home/juhe','Home@juhe');

---

---

2.min():最小值

控制器：Home.php

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
Class Home extends Controller{
    //链式操作
    //聚合查询
public function juhe(){
    //最小值
   $res = DB::table('staff')->min('salary');
   var_dump($res);
    }
}

路由：web.php

<?php
use Illuminate\Support\Facades\Route;
Route::get('/home/juhe','Home@juhe');

---

---

3.avg():平均值(所有记录的平均值)

控制器：Home.php

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
Class Home extends Controller{
    //链式操作
    //聚合查询
public function juhe(){
    //平均值
   $res = DB::table('staff')->avg('salary');
   var_dump($res);
    }
}

路由：web.php

<?php
use Illuminate\Support\Facades\Route;
Route::get('/home/juhe','Home@juhe');

---

---

4.sum():求和

控制器：Home.php

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
Class Home extends Controller{
    //链式操作
    //聚合查询
public function juhe(){
    //求和
   $res = DB::table('staff')->sum('salary');
   var_dump($res);
    }
}

路由：web.php

<?php
use Illuminate\Support\Facades\Route;
Route::get('/home/juhe','Home@juhe');

---

5.count():统计记录条数

控制器：Home.php

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
Class Home extends Controller{
    //链式操作
    //聚合查询
public function juhe(){
   //count统计记录
   $res = DB::table('staff')->count('salary');
   var_dump($res);
    }
}

路由：web.php

<?php
use Illuminate\Support\Facades\Route;
Route::get('/home/juhe','Home@juhe');

---

---

6.as:临时别名

控制器：Home.php

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
Class Home extends Controller{
    //链式操作
    public function select(){
        $res = DB::table('staff')->select('id','name as username','age as userage')->get();
        echo '<pre>';
        print_r($res);
        }
}

路由：web.php

<?php
use Illuminate\Support\Facades\Route;
Route::get('/home/select','Home@select');

---

---

7.dump()打印输出结果,执行完继续执行下面的代码（只做调试用）

控制器：Home.php

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
Class Home extends Controller{
    //链式操作
    public function select(){
        $res = DB::table('staff')->select('id','name as username','age as userage')->get();
        dump($res);
        echo '<h1>继续执行</h1>';
        }
}

---

---

8.dd()执行完直接终止，不再执行下面的代码（只做调试用）

控制器：Home.php

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
Class Home extends Controller{
    //链式操作
    public function select(){
        $res = DB::table('staff')->select('id','name as username','age as userage')->get();
        dd($res);
        echo '<h1>继续执行</h1>';
        }
}

---

---

9.where():多条件查询

控制器：Home.php

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
Class Home extends Controller{
    //链式操作
    public function where(){
        $res = DB::table('staff')->where('age','<',30)->where('salary','<',10000)->get();
        echo '<pre>';
        print_r($res);
    }
}

路由：web.php

<?php
use Illuminate\Support\Facades\Route;
Route::get('/home/where','Home@where');

---

---