7 MySQL-单行函数
数值函数
基本函数
函数 | 用法 |
---|
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 |
角度与弧度函数
函数 | 用法 |
---|
RADIANS(x) | 将角度转化为弧度,其中,参数x为角度值 |
DEGREES(x) | 将弧度转化为角度,其中,参数x为弧度值 |
三角函数
函数 | 用法 |
---|
SIN(x) | 返回x的正弦值,其中,参数x为弧度值 |
ASIN(x) | 返回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(m,n) | 返回两个参数的反正切值 |
COT(x) | 返回x的余切值,其中,X为弧度值 |
指数和对数
函数 | 用法 |
---|
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 |
进制间的转换
函数 | 用法 |
---|
BIN(x) | 返回x的二进制编码 |
HEX(x) | 返回x的十六进制编码 |
OCT(x) | 返回x的八进制编码 |
CONV(x,f1,f2) | 返回f1进制数变成f2进制数 |
SELECT ABS(-123),
ABS(12),
SIGN(23),
PI(),
CEIL(32.2),
CEILING(32.333),
FLOOR(32.12),
MOD(12, 5)
FROM dual;
SELECT RAND() * 10, RAND(), RAND(10), RAND(10), RAND(-1), RAND(-1)
FROM dual;
SELECT ROUND(123.456), ROUND(123.456, 0), ROUND(123.456, 1), ROUND(123.456, 2), ROUND(123.456, -1)
FROM dual;
SELECT TRUNCATE(123.456, 0), TRUNCATE(123.456, 1), TRUNCATE(123.456, 2)
FROM dual;
SELECT FLOOR(RAND() * 20)
FROM dual;
SELECT RADIANS(180), RADIANS(90), DEGREES(PI()), DEGREES(PI() / 2)
FROM dual;
SELECT SIN(PI() / 6), COS(PI() / 6), TAN(PI()), ASIN(PI() / 6)
FROM dual;
SELECT POW(2, 4), POW(4, 2), EXP(0), LOG(EXP(1)), LOG2(8), LOG10(100)
FROM dual;
SELECT BIN(10), HEX(22), OCT(10), CONV(8, 16, 8)
FROM dual;
字符串函数
函数 | 用法 |
---|
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开始与结尾的s |
TRIM(LEADING s FROM s) | 去掉字符串s开始处的s |
TRIM(TRAILING s FROM s) | 去掉字符串s结尾处的s |
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 |
SELECT ASCII('abc'), CHAR_LENGTH('我'), LENGTH('我')
FROM dual;
SELECT CONCAT('hello', ' world', ' sql') AS "str"
FROM dual;
SELECT CONCAT(first_name, '_', last_name) AS "name"
FROM employees;
SELECT INSERT('hello world', 2, 3, 'aaaaa'), REPLACE('hello world', 'world', 'sql')
FROM dual;
SELECT UPPER('abC'), LOWER('ABc')
FROM dual;
SELECT LEFT('abcdef', 2), RIGHT('abcdef', 3)
FROM dual;
SELECT employee_id, last_name, LPAD(salary, 10, ' '), RPAD(salary, 10, '*')
FROM employees;
SELECT LENGTH(TRIM(' hello ')),
LTRIM(' hello '),
RTRIM(' hello '),
TRIM('o' FROM 'oooohellooo')
FROM dual;
SELECT REPEAT('hello', 3), LENGTH(SPACE(5)), STRCMP('abc', 'ss')
FROM dual;
SELECT SUBSTR('abcedf', 2, 3), LOCATE('d', 'abcdef')
FROM dual;
SELECT ELT(2, 'abc', 'cde', 'xxx'), FIELD('abc', 'cde', 'abcd', 'abc')
FROM dual;
SELECT REVERSE('abcdef')
FROM dual;
日期和时间函数
获取日期、时间
函数 | 用法 |
---|
CURDATE() ,CURRENT_DATE() | 返回当前日期,只包含年、月、日 |
CURTIME() , CURRENT_TIME() | 返回当前时间,只包含时、分、秒 |
NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP() | 返回当前系统日期和时间 |
UTC_DATE() | 返回UTC(世界标准时间)日期 |
UTC_TIME() | 返回UTC(世界标准时间)时间 |
日期与时间戳的转换
函数 | 用法 |
---|
UNIX_TIMESTAMP() | 以UNIX时间戳的形式返回当前时间。SELECT UNIX_TIMESTAMP() - >1634348884 |
UNIX_TIMESTAMP(date) | 将时间date以UNIX时间戳的形式返回。 |
FROM_UNIXTIME(timestamp) | 将UNIX时间戳的时间转换为普通格式的时间 |
获取月份、星期、星期数、天数等函数
函数 | 用法 |
---|
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 |
日期的操作函数
函数 | 用法 |
---|
EXTRACT(type FROM date) | 返回指定日期中特定的部分,type指定返回的值 |
时间和秒钟转换的函数
函数 | 用法 |
---|
TIME_TO_SEC(time) | 将time转化为秒并返回结果值。转化的公式为:小时 * 3600+分钟 * 60+秒 |
SEC_TO_TIME(seconds) | 将 seconds 描述转化为包含小时、分钟和秒的时间 |
计算日期和时间的函数
函数 | 用法 |
---|
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时间间隔的日期 |
函数 | 用法 |
---|
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后的时间 |
日期的格式化与解析
函数 | 用法 |
---|
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进行解析,解析为一个日期 |
格式符 | 说明 | 格式符 | 说明 |
---|
%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 CURDATE(),
CURRENT_DATE,
CURTIME(),
CURRENT_TIME(),
NOW(),
SYSDATE(),
UTC_DATE(),
UTC_TIME()
FROM dual;
SELECT UNIX_TIMESTAMP(), UNIX_TIMESTAMP(NOW()), FROM_UNIXTIME(UNIX_TIMESTAMP()), UNIX_TIMESTAMP('2022-2-2')
FROM dual;
SELECT YEAR('2022-3-5 12:45:20') AS "year",
MONTH(NOW()) AS "month",
DAY(NOW()) AS "day",
HOUR(NOW()) AS "hour",
MINUTE(NOW()) AS "minute",
SECOND(NOW()) AS "second",
WEEK(CURDATE()) AS "week",
WEEKDAY(CURDATE()) + 1 % 7 AS "weekday",
DAYOFWEEK(CURDATE()) AS "us weekday",
DAYOFYEAR(CURDATE()) AS "year dayth",
DAYOFMONTH(CURDATE()) AS "month dayth",
DAYOFWEEK(CURDATE()) AS "week dayth"
FROM dual;
SELECT EXTRACT(YEAR FROM CURDATE()), EXTRACT(DAY FROM CURDATE()), EXTRACT(HOUR_MINUTE FROM CURDATE())
FROM dual;
SELECT time_to_sec(curtime()), SEC_TO_TIME(64446)
FROM dual;
SELECT date_add(now(), Interval 1 YEAR )
FROM dual;
SELECT date_sub(now(), INTERVAL 2 YEAR )
FROM dual;
SELECT addtime('20:20:20', 50), subtime('20:20:20', -50),
DATEDIFF(now(), '1998-10-23'),timediff(curtime(), '06:00:00'),
from_days(2020*365),to_days(now()),
last_day(curdate()), makedate(2000,200), maketime(10,20,30)
FROM dual;
SELECT *
FROM employees
WHERE hire_date = '1993-01-13';
SELECT date_format(now(), '%y-%m-%d %h:%i:%s %w'),
date_format(now(), '%Y-%M-%D %H:%i:%S %W'),
TIME_FORMAT(curtime(), '%H:%i:%s')
FROM dual;
SELECT str_to_date('22-05-31 06:32:49', '%y-%m-%d %h:%i:%s')
FROM dual;
流程控制函数
函数 | 用法 |
---|
IF(value,value1,value2) | 如果value的值为TRUE,返回value1,否则返回value |
IFNULL(value1, value2) | 如果value1不为NULL,返回value1,否则返回value |
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… |
SELECT last_name, salary, IF(salary >= 10000, 'high', 'low') AS 'detail'
FROM employees;
SELECT last_name, commission_pct, IF(commission_pct IS NOT NULL, commission_pct, 0) AS 'new commission_pct'
FROM employees;
SELECT last_name, commission_pct, IFNULL(commission_pct, 0) AS 'new commission_pct'
FROM employees;
SELECT last_name,
salary,
CASE
WHEN salary >= 15000 THEN 'A'
WHEN salary > 10000 THEN 'B'
WHEN salary > 8000 THEN 'C'
ELSE 'D' END AS "class"
FROM employees;
SELECT last_name,
salary,
department_id,
CASE department_id
WHEN 10 THEN salary * 1.1
WHEN 20 THEN salary * 1.2
WHEN 30 THEN salary * 1.3
ELSE salary * 1.4 END 'new sal'
FROM employees
WHERE department_id IN (10,20,30);
加密与解密函数
函数 | 用法 |
---|
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 |
SELECT MD5('12345678'), SHA('12345678')
FROM dual;
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()
FROM dual;
SELECT CHARSET('string'), collation('str')
FROM dual;
其他函数
函数 | 用法 |
---|
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 |
Exercise
SELECT NOW()
FROM dual;
SELECT employee_id, last_name, salary, salary * 0.2 AS "new salary"
FROM employees;
SELECT employee_id, last_name, LENGTH(last_name) AS "length"
FROM employees
ORDER BY last_name ASC;
SELECT CONCAT(employee_id, '-', last_name, '-', salary) AS "Output"
FROM employees;
SELECT employee_id,
last_name,
hire_date,
YEAR(CURDATE()) - YEAR(hire_date) AS yearNum,
DATEDIFF(CURDATE(), hire_date) AS dayNum
FROM employees
ORDER BY yearNum DESC;
SELECT hire_date, department_id
FROM employees
WHERE commission_pct IS NOT NULL
AND department_id IN (80, 90, 110)
AND YEAR(hire_date) > '1997';
SELECT last_name, hire_date
FROM employees
WHERE DATEDIFF(CURDATE(), hire_date) > 10000;
SELECT CONCAT(last_name, ' earns ', TRUNCATE(salary, 0), ' monthly but wants ',
TRUNCATE(salary * 3, 0)) AS "Dream Salary"
FROM employees;
SELECT last_name,
job_id,
CASE job_id
WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
WHEN 'SA_REP' THEN 'D'
WHEN 'ST_CLERK' THEN 'E'
ELSE 'F' END AS "grade"
FROM employees
WHERE last_name = 'King';