目录
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 中我们也可以使用函数对检索出来的数据进行函数操作。使用这些函数，可以极大地 提高用户对数据库的管理效率 。
单行函数
- 操作数据对象
- 接受参数返回一个结果
- 只对一行进行变换
- 每行返回一个结果
- 可以嵌套
- 参数可以是一列或一个值
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. 其他函数
