MySQL数据库内置函数和内外连图文教程

MySQL 内置函数与连接查询图文教程

一、MySQL 内置函数

1. 数学函数

-- 绝对值
SELECT ABS(-10); -- 结果: 10

-- 向上/向下取整
SELECT CEIL(3.14); -- 结果: 4
SELECT FLOOR(3.14); -- 结果: 3
SELECT ROUND(3.1415, 2); -- 结果: 3.14

-- 随机数
SELECT RAND(); -- 0~1之间的随机数
SELECT ROUND(RAND() * 100); -- 0~100的随机整数

-- 幂运算
SELECT POWER(2, 3); -- 结果: 8
SELECT SQRT(16); -- 结果: 4

2. 字符串函数

-- 字符串连接
SELECT CONCAT('Hello', ' ', 'World'); -- 结果: Hello World
SELECT CONCAT_WS('-', '2023', '10', '01'); -- 结果: 2023-10-01

-- 字符串长度
SELECT LENGTH('MySQL'); -- 字节数: 5
SELECT CHAR_LENGTH('数据库'); -- 字符数: 3

-- 大小写转换
SELECT UPPER('mysql'); -- 结果: MYSQL
SELECT LOWER('MySQL'); -- 结果: mysql

-- 截取字符串
SELECT SUBSTRING('Hello World', 7, 5); -- 结果: World
SELECT LEFT('MySQL', 2); -- 结果: My
SELECT RIGHT('MySQL', 3); -- 结果: SQL

-- 去除空格
SELECT TRIM('   MySQL   '); -- 结果: MySQL
SELECT LTRIM('   MySQL'); -- 结果: MySQL
SELECT RTRIM('MySQL   '); -- 结果: MySQL

-- 替换字符串
SELECT REPLACE('I love Java', 'Java', 'MySQL'); -- 结果: I love MySQL

3. 日期时间函数

-- 当前日期时间
SELECT NOW(); -- 2023-10-01 14:30:00
SELECT CURDATE(); -- 2023-10-01
SELECT CURTIME(); -- 14:30:00

-- 日期部分提取
SELECT YEAR(NOW()); -- 2023
SELECT MONTH(NOW()); -- 10
SELECT DAY(NOW()); -- 1
SELECT DAYNAME(NOW()); -- Sunday
SELECT WEEKDAY(NOW()); -- 0-6 (周一为0)

-- 日期加减
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY); -- 加7天
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH); -- 减1个月

-- 日期格式化
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日');

4. 聚合函数

-- 统计数量
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT department) FROM employees;

-- 求和、平均
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MIN(salary), MAX(salary) FROM employees;

-- 分组统计
SELECT department, AVG(salary), COUNT(*) 
FROM employees 
GROUP BY department;

二、连接查询详解

连接查询示意图

+------------+        +-------------+
|  员工表    |        |  部门表     |
| (employees)|        | (departments)|
+------------+        +-------------+
| id         |        | id          |
| name       |        | dept_name   |
| dept_id    |<------>| manager_id  |
| salary     |        +-------------+
+------------+

1. 内连接 (INNER JOIN)

概念：只返回两个表中匹配的行

-- 示例数据表
CREATE TABLE departments (
    id INT PRIMARY KEY,
    dept_name VARCHAR(50)
);

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    dept_id INT,
    salary DECIMAL(10, 2)
);

INSERT INTO departments VALUES 
(1, '技术部'), (2, '销售部'), (3, '人事部');

INSERT INTO employees VALUES 
(1, '张三', 1, 8000),
(2, '李四', 2, 6000),
(3, '王五', 1, 9000),
(4, '赵六', 4, 7000); -- dept_id=4不存在

内连接示例：

-- 查询员工及其部门信息
SELECT e.name, e.salary, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

-- 等价写法
SELECT e.name, e.salary, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.id;

结果：

+--------+--------+-----------+
| name   | salary | dept_name |
+--------+--------+-----------+
| 张三   | 8000   | 技术部    |
| 李四   | 6000   | 销售部    |
| 王五   | 9000   | 技术部    |
+--------+--------+-----------+

注：赵六没有显示，因为dept_id=4在部门表中不存在

2. 左连接 (LEFT JOIN)

概念：返回左表所有行 + 右表匹配行

-- 查询所有员工及其部门信息（包括无部门的员工）
SELECT e.name, e.salary, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

结果：

+--------+--------+-----------+
| name   | salary | dept_name |
+--------+--------+-----------+
| 张三   | 8000   | 技术部    |
| 李四   | 6000   | 销售部    |
| 王五   | 9000   | 技术部    |
| 赵六   | 7000   | NULL      |
+--------+--------+-----------+

3. 右连接 (RIGHT JOIN)

概念：返回右表所有行 + 左表匹配行

-- 查询所有部门及其员工信息（包括无员工的部门）
SELECT d.dept_name, e.name, e.salary
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

结果：

+-----------+--------+--------+
| dept_name | name   | salary |
+-----------+--------+--------+
| 技术部    | 张三   | 8000   |
| 技术部    | 王五   | 9000   |
| 销售部    | 李四   | 6000   |
| 人事部    | NULL   | NULL   |
+-----------+--------+--------+

4. 全连接 (FULL OUTER JOIN)

概念：返回两个表的所有行

-- MySQL不支持FULL OUTER JOIN，用UNION实现
SELECT e.name, e.salary, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id

UNION

SELECT e.name, e.salary, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

三、多表连接示例

三表连接示例

-- 创建第三个表：项目表
CREATE TABLE projects (
    project_id INT PRIMARY KEY,
    project_name VARCHAR(50),
    leader_id INT -- 项目负责人ID
);

-- 查询员工、部门、项目信息
SELECT 
    e.name AS 员工姓名,
    d.dept_name AS 部门名称,
    p.project_name AS 负责项目
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id
LEFT JOIN projects p ON e.id = p.leader_id
ORDER BY d.dept_name, e.name;

四、连接查询性能优化

1. 使用索引

-- 为连接字段创建索引
CREATE INDEX idx_dept_id ON employees(dept_id);
CREATE INDEX idx_emp_id ON projects(leader_id);

2. EXPLAIN分析查询

EXPLAIN 
SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id
WHERE e.salary > 5000;

3. 避免 SELECT *

-- 不好
SELECT * FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

-- 好
SELECT e.name, e.salary, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

五、综合示例

示例：统计各部门薪资情况

SELECT 
    d.dept_name AS 部门名称,
    COUNT(e.id) AS 员工人数,
    IFNULL(ROUND(AVG(e.salary), 2), 0) AS 平均薪资,
    IFNULL(SUM(e.salary), 0) AS 薪资总额
FROM departments d
LEFT JOIN employees e ON d.id = e.dept_id
GROUP BY d.id, d.dept_name
ORDER BY 平均薪资 DESC;

总结对比表

连接类型	关键字	描述	返回结果
内连接	INNER JOIN	取交集	只返回匹配的行
左连接	LEFT JOIN	左表全集+右表匹配	左表所有行 + 右表匹配行
右连接	RIGHT JOIN	右表全集+左表匹配	右表所有行 + 左表匹配行
全连接	FULL OUTER JOIN	并集	两个表的所有行