通过Yii2 Gii脚手架，能够快速开发产品原型，验证商业市场。平时常用到代码总结

工具篇

help类

模型

手动切换数据库，Yii::$app->db，修改db为配置文件的key，例如YII::$app->operation
model类静态方法getDb，返回Yii::$app->operation

AR

// 查询一条记录
// asArray的作用是以数组形式返回结果
// find()会返回一个ActiveQuery对象，ActiveQuery对象继承自yii\db\Query，所以可以使用Query中的所有查询方法
$data1 = MyUser::find()->where(['id' => 1])->asArray()->one();

// 查询多条记录
$data2 = MyUser::find()->where([])->orderBy('id desc')->asArray()->all();

// 根据主键获取一条记录
$data3 = MyUser::findOne(3);

// 根据主键获取多条记录
$data4 = MyUser::findAll([4, 5, 6, 7]);

// 带条件查询多条记录
$data5 = MyUser::findAll(['sex' => 1]);

// 运行原生SQL
$data6 = MyUser::findBySql('select * from {{%user}} where id=:id', [':id' => 8])->asArray()->one();

// 添加数据
// 准备一个新的实例，然后设置对应字段属性，保存
$user = new MyUser();
$user->name = 'test';
$user->sex = 1;
$user->age = 22;
$user->save();

// 修改数据
// 先获取一个对象，然后设置对应字段属性，保存
$upd = MyUser::findOne(10);
$upd->name = 'abcdefg';
$upd->save();

// 修改多条数据
$data7 = MyUser::updateAll(['name' => 'update'], ['sex' => 1]);

// 删除一条数据
$del = MyUser::findOne(15);
$del->delete();

// 删除多条数据
$data8 = MyUser::deleteAll(['sex' => 0]);

// 事务操作
$user2 = MyUser::findOne(22);
$user3 = MyUser::findOne(33);
$trans = MyUser::getDb()->beginTransaction();
try {
    $user2->name = '2222';
    $user3->name = '3333';
    //修改数据
    $user2->save();
    $user3->save();

    $trans->commit();
} catch (\Exception $e) {
    $trans->rollBack();
    throw $e;
}

createCommand来处理数据库

// YII2中通过createCommand来处理数据库

// 查询多条记录
// {{%user}} 表示如果设置了表前缀，YII会自动帮你替换
$data1 = YII::$app->db->createCommand('select * from {{%user}}')->queryAll();

// 查询一条记录
// createCommand的第二个参数可以进行参数绑定
$data2 = YII::$app->db->createCommand('select * from {{%user}} where id=:id', ['id' => 2])->queryOne();

// 返回一列(第一列)数据
$data3 = YII::$app->db->createCommand('select name from {{%user}}')->queryColumn();

// 返回一个标量值，常用于统计
$data4 = YII::$app->db->createCommand('select count(*) as cnt from {{%user}}')->queryScalar();

// 绑定参数，防止SQL注入问题
// bindValue绑定一个参数
$data5 = YII::$app->db->createCommand('select * from {{%user}} where id=:id')
    ->bindValue(':id', 3)
    ->queryOne();

// 绑定多个参数
$data6 = YII::$app->db->createCommand('select * from {{%user}} where id=:id and name=:name')
    ->bindValues([':id' => 5, ':name' => 'eee'])
    ->queryOne();

// 绑定参数引用
$id = 7;
$data7 = YII::$app->db->createCommand('select * from {{%user}} where id=:id')
    ->bindParam(':id', $id)
    ->queryOne();

// 执行非查询语句
$data8 = YII::$app->db->createCommand('update {{%user}} set name=:name where id=:id')
    ->bindValues([':name' => 'abcdef', ':id' => 8])
    ->execute();

// 当然，我们也可以用更加简便的方法
// insert()插入
$data9 = YII::$app->db->createCommand()->insert('{{%user}}', [
    'name' => 'test',
    'sex' => 1,
    'age' => 28,
])->execute();

// batchInsert()批量插入
$data10 = YII::$app->db->createCommand()->batchInsert('{{%user}}', ['name', 'sex', 'age'], [
    ['111', 1, 11],
    ['222', 1, 22],
])->execute();

// update()更新
$data11 = YII::$app->db->createCommand()->update('{{%user}}', [
    'name' => '1242143214'
], 'id=:id', ['id' => 10])->execute();

// delete()删除
$data12 = YII::$app->db->createCommand()->delete('{{%user}}', 'id=:id', ['id' => 11])->execute();

// 执行事务
$trans = YII::$app->db->beginTransaction();

try {
    YII::$app->db->createCommand()->update('{{%user}}', ['age' => 12], 'id=:id', ['id' => 13])->execute();
    YII::$app->db->createCommand()->update('{{%user}}', ['age' => 22], 'id=:id', ['id' => 14])->execute();

    $trans->commit();
} catch (\Exception $e) {
    //如果语句中有一个执行失败，那么就将回滚
    $trans->rollBack();
    throw $e;
}

// 获取表的定义信息
$info = YII::$app->db->getTableSchema('{{%user}}');

聚合

# 数据库一列求和
Book->find()->sum('price');
// SELECT SUM(price) FROM `book`

# 两列求和
Book->find()->select('sum(price), sum(view_count)')->asArray()->one();
// SELECT SUM(price), sum(view_count) FROM `book`

Book->find()->select('sum(price) as price_sum, sum(view_count) as view_count_sum')->asArray()->one();
// SELECT sum(price) as price_sum, sum(view_count) as view_count_sum FROM `book`

视图

GridView

# 是否可显示
[
    "attribute" => "name",
    "value" => $model->name,
    "visible" => intval(Yii::$app->request->get("type")) == 1,
],

# 跳转链接
[
    "attribute" => "order_id",
    "format" => "raw",
    "value" => function ($model) {
        return Html::a($model->order_id, "/order?id={$model->order_id}", ["target" => "_blank"]);
    },
],

# format格式参数，图像定义；raw表示html
[
    "label" => "头像",
    "format" => [
        "image", 
        [
            "width"=>"84",
            "height"=>"84"
        ]
    ],
    "value" => function ($model) { 
        return $model->image; 
    }
],

# 自定义按钮
[
    "class" => "yii\grid\ActionColumn",
    "template" => "{get-xxx} {view} {update}",
    "header" => "操作",
    "buttons" => [
        "get-xxx" => function ($url, $model, $key) { 
            return Html::a("获取xxx", $url, ["title" => "获取xxx"] ); 
        },
    ],
],

# 表格列宽度
[
    "attribute" => "title",
    "value" => "title",
    "headerOptions" => ["width" => "100"],
],

# 自定义行样式
<?= GridView::widget([
    // ......
    "dataProvider" => $dataProvider,
    "rowOptions" => function($model, $key, $index, $grid) {
        return ["class" => $index % 2 ==0 ? "label-red" : "label-green"];
    },
    // ......
]); ?>

# 调用JS函数
[
    "class" => "yii\grid\ActionColumn",
    "header" => "操作",
    "template" => "{view} {update} {update-status}",
    "buttons" => [
        "update-status" => function ($url, $model, $key) {
            return Html::a("更新状态", "javascript:;", ["onclick"=>"update_status(this, ".$model->id.");"]); },
    ],
],

# 禁止排序
$dataProvider = new ActiveDataProvider([ "query" => $query, ]); 
$dataProvider->setSort(false);

# 禁止搜索"filter" => false,
[
    "attribute" => "title",
    "value" => "title",
    "filter" => false,
    "headerOptions" => ["width" => "100"],
]