MySQL之单行函数

目录

1. 函数的理解

单行函数

 2. 数值函数

2.1 基本函数

2.2 角度与弧度互换函数

2.3 三角函数

2.4 指数与对数

2.5 进制间的转换

3. 字符串函数

4. 日期和时间函数

4.1 获取日期、时间

4.2 日期与时间戳的转换​编辑

4.3 获取月份、星期、星期数、天数等函数

4.4 日期的操作函数

4.5 时间和秒钟转换的函数

4.6 计算日期和时间的函数​编辑

4.7 日期的格式化与解析​编辑

​编辑

5. 流程控制函数

6. 加密与解密函数

7. MySQL信息函数

8. 其他函数

---

1. 函数的理解

        函数在计算机语言的使用中贯穿始终，函数的作用是什么呢？它可以把我们经常使用的代码封装起来，需要的时候直接调用即可。这样既 提高了代码效率 ，又 提高了可维护性 。在 SQL 中我们也可以使用函数对检索出来的数据进行函数操作。使用这些函数，可以极大地 提高用户对数据库的管理效率 。

单行函数

1. 操作数据对象
2. 接受参数返回一个结果
3. 只对一行进行变换
4. 每行返回一个结果
5. 可以嵌套
6. 参数可以是一列或一个值

 2. 数值函数

2.1 基本函数

2.2 角度与弧度互换函数

2.3 三角函数

2.4 指数与对数

2.5 进制间的转换

#1.数值函数
#基本的操作
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),12 MOD 5,12 % 5
FROM DUAL;#取随机数
SELECT RAND(), RAND(), RAND(10), RAND(10), RAND(-1), RAND(-1)
FROM DUAL;#四舍五入，截断操作
SELECT ROUND(123.556),ROUND(123.456, 0),ROUND(123.456, 1),ROUND(123.456, 2),ROUND(123.456, -1),ROUND(153.456, -2)
FROM DUAL;SELECT TRUNCATE(123.456, 0), TRUNCATE(123.496, 1), TRUNCATE(129.45, -1)
FROM DUAL;#单行函数可以嵌套
SELECT TRUNCATE(ROUND(123.456, 2), 0)
FROM DUAL;#角度与弧度的互换SELECT RADIANS(30),RADIANS(45),RADIANS(60),RADIANS(90),DEGREES(2 * PI()),DEGREES(RADIANS(60))
FROM DUAL;#三角函数
SELECT SIN(RADIANS(30)), DEGREES(ASIN(1)), TAN(RADIANS(45)), DEGREES(ATAN(1))
FROM DUAL;#指数和对数
SELECT POW(2, 5), POWER(2, 4), EXP(2)
FROM DUAL;SELECT LN(EXP(2)), LOG(EXP(2)), LOG10(10), LOG2(4)
FROM DUAL;#进制间的转换
SELECT BIN(10), HEX(10), OCT(10), CONV(10, 10, 8)
FROM DUAL;

3. 字符串函数

注意：MySQL中，字符串的位置是从1开始的。

# 在UTF-8中汉字占3个字节
SELECT ASCII('Abcdfsf'),CHAR_LENGTH('hello'),CHAR_LENGTH('我们'),LENGTH('hello'),LENGTH('我们')
FROM DUAL;# xxx worked for yyy
SELECT CONCAT(emp.last_name, ' worked for ', mgr.last_name) "details"
FROM employees empJOIN employees mgr
WHERE emp.`manager_id` = mgr.employee_id;SELECT CONCAT_WS('-', 'hello', 'world', 'hello', 'beijing')
FROM DUAL;
# 结果：hello-world-hello-beijing#字符串的索引是从1开始的！
# insert将从1开始的字符串(idx)之后的几个字符串(len)全替换为替换的内容
# replace 将对应内容替换为需要的内容
SELECT INSERT('helloworld', 2, 3, 'aaaaa'), REPLACE('hello', 'lol', 'mmm')
FROM DUAL;# upper转换为大写
# lower转换为小写
SELECT UPPER('HelLo'), LOWER('HelLo')
FROM DUAL;SELECT last_name, salary
FROM employees
WHERE LOWER(last_name) = 'King';# left(str ,x)取str左边的x个字符
# right(str ,x)取str右边的x个字符
SELECT LEFT('hello', 2), RIGHT('hello', 3), RIGHT('hello', 13)
FROM DUAL;# LPAD:实现右对齐效果
# RPAD:实现左对齐效果
SELECT employee_id, last_name, LPAD(salary, 10, ' ')
FROM employees;# trim(str)：去除str开头和结尾的空格 trim(s1 from s)去除字符串s开始的s1
SELECT CONCAT('---', LTRIM('    h  el  lo   '), '***'),TRIM('oo' FROM 'ooheollo')
FROM DUAL;# repeat(str,x)：重复str字符串x遍
# space(n):返回n个空格
# strcmp(s1,s2):比较s1和s2的大小，从第一个字母开始比较，相同比较下一个(正数前者大，0是一样大，负数是后者大)
SELECT REPEAT('hello', 4), LENGTH(SPACE(5)), STRCMP('abc', 'abe')
FROM DUAL;# substr(str ,indx,len):返回str从indx开始的长度是len个的字符串
# locate(substr ,str):返回substr(子字符串)在字符串str中第一次出现的位置，没有，返回0
SELECT SUBSTR('hello', 2, 2), LOCATE('lll', 'hello')
FROM DUAL;# elt(n,s1,s2,s3):n是几，返回第几个s
# field(s,s1,s2,s3...):返回字符串s在字符串列表中第一次出现的位置
# field_in_sec(s1,s2):返回字符串s1在字符串s2中出现的位置，字符串s2以一个逗号分隔的字符串
SELECT ELT(2, 'a', 'b', 'c', 'd'),FIELD('mm', 'gg', 'jj', 'mm', 'dd', 'mm'),FIND_IN_SET('mm', 'gg,mm,jj,dd,mm,gg')
FROM DUAL;# reverse(str)：反转str字符串
# nullif(value1,value2)；比较value1和value2字符串，相同返回null，不相同返回value1
SELECT employee_id, NULLIF(LENGTH(first_name), LENGTH(last_name)) "compare"
FROM employees;

4. 日期和时间函数

4.1 获取日期、时间

4.2 日期与时间戳的转换

4.3 获取月份、星期、星期数、天数等函数

4.4 日期的操作函数

4.5 时间和秒钟转换的函数

4.6 计算日期和时间的函数

4.7 日期的格式化与解析

上述 非GET_FORMAT 函数中fmt参数常用的格式符： 

GET_FORMAT 函数中 date_type 和 format_type 参数取值如下：

#3. 日期和时间函数
# 规范
# 描述
# %a 缩写工作日名称 （Sun..Sat）
# %b 缩写月份名称 （Jan..Dec）
# %c 月份，数字 （0..12）
# %D 带有英文后缀 （0th， ， 1st， 2nd3rd， ...） 的月份中的某天
# %d 月份中的某天，数字 （00..31）
# %e 月份中的某天，数字 （0..31）
# %f 微秒 （000000..999999）
# %H 小时 （00..23）
# %h 小时 （01..12）
# %I 小时 （01..12）
# %i 分钟，数字 （00..59）
# %j 一年中的某天 （001..366）
# %k 小时 （0..23）
# %l 小时 （1..12）
# %M 月份名称 （January..December）
# %m 月份，数字 （00..12）
# %p AM 或 PM
# %r 时间，12 小时（hh:mm:ss 后跟 AM 或 PM）
# %S 秒 （00..59）
# %s 秒 （00..59）
# %T  时间，24 小时 （hh:mm:ss）
# %U  周 （00..53），其中星期日是一周的第一天; WEEK() 模式 0
# %u  周 （00..53），其中星期一是一周的第一天; WEEK() 模式 1
# %V Week （01..53），其中星期日是一周的第一天; WEEK() 模式 2;与 一起使用 %X
# %v Week （01..53），其中星期一是一周的第一天; WEEK() 模式 3;与 一起使用 %x
# %W 工作日名称 （Sunday..Saturday）
# %w 星期几 （0=星期日..6=星期六）
# %X 星期日是一周的第一天的周的年份，数字，四位数字;用于 %V
# %x Year 表示一周，其中 Monday 是一周的第一天，数字，四位数字;用于 %v
# %Y 年份、数字、四位数字
# %y 年份，数字 （两位数）#3.1  获取日期、时间
# curdate()和current_day():返回当前时间，只包含年月日
# curtime()和current_time():返回当前时间，只包含时分秒
# now()和sysdate()和current_timestamp()和localtime()和localtimestamp():返回当前系统时间
# utc_date():返回utc(世界标准时间)日期
# utc_time():返回utc(世界标准时间)时间 和北京时间相差8小时
SELECT CURDATE(),CURRENT_DATE(),CURTIME(),NOW(),SYSDATE(),UTC_DATE(),UTC_TIME()
FROM DUAL;SELECT CURDATE(), CURDATE() + 0, CURTIME() + 0, NOW() + 0
FROM DUAL;#3.2 日期与时间戳的转换
SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP('2021-10-01 12:12:32'),FROM_UNIXTIME(1635173853),FROM_UNIXTIME(1633061552)
FROM DUAL;#3.3 获取月份、星期、星期数、天数等函数
#year(date)/month(date)/day(date):返回具体时间
# hour(time)/minute(time)/second(time):返回具体时间
# monthname(data):返回星期几
# weekday(data):返回周几 [注意周1是0，周2是1....]
# quarter(data):返回季度几(0范围是1~4)
# week(data):/weekofyear(data):返回一年中第几周
# dayofyear(data):返回一年中第几天
# dayofmonth(data):返回当前月中第几天
# dayofweek(data):返回周几 [注意周1是0，周2是1....]
SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE())
FROM DUAL;SELECT MONTHNAME('2024-11-15'),DAYNAME('2024-11-15'),WEEKDAY('2024-11-15'),QUARTER(CURDATE()),WEEK(CURDATE()),DAYOFYEAR(NOW()),DAYOFMONTH(NOW()),DAYOFWEEK(NOW())
FROM DUAL;#3.4 日期的操作函数
# EXTRACT(SECOND FROM NOW())；返回当前时间秒
# EXTRACT(DAY FROM NOW())：返回当前月的第几天
# EXTRACT(HOUR_MINUTE FROM NOW())：返回当前时间小时和分
# EXTRACT(QUARTER FROM 'XXXX-xx-xx')：返回XXXX-xx-xx时间所在的季度
SELECT EXTRACT(SECOND FROM NOW()),EXTRACT(DAY FROM NOW()),EXTRACT(HOUR_MINUTE FROM NOW()),EXTRACT(QUARTER FROM '2024-11-15')
FROM DUAL;#3.5 时间和秒钟转换的函数
# TIME_TO_SEC(time)：将time转换成秒 小时*3600+分*60+秒
# SEC_TO_TIME(seconds)：将seconds(秒)转换成包含小时，分钟，秒的时间
SELECT TIME_TO_SEC(CURTIME()),#CURTIME()：获取当前时间SEC_TO_TIME(83355)
FROM DUAL;#3.6 计算日期和时间的函数
# DATE_ADD(时间,INTERVAL x YEAR/month/day/hour/minute/second)：在time(时间)上加上或减去x(时间)，可以是年/月/日/时/分/秒
SELECT NOW(),DATE_ADD(NOW(), INTERVAL 1 YEAR),DATE_ADD(NOW(), INTERVAL -1 YEAR),DATE_SUB(NOW(), INTERVAL 1 YEAR)
FROM DUAL;# MINUTE：分钟  SECOND：秒
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY)                               AS col1,DATE_ADD('2024-11-15 11:23:20', INTERVAL 1 SECOND)            AS col2,ADDDATE('2024-11-15 11:23:20', INTERVAL 1 SECOND)             AS col3,DATE_ADD('2024-11-15 11:23:20', 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;# SUBTIME：减时间  ADDTIME：加时间
# MAKETIME(hour，minute，second)：将给定时间组合时间返回
# DATEDIFF(data1，data2)：返回两个时间相差天数
# TIMEDIFF(time1，time2)：返回两个时间相差时间间隔
# TO_DAYS(data)：返回从data距离0000年1月1日的天数
# LAST_DAY(data)：返回data在的月距离最后一天的日期
# MAKEDATE(YEAR ，n)对给的年份和所在年的天数返回一个日期
# PERIOD_ADD(time，n)返回time加n后的时间
SELECT ADDTIME(NOW(), 20),SUBTIME(NOW(), 30),SUBTIME(NOW(), '1:1:3'),DATEDIFF(NOW(), '2024-10-01'),TIMEDIFF(NOW(), '2024-11-15 11:23:20'),FROM_DAYS(366),TO_DAYS('0000-12-25'),LAST_DAY(NOW()),MAKEDATE(YEAR(NOW()), 32),MAKETIME(10, 21, 23),PERIOD_ADD(20200101010101, 10)
FROM DUAL;
# 结果
# 2024-11-15 11:53:52,
# 2024-11-15 11:53:02,
# 2024-11-15 10:52:29,
# 45,
# 00:30:12,
# 0001-01-01,
# 359,2024-11-30,
# 2024-02-01,
# 10:21:23,
# 20200101010111#3.7 日期的格式化与解析
# 格式化：日期 ---> 字符串
# 解析：  字符串 ----> 日期#此时我们谈的是日期的显式格式化和解析#之前，我们接触过隐式的格式化或解析
SELECT *
FROM employees
WHERE hire_date = '1993-01-13';#格式化：
# DATE_FORMAT(data，fmt)：按照fmt字符串格式格式化日期data值
# TIME_FORMAT(time，fmt)：按照fmt字符串格式格式化时间time值
# %Y ：4位是年                           %y：两位是年份
# %M：月(january)                       %m：两位是月份(01,02,03...)
# %D：英文后缀表示月的天数(1st,2nd,3rd)    %d：两位数字表示月中天数(01,0,2,03...)
# %c：数字表示月份(1,2,3,...)             %b：缩写的月名(jan,feb)
# %e：数字形式表示月的天数(1,2,3,4,5...)# %H:24小时制 (小数形式)                  %h和%l：12小时制(两位数字)（01,02,03,...）
# %k：24小时制 (数字形式)                 %l:12小时制 (数字形式)     (1,2,3...)
# %i:两位数字表示分钟(00,01,02...)        %S和%s：两位数字表示秒(00,01,02...)
SELECT DATE_FORMAT(CURDATE(), '%Y-%M-%D'),DATE_FORMAT(NOW(), '%Y-%m-%d'),TIME_FORMAT(CURTIME(), '%h:%i:%S'),DATE_FORMAT(NOW(), '%Y-%M-%D %h:%i:%S %W %w %T %r')
FROM DUAL;#解析：格式化的逆过程
# STR_TO_DATE(str,dmt):按照字符串fmt对str解析为日期
# GET_FORMAT(sata_type，fromat_type)：返回日期字符串的显示格式
SELECT STR_TO_DATE('2021-October-25th 11:37:30 Monday 1', '%Y-%M-%D %h:%i:%S %W %w')
FROM DUAL;SELECT GET_FORMAT(DATE, 'USA')
FROM DUAL;SELECT DATE_FORMAT(CURDATE(), GET_FORMAT(DATE, 'USA'))
FROM DUAL;

5. 流程控制函数

#4.1 IF(VALUE,VALUE1,VALUE2)SELECT last_name, salary, IF(salary >= 6000, '高工资', '低工资') "details"
FROM employees;SELECT last_name,commission_pct,IF(commission_pct IS NOT NULL, commission_pct, 0)                     "details",salary * 12 * (1 + IF(commission_pct IS NOT NULL, commission_pct, 0)) "annual_sal"
FROM employees;#4.2 IFNULL(VALUE1,VALUE2):看做是IF(VALUE,VALUE1,VALUE2)的特殊情况
SELECT last_name, commission_pct, IFNULL(commission_pct, 0) "details"
FROM employees;#4.3 CASE WHEN ... THEN ...WHEN ... THEN ... ELSE ... END
# 类似于java的if ... else if ... else if ... else
SELECT last_name,salary,CASEWHEN salary >= 15000 THEN '白骨精'WHEN salary >= 10000 THEN '潜力股'WHEN salary >= 8000 THEN '小屌丝'ELSE '草根' END "details",department_id
FROM employees;SELECT last_name,salary,CASEWHEN salary >= 15000 THEN '白骨精'WHEN salary >= 10000 THEN '潜力股'WHEN salary >= 8000 THEN '小屌丝'END "details"
FROM employees;#4.4 CASE ... WHEN ... THEN ... WHEN ... THEN ... ELSE ... END
# 类似于java的swich ... case...
/*练习1
查询部门号为 10,20, 30 的员工信息,
若部门号为 10, 则打印其工资的 1.1 倍,
20 号部门, 则打印其工资的 1.2 倍,
30 号部门,打印其工资的 1.3 倍数,
其他部门,打印其工资的 1.4 倍数*/
SELECT employee_id,last_name,department_id,salary,CASE department_idWHEN 10 THEN salary * 1.1WHEN 20 THEN salary * 1.2WHEN 30 THEN salary * 1.3ELSE salary * 1.4 END "details"
FROM employees;/*练习2
查询部门号为 10,20, 30 的员工信息,
若部门号为 10, 则打印其工资的 1.1 倍,
20 号部门, 则打印其工资的 1.2 倍,
30 号部门打印其工资的 1.3 倍数*/
SELECT employee_id,last_name,department_id,salary,CASE department_idWHEN 10 THEN salary * 1.1WHEN 20 THEN salary * 1.2WHEN 30 THEN salary * 1.3END "details"
FROM employees
WHERE department_id IN (10, 20, 30);

6. 加密与解密函数

7. MySQL信息函数

8. 其他函数