声明:本文翻译自yiiframework官网的Query Builder文档,yii框架版本v1.1,网址:“http://www.yiiframework.com/doc/guide/1.1/zh_cn/database.query-builder”。
Query Builder,我们可以翻译成(数据库)查询助手,不知道有没有官方的翻译方法,暂且先这么翻译吧。
以下开始翻译文档正文。
----------------------------------------------------------------------------------------------------------------------------------------------------
- 查询助手初体验
- 创建数据检索查询
- 创建数据处理查询
- 创建表操作查询
Yii查询助手提供了一种完全面向对象的方式来编写SQL语句。它使得开发者可以使用类的方法和属性来操作SQL语句的各个组成部分。查询助手会将这些不同的SQL语句组成部分组合在一起,形成一个完整、有效的SQL语句,并调用DAO的相关方法来执行。下面展示了一个典型的使用查询助手来创建一个SELECT语句的用法:
<span style="font-family:Tahoma;font-size:12px;"><span style="font-size:12px;"><span style="font-family:Tahoma;font-size:12px;">$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();</span></span></span>
查询助手的最佳使用场景是,程序上地组装一条SQL语句,或者基于你的应用上的一些条件逻辑。(译者注:原文这话有点别扭,我个人的理解是,在我们的程序中,我们需要根据一些变量带入SQL语句执行相关数据库操作,通过查询助手,我们可以轻松做到这一点,并且不用编写完整的SQL语句,以免造成语法错误导致程序无法执行。)使用查询助手的好处主要包括:
- 它可以帮助我们在程序上创建复杂的SQL语句。
- 它自动引入数据表名和字段名,以免出现SQL预留字和特殊字符的冲突问题。
- 在必要的时候,它会引入参数值并绑定参数,以降低SQL注入攻击的风险。
- 它提供了一定程度上的数据库抽象,来简化在不同的数据库平台之间的迁移工作。
使用查询助手并不是强制和必须的,实际上,如果你的查询非常简单,直接编写SQL语句反而更简单快速。
#########################################################################################
注意:查询助手不能用来变更一个已存在的通过SQL语句来执行的查询。举个例子,下面的代码将不会起作用:
<span style="font-family:Tahoma;font-size:12px;"><span style="font-size:12px;">$command = Yii::app()->db->createCommand('SELECT * FROM tbl_user');
// the following line will NOT append WHERE clause to the above SQL</span></span>
<span style="font-family:Tahoma;font-size:12px;"><span style="font-size:12px;">//译者注:下面一行的代码并不会起作用,因为上面一行的查询指令是通过SQL语句来创建的,不能和查询助手的方法进行混用。
$command->where('id=:id', array(':id'=>$id));</span></span>
再强调一点,不要把查询助手和普通的SQL语句进行混用。
#########################################################################################
1. 查询助手初体验
<span style="font-family:Tahoma;font-size:12px;"><span style="font-size:12px;"><span style="font-family:Tahoma;font-size:12px;">$command = Yii::app()->db->createCommand();</span></span></span>
2. 创建数据检索查询
- select(): 指定查询的SELECT部分
- selectDistinct(): 指定查询的SELECT部分,并开启DISTINCT标识
- from():指定查询的FROM部分
- where():指定查询的WHERE部分
- andWhere(): 在WHERE部分使用AND操作符添加一个条件
- orWhere():在WHERE部分使用OR操作符添加一个条件
- join(): 添加内联查询部分
- leftJoin(): 添加左联查询部分
- rightJoin():添加右联查询部分
- crossJoin():添加CROSS JOIN查询部分
- naturalJoin():添加NATURAL JOIN查询部分
- group():添加GROUP BY查询部分
- having():添加HAVING查询部分
- order():添加ORDER查询部分
- limit():添加LIMIT查询部分
- offset():添加OFFSET查询部分
- union(): 添加联合查询部分
select()
<span style="font-family:Tahoma;font-size:12px;"><span style="font-size:12px;">function select($columns='*')</span></span>
<span style="font-family:Tahoma;font-size:12px;"><span style="font-size:12px;">// 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'))</span></span>
selectDistinct()
<span style="font-family:Tahoma;font-size:12px;"><span style="font-size:12px;">function selectDistinct($columns)</span></span>
<span style="font-family:Tahoma;font-size:12px;"><span style="font-size:12px;">SELECT DISTINCT `id`, `username`</span></span>
from()
<span style="font-family:Tahoma;font-size:12px;"><span style="font-size:12px;">function from($tables)</span></span>
<span style="font-family:Tahoma;font-size:12px;"><span style="font-size:12px;">// 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'))</span></span>
where()
<span style="font-family:Tahoma;font-size:12px;"><span style="font-size:12px;">function where($conditions, $params=array())</span></span>
<span style="font-family:Tahoma;font-size:12px;"><span style="font-size:12px;">array(operator, operand1, operand2, ...)</span></span>
- and: operand会被AND操作符连接在一起。举个例子,
array('and', 'id=1', 'id=2')
将会生成id=1 AND id=2。如果operand是一个数组,它将会使用相同的规则转换成字符串。例如,
array('and', 'type=1', array('or', 'id=1', 'id=2'))
将会生成type=1 AND (id=1 OR id=2)。该方法不会做任何的编码或引用。
- or: 用法和and类似,只不过连接符变成了OR。
- in: operand 1应该是一个字段或DB表达式,operand 2是一个表示 operand1的值所在范围的一个数组。举个例子,
array('in', 'id', array(1,2,3))
将会生成id IN (1,2,3)。该方法会正确的引用字段名并对IN范围值进行编码。
- not in: 类似in,in表示包含在某个范围内的值,not in指不包含在某个范围内的值。
- like: operand 1应该是一个字段或DB表达式,operand 2是一个表示 operand1的值所在范围的一个数组或字符串。举个例子,
array('like', 'name', '%tester%')
将会生成name LIKE '%tester%'。如果operand 2是一个数组,那么将会生成多个LIKE判断值,这些判断值通过AND连接在一起。比如,
array('like', 'name', array('%test%', '%sample%'))
将会生成name LIKE '%test%' AND name LIKE '%sample%'。该方法会正确的引用字段名并对IN范围值进行编码。
- not like: 类似like。只不过对like的判断范围取反。
- or like: 类似like,只不过当like的operand 2为一个数组时,连接该数组中的多个判断值的不是AND,而是OR。
- or not like: 类似not like,只不过当like的operand 2为一个数组时,连接该数组中的多个判断值的不是AND,而是OR。
<span style="font-family:Tahoma;font-size:12px;"><span style="font-size:12px;">// 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%')))</span></span>
<span style="font-family:Tahoma;font-size:12px;"><span style="font-size:12px;">$keyword=$_GET['q'];
// escape % and _ characters
$keyword=strtr($keyword, array('%'=>'\%', '_'=>'\_'));
$command->where(array('like', 'title', '%'.$keyword.'%'));</span></span>
andWhere()
<span style="font-family:Tahoma;font-size:12px;"><span style="font-size:12px;">function andWhere($conditions, $params=array())</span></span>
orWhere()
<span style="font-family:Tahoma;font-size:12px;"><span style="font-size:12px;">function orWhere($conditions, $params=array())</span></span>
order()
<span style="font-family:Tahoma;font-size:12px;"><span style="font-size:12px;">function order($columns)</span></span>
<span style="font-family:Tahoma;font-size:12px;"><span style="font-size:12px;">// ORDER BY `name`, `id` DESC
order('name, id desc')
// ORDER BY `tbl_profile`.`name`, `id` DESC
order(array('tbl_profile.name', 'id desc'))</span></span>
limit() 和 offset()
<span style="font-family:Tahoma;font-size:12px;"><span style="font-size:12px;">function limit($limit, $offset=null)
function offset($offset)</span></span>
<span style="font-family:Tahoma;font-size:12px;"><span style="font-size:12px;">// LIMIT 10
limit(10)
// LIMIT 10 OFFSET 20
limit(10, 20)
// OFFSET 20
offset(20)</span></span>
join()和相关变体
<span style="font-family:Tahoma;font-size:12px;"><span style="font-size:12px;">function join($table, $conditions, $params=array())
function leftJoin($table, $conditions, $params=array())
function rightJoin($table, $conditions, $params=array())
function crossJoin($table)
function naturalJoin($table)</span></span>
INNER JOIN
, LEFT OUTER JOIN
,RIGHT OUTER JOIN
, CROSS JOIN
, NATURAL JOIN与其他表进行关联查询。
<span style="font-family:Tahoma;font-size:12px;"><span style="font-size:12px;">// 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))</span></span>
group()
<span style="font-family:Tahoma;font-size:12px;"><span style="font-size:12px;">function group($columns)</span></span>
<span style="font-family:Tahoma;font-size:12px;"><span style="font-size:12px;">// GROUP BY `name`, `id`
group('name, id')
// GROUP BY `tbl_profile`.`name`, `id`
group(array('tbl_profile.name', 'id'))</span></span>
having()
<span style="font-family:Tahoma;font-size:12px;"><span style="font-size:12px;">function having($conditions, $params=array())</span></span>
<span style="font-family:Tahoma;font-size:12px;"><span style="font-size:12px;">// 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'))</span></span>
union()
<span style="font-family:Tahoma;font-size:12px;"><span style="font-size:12px;">function union($sql)</span></span>
<span style="font-family:Tahoma;font-size:12px;"><span style="font-size:12px;">// UNION (select * from tbl_profile)
union('select * from tbl_profile')</span></span>
执行查询
<span style="font-family:Tahoma;font-size:12px;">$users = Yii::app()->db->createCommand()
->select('*')
->from('tbl_user')
->queryAll();</span>
获取SQL语句
<span style="font-family:Tahoma;font-size:12px;">$sql = Yii::app()->db->createCommand()
->select('*')
->from('tbl_user')
->text;</span>
如果绑定了任何参数到一个查询操作上,我们可以通过CDbCommand::params来获取这些参数。
创建查询的替代语法
<span style="font-family:Tahoma;font-size:12px;">$command->select(array('id', 'username'));
$command->select = array('id', 'username');</span>
此外,CDbConnection::createCommand()方法可以使用一个数组作为参数。数组中的键值对将被用来初始化创建的CDbCommand实例的属性。这意味着,我们可以使用如下代码创建一个查询:
<span style="font-family:Tahoma;font-size:12px;">$row = Yii::app()->db->createCommand(array(
'select' => array('id', 'username'),
'from' => 'tbl_user',
'where' => 'id=:id',
'params' => array(':id'=>1),
))->queryRow();</span>
创建多个查询
<span style="font-family:Tahoma;font-size:12px;">$row = Yii::app()->db->createCommand(array(
'select' => array('id', 'username'),
'from' => 'tbl_user',
'where' => 'id=:id',
'params' => array(':id'=>1),
))->queryRow();</span>
3. 创建数据处理查询
- [insert()|CDbCommand::insert]: 在表中插入一行
- [update()|CDbCommand::update]: 更新表中数据
- [delete()|CDbCommand::delete]: 从表中删除数据
insert()
<span style="font-family:Tahoma;font-size:12px;">function insert($table, $columns)</span>
[insert()|CDbCommand::insert]方法创建并执行一条INSERT SQL语句。
<span style="font-family:Tahoma;font-size:12px;">// build and execute the following SQL:
// INSERT INTO `tbl_user` (`name`, `email`) VALUES (:name, :email)
$command->insert('tbl_user', array(
'name'=>'Tester',
'email'=>'tester@example.com',
));</span>
update()
function update($table, $columns, $conditions='', $params=array())
update()方法创建并执行了一个UPDATE操作的SQL语句。
// 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()方法创建并执行了一个DELETE操作的SQL语句。// build and execute the following SQL: // DELETE FROM `tbl_user` WHERE id=:id $command->delete('tbl_user', 'id=:id', array(':id'=>1));
4. 数据库对象操作
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
- addForeignKey(): adds a foreign key (available since 1.1.6)
- dropForeignKey(): drops a foreign key (available since 1.1.6)
- 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.
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 intoint(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
for MySQL; -
string
: string type, will be converted intovarchar(255)
for MySQL; -
text
: text type (long string), will be converted intotext
for MySQL; -
integer
: integer type, will be converted intoint(11)
for MySQL; -
float
: floating number type, will be converted intofloat
for MySQL; -
decimal
: decimal number type, will be converted intodecimal
for MySQL; -
datetime
: datetime type, will be converted intodatetime
for MySQL; -
timestamp
: timestamp type, will be converted intotimestamp
for MySQL; -
time
: time type, will be converted intotime
for MySQL; -
date
: date type, will be converted intodate
for MySQL; -
binary
: binary data type, will be converted intoblob
for MySQL; -
boolean
: boolean type, will be converted intotinyint(1)
for MySQL; -
money
: money/currency type, will be converted intodecimal(19,4)
for MySQL. This type has been available since version 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.
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.
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).
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=InnoDB createTable('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 $table
parameter 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:
// 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:
// 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 $table
parameter 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:
// 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 $table
parameter 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:
// ALTER TABLE `tbl_user` ADD `email` varchar(255) NOT NULL addColumn('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 $table
parameter 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:
// 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 $table
parameter 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:
// ALTER TABLE `tbl_users` CHANGE `name` `username` varchar(255) NOT NULL renameColumn('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 $table
parameter 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:
// 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)
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 the ON 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,
// 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)
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.
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 $unique
parameter 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).
Below is an example showing how to create an index:
// CREATE INDEX `idx_username` ON `tbl_user` (`username`) createIndex('idx_username', 'tbl_user', 'username')
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.
Below is an example showing how to drop an index:
// DROP INDEX `idx_username` ON `tbl_user` dropIndex('idx_username', 'tbl_user')