Yii框架的Query Builder提供了以面向对象的方式编写SQL语句,允许开发者使用类方法和属性来指定SQL语句中的独立部分,并且将这些不同部分组装成一个可以通过调用如上一章节所述的DAO方法来执行的SQL语句。下面展示了使用Query Builder来构建SELECT SQL语句的典型方法:

当你需要组装分块组装SQL语句或者基于应用的特殊条件时最好使用Query Builder. 使用Query Builder的最大好处是:

  • 可以构建复杂的 SQL 语句.
  • 自动引用表名和列名以避免SQL保留字及特殊字符的冲突.
  • 如果可能的化也可以使用参数值进行参数绑定, 这样可以减少SQL注入攻击.
  • 支持一定程度上的数据库抽象,这样可以简化不同数据库平台间的数据库迁移.

使用Query Builder并不是强制性的. 实际上, 如果你的查询很简单, 直接使用SQL语句会更加简单快捷.
注意: Query builder不能修改已存在的指定SQL语句的查询,例如, 下面的代码是无效的:

换句话说, 不要修复原生SQL和SQL和query builder的使用.

1. 准备 Query Builder

Yii框架的Query Builder 以CDbCommand的形式支持, 这个数据库查询类在DAO这一章节中已经描述过.

在使用 Query Builder 之前,我们先要创建 CDbCommand 的实例,如下所示,

也就是说我们使用 Yii::app()->db 来获取 DB 连接, 然后调用 CDbConnection::createCommand() 来创建所需的command 实例.

注意我们并没有像在DAO中一样将整个SQL语句传入createCommand()方法中调用,而是没有传入任何参数. 这是因为我们接下来将会使用Query Builder 的方法来构建独立的SQL语句.

2. 构建数据检索查询

数据检索查询使用 SELECT 语句. query builder提供了一系列的方法来构建独立的SELECT语句.因为这些方法返回的都是 CDbCommand 实例, 所以我们可以使用方法链, 正如我们这一章节的开头所示那样.

  • select(): 指定查询的SELECT 部分
  • selectDistinct(): 指定查询的SELECT 部分并且开启DISTINCT标识符
  • from(): 指定查询的FROM部分
  • where(): 指定查询的WHERE部分
  • andWhere(): 使用AND操作符添加条件到查询的 WHERE 部分
  • orWhere(): 使用OR操作符添加条件到查询的 WHERE 部分
  • join(): 添加内联查询
  • leftJoin(): 添加左外联接
  • rightJoin():添加右外联接查询
  • crossJoin(): 添加交叉联接查询
  • naturalJoin(): 添加自然联接查询
  • group(): 指定查询的GROUP BY部分
  • having(): 指定查询的HAVING部分
  • order(): 指定查询的 ORDER BY 部分
  • limit(): 指定查询的LIMIT 部分
  • offset(): 指定查询的 OFFSET 部分
  • union(): 添加 UNION 查询

接下来我们会阐述如何使用这些构建方法. 简单起见, 我们假设使用的数据库是MySQL. 注意如果你使用的是其他DBMS, 示例中table/column/value 的引用可能会不一样.

select()

select() 方法指定查询的SELECT 部分. $columns 参数指定被查询的列, 可以是以逗号分割的字符串, 也可以是包含列名的数组.列名可以包含表前缀及列的别名. 这个方法会自动引用列名,除非该列是DB expression.

下面是示例:

selectDistinct()

selectDistinct() 和select()类似,不同之处是前者开启了 DISTINCT . 例如,selectDistinct('id, username') 会生成如下SQL:

SELECT DISTINCT `id`, `username`

from()

from() 方法指定了查询的 FROM 部分. $tables 参数指定了要查询的表,它既可以是一个以逗号分隔的字符串表名,也可以是包含表名的数组.表名可能包含作用域前缀 (e.g. public.tbl_user) 和表别名 (e.g.tbl_user u). 这个方法会自动引用表名,除非包含插入语句 (表名以子查询或者 DB expression的形式提供).

下面是一些示例:

where()

where() 方法指定了查询的 WHERE 部分.  $conditions 指定查询的条件,$params 指定查询条件中的参数.e $conditions参数可能是一个字符串也可能是数组如下所示:

里面的 operator 可以下列所示的任意一个:

  • and: 操作数通过 AND连接到一起. 例如, array('and', 'id=1', 'id=2') 将会生成 id=1 AND id=2. 如果一个操作数是数组, 那么它将会通过同样的规则转化为字符串. 例如, array('and', 'type=1', array('or', 'id=1', 'id=2')) 将会生成 type=1 AND (id=1 OR id=2). 这个方法不会做任何引用和转义.
  • or: 和 and 操作符类似,不同之处在于操作数使用OR连接到一起.
  • in: 第一个操作数operand1应该是一个列名或者DB expression, 第二个操作数operand2是一个表示该列或DB expression所在范围的数组, array('in', 'id', array(1,2,3)) 将会生成 id IN (1,2,3). 这个方法将会引用列名并转义取值范围中的值.
  • not in: 和in 操作符类似,不同之处是在生成条件语句中将 IN 换成 NOT IN .
  • like: 操作符operand 1应该是一个列或者 DB expression, 操作符operand 2是一个表示与列或者DB expression相似的字符串或数组. 例如,array('like', 'name', '%tester%') 将会生成 name LIKE '%tester%'. 当值的范围是数组形式的时候, 多个 LIKE 语句会通过AND 连接起来. 例如, array('like', 'name', array('%test%', '%sample%')) 将生成 name LIKE '%test%' AND name LIKE '%sample%'. 这个方法将会引用列名并转义取值范围中的值.
  • not like: 和 like 操作符类似
  • or like: 和 like 操作符类似,不同之处是多个LIKE之间使用 OR 连接.
  • or not like: 和 not like 操作符类似.

下面是使用 where的一些示例:

值得注意的是当操作符包含like的时候,我们必须在patterns明确指定通配符字符串 (such as % and _). 如果 patterns 来自用户输入,我们还要使用下面的代码来转义特殊字符以避免被作为通配符处理:

andWhere()

addWhere() 方法通过AND操作符添加额外的条件到查询的WHERE部分,这个方法和行为和where()几乎一样,不同之处是它追加条件而不是替换.

orWhere()

orWhere() 方法通过OR操作符添加额外的条件到查询的WHERE部分, 这个方法和行为和where()几乎一样,不同之处是它追加条件而不是替换.

order()

order() 方法指定查询的 ORDER BY 部分.  $columns 参数指定要排序的列, 可以是以逗号分隔的列和排序方向(ASCor DESC)字符串,或者包含列和排序方向的数组. 列名可能包含表前缀. 该方法会自动引用列名,除非该列名是一些插入语句 (如DB expression).

下面是一些示例:

limit() 和 offset()

limit() 和 offset() 方法指定查询的 LIMIT 和 OFFSET 部分. 注意一些 DBMS 可能不支持 LIMIT 和 OFFSET 语法. 在这种情况下,  Query Builder 将会重写整个 SQL 语句来模拟limit 和 offset的功能.

下面是一些示例:

join() 及其变种

join() 方法及其变种指定了使用 INNER JOINLEFT OUTER JOIN,RIGHT OUTER JOINCROSS JOIN, 或者 NATURAL JOIN联接其他表进行查询.  $table 参数指定了要联接的表名. 表名可以包含作用域前缀及别名. 这个方法将会引用表名(除非表名是DB expression或者子查询).  $conditions参数指定了联接条件,它的语法和where()类似.  $params 指定了整个查询的参数绑定.

注意不同于其他 query builder 方法, 每一次调用 join 方法都会追加到前一个上去.

下面是一些示例:

group()

group() 方法指定查询的 GROUP BY 部分. $columns 参数指定分组的列, 可以是以逗号分隔的列字符串, 也可以是列数组。列名可能包含表前缀. 这个方法会自动引用列名,除非列是插入语句(which means the column is given as a DB expression).

下面是一些示例:

having()

having() 方法指定了查询的 HAVING 部分. 它的使用 where()类似.

下面是一些示例:

union()

union() 方法指定了查询的 UNION 部分. 它使用UNION操作符追加 $sql 到已存在的 SQL. 调用 union() 多次将会追加多个 SQLs 到已存在的SQL上.

下面是一些示例:

执行查询

在调用上述的查询构建方法构建一个查询之后, 我们可以调用如上一章节DAO中所述的DAO方法来执行查询. 例如, 我们可以调用 CDbCommand::queryRow() 来获取一行的结果, 或者 CDbCommand::queryAll() 来一次获取所有结果. 例子:

检索SQL

除了执行 Query Builder构建的查询之外, 我们还可以检索相应SQL语句的结果. 这可以通过调用 CDbCommand::getText()方法来解决.

如果有多个参数绑定到一个查询上, 那么它们可以通过 CDbCommand::paramsproperty来获取结果.

构建查询的可选语法

有时候, 使用方法链来构建查询并不是一个明智的选择.  Yii框架的 Query Builder允许一个查询使用简单对象属性赋值的方式来构建 . 特别地,对每一个查询构建方法,有一个同名的相应属性. 赋值到该属性等价于调用相应的方法. 例如,下面的两个语句是等价的($command 代表 CDbCommand 对象):

此外, CDbConnection::createCommand()方法可以传入一个数组参数. 数组中的键值对会被用来初始化已创建的CDbCommand实例的属性.这意味着,我们可以使用下面的代码来构建查询:

构建多个查询

一个DbCommand 实例可以被多次复用来构建多个查询. 在构建一个新的查询之前一定要调用 CDbCommand::reset() 方法来清除上一个查询. 例如:

3. 构建数据操纵(增、删、改)SQL语句

数据操纵查询指的是SQL语句中数据库表格记录的插入、更新和删除. 与之相应的,查询构建器提供了insert,update和delete方法. 不同于 SELECT 查询方法,每一个数据操纵查询方法都会构建一个完整的SQL语句,并且立即执行.

  • insert(): 在数据表中插入一行
  • update(): 更新数据表数据
  • delete(): 从数据表删除数据

下面我们阐述数据操纵查询方法.

insert()

insert() 方法构建并执行一个 INSERT SQL 语句. $table参数指定要插入的数据表,  $columns是一个键值对数组,指定了插入的列及对应的值. 这个方法会引用表名并且使用绑定参数.

Below is an example:

update()

update() 方法构建并执行一个UPDATE SQL语句. $table参数指定要更新的表$columns是一个键值对数组,该数组指定了要更新的列及其对应的值;$conditions 和 $params 和 where()中的类似, 指定了UPDATE语句中的 WHERE部分. 这个方法会自动引用相应的列名并且使用参数绑定.

Below is an example:

delete()

delete() 方法构建并执行一个DELETE SQL语句. $table参数指定要删除的表$conditions 和 $params 和 where()中的类似, 指定了DELETE语句中的 WHERE部分. 这个方法会自动引用相应的列名.

下面是示例:

4. 构建数据表操作SQL语句

除了正常的数据取回和操纵查询,  query builder 还提供了一系列的方法来构建和执行操作数据库层面的SQL操作. 特殊地,它支持下列查询:

  • createTable(): 创建表
  • renameTable(): 重命名表
  • dropTable(): 删除表
  • truncateTable(): 清空表
  • addColumn(): 新增表的列
  • renameColumn(): 重命名表的列
  • alterColumn(): 修改表的列
  • addForeignKey(): 添加外键(版本1.1.6起生效)
  • dropForeignKey(): 删除外键 (版本1.1.6起生效)
  • dropColumn(): 删除表列
  • createIndex(): 创建索引
  • dropIndex(): 删除索引

Info: 尽管实际的SQL语句在不同的DBMS中操作数据库的方法不尽相同,但是查询构建器试图提供一个统一接口来构建查询。这极大简化了数据库迁移时带来的麻烦。

抽象数据类型

query builder 介绍了一系列抽象数据类型用来定义数据表的列. 不同于物理数据类型用来指定特殊的DBMS而且在不同的DBMS中不一样,这里的抽象数据类型独立于DBMS. 当抽象数据类型用于定义表的列时,查询构建器将会将其转化为相应的物理数据类型.

下面的抽象数据类型都被 query builder 所支持.

  • pk: 一个通用的主键类型, 在MySQL中将会被转化为 int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY;
  • string: 字符串类型, 在MySQL中将会被转化为 varchar(255);
  • text: 文本类型 (长字符串), 在MySQL中将会被抓化为 text;
  • integer: 整型, 在MySQL中将会被转化为 int(11);
  • float: 浮点数类型, 在MySQL中将会被转化为will be converted into float;
  • decimal: 小数类型, 在MySQL中将会被转化为 decimal;
  • datetime: 日期时间类型, 在MySQL中将会被转化为 datetime ;
  • timestamp: 时间戳类型,在MySQL中将会被转化为  timestamp ;
  • time: 时间类型, 在MySQL中将会被转化为 time;
  • date: 日期类型, 在MySQL中将会被转化为 date;
  • binary: 二进制数据类型, 在MySQL中将会被转化为 blob;
  • boolean: 布尔类型, 在MySQL中将会被转化为tinyint(1);
  • money: 金钱/货币类型, 在MySQL中将会被转化为 decimal(19,4). 这个类型从Yii版本1.1.8起生效.

createTable()

createTable() 方法构建和执行创建数据表的 SQL语句. $table参数指定了要创建的表名.  $columns 参数指定了新表中的列. 它们必须定义为 name-definition 对 (e.g. 'username'=>'string').  $options 参数指定任意额外的应该追加到已生成的SQL语句的SQL 片段 .  query builder 将会引用表名和合适的列名.

当指定一个列定义时,可以使用上述所叙的抽象数据类型. query builder 会基于当前使用的DBMS转化抽象数据类型为相应的物理数据类型。

一个列定可以包含非抽象数据类型或者指定. 它们将会毫无改变的生成 SQL 语句。例如, point 不是一个抽象数据类型, 如果用在了列定义中, 它将会出现在结果SQL语句中,同时string NOT NULL 将会转化为 varchar(255) NOT NULL.

下面的例子展示了如何创建新表:

renameTable()

renameTable() 方法构建并执行重命名表的SQL语句. $table参数指定了要重命名的表,$newName参数指定了表的新名称. 查询构建器将会引用合适的表名.

下面的示例展示了如何重命名一个表:

dropTable()

dropTable() 方法构建并执行删除表的SQL语句.  $table 参数指定要删除的表名. query builder将会引用合适的表名.

下面是展示如何删除表的示例:

truncateTable()

truncateTable() 方法会构建并执行清空表的SQL语句. $table参数指定要清空的表名.query builder将会引用合适的表名.

下面是展示如何清空表的示例:

addColumn()

addColumn() 方法构建并执行 SQL语句来添加一个新的列,$table参数指定新增列的表名$column 参数指定新增列的名称. $type 指定了新增列的定义. 列定义可能包含抽象数据类型, 这在 “createTable”里已经说明过. query builder 将会引用表名和合适的列名.

下面展示的是如何为表新增列的例子:

dropColumn()

dropColumn() 方法构建并执行删除表的列的SQL语句.  $table参数指定了要删除列的表名. $column 参数指定了要删除的列名. query builder 将会引用表名和合适的列名.

下面是如何删除表列的例子:

renameColumn()

renameColumn() 方法构建并执行重命名表的列的SQL语句. $table参数指定了要重命名列的表名. $name参数指定了旧的列名. $newName 参数指定了新的列名. query builder 将会引用表名和合适的列名.

下面展示了如何重命名一个表的列:

alterColumn()

alterColumn() 方法构建并执行修改表列的 SQL 语句 .  $table参数指定了将要被修改列的表名. $column 参数指定了将要被修改的列名.$type 指定了列的新定义,列定义可能包含抽象数据类型. query builder将会引用表名和合适的列名.

下面展示了如何修改一个表的列:

addForeignKey()

addForeignKey() 方法构建并执行为一个表新增外键约束的SQL语句. $name 参数指定外键名称. $table 和 $columns 参数指定表名和添加外键的列名. 如果有多个列, 它们必须由逗号分隔. $refTable和 $refColumns 参数指定了外键指向的表名和对应的列名.$delete 和 $update 参数分别指定SQL语句中的ON DELETE 和 ON UPDATE 操作. 大多数 DBMS 支持这些操作:RESTRICTCASCADENO ACTIONSET DEFAULTSET NULL. query builder 将会引用表名,索引名和列名

下面的例子展示了如何该表添加外键约束,

dropForeignKey()

dropForeignKey() 方法构建和执行删除外键约束的 SQL 语句. $name 参数指定了要删除的外键名. $table 参数指定了外键所在的表名.  query builder 将会引用表名和合适的约束名.

下面的例子展示了如何删除外键约束:

createIndex()

createIndex() 方法构建并执行创建索引的 SQL语句. $name 参数指定了要创建的索引名称. $table 参数指定了创建索引的表名. $column 参数指定了被索引的列名. $unique参数指定了是否要创建unique索引. 如果索引包含多个列,按么必须通过逗号分隔.  query builder 将会引用表名, 索引名和列名.

下面的例子展示了如何创建索引:

dropIndex()

dropIndex() 方法构建并执行了删除索引的 SQL 语句. $name 参数指定要删除的索引名. $table 参数指定了索引所在的表名. query builder 将会引用表名和合适的索引名.

下面的例子展示了如何删除索引: