laravel 数据库操作(curd，软删除，数据迁移与执行，使用faker填充数据)

原创于 2021-04-06 18:16:14 发布 · 155 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#laravel #数据库

laravel 专栏收录该内容

8 篇文章

订阅专栏

这篇博客详细介绍了 Laravel 中使用 Facades 进行 SQL 查询，包括 select, insert, update, delete 等操作，并展示了如何使用构造查询器进行数据操作。此外，还探讨了 Eloquent ORM 的用法，如获取数据、条件查询、数据更新与删除，以及如何处理软删除。最后，提到了数据库迁移与填充，包括 Faker 工具的使用和数据工厂的创建。

使用Facades

<?php

namespace App\Http\Controllers;

use Illuminate\Support\Facades\DB;
class StudentController extends Controller
{
    //
    public function test1(){
        //query
        $student = DB::select('select name,age from student');
        //insert
        $bool = DB::insert("insert into student(name, age) values(?, ?)",[
            "imooc", 18
        ]);
        //update
        $num = DB::update('update student set age = ? where name = ?', [20, 'sean']);
        //delete
        $num = DB::delete("delete from student where name = ?" , ["sean"]);
        //占位符
        $res = DB::selectOne("select * from class where id = :id", [':id' => 5]);
        dump($student);
    }
}

使用构造查询器

sql语句如下：CREATE TABLE student (
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(255) DEFAULT NULL,
age TINYINT(3) DEFAULT NULL,
sex TINYINT(3) DEFAULT NULL,
create_at INT(11) DEFAULT NULL,
update_at INT(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=MYISAM DEFAULT CHARSET=utf8;

    public function query1() {
        //insert
        $res = DB::table("student")->insert(['name'=>'imooc', 'age'=>18]);
        //insertGetId
        $id = DB::table("student")->insertGetId(['name'=>'imooc1', 'age'=>18]);
        //insert multi rows
        DB::table("student")->insert([
            ['name'=>'imooc1', 'age'=>18],
            ['name'=>'imooc2', 'age'=>18]
        ]);
        var_dump($id);//int(4)
    }
     public function query2() {
        //update
        $num = DB::table("student")->where("id", 4)->update(
            ['age'=>30]
        );
        //increment and decrement
        $num = DB::table('student')->increment('age', 3);
        $num = DB::table('student')->where("id", 4)->decrement('age', 3, ['name'=>'test']);

    }
    public function query3() {
        //delete
        DB::table("student")->where("id", ">=", 3)->delete();
        DB::table("student")->truncate();
    }
     public function query4() {
        //get all
        $students = DB::table("student")->get();

        //get the first item of query result
        $student = DB::table("student")->orderBy("id", "desc")->first();
        //orWhere
        $student = DB::table("student")->where("id",4)->orWhere("name",'tom')->first();
        //闭包查询
        $kw = $request->get("kw");
        //如果$kw为真则执行匿名函数里的查询
        $res = DB::table('class')->when($kw, function(Builder $query) use ($kw) {
            $query->where('pid','like',"%{$kw}%");
        })->get();
        //多条件查询
        $students = DB::table("student")->whereRaw("id >= ? and age > ?", [5, 20])->get();
        //pluck (返回指定的字段)
        $names = DB::table("student")->pluck("name");
        //lists (效果同pluck) 但可以指定第二个参数值为下标
        $list = DB::table("student")->lists("name", "id");
        //select 同tp5中的column
        $names = DB::table("student")->select("id", "name", 'age')->get();
        //chunk 用法同tp5
        DB::table("student")->chunk(2, function($students) {
            var_dump($students);
        });
    }
    public function query5() {
        $num = DB::table("student")->count();
        DB::table("student")->max("age");
        DB::table("student")->min("age");
        DB::table("student")->sum("age");
        var_dump($num);
    }

Eloquent ORM

默认情况下，模型文件在laravel的根目录下。
创建模型的命令 php artisan make:model Model/Article

    public function orm1() {
        //get all the items
        $all = Student::all();
        //get one by pk [TP5 get]
        $one = Student::find(1000);
        // findOrFail() will throw an error if the res does not exist
        $one = Student::findOrFail(3000);
        // query by condition
        Student::where("id", ">", 3)->orderBy("id", "desc")->first();
        Student::chunk(2, function($students){
            //pass
        });
        Student::max("id");
    }
    public function orm2() {
        //if save failed add public $timestamps = false in the model
//        $student = new Student();
//        $student->name = "sean";
//        $student->age = 20;
//        $bool = $student->save();
		//插入的字段需要在模型$fillable中设定
        $student = Student::create(['name'=>'imooc', 'age'=> 18]);
		$data = ['name'=>'tom', 'age'=>18];
		//insert插入，不会自动设置timestamp
		Student::insert($data);
        //以属性查找，如没有则新增
        Student::firstOrCreate(
          ["name" => "imoocfirst"]
        );
        //以属性查找，如果没有则新增实例
        $s = Student::firstOrNew(
            ["name" => "imoocnew"]
        );
        $s->save();
    }
    public function orm3() {
        //update by model
        $student = Student::find(4);
        $student->name = "update orm3";
        $bool = $student->save();
        $num = Student::where("id", ">", 4)->update(
            ['age' => 41]
        );
        var_dump($num);
    }
    public function orm4() {
        //delete
        //1.
//        $student = Student::find(4);
//        $num = $student->delete();
        //2. by pk
        $num = Student::destroy(15,16);
        //3. by condition
        Student::where("id", ">", 104)->delete();
    }

软删除配置
1.在migration文件中配置 $table->softDeletes();
2.在模型文件中，引入trait SoftDeletes;
3.在模型文件中配置 protected $dates = [“deleted_at”]
    //引入软删除
   use SoftDeletes;
   //指定删除的字段标识
   protected $dates = ['deleted_at'];
查询软删除的数据使用 onlyTrashed()方法，恢复使用 restore()

$res = Article::onlyTrashed()->get();

添加表前缀

数据库迁移与数据填充

生成迁移

php artisan make:migration create_test_table --create=test
create_test_table 生成的文件后缀名称
–create=test 生成表的名称

编写迁移

$table->字段函数(“字段名”)->约束函数()

        Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('email')->unique();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
        });

执行迁移

php artisan migrate
php artisan migrate:rollback --step=4 回滚迁移
php artisan migrate:reset 回滚所有迁移
php artisan migrate:refresh 回滚后再创建
php artisan migrate:fresh 删除数据库中所有的表，再migrate

使用faker

先创建一个seed文件
php artisan make:seeder seedName
在DatabaseSeeder中调用创建的seedClass

    public function run()
    {
        //
        $data = [];
        $faker = \Faker\Factory::create();
        for ($i=0; $i<=10; $i++) {
            $data[] = [
                'uid' => $i,
                'title' => $faker->word,
                'cnt' => $faker->text
            ];
        }
        DB::table("articles")->insert($data);
    }

调用 php artisan db:seed

使用factory创建

创建工厂文件
php artisan make:factory FactoryName -m Model (如不在app目录下，要带目录，如Models/Article)
生成的文件在database/factories下

use Faker\Generator as Faker;
//定义要创建的数据
$factory->define(App\Models\Article::class, function (Faker $faker) {
    return [
        //
        'uid' => rand(1, 10),
        'title' => $faker->word,
        'cnt' => $faker->text
    ];
});

在seed文件中，调用factory()方法

    public function run()
    {
        //
        factory(Article::class, 20)->create();
    }

最后执行 php artisan migrate:refresh --seed 生成数据