mysql单行函数：数值、字符串、日期时间、流程控制

起步

• 简介

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

数值

• 基本函数
  

• 代码案例

# 绝对值
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;
 
# 取随机数，当传入的参数相同时，生成的随机数一致
SELECT RAND(), RAND(), RAND(10), RAND(10), RAND(-1), RAND(-1) FROM DUAL;
 
# 四舍五入，截断操作，第2个参数表示小数点后保留几位
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;
 
# 第2个参数为负数时，表示小数点前几位开始四舍五入
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(60), RADIANS(90), DEGREES(2*PI()), DEGREES(RADIANS(90)) FROM DUAL;

• 三角函数
  

• 代码案例

# SIN函数中传入参数为弧度
SELECT SIN(RADIANS(30)), DEGREES(ASIN(1)), TAN(RADIANS(45)), DEGREES(ATAN(1)), DEGREES(ATAN2(1,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;

字符串

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

• 代码案例

# 获取ascii值
SELECT ASCII('Abcdfsf'), CHAR_LENGTH('hello'), CHAR_LENGTH('狗蛋'), LENGTH('hello'), LENGTH('狗蛋') FROM DUAL;
 
# 连接字符串
SELECT CONCAT(emp.last_name, ' worked for ', mgr.last_name) "details" FROM employees emp JOIN employees mgr WHERE emp.`manager_id` = mgr.employee_id;
 
# 为每个字符串拼接上指定值
SELECT CONCAT_WS('-', 'hello', 'world', 'hello', 'beijing') FROM DUAL;
 
# 字符串的索引是从1开始的，替换字符串
SELECT INSERT('helloworld', 2, 3, 'aaaaa'), REPLACE('hello', 'lol', 'mmm') FROM DUAL;
 
# 转大写
SELECT UPPER('HelLo'), LOWER('HelLo') FROM DUAL;
 
# 转小写
SELECT last_name, salary FROM employees WHERE LOWER(last_name) = 'King';
 
# 返回左边的n个字符
SELECT LEFT('hello',2), RIGHT('hello',3), RIGHT('hello',13) FROM DUAL;
 
# LPAD:实现右对齐效果
# RPAD:实现左对齐效果
SELECT employee_id, last_name, LPAD(salary,10,' ') FROM employees;
 
# 连接字符串
SELECT CONCAT('---', LTRIM('    h  el  lo   '), '***'), TRIM('oo' FROM 'ooheollo') FROM DUAL;
 
# 返回str重复n次的结果
SELECT REPEAT('hello',4), LENGTH(SPACE(5)), STRCMP('abc','abe') FROM DUAL;
 
# 返回指定字符串
SELECT SUBSTR('hello',2,2), LOCATE('lll','hello') FROM DUAL;
 
# 返回指定字符串
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;
 
# 比较字符串
SELECT employee_id, NULLIF(LENGTH(first_name), LENGTH(last_name)) "compare" FROM employees;

日期时间

• 获取日期时间
  

• 代码案例

SELECT CURDATE(), CURRENT_DATE(), CURTIME(), NOW(), SYSDATE(), UTC_DATE(), UTC_TIME() FROM DUAL;
 
SELECT CURDATE(), CURDATE() + 0, CURTIME() + 0, NOW() + 0 FROM DUAL;

• 日期与时间戳的转换
  

• 代码案例

# 返回当前时间戳
SELECT UNIX_TIMESTAMP(now());
 
SELECT UNIX_TIMESTAMP(), UNIX_TIMESTAMP('2021-10-01 12:12:32'), FROM_UNIXTIME(1635173853), FROM_UNIXTIME(1633061552) FROM DUAL;

• 获取月份、星期、星期数、天数
  

• 代码案例

# 获取当前年月日时分秒
SELECT YEAR(CURDATE()), MONTH(CURDATE()), DAY(CURDATE()), HOUR(CURTIME()), MINUTE(NOW()), SECOND(SYSDATE()) FROM DUAL;
 
SELECT MONTHNAME('2021-10-26'), DAYNAME('2021-10-26'), WEEKDAY('2021-10-26'), QUARTER(CURDATE()), WEEK(CURDATE()), DAYOFYEAR(NOW()),DAYOFMONTH(NOW()), DAYOFWEEK(NOW()) FROM DUAL;

• 日期的操作
  

• 代码案例

SELECT EXTRACT(MINUTE FROM NOW()), EXTRACT( WEEK FROM NOW()), EXTRACT( QUARTER FROM NOW()), EXTRACT( MINUTE_SECOND FROM NOW()) FROM DUAL;
 
SELECT EXTRACT(SECOND FROM NOW()), EXTRACT(DAY FROM NOW()), EXTRACT(HOUR_MINUTE FROM NOW()), EXTRACT(QUARTER FROM '2021-05-12') FROM DUAL;

• 时间和秒钟转换
  

• 代码案例

SELECT TIME_TO_SEC(CURTIME()), SEC_TO_TIME(83355) FROM DUAL;

• 计算日期和时间
  
  

• 代码案例

SELECT NOW(), DATE_ADD(NOW(), INTERVAL 1 YEAR), DATE_ADD(NOW(), INTERVAL -1 YEAR), DATE_SUB(NOW(), INTERVAL 1 YEAR) FROM DUAL;
 
# 使用负数和单引号
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 ADDTIME(NOW(),20), SUBTIME(NOW(),30), SUBTIME(NOW(),'1:1:3'), DATEDIFF(NOW(), '2021-10-01'), TIMEDIFF(NOW(),'2021-10-25 22:10:10'), 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;

• 日期的格式化与解析
  

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

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

• 代码案例

# 格式化：
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;
 
# 解析，格式化的逆过程
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;

流程控制

• 简介

1、流程处理函数可以根据不同的条件，执行不同的处理流程，可以在SQL语句中实现不同的条件选择
2、MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数

• 代码案例

# 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;
 
# IFNULL(VALUE1,VALUE2):看做是IF(VALUE,VALUE1,VALUE2)的特殊情况
SELECT last_name, commission_pct, IFNULL(commission_pct, 0) "details" FROM employees;
 
# CASE WHEN ... THEN ...WHEN ... THEN ... ELSE ... END
# 类似于java的if ... else if ... else if ... else
SELECT last_name, salary, CASE WHEN salary >= 15000 THEN '白骨精' WHEN salary >= 10000 THEN '潜力股' WHEN salary >= 8000 THEN '小屌丝' ELSE '草根' END "details", department_id FROM employees;
 
SELECT last_name, salary, CASE WHEN salary >= 15000 THEN '白骨精' WHEN salary >= 10000 THEN '潜力股' WHEN salary >= 8000 THEN '小屌丝' END "details" FROM employees;
 
# 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_id WHEN 10 THEN salary * 1.1 WHEN 20 THEN salary * 1.2 WHEN 30 THEN salary * 1.3 ELSE 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_id WHEN 10 THEN salary * 1.1 WHEN 20 THEN salary * 1.2 WHEN 30 THEN salary * 1.3 END "details" FROM employees WHERE department_id IN (10,20,30);

加解密

• 简介

加密与解密函数主要用于对数据库中的数据进行加密和解密处理，以防止数据被他人窃取。这些函数在保证数据库安全时非常有用

• 代码案例

# PASSWORD()在mysql8.0中弃用
SELECT MD5('mysql'), SHA('mysql'), MD5(MD5('mysql')) FROM DUAL;
 
# ENCODE() 和 DECODE() 在mysql8.0中弃用

信息函数

• 简介

MySQL中内置了一些可以查询MySQL信息的函数，这些函数主要用于帮助数据库开发或运维人员更好地对数据库进行维护工作

• 代码案例

SELECT VERSION(), CONNECTION_ID(), DATABASE(), SCHEMA(), USER(), CURRENT_USER(), CHARSET('博客园'), COLLATION('博客园') FROM DUAL;

其他函数

• 代码案例

# 如果n的值小于或者等于0，则只保留整数部分
SELECT FORMAT(123.125,2), FORMAT(123.125,0), FORMAT(123.125,-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(100000, MD5('mysql')) FROM DUAL;
 
# CONVERT()可以实现字符集的转换
SELECT CHARSET('atguigu'), CHARSET(CONVERT('atguigu' USING 'gbk')) FROM DUAL;

课后练习

# 1.显示系统时间(注：日期+时间)
SELECT NOW(), SYSDATE(), CURRENT_TIMESTAMP(), LOCALTIME(), LOCALTIMESTAMP() FROM DUAL;
 
# 2.查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）
SELECT employee_id, last_name, salary, salary * 1.2 "new salary" FROM employees;
 
# 3.将员工的姓名按首字母排序，并写出姓名的长度（length）
SELECT last_name, LENGTH(last_name) "name_length" FROM employees
#order by last_name asc;
ORDER BY name_length ASC;
 
# 4.查询员工id,last_name,salary，并作为一个列输出，别名为OUT_PUT
SELECT CONCAT(employee_id, ',', last_name, ',', salary) "OUT_PUT" FROM employees;
 
# 5.查询公司各员工工作的年数、工作的天数，并按工作年数的降序排序
SELECT employee_id, DATEDIFF(CURDATE(), hire_date)/365 "worked_years", DATEDIFF(CURDATE(),hire_date) "worked_days", TO_DAYS(CURDATE()) - TO_DAYS(hire_date) "worked_days1" FROM employees ORDER BY worked_years DESC;
 
# 6.查询员工姓名，hire_date, department_id，满足以下条件：
# 雇用时间在1997年之后，department_id 为80 或 90 或110, commission_pct不为空
SELECT last_name, hire_date, department_id FROM employees WHERE department_id IN (80,90,110) AND commission_pct IS NOT NULL
#and hire_date >= '1997-01-01';  #存在着隐式转换
#and  date_format(hire_date,'%Y-%m-%d') >= '1997-01-01';  # 显式转换操作，格式化：日期---> 字符串
#and  date_format(hire_date,'%Y') >= '1997';   # 显式转换操作，格式化
AND hire_date >= STR_TO_DATE('1997-01-01','%Y-%m-%d');# 显式转换操作，解析：字符串 ----> 日期
 
# 7.查询公司中入职超过10000天的员工姓名、入职时间
SELECT last_name, hire_date FROM employees WHERE DATEDIFF(CURDATE(), hire_date) >= 10000;
 
# 8.做一个查询，产生下面的结果
# <last_name> earns <salary> monthly but wants <salary*3> 
SELECT CONCAT(last_name, ' earns ', TRUNCATE(salary,0), ' monthly but wants ', TRUNCATE(salary * 3,0)) "Dream Salary" FROM employees;
 
# 9.使用case-when，按照下面的条件：
/* job           grade
 * AD_PRES       A
 * ST_MAN        B
 * IT_PROG       C
 * SA_REP        D
 * ST_CLERK      E
 */
 
SELECT last_name "Last_name", job_id "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'
END "Grade"
FROM employees;
 
SELECT last_name "Last_name", job_id "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 "undefined" END "Grade"
FROM employees;