Laravel 쿼리 빌더 CRUD 예제

Ex Table

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    position VARCHAR(100),
    salary DECIMAL(10, 2)
);

 

생성(Create)

use Illuminate\Support\Facades\DB;

// 레코드 삽입
DB::table('employees')->insert([
    'name' => 'Alice',
    'position' => 'Developer',
    'salary' => 60000
]);

// 레코드 삽입후 PK추출
$id = DB::table('employees')->insertGetId([
    'name' => 'Alice',
    'position' => 'Developer',
    'salary' => 60000
]);

// 여러 레코드 삽입
DB::table('employees')->insert([
    ['name' => 'Bob', 'position' => 'Designer', 'salary' => 50000],
    ['name' => 'Charlie', 'position' => 'Manager', 'salary' => 70000]
]);

 

읽기(Read)

use Illuminate\Support\Facades\DB;

// 모든 레코드 가져오기
$employees = DB::table('employees')->get();
foreach ($employees as $employee) {
    echo "Name: {$employee->name}, Position: {$employee->position}, Salary: {$employee->salary}\n";
}

// 조건에 맞는 단일 레코드 가져오기
$employee = DB::table('employees')->where('name', 'Alice')->first();
echo "Name: {$employee->name}, Position: {$employee->position}, Salary: {$employee->salary}\n";

// 특정 컬럼만 가져오기
$names = DB::table('employees')->pluck('name');
foreach ($names as $name) {
    echo "Name: {$name}\n";
}

 

업데이트(Update)

use Illuminate\Support\Facades\DB;

// 단일 레코드 업데이트
DB::table('employees')
    ->where('name', 'Alice')
    ->update(['position' => 'Senior Developer', 'salary' => 65000]);

// 여러 레코드 업데이트
DB::table('employees')
    ->where('position', 'Designer')
    ->update(['salary' => 55000]);

 

삭제(Delete)

use Illuminate\Support\Facades\DB;

// 단일 레코드 삭제
DB::table('employees')->where('name', 'Alice')->delete();

// 조건에 맞는 여러 레코드 삭제
DB::table('employees')->where('salary', '<', 60000)->delete();

// 모든 레코드 삭제
DB::table('employees')->delete();

 

페이징(Pagination)

use Illuminate\Support\Facades\DB;

// 페이지네이션 사용
$employees = DB::table('employees')->paginate(10);

foreach ($employees as $employee) {
    echo "Name: {$employee->name}, Position: {$employee->position}, Salary: {$employee->salary}\n";
}

// 페이지네이션 링크 표시 (Blade 템플릿에서 사용)
echo $employees->links();

 

정렬(Sorting)

use Illuminate\Support\Facades\DB;

// 이름 기준으로 정렬
$employees = DB::table('employees')->orderBy('name', 'asc')->get();
foreach ($employees as $employee) {
    echo "Name: {$employee->name}, Position: {$employee->position}, Salary: {$employee->salary}\n";
}

 

조인(Joins)

use Illuminate\Support\Facades\DB;

// employees와 departments 테이블 조인
$employees = DB::table('employees')
    ->join('departments', 'employees.department_id', '=', 'departments.id')
    ->select('employees.*', 'departments.name as department_name')
    ->get();

foreach ($employees as $employee) {
    echo "Name: {$employee->name}, Position: {$employee->position}, Department: {$employee->department_name}\n";
}