Laravel数据库操作

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'
 }

数据库操作三种方式

1. DB facade

• 查询

$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]);

2. DB Query Builder查询构造器

• 查询

// 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');　

3. DB Eloquent ORM

• 查询

// 查询所有记录
$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();