常用函数

ganwong / 2023-07-31 / 原文

数值函数

{
SELECT ROUND(5.73,1)  -- 5.73四舍五入保留一位小数
SELECT TRUNCATE(5.7345,2) -- 5.7345截断保留两位小数
SELECT CEILING(5.2) -- 5.2向上取整
SELECT FLOOR(5.2) -- 5.2向下取整
SELECT ABS(-5.73) -- 取绝对值
SELECT RAND() -- 随机获得0-1之间的一个浮点数
}

字符串函数

{
SELECT LENGTH('sky') -- 返回字符串长度
SELECT LOWER('Sky') -- 转化成小写
SELECT UPPER('Sky')-- 转化成大写
SELECT LTRIM('   Sky')-- 去除左侧填充的空格
SELECT RTRIM('Sky   ') -- 去除右侧填充的空格
SELECT TRIM('   Sky   ')-- 去除两侧的空格
SELECT LEFT('Kingdergarden',4)-- 返回字符串左侧的4个字符
SELECT RIGHT('Kingdergarden',6)-- 返回字符串右侧的6个字符
SELECT SUBSTRING('Kingdergarden',3,5)-- 从字符串中，第3个起取5个；省略5则一直到结尾
SELECT LOCATE('N','Kingdergarden')-- 查找字符在字符串内所在位置，不区分大小写
SELECT SUBSTRING_INDEX("www.w3schools.com", ".", 1);-- 在出现指定数量的分隔符之前返回字符串的子字符串，这里是在出现1个'.'之前的子字符串www
SELECT REPLACE('Kindergarten','garten','garden')-- 替换字符串
CONCAT(x, '%')-- 百分比格式化
CONCAT(string1,' ',string2)-- 连接字符串

---

USE sql_store;
SELECT CONCAT(first_name,' ',last_name) AS full_name
FROM customers


SELECT 
    cust_id, 
    cust_name, 
    UPPER(CONCAT(LEFT(cust_contact,2),LEFT(cust_city,3))) AS user_login
FROM Customers
}

日期函数

{
SELECT NOW(),CURDATE(),CURTIME()
SELECT YEAR(NOW())  -- SELECT MONTH(NOW()) 返回整数值的现在的年月日周分秒等
SELECT MONTHNAME(NOW()) -- 返回月的名字，还有SELECT DAYNAME(NOW())
SELECT EXTRACT(YEAR FROM NOW())

---

SELECT *
FROM orders
WHERE YEAR(order_date) = YEAR(NOW())-- 返回当年的订单

---

SELECT order_num, order_date
FROM Orders
WHERE YEAR(order_date)='2020' AND MONTH(order_date) = '01'
ORDER BY order_date
}

格式化日期和时间

{
SELECT DATE_FORMAT(NOW(),'%m %M %Y %d')-- 依次为%m月份，%M月份名字，%Y年份，%d日
SELECT TIME_FORMAT(NOW(),'%H:%i %p')-- %H时，%i分，%p代表am或者pm
}

计算日期和时间

{
SELECT DATE_ADD(NOW(),INTERVAL 1 DAY)--加，返回明天同一时间日期 SELECT DATE_ADD(NOW(),INTERVAL -1 YEAR)返回去年同一时间日期
SELECT DATE_SUB(NOW(),INTERVAL 1 YEAR)-- 减 ，INTERVAL是间隔的意思，不配合函数则需要+-号
SELECT DATEDIFF('2019-01-05','2019-01-01')-- 计算日期间隔，注意两者顺序影响正负值
SELECT TIME_TO_SEC('08:11') -TIME_TO_SEC('08:09') -- 精确到秒计算间隔 -号
TIMESTAMPDIFF(interval, time_start, time_end)-- 可计算time_start-time_end的时间差，单位以指定的interval为准，常用可选：SECOND 秒；MINUTE 分钟（返回秒数差除以60的整数部分）；HOUR 小时（返回秒数差除以3600的整数部分）；DAY 天数（返回秒数差除以3600*24的整数部分）；MONTH 月数；YEAR 年数 小时间在前，大时间在后
}

IFNULL和COALESCE函数

{
SELECT 
	order_id,
    IFNULL(shipper_id,'Not assigned') AS shipper-- 如果shipper_id是NULL值，就替换为后面的字符串
FROM orders

---

SELECT 
	order_id,
    COALESCE(shipper_id,comments,'Not assigned') AS shipper-- 如果shipper_id为NULL就返回comments里的，如果comments还是NULL就返回字符串
FROM orders

}

IF函数

{
SELECT 
	order_id,
    order_date,
    IF(YEAR(order_date)=YEAR(NOW()),'Active','Archived')-- IF(条件,a,b)如果满足条件就返回a，否则返回b
FROM orders

---

SELECT 
	product_id,
    name,
    COUNT(*) AS orders,
    IF(COUNT(*) > 1,'Many times','Once') AS frequency
FROM products 
LEFT JOIN order_items 
	USING(product_id) 
GROUP BY product_id,name
}

CASE运算符

{
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()) THEN 'Archived'
        ELSE 'Future'
    END AS category
FROM orders
}