7 - laravel基础 - 数据库操作

连接数据库

配置 app 同级目录下的 .env 文件, 根据自己项目的实际情况配置即可

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=study_laravel
DB_USERNAME=root
DB_PASSWORD=123

运行原生SQL语句

namespace App\Http\Controllers;
use Illuminate\Http\Request;
use DB;

class DBController extends Controller{
    public function index(){
        // 增加:
         $result = DB::insert("insert into articles(title,content) values('?','?')", [
            'title'   => 'test artcile title',
            'content' => 'test artcile contents'
        ]);
        dump($result);

        // 删除:
        $result = DB::delete("delete from articles where id=?", [11]);
        dump($result);

        // 修改: 
        $result = DB::update("update articles set title=?,content=? where id=?", [
            'updated title', 'updated content', '10'
        ]);
        dump($result);

        // 查询: 
        $data = DB::select("select * from articles where id= ? and title", [10]);
        dump($data);
    }
}

查询构造器

namespace App\Http\Controllers;
use Illuminate\Http\Request;
use DB;
class DBController extends Controller{
    public function index(){
        // 增加:
        $result = DB::table('articles')->insert([
            'title'   => 'test title',
            'content' => 'test content'
        ]);
        dump($result);

        // 删除
        $result = DB::table('articles')->where('id', 10)->delete();
        dump($result);

        // 修改
         $result = DB::table('articles')->where('id', 13)->update([
            'title' => 'updated title',
            'content' => 'updated article content'
        ]);
        dump($result);
        
        // 查询
        $result = DB::table('articles')->get();
        dump($result);
    }
}

模型

• 创建模型

php artisan make:model Articles

• 设置模型

namespace App;

use Illuminate\Database\Eloquent\Model;

class Article extends Model {
    protected $table      = 'articles'; // 表名
    protected $primaryKey = 'id';       // 主键
    protected $guarded    = [];         // 不能被批量修改的字段
    protected $fillable   = ['title', 'content'];  // 能够被批量修改的字段 
    public    $timestamps = false;      // 是否开启时间戳字段
}

• 使用模型

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Article;

class DBController extends Controller{
    public function index(){
        // 增加: 使用create方法字段必须可以被批量修改
       $article = [
            'title'   => 'model title',
            'content' => 'model create content'
        ];
        $resutl  = Article::create($article);
        
        // 删除,根据id删除, 查询指定的id删除
        $result  = Article::destroy(10);
        $result  = Article::where('id', 11)->delete();
        dump($result);

        // 修改
        $result  = Article::where('id', 15)->update([
            'title' => 'xiugai title',
            'content' => 'gengxin content',
        ]);
        dump($result);

        // 查询数据
        $result  = Article::where(['id'=>10])->get();
        dump($result);
    }
}

---

关联模型

关联关系

• 一对一: 一个用户可以写多篇文章
• 一对多: 一片文章有多个评论
• 多对多: 多个角色有多个权限

一对一实例 hasOne('关联的模型', '外键', '主键')

数据表如下图所示

数据表

// ========== 用户模型 ==========
namespace App;
use Illuminate\Database\Eloquent\Model;

class User extends Model {
    protected $table      = 'users';
    protected $primaryKey = 'id';
    protected $guarded    =  [];
    public    $timestamps = false;

    // 使用hasOne方法关联 App\UserInfo 模型
    public function getInfo(){
        return $this->hasOne('App\UserInfo', 'user_id', 'id');
    }
    
}

// ========== 用户信息模型 ==========
namespace App;
use Illuminate\Database\Eloquent\Model;
class UserInfo extends Model {
    protected $table      = 'user_info';
    protected $primaryKey = 'id';
    protected $guarded    =  [];
    public    $timestamps = false;

    public function user(){
        return $this->hasOne('App\User', 'id', 'user_id');
    }
    
}

// ========== 连表查询 ==========
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\User;
use App\UserInfo;

class DBController extends Controller{
    public function index(){
        // 1. 根据用户id查询详细信息
        $name = User::find(1)->getInfo->cn_name;
        //  等价于: $name = User::where('id', 1)->first()->getInfo->cn_name;
        dump($user);

        // 2. 根据详细信息id查询用户
        $info = UserInfo::where('id', 1)->get()[0];
        dump($info->user->name);
    }
}

---

一对多实例

数据表如下图所示

数据表设计

// ========== 用户模型 ==========
namespace App;

use Illuminate\Database\Eloquent\Model;

class User extends Model {
    protected $table      = 'users';
    protected $primaryKey = 'id';
    protected $guarded    =  [];
    public    $timestamps = false;

    // 一对多使用hasMany方法关联 App\Article 模型
    public function article(){
        return $this->hasMany('App\Article', 'user_id', 'id');
    }
    
}

// ========== 文章模型 ==========
namespace App;

use Illuminate\Database\Eloquent\Model;

class Article extends Model {
    protected $table      = 'articles';
    protected $primaryKey = 'id';
    public    $timestamps = false;
    protected $guarded    = [];

    // 与 App\User 模型建立关联关系
    public function author(){
        return $this->belongsTo('App\User', 'user_id');
    }

}

// ========== 查询数据 ==========
namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Article;
use App\User;

class DBController extends Controller{
    public function index() {
        // 查询出指定id用户的所有文章
        $articles = User::find(1)->article;
        dump($articles); // collection
    }
}