数据库之Query Builder

Yii的查询构造器提供了一个用面向对象的方法来构造SQL语句。他让开发人员可以用类的方法，属性来作为SQL语句的一部分。然后把不同部分组装到一个正确的SQL语句中，调用DAO的方法来执行。下面的例子演示如何用QB来构造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，或者是基于你的程序逻辑来拼装，那么QB就是最合适的。用QB的好处诸多：

●可以程序化的编写负责的SQL语句
●自动引用表名，列名，避免跟其他的关键词冲突
●引用参数banding的方法，防止SQL注入
●提供了抽象数据库，这可以让数据库迁移变得很简单。

QB的使用并非强制性的，实际上，如果你的查询非常的简单，那么直接写SQL语句更简单，也更快，没必要非得用QB。但是需要特别注意的一定，不要把QB跟纯的SQL混用。

1.1 准备QB

Yii QB 是在CDbCommand的选项里提供的。主要的查询类是在DAO里。

用QB之前，我们先实例化一个CDbCommand：

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

这行代码里，我们用Yii::app()->db获取数据库连接，然后调用createCommand()创建所需要的命名对象。注意到上面我们调用createCommand时，参数是空的。之前DAO的时候是直接把SQL语句初始化进去。现在这里留空，是因为我们要用QB来构造SQL语句。

1.2 创建查询SQL

查询语句涉及到SELECT的语句，QB提供了一整套的方法来构造SELECT的每个部分。由于这些方法都返回的是CDbCommand句柄，所以我们在用的时候，可以用串链的方式来做，就像本节开篇的那样作法。具体的方法就不翻译了，节约时间

接下来，我们一一解释如何使用这些构造器的方法。为了简便，默认底层数据库是Mysql，如果你使用的是其他数据库，table/column/value 这些在接下来的例子中或许会有所不同。

select()

function select($columns='*')

这个不解释了，列出一些例子。

// 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('id, username') 就等同于SELECT DISTINCT `id`, `username`

from()

function from($tables)

from这个方法用来指定查询的FROM部分。变量$tables指定了那些表将是用来选择的。这些表名可以用“,”隔开。表明也可以携带schema的前缀。

// 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：例如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： 同上
● in：第一项表示数据库的字段，第二项表示一个范围的数组。例如：array('in', 'id', array(1,2,3)) 等同于id IN (1,2,3)。
● not in：同上
● like：array('like', 'name', '%tester%')等同于nameLIKE '%tester%'
● not like：同上
● or like：跟like一致，只是为了连接多个like语句，加了一个or的操作符。
● 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%')))

注意，例如我们在用%或者是_这些特殊字符集的时候，要特别注意。如果这些模式是用户输入的，我们要用以下的方法来对待特殊字符：

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

order()

function order($columns)

这些命令如果懂SQL的，都不需要解释了，不懂的去补习。后面的开始只给例子了。

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

function limit($limit, $offset=null)
function offset($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)

例子

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

执行语句

在用上面的这些构造器方法，构造好SQL后，我们可以调用DAO来执行语句了。例如我们可以调用CDbCommand::queryRow()获取第一个符合结果的记录，也可以用CDbCommand::queryAll()来一次性获取所有的查询结果。例如：

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

访问SQL

除了执行构造器组装的SQL语句外，我们也可以调用CDbCommand::getText()来获取当前的SQL语句：

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

如果查询中有用到变量，可以使用CDbCommand::params来读取属性。

QB的语法替换

有时候，用QB的方法链构造语句并不是最好的方法。Yii 的QB提供了用简单对象赋值的方法。特别是在每个查询构造方法中都有相同名字的时候。给属性值就跟调用方法是一样的。例如，下面的两个例子效果是一样的。

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

甚至说，CDbConnection::createCommand()都可以用数组来做参数！这意味着我们可以按照下面的方法来执行（本人觉得不是很好的习惯，不知道什么特定的情况需要这样搞）

$row = Yii::app()->db->createCommand(array(
    'select' => array('id', 'username'),
    'from' => 'tbl user',
    'where' => 'id=:id',
    'params' => array(':id'=>1),
))->queryRow();

创建多次查询

一个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();

1.3 创建数据操作语句

操作语句就是增删改，会对数据库的数据进行更改的操作。具体就不介绍太多了

● 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'=>'tester@example.com',
));

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

1.4 创建SML查询（好像按照常规的分应该是属于DDL）

除了DML的操作以外，QB也提供了一些操作数据库架构的方法。主要有以下几种

● 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

抽象数据类型

Yii QB 提供了一些抽象数据类型，可以用来定义表的字段类型。不像传统的底层数据库系统类型，这些抽象数据类型是独立于底层数据库的。当用抽象数据类型来定义字段类型的时候，QB在执行的时候，会自动转换为底层数据库的物理类型。

QB提供的数据类型包括

 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:
oating 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.

题外话，这些都是基本的SQL，用的时候翻一下手册就可以了。都是很好理解的。

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

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

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

dropIndex()

function dropIndex($name, $table)