Laravel是世界上最好的PHP Web开发框架,没有之一。Laravel提供了3种操作数据库方式:DB facade(原始方式)、查询构造器和Eloquent ORM。
数据库模型Model
class Student extends Model{
// 指定表名
protected $table = 'student';
// 指定主键
protected $primaryKey = 'id';
// 指定允许批量赋值的字段
protected $fillable = ['name','age'];
// 指定禁止批量赋值的字段
protected $guarded = ['sex'];
// 自动维护时间戳
public $timestamps = true;
// 数据库日期列的存储格式(unix时间戳存储 int类型)
protected $dateFormat = 'U'
}
数据库操作三种方式
- 查询
$students = DB::select('select * from student');
- 新增
$bool = DB::insert('insert into student(name, age) values(?, ?)', ['zhangming', 18]);
- 更新
$num = DB::update('update student set age = ? where id=?', [20, 1]);
- 删除
$num = DB::delete('delete from student where id= ? ', [1]);
- 查询
// get()获取所有记录;
$students = DB::table('student')->get();
// first()获取第一条记录;
$first = DB::table('student')->where('id', '>', 1)->orderBy('id', 'desc')->first();
// first()获取(id>10)的记录;
$first = DB::table('student')->where('id', '>', 10)->orderBy('id', 'desc')->get();
// whereRaw()多添件查询
$students = DB::table('student')->whereRaw('id >? and age > ?', [1,2])->get();
// pluck()返回字段
$students = DB::table('student')->whereRaw('id >? and age > ?', [1,2])->pluck('name');
// pluck()返回一个字段为值,第二个字段为键
$students = DB::table('student')->whereRaw('id >? and age > ?', [1,2])->pluck('name','id');
// select()指定字段查找
$students = DB::table('student')->select('id', 'name', 'age')->get();
// chunk()分段查找,每次查询多少条
DB::table('student')
->orderBy('id')
->chunk(20, function ($students) {
return true;
});
- 新增
$bool = DB::table('student')->insert(['name'=>'king', 'age'=>20]);
$insertId = DB::table('student')->insertGetId(['name'=>'king', 'age'=>20]);
$bool = DB::table('student')->insert(
['name'=>'king1', 'age'=>20],
['name'=>'king2', 'age'=>21],
['name'=>'king3', 'age'=>22],
);
- 更新
$num = DB::table('student')->where('id', 20)->update(['age'=>20]);
$num = DB::table('student')->where('id', 21)->increment('age', 1);
$num = DB::table('student')->where('id', 22)->decrement('age', 1);
$num = DB::table('student')->where('id', 23)->decrement('age', 1, ['name'=>'zhang']);
- 删除
$num = DB::table('student')->where('id', 1)->delete();
$num = DB::table('student')->where('id', '>', 1)->delete();
DB::table('student')->truncate();<清除表数据,一般不使用>
- 构造器聚合函数
$num = DB::table('student')->count();
$max = DB::table('student')->max('age');
$min = DB::table('student')->min('age');
$max = DB::table('student')->sum('age');
$avg = DB::table('student')->avg('age');
- 查询
// 查询所有记录
$students = Student::get();
$students = Student::all();
// 按主键id查询
$students = Student::find(7);
$students = Student::findOrFail(17);
// 条件查询
$students = Student::where('id', '>', '5')
->orderBy('age', 'desc')
->first();
// 分段查询
Student::chunk(2,function ($students){
var_dump($students);
});
$students = Student::count();
$students = Student::where('id','>',4)->max('age');
- 新增
// 通过模型新增
$student = new Student();
$student ->name = 'king';
$student ->age = 20;
$bool = $student -> save();
// 通过Create()方法新增
$student = Student::create([
'name'=>'xiaoxiao','age'=>21
])
// firstOrCreate()以属性查找,没有则添加
$student = Student::firstOrCreate([
['name'=>'king1']
])
// firstOrNew()手动添加
$student = Student::firstOrNew([
['name'=>'king2']
])
$bool = $student->save();
- 更新
// 通过模型新增
$student = Student::find(1022);
$student->name = 'king100';
$bool = $student->save();
// 通过条件更新
$num = Student::where('id','>',1020)->update(
['age'=>100]
);
- 删除
# 通过模型删除
$student= Student::find(1026);
$bool = $student->delete();
# 通过主键删除
$num = Student::destroy([1023,1024,1025,1022]);
# 指定条件删除
$num = Student::where('id','>',1000)->delete();