使用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 生成数据