插入语句

insert

1. Accepts an array of column names and values

DB::table('users')->insert([
    'email' => '[email protected]',
    'votes' => 0
]);

2. Pass an array of arrays to insert multiple records at once

DB::table('users')->insert([
    ['email' => '[email protected]', 'votes' => 0],
    ['email' => '[email protected]', 'votes' => 0],
]);

insertOrIgnore

1. 插入数据库时​​忽略错误

DB::table('users')->insertOrIgnore([
    ['id' => 1, 'email' => '[email protected]'],
    ['id' => 2, 'email' => '[email protected]'],
]);

insertUsing

1. 插入新记录,Also use subqueries to determine what data should be inserted

 DB::table('pruned_users')->insertUsing([
    'id', 'name', 'email', 'email_verified_at'
], DB::table('users')->select(
    'id', 'name', 'email', 'email_verified_at'
)->where('updated_at', '<=', now()->subMonth()));

自增 IDs

1. insertGetId

$id = DB::table('users')->insertGetId(
    ['email' => '[email protected]', 'votes' => 0]
);

更新语句

update

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

updateOrInsert

Update existing records in the database,不存在则创建

Use the first argument's column and value pair to locate the matching database record. 如果记录存在,It will be updated with the value in the second parameter.如果找不到记录,will insert a new record.

DB::table('users')
    ->updateOrInsert(
        ['email' => '[email protected]', 'name' => 'John'],
        ['votes' => '2']
    );

自增与自减

1. increment

DB::table('users')->increment('votes');

DB::table('users')->increment('votes', 5);

2. decrement

DB::table('users')->decrement('votes');

DB::table('users')->decrement('votes', 5);

3. 在操作期间,Specifies additional columns to update

DB::table('users')->increment('votes', 1, ['name' => 'John']);

删除语句

1. delete
   从表中删除记录.返回受影响的行数

$deleted = DB::table('users')->delete();

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

2. truncate
   All records will be deleted from the table and will be auto-incremented ID 重置为零

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