MySQL的基本函数

MySQL的基本函数

数值函数 Numeric Functions

SELECT ROUND(5.7365, 2)  -- 四舍五入
SELECT TRUNCATE(5.7365, 2)  -- 截断
SELECT CEILING(5.2)  -- 天花板，上取整
SELECT FLOOR(5.6)  -- 地板，下取整
SELECT ABS(-5.2)  -- 绝对值，和c++的abs函数相同
SELECT RAND()  -- 随机[0, 1)的浮动数，相对于java的Math.Random()，也可以传入随机种子

字符串函数 String Functions

SELECT LENGTH('sky')  -- 字符串字符个数/长度（LENGTH）
SELECT UPPER('sky')  -- 转大写
SELECT LOWER('Sky')  -- 转小写
-- 去除空格等不可见字符，可以指定左右
SELECT LTRIM('  Sky')
SELECT RTRIM('Sky  ')
SELECT TRIM(' Sky ')
-- 切片
SELECT LEFT('Kindergarden', 4)  -- 取左边4个字符
SELECT RIGHT('Kindergarden', 6)  -- 取右边6个字符
SELECT SUBSTRING('Kindergarden', 7, 6)  -- 从第7位开始长度为6的字符串，第三个参数可以省略（默认到结尾）
SELECT LOCATE('gar', 'Kindergarden') -- 定位子串首次出现的位置，下标从1开始，不存在返回0
-- 替换
SELECT REPLACE('Kindergarten', 'garten', 'garden') -- 所有相同的子串都会被替换
-- 连接 在mybatis中很好用
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM customers

日期函数 Date Functions

SELECT NOW()  -- 2020-09-12 08:50:46
SELECT CURDATE()  -- current date, 2020-09-12
SELECT CURTIME()  -- current time, 08:50:46
 -- 还可以使用MONTH, DAY, HOUR, MINUTE, SECOND，返回值是数值类型
SELECT YEAR(NOW()) -- 当前是第几年
-- 返回字符串类型
SELECT DAYNAME(NOW())  -- Saturday
SELECT MONTHNAME(NOW())  -- September

-- 标准SQL函数
-- 同时支持MONTH, DAY, HOUR, MINUTE, SECOND
SELECT EXTRACT(YEAR FROM NOW())

-- gist: 返回今年的订单信息
SELECT * 
FROM orders
WHERE YEAR(order_date) = YEAR(now());

-- 格式化日期和时间 Formatting Dates and Times
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日') -- 2021年08月15日
SELECT TIME_FORMAT(NOW(), '%H:%i %p')  -- 16:53 PM

-- 计算日期和时间 Calculating Dates and Times
SELECT DATE_ADD(NOW(), INTERVAL -1 DAY)
SELECT DATE_SUB(NOW(), INTERVAL 1 YEAR)

-- 计算日期之差
SELECT DATEDIFF('2021-8-15 16:56:39', '2021-8-13 16:00:39') -- 2只能计算天数的差值
-- 通过秒数计算时间之差
SELECT TIME_TO_SEC('09:00') - TIME_TO_SEC('08:20') -- 2400

条件函数

-- The IFNULL 
SELECT 
    order_id,
    IFNULL(shipper_id, 'Not Assigned') AS shipper
FROM orders;

-- COALESCE Functions
SELECT 
    order_id,
    -- coalesce(val, ...) 返回第一个非null的值
    COALESCE(shipper_id, comments, 'Not Assigned') AS shipper
FROM orders;

-- IFNULL Functions
SELECT 
    CONCAT(first_name, ' ', last_name) AS customer,
    IFNULL(phone, 'Unknown') AS phone   
FROM customers;

-- The IF Function
-- gist: 对订单分类，把今年的记为Active，其他记为Archived
SELECT 
    *,
    IF(YEAR(order_date) = YEAR(NOW()),
       'Active',
       'Archived') AS category
FROM orders;

-- The IF Function
-- gist: 订单出现一次和多次进行打标签
SELECT 
    product_id,
    name,
    COUNT(*) AS orders,
    IF(COUNT(*) = 1, 'Once', 'Many times') AS frequency
FROM products
JOIN order_items USING(product_id)
GROUP BY product_id, name; -- 注意最好把出现的列都作为group by 语句

-- case when then end 多条件判断
SELECT
    order_id,
    CASE
        WHEN YEAR(order_date) = YEAR(NOW()) THEN 'Active'
        WHEN YEAR(order_date) = YEAR(NOW()) - 1 THEN 'Last Year'
        WHEN YEAR(order_date) < YEAR(NOW()) - 1 THEN 'Achived'
        ELSE 'Future'  
    END AS 'category'
FROM orders;

-- case when then end 多条件判断
SELECT
    CONCAT(first_name, ' ', last_name) AS customer,
    points,
    CASE
        WHEN points < 2000 THEN 'Bronze'
        WHEN points BETWEEN 2000 AND 3000 THEN 'Silver'
        WHEN points > 3000 THEN 'Gold'
        -- ELSE null
    END AS category
FROM customers
ORDER BY points DESC;

补充标准的SQL函数

-- ● 算术函数（用来进行数值计算的函数）
+ - * / 
ABS(数值)  -- ABS 函数的参数为 NULL 时，结果也是 NULL
MOD(被除数，除数)
ROUND(对象数值，保留小数的位数)

-- ● 字符串函数（用来进行字符串操作的函数）
-- || 函数在 SQL Server 和 MySQL 中无法使用, MySQL使用CONCAT函数来完成字符串的拼接
||函数(字符串拼接) -- 包含 NULL，结果是NULL，可以平均3个或更多的参数
LENGTH(字符串)
   SELECT LENGTH('山田'); -- 结果是6，因为是按字节计算，感觉没什么意义
LOWER(字符串)
UPPER(字符串)
REPLACE(对象字符串，替换前的字符串，替换后的字符串)
-- （PostgreSQL/MySQL专用语法）该函数也存在和LENGTH函数同样的多字节符的问题
SUBSTRING（对象字符串 FROM 截取的起始位置 FOR 截取的字符数）
SELECT SUBSTRING('我是一个中国人', 5, 3); -- 中国人

-- ● 日期函数（用来进行日期操作的函数）
SELECT CURRENT_DATE; -- 2021-08-16
SELECT CURRENT_TIME; -- 10:45:46
SELECT CURRENT_TIMESTAMP; -- 2021-08-16 10:46:33
-- EXTRACT(日期元素 FROM 日期) 截取日期元素
SELECT CURRENT_TIMESTAMP,
EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS YEAR,
EXTRACT(MONTH FROM CURRENT_TIMESTAMP) AS MONTH,
EXTRACT(DAY FROM CURRENT_TIMESTAMP) AS DAY,
EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS HOUR,
EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS MINUTE,
EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS SECOND;
-- CURRENT_TIMESTAMP	year	month	day	hour	minute	second
-- 2021-08-16 10:47:59	2021	8	16	10	47	59

-- ● 转换函数（用来转换数据类型和值的函数）
SELECT CAST('0001' AS SIGNED INTEGER) AS int_col; -- 将字符串类型转换为数值类型
SELECT CAST('2009-12-14' AS DATE) AS date_col; -- 将字符串类型转换为日期类型
-- COALESCE——将NULL转换为其他值
SELECT COALESCE(NULL, 1) AS col_1, -- 1
COALESCE(NULL, 'test', NULL) AS col_2, -- test
COALESCE(NULL, NULL, '2009-11-01') AS col_3; -- 2009-11-01

-- ● 聚合函数（用来进行数据聚合的函数）