SQL SELECT语句的执行顺序:
1、from子句组装来自不同数据源的数据;
2、where子句基于指定的条件对记录行进行筛选;
3、group by子句将数据划分为多个分组;
4、使用聚集函数进行计算;
5、使用having子句筛选分组;
6、计算所有的表达式;
7、使用order by对结果集进行排序;
8、select 集合输出。
前言:
1. group by:如何让 group by分组后,每组中的所有数据都显示出来
问题描述:
表如下,如何让这个表按device_id这个字段分组,且组中的每条数据都查寻出来?(假如说这个表名为:devicedata)
错误答案:select * from devicedata GROUP BY device_id
这个sql得到的结果是:
每一组只显示了一条数据, 显然没达到我们的目的。
正确的结果:select * from devicedata GROUP BY device_id, id
查询到的结果:
这样就达到了我们的目的了,将每组中的每条数据都查寻出来了。
解释一下这条sql:
select * from devicedata GROUP BY device_id, id
这条sql就是,首先根据device_id这个字段来分组,因为这个表里面的id为1、3、4、5的记录 他们的device_id的值相同,所以就查询了这个组中的一条,然后在将1、3、4、5这四条记录按id分组,因为这四条记录的id都不同,所以每一条都查寻出来了。
2.时间格式化:
SELECT date_format('2022-07-06 16:26:20.125', '%Y-%m-%d %H:%i:%S')
年:
%Y 四位数字表示的年份(2015,2016...)
%y 两位数字表示的年份(15,16...)
月:
%M 英文月名(January,February, ...,December)
%b 英文缩写月名(Jan,Feb, ...,Dec)
%m 两位数字表示月份(01,02, ...,12)
%c 数字表示月份(1,2, ...,12)
日:
%d 两位数字表示月中天数(01,02, ...,31)
%e 数字表示月中天数(1,2, ...,31)
%D 英文后缀表示月中天数(1st,2nd,3rd ...)
%j 以三位数字表示年中天数(001,002, ...,366)
时:
%H 24小时制,两位数形式小时(00,01, ...,23)
%h 12小时制,两位数形式小时(00,01, ...,12)
%k 24小时制,数形式小时(0,1, ...,23)
%l 12小时制,数形式小时(0,1, ...,12)
%T 24小时制,时间形式(HH:mm:ss)
%r 12小时制,时间形式(hh:mm:ss AM 或 PM)
%p AM上午或PM下午
分:
%I、%i 两位数字形式的分( 00,01, ..., 59)
秒:
%S、%s 两位数字形式的秒( 00,01, ..., 59)
周:
%W 一周中每一天的名称(Sunday,Monday, ...,Saturday)
%a 一周中每一天名称的缩写(Sun,Mon, ...,Sat)
%w 以数字形式标识周(0=Sunday,1=Monday, ...,6=Saturday)
%U 数字表示周数,星期天为周中第一天
%u 数字表示周数,星期一为周中第一天
文字输出:
%文字 直接输出文字内容
3.获取当前时间:now()
4.find_in_set函数:
find_in_set(a,b)a单个内容,而b是多个内容
5.使用group by函数后,获取最新日期的那一条数据
select member_id,max(add_time),name from print_share_log where store_id = #{storeId} and share_id = #{shareId} group by member_id
在group by 之后,可以直接使用max函数
---------------------------------------------------------------------------------------------------------------------------------
需求1:分组获取某天的数据数量,例如每一天新注册的用户数量,效果图:
select date_format(created_time,'%Y-%m-%d') as created_time,count(created_time) as couont from print_user where created_time is not null group by date_format(created_time,'%y-%m-%d') order by created_time desc;
解释:查询表 print_user 然后先排除 create_time 为 null 的数据,然后按照 create_time 分组,使用 date_format(created_time,'%y-%m-%d') 来进行格式化,例如格式化的结果为 '2023-01-09' 那就按照这个时间来分组,然后select 做统计
注:avg()忽略值为null的行,count(*)时统计所有行,count(列)时忽略为null的行
对应的mybatisplus:
QueryWrapper<PrintUser> printUserQueryWrapper = new QueryWrapper<>(); printUserQueryWrapper .select("date_format(created_time,'%Y-%m-%d') as created_time,count(created_time) as couont") .eq("referral_id",dealerUser.getId()) .isNotNull("created_time") .groupBy("date_format(created_time,'%y-%m-%d')") .orderByDesc("created_time"); // 使用selectmaps 可以直接返回 created_time , couont List<Map<String, Object>> mapList = printUserMapper.selectMaps(printUserQueryWrapper);
需求2:获取10天内,每一天的用户新注册数量,没有数据的话,就补0,下图:
这个需求并没有使用sql语句直接写出,查询网上的资料,也并不能完成理解,所以我结合了一下我自己的方法,使用java的map集合以及sql语句,做出类似的结果:
先查询出10天内注册,且有数据的统计一个统计结果,然后把10天内的每一天日期写入到一个map结果:
然后把查询出来的结果再赋值到map中:
LocalDate now = LocalDate.now(); Map<String, String> map = new HashMap<>(); for (int i = 0; i < 10; i++) { map.put(now.minusDays(i).toString(),"0"); } System.out.println(map); List<Map<String, String>> resultList = printUserMapper.getWithint10DaysNewUserCount(); for (Map<String, String> Map : resultList) { map.put(Map.get("created_time"),Map.get("count")); } TreeMap<String, String> stringStringTreeMap = new TreeMap<>(map); System.out.println(stringStringTreeMap);
mapper接口中的select语句:
@Select(value = "select date_format(created_time, '%Y-%m-%d') as created_time, count(created_time) as count from print_user where created_time >= date_sub(date_format(now(), '%Y-%m-%d'), interval 9 day) group by date_format(created_time, '%Y-%m-%d')") List<Map<String,String>> getWithint10DaysNewUserCount();
需求3:递归查询mysql中分层菜单,在java中stream流处理结果,变成树
需求图:
该需求的难度较大,我单独写了一篇文章:大家可以去参考一下
mysql数据库递归查询树形结构(适用场景:菜单多级分类,多级关联评论查询)_流连勿忘返的博客-CSDN博客
需求4:模糊匹配字段里面的json数据
需求:
现在在表里面有一个字段pa_data,其中一条数据保存的值:[{"barcode":"169231110065","converts":1.00,"id":"1692358969441239041"}] 然后想对pa_data字段里面的barcode这个key对应的value值做模糊匹配
sql语句:SELECT * FROM your_table WHERE JSON_EXTRACT(pa_data, '$[*].barcode') REGEXP 'your_string'
在上述SQL语句中,
your_table
是表名,pa_data
是保存JSON字符串的字段名,your_string
是你想要模糊匹配的字符串。JSON_EXTRACT(pa_data, '$[*].barcode')
用于提取JSON字段中barcode
键的值,并将其与REGEXP
正则表达式进行匹配。请注意,在使用
REGEXP
进行模糊匹配时,你可以编写自己的正则表达式来匹配特定的模式,例如:
your_string
:模糊匹配your_string
字符串出现在barcode
值的任意位置。^your_string
:模糊匹配以your_string
字符串开头的barcode
值。your_string$
:模糊匹配以your_string
字符串结尾的barcode
值。
需求5:同一天,同一账号视为一条数据(要用到去重),然后统计七天内,每一天都有几条数据
select card_share_log.add_time as add_time ,count(add_time) as count from (select DISTINCT member_id,date_format(add_time, '%Y-%m-%d') as add_time from card_share_log where share_id = #{shareId} and type = '4') card_share_log where card_share_log.add_time >= date_sub(date_format(now(), '%Y-%m-%d'), interval 6 day) group by date_format(card_share_log.add_time, '%Y-%m-%d')
这里主要是用到了去重以及嵌套查询
6.查询到两个结果集,分别是:A,B,这两个结果集中都有一个字段叫PD,然后要找到A结果集中,PD字段对应的值是在B的结果集中有的数据,以及没有的数据
需求:A的结果集:1,2 B的结果集:2,3
我要找到在A结果集中的数据,在B的结果集中也是有的数据,以及没有的数据
相同的数据:2,不同的数据:1
mysql:
SELECT DISTINCT a.pd FROM a INNER JOIN b ON a.pd = b.pd;
这个是找相同的
SELECT DISTINCT a.pd FROM a LEFT JOIN b ON a.pd = b.pd WHERE b.pd IS NULL;
这个是找不同的
7.IFNULL和COALESCE和IF
1. IFNULL 函数接受两个参数,第一个参数是需要检查是否为NULL的值,第二个参数是当第一个参数为NULL时返回的默认值。
2. COALESCE 函数接受一个或多个参数,依次检查参数是否为NULL,返回第一个非NULL值。
3. IFSUM(IF(handle_time IS NULL, 1, 0)) countSum card_gather;
在这个查询中,使用 IF 函数来判断 handle_time 是否为null。如果是,则返回1,否则返回0。然后,SUM函数将计算返回的值的总和,并将结果命名为countSum。
8.CASE 的语法
CASE WHEN cond1 THEN value1 WHEN cond2 THEN value2 WHEN condN THEN valueN ELSE value END;
9.现在有两个表,一个是用户表,一个是邮件表,然后每天凌晨都会执行一个任务,如果这个用户三十天都没有发送过邮件,那么系统就会自动删除该用户,然后在管理员查看用户列表的时候,要显示该用户还有多少天会被清理
对于这个需求,可以考虑使用定时任务来实现自动删除用户的功能,而不是在每次查询用户列表时都进行关联查询。具体思路如下:
- 创建一个定时任务,每天凌晨执行一次。
- 定时任务中,查询邮件表,获取所有发送过邮件的用户ID。
- 将这些用户ID存储到一个临时表中,作为已发送过邮件的用户列表。
- 查询用户表,获取所有用户ID。
- 遍历用户列表,对于每个用户ID,判断是否在已发送过邮件的用户列表中。
- 如果用户ID存在于已发送过邮件的用户列表中,说明用户发送过邮件,将该用户的清理计时器重置为30天。
- 如果用户ID不存在于已发送过邮件的用户列表中,说明用户三十天都没有发送过邮件,将该用户删除。
- 在管理员查看用户列表时,直接从用户表中查询用户信息,并计算出距离清理的天数。
- 在用户表中添加一个字段,用于存储清理倒计时的天数,每天凌晨定时任务执行时更新该字段的值。
通过这种方式,可以避免在查询用户列表时进行关联查询,提高查询效率。定时任务每天凌晨执行一次,将用户的发送邮件状态进行更新,同时删除符合条件的用户。管理员查看用户列表时,直接查询用户表并计算清理倒计时的天数,而不需要再进行复杂的关联查询操作。
10.需求图片(表设计):
这个需求需要用到一个数据统计流水表,记录每一个用户,当天所发生的操作数量,这个功能里面,有一个明星表,然后每一个用户都会对这个明星做关联,表示某一个明星是由该用户进行对接,然后这个明星表里面有个合作状态的,对应到表上就是达人合作效果,比如:星期一对这个达人的合作状态是:已合作,那么在星期一进行统计的时候,就要记录这个用户的合作数量加1,但是星期二在统计合作状态的时候,如果这个达人的合作状态没有变过,那就不能合作数量加1,所以要在明星表里面加一个合作状态的变化时间字段,用来判断这个合作状态的变化时间,比如星期一达成合作的,那变化时间就是星期一,在统计的时候,就可以进行加1,然后在星期二进行统计的时候,因为合作时间没有变化,还是星期一,所以就知道,这个明星的合作状态没变过,所以就不用对他进行统计