标签:返回 函数 05 -- 单行 str 字符串 SELECT
一、函数的理解
1.1 什么是函数
-
函数在计算机语言的使用中贯穿始终,函数的作用是什么呢?它可以把我们经常使用的代码封装起来,需要的时候直接调用即可。这样既 提高了代码效率 ,又 提高了可维护性 。在 SQL 中我们也可以使用函数 对检索出来的数据进行函数操作。使用这些函数,可以极大地 提高用户对数据库的管理效率 。
-
从函数定义的角度出发,我们可以将函数分成 内置函数 和 自定义函数 。在 SQL 语言中,同样也包括了 内置函数和自定义函数。内置函数是系统内置的通用函数,而自定义函数是我们根据自己的需要编写的。
1.2 不同DBMS函数的差异
- 我们在使用 SQL 语言的时候,不是直接和这门语言打交道,而是通过它使用不同的数据库软件,即DBMS。DBMS 之间的差异性很大,远大于同一个语言不同版本之间的差异。实际上,只有很少的函数是 被 DBMS 同时支持的。比如,大多数 DBMS 使用(||)或者(+)来做拼接符,而在 MySQL 中的字符串拼 接函数为concat()。大部分 DBMS 会有自己特定的函数,这就意味着采用 SQL 函数的代码可移植性是很差的,因此在使用函数的时候需要特别注意
1.3 MySQL的内置函数及分类
-
MySQL提供了丰富的内置函数,这些函数使得数据的维护与管理更加方便,能够更好地提供数据的分析 与统计功能,在一定程度上提高了开发人员进行数据分析与统计的效率。
-
MySQL提供的内置函数从 实现的功能角度 可以分为数值函数、字符串函数、日期和时间函数、流程控制 函数、加密与解密函数、获取MySQL信息函数、聚合函数等。这里,我将这些丰富的内置函数再分为两类: 单行函数 、 聚合函数(或分组函数) 。
-
两种SQL函数
![image-20220722200055962](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205002291-1329341785.png)
-
单行函数
- 操作数据对象;
- 接受参数返回一个结果;
- 只对一行进行变换;
- 每行返回一个结果;
- 可以嵌套;
- 参数可以是一列或一个值
二、数值函数
2.1 基本函数
函数 |
用法 |
ABS(x) |
返回x的绝对值 |
SIGN(X) |
返回X的符号。正数返回1,负数返回-1,0返回0 |
PI() |
返回圆周率的值 |
CEIL(x),CEILING(x) |
返回大于或等于某个值的最小整数 |
FLOOR(x) |
返回小于或等于某个值的最大整数 |
LEAST(e1,e2,e3…) |
返回列表中的最小值 |
GREATEST(e1,e2,e3…) |
返回列表中的最大值 |
MOD(x,y) |
返回X除以Y后的余数 |
RAND() |
返回0~1的随机值 |
RAND(x) |
返回0~1的随机值,其中x的值用作种子值,相同的X值会产生相同的随机数 |
ROUND(x) |
返回一个对x的值进行四舍五入后,最接近于X的整数 |
ROUND(x,y) |
返回一个对x的值进行四舍五入后最接近X的值,并保留到小数点后面Y位 |
TRUNCATE(x,y) |
返回数字x截断为y位小数的结果 |
SQRT(x) |
返回x的平方根。当X的值为负数时,返回NULL |
-- CEIL(x),CEILING(x):返回大于或等于某个值的最小整数
-- FLOOR(x):返回小于或等于某个值的最大整数
-- 基本操作
SELECT
ABS(-123),ABS(32),SIGN(-23),SIGN(43),PI(),CEIL(32.32),CEILING(-43.23),FLOOR(32.32),
FLOOR(-43.23),MOD(12,5)
FROM DUAL;
![image-20220820205801139](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205003102-791194738.png)
-- RAND():返回0~1的随机值
-- RAND(x):返回0~1的随机值,其中x的值用作种子值,相同的X值会产生相同的随机数
-- 取随机数
SELECT RAND(),RAND(),RAND(10),RAND(10),RAND(-1),RAND(-1)
![image-20220820211233495](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205003575-496821995.png)
-- ROUND(x):返回一个对x的值进行四舍五入后,最接近于X的整数
-- ROUND(x,y):返回一个对x的值进行四舍五入后最接近X的值,并保留到小数点后面Y位
-- 注意ROUND(x,y)函数既做四舍五入操作,又做截断操作
SELECT
ROUND(12.33),ROUND(12.343,2),ROUND(12.324,-1),ROUND(125.234,-2),ROUND(153.234,-2)
FROM DUAL;
![image-20220820213146805](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205004046-1766941334.png)
-- TRUNCATE(x,y):返回数字x截断为y位小数的结果
-- 注意TRUNCATE(x,y)函数不做四舍五入操作,只做截断操作
SELECT TRUNCATE(123.45,0),TRUNCATE(123.45,1),TRUNCATE(123.45,-2),TRUNCATE(163.45,-2)
FROM DUAL;
![image-20220820213300164](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205004926-2089091389.png)
-- 单行函数可以嵌套
SELECT TRUNCATE(ROUND(123.548,2),0)
FROM DUAL;
![image-20220820213726290](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205005206-1897671836.png)
2.2 角度与弧度互换函数
函数 |
用法 |
RADIANS(x) |
将角度转化为弧度,其中,参数x为角度值 |
DEGREES(x) |
将弧度转化为角度,其中,参数x为弧度值 |
-
举例
-- 角度与弧度的互换
SELECT RADIANS(30),RADIANS(60),RADIANS(90),
DEGREES(2*PI()),DEGREES(RADIANS(60))
FROM DUAL;
![image-20220820222204933](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205005529-1073272811.png)
2.3 三角函数
函数 |
用法 |
SIN(x) |
返回x的正弦值,其中,参数x为弧度值 |
ASIN(x) |
返回x的反正弦值,即获取正弦为x的值不在-1到1之间,则返回NULL |
COS(x) |
返回x的余弦值,其中,参数x为弧度值 |
ACOS(x) |
返回x的反余弦值,即获取余弦为x的值。如果x的值不在-1到1之间,则返回NULL |
TAN(x) |
返回x的正切值,其中,参数x为弧度值 |
ATAN(x) |
返回x的反正切值,即返回正切值为x的值 |
ATAN2(x) |
返回两个参数的反正切值 |
COT(x) |
返回x的余切值,其中,x为弧度值 |
-
举例
-- SIN(x):返回x的正弦值,其中,参数x为弧度值
-- TAN(x):返回x的正切值,其中,参数x为弧度值
SELECT SIN(RADIANS(30)),DEGREES(ASIN(1)),TAN(RADIANS(45)),DEGREES(ATAN(1))
FROM DUAL;
![image-20220820223834448](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205005840-1020548225.png)
2.4 指数与对数
函数 |
用法 |
POW(x,y),POWER(X,Y) |
返回x的y次方 |
EXP(X) |
返回e的X次方,其中e是一个常数,2.718281828459045 |
LN(X),LOG(X) |
返回以e为底的X的对数,当X <= 0 时,返回的结果为NULL |
LOG10(X) |
返回以10为底的X的对数,当X <= 0 时,返回的结果为NULL |
LOG2(X) |
返回以2为底的X的对数,当X <= 0 时,返回NULL |
-
举例
-- 指数与对数
SELECT POW(2,5),POWER(2,4),EXP(2),LN(10),LOG10(10),LOG2(4)
FROM DUAL;
![image-20220820224547883](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205006157-1890894624.png)
2.5 进制间的转换
函数 |
用法 |
BIN(x) |
返回x的二进制编码 |
HEX(x) |
返回x的十六进制编码 |
OCT(x) |
返回x的八进制编码 |
CONV(x,f1,f2) |
返回f1进制数变成f2进制数 |
-
举例
-- 进制间转换
SELECT BIN(10),HEX(10),OCT(10),CONV(10,2,8)
FROM DUAL;
![image-20220820224952908](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205006418-1847936810.png)
三、字符串函数
函数 |
用法 |
ASCII(S) |
返回字符串S中的第一个字符的ASCII码值 |
CHAR_LENGTH(s) |
返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同 |
LENGTH(s) |
返回字符串s的字节数,和字符集有关 |
CONCAT(s1,s2,......,sn) |
连接s1,s2,......,sn为一个字符串 |
CONCAT_WS(x, s1,s2,......,sn) |
同CONCAT(s1,s2,...)函数,但是每个字符串之间要加上x |
INSERT(str, idx, len, replacestr) |
将字符串str从第idx位置开始,len个字符长的子串替换为字符串replacestr |
REPLACE(str, a, b) |
用字符串b替换字符串str中所有出现的字符串a |
UPPER(s) 或 UCASE(s) |
将字符串s的所有字母转成大写字母 |
LOWER(s) 或LCASE(s) |
将字符串s的所有字母转成小写字母 |
LEFT(str,n) |
返回字符串str最左边的n个字符 |
RIGHT(str,n) |
返回字符串str最右边的n个字符 |
LPAD(str, len, pad) |
用字符串pad对str最左边进行填充,直到str的长度为len个字符 |
RPAD(str ,len, pad) |
用字符串pad对str最右边进行填充,直到str的长度为len个字符 |
LTRIM(s) |
去掉字符串s左侧的空格 |
RTRIM(s) |
去掉字符串s右侧的空格 |
TRIM(s) |
去掉字符串s开始与结尾的空格 |
TRIM(s1 FROM s) |
去掉字符串s开始与结尾的s1 |
TRIM(LEADING s1 FROM s) |
去掉字符串s开始处的s1 |
TRIM(TRAILING s1 FROM s) |
去掉字符串s结尾处的s1 |
REPEAT(str, n) |
返回str重复n次的结果 |
SPACE(n) |
返回n个空格 |
STRCMP(s1,s2) |
比较字符串s1,s2的ASCII码值的大小 |
SUBSTR(s,index,len) |
返回从字符串s的index位置其len个字符,作用与SUBSTRING(s,n,len)、 MID(s,n,len)相同 |
LOCATE(substr,str) |
返回字符串substr在字符串str中首次出现的位置,作用于POSITION(substr IN str)、INSTR(str,substr)相同。未找到,返回0 |
ELT(m,s1,s2,…,sn) |
返回指定位置的字符串,如果m=1,则返回s1,如果m=2,则返回s2,如 果m=n,则返回sn |
FIELD(s,s1,s2,…,sn) |
返回字符串s在字符串列表中第一次出现的位置 |
FIND_IN_SET(s1,s2) |
返回字符串s1在字符串s2中出现的位置。其中,字符串s2是一个以逗号分 隔的字符串 |
REVERSE(s) |
返回s反转后的字符串 |
NULLIF(value1,value2) |
比较两个字符串,如果value1与value2相等,则返回NULL,否则返回 value1 |
-- ASCII(S):返回字符串S中的第一个字符的ASCII码值
-- CHAR_LENGTH(s):返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同
-- LENGTH(s):返回字符串s的字节数,和字符集有关
-- 注意:在UTF-8编码中,一个中文字符等于三个字节,一个中文标点符号占三个字节;一个英文字符等于一个字节,一个英文标点占一个字节;一个数字符号等于一个字节。
SELECT ASCII('AbcdGEg'),LENGTH('hello world'),LENGTH('朋友'),CHAR_LENGTH('hello world'),CHAR_LENGTH('朋友')
FROM DUAL;
![image-20220821213205415](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205006734-197496329.png)
-- CONCAT(s1,s2,......,sn):连接s1,s2,......,sn为一个字符串;
-- CONCAT_WS(x, s1,s2,......,sn):同CONCAT(s1,s2,...)函数,但是每个字符串之间要加上x;
SELECT CONCAT('We',' Are',' Family'),CONCAT_WS('-','We',' Are',' Family')
FROM DUAL;
![image-20220821215538328](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205007070-215823831.png)
-- INSERT(str, idx, len, replacestr):将字符串str从第idx位置开始,len个字符长的子串替换为字符串replacestr
-- 字符串的索引是从1开始的
SELECT INSERT('Hello World!',1,5,'Good'),INSERT('Hello World!',0,5,'Good')
FROM DUAL;
![image-20220821220104924](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205007930-959923779.png)
-- UPPER(s):将字符串s的所有字母转成大写字母;
-- LOWER(s):将字符串s的所有字母转成小写字母;
SELECT UPPER('Hello World!'),LOWER('Hello World!')
FROM DUAL;
![image-20220821220453258](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205008237-726765852.png)
-- LEFT(str,n):返回字符串str最左边的n个字符;
-- RIGHT(str,n):返回字符串str最右边的n个字符;
SELECT LEFT('Hello',2),RIGHT('World',3),LEFT('Hello',10),RIGHT('World',7)
FROM DUAL;
![image-20220821221047982](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205008519-1271229802.png)
-- LPAD(str, len, pad):用字符串pad对str最左边进行填充,直到str的长度为len个字符;同时也能实现右对齐效果。
-- RPAD(str ,len, pad):用字符串pad对str最右边进行填充,直到str的长度为len个字符;同时也能实现左对齐效果。
SELECT sno,sbir,RPAD(sno,5,' '),LPAD(sbir,25,' ') FROM j_student ;
![image-20220821222602141](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205009579-194803527.png)
-- LTRIM(s):去掉字符串s左侧的空格;
-- RTRIM(s):去掉字符串s右侧的空格;
-- TRIM(s):去掉字符串s开始与结尾的空格;
-- TRIM(s1 FROM s):去掉字符串s开始与结尾的s1
SELECT
CONCAT( '--->', TRIM( ' H e ll o ' ), '<---' ),
CONCAT( '--->', LTRIM( ' H e ll o ' ), '<---' ),
CONCAT('--->',RTRIM( ' H e ll o ' ),'<---') ,
TRIM('He' FROM 'Hello')
FROM
DUAL;
![image-20220821223425925](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205009866-789922009.png)
-- REPEAT(str, n):返回str重复n次的结果;
-- SPACE(n):返回n个空格;
-- STRCMP(s1,s2):比较字符串s1,s2的ASCII码值的大小
SELECT REPEAT('TEST-',4),LENGTH(SPACE(5)),STRCMP('ABC','ABD')
FROM DUAL;
![image-20220821224904905](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205010222-1364328826.png)
-- SUBSTR(s,index,len):返回从字符串s的index位置其len个字符,作用与SUBSTRING(s,n,len)、 MID(s,n,len)相同;
-- LOCATE(substr,1str):返回字符串substr在字符串str中首次出现的位置,作用于POSITION(substr IN str)、INSTR(str,substr)相同。未找到,返回0;
SELECT SUBSTR('hello world',3,5),LOCATE('ll','hello')
FROM DUAL;
![image-20220821224947564](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205010498-2134303441.png)
-- ELT(m,s1,s2,…,sn):返回指定位置的字符串,如果m=1,则返回s1,如果m=2,则返回s2,如 果m=n,则返回sn;
-- FIELD(s,s1,s2,…,sn):返回字符串s在字符串列表中第一次出现的位置
-- REVERSE(s):返回s反转后的字符串;
SELECT ELT(2,'A','B','C','D'),FIELD('E','A','E','C','E'),REVERSE('HAPPY')
FROM DUAL;
![image-20220821225032591](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205010784-964924015.png)
四、日期和时间函数
4.1 获取日期、时间
函数 |
用法 |
CURDATE() ,CURRENT_DATE() |
返回当前日期,只包含年、 月、日 |
CURTIME() , CURRENT_TIME() |
返回当前时间,只包含时、 分、秒 |
NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP() |
返回当前系统日期和时间 |
UTC_DATE() |
返回UTC(世界标准时间) 日期 |
UTC_TIME() |
返回UTC(世界标准时间) 时间 |
-- 获取日期、时间
SELECT CURDATE(),CURRENT_DATE(),CURTIME(),NOW(),SYSDATE(),UTC_DATE(),UTC_TIME() FROM DUAL;
![image-20221003105608431](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205011773-533652749.png)
4.2日期与时间戳的转换
函数 |
用法 |
UNIX_TIMESTAMP() |
以UNIX时间戳的形式返回当前时间。SELECT UNIX_TIMESTAMP() - >1634348884 |
UNIX_TIMESTAMP(date) |
将时间date以UNIX时间戳的形式返回。 |
FROM_UNIXTIME(timestamp) |
将UNIX时间戳的时间转换为普通格式的时间 |
SELECT
UNIX_TIMESTAMP(),
UNIX_TIMESTAMP( '2022-10-03 11:02:22' ),
FROM_UNIXTIME( 1664766142 )
FROM
DUAL;
![image-20221003110443229](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205012666-599394750.png)
4.3 获取月份、星期、星期数、天数等函数
函数 |
用法 |
YEAR(date) / MONTH(date) / DAY(date) |
返回具体的日期值 |
HOUR(time) / MINUTE(time) / SECOND(time) |
返回具体的时间值 |
MONTHNAME(date) |
返回月份:January,... |
DAYNAME(date) |
返回星期几:MONDAY,TUESDAY.....SUNDAY |
WEEKDAY(date) |
返回周几,注意,周1是0,周2是1,。。。周日是6 |
QUARTER(date) |
返回日期对应的季度,范围为1~4 |
WEEK(date) , WEEKOFYEAR(date) |
返回一年中的第几周 |
DAYOFYEAR(date) |
返回日期是一年中的第几天 |
DAYOFMONTH(date) |
返回日期位于所在月份的第几天 |
DAYOFWEEK(date) |
返回周几,注意:周日是1,周一是2,。。。周六是 7 |
-- 获取月份、星期、星期数、天数等函数
SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),
HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE())
FROM DUAL;
SELECT MONTHNAME('2022-10-03'),DAYNAME('2022-10-03'),WEEKDAY('2022-10-03'),
QUARTER(CURDATE()),WEEK(CURDATE()),DAYOFYEAR(NOW()),
DAYOFMONTH(NOW()),DAYOFWEEK(NOW())
FROM DUAL;
![image-20221003111235112](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205013715-951123701.png)
![image-20221003111354321](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205014571-97821050.png)
4.4 日期的操作函数
函数 |
用法 |
用法 EXTRACT(type FROM date) |
返回指定日期中特定的部分,type指定返回的值 |
- EXTRACT(type FROM date)函数中type的取值与含义:
![image-20221003111720755](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205019319-713894728.png)
-- 日期的操作函数
SELECT EXTRACT(MINUTE FROM NOW()),EXTRACT( WEEK FROM NOW()),
EXTRACT( QUARTER FROM NOW()),EXTRACT( MINUTE_SECOND FROM NOW()),
EXTRACT(QUARTER FROM '2022-10-03')
FROM DUAL;
![image-20221003112147996](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205021023-942966478.png)
4.5 时间和秒钟转换的函数
函数 |
用法 |
TIME_TO_SEC(time) |
将 time 转化为秒并返回结果值。转化的公式为: 小时*3600+分钟 *60+秒 |
SEC_TO_TIME(seconds) |
将 seconds 描述转化为包含小时、分钟和秒的时间 |
-- 时间和秒钟转换的函数
SELECT TIME_TO_SEC(CURTIME()),SEC_TO_TIME(41164)
FROM DUAL;
![image-20221003112731893](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205021751-20255979.png)
4.6 计算日期和时间的函数
函数 |
用法 |
DATE_ADD(datetime, INTERVAL expr type), ADDDATE(date,INTERVAL expr type) |
返回与给定日期时间相差INTERVAL时 间段的日期时间 |
DATE_SUB(date,INTERVAL expr type), SUBDATE(date,INTERVAL expr type) |
返回与date相差INTERVAL时间间隔的 日期 |
上述函数中type的取值:
![image-20221003112916763](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205023668-1335746417.png)
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) AS col1,DATE_ADD('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col2,
ADDDATE('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col3,
DATE_ADD('2021-10-21 23:32:12',INTERVAL '1_1' MINUTE_SECOND) AS col4,
DATE_ADD(NOW(), INTERVAL -1 YEAR) AS col5, #可以是负数
DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH) AS col6 #需要单引号
FROM DUAL;
SELECT DATE_SUB('2021-01-21',INTERVAL 31 DAY) AS col1,
SUBDATE('2021-01-21',INTERVAL 31 DAY) AS col2,
DATE_SUB('2021-01-21 02:01:01',INTERVAL '1 1' DAY_HOUR) AS col3
FROM DUAL;
![image-20221003115850012](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205024627-336033980.png)
![image-20221003115912351](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205025495-2095596024.png)
函数 |
用法 |
ADDTIME(time1,time2) |
返回time1加上time2的时间。当time2为一个数字时,代表的是 秒 ,可以为负数 |
SUBTIME(time1,time2) |
返回time1减去time2后的时间。当time2为一个数字时,代表的 是 秒 ,可以为负数 |
DATEDIFF(date1,date2) |
返回date1 - date2的日期间隔天数 |
TIMEDIFF(time1, time2) |
返回time1 - time2的时间间隔 |
FROM_DAYS(N) |
返回从0000年1月1日起,N天以后的日期 |
TO_DAYS(date) |
返回日期date距离0000年1月1日的天数 |
LAST_DAY(date) |
返回date所在月份的最后一天的日期 |
MAKEDATE(year,n) |
针对给定年份与所在年份中的天数返回一个日期 |
MAKETIME(hour,minute,second) |
将给定的小时、分钟和秒组合成时间并返回 |
PERIOD_ADD(time,n) |
返回time加上n后的时间 |
SELECT
ADDTIME( NOW(), 20 ) a1,
SUBTIME( NOW(), 30 ) a2,
SUBTIME( NOW(), '1:1:3' ) a3,
DATEDIFF( NOW(), '2021-10-01' ) a4,
TIMEDIFF( NOW(), '2021-10-25 22:10:10' ) a5,
FROM_DAYS( 366 ) a6,
TO_DAYS( '0000-12-25' ) a7,
LAST_DAY(NOW()) a8,
MAKEDATE( YEAR ( NOW()), 12 ) a9,
MAKETIME( 10, 21, 23 ) a10,
PERIOD_ADD( 20200101010101, 10 ) a11
FROM
DUAL;
![image-20221007110017615](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205026439-304289186.png)
4.7日期的格式化与解析
函数 |
用法 |
DATE_FORMAT(date,fmt) |
按照字符串fmt格式化日期date值 |
TIME_FORMAT(time,fmt) |
按照字符串fmt格式化时间time值 |
GET_FORMAT(date_type,format_type) |
返回日期字符串的显示格式 |
STR_TO_DATE(str, fmt) |
按照字符串fmt对str进行解析,解析为一个日期 |
- 上述 非GET_FORMAT 函数中fmt参数常用的格式符:
格式符 |
说明 |
格式符 |
说明 |
%Y |
4位数字表示年份 |
%y |
表示两位数字表示年份 |
%M |
月名表示月份(January,....) |
%m |
两位数字表示月份 (01,02,03。。。) |
%b |
缩写的月名(Jan.,Feb.,....) |
%c |
数字表示月份(1,2,3,...) |
%D |
英文后缀表示月中的天数 (1st,2nd,3rd,...) |
%d |
两位数字表示月中的天数(01,02...) |
%e |
数字形式表示月中的天数 (1,2,3,4,5.....) |
|
|
%H |
两位数字表示小数,24小时制 (01,02..) |
%h 和%I |
两位数字表示小时,12小时制 (01,02..) |
%k |
数字形式的小时,24小时制(1,2,3) |
%l |
数字形式表示小时,12小时制 (1,2,3,4....) |
%i |
两位数字表示分钟(00,01,02) |
%S 和%s |
两位数字表示秒(00,01,02...) |
%W |
一周中的星期名称(Sunday...) |
%a |
一周中的星期缩写(Sun., Mon.,Tues.,..) |
%w |
以数字表示周中的天数 (0=Sunday,1=Monday....) |
|
|
%j |
以3位数字表示年中的天数(001,002...) |
%U |
以数字表示年中的第几周, (1,2,3。。)其中Sunday为周中第一 天 |
%u |
以数字表示年中的第几周, (1,2,3。。)其中Monday为周中第一 天 |
|
|
%T |
24小时制 |
%r |
12小时制 |
%p |
AM或PM |
%% |
表示% |
-- 格式化:日期 ---> 字符串
-- 解析: 字符串---> 日期
-- 格式化:
SELECT
DATE_FORMAT( CURDATE(), '%Y-%m-%d' ) ,
DATE_FORMAT(NOW(),'%y-%M-%D'),
TIME_FORMAT(CURRENT_TIME,'%H:%i:%s'),
DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s %p %W %w %T %r')
FROM
DUAL;
-- 格式化的逆过程
SELECT
STR_TO_DATE( '2022-10-07 11:55:37', '%Y-%m-%d %H:%i:%s' )
FROM
DUAL;
![image-20221007120325848](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205027322-892129135.png)
![image-20221007120352257](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205028148-185687563.png)
- GET_FORMAT函数中date_type和format_type参数取值如下:
![image-20221007114138994](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205030749-1705697607.png)
-- GET_FORMAT函数
SELECT GET_FORMAT(DATE, 'USA') FROM DUAL;
SELECT DATE_FORMAT(CURDATE(),GET_FORMAT(DATE, 'USA')) FROM DUAL;
![image-20221007120520089](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205032156-1641240544.png)
![image-20221007120539110](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205033371-1468092406.png)
五、流程控制函数
- 流程处理函数可以根据不同的条件,执行不同的处理流程,可以在SQL语句中实现不同的条件选择。 MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。
函数 |
用法 |
IF(value,value1,value2) |
如果value的值为TRUE,返回value1, 否则返回value2 |
IFNULL(value1, value2) |
如果value1不为NULL,返回value1,否 则返回value2 |
CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 .... [ELSE resultn] END |
相当于Java的if...else if...else... |
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 .... [ELSE 值n] END |
相当于Java的switch...case... |
-- IF(VALUE,VALUE1,VALUE2)
-- IFNULL(value1, value2)
SELECT
j1.sname,
j2.score,
IF( J2.score >= 60, '及格', '不及格' ) AS '评价1',
IFNULL(J2.score, '空值' ) AS '评价2'
FROM
j_student j1
INNER JOIN j_score j2 ON j1.sno = j2.sno;
![image-20221007122408446](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205034876-1513253953.png)
-- CASE WHEN ... THEN ... WHEN ...THEN ...ELSE ...END:相当于Java的if...else if...else...
SELECT
j1.sname,
J2.score,
CASE WHEN j2.score >=90 THEN '优秀'
WHEN j2.score >=80 THEN '良好'
WHEN j2.score>=60 THEN '一般'
ELSE '差劲' END '评估'
FROM
j_student j1
INNER JOIN j_score j2 ON j1.sno = j2.sno;
![image-20221007123254804](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205037122-2010320673.png)
-- -- CASE ... WHEN ... THEN ... WHEN ...THEN ...ELSE ...END :相当于Java的switch...case...
SELECT
j1.sname,
J2.score,
CASE j2.score WHEN 90 THEN 'S'
WHEN 80 THEN 'A'
WHEN 70 THEN 'B'
WHEN 60 THEN 'C'
END '评估'
FROM
j_student j1
INNER JOIN j_score j2 ON j1.sno = j2.sno;
![image-20221007124004516](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205038467-2074766033.png)
六、加密与解密函数
- 加密与解密函数主要用于对数据库中的数据进行加密和解密处理,以防止数据被他人窃取。这些函数在 保证数据库安全时非常有用。
函数 |
用法 |
PASSWORD(str) |
返回字符串str的加密版本,41位长的字符串。加密结果 不可 逆 ,常用于用户的密码加密 |
MD5(str) |
返回字符串str的md5加密后的值,也是一种加密方式。若参数为 NULL,则会返回NULL |
SHA(str) |
从原明文密码str计算并返回加密后的密码字符串,当参数为 NULL时,返回NULL。 SHA加密算法比MD5更加安全 。 |
ENCODE(value,password_seed) |
返回使用password_seed作为加密密码加密value |
DECODE(value,password_seed) |
返回使用password_seed作为加密密码解密value |
-- 加密与解密
-- PASSWORD()在mysql8.0中已弃用
SELECT PASSWORD('MYSQL') FROM DUAL;
SELECT MD5('MYSQL'),MD5(MD5('MYSQL')),SHA('MYSQL') FROM DUAL;
![image-20221007150648873](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205039301-72713646.png)
七、MYSQL信息函数
- MySQL中内置了一些可以查询MySQL信息的函数,这些函数主要用于帮助数据库开发或运维人员更好地 对数据库进行维护工作。
函数 |
用法 |
VERSION() |
返回当前MySQL的版本号 |
CONNECTION_ID() |
返回当前MySQL服务器的连接数 |
DATABASE(),SCHEMA() |
返回MySQL命令行当前所在的数据库 |
USER(),CURRENT_USER()、SYSTEM_USER(), SESSION_USER() |
返回当前连接MySQL的用户名,返回结果格式为 “主机名@用户名” |
CHARSET(value) |
返回字符串value自变量的字符集 |
COLLATION(value) |
返回字符串value的比较规则 |
SELECT
VERSION(),
CONNECTION_ID(),
DATABASE (),
SCHEMA (),
USER (),
CURRENT_USER (),
SYSTEM_USER(),
SESSION_USER(),
CHARSET( 'MYSQL' ),
COLLATION ( 'MYSQL' )
FROM
DUAL;
![image-20221007151424585](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205040473-1838998246.png)
八、其他函数
- MySQL中有些函数无法对其进行具体的分类,但是这些函数在MySQL的开发和运维过程中也是不容忽视 的。
函数 |
用法 |
FORMAT(value,n) |
返回对数字value进行格式化后的结果数据。n表示 四舍五入 后保留 到小数点后n位 |
CONV(value,from,to) |
将value的值进行不同进制之间的转换 |
INET_ATON(ipvalue) |
将以点分隔的IP地址转化为一个数字 |
INET_NTOA(value) |
将数字形式的IP地址转化为以点分隔的IP地址 |
BENCHMARK(n,expr) |
将表达式expr重复执行n次。用于测试MySQL处理expr表达式所耗费 的时间 |
CONVERT(value USING char_code) |
将value所使用的字符编码修改为char_code |
-- 如果n的值小于或者等于0,则只保留整数部分
SELECT FORMAT(123.76324,2),FORMAT(345.123,0),FORMAT(124.54,-2) FROM DUAL;
SELECT CONV(16, 10, 2), CONV(8888,10,16), CONV(NULL, 10, 2) FROM DUAL;
-- 以“192.168.1.100”为例,计算方式为192乘以256的3次方,加上168乘以256的2次方,加上1乘以256,再加上100
SELECT INET_ATON('192.168.1.100'),INET_NTOA(3232235876) FROM DUAL;
-- BENCHMARK():用于测试表达式的执行效率
SELECT BENCHMARK(1000000,MD5('MYSQL')) FROM DUAL;
-- CONVERT():可以实现字符集的转换
SELECT CHARSET('MYSQL'),CONVERT('MYSQL' USING 'utf8mb4' ),CONVERT('MYSQL' USING 'gbk' ) FROM DUAL;
![image-20221007153140701](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205041460-1363608684.png)
![image-20221007153202549](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205041995-226225865.png)
![image-20221007153254941](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205042956-860198470.png)
![image-20221007153350230](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205043926-1836419778.png)
![image-20221007153429273](/i/l/?n=22&i=blog/2301358/202210/2301358-20221007205044887-1616119460.png)
标签:返回,
函数,
05,
--,
单行,
str,
字符串,
SELECT
From: https://www.cnblogs.com/wl-666/p/16765764.html