thinkphp笔记-查询表达式where、时间查询、聚合、原生、子查询

9453次阅读 303人点赞 作者: WuBin 发布时间: 2024-04-01 14:12:06
扫码到手机查看

查询表达式

比较查询

查询表达式支持大部分常用的SQL 语句,语法格式如下:

where('字段名','查询表达式','查询条件');

在查询数据进行筛选时,我们采用where()方法,比如id=80;

Db::name('user')->where('id', 80)->find();
等价
Db::name('user')->where('id','=',80)->find();

使用<>、>、<、>=、<=可以筛选出各种符合比较值的数据列表(找到不等于19的全部记录);

Db::name('user')->where('id','<>',19)->select();

区间查询

idemailpassworduid
19xiaoming@163.com12311
38

yihu@163.com

eqwenull
20xiaoxin@qq.comsda876
41

wufan@163.com

adsanull

使用like 表达式进行模糊查询;

$user = Db::name('user')->where('email', 'like', 'xiao%')->select();
// 找到email前面是 xiao开头的项

like 表达式还可以支持数组传递进行模糊查询;

$user = Db::name('user')->where('email', 'like', ['xiao%', 'wu%'])->select();
// SELECT * FROM `tp_user` WHERE (`email` LIKE 'xiao%' AND `email` LIKE 'wu%')

return Db::getlastSql();

如果不传递第四个参数,那么默认是and,所以这里我们给它传入第四个参数or(无所谓大小写)

 $user = Db::name('user')->where('email', 'like', ['xiao%', 'wu%'], 'or')->select();
// SELECT * FROM `tp_user` WHERE (`email` LIKE 'xiao%' OR `email` LIKE 'wu%')

like 表达式具有两个快捷方式whereLike()和whereNoLike();

 $user = Db::name('user')->whereLike('email', 'xiao%')->select();
Db::name('user')->whereNotLike('email', 'xiao%')->select(); // 反向,全部email中不含有xiao的

between 表达式具有两个快捷方式whereBetween()和whereNotBetween();

 // 列出id在21~27之间的项 以下均效果等价
$user = Db::name('user')->where('id', 'between', '21, 27')->select();
// 同时也支持数组方式
$user = Db::name('user')->where('id', 'between', [21, 27])->select();
$user = Db::name('user')->whereBetween('id', [21, 27])->select();
$user = Db::name('user')->whereBetween('id', '21, 27')->select();

in 表达式具有两个快捷方式whereIn()和whereNotIn();

// 以下语句效果等价
$user = Db::name('user')->where('id', 'in', '25, 26,29')->select();
$user = Db::name('user')->where('id', 'in', [25, 26,29])->select();
Db::name('user')->whereIn('id','19,21,29')->select();
Db::name('user')->whereNotIn('id','19,21,29')->select();

null 表达式具有两个快捷方式whereNull()和whereNotNull();

 // 找到所有的null数据 和 非null数据 以下效果等价
$user = Db::name('user')->where('uid', 'null')->select();
$user = Db::name('user')->where('uid', 'not null')->select();
Db::name('user')->whereNull('uid')->select();
Db::name('user')->whereNotNull('uid')->select();

EXP 查询

假设提供的查询方法没有想要的,那么使用exp 可以自定义字段后的SQL 语句;

exp方法主要就是使用mysql原生的查询语句进行拼装。

比如:Db::name('user')->select(); 等价于 SELECT * FROM `tp_user`

Db::name('user')->where('id','in','19,21,22')->select(); 等价于 SELECT * FROM `tp_user` WHERE `id` IN (19,21,22)

所以使用exp实现的方法就是:

Db::name('user')->where('id', 'exp', 'IN (19,21,22)')->select();
// 等价于
SELECT * FROM `tp_user` WHERE ( `id` in (19,21,22) )

即第二个参数传递exp,然后第三个参数传递一个sql语句

同样的也有一个简单方法

Db::name('user')->whereExp('id', 'IN (19,21,25)')->select();

时间查询

传统方式

可以使用>、<、>=、<=来筛选匹配时间的数据;

 // 大于2018-1-1日期的
$user = Db::name('user')->where('create_time', '>', '2018-01-01')->select();

可以使用between 关键字来设置时间的区间;

Db::name('user')->where('create_time', 'between', ['2018-01-01', '2019-12-1'])->select();

以及反向区间

 // 反向区间操作
$user = Db::name('user')->where('create_time', 'not between', ['2018-01-01', '2019-12-1'])->select();

快捷方式

时间查询的快捷方法为whereTime(),直接使用>、<、>=、<=;

Db::name('user')->whereTime('create_time', '>', '2018-01-01')->select();

默认的大于>,可以省略(如果表达式是大于某个时间),一般不要省略,语义化更好

Db::name('user')->whereTime('create_time', '2018-01-01')->select();

快捷方式也可以使用between 和not between;

Db::name('user')->whereBetween('create_time', ['2018-01-01', '2019-12-1'])->select();
Db::name('user')->whereNotBetween('create_time', ['2018-01-01', '2019-12-1'])->select();

还有一种快捷方式为:whereBetweenTime()和whereNotBetweenTime();这种形式就是不用数组,而用字符串的形式。

Db::name('user')->whereBetweenTime('create_time', '2018-01-01', '2019-12-1')->select();

固定查询

固定查询就是固定查询今天、当月、本周之类的操作,使用whereYear 查询今年的数据、去年的数据和某一年的数据。比如,我要查找时间在今年的数据:

Db::name('user')->whereYear('create_time')->select();

如果要查询去年的数据,那么就需要传入第二个参数

 Db::name('user')->whereYear('create_time', 'last year')->select();

如果要找指定某一年份的数据:

Db::name('user')->whereYear('create_time', '2016')->select();

使用whereMonth 查询当月的数据、上月的数据和某一个月的数据;

Db::name('user')->whereMonth('create_time')->select();
Db::name('user')->whereMonth('create_time', 'last month')->select();
Db::name('user')->whereMonth('create_time', '2016-6')->select();

使用whereDay 查询今天的数据、昨天的数据和某一个天的数据;

Db::name('user')->whereDay('create_time')->select();
Db::name('user')->whereDay('create_time', 'last day')->select();
Db::name('user')->whereDay('create_time', '2016-6-27')->select();

其他查询

比如我要找到两个小时之内新增的数据、两个小时内注册的用户全部列出来,就可以使用这个方法

$user = Db::name('user')->whereTime('create_time', '-2 hours')->select();
// SELECT * FROM `tp_user` WHERE `create_time` >= '2024-04-02 08:14:00'
// 当前时间是2024-04-02 10:14:00 所以sql语句就是大于8:14的时间筛选
return Db::getLastSql();

查询两个时间字段时间有效期的数据,比如会员开始到结束的期间;比如,查询两个字段的之间的有效期,根据会员开始时间和结束时间去查找一下有哪些,在有效期内的会员以及在有效期之内的活动等。

 $user = Db::name('user')->whereBetweenTimeField('create_time', 'update_time')->select();
// SELECT * FROM `tp_user` WHERE `create_time` <= '2024-04-02 10:19:41' AND `update_time` >= '2024-04-02 10:19:41'
return Db::getLastSql();

聚合.原生.子查询

聚合查询

系统提供了一系列的方法,更好的计算所有列整合的数据。

使用count()方法,可以求出所查询数据的数量;

 $result = Db::name('user')->count();
return json($result);
// 返回17 代表共有17条数据

此返回的sql语句为:SELECT COUNT(*) AS think_count FROM `tp_user` LIMIT 1

count()可设置指定id,比如有空值(Null)的uid,不会计算数量,即排除值为null的行,统计其余有值行的数量;

$result = Db::name('user')->count('uid');
return json($result);

使用max()方法,求出所查询数据字段的最大值;

$result = Db::name('user')->max('price');

使用min()方法,求出所查询数据字段的最小值

$result = Db::name('user')->min('price');

min、max方法都有第二个参数,如果得到的结果不是数值,则通过第二参数强制转换;比如

 $result = Db::name('user')->min('email'); // 得到的是0 

然后我们传入第二个参数,不要求其强制转换

$result = Db::name('user')->min('email', false); // 得到HUIYE@163.COM

avg()方法,求出所查询数据字段的平均值,sum()方法,求出所查询数据字段的总和;

$result = Db::name('user')->avg('price');
$result = Db::name('user')->sum('price');

子查询

使用fetchSql()方法,可以设置不执行SQL,而返回SQL 语句,默认true;

$result = Db::name('user')->fetchSql(true)->select();
return json($result);
// 结果返回 "SELECT * FROM `tp_user`" 所以它返回的是一个sql语句

如果传入的是false,那么就会执行语句,得到查询结果

 $result = Db::name('user')->fetchSql(false)->select();
$result = Db::name('user')->buildSql(true);
return json($result);
// 得到的结果带括号,方便拼接查询的时候不需要拼装了( SELECT * FROM `tp_user` )

使用buildSql()方法,也是返回SQL 语句,不需要再执行select(),且有括号;

下面来看一个子查询的例子,有如下两张表:

tp_two:

uidgender
1
2
3
4
25

tp_one:

iduserage
1张三55
2李四41
3王五34
4马七56
25赵八98

我们要实现根据第one表的id=two表的uid且是男的行。

第一步,求出所有男的uid

 // 求出所有男的uid 返回其sql语句,如果末尾换成->selcet()则会输出结果
$subQuery = Db::name('two')->field('uid')->where('gender', '男')->buildSql(true);
// 得到 ( SELECT `uid` FROM `tp_two` WHERE  `gender` = '男' )

第二步,找到id在第一步区间内的行

// 这个写法不对
$result = Db::name('one')->where('id', 'in', $subQuery)->select();
return Db::getLastSql();

上面的写法会返回:SELECT * FROM `tp_one` WHERE `id` = ( SELECT `uid` FROM `tp_two` WHERE `gender` = '男' ) 这个语句不对,所以第二个参数不能用In,应该使用exp进行拼接。

$result = Db::name('one')->where('id', 'exp', 'IN ' . $subQuery)->select();
// SELECT * FROM `tp_one` WHERE ( `id` IN ( SELECT `uid` FROM `tp_two` WHERE `gender` = '男' ) )
return Db::getLastSql();

最终得到的结果就是:

[
{
id: 1,
user: "张三"
},
{
id: 3,
user: "王五"
},
{
id: 4,
user: "马七"
}
]

使用闭包的方式执行子查询

// $query 就是数据库链接对象 相当于Db
$result = Db::name('one')->where('id', 'in', function($query) {
    $query->name('two')->field('uid')->where('gender', '男');
})->select();

以上得到的sql语句也是:SELECT * FROM `tp_one` WHERE `id` IN (SELECT `uid` FROM `tp_two` WHERE `gender` = '男')

原生查询

使用query()方法,进行原生SQL 查询,适用于读取操作,SQL 错误返回false

$result = Db::query('SELECT * FROM tp_user');
return json($result);

使用execute 方法,进行原生SQL 更新写入等,SQL 错误返回false;

$result = Db::execute('UPDATE tp_user SET username="孙武" WHERE id=29');
return json($result);

点赞 支持一下 觉得不错?客官您就稍微鼓励一下吧!
关键词:where
推荐阅读
  • uniapp实现被浏览器唤起的功能

    当用户打开h5链接时候,点击打开app若用户在已经安装过app的情况下直接打开app,若未安装过跳到应用市场下载安装这个功能在实现上主要分为两种场景,从普通浏览器唤醒以及从微信唤醒。

    8186次阅读 521人点赞 发布时间: 2022-12-14 16:34:53 立即查看
  • Vue

    盘点Vue2和Vue3的10种组件通信方式

    Vue中组件通信方式有很多,其中Vue2和Vue3实现起来也会有很多差异;本文将通过选项式API组合式API以及setup三种不同实现方式全面介绍Vue2和Vue3的组件通信方式。

    3282次阅读 241人点赞 发布时间: 2022-08-19 09:40:16 立即查看
  • JS

    几个高级前端常用的API

    推荐4个前端开发中常用的高端API,分别是MutationObserver、IntersectionObserver、getComputedstyle、getBoundingClientRect、requ...

    13228次阅读 854人点赞 发布时间: 2021-11-11 09:39:54 立即查看
  • PHP

    【正则】一些常用的正则表达式总结

    在日常开发中,正则表达式是非常有用的,正则表达式在每个语言中都是可以使用的,他就跟JSON一样,是通用的。了解一些常用的正则表达式,能大大提高你的工作效率。

    12104次阅读 385人点赞 发布时间: 2021-10-09 15:58:58 立即查看
  • 【中文】免费可商用字体下载与考证

    65款免费、可商用、无任何限制中文字体打包下载,这些字体都是经过长期验证,经得住市场考验的,让您规避被无良厂商起诉的风险。

    10347次阅读 829人点赞 发布时间: 2021-07-05 15:28:45 立即查看
  • Vue

    Vue3开发一个v-loading的自定义指令

    在vue3中实现一个自定义的指令,有助于我们简化开发,简化复用,通过一个指令的调用即可实现一些可高度复用的交互。

    14248次阅读 1132人点赞 发布时间: 2021-07-02 15:58:35 立即查看
  • JS

    关于手机上滚动穿透问题的解决

    当页面出现浮层的时候,滑动浮层的内容,正常情况下预期应该是浮层下边的内容不会滚动;然而事实并非如此。在PC上使用css即可解决,但是在手机端,情况就变的比较复杂,就需要禁止触摸事件才可以。

    14180次阅读 1156人点赞 发布时间: 2021-05-31 09:25:50 立即查看
  • Vue

    Vue+html2canvas截图空白的问题

    在使用vue做信网单页专题时,有海报生成的功能,这里推荐2个插件:一个是html2canvas,构造好DOM然后转canvas进行截图;另外使用vue-canvas-poster(这个截止到2021年3月...

    27073次阅读 2131人点赞 发布时间: 2021-03-02 09:04:51 立即查看
  • Vue

    vue-router4过度动画无效解决方案

    在初次使用vue3+vue-router4时候,先后遇到了过度动画transition进入和退出分别无效的情况,搜遍百度没没找到合适解决方法,包括vue-route4有一些API都进行了变化,以前的一些操...

    23237次阅读 1791人点赞 发布时间: 2021-02-23 13:37:20 立即查看
交流 收藏 目录