1.1 算术运算符及应用详解

算术运算符是MySQL中最基础的运算工具，常用于数据计算、字段值处理等场景。

• 加法（+）
  用于计算两个值的和，支持常量、字段与常量、字段与字段之间的运算。
  示例：

-- 常量相加
select 1+1; -- 结果为2

实际业务中，常需对表中字段进行求和运算。先创建测试表并插入数据：

-- 创建测试表
create table count_test(a int, b int);

-- 插入测试数据
insert into count_test values (5,2),(4,3);

基于测试表的加法运算：

-- 计算字段a和字段b的和
select a + b from count_test; -- 结果为7、7

-- 计算字段a与常量3的和
select a + 3 from count_test; -- 结果为8、7

• 减法（-）
  用于计算两个值的差值，用法与加法类似。
  示例：

-- 常量相减
select 2-1; -- 结果为1

-- 计算字段a与字段b的差值
select a - b from count_test; -- 结果为3、1

• 乘法（*）
  用于计算两个值的乘积。
  示例：

-- 常量相乘
select 2*2; -- 结果为4

-- 计算字段a与字段b的乘积
select a * b from count_test; -- 结果为10、12

• 除法（/）
  用于计算两个值的商（注意：MySQL中除法结果为浮点数）。
  示例：

-- 常量相除
select 4/2; -- 结果为2.0

-- 计算字段a与字段b的商
select a / b from count_test; -- 结果为2.5、1.333...

• 取余（% 或 MOD）
  用于计算两个值相除后的余数。
  示例：

-- 常量取余
select 4%3; -- 结果为1

-- 计算字段a与字段b的余数
select a % b from count_test; -- 结果为1、1

1.2 比较运算符的应用

比较运算符用于判断两个值的关系，返回结果为布尔值（1表示真，0表示假），常用于查询条件过滤。

1.2.1 常用比较运算符

• =：判断两个值是否相等
• <> 或 !=：判断两个值是否不相等
• >：判断左侧值是否大于右侧值
• <：判断左侧值是否小于右侧值
• >=：判断左侧值是否大于等于右侧值
• <=：判断左侧值是否小于等于右侧值
• BETWEEN ... AND ...：判断值是否在指定范围内（包含边界）
• IN：判断值是否属于指定集合中的某一个
• IS NULL：判断值是否为NULL
• IS NOT NULL：判断值是否不为NULL

1.2.2 实验演示

创建测试表用于验证比较运算符：

create table compare_test(
  a int,
  b int
) engine=innodb;

-- 插入测试数据
insert into compare_test values (1,1),(3,2);

• 等于（=）
  判断两个字段值是否相等：

select a=b from compare_test; -- 结果为1（真）、0（假）

• 不等于（<> 或 !=）
  判断两个字段值是否不相等：

select a<>b from compare_test; -- 结果为0（假）、1（真）

• 大于（>）
  判断字段a是否大于字段b：

select a>b from compare_test; -- 结果为0（假）、1（真）

• 范围判断（BETWEEN … AND …）
  判断字段a的值是否在2到4之间：

select a between 2 and 4 from compare_test; -- 结果为0（假）、1（真）

• 集合判断（IN）
  判断字段a的值是否属于{1,5,9}：

select a in (1,5,9) from compare_test; -- 结果为1（真）、0（假）

• 空值判断（IS NULL / IS NOT NULL）
  先创建含NULL值的测试表：

create table null_test(a varchar(10));
insert into null_test values ('aaa'), (null);

判断字段是否为空或非空：

select a is null from null_test; -- 结果为0（假）、1（真）
select a is not null from null_test; -- 结果为1（真）、0（假）

1.3 逻辑运算符的应用

逻辑运算符用于组合多个条件，判断结果的真假关系，常用于复杂查询的条件过滤。

1.3.1 常用逻辑运算符

• 逻辑非（! 或 NOT）：对单一条件取反（真→假，假→真）
• 逻辑与（AND）：需所有条件同时为真，结果才为真
• 逻辑或（OR）：只要任一条件为真，结果就为真
• 逻辑异或（XOR）：两个条件一真一假时结果为真，否则为假

1.3.2 实验演示

创建测试表用于验证逻辑运算符：

create table logic_test(
  a int,
  b int
) engine=innodb;

-- 插入测试数据
insert into logic_test values (2,2), (1,2);

• 逻辑非（! 或 NOT）
  对条件取反（真→0，假→1）：

-- 判断1+2是否不等于2（结果为真，返回1）
select (1 + 2) != 2; -- 结果为1

-- 判断1+2是否不等于3（结果为假，返回0）
select (1 + 2) != 3; -- 结果为0

-- 判断字段a与b是否不相等
select a != b from logic_test; -- 结果为0、1

-- 过滤出a与b不相等的行
select * from logic_test where a != b; -- 返回第2行数据

• 逻辑与（AND）
  需两个条件同时为真：

-- 1=1为真，2=1为假，结果为0
select 1=1 and 2=1; -- 结果为0

-- 过滤出a=1且b=2的行
select * from logic_test where a=1 and b=2; -- 返回第2行数据

• 逻辑或（OR）
  任一条件为真则结果为真：

-- 1=1为真，2=1为假，结果为1
select 1=1 or 2=1; -- 结果为1

-- 过滤出a=1或b=2的行
select * from logic_test where a=1 or b=2; -- 返回两行数据

• 逻辑异或（XOR）
  条件一真一假时结果为真：

-- 1=1为真，2=1为假，结果为1
select 1=1 xor 2=1; -- 结果为1

-- 1=1为真，2=2为真，结果为0
select 1=1 xor 2=2; -- 结果为0

-- 1=2为假，2=3为假，结果为0
select 1=2 xor 2=3; -- 结果为0

1.4 位运算符的应用

位运算符用于对数值的二进制位进行运算，适用于底层数据处理或特定场景（如权限控制）。

1.4.1 常用位运算符

• 位与（&）：两个二进制位均为1时，结果位为1，否则为0
  例：5（0101） & 3（0011） = 1（0001）
• 位或（|）：两个二进制位至少有一个为1时，结果位为1
  例：5（0101） | 3（0011） = 7（0111）
• 位异或（^）：两个二进制位不同时，结果位为1，否则为0
  例：5（0101） ^ 3（0011） = 6（0110）
• 位取反（~）：对二进制位取反（1→0，0→1），需注意符号位处理
• 位左移（<<）：将二进制位向左移动指定位数，右侧补0
  例：5（0101） << 2 = 20（10100）
• 位右移（>>）：将二进制位向右移动指定位数，左侧补0（正数）或符号位（负数）
  例：24（11000） >> 2 = 6（00110）

1.4.2 实验演示

-- 位与运算
select 5&6; -- 5（0101）&6（0110）=4（0100），结果为4

-- 位或运算
select 5|6; -- 5（0101）|6（0110）=7（0111），结果为7

-- 位异或运算
select 5^6; -- 5（0101）^6（0110）=3（0011），结果为3

-- 位取反运算
select ~8; -- 8的二进制取反后结果为-9（补码机制）

-- 位左移运算
select 5<<1; -- 5（0101）左移1位为10（1010），结果为10

-- 位右移运算
select 5>>1; -- 5（0101）右移1位为2（0010），结果为2

1.5 运算符优先级

MySQL中运算符的优先级决定了运算的执行顺序，优先级高的先执行。若需调整顺序，可使用括号()包裹表达式。优先级从高到低大致如下（部分）：

1. 括号（()）
2. 位取反（~）、逻辑非（!）
3. 乘法（*）、除法（/）、取余（%）
4. 加法（+）、减法（-）
5. 位左移（<<）、位右移（>>）
6. 位与（&）
7. 位异或（^）
8. 位或（|）
9. 比较运算符（=, >, <等）
10. 逻辑与（AND）
11. 逻辑或（OR）、逻辑异或（XOR）

1.6 时间函数的使用

时间函数用于处理日期和时间类型的数据，是业务中记录、计算时间的核心工具。

1.6.1 常用时间函数

• CURDATE()：获取当前日期（DATE类型），等效于CURRENT_DATE()或CURRENT_DATE
• CURTIME()：获取当前时间（TIME类型），等效于CURRENT_TIME()或CURRENT_TIME
• NOW()：获取当前日期和时间（DATETIME类型），等效于CURRENT_TIMESTAMP()或CURRENT_TIMESTAMP
• ADDDATE(date, INTERVAL expr unit)：给日期增加指定时间间隔（如天数、周数）
• ADDTIME(time, expr)：给时间增加指定时间（如小时、分钟）
• UNIX_TIMESTAMP([date])：将日期转换为Unix时间戳（1970-01-01 00:00:00至今的秒数），默认返回当前时间戳
• FROM_UNIXTIME(unix_timestamp[, format])：将Unix时间戳转换为可读日期，支持自定义格式
• WEEK(date, [mode])：计算日期所在年份的周数（mode指定周起始日，默认周日）
• DATE_FORMAT(date, format)：按自定义格式格式化日期（如%Y-%m-%d表示“年-月-日”）
• DATEDIFF(date1, date2)：计算两个日期的天数差（date1 – date2）
• DATE_ADD(date, INTERVAL expr type)：与ADDDATE()类似，支持更多时间单位（如小时、月）

1.6.2 实验演示

-- 获取当前日期
select CURDATE(); -- 结果如2025-08-27

-- 获取当前时间
select CURTIME(); -- 结果如15:30:45

-- 获取当前日期时间
select NOW(); -- 结果如2025-08-27 15:30:45

-- 日期加30天
select ADDDATE('2010-01-01', 30); -- 结果为2010-01-31

-- 时间加2小时30分钟
select ADDTIME('2030-01-01 00:00:00', '02:30:00'); -- 结果为2030-01-01 02:30:00

-- 日期转时间戳
select UNIX_TIMESTAMP('2008-08-08 20:08:08'); -- 结果为1218125288

-- 当前时间戳
select UNIX_TIMESTAMP(); -- 结果为当前Unix时间戳

-- 时间戳转日期
select FROM_UNIXTIME(1447430881); -- 结果为对应日期

-- 日期所在周数
select WEEK('2008-08-08'); -- 结果为32（2008年第32周）

-- 格式化日期
select DATE_FORMAT('2008-08-08 20:08:08', '%Y-%M-%D'); -- 结果为2008-August-8th

-- 日期差
select DATEDIFF('2030-06-07', '2030-01-01'); -- 结果为157（相差157天）

-- 日期加200天
select DATE_ADD('2030-01-01', INTERVAL 200 DAY); -- 结果为2030-07-20

1.7 字符串函数的使用

字符串函数用于处理文本数据，如拼接、截取、转换等，是数据清洗和格式化的常用工具。

1.7.1 常用字符串函数

• CONCAT(str1, str2, …)：拼接多个字符串
• CHAR_LENGTH(str)：计算字符串的字符数（与字节数无关）
• INSERT(str, pos, len, newstr)：从str的pos位置开始，替换len长度的子串为newstr（pos从1开始）
• LOWER(str)：将字符串转为小写
• UPPER(str)：将字符串转为大写
• LEFT(str, len)：截取str左侧的len个字符
• RIGHT(str, len)：截取str右侧的len个字符
• REPEAT(str, count)：重复str指定次数
• SUBSTRING(str, start, len)：从start位置截取str的len个字符（start可为负数，从末尾计数）
• LPAD(str, len, padstr)：在str左侧填充padstr，直到总长度为len
• RPAD(str, len, padstr)：在str右侧填充padstr，直到总长度为len
• LTRIM(str)：去除str左侧的空格
• RTRIM(str)：去除str右侧的空格
• TRIM(str)：去除str两侧的空格
• REPLACE(str, from_str, to_str)：将str中所有from_str替换为to_str

1.7.2 实验演示

创建测试表用于验证字符串函数：

create table string_test(name varchar(10), class int);
insert into string_test values ('zhangsan', 1101), (' lisi ', 1102);

-- 拼接字符串（前后加横线）
select concat('-', name, '-') from string_test; -- 结果为-zhangsan-、- lisi -

-- 拼接字段与常量
select concat(name, '-', class) from string_test; -- 结果为zhangsan-1101、 lisi -1102

-- 计算字符数
select CHAR_LENGTH(name) from string_test; -- 结果为8（zhangsan）、5（含空格的lisi）

-- 替换子串（从第3位开始，替换2个字符为test）
select INSERT(name, 3, 2, "test") from string_test; -- 结果为zhtestgsan、  tetisi 

-- 大小写转换
select LOWER("MySQL"); -- 结果为mysql
select UPPER("MySQL"); -- 结果为MYSQL

-- 截取左侧/右侧字符
select LEFT('MySQL', 2); -- 结果为My
select RIGHT('MySQL', 2); -- 结果为QL

-- 重复字符串
select REPEAT('MySQL', 3); -- 结果为MySQLMySQLMySQL

-- 截取子串（从第3位开始，取3个字符）
select SUBSTRING('MySQL', 3, 3); -- 结果为SQL

-- 左侧/右侧填充
select LPAD('MySQL', 6, 'A'); -- 结果为AMySQL（总长度6）
select RPAD('MySQL', 8, 'B'); -- 结果为MySQLBBB（总长度8）

-- 去除空格
select concat('-', LTRIM(' MySQL '), '-'); -- 结果为-MySQL -（去左侧空格）
select concat('-', RTRIM(' MySQL '), '-'); -- 结果为- MySQL-（去右侧空格）
select concat('-', TRIM(' MySQL '), '-'); -- 结果为-MySQL-（去两侧空格）

-- 替换子串
select REPLACE('MySQL', 'My', 'Postgre'); -- 结果为PostgreSQL

1.8 数值函数的使用

数值函数用于处理数值型数据，如取绝对值、四舍五入等，适用于数据计算和统计场景。

1.8.1 常用数值函数

• ABS(x)：返回x的绝对值
• MOD(x, y)：返回x除以y的余数（与x%y等效）
• RAND()：返回0~1之间的随机浮点数
• CEILING(x) 或 CEIL(x)：向上取整（返回大于等于x的最小整数）
• FLOOR(x)：向下取整（返回小于等于x的最大整数）
• ROUND(x, d)：将x四舍五入保留d位小数（d默认0）
• TRUNCATE(x, d)：截断x保留d位小数（直接去除多余小数位）

1.8.2 实验演示

-- 绝对值
select ABS(-10); -- 结果为10
select ABS(10.5); -- 结果为10.5

-- 取余
select MOD(10, 3); -- 结果为1
select MOD(10.5, 3); -- 结果为1.5

-- 随机数
select RAND(); -- 结果为0~1的随机数（如0.3827160493334249）

-- 向上取整
select CEILING(1.1); -- 结果为2
select CEIL(1.9); -- 结果为2

-- 向下取整
select FLOOR(1.1); -- 结果为1
select FLOOR(1.9); -- 结果为1

-- 四舍五入（保留2位小数）
select ROUND(10.565, 2); -- 结果为10.57
select ROUND(10.564, 2); -- 结果为10.56

-- 截断（保留2位小数）
select TRUNCATE(10.567, 2); -- 结果为10.56
select TRUNCATE(10.564, 2); -- 结果为10.56

1.9 JSON函数的使用

JSON函数用于处理MySQL中的JSON类型数据，支持创建、解析、修改JSON格式数据，适用于灵活存储场景。

1.9.1 常用JSON函数

• JSON_OBJECT(key1, val1, …)：创建JSON对象
• JSON_ARRAY(val1, val2, …)：创建JSON数组
• JSON_MERGE(json1, json2, …)：合并多个JSON对象/数组（同键后值覆盖前值）
• JSON_EXTRACT(json, path)：从JSON中提取path路径的值（可简写为json->path）
• JSON_SEARCH(json, mode, str)：在JSON中搜索str，返回路径（mode为one或all）
• JSON_INSERT(json, path, val)：向JSON插入val（仅path不存在时生效）
• JSON_SET(json, path, val)：更新或插入val（无论path是否存在）
• JSON_REPLACE(json, path, val)：替换JSON中path的值（仅path存在时生效）
• JSON_REMOVE(json, path)：删除JSON中path的值

1.9.2 实验演示

-- 创建JSON对象
select JSON_OBJECT('name', 'John', 'age', 30); -- 结果为{"name": "John", "age": 30}

-- 创建含JSON字段的表
create table json_test(id int, a json);

-- 插入JSON对象数据
insert into json_test values (1, JSON_OBJECT('name', 'John', 'age', 30));

-- 插入JSON数组数据
insert into json_test values (2, JSON_ARRAY('apple', 'banana', 'orange'));

-- 查询全表数据
select * from json_test;

-- 合并JSON对象
select JSON_MERGE(
  JSON_OBJECT('name', 'John', 'age', 30),
  JSON_OBJECT('address', '123 Main St')
); -- 结果为{"name": "John", "age": 30, "address": "123 Main St"}

-- 提取JSON值（获取name字段）
select JSON_EXTRACT(a, '$.name') from json_test where id=1; -- 结果为"John"

-- 搜索JSON中的值（查找John的路径）
select JSON_SEARCH(a, 'one', 'John') from json_test where id=1; -- 结果为"$.name"

-- 插入新键值（添加class字段）
select JSON_INSERT(a, '$.class', 'class1') from json_test where id=1;

-- 更新已有键值（修改name为Jane）
select JSON_SET(a, '$.name', 'Jane') from json_test where id=1;

-- 替换已有键值（修改age为31）
select JSON_REPLACE(a, '$.age', 31) from json_test where id=1;

-- 删除键值（删除age字段）
select JSON_REMOVE(a, '$.age') from json_test where id=1;

1.10 其他常用函数

1.10.1 条件判断函数

• IF(expr, val1, val2)：若expr为真，返回val1，否则返回val2
• IFNULL(val1, val2)：若val1不为NULL，返回val1，否则返回val2
• CASE：多条件分支判断，语法：

CASE 
  WHEN cond1 THEN val1 
  WHEN cond2 THEN val2 
  ELSE val3 
END

1.10.2 系统相关函数

• VERSION()：返回MySQL服务器版本
• USER()：返回当前登录用户名（格式：user@host）
• CHARSET(str)：返回字符串str的字符集

1.10.3 IP与数字转换函数

• INET_ATON(ip)：将IPv4地址转换为长整型数值
• INET_NTOA(num)：将长整型数值转换为IPv4地址

1.10.4 实验演示

-- IF函数（判断2>1是否成立）
select IF(2>1, 'true', 'false'); -- 结果为true

-- IFNULL函数（处理NULL值）
select IFNULL(null, 'is null'); -- 结果为is null

-- CASE函数（多条件判断）
select case 1
  when 1 then '等于1'
  when 2 then '等于2'
  else '不是1也不是2'
end; -- 结果为等于1

-- 系统函数
select VERSION(); -- 结果如8.0.36
select USER(); -- 结果如root@localhost
select CHARSET('测试'); -- 结果如utf8mb4

-- IP与数字转换
select INET_ATON('192.168.1.2'); -- 结果为3232235778
select INET_NTOA(3232235778); -- 结果为192.168.1.2

1.11 案例：商品库存表操作

1.11.1 创建表并插入数据

-- 创建商品库存表
CREATE TABLE product_inventory (
  id INT NOT NULL AUTO_INCREMENT COMMENT '行ID',
  product_number VARCHAR(50) NOT NULL COMMENT '商品编号',
  product_name VARCHAR(255) NOT NULL COMMENT '商品名称',
  manufacturer VARCHAR(255) NOT NULL COMMENT '商品厂商',
  production_date DATE NOT NULL COMMENT '生产日期',
  shelf_life INT NOT NULL COMMENT '保质期（天）',
  price DECIMAL(10, 2) NOT NULL COMMENT '单价',
  stock INT NOT NULL COMMENT '库存数量',
  PRIMARY KEY (id),
  UNIQUE KEY (product_number) -- 商品编号唯一
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品库存表';

-- 插入测试数据
INSERT INTO product_inventory (product_number, product_name, manufacturer, production_date, shelf_life, price, stock)
VALUES 
  ('P10001', '可口可乐', '可口可乐公司', '2021-07-01', 365, 3.00, 1000),
  ('P10002', '王老吉', '广州王老吉生物科技有限公司', '2021-06-01', 360, 2.50, 800);

1.11.2 业务查询示例

-- 拼接商品名称与厂商
select concat(product_name, ' - ', manufacturer) as product_info from product_inventory;
-- 结果：可口可乐 - 可口可乐公司、王老吉 - 广州王老吉生物科技有限公司

-- 计算总库存价值（单价×库存求和）
select sum(price * stock) as total_value from product_inventory;
-- 结果：3.00×1000 + 2.50×800 = 5000.00

-- 计算过期日期（生产日期+保质期）
select product_name, 
  date_add(production_date, interval shelf_life day) as expiration_date 
from product_inventory;
-- 结果：可口可乐→2022-07-01，王老吉→2022-05-27

1.12 章末总结

知识点	需要掌握的内容
算术运算符	加减乘除、取余
比较运算符	=、<>、>、<、>=、<=、BETWEEN、IN、IS NULL
逻辑运算符	NOT或!、AND、OR、XOR
位运算符	&、|、^、~、<<、>>
运算符优先级	优先级表格、建议给需要做运算的表达式加上括号
时间函数	CURDATE()、CURTIME()、NOW()、ADDDATE()、ADDTIME()、UNIX_TIMESTAMP()、FROM_UNIXTIME()、WEEK()、DATE_FORMAT()、DATEDIFF()、DATE_ADD()
字符串函数	CONCAT()、CHAR_LENGTH()、INSERT()、LOWER()、UPPER()、LEFT()、RIGHT()、REPEAT()、SUBSTRING()、LPAD()、RPAD()、LTRIM()、RTRIM()、TRIM()、REPLACE()
数值函数	ABS()、MOD()、RAND()、CEILING()、FLOOR()、ROUND()、TRUNCATE()
JSON函数	JSON_OBJECT()、JSON_ARRAY()、JSON_MERGE()、JSON_EXTRACT()、JSON_SEARCH()、JSON_INSERT()、JSON_SET()、JSON_REPLACE()、JSON_REMOVE()
其他函数	IF()、IFNULL()、CASE、VERSION()、USER()、CHARSET()、INET_ATON()、INET_NTOA()

本文围绕MySQL的运算与函数展开，涵盖了算术、比较、逻辑、位运算符的用法，以及时间、字符串、数值、JSON等常用函数的实践。这些工具是处理数据计算、条件过滤、格式转换的核心能力，需结合业务场景灵活运用。实际工作中，应注意运算符优先级对结果的影响，以及函数在性能优化中的合理使用（如避免在WHERE子句中对字段使用函数，以免索引失效）。