php kohana db_综合

1：insert DB::insert('表名',array('字段名1','字段名2'))->values(array('值1','值2'))->execute();

2：find $restult=DB::select('字段名')->from('表名')->execute(); //返回一个数据对象

*$result->as_array(); //用数组输出

*DB:select('字段名')->from('表名')->as_object()->execute(); //返回标准对象

*DB::select('字段名')->from('表名')->execute()->current(); //返回数组第一行

3：select $result=DB::select('字段名')->from('表名')->where('字段名','=','条件')-execute();

4：update $total_rows = DB::update('表名')->set(array('字段名'=>'值'))->where('字段名','=','条件')->execute(); //返回1

5：drop $total_rows = DB::delete('表名')->where('字段名','=',''值')->execute(); //成功返回1

6 ：add_column_vale $total_rows = DB::update('users')

->set(array('字段名1'=>DB::expr('字段名1+1')))

->where('字段名','=','条件')->execute();

7：join $arr= DB::select(array('字段名','别名'))->from('表名')->compile(); //打印sql

8：group_by $arr= DB::select('字段名')->from('表名')->group_by('字段名')->execute();

9：order_by(desc,asc) $arr=DB::select()->from('表名')->order_by('字段名','ASC/DESC')->execute()->as_array();//排序的字段必须是int型

10：limit $arr=DB::select()->from('表名')->limit('num')->execute()->as_array();

11：offset $arr = DB::select()->from('表名')->imit(截取几位)->offset(从哪儿开始)->execute()->as_array();

//加强版

$query = DB::select();$query = DB::select()->from('users'); //查找users表所有信息    sql SELECT * FROM `posts` LIMIT 10 OFFSET 30$query = DB::select()->from('posts')->limit(10)->offset(30); //查询posts表从第30条开始的后十条信息（31-40）   $query = DB::select()->from('posts')->order_by('published', 'DESC');  //根据published字段升序查询$query = DB::select('username', 'password')->from('users'); //查询users表的 username与password字段$query = DB::select('username')->distinct(TRUE)->from('posts'); //返回username不重复的记录    SELECT `username` AS `u`, `password` AS `p` FROM `users`$query = DB::select(array('username', 'u'), array('password', 'p'))->from('users'); //as用法$query = DB::select()->from('users')->where('username', '=', 'john')->and_where('','','')->or_where('','','');//and or 用法$query = DB::select()->from('users')->where('logins', '<=', 1); //查找users表的 logins字段 <= 1 的数据 
$query = DB::select()->from('users')->where('logins', '>', 50); //返回logins > 50 的数据
$query = DB::select()->from('users')->where('username', 'IN', array('john','mark','matt')); //返回username等于john or mark or matt 的数据
$query = DB::select()->from('users')->where('joindate', 'BETWEEN', array($then, $now));  //查找users表 joindate字段 >= $then and <= $now 的数据$query = DB::insert('users', array('username', 'password'))->values(array('fred', 'p@5sW0Rd'))->values(array('fred', 'p@5sW0Rd')); //插入多条数据UPDATE `users` SET `username` = 'jane' WHERE `username` = 'john'$query = DB::update('users')->set(array('username' => 'jane'))->where('username', '=', 'john'); //修改username=john 为 =jane DELETE FROM `users` WHERE `username` IN ('john', 'jane')$query = DB::delete('users')->where('username', 'IN', array('john', 'jane')); //删除users表中的 username=john and username=jane 的数据//表连接
$query = DB::select('authors.name', 'posts.content')->from('authors')->join('posts')->on('authors.id', '=', 'posts.author_id')->where('authors.name', '=', 'smith');// This query will find all the posts related to "smith" with LEFT JOIN
$query = DB::select()->from('authors')->join('posts', 'LEFT')->on('authors.id', '=', 'posts.author_id')->where('authors.name', '=', 'smith');//sql:
SELECT `username`, COUNT(`id`) AS `total_posts` FROM `posts` GROUP BY `username` HAVING `total_posts` >= 10$query = DB::select('username', array('COUNT("id")', 'total_posts'))->from('posts')->group_by('username')->having('total_posts', '>=', 10);$sub = DB::select('username', array('COUNT("id")', 'total_posts'))->from('posts')->group_by('username')->having('total_posts', '>=', 10);$query = DB::select('profiles.*', 'posts.total_posts')->from('profiles')->join(array($sub, 'posts'), 'INNER')->on('profiles.username', '=', 'posts.username');INSERT INTO `post_totals` (`username`, `posts`) 
SELECT `username`, COUNT(`id`) AS `total_posts` FROM `posts` GROUP BY `username` HAVING `total_posts` >= 10 $query = DB::select()->from('users')->where_open()->or_where('id', 'IN', $expired)->and_where_open()->where('last_login', '<=', $last_month)->or_where('last_login', 'IS', NULL)->and_where_close()->where_close()->and_where('removed','IS', NULL);SELECT * FROM `users` WHERE ( `id` IN (1, 2, 3, 5) OR ( `last_login` <= 1276020805 OR `last_login` IS NULL ) ) AND `removed` IS NULL $query = DB::update('users')->set(array('login_count' => DB::expr('login_count + 1')))->where('id', '=', $id);
UPDATE `users` SET `login_count` = `login_count` + 1 WHERE `id` = 45$result = $query->execute();$results = DB::select('id', 'email')->from('users')->execute();
$users = $results->as_array();
foreach($users as $user)
{echo 'User ID: '.$user['id'];echo 'User Email: '.$user['email'];
}$results = DB::select('id', 'email')->from('users')->execute();
$users = $results->as_array('id');
foreach($users as $id => $user)
{echo 'User ID: '.$id;echo 'User Email: '.$user['email'];
}$results = DB::select('email')->from('users')->execute();
$users = $results->as_array(NULL, 'email');
foreach($users as $email)
{echo 'User Email: '.$email;
}$insert = DB::insert('tools')->columns(array('name', 'model', 'description'))->values(array('Skil 3400 10" Table Saw', '3400', 'Powerful 15 amp motor; weighs just 54-pounds'));list($insert_id, $affected_rows) = $insert->execute();$rows_deleted = DB::delete('tools')->where('model', 'like', '3400')->execute();$query = DB::query(Database::SELECT, 'SELECT * FROM users WHERE username = :user');order 关键字:
asc    :升序 desc   :降序