Yii2 数据操作Query Builder

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

1. 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) { // 一行一行取 }

G

M

T

Text-to-speech function is limited to 200 characters