MySQL数据库内置函数和内外连教程
在日常的数据库开发与维护中，MySQL的内置函数和多表连接查询（JOIN）是极其重要的核心知识。内置函数能够帮助我们在SQL层面快速处理数据转换、计算与统计，减少后端代码的压力；而内外连接则是处理关系型数据库多表关联数据提取的利器。本文将系统性地讲解这两大模块，并提供详细的代码示例。如需在线实践，可访问 www.ipipp.com 获取相关演示环境。
一、MySQL常用内置函数
MySQL提供了丰富的内置函数，按功能主要分为字符串函数、数值函数、日期函数、聚合函数和流程控制函数。下面我们逐一介绍最常用的函数。
1. 字符串函数
字符串函数主要用于处理文本类型的数据。
CONCAT(s1, s2, ...): 将多个字符串拼接成一个字符串。
LENGTH(s): 返回字符串的字节长度（UTF8编码下，一个汉字占3个字节）。
SUBSTRING(s, start, length): 从字符串s的start位置截取length长度的子串。
UPPER(s) / LOWER(s): 将字符串转换为大写/小写。
-- 字符串函数示例
SELECT CONCAT('MySQL', ' ', 'Tutorial') AS full_str; -- 输出: MySQL Tutorial
SELECT LENGTH('数据库') AS byte_len; -- 输出: 9
SELECT SUBSTRING('Hello World', 1, 5) AS sub_str; -- 输出: Hello
SELECT UPPER('hello') AS upper_str; -- 输出: HELLO2. 数值函数
数值函数用于数学计算。
ROUND(x, y): 对数值x保留y位小数进行四舍五入。
CEIL(x) / FLOOR(x): 向上取整 / 向下取整。
ABS(x): 返回x的绝对值。
-- 数值函数示例 SELECT ROUND(3.1415, 2) AS rounded_val; -- 输出: 3.14 SELECT CEIL(3.14) AS ceil_val; -- 输出: 4 SELECT FLOOR(3.14) AS floor_val; -- 输出: 3 SELECT ABS(-10) AS abs_val; -- 输出: 10
3. 日期和时间函数
日期函数在处理时间戳和业务时间逻辑时必不可少。
NOW(): 返回当前的日期和时间。
CURDATE() / CURTIME(): 返回当前日期 / 当前时间。
DATE_FORMAT(date, format): 按指定格式格式化日期。
DATEDIFF(expr1, expr2): 返回expr1和expr2之间的天数差。
-- 日期函数示例
SELECT NOW() AS current_time; -- 输出: 当前时间 如 2023-10-25 15:30:00
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日') AS formatted_date; -- 输出: 2023年10月25日
SELECT DATEDIFF('2023-12-31', '2023-01-01') AS days_diff; -- 输出: 3644. 流程控制函数
流程控制函数类似于代码中的if-else逻辑，在SQL中非常实用。
IF(cond, val_true, val_false): 如果cond为真，返回val_true，否则返回val_false。
IFNULL(val1, val2): 如果val1不为NULL，返回val1，否则返回val2。
CASE WHEN ... THEN ... ELSE ... END: 多条件分支判断。
-- 流程控制函数示例 SELECT IF(1 > 2, '真', '假') AS result_if; -- 输出: 假 SELECT IFNULL(NULL, '默认值') AS result_ifnull; -- 输出: 默认值 -- CASE WHEN 示例：根据分数划分等级 SELECT student_name, score, CASE WHEN score >= 90 THEN '优秀' WHEN score >= 60 THEN '及格' ELSE '不及格' END AS grade FROM students;
二、MySQL内外连接（JOIN）教程
在实际业务中，数据通常分散在不同的表中以满足范式要求。为了获取完整的信息，我们需要使用连接查询（JOIN）。连接主要分为：内连接（INNER JOIN）、左外连接（LEFT JOIN）和右外连接（RIGHT JOIN）。
1. 数据准备
为了演示连接查询，我们创建两个表：员工表（employees）和部门表（departments）。
-- 创建部门表
CREATE TABLE departments (
dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(50)
);
-- 创建员工表
CREATE TABLE employees (
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(50),
dept_id INT
);
-- 插入测试数据
INSERT INTO departments (dept_name) VALUES ('研发部'), ('销售部'), ('财务部');
INSERT INTO employees (emp_name, dept_id) VALUES ('张三', 1), ('李四', 2), ('王五', NULL);注意：员工“王五”没有分配部门（dept_id为NULL），而“财务部”目前没有员工。
2. 内连接（INNER JOIN）
内连接只返回两个表中连接条件匹配的记录。如果某条记录在另一张表中找不到匹配，则不会出现在结果中。
-- 查询有部门的员工信息（内连接） SELECT e.emp_name, d.dept_name FROM employees e INNER JOIN departments d ON e.dept_id = d.dept_id;
执行结果：只会查出张三和李四，因为王五没有部门ID，财务部没有员工，他们都不满足匹配条件。
3. 左外连接（LEFT JOIN）
左外连接返回左表中的所有记录，即使右表中没有匹配的记录。如果右表没有匹配，则结果中右表的部分全部为NULL。
-- 查询所有员工及其部门信息（左外连接） SELECT e.emp_name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.dept_id;
执行结果：会查出张三、李四和王五。王五对应的dept_name将为NULL，因为左表(employees)的所有记录必须返回。
4. 右外连接（RIGHT JOIN）
右外连接与左外连接相反，它会返回右表中的所有记录，即使左表中没有匹配的记录。如果左表没有匹配，左表部分为NULL。
-- 查询所有部门及其员工信息（右外连接） SELECT e.emp_name, d.dept_name FROM employees e RIGHT JOIN departments d ON e.dept_id = d.dept_id;
执行结果：会查出张三、李四和财务部。财务部对应的emp_name将为NULL，因为右表(departments)的所有记录必须返回。
三、总结
MySQL的内置函数极大地丰富了SQL的数据处理能力，让我们可以在数据库层直接完成部分业务逻辑计算，提升效率。而多表连接（JOIN）则是关系型数据库的灵魂操作，理解INNER JOIN的交集特性以及LEFT/RIGHT JOIN的保全特性，是写出高效、准确查询语句的基础。建议读者结合 www.ipipp.com 提供的在线环境反复练习，以熟练掌握这些关键技能。