1 개요

Laravel Query Builder라라벨 쿼리빌더

• 라라벨 DB 저장방법 중 하나
• DB 쿼리 관리에 편리한 인터페이스 제공
• PDO 파라미터 바인딩을 통하여 SQL 인젝션 공격 방어

2 where 사용하기

• where을 사용하기 위해서는 컬럼명, 연산자, 값을 입력해 줘야함.

users 테이블에서 votes 컬럼이 100의 값을 가지는 사용자 쿼리

$users = DB::table('users')->where('votes', '=', 100)->get(); //

3 조회 예시

$users = DB::table('users')->get();
SELECT * FROM users;

$user = DB::table('users')->where('name', 'John')->first();
SELECT * FROM users WHERE name='John' LIMIT 1;

$users = DB::table('users')->where('votes', 100)->get();
$users = DB::table('users')->where('votes', '=', 100)->get();
SELECT * FROM users WHERE votes=100;

$users = DB::table('users')->where('votes', '>=', 100)->get();
SELECT * FROM users WHERE votes >= 100;

$users = DB::table('users')->where('votes', '<>', 100)->get();
SELECT * FROM users WHERE votes <> 100;

$users = DB::table('users')->where('name', 'like', 'T%')->get();
SELECT * FROM users WHERE name LIKE 'T%';

$users = DB::table('users')->where([
    ['status', '=', '1'],
    ['subscribed', '<>', '1'],
])->get();
SELECT * FROM users WHERE status='1' AND subscribed<>'1';

$users = DB::table('users')->where('votes', '>', 100)->orWhere('name', 'John')->get();
SELECT * FROM users WHERE votes>100 OR name='John';

$email = DB::table('users')->where('name', 'John')->value('email');
SELECT email FROM users WHERE name='John';

$users = DB::table('users')->orderBy('name', 'desc')->get();
SELECT * FROM users ORDER BY name DESC;

$users = DB::table('users')->take(5)->get();
SELECT * FROM users LIMIT 5;

DB::table('users')->where('votes', '>', 100)->delete();
DELETE FROM users WHERE votes > 100;

#원하는 컬럼만 추출
$users = DB::table('users')->select('name', 'email')->get();

$users = DB::table('users')->select('name', 'email as user_email')->get();

$query = DB::table('users')->select('name');
$users = $query->addSelect('age')->get();

#첫 행만 추출
$user = DB::table('users')->where('name', 'John')->first();
echo $user->name;

#첫 행만 추출 { (web) view 전달 }
$user = DB::table('users')->where('name', 'John')->first();
return view('userview', ['user'=>$user]);

#첫 행만 추출 { (api) json 응답 }
$user = DB::table('users')->where('name', 'John')->first();
return response()->json($user);

#첫 행만 추출 { toArray() }
$group = collect(DB::table('users')->where('name', 'John')->first())->toArray();

Insert 예시

DB::table('users')->insert(
    ['email' => '[email protected]', 'votes' => 0]
);

DB::table('users')->insert([
    ['email' => '[email protected]', 'votes' => 0],
    ['email' => '[email protected]', 'votes' => 0]
]);

$id = DB::table('users')->insertGetId(
    ['email' => '[email protected]', 'votes' => 0]
);

Update 예시

#일반 컬럼 업데이트
DB::table('users')
            ->where('id', 1)
            ->update(['votes' => 1]);
#JSON 컬럼 업데이트
DB::table('users')
            ->where('id', 1)
            ->update(['options->enabled' => true]);
#증가, 감소
DB::table('users')->increment('votes');
DB::table('users')->increment('votes', 5);
DB::table('users')->decrement('votes');
DB::table('users')->decrement('votes', 5);