MySQL 常用函数

核心目标： 学习使用 MySQL 内建函数来处理和计算数据，增强 SELECT 查询以及其他 SQL 语句的功能。

前提： 你已经熟悉基本的 SELECT 语句。函数通常用在 SELECT 的列列表、WHERE 子句、GROUP BY 子句、HAVING 子句或 ORDER BY 子句中。

1. 聚合函数 (Aggregate Functions)

作用：对一组行的值进行计算，并返回单个汇总值。常与 GROUP BY 结合使用。

• COUNT(*) / COUNT(column) / COUNT(DISTINCT column):
  计算行数。COUNT(*) 计算所有行；COUNT(column) 计算指定列非 NULL 的行数；COUNT(DISTINCT column) 计算指定列唯一非 NULL 值的数量。

-- 总学生数
select count(*) as total_students from students;
-- 有效 GPA 记录的学生数 (gpa 非 NULL)
select count(gpa) as gpa_count from students;
-- 不同专业的数量
select count(distinct major) as distinct_major_count from students;

• SUM(column):
  计算数值列的总和 (忽略 NULL)。

-- 所有产品总库存
select sum(stock_quantity) as total_stock from products;

• AVG(column):
  计算数值列的平均值 (忽略 NULL)。

-- 平均 GPA
select avg(gpa) as average_gpa from students;

• MAX(column):
  找出列中的最大值。

-- 最高价格
select max(price) as max_price from products;

• MIN(column):
  找出列中的最小值。

-- 最低 GPA
select min(gpa) as min_gpa from students;

2. 字符串函数 (String Functions)

作用：用于操作和处理文本（字符串）数据。

• CONCAT(str1, str2, ...):
  连接（拼接）字符串。

-- 拼接学生姓名
select concat(first_name, ' ', last_name) as full_name from students;

• LENGTH(str):
  返回字符串的字节长度。对于多字节字符（如中文 UTF-8），结果可能大于字符数。

-- 产品名称的字节长度
select product_name, length(product_name) from products;

• CHAR_LENGTH(str) 或 CHARACTER_LENGTH(str):
  返回字符串的字符长度。

-- 产品名称的字符数
select product_name, char_length(product_name) from products;

• SUBSTRING(str, pos, [len]) 或 SUBSTR(str, pos, [len]):
  提取子字符串。pos 从 1 开始，len 是可选的长度。

-- 提取专业名称前两个字
select major, substring(major, 1, 2) as major_prefix from students where major is not null;

• UPPER(str) / LOWER(str):
  转换为大写/小写。

-- 产品名称大写
select upper(product_name) from products;
-- 邮箱小写
select lower(email) from students where email is not null;

• REPLACE(str, from_str, to_str):
  替换字符串中的子串。

-- 将空格替换为下划线
select product_name, replace(product_name, ' ', '_') from products;

• TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str):
  去除首尾（BOTH）、开头（LEADING）或结尾（TRAILING）的空格或指定字符 remstr。

-- 去除首尾空格
select trim('  some text   ') as trimmed; -- 'some text'
-- 去除开头的 '0'
select trim(leading '0' from '00123'); -- '123'

3. 日期和时间函数 (Date and Time Functions)

作用：用于处理日期和时间类型的数据。

• NOW(): 返回当前日期和时间 (YYYY-MM-DD HH:MM:SS)。
• CURDATE(): 返回当前日期 (YYYY-MM-DD)。
• CURTIME(): 返回当前时间 (HH:MM:SS)。

select now(), curdate(), curtime();

• DATE(datetime_expr) / TIME(datetime_expr):
  提取日期/时间部分。

-- 假设 created_at 是 DATETIME 类型
-- select date(created_at), time(created_at) from orders;

• YEAR(date_expr) / MONTH(date_expr) / DAY(date_expr):
  提取年/月/日。

-- 提取入学年份
select student_name, year(enrollment_date) as enroll_year from students;

• DATE_ADD(date, INTERVAL expr unit) / DATE_SUB(date, INTERVAL expr unit):
  日期加/减时间间隔。unit 可以是 DAY, WEEK, MONTH, YEAR, HOUR, MINUTE, SECOND 等。

-- 当前日期加 7 天
select date_add(curdate(), interval 7 day) as next_week;
-- 当前时间减 1 小时
select date_sub(now(), interval 1 hour) as hour_ago;

• DATEDIFF(date1, date2):
  返回两个日期之间的天数差 (date1 - date2)。

-- 计算入学至今的天数
select student_name, datediff(curdate(), enrollment_date) as days_enrolled from students;

• DATE_FORMAT(date, format):
  按指定格式格式化日期。format 是格式代码（如 %Y 年, %m 月, %d 日, %H 时24h, %i 分, %s 秒）。

-- 格式化入学日期为 YYYY年MM月DD日
select student_name, date_format(enrollment_date, '%Y年%m月%d日') as formatted_date from students;

4. 数学函数 (Mathematical Functions)

作用：执行数学运算。

• ABS(x):
  返回 x 的绝对值。

select abs(-50); -- 50

• ROUND(x, [d]):
  四舍五入到 d 位小数（默认为 0）。

select round(123.456); -- 123
select round(123.456, 2); -- 123.46

• CEIL(x) 或 CEILING(x):
  向上取整（返回大于或等于 x 的最小整数）。

select ceil(4.1); -- 5

• FLOOR(x):
  向下取整（返回小于或等于 x 的最大整数）。

select floor(4.9); -- 4

• RAND():
  返回 0 到 1 之间（不含 1）的随机浮点数。

-- 获取一个随机学生
select * from students order by rand() limit 1;

• MOD(N, M) 或 N % M:
  取模（N 除以 M 的余数）。

select mod(10, 3); -- 1
select 10 % 3; -- 1

5. 控制流函数 (Control Flow Functions)

作用：在 SQL 中实现条件逻辑。

• IF(condition, value_if_true, value_if_false):
  如果 condition 为真，返回 value_if_true，否则返回 value_if_false。

-- 根据 GPA 判断是否优秀
select student_name, gpa, if(gpa >= 3.5, '优秀', '良好') as performance from students;

• IFNULL(expr1, expr2):
  如果 expr1 不为 NULL，返回 expr1，否则返回 expr2。常用于处理 NULL 值。

-- 如果 major 为 NULL，显示 '未分配'
select student_name, ifnull(major, '未分配') as student_major from students;

• COALESCE(expr1, expr2, ...): 返回参数列表中第一个非 NULL 的表达式，功能更强大。

-- select coalesce(preferred_name, first_name, 'N/A') as display_name from users;

• CASE WHEN condition1 THEN result1 [WHEN condition2 THEN result2 ...] [ELSE else_result] END:
  多重条件判断（类似 if-else if-else）。

-- 根据价格区间给产品分类
select product_name, price,
case
when price < 100 then '经济型'
when price between 100 and 500 then '标准型'
when price > 500 then '高端型'
else '价格未知' -- 可选的 ELSE
end as price_category
from products;

• CASE expression WHEN value1 THEN result1 [WHEN value2 THEN result2 ...] [ELSE else_result] END:
  简单 CASE 表达式，判断 expression 是否等于 value。

-- 根据部门 ID 显示部门名称 (假设 1=HR, 2=IT)
-- select emp_name,
-- case dept_id
-- when 1 then '人力资源部'
-- when 2 then '信息技术部'
-- else '其他部门'
-- end as department_name
-- from employees;

练习题 (Practice Exercises - Functions with Answers)

假设使用之前的 students 和 products 表数据。

1. 统计 students 表中共有多少个 ‘计算机科学’ 专业的学生。
   答案:

select count(*) from students where major = '计算机科学';

2. 计算 products 表中所有产品的平均价格，结果保留两位小数。
   答案:

select round(avg(price), 2) as average_price from products;

3. 查询 students 表中每个专业的最高 GPA。
   答案:

select major, max(gpa) as highest_gpa from students where major is not null group by major;

4. 查询 products 表中所有产品名称，并将其转换为大写。
   答案:

select upper(product_name) as uppercase_name from products;

5. 查询 students 表中所有学生的姓名（姓和名拼接，中间加空格）和入学年份。
   答案:

select concat(first_name, ' ', last_name) as full_name, year(enrollment_date) as enrollment_year from students;

6. 查询 products 表中所有价格超过 500 元的产品，并计算其价格打九折后的结果（保留两位小数）。
   答案:

select product_name, price, round(price * 0.9, 2) as discounted_price from products where price > 500;

7. 查询 students 表中入学日期在 ‘2022-01-01’ 之后的学生人数。
   答案:

select count(*) from students where enrollment_date > '2022-01-01';

8. 查询 students 表中每个专业名称的字符长度。
   答案:

select distinct major, char_length(major) as major_length from students where major is not null;

9. 找出 products 表中库存量大于 100 且产品名称包含 ‘耳机’ 的产品数量。
   答案:

select count(*) from products where stock_quantity > 100 and product_name like '%耳机%';

10. 查询 students 表，计算每个学生入学 1 年后的日期。
    答案:

select student_id, enrollment_date, date_add(enrollment_date, interval 1 year) as one_year_after_enrollment from students;

11. 查询 students 表，显示学生姓名，如果专业 (major) 为 NULL，则显示 “待定专业”，否则显示原专业名称。
    答案:

select student_name, ifnull(major, '待定专业') as student_major from students;

12. 查询 products 表，根据库存量 (stock_quantity) 显示库存状态：0 表示 ‘售罄’, 1-50 表示 ‘库存紧张’, 大于 50 表示 ‘库存充足’。
    答案:

select product_name, stock_quantity,
case
when stock_quantity = 0 then '售罄'
when stock_quantity between 1 and 50 then '库存紧张'
when stock_quantity > 50 then '库存充足'
else '状态未知'
end as stock_status
from products;