MySQL函数

-- 连接字符串
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

-- 截取字符串
SELECT SUBSTRING(email, 1, 5) AS username FROM users;

-- 替换字符串
SELECT REPLACE(description, 'old', 'new') FROM products;

-- 格式化数字
SELECT product_name, FORMAT(price, 2) AS formatted_price FROM products;

-- 提取域名
SELECT email, SUBSTRING(email, LOCATE('@', email) + 1) AS domain FROM users;

-- 四舍五入
SELECT ROUND(price, 2) AS rounded_price FROM products;

-- 计算平均值并四舍五入
SELECT ROUND(AVG(salary), 0) AS avg_salary FROM employees;

-- 随机排序
SELECT * FROM products ORDER BY RAND() LIMIT 5;

-- 计算几何平均值
SELECT EXP(AVG(LOG(price))) AS geometric_mean FROM products;

-- 获取当前日期和时间
SELECT NOW() AS current_datetime;

-- 提取年份
SELECT YEAR(birth_date) AS birth_year FROM employees;

-- 计算年龄
SELECT name, YEAR(CURDATE()) - YEAR(birth_date) AS age FROM employees;

-- 查询最近7天的订单
SELECT * FROM orders 
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY);

-- 格式化日期
SELECT order_id, DATE_FORMAT(order_date, '%Y年%m月%d日') AS formatted_date FROM orders;

-- 计算工作日（排除周末）
SELECT 
    start_date,
    end_date,
    DATEDIFF(end_date, start_date) - 
    FLOOR(DATEDIFF(end_date, start_date) / 7) * 2 -
    (CASE WHEN DAYOFWEEK(start_date) = 1 THEN 1 ELSE 0 END) -
    (CASE WHEN DAYOFWEEK(end_date) = 7 THEN 1 ELSE 0 END) AS work_days
FROM projects;

-- 统计员工总数
SELECT COUNT(*) AS total_employees FROM employees;

-- 计算各部门平均薪资
SELECT department, AVG(salary) AS avg_salary 
FROM employees 
GROUP BY department;

-- 获取每个部门的员工姓名列表
SELECT department, GROUP_CONCAT(name) AS employees 
FROM employees 
GROUP BY department;

-- 计算薪资统计信息
SELECT 
    COUNT(*) AS employee_count,
    AVG(salary) AS average_salary,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary,
    STDDEV(salary) AS salary_stddev
FROM employees;

-- 使用IF函数
SELECT name, score, 
       IF(score >= 60, '及格', '不及格') AS result 
FROM students;

-- 使用CASE语句
SELECT name, score,
       CASE
           WHEN score >= 90 THEN '优秀'
           WHEN score >= 80 THEN '良好'
           WHEN score >= 60 THEN '及格'
           ELSE '不及格'
       END AS grade
FROM students;

-- 处理NULL值
SELECT name, IFNULL(bonus, 0) AS bonus_amount 
FROM employees;

-- 使用COALESCE处理多个可能的NULL值
SELECT name, COALESCE(phone, mobile, '无联系方式') AS contact 
FROM customers;

-- 搜索式CASE语句
SELECT product_name, category_id,
       CASE category_id
           WHEN 1 THEN '电子产品'
           WHEN 2 THEN '家居用品'
           WHEN 3 THEN '服装'
           ELSE '其他'
       END AS category_name
FROM products;