YII Framework学习教程-YII的Model-数据库操作4-翻译

原文：http://www.yiiframework.com/doc/guide/1.1/en/database.query-builder

           官方提供的文档是第一手的资料，但是<Query Builder>没有进行中文翻译，下面粗略的翻译一下，仅供参考。

 

 

Query Builder查询生成器

 

The Yii Query Builder provides an object-oriented way of writing SQL statements. It allows developers to use class methods and properties to specify individual parts of a SQL statement. It then assembles different parts into a valid SQL statement that can be further executed by calling the DAO methods as described in Data Access Objects. The following shows a typical usage of the Query Builder to build a SELECT SQL statement:

Yii的查询生成器提供了一个面向对象的方式编写SQL语句。它允许开发人员使用类的方法和属性来指定一个SQL语句的各个部分。然后，组装成一个有效的SQL语句，再通过调用数据访问对象DAO方法执行。下面例子主要说明了如何用Query Builder来建立一个SELECT SQL语句：

$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();

The Query Builder is best used when you need to assemble a SQL statement procedurally, or based on some conditional logic in your application. The main benefits of using the Query Builder include:

• It allows building complex SQL statements programmatically.
• It automatically quotes table names and column names to prevent conflict with SQL reserved words and special characters.
• It also quotes parameter values and uses parameter binding when possible, which helps reduce risk of SQL injection attacks.
• It offers certain degree of DB abstraction, which simplifies migration to different DB platforms.

当你需要组装SQL语句的程序，或在应用程序中动态生产条件查询语句使用查询生成器是你最好的选择方式。使用查询生成器的主要优点包括：

 

•  它可以用编程的方式构建复杂的SQL语句 。
• 它会自动转义表名和列名，以防止SQL保留字和特殊字符冲突。
• 它还可以在使用参数绑定时转义参数值，这有助于减少SQL注入攻击。
• 它提供了一定程度的数据库抽象，从而简化了迁移到不同的数据库平台。

 

 

It is not mandatory to use the Query Builder. In fact, if your queries are simple, it is easier and faster to directly write SQL statements.

你也可以不使用Query Builder。如果你的查询很简单，可以直接sql语句。这样比较即简单有快。

Note: Query builder cannot be used to modify an existing query specified as a SQL statement. For example, the following code will not work:

注：查询生成器不能用于修改现有的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));

In other words, do not mix the usage of plain SQL and query builder. 

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

1. Preparing Query Builder 初始化查询生成器

The Yii Query Builder is provided in terms of CDbCommand, the main DB query class described in Data Access Objects.

 

To start using the Query Builder, we create a new instance of CDbCommand as follows,

Yii的查询生成器是CDbCommand类提供的，它是DAO操作中最主要的一个类

要开始使用“查询生成器”，首先要创建一个CDbCommand的实例，如下

$command = Yii::app()->db->createCommand();

That is, we use Yii::app()->db to get the DB connection, and then call CDbConnection::createCommand()to create the needed command instance.

Note that instead of passing a whole SQL statement to the createCommand() call as we do in Data Access Objects, we leave it empty. This is because we will build individual parts of the SQL statement using the Query Builder methods explained in the following.

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

注意，不要直接通过createCommand()方法参数添加整个SQL语句，默认为空即可。下面我们将使用查询生成器建立SQL语句的各个部分。

2. Building Data Retrieval Queries 构建数据查询语句

Data retrieval queries refer to SELECT SQL statements. The query builder provides a set of methods to build individual parts of a SELECT statement. Because all these methods return the CDbCommand instance, we can call them using method chaining, as shown in the example at the beginning of this section.

• select(): specifies the SELECT part of the query
• selectDistinct(): specifies the SELECT part of the query and turns on the DISTINCT flag
• from(): specifies the FROM part of the query
• where(): specifies the WHERE part of the query
• 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(): specifies the GROUP BY part of the query
• having(): specifies the HAVING part of the query
• order(): specifies the ORDER BY part of the query
• limit(): specifies the LIMIT part of the query
• offset(): specifies the OFFSET part of the query
• union(): appends a UNION query fragment

In the following, we explain how to use these query builder methods. For simplicity, we assume the underlying database is MySQL. Note that if you are using other DBMS, the table/column/value quoting shown in the examples may be different.

数据查询语句用SELECT SQL语句。查询生成器提供了一系列的方法，建立一个SELECT语句的各个部分。由于所有这些方法都是返回CDbCommand的实例，我们可以称他们方法链，如本节开头的例子所示。

 

• select(): 指定查询的SELECT部分
• selectDistinct(): 指定查询的SELECT部分，并且使用DISTINCT
• from()指定查询的FROM部分
• where(): 指定查询的WHERE部分
• join():追加一个内部联接的查询片段
• leftJoin(): 追加左外连接查询片段
• rightJoin():追加右外连接查询片段
• crossJoin(): 追加一个交叉连接查询的片段
• naturalJoin(): 追加一个自然连接查询的片段
• group():  指定查询的GROUP BY部分
• having(): 指定查询的HAVING部分
• order():  指定查询的ORDER BY部分
• limit():  指定查询的LIMIT部分
• offset():  指定查询的OFFSET部分
• union():  追加UNION部分

在下面，我们将解释如何使用这些查询生成器方法。为方便起见，我们假设底层数据库是MySQL。请注意，如果你是使用其他DBMS的例子中，表/列/值可能会有所不同。

 

 

select()

function select($columns='*')

The select() method specifies the SELECT part of a query. The $columns parameter specifies the columns to be selected, which can be either a string representing comma-separated columns, or an array of column names. Column names can contain table prefixes and/or column aliases. The method will automatically quote the column names unless a column contains some parenthesis (which means the column is given as a DB expression).

Below are some examples:

select() 方法用于指定查询的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 numselect(array('id', 'count(*) as num'))

selectDistinct()

function selectDistinct($columns)

The selectDistinct() method is similar as select() except that it turns on the DISTINCT flag. For example,selectDistinct('id, username') will generate the following SQL:

 selectDistinct() 方法和select()类似 ，只不过它附加上DISTINCT标志。例如，selectDistinct(‘id, username’) 将产生以下的SQL：

SELECT DISTINCT `id`, `username`

from()

function from($tables)

The from() method specifies the FROM part of a query. The $tables parameter specifies which tables to be selected from. This can be either a string representing comma-separated table names, or an array of table names. Table names can contain schema prefixes (e.g. public.tbl_user) and/or table aliases (e.g.tbl_user u). The method will automatically quote the table names unless it contains some parenthesis (which means the table is given as a sub-query or DB expression).

Below are some examples:

用 from()方法指定一个查询的FROM部分。  $tables参数指定表名。这可以是一个字符串，用逗号分隔的表名或表名数组。表名可以包含前缀（例如public.tbl_user）和/或表别名（egtbl_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) pfrom(array('tbl_user', '(select * from tbl_profile) p'))

where()

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

The where() method specifies the WHERE part of a query. The $conditions parameter specifies query conditions while $params specifies the parameters to be bound to the whole query. The $conditionsparameter can be either a string (e.g. id=1) or an array of the format:

where()方法用来指定查询的WHERE查询部分。$conditions参数指定查询条件，而$params 指定绑定参数。$condition 参数可以是一个字符串（如 id=1）或数组，格式如下：

array(operator, operand1, operand2, ...)

where operator can be any of the following:

• and: the operands should be concatenated together using AND. For example, array('and', 'id=1', 'id=2') will generate id=1 AND id=2. If an operand is an array, it will be converted into a string using the same rules described here. For example, array('and', 'type=1', array('or', 'id=1', 'id=2')) will generate type=1 AND (id=1 OR id=2). The method will NOT do any quoting or escaping.
• or: similar as the and operator except that the operands are concatenated using OR.
• in: operand 1 should be a column or DB expression, and operand 2 be an array representing the range of the values that the column or DB expression should be in. For example, array('in', 'id', array(1,2,3)) will generate id IN (1,2,3). The method will properly quote the column name and escape values in the range.
• not in: similar as the in operator except that IN is replaced with NOT IN in the generated condition.
• like: operand 1 should be a column or DB expression, and operand 2 be a string or an array representing the range of the values that the column or DB expression should be like. For example,array('like', 'name', '%tester%') will generate name LIKE '%tester%'. When the value range is given as an array, multiple LIKE predicates will be generated and concatenated using AND. For example, array('like', 'name', array('%test%', '%sample%')) will generate name LIKE '%test%' AND name LIKE '%sample%'. The method will properly quote the column name and escape values in the range.
• not like: similar as the like operator except that LIKE is replaced with NOT LIKE in the generated condition.
• or like: similar as the like operator except that OR is used to concatenated several LIKE predicates.
• or not like: similar as the not like operator except that OR is used to concatenated several NOT LIKE predicates.

Below are some examples of using where:

where 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相似。不是and关系而是or。
• in: 操作数1应是列或DB表达式，操作数2是一个数组表示的是列取值范围或DB表达式。例如，array(‘in’, ‘id’, array(1,2,3))将生成的IN (1,2,3)。该方法将正确转义列名和值。
• not in: 和in的用法类似，不过是NOT IN 而不是IN.
• like: 操作数1应是一个列或DB的表达式，而操作数2是一个字符串或数组。例如，array(‘like’, ‘name’, ‘%tester%’)将产生  name LIKE ‘%tester%’。当值是一个数组，会使用AND 连接这些LIKE。例如，array(‘like’, ‘name’, array(‘%test%’, ‘%sample%’)) 将产生  name  LIKE ‘%test%’ AND name LIKE ‘%sample%’。该方法将正确转义引用的列名和值。
• not like:和LIKE用法类似，不同是把LIKE替换为NOT LIKE 
• or like: 和like运算类似，不同是当多个值时不是用AND连接，而是用OR.
• or not like: 和 NOT LIKE类似，当多个值时不是用AND连接，而是用OR

下面是使用Where的一些例子：

 

 

// WHERE id=1 or id=2where('id=1 or id=2')// WHERE id=:id1 or id=:id2where('id=:id1 or id=:id2', array(':id1'=>1, ':id2'=>2))// WHERE id=1 OR id=2where(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%')))

Please note that when the operator contains like, we have to explicitly specify the wildcard characters (such as % and _) in the patterns. If the patterns are from user input, we should also use the following code to escape the special characters to prevent them from being treated as wildcards:

请注意，当操作包含LIKE时，我们必须明确指定通配符（如％和_）。如果是用户输入的%_，可以使用如下的方法转义，以防止它们被作为通配符处理：

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

order()

function order($columns)

The order() method specifies the ORDER BY part of a query. The $columns parameter specifies the columns to be ordered by, which can be either a string representing comma-separated columns and order directions (ASCor DESC), or an array of columns and order directions. Column names can contain table prefixes. The method will automatically quote the column names unless a column contains some parenthesis (which means the column is given as a DB expression).

order()方法用于指定查询的ORDER BY部分。$columns 参数指定排序的列名列表，可以是一个字符串，用逗号分隔的列，列可以指定排序方式（ASC or DESC），或用数组指定列和排序方式。列名可以包含表前缀。该方法将自动转义列名，除非列是一个DB表达式

Below are some examples:

下面是一些例子：

// ORDER BY `name`, `id` DESCorder('name, id desc')// ORDER BY `tbl_profile`.`name`, `id` DESCorder(array('tbl_profile.name', 'id desc')

limit() and offset()

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

The limit() and offset() methods specify the LIMIT and OFFSET part of a query. Note that some DBMS may not support LIMIT and OFFSET syntax. In this case, the Query Builder will rewrite the whole SQL statement to simulate the function of limit and offset.

 limit() 和 offset() 方法指定查询的LIMIT和OFFSET部分。注意：某些DBMS可能不支持LIMIT和OFFSET语法。在这种情况下，查询生成器将改写整个SQL语句，模拟limit and offset功能

Below are some examples:

下面是一些例子：

// LIMIT 10limit(10)// LIMIT 10 OFFSET 20limit(10, 20)// OFFSET 20offset(20)

join() and its variants join() 及其变种

function join($table, $conditions, $params=array())function leftJoin($table, $conditions, $params=array())function rightJoin($table, $conditions, $params=array())function crossJoin($table)function naturalJoin($table)

The join() method and its variants specify how to join with other tables using INNER JOIN, LEFT OUTER JOIN,RIGHT OUTER JOIN, CROSS JOIN, or NATURAL JOIN. The $table parameter specifies which table to be joined with. The table name can contain schema prefix and/or alias. The method will quote the table name unless it contains a parenthesis meaning it is either a DB expression or sub-query. The $conditionsparameter specifies the join condition. Its syntax is the same as that in where(). And $params specifies the parameters to be bound to the whole query.

Note that unlike other query builder methods, each call of a join method will be appended to the previous ones.

Below are some examples:

join()方法及其变种指定如何与其他表连接。具体的连接方式可以用INNER JOIN，LEFT OUTER JOIN，RIGHT OUTER JOIN，CROSS JOIN，或NATURAL JOIN。 $table参数指定哪些表。表名可以包含数据库的前缀和/或别名。该方法将转义表的名称，除非它包含是一个DB表达式或子查询。$condition指定连接条件。它的语法和where()相同。$params指定的参数绑定到整个查询。

请注意，不用叠加使用查询生成器方法，每次调用一个join方法将自动追加到以前的。

下面是一些例子：

// JOIN `tbl_profile` ON user_id=idjoin('tbl_profile', 'user_id=id')// LEFT JOIN `pub`.`tbl_profile` `p` ON p.user_id=id AND type=1leftJoin('pub.tbl_profile p', 'p.user_id=id AND type=:type', array(':type'=>1))

group()

function group($columns)

The group() method specifies the GROUP BY part of a query. The $columns parameter specifies the columns to be grouped by, which can be either a string representing comma-separated columns, or an array of columns. Column names can contain table prefixes. The method will automatically quote the column names unless a column contains some parenthesis (which means the column is given as a DB expression).

Below are some examples:

group() 方法指定查询的GROUP BY部分。$columns指定进行分组的列，它可以是一个字符串，用逗号分隔的列，或数组。列名可以包含表前缀。该方法将自动转义列名，带有括号的列除外

下面是一些例子：

// 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())

The having() method specifies the HAVING part of a query. Its usage is the same as where().

Below are some examples:

having() 方法指定查询的HAVING部分。它的用法和where()类似。

下面是一些例子：

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

union()

function union($sql)

The union() method specifies the UNION part of a query. It appends $sql to the existing SQL using UNIONoperator. Calling union() multiple times will append multiple SQLs to the existing SQL.

Below are some examples:

 union() 方法指定查询的UNION部分。$sql 将追加到现有的 SQL后。调用多个union() 将追加多个SQL到现有的SQL。

下面是一些例子：

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

Executing Queries执行查询

After calling the above query builder methods to build a query, we can call the DAO methods as described inData Access Objects to execute the query. For example, we can call CDbCommand::queryRow() to obtain a row of result, or CDbCommand::queryAll() to get all rows at once. Example:

调用上面的查询生成器方法来建立一个查询后，我们可以调用DAO方法， 执行查询。例如，我们可以调用CDbCommand::queryRow() 获得结果集，或CDbCommand::queryAll() 一次获得所有行。例如：

$users = Yii::app()->db->createCommand()    ->select('*')    ->from('tbl_user')    ->queryAll();

Retrieving SQLs检索SQLs

Besides executing a query built by the Query Builder, we can also retrieve the corresponding SQL statement. This can be done by calling CDbCommand::getText().

除了使用查询生成器生成一个查询，我们也可以通过调用CDbCommand::getText()获取相应的SQL语句。 

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

If there are any parameters to be bound to the query, they can be retrieved via the CDbCommand::paramsproperty.

可以通过CDbCommand：params实现绑定参数到查询

Alternative Syntax for Building Queries建立查询的替代语法

Sometimes, using method chaining to build a query may not be the optimal choice. The Yii Query Builder allows a query to be built using simple object property assignments. In particular, for each query builder method, there is a corresponding property that has the same name. Assigning a value to the property is equivalent to calling the corresponding method. For example, the following two statements are equivalent, assuming $command represents a CDbCommand object:

有时，使用方法链来建立一个查询可能不是最佳选择。 Yii的查询生成器允许使用对象属性赋值建立查询语句。YII中每一个查询生成器的方法，都有一个对应的和方法名具有相同属性。他们的功能类似。例如，下面的两个语句是等价的，假设 $command代表CDbCommand对象：

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

Furthermore, the CDbConnection::createCommand() method can take an array as the parameter. The name-value pairs in the array will be used to initialize the properties of the created CDbCommand instance. This means, we can use the following code to build a query:

此外， 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 建立多个查询

A CDbCommand instance can be reused multiple times to build several queries. Before building a new query, however, the CDbCommand::reset() method must be invoked to clean up the previous query. For example:

一个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 建立数据操作查询

Data manipulation queries refer to SQL statements for inserting, updating and deleting data in a DB table. Corresponding to these queries, the query builder provides insert, update and delete methods, respectively. Unlike the SELECT query methods described above, each of these data manipulation query methods will build a complete SQL statement and execute it immediately.

数据操作查询是指在数据库表中的数据插入，更新和删除的SQL语句。对应于这些查询，查询生成器分别提供了insert, update and delete方法。 和以上所述的SELECT查询的方法不同，这些数据操作查询方法将建立一个完整的SQL语句后，立即执行。

• insert(): inserts a row into a table
• update(): updates the data in a table
• delete(): deletes the data from a table

Below we describe these data manipulation query methods.

下面我们说说这些数据操作查询方法。

insert()

function insert($table, $columns)

The insert() method builds and executes an INSERT SQL statement. The $table parameter specifies which table to be inserted into, while $columns is an array of name-value pairs specifying the column values to be inserted. The method will quote the table name properly and will use parameter-binding for the values to be inserted.

Below is an example:

 

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())

The update() method builds and executes an UPDATE SQL statement. The $table parameter specifies which table to be updated; $columns is an array of name-value pairs specifying the column values to be updated;$conditions and $params are like in where(), which specify the WHERE clause in the UPDATE statement. The method will quote the table name properly and will use parameter-binding for the values to be updated.

Below is an example:

update() 方法将建立和执行UPDATE的SQL语句。$table指定表名; $columns指定要更新的列，值为名称 – 值对的数组;$conditions and $param的用法和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())

The delete() method builds and executes a DELETE SQL statement. The $table parameter specifies which table to delete from; $conditions and $params are like in where(), which specify the WHERE clause in theDELETE statement. The method will quote the table name properly.

Below is an example:

 delete() 方法将建立和执行DELETE SQL语句。 $table 参数指定表名;$conditions 、 $params和where()的用法类似，指定DELETE的条件。该方法将正确转义表名。

下面是一些例子：

// 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构建表结构操作查询 

Besides normal data retrieval and manipulation queries, the query builder also offers a set of methods for building and executing SQL queries that can manipulate the schema of a database. In particular, it supports the following queries:

• createTable(): creates a table
• renameTable(): renames a table
• dropTable(): drops a table
• truncateTable(): truncates a table
• addColumn(): adds a table column
• renameColumn(): renames a table column
• alterColumn(): alters a table column
• dropColumn(): drops a table column
• createIndex(): creates an index
• dropIndex(): drops an index

Info: Although the actual SQL statements for manipulating database schema vary widely across different DBMS, the query builder attempts to provide a uniform interface for building these queries. This simplifies the task of migrating a database from one DBMS to another.

除了正常的数据检索和操作查询，查询生成器还提供了一套操作数据库结构的的sql语句的构建和执行的方法， 它支持下面的操作：

• createTable(): 创建表
• renameTable():重命名
• dropTable(): 删除
• truncateTable(): 清空
• addColumn(): 添加一列
• renameColumn(): 修改列名
• alterColumn(): 修改列
• dropColumn(): 删除列
• createIndex(): 添加索引
• dropIndex(): 删除索引

注意：虽然实际的SQL语句操作数据库结构根据不同DBMS而不同，但是查询生成器，试图提供一个建立这些查询统一的接口。这简化了从一个DBMS数据库迁移到另一个DBMS的任务。

Abstract Data Types

The query builder introduces a set of abstract data types that can be used in defining table columns. Unlike the physical data types that are specific to particular DBMS and are quite different in different DBMS, the abstract data types are independent of DBMS. When abstract data types are used in defining table columns, the query builder will convert them into the corresponding physical data types.

The following abstract data types are supported by the query builder.

• pk: a generic primary key type, will be converted into int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY for MySQL;
• string: string type, will be converted into varchar(255) for MySQL;
• text: text type (long string), will be converted into text for MySQL;
• integer: integer type, will be converted into int(11) for MySQL;
• float: floating number type, will be converted into float for MySQL;
• decimal: decimal number type, will be converted into decimal for MySQL;
• datetime: datetime type, will be converted into datetime for MySQL;
• timestamp: timestamp type, will be converted into timestamp for MySQL;
• time: time type, will be converted into time for MySQL;
• date: date type, will be converted into date for MySQL;
• binary: binary data type, will be converted into blob for MySQL;
• boolean: boolean type, will be converted into tinyint(1) for MySQL;
• money: money/currency type, will be converted into decimal(19,4) for MySQL. This type has been available since version 1.1.8.

查询生成器引入了一个抽象数据类型，可以用于定义表列。不像是物理数据类型，根据不同的DBMS，而数据类型也不相同。抽象数据类型和DBMS无关。当用抽象数据类型定义表中的列时，查询生成器将其转换成相应的物理数据类型。

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

• pk: 主键,他将转换为int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY，如果DBMS是mysql;
• string: string type, 将转换为 varchar(255)，如果DBMS是mysql;
• text: text type (long string), 将转换为 text ，如果DBMS是mysql;
• integer: integer type, 将转换为 int(11)，如果DBMS是mysql;
• float: floating number type, 将转换为 float ，如果DBMS是mysql;
• decimal: decimal number type, 将转换为 decimal ，如果DBMS是mysql;
• datetime: datetime type,将转换为 datetime，如果DBMS是mysql;
• timestamp: timestamp type, 将转换为 timestamp，如果DBMS是mysql;
• time: time type,将转换为 time ，如果DBMS是mysql;
• date: date type, 将转换为 date，如果DBMS是mysql;
• binary: binary data type, 将转换为 blob f，如果DBMS是mysql;
• boolean: boolean type, 将转换为 tinyint(1) ，如果DBMS是mysql;
• money: money/currency type, 将转换为 decimal(19,4) ，如果DBMS是mysql;. 此类型在 1.1.8版本后可用.

createTable()

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

The createTable() method builds and executes a SQL statement for creating a table. The $table parameter specifies the name of the table to be created. The $columns parameter specifies the columns in the new table. They must be given as name-definition pairs (e.g. 'username'=>'string'). The $options parameter specifies any extra SQL fragment that should be appended to the generated SQL. The query builder will quote the table name as well as the column names properly.

 createTable()方法用于建立和执行创建一个表的SQL语句。 $table参数指定要创建的表的名称。 $columns 参数指定新表中的列。他们必须为键值对(名称=>类型)( ‘username’=>’string’)。 $options参数指定额外的SQL片段，它会附加到生成的SQL上。查询生成器会正确转义表名以及列名。

When specifying a column definition, one can use an abstract data type as described above. The query builder will convert the abstract data type into the corresponding physical data type, according to the currently used DBMS. For example, string will be converted into varchar(255) for MySQL.

varchar(255) for MySQL.在列定义时，可以使用一个抽象的数据类型，如上所述。查询生成器根据目前的DBMS将抽象数据类型转换成相应的物理数据类型。例如，在MySQL中将字符串转换为VARCHAR（255）。

A column definition can also contain non-abstract data type or specifications. They will be put in the generated SQL without any change. For example, point is not an abstract data type, and if used in a column definition, it will appear as is in the resulting SQL; and string NOT NULL will be converted into varchar(255) NOT NULL(i.e., only the abstract type string is converted).

列定义，也可以包含非抽象数据类型和其他的特性。他们将在生成的SQL是不会做任何改变。例如， point 是不是一个抽象数据类型，如果在列定义中使用，它会出现在生成的SQL中。

string NOT NULL将被转换为数据类型为varchar（255）NOT NULL。

Below is an example showing how to create a table:

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

// CREATE TABLE `tbl_user` (//     `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,//     `username` varchar(255) NOT NULL,//     `location` point// ) ENGINE=InnoDBcreateTable('tbl_user', array(    'id' => 'pk',    'username' => 'string NOT NULL',    'location' => 'point',), 'ENGINE=InnoDB')

renameTable()

function renameTable($table, $newName)

The renameTable() method builds and executes a SQL statement for renaming a table. The $tableparameter specifies the name of the table to be renamed. The $newName parameter specifies the new name of the table. The query builder will quote the table names properly.

Below is an example showing how to rename a table:

renameTable() 方法将建立并执行一个重命名表的SQL语句。$table指定要重命名的表的名称。 $newname参数指定表的新名称。

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

// RENAME TABLE `tbl_users` TO `tbl_user`renameTable('tbl_users', 'tbl_user')

dropTable()

function dropTable($table)

The dropTable() method builds and executes a SQL statement for dropping a table. The $table parameter specifies the name of the table to be dropped. The query builder will quote the table name properly.

Below is an example showing how to drop a table:

 dropTable()方法的建立和执行一个删除表的SQL语句。 $table 参数指定要删除的表的名称。

下面是一个例子展示了删除表

// DROP TABLE `tbl_user`dropTable('tbl_user')

truncateTable()

function truncateTable($table)

The truncateTable() method builds and executes a SQL statement for truncating a table. The $tableparameter specifies the name of the table to be truncated. The query builder will quote the table name properly.

Below is an example showing how to truncate a table:

truncateTable()方法将建立和执行清空表的SQL语句。$table指定的表的名称被截断。

下面是一些例子：

// TRUNCATE TABLE `tbl_user`truncateTable('tbl_user')

addColumn()

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

The addColumn() method builds and executes a SQL statement for adding a new table column. The $tableparameter specifies the name of the table that the new column will be added to. The $column parameter specifies the name of the new column. And $type specifies the definition of the new column. Column definition can contain abstract data type, as described in the subsection of “createTable”. The query builder will quote the table name as well as the column name properly.

Below is an example showing how to add a table column:

 addColumn() 方法生成并执行添加一个新的列到指定表的SQL语句。$table参数表名。$column指定新的列名。$type指定新列的定义。列定义可以包含抽象数据类型，如“createTable”所述。 

下面是一些例子：

// ALTER TABLE `tbl_user` ADD `email` varchar(255) NOT NULLaddColumn('tbl_user', 'email', 'string NOT NULL')

dropColumn()

function dropColumn($table, $column)

The dropColumn() method builds and executes a SQL statement for dropping a table column. The $tableparameter specifies the name of the table whose column is to be dropped. The $column parameter specifies the name of the column to be dropped. The query builder will quote the table name as well as the column name properly.

Below is an example showing how to drop a table column:

dropColumn() 方法的建立和执行一个删除表中指定列的SQL语句。$table指定表名，$column 指定要删除的列的名称。

下面是一些例子：

// ALTER TABLE `tbl_user` DROP COLUMN `location`dropColumn('tbl_user', 'location')

renameColumn()

function renameColumn($table, $name, $newName)

The renameColumn() method builds and executes a SQL statement for renaming a table column. The $tableparameter specifies the name of the table whose column is to be renamed. The $name parameter specifies the old column name. And $newName specifies the new column name. The query builder will quote the table name as well as the column names properly.

Below is an example showing how to rename a table column:

renameColumn() 方法建立并执行一个重命名表中指定列的SQL语句。 $table指定表名。 $ name参数指定旧列名。$newName指定新的列名。 

下面是一些例子：

// ALTER TABLE `tbl_users` CHANGE `name` `username` varchar(255) NOT NULLrenameColumn('tbl_user', 'name', 'username')

alterColumn()

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

The alterColumn() method builds and executes a SQL statement for altering a table column. The $tableparameter specifies the name of the table whose column is to be altered. The $column parameter specifies the name of the column to be altered. And $type specifies the new definition of the column. Column definition can contain abstract data type, as described in the subsection of “createTable”. The query builder will quote the table name as well as the column name properly.

Below is an example showing how to alter a table column:

alterColumn()方法的建立和执行一个修改表列的SQL语句。$table指定表称，$column指定列的新定义格式。列定义可以包含抽象数据类型，如“createTable”所述。

下面是一些例子：

// ALTER TABLE `tbl_user` CHANGE `username` `username` varchar(255) NOT NULLalterColumn('tbl_user', 'username', 'string NOT NULL')

addForeignKey()

function addForeignKey($name, $table, $columns,    $refTable, $refColumns, $delete=null, $update=null)

The addForeignKey() method builds and executes a SQL statement for adding a foreign key constraint to a table. The $name parameter specifies the name of the foreign key. The $table and $columns parameters specify the table name and column name that the foreign key is about. If there are multiple columns, they should be separated by comma characters. The $refTable and $refColumns parameters specify the table name and column name that the foreign key references. The $delete and $update parameters specify theON DELETE and ON UPDATE options in the SQL statement, respectively. Most DBMS support these options:RESTRICT, CASCADE, NO ACTION, SET DEFAULT, SET NULL. The query builder will properly quote the table name, index name and column name(s).

Below is an example showing how to add a foreign key constraint,

 addForeignKey() 方法建立并执行一个添加一个外键约束SQL语句。 $ name参数指定外键的名称。$table 和 $columns 参数指定表名和列名外键。如果有多个列，他们应该分开逗号字符。$refTable和$refColumns参数指定的外键引用的表名和列名。$delete和$update参数指定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 CASCADEaddForeignKey('fk_profile_user_id', 'tbl_profile', 'user_id',    'tbl_user', 'id', 'CASCADE', 'CASCADE')

dropForeignKey()

function dropForeignKey($name, $table)

The dropForeignKey() method builds and executes a SQL statement for dropping a foreign key constraint. The$name parameter specifies the name of the foreign key constraint to be dropped. The $table parameter specifies the name of the table that the foreign key is on. The query builder will quote the table name as well as the constraint names properly.

dropForeignKey() 方法建立并执行一个删除外键约束的SQL语句。 $name参数指定要删除外键约束的名称。 $table参数指定外键的表的名称。

Below is an example showing how to drop a foreign key constraint:

// 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)

The createIndex() method builds and executes a SQL statement for creating an index. The $name parameter specifies the name of the index to be created. The $table parameter specifies the name of the table that the index is on. The $column parameter specifies the name of the column to be indexed. And the $uniqueparameter specifies whether a unique index should be created. If the index consists of multiple columns, they must be separated by commas. The query builder will properly quote the table name, index name and column name(s).

createIndex() 方法的建立和执行一个创建索引的SQL语句。 $name参数指定要创建的索引的名称。 $table参数指定要建立索引的表的名称。$column参数指定要建立索引的列的名称。$unique参数指定是否创建一个唯一索引。如果该参数包含多个列，他们必须用逗号隔开。

Below is an example showing how to create an index:

// CREATE INDEX `idx_username` ON `tbl_user` (`username`)createIndex('idx_username', 'tbl_user')

dropIndex()

function dropIndex($name, $table)

The dropIndex() method builds and executes a SQL statement for dropping an index. The $name parameter specifies the name of the index to be dropped. The $table parameter specifies the name of the table that the index is on. The query builder will quote the table name as well as the index names properly.

dropIndex()方法的建立和执行一个删除索引的SQL语句。 $name参数指定要删除的索引的名称。$table参数指定索引所在的表的名称。

Below is an example showing how to drop an index:

// DROP INDEX `idx_username` ON `tbl_user`dropIndex('idx_username', 'tbl_user')