JinLinC个人在线文档库

Appearance

函数相关 ​

数学函数 ​

数学运算相关的常见函数有：

image-20250503131947746

SELECT CONV(8, 10, 2) FROM DUAL; 将数值8从10进制转化为2进制

FORMAT()保留小数位数，是通过四舍五入的方式进行保存的

字符串函数 ​

字符串函数可以方便我们快速的解决一些字符串拆分和合并相关的一些问题

常见的字符串函数：

函数描述
charset(字段名)返回字符串的字符集
left(字段名, i)从给定字段内容的从左边进行取i个字符串（包括第i个）
right(字段名, i)从给定字段内容的从右边进行取i个字符串（包括第i个）
mid(字段名, i, n)从给定字段内容的任何位置的第i个位置取n个字符串，如果没有输入n，表示从第i个位置进行取后面所有字符串
substring(字段名, i, length)对于表中的给定字段名，从该字段内容的第i个开始取（包括第i个），截取length个字符串，如果没有length，则一直取到最后
char_length(字段名)/LENGTH(字段名)获取给定字段中字符串的长度，如果是汉字，且字符集为utf8，一个汉字的长度为3
concat("前缀", 字段名)连接字符串，给选定的字符串的所有内容都连接一个字符串前缀
INSTR(string, substring)返回substring在string中出现的位置，没有则返回0
UCASE(字段名)将指定字段名列中的所有内容字母都转换成大写
LCASE(字段名)将指定字段名列中的所有内容字母都转换成小写
REPLACE(字段名, search_str, replace_str)在对应字段中的内容用replace_str替换search_str
STRCMP(str1, str2)逐字符比较两个字符串的大小 （字符串相同，返回0，前面的字符串小，返回-1，前面的字符串大，返回1）
LTRIM(str)去掉字符串的前端空格
RTRIM(str)去掉字符串的后端空格
TRIM(str)去掉字符串左右两边的空格

SELECT INSTR('mynameisjlc', 'jlc') FROM DUAL; 返回9，DUAL是亚元表，是一个系统表，可以作为测试使用

小案例：如果字段内容的长度大于8个，我们在8位之后使用...进行连接：

sql
select if (char_length(cname)>8,concat(left(cname,8),'...'),cname) as cname from class;

如果长度大于8位，8位后面使用...进行连接；如果长短小于8位，不做任何处理

时间处理的函数 ​

Mysql提供了非常丰富的时间处理函数，具体如下所示：

函数说明
HOUR时（范围从 0 到 23）
MINUTE分（范围从 0 到 59）
SECOND秒（范围从 0 到 59）
YEAR年（范围从 1000 到 9999）
MONTH月（范围从 1 到 12）
DAY日（范围从 1 开始）
TIME获取时间
WEEK一年中的第几周，从 1 开始计数
QUARTER一年中的季度，从 1 开始计数
CURRENT_DATE当前日期
CURRENT_TIME当前时间
CURRENT_TIMESTAMP获取当前的时间（年月日，时分秒）
NOW当前时间（年月日，时分秒）
DAYOFYEAR一年中的第几天（从 1 开始）
DAYOFMONTH月份中天数（从 1 开始）
DAYOFWEEK星期天（1）到星期六（7）
WEEKDAY星期一（0）到星期天（6）
TO_DAYS从元年到现在的天数（忽略时间部分）
FROM_DAYS根据距离元年的天数得到日期（忽略时间部分）
TIME_TO_SEC时间转为秒数（忽略日期部分）
SEC_TO_TIME根据秒数转为时间（忽略日期部分）
UNIX_TIMESTAMP根据日期返回秒数（包括日期与时间），返回的是1970-1-1到当前日期时间的毫秒数
FROM_UNIXTIME根据秒数返回日期与时间（包括日期与时间）
DATE(datetime)返回datetime的日期部分
DATE_ADD(date, INTERVAL d_value d_type)在date中加上日期或时间
DATE_SUB(date, INTERVAL d_value d_type)在date中减去日期或时间
DATEDIFF两个日期相差的天数（忽略时间部分，前面日期减后面日期）
TIMEDIFF计算两个时间的间隔（忽略日期部分）
TIMESTAMPDIFF根据指定单位计算两个日期时间的间隔（包括日期与时间）
LAST_DAY该月的最后一天

基本使用：使用频率比较高的时间处理函数

  • 获取时间日期数据的年月日：SELECT YEAR(birthday), MONTH(birthday), DAY(birthday) from stu;

    通过时间处理函数，可以把时间中的时间信息进行独立和局部的获取

  • 获取当前时刻的日期和时间：SELECT NOW();

    我们也可以进行当前日期和时间的分开获取：SELECT CURRENT_DATE();和 SELECT CURRENT_TIME();

  • 查看给定的日期时间是一年当中的第几天：SELECT DAYOFYEAR(NOW());

    同理，也可以查看给定的时间是当前月的第几天DAYOFMONTH；是当前周的第几天DAYOFWEEK（星期六是7，星期天是1）或者使用WEEKDAY（星期一是0，星期天是6）

小案例：如果当前时间大于更新时间publish_time时，所属条目的状态字段status就由0更新到1：

sql
update 数据表名称 set status = 1 WHERE status = 0 and publish_time < now();

  • 定义变量获取当前的时间：set @time = time(now()); 输出变量：select @time;

  • 将时间转化成秒数：select TIME_TO_SEC(@time); 00:00:00为第0秒

  • 也可以将秒数转化回时间：select SEC_TO_TIME(TIME_TO_SEC(@time));

  • 将当前时间转化为从元年到现在所经过的天数：select TO_DAYS(now());

  • 查看出生到现在所经过的天数：select DATEDIFF(now(), birthday) from stu;

  • 根据单位来获取时间差：select TIMESTAMPDIFF(day, birthday, now()) from stu;

    按天来进行比较，比较两段时间之间相差多少天；也可以按照月来进行比较month；可以按照周数week；可以按照分钟数据来进行比较minute；可以按照秒数来进行比较second

  • 查找出生年月在某个范围内：

    SELECT * from stu WHERE birthday BETWEEN '1995-01-01' AND '2005-01-01';

  • 查找年龄最小的（先降序排列，再取第一个）：

    SELECT * from stu order by birthday desc limit 1;

  • 查找在2000年出生的学生：SELECT * from stu WHERE YEAR(birthday) = '2000';

  • 查找20岁以上的学生：SELECT * from stu WHERE TIMESTAMPDIFF(YEAR, birthday, NOW()) > 20;

日期时间的计算 ​

日期时间的提前和推迟 ​

在有的时候，我们需要对日期和时间进行计算，如：在某个时间后面再加几个小时

  • 在当前的时间上再加8个小时：

    SELECT addtime(now(), '08:00:00');或SELECT timestamp(now(), '08:00:00');

  • 得到当前日期时间7天之后的日期时间：SELECT date_add(now(), INTERVAL 7 DAY);

    如果7前面加个负号，就表示获取7天之前的日期时间，也可以使用date_add的反函数date_sub

    对于日期时间的提前和推迟，我们不仅可以使用天，也可以使用年月日，时分秒等等

    • 提前或推迟10个小时30分钟：SELECT date_add(now(), INTERVAL "10:30" HOUR_MINUTE);
    • 提前或推迟3天8小时：SELECT date_add(now(), INTERVAL "3 8" DAY_HOUR);
月初月末的计算 ​

  • 获得当前日期时间在当前月月末的日期：SELECT last_day(now());

  • 获取当前日期时间上个月的最后一天的日期时间：SELECT last_day(date_sub(now(), INTERVAL 1 MONTH));

  • 获取当前日期时间下个月的最后一天的日期时间：SELECT date_add(last_day(now()), INTERVAL 1 DAY);

  • 获取当前日期时间在当前月月初的日期：SELECT date_sub(now(), INTERVAL DAYOFMONTH(now())-1 DAY);

    DAYOFMONTH(now())表示当前日期时间是这个月的第几天

小案例：获取本月发表文章条目：文章的数据表为article，包括字段发布时间：publish_time

sql
SELECT * FROM article
WHERE publish_time <= last_day(now())
ADD publish_time >= date_sub(now(), INTERVAL DAYOFMONTH(now())-1 day);
日期对周的控制 ​

在mysql中，系统提供了两个常用的函数来实现日期对周的控制：DAYOFWEEK（星期天（1）到星期六（7））和WEEKDAY（星期一（0）到星期天（6））

获取当前日期所在周星期二的日期：SELECT date_add(now(), INTERVAL 3-DAYOFWEEK(now()) DAY);

如果想要获得星期三的日期，就使用4减去DAYOFWEEK(now())即可

如果使用WEEKDAY的方法获得当前日期所在周星期二的日期：SELECT date_add(now(), INTERVAL 1-WEEKDAY(now()) DAY);

获取当前日期三周之前星期二的日期：

SELECT date_sub(date_add(now(), INTERVAL 1-WEEKDAY(now()) DAY), INTERVAL 21 Day);

日期时间常见案例 ​

月考勤 ​

查找本月迟到的学生：创建一张考情表attendance，包含字段表示打卡时间created_at

sql
SELECT * FROM attendance WHERE time(created_at) > '08:30:00'
AND date(created_at) > date(date_sub(now(), INTERVAL DAYOFMONTH(now())-1 DAY));

查找本月迟到超过两次的学生：

sql
SELECT stu_id, count(id) FROM attendance WHERE time(created_at) > '08:30:00'
AND date(created_at) > date(date_sub(now(), INTERVAL DAYOFMONTH(now())-1 DAY))
GROUP by stu_id
HAVING count(id) >=2;
周考勤 ​

查找本周迟到的学生：创建一张考情表attendance，包含字段表示打卡时间created_at

sql
SELECT * FROM attendance WHERE time(created_at) > '08:30:00'
AND date(created_at) > date(date_add(now(), INTERVAL 0-WEEKDAY(NOW()) DAY));

在计算日期时间时，会使用大量的函数，函数在数据库中进行操作时，会为每一条数据都执行函数，比较消耗性能，一般后续都在后端得到具体的日期，再在数据库中进行比对，这样比较高效

加密和系统函数 ​

系统内置提供的加密和系统函数有：

函数描述
USER()查询用户（查询当前是哪个用户正在使用数据库，可以查看登录到mysql的有哪些用户，以及登录的IP）
DATABASE()数据库名称
MDS(str)为字符串算出一个MDS32位（长度为32）的字符串（用户密码）加密计算，在数据库中存放的是加密后的密码
PASSWORD(str)从原文密码str计算并返回密码字符串，通常用于对mysql数据库的用户密码加密

流程控制函数 ​

函数描述
IF(expr1, expr2, expr3)如果表达式expr1为True，则返回expr2，否则返回expr3
IFNULL(expr1, expr2)如果expr1不为NULL，则返回expr1，否则返回expr2
SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END;类似于多分支，如果expr1为TRUE，则返回expr2，如果expr3为TRUE，返回expr4，否则返回expr5

SELECT ename, IF(comm IS NULL, 0.0, comm) FROM emp; 对于comm字段中，为空的数据，用0.0代替，原先有内容的数据，还是使用原先的内容

等价于：SELECT ename, IFNULL(comm, 0.0)

Released under the MIT License.

Copyright © 2023-present JinLinC