Laravel—数据库操作与Eloquent模型使用总结

数据库操作

执行原生SQL

//查询
$emp = DB::select('select * from employees where emp_no = 1');
$emp = DB::select('select * from employees where emp_no = ? and gender = ?',[1,'M']);
$emp = DB::select('select * from employees where emp_no = :empNo and gender = :gender',['empNo'=>1,'gender'=>'M']);

//插入
DB::insert('insert into employees(first_name,last_name) values(?,?,?)',['Jack','Ma']);
//更新，返回受影响的行数
$affected = DB::update('update employees set gender = ? where emp_no = ?',['M',123]);
//删除，返回被删除的行数
$deleted = DB::delete('delete from employees where first_name = ?',['Jack']);
//运行通用语句，不返回任何值
DB::statement('drop table employees');

事务

//如果事务闭包中抛出异常，事务将会自动回滚；如果闭包执行成功，事务将会自动提交：
DB::transaction(function(){
    DB::insert('insert into employees(first_name,last_name) values(?,?,?)',['Jack','Ma']);
    $affected = DB::update('update employees set gender = ? where emp_no = ?',['M',123]);
});

//手动开启事务
DB::beginTransaction();
//手动回滚
DB::rollBack();
//手动提交
DB::commit();

查询构建器

table()

通过DB门面的table()函数来获取一个查询构建器实例。

get()

$emps = DB::table('employees').get();

返回包含结果集额的Illuminate\Support\Collection，其中每一个结果都是 PHP 的 StdClass 对象实例

first()

$emp = DB::table('employees')->first();

从数据表中获取一行数据

value()

$values = DB::table('employees')->where('emp_no','>=',499995)->value('first_name');

只获取第一条结果的某一列的数据

pluck()

与value类似，但是返回所有符合条件的记录列数组。

chunk()

DB::table('employees')->where('emp_no','>=',499980)->orderby('emp_no')->chunk(10,function($emps){
    foreach ($emps as $emp) {
        echo $emp->first_name. '<br>';
    }
});

将查询结果分块在回调函数中进行处理。

聚合函数

//count()
$result = DB::table('employees')->where('emp_no','>=',499980)->count();
//max()
$result = DB::table('salaries')->max('salary');
//min()
$result = DB::table('salaries')->min('salary');
//sum()
$result = DB::table('salaries')->where('emp_no','>=',499980)->sum('salary');
//avg()
$result = DB::table('salaries')->where('emp_no','>=',499980)->avg('salary');

exists()、doesntExist

$result = DB::table('employees')->where('emp_no','=','500000')->exists();
$result = DB::table('employees')->where('emp_no','=','500000')->doesntExist();

判断结果是否存在或不存在

select()，andSelect()

$result = DB::table('employees')->where('emp_no','>=','499980')->select('first_name','last_name')->get();
//上面的查询等同于：
$query = DB::table('employees')->where('emp_no','>=','499980')->select('first_name');
$result = $query->andSelect('last_name')->get();

指定查询的列

distinct()

$result = DB::table('employees')->where('emp_no','>=','499980')->select('first_name','last_name')->distinct()->get();

过滤重复结果

原生语句

DB::Raw()

$result = DB::table('employees')->select(DB::raw('count(1) as num'))->where('emp_no','>=', 499980')->get();

whereRaw()

$result = DB::table('employees')->select(DB::raw('count(1) as num'))->whereRaw('emp_no>=?',[499980])->get();

此外还有orWhereRaw(),havingRaw(),orhavingRaw(),orderByRaw()

Join

join

$result = DB::table('employees')->join('salaries','employees.emp_no','=','salaries.emp_no')->where('employees.emp_no','>=','499980')->get();

leftJoin,rightJoin类似。

crossJoin():生成一个笛卡尔积

$users = DB::table('sizes')
        ->crossJoin('colours')
        ->get();

• 高级连接

  $result = DB::table('employees')->join('salaries',function($join){
      $join->on('employees.emp_no','=','salaries.emp_no')->where('employees.emp_no','>=','499980');
  })->get();
  

  join函数的第二个参数为一个闭包，该闭包将会接收一个 JoinClause 对象用于指定 join 子句约束。

• 子查询连接

  $salaryQ = DB::table('salaries')->where('emp_no','>=','499980');
  $result = DB::table('employees')->joinSub($salaryQ,'salaries',function($join){
      $join->on('employees.emp_no','=','salaries.emp_no');
  })->get();
  return $result;
  

  可以使用 joinSub、leftJoinSub 和 rightJoinSub 方法将查询和一个子查询进行连接，每个方法都接收三个参数 —— 子查询、表别名和定义关联字段的闭包

Union

$emps = DB::table('employees')->whereRaw('emp_no between 499980 and 499990');
$result = DB::table('employees')->whereRaw('emp_no > 499990')->union($emps1)->get();

Where 子句

• 简单where子句

  //添加一个条件
  DB::table('employees')->where('emp_no','>=','499980');
  //添加多个条件,传二维数组
  DB::table('employees')->where([
      ['emp_no','>=','499980'],
      ['gender','=','F']
  ]);
  

• or语句

  DB::table('employees')->where('emp_no','>=','499980')->orWhere('emp_no','<','10')->get();
  

• whereBetween

  DB::table('employees')->whereBetween('emp_no',[499980,499999])->get();
  

  whereNotBetween同理

• whereIn,whereNotIn

  DB::table('employees')->whereIn('emp_no',[11111,11112,11113])->get();
  

  whereNotIn同理

• whereNull,whereNotNull

  DB::table('employees')->whereNull('birth_date')->get();
  

  wherNotNull同理

• whereDate / whereMonth / whereDay / whereYear / whereTime

  

orderBy()

$result = DB::table('employees')->whereIn('emp_no',[11111,11112,11113])->orderBy('hire_date','asc','birth_date','asc')->get();

groupBy(),having

DB::table('salaries')->select('emp_no','salary')->groupBy('emp_no','salary')->having('emp_no','>','499990')->get();

insert()

DB::table('employees')->insert(['firtst_name'=>'Jack','last_name'=>'Ma']);
DB::table('employees')->insert([
   ['firtst_name'=>'Jack','last_name'=>'Ma'],
   ['firtst_name'=>'Jack2','last_name'=>'Ma']
]);

• 获取自增ID

  $id = DB::table('employees')->insertGetId(['firtst_name'=>'Jack','last_name'=>'Ma']);
  

update()

DB::table('employees')->where('id',1)->update(['last_name'=>'Ma','gendger'=>'M']);

delete()

DB::table('employees')->where('id',1)->delete();

Eloquent

定义模型

$ php artisan make:model Employee

这将在项目中生成一个Employee.php，内容如下：

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Employee extends Model
{
    //
}

此时Employee模型默认对应employees表（小写的模型类名复数格式）。

模型成员变量

• $table:关联到模型的数据表
• $primaryKey:主键名（默认为id)
• $keyType:主键类型（默认为整形数据）
• $incrementing:主键是否自增（默认为true)
• $fillable:可以被赋值的属性，不能与$guarded同时使用
• $guarded:不会被赋值的属性，不能与$fillable同时使用

获取

$emps = Employee::all();//返回表中所有数据
$emps = Employee::where([['last_name','like','A%'],['gender','=','M']])->orderby('birth_date','asc')->limit(3)->get();//条件查询

就是将Employee模型就是一个查询构建器，我们可以在模型上使用查询构建起的所有方法。

插入

public function store(Request $request){
    $emp = new Employee();
    $emp->first_name = $request->input('first_name');
    $emp->last_name = $request->input('last_name');
    $emp->birth_date = $request->input('birth_date');
    $emp->hire_date = date('Y-m-d',time());
    $emp->gender = $request->input('gender');
    var_dump($emp);
    $emp->save();
}

更新

$emp = Employee::find($emp_no);//先查询
$emp->first_name = $request->input('first_name');
$emp->last_name = $request->input('last_name');
$emp->birth_date = $request->input('birth_date');
$emp->hire_date = $request->input('hire_date');
$emp->gender = $request->input('gender');
$emp->save();//更新到数据库

批量更新：

Employee::where('emp_no','>','500000')->update(['hire_date'=>'2020-05-10']);

删除

Employee::find(1)->delete();//按主键删除
Employee::where('last_name','AAA')->delete();//批量删除
Employee::destroy(1);//按主键删除
Employee::destroy([1,2,3]);//批量删除

软删除

class Employee extends Model
{
    use SoftDeletes;

    /**
     * 应该被调整为日期的属性
     *
     * @var array
     */
    protected $dates = ['deleted_at'];
}

表中有deleted_at列，那么上面的配置将会软删除

关联关系

class Employee extends Model
{
    // protected $table = 'employees_temp';
    protected $primaryKey = 'emp_no';
    public $timestamps = false;
	// 一对一，一个Employee关联一个salary
    public function salary(){
      return $this->hasOne('App\Salary','emp_no','emp_no');
    }
    // 一对多，一个Employee关联一个title
    public function titles(){
      return $this->hasMany('App\Title','emp_no','emp_no');
    }
}

hasOne和hasMany的第二个参数是外键名，默认为方法名加_id后缀，第二个参数为主键名，或当前模型的关联字段名称。

$emp = Employee::find(1);
$salary = $emp->salary;
$titles = $emp->titles;

逆向

class Salary extends Model
{
    public function Employee(){
      return $this->belongsTo('App\Employee','emp_no','emp_no');
    }
}

belongsTo第二个参数是外键名，默认为当前模型名加_id后缀，第三个参数为主键名，默认为id。

$salary = Salary::find(1);
$emp = $salary->emplioyee;

多对多

class Employee extends Model
{
    protected $primaryKey = 'emp_no';
    public $timestamps = false;
    //一个员工对应多个部门，一个部门有多个员工
    public function departments(){
      return $this->belongsToMany('App\Department','dept_emp','emp_no','dept_no');
    }
}
class Department extends Model
{
    protected $primaryKey = 'dept_no';
}

除了employees和departments两张表之外，还有一张dept_emp关系表，在belongsToMany方法的第二个参数中指定，不指定的话关系表名称默认为departments_employees（按首字母排序），belongsToMany的第三个参数为关系表中关联employees表的外键名，默认为employee_id，第四个参数为关系表中，关联departments表的外键，默认为department_id。

• pivot：我们获取的每个Employee模型都会自动附上pivot属性，该属性是中间关系表模型。

关联插入、更新

$emp = Employee::find(1);
$emp->titles()->save(new Title(['title'=>'Manager']));//插入一条
$emp->titles()->saveMany([new Title(['title'=>'Manager']), new Title(['title'=>'Developer']));