ThinkPHP5数据查询表达式生成技巧

ThinkPHP5中的查询表达式大揭密

主要使用where(条件表达式)方法；

语法一：where('字段','条件','值');

等于：EQ ，解析为"="

不等于：NEQ，解析为"<>"

小于：LT ，解析为"<"

小于等于：ELT，解析为"<="

大于：GT，解析为">"

大于等于：EGT，解析为">="

区间：BETWEEN :解析为 "BETWEEN * AND *"

不在区间: NOTBETWEEN :解析为 "NOTBETWEEN * AND *"

集合：IN, 解析为: IN((* , *);

不在集合中：NOTIN, 解析为：NOTIN(*, *)

字段值为NULL 和 NOT NULL

//省略条件,默认为相等
$res = Db::table("staff")
      ->where("id",1010)  //等价于:where('id','=',1010)
      ->fetchSql(true)  //输出SQL语句,不执行后面***作
      ->select();
//完整的单值条件表达式
$res = Db::table("staff")
      ->where("id",'EGT',1010)  //等价于:where('id','>=',1010)
      ->fetchSql(true)
      ->select();

//区间条件表达式
$res = Db::table("staff")
      ->where("age",'BETWEEN',[18, 35]) //between 18 and 35
      ->where("age",'NOT BETWEEN',[18, 35]) //between 18 and 35
      ->fetchSql(true)
      ->select();
//集合条件表达式
$res = Db::table("staff")
      ->where("id",'IN',[2, 3, 5])  //in(2,3,5)
      ->where("id",'NOT IN',[2, 3, 5])  //not in(2,3,5)
      ->fetchSql(true)
      ->select();

//null值判断,注意中间有"="号的区别
$res = Db::table("staff")
      ->where("salary",'NULL')  //salary IS NULL,该列无值
      ->where("salary",'=','NULL')  //salary = 0,字段值等于0
      ->fetchSql(true)
      ->select();
//like 或者 not like
$res = Db::table("staff")
      ->where("name","LIKE", "%php")  //模糊匹配php结尾字段值name like %php
      ->where("name","NOT LIKE", "%php")
      ->where("salary",'=','NULL')  //salary = 0,字段值等于0
      ->fetchSql(true)
      ->select();
//从ThinkPHP5.0.5+,like支持数组参数,参数之间的关系由最后一个参数给出:or
where('name','like',['%think','php%'],'OR');
//SQL:SELECT * FROM `abc` WHERE  (`name` LIKE '%think' OR `name` LIKE 'php%')

语法二、where(['字段1'=>['条件1','值1'], '字段2'=>['条件2','值2'],...])

说明：:每个数组元素都是一个查询条件,使用where()方法时,每个条件之间为AND关系,使用whereOr()方法为OR

//AND
$res = Db::table("abc")
    ->where([
      "id"  =>  [">",10],
      "age" => ["BETWEEN",[18,22]]
    ])
    ->fetchSql(true)
    ->select();
//SQL:SELECT * FROM `abc` WHERE  `id` > 10  AND `age` BETWEEN 18 AND 22
//OR
$res = Db::table("abc")
    ->whereOr([
      "salary"  =>  ["EGT",3500],
      "age" => ["BETWEEN",[28,42]]
    ])
    ->fetchSql(true)
    ->select();
//SQL:SELECT * FROM `abc` WHERE  `salary` >= 3500  OR `age` BETWEEN 28 AND 42

语法三：where('字段',"EXP","条件表达式")

生成条件表达式的终极***,如果以上都无法生成满足要求的查询条件,就要考虑用这个啦,支持字段函数

条件表达式参数的语法与SQL语句中保持一致,就是说，你在SQL语句中怎么写，这里就怎么写

最终生成的SQL时，会自动忽略掉EXP字符,直接将：字段与条件表达式进行拼接

//简单条件
$res = Db::table("staff")
     ->where("id","EXP",">=10")
     ->select();
//SQL:SELECT * FROM `staff` WHERE ( `id` >=10 )
//复合条件
$res = Db::table("staff")
    ->where("age","EXP","BETWEEN 20 AND 30 ")
    ->select();
//SQL:SELECT * FROM `staff` WHERE ( `age` BETWEEN 20 AND 30 )

语法四: where()和whereOr()链式调用生成AND或OR关系的查询表达式

$res =Db::table("staff")
         ->where("id","EXP",">=10")
         ->where("age",">",30)
         ->whereOr("dept","市场部")
         ->select();
//SQL:SELECT * FROM `staff` WHERE ( `id` >=10 ) AND `age` > 30 OR `dept` = '市场部'

相关文章