常见函数
– 字符串函数
-- CONCAT将多个字符串连接成一个字符串
SELECT CONCAT(id,name,department) FROM employees
-- LOWER(str)把字符串转成小写
SELECT LOWER(name) FROM employees WHERE id = 6
-- UPPER(STR)(str)把字符串转成大写
SELECT UPPER(name) FROM employees WHERE id = 6
-- LPAD(str,len,padstr) 左填充
SELECT LPAD(NAME,7,'SSS') FROM employees WHERE name='liuyuqing'
-- RPAD(str,len,padstr) 左填充 例句表示不足7位就填充sss
SELECT RPAD(NAME,7,'SSS') FROM employees WHERE name='com.github'
-- TRIM([remstr FROM] str) 去除前后空格
SELECT TRIM(name) FROM employees WHERE id = 10
-- SUBSTRING(str FROM pos FOR len) 字符截取
SELECT SUBSTRING(name,1,2) FROM employees WHERE id =10
– 数值函数
-- CEIL()向上取整
SELECT CEIL(213.1)
-- FLOOR(X) 向下取整
SELECT FLOOR(67.6)
-- MOD 求余
SELECT MOD(3,1)
-- RAND() 随机数
SELECT RAND()
-- ROUND(X) 四舍五入
SELECT ROUND(15.5)
– 日期函数
-- CURDATE() yyyy-MM-dd
SELECT CURDATE()
-- CURTIME() HH:mm:ss
SELECT CURTIME()
-- NOW() 当前时间 yyyy-MM-dd HH:mm:ss
SELECT NOW()
-- YEAR
SELECT YEAR(NOW())
-- MONTH
SELECT MONTH(NOW())
-- DAY
SELECT DAY(NOW())
-- DATE_ADD(date,INTERVAL expr unit) 在原有日期上加
SELECT DATE_ADD(NOW(),INTERVAL 7 DAY)
-- DATA 两个日期差值(返回天数)
SELECT DATEDIFF('2023-08-12',CURDATE())