首页 > 代码库 > 数据库之Query Builder

数据库之Query Builder

Yii的查询构造器提供了一个用面向对象的方法来构造SQL语句。他让开发人员可以用类的方法,属性来作为SQL语句的一部分。然后把不同部分组装到一个正确的SQL语句中,调用DAO的方法来执行。下面的例子演示如何用QB来构造SQL语句

  1. $user = Yii::app()->db->createCommand()  
  2. ->select(‘id, username, profile‘)  
  3. ->from(‘tbl_user u‘)  
  4. ->join(‘tbl_profile p‘, ‘u.id=p.user_id‘)  
  5. ->where(‘id=:id‘, array(‘:id‘=>$id))  
  6. ->queryRow(); 

如果你是在程序里拼装SQL,或者是基于你的程序逻辑来拼装,那么QB就是最合适的。用QB的好处诸多:

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

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

1.1 准备QB

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

用QB之前,我们先实例化一个CDbCommand:

  1. $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()

  1. function select($columns=‘*‘) 

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

  1. // SELECT *  
  2. select()  
  3. // SELECT `id`, `username`  
  4. select(‘id, username‘)  
  5. // SELECT `tbl user`.`id`, `username` AS `name`  
  6. select(‘tbl user.id, username as name‘)  
  7. // SELECT `id`, `username`  
  8. select(array(‘id‘, ‘username‘))  
  9. // SELECT `id`, count(*) as num  
  10. select(array(‘id‘, ‘count(*) as num‘)) 

selectDistinct()

  1. function selectDistinct($columns) 

这个就是在选择的时候,去掉重复的值。例如,selectDistinct(‘id, username‘) 就等同于SELECT DISTINCT `id`, `username`

from()

  1. function from($tables) 

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

  1. // FROM `tbl user`  
  2. from(‘tbl user‘)  
  3. // FROM `tbl user` `u`, `public`.`tbl profile` `p`  
  4. from(‘tbl user u, public.tbl profile p‘)  
  5. // FROM `tbl user`, `tbl profile`  
  6. from(array(‘tbl user‘, ‘tbl profile‘))  
  7. // FROM `tbl user`, (select * from tbl profile) p  
  8. from(array(‘tbl user‘, ‘(select * from tbl profile) p‘)) 

where()

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

where这个方法,指定了查询中的WHERE这个部分。变量$conditions指定了查询条件,$params 指定了所有用到的变量。$conditions可以是一个简单的字符串(如id=1),也可以是一个数组的形式:

  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:同上

以下是一些具体的实例:

  1. // WHERE id=1 or id=2  
  2. where(‘id=1 or id=2‘)  
  3. // WHERE id=:id1 or id=:id2  
  4. where(‘id=:id1 or id=:id2‘, array(‘:id1‘=>1, ‘:id2‘=>2))  
  5. // WHERE id=1 OR id=2  
  6. where(array(‘or‘, ‘id=1‘, ‘id=2‘))  
  7. // WHERE id=1 AND (type=2 OR type=3)  
  8. where(array(‘and‘, ‘id=1‘, array(‘or‘, ‘type=2‘, ‘type=3‘)))  
  9. // WHERE `id` IN (1, 2)  
  10. where(array(‘in‘, ‘id‘, array(1, 2))  
  11. // WHERE `id` NOT IN (1, 2)  
  12. where(array(‘not in‘, ‘id‘, array(1,2)))  
  13. // WHERE `name` LIKE ‘%Qiang%‘  
  14. where(array(‘like‘, ‘name‘, ‘%Qiang%‘))  
  15. // WHERE `name` LIKE ‘%Qiang‘ AND `name` LIKE ‘%Xue‘  
  16. where(array(‘like‘, ‘name‘, array(‘%Qiang‘, ‘%Xue‘)))  
  17. // WHERE `name` LIKE ‘%Qiang‘ OR `name` LIKE ‘%Xue‘  
  18. where(array(‘or like‘, ‘name‘, array(‘%Qiang‘, ‘%Xue‘)))  
  19. // WHERE `name` NOT LIKE ‘%Qiang%‘  
  20. where(array(‘not like‘, ‘name‘, ‘%Qiang%‘))  
  21. // WHERE `name` NOT LIKE ‘%Qiang%‘ OR `name` NOT LIKE ‘%Xue%‘  
  22. where(array(‘or not like‘, ‘name‘, array(‘%Qiang%‘, ‘%Xue%‘))) 

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

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

order()

  1. function order($columns) 

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

  1. // ORDER BY `name`, `id` DESC  
  2. order(‘name, id desc‘)  
  3. // ORDER BY `tbl profile`.`name`, `id` DESC  
  4. order(array(‘tbl profile.name‘, ‘id desc‘)) 

limit() and o set()

  1. function limit($limit, $offset=null)  
  2. function offset($offset) 

例子:

  1. // LIMIT 10  
  2. limit(10)  
  3. // LIMIT 10 OFFSET 20  
  4. limit(10, 20)  
  5. // OFFSET 20  
  6. offset(20) 

join() and its variants

  1. function join($table, $conditions, $params=array())  
  2. function leftJoin($table, $conditions, $params=array())  
  3. function rightJoin($table, $conditions, $params=array())  
  4. function crossJoin($table)  
  5. function naturalJoin($table) 

例子

  1. // JOIN `tbl profile` ON user id=id  
  2. join(‘tbl profile‘, ‘user id=id‘)  
  3. // LEFT JOIN `pub`.`tbl profile` `p` ON p.user id=id AND type=1  
  4. leftJoin(‘pub.tbl profile p‘, ‘p.user id=id AND type=:type‘, array(‘:type‘=>1)) 

group()

  1. function group($columns) 

例子

  1. // GROUP BY `name`, `id`  
  2. group(‘name, id‘)  
  3. // GROUP BY `tbl profile`.`name`, `id`  
  4. group(array(‘tbl profile.name‘, ‘id‘) 

having()

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

例子

  1. // HAVING id=1 or id=2  
  2. having(‘id=1 or id=2‘)  
  3. // HAVING id=1 OR id=2  
  4. having(array(‘or‘, ‘id=1‘, ‘id=2‘)) 

union()

  1. function union($sql) 

例子

  1. // UNION (select * from tbl profile)  
  2. union(‘select * from tbl profile‘) 

执行语句

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

  1. $users = Yii::app()->db->createCommand()  
  2. ->select(‘*‘)  
  3. ->from(‘tbl user‘)  
  4. ->queryAll(); 

访问SQL

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

  1. $sql = Yii::app()->db->createCommand()  
  2. ->select(‘*‘)  
  3. ->from(‘tbl user‘)  
  4. ->text; 

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

QB的语法替换

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

  1. $command->select(array(‘id‘, ‘username‘));  
  2. $command->select = array(‘id‘, ‘username‘); 

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

  1. $row = Yii::app()->db->createCommand(array(  
  2. ‘select‘ => array(‘id‘, ‘username‘),  
  3. ‘from‘ => ‘tbl user‘,  
  4. ‘where‘ => ‘id=:id‘,  
  5. ‘params‘ => array(‘:id‘=>1),  
  6. ))->queryRow(); 

创建多次查询

一个CDbCommand的对象,可以用来多次执行查询动作。在执行新的查询语句之前,必须要调用CDbCommand::reset()先清除之前的查询语句。

  1. $command = Yii::app()->db->createCommand();  
  2. $users = $command->select(‘*‘)->from(‘tbl users‘)->queryAll();  
  3. $command->reset(); // clean up the previous query  
  4. $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()

  1. function insert($table, $columns) 
  1. // build and execute the following SQL:  
  2. // INSERT INTO `tbl user` (`name`, `email`) VALUES (:name, :email)  
  3. $command->insert(‘tbl user‘, array(  
  4.     ‘name‘=>‘Tester‘,  
  5.     ‘email‘=>‘tester@example.com‘,  
  6. )); 

update()

  1. function update($table, $columns, $conditions=‘‘, $params=array()) 
  1. // build and execute the following SQL:  
  2. // UPDATE `tbl user` SET `name`=:name WHERE id=:id  
  3. $command->update(‘tbl user‘, array(  
  4.     ‘name‘=>‘Tester‘,  
  5. ), ‘id=:id‘, array(‘:id‘=>1)); 

delete()

  1. function delete($table, $conditions=‘‘, $params=array()) 
  1. // build and execute the following SQL:  
  2. // DELETE FROM `tbl user` WHERE id=:id  
  3. $command->delete(‘tbl user‘, ‘id=:id‘, array(‘:id‘=>1)); 

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

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

  1. ● createTable(): creates a table  
  2. ● renameTable(): renames a table  
  3. ● dropTable(): drops a table  
  4. ● truncateTable(): truncates a table  
  5. ● addColumn(): adds a table column  
  6. ● renameColumn(): renames a table column  
  7. ● alterColumn(): alters a table column  
  8. ● dropColumn(): drops a table column  
  9. ● createIndex(): creates an index  
  10. ● dropIndex(): drops an index 

抽象数据类型

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

QB提供的数据类型包括

  1.  pk: a generic primary key type, will be converted into int(11) NOT NULL AUTO  
  2. INCREMENT PRIMARY KEY for MySQL;  
  3.  string: string type, will be converted into varchar(255) for MySQL;  
  4.  text: text type (long string), will be converted into text for MySQL;  
  5.  integer: integer type, will be converted into int(11) for MySQL;  
  6.  float:   
  7. oating number type, will be converted into float for MySQL;  
  8.  decimal: decimal number type, will be converted into decimal for MySQL;  
  9.  datetime: datetime type, will be converted into datetime for MySQL;  
  10.  timestamp: timestamp type, will be converted into timestamp for MySQL;  
  11.  time: time type, will be converted into time for MySQL;  
  12. date: date type, will be converted into date for MySQL;  
  13.  binary: binary data type, will be converted into blob for MySQL;  
  14.  boolean: boolean type, will be converted into tinyint(1) for MySQL;  
  15.  money: money/currency type, will be converted into decimal(19,4) for MySQL. This  
  16. type has been available since version 1.1.8. 

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

createTable()

  1. function createTable($table, $columns, $options=null) 
  1. // CREATE TABLE `tbl user` (  
  2. // `id` int(11) NOT NULL AUTO INCREMENT PRIMARY KEY,  
  3. // `username` varchar(255) NOT NULL,  
  4. // `location` point  
  5. // ) ENGINE=InnoDB  
  6. createTable(‘tbl user‘, array(  
  7.         ‘id‘ => ‘pk‘,  
  8.         ‘username‘ => ‘string NOT NULL‘,  
  9.         ‘location‘ => ‘point‘,  
  10.     ), ‘ENGINE=InnoDB‘) 

renameTable()

  1. function renameTable($table, $newName) 
  1. // RENAME TABLE `tbl users` TO `tbl user`  
  2. renameTable(‘tbl users‘, ‘tbl user‘) 

dropTable()

  1. function dropTable($table) 
  1. // DROP TABLE `tbl user`  
  2. dropTable(‘tbl user‘) 

truncateTable()

  1. function truncateTable($table) 
  1. // TRUNCATE TABLE `tbl user`  
  2. truncateTable(‘tbl user‘) 

addColumn()

  1. function addColumn($table, $column, $type) 
  1. // ALTER TABLE `tbl user` ADD `email` varchar(255) NOT NULL  
  2. addColumn(‘tbl user‘, ‘email‘, ‘string NOT NULL‘) 

dropColumn()

  1. function dropColumn($table, $column) 
  1. // ALTER TABLE `tbl user` DROP COLUMN `location`  
  2. dropColumn(‘tbl user‘, ‘location‘) 

renameColumn()

  1. function renameColumn($table, $name, $newName) 
  1. // ALTER TABLE `tbl users` CHANGE `name` `username` varchar(255) NOT NULL  
  2. renameColumn(‘tbl user‘, ‘name‘, ‘username‘) 

alterColumn()

  1. function alterColumn($table, $column, $type) 
  1. // ALTER TABLE `tbl user` CHANGE `username` `username` varchar(255) NOT NULL  
  2. alterColumn(‘tbl user‘, ‘username‘, ‘string NOT NULL‘) 

addForeignKey()

  1. function addForeignKey($name, $table, $columns,  
  2. $refTable, $refColumns, $delete=null, $update=null) 
  1. // ALTER TABLE `tbl profile` ADD CONSTRAINT `fk profile user id`  
  2. // FOREIGN KEY (`user id`) REFERENCES `tbl user` (`id`)  
  3. // ON DELETE CASCADE ON UPDATE CASCADE  
  4. addForeignKey(‘fk profile user id‘, ‘tbl profile‘, ‘user id‘,  
  5. ‘tbl user‘, ‘id‘, ‘CASCADE‘, ‘CASCADE‘) 

dropForeignKey()

  1. function dropForeignKey($name, $table) 
  1. // ALTER TABLE `tbl profile` DROP FOREIGN KEY `fk profile user id`  
  2. dropForeignKey(‘fk profile user id‘, ‘tbl profile‘) 

createIndex()

  1. function createIndex($name, $table, $column, $unique=false) 
  1. // CREATE INDEX `idx username` ON `tbl user` (`username`)  
  2. createIndex(‘idx username‘, ‘tbl user‘) 

dropIndex()

  1. function dropIndex($name, $table) 
    1. // DROP INDEX `idx username` ON `tbl user`  
    2. dropIndex(‘idx username‘, ‘tbl user‘) 

数据库之Query Builder