学习笔记：MYSQL(3）（常用函数和约束）-EW帮帮网

SQL中最基础、最常用的几类核心函数。以下按类别详细展开，包含函数语法、参数说明、多种示例及注意事项：

一、聚合函数（Aggregate Functions）

核心特点：对一组数据（列）进行计算，返回单一结果，常与GROUP BY配合，也可单独用于整表统计。

1. `COUNT()`：统计记录数量

语法：

COUNT(*)  -- 统计所有记录（包括NULL值、重复值）
COUNT(字段名)  -- 统计该字段非NULL值的记录数
COUNT(DISTINCT 字段名)  -- 统计该字段非NULL且去重后的记录数

示例（基于emp表）：

-- 1. 统计员工总人数（包括所有记录，即使字段有NULL）
SELECT COUNT(*) AS 总人数 FROM emp;  -- 结果：35（假设35条数据）

-- 2. 统计有年龄记录的员工数（排除age为NULL的记录）
SELECT COUNT(age) AS 有年龄记录的人数 FROM emp;  -- 结果：35（假设age无NULL）

-- 3. 统计不同性别的人数（去重统计）
SELECT COUNT(DISTINCT gender) AS 性别种类数 FROM emp;  -- 结果：2（男/女）

注意：COUNT(*)效率通常高于COUNT(字段名)，因为前者不判断字段是否为NULL。

2. `SUM()`：计算数值总和

语法：

SUM(字段名)  -- 计算该字段所有非NULL值的总和
SUM(DISTINCT 字段名)  -- 计算该字段去重后非NULL值的总和

示例：

-- 1. 计算所有员工年龄总和
SELECT SUM(age) AS 年龄总和 FROM emp;  -- 结果：假设总和为980

-- 2. 计算不同年龄的总和（去重后）
SELECT SUM(DISTINCT age) AS 去重年龄总和 FROM emp;  -- 结果：假设去重后年龄和为350

注意：仅适用于数值类型字段（int/float等），字符串或日期字段使用会报错。

3. `AVG()`：计算平均值

语法：

AVG(字段名)  -- 计算该字段非NULL值的平均值（总和/非NULL记录数）
AVG(DISTINCT 字段名)  -- 计算去重后非NULL值的平均值

示例：

-- 1. 计算所有员工的平均年龄
SELECT AVG(age) AS 平均年龄 FROM emp;  -- 结果：980/35=28

-- 2. 计算不同年龄的平均值（去重后）
SELECT AVG(DISTINCT age) AS 去重年龄平均值 FROM emp;  -- 结果：350/去重年龄数量

注意：计算时会自动排除NULL值，例如10条记录中2条age为NULL，则按8条计算。

4. `MAX()`/`MIN()`：最大值/最小值

语法：

MAX(字段名)  -- 返回该字段非NULL值中的最大值
MIN(字段名)  -- 返回该字段非NULL值中的最小值

适用字段类型：

数值型（int/float）：比较大小
字符串型（varchar）：按字符编码顺序（如'a' < 'b'）
日期型（date）：按时间先后（如'2020-01-01' < '2023-01-01'）

示例：

-- 1. 数值型：最大/最小年龄
SELECT MAX(age) AS 最大年龄, MIN(age) AS 最小年龄 FROM emp;  -- 结果：33, 23

-- 2. 日期型：最新/最早入职日期
SELECT MAX(entrydate) AS 最新入职, MIN(entrydate) AS 最早入职 FROM emp;  -- 结果：2022-05-12, 2017-05-02

-- 3. 字符串型：按姓名排序的最大/最小值（按拼音首字母）
SELECT MAX(name) AS 姓名最大值, MIN(name) AS 姓名最小值 FROM emp;  -- 结果：如'郑十', '白一'（假设存在）

二、字符串函数（String Functions）

核心特点：处理字符串类型字段（char/varchar），包括拼接、截取、转换等操作。

1. `CONCAT()`/`CONCAT_WS()`：字符串拼接

语法：

CONCAT(str1, str2, ...)  -- 拼接多个字符串，若任何一个为NULL，结果为NULL
CONCAT_WS(分隔符, str1, str2, ...)  -- 用指定分隔符拼接，忽略NULL值

示例：

-- 1. 拼接姓名和性别（无分隔符）
SELECT CONCAT(name, '(', gender, ')') AS 姓名性别 FROM emp;  -- 结果：'张三(男)'

-- 2. 用逗号分隔拼接多个字段（忽略NULL）
SELECT CONCAT_WS(',', name, workno, age) AS 员工信息 FROM emp;  -- 结果：'张三,W001,25'

注意：CONCAT_WS中“WS”是“With Separator”的缩写，适合拼接带分隔符的字符串（如地址、联系方式）。

2. `SUBSTRING()`（别名`SUBSTR()`）：截取子串

语法：

SUBSTRING(str, pos, len)  -- 从str的pos位置开始，截取len长度的子串
-- pos：起始位置（正数从左数，负数从右数）；len：可选，默认截取到末尾

示例：

-- 1. 截取身份证号前6位（地址码）
SELECT idcard, SUBSTRING(idcard, 1, 6) AS 地址码 FROM emp;  -- 结果：'1101011998...' → '110101'

-- 2. 截取姓名最后1个字符（从右数第1位）
SELECT name, SUBSTRING(name, -1) AS 姓名尾字 FROM emp;  -- 结果：'张三' → '三'

-- 3. 从第3位开始截取到末尾
SELECT SUBSTRING('HelloWorld', 3) AS 子串;  -- 结果：'lloWorld'

3. `TRIM()`/`LTRIM()`/`RTRIM()`：去除空格

语法：

TRIM(str)  -- 去除str首尾空格
LTRIM(str)  -- 去除str左侧（开头）空格
RTRIM(str)  -- 去除str右侧（结尾）空格

示例：

-- 处理带空格的姓名
SELECT 
  name AS 原姓名,
  TRIM(name) AS 去除首尾空格,
  LTRIM(name) AS 去除左空格,
  RTRIM(name) AS 去除右空格
FROM emp WHERE name LIKE ' %' OR name LIKE '% ';  -- 筛选带空格的姓名

4. `UPPER()`/`LOWER()`：大小写转换

语法：

UPPER(str)  -- 将字符串转为大写
LOWER(str)  -- 将字符串转为小写

示例：

-- 统一工号格式为大写
SELECT workno, UPPER(workno) AS 大写工号 FROM emp;  -- 结果：'w001' → 'W001'

-- 统一邮箱格式为小写（假设表中有email字段）
SELECT email, LOWER(email) AS 小写邮箱 FROM user;  -- 结果：'ZhangSan@XX.com' → 'zhangsan@xx.com'

注意：对中文、数字无效，仅影响英文字母。

三、日期函数（Date Functions）

核心特点：处理日期时间类型（date/datetime/timestamp），用于提取日期部分、计算时间差等。

1. `NOW()`/`CURDATE()`/`CURTIME()`：获取当前时间

语法：

NOW()  -- 返回当前日期时间（格式：YYYY-MM-DD HH:MM:SS）
CURDATE()  -- 返回当前日期（格式：YYYY-MM-DD）
CURTIME()  -- 返回当前时间（格式：HH:MM:SS）

示例：

SELECT 
  NOW() AS 当前日期时间,
  CURDATE() AS 当前日期,
  CURTIME() AS 当前时间;
-- 结果：
-- 2023-10-01 15:30:45 | 2023-10-01 | 15:30:45

2. `YEAR()`/`MONTH()`/`DAY()`：提取日期部分

语法：

YEAR(date)  -- 提取年份
MONTH(date)  -- 提取月份（1-12）
DAY(date)  -- 提取日（1-31）
HOUR(time)  -- 提取小时（0-23）
MINUTE(time)  -- 提取分钟（0-59）
SECOND(time)  -- 提取秒（0-59）

示例：

-- 分析员工入职年份分布
SELECT 
  entrydate AS 入职日期,
  YEAR(entrydate) AS 入职年份,
  MONTH(entrydate) AS 入职月份
FROM emp;

3. `DATEDIFF()`：计算日期差

语法：

DATEDIFF(date1, date2)  -- 返回date1 - date2的天数（正数表示date1晚于date2）

示例：

-- 计算员工入职至今的天数
SELECT 
  name,
  entrydate AS 入职日期,
  DATEDIFF(CURDATE(), entrydate) AS 入职天数
FROM emp;

4. `DATE_ADD()`/`DATE_SUB()`：日期增减

语法：

DATE_ADD(date, INTERVAL 数值 单位)  -- 日期加指定间隔
DATE_SUB(date, INTERVAL 数值 单位)  -- 日期减指定间隔
-- 单位：DAY（天）、MONTH（月）、YEAR（年）、HOUR（小时）等

示例：

-- 计算员工入职3年后的日期
SELECT 
  name,
  entrydate AS 入职日期,
  DATE_ADD(entrydate, INTERVAL 3 YEAR) AS 入职3年后
FROM emp;

-- 计算10天前的日期
SELECT DATE_SUB(CURDATE(), INTERVAL 10 DAY) AS 10天前;  -- 结果：2023-09-21（假设当前是2023-10-01）

四、条件函数（Conditional Functions）

核心特点：根据条件返回不同结果，类似编程语言中的if-else逻辑。

1. `IF(condition, val_true, val_false)`：简单条件判断

语法：

IF(条件表达式, 条件为真时返回值, 条件为假时返回值)

示例：

-- 判断员工年龄是否大于30
SELECT 
  name,
  age,
  IF(age > 30, '大龄员工', '青年员工') AS 年龄分类
FROM emp;

-- 判断性别并转换为中文
SELECT name, IF(gender = '男', '男性', '女性') AS 性别 FROM emp;

2. `CASE`：多条件分支判断

语法1：简单等值判断（类似switch-case）

CASE 字段名
  WHEN 值1 THEN 结果1
  WHEN 值2 THEN 结果2
  ...
  ELSE 默认结果  -- 可选，所有条件不满足时返回
END

语法2：复杂条件判断（类似if-else if-else）

CASE
  WHEN 条件1 THEN 结果1
  WHEN 条件2 THEN 结果2
  ...
  ELSE 默认结果  -- 可选
END

示例：

-- 示例1：按性别分组统计（等值判断）
SELECT 
  CASE gender
    WHEN '男' THEN '男性员工'
    WHEN '女' THEN '女性员工'
    ELSE '未知性别'
  END AS 性别分组,
  COUNT(*) AS 人数
FROM emp
GROUP BY gender;

-- 示例2：按年龄范围分组（复杂条件）
SELECT 
  name,
  age,
  CASE
    WHEN age < 25 THEN '25岁以下'
    WHEN age BETWEEN 25 AND 30 THEN '25-30岁'
    WHEN age > 30 THEN '30岁以上'
    ELSE '年龄未知'
  END AS 年龄范围
FROM emp;

五、数值函数（Numeric Functions）

核心特点：处理数值类型数据，包括取整、四舍五入、计算绝对值等。

1. `ROUND()`/`CEIL()`/`FLOOR()`：取整相关

语法：

ROUND(num, 小数位数)  -- 四舍五入（默认保留0位小数）
CEIL(num)  -- 向上取整（返回≥num的最小整数）
FLOOR(num)  -- 向下取整（返回≤num的最大整数）

示例：

SELECT 
  ROUND(3.1415, 2) AS 四舍五入2位,  -- 结果：3.14
  ROUND(3.5) AS 四舍五入整数,       -- 结果：4
  CEIL(3.2) AS 向上取整,            -- 结果：4
  FLOOR(3.8) AS 向下取整;           -- 结果：3

2. `ABS()`/`MOD()`：绝对值与取模

语法：

ABS(num)  -- 取绝对值
MOD(num1, num2)  -- 取模（num1除以num2的余数，同 num1 % num2）

示例：

SELECT 
  ABS(-100) AS 绝对值,  -- 结果：100
  MOD(7, 3) AS 取模,    -- 结果：1（7÷3=2余1）
  MOD(10, 2) AS 偶数判断;  -- 结果：0（10是偶数）

总结

这些基础函数是SQL查询的“积木”，灵活组合可实现复杂的数据处理需求：

聚合函数 + GROUP BY：用于统计分析（如各部门人数、平均工资）；
字符串函数：处理格式清洗（如手机号脱敏、姓名拼接）；
日期函数：计算时间差（如用户活跃天数、合同到期提醒）；
条件函数：实现数据分类（如等级划分、状态转换）。

实际使用时，可通过SELECT 函数(参数)直接测试函数效果（无需从表中查询），例如：

SELECT CONCAT('Hello', 'SQL'), DATEDIFF('2023-12-31', CURDATE());

----------------------------------------------分割线---------------------------------------------------------
在 SQL 中，约束（Constraint） 是用于限制表中数据的规则，确保数据的完整性、一致性和有效性。通过约束，可以避免无效数据、重复数据或不符合业务规则的数据进入表中。

常见的约束类型有以下几种：

1. 主键约束（PRIMARY KEY）

作用：唯一标识表中的每条记录，确保记录不重复，且不为 NULL。
特点：
- 一个表只能有一个主键（可以是单个字段或多个字段组合，即联合主键）。
- 主键字段的值必须唯一且非空。

示例：

-- 单字段主键
CREATE TABLE emp (
    id INT PRIMARY KEY,  -- id 作为主键
    name VARCHAR(50)
);

-- 联合主键（多字段组合唯一）
CREATE TABLE student_course (
    student_id INT,
    course_id INT,
    score INT,
    PRIMARY KEY (student_id, course_id)  -- 学生ID+课程ID组合唯一
);

2. 非空约束（NOT NULL）

作用：限制字段的值不能为 NULL（必须填写）。

示例：

CREATE TABLE emp (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,  -- 姓名不能为空
    age INT
);

插入数据时，如果 name 字段为空，会报错：Column 'name' cannot be null。

3. 唯一约束（UNIQUE）

作用：确保字段的值唯一（可以为 NULL，但 NULL 可以重复）。
与主键的区别：
- 主键：唯一且非空，一个表只能有一个。
- 唯一约束：唯一但可以为 NULL，一个表可以有多个。

示例：

CREATE TABLE emp (
    id INT PRIMARY KEY,
    workno VARCHAR(10) UNIQUE,  -- 工号唯一（允许为NULL，但实际业务中通常也会加NOT NULL）
    name VARCHAR(50) NOT NULL
);

4. 外键约束（FOREIGN KEY）

作用：建立两个表之间的关联关系，确保从表（子表）的字段值必须在主表（父表）的关联字段中存在。
术语：
- 主表：被引用的表（如 dept 部门表）。
- 从表：引用主表的表（如 emp 员工表）。
- 外键：从表中用于关联主表的字段（如 emp.dept_id）。

示例：

-- 主表：部门表
CREATE TABLE dept (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL
);

-- 从表：员工表（含外键关联部门表）
CREATE TABLE emp (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    dept_id INT,
    -- 外键约束：emp.dept_id 必须存在于 dept.dept_id 中
    FOREIGN KEY (dept_id) REFERENCES dept(dept_id)
);

效果：
- 无法在 emp 中插入 dept_id 不存在于 dept 表的值。
- 无法删除 dept 表中已被 emp 表引用的 dept_id 记录（除非配置了级联删除）。

5. 检查约束（CHECK）

作用：限制字段的值必须满足指定的条件（如年龄范围、性别只能是“男”或“女”）。

示例：

CREATE TABLE emp (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT CHECK (age >= 18 AND age <= 60),  -- 年龄必须在18-60之间
    gender VARCHAR(2) CHECK (gender IN ('男', '女'))  -- 性别只能是男或女
);

插入数据时，如果年龄为 17 或性别为“未知”，会报错。

6. 默认约束（DEFAULT）

作用：为字段设置默认值，当插入数据时未指定该字段的值，自动使用默认值。

示例：

CREATE TABLE emp (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    status VARCHAR(10) DEFAULT '在职',  -- 默认状态为“在职”
    entrydate DATE DEFAULT (CURDATE())  -- 默认入职日期为当前日期
);

-- 插入时不指定 status 和 entrydate，会自动使用默认值
INSERT INTO emp (id, name) VALUES (1, '张三');

约束的添加与删除

1. 新增表时添加约束（推荐）

如上述示例，在 CREATE TABLE 语句中直接定义约束。

2. 对已有表添加约束

-- 给 emp 表的 name 字段添加非空约束
ALTER TABLE emp MODIFY name VARCHAR(50) NOT NULL;

-- 给 emp 表的 workno 字段添加唯一约束
ALTER TABLE emp ADD UNIQUE (workno);

-- 给 emp 表添加外键约束
ALTER TABLE emp ADD FOREIGN KEY (dept_id) REFERENCES dept(dept_id);

3. 删除约束

-- 删除唯一约束（需指定约束名，可通过 SHOW INDEX FROM 表名 查看）
ALTER TABLE emp DROP INDEX workno;

-- 删除外键约束（需指定外键名）
ALTER TABLE emp DROP FOREIGN KEY emp_ibfk_1;  -- 外键名通常为系统自动生成

-- 删除非空约束（改为允许 NULL）
ALTER TABLE emp MODIFY name VARCHAR(50) NULL;

总结

约束是保证数据质量的重要手段，不同约束的适用场景：

主键：唯一标识记录（如 id）。
非空：确保必填字段（如 name）。
唯一：确保字段值不重复（如 workno、email）。
外键：维护表之间的关联关系（如员工与部门）。
检查：限制字段值的范围（如年龄、性别）。
默认：简化常用值的插入（如默认状态、当前日期）。

合理使用约束可以减少程序中的数据校验逻辑，降低数据错误的风险。

学习笔记：MYSQL(3）（常用函数和约束）

一、聚合函数（Aggregate Functions）

1. `COUNT()`：统计记录数量

2. `SUM()`：计算数值总和

3. `AVG()`：计算平均值

4. `MAX()`/`MIN()`：最大值/最小值

二、字符串函数（String Functions）

1. `CONCAT()`/`CONCAT_WS()`：字符串拼接

2. `SUBSTRING()`（别名`SUBSTR()`）：截取子串

3. `TRIM()`/`LTRIM()`/`RTRIM()`：去除空格

4. `UPPER()`/`LOWER()`：大小写转换

三、日期函数（Date Functions）

1. `NOW()`/`CURDATE()`/`CURTIME()`：获取当前时间

2. `YEAR()`/`MONTH()`/`DAY()`：提取日期部分

3. `DATEDIFF()`：计算日期差

4. `DATE_ADD()`/`DATE_SUB()`：日期增减

四、条件函数（Conditional Functions）

1. `IF(condition, val_true, val_false)`：简单条件判断

2. `CASE`：多条件分支判断

五、数值函数（Numeric Functions）

1. `ROUND()`/`CEIL()`/`FLOOR()`：取整相关

2. `ABS()`/`MOD()`：绝对值与取模

总结

1. 主键约束（PRIMARY KEY）

2. 非空约束（NOT NULL）

3. 唯一约束（UNIQUE）

4. 外键约束（FOREIGN KEY）

5. 检查约束（CHECK）

6. 默认约束（DEFAULT）

约束的添加与删除

1. 新增表时添加约束（推荐）

2. 对已有表添加约束

3. 删除约束

总结

网站公告

今日签到

热门文章

最新发布

学习笔记：MYSQL(3）（常用函数和约束）

一、聚合函数（Aggregate Functions）

1. COUNT()：统计记录数量

2. SUM()：计算数值总和

3. AVG()：计算平均值

4. MAX()/MIN()：最大值/最小值

二、字符串函数（String Functions）

1. CONCAT()/CONCAT_WS()：字符串拼接

2. SUBSTRING()（别名SUBSTR()）：截取子串

3. TRIM()/LTRIM()/RTRIM()：去除空格

4. UPPER()/LOWER()：大小写转换

三、日期函数（Date Functions）

1. NOW()/CURDATE()/CURTIME()：获取当前时间

2. YEAR()/MONTH()/DAY()：提取日期部分

3. DATEDIFF()：计算日期差

4. DATE_ADD()/DATE_SUB()：日期增减

四、条件函数（Conditional Functions）

1. IF(condition, val_true, val_false)：简单条件判断

2. CASE：多条件分支判断

五、数值函数（Numeric Functions）

1. ROUND()/CEIL()/FLOOR()：取整相关

2. ABS()/MOD()：绝对值与取模

总结

1. 主键约束（PRIMARY KEY）

2. 非空约束（NOT NULL）

3. 唯一约束（UNIQUE）

4. 外键约束（FOREIGN KEY）

5. 检查约束（CHECK）

6. 默认约束（DEFAULT）

约束的添加与删除

1. 新增表时添加约束（推荐）

2. 对已有表添加约束

3. 删除约束

总结

网站公告

今日签到

热门文章

最新发布

1. `COUNT()`：统计记录数量

2. `SUM()`：计算数值总和

3. `AVG()`：计算平均值

4. `MAX()`/`MIN()`：最大值/最小值

1. `CONCAT()`/`CONCAT_WS()`：字符串拼接

2. `SUBSTRING()`（别名`SUBSTR()`）：截取子串

3. `TRIM()`/`LTRIM()`/`RTRIM()`：去除空格

4. `UPPER()`/`LOWER()`：大小写转换

1. `NOW()`/`CURDATE()`/`CURTIME()`：获取当前时间

2. `YEAR()`/`MONTH()`/`DAY()`：提取日期部分

3. `DATEDIFF()`：计算日期差

4. `DATE_ADD()`/`DATE_SUB()`：日期增减

1. `IF(condition, val_true, val_false)`：简单条件判断

2. `CASE`：多条件分支判断

1. `ROUND()`/`CEIL()`/`FLOOR()`：取整相关

2. `ABS()`/`MOD()`：绝对值与取模