tp5的一些查询

//不需要 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();