支持视频地址https://www.bilibili.com/video/av70545323?p=6创建模型phpartisanmake:modelUser//默认对应表为usersphpartisanmake:modelAbCd//默认对应表是ab_cds/**规则：1.除第一个大写字母外，其他大写字母前加下划线2.将所有大写字母改为小写3.在末尾加s*/modelreading\App\User：：全部（）;//返回包含所有对象的集合\App\User::where('name','John')->first();//返回对象\App\User::where('id',1)->value('name');//返回值，例如'leon'\App\User::find(3);//返回主键等于3的对象\App\User::find([1,2]);//返回主键等于1和2的对象集合\App\User::pluck('age');//返回包含字段值的集合\App\User::pluck('age','id');//返回关联的集合id=>age，最多摘取2个参数\App\User::count();//返回记录总数\App\User::max('id');//返回一个数字，图书馆没有任何记录返回null\App\User::min('id');//返回一个数字，库中没有记录returnnull\App\User::avg('age');//返回一个数字，库中没有任何记录返回null，同名averge\App\User::sum('salary');//返回一个数字，图书馆没有任何记录return0wherewhere('votes','=',100)where('votes',100)where('votes','>=',100)where('votes','<>',100)where('name','like','T%')where([['status','=','1'],['subscribed','<>','1'],])where('votes','>',100)->orWhere('name','John')whereBetween('votes',[1,100])//包含1和100whereNotBetween('votes',[1,100])whereIn('id',[1,2,3])whereNotIn('id',[1,2,3])whereNull('last_name')whereNotNull('updated_at')whereDate('created_at','2016-12-31')//其中日期(created_at)='2016-12-31')whereMonth('created_at','12')whereDay('created_at','31')whereYear('created_at','2016')whereTime('created_at','=','11:20:45')whereColumn('first_name','last_name')//判断两个字段相等whereColumn('updated_at','>','created_at')whereColumn([['first_name','=','last_name'],['updated_at','>','created_at']])where('finalized',1)->exists();//returntrueorfalse//selectexists(select*from`xxx`where`finalized`=1)as`exists`where('finalized',1)->doesntExist();//运行的SQL是一样的如上，Laravel将运行结果反转，达到目的where('name','=','John')->orWhere(function($query){//将闭包传入orWhere，避免对全局造成不利影响scope$query->where('votes','>',100)->where('title','<>','Admin');})//where`name`=Johnor(`votes`>100和`title`<>Admin)whereExists(function($query){$query->selectRaw(1)->from('orders')->whereRaw('orders.user_id=users.id');})//whereexists(select1fromorderswhereorders.user_id=users.id)->whereRaw('price>IF(state="TX",?,100)',[200])->get();//IF用法：IF(expr1,expr2,expr3)//如果(expr1<>0andexpr1<>NULL)，则expr1为真json$users=\App\User::where('options->language','en')->get();//select*from`users`wherejson_unquote(json_extract(`options`,'$."language"'))='en'$users=\App\User::where('preferences->dining->meal','salad')->get();//select*from`users`wherejson_unquote(json_extract(`preferences`,'$."dining"."meal"'))='沙拉'$users=\App\User::whereJsonLength('options->languages',0)->get();//select*from`users`wherejson_length(`options`,'$."languages"')=0$users=\App\User::whereJsonLength('options->languages','>',1)->get();//select*from`users`wherejson_length(`options`,'$."languages"')>1\App\User::orderBy('date')->where('active',false)->chunk(100,function($users){//取每100个一组foreach($usersas$user){//...//returnfalse;//随时可以退出}});//选择ct*from`users`where`active`=0orderby`date`asclimit3offset0//select*from`users`where`active`=0orderby`date`asclimit3offset3//注意，laravel把false转化了0//不会出现chunk绑定后，还有很多记录没有被处理的情况\App\User::where('active',false)->orderBy('date')->chunkById(100,function($users){foreach($usersas$user){\App\User::where('id',$user->id)->update(['active'=>true]);}});//select*from`users`where`active`=0orderby`date`asc,`id`asclimit3//select*from`users`where`active`=0and`id`>3orderby`date`asc,`id`asclimit3select$users=\App\User::select('name','emailasuser_email')->get();$users=\App\User::distinct()->get();//selectdistinct*from`users1`$query=\App\User::select('name');$users=$query->addSelect('age')->get();//从`users`中选择`name`,`age`->selectRaw('department,SUM(price)astotal_sales')->groupBy('department')->havingRaw('SUM(price)>?',[2500])->orderByRaw('updated_at-created_atDESC')->get();//选择部门,SUM(price)astotal_salesfrom`destinations`groupby`department`havingSUM(price)>2500orderbyupdated_at-created_atDESCjoin和union#innerjoin->join('contacts','users.id','=','contacts.user_id')->join('orders','users.id','=','orders.user_id')->select('users.*','contacts.phone','orders.price')->get();#leftjoin->leftJoin('posts','users.id','=','posts.user_id')->get();#rightjoin->rightJoin('posts','users.id','=','posts.user_id')->get();#crossjoin->crossJoin('colours')->get();#高级join->join('contacts',function($join){$join->on('users.id','=','contacts.user_id')->orOn('users.pid','=','contacts.pid');})->get();//select*from`users`//innerjoin//`contacts`//on`users`.`id`=`contacts`.`user_id`or`users`.`pid`=`contacts`.`pid`->join('contacts',function($join){$join->on('users.id','=','contacts.user_id')->where('contacts.user_id','>',5);})->get();//select*from`users`innerjoin`contacts`//on//`users`.`id`=`contacts`.`user_id`和`contacts`.`user_id`>5#union$first=App\User::whereNull('first_name');$users=App\Student::whereNull('last_name')->union($first)->get();//(select*from`students`where`last_name`isnull)union(select*from`users`where`first_name`isnull)#unionAll和union参数一式//unionAll不会去去除重新排序、分组和页面orderBy('name','desc')latest()//===orderBy('created_at','desc')inRandomOrder()//orderbyRAND()->groupBy('account_id')->having('account_id','>',100)->groupBy('site','qianjinyike.com')->having('account_id','>',100)skip(10)->take(5)//等价于offset(10)->limit(5)//limit5offset10分支持行sql//$role有值执行闭包$role=$request->input('role');$users=\App\User::when($role,function($query)use($role){return$query->where('role_id',$role);})->get();//$role有值执行第一个闭包，否则执行第二个闭包$sortBy=null;$users=\App\User::when($sortBy,function($query,$sortBy){return$query->orderBy($sortBy);},function($query){返回$query->orderBy('名称');})->get();刷新模型$flight=App\Flight::where('number','FR900')->first();$freshFlight=$flight->fresh();//转到数据库并再次获取$flight=App\Flight::where('number','FR900')->first();$flight->number='FR456';$flight->refresh();$航班->号码;//“FR900”游标允许您使用游标遍历数据库数据，一次执行一个查询游标方式在处理大数据量请求时可以大大减少内存占用：foreach(Flight::where('foo','bar')->cursor()as$flight){//}$users=App\User::cursor()->filter(function($user){return$user->id>500;});高级子查询Destination::addSelect(['last_flight'=>Flight::select('name')->whereColumn('destination_id','destinations.id')->orderBy('arrived_at','desc')->latest()->limit(1)])->get();//选择`destinations`.*,(select`name`from`flights`where`destination_id`=`destinations`.`id`orderby`arrived_at`desc,`created_at`desclimit1)as`last_flight`from`destinations`notfoundexceptionfindOrFail和firstOrFail方法将检索查询的第一个结果。如果没有找到相应的结果，则抛出Illuminate\Database\Eloquent\ModelNotFoundException：$model=App\Flight::findOrFail(1);$model=App\Flight::where('legs','>',100)->firstOrFail();Globalscope//可以在app文件夹下自由创建一个Scopes文件夹存放where('age','>',200);}}//使用addSelect而不是select来避免覆盖}//需要重写给定模型的boot方法，使用addGlobalScope方法200匿名全局范围（专门用于处理单个模型）where('age','>',200);});}}移除全局作用域#我们也可以通过以下方式使用年龄标识符来移除全局作用域：User::withoutGlobalScope('age')->get();#移除指定的全局作用域User::withoutGlobalScope(AgeScope::class)->get();#移除几个或所有全局作用域User::withoutGlobalScopes([FirstScope::class,SecondScope::class])->get();#删除所有全局作用域domainUser::withoutGlobalScopes()->get();localscopewhere('votes','>',100);}publicfunctionscopeActive($query){return$query->where('active',1);}}#进行方法调用时无需添加作用域e前缀$users=App\User::popular()->active()->orderBy('created_at')->get();$users=App\User::popular()->orWhere(function(Builder$query){$query->active();})->get();$users=App\User::popular()->orWhere->active()->get();动态作用域其中（'类型'，$类型）；现在，您可以在调用时传递参数：$users=App\User::ofType('admin')->get();
