MySQL常用函数、视图与索引全面梳理

常用函数

数据库中的函数用于对数据进行处理和转换，与编程语言中的函数类似，但具有特定的运行环境。其最显著的特点是执行后必须返回一个确定的值（通常为一行一列的结果），这是进行数据计算、格式化或聚合的基础。

第1步：使用日期函数处理时间数据

目的：获取当前时间、提取日期的年/月/日部分或格式化为特定字符串，常用于日志分析或年龄计算。

• now()：获取当前日期和时间。例如 SELECT now();
• day() / month() / year()：分别提取指定日期的“日”、“月”、“年”。例如 SELECT day(now());
• date_format(date, format)：将日期对象转换为指定格式的字符串，广泛用于报表展示。例如 SELECT date_format(now(), '%Y-%m-%d %H:%i:%s');
• str_to_date(str, format)：将符合特定格式的字符串转换回日期对象。例如 SELECT str_to_date('2017-01-06 10:20:30', '%Y-%m-%d %H:%i:%s');

综合应用场景：

1. 计算年龄：简单算法直接用年份相减，但需考虑当月生日是否已过的情况。若当前月日的数值小于出生月日，说明今年生日未至，需扣减一岁。
2. 查询本月过生日的人员：比较月份字段差值是否为0。

-- 1）直接按年份计算学生年龄（忽略月日差异）
SELECT t.sname, YEAR(NOW()) - YEAR(t.birthday) FROM t_student t;

-- 2）精确年龄：若当前月日 < 出生月日，则真实年龄减一
SELECT sid, sname, birthday,
       YEAR(NOW())-YEAR(birthday) AS '粗略年龄',
       YEAR(NOW())-YEAR(birthday) + IF(CONVERT(DATE_FORMAT(NOW(),'%m%d'),SIGNED) - CONVERT(DATE_FORMAT(birthday,'%m%d'),SIGNED) < 0, -1, 0) AS '真实年龄'
FROM t_student;

-- 3）查询本月过生日的学生信息
SELECT * FROM t_student t WHERE MONTH(NOW()) - MONTH(t.birthday) = 0;

第2步：使用字符串函数处理文本内容

目的：规范化用户输入、提取子串或统计文本长度。

• upper(str) / lower(str)：统一大小写。例：SELECT upper('faafafa');
• replace(str, from_str, to_str)：替换字符串中的子串。例：SELECT replace('www.google.net', 'w', 'n');
• substring(str, pos [, len])：提取子串。pos为起始位置（从1开始），len可选。支持负数位置（从末尾倒数）。例：SELECT substring('abcdefghijk', 1, 3);
• trim(str)：去除字符串首尾空格。
• length(str)：获取字符串字符长度。

第3步：使用数字函数进行数学运算

目的：对数值型数据进行取整或四舍五入，常用于金额或精度的处理。

• floor(x)：向下取整。例：SELECT floor(123.8934); 结果为 123。
• ceil(x)：向上取整。例：SELECT ceil(123.8934); 结果为 124。
• round(x [, d])：四舍五入，d为保留的小数位。例：SELECT round(123.8934, 2); 结果为 123.89。

第4步：使用聚合函数统计数据特征

目的：对多行数据进行汇总计算，常配合 GROUP BY 子句使用；若需对聚合结果过滤，请使用 HAVING 子句而非 WHERE。

• SUM()：求和。
• COUNT()：统计记录数。
• AVG()：求平均值。
• MAX() / MIN()：求最大值和最小值。

数据合并技巧

当需要将两个或多个查询结果集纵向拼接时，可使用 UNION 系列关键字。合并的前提条件是：参与合并的各查询语句必须具有相同数量的列，且对应列的数据类型兼容。

正确执行 UNION 合并操作

目的：在项目统计报表或汇总模块中，合并来自不同条件或多张表的数据。

• UNION：合并结果集并去重。适用于需要排除重复数据的场景。
• UNION ALL：合并结果集并保留所有行（包括重复）。性能通常优于 UNION，因为无需执行去重排序操作。

-- 测试 UNION (去重)
select 'abc', 123 
union
select 'def', 456;
-- 注意：如果再次 select 'abc', 123，因重复将被移除

-- 测试 UNION ALL (不去重)
select sid from t_score where cid = 1
union all
select sid from t_score where cid = 2;
-- 此语句会保留所有匹配的记录，即使同一个学生两门课都考过，其ID会出现两次

视图与索引管理

视图是数据库的高级抽象，索引则是提升性能的底层机制。合理使用二者可大幅提升系统的可维护性与运行效率。

第1步：创建与管理视图

目的：简化复杂的连接查询逻辑，隔离底层表结构变化（提高逻辑独立性），并通过限制访问特定列来增强数据安全。

视图是一种“虚拟表”，数据库中仅存储视图的定义（SQL语句），不存储实际数据。数据仍保存在原表中，查询视图时会动态执行定义中的SQL。

-- 创建一个包含学生基本信息、选修课程数及总分统计的视图
CREATE VIEW stu_score_statistics AS
SELECT 
    t1.sid, 
    t1.sname, 
    t1.ssex,
    COALESCE(t2.courses, 0) AS courses,
    COALESCE(t3.total, 0) AS total_score 
FROM t_student t1 
LEFT JOIN (
    SELECT sid, COUNT(*) AS courses 
    FROM t_score 
    GROUP BY sid
) t2 ON t1.sid = t2.sid
LEFT JOIN (
    SELECT sid, SUM(score) AS total 
    FROM t_score 
    GROUP BY sid
) t3 ON t1.sid = t3.sid;

注：MySQL默认不支持物化视图，如需高性能预计算结果，需借助定时任务或其他技术栈实现。

第2步：理解索引的作用与分类

索引如同书的目录，通过建立特定数据结构（如B+树）加速数据检索。

• 优点：显著提高查询速度，尤其是中型或大型表。
• 缺点：增加磁盘占用空间；在插入、更新和删除数据时，需要同时维护索引，会降低写入性能。
• 分类：
  • 普通索引：最基本的索引，无限制。
  • 唯一索引：索引列的值必须唯一，允许为空（NULL）。
  • 主键索引：特殊的唯一索引，表只能有一个，不允许为空（通常是自增ID）。
  • 组合索引：多列组成的索引，需注意最左前缀原则。
  • 全文索引：专门用于FULLTEXT搜索，适用于长文本。

第3步：维护索引

目的：根据业务需求灵活地添加或移除索引以平衡读写性能。

• 创建索引：

  CREATE INDEX idx_name ON table_name(column_name);

  示例：CREATE INDEX sname_inx ON t_student(sname);
• 修改（添加）索引：

  ALTER TABLE table_name ADD INDEX idx_name(column_name);

  示例：ALTER TABLE t_student ADD INDEX birthday_inx(birthday);
• 删除索引：

  DROP INDEX idx_name ON table_name;

  示例：DROP INDEX birthday_inx ON t_student;

常见错误排查

在实际编写和维护SQL时，遇到报错往往是因为权限、语法或约束问题。以下是高频错误代码的快速对照：