一文告诉你（yii框架是如何使用SQL语句的）

我们都知道，php是互联网目前最为流行的开发语言之一，而yii框架是在互联网中常用框架。而互联网开发过程中需要进行大量的数据处理，最常用的数据（Database）一般是通过SQL语句来处理的。

不过，如果每次进行处理的时候都需要将SQL语言一句一句的写出来的话，那不仅浪费时间，还容易出错，同时也会造成代码臃肿。所以yii框架中将SQL的where、from、left join、select等语句封装到了查询生成器中。今天我们一起来学习下吧（下列数据来自于yii框架文档中的查询生成器部分，有修改）

查询构建器建立在 Database Access Objects 基础之上，可让你创建 程序化的、DBMS无关的SQL语句。相比于原生的SQL语句，查询构建器可以帮你 写出可读性更强的SQL相关的代码，并生成安全性更强的SQL语句。

使用查询构建器通常包含以下两个步骤：

创建一个 yii\db\Query 对象来代表一条 SELECT SQL 语句的不同子句（例如 SELECT, FROM执行 yii\db\Query 的一个查询方法（例如：all()）从数据库当中检索数据。如下所示代码是查询构造器的一个典型用法（每一个和SQL相似的关键字都代表相应的SQL语句）：$rows = (new \yii\db\Query()) ->select(["id", "email"]) ->from("user") ->where(["last_name" => "Smith"]) ->limit(10) ->all();

上面的代码将会生成并执行如下的SQL语句，其中 :last_name

参数绑定了 字符串 "Smith"。

SELECT`id`, `email`FROM`user`WHERE`last_name` = :last_nameLIMIT10

提示： 你平时更多的时候会使用 yii\db\Query 而不是 yii\db\QueryBuilder。 当你调用其中一个查询方法时，后者将会被前者隐式的调用。yii\db\QueryBuilder主要负责将 DBMS 不相关的 yii\db\Query 对象转换成 DBMS 相关的 SQL 语句（例如， 以不同的方式引用表或字段名称）。

为了创建一个 yii\db\Query 对象，你需要调用不同的查询构建方法来代表SQL语句的不同子句。 这些方法的名称集成了在SQL语句相应子句中使用的关键字。例如，为了指定 SQL 语句当中的 子句，你应该调用 from()方法。所有的查询构建器方法返回的是查询对象本身， 也就是说，你可以把多个方法的调用串联起来。接下来，我们会对这些查询构建器方法进行一一讲解：

select() 方法用来指定 SQL 语句当中的子句。 你可以像下面的例子一样使用一个数组或者字符串来定义需要查询的字段。当 SQL 语句 是由查询对象生成的时候，被查询的字段名称将会自动的被引号括起来（注意：在php语言中，变量名前面要添加“$”符号）。

$query->select(["id", "email"]);// 等同于：$query->select("id, email");

就像写原生 SQL 语句一样，被选取的字段可以包含表前缀，以及/或者字段别名。 例如：

$query->select(["user.id AS user_id", "email"]);// 等同于：$query->select("user.id AS user_id, email");

如果使用数组格式来指定字段，你可以使用数组的键值来表示字段的别名。 例如，上面的代码可以被重写为如下形式：

$query->select(["user_id" => "user.id", "email"]);

如果你在组建查询时没有调用 select() 方法，那么选择的将是 "*"， 也即选取的是所有的字段。

除了字段名称以外，你还可以选择数据库的表达式。当你使用到包含逗号的数据库表达式的时候， 你必须使用数组的格式，以避免自动的错误的引号添加。例如：

$query->select(["CONCAT(first_name, " ", last_name) AS full_name", "email"]);

与所有涉及原始 SQL 的地方一样，当在 select 中编写 DB 表达式时，可以对表名和列名使用 与 DBMS 无关的引用语法。从 2.0.1 的版本开始你就可以使用子查询了。在定义每一个子查询的时候， 你应该使用 yii\db\Query 对象。例如：

$subQuery = (new Query())->select("COUNT(*)")->from("user");// SELECT `id`, (SELECT COUNT(*) FROM `user`) AS `count` FROM `post`$query = (new Query())->select(["id", "count" => $subQuery])->from("post");

你应该调用 distinct() 方法来去除重复行，如下所示：

SELECT DISTINCT `user_id` ...$query->select("user_id")->distinct();

你可以调用 addSelect() 方法来选取附加字段，例如：

$query->select(["id", "username"]) ->addSelect(["email"]);

from() 方法指定了 SQL 语句当中的 子句。例如：

SELECT * FROM `user`$query->from("user");

你可以通过字符串或者数组的形式来定义被查询的表名称。就像你写原生的 SQL 语句一样， 表名称里面可包含数据库前缀，以及/或者表别名。例如：

$query->from(["public.user u", "public.post p"]);// 等同于：$query->from("public.user u, public.post p");

如果你使用的是数组的格式，那么你同样可以用数组的键值来定义表别名，如下所示：

$query->from(["u" => "public.user", "p" => "public.post"]);

除了表名以外，你还可以从子查询中再次查询，这里的子查询是由 yii\db\Query 创建的对象。 例如：

$subQuery = (new Query())->select("id")->from("user")->where("status=1");

上述语句的意思可以用SQL语句表示为： SELECT * FROM (SELECT `id` FROM `user` WHERE status=1) u $query->from(["u" => $subQuery]);

where() 方法定义了 SQL 语句当中的 WHERE子句。 你可以使用如下四种格式来定义 条件：

字符串格式，例如："status=1"哈希格式，例如： ["status" => 1, "type" => 2]操作符格式，例如：["like", "name", "test"]对象格式，例如：new LikeCondition("name", "LIKE", "test")

在定义非常简单的查询条件的时候，字符串格式是最合适的。 它看起来和原生 SQL 语句差不多。例如：

$query->where("status=1");// 或使用参数绑定来绑定动态参数值$query->where("status=:status", [":status" => $status]);// 原生 SQL 在日期字段上使用 MySQL YEAR() 函数$query->where("YEAR(somedate) = 2015");

千万不要像如下的例子一样直接在条件语句当中嵌入变量，特别是当这些变量来源于终端用户输入的时候， 因为这样我们的软件将很容易受到 SQL 注入的攻击。

$query->where("status=$status");（千万别这样干，除非你非常的确定 $status 是一个整型数值。）当使用 参数绑定的时候，你可以调用 params() 或者 addParams() 方法 来分别绑定不同的参数。

$query->where("status=:status") ->addParams([":status" => $status]);

与涉及原生 SQL 的所有地方一样，在以字符串格式写入条件时，可以对表名和列名使用 与 DBMS 无关的引用语法。

哈希格式最适合用来指定多个 AND串联起来的简单的"等于断言"子条件。 它是以数组的形式来书写的，数组的键表示字段的名称，而数组的值则表示 这个字段需要匹配的值。例如：

WHERE (`status` = 10) AND (`type` IS NULL) AND (`id` IN (4, 8, 15))$query->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]);

使用哈希格式，Yii 在内部对相应的值进行参数绑定，与 字符串格式 相比， 此处你不需要手动添加参数绑定。但请注意，Yii 不会帮你转义列名，所以如果你 从用户端获得的变量作为列名而没有进行任何额外的检查，对于 SQL 注入攻击， 你的程序将变得很脆弱。为了保证应用程序的安全，请不要将变量用作列名 或者你必须用白名单过滤变量。如果你实在需要从用户获取列名，请阅读 过滤数据 章节。例如，以下代码易受攻击：$column = $request->get("column");$value = $request->get("value");$query->where([$column => $value]);// $value 是安全的，但是 $column 名不会被转义处理！

操作符格式允许你指定类程序风格的任意条件语句，如下所示：

[操作符, 操作数1, 操作数2, ...]

其中每个操作数可以是字符串格式、哈希格式或者嵌套的操作符格式， 而操作符可以是如下列表中的一个：

and：操作数会被 关键字串联起来。例如，

["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)。 这个方法不会自动加引号或者转义。

or：用法和 操作符类似，这里就不再赘述。

not：只需要操作数 1，它将包含在 NOT()中。例如，

["not"，"id = 1"]将生成

NOT (id=1)

。操作数 1 也可以是个描述多个表达式的数组。例如

["not", ["status" => "draft", "name" => "example"]]

NOT ((status="draft") AND (name="example"))

between：第一个操作数为字段名称，第二个和第三个操作数代表的是这个字段 的取值范围。例如，["between", "id", 1, 10]id BETWEEN 1 AND 10。 如果你需要建立一个值在两列之间的查询条件（比如 11 BETWEEN min_id AND max_id）， 你应该使用 BetweenColumnsCondition。 请参阅 条件-对象格式 一章以了解有关条件的对象定义的更多信息。not between：与 类似，除了 BETWEEN 被 NOT BETWEEN 替换 在生成条件时。in：第一个操作数应为字段名称或者 DB 表达式。第二个操作符既可以是一个数组， 也可以是一个 Query 对象。它会转换成IN 条件语句。如果第二个操作数是一个 数组，那么它代表的是字段或 DB 表达式的取值范围。如果第二个操作数是 对象，那么这个子查询的结果集将会作为第一个操作符的字段或者 DB 表达式的取值范围。 例如， ["in", "id", [1, 2, 3]]id IN (1, 2, 3)。 该方法将正确地为字段名加引号以及为取值范围转义。 操作符还支持组合字段，此时， 操作数1应该是一个字段名数组，而操作数2应该是一个数组或者 对象， 代表这些字段的取值范围。not inlike：第一个操作数应为一个字段名称或 DB 表达式， 第二个操作数可以使字符串或数组， 代表第一个操作数需要模糊查询的值。比如，["like", "name", "tester"] 会生成 name LIKE "%tester%"。 如果范围值是一个数组，那么将会生成用 串联起来的 多个 语句。例如，["like", "name", ["test", "sample"]]name LIKE "%test%" AND name LIKE "%sample%"。 你也可以提供第三个可选的操作数来指定应该如何转义数值当中的特殊字符。 该操作数是一个从需要被转义的特殊字符到转义副本的数组映射。 如果没有提供这个操作数，将会使用默认的转义映射。如果需要禁用转义的功能， 只需要将参数设置为 false 或者传入一个空数组即可。需要注意的是， 当使用转义映射（又或者没有提供第三个操作数的时候），第二个操作数的值的前后 将会被加上百分号。注意： 当使用 PostgreSQL 的时候你还可以使用 ilike， 该方法对大小写不敏感。

or like 操作符类似，区别在于当第二个操作数为数组时， 会使用 OR 来串联多个 LIKE 条件语句。not like 操作符类似，区别在于会使用 NOT LIKE 来生成条件语句。or not likeexists：需要一个操作数，该操作数必须是代表子查询 yii\db\Query 的一个实例， 它将会构建一个 EXISTS (sub-query) 表达式。not exists 操作符类似，它将创建一个 NOT EXISTS (sub-query)>，<= 或者其他包含两个操作数的合法 DB 操作符：第一个操作数必须为字段的名称， 而第二个操作数则应为一个值。例如，[">", "age", 10]age>10使用操作符格式，Yii 在内部对相应的值进行参数绑定，因此与 字符串格式 相比， 此处你不需要手动添加参数。但请注意，Yii 不会帮你转义列名，所以如果你 从用户端获得的变量作为列名而没有进行任何额外的检查，对于 SQL 注入攻击， 你的程序将变得很脆弱。为了保证应用程序的安全，请不要将变量用作列名 或者你必须用白名单过滤变量。如果你实在需要从用户获取列名，请阅读 过滤数据 章节。例如，以下代码易受攻击：

$column = $request->get("column");$value = $request->get("value);$query->where(["=", $column, $value]);// $value 是安全的，但是 $column 名不会被转义处理！

对象格式自 2.0.14 开始提供，是定义条件的最强大和最复杂的方法。 如果你要在查询构建器上构建自己的抽象方法或者如果你要实现自己的复杂条件， 你需要它（Object Form）

条件类的实例是不可变的。他们唯一的用途是存储条件数据 并为条件构建器提供 getters 属性。条件构建器是一个包含转换逻辑的类， 它将存储的条件数据转换为 SQL 表达式。

在内部，上面描述的格式在构建 SQL 之前被隐式转换为对象格式， 因此可以在单一条件语句下组合适合的格式：

$query->andWhere(new OrCondition([ new InCondition("type", "in", $types), ["like", "name", "%good%"], "disabled=false"]))

操作符格式与对象格式的对应关系是在 QueryBuilder::conditionClasses 属性中定义， 这里列举一些比较有代表性的映射关系：

-> yii\db\conditions\ConjunctionConditionNOTyii\db\conditions\NotConditionNOT INyii\db\conditions\InConditionyii\db\conditions\BetweenCondition等等

使用对象格式可以定义自己的条件集，并且可以更容易维护别人定义的条件集。（注：这里是说对象比数组更可靠） 更多细节请参考 Adding Custom Conditions and Expressions 章节。

你可以使用 andWhere() 或者 orWhere() 在原有条件的基础上 附加额外的条件。你可以多次调用这些方法来分别追加不同的条件。 例如，

$status = 10;$search = "yii";$query->where(["status" => $status]);if (!empty($search)) { $query->andWhere(["like", "title", $search]);}

如果 $search

不为空，那么将会生成如下 SQL 语句：

WHERE (`status` = 10) AND (`title` LIKE "%yii%")

当条件来自于用户的输入时，你通常需要忽略用户输入的空值。 例如，在一个可以通过用户名或者邮箱搜索的表单当中，用户名或者邮箱 输入框没有输入任何东西，这种情况下你想要忽略掉对应的搜索条件， 那么你就可以使用 filterWhere() 方法来实现这个目的：

$username 和 $email 来自于用户的输入$query->filterWhere([ "username" => $username, "email" => $email, ]);

filterWhere() 和 where() 唯一的不同就在于，前者 将忽略在条件当中的hash format的空值。所以如果

$email为空而

$username

不为空，那么上面的代码最终将生产如下 SQL

...WHERE username=:username

提示： 当一个值为 null、空数组、空字符串或者一个只包含空格的字符串时，那么它将被判定为空值。

类似于 andWhere() 和 orWhere()， 你可以使用 andFilterWhere() 和 orFilterWhere() 方法 来追加额外的过滤条件。

此外，yii\db\Query::andFilterCompare() 可以根据值中的内容智能地确定运算符：

$query->andFilterCompare("name", "John Doe");$query->andFilterCompare("rating", ">9");$query->andFilterCompare("value", "<=100");

您还可以显式指定运算符：

$query->andFilterCompare("name", "Doe", "like");

本次就先学习到这里，喜欢就点个赞吧！