常见的一些SQL函数, 包含数值, 字符串, 日期和IF, CASE等函数
-- 数值函数
-- ROUND 四舍五入, CEILING,最大值
SELECT ROUND(5.73, 1);
SELECT CEILING(5.7);
SELECT FLOOR(5.2);
SELECT ABS(-5.2);
-- 0-1 , 随机数字
SELECT RAND();
-- 字符串函数
SELECT LENGTH('sky');
SELECT UPPER('sky');
SELECT LOWER('Sky');
-- 去空格
SELECT LTRIM(' Sky');
SELECT LTRIM('Sky ');
SELECT TRIM('Sky ');
-- 取一段字符串, 从1开始数
SELECT LEFT('Kindergarten', 4);
SELECT RIGHT('Kindergarten', 4);
SELECT SUBSTRING('Kindergarten', 3, 5);
-- 取字符位置
SELECT LOCATE('q', 'Kindergarten');
-- 替代
SELECT REPLACE('Kindergarten', 'garten', 'garden');
-- 连接
SELECT CONCAT('first', 'last');
USE sql_store;
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM customers;
-- 时间函数
SELECT NOW(), CURDATE(), CURTIME();
SELECT(YEAR(NOW()));
SELECT(MONTH(NOW()));
SELECT(DAY(NOW()));
SELECT(HOUR(NOW()));
SELECT(MINUTE(NOW()));
SELECT(SECOND(NOW()));
SELECT(HOUR(NOW()));
SELECT(DAYNAME(NOW()));
SELECT(MONTHNAME(NOW()));
SELECT EXTRACT(DAY FROM NOW());
-- Exercise
SELECT *
FROM orders
WHERE YEAR(order_date) = YEAR(NOW());
SELECT DATE_FORMAT(NOW(), '%M %d %Y');
-- 计算日期和函数
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);
SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR);
SELECT DATE_SUB(NOW(), INTERVAL 1 YEAR);
SELECT DATEDIFF('2019-01-05', '2019-01-01');
SELECT TIME_TO_SEC('09:00') - TIME_TO_SEC('09:02');
-- IFNULL
USE sql_store;
SELECT
order_id,
shipper_id,
IFNULL(shipper_id, 'Not assigned') AS shipper
FROM orders;
-- COALESCE
SELECT
order_id,
shipper_id,
comments,
COALESCE(shipper_id, comments, 'Not assigned') AS shipper
FROM orders;
-- Exercise
SELECT
CONCAT(first_name, ' ', last_name) AS customer,
IFNULL(phone, 'Unknown') AS phone
FROM customers;
SELECT
CONCAT(first_name, ' ', last_name) AS customer,
COALESCE(phone, 'Unknown') AS phone
FROM customers;
-- IF
SELECT
order_id,
order_date,
IF(
YEAR(order_date) = 2019,
'Active',
'Archived') AS category
FROM orders;
-- Exercise
SELECT
product_id,
name,
COUNT(*) AS orders,
IF(
COUNT(*) > 1,
'Many times',
'Once'
) AS frequency
FROM products
JOIN order_items USING(product_id)
GROUP BY product_id, name;
-- CASE
SELECT
order_id,
CASE
WHEN YEAR(order_date) = 2019 THEN 'Active'
WHEN YEAR(order_date) = 2018 THEN 'Last Year'
WHEN YEAR(order_date) < 2018 THEN 'Archived'
ELSE 'Future'
END AS category
FROM orders;