MySQL函数

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

-- 正则表达式替换
SELECT REGEXP_REPLACE(description, '[0-9]+', '[REDACTED]') FROM products;

-- 连接字符串
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 TRUNCATE(123.456, 2);  -- 123.45
SELECT RADIANS(180);  -- 3.14159

-- 四舍五入
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 TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age FROM users;
SELECT LAST_DAY(CURDATE());  -- 当月最后一天

-- 获取当前日期和时间
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 CONVERT_TZ(NOW(), 'SYSTEM', 'America/New_York');

-- 统计员工总数
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;

-- MD5哈希
SELECT MD5('password123');

-- SHA1/SHA2哈希
SELECT SHA1('password123');
SELECT SHA2('password123', 256);

-- AES加密解密
SET @key = 'my_secret_key_32_bytes_long_123';
SELECT AES_ENCRYPT('sensitive_data', @key);
SELECT AES_DECRYPT(encrypted_data, @key) FROM secure_table;

-- CAST转换
SELECT CAST('123' AS UNSIGNED);
SELECT CAST(123.45 AS CHAR);
SELECT CAST('2023-10-15' AS DATE);

-- CONVERT转换
SELECT CONVERT('123', UNSIGNED);
SELECT CONVERT(123.45, CHAR);
SELECT CONVERT('2023-10-15', DATE);

-- 创建JSON数据
SELECT JSON_OBJECT('name', 'John', 'age', 30);
SELECT JSON_ARRAY(1, 2, 'three');

-- 提取JSON数据
SELECT data, data->'$.name', JSON_EXTRACT(data, '$.address.city') FROM customers;

-- 修改JSON数据
UPDATE customers SET data = JSON_SET(data, '$.age', 31, '$.email', 'john@example.com');
UPDATE customers SET data = JSON_INSERT(data, '$.phone', '123456');  -- 仅当路径不存在时插入
UPDATE customers SET data = JSON_REPLACE(data, '$.age', 32);  -- 仅当路径存在时替换
UPDATE customers SET data = JSON_REMOVE(data, '$.temp_field');

-- JSON数组操作
SELECT JSON_ARRAY_APPEND('[1,2]', '$', 3);  -- [1,2,3]
SELECT JSON_SEARCH(data, 'one', 'John') FROM customers;

-- JSON_TABLE: 将JSON数据转换为关系表（MySQL 8.0+）
SELECT jt.*
FROM customers,
JSON_TABLE(
    data,
    '$' COLUMNS(
        name VARCHAR(50) PATH '$.name',
        age INT PATH '$.age',
        NESTED PATH '$.address' COLUMNS(
            city VARCHAR(50) PATH '$.city'
        )
    )
) AS jt;