//不需要 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();
TP5的一些查询
最新推荐文章于 2024-07-22 09:56:56 发布