// 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();
YII2.0 查询构建器 的使用
最新推荐文章于 2023-04-23 13:10:45 发布