缘博客
发布日期

MySQL基础-2

作者
  • 姓名
    缘
    社交账号
文章目录

函数

函数是指一段可以直接被其他程序调用的程序或代码。

MySQL 提供了大量内置函数，可以直接在 SQL 语句中调用。基础篇主要介绍：

  • 字符串函数
  • 数值函数
  • 日期函数
  • 流程函数

常用函数

字符串函数

函数功能
CONCAT(S1, S2, ..., Sn)将多个字符串拼接成一个字符串
LOWER(str)将字符串全部转换为小写
UPPER(str)将字符串全部转换为大写
LPAD(str, n, pad)在字符串左侧填充内容，使其达到指定长度
RPAD(str, n, pad)在字符串右侧填充内容，使其达到指定长度
TRIM(str)去除字符串首尾的空格
SUBSTRING(str, start, len)从指定位置开始截取指定长度的字符串

字符串位置从 1 开始计算。

拼接字符串：

SELECT CONCAT('Hello', ' ', 'MySQL');

结果：

Hello MySQL

转换大小写：

SELECT LOWER('Hello MySQL');
SELECT UPPER('Hello MySQL');

左填充与右填充：

SELECT LPAD('01', 5, '-');
SELECT RPAD('01', 5, '-');

结果：

---01
01---

当原字符串长度超过目标长度时，LPAD 和 RPAD 会将结果截短到指定长度。

去除首尾空格：

SELECT TRIM('   Hello MySQL   ');

截取字符串：

SELECT SUBSTRING('Hello MySQL', 1, 5);

结果：

Hello

SUBSTR() 是 SUBSTRING() 的同义写法：

SELECT SUBSTR('Hello MySQL', 7, 5);

结果：

MySQL

字符串函数案例：统一员工工号长度

需求：员工工号统一为 5 位，不足 5 位的在左侧补 0。

例如：

1    → 00001
12   → 00012
123  → 00123

执行：

UPDATE emp
SET workno = LPAD(workno, 5, '0');

查询结果：

SELECT id, workno, name
FROM emp;

数值函数

函数功能
CEIL(x) / CEILING(x)向上取整
FLOOR(x)向下取整
MOD(x, y)返回 x 除以 y 的余数
RAND()返回一个 0 到 1 之间的随机数
ROUND(x, y)对 x 四舍五入，保留 y 位小数

向上取整：

SELECT CEIL(1.1);

结果：

2

向下取整：

SELECT FLOOR(1.9);

结果：

1

求余数：

SELECT MOD(5, 3);

结果：

2

生成随机数：

SELECT RAND();

四舍五入：

SELECT ROUND(3.1415926, 2);

结果：

3.14

数值函数案例：生成六位随机验证码

SELECT LPAD(ROUND(RAND() * 1000000), 6, '0');

也可以使用向下取整：

SELECT LPAD(FLOOR(RAND() * 1000000), 6, '0');

执行结果可能为：

038521

RAND() 每次执行的结果通常不同。该写法适合课堂演示，不适合作为高安全性的验证码生成方式。

日期函数

函数功能
CURDATE() / CURRENT_DATE()返回当前日期
CURTIME() / CURRENT_TIME()返回当前时间
NOW() / CURRENT_TIMESTAMP()返回当前日期和时间
YEAR(date)获取年份
MONTH(date)获取月份
DAY(date)获取日期中的日
DATE_ADD(date, INTERVAL expr type)在指定日期上增加一段时间
DATE_SUB(date, INTERVAL expr type)在指定日期上减去一段时间
DATEDIFF(date1, date2)返回两个日期之间相差的天数

查询当前日期：

SELECT CURDATE();

查询当前时间：

SELECT CURTIME();

查询当前日期和时间：

SELECT NOW();

获取年月日：

SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());

在当前时间基础上增加 70 天：

SELECT DATE_ADD(NOW(), INTERVAL 70 DAY);

在当前时间基础上减少 1 个月：

SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);

常见时间间隔单位：

单位说明
YEAR年
MONTH月
DAY日
HOUR小时
MINUTE分钟
SECOND秒

计算日期差值：

SELECT DATEDIFF('2026-08-02', '2026-08-01');

结果：

1

DATEDIFF(date1, date2) 的计算方向是：

date1 - date2

日期函数案例：统计员工入职天数

查询所有员工的入职天数：

SELECT name,
       DATEDIFF(CURDATE(), entrydate) AS entry_days
FROM emp;

按照入职天数降序排列：

SELECT name,
       DATEDIFF(CURDATE(), entrydate) AS entry_days
FROM emp
ORDER BY entry_days DESC;

流程函数

流程函数可以在 SQL 中实现条件判断，根据不同条件返回不同结果。

函数功能
IF(value, t, f)条件为真返回 t，否则返回 f
IFNULL(value1, value2)value1 不为 NULL 时返回它，否则返回 value2
CASE expr WHEN value THEN result ... ELSE default END根据表达式的具体值返回结果
CASE WHEN condition THEN result ... ELSE default END根据多个条件判断返回结果

IF 示例：

SELECT IF(TRUE, 'OK', 'Error');

结果：

OK

IFNULL 示例：

SELECT IFNULL('OK', 'Default');
SELECT IFNULL(NULL, 'Default');

结果：

OK
Default

简单 CASE：

CASE 表达式
    WHEN 值1 THEN 结果1
    WHEN 值2 THEN 结果2
    ELSE 默认结果
END

示例：将员工所在城市划分为一线城市和二线城市。

SELECT name,
       CASE workaddress
           WHEN '北京' THEN '一线城市'
           WHEN '上海' THEN '一线城市'
           ELSE '二线城市'
       END AS city_level
FROM emp;

搜索 CASE：

CASE
    WHEN 条件1 THEN 结果1
    WHEN 条件2 THEN 结果2
    ELSE 默认结果
END

示例：根据员工年龄划分年龄段。

SELECT name,
       age,
       CASE
           WHEN age < 18 THEN '未成年'
           WHEN age <= 35 THEN '青年'
           WHEN age <= 60 THEN '中年'
           ELSE '老年'
       END AS age_group
FROM emp;

CASE 会从上到下依次判断，匹配到第一个成立的条件后返回结果，后面的条件不再继续判断。

函数案例

案例一：成绩等级转换

假设有如下学生成绩表：

CREATE TABLE score (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    math INT,
    english INT,
    chinese INT
);

将分数转换为等级：

SELECT name,
       math,
       CASE
           WHEN math >= 85 THEN '优秀'
           WHEN math >= 60 THEN '及格'
           ELSE '不及格'
       END AS math_level
FROM score;

案例二：统计员工工龄

SELECT name,
       entrydate,
       DATEDIFF(CURDATE(), entrydate) AS entry_days,
       FLOOR(DATEDIFF(CURDATE(), entrydate) / 365) AS entry_years
FROM emp;

案例三：格式化员工信息

SELECT CONCAT(name, '（', gender, '）') AS employee,
       LPAD(workno, 5, '0') AS workno,
       UPPER(workaddress) AS workaddress
FROM emp;

函数小结

字符串函数：处理字符串
数值函数：处理数值和随机数
日期函数：获取、计算日期和时间
流程函数：在 SQL 中实现条件判断

约束

约束是作用于表中字段上的规则，用于限制表中存储的数据。

约束的主要目的，是保证数据库中数据的正确性、有效性和完整性。

约束可以在以下时机添加：

  • 创建表时添加
  • 修改表时添加

常用约束

约束描述关键字
非空约束限制字段不能存储 NULLNOT NULL
唯一约束保证字段中的值唯一、不重复UNIQUE
主键约束一行数据的唯一标识，要求非空且唯一PRIMARY KEY
默认约束未指定字段值时使用默认值DEFAULT
检查约束保证字段值满足指定条件CHECK
外键约束建立表之间的联系，保证数据一致性和完整性FOREIGN KEY

AUTO_INCREMENT 不是约束，但通常与整数主键配合使用，用于自动生成递增值。

约束演示

需求：创建用户表。

字段名含义类型要求
id用户 IDINT主键、自动增长
name姓名VARCHAR(10)非空、唯一
age年龄INT大于 0 且小于等于 120
status状态CHAR(1)默认值为 1
gender性别CHAR(1)无特殊约束

建表语句：

CREATE TABLE user (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
    name VARCHAR(10) NOT NULL UNIQUE COMMENT '姓名',
    age INT CHECK (age > 0 AND age <= 120) COMMENT '年龄',
    status CHAR(1) DEFAULT '1' COMMENT '状态',
    gender CHAR(1) COMMENT '性别'
) COMMENT '用户表';

正常插入：

INSERT INTO user (name, age, status, gender)
VALUES
    ('Tom1', 19, '1', '男'),
    ('Tom2', 25, '0', '男');

省略 status 时使用默认值：

INSERT INTO user (name, age, gender)
VALUES ('Tom3', 20, '女');

由于 status 没有指定，因此保存为：

1

违反非空约束：

INSERT INTO user (name, age, gender)
VALUES (NULL, 20, '男');

违反唯一约束：

INSERT INTO user (name, age, gender)
VALUES ('Tom1', 20, '男');

违反检查约束：

INSERT INTO user (name, age, gender)
VALUES ('Tom4', 150, '男');

主键约束

主键用于唯一标识表中的一条记录。

主键具有以下特点：

  1. 不能为 NULL。
  2. 不能重复。
  3. 一张表只能定义一个主键。
  4. 一个主键可以由一个字段组成，也可以由多个字段共同组成。

单字段主键：

id INT PRIMARY KEY

表级写法：

CREATE TABLE student (
    id INT,
    name VARCHAR(20),
    PRIMARY KEY (id)
);

联合主键：

CREATE TABLE student_course (
    student_id INT,
    course_id INT,
    score INT,
    PRIMARY KEY (student_id, course_id)
);

联合主键表示：

student_id 和 course_id 的组合不能重复

自动增长

id INT PRIMARY KEY AUTO_INCREMENT

插入数据时可以省略自增字段：

INSERT INTO user (name, age, gender)
VALUES ('Tom5', 22, '男');

也可以显式写入 NULL：

INSERT INTO user
VALUES (NULL, 'Tom6', 23, '1', '女');

自增值通常只保证递增，不保证连续。插入失败、事务回滚或删除记录后，都可能留下空缺。

修改表时添加约束

添加非空约束：

ALTER TABLE user
MODIFY name VARCHAR(10) NOT NULL;

添加唯一约束：

ALTER TABLE user
ADD CONSTRAINT uk_user_name UNIQUE (name);

添加检查约束：

ALTER TABLE user
ADD CONSTRAINT chk_user_age CHECK (age > 0 AND age <= 120);

删除唯一约束时，本质上通常删除对应的唯一索引：

ALTER TABLE user
DROP INDEX uk_user_name;

删除检查约束：

ALTER TABLE user
DROP CHECK chk_user_age;

外键约束

外键用于让两张表之间建立联系，保证数据的一致性和完整性。

例如：

部门表 dept：保存部门信息
员工表 emp：通过 dept_id 保存员工所属部门

其中：

dept.id      主表主键
emp.dept_id  子表外键

准备部门表

CREATE TABLE dept (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '部门ID',
    name VARCHAR(50) NOT NULL COMMENT '部门名称'
) COMMENT '部门表';

INSERT INTO dept (id, name)
VALUES
    (1, '研发部'),
    (2, '市场部'),
    (3, '财务部'),
    (4, '销售部'),
    (5, '总经办');

准备员工表

CREATE TABLE emp2 (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '员工ID',
    name VARCHAR(50) NOT NULL COMMENT '姓名',
    age INT COMMENT '年龄',
    job VARCHAR(20) COMMENT '职位',
    salary INT COMMENT '薪资',
    entrydate DATE COMMENT '入职时间',
    managerid INT COMMENT '直属领导ID',
    dept_id INT COMMENT '部门ID'
) COMMENT '员工表';

此时，虽然 emp2 中存在 dept_id，但数据库还没有真正建立外键关系。

没有外键时，可能出现以下无效数据：

INSERT INTO emp2 (name, dept_id)
VALUES ('测试员工', 1000);

即使 dept 中没有编号为 1000 的部门，也可能插入成功。

创建表时添加外键

基本语法：

CREATE TABLE 子表名 (
    ...,
    [CONSTRAINT 外键名称]
    FOREIGN KEY (外键字段)
    REFERENCES 主表名 (主表字段)
);

示例：

CREATE TABLE employee (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    dept_id INT,
    CONSTRAINT fk_employee_dept
        FOREIGN KEY (dept_id)
        REFERENCES dept (id)
);

修改表时添加外键

ALTER TABLE emp2
ADD CONSTRAINT fk_emp2_dept
FOREIGN KEY (dept_id)
REFERENCES dept (id);

添加外键后：

  • 子表不能引用主表中不存在的数据。
  • 删除或修改主表记录时，需要考虑子表中的引用。

删除外键

ALTER TABLE emp2
DROP FOREIGN KEY fk_emp2_dept;

删除外键约束不等于删除外键字段，dept_id 字段仍然存在。

外键删除与更新行为

外键可以使用 ON DELETE 和 ON UPDATE 指定主表数据删除或更新时，子表如何处理。

行为说明
NO ACTION存在关联记录时，不允许删除或更新主表数据
RESTRICT存在关联记录时，不允许删除或更新主表数据
CASCADE主表删除或更新时，子表关联数据跟随删除或更新
SET NULL主表删除或更新时，将子表外键设置为 NULL
SET DEFAULT将子表外键设为默认值，但 InnoDB 不支持

在 MySQL 的 InnoDB 中，NO ACTION 与 RESTRICT 的效果基本一致。

添加级联更新和级联删除：

ALTER TABLE emp2
ADD CONSTRAINT fk_emp2_dept
FOREIGN KEY (dept_id)
REFERENCES dept (id)
ON UPDATE CASCADE
ON DELETE CASCADE;

当主表部门编号被修改时，子表的 dept_id 会同步修改。

当主表部门被删除时，该部门对应的员工记录也会被删除。

ON DELETE CASCADE 影响较大，实际项目中必须谨慎使用。

设置为空：

ALTER TABLE emp2
ADD CONSTRAINT fk_emp2_dept
FOREIGN KEY (dept_id)
REFERENCES dept (id)
ON UPDATE SET NULL
ON DELETE SET NULL;

使用 SET NULL 时，子表外键字段必须允许为 NULL：

dept_id INT NULL

外键约束注意事项

  1. 外键字段与被引用字段的数据类型应保持一致。
  2. 被引用字段通常是主键或唯一键。
  3. 子表外键值必须存在于主表中，或者为 NULL。
  4. 使用 SET NULL 时，外键字段必须允许为 NULL。
  5. 使用 CASCADE 前应确认级联影响范围。
  6. 外键可以保证数据完整性，但也会增加写入和维护成本。
  7. 删除外键约束时，需要使用外键名称，而不是字段名称。

约束小结

NOT NULL       不允许为空
UNIQUE         不允许重复
PRIMARY KEY    非空且唯一，用于标识一行数据
DEFAULT        未指定值时使用默认值
CHECK          限制数据必须满足条件
FOREIGN KEY    建立表之间的引用关系
AUTO_INCREMENT 自动生成递增编号