laravel查询构造器实现增删改查和聚合函数的使用

1.查询构造器-新增数据

//添加单条数据
$result = DB::table('student')->insert([
    'name'    => 'kevin',
    'age'    => '18',
]);

//添加单条数据并返回自增ID值
$result = DB::table('student')->insertGetId([
    'name'    => 'luke',
    'age'    => '21',
    'sex'    => '1',
]);

//添加多条数据
$result = DB::table('student')->insert([
    [
        'name'    => 'mark',
        'age'    => '22',
        'sex'    => '1',
    ],
    [
        'name'    => 'charles',
        'age'    => '23',
        'sex'    => '1',
    ],
]);

 

2.查询构造器-修改数据

//修改指定字段数据
$num = DB::table('student')
    ->where(['name'=>'小田'])
    ->update(['age'=>24]);

//increment：自增
$num = DB::table('student')
    ->increment('age',2);

//decrement：自减，并且修改其他字段
$num = DB::table('student')
    ->where(['id'=>9])
    ->decrement('age',2, ['name'=>'mark']);

 

3.查询构造器-删除数据

$num = DB::table('student')
    ->where('id','>',7)
    ->delete();

 

4.查询构造器-查询数据

//get：查询多条数据
$result = DB::table('student')
    ->whereRaw('id > ? and age > ?', [1, 18])
    ->get();

//first：查询单条数据
$result = DB::table('student')
    ->orderBy('id', 'desc')
    ->first();

//pluck：返回单个指定字段
$result = DB::table('student')
    ->whereRaw('id > ? and age > ?', [1, 18])
    ->pluck('name');

//select：返回多个指定字段
$result = DB::table('student')
    ->whereRaw('id > ? and age > ?', [1, 18])
    ->select('id','name')
    ->get();

//chunk：将集合分割成指定大小的集合
$result = DB::table('student')
    ->get()
    ->chunk(2);

 

5.查询构造器-聚合函数

//count：查询指定字总记录数
$result = DB::table('student')->count();

//max：查询指定字最大值
$result = DB::table('student')->max('age');

//min：查询指定字最小值
$result = DB::table('student')->min('age');

//avg：查询指定字平均值
$result = DB::table('student')->avg('age');

//sum：查询指定字总和
$result = DB::table('student')->sum('age');    

 

更多请查看laravel手册：https://learnku.com/docs/laravel/8.x/queries/9401

 

【版权申明】未经博主同意,谢绝转载!(请尊重原创,博主保留追究权) https://www.cnblogs.com/facetwitter/p/15781254.html