mysql数据库应用②
函数
一、字符串函数
| 函数语法 | 功能说明 | 示例 |
|---|---|---|
CONCAT(str1, str2, ...) | 拼接多个字符串；任意参数为NULL则整体结果为NULL | CONCAT(name, '-', age)→张三-20 |
CONCAT_WS(分隔符, str1, str2, ...) | 用指定分隔符拼接字符串；自动忽略NULL值参数 | CONCAT_WS(',', name, gender)→张三,男 |
SUBSTRING(str, 起始位置, 截取长度) | 从指定位置截取子串，位置从1开始计数 | SUBSTRING('张三三', 1, 2)→张三 |
LENGTH(str) | 返回字符串的字节长度（utf8下1个中文占3字节） | LENGTH('张三')→6 |
CHAR_LENGTH(str) | 返回字符串的字符个数 | CHAR_LENGTH('张三')→2 |
REPLACE(str, 旧内容, 新内容) | 替换字符串中指定的子串 | REPLACE('一班', '班', '级')→一级 |
UPPER(str)/LOWER(str) | 英文字母全部转大写 / 转小写 | UPPER('hello')→HELLO |
TRIM(str) | 去除字符串首尾的空格，中间空格保留 | TRIM(' 张三 ')→张三 |
INSTR(str, 子串) | 返回子串第一次出现的位置，找不到返回0 | INSTR('张三三', '三')→2 |
LPAD(str, 总长度, 填充字符) | 左侧填充字符，使字符串达到固定长度；超长则截断 | LPAD('123', 6, '0')→000123 |
RPAD(str, 总长度, 填充字符) | 右侧填充字符，使字符串达到固定长度；超长则截断 | RPAD('姓名', 10, '*')→姓名******** |
二、日期时间函数
| 函数语法 | 功能说明 | 示例 |
|---|---|---|
NOW() | 返回当前系统日期+时间（格式：YYYY-MM-DD HH:MM:SS） | NOW()→2026-06-16 15:30:00 |
CURDATE()/CURTIME() | 只返回当前日期 / 只返回当前时间 | CURDATE()→2026-06-16 |
DATE_FORMAT(日期, 格式) | 按指定格式格式化日期，常用%Y年、%m月、%d日 | DATE_FORMAT(NOW(), '%Y年%m月%d日')→2026年06月16日 |
DATEDIFF(日期1, 日期2) | 计算两个日期相差的天数（日期1 - 日期2） | DATEDIFF('2026-06-16','2026-06-10')→6 |
TIMESTAMPDIFF(单位, 开始日期, 结束日期) | 按指定单位计算时间差，单位：YEAR/MONTH/DAY/HOUR | TIMESTAMPDIFF(YEAR, birthday, NOW())→ 计算年龄 |
DATE_ADD(日期, INTERVAL 数值 单位) | 给日期增加指定时长 | DATE_ADD(NOW(), INTERVAL 1 YEAR)→ 加1年 |
DATE_SUB(日期, INTERVAL 数值 单位) | 给日期减少指定时长 | DATE_SUB(NOW(), INTERVAL 3 MONTH)→ 减3个月 |
YEAR(date)/MONTH(date)/DAY(date) | 分别提取日期中的年、月、日数值 | YEAR('2026-06-16')→2026 |
三、数值函数
| 函数语法 | 功能说明 | 示例 |
|---|---|---|
ROUND(数值, 保留小数位) | 四舍五入保留指定小数位 | ROUND(3.14159, 2)→3.14 |
FLOOR(数值) | 向下取整（取小于等于该值的最大整数） | FLOOR(3.9)→3 |
CEIL(数值) | 向上取整（取大于等于该值的最小整数） | CEIL(3.1)→4 |
TRUNCATE(数值, 保留小数位) | 直接截断小数位，不做四舍五入 | TRUNCATE(3.999, 2)→3.99 |
ABS(数值) | 返回数值的绝对值 | ABS(-10)→10 |
MOD(被除数, 除数) | 取余数 | MOD(10, 3)→1 |
RAND() | 返回0~1之间的随机浮点数 | FLOOR(RAND() * 100)→ 0~100随机整数 |
四、流程控制函数
| 函数语法 | 功能说明 | 示例 |
|---|---|---|
IF(条件, 满足值, 不满足值) | 单条件判断，等价于三元表达式 | IF(score >= 60, '及格', '不及格') |
IFNULL(表达式, 替代值) | 表达式为NULL时返回替代值，否则返回原值，空值处理最常用 | IFNULL(class_id, '未分配') |
CASE WHEN 条件1 THEN 结果1 ... ELSE 默认值 END | 多条件分支判断，支持任意复杂条件 | CASE WHEN score>=90 THEN '优秀' WHEN score>=60 THEN '及格' ELSE '不及格' END |
五、聚合函数（配合GROUP BY使用）
| 函数语法 | 功能说明 | 注意事项 |
|---|---|---|
COUNT(*) | 统计结果集总行数 | 包含NULL行，统计人数最常用 |
COUNT(字段名) | 统计该字段非空的行数 | 自动排除NULL值 |
SUM(字段) | 对数值字段求和 | 忽略NULL，非数值结果为0 |
AVG(字段) | 对数值字段求平均值 | 自动忽略NULL值 |
MAX(字段) | 求字段的最大值 | 支持数值、日期、字符串 |
MIN(字段) | 求字段的最小值 | 支持数值、日期、字符串 |
GROUP_CONCAT(字段) | 分组后将组内字段值拼接成一个字符串 | 可指定排序和分隔符 |
六、窗口函数（MySQL 8.0+ 支持）
| 函数语法 | 功能说明 | 排名特点（同分场景） |
|---|---|---|
ROW_NUMBER() OVER(分区+排序) | 连续排名 | 同分也分配不同名次，如 1,2,3,4 |
RANK() OVER(分区+排序) | 跳跃排名 | 同分同名次，后续跳号，如 1,1,3,4 |
DENSE_RANK() OVER(分区+排序) | 密集排名 | 同分同名次，后续不跳号，如 1,1,2,3 |
SUM(字段) OVER(排序) | 累计求和 | 按排序顺序逐行累加 |
约束
一、约束总览
MySQL 日常开发最常用 6 种约束，核心功能如下：
| 约束名称 | 关键字 | 核心作用 |
|---|---|---|
| 主键约束 | PRIMARY KEY | 唯一标识一行数据，特性是非空+唯一，一张表只能有一个主键 |
| 非空约束 | NOT NULL | 强制该字段必须填写，不能为 NULL |
| 唯一约束 | UNIQUE | 该字段所有行的值不能重复，允许存在 NULL |
| 默认约束 | DEFAULT | 不主动赋值时，自动用默认值填充字段 |
| 检查约束 | CHECK | 自定义字段取值范围规则（MySQL 8.0.16 后正式生效） |
| 外键约束 | FOREIGN KEY | 关联两张表，保证跨表数据的参照一致性 |
补充：列级约束 vs 表级约束
- 列级约束：直接写在字段定义的后方，跟在数据类型后面，大部分约束都支持列级写法
- 表级约束：所有字段定义完成后单独声明，复合主键、复合唯一键必须用表级写法
二、逐个约束详解
1. 主键约束PRIMARY KEY
核心作用
主键是一张表的「身份证号」，用来唯一标识每一行数据，两大核心特性：非空 + 唯一。
语法与示例
列级写法（单字段主键，最常用）
CREATETABLEstudent(idINTPRIMARYKEY,-- id 设为主键nameVARCHAR(20));表级写法（复合主键）
多个字段联合组成主键，两个字段加起来唯一，单个字段可重复：
CREATETABLEstudent(class_idINT,student_noINT,nameVARCHAR(20),PRIMARYKEY(class_id,student_no)-- 班级+学号联合做主键);自增主键（开发标准写法）
主键通常配合AUTO_INCREMENT自增属性使用，插入数据时无需手动赋值，数据库自动生成递增主键：
CREATETABLEstudent(idINTPRIMARYKEYAUTO_INCREMENT,-- 主键自增，默认从1开始，每次+1nameVARCHAR(20));关键注意事项
- 一张表有且只有一个主键约束，但主键可以包含多个字段（复合主键）
- 主键字段不允许为 NULL，不允许重复值
- 主键默认自动创建聚簇索引，按主键查询速度最快
- 删除数据后，自增计数器不会回退
修改表添加/删除主键
-- 给已有表添加主键ALTERTABLEstudentADDPRIMARYKEY(id);-- 删除主键ALTERTABLEstudentDROPPRIMARYKEY;2. 非空约束NOT NULL
核心作用
强制该字段必须填写值，不能为 NULL。
语法与示例
只能用列级写法，直接跟在字段类型后：
CREATETABLEstudent(idINTPRIMARYKEY,nameVARCHAR(20)NOTNULL,-- 姓名字段不能为空phoneVARCHAR(11));关键注意事项
- 空字符串
''不是 NULL，属于合法值，NOT NULL不会限制空字符串 - 没有表级写法，只能定义在字段后方
修改表添加/删除非空约束
-- 添加非空约束（修改字段类型同时加约束）ALTERTABLEstudentMODIFYnameVARCHAR(20)NOTNULL;-- 移除非空约束ALTERTABLEstudentMODIFYnameVARCHAR(20)NULL;3. 唯一约束UNIQUE
核心作用
保证该字段的所有值不重复，常用于手机号、身份证号、工号等业务唯一字段。
和主键的核心区别：唯一约束允许为 NULL，且一张表可以有多个唯一约束。
语法与示例
列级写法（单字段唯一）
CREATETABLEstudent(idINTPRIMARYKEY,phoneVARCHAR(11)UNIQUE-- 手机号全局唯一，不能重复);表级写法（复合唯一键）
多个字段联合唯一，比如同一个班级内学号不能重复：
CREATETABLEstudent(idINTPRIMARYKEY,class_idINT,student_noVARCHAR(20),UNIQUE(class_id,student_no));关键注意事项
- 唯一约束允许字段为 NULL，且可以存在多个 NULL（因为 NULL 不等于任何值，包括自己）
- 一张表可以定义多个唯一约束
- 唯一约束默认自动创建唯一索引
修改表添加/删除唯一约束
-- 添加唯一约束ALTERTABLEstudentADDUNIQUE(phone);-- 删除唯一约束（本质是删除对应的唯一索引）ALTERTABLEstudentDROPINDEXphone;4. 默认约束DEFAULT
核心作用
插入数据时，如果没有给该字段显式赋值，自动用默认值填充。
语法与示例
只能用列级写法：
CREATETABLEstudent(idINTPRIMARYKEY,nameVARCHAR(20)NOTNULL,ageINTDEFAULT18,-- 年龄默认18岁statusTINYINTDEFAULT1,-- 状态默认1（在职/正常）create_timeDATETIMEDEFAULTNOW()-- 创建时间默认当前时间);关键注意事项
- 只有完全不写该字段时才会触发默认值；如果显式写
NULL，会存储 NULL，不会用默认值 - 可以配合函数使用，比如默认时间
DEFAULT NOW()
修改表添加/删除默认约束
-- 添加默认值ALTERTABLEstudentALTERageSETDEFAULT18;-- 删除默认值ALTERTABLEstudentALTERageDROPDEFAULT;5. 检查约束CHECK
核心作用
自定义字段的取值规则，比如年龄必须大于0、分数必须在 0-100 之间。
⚠️ 重要说明：MySQL 8.0.16 版本之前，CHECK语法可以写但不会生效；8.0.16 及之后版本才正式支持检查约束。
语法与示例
列级写法
CREATETABLEstudent(idINTPRIMARYKEY,ageINTCHECK(age>0ANDage<120),-- 年龄必须在 0-120 之间scoreINTCHECK(scoreBETWEEN0AND100)-- 分数必须在 0-100 之间);表级写法（命名约束，方便管理）
CREATETABLEstudent(idINTPRIMARYKEY,ageINT,scoreINT,CONSTRAINTck_ageCHECK(age>0ANDage<120),CONSTRAINTck_scoreCHECK(score>=0ANDscore<=100));修改表添加/删除检查约束
-- 添加检查约束ALTERTABLEstudentADDCONSTRAINTck_ageCHECK(age>0);-- 删除检查约束ALTERTABLEstudentDROPCHECKck_age;6. 外键约束FOREIGN KEY
核心作用
用来建立两张表的关联关系，保证「从表」的关联字段值，必须在「主表」的主键/唯一键中存在，防止出现无效的关联数据。
- 主表（父表）：被关联的表，提供主键值（比如班级表）
- 从表（子表）：添加外键的表，引用主表的主键（比如学生表）
语法与示例
-- 主表：班级表CREATETABLEclass(idINTPRIMARYKEY,class_nameVARCHAR(20));-- 从表：学生表，添加外键关联班级表CREATETABLEstudent(idINTPRIMARYKEY,nameVARCHAR(20),class_idINT,-- 外键约束：student表的class_id 关联 class表的idCONSTRAINTfk_student_classFOREIGNKEY(class_id)REFERENCESclass(id));加了外键后，不能给学生分配一个班级表中不存在的 class_id。
外键的删除/更新行为
当主表的数据被删除/修改时，可以设置从表关联数据的处理策略：
| 行为 | 说明 |
|---|---|
RESTRICT / NO ACTION | 默认行为：主表数据被从表引用时，禁止删除/修改主表数据 |
CASCADE | 级联：主表删除/修改数据时，从表关联数据同步删除/修改 |
SET NULL | 置空：主表删除数据时，从表关联字段设为 NULL（前提是字段允许为 NULL） |
示例：级联删除 + 级联更新
CREATETABLEstudent(idINTPRIMARYKEY,nameVARCHAR(20),class_idINT,FOREIGNKEY(class_id)REFERENCESclass(id)ONDELETECASCADEONUPDATECASCADE);- 删除班级时，该班级的所有学生同步被删除
- 修改班级 id 时，学生的 class_id 同步修改
关键注意事项
- 从表的外键字段，类型必须和主表的主键/唯一键字段完全一致
- 主表被引用的字段必须是主键或唯一键
- 一张表可以有多个外键
- 高并发生产环境通常不使用物理外键，会影响性能、增加表耦合，数据一致性由业务代码保证
修改表添加/删除外键
-- 添加外键ALTERTABLEstudentADDCONSTRAINTfk_student_classFOREIGNKEY(class_id)REFERENCESclass(id);-- 删除外键ALTERTABLEstudentDROPFOREIGNKEYfk_student_class;三、核心易混点对比
主键 vs 唯一约束
| 对比项 | 主键PRIMARY KEY | 唯一约束UNIQUE |
|---|---|---|
| 非空要求 | 绝对不允许为 NULL | 允许为 NULL，且可以有多个 NULL |
| 单表数量 | 只能有 1 个 | 可以有多个 |
| 索引类型 | 默认创建聚簇索引 | 默认创建唯一非聚簇索引 |
| 核心作用 | 唯一标识一行数据 | 保证业务字段值不重复 |
NOT NULLvsDEFAULT
NOT NULL：强制字段不能为 NULL，必须有值DEFAULT：没赋值的时候用默认值，主动写 NULL 仍会存 NULL- 最佳实践：两者配合使用
age INT NOT NULL DEFAULT 18，既不能为空，又有默认兜底，数据最稳定
四、完整建表约束示例
综合所有约束，一张设计规范的员工表：
CREATETABLEemp(idINTPRIMARYKEYAUTO_INCREMENTCOMMENT'员工ID，主键自增',emp_noVARCHAR(20)NOTNULLUNIQUECOMMENT'工号，非空且全局唯一',nameVARCHAR(20)NOTNULLCOMMENT'姓名，非空',ageTINYINTUNSIGNEDCHECK(age>18ANDage<65)COMMENT'年龄，范围校验',dept_idINTCOMMENT'部门ID',statusTINYINTDEFAULT1COMMENT'在职状态，默认1在职',create_timeDATETIMEDEFAULTNOW()COMMENT'创建时间，默认当前时间',-- 外键关联部门表，删除部门时员工部门ID置空FOREIGNKEY(dept_id)REFERENCESdept(id)ONDELETESETNULL)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;