kohana3 如何构建复杂sql语句
构建复杂的 SELECT 语句–kohana3使用手册
选择特定的列
DB::select('column1','column2')->from('table_name'); SELECT `column1`, `column2` FROM `table_name`
选择列 AS
DB::select(array('column','my_column'))->from('table_name')->compile($db); SELECT `column` AS `my_column` FROM `table_name`
join()
DB::select()->from('table_name')->join('table_2')->on('table_2.table_id', '=', 'table_name.id'); SELECT * FROM `table_name` JOIN `table_2` ON `table_2`.`table_id` = `table_name`.`id`
group_by()
DB::select()->from('table_name')->group_by('column'); SELECT * FROM `table_name` GROUP BY `column` DB::select()->from('table_name')->group_by(array('column1', 'mycol')); SELECT * FROM `table_name` GROUP BY `column1` AS `mycol`
having()
DB::select()->from('table_name')->having('column','=','value'); SELECT * FROM `table_name` HAVING `column` = 'value'
and_having()
DB::select()->from('table_name')->having('column','=','value')->and_having('column2','=','value'); SELECT * FROM `table_name` HAVING `column` = 'value' AND `column2` = 'value'
or_having()
DB::select()->from('table_name')->having('column','=','value')->or_having('column2','=','value'); DB::select()->from('table_name')->having('column','=','value')->or_having('column2','=','value');
having_open()
DB::select()->from('table_name')->having_open()->having('column','=','value') ->or_having('column2','=','value')->having_close(); SELECT * FROM `table_name` HAVING (`column` = 'value' OR `column2` = 'value')
and_having_open()
DB::select()->from('table_name')->where('column','=','value')->and_having_open()->having('column2','=','value') ->and_having('column3','=','value')->and_having_close(); SELECT * FROM `table_name` WHERE `column` = 'value' HAVING (`column2` = 'value' AND `column3` = 'value')
or_having_open()
DB::select()->from('table_name')->where('column','=','value')->or_having_open()->having('column2','=','value') ->or_having('column3','=','value')->or_having_close(); SELECT * FROM `table_name` WHERE `column` = 'value' HAVING (`column2` = 'value' OR `column3` = 'value')
order_by()
DB::select()->from('table_name')->order_by('column', 'ASC'); SELECT * FROM `table_name` ORDER BY `column` ASC
limit()
DB::select()->from('table_name')->limit(10); SELECT * FROM `table_name` LIMIT 10
offset()
DB::select()->from('table_name')->limit(10)->offset(50); SELECT * FROM `table_name` LIMIT 10 OFFSET 50
where()
DB::select()->from('table_name')->where('column','=','value'); SELECT * FROM `table_name` WHERE `column` = 'value'
and_where()
DB::select()->from('table_name')->where('column','=','value')->and_where('column2','=','value'); SELECT * FROM `table_name` WHERE `column` = 'value' AND `column2` = 'value'
or_where()
DB::select()->from('table_name')->where('column','=','value')->or_where('column2','=','value'); SELECT * FROM `table_name` WHERE `column` = 'value' OR `column2` = 'value'
where_open()
DB::select()->from('table_name')->where_open()->where('column','=','value') ->or_where('column2','=','value')->where_close(); SELECT * FROM `table_name` WHERE (`column` = 'value' OR `column2` = 'value')
and_where_open()
DB::select()->from('table_name')->where('column','=','value')->and_where_open()->where('column2','=','value') ->or_where('column3','=','value')->and_where_close(); SELECT * FROM `table_name` WHERE `column` = 'value' AND (`column2` = 'value' OR `column3` = 'value')
or_where_open()
DB::select()->from('table_name')->where('column','=','value')->or_where_open()->where('column2','=','value') ->and_where('column3','=','value')->or_where_close(); SELECT * FROM `table_name` WHERE `column` = 'value' OR (`column2` = 'value' AND `column3` = 'value')