swoft 學習筆記之資料庫操作

查詢資料

• 查詢一條資料,返回資料是一個實體或者null

  $id  = $request->input('id',0);
  $res = Saying::find($id,['content','type']);
  $res = Saying::where('id',$id)->select('content','type')->first();

• 查詢多條資料,返回的是實體集合，如果沒找到則為空集合，toArray之後，轉換成二維陣列或者一維空陣列

  $ids  = [1,2,3];
  $res = Saying::findMany($ids,['content','type']);
  $res = Saying::whereIn('id',$ids)->select('content','type')->get();

• 獲取記錄中的單個值

  $content = Saying::where('id',1)->value('content');

插入資料

• 物件方式

  $saying = Saying::new();
  $saying->setContent('嘻嘻哈哈咻咻咻~');
  $saying->save();
  $id = $saying->getId();

• 陣列方式

  $data = [
  'content' => '好嗨喲~',
  ];
  $saying = Saying::new($data);
  $result = $saying->save();
  $id = $saying->getId();

• 批量插入

  $data = [
  ['content' => '好嗨喲~'],
  ['content' => '你是隔壁的泰山'],
  ['content' => '打架能增進感情，來，讓我們親熱親熱'],
  ];
  $result = Saying::insert($data);

更新資料

• 實體更新，返回 bool 值

  $id = 9;
  $result = Saying::find($id)->update(['content'=>'一想到你我就~~~~']);

• 批量更新,返回受影響行數

  $ids = [4,5,6,7];
  $result = Saying::whereIn('id',$ids)->update(['content'=>'驚悚有聲故事的鼻祖~']);

刪除資料

• 實體刪除,返回 bool 值

  $id = 8;
  $result = Saying::find($id)->delete();

• 批量刪除

  $ids = [6,7];
  $result = Saying::whereIn('id',$ids)->delete();

聚合查詢

count, max，min，avg，sum

$userNum = DB::table('users')->count();
$price   = DB::table('orders')->max('price');
$price = DB::table('orders')->where('status', 1)->avg('price');

原生表示式

 // select count(*) as `user_count`, `name` from `user`
$users = DB::table('user')
         ->selectRaw('count(*) as `user_count`, `name`'));
         ->get();

事務

DB::beginTransaction();
DB::connection()->beginTransaction();

DB::rollBack();
DB::connection()->rollBack();

DB::commit();
DB::connection()->commit();