public function gaoji()
{
//快捷查询
$data=Db::table('attr')
->where('name|color','like','%d')
// ->where()
->find();
// 在多个字段之间用|分割表示OR查询 用&分割表示AND查询
//区间查询
$data=Db::table('attr')
->where('name', ['like', '%头'], ['like', '头%'], 'or')
->where('id', ['>', 0], ['<>', 5], 'and')
->find();
//高级查询sql语句为(<> 是不等于的意思):
//SELECT * FROM `think_user` WHERE ( `name` LIKE '%thinkphp%' OR `name` LIKE '%kancloud%' ) AND ( `id` > 0 AND `id` <> 10 ) LIMIT 1
$data=Db::table('attr')
->where([
['name', 'like', '%o%'],
['color', 'like', '%o%'],
['id', '>', 0],
['status', '=', 1],
])
->select();
//高级查询sql语句为
//SELECT * FROM `think_user` WHERE `name` LIKE 'thinkphp%' AND `title` LIKE '%thinkphp' AND `id` > 0 AND `status` = '1'
//批量(字段)查询
$data=Db::table('attr')
->where([
['name', 'like', '%o%'],
['color', 'like', '%o%'],
['id', 'exp', Db::raw('>create_time')],
['status', '=', 1],
])
->fetchSql(true)
->select();
//打印出来sql语句 是 :"SELECT * FROM `attr` WHERE `name` LIKE '%o%' AND `color` LIKE '%o%' AND ( `id` >creat_time ) AND `status` = 1"
$map = [
['name', 'like', 'thinkphp%'],
['title', 'like', '%thinkphp'],
['id', '>', 0],
];
$data=Db::table('attr')
->where([ $map ])
->where('status',1)
->fetchSql(true)
->select();
//打印出来SQl语句是: "SELECT * FROM `attr` WHERE ( `name` LIKE 'thinkphp%' AND `title` LIKE '%thinkphp' AND `id` > 0 ) AND `status` = 1"
$map1 = [
['name', 'like', '%o%'],
['color', 'like', '%o%'],
];
$map2 = [
['name', 'like', '%o%'],
['color', 'like', '%o%'],
];
$data= Db::table('attr')
->whereOr([ $map1, $map2 ])
->fetchSql(true)
->select();
//打印出来SQL语句是 :"SELECT * FROM `attr` WHERE ( `name` LIKE '%o%' AND `color` LIKE '%o%' ) OR ( `name` LIKE '%o%' AND `color` LIKE '%o%' )"
//数组对象查询
$map = [
'name' => ['like', '%o%'],
'color' => ['like', '%o%'],
'id' => ['>', 10],
'status' => 1,
];
$where = new Where;
$where['id'] = ['in', [1, 2, 3]];
$where['color'] = ['like', '%o%'];
$data=Db::table('attr')
->where(new Where($map))
->whereOr($where->enclose())
->fetchSql(true)
->select();
//"SELECT * FROM `attr` WHERE `name` LIKE '%o%' AND `color` LIKE '%o%' AND `id` > 10 AND `status` = 1 OR ( `id` IN (1,2,3) AND `color` LIKE '%o%' )"
// enclose方法表示该查询条件两边会加上括号包起来。
// 闭包查询
$name = 'thinkphp';
$id = 10;
$data=Db::table('attr')->where(function ($query) use($name, $id) {
$query->where('name', $name)
->whereOr('id', '>', $id);
})->select();
// SELECT * FROM `attr` WHERE ( `name` = 'thinkphp' OR `id` > 10 )
// 混合查询
$data= Db::table('attr')
->where('name', ['like', 'thinkphp%'], ['like', '%thinkphp'])
->where(function ($query) {
$query->where('id', ['<', 10], ['>', 100], 'or');
})
->select();
//SELECT * FROM `attr` WHERE (`name` LIKE 'thinkphp%' AND `name` LIKE '%thinkphp' ) AND ( `id` < 10 or `id` > 100)
//字符串条件查询
$data= Db::table('attr')
->whereRaw('id > :id AND name LIKE :name ', ['id' => 0, 'name' => 'thinkphp%'])
->fetchSql(true)
->select();
//"SELECT * FROM `attr` WHERE ( id > 0 AND name LIKE 'thinkphp%' )"
//如果你要使用字符串条件查询,推荐使用whereRaw方法。
// //使用Query对象查询
$query = new \think\db\Query;
$query->where('id','>',0)
->where('name','like','%thinkphp');
Db::table('think_user')
->where($query)
->select();
//Query对象的where方法仅能调用一次,如果query对象里面使用了非查询条件的链式方法,则不会传入当前查询。
$query = new \think\db\Query;
$query->where('id','>',0)
->where('name','like','%thinkphp')
->order('id','desc') // 不会传入后面的查询
->field('name,id'); // 不会传入后面的查询
Db::table('think_user')
->where($query)
->where('title','like','thinkphp%') // 有效
->select();
// 条件查询
//5.1的查询构造器支持条件查询,例如:
Db::name('user')->when($condition, function ($query) {
// 满足条件后执行
$query->where('score', '>', 80)->limit(10);
})->select();
//并且支持不满足条件的分支查询
Db::name('user')->when($condition, function ($query) {
// 满足条件后执行
$query->where('score', '>', 80)->limit(10);
}, function ($query) {
// 不满足条件执行
$query->where('score', '>', 60);
});
// 方法 作用
// whereOr 字段OR查询
// whereXor 字段XOR查询
// 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 表达式查询
// whereColumn 比较两个字段
return json($data);
}
TP5.1数据库高级查询
最新推荐文章于 2022-08-16 13:49:37 发布