1. DB facade (original lookup)
// query $objectArray=DB::select('select * from student'); foreach ($objectArray as $obj){ echo $obj->id; } // insert $bool=DB::insert('insert into student(name,age) values(?,?)',['tom',18]); // modify $num=DB::update('update student set age=? where name=?',[20,'tom']); // delete $num=DB::delete('delete from student where id=?',[1001]);
2. Query Constructor
Laravel query constructor provides a convenient and smooth interface for establishing and executing database lookup syntax. pdo parameter bindings are used to protect the application from sql injection, so the incoming parameters do not need to escape special characters. Basically, it can satisfy all database operations, and it can be implemented on all supported database systems.
query
// Query all data $objectArray=DB::table('student')->get()->toArray(); foreach ($objectArray as $object){ echo $object->id; } // Query data according to conditions $objectArray=DB::table('student')->where('id','>',1001)->get()->toArray(); foreach ($objectArray as $object){ echo $object->id; } // Query data according to multiple conditions $objectArray=DB::table('student')->whereRaw('id > ? and age = ?',[1001,15])->get()->toArray(); foreach ($objectArray as $object){ echo $object->id; } // Take out the first data (ascending/descending) $object=DB::table('student')->orderBy('id','desc')->first(); echo $object->id; // Query specified field names (you can specify field names as array subscripts, not default numeric subscripts, plus instead of lists method) $names=DB::table('student')->pluck('name','id')->toArray(); // Query one or more fields specified $objectArray=DB::table('student')->select('id')->get()->toArray(); // Query data according to specified number of records and execute corresponding methods DB::table('student')->orderBy('id','desc')->chunk(2,function ($objects){ foreach ($objects as $object){ if ($object->id==1004){ echo 'find'; } } });
insert
// Single insertion $bool=DB::table('student')->insert( ['name'=>'tom','age'=>18] ); // Insert and get id $id=DB::table('student')->insertGetId( ['name'=>'john','age'=>10] ); // Multiple inserts $bool=DB::table('student')->insert([ ['name'=>'ke1','age'=>12], ['name'=>'he1','age'=>19] ]);
modify
// Single modification $num=DB::table('student') ->where('id',1002) ->update( ['age'=>50] ); // Running this statement increases by itself (default 1) $num=DB::table('student')->increment('age'); // Running this statement increases by itself (by 3) $num=DB::table('student')->increment('age',3); // Running this statement decreases by default (1) $num=DB::table('student')->decrement('age'); // Running this statement decreases itself (decreases 3) $num=DB::table('student')->decrement('age',3); // Conditional self-reduction $num=DB::table('student') ->where('id',1002) ->decrement('age',3); // Reduce and modify fields according to conditions $num=DB::table('student') ->where('id',1002) ->decrement('age',3,['name'=>'ioc']);
delete
// Single deletion $num=DB::table('student')->where('id',1003)->delete(); // Delete by condition $num=DB::table('student')->where('id','>=',1005)->delete(); // Delete the entire table $num=DB::table('student')->truncate();
Aggregate function
// Number of Statistical Records $num=DB::table('student')->count(); // Specify field maximum $max=DB::table('student')->max('age'); // Specify field minimum $min=DB::table('student')->min('age'); // Specified field average $avg=DB::table('student')->avg('age'); // Specifies the sum of fields $sum=DB::table('student')->sum('age');
Eloquent ORM
Laravel's Eloquent ORM is an ActiveRecord implementation for database operations. Each data table has a corresponding model for data table interaction
First, create a new model class file, which reads as follows:
namespace App; use Illuminate\Database\Eloquent\Model; class Student extends Model { // Specify the database table name protected $table='student'; // Specify primary key protected $primaryKey='id'; // Automatic maintenance timestamp public $timestamps = true; // Specify fields that allow batch assignment (you need to specify allowable fields when using the create method to increase batches) protected $fillable=['name','age']; // Specify fields that do not allow batch assignment protected $guarded=[]; // Automatic Formatting Time protected function getDateFormat() { return time(); } // Direct return timestamp (getDateFormat and asDateTime coexist, asDateTime takes effect) protected function asDateTime($value) { return $value; } }
Next, call the new model class in the controller
query
// Query all data $array=Student::all()->toArray(); // Query by primary key $array=Student::find(1001)->toArray(); // No record error was found $array=Student::findOrFail(101)->toArray(); // [Query constructor] Query all data, omit the specified table name in ORM, and use the rest in the same way. $array=Student::get()->toArray();
insert
// New data added to the model $student=new Student(); $student->name='yy'; $student->age=13; $bool=$student->save(); // New batch data added by model creation method $object=Student::create( ['name'=>'ui','age'=>13] ); // Search for records by attributes, add if not $object=Student::firstOrCreate( ['name'=>'tom'] ); // Find records by attributes, create new instances if not, and save() manually if you need to save them to the database. $object=Student::firstOrNew( ['name'=>'tom2'] ); $bool=$object->save(); // [Query Constructor] Insert Data $bool=Student::insert( ['name'=>'mary','age'=>18] );
modify
// Model modification data $object=Student::find(1025); $object->name='kero'; $bool=$object->save(); // [Query Constructor] Modify according to conditions $num=Student::where('id','=',1025)->update( ['age'=>10] );
delete
// Model deletion data $object=Student::find(1025); $bool=$object->delete(); // Delete by primary key (also in array form) $num=Student::destroy(1019,1020); // [Query Constructor] Delete by Conditions $num=Student::where('id','>',1016)->delete();