laravel增删改查+事务

数据库迁移：

BB：实际就是通过代码生成表结构，没啥用，不支持修改字段长度，还会额外生成migrate和migrates表

1、创建迁移文件(创建建表语句)：php artisan make:migration create_hello_table --create=hello

2、database/migrations下会生成名为“日期_create_hello_table.php”

3、生成迁移(生成表结构)：php artisan migrate

生成模型：

php artisan make:model Hello

 

一、增

1.1 DB方式：

DB::table('users')->insert(['email' => 'john@example.com', 'votes' => 0]);//查1条

DB::table('users')->insert([

['email' => 'taylor@example.com', 'votes' => 0],

['email' => 'dayle@example.com', 'votes' => 0]

]);//插多条

$id = DB::table('users')->insertGetId(['email' => 'john@example.com', 'votes' => 0]);//插入后获取其id

1.2 ORM方式：

$flight = new Flight; $flight->name = $request->name; $flight->save();
//批量赋值---法1
namespace App;
use Illuminate\Database\Eloquent\Model;
class Flight extends Model
{
    protected $fillable = ['name'];//白名单,可以被批量赋值的属性
}
$flight = App\Flight::create(['name' => 'Flight 10']);
如果你已经有一个模型实例，你可以传递数组给fill方法：$flight->fill(['name' => 'Flight 22']);

//批量赋值---法2
namespace App;
use Illuminate\Database\Eloquent\Model;
class Flight extends Model
{
    protected $guarded = ['price'];//黑名单,不可被批量赋值的属性。如果想让所有的属性都可以被批量赋值，就把$guarded定义为空数组。
}

firstOrCreate/firstOrNew的区别:
firstOrCreate方法会使用给定的字段及其值在数据库中查找记录。如果在数据库中找不到模型，则将使用第一个参数中的属性以及可选的第二个参数中的属性插入记录。
firstOrNew方法在数据库中查找匹配给定属性的记录。如果模型未被找到，则会返回一个新的模型实例。但返回的模型还尚未保存到数据库，必须要手动调用save方法才能保存它。
// 通过 name 属性检索航班，当结果不存在的时候用 name 属性和 delayed 属性去创建它
$flight = Flight::firstOrCreate(['name' => 'Flight 10'], ['delayed' => 1]);
$flight = Flight::firstOrNew(['name' => 'Flight 10'], ['delayed' => 1]);$flight->save();

 

二、删

2.1 DB方式：

DB::table('users')->delete();

DB::table('users')->where('votes', '>', 100)->delete();

DB::table('users')->truncate();//用truncate清空表，这将删除所有行，并重置自动递增id=0

2.2 ORM方式：

//1、主键删除
$flight = Flight::find(1);$flight->delete();
Flight::destroy(1); Flight::destroy([1, 2, 3]); Flight::destroy(1, 2, 3);

//2、查询删除
$deletedRows = Flight::where('active', 0)->delete();

//3、软删除
namespace App;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;
class Flight extends Model
{
    use SoftDeletes;//使用软删除特性
    protected $dates = ['deleted_at'];//需要被转换成日期的属性。
}
//同时应该添加deleted_at字段到数据表中。当调用模型上delete方法时，deleted_at字段会被设置为当前的日期和时间。
$flight = Flight::find(1);$flight->delete();

//判断是否已被软删除： if ($flight->trashed()) {}

//恢复被软删除的模型： $flight->restore();
//恢复关联查询：$flight->history()->restore();
//批量恢复被软删除的模型： Flight::withTrashed()->where('airline_id', 1)->restore();

//查询模型(包括被软删除的模型)：$flights = Flight::withTrashed()->where('account_id', 1)->get();
//withTrashed方法也可用于关联查询：$flight->history()->withTrashed()->get();
//只检索被软删除的模型：$flights = Flight::onlyTrashed()->where('airline_id', 1)->get();

//4、永久删除
//强制删除单个模型实例：$flight->forceDelete();
//强制删除所有相关模型：$flight->history()->forceDelete();

三、改

3.1 DB方式：

DB::table('users')->where('id', 1)->update(['votes' => 1]);

DB::table('users')->where('id', 1)->update(['options->enabled' => true]);//更新 JSON 字段

DB::table('users')->increment('votes');//自增1

DB::table('users')->increment('votes', 5);//自增指定数量

DB::table('users')->decrement('votes');//自减1

DB::table('users')->decrement('votes', 5);//自减指定数量

DB::table('users')->increment('votes', 1, ['name' => 'John']);//在操作过程中指定要更新的字段

3.2 ORM方式：

//1、单个更新：$flight = Flight::find(1); $flight->name = 'New Flight Name'; $flight->save();
//2、批量更新：Flight::where('active', 1)->where('destination', 'San Diego')->update(['delayed' => 1]);//所有active =1且destination='San Diego'的航班的delayed都会更新为1
//3、updateOrCreate: 更新现有模型或创建新模型(如果不存在)的情况。
//如果有从奥克兰飞往圣地亚哥的航班，将价格设为99美元。如果不存在匹配的模型就创建一个。
$flight = App\Flight::updateOrCreate(['departure' => 'Oakland', 'destination' => 'San Diego'],['price' => 99]);

 

四、查

4.1 DB方式

(1).取多行：DB::table('users')->get();

多条件查询：

$query->where([     ['column_1', '=', 'value_1'],     ['column_2', '<>', 'value_2'], ]);

$query->whereIn('ip', $ipArr)     ->Where('status', '=', 0);

(2).取单行：DB::table('users')->where('name', 'John')->first();$user->name;

(3).取单列：pluck/lists

DB::table('roles')->pluck('title');foreach ($titles as $title) {echo $title;}

DB::table('roles')->lists('title');

DB::table('roles')->lists('title', 'name');

(4).取单个(单行单列)：DB::table('users')->where('name', 'John')->value('email');

(5).分批获取：DB::table('users')->orderBy('id')->chunk(100, function ($users) {

foreach ($users as $user) {}

});//假如上千条数据，每次取100个

DB::table('users')->orderBy('id')->chunk(100, function ($users) {

return false;

});//可从闭包中返回false来阻止进一步的分块

(6).去重：DB::table('users')->distinct()->get();

(7).查询聚合count,max,min,avg,sum：

DB::table('users')->count();

DB::table('orders')->max('price');

DB::table('orders')->where('finalized', 1)->avg('price');

(8).选择结果集字段select：

DB::table('users')->select('name', 'email as user_email')->get();

$query = DB::table('users')->select('name'); $users = $query->addSelect('age')->get();

DB::table('users')->select(DB::raw('count(*) as user_count, status'))->where('status', '<>', 1)->groupBy('status')->get();

DB::table('orders')->selectRaw('price * ? as price_with_tax', [1.0825])->get();

(9).join联查

DB::table('users')->join('contacts', 'users.id', '=', 'contacts.user_id')->join('orders', 'users.id', '=', 'orders.user_id')->select('users.*', 'contacts.phone', 'orders.price')->get();

DB::table('users')->leftJoin('posts', 'users.id', '=', 'posts.user_id')->get();

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

DB::table('users')->join('contacts', function ($join) {

$join->on('users.id', '=', 'contacts.user_id')->orOn(...);

})->get();

DB::table('users')->join('contacts', function ($join) {

$join->on('users.id', '=', 'contacts.user_id')->where('contacts.user_id', '>', 5);

})->get();

(10).where/orWhere,whereBetween/whereNotBetween,whereIn/whereNotIn,whereNull/whereNotNull,

whereYear/whereMonth/whereDay/whereDate/whereTime,whereColumn,whereExists,,,,

DB::table('users')->where('votes', '=', 100)->get();

DB::table('users')->where('votes', 100)->get();

DB::table('users')->where('name', 'like', 'T%')->get();

DB::table('users')->where([ ['status', '=', '1'],['subscribed', '<>', '1'] ])->get();

DB::table('users')->where('votes', '>', 100)->orWhere('name', 'John')->get();

DB::table('users')->whereBetween('votes', [1, 100])->get();

DB::table('users')->whereNotBetween('votes', [1, 100])->get();

DB::table('users')->whereIn('id', [1, 2, 3])->get();

DB::table('users')->whereNotIn('id', [1, 2, 3])->get();

DB::table('users')->whereNull('updated_at')->get();

DB::table('users')->whereNotNull('updated_at')->get();

DB::table('users')->whereYear('created_at', '2016')->get();

DB::table('users')->whereMonth('created_at', '12')->get();

DB::table('users')->whereDay('created_at', '31')->get();//whereDay用于比较字段的值与特定的一个月的某一天

DB::table('users')->whereDate('created_at', '2016-12-31')->get();

DB::table('users')->whereTime('created_at', '=', '11:20')->get();

DB::table('users')->whereColumn('first_name', 'last_name')->get();//用于验证两个字段是否相等

DB::table('users')->whereColumn('updated_at', '>', 'created_at')->get();

DB::table('users')->whereColumn([ ['first_name', '=', 'last_name'],['updated_at', '>', 'created_at'] ])->get();

DB::table('users')->where('name', '=', 'John')->orWhere(function ($query) {

$query->where('votes', '>', 100)->where('title', '<>', 'Admin');

})->get();

DB::table('users')->whereExists(function ($query) {

$query->select(DB::raw(1))->from('orders')->whereRaw('orders.user_id = users.id');

})->get();//等价于select * from users where exists (select 1 from orders where orders.user_id = users.id)

DB::table('users')->where('options->language', 'en')->get();//取json字段

$users = DB::table('users')->where('preferences->dining->meal', 'salad')->get();

(11).group,order/inRandomOrder,latest/oldest,skip/take/limit/offset

DB::table('users')->groupBy('account_id')->having('account_id', '>', 100)->get();

DB::table('users')->groupBy('first_name', 'status')->having('account_id', '>', 100)->get();

DB::table('users')->orderBy('name', 'desc')->get();

DB::table('users')->inRandomOrder()->first();//将查询结果随机排序

DB::table('users')->latest()->first();默认情况下是对created_at字段进行排序

DB::table('users')->skip(10)->take(5)->get();

DB::table('users')->offset(10)->limit(5)->get();

(12).when-where

$role = $request->input('role');

$users = DB::table('users')->when($role, function ($query) use ($role) {

return $query->where('role_id', $role);

})->get();//只有当when方法的第一个参数为true时，闭包里的where语句才会执行。如果第一个参数是false，这个闭包将不会被执行。

-----------------------------------------

$sortBy = null;

$users = DB::table('users')->when(

$sortBy,

function ($query) use ($sortBy) {return $query->orderBy($sortBy);},

function ($query) {return $query->orderBy('name');}

)->get();//还可以将另一个闭包当作第三个参数传递给when方法。如果第一个参数的值为false时，这个闭包将执行。

(13).union合并查询

$first = DB::table('users')->whereNull('first_name');

$users = DB::table('users')->whereNull('last_name')->union($first)->get();

(14).原生查询

--->selectRaw代替select(DB::raw(...))

DB::table('users')->select(DB::raw('count(*) as user_count, status'))->where('status', '<>', 1)->groupBy('status')->get();

DB::table('orders')->selectRaw('price * ? as price_with_tax', [1.0825])->get();

--->whereRaw/orWhereRaw

DB::table('orders')->whereRaw('price > IF(state = "TX", ?, 100)', [200])->get();

--->havingRaw/orHavingRaw

DB::table('orders')->select('department', DB::raw('SUM(price) as total_sales'))->groupBy('department')->havingRaw('SUM(price) > 2500')->get();

--->orderByRaw

DB::table('orders')->orderByRaw('updated_at - created_at DESC')->get();

(15).悲观锁定查询(共享锁sharedLock,排他锁lockForUpdate)

DB::table('users')->where('votes', '>', 100)->sharedLock()->get();//共享锁可以防止选中的行被篡改，直到事务被提交为止

DB::table('users')->where('votes', '>', 100)->lockForUpdate()->get();//排他锁可避免行被其它共享锁修改或读取

(16).缓存查询

DB::table('users')->remember(10)->get();

DB::table('users')->cacheTags(array('people', 'authors'))->remember(10)->get();

4.2 ORM方式

各种操作的具体用法详见：https://learnku.com/docs/laravel/5.5/collections/1317#method-all
all,average,avg,chunk,collapse,combine,contains,containsStrict,count,diff,diffKeys,each,every,except,filter,first,flatMap,flatten,flip,forget,forPage,get,groupBy,has,implode,intersect,isEmpty,isNotEmpty,keyBy,keys,last,map,mapWithKeys,max,median,merge,min,mode,nth,only,partition,pipe,pluck,pop,prepend,pull,push,put,random,reduce,reject,reverse,search,shift,shuffle,slice,sort,sortBy,sortByDesc,splice,split,sum,take,tap,toArray,toJson,transform,union,unique,uniqueStrict,values,when,where,whereStrict,whereIn,whereInStrict,whereNotIn,whereNotInStrict,zip
如下选几个常用的举例说明：
//1、all与first
$flights = Flight::all();
foreach ($flights as $flight) {
    echo $flight->name;
}

//first,取回符合查询限制的第一个模型
$flight = Flight::where('active', 1)->first();

//2、通过主键id取模型
$flight = Flight::find(1);//id=1
$flights = Flight::find([1, 2, 3]);//id in (1,2,3)

//3、take
$flights = Flight::where('active', 1)->orderBy('name', 'desc')->take(10)->get();

//4、chunk分块获取,每次取200个处理。大量数据时用到。
Flight::chunk(200, function ($flights) {
    foreach ($flights as $flight) {}
});

//5、cursor游标来遍历数据库数据,每次执行一个。大量数据时用到。
foreach (Flight::where('foo', 'bar')->cursor() as $flight) {} 

//6、count与max
$count = Flight::where('active', 1)->count();
$max = Flight::where('active', 1)->max('price');

//7、找不到就抛出404异常。
$flight = Flight::findOrFail(1);
$flight = Flight::where('legs', '>', 100)->firstOrFail();

 

五、事务

DB::beginTransaction();
try {
    //...
    DB::commit();
} catch (\Exception $e) {
    DB::rollBack();
    \Log::error(json_encode([
        'class' => __CLASS__,
        'code' => $e->getCode(),
        'message' => $e->getMessage(),
        'trace' => $e->getTraceAsString()
    ]));
}