MySQL 基础（三）：函数、约束、表关系与多表查询

1. 常用函数#

1.1 字符串函数#

函数	作用	示例
`CONCAT(s1, s2, ...)`	拼接字符串	`CONCAT('Hello', ' MySQL')`
`LOWER(str)`	转小写	`LOWER('MySQL')`
`UPPER(str)`	转大写	`UPPER('mysql')`
`LPAD(str, n, pad)`	左侧填充到指定长度	`LPAD('1', 5, '0')`
`RPAD(str, n, pad)`	右侧填充到指定长度	`RPAD('1', 5, '0')`
`TRIM(str)`	去除两端空格	`TRIM(' abc ')`
`SUBSTRING(str, start, len)`	截取字符串；位置从 1 开始	`SUBSTRING('Hello', 1, 2)`
`LENGTH(str)`	返回字节数	`LENGTH('中国')`
`CHAR_LENGTH(str)`	返回字符数	`CHAR_LENGTH('中国')`
`REPLACE(str, from, to)`	替换子串	`REPLACE('abc', 'b', 'B')`

工号补零：

1
UPDATE emp
2
SET work_no = LPAD(work_no, 5, '0');

CONCAT 的任一参数为 NULL 时，结果可能为 NULL。需要忽略空值拼接时，可使用 CONCAT_WS 或先用 COALESCE 处理。

1.2 数值函数#

函数	作用
`CEIL(x)` / `CEILING(x)`	向上取整
`FLOOR(x)`	向下取整
`ROUND(x, d)`	四舍五入并保留 `d` 位小数
`ABS(x)`	绝对值
`MOD(x, y)`	取余
`RAND()`	生成 `[0, 1)` 范围的伪随机数
`POW(x, y)` / `POWER(x, y)`	幂运算
`SQRT(x)`	平方根

1
SELECT CEIL(1.1), FLOOR(1.9), MOD(7, 4), ROUND(2.344, 2);

资料中的六位验证码示例适合演示函数，但不适合安全验证码。安全验证码应由应用层使用密码学安全随机源生成，并设置有效期与重试限制。

1.3 日期函数#

函数	作用
`CURDATE()`	当前日期
`CURTIME()`	当前时间
`NOW()`	当前日期和时间
`YEAR(date)` / `MONTH(date)` / `DAY(date)`	提取年月日
`DATE_ADD(date, INTERVAL n unit)`	增加时间间隔
`DATE_SUB(date, INTERVAL n unit)`	减少时间间隔
`DATEDIFF(date1, date2)`	`date1 - date2` 的天数
`DATE_FORMAT(date, format)`	格式化日期时间

1
SELECT NOW();
2
SELECT DATE_ADD(CURDATE(), INTERVAL 7 DAY);
3
SELECT DATEDIFF(CURDATE(), '2026-01-01');
4
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');

查询员工入职天数：

1
SELECT
2
    name,
3
    DATEDIFF(CURDATE(), entry_date) AS entry_days
4
FROM emp
5
ORDER BY entry_days DESC;

1.4 流程控制函数#

1
-- IF
2
SELECT IF(score >= 60, '及格', '不及格');
3

4
-- IFNULL：第一个参数为 NULL 时返回第二个参数
5
SELECT IFNULL(nickname, name) AS display_name
6
FROM user;
7

8
-- COALESCE：返回第一个非 NULL 参数
9
SELECT COALESCE(nickname, username, '匿名用户') AS display_name
10
FROM user;

简单 CASE：

1
SELECT
2
    name,
3
    CASE work_address
4
        WHEN '北京' THEN '一线城市'
5
        WHEN '上海' THEN '一线城市'
6
        ELSE '其他城市'
7
    END AS city_level
8
FROM emp;

搜索 CASE：

1
SELECT
2
    name,
3
    CASE
4
        WHEN score >= 85 THEN '优秀'
5
        WHEN score >= 60 THEN '及格'
6
        ELSE '不及格'
7
    END AS score_level
8
FROM student_score;

条件从上到下判断，应把更严格的条件放在前面。

2. 约束与表关系#

2.1 常用约束#

约束	关键字	作用
非空	`NOT NULL`	禁止字段为 `NULL`
唯一	`UNIQUE`	禁止出现重复值
主键	`PRIMARY KEY`	唯一标识一行，天然非空且唯一
默认值	`DEFAULT`	插入时未指定该字段则采用默认值
检查	`CHECK`	限制字段值必须满足条件
外键	`FOREIGN KEY`	维护父子表之间的引用完整性

1
CREATE TABLE app_user (
2
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
3
    username VARCHAR(50) NOT NULL UNIQUE,
4
    age TINYINT UNSIGNED,
5
    status TINYINT NOT NULL DEFAULT 1,
6
    CONSTRAINT chk_app_user_age CHECK (age IS NULL OR age BETWEEN 1 AND 120),
7
    CONSTRAINT chk_app_user_status CHECK (status IN (0, 1))
8
);

补充：

一张表只能有一个主键，但主键可以由多个字段组成。
AUTO_INCREMENT 常与整数主键配合。
MySQL 8.0.16 以后，CHECK 约束才真正执行检查；本资料使用的 8.0.26 会执行。
UNIQUE 对 NULL 的处理与普通值不同，不应把它当作“必须填写”的替代品；需要必填时同时加 NOT NULL。

2.2 外键#

建表时添加：

1
CREATE TABLE dept (
2
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
3
    name VARCHAR(50) NOT NULL UNIQUE
4
);
5

6
CREATE TABLE emp (
7
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
8
    name VARCHAR(50) NOT NULL,
9
    dept_id BIGINT UNSIGNED,
10
    CONSTRAINT fk_emp_dept
11
        FOREIGN KEY (dept_id) REFERENCES dept(id)
12
);

已有表添加或删除：

1
ALTER TABLE emp
2
    ADD CONSTRAINT fk_emp_dept
3
    FOREIGN KEY (dept_id) REFERENCES dept(id);
4

5
ALTER TABLE emp
6
    DROP FOREIGN KEY fk_emp_dept;

父表字段和子表外键字段的类型、符号属性应一致；创建外键前，已有数据也必须满足引用关系。

2.3 外键更新与删除行为#

行为	说明
`RESTRICT` / `NO ACTION`	存在子表引用时，拒绝删除或更新父表记录
`CASCADE`	父表更新或删除时，级联更新或删除子表记录
`SET NULL`	父表变化时将子表外键设为 `NULL`；外键列必须允许 `NULL`
`SET DEFAULT`	InnoDB 不支持该行为

1
ALTER TABLE emp
2
    ADD CONSTRAINT fk_emp_dept
3
    FOREIGN KEY (dept_id) REFERENCES dept(id)
4
    ON UPDATE CASCADE
5
    ON DELETE SET NULL;

ON DELETE CASCADE 可能批量删除大量子表数据，应根据业务语义谨慎选择。

2.4 三种常见表关系#

一对多#

例：一个部门有多个员工，一个员工属于一个部门。

实现：在“多”的一方 emp 保存外键 dept_id，指向 dept.id。

多对多#

例：学生可以选择多门课程，一门课程可以被多个学生选择。

实现：增加中间表，并用联合唯一约束避免重复选课。

1
CREATE TABLE student_course (
2
    student_id BIGINT UNSIGNED NOT NULL,
3
    course_id BIGINT UNSIGNED NOT NULL,
4
    selected_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
5
    PRIMARY KEY (student_id, course_id),
6
    CONSTRAINT fk_sc_student
7
        FOREIGN KEY (student_id) REFERENCES student(id),
8
    CONSTRAINT fk_sc_course
9
        FOREIGN KEY (course_id) REFERENCES course(id)
10
);

一对一#

例：用户基本信息和用户教育信息。

实现：在详情表中使用外键，并为外键增加 UNIQUE；也可以直接让详情表主键同时作为外键。

1
CREATE TABLE user_profile (
2
    user_id BIGINT UNSIGNED PRIMARY KEY,
3
    degree VARCHAR(20),
4
    university VARCHAR(100),
5
    CONSTRAINT fk_profile_user
6
        FOREIGN KEY (user_id) REFERENCES app_user(id)
7
);

3 多表查询#

3.1 笛卡尔积与连接条件#

1
SELECT * FROM emp, dept;

若 emp 有 17 行、dept 有 6 行，会得到 102 种组合。多表查询必须明确连接关系，避免无意义的笛卡尔积。

3.2 内连接#

内连接只返回两表能够匹配的记录。

1
-- 显式内连接，推荐
2
SELECT
3
    e.id,
4
    e.name AS employee_name,
5
    d.name AS department_name
6
FROM emp AS e
7
INNER JOIN dept AS d ON d.id = e.dept_id;

隐式内连接也能实现，但复杂查询中不够直观：

1
SELECT e.name, d.name
2
FROM emp AS e, dept AS d
3
WHERE e.dept_id = d.id;

推荐使用显式 JOIN ... ON ...，将“表怎么关联”和“业务如何过滤”分开。

3.3 外连接#

左连接保留左表全部记录；右表未匹配的列补 NULL：

1
SELECT e.id, e.name, d.name AS department_name
2
FROM emp AS e
3
LEFT JOIN dept AS d ON d.id = e.dept_id;

查询所有部门，包括没有员工的部门：

1
SELECT
2
    d.id,
3
    d.name,
4
    COUNT(e.id) AS employee_count
5
FROM dept AS d
6
LEFT JOIN emp AS e ON e.dept_id = d.id
7
GROUP BY d.id, d.name;

这里应使用 COUNT(e.id)，不能用 COUNT(*)；对于没有员工的部门，左连接仍产生一行占位结果，COUNT(*) 会错误地计为 1。

3.4 `ON` 与 `WHERE` 的位置#

对内连接而言，很多过滤条件放在 ON 或 WHERE 中结果相同；对外连接而言，两者可能完全不同。

保留所有部门，只连接在职员工：

1
SELECT d.name, e.name
2
FROM dept AS d
3
LEFT JOIN emp AS e
4
    ON e.dept_id = d.id
5
   AND e.status = 1;

如果把 e.status = 1 放入 WHERE，右表为 NULL 的行会被过滤，结果可能退化得像内连接。

3.5 自连接#

同一张表扮演两个角色时必须使用不同别名：

1
SELECT
2
    e.name AS employee_name,
3
    m.name AS manager_name
4
FROM emp AS e
5
LEFT JOIN emp AS m ON m.id = e.manager_id;

3.6 UNION 与 UNION ALL#

1
SELECT id, name FROM emp WHERE salary < 5000
2
UNION ALL
3
SELECT id, name FROM emp WHERE age > 50;

UNION ALL 直接合并，保留重复行，通常更快。
UNION 合并后去重。
各查询的列数必须相同，对应列的数据类型应兼容。

MySQL 没有直接的 FULL OUTER JOIN 语法。需要完整保留两边时，可用左连接与“仅右表未匹配部分”组合：

1
SELECT a.id AS a_id, b.id AS b_id
2
FROM table_a AS a
3
LEFT JOIN table_b AS b ON b.a_id = a.id
4

5
UNION ALL
6

7
SELECT a.id AS a_id, b.id AS b_id
8
FROM table_a AS a
9
RIGHT JOIN table_b AS b ON b.a_id = a.id
10
WHERE a.id IS NULL;

3.7 子查询分类#

分类	返回结果	常见操作符
标量子查询	一行一列	`=`、`<>`、`>`、`>=`、`<`、`<=`
列子查询	多行一列	`IN`、`NOT IN`、`ANY`、`SOME`、`ALL`
行子查询	一行多列	`=`、`<>`、`IN`、`NOT IN`
表子查询	多行多列	放在 `FROM` 后作为派生表，或配合多列 `IN`

标量子查询：

1
SELECT *
2
FROM emp
3
WHERE salary > (SELECT AVG(salary) FROM emp);

列子查询：

1
SELECT *
2
FROM emp
3
WHERE dept_id IN (
4
    SELECT id
5
    FROM dept
6
    WHERE name IN ('研发部', '市场部')
7
);

行子查询：

1
SELECT *
2
FROM emp
3
WHERE (salary, manager_id) = (
4
    SELECT salary, manager_id
5
    FROM emp
6
    WHERE name = '张无忌'
7
);

表子查询：

1
SELECT e.name, d.name AS department_name
2
FROM (
3
    SELECT *
4
    FROM emp
5
    WHERE entry_date > '2006-01-01'
6
) AS e
7
LEFT JOIN dept AS d ON d.id = e.dept_id;

3.8 相关子查询与 EXISTS#

相关子查询会引用外层查询字段：

1
-- 查询低于本部门平均工资的员工
2
SELECT e.id, e.name, e.salary, e.dept_id
3
FROM emp AS e
4
WHERE e.salary < (
5
    SELECT AVG(e2.salary)
6
    FROM emp AS e2
7
    WHERE e2.dept_id = e.dept_id
8
);

EXISTS 关注子查询是否至少返回一行：

1
-- 查询至少有一名员工的部门
2
SELECT d.id, d.name
3
FROM dept AS d
4
WHERE EXISTS (
5
    SELECT 1
6
    FROM emp AS e
7
    WHERE e.dept_id = d.id
8
);

SELECT 1 表示只关心是否存在，不依赖子查询实际返回哪些列。

音乐

I'm Aron

音乐

1. 常用函数#

1.1 字符串函数#

1.2 数值函数#

1.3 日期函数#

1.4 流程控制函数#

2. 约束与表关系#

2.1 常用约束#

2.2 外键#

2.3 外键更新与删除行为#

2.4 三种常见表关系#

一对多#

多对多#

一对一#

3 多表查询#

3.1 笛卡尔积与连接条件#

3.2 内连接#

3.3 外连接#

3.4 `ON` 与 `WHERE` 的位置#

3.5 自连接#

3.6 UNION 与 UNION ALL#

3.7 子查询分类#

3.8 相关子查询与 EXISTS#

评论区

音乐

文章目录

音乐

I'm Aron

音乐

MySQL 基础（三）：函数、约束、表关系与多表查询

1. 常用函数#

1.1 字符串函数#

1.2 数值函数#

1.3 日期函数#

1.4 流程控制函数#

2. 约束与表关系#

2.1 常用约束#

2.2 外键#

2.3 外键更新与删除行为#

2.4 三种常见表关系#

一对多#

多对多#

一对一#

3 多表查询#

3.1 笛卡尔积与连接条件#

3.2 内连接#

3.3 外连接#

3.4 ON 与 WHERE 的位置#

3.5 自连接#

3.6 UNION 与 UNION ALL#

3.7 子查询分类#

3.8 相关子查询与 EXISTS#

评论区

音乐

文章目录

3.4 `ON` 与 `WHERE` 的位置#