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();
1
返回包含结果集额的Illuminate\Support\Collection，其中每一个结果都是 PHP 的 StdClass 对象实例

first()
$emp = DB::table('employees')->first();
1
从数据表中获取一行数据

value()
$values = DB::table('employees')->where('emp_no','>=',499995)->value('first_name');
1
只获取第一条结果的某一列的数据

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>';
    }
});
1
2
3
4
5
将查询结果分块在回调函数中进行处理。

聚合函数
//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();
1
过滤重复结果

原生语句
DB::Raw()

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

$result = DB::table('employees')->select(DB::raw('count(1) as num'))->whereRaw('emp_no>=?',[499980])->get();
1
此外还有orWhereRaw(),havingRaw(),orhavingRaw(),orderByRaw()

Join
join

$result = DB::table('employees')->join('salaries','employees.emp_no','=','salaries.emp_no')->where('employees.emp_no','>=','499980')->get();
1
leftJoin,rightJoin类似。

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

$users = DB::table('sizes')
        ->crossJoin('colours')
        ->get();
1
2
3
高级连接

$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();
1
whereBetween

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

whereIn,whereNotIn

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

whereNull,whereNotNull

DB::table('employees')->whereNull('birth_date')->get();
1
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();
1
groupBy(),having
DB::table('salaries')->select('emp_no','salary')->groupBy('emp_no','salary')->having('emp_no','>','499990')->get();
1
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']);
1
update()
DB::table('employees')->where('id',1)->update(['last_name'=>'Ma','gendger'=>'M']);
1
delete()
DB::table('employees')->where('id',1)->delete();
1
Eloquent
定义模型
$ php artisan make:model Employee
1
这将在项目中生成一个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();//条件查询

1
2
3
就是将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();
0
更新
$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']);
1
删除
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;
1
2
3
逆向
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';
}