Yii查询生成器(Query Builder)用法实例教程

本文为yii官网英文文档的翻译版本，主要介绍了yii查询生成器(query builder)的用法。分享给大家供大家参考之用。具体如下：

首先，yii的查询生成器提供了用面向对象的方式写sql语句。它允许开发人员使用类的方法和属性来指定一个sql语句的各个部分。然后，组装成一个有效的sql语句，可以通过调用dao数据访问对象的描述方法为进一步执行。以下显示了一个典型的使用查询生成器建立一个select语句：

$user = yii::app()->db->createcommand()
  ->select('id, username, profile')
  ->from('tbl_user u')
  ->join('tbl_profile p', 'u.id=p.user_id')
  ->where('id=:id', array(':id'=>$id))
  ->queryrow();

当你在应用程序中需要组装sql语句的程序，或基于一些条件逻辑时，最好使用查询生成器。使用查询生成器的好处主要包括：

①.它可以建立复杂的sql语句编程。

②.它会自引用表名和列名防止sql保留字和特殊字符的冲突。

③.它还可以引用参数值和使用参数绑定，这有助于减少sql注入攻击的风险。

④.它提供了一定程度的数据库抽象，简化了迁移到不同的数据库平台的成本。

它不强制使用查询生成器。事实上，如果你的查询是简单的，它是更容易和更快的直接写sql语句。

注：查询生成器不能用于修改现有的查询指定为sql语句。例如，下面的代码将不能工作：

$command = yii::app()->db->createcommand('select * from tbl_user');
// the following line will not append where clause to the above sql
$command->where('id=:id', array(':id'=>$id));

换句话说，不要混合使用普通的sql和查询生成器。

1. 制备查询生成器（preparing query builder）

yii的查询生成器是从 cdbcommand 提供主要数据库查询类，描述数据访问对象。

开始使用查询生成器，我们创造了 cdbcommand 的一个新实例，

$command = yii::app()->db->createcommand();

我们使用 yii::app()->db 来获得数据库连接，然后调用 cdbconnection::createcommand() 创建所需的命令实例。

请注意，将整个sql语句放入 createcommand() ，叫做数据访问对象，我们需呀把设置它为空。这是因为我们将在下面的解释中使用查询生成器添加sql语句的各个部分的方法。

2. 建立数据检索查询（building data retrieval queries）

数据检索查询是指选择sql语句。查询生成器提供了一套方法来建立一个select语句的各个部分。因为所有这些方法返回 cdbcommand 的实例，我们可以使用方法链调用他们，如图所示，在本节开头的例子。

select():             指定查询的选择部分                       specifies the select part of the query
selectdistinct():  指定查询不重复的选择部分               specifies the select part of the query and turns on the distinct flag
from():              指定查询的from                           specifies the from part of the query
where():            指定查询的where                        specifies the where part of the query
andwhere():      用and的方式添加到where的条件中  appends condition to the where part of the query with and operator
orwhere():        用or的方式添加到where的条件中    appends condition to the where part of the query with or operator
join():               添加一个内部联接的查询片段            appends an inner join query fragment
leftjoin():          追加一个左外连接查询片段               appends a left outer join query fragment
rightjoin():        追加一个右外部联接查询片段            appends a right outer join query fragment
crossjoin():       追加一个交叉连接查询片段               appends a cross join query fragment
naturaljoin():     追加一个自然连接查询片段               appends a natural join query fragment
group():            指定查询的group by                   specifies the group by part of the query
having():           指定查询的having                       specifies the having part of the query
order():            指定查询的order by                    specifies the order by part of the query
limit():              指定查询的limit                           specifies the limit part of the query
offset():           指定查询的offset                        specifies the offset part of the query
union():           添加查询的union                          appends a union query fragment

在下面，我们将解释如何使用这些查询生成器方法。为简单起见，我们假设底层数据库是mysql。注意：如果你使用的是其他数据库，表/列/值引用的例子可能是不同的。

select()

function select($columns='*')

该select()方法指定一个查询的选择部分。$columns参数指定要选择的列，它可以是一个字符串，用逗号分隔列，或者一个数组的列名称。列的名称可以包含表的前缀和 / 或列别名。该方法将自动引用列名，除非一列包含一些括号（这意味着这个列是一个db的表达式）。

下面是一些例子：

// select *
select()
// select `id`, `username`
select('id, username')
// select `tbl_user`.`id`, `username` as `name`
select('tbl_user.id, username as name')
// select `id`, `username`
select(array('id', 'username'))
// select `id`, count(*) as num
select(array('id', 'count(*) as num'))

selectdistinct()

function selectdistinct($columns)

selectdistinct()方法类似于select()，除了它打开了 distinct 的标志。例如，selectdistinct（'id，用户名”）会产生以下sql：

select distinct `id`, `username`

from()

function from($tables)

from()方法指定来了一个查询的from部分。 $tables 参数指定表的选择。这可以是一个字符串，用逗号分隔的表的名称，或表名数组。表的名称可以包含架构前缀（例如公共。tbl_user）和/或表的别名（e.g.tbl_user u）。该方法将自动引用表的名称，除非它包含一些括号（即表是一个给定的子查询或db的表达式）。

下面是一些例子：

// from `tbl_user`
from('tbl_user')
// from `tbl_user` `u`, `public`.`tbl_profile` `p`
from('tbl_user u, public.tbl_profile p')
// from `tbl_user`, `tbl_profile`
from(array('tbl_user', 'tbl_profile'))
// from `tbl_user`, (select * from tbl_profile) p
from(array('tbl_user', '(select * from tbl_profile) p'))

where()

function where($conditions, $params=array())

where()方法指定查询的where。$conditions 参数指定查询条件的同时，$params 指定参数绑定到整个查询。$conditions参数可以是一个字符串（例如id = 1）或一个数组中的格式：

array(operator, operand1, operand2, ...)
operator 可以是以下的任何一个：

①.and: operands 应该使用 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 操作，除了operands 是使用 or 连接的。

③.in: operand 1 应是一个列或 db 表达式，而 operand 2 是代表值的范围的数组，列或 db 表达式应在这个数组的范围内。例如，array('in', 'id', array(1,2,3)) 将生成 id in (1,2,3)。

④.not in: 类似 in 操作，除了用 not in 代替 in  去生成sql。

⑤.like: operand 1 应是一个列或 db 表达式，而 operand 2 是代表值的范围的数组，列或 db 表达式应在这个数组的范围内。例如，array('like', 'name', '%tester%') 会生成 name like '%tester%'。当规定值的范围为一个数组时，多个 like 生成sql时会用 and 连接。例如，array('like', 'name', array('%test%', '%sample%')) 会生成 name like '%test%' and name like '%sample%'。

⑥.not like: 类似 like 的操作，除了用 not like 代替 like  去生成sql。

⑦.or like: 类似 like 的操作，除了多个 like 生成 sql 时用or连接。

⑧.or not like:  类似 not like 的操作，除了多个 like 生成 sql 时用or连接。

下面是一些例子，使用的地方：

// where id=1 or id=2
where('id=1 or id=2')
// where id=:id1 or id=:id2
where('id=:id1 or id=:id2', array(':id1'=>1, ':id2'=>2))
// where id=1 or id=2
where(array('or', 'id=1', 'id=2'))
// where id=1 and (type=2 or type=3)
where(array('and', 'id=1', array('or', 'type=2', 'type=3')))
// where `id` in (1, 2)
where(array('in', 'id', array(1, 2))
// where `id` not in (1, 2)
where(array('not in', 'id', array(1,2)))
// where `name` like '%qiang%'
where(array('like', 'name', '%qiang%'))
// where `name` like '%qiang' and `name` like '%xue'
where(array('like', 'name', array('%qiang', '%xue')))
// where `name` like '%qiang' or `name` like '%xue'
where(array('or like', 'name', array('%qiang', '%xue')))
// where `name` not like '%qiang%'
where(array('not like', 'name', '%qiang%'))
// where `name` not like '%qiang%' or `name` not like '%xue%'
where(array('or not like', 'name', array('%qiang%', '%xue%')))

请注意，当操作者含有like时，我们必须明确指定的通配符（如%和_）的模式。如果参数是从用户的输入，我们也应该使用下面的代码转义特殊字符，以防止他们被当作通配符：

$keyword=$_get['q'];
// escape % and _ characters
$keyword=strtr($keyword, array('%'=>'\%', '_'=>'\_'));
$command->where(array('like', 'title', '%'.$keyword.'%'));

andwhere()

function andwhere($conditions, $params=array())

用and 的方式添加到where的条件中。此方法的行为几乎是 where() 相同，除了它只是添加条件不能取代它。在 where() 文档中有该方法参数的详细信息。

orwhere()

function orwhere($conditions, $params=array())

用 or 的方式添加到where的条件中。此方法的行为几乎是 where() 相同，除了它只是添加条件不能取代它。在 where() 文档中有该方法参数的详细信息。

order()

function order($columns)

order() 方法指定查询的order by部分。$columns 参数指定列进行排序，这可以是一个包含用逗号分隔列和order的方向（asc 或 desc）的字符串，或者一个列和order的方向的数组。列名称可以包含表前缀。该方法将自动引用列名，除非一列包含一些括号（即给出一个 db 表达式）。

下面是一些例子：

// order by `name`, `id` desc
order('name, id desc')
// order by `tbl_profile`.`name`, `id` desc
order(array('tbl_profile.name', 'id desc'))

limit() and offset()

function limit($limit, $offset=null)
function offset($offset)

limit()和offset()方法指定查询的 limit 和 offset 部分。请注意，某些dbms不支持 limit 和 offset 语法。在这种情况下，查询生成器将改写整个sql语句来模拟 limit 和 offset 的功能。

下面是一些例子：

// limit 10  取前10条
limit(10)
// limit 10 offset 20  取到第21~30条
limit(10, 20)
// offset 20  去掉前20条后剩下的数据
offset(20)

join() and its variants

function join($table, $conditions, $params=array())
function leftjoin($table, $conditions, $params=array())
function rightjoin($table, $conditions, $params=array())
function crossjoin($table)
function naturaljoin($table)

join()方法及其变种指定如何与其他表连接，使用内部联接，左外连接，右外部联接，交叉连接，或自然连接。 $table 参数指定要加入哪个表。表名可以包含数据库的前缀和 / 或别名。该方法将引用表名，除非它包含一个插入语，即一个db表达式或子查询。 $conditions 参数指定连接条件。它的语法与where() 相同。$params 参数指定绑定到整个查询的参数。

值得注意的是，它不像其他的查询生成器方法，每次调用一个join方法都会追加到sql中。

下面是一些例子：

// join `tbl_profile` on user_id=id
join('tbl_profile', 'user_id=id')
// left join `pub`.`tbl_profile` `p` on p.user_id=id and type=1
leftjoin('pub.tbl_profile p', 'p.user_id=id and type=:type', array(':type'=>1))

group()

function group($columns)

group() 方法指定查询的group by。 $columns 参数指定列进行分组，它可以是一个用逗号分隔的列的字符串，或者一个列的数组。列名称可以包含表前缀。该方法将自动引用列名，除非一列包含一些括号（即是一个 db 表达式）。

下面是一些例子：

// group by `name`, `id`
group('name, id')
// group by `tbl_profile`.`name`, `id`
group(array('tbl_profile.name', 'id'))

having()

function having($conditions, $params=array())

having() 方法指定查询的 having。它的使用方式与 where() 相同。

下面是一些例子：

// having id=1 or id=2
having('id=1 or id=2')
// having id=1 or id=2
having(array('or', 'id=1', 'id=2'))

union()

function union($sql)

union() 方法指定查询 union。它使用union操作附加到现有的sql上。调用union()多次将现有的sql附加多个表。

下面是一些例子：

// union (select * from tbl_profile)
union('select * from tbl_profile')

执行查询（executing queries）

调用上面查询生成器的方法来建立一个查询后，我们可以调用dao方法数据访问对象执行查询。例如，我们可以调用 cdbcommand::queryrow() 获得连续的结果，或者使用 cdbcommand::queryall() 立刻得到所有行。

例子：

$users = yii::app()->db->createcommand()
  ->select('*')
  ->from('tbl_user')
  ->queryall();

检索表（retrieving sqls）

除了执行查询生成器的查询，我们也可以获取相应的sql语句。这可以通过调用 cdbcommand::gettext() 获得。

$sql = yii::app()->db->createcommand()
  ->select('*')
  ->from('tbl_user')
  ->text;

如果有任何参数必须绑定到查询的，他们可以通过 cdbcommand::params 的属性绑定。

建立查询的替代语法（alternative syntax for building queries）

有时，使用方法链来建立一个查询可能不是最佳的选择。yii的查询生成器允许使用对简单象属性赋值的方式去建立查询。特别是，每个查询生成器的方法都有一个具有相同名称的属性。给属性赋值相当于调用相应的方法。例如，下面两个语句是等价的，假设$command 代表 cdbcommand 对象：

$command->select(array('id', 'username'));
$command->select = array('id', 'username');

此外， cdbconnection::createcommand() 方法可以把一个数组作为参数。数组中的键值对将被用来初始化创建的 cdbcommand 实例的属性。这意味着，我们可以使用下面的代码来建立一个查询：

$row = yii::app()->db->createcommand(array(
  'select' => array('id', 'username'),
  'from' => 'tbl_user',
  'where' => 'id=:id',
  'params' => array(':id'=>1),
))->queryrow();

建立多个查询（building multiple queries）

一个 cdbcommand 实例可以重复使用多次建立几个查询。建立一个新的查询之前，需要调用 cdbcommand::reset() 方法以清理前面的查询。例子：

$command = yii::app()->db->createcommand();
$users = $command->select('*')->from('tbl_users')->queryall();
$command->reset(); // clean up the previous query
$posts = $command->select('*')->from('tbl_posts')->queryall();

3. 建立数据操作查询（building data manipulation queries)

数据操作查询是指sql语句插入，更新和删除数据库表中的数据。对应于这些查询，查询生成器分别提供了插入，更新和删除的方法。不同于上面介绍 select 的查询方法，这些数据操作查询方法将建立一个完整的sql语句，并立即执行。

insert(): 将行插入到表
update(): 更新表中的数据
delete(): 从表中删除数据
下面描述了这些数据操作查询方法。

insert()

function insert($table, $columns)

insert()方法的建立和执行一条 insert sql 语句。 $table 参数指定要插入的表，而键值对数组 $columns 指定要插入的列的值。该方法将转义表名，并且将与绑定参数结合使用。

下面是一个例子：

// build and execute the following sql:
// insert into `tbl_user` (`name`, `email`) values (:name, :email)
$command->insert('tbl_user', array(
  'name'=>'tester',
  'email'=>'[e-mail]',
));

update()

function update($table, $columns, $conditions='', $params=array())

update()方法的建立和执行一个sql更新语句。 $table 参数指定要更新的表； $columns 是键值对的数组，用于指定要更新的列值的；$conditions 和 $params 像where()一样，指定 update 语句中的 where 子句。该方法将转义表名，并且将与要更新的值的参数结合使用。

下面是一个例子：

// build and execute the following sql:
// update `tbl_user` set `name`=:name where id=:id
$command->update('tbl_user', array(
  'name'=>'tester',
), 'id=:id', array(':id'=>1));

delete()

function delete($table, $conditions='', $params=array())

delete()方法的建立和执行一个sql删除语句。 $table 参数指定要删除数据的表；$conditions 和$params像where()一样，指定 delete 语句中的 where 子句。该方法将正确转义表名。

下面是一个例子：

// build and execute the following sql:
// delete from `tbl_user` where id=:id
$command->delete('tbl_user', 'id=:id', array(':id'=>1));

4. 建立架构操作查询（building schema manipulation queries）

除了正常的数据检索和操作查询，查询生成器提供了一套用于可以操纵数据库的构建和执行sql查询的方法。它支持以下操作：

createtable(): 创建一个表
renametable(): 重命名表
droptable(): 删除一个表
truncatetable(): 截断一个表，即删除表中的所有数据但不删除表本身
addcolumn(): 给表添加列
renamecolumn(): 重命名表中的列
altercolumn(): 改变一个表的列
addforeignkey(): 添加一个外键（自1.1.6可用）
dropforeignkey(): 删除一个外键（自1.1.6可用）
dropcolumn(): 删除一个表的列
createindex(): 创建一个索引
dropindex(): 删除一个索引

info: 虽然在不同的数据库管理系统中sql语句操作数据库的模式有很大的不同，但查询生成器试图提供一个统一的接口，用于构建这些查询。这简化了数据库迁移从一个数据库管理系统到另一个任务。

抽象数据类型 abstract data types

查询生成器中引入了一组可以在定义表的列中使用抽象数据类型。与物理数据类型不同，物理数据类型有具体独特的dbms而且在不同的dbms中有很大的不同，抽象数据类型都是独立的dbms。当抽象数据类型在定义表的列中使用时，查询生成器将其转换成相应的物理数据类型。

下面的抽象数据类型由查询生成器的支持。

pk: 一个通用的主键类型，将被转换成 int(11) not null auto_increment primary key for mysql;
string: 字符串类型，将被转换成 varchar(255) for mysql;
text: 文本型（长字符串），将被转换成 text for mysql;
integer: 整数类型，将被转换成 int(11) for mysql;
float: 浮点数类型，将被转换成 float for mysql;
decimal: 十进制数类型，将被转换成 decimal for mysql;
datetime: datetime类型，将被转换成 datetime for mysql;
timestamp: 时间戳类型，将被转换成 timestamp for mysql;
time: 时间类型，将被转换成 time for mysql;
date: 日期类型，将被转换成 date for mysql;
binary: 二进制数据类型，将被转换成 blob for mysql;
boolean: 布尔类型，将被转换成 tinyint(1) for mysql;
money: 金钱/货币型，将被转换成 decimal(19,4) for mysql. 自1.1.8版本开始此类型可以使用。

createtable()

function createtable($table, $columns, $options=null)

createtable() 方法构建和执行一条创建表的sql语句。$table 参数指定要创建的表的名称。 $columns 参数指定在新表中的列。他们必须被指定为名称类型的键值对(e.g. 'username'=>'string')。 $options 参数指定应附加到生成的sql上的任何额外的sql片段。查询生成器将正确的引用的表名以及列名。

当指定一个列的定义时，可以使用如上所述的抽象数据类型。查询生成器会根据当前使用的数据库管理系统的抽象数据类型转换成相应的物理数据类型。例如，string 类型将被转换为mysql的 varchar(255)。

一个列定义还可以包含非抽象数据类型或规格。他们将没有任何改变的被放置在生成的sql中。例如，point 不是一个抽象数据类型，如果用在列定义，它会出现在生成的sql中，而且 string not null将被转换varchar(255) not null（即只有抽象类型的字符串转换）。

下面是一个例子，显示了如何创建一个表：

// create table `tbl_user` (
//   `id` int(11) not null auto_increment primary key,
//   `username` varchar(255) not null,
//   `location` point
// ) engine=innodb
createtable('tbl_user', array(
  'id' => 'pk',
  'username' => 'string not null',
  'location' => 'point',
), 'engine=innodb')

renametable()

function renametable($table, $newname)

renametable() 方法创建和执行一条重命名表名的sql语句。 $table 参数指定要重命名的表的名称。 $newname 参数指定表的新名称。查询生成器将正确引用的表名。

下面是一个示例，演示了如何重命名表：

// rename table `tbl_users` to `tbl_user`
renametable('tbl_users', 'tbl_user')

droptable()

function droptable($table)

droptable() 方法创建和执行一条删除表的sql语句。在$table 参数指定要删除的表的名称。查询生成器会正确地引用的表名。

下面是一个示例展示如何删除一个表：

// drop table `tbl_user`
droptable('tbl_user')

truncatetable()

function truncatetable($table)

truncatetable() 方法建立和执行一条清空表数据的sql语句。 $table 参数指定清空的表名。查询生成器会正确地引用的表名。

下面是一个示例显示如何清空表：

// truncate table `tbl_user`
truncatetable('tbl_user')

addcolumn()

function addcolumn($table, $column, $type)

addcolumn() 方法创建并执行一条添加一个表的新列的sql语句。 $table 参数指定新列将被添加到的表的名称。 $column 参数指定新列的名称。 $type 指定新列的定义。列定义中可以包含抽象数据类型，如 "createtable"小节中的描述。查询生成器将正确的引用的表名以及列名。

下面是一个示例，演示如何添加一个表的列：

// alter table `tbl_user` add `email` varchar(255) not null
addcolumn('tbl_user', 'email', 'string not null')

dropcolumn()

function dropcolumn($table, $column)

dropcolumn() 方法创建和执行一条删除表列的sql语句。 $table 参数指定要被删除的列所属的表名。 $column 参数指定要被删除的列名。查询生成器将正确地引用的表名以及列名。

下面是一个示例展示如何删除一个表列：

// alter table `tbl_user` drop column `location`
dropcolumn('tbl_user', 'location')

renamecolumn()

function renamecolumn($table, $name, $newname)

renamecolumn() 方法创建和执行一条重命名列的sql语句。 $table 参数指定要重命名的列所属的表的名称。 $name 参数指定的旧列名。 $newname 指定新的列名。查询生成器将正确地引用的表名以及列名。

下面是一个示例，展示了如何重命名表列：

// alter table `tbl_users` change `name` `username` varchar(255) not null
renamecolumn('tbl_user', 'name', 'username')

altercolumn()

function altercolumn($table, $column, $type)

altercolumn() 方法创建和执行一条修改表列的sql语句。 $table 参数指定要被改变的列所属的表的名称。 $column 参数指定被改变的列的名称。$type 指定列的新定义。列定义中可以包含抽象数据类型，如 "createtable"小节中的描述。查询生成器将正确的引用的表名以及列名。

下面是一个示例，展示了如何更改一个表列：

// alter table `tbl_user` change `username` `username` varchar(255) not null
altercolumn('tbl_user', 'username', 'string not null')

addforeignkey()

function addforeignkey($name, $table, $columns,$reftable, $refcolumns, $delete=null, $update=null)

addforeignkey() 方法创建和执行一条添加一个外键约束sql语句。 $name 参数指定外键的名称。 $table 和 $columns 参数指定外键的表名和列名。如果有多个列，他们应该用逗号字符分隔。 $reftable 和 $refcolumns 参数指定表名和列名的外键引用。 $delete 和 $update 参数指定 sql语句中的 on delete 和on update选项。大多数的dbms都支持这些选项：restrict, cascade, no action, set default, set null. 。查询生成器会正确地引用表名，索引名和列名。

下面是一个示例，演示如何添加一个外键约束：

// alter table `tbl_profile` add constraint `fk_profile_user_id`
// foreign key (`user_id`) references `tbl_user` (`id`)
// on delete cascade on update cascade
addforeignkey('fk_profile_user_id', 'tbl_profile', 'user_id', 'tbl_user', 'id', 'cascade', 'cascade')

dropforeignkey()

function dropforeignkey($name, $table)

dropforeignkey() 方法创建和执行一条删除一个外键约束的sql语句。 $name 参数指定的要删除的外键约束的名称。 $table 参数指定外键所属的表的名称。查询生成器将正确地引用的表名以及约束名称。

下面是一个示例，显示了如何删除外键约束：

// alter table `tbl_profile` drop foreign key `fk_profile_user_id`
dropforeignkey('fk_profile_user_id', 'tbl_profile')

createindex()

function createindex($name, $table, $column, $unique=false)

createindex() 方法构建和执行一条创建索引的sql语句。 $name 参数指定要创建的索引的名称。$table 参数指定索引所属的表的名称。$column 参数指定要索引的列的名称。 $unique 参数指定是否创建一个唯一索引。如果索引由多个列，则必须用逗号将它们隔开。查询生成器会正确地引用表名，索引名和列名。

下面是一个示例，显示了如何创建索引：

// create index `idx_username` on `tbl_user` (`username`)
createindex('idx_username', 'tbl_user', 'username')

dropindex()

function dropindex($name, $table)

dropindex() 方法创建和执行一条删除索引的sql语句。 $name 参数指定要删除的索引的名称。$table 参数指定索引所属的表的名称。查询生成器将正确地引用的表名以及索引名称。

下面是一个示例，显示了如何删除索引：

// drop index `idx_username` on `tbl_user`
dropindex('idx_username', 'tbl_user')

希望本文所述对大家yii学习有所帮助。