YII2.0框架(四) 数据库操作yii\db\Commond

来源：互联网发布：直通网络测试仪怎么用编辑：程序博客网时间：2024/06/06 03:47

摘自：http://www.yiichina.com/doc/guide/2.0/db-dao

// 1. 执行SQL查询

$admins = Yii::$app->db->createCommand('SELECT * FROM tb_admin')->queryAll(); //查询所有数据

$admins = Yii::$app->db->createCommand('SELECT * FROM tb_admin WHERE id = 1')->queryOne(); //查询单条数据

            $admins = Yii::$app->db->createCommand('SELECT name FROM tb_admin WHERE id = 1')->queryColumn();//查询某列数据
            $admins = Yii::$app->db->createCommand('SELECT COUNT(id) FROM tb_admin WHERE id = 1')->queryScalar(); //查询标量
            // 2. 绑定参数
            $params = [':id' => 1];
            $id = 1;
            $admins = Yii::$app->db->createCommand('SELECT * FROM tb_admin WHERE id = :id')->bindValue(':id',1)->queryOne(); //查询单条数据
            $admins = Yii::$app->db->createCommand('SELECT * FROM tb_admin WHERE id = :id')->bindValues($params)->queryOne(); //查询单条数据
            $admins = Yii::$app->db->createCommand('SELECT * FROM tb_admin WHERE id = :id', $params)->queryOne(); //查询单条数据
            $admins = Yii::$app->db->createCommand('SELECT * FROM tb_admin WHERE id = :id')->bindParam(':id', $id)->queryOne(); //查询单条数据
            // 3. 执行非查询语句
            $rs = Yii::$app->db->createCommand('
                    UPDATE tb_admin SET name="小明" WHERE id=:id
                ')->bindParam(':id', $id)->execute(); //修改单条数据
            $rs = Yii::$app->db->createCommand()->update('tb_admin', ['name'=>'小芳'], 'id=1')->execute(); //修改单条数据
            $rs = Yii::$app->db->createCommand()->insert('tb_admin', [
                'name' => '小芳',
                'phone' => '1500',
                'role_id' => '10',
                'password' => 'admin',
                'created' => '2017-05-05 19:52:07',
                ])->execute(); //插入单条数据
            $rs = Yii::$app->db->createCommand()->delete('tb_user', 'id=1')->execute(); //删除单条数据
            $rs = Yii::$app->db->createCommand()->batchInsert('tb_admin', ['name', 'phone', 'role_id', 'password', 'created'],[
                ['小明','1233','23','23','2017-05-05 19:52:07'],
                ['小明','1233','23','23','2017-05-05 19:52:07'],
                ])->execute(); //插入多条数据
            // 4. 引用表和列名称
            $admins = Yii::$app->db->createCommand('SELECT COUNT([[id]]) FROM {{tb_admin}} WHERE id = 1')->queryScalar(); //查询标量
            // 5. 使用表前缀
            $admins = Yii::$app->db->createCommand('SELECT COUNT([[id]]) FROM {{%admin}} WHERE id = 1')->queryScalar(); //查询标量
            // 6. 执行事务
            Yii::$app->db->transaction(function($db){
                $sql1 = 'UPDATE tb_admin SET name="小明" WHERE id=1';
                $sql2 = 'UPDATE tb_user SET name="小明" WHERE id=12';
                $db->createCommand($sql1)->execute();
                $db->createCommand($sql2)->execute();
            });
            $db = Yii::$app->db;
            $transaction = $db->beginTransaction();
            try {
                $sql1 = 'UPDATE tb_admin SET name="11明" WHERE id=1';
                $sql2 = 'UPDATE tb_admin SET name="" WHERE id=null';
                $db->createCommand($sql1)->execute();
                $db->createCommand($sql2)->execute();
                $transaction->commit();
            } catch (\Exception $e) {
                $transaction->rollback();
                throw $e;
            }

0 0