yii2 对数据库的一系列操作

//数据库访问
        $db = \Yii::$app->db;
        $sql = "select id, title, content from article";
        $res = $db->createCommand($sql)->queryAll();  //返回数组数据
        $res = $db->createCommand($sql)->queryOne(); // 返回第一条数据
        $res = $db->createCommand($sql)->queryColumn(); //返回第一列数据  id
        $sql = "select count(*) from test";
        $res = $db->createCommand($sql)->queryScalar();  //返回一个标量

        //传入条件时，防止sql攻击  通过绑定参数，原理就是预处理语句
        $res = $db->createCommand("select id,title,content from article where id=:id and status=:status")
            ->bindValue(':id',1)
            ->bindValue(':status', 1)
            ->queryOne();

        //可以一次预处理sql  绑定不一样的参数执行  提高性能
        $command = \Yii::$app->db->createCommand('SELECT id,title,content FROM article WHERE id=:id');
        $post1 = $command->bindValue(':id', 1)->queryOne();
        $post2 = $command->bindValue(':id', 2)->queryOne();
        //var_dump($post1);
        //var_dump($post2);

        // bindParam 可以传引用
        $command = \Yii::$app->db->createCommand('SELECT id,title,content FROM article WHERE id=:id')->bindParam(':id',$id);
        $id = 2;
        $res = $command->queryOne();

        //非查询语句（增，删，改）
        $sql = "insert into article(id,title,content) VALUE (null,'我是标题','我是内容')";
        $sql = "delete from article where id = 6";
        $sql = "update article set title='I am title' where id=7";
        $res = \Yii::$app->db->createCommand($sql)->execute();
        //对于增删改也提供了另外一种写法，不需要写sql语句
        //$res = \Yii::$app->db->createCommand()->insert('article',['id'=>null,'title'=>'我是标题','content'=>'我是内容'])->execute();
        //$res = \Yii::$app->db->createCommand()->delete('article',['id'=>4])->execute();
        //$res = \Yii::$app->db->createCommand()->update('article',['title'=>'wo shi biaoti'], ['id'=>10])->execute();
        //注： delete和update最后一个参数params 不知道如何用，如果有人看到，请留言  谢谢

        // 一次插入多条
        /*$res = \Yii::$app->db->createCommand()->batchInsert('article',['id','title','content'],
            [[null, '标题1','内容1'],
            [null, '标题2','内容2'],
            [null, '标题3','内容3']]
            )->execute();*/

        // upsert 一种原子操作，修改或者插入
        $res = \Yii::$app->db->createCommand()->upsert('article',['id'=>26, 'title'=>'标题4', 'content'=>'内容4'],['title'=>'biaoti'])->execute();

        // 引用表和列名称
        $count = \Yii::$app->db->createCommand("SELECT COUNT([[id]]) FROM {{article}}")
            ->queryScalar();
        //var_dump($count);
       // var_dump($res);die;
        //事务操作
       /* \Yii::$app->db->transaction(function($db) {
            $db->createCommand("update article set title='title11111' where id=1")->execute();
            $db->createCommand("update article1 set title='title1' where id=100")->execute();
            // ... executing other SQL statements ...
        });*/

        /*$db = \Yii::$app->db;
        $transaction = $db->beginTransaction();
        try {
            $db->createCommand("update article set title='title11111' where id=1")->execute();
            $db->createCommand("update article set title1='title' where id=100")->execute();
            // ... executing other SQL statements ...

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

     //查询生成器
        //查询构造器
        $db = new \yii\db\Query();
        $data = $db->select(['id', 'title', 'content'])   //查看的字段
            ->from('article')                               // 表名
            ->where(['id'=>1])                                // 条件
            ->one();                                         // 在这里可以指定数据库
        $data = $db->select(['id', 'title', 'content'])
        ->from('article')
        ->where(['id'=>1])
        ->all();

        // 查询条件
        // 1> and
        $data = $db->select(['id', 'title', 'content'])
            ->from('article')
            ->where(['id'=>1, 'title'=>'title1'])
            ->one();
        // 2> or
        $data = $db->select(['id', 'title', 'content'])
            ->from('article')
            ->where(['or','id=1', 'title="title1"'])
            ->all();
        // 3> between
        $data = $db->select(['id', 'title', 'content'])
            ->from('article')
            ->where(['between','id', 1, 10])
            ->all();
        // 4> in
        $data = $db->select(['id', 'title', 'content'])
            ->from('article')
            ->where(['in','id', [1, 2, 5]])
            ->all();
        // 5> like
        $data = $db->select(['id', 'title', 'content'])
            ->from('article')
            ->where(['like','title', '我'])
            ->all();
        // 6> > <
        /*$data = $db->select(['id', 'title', 'content'])
            ->from('article')
            ->where(['<','id', 10])
            ->all();*/
        //联查 join  leftjone  rightjoin union
        $db = new \yii\db\Query();
        /*$data = $db->select(['a.id', 'title', 'content','name'])
            ->from('article as a')
            ->innerJoin('category as c', 'a.category_id=c.id')
            ->all();*/

         $db->select(['id'])
            ->from('article')
            ->limit(5);

        $db2 = new \yii\db\Query();
        $db2->select(['id'])
            ->from('category')
            ->limit(3);

        $data = $db->union($db2)->all();
        //分页  分组
        $dbdb = new \yii\db\Query();
        $data = $dbdb->select(['id', 'title', 'content'])
            ->from('article')
            ->offset(2)
            ->limit(5)
            ->all();
        $data = $dbdb->select(['id', 'title', 'content', 'count(id) c'])
            ->from('article')
            ->groupBy('title')
            ->orderBy('id DESC')
            ->all();
        //var_dump($data);
     //活动记录

        //增
       /* $model = new Article();
        $model->title = 'wo Shi';
        $model->content = 'content';
        $model->is_top = 1;*/
       // var_dump($model->save()); die;  // 默认会去model做验证

        //删
       /* $article = Article::findOne(50);
        $bool = $article->delete();
        var_dump($bool);die;*/

        //改
        /*$article = Article::findOne(1);
        $article->title = '我要修改你';
        $article->save();die;*/

        //查询
        $res = Article::findAll(['id'=>1]);  //对象
        $res = Article::findOne(1)->toArray(['id', 'title']);  //默认条件是主键  默认对象 toArray 转成数组 里边可以设置显示的字段
        $res = Article::find()->select(['id', 'title'])->asArray()->where(['id'=>1])->all();
        $res = Article::find()->select(['id', 'title'])->asArray()->groupBy('title')->orderBy('id DESC')->all();
        $res = Article::find()
            ->select(['id', 'title'])
            ->where(['id'=>1])
            ->andWhere(['is_top'=>0])
            ->asArray()
            ->one();
        $modle = Article::find();
        $res = $modle
            ->select(['id', 'title'])
            ->where(['id'=>1])
            ->orWhere(['is_top'=>0])
            ->asArray()
            ->all();

        //表关联
       // header('content-type:text/html;charset=utf-8');
        // 一对一
        $res = Article::find()
            //->select('article.*')
            ->asArray()
            ->with('category')
            ->all();
        // with 后边跟的category是在Artile模型里定义的getCategory方法
        //内容为如下
       /* public function getCategory(){
            return $this->hasOne(Category::className(), ['id'=>'category_id']);
        }*/
       // echo $modle->createCommand()->getRawSql();die;//输出sql语句  调试代码很有用哦

        //一对多
        $res = \app\models\Category::find()->with('article')->asArray()->all();
        // with 后边跟的article是在category模型里定义的方法getArticle
        //内容如下
        /*public function getArticle(){
            return $this->hasMany(Article::className(), ['category_id'=>'id']);
        }*/
        var_dump($res);die;

如有错误，欢迎指正  谢谢哦