MySql使用及SQL语法学习

标签： Mysql 分类： 数据库 创建时间：2019-07-31 08:28:24 更新时间：2025-01-20 09:45:24

作为一个半吊子的全栈开发工程师，有些时候，不想和数据库打交道，但是任何一个系统都离不开数据库，想避开是不可能的了，硬着头皮上呗。

1.MySQL中字符串转成日期格式

在进行查询的时候，有些日期格式是使用的字符串存储的，比如:”2019-07-31 12:23:23“,但是比较的时候，需要用到日期格式，比如比较两个日期的大小:BETWEEN,这个时候就需要使用STR_TO_DATE函数。奇怪的问题是这样的。
第一张图,显示了字符串型的日期，没毛病：

第二张图，查询的时候，却出现了null,关键不是所有的都是null,而是有的是null有的不是：

经过仔细的比对，可以发现，当小时数小于12的时候，是可以转换成功的，但是没有一个18这种小时数被转换成功的，所以，猜测，应该是使用：STR_TO_DATE(i.statustime,’%Y-%m-%d %h:%i:%s’)，只能转换12小时格式的时间。将 %h 改为 %H ，果然就正常了。（基础知识不扎实，硬伤啊）

参考文章：
1.mysql str_to_date 字符串转换为日期

2.统计一天中记录数

比如说，我想知道2019年7月31号，这一天，总共有多少条记录，可以使用

1
SELECT COUNT(*) AS interruptCount FROM  interrupt_server i WHERE (STR_TO_DATE(i.statustime,'%Y-%m-%d %H:%i:%s') BETWEEN '2019-07-31 00:00:00' AND '2019-07-31 23:59:59') and serviceid=3207

如果更进一步，分组查询某个时间段内容，某个分组中的某个条件大于一定值的数据怎么办呢？这里给个例子

1
SELECT *,COUNT(*) AS interruptCount FROM  interrupt_server i WHERE servicestatus>0 and (STR_TO_DATE(i.statustime,'%Y-%m-%d %H:%i:%s') BETWEEN ?1 AND ?2)  and city LIKE ?3 and county like ?4  GROUP BY serviceid HAVING interruptCount >= ?5

里面的?1,?2,?3…这些是为了java spring boot传递参数预留的。上面的大致的意思是，先根据wehere条件进行了筛选，然后根据serviceid进行了分组，最后在各个分组中挑选出符合条件的一系列数据行，组成最后的结果集展示出来。

3.查询离某个时间点最近的记录

查询离’2018-03-20 23:57:33’这个时间点最近的一条记录:

1
2
SELECT *, ABS(STR_TO_DATE('2018-03-20 23:57:33','%Y-%m-%d %H:%i:%s') - STR_TO_DATE(statustime,'%Y-%m-%d %H:%i:%s') )  AS diffTime
FROM interrupt WHERE serviceid = 3207 ORDER BY diffTime ASC limit 0,1

查询离某个时间点最近的记录，并且时间要早于这个时间点:

1
2
SELECT *, (STR_TO_DATE('2019-03-25 17:19:02','%Y-%m-%d %H:%i:%s') - STR_TO_DATE(statusutime,'%Y-%m-%d %H:%i:%s')) AS diffTime
FROM monitoring WHERE serviceid = 3207 and statusutime < '2019-03-25 17:19:02' ORDER BY diffTime ASC LIMIT 0,1

4.时间分段分组求和

需求是这样的：给定一个开始时间，一个结束时间，然后一个数据周期，比如说一个小时，那么就要统计从开始时间到结束时间，每一个小时之内的某一个字段的累计总量或者是平均值，如果周期是两个小时，那么就是统计从开始时间到结束时间每两个小时之内的某个字段的累计总量或者是平均值。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
declare @begin datetime,@end datetime,@interval int
select @begin = '2010-02-04 14:00:00',
@end='2010-02-04 14:50:00',
@interval=10
 
select 
r.[GID],r.[VaID],r.[Mac],
r.StartTime,r.EndTime,
isnull(sum(t.[Acount]),0) as SumAcount
from [tb] t
right join
(
select 
h.[GID],h.[VaID],h.[Mac],
dateadd(minute,number*@interval,@begin) as StartTime,
dateadd(minute,(number+1)*@interval,@begin) as EndTime
from master..spt_values,(select distinct [GID],[VaID],[Mac] from [tb]) h
where type = 'P' and
dateadd(minute,(number+1)*@interval,@begin) <= @end
) r 
on t.[GID]=r.[GID] and t.[VaID]=r.[VaID] and t.[Mac]=r.[Mac] and
t.[ActiveTime] between r.StartTime and r.EndTime
group by r.[GID],r.[VaID],r.[Mac],
r.StartTime,r.EndTime
参考文章：
1.用sql语句按指定时间段分组统计
2.SQL——按照季度，固定时间段，分组统计数据
3.用sql语句按指定时间段分组统计
4.sql查询技巧，按时间分段进行分组，每半小时一组统计组内记录数量
5.SQL按时间分段分组统计数据
4.sql 语言 groupBy 分组统计时间段解决方案
5.关于MYSQL group by 分组按时间取最大值的实现方法
6.SQL GROUP BY 语句
7.如何按ClickHouse中的时间段进行分组，并使用nulls / 0s填充缺失的数据
8.这大概是最全的sql优化方案了

5.join联合查询

参考文章：
1.sql联合查询 JOIN和UNION区别

6.自动增长

1
2
3
CREATE TABLE tb_student(
    id INT(4) PRIMARY KEY AUTO_INCREMENT
);
参考文章:
1.MySQL AUTO_INCREMENT：主键自增长

7.重置自动增长列

(1) 首先，取消 kalacloud_table_demo 表中 ID 列的自增和主键的定义：

1
ALTER TABLE kalacloud_table_demo MODIFY COLUMN id int NOT NULL FIRST ,DROP PRIMARY KEY;

(2) 在表里新增 id_renew 列，设置为自增（AUTO_INCREMENT）主键（PRIMARY KEY），我们将使用这个列来替换 ID 列。

1
ALTER TABLE kalacloud_table_demo ADD COLUMN id_renew int NULL AUTO_INCREMENT FIRST ,ADD PRIMARY KEY (id_renew);

(3) 新增的id_renew列已经是连续数字,使用 SELECT 查看当前表情况，可以看到新增的 id_renew 这一列的顺序已经是连续的了。

(4) 接着，我们可以删除掉原来乱序的 ID 列

1
ALTER TABLE kalacloud_table_demo DROP COLUMN id;

(5) 最后，我们将 id_renew ，重命名，更为 id

1
ALTER TABLE kalacloud_table_demo CHANGE COLUMN id_renew id int(7) NOT NULL AUTO_INCREMENT FIRST;
参考文章:
1. MySQL / MariaDB 重置自增 ID (AUTO_INCREMENT)教程 - 完美保留表数据的终极解决方案
**本文已结束，感谢您的观看**
鼓励一下

往期推荐

文章目录
  1. 1. 1.MySQL中字符串转成日期格式
  2. 2. 2.统计一天中记录数
  3. 3. 3.查询离某个时间点最近的记录
  4. 4. 4.时间分段分组求和
  5. 5. 5.join联合查询
  6. 6. 6.自动增长
  7. 7. 7.重置自动增长列
小额赞助
本人提供免费与付费咨询服务，感谢您的支持！赞助请发邮件通知，方便公布您的善意！
**光 3.01 元
Sun 3.00 元
bibichuan 3.00 元
捐赠者名单 合计：9.01 元
微信公众号
广告位
诚心邀请广大金主爸爸洽谈合作
每日一省
isNaN 和 Number.isNaN 函数的区别？

1.函数 isNaN 接收参数后，会尝试将这个参数转换为数值，任何不能被转换为数值的的值都会返回 true，因此非数字值传入也会返回 true ，会影响 NaN 的判断。

2.函数 Number.isNaN 会首先判断传入参数是否为数字，如果是数字再继续判断是否为 NaN ，不会进行数据类型的转换，这种方法对于 NaN 的判断更为准确。

2021-04-07 链接地址
每日二省
为什么0.1+0.2 ! == 0.3，如何让其相等?

一个直接的解决方法就是设置一个误差范围，通常称为“机器精度”。对JavaScript来说，这个值通常为2-52，在ES6中，提供了Number.EPSILON属性，而它的值就是2-52，只要判断0.1+0.2-0.3是否小于Number.EPSILON，如果小于，就可以判断为0.1+0.2 ===0.3。

2021-04-07 链接地址
每日三省
== 操作符的强制类型转换规则？

1.首先会判断两者类型是否**相同，**相同的话就比较两者的大小。

2.类型不相同的话，就会进行类型转换。

3.会先判断是否在对比 null 和 undefined，是的话就会返回 true。

4.判断两者类型是否为 string 和 number，是的话就会将字符串转换为 number。

5.判断其中一方是否为 boolean，是的话就会把 boolean 转为 number 再进行判断。

6.判断其中一方是否为 object 且另一方为 string、number 或者 symbol，是的话就会把 object 转为原始类型再进行判断。

2021-04-07 链接地址
每日英语
Happiness is time precipitation, smile is the lonely sad.
幸福是年华的沉淀，微笑是寂寞的悲伤。
2016 ——— 美文