kohana框架DB操作方法详情，涉及到DB::select，DB::update, DB::delete和链表等常用操作数据方法
一.读取数据库记录 读取数据库记录需要使用到 DB::select() 方法
// 返回一个结果对象
$result = DB::select('column')->from('table_name')->execute();
// 结果作为数组返回
$result = DB::select('column')->from('table_name')->execute()->as_array();
// 结果作为标准类对象返回s
$result = DB::select('column')->from('table_name')->as_object()->execute();
// 仅返回第一行
$result = DB::select('column')->from('table_name')->execute()->current();
你可以按你所需要的在上面的示例中选择一个方法。
// 返回一个列
$result = DB::select('column')->from('table_name')->execute()->current();
//返回3列
$result = DB::select('column', 'column2', 'column3')->from('table_name')->execute()->current();
// 列名的别名
$result = DB::select(array('longcolumnname1', 'col1'), array('longcolumnname2', 'aliascol2'))->from('table_name')->execute()->current();
你可以用 where() 方法来选择特定的记录
$result = DB::select()->from('table_name')->where('column','=','value')->execute();
二，在直接写SQL方式的增删改查：
1.直接写sql语句方式：
1).查询：
$qu_relation_sql="select * from alipay_account where status = 1";
$totlemoney = DB::query(Database::SELECT, $qu_relation_sql)->execute('alipay')->current();
2).修改：
$qu_relation_sql="update alipay_account set totlemoney='$new_totlemoney' WHERE openid='" . $openid['openid'] . "' ";
$totlemoney = DB::query(Database::UPDATE, $qu_relation_sql)->execute('alipay');
3).删除:
$totlemoney = DB::query(Database::DELETE, $qu_relation_sql)->execute('alipay');
4)添加：
$sql_bankinfo = " INSERT INTO `wp_bankinfo`( `openid`, `bank`, `name`, `bankcard`, `mobile`) VALUES ('" . $openid['openid'] . "','" . $_GET['bank'] . "','" . $_GET['card_name'] . "','" . $_GET['bank_num'] . "','" . $_GET['mobile'] . "') ";
$insert_id = DB::query(Database::INSERT, $sql_bankinfo)->execute('weixin');
2，不写sql语句：(alipay_account:表名 alipay：库名)
1).查询：上面第一读取数据库上有。
2).删除：DB::delete('alipay_account')->where('status','=',3)->execute('alipay');
3)修改：DB::update('pages')
->set(array('views' => DB::expr('views + 1')))
->where('id', '=', 1)
->execute();
4)增加：DB::insert('alipay_account')
->columns(array_keys($in_account))
->values(array_values($in_account))
->execute('alipay');
三，数据表链表join
// This query will find all the posts related to "smith" with JOIN
$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');
$query = DB::select('username', array('COUNT("id")', 'total_posts')
->from('posts')->group_by('username')->having('total_posts', '>=', 10);
SELECT `username`, COUNT(`id`) AS `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
