php laravel db增删改查(使用sql文)

参考https://qiita.com/Yorinton/items/3d2e3c283b4cd8dbc955
1，首先配置/.env文件（也可配置/config/database.php），sample如下

DB_CONNECTION=pgsql
DB_HOST=localhost
DB_PORT=5432
DB_DATABASE=db名
DB_USERNAME=db用户名
DB_PASSWORD=db密码

2，编写/routes/web.php

Route::group(['prefix' => 'user'], function () {
    Route::get('/get', [TestController::class, 'get']);
    Route::get('/del', [TestController::class, 'del']);
    Route::get('/update', [TestController::class, 'update']);
    Route::get('/add', [TestController::class, 'add']);
    Route::get('/stmt', [TestController::class, 'stmt']);
});

3，编写/app/Http/Controllers/TestController.php

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class TestController extends Controller
{
    //http://localhost:3000/public/user/add?name=gg
    public function add(Request $request)
    {
        $db = DB::table('users');
        //插入多条数据，返回bool
        //写法1
        // $result=$db->insert([
        //     ['name'=>$request->input('name', 'kitty1'),'age'=>1],
        //     ['name'=>$request->input('name2', 'kitty2'),'age'=>2],
        // ]);
        //写法2
        $result = DB::insert('insert into users (name,age) values (?,?),(?,?)', [$request->input('name', 'kitty1'), 1, $request->input('name2', 'kitty2'), 2]);
        echo  $result . '<br/>';
        //插入一条数据，返回其主键
        $newId = $db->insertGetId(['name' => $request->input('name3', 'kitty3')]);
        echo $newId;
    }

    //http://localhost:3000/public/user/del?id=37
    public function del(Request $request)
    {
        $result = DB::delete('delete from users WHERE age = ?', [$request->input('age', 0)]);
        echo  $result . '<br/>';
    }

    //http://localhost:3000/public/user/update?id=36&name=john
    public function update(Request $request)
    {
        $result = DB::update('update users set name = ? where age = ?', [$request->input('name'), $request->input('age')]);
        echo  $result . '<br/>';
    }

    //http://localhost:3000/public/user/get?id=36
    public function get(Request $request)
    {
        $users = DB::select("SELECT * FROM users");
        var_dump($users);
        $user = DB::selectOne("SELECT * FROM users WHERE id = ?", [$request->input('id')]);
        echo '<br/>';
        var_dump($user);
    }
    
    //http://localhost:3000/public/user/stmt?sql=delete%20from%20users%20where%20id=41
    public function stmt(Request $request)
    {
        echo $request->input('sql') . '<br/>';
        $result = DB::statement($request->input('sql'));
        var_dump($result);
    }
}

传入到sql文的参数全写在[ ]里，且按sql文的？的顺序排列。

返回值

insert成功为true,失败为false
insertGetId成功为插入时的ID值
delete返回修改成功记录的条数
update返回修改成功记录的条数
select对象集合
selectOne单个对象
statement成功为true,失败为false