[moka同学笔记]Yii2 数据操作Query Builder 2

Query Builder

    $rows = (new \yii\db\Query())  
        ->select(['dyn_id', 'dyn_name'])  
        ->from('zs_dynasty')  
        ->where(['between','dyn_id', 1,30])  
        ->limit(10)  
        ->all();  
    print_r($rows);  

 

    use yii\db\Query;  
    $query = (new Query())  
        ->from('user')  
        ->orderBy('id');  

 

SELECT

 

    $query->select('*')->  
    select('dyn_id as id, dynasty.dyn_name')->  
    $query->select(['dyn_id as id', "CONCAT(dyn_name,'a')"])->  
    $query->select('user_id')->distinct()->  

 

FORM

    $query->from('user');  
    $query->from(['public.user u', 'public.post p']);  
    $query->from('public.user u, public.post p');  
    $query->from(['u' => 'public.user', 'p' => 'public.post']);  
    ----------  
    $subQuery = (new Query())->select('id')->from('user')->where('status=1');  
    // SELECT * FROM (SELECT `id` FROM `user` WHERE status=1) u   
    $query->from(['u' => $subQuery]);  

 

WHERE

 

    where('status=1')->  
    where('status=:status', [':status' => $status])->  
    where([  
        'status' => 10,  
        'type' => null,  
        'id' => [4, 8, 15],  
    ])->  
    -------  
    $userQuery = (new Query())->select('id')->from('user');  
    // ...WHERE `id` IN (SELECT `id` FROM `user`)  
    $query->...->where(['id' => $userQuery])->...  
    --------  
    ['and', 'id=1', 'id=2'] //id=1 AND id=2  
    ['and', 'type=1', ['or', 'id=1', 'id=2']] //type=1 AND (id=1 OR id=2)  
    ['between', 'id', 1, 10] //id BETWEEN 1 AND 10  
    ['not between', 'id', 1, 10] //not id BETWEEN 1 AND 10  
    ['in', 'id', [1, 2, 3]] //id IN (1, 2, 3)  
    ['not in', 'id', [1, 2, 3]] //not id IN (1, 2, 3)  
    ['like', 'name', 'tester'] //name LIKE '%tester%'  
    ['like', 'name', ['test', 'sample']] //name LIKE '%test%' AND name LIKE '%sample%'  
    ['not like', 'name', ['or', 'test', 'sample']] //not name LIKE '%test%' OR not name LIKE '%sample%'  
    ['exists','id', $userQuery] //EXISTS (sub-query) | not exists  
    ['>', 'age', 10] //age>10  

 

 

ADD WHERE

 

    $status = 10;  
    $search = 'yii';  
    $query->where(['status' => $status]);  
    if (!empty($search)) {  
        $query->andWhere(['like', 'title', $search]);  
    }  
    //WHERE (`status` = 10) AND (`title` LIKE '%yii%')  
    //andWhere() or orWhere()  

 

 

FILTER WHERE

 

$query->filterWhere([  
    'username' => $username,  
    'email' => $email,  
]);  
//如果email为空,则 WHERE username=:username 

 

ORDER BY

    $query->orderBy([  
        'id' => SORT_ASC,  
        'name' => SORT_DESC,  
    ]);  
    //orderBy , addOrderBy  

 

GROUP BY

    $query->groupBy('id, status');  
    $query->addGroupBy(['created_at', 'updated_at']);  

 

HAVING

    $query->having(['status' => $status]);  
    //having,andHaving,orHaving  

 

 

LIMIT OR OFFSET

 

    $query->limit(10);  
    $query->offset(10);  

 

 

JOIN

 

• innerJoin()
• leftJoin()
• rightJoin()

    $query->select(['user.name AS author', 'post.title as title'])  
        ->from('user')  
        ->leftJoin('post', 'post.user_id = user.id');  
    $query->join('FULL OUTER JOIN', 'post', 'post.user_id = user.id');  
    $query->leftJoin(['u' => $subQuery], 'u.id=author_id');  

 

 

UNION

    $query = new Query();  
    $query->select("id, category_id as type, name")->from('post')->limit(10);  
    $anotherQuery = new Query();  
    $anotherQuery->select('id, type, name')->from('user')->limit(10);  
    $query->union($anotherQuery);  

 

QUERY METHODS

• all() //所有行列
• one() //第一行
• column() //第一列
• scalar() //第一行第一列
• exists() //是否有结果存在
• count() //记录数量
• sum($q), average($q), max($q), min($q) //$q 为字段或表达式

    $count = (new \yii\db\Query())  
        ->from('user')  
        ->where(['last_name' => 'Smith'])  
        ->count();  
    //SELECT COUNT(*) FROM `user` WHERE `last_name`=:last_name  
      
      
    $command = (new \yii\db\Query())  
        ->select(['id', 'email'])  
        ->from('user')  
        ->where(['last_name' => 'Smith'])  
        ->limit(10)  
        ->createCommand();  
          
    // show the SQL statement  
    echo $command->sql;  
    // show the parameters to be bound  
    print_r($command->params);  
      
      
    // returns all rows of the query result  
    $rows = $command->queryAll();  

 

QUERY RESULTS

 

    use yii\db\Query;  
    $query = (new Query())  
        ->from('user')  
        ->indexBy('username');  
    foreach ($query->batch() as $users) {  
        // $users is indexed by the "username" column  
    }  
    foreach ($query->each() as $username => $user) {  
    }  

 

 

INDEXING

 

    use yii\db\Query;  
    $query = (new Query())  
        ->from('user')  
        ->orderBy('id');  
    foreach ($query->batch() as $users) {  
        // batch( $batchSize = 100, $db = null )  
        // 一个批次取100行  
    }  
    foreach ($query->each() as $user) {  
        // 一行一行取  
    }  

 

 

转载于：http://blog.csdn.net/hzqghost/article/details/44117081