7 MySQL-单行函数

SoaringW

于 2022-05-31 19:33:17 发布

阅读量103

点赞数

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_40823104/article/details/125072067

版权

MySQL 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

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进制数

-- 1. 数值函数
-- ---------------------- ---------------------- ----------------------
# 1.1 基本函数
SELECT ABS(-123),
       ABS(12),
       SIGN(23),
       PI(),
       CEIL(32.2),
       CEILING(32.333),
       FLOOR(32.12),
       MOD(12, 5)
FROM dual;
# 1.2 随机数函数
SELECT RAND() * 10, RAND(), RAND(10), RAND(10), RAND(-1), RAND(-1)
FROM dual;
# 1.3 四舍五入
SELECT ROUND(123.456), ROUND(123.456, 0), ROUND(123.456, 1), ROUND(123.456, 2), ROUND(123.456, -1)
FROM dual;
# 1.4 截断
SELECT TRUNCATE(123.456, 0), TRUNCATE(123.456, 1), TRUNCATE(123.456, 2)
FROM dual;
# 1.5 嵌套
SELECT FLOOR(RAND() * 20)
FROM dual;
# 1.6 角度和弧度之间的换算
SELECT RADIANS(180), RADIANS(90), DEGREES(PI()), DEGREES(PI() / 2)
FROM dual;
# 1.7 三角函数
SELECT SIN(PI() / 6), COS(PI() / 6), TAN(PI()), ASIN(PI() / 6)
FROM dual;
# 1.8 指数和对数
SELECT POW(2, 4), POW(4, 2), EXP(0), LOG(EXP(1)), LOG2(8), LOG10(100)
FROM dual;
# 1.9 进制间转换
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

-- 2. 字符串函数
-- ---------------------- ---------------------- ----------------------
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;
# INSERT(str,idx,len,replacestr)将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr
# REPLACE(str, a, b)用字符串b替换字符串str中所有出现的字符串a
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	%%	表示%

-- 3. 日期和时间函数
-- ---------------------- ---------------------- ----------------------
-- 3.1 获取日期和时间
SELECT CURDATE(),
       CURRENT_DATE,
       CURTIME(),
       CURRENT_TIME(),
       NOW(),
       SYSDATE(),
       UTC_DATE(),
       UTC_TIME()
FROM dual;

-- 3.2 日期与时间戳的转换
SELECT UNIX_TIMESTAMP(), UNIX_TIMESTAMP(NOW()), FROM_UNIXTIME(UNIX_TIMESTAMP()), UNIX_TIMESTAMP('2022-2-2')
FROM dual;

-- 3.3 获取月份、星期、星期数、天数
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;

-- 3.4 日期的操作函数
SELECT EXTRACT(YEAR FROM CURDATE()), EXTRACT(DAY FROM CURDATE()), EXTRACT(HOUR_MINUTE FROM CURDATE())
FROM dual;

-- 3.5 时间和秒钟转换的函数
SELECT time_to_sec(curtime()), SEC_TO_TIME(64446)
FROM dual;

-- 3.6 计算日期和时间的函数
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;

-- 3.7 日期的格式化与解析
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…

-- 4. 流程控制函数
-- ---------------------- ---------------------- ----------------------
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

-- 5. 加密和解密函数
-- ---------------------- ---------------------- ----------------------
# PASSWORD() ENCODE() DECODE() mysql8.0不支持
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的比较规则

-- 6. 信息函数
-- ---------------------- ---------------------- ----------------------
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

-- 8. Exercise
-- ---------------------- ---------------------- ----------------------

# 1.显示系统时间(注：日期+时间)
SELECT NOW()
FROM dual;

# 2.查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）
SELECT employee_id, last_name, salary, salary * 0.2 AS "new salary"
FROM employees;


# 3.将员工的姓名按首字母排序，并写出姓名的长度（length）
SELECT employee_id, last_name, LENGTH(last_name) AS "length"
FROM employees
ORDER BY last_name ASC;

# 4.查询员工id,last_name,salary，并作为一个列输出，别名为OUT_PUT
SELECT CONCAT(employee_id, '-', last_name, '-', salary) AS "Output"
FROM employees;

# 5.查询公司各员工工作的年数、工作的天数，并按工作年数的降序排序
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;

# 6.查询员工姓名，hire_date , department_id，满足以下条件：雇用时间在1997年之后，department_id
# 为80 或 90 或110, commission_pct不为空
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';

# 7.查询公司中入职超过10000天的员工姓名、入职时间
SELECT last_name, hire_date
FROM employees
WHERE DATEDIFF(CURDATE(), hire_date) > 10000;

# 8.做一个查询，产生下面的结果
SELECT CONCAT(last_name, ' earns ', TRUNCATE(salary, 0), ' monthly but wants ',
              TRUNCATE(salary * 3, 0)) AS "Dream Salary"
FROM employees;
# 9.使用case-when，按照下面的条件：
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';