Laravel操作数据库的三种方法

DB facade实现CURD操作

//引入DB类命名空间
use Illuminate\Support\Facades\DB;
//新增数据
DB::insert("INSERT INTO student(`name`,age,sex) VALUES(?,?,?)",['王舞',20,'女']);
//查询数据
DB::select('select * from student');
//修改数据
DB::update('update student set name=? WHERE id=?',['陆地',6]);
//删除指定数据
DB::update('delete from student WHERE id=?',[6]);

查询构造器实现对数据的CURD操作

插入一条新数据,返回为布尔值

$bool = DB::table('student')->insert(['name' => 'hellw', 'age' => 20]);

获取插入数据的id

$id = DB::table('student')->insertGetId(['name' => '王五', 'age' => 33]);

批量插入多条数据

$bool = DB::table('student')->insert([
   ['name' => 'john1', 'age' => 20, 'sex' => '男'],
   ['name' => 'john2', 'age' => 21, 'sex' => '男']
]);

获取所有数据,返回结果集对象

$students = DB::table('student')->get();

更新数据,返回影响的行数

$num = DB::table('student')->where('id',7)->update(['name'=>'啊啊啊','age'=>30]);

自增操作,返回影响的行数

$num = DB::table('student')->increment('age');

自增指定增量,返回影响的行数

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

自增同时修改其他数据

$num = DB::table('student')->where('id',16)->increment('age', 1, ['name' => 'John']);

自减,返回影响的行数

$num = DB::table('student')->decrement('age',2);
$num = DB::table('student')->decrement('age');

删除数据

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

清空数据表,不返回任何值,慎用

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

使用whereIn条件语句删除多条数据,反语句whereNotIn()

$num = DB::table('student')->whereIn('id', [15,17,19])->delete();

使用whereBetween条件查询,在自定条件之前的数据

$students = DB::table('student')->whereBetween('id', [4,10])->get();
$students = DB::table('student')->whereNotBetween('id', [4,10])->get();
$student = DB::table('student')->find([2]);

动态查询,根据id获取一条数据,frist()获取一条数据

$student = DB::table('student')->whereId(5)->first();
$student = DB::table('student')->whereIdAndAgeAndSex('14','16','女')->first();
$student = DB::table('student')->whereIdOrAgeOrSex('14','16','女')->get();

偏移及限制,skip=开始位置,take=数量,可以理解为limit(skip,take)

$students = DB::table('student')->skip(5)->take(5)->get();

聚合函数

$res = DB::table('student')->count();
$res = DB::table('student')->max('age');
$res = DB::table('student')->min('age');
$res = DB::table('student')->avg('age');
$res = DB::table('student')->sum('age');

标签: Laravel

¥ 1.88
微信扫描即可打赏
服务器好贵
网站需要运营
给点小费以表支持