/****
Query Builder
translated by php攻城師
http://blog.csdn.net/phpgcs
Preparing Query Builder 准備 Query Builder
Building Data Retrieval Queries 構建數據查找查詢
Building Data Manipulation Queries 構建數據操作查詢
Building Schema Manipulation Queries構建數據結構操作查詢
****/
Yii Query Builder 提供了一個以面向對象的方式寫SQL表達式的方法。
允許開發者用 類的方法和屬性來 明確一個SQL表達式中的獨立的單元。然後將不同的單元組裝成一個合法的SQL表達式，進而讓DAO方法調用和執行。
下面是一個典型的使用Yii 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();
當你需要程序化地組裝一個SQL語句時，或者基於一些額外的邏輯在你的應用中時， 用Yii Query Builder是最好不過的了。
主要的好處是：
1, 允許程序化建立一個復雜的SQL表達式
2， 自動引用表明和列名來 防止跟SQL保留關鍵字以及特殊字符的沖突
3， 在可以的情況下引用參數值，使用參數綁定，從而降低了SQL 注入攻擊的風險。
4， 提供一定程度的DB抽象， 從而簡化了向不同DB平台遷移的工作。
並不是強制要使用 Query Builder， 事實上， 如果你的查詢很簡單，還是直接寫SQL 語句來的快捷方便。
注意： Query Builder 不可以被用語修改一個已經被定制了的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 和 Query Builder 混合使用！
/*****
1. Preparing Query Builder
translated by php攻城師
http://blog.csdn.net/phpgcs
*****/
Query Builder 是跟 CDbCommand 相關聯的， 主 DB 查詢類 定義在 DAO 中。
要開始使用 Query Builder， 我們創建一個 CDbCommand 實例如下：
$command = Yii::app()->db->createCommand();
我們使用 Yii::app()->db 獲得 DB connection, 然後用 CDbConnection::createCommand() 來創建實例.
注意：這裡我們不是想在DAO 中把一整個SQL語句給了 createCommand() ， 而是留空了。
這是因為我們將在後面使用 Query Builder 的方法來構建 這個SQL 表達式的不同部分。
/*****
2. Building Data Retrieval Queries
translated by php攻城師
http://blog.csdn.net/phpgcs
*****/
Data retrieval queries 指的是 SELECT SQL statements.
query builder 提供了一系列方法來建立一個 SELCET 語句的不同部分。
因為所有這些方法返回了 CDbCommand 實例 ，我們可以通過使用 方法鏈來調用它們。如下：
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
andWhere(): appends condition to the WHERE part of the query with AND operator
orWhere(): 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(): 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()
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'))
from()
function from($tables)
看例子：
// 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())
其中 $conditions 參數既可以是一個 (e.g. id=1) 也可以是一個如下格式的 array:
array(operator, operand1, operand2, ...)
operator 有以下幾個:
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).
這個方法不會做任何的 quoting 或者 escaping.
or: 類似 and
in: array('in', 'id', array(1,2,3)) =====> id IN (1,2,3). The method will properly quote the column name and escape values in the range.
not in:
like: operand 1 ====> a column or DB expression
operand 2 ====> a string or an array
array('like', 'name', '%tester%') =====> name LIKE '%tester%'
array('like', 'name', array('%test%', '%sample%')) =====> name LIKE '%test%' AND name LIKE '%sample%'.
The method will properly quote the column name and escape values in the range.
not like:
or like:
or not like:
看例子：
// 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 時， 我們需要確定 % and _ .
如果來自用戶的輸入，我們還應該 使用如下的代碼 來 過濾掉特殊字符 防止它們被當作通配符（wildcards）
$keyword=$_GET['q'];
// escape % and _ characters
$keyword=strtr($keyword, array('%'=>'\%', '_'=>'\_'));
$command->where(array('like', 'title', '%'.$keyword.'%'));
andWhere()
function andWhere($conditions, $params=array())
orWhere()
function orWhere($conditions, $params=array())
order()
function order($columns)
看例子：
// 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)
注意，一些 DBMS 不支持 LIMIT and OFFSET ， 但是我們的 the Query Builder 將會重寫整個SQL 語句來模擬 limit and offset.的功能。
看例子：
// LIMIT 10
limit(10)
// LIMIT 10 OFFSET 20
limit(10, 20)
// OFFSET 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)
注意：不像其他的 query builder 方法， 每次調用 join 將會被添加到 之前的join。
看例子：
// 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 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 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 (select * from tbl_profile)
union('select * from tbl_profile')
/*****
Executing Queries 執行查詢
****/
通過調用上面的 query builder 方法來構造 查詢， 我們就可以用 DAO 方法來執行這個查詢了。
例如：
$users = Yii::app()->db->createCommand()
->select('*')
->from('tbl_user')
->queryAll();
/****
復原 SQLs
****/
CDbCommand::getText().
$sql = Yii::app()->db->createCommand()
->select('*')
->from('tbl_user')
->text;
如果綁定有參數，我們也可以通過 CDbCommand::params property 來獲得參數。
其他語法 Building Queries
有時候通過方法鏈來構建查詢並不是最佳選擇。
舉例來說，下面的兩個表達是等價的， 假設 $command 代表了一個CDbCommand 對象：
$command->select(array('id', 'username'));
$command->select = array('id', 'username');
進一步 CDbConnection::createCommand() 方法可以把 array 作為參數.
如下：
$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 實例可以被用多次來構建幾個查詢。
在構建新查詢之前， 要使用 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
translated by php攻城師
http://blog.csdn.net/phpgcs
*****/
數據操作 指的是 在一個 DB 表中 inserting, updating and deleting 數據。
insert(): inserts a row into a table
update(): updates the data in a table
delete(): deletes the data from a table
insert()
function insert($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'=>'[email protected]',
));
update()
function update($table, $columns, $conditions='', $params=array())
看例子：
// 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())
看例子：
// 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
translated by php攻城師
http://blog.csdn.net/phpgcs
******/
除了常規的查找查詢和操作查詢， 還有一系列的方法 來構建和執行 可以 操作 數據庫結構的 SQL 查詢。
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
Abstract Data Types 抽象數據類型
實質上就是為了兼容不同的DBMS 而構建的數據類型， 一個通用的接口。
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.
createTable()
function createTable($table, $columns, $options=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)
// RENAME TABLE `tbl_users` TO `tbl_user`
renameTable('tbl_users', 'tbl_user')
dropTable()
function dropTable($table)
// DROP TABLE `tbl_user`
dropTable('tbl_user')
truncateTable()
function truncateTable($table)
// TRUNCATE TABLE `tbl_user`
truncateTable('tbl_user')
addColumn()
function addColumn($table, $column, $type)
// ALTER TABLE `tbl_user` ADD `email` varchar(255) NOT NULL
addColumn('tbl_user', 'email', 'string NOT NULL')
dropColumn()
function dropColumn($table, $column)
// ALTER TABLE `tbl_user` DROP COLUMN `location`
dropColumn('tbl_user', 'location')
renameColumn()
function renameColumn($table, $name, $newName)
// ALTER TABLE `tbl_users` CHANGE `name` `username` varchar(255) NOT NULL
renameColumn('tbl_user', 'name', 'username')
alterColumn()
// 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)
// 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)
// 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)
// CREATE INDEX `idx_username` ON `tbl_user` (`username`)
createIndex('idx_username', 'tbl_user', 'username')
dropIndex()
function dropIndex($name, $table)
// DROP INDEX `idx_username` ON `tbl_user`
dropIndex('idx_username', 'tbl_user')
