WHMCS NEW MYSQL Action Mode

###使用
use Illuminate\Database\Capsule\Manager as DB;

$users = DB::table('users')
->where('name', 'like', 'T%')
->get();
$users = DB::table('users')
->where('votes', '>', 100)
->orWhere('name', 'John')
->get();
$users = DB::table('users')
->whereBetween('votes', [1, 100])->get();

$users = DB::table('users')
->whereNotBetween('votes', [1, 100])
->get();
$users = DB::table('users')
->whereIn('id', [1, 2, 3])
->get();
$users = DB::table('users')
->whereNotIn('id', [1, 2, 3])
->get();
$users = DB::table('users')
->whereNull('updated_at')
->get();
$users = DB::table('users')
->whereNotNull('updated_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();

$results = DB::select('select * from users where id = ?', array(1));
$users = DB::select('select * from users where active = ?', [1]);
$results = DB::select('select * from users where id = :id', ['id' => 1]);
$users = DB::table('users')->select('name', 'email as user_email')->get();
$users = DB::table('users')->distinct()->get();
$query = DB::table('users')->select('name');

##排序查询
$users = DB::table('users')
->orderBy('name', 'desc')
->get();

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

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

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

##群组查询
$users = DB::table('users')
->select(DB::raw('count(*) as user_count, status'))
->where('status', '<>', 1)
->groupBy('status')
->get();

##分片查询
DB::table('users')->chunk(100, function($users) {
foreach ($users as $user) {
}
});
##分片查询 强制结束
DB::table('users')->chunk(100, function($users) {
// Process the records...
return false;
});

##查询单列内容
$titles = DB::table('roles')->lists('title');
foreach ($titles as $title) {
echo $title;
}
##定制键
$roles = DB::table('roles')->lists('title', 'name');
foreach ($roles as $name => $title) {
echo $title;
}
##计算查询 支持 count, max, min, avg, and sum.
$users = DB::table('users')->count();
$price = DB::table('orders')->max('price');
$price = DB::table('orders')
->where('finalized', 1)
->avg('price');

##关联查询
$users = DB::table('users')
->join('contacts', 'users.id', '=', 'contacts.user_id')
->join('orders', 'users.id', '=', 'orders.user_id')
->select('users.*', 'contacts.phone', 'orders.price')
->get();

$users = DB::table('users')
->leftJoin('posts', 'users.id', '=', 'posts.user_id')
->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();

##联合查询
$first = DB::table('users')
->whereNull('first_name');

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

插入
DB::insert('insert into users (id, name) values (?, ?)', [1, 'Dayle']);
DB::table('users')->insert(
['email' => '[email protected]', 'votes' => 0]
);
DB::table('users')->insert([
['email' => '[email protected]', 'votes' => 0],
['email' => '[email protected]', 'votes' => 0]
]);
##返回插入ID
$id = DB::table('users')->insertGetId(
['email' => '[email protected]', 'votes' => 0]
);

更新
$affected = DB::update('update users set votes = 100 where name = ?', ['John']);
DB::table('users')
->where('id', 1)
->update(['votes' => 1]);
DB::table('users')->increment('votes');
DB::table('users')->increment('votes', 5);
DB::table('users')->decrement('votes');
DB::table('users')->decrement('votes', 5);
DB::table('users')->increment('votes', 1, ['name' => 'John']);

删除表
$deleted = DB::delete('delete from users');
DB::table('users')->where('votes', '< ', 100)->delete();
DB::table('users')->truncate(); ##自增ID恢复为0

删除数据
DB::statement('drop table users');
多数据库
$users = DB::connection('foo')->select(...);
PDO模式
$pdo = DB::connection()->getPdo();

共享锁
DB::table('users')->where('votes', '>', 100)->sharedLock()->get();
DB::table('users')->where('votes', '>', 100)->lockForUpdate()->get();

创建表
DB::schema()->create('users', function($table) {
$table->increments('id');
$table->string('email')->unique();
$table->timestamps();
});
##判断是否存在 表 列
if (Schema::hasTable('users')) {

}
if (Schema::hasColumn('users', 'email')) {

}
## 创建非主链接
Schema::connection('foo')->create('users', function ($table) {
$table->increments('id');
});
##设置存储引擎
Schema::create('users', function ($table) {
$table->engine = 'InnoDB';
$table->increments('id');
});
##重命名
Schema::rename($from, $to);
##删除
Schema::drop('users');
Schema::dropIfExists('users');
##创建字段
Schema::table('users', function ($table) {
$table->string('email');
});

CREATE TABLE cloud_keypairs (
id bigint unsigned NOT NULL auto_increment COMMENT 'id',
uid bigint unsigned NOT NULL COMMENT 'owner, foreign key to account table',
keypair_name varchar(256) NOT NULL COMMENT 'name of the key pair',
fingerprint varchar(128) NOT NULL COMMENT 'fingerprint for the ssh public key',
public_key varchar(5120) NOT NULL COMMENT 'public key of the ssh key pair',
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

改为
DB::schema()->create('mod_sshkeys', function ($table) {
$table->bigIncrements('id');
$table->bigInteger('userid');
$table->string('name', 256);
$table->string('fingerprint', 256);
$table->longText('sshkeys', 5120);
//$table->primary('id'); #ID自动主键 不需要额外追加!
});

##设置变量
$table->bigIncrements('id'); Incrementing ID (primary key) using a "UNSIGNED BIG INTEGER" equivalent.
$table->bigInteger('votes'); BIGINT equivalent for the database.
$table->binary('data'); BLOB equivalent for the database.
$table->boolean('confirmed'); BOOLEAN equivalent for the database.
$table->char('name', 4); CHAR equivalent with a length.
$table->date('created_at'); DATE equivalent for the database.
$table->dateTime('created_at'); DATETIME equivalent for the database.
$table->decimal('amount', 5, 2); DECIMAL equivalent with a precision and scale.
$table->double('column', 15, 8); DOUBLE equivalent with precision, 15 digits in total and 8 after the decimal point.
$table->enum('choices', ['foo', 'bar']); ENUM equivalent for the database.
$table->float('amount'); FLOAT equivalent for the database.
$table->increments('id'); Incrementing ID (primary key) using a "UNSIGNED INTEGER" equivalent.
$table->integer('votes'); INTEGER equivalent for the database.
$table->json('options'); JSON equivalent for the database.
$table->jsonb('options'); JSONB equivalent for the database.
$table->longText('description'); LONGTEXT equivalent for the database.
$table->mediumInteger('numbers'); MEDIUMINT equivalent for the database.
$table->mediumText('description'); MEDIUMTEXT equivalent for the database.
$table->morphs('taggable'); Adds INTEGER taggable_id and STRING taggable_type.
$table->nullableTimestamps(); Same as timestamps(), except allows NULLs.
$table->rememberToken(); Adds remember_token as VARCHAR(100) NULL.
$table->smallInteger('votes'); SMALLINT equivalent for the database.
$table->softDeletes(); Adds deleted_at column for soft deletes.
$table->string('email'); VARCHAR equivalent column.
$table->string('name', 100); VARCHAR equivalent with a length.
$table->text('description'); TEXT equivalent for the database.
$table->time('sunrise'); TIME equivalent for the database.
$table->tinyInteger('numbers'); TINYINT equivalent for the database.
$table->timestamp('added_on'); TIMESTAMP equivalent for the database.
$table->timestamps(); Adds created_at and updated_at columns.

##修改表
Schema::table('users', function ($table) {
$table->string('email')->nullable();
});
->first() Place the column "first" in the table (MySQL Only)
->after('column') Place the column "after" another column (MySQL Only)
->nullable() Allow NULL values to be inserted into the column
->default($value) Specify a "default" value for the column
->unsigned() Set integer columns to UNSIGNED

Schema::table('users', function ($table) {
$table->string('name', 50)->change();
});

Schema::table('users', function ($table) {
$table->string('name', 50)->nullable()->change();
});

Schema::table('users', function ($table) {
$table->renameColumn('from', 'to');
});

Schema::table('users', function ($table) {
$table->dropColumn('votes');
});

Schema::table('users', function ($table) {
$table->dropColumn(['votes', 'avatar', 'location']);
});

##创建索引
$table->string('email')->unique();
$table->unique('email');
$table->index(['account_id', 'created_at']);
可用类型
$table->primary('id'); Add a primary key.
$table->primary(['first', 'last']); Add composite keys.
$table->unique('email'); Add a unique index.
$table->index('state'); Add a basic index.
##删除索引
$table->dropPrimary('users_id_primary'); Drop a primary key from the "users" table.
$table->dropUnique('users_email_unique'); Drop a unique index from the "users" table.
$table->dropIndex('geo_state_index'); Drop a basic index from the "geo" table.

##联动
Schema::table('posts', function ($table) {
$table->integer('user_id')->unsigned();
$table->foreign('user_id')->references('id')->on('users');
});
$table->foreign('user_id')
->references('id')->on('users')
->onDelete('cascade');
$table->dropForeign('posts_user_id_foreign');