11、ThinkPhP5：查询语言下

1、获取列数据

        
        //获取 某行某列某个值
        $name = Db::name('data')->where('id',1)->value('name');
        //SELECT `name` FROM `tp_data` WHERE `id` = 1 LIMIT 1 
        //获取某列
        $name = Db::name('data')->where('status',1)->column('name');
        //SELECT `name` FROM `tp_data` WHERE `status` = 1 
        //获取ID键名 nmae 位置的 键值对
        $list = Db::name('data')
        ->where('status',1)
        ->column('name','id');
        //SELECT `id`,`name` FROM `tp_data` WHERE `status` = 1 
        //结果为：键值对（数组） select 就很合适
/*      1 => string '222' (length=3)
        2 => string 'hello' (length=5)
        3 => string '2222222' (length=7)
        4 => string '' (length=0)
        6 => string 'thinkphp' (length=8)
        7 => string 'thinkphp' (length=8)
        12 => string '' (length=0)
        13 => string 'thinkphp' (length=8)
        14 => string 'thinkphp' (length=8)
        15 => string 'thinkphp' (length=8)
        16 => string 'thinkphp' (length=8)
        20 => string '222' (length=3)
        25 => string 'thinkphp' (length=8)
        33 => string 'thinkphp' (length=8)
        100 => string 'thinkphp' (length=8) */
        //获取ID键名的数据集
        $list = Db::name('data')
        ->where('status',1)
        ->column('*','id');
        // SELECT * FROM `tp_data` WHERE `status` = 1 

2、聚合查询

      //聚合查询 count max min avg sum
        $count = Db::name('data')->where('status',1)->count();
        echo $count;

3、字符串查询

        //建议字符串 简单查询
        $result =  Db::name('data')
        ->where('id >:id and name like "%php%"',['id' =>10])
        ->select();
        //SELECT * FROM `tp_data` WHERE ( id >'10' and name like "%php%" ) 
        $result =  Db::name('data')
        ->where('id >:id and name  like :name',['id' =>10,'name'=>'%php%'])
        ->select();
        //SELECT * FROM `tp_data` WHERE ( id >'10' and name like '%php%' ) 
        

4、日期查询

 

        //日期查询 建议 日期类型使用 int 自动转换成时间戳
        //查询时间大于 2016-1-1的数据
        $result = Db::name('users')
        ->whereTime('reg_time', '>','2016-1-1')
        ->select();
        //SELECT * FROM `tp_users` WHERE `reg_time` > 1451577600
        //查询本周
        $result =  Db::name('users')
        ->whereTime('reg_time','>', 'this week')
        ->select();
        //SELECT * FROM `tp_users` WHERE `reg_time` > 1541402547
        //查寻最近两天添加的数据
        $result = Db::name('users')
        ->whereTime('reg_time', '>','-2 days')
        ->select();
        //SELECT * FROM `tp_users` WHERE `reg_time` > 1541318157
        //查寻创建日期 2016-1-1~2018-11-6 的数据
        $result = Db::name('users')
        ->whereTime('reg_time', 'between',['2016-1-1','2018-11-6'])
        ->select();
        //SELECT * FROM `tp_users` WHERE `reg_time` BETWEEN 1451577600 AND 1541433600 
        //获取今天的数据 today, 昨天的yesterday, 本周 week 上周 last week
        $result = Db::name('users')
        ->whereTime('reg_time', 'today')
        ->select();
        //SELECT * FROM `tp_users` WHERE `reg_time` BETWEEN 1541433600 AND 1541520000 

5、分块查询

        //分块查询
        Db::name('data')
        ->where('status','>',0)
        ->chunk(2, function($list){ //2代表每次查寻几条
            foreach ($list as $data){
                //处理100条记录
            }
        });
        //也可以使用循环改造后
        $p = 0;
        do{
            $result = Db::name('data')->limit($p,2)->select();
            $p += 2;
            print_r($result);
        } while (count($result) > 0);