高级查询
快捷查询
快捷查询方式是一种多字段相同查询条件的简化写法,可以进一步简化查询条件的写法,在多个字段之间用|
分割表示OR查询,用&
分割表示AND查询,可以实现下面的查询,例如:
Db::table("think_user")
->where("name|title","like","thinkphp%")
->where("create_time&update_time",">",0)
->find();
生成的查询SQL是:
SELECT * FROM `think_user` WHERE ( `name` LIKE "thinkphp%" OR `title` LIKE "thinkphp%" ) AND ( `create_time` > 0 AND `update_time` > 0 ) LIMIT 1
快捷查询支持所有的查询表达式。
区间查询
区间查询是一种同一字段多个查询条件的简化写法,例如:
Db::table("think_user")
->where("name",["like","thinkphp%"],["like","%thinkphp"])
->where("id",[">",0],["<>",10],"or")
->find();
生成的SQL语句为:
SELECT * FROM `think_user` WHERE ( `name` LIKE "thinkphp%" AND `name` LIKE "%thinkphp" ) AND ( `id` > 0 OR `id` <> 10 ) LIMIT 1
区间查询的查询条件必须使用数组定义方式,支持所有的查询表达式。
下面的查询方式是错误的:
Db::table("think_user")
->where("name",["like","thinkphp%"],["like","%thinkphp"])
->where("id",5,["<>",10],"or")
->find();
批量查询
可以进行多个条件的批量条件查询定义,例如:
Db::table("think_user")
->where([
"name" => ["like","thinkphp%"],
"title" => ["like","%thinkphp"],
"id" => [">",0],
"status"=> 1
])
->select();
生成的SQL语句为:
SELECT * FROM `think_user` WHERE `name` LIKE "thinkphp%" AND `title` LIKE "%thinkphp" AND `id` > 0 AND `status` = "1"
闭包查询
Db::table("think_user")->select(function($query){
$query->where("name","thinkphp")
->whereOr("id",">",10);
});
生成的SQL语句为:
SELECT * FROM `think_user` WHERE `name` = "thinkphp" OR `id` > 10
使用Query对象查询
也可以事先封装Query对象,并传入select方法,例如:
$query = new hinkdbQuery;
$query->name("user")
->where("name","like","%think%")
->where("id",">",10)
->limit(10);
Db::select($query);
如果使用
Query
对象的话,select
方法之前调用的任何的链式操作都是无效。
混合查询
可以结合前面提到的所有方式进行混合查询,例如:
Db::table("think_user")
->where("name",["like","thinkphp%"],["like","%thinkphp"])
->where(function($query){
$query->where("id",["<",10],[">",100],"or");
})
->select();
生成的SQL语句是:
SELECT * FROM `think_user` WHERE ( `name` LIKE "thinkphp%" AND `name` LIKE "%thinkphp" ) AND ( `id` < 10 or `id` > 100 )
字符串条件查询
对于一些实在复杂的查询,也可以直接使用原生SQL语句进行查询,例如:
Db::table("think_user")
->where("id > 0 AND name LIKE "thinkphp%"")
->select();
为了安全起见,我们可以对字符串查询条件使用参数绑定,例如:
Db::table("think_user")
->where("id > :id AND name LIKE :name ",["id"=>0, "name"=>"thinkphp%"])
->select();
V5.0.4+
开始,ThinkPHP支持对同一个字段多次调用查询条件,例如:
Db::table("think_user")
->where("name","like","%think%")
->where("name","like","%php%")
->where("id","in",[1,5,80,50])
->where("id",">",10)
->find();
快捷方法(V5.0.5+
)
V5.0.5+
版本开始新增了一系列快捷方法,用于简化查询,包括:
方法 | 作用 |
---|---|
whereNull | 查询字段是否为Null |
whereNotNull | 查询字段是否不为Null |
whereIn | 字段IN查询 |
whereNotIn | 字段NOT IN查询 |
whereBetween | 字段BETWEEN查询 |
whereNotBetween | 字段NOT BETWEEN查询 |
whereLike | 字段LIKE查询 |
whereNotLike | 字段NOT LIKE查询 |
whereExists | EXISTS条件查询 |
whereNotExists | NOT EXISTS条件查询 |
whereExp | 表达式查询 |