sql 语句相关的函数

1. 聚合函数

这些函数用于对一组值进行计算，并返回单个值。

1.COUNT(): 计算行数。count

SELECT COUNT(*) FROM students;

2.SUM(): 求和。sum

SELECT SUM(salary) FROM employees;

3.AVG(): 计算平均值。avg

SELECT AVG(score) FROM test_scores;

4.MAX(): 找到最大值。max

SELECT MAX(age) FROM users;

5.MIN(): 找到最小值。min 

SELECT MIN(price) FROM products;

2. 字符串函数

1.CONCAT(): 连接两个或多个字符串。concat

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

2.LENGTH(): 返回字符串的长度。length

SELECT LENGTH(name) FROM customers;

3.UPPER(): 将字符串转换为大写。upper

SELECT UPPER(title) FROM books;

4.LOWER(): 将字符串转换为小写。lower

SELECT LOWER(description) FROM products;

5.SUBSTRING(): 提取字符串的一部分。substring

SELECT SUBSTRING(email, 1, 5) FROM users;

3. 日期/时间函数

1.NOW(): 返回当前日期和时间。now

SELECT NOW();

2.CURDATE(): 返回当前日期。curdate

SELECT CURDATE();

3.DATEDIFF(): 计算两个日期之间的差异。datadiff

SELECT DATEDIFF('2024-01-01', '2023-01-01') AS days_difference;

4.DATE_FORMAT(): 格式化日期。date_fromat

SELECT DATE_FORMAT(order_date, '%Y-%m-%d') FROM orders;

4. 数学函数

1.ABS(): 返回绝对值。abs

SELECT ABS(-10) AS absolute_value;

2.CEIL(): 向上取整。ceil

SELECT CEIL(4.3) AS rounded_up;

3.FLOOR(): 向下取整。floor

SELECT FLOOR(4.7) AS rounded_down;

4.POWER(): 计算幂。power

SELECT POWER(2, 3) AS two_cubed;  -- 2^3 = 8

5. ROUND（）：取整round

ROUND 是一个常用的数学函数，用于对数字进行四舍五入。它可以在 SQL 查询中使用，以便将某个数值字段的结果四舍五入到指定的小数位数。

ROUND(number, decimals)

• number: 要四舍五入的数字或表达式。
• decimals: 指定要保留的小数位数。如果这个参数为 0，则会将数字四舍五入到最接近的整数。

SELECT ROUND(3.14159, 2) AS rounded_value;

5. 条件函数

1.CASE: 条件表达式。case

SELECT 
    name,
    CASE 
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        ELSE 'C'
    END AS grade
FROM students;

2.IF(): 简单的条件判断。if

SELECT IF(is_active, 'Active', 'Inactive') AS status FROM users;