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 值的数量。
  

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

复制代码

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

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

复制代码

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

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

复制代码

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

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

复制代码

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

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

复制代码

2. 字符串函数 (String Functions)
作用：用于操作和处置惩罚文本（字符串）数据。

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

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

复制代码

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

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

复制代码

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

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

复制代码

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

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

复制代码

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

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

复制代码

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

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

复制代码

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

1. -- 去除首尾空格
2. select trim('  some text   ') as trimmed; -- 'some text'
3. -- 去除开头的 '0'
4. 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)。
  

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

复制代码

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

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

复制代码

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

1. -- 提取入学年份
2. 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 等。
  

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

复制代码

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

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

复制代码

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

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

复制代码

4. 数学函数 (Mathematical Functions)
作用：实行数学运算。

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

1. select abs(-50); -- 50

复制代码

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

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

复制代码

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

1. select ceil(4.1); -- 5

复制代码

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

1. select floor(4.9); -- 4

复制代码

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

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

复制代码

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

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

复制代码

5. 控制流函数 (Control Flow Functions)
作用：在 SQL 中实现条件逻辑。

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

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

复制代码

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

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

复制代码

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

1. -- 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）。
  

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

复制代码

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

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

复制代码

---

练习题 (Practice Exercises - Functions with Answers)
假设利用之前的 students 和 products 表数据。

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

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

复制代码

• 盘算 products 表中全部产品的平均价格，效果生存两位小数。
  答案:
  

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

复制代码

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

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

复制代码

• 查询 products 表中全部产品名称，并将其转换为大写。
  答案:
  

1. select upper(product_name) as uppercase_name from products;

复制代码

• 查询 students 表中全部学生的姓名（姓和名拼接，中心加空格）和入学年份。
  答案:
  

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

复制代码

• 查询 products 表中全部价格超过 500 元的产品，并盘算其价格打九折后的效果（生存两位小数）。
  答案:
  

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

复制代码

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

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

复制代码

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

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

复制代码

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

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

复制代码

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

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

复制代码

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

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

复制代码

• 查询 products 表，根据库存量 (stock_quantity) 显示库存状态：0 体现 ‘售罄’, 1-50 体现 ‘库存告急’, 大于 50 体现 ‘库存充足’。
  答案:
  

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

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。