10、ThinkPhP5：查询语言-上

1、查询表达式

        //查询表达式
        $table = Db::name('data');
        $result = $table->where('id',1)->find();
        //也可以写成
        $result = Db::name('data')->where('id',1)->find();
        //条件的基他写法 >= <= <> in[4,5,6,7,9] 'between', [5,8]
        $result = Db::name('data')->where('id','=',1)->select();
        $result = Db::name('data')->where('id','in',[1,2,3,4,5,6])->select();
        //助手函数
        $result = db('data')->where('id',1)->find();
        //查询某个字段是否为NULL
        $result = db('data')->where('name','null')->select();
        //使用EXP条件表达式，表示后面是原生的SQL语句表达式
        $result = Db::name('data')->where('id','exp','in(1,2,3,5,6,7,8)')->select();
        $result = Db::name('data')->where('id','exp','>1 and name = "222"')->select();
        
        //使用多个字段查询
        $result = Db::name('data')->where('id','>=',1)
                                  ->where('name','like','%php%')
                                  ->select();
        //数组写法
        $result = Db::name('data')
                ->where(
                    [
                        'id'=>['>=',1],
                        'name' => ['like','%think%'],
        ])->select();
        //SELECT * FROM `tp_data` WHERE `id` >= 1 AND `name` LIKE '%think%
        
        
        //OR和AND混合条件查询
        $result = Db::name('data')
        ->where('name','like','%think%')
        ->where('id',['in',[1,2,3]],['>=',1],'or')
        ->limit(2)
        ->select();
        //SELECT * FROM `tp_data` WHERE `name` LIKE '%think%' AND ( `id` IN (1,2,3) or `id` >= 1 ) LIMIT 2 

2、批量查询

 

        //批量查询
        $result = Db::name('data')->where([
            'id' =>[['in',[1,2,3]],['>=',1],'or'],
            'name' => ['like',['%php%']],
        ])
        ->limit(10)
        ->select();
        //SELECT * FROM `tp_data` WHERE ( `id` IN (1,2,3) or `id` >= 1 ) AND (`name` LIKE '%php%') LIMIT 10

3、快捷查询

        //快捷查询 and 关系
        $result = Db::name('data')
        ->where('id&status','>',0)
        ->limit(10)
        ->select();
        //SELECT * FROM `tp_data` WHERE ( `id` > 0 AND `status` > 0 ) LIMIT 10
        //或关系
        $result = Db::name('data')
        ->where('id|status','>',0)
        ->limit(10)
        ->select();
        //SELECT * FROM `tp_data` WHERE ( `id` > 0 OR `status` > 0 ) LIMIT 10 
        //dump($result);

4、视图查询

 

        //视图查询 把查出来的记录存在一个临时的虚拟表里面，就叫视图
        $result = Db::view('data','id,name,status') //'data' 是查那张表 那些字段
        ->view('users',['nickname'=>'user_name','mobile','email'],'users.user_id = data.id') //users 表，后面的数组是users 表中的那些字段 'nickname'=>'user_name' 取了一个user_name别名
        ->where('data.status',1)
        ->select();
        //SELECT `data`.`id`,`data`.`name`,`data`.`status`,`users`.`nickname` AS `user_name`,`users`.`mobile`,`users`.`email` FROM `tp_data` `data` INNER JOIN `tp_users` `users` ON `users`.`user_id`=`data`.`id` WHERE `data`.`status` = 1
      

5、使用QUERY对象

 

        //使用QUERY对象
        $query = new \think\db\Query(); //没有引用QUERY对象就需要 实例化的时候带上目录
        $query->name('data')->where('name','like','%think%')
        ->where('id','>=',1)
        ->limit(10);
        $result = Db::select($query);
        //SELECT * FROM `tp_data` WHERE `name` LIKE '%think%' AND `id` >= 1 LIMIT 10