laravel框架數(shù)據(jù)庫操作、查詢構(gòu)建器、Eloquent ORM操作實(shí)例分析

'mysql' => [
  'driver' => 'mysql',
  'host' => env('DB_HOST', 'localhost'),
  'port' => env('DB_PORT', '3306'),
  'database' => env('DB_DATABASE', 'forge'),
  'username' => env('DB_USERNAME', 'forge'),
  'password' => env('DB_PASSWORD', ''),
  'charset' => 'utf8',
  'collation' => 'utf8_unicode_ci',
  'prefix' => '',
  'strict' => false,
  'engine' => null,
],

public static function testDB(){
  //增加一條數(shù)據(jù)
  DB::insert("insert into student(name,age) values(?,?)",['sandy',19]);
  //刪除一條數(shù)據(jù)
  DB::delete('delete from student where name=?',['sandy']);
  //修改一條數(shù)據(jù)
  DB::update('update student set sex=? where name=?',['男','tory']);
  //查詢數(shù)據(jù)
  $res=DB::select('select * from student');
  //進(jìn)行數(shù)據(jù)庫通用操作
  DB::statement('drop table users');
  //打印結(jié)果
  dd($res);
}

$res = DB::select('select * from users where id = :id', ['id' => 1]);

//get()返回表中所有數(shù)據(jù)
$res=DB::table('student')->get();
//first()返回結(jié)果集中的第一條數(shù)據(jù)
$res=DB::table('student')->where('id','1001')->first();
//value()返回一條數(shù)據(jù)中的指定字段
$res=DB::table('student')->where('id','1003')->value('name');
//pluck()返回結(jié)果集中name字段的所有值
$res=DB::table('student')->pluck('name');

DB::table('student')->chunk(2,function ($res){
  foreach ($res as $user){
    var_dump($user);
    if ($user->id >=1003) return false;
  }
});

//增加一條數(shù)據(jù)
DB::table('student')->insert(['name'=>'李four','sex'=>'男','age'=>22]);
//增加多條數(shù)據(jù)
DB::table('student')->insert([
  ['name'=>'wang五','sex'=>'女','age'=>21],
  ['name'=>'zhao六','sex'=>'女','age'=>20],
]);
//刪除數(shù)據(jù)
DB::table('student')->where('id','>=',1006)->delete();
//刪除整個(gè)表
DB::table('student')->truncate();
//修改數(shù)據(jù)
DB::table('student')->where('id',1005)->update(['sex'=>'女','age'=>21]);
//自增increment、自減decrement，默認(rèn)增1
DB::table('student')->where('id',1005)->increment('age',2);
//自增同時(shí)可以進(jìn)行修改
DB::table('student')->where('id',1005)->increment('age',1,['sex'=>'女']);
//查詢指定字段
$res=DB::table('student')->select('name','age')->get();

$res=DB::table('student')->where('id','>=',1003)->get();

$res=DB::table('student')->where([
  ['id','>=',1003],
  ['id','',1005]
])->get();

$res=DB::table('student')->where('id','>=',1003)->orwhere('id','',1002)->get();

$res=DB::table('student')->whereBetween('id',[1003,1006])->get();

$order=false;
$res=DB::table('student')->when($order,function ($query){
  return $query->orderBy('age','desc');       //$order為true時(shí)才執(zhí)行此語句
})->get();

//orderBy對age字段升序
$res=DB::table('student')->orderBy('age','asc')->get();
//按照create_at字段進(jìn)行時(shí)間排序
$res=DB::table('student')->latest('create_at')->get();
//分組
$res=DB::table('student')->groupBy('sex')->get();
//跳過一條數(shù)據(jù)后返回2條數(shù)據(jù)
$res=DB::table('student')->skip(1)->limit(2)->get();

$res=DB::table('student')->avg('age');

namespace App;
use Illuminate\Database\Eloquent\Model;
class Student extends Model
{
  //指定對應(yīng)的表
  protected $table='student';
  //指定主鍵
  protected $primaryKey='id';
  //允許批量賦值的字段
  protected $fillable=['name','age'];
  //不允許批量賦值的字段
  protected $guarded=['created_at'];
}

const CREATED_AT = 'my_create';
const UPDATED_AT = 'my_update';

//新建實(shí)例并賦值、保存
$stu=new Student();
$stu->name='orm2';
$stu->save();
//create方法批量添加數(shù)據(jù)
Student::create(['name'=>'orm3','age'=>13]);

//destroy刪除指定主鍵值
Student::destroy(1006,1007);
//通過查詢構(gòu)建器刪除
Student::where('id',1008)->delete();

//通過返回Student對象進(jìn)行修改
$stu=Student::find(1005);
$stu->age=21;
$stu->save();
//通過查詢構(gòu)建器修改
Student::where('id',1005)->update(['age'=>22]);

//查詢表中所有記錄
$table=Student::all();
//根據(jù)id查詢一條數(shù)據(jù)
$row=Student::find(1002);
dd($table);