Yii2 常用数据库操作函数用法

本文实例讲述了Yii2 常用数据库操作函数用法。分享给大家供大家参考，具体如下：

//查询

// 根据主键查询  主键为 1
$user = User::findAll(1);
$user = User::findOne(10);

// 上面的代码等价于
$user = User::find()->where(['id' => 10])->all();

// 查询主键为 1, 2 or 6.
$user = User::findAll([1, 2, 6]);
$user = User::find()->where(['IN', 'id', [1, 2, 6]])->all();

// 上面的代码等价于
$user = User::find()->where(['id' => [1, 2, 6]])->all();

// 查找排名为2，状态为1
$user = User::findAll(['rank' => 2, 'status' => 1]);

// 上面的代码等价于
$user = User::find()->where(['rank' => 2, 'status' => 1])->all();

// 使用参数绑定
$user = User::find()->where('rank=:rank AND status=:status')->addParams([':rank' => 2, ':status' => 1])->all();

// 使用索引，返回的结果集数组以主键id为键名
$user = User::find()->indexBy('id')->where(['rank' => 2, 'status' => 1])->all();

// 获取结果集数量
$count = User::find()->where(['rank' => 2, 'status' => 1])->count();

// 添加额外的查询条件
$user = User::find()->where(['rank' => 2, 'status' => 1])->andWhere('score > 100')->orderBy('id DESC')->offset(5)->limit(10)->all();

// 通过sql语句查询
$user = User::findBySql('SELECT * FROM user WHERE rank=2 AND status=1 AND score>100 ORDER BY id DESC LIMIT 5,10')->all();


//修改

// 更新id为1这条记录的状态
$user = User::findOne(1);
$user->status = 1;
$user->update();

// 上面的代码等价于
User::updateAll(['status' => 1], 'id = :id', [':id' => 1]);


//删除

// 删除id为1这条记录
User::findOne(1)->delete();

// 上面的代码等价于
User::deleteAll(['status' => 1], 'id = :id', [':id' => 10]);




//----------------使用子查询----------------------

$subQuery = (new Query())->select('COUNT(*)')->from('user');
// SELECT `id`, (SELECT COUNT(*) FROM `user`) AS `count` FROM `user`
$query = (new Query())->select(['id', 'count' => $subQuery])->from('user');


//----------------写SQL-----------------------

// select
$user = Yii::$app->db->createCommand('SELECT * FROM user')->queryAll();
// update
Yii::$app->db->createCommand()->update('user', ['status' => 1], 'id=2')->execute();
// delete
Yii::$app->db->createCommand()->delete('user', 'id=2')->execute();

//事务
$transaction1 = $connection->beginTransaction();
try {
    $connection->createCommand($sql1)->execute();
// internal
    $transaction2 = $connection->beginTransaction();
    try {
        $connection->createCommand($sql2)->execute();
        $transaction2->commit();
    } catch (Exception $e) {
        $transaction2->rollBack();
    }
    $transaction1->commit();
} catch (Exception $e) {
    $transaction1->rollBack();
}

主从配置

[
    'class' => 'yii\db\Connection',
    // master
    'dsn' => 'dsn for master server',
    'username' => 'master',
    'password' => '',
    // slaves
    'slaveConfig' => [
        'username' => 'slave',
        'password' => '',
        'attributes' => [
            // use a smaller connection timeout
            PDO::ATTR_TIMEOUT => 10,
        ],
    ],
    'slaves' => [
        ['dsn' => 'dsn for slave server 1'],
        ['dsn' => 'dsn for slave server 2'],
        ['dsn' => 'dsn for slave server 3'],
        ['dsn' => 'dsn for slave server 4'],
    ],
]

希望本文所述对大家基于Yii框架的PHP程序设计有所帮助。