laravel 数据库操作之 DB facade & 查询构造器 & Eloquent ORM
<?php namespace App\Http\Controllers; use App\Student; use Illuminate\Support\Facades\DB; class StudentController extends Controller {
//DB facade原始SQL语句 public function test1() {
$students = DB::select('select * from student'); //var_dump($students); dd($students); }
//查询构造器新增数据-增 public function query1() { //普通插入 // $bool = DB::table('student')->insert( // ['name' => 'imooc', 'age'=> 20] // ); // dd($bool); //返回自增长id // $id = DB::table('student')->insertGetId( // ['name' => 'cxll', 'age'=> 18] // ); // var_dump($id);
//批量插入 $bool = DB::table('student')->insert([ ['name'=>'name1', 'age' =>20], ['name'=>'name2', 'age' =>19] ]); dd($bool); } //查询构造器更新数据-改 public function query2() { //返回影响行数 // $num = DB::table('student') // ->where('id', 2) // ->update(['age'=>30]); // dd($num); //自增减 默认1 //$num = DB::table('student')->increment('age'); //$num = DB::table('student')->increment('age',3); //$num = DB::table('student')->decrement('age',5); // $num = DB::table('student') // ->where('id', 3) // ->decrement('age');
//自增同时更新其它数据 $num = DB::table('student') ->where('id', 3) ->increment('age',2,['name'=> 'newname']); dd($num); } //查询构造器删除数据-删 public function query3() { //返回操作影响的行数 // $num = DB::table('student') // ->where('id',4) // ->delete(); // $num = DB::table('student') // ->where('id','>',2) // ->delete(); // dd($num); //清空表 无返回值 DB::table('student')->truncate(); }
//查询构造器查询数据-查 public function query4(){ // $bool = DB::table('student') ->insert([ // ['id' => 1001, 'name' => 'name1', 'age' =>18], // ['id' => 1002, 'name' => 'name2', 'age' =>19], // ['id' => 1003, 'name' => 'name3', 'age' =>18], // ['id' => 1004, 'name' => 'name4', 'age' =>21], // ['id' => 1005, 'name' => 'name5', 'age' =>18] // ]); // dd($bool); //get() //$students = DB::table('student')->get(); //first() // $student = DB::table('student') // ->orderBy('id', 'desc') // ->first(); // dd($student); //where() // $students = DB::table('student') // ->where('id', '>=', 1002) // ->get(); // $students = DB::table('student') // ->whereRaw('id >= ? and age > ?', [1001, 18]) // ->get(); // dd($students); //pluck() 取字段 // $names = DB::table('student') // ->pluck('name'); //指定下标为id // $names = DB::table('student') // ->pluck('name','id'); //lists() laravel5.5不支持lists了, 被pluck取代 //$names = DB::table('student')->lists('name','id'); //select() 指定查找 // $names = DB::table('student') // ->select('id', 'name') // ->get(); // dd($names); //chunk() 分段获取 需指定排序字段 echo '<pre>'; DB::table('student')->orderBy('id') ->chunk(2, function($students){ var_dump($students); }); } //聚合函数 public function query5() { //$num = DB::table('student')->count(); //$max = DB::table('student')->max('age'); //$min = DB::table('student')->min('age'); //$avg = DB::table('student')->avg('age'); $sum = DB::table('student')->sum('age'); dd($sum); }
//使用orm查询数据-查 public function orm1() { //all() //$students = Student::all(); //find() //$student = Student::find(1001); //findOrFail() //$student = Student::findOrFail(1001); //dd($student); //get() //$students = Student::get(); //first() // $student = Student::where('id', '>', '1001') // ->orderBy('age', 'desc') // ->first(); // dd($student); //chunk() // echo '<pre>'; // Student::chunk(2, function($students){ // var_dump($students); // }); //聚合函数 //$num = Student::count(); $max = Student::where('id','>',1001)->max('age'); dd($max); } //使用orm新增数据-增 public function orm2() { //使用模型新增数据 // $student = new Student(); // $student->name = 'sean2'; // $student->age = 20; // //存入数据库 // $bool = $student->save(); // dd($bool); //$student = Student::find(1010); //dd($student->created_at); //将时间戳按格式输出 //echo date('Y-m-d H:i:s', 1464509164); //使用模型的Create方法新增数据 // $student = Student::create( // ['name' => 'imooc2', 'age' => 19] // ); // dd($student); //firstOrCreate() 查不到就新增至数据库 // $student = Student::firstOrCreate( // ['name'=>'imooc3'], // ['age'=>18] // ); //firstOrNew() 查不到就创建实例,不主动插入数据库 $student = Student::firstOrNew( ['name'=>'imooc4'], ['age'=>18] ); $student->save(); dd($student); }
//使用orm更新数据-改 public function orm3() { //通过模型更新数据 // $student = Student::find(1014); // $student->name = 'kitty'; // $bool = $student->save(); // dd($bool); $num = Student::where('id', '>', 1012)->update( ['age'=>41] ); dd($num); } //使用orm删除数据-删 public function orm4() { //通过模型删除 // $student = Student::find(1014); // $bool = $student->delete(); // dd($bool); //通过主键删除 // $num = Student::destroy(1013); // $num = Student::destroy(1012,1013); // $num = Student::destroy([1012,1013]); // dd($num); //删除指定条件的数据 $num = Student::where('id', '>', 1008)->delete(); dd($num); } }
Student 控制器类👆
Student 模型类👇
<?php namespace App; use Illuminate\Database\Eloquent\Model; class Student extends Model{ //每个模型对应一个数据表,用于交互 //指定表名 protected $table = 'student'; //指定主键 protected $primaryKey = 'id'; //指定允许批量赋值的字段 protected $fillable = ['name', 'age']; //指定不允许批量赋值的字段 protected $guarded = []; public static function getStudent(){ return 'student is sean'; } //自动维护时间戳 // public $timestamps = true; //针对时间戳 将其转换为标准日期格式 // protected function getDateFormat() // { // return time(); // } //针对时间戳 不做格式处理 // protected function asDateTime($val) // { // return $val; // } }