yii2数据操作日志|Yii2数据操作Query Builder详解

时间:2020-12-17  来源:班主任工作日志  阅读:

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

推荐访问:
相关阅读 猜你喜欢
本类排行 本类最新