YII2.0 查询构建器的使用

╰半橙微兮° 2022-05-11 03:42 330阅读 0赞

// DAO(Data Access Object) 数据访问对
        // 查询构建器 的使用
    
        // 主要的好处是：
        //
        // 1, 允许以面向对象方式建立一个复杂的SQL表达式
        // 2，自动引用表明和列名来 防止跟SQL保留关键字以及特殊字符的冲突
        // 3，引用参数值，使用参数绑定，从而降低了SQL 注入攻击的风险。
    
        $status = isset($_POST['status'])?$_POST['status']:null;
    
    
        // 联合方式 UNION / UNION ALL
        $subQuery_1 = (new Query())->select("id")->from("order");
        $subQuery_2 = (new Query())->select("id")->from("order");
    
        $subQuery_1->union($subQuery_2,true);// 第二个参数设置 true 则使用 UNION ALL
    
        $subQuerySql    = $subQuery_1->createCommand()->getRawSql();// 输出SQL
        $rawSql         = $subQuery_1->createCommand()->rawSql;// SQL语句  已绑定查询参数 与getRawSql()一样
        $sql            = $subQuery_1->createCommand()->sql;// SQL语句  参数使用占位符代替
        $params         = $subQuery_1->createCommand()->params;// 获取绑定的参数
    
        $results        = $subQuery_1->all();// 执行查询
    
        // 使用子查询（必须是Query查询对象） 其结果是： SELECT id FROM ($subQuerySql) AS tmp_table;
        $subSql1 = (new Query())->select('id')->from(['tmp_table' => $subQuery_1]);// tmp_table 是别名
        $subSql2 = (new Query())->select('id')->from('user')->where(['=','username','张三'])->one();
    
        $query = (new Query())->select('id,username')
            ->from('tableName')
            ->where('1=1');
        $query->addSelect('age');// 追加查询字段 SELECT id,username,age
        $query->addSelect(['address,phone']);// SELECT id,username,address,phone
    
    
    
        // JOIN => LEFT / RIGHT / INNER
        $query->join('LEFT JOIN','order','order.user_id = tableName.id');
        $query->leftJoin('order', 'order.user_id = tableName.id');
        $query->leftJoin(['orderList' => $subQuery_2], 'orderList.user_id = tableName.id');// LEFT JOIN ($subQuery_2) AS orderList ON orderList.user_id = tableName.id
    
    
        // AND
        $query->andWhere(['in','sub_id',$subSql1]);// AND sub_id IN ($subSql1)
        $query->andWhere(['sub_id',$subSql1]);// AND sub_id IN ($subSql1)
        $query->andWhere(['=','user_id',$subSql2]);// AND user_id = $subSql2
    
        // AND OR
        $query->andWhere(['and','id=1','id=3']);// AND (id=1 AND id=3)
        $query->andWhere(['or','id=1','id=3']);// AND (id=1 or id=3)
        $query->andWhere(['and','id=1',['or','id=2','id=3']]);// AND id=1 AND (id=2 OR id=3)
    
        // OR AND
        $query->orWhere(['id' => 1,'username' => 'abc']);// OR ( id=1 AND username='abc' )
        $query->orWhere(['and', ['>','id',10],['username' => 'abc']]);// OR ( id>10 AND username='abc' )
    
    
        // 判断记录是否存在  EXISTS / NOT EXISTS
        $subQuery_3 = (new Query())->select("id")
            ->from("tableNameSub")
            ->where("tableNameSub.id=tableName.sub_id");
        $query->andWhere(['exists',$subQuery_3]);// EXISTS 第二个参数必须是 Query查询实例
    
    
        // LIKE / AND LIKE / OR LIKE
        $query->andWhere(['like','tableName.name','abc']);// AND tableName.name LIKE '%abc%'
        $query->andWhere(['like','tableName.name','abc%',false]);// AND tableName.name LIKE 'abc%'
        $query->andWhere(['like','tableName.name',['abc','def']]);// AND (tableName.name LIKE '%abc%' AND tableName.name LIKE '%def%')
        $query->andWhere(['like','tableName.name',['abc','def%'],false]);// AND (tableName.name LIKE 'abc' AND tableName.name LIKE 'def%')
        $query->andWhere(['or like','tableName.name',['abc','def']]);// AND (tableName.name LIKE '%abc%' OR tableName.name LIKE '%def%')
    
    
    
        // 绑定查询参数（过滤用户输入，防止SQL 注入的攻击）
        $query->andWhere('status=:status',[':status' => $status]);
        $query->andWhere('status=:status')->addParams([':status' => $status]);
    
        // BETWEEN / NOT BETWEEN
        $query->andWhere(['between','id',2,5]);// AND id BETWEEN 2 AND 5
    
    
        // andFilterWhere() :会过滤为空的查询
        $query->andWhere(['username' => '','age' => '22']);// AND username='’AND age=22
        $query->andFilterWhere(['username' => '','age' => '22']);// AND age=22   其中 username=''被过滤
    
        // ORDER BY
        $query->orderBy(['id' => SORT_ASC, 'username' => SORT_DESC]);
        $query->orderBy('id ASC, username DESC');
        $query->orderBy('id ASC')->addOrderBy('username DESC');// 追加排序方式
        // GROUP BY
        $query->groupBy(['id', 'status']);
        $query->groupBy('id, status');
        $query->groupBy(['id', 'status'])->addGroupBy('age');// 追加分组方式
        // HAVING
        $query->having(['status' => 1]);
        $query->having(['status' => 1])->andHaving(['>', 'age', 30]);// 追加 HAVING 条件
        // LIMIT
        $query->limit(10)->offset(20);// LIMIT 20,10
    
    
        // indexBy 查询结果集的索引
        $query->indexBy('username');// 将把 username 字段的值作为数组的键名
        $query->indexBy(function($row){ return $row['id'].'-'.$row['username'];});// 将把 id-username 连在一起作为 键名
    
    
        // 查询方法
        $query->all();// 所有记录
        $query->one();// 结果集第一条记录
        $query->column();// 返回第一列的值
        $query->scalar();// 结果集的第一行第一列的标量值
        $query->exists();// 结果是否存在
        $query->count();// 记录条数
    
    
    
        // 其他拓展
        // 获取大量数据时 使用 batch 或 each 方法代替 all 方法
        //（前两者可以节省时间和内存，以前一直认为一次查询所有数据肯定比分批查询快，看来我错了，查询方式不一样处理的机制可能不一样）
        $query = (new Query())->from('user');
        foreach($query->batch() as $user){
            echo $user['username'];
            echo "<br/>";
        }
        foreach($query->each() as $user){
            echo $user['username'];
            echo "<br/>";
        }
    
    
        // batchInsert 批量插入数据
        $result = Yii::$app->db->createCommand()
            ->batchInsert('tableName',['username','age'],[
                    ['张三','21'],
                    ['李四','22'],
                    ['王五','25']
                ]
            )->execute();
    
        // insert 插入单条记录
        $result = Yii::$app->db->createCommand()
            ->insert("user", ['username' => '将军','age' => '36'])
            ->execute();