查询语言.where

函数原型

  1. public function where($mixCond /* args */);

字段 （表达式） 值

  1. ## SELECT `test`.* FROM `test` WHERE `test`.`id` = 1
  2. Db::table('test')->
  4. where('id', '=', 1)->
  6. getAll();
  8. ## SELECT `test`.* FROM `test` WHERE `test`.`id` = 2 
  9. Db::table('test')->
  11. where('id', 2)->
  13. getAll();
  15. ## SELECT `test`.* FROM `test` WHERE `test`.`id` = 2 AND `test`.`name` > '狗蛋' AND `test`.`value` LIKE '小鸭子'
  16. Db::table('test')->
  18. where('id', 2)->
  20. where('name', '>', '狗蛋')->
  22. where('value', 'like', '小鸭子')->
  24. getAll();

以数组直接传入

  1. ## SELECT `test`.* FROM `test` WHERE `test`.`name` LIKE '技术'
  2. Db::table('test')->
  4. where(['name','like', '技术'])->
  6. getAll();
  8. ## SELECT `test`.* FROM `test` WHERE `test`.`name` LIKE '技术' AND `test`.`value` <> '结局' 
  9. Db::table('test')->
  11. where ([
  12.   ['name','like', '技术'],
  13.   ['value','<>', '结局']
  14. ])->
  16. getAll();

OrWhere 语法

  1. ## SELECT `test`.* FROM `test` WHERE `test`.`name` LIKE '技术'
  2. Db::table('test')->
  4. where('name','like', '技术')->
  6. orWhere('value','<>', '结局')->
  8. getAll();

whereBetween 语法

  1. ## SELECT `test`.* FROM `test` WHERE `test`.`id` BETWEEN 1 AND 100
  2. Db::table('test')->
  4. whereBetween('id', [1, 100])->
  6. getAll();
  8. ## SELECT `test`.* FROM `test` WHERE `test`.`id` BETWEEN 1 AND 10
  9. Db::table('test')->
  11. where('id','between', [1, 10])->
  13. getAll();

whereBetween 二维数组语法支持

后面的语法均支持这样的写法，后面不再继续示例了。

  1. ## SELECT `test`.* FROM `test` WHERE `test`.`id` BETWEEN 1 AND 100 AND `test`.`name` BETWEEN 5 AND 22
  2. Db::table('test')->
  4. whereBetween([
  5.   ['id', [1, 100]],
  6.   ['name', [5, 22]]
  7. ])->
  9. getAll();

whereNotBetween 语法

  1. ## SELECT `test`.*  FROM `test` WHERE `test`.`id` NOT BETWEEN 1 AND 10
  2. Db::table('test')->
  4. whereNotBetween('id', [1, 10])->
  6. getAll();
  8. ## SELECT `test`.* FROM `test` WHERE `test`.`id` NOT BETWEEN 1 AND 10
  9. Db::table('test')->
  11. where('id','not between', [1, 10])->
  13. getAll();

whereIn 语法

  1. ## SELECT `test`.* FROM `test` WHERE `test`.`id` IN (2, 50)
  2. Db::table('test')->
  4. whereIn('id', [2, 50])->
  6. getAll();
  8. ## SELECT `test`.* FROM `test` WHERE `test`.`id` IN ('1','10')
  9. Db::table('test')->
  11. where('id', 'in', '1,10')
  13. ->getAll();
  15. ## SELECT `test`.* FROM `test` WHERE `test`.`id` IN (2,50)
  16. Db::table('test')->
  18. where('id', 'in', [2, 50])
  20. ->getAll();

whereNotIn 语法

  1. ## SELECT `test`.* FROM `test` WHERE `test`.`id` NOT IN (2,50)
  2. Db::table('test')->
  4. whereNotIn('id', [2, 50])->
  6. getAll();
  8. ## SELECT `test`.* FROM `test` WHERE `test`.`id` NOT IN ('1','10')
  9. Db::table('test')->
  11. where('id','not in', '1,10')->
  13. getAll();

whereNull 语法

  1. ## SELECT `test`.* FROM `test` WHERE `test`.`id` IS NULL
  2. Db::table('test')->
  4. whereNull('id')->
  6. getAll();
  8. ## SELECT `test`.* FROM `test` WHERE `test`.`id` IS NULL
  9. Db::table('test')->
  11. where('id','null')->
  13. getAll();

whereNotNull 语法

  1. ## SELECT `test`.* FROM `test` WHERE `test`.`id` IS NOT NULL
  2. Db::table('test')->
  4. whereNotNull('id')->
  6. getAll();
  8. ## SELECT `test`.* FROM `test` WHERE `test`.`id` IS NOT NULL
  9. Db::table('test')->
  11. where('id','not null')->
  13. getAll();

whereLike 语法

  1. ## SELECT `test`.* FROM `test` WHERE `test`.`id` LIKE '5'
  2. Db::table('test')->
  4. whereLike('id','5')->
  6. getAll();
  8. ## SELECT `test`.* FROM `test` WHERE `test`.`id` LIKE '5'
  9. Db::table('test')->
  11. where('id','like', '5')->
  13. getAll();

whereNotLike 语法

  1. ## SELECT `test`.* FROM `test` WHERE `test`.`id` NOT LIKE '5'
  2. Db::table('test')->
  4. whereNotLike('id','5')->
  6. getAll();
  8. ## SELECT `test`.* FROM `test` WHERE `test`.`id` NOT LIKE '5'
  9. Db::table('test')->
  11. where('id','not like', '5')->
  13. getAll();

whereExists 语法

  1. ## SELECT `test`.* FROM `test` WHERE EXISTS (SELECT `subsql`.* FROM `subsql` WHERE `subsql`.`id` = 1)
  2. Db::table('test')->
  4. whereExists(
  5.     function($select) {
  6.         $select->table('subsql')->where('id', 1);
  7.     }
  8. )->
  10. getAll();
  12. ## SELECT `test`.* FROM `test` WHERE EXISTS (SELECT `subsql`.* FROM `subsql`)
  13. $subSelect = Db::table('subsql');
  15. Db::table('test')->
  17. where(
  18.    [
  19.       'exists__' => $subSelect
  20.    ]
  21. )->
  23. getAll();
  25. ## SELECT `test`.* FROM `test` WHERE EXISTS (select *from d_sub)
  26. Db::table('test')->
  28. where(
  29.    [
  30.       'exists__' => 'select *from d_sub'
  31.    ]
  32. )->
  34. getAll();
  36. ## SELECT `test`.* FROM `test` WHERE EXISTS (SELECT `subsql`.* FROM `subsql` WHERE `subsql`.`id` = 1)
  37. Db::table('test')->
  39. where(
  40.    [
  41.       'exists__' => function($select) {
  42.           $select->table('subsql')->where('id', 1);
  43.       }
  44.    ]
  45. )->
  47. getAll();

whereNotExists 语法

  1. ## SELECT `test`.* FROM `test` WHERE NOT EXISTS ( SELECT `subsql`.* FROM `subsql` WHERE `subsql`.`id` = 1  )
  2. Db::table('test')->
  4. whereNotExists(
  5.     function($select){
  6.         $select->table('subsql')->where('id', 1);
  7.     }
  8. )->
  10. getAll();

whereDate、whereMonth、whereDay、whereYear 语法

本功能比较独立，剔除来了见《数据库构造器时间运算符 time endTime》

分组查询

  1. ## SELECT `test`.* FROM `test` WHERE `test`.`id` = '5' OR (`test`.`votes` > 100 AND `test`.`title` <> 'Admin')
  2. Db::table('test')->
  4. where('id', 5)->
  6. orWhere(function ($select) {
  7.         $select->where('votes', '>', 100)->where('title', '<>', 'Admin');
  8. })->
  10. getAll();
  12. ## SELECT `test`.* FROM `test` WHERE `test`.`id` = '5' OR `test`.`name` = '小牛' AND (`test`.`votes` > 100 OR `test`.`title` <> 'Admin')
  13. Db::table('test')->
  15. where('id', 5)->
  17. orWhere('name', '小牛')->
  19. where(function ($select) {
  20.     $select->where('votes', '>', 100)->orWhere('title', '<>', 'Admin');
  21. })->
  23. getAll();

条件表达式支持

条件表达式由 “{}” 包起来表示支持条件表达式，“[]” 表示支持字段格式化。

  1. ## SELECT `test`.`post`,`test`.`value`,concat("tt_",`test`.`id`) FROM `test` WHERE concat("hello_",`test`.`posts`) = `test`.`id` 
  2. Db::table('test', 'post,value,{concat("tt_",[id])}')->
  4. where('{concat("hello_",[posts])}', '=', '{[id]}')->
  6. getAll();

字段作为键值

  1. ## SELECT `test`.* FROM `test` WHERE `test`.`id` = '故事' AND `test`.`name` IN (1,2,3) AND `test`.`weidao` BETWEEN '40' AND '100' AND `test`.`value` IS NULL AND `test`.`remark` IS NOT NULL AND `test`.`goods` = '东亚商品' AND `test`.`hello` = 'world'
  2. Db::table('test')->
  4. where([ 
  5.     'id' => ['=', '故事'],
  6.     'name' => ['in', [1,2,3]],
  7.     'weidao' => ['between', '40,100'],
  8.     'value' => 'null',
  9.     'remark' => ['not null'],
  10.     'goods' => '东亚商品',
  11.     'hello' => ['world'] 
  12. ])->
  14. getAll();

string__ 支持（支持字段）

  1. ## SELECT `test`.* FROM `test` WHERE `test`.`name` = 11 and `post`.`value` = 22 and concat("tt_",`test`.`id`)
  2. Db::table('test')->
  4. where (
  5.     ['string__' => '{[name] = 11 and [post.value] = 22 and concat("tt_",[id])}']
  6. )->
  8. getAll();

subor 和 suband 支持

  1. ## SELECT `test`.* FROM `test` WHERE `test`.`hello` = 'world' OR (`test`.`id` LIKE '你好')
  2. Db::table('test')->
  4. where (
  5.     [
  6.         'hello' => 'world',
  7.         'subor__' => ['id', 'like', '你好']
  8.     ]
  9. )->
  11. getAll();
  13. ## SELECT `test`.* FROM `test` WHERE `test`.`hello` = '111' OR (`test`.`id` LIKE '你好' AND `test`.`value` = 'helloworld') AND (`test`.`id` LIKE '你好' OR `test`.`value` = 'helloworld' OR (`test`.`child_one` > '123' AND `test`.`child_two` LIKE '123')) 
  14. Db::table('test')->
  16. where (
  17.    [
  18.         'hello' => '111',
  19.         'subor__' => [
  20.             ['id', 'like', '你好'],
  21.             ['value', '=', 'helloworld']
  22.         ],
  23.         'suband__' => [
  24.             'logic__' => 'or',
  25.             ['id2', 'like', '你好2'],
  26.             ['value2', '=', 'helloworld2'],
  27.             'subor__' => [
  28.                 ['child_one', '>', '123'],
  29.                 ['child_two', 'like', '123'],
  30.             ]
  31.         ]
  32.    ]
  33. )->
  35. getAll();