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, ) 或 SUBSTR(str, pos, ):
提取子字符串。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} 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 位小数（默认为 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 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 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 表数据。
[*]统计 students 表中共有多少个 ‘盘算机科学’ 专业的学生。
答案:
select count(*) from students where major = '计算机科学';
[*]盘算 products 表中全部产品的平均价格，效果生存两位小数。
答案:
select round(avg(price), 2) as average_price from products;
[*]查询 students 表中每个专业的最高 GPA。
答案:
select major, max(gpa) as highest_gpa from students where major is not null group by major;
[*]查询 products 表中全部产品名称，并将其转换为大写。
答案:
select upper(product_name) as uppercase_name from products;
[*]查询 students 表中全部学生的姓名（姓和名拼接，中心加空格）和入学年份。
答案:
select concat(first_name, ' ', last_name) as full_name, year(enrollment_date) as enrollment_year from students;
[*]查询 products 表中全部价格超过 500 元的产品，并盘算其价格打九折后的效果（生存两位小数）。
答案:
select product_name, price, round(price * 0.9, 2) as discounted_price from products where price > 500;
[*]查询 students 表中入学日期在 ‘2022-01-01’ 之后的学生人数。
答案:
select count(*) from students where enrollment_date > '2022-01-01';
[*]查询 students 表中每个专业名称的字符长度。
答案:
select distinct major, char_length(major) as major_length from students where major is not null;
[*]找出 products 表中库存量大于 100 且产品名称包罗 ‘耳机’ 的产品数量。
答案:
select count(*) from products where stock_quantity > 100 and product_name like '%耳机%';
[*]查询 students 表，盘算每个学生入学 1 年后的日期。
答案:
select student_id, enrollment_date, date_add(enrollment_date, interval 1 year) as one_year_after_enrollment from students;
[*]查询 students 表，显示学生姓名，如果专业 (major) 为 NULL，则显示 “待定专业”，否则显示原专业名称。
答案:
select student_name, ifnull(major, '待定专业') as student_major from students;
[*]查询 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;
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]