查询生成器（Query Builder）: 使用简单抽象层查询数据库

由  创建， 最后一次修改 2016-02-24 查询构建器查询构建器建立在?Database Access Objects?基础之上，可让你创建 程序化的、DBMS无关的SQL语句。相比于原生的SQL语句，查询构建器可以帮你 写出可读性更强的SQL相关的代码，并生成安全性更强的SQL语句。使用查询构建器通常包含以下两个步骤：创建一个 yii\db\Query 对象来代表一条 SELECT SQL 语句的不同子句（例如?SELECT,?FROM）。执行 yii\db\Query 的一个查询方法（例如：all()）从数据库当中检索数据。如下所示代码是查询构造器的一个典型用法：$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_nameLIMIT 10提示: 你平时更多的时候会使用 yii\db\Query 而不是 [yii\db\QueryBuilder]]。 当你调用其中一个查询方法时，后者将会被前者隐式的调用。yii\db\QueryBuilder主要负责将 DBMS 不相关的 yii\db\Query 对象转换成 DBMS 相关的 SQL 语句（例如， 以不同的方式引用表或字段名称）。创建查询为了创建一个 yii\db\Query 对象，你需要调用不同的查询构建方法来代表SQL语句的不同子句。 这些方法的名称集成了在SQL语句相应子句中使用的关键字。例如，为了指定 SQL 语句当中的?FROM?子句，你应该调用?from()?方法。所有的查询构建器方法返回的是查询对象本身， 也就是说，你可以把多个方法的调用串联起来。接下来，我们会对这些查询构建器方法进行一一讲解：yii\db\Query::select()yii\db\Query::select() 方法用来指定 SQL 语句当中的?SELECT?子句。 你可以像下面的例子一样使用一个数组或者字符串来定义需要查询的字段。当 SQL 语句 是由查询对象生成的时候，被查询的字段名称将会自动的被引号括起来。$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']);如果你在组建查询时没有调用 yii\db\Query::select() 方法，那么选择的将是?'*'?， 也即选取的是所有的字段。除了字段名称以外，你还可以选择数据库的表达式。当你使用到包含逗号的数据库表达式的时候， 你必须使用数组的格式，以避免自动的错误的引号添加。例如：$query->select(["CONCAT(first_name, ' ', last_name) AS full_name", 'email']); 从 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');你应该调用 yii\db\Query::distinct() 方法来去除重复行，如下所示：// SELECT DISTINCT `user_id` ...$query->select('user_id')->distinct();你可以调用 yii\db\Query::addSelect() 方法来选取附加字段，例如：$query->select(['id', 'username']) ->addSelect(['email']);yii\db\Query::from()yii\db\Query::from() 方法指定了 SQL 语句当中的?FROM?子句。例如：// 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');// SELECT * FROM (SELECT `id` FROM `user` WHERE status=1) u $query->from(['u' => $subQuery]);yii\db\Query::where()yii\db\Query::where() 方法定义了 SQL 语句当中的?WHERE?子句。 你可以使用如下三种格式来定义?WHERE?条件：字符串格式，例如：'status=1'哈希格式，例如：?['status' => 1, 'type' => 2]操作符格式，例如：['like', 'name', 'test']字符串格式在定义非常简单的查询条件的时候，字符串格式是最合适的。它看起来和原生 SQL 语句差不多。例如：$query->where('status=1');// 或者使用参数绑定来绑定动态参数值$query->where('status=:status', [':status' => $status]);千万不要像如下的例子一样直接在条件语句当中嵌入变量，特别是当这些变量来源于终端用户输入的时候， 因为这样我们的软件将很容易受到 SQL 注入的攻击。// 危险！千万别这样干，除非你非常的确定 $status 是一个整型数值。$query->where("status=$status");当使用参数绑定的时候，你可以调用 yii\db\Query::params() 或者 yii\db\Query::addParams() 方法 来分别绑定不同的参数。$query->where('status=:status') ->addParams([':status' => $status]);哈希格式哈希格式最适合用来指定多个?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