TP5.1数据库高级查询

 

TP5.1数据库高级查询

 

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);
     
      }