TP5 后端小知识点总结(后续会不断继续更新)

一、TP5 模糊查询-多条件-多种查询形式

1、具有相同效果单个字段对应单个查询条件

$where[] = ['title','like',"%" . $keyword . "%"]; $where['title'] = ['like', "%" . $keyword . "%"]; where('title', 'like', "%" . $keyword . "%");

2、具有相同效果单个字段对应多个查询条件

where('title', 'like', ["%" . $keyword1 . "%", "%" . $keyword2 . "%"], 'OR'); $where['title'] = ['like', ["%" . $keyword1 . "%", "%" . $keyword2 . "%"], 'OR'];

3、具有相同效果多个字段对应单个查询条件

$where['nickname|username|title'] = ['like', "%" . $keyword . "%"]; $where = [ ['nickname', 'like', "%" . $keyword . "%"], ['username', 'like', "%" . $keyword . "%"], ['title', 'like', "%" . $keyword . "%"], ];

4、多个字段对应多个查询条件

$where = [ ['nickname', 'like', "%" . $keyword1 . "%"], ['username', 'like', "%" . $keyword2 . "%"], ['title', 'like', "%" . $keyword3 . "%"], ];

5、多字段分别从左右两边进行匹配

$where['nickname|username|title'] = ['like', "%" . $keyword]; //从左侧开始匹配查询 $where['nickname|username|title'] = ['like', $keyword . "%"]; //从右侧开始匹配查询

二、TP5 多种运算符查询

条件表达式格式为：$where['字段名'] = ['TP运算符', '操作条件']; TP运算符SQL运算符示例实际查询条件eq=$where[‘id’] = [‘eq’, 100]id = 100neq=$where[‘id’] = [‘neq’, 100]id <> 100gt>$where[‘id’] = [‘gt’, 100]id > 100egt>=$where[‘id’] = [‘egt’, 100]id >= 100lt<$where[‘id’] = [‘lt’, 100]id < 100elt<=$where[‘id’] = [‘elt’, 100]id <= 100likelike$where[‘username’] = [‘like’, ‘%ThinkPHP%’]username like ‘%ThinkPHP%’betweenbetween and$where[‘id’] = [‘between’, ‘1,10’]id between 1 and 10not betweennot between and$where[‘id’] = [‘not between’, ‘1,10’]id not between 1 and 10inin$where[‘id’] = [‘in’, ‘1,2,3,4,5’]id in(‘1,2,3,4,5’)not innot in$where[‘id’] = [‘not in’, ‘1,2,3,4,5’]id not in(‘1,2,3,4,5’)andand$where[‘id’] = [[‘gt’, 5], [‘elt’, 10]](id > 5) and (id <= 10)oror$where[‘id’] = [[‘elt’, 5], [‘gt’, 10], ‘or’](id <= 5) or (id > 10)exp综合表达式$where[‘id’] = [‘exp’, ‘in(4,5,6)’] 等同于 $where[‘id’] = [‘in’, ‘4,5,6’]id in(4,5,6)

备注：

ThinkPHP运算符不区分大小写，例如：eq与EQ效果相同。between 和 in 支持字符串或者数组写法，如下所示： $where['id'] = ['in','1,5,8']; 等同于 $where['id'] = ['in', ['1','5','8']]; $where['between'] = ['between','1,5']; 等同于 $where['between'] = ['between', ['1','5']]; 上表中的 exp 不是一个运算符，而是一个综合表达式以支持更复杂的条件设置。exp 的操作条件不会被当成字符串，可以使用任何 SQL 支持的语法，包括使用函数和字段名称，exp 不仅用于 where 条件，也可以用于数据更新。示例如下： // exp作为where查询条件 where('id','exp','in(4,5,6)'); 等效于 where('id','in','4,5,6'); 等效于 $where['id'] = ['in','4,5,6']; 等效于 $where['id'] = ['in',['4','5','6']]; $where['hits'] = ['exp', 'hits + 1']; // 构建update的数组，文章点击量 +1 where查询语句还支持其他写法： where('id', 'eq', 100); 等同于 where(['id' => 100]); 等同于 where('id', 100); 等同于 where('id', '=', 100);

三、TP5 多张表链式查询(join)

1、链式查询

$orderInfo = Db::name('order') ->alias('a') ->join('order_details b', 'a.id = b.order_id', 'left') ->join('address c', 'a.address_id = c.id', 'left') ->join('classify d', 'b.classify_id = d.id', 'left') ->join('receive e', 'c.receive_id = e.id', 'left') ->where(['a.id' => $order_id]) ->field("a.id as order_id,a.pay_time,from_unixtime(a.create_time, '%Y/%m/%d %H:%i:%s') as create_time, b.pickinfo,b.fragile_data,b.downstairs_data,b.images as pick_images, c.mobile,c.address, d.name as classify_name, e.build_name,e.dormitory_data") ->find();

备注：

from_unixtime(a.create_time, '%Y/%m/%d %H:%i:%s') as create_time // SQL转化时间戳

2、聚合查询

// 获取用户数 Db::table('user')->where(['id' => $user_id])->count(); // 根据字段获取用户数 Db::table('user')->where(['id' => $user_id])->count('username'); // 根据字段获取用户最高分 Db::table('user')->where(['id' => $user_id])->max('score'); // 根据字段获取用户最低分 Db::table('user')->where(['id' => $user_id])->min('score'); // 根据字段获取用户平均分 Db::table('user')->where(['id' => $user_id])->avg('score'); // 根据字段获取用户分数总和 Db::table('user')->where(['id' => $user_id])->sum('score');