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);
在CODE上查看代码片派生到我的代码片
use yii\db\Query;
$query = (new Query())
->from("user")
->orderBy("id");
SELECT
在CODE上查看代码片派生到我的代码片
$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
在CODE上查看代码片派生到我的代码片
$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
在CODE上查看代码片派生到我的代码片
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
在CODE上查看代码片派生到我的代码片
$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
在CODE上查看代码片派生到我的代码片
$query->filterWhere([
"username" => $username,
"email" => $email,
]);
//如果email为空,则 WHERE username=:username
ORDER BY
在CODE上查看代码片派生到我的代码片
$query->orderBy([
"id" => SORT_ASC,
"name" => SORT_DESC,
]);
//orderBy , addOrderBy
GROUP BY
在CODE上查看代码片派生到我的代码片
$query->groupBy("id, status");
$query->addGroupBy(["created_at", "updated_at"]);
HAVING
在CODE上查看代码片派生到我的代码片
$query->having(["status" => $status]);
//having,andHaving,orHaving
LIMIT OR OFFSET
在CODE上查看代码片派生到我的代码片
$query->limit(10);
$query->offset(10);
JOIN
innerJoin()
leftJoin()
rightJoin()
在CODE上查看代码片派生到我的代码片
$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
在CODE上查看代码片派生到我的代码片
$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 为字段或表达式
在CODE上查看代码片派生到我的代码片
$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
在CODE上查看代码片派生到我的代码片
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
在CODE上查看代码片派生到我的代码片
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) {
// 一行一行取
}
yii2数据操作日志|Yii2数据操作Query Builder详解
http://m.bbyears.com/banzhurengongzuo/116920.html
推荐访问: