db写法query

  
  

   
   $query = DB::select();


?
$query = DB::select()->from('users');
Limiting the results of queries is done using the where(), and_where() and or_where() methods. These methods take three parameters: a column, an operator, and a value.

?
$query = DB::select()->from('users')->where('username', '=', 'john');
Multiple where() methods may be used to string together multiple clauses connected by the boolean operator in the method's prefix. The where() method is a wrapper that just calls and_where().

?
$query = DB::select()->from('users')->where('username', '=', 'john')->or_where('username', '=', 'jane');
You can use any operator you want. Examples include IN, BETWEEN, >, =<, !=, etc. Use an array for operators that require more than one value.

?
$query = DB::select()->from('users')->where('logins', '<=', 1);
 
$query = DB::select()->from('users')->where('logins', '>', 50);
 
$query = DB::select()->from('users')->where('username', 'IN', array('john','mark','matt'));
 
$query = DB::select()->from('users')->where('joindate', 'BETWEEN', array($then, $now));
By default, DB::select will select all columns (SELECT * ...), but you can also specify which columns you want returned by passing parameters to DB::select:

?
$query = DB::select('username', 'password')->from('users')->where('username', '=', 'john');


?
echo Kohana::debug((string) $query);
// Should display:
// SELECT `username`, `password` FROM `users` WHERE `username` = 'john'


?
$query = DB::select(array('username', 'u'), array('password', 'p'))->from('users');
This query would generate the following SQL:

?
SELECT `username` AS `u`, `password` AS `p` FROM `users`
Select - DISTINCT

Unique column values may be turned on or off (default) by passing TRUE or FALSE, respectively, to thedistinct() method.

?
$query = DB::select('username')->distinct(TRUE)->from('posts');
This query would generate the following SQL:

?
SELECT DISTINCT `username` FROM `posts`
Select - LIMIT & OFFSET

When querying large sets of data, it is often better to limit the results and page through the data one chunk at a time. This is done using the limit() and offset() methods.

?
$query = DB::select()->from(`posts`)->limit(10)->offset(30);
This query would generate the following SQL:

?
SELECT * FROM `posts` LIMIT 10 OFFSET 30


?
$query = DB::select()->from(`posts`)->order_by(`published`, `DESC`);
This query would generate the following SQL:

?
SELECT * FROM `posts` ORDER BY `published` DESC


?
$query = DB::insert('users', array('username', 'password'))->values(array('fred', 'p@5sW0Rd'));
This query would generate the following SQL:

?
INSERT INTO `users` (`username`, `password`) VALUES ('fred', 'p@5sW0Rd')


?
$query = DB::update('users')->set(array('username' => 'jane'))->where('username', '=', 'john');
This query would generate the following SQL:

?
UPDATE `users` SET `username` = 'jane' WHERE `username` = 'john'


?
$query = DB::delete('users')->where('username', 'IN', array('john', 'jane'));
This query would generate the following SQL:

?
DELETE FROM `users` WHERE `username` IN ('john', 'jane')


?
// 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 would generate the following SQL:

?
SELECT `authors`.`name`, `posts`.`content` FROM `authors` JOIN `posts` ON (`authors`.`id` = `posts`.`author_id`) WHERE `authors`.`name` = 'smith'
If you want to do a LEFT, RIGHT or INNER JOIN you would do it like this join('colum_name', 'type_of_join'):

?
// 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');
This query would generate the following SQL:

?
SELECT `authors`.`name`, `posts`.`content` FROM `authors` LEFT JOIN `posts` ON (`authors`.`id` = `posts`.`author_id`) WHERE `authors`.`name` = 'smith'


?
$query = DB::select(array('COUNT("username")', 'total_users'))->from('users');


?
SELECT COUNT(`username`) AS `total_users` FROM `users`


?
$query = DB::select()->from('users')
    ->join('posts')->on('posts.username', '=', 'users.username')
    ->where('users.active', '=', TRUE)
    ->where('posts.created', '>=', $yesterday);
 
$total = clone $query;
$total->select(array('COUNT( DISTINCT "username")', 'unique_users'));
$query->select('posts.username')->distinct();


?
$query = DB::select('username', array('COUNT("id")', 'total_posts')
    ->from('posts')->group_by('username')->having('total_posts', '>=', 10);
This will generate the following query:

?
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');
This will generate the following query:

?
SELECT `profiles`.*, `posts`.`total_posts` FROM `profiles` INNER JOIN
( SELECT `username`, COUNT(`id`) AS `total_posts` FROM `posts` GROUP BY `username` HAVING `total_posts` >= 10 ) AS posts
ON `profiles`.`username` = `posts`.`username`
Insert queries can also use a select query for the input values

?
$sub = DB::select('username', array('COUNT("id")', 'total_posts')
    ->from('posts')->group_by('username')->having('total_posts', '>=', 10);
 
$query = DB::insert('post_totals', array('username', 'posts'))->select($sub);
This will generate the following query:

?
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);
This will generate the following query:

?
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);
This will generate the following query, assuming $id = 45:

?
UPDATE `users` SET `login_count` = `login_count` + 1 WHERE `id` = 45