yii2 各种查询

简单的用法中，where()方法是设置查询条件的，也就是sql语句中的where部分，例如：

$query = Article::find()->where(['status'=>10]);
//sql: SELECT * FROM `article` WHERE `status`=10
$query = Article::find()->where(['status'=>10, 'type'=>1]);
//sql: SELECT * FROM `article` WHERE (`status`=10) AND (`type`=1)
$query = Article::find()->where(['status'=>10, 'id'=>[1,2,3]]);
//sql: SELECT * FROM `article` WHERE (`status`=10) AND (`id` IN (1, 2, 3))

where()还可以指定运输符，例如：

$query = Article::find()->where(['>=', 'id', 10]);
//sql: SELECT * FROM `article` WHERE `id` >= 10

当然，where()还有更复杂的用法，例如：

$query = Article::find()->where(['and', 'type=1', 'status=10']);
//sql: SELECT * FROM `article` WHERE (type=1) AND (status=10)
$query = Article::find()->where(['and', 'type=1', ['or', 'cid=1', 'status=10']]);
//sql: SELECT * FROM `article` WHERE (type=1) AND ((cid=1) OR (status=10))

除了and，当然还有如or、not、between、not between、in、not in、like、or like、not like、or not like、exists、not exists，用法示例如下：

//or
$query = Article::find()->where(['or', ['type'=>[7,8,9]], ['status'=>[1,2,3]]]);
//sql: SELECT * FROM `article` WHERE (`type` IN (7, 8, 9)) OR (`status` IN (1, 2, 3))

//not
$query = Article::find()->where(['not', ['type'=>[7,8,9]]]);
//sql: SELECT * FROM `article` WHERE NOT (`type` IN (7, 8, 9))

//between
$query = Article::find()->where(['between', 'id', 1, 10]);
//sql: SELECT * FROM `article` WHERE `id` BETWEEN 1 AND 10

//not between
$query = Article::find()->where(['not between', 'id', 1, 10]);
//sql: SELECT * FROM `article` WHERE `id` NOT BETWEEN 1 AND 10

//in
$query = Article::find()->where(['in', 'id', [1,2,3]]);
//sql: SELECT * FROM `article` WHERE `id` IN (1, 2, 3)
$query = Article::find()->where(['in', ['id', 'type'], [['id'=>1, 'type'=>10], ['id'=>2, 'type'=>8]]]);
//sql: SELECT * FROM `article` WHERE (`id`, `type`) IN ((1, 10), (2, 8))

//not in
$query = Article::find()->where(['not in', 'id', [1,2,3]]);
//sql: SELECT * FROM `article` WHERE `id` NOT IN (1, 2, 3)

//like
$query = Article::find()->where(['like', 'name', 'tester']);
//sql: SELECT * FROM `article` WHERE `name` LIKE '%tester%'
$query = Article::find()->where(['like', 'name', ['test', 'sample']]);
//sql: SELECT * FROM `article` WHERE `name` LIKE '%test%' AND `name` LIKE '%sample%'
$query = Article::find()->where(['like', 'name', '%tester', false]);
//sql: SELECT * FROM `article` WHERE `name` LIKE '%tester'

//or like
$query = Article::find()->where(['or like', 'name', ['test', 'sample']]);
//sql: SELECT * FROM `article` WHERE `name` LIKE '%test%' OR `name` LIKE '%sample%'

//not like
$query = Article::find()->where(['not like', 'name', 'tester']);
//sql: SELECT * FROM `article` WHERE `name` NOT LIKE '%tester%'

//or not like
$query = Article::find()->where(['or not like', 'name', ['test', 'sample']]);
//sql: SELECT * FROM `article` WHERE `name` NOT LIKE '%test%' OR `name` NOT LIKE '%sample%'

//exists
$query = Article::find()->where(['exists', Article::find()->select('id')->from('users')->where(['active'=>1])]);
//sql: SELECT * FROM `article` WHERE EXISTS (SELECT `id` FROM `users` WHERE `active`=1)

//not exists
$query = Article::find()->where(['not exists', Article::find()->select('id')->from('users')->where(['active'=>1])]);
//sql: SELECT * FROM `article` WHERE NOT EXISTS (SELECT `id` FROM `users` WHERE `active`=1)

另外，andWhere()和orWhere()的用法，与where()基本相同。