• 查询语言.where
    • 函数原型
    • 字段 （表达式） 值
    • 以数组直接传入
    • OrWhere 语法
    • whereBetween 语法
    • whereBetween 二维数组语法支持
    • whereNotBetween 语法
    • whereIn 语法
    • whereNotIn 语法
    • whereNull 语法
    • whereNotNull 语法
    • whereLike 语法
    • whereNotLike 语法
    • whereExists 语法
    • whereNotExists 语法
    • whereDate、whereMonth、whereDay、whereYear 语法
    • 分组查询
    • 条件表达式支持
    • 字段作为键值
    • string__ 支持（支持字段）
    • subor 和 suband 支持

    查询语言.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();