yii2数据库访问对象

yii2数据库访问对象

配置数据库链接

$db = new yii\db\Connection([
            'dsn' => 'mysql:host=localhost;dbname=xhj',
            'username' => 'root',
            'password' => '123456',
            'charset' => 'utf8',
        ]);

或 配置文件 config/web.php 添加

 'components' => [
        // ...
        'db' => [
            'class' => 'yii\db\Connection',
            'dsn' => 'mysql:host=localhost;dbname=example',
            'username' => 'root',
            'password' => '',
            'charset' => 'utf8',
        ],
    ],

查询

        //$user = Yii::$app->db->createCommand('SELECT * FROM country')->queryAll();
        
        $result = $db->createCommand('SELECT * FROM xhj_backend_member')->queryAll();
        $result = $db->createCommand('SELECT * FROM xhj_backend_member where id= 8')->queryOne();
        $result = $db->createCommand('SELECT username FROM xhj_backend_member')->queryColumn();
        $result = $db->createCommand('SELECT COUNT(*) FROM xhj_backend_member')->queryScalar();

        $id = 8;
        $result = $db->createCommand('SELECT * FROM xhj_backend_member where id=:id')->bindValue(':id',$id)->queryOne();

        $params = [':id' => 1, ':status' => 1];
        $result = $db->createCommand('SELECT * FROM xhj_backend_member where id=:id AND status=:status')->bindValues($params)->queryOne();
        $result = $db->createCommand('SELECT * FROM xhj_backend_member where id=:id AND status=:status',$params)->queryOne();

CURD

        // Yii::$app->db->createCommand('UPDATE country SET population=112233 WHERE `code`="US"')->execute();

       // Yii::$app->db->createCommand()->insert('country', ['code' => 'AD', 'name' => '艾莉','population'=>2])->execute();
       // Yii::$app->db->createCommand()->update('country', ['population' => 222], ['code'=>'AD'])->execute();

        //Yii::$app->db->createCommand()->delete('country', ['code'=>'AD'])->execute();

        Yii::$app->db->createCommand()->batchInsert('country', ['code','name','population'],[['code1', 'name1', 1], ['code2', 'name2', 2],['code3', 'name3', 3]])->execute();

事务

        $db = Yii::$app->db;
        $transaction = $db->beginTransaction();
        try {
            $db->createCommand($sql1)->execute();
            $db->createCommand($sql2)->execute();
            // ... executing other SQL statements ...

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