TP5的一些查询

一时失言乱红尘 2022-12-02 01:00 206阅读 0赞

//不需要  use app\index\model\Store;
            $res = model('store')->all();
            $res = collection($res)->toArray();
     
            //需要  use app\index\model\Store;
            $res = store::select();
            $res = collection($res)->toArray();
     
            //六 必须指定完整表名
            $res = Db::table('jh_store')->select();
            //加false只返回sql语句 或者在select前面加上->fetchSql(true) 或者用getlastsql() 或者->buildSql();
            $res = Db::table('jh_store')->select(false);
            
            //七 原生查询
            $res = Db::query("select * from jh_store where store_id=127");
     
            //八也可以在table方法中指定数据库
            $res = Db::table('tianxi.tx_admin')->where('admin_id>1')->select();
            //__大写表名__这样写来代替全表名
            $res2 = Db::table('__STORE__')->select();
            //多表操作
            $res3 = Db::field('a.store_id,a.title,b.store_goods_id,b.shop_goods_name')
                ->table(['jh_store'=>'a','jh_store_goods'=>'b'])
                ->limit(10)->select();
     
             $res4 = Db::field('a.store_id,a.title,b.store_goods_id,b.shop_goods_name')
             ->table('jh_store')->alias('a')
             ->join('jh_store_goods b','b.store_id=a.store_id','LEFT')
             ->limit(10)->select();
             $res5 = Db::table('jh_store_goods')->where('store_id','127')->limit(100)->select();
             Db::table('think_user')->alias(['think_user'=>'user','think_dept'=>'dept'])->join('think_dept','dept.user_id= user.id')->select();
            
            //分页完整查询
             $where['id']  = ['>',1];
             $where['mail']  = ['like','%thinkphp@qq.com%'];
             $field = 'a.id,a.user_id,a.grades,a.is_involved,b.user_id,b.user_name,b.open_id,b.avatar,b.phone,c.user_id,c.alias_name';
             $join = [
                 ['tx_user b', 'a.user_id=b.user_id', 'left'],
                 ['tx_user_info c', 'a.user_id=c.user_id', 'left']
             ];
             $data['user'] = Db::table('tx_game_record')->alias('a')->join($join)->field($field)->where($where)->paginate(10);
             $data['page'] = $data['user']->render();
             $data['total'] = $data['user']->total();
            
            //可以在查询中直接使用函数
            $res = Db::name('order_goods')->field('order_goods_id,SUM(price)')->select();
            //字段排除
            Db::table('think_user')->field('user_id,content',true)->select();
            //或者用
            Db::table('think_user')->field(['user_id','content'],true)->select();
     
            //分页查询
            Db::name('order_goods')->page('1,10')->select();//查询第一页的十条数据
            Db::name('order_goods')->page('2,10')->select();//查询第二页的十条数据
            $res = Db::name('order_goods')->limit(10)->page(2)->select();//查询第二页的十条数据
            
            //合并两个或多个语句的结果集
             $res = Db::field('name')->table('think_user')
             ->union('select name from think_user_1')
             ->union('select name from think_user_2')
             //->union(['SELECT name FROM think_user_1','SELECT name FROM think_user_2'])
             ->select();
            
            //去除重复
            Db::name('user')->distinct(true)->field('name')->select();
            //SQL:select distinct name from user
     
            //给sql语句添加注释
            Db::table('tp_user')
                ->comment('查询前十名的学生')
                ->field('name')
                ->limit(10)
                ->order('id asc')
                ->select();