一、背景
这个是上年总结的,我现在把他拿出来供大家学习,因为当时没有别人告诉你该怎么去做,只有自己去一点一点积累,现在我拿出来供大家学习用,同时如果那里做的不足的请大家指出。
二、知识点总结
1.多重left join的用法
(a)关键字:on
数据库在通过连接两张或者多张表来返回记录时,都会生成一张中间的临时表,然后再将这张临时表返回给用户。
(b)多表联查的时候,多张表相互关联的时候,不一定都需要主外键的关系,只要是字段匹配就行。
2.关于什么时候用union,什么时候用union all 的情况?
2.1前者会把两个记录集中相同的记录合并,而后者不会,性能上前者优。如此一说,你知道什么时候用Union什么时候用Union All了吧。
2.2当确认多个记录集不会存在相同记录,或者有可能有相同记录但明确要合并的,用Union即使有相同记录也不合并的,用Union All
3.mysql的内置函数的介绍
3.1.concat()
例1:concat('提前',datediff(t.due_date,t.repayment_time),'天') advance_time,#提前天数
例2:concat(id,'->',name) 其中id是一个字段,name也是一个字段,可以这样用的。
3.2 now() 是返回当前的日期和时间
例如: date(date_add(now(),interval -1 day))
3.3 left()函数返回串左边的字符
例如:select left(name,3) from roles;
注解:对于name字段来说,可以把name字段中的前三个字符截取出来。
right()函数返回的串的右边的字符,原理同上面的例子一样。
3.4 substring()函数
用法:substring(str,position) or substring(str,position,length),若position为负数则从后面开始数。
select substring(name,2,3) from roles order by id 对例子中的数据进行分析,name是个字段,可以对它下面的字符进行截取,2 代表着从第几位字符开始,3是截取多少个字符。
3.5 date_add()函数
例子:date_add(now(),interval -1 day)
对这个例子的详细说明,interval表示时间间隔,-1是数量,day是单位(还可以是month,year)
这句话的意思可以理解为:找出一个表中所有24小时之内的数据。
date_sub()函数
用法:mysql为日期减去一个时间间隔:date_sub()
例子:select date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second);
结果:1997-12-30 22:58:59
3.6 datediff()函数
例子:concat('提前',datediff(t.due_date,t.repayment_time),'天') advance_time,#提前天数
注解:计算两个日期之差
3.8 timestampdiff()函数
语法:timestampdiff(interval,datetime_expr1,datetime_expr2)
例子1: timestampdiff(year,str_to_date(substring(a.new,7,8),'%Y%m%d'),now()) as age #年龄
4.注意点:
4.1 关于left join on 的时候,where条件后面只能跟主表的条件吗?
答案:都可以的,如果只跟主表的条件的话,只会限制住主表的条件,主表全部显示,再与从表匹配,从表如果匹配的上就显示,匹配不上就为NUll。如果跟主表的条件又跟从表的条件,从表匹配不上就显示不出来,就算主表有这个东西也显示不出来。
4.2 当多个表 left join on的时候,on 和where 跟的条件的区别和差异?
答案:都会匹配出一个超大表,其中还都有一个表的条件,where后面可以跟多个表的条件,包括从表的条件,限制一行数据的多个条件,如果都能匹配的上的话,就只显示匹配上的结果行。如果把从表的条件不写在where后面,而是写在on的后面的话,主表会全部显示,匹配不上的从表会为NUll。
5.对于多个表left join的时候,如果主表的条件写在on的后面,那么主表的结果会全部显示,那么从表的结果匹配的上就显示,匹配不上就是Null.如果原来的主表变为从表的话在写条件在on的后面就会过滤掉一部分数据
6.通过日期计算时间
6.1 select extract(day from now())-1;
结果:28 就相当于是假如时间周期现在是2018-04-29,所以这个SQL查询的结果就是28天。
6.2 SELECT date_sub(date_format(now(),'%y-%m-%d'),interval extract(day from now()) day);
结果:2018-03-31 假如现在的时间周期是2018-04-29,所以这个SQL查询的结果就是上个月的最后一天
6.3 select date_sub(date_sub(date_format(now(),'%y-%m-%d'),interval extract(day from now())-1 day),interval 1 month);
结果:2018-03-01 假如现在的时间周期是2018-04-29,所以这个SQL查询的结果就是上个月的第一天
6.4 SELECT date_sub(date_sub(date_format(now(),'%y-%m-%d'),interval extract(day from now()) day),interval 0 month);
结果:2018-03-31 假如现在的时间周期是2018-04-29,所以这个SQL查询的结果就是上个月的最后一天
6.5 select extract(year from now());
结果:2018 假如现在的时间周期是2018-04-29,所以这个SQL查询的结果就是本年的结果
6.6 SELECT DATE_SUB(date_sub(date_format(now(),'%y-%m-%d'),interval 13 day),interval 0 month);
结果:2018-04-16 假如现在的时间周期是2018-04-29,所以这个SQL查询的结果就是本月的13天之前的日期
6.7 SELECT CURDATE();
结果:2018-04-29 假如现在的时间周期是2018-04-29,所以这个SQL查询的结果就是这年的这个月的这一天
---上个月的第一天:
select date_sub(date_sub(date_format(now(), '%y-%m-%d'),interval extract(day from now()) - 1 day),interval 1 month);
---上个月的最后一天:
select date_sub(date_sub(date_format(now(), '%y-%m-%d'), interval extract(day from now()) day),interval 0 month);
---这个月的第一天:
select date_sub(date_sub(date_format(now(), '%y-%m-%d'),interval extract(day from now()) - 1 day),interval 0 month);
---这个月的最后一天:
select date_sub(date_sub(date_format(now(), '%y-%m-%d'),interval extract(day from now()) day),interval - 1 month);
三、结束
希望能帮到你们初学者,共勉!!!