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

使用Facades

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


使用构造查询器

sql语句如下&＃xff1a;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&＃61;MYISAM DEFAULT CHARSET&＃61;utf8;

public function query1() {//insert$res &＃61; DB::table("student")->insert([&＃39;name&＃39;&＃61;>&＃39;imooc&＃39;, &＃39;age&＃39;&＃61;>18]);//insertGetId$id &＃61; DB::table("student")->insertGetId([&＃39;name&＃39;&＃61;>&＃39;imooc1&＃39;, &＃39;age&＃39;&＃61;>18]);//insert multi rowsDB::table("student")->insert([[&＃39;name&＃39;&＃61;>&＃39;imooc1&＃39;, &＃39;age&＃39;&＃61;>18],[&＃39;name&＃39;&＃61;>&＃39;imooc2&＃39;, &＃39;age&＃39;&＃61;>18]]);var_dump($id);//int(4)}public function query2() {//update$num &＃61; DB::table("student")->where("id", 4)->update([&＃39;age&＃39;&＃61;>30]);//increment and decrement$num &＃61; DB::table(&＃39;student&＃39;)->increment(&＃39;age&＃39;, 3);$num &＃61; DB::table(&＃39;student&＃39;)->where("id", 4)->decrement(&＃39;age&＃39;, 3, [&＃39;name&＃39;&＃61;>&＃39;test&＃39;]);}public function query3() {//deleteDB::table("student")->where("id", ">&＃61;", 3)->delete();DB::table("student")->truncate();}public function query4() {//get all$students &＃61; DB::table("student")->get();//get the first item of query result$student &＃61; DB::table("student")->orderBy("id", "desc")->first();//orWhere$student &＃61; DB::table("student")->where("id",4)->orWhere("name",&＃39;tom&＃39;)->first();//闭包查询$kw &＃61; $request->get("kw");//如果$kw为真则执行匿名函数里的查询$res &＃61; DB::table(&＃39;class&＃39;)->when($kw, function(Builder $query) use ($kw) {$query->where(&＃39;pid&＃39;,&＃39;like&＃39;,"%{$kw}%");})->get();//多条件查询$students &＃61; DB::table("student")->whereRaw("id >&＃61; ? and age > ?", [5, 20])->get();//pluck (返回指定的字段)$names &＃61; DB::table("student")->pluck("name");//lists (效果同pluck) 但可以指定第二个参数值为下标$list &＃61; DB::table("student")->lists("name", "id");//select 同tp5中的column$names &＃61; DB::table("student")->select("id", "name", &＃39;age&＃39;)->get();//chunk 用法同tp5DB::table("student")->chunk(2, function($students) {var_dump($students);});}public function query5() {$num &＃61; DB::table("student")->count();DB::table("student")->max("age");DB::table("student")->min("age");DB::table("student")->sum("age");var_dump($num);}


Eloquent ORM

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

public function orm1() {//get all the items$all &＃61; Student::all();//get one by pk [TP5 get]$one &＃61; Student::find(1000);// findOrFail() will throw an error if the res does not exist$one &＃61; Student::findOrFail(3000);// query by conditionStudent::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 &＃61; false in the model
// $student &＃61; new Student();
// $student->name &＃61; "sean";
// $student->age &＃61; 20;
// $bool &＃61; $student->save();//插入的字段需要在模型$fillable中设定$student &＃61; Student::create([&＃39;name&＃39;&＃61;>&＃39;imooc&＃39;, &＃39;age&＃39;&＃61;> 18]);$data &＃61; [&＃39;name&＃39;&＃61;>&＃39;tom&＃39;, &＃39;age&＃39;&＃61;>18];//insert插入&＃xff0c;不会自动设置timestampStudent::insert($data);//以属性查找&＃xff0c;如没有则新增Student::firstOrCreate(["name" &＃61;> "imoocfirst"]);//以属性查找&＃xff0c;如果没有则新增实例$s &＃61; Student::firstOrNew(["name" &＃61;> "imoocnew"]);$s->save();}public function orm3() {//update by model$student &＃61; Student::find(4);$student->name &＃61; "update orm3";$bool &＃61; $student->save();$num &＃61; Student::where("id", ">", 4)->update([&＃39;age&＃39; &＃61;> 41]);var_dump($num);}public function orm4() {//delete//1.
// $student &＃61; Student::find(4);
// $num &＃61; $student->delete();//2. by pk$num &＃61; Student::destroy(15,16);//3. by conditionStudent::where("id", ">", 104)->delete();}


软删除配置
1.在migration文件中配置 $table->softDeletes();
2.在模型文件中&＃xff0c;引入trait SoftDeletes;
3.在模型文件中配置 protected $dates &＃61; [“deleted_at”]

//引入软删除use SoftDeletes;//指定删除的字段标识protected $dates &＃61; [&＃39;deleted_at&＃39;];


查询软删除的数据使用 onlyTrashed()方法&＃xff0c;恢复使用 restore()

$res &＃61; Article::onlyTrashed()->get();


添加表前缀

数据库迁移与数据填充

生成迁移

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

编写迁移

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

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


执行迁移

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

使用faker

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

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


调用 php artisan db:seed

使用factory创建

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

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


在seed文件中&＃xff0c;调用factory()方法

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


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