« Module_Database_Expression::__toString

Module_Database_QueryBuilder::__construct »

选择语言 :

Module_Database_QueryBuilder

SQL构造核心类

API - Module_Database_QueryBuilder

Database_QueryBuilder::__construct

Database_QueryBuilder::compile - 解析为SQL语句

Database_QueryBuilder::get_builder - 获取Builder配置

Database_QueryBuilder::set_builder - 设置Builder信息

Database_QueryBuilder::distinct - 构成查询 SELECT DISTINCT

Database_QueryBuilder::select - select(c1, c2, c3,......)

Database_QueryBuilder::select_array - Choose the columns to select from, using an array.

Database_QueryBuilder::select_max - 查询最大值

Database_QueryBuilder::select_min - 查询平均值

Database_QueryBuilder::select_avg - 查询平均值

Database_QueryBuilder::select_sum - 查询总和

Database_QueryBuilder::select_adv - 高级查询方式

Database_QueryBuilder::columns - Set the columns that will be inserted.

Database_QueryBuilder::values - 加入多条数据

Database_QueryBuilder::set - 为update,insert设置数据

Database_QueryBuilder::value - Set the value of a single column.

Database_QueryBuilder::value_increment - 数据递增

Database_QueryBuilder::value_decrement - 数据递减

Database_QueryBuilder::table - Sets the table to update.

Database_QueryBuilder::from - from(tableA,tableB,...)

Database_QueryBuilder::join - Adds addition tables to "JOIN ...".

Database_QueryBuilder::on - Adds "ON ..." conditions for the last created JOIN statement.

Database_QueryBuilder::group_by - group_by(c1,c2,c3,.....)

Database_QueryBuilder::having - Alias of and_having()

Database_QueryBuilder::and_having - Creates a new "AND HAVING" condition for the query.

Database_QueryBuilder::or_having - Creates a new "OR HAVING" condition for the query.

Database_QueryBuilder::having_open - Alias of and_having_open()

Database_QueryBuilder::and_having_open - Opens a new "AND HAVING (...)" grouping.

Database_QueryBuilder::or_having_open - Opens a new "OR HAVING (...)" grouping.

Database_QueryBuilder::having_close - Closes an open "AND HAVING (...)" grouping.

Database_QueryBuilder::and_having_close - Closes an open "AND HAVING (...)" grouping.

Database_QueryBuilder::or_having_close - Closes an open "OR HAVING (...)" grouping.

Database_QueryBuilder::offset - Start returning results after "OFFSET ..."

Database_QueryBuilder::reset - 重设数据

Database_QueryBuilder::in

Database_QueryBuilder::notin

Database_QueryBuilder::where - Alias of and_where()

Database_QueryBuilder::and_where - Creates a new "AND WHERE" condition for the query.

Database_QueryBuilder::or_where - Creates a new "OR WHERE" condition for the query.

Database_QueryBuilder::where_open - Alias of and_where_open()

Database_QueryBuilder::and_where_open - Opens a new "AND WHERE (...)" grouping.

Database_QueryBuilder::or_where_open - Opens a new "OR WHERE (...)" grouping.

Database_QueryBuilder::where_close - Closes an open "AND WHERE (...)" grouping.

Database_QueryBuilder::and_where_close - Closes an open "AND WHERE (...)" grouping.

Database_QueryBuilder::or_where_close - Closes an open "OR WHERE (...)" grouping.

Database_QueryBuilder::order_by - Applies sorting with "ORDER BY ..."

Database_QueryBuilder::limit - Return up to "LIMIT ..." results

Database_QueryBuilder::like - 返回 "LIKE ..."

Database_QueryBuilder::or_like - 返回 "OR LIKE ..."

Database_QueryBuilder::mod - 返回 "$column MOD $mod = $value"

Database_QueryBuilder::or_mod - 返回 "OR $column MOD $mod = $value"

Database_QueryBuilder::use_index - 使用指定索引

Database_QueryBuilder::force_index - 强制使用指定索引

Database_QueryBuilder::ignore_index - 或略指定索引

Database_QueryBuilder::recovery_last_builder - 恢复最后查询或reset时的Builder数据

Database_QueryBuilder::expr_value - 创建一个不会被过滤处理的字符串

非Public项目
Database_QueryBuilder::$_builder - Builder数组

Database_QueryBuilder::$_builder_bak - Builder备份

Database_QueryBuilder::$_last_join

author: 呼吸二氧化碳 jonwang@myqee.com
category: MyQEE
package: Module
subpackage: Database
copyright: Copyright © 2008-2013 myqee.com
license: http://www.myqee.com/license.html

$this->__construct( )

$this->compile( $db )

解析为SQL语句

参数列表

参数	类型	描述	默认值
`$db`	`object`	Database instance

返回值

string

$this->get_builder( )

获取Builder配置

可用 $builder = & $obj->get_builder(); 获取到内存指针

返回值

array

$this->set_builder( $builder )

设置Builder信息

 // 设置一样的builder
 $db->from('mytable')->where('id>', 10)->limit(10)->order_by('id', 'DESC');

 // 获取当前builder
 $builder = $db->get_builder();

 // 执行查询
 $data1 = $db->where('type', 1)->get()->as_array();
 echo $db->last_query();     //SELECT * FROM `mytable` WHERE `id` > 10 AND `type` = '1' ORDER BY `id` DESC LIMIT 10

 // 将前面获取的builder重新设置回去
 $db->set_builder($builder);

 // 再次执行另外一个附加条件的查询
 $data2 = $db->where('type', 3)->get()->as_array();
 echo $db->last_query();     //SELECT * FROM `mytable` WHERE `id` > 10 AND `type` = '3' ORDER BY `id` DESC LIMIT 10

参数列表

参数	类型	描述	默认值
`$builder`	`array`	Builder信息数组，不必完整的，建议通过get_builder()获取后设置

返回值

Database

$this->distinct( $value = true)

构成查询 SELECT DISTINCT 如果传的是字符串则构造出 SELECT DISTINCT(test) as test 这样的查询(MySQL)

参数列表

参数	类型	描述	默认值
`$value`	`boolean`	Enable or disable distinct columns	bool true

返回值

Database

$this->select( $columns )

select(c1, c2, c3,......)

如果查询是SELECT * 则不需要设置，系统会自动处理

 $db->select('id', 'username')->from('members')->get()->as_array();
 echo $db->last_query();     //SELECT `id`, `username` FROM `members`;

 $db->select('db1.id', 'db2.username')->from('members as db1')->join('mydb as db2')->on('db1.id', 'db2.mid')->get()->as_array();
 echo $db->last_query();     //SELECT `db1`.`id`, `db2`.`username` FROM `members` AS `db1` JOIN ON `db1`.`id` = `db2`.`mid`;

 // 使用Database::expr_value()方法可以传入一个不被解析的字符串
 $db->select(Database::expr_value('SUM("id") as `id`'))->from('members')->get()->as_array();
 echo $db->last_query();     //SELECT SUM("id") as `id` FROM `members`;

参数列表

参数	类型	描述	默认值
`$columns`	`mixed`	Column name or array($column, $alias) or object

返回值

Database

$this->select_array( $columns )

Choose the columns to select from, using an array.

参数列表

参数	类型	描述	默认值
`$columns`	`array`	List of column names or aliases

返回值

Database

$this->select_max( $conlumn )

查询最大值

$db->select_max('test')->from('db')->group_by('class_id')->get()->as_array();

参数列表

参数	类型	描述	默认值
`$conlumn`	`string`	$conlumn

返回值

Database

$this->select_min( $conlumn )

查询平均值

$db->select_min('test')->from('db')->group_by('class_id')->get()->as_array();

参数列表

参数	类型	描述	默认值
`$conlumn`	`string`	$conlumn

返回值

Database

$this->select_avg( $conlumn )

查询平均值

$db->select_avg('test')->from('db')->group_by('class_id')->get()->as_array();

参数列表

参数	类型	描述	默认值
`$conlumn`	`string`	$conlumn

返回值

Database

$this->select_sum( $conlumn )

查询总和

$db->select_sum('test')->from('db')->group_by('class_id')->get()->as_array();

参数列表

参数	类型	描述	默认值
`$conlumn`	`string`	$conlumn

返回值

Database

$this->select_adv( $conlumn , $type , $opt1 = null, $opt2 = null)

高级查询方式

需要相应接口支持，目前支持MongoDB的aggregation框架Group查询：$sum,$max,$min,$avg,$last,$first等，详情见 http://docs.mongodb.org/manual/reference/aggregation/group/ MySQL支持sum,max,min,svg等

$db->select_adv('test','max'); //查询最大值 $db->seleve_adv('test','sum',3); //查询+3的总和

参数列表

参数	类型	描述	默认值
`$conlumn`	`string`	$conlumn
`$type`	`string`	$opt
`$opt1`	`unknown`		null
`$opt2`	`unknown`		null

返回值

Database

$this->columns( $columns )

Set the columns that will be inserted.

参数列表

参数	类型	描述	默认值
`$columns`	`array`	Column names

返回值

Database

$this->values( $values )

加入多条数据

// 例1
$v1 = array('k1'=>1,'k2'=>1);
$v2 = array('k1'=>2,'k2'=>1);
$v3 = array('k1'=>3,'k2'=>1);
$db->values($v1,$v2,$v3);        //加入3行数据

// 例2
$values = array();
$values[] = array('k1'=>1,'k2'=>1);
$values[] = array('k1'=>2,'k2'=>1);
$values[] = array('k1'=>3,'k2'=>1);
$db->values($values);            //加入3行数据,等同上面的效果

参数列表

参数	类型	描述	默认值
`$values`	`array`	Values list

返回值

Database

$this->set( $pairs )

为update,insert设置数据

参数列表

参数	类型	描述	默认值
`$pairs`	`array`	Associative (column => value) list

返回值

Database

$this->value( $column , $value , $op = '=')

Set the value of a single column.

参数列表

参数	类型	描述	默认值
`$column`	`mixed`	Table name or array($table, $alias) or object
`$value`	`mixed`	Column value
`$op`	`string`	=\|+\|-	string(1) "="

返回值

Database

$this->value_increment( $column , $value )

数据递增

参数列表

参数	类型	描述	默认值
`$column`	`string`	$column
`$value`	`int`	$value

返回值

Database

$this->value_decrement( $column , $value )

数据递减

参数列表

参数	类型	描述	默认值
`$column`	`string`	$column
`$value`	`int`	$value

返回值

Database

$this->table( $table )

Sets the table to update.

参数列表

参数	类型	描述	默认值
`$table`	`mixed`	Table name or array($table, $alias) or object

返回值

Database

$this->from( $tables )

from(tableA,tableB,...)

参数列表

参数	类型	描述	默认值
`$tables`	`mixed`	Table name or array($table, $alias) or object

返回值

Database

$this->join( $table , $type = null)

Adds addition tables to "JOIN ...".

参数列表

参数	类型	描述	默认值
`$table`	`mixed`	Column name or array($column, $alias) or object
`$type`	`string`	Join type (LEFT, RIGHT, INNER, etc)	null

返回值

Database

$this->on( $c1 , $c2 , $op = '=')

Adds "ON ..." conditions for the last created JOIN statement.

参数列表

参数	类型	描述	默认值
`$c1`	`mixed`	Column name or array($column, $alias) or object
`$c2`	`string`	Logic operator
`$op`	`mixed`	Column name or array($column, $alias) or object	string(1) "="

返回值

Database

$this->group_by( $columns )

group_by(c1,c2,c3,.....)

参数列表

参数	类型	描述	默认值
`$columns`	`mixed`	Column name or array($column, $alias) or object

返回值

Database

$this->having( $column , $value = null, $op = '=')

Alias of and_having()

参数列表

参数	类型	描述	默认值
`$column`	`mixed`	Column name or array($column, $alias) or object
`$value`	`string`	Logic operator	null
`$op`	`mixed`	Column value	string(1) "="

返回值

Database

$this->and_having( $column , $value = null, $op = '=')

Creates a new "AND HAVING" condition for the query.

参数列表

参数	类型	描述	默认值
`$column`	`mixed`	Column name or array($column, $alias) or object
`$value`	`string`	Logic operator	null
`$op`	`mixed`	Column value	string(1) "="

返回值

Database

$this->or_having( $column , $value = null, $op = '=')

Creates a new "OR HAVING" condition for the query.

参数列表

参数	类型	描述	默认值
`$column`	`mixed`	Column name or array($column, $alias) or object
`$value`	`string`	Logic operator	null
`$op`	`mixed`	Column value	string(1) "="

返回值

Database

$this->having_open( )

Alias of and_having_open()

返回值

Database

$this->and_having_open( )

Opens a new "AND HAVING (...)" grouping.

返回值

Database

$this->or_having_open( )

Opens a new "OR HAVING (...)" grouping.

返回值

Database

$this->having_close( )

Closes an open "AND HAVING (...)" grouping.

返回值

Database

$this->and_having_close( )

Closes an open "AND HAVING (...)" grouping.

返回值

Database

$this->or_having_close( )

Closes an open "OR HAVING (...)" grouping.

返回值

Database

$this->offset( $number )

Start returning results after "OFFSET ..."

参数列表

参数	类型	描述	默认值
`$number`	`integer`	Starting result number

返回值

Database

$this->reset( $key = null)

重设数据

参数列表

参数	类型	描述	默认值
`$key`	`$key`	不传则全部清除，可选参数 select,select_adv,from,join,where,group_by,having,parameters,set,columns,values,where,index,order_by,distinct,limit,offset,table,last_join,join,on	null

返回值

Database

$this->in( $column , $value , $no_in = false)

参数列表

参数	类型	描述	默认值
`$column`	`string`	$key
`$value`	`array`	$value
`$no_in`	`unknown`		bool false

返回值

Database

$this->notin( )

$this->where( $column , $value = null, $op = '=')

Alias of and_where()

参数列表

参数	类型	描述	默认值
`$column`	`mixed`	Column name or array($column, $alias) or object
`$value`	`string`	Logic operator	null
`$op`	`mixed`	Column value	string(1) "="

返回值

Database

$this->and_where( $column , $value , $op = '=')

Creates a new "AND WHERE" condition for the query.

参数列表

参数	类型	描述	默认值
`$column`	`mixed`	Column name or array($column, $alias) or object
`$value`	`string`	Logic operator
`$op`	`mixed`	Column value	string(1) "="

返回值

Database

$this->or_where( $column , $value , $op = '=')

Creates a new "OR WHERE" condition for the query.

参数列表

参数	类型	描述	默认值
`$column`	`mixed`	Column name or array($column, $alias) or object
`$value`	`string`	Logic operator
`$op`	`mixed`	Column value	string(1) "="

返回值

Database

$this->where_open( )

Alias of and_where_open()

返回值

Database

$this->and_where_open( )

Opens a new "AND WHERE (...)" grouping.

返回值

Database

$this->or_where_open( )

Opens a new "OR WHERE (...)" grouping.

返回值

Database

$this->where_close( )

Closes an open "AND WHERE (...)" grouping.

返回值

Database

$this->and_where_close( )

Closes an open "AND WHERE (...)" grouping.

返回值

Database

$this->or_where_close( )

Closes an open "OR WHERE (...)" grouping.

返回值

Database

$this->order_by( $column , $direction = 'ASC')

Applies sorting with "ORDER BY ..."

参数列表

参数	类型	描述	默认值
`$column`	`mixed`	Column name or array($column, $alias) or object
`$direction`	`string`	Direction of sorting	string(3) "ASC"

返回值

Database

$this->limit( $number , $offset = null)

Return up to "LIMIT ..." results

参数列表

参数	类型	描述	默认值
`$number`	`integer`	Maximum results to return
`$offset`	`integer`	Maximum results from offset	null

返回值

Database

$this->like( $column , $value = null)

返回 "LIKE ..."

参数列表

参数	类型	描述	默认值
`$column`	`string`	$column
`$value`	`string`	$value	null

返回值

Database

$this->or_like( $column , $value = null)

返回 "OR LIKE ..."

参数列表

参数	类型	描述	默认值
`$column`	`string`	$column
`$value`	`string`	$value	null

返回值

Database

$this->mod( $column , $mod_dig , $value )

返回 "$column MOD $mod = $value"

参数列表

参数	类型	描述
`$column`	`string`	$column
`$mod_dig`	`int`	$mod_dig
`$value`	`int`	$value

返回值

Database

$this->or_mod( $column , $mod_dig , $value , $op = '=')

返回 "OR $column MOD $mod = $value"

参数列表

参数	类型	描述	默认值
`$column`	`string`	$column
`$mod_dig`	`int`	$mod_dig
`$value`	`int`	$value
`$op`	`unknown`		string(1) "="

返回值

Database

$this->use_index( $index )

使用指定索引

参数列表

参数	类型	描述	默认值
`$index`	`string`

返回值

Database

$this->force_index( $index )

强制使用指定索引

参数列表

参数	类型	描述	默认值
`$index`	`string`

返回值

Database

$this->ignore_index( $index )

或略指定索引

参数列表

参数	类型	描述	默认值
`$index`	`string`

返回值

Database

$this->recovery_last_builder( )

恢复最后查询或reset时的Builder数据

此方法等同于在执行查询前先获取 $builder = $db->get_builder(); 然后执行SQL完毕后把原先的builder重新设置 $db->set_builder($builder);

 $db->from('mydb')->where('id', 1)->get()->as_array();    // 执行查询
 $db->recovery_last_builder();                            // 恢复

等同于下面代码，但明显上面代码更优雅

 $db->from('mydb')->where('id', 1);

 $builder = $db->get_builder();      // 在执行前获取builder设置
 $db->get()->as_array();             // 执行查询
 $db->set_builder($builder);         // 将前面获取的builder重新复原

例子一

 $count = $db->from('mydb')->where('id', 10, '>')->count_records();
 // 在执行count_records()时，所有的builder数据将会被清空
 echo $db->last_query();   // SELECT COUNT(1) AS `total_row_count` FROM `mydb` WHERE `id` > '10'

 // 恢复builder
 $db->recovery_last_builder();
 $db->limit(20)->order_by('id', 'DESC')->get()->as_array();

 echo $db->last_query();   // SELECT * FROM `mydb` WHERE `id` > '10' ORDER BY `id` DESC LIMIT 10

返回值

Database

Database_QueryBuilder::expr_value( $string )

创建一个不会被过滤处理的字符串

参数列表

参数	类型	描述	默认值
`$string`	`string`	Expression

返回值

Database_Expression

« Module_Database_Expression::__toString

Module_Database_QueryBuilder::__construct »