MySQL 运算符 - MySQL 教程 | 小傅哥技术教程

运算符概述

MySQL 运算符用于在查询中执行各种计算和比较操作。理解运算符的优先级和使用方法对编写高效的SQL查询至关重要。

🔢 算术运算符

执行基本的数学运算：加、减、乘、除、取模

⚖️ 比较运算符

比较两个值的大小关系：等于、大于、小于等

🔗 逻辑运算符

组合多个条件：AND、OR、NOT等逻辑操作

🎯 位运算符

对二进制位进行操作：位与、位或、位异或等

📝 赋值运算符

为变量或列赋值：等号赋值和复合赋值

🔍 特殊运算符

特殊用途运算符：LIKE、IN、BETWEEN等

准备测试环境

-- 创建测试数据库
CREATE DATABASE operator_demo;
USE operator_demo;

-- 创建测试表
CREATE TABLE test_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    salary DECIMAL(10,2),
    department VARCHAR(50),
    is_active BOOLEAN,
    join_date DATE,
    score FLOAT,
    status_code INT
);

-- 插入测试数据
INSERT INTO test_data (name, age, salary, department, is_active, join_date, score, status_code) VALUES
('张三', 25, 8500.00, '技术部', TRUE, '2022-01-15', 85.5, 1),
('李四', 30, 9200.00, '销售部', TRUE, '2021-03-22', 92.0, 2),
('王五', 28, 7800.00, '技术部', FALSE, '2023-06-10', 78.5, 1),
('赵六', 35, 12000.00, '管理部', TRUE, '2020-09-05', 95.0, 4),
('钱七', 22, 6500.00, '人事部', TRUE, '2023-02-14', 88.0, 2),
('孙八', 32, 10500.00, '财务部', TRUE, '2019-11-30', 90.5, 3),
('周九', 27, 8800.00, '技术部', TRUE, '2022-07-18', 82.0, 1),
('吴十', 29, 7200.00, '销售部', FALSE, '2023-12-01', 75.5, 2);

-- 创建数值测试表
CREATE TABLE numbers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    value1 INT,
    value2 INT,
    decimal_val DECIMAL(5,2),
    binary_val INT
);

-- 插入数值数据
INSERT INTO numbers (value1, value2, decimal_val, binary_val) VALUES
(10, 3, 15.75, 12),  -- 1100 in binary
(20, 4, 25.50, 10),  -- 1010 in binary
(15, 5, 30.25, 6),   -- 0110 in binary
(8, 2, 12.80, 9),    -- 1001 in binary
(25, 7, 45.00, 15);  -- 1111 in binary

SELECT 'Test data created successfully' AS message;
                

算术运算符

基本算术运算

-- 1. 基本算术运算符
SELECT 
    value1,
    value2,
    value1 + value2 AS addition,        -- 加法
    value1 - value2 AS subtraction,     -- 减法
    value1 * value2 AS multiplication,  -- 乘法
    value1 / value2 AS division,        -- 除法
    value1 % value2 AS modulo,          -- 取模
    value1 DIV value2 AS integer_division -- 整数除法
FROM numbers;

-- 2. 在实际查询中使用算术运算
SELECT 
    name,
    salary,
    salary * 12 AS annual_salary,
    salary * 0.1 AS tax_deduction,
    salary - (salary * 0.1) AS net_salary,
    salary + 1000 AS salary_with_bonus,
    ROUND(salary / 30, 2) AS daily_salary
FROM test_data;

-- 3. 复杂算术表达式
SELECT 
    name,
    age,
    salary,
    score,
    (salary * 0.3 + score * 100) AS performance_index,
    POWER(score / 10, 2) AS score_squared,
    SQRT(salary / 1000) AS salary_sqrt,
    ABS(score - 85) AS score_deviation
FROM test_data;

-- 4. 处理NULL值的算术运算
SELECT 
    10 + NULL AS add_null,
    10 - NULL AS subtract_null,
    10 * NULL AS multiply_null,
    10 / NULL AS divide_null,
    IFNULL(10 + NULL, 0) AS handle_null_addition
;
                

算术运算的实际应用

-- 1. 薪资计算
SELECT 
    name,
    salary,
    CASE 
        WHEN department = '技术部' THEN salary * 1.15  -- 技术部加薪15%
        WHEN department = '销售部' THEN salary * 1.10  -- 销售部加薪10%
        ELSE salary * 1.05  -- 其他部门加薪5%
    END AS adjusted_salary,
    CASE 
        WHEN department = '技术部' THEN (salary * 1.15) - salary
        WHEN department = '销售部' THEN (salary * 1.10) - salary
        ELSE (salary * 1.05) - salary
    END AS salary_increase
FROM test_data;

-- 2. 年龄和工作年限计算
SELECT 
    name,
    age,
    join_date,
    YEAR(CURDATE()) - YEAR(join_date) AS years_of_service,
    65 - age AS years_to_retirement,
    DATEDIFF(CURDATE(), join_date) / 365.25 AS precise_years_service,
    age + (YEAR(CURDATE()) - YEAR(join_date)) AS age_when_started
FROM test_data;

-- 3. 绩效评分计算
SELECT 
    name,
    score,
    salary,
    age,
    (score * 0.4 + (salary / 100) * 0.3 + (40 - age) * 0.3) AS composite_score,
    CASE 
        WHEN score >= 90 THEN score + 5  -- 优秀加分
        WHEN score >= 80 THEN score + 2  -- 良好加分
        WHEN score < 70 THEN score - 3   -- 不及格扣分
        ELSE score
    END AS adjusted_score
FROM test_data;
                

算术运算符总结

运算符	名称	示例	结果	说明
+	加法	10 + 3	13	两个数相加
-	减法	10 - 3	7	两个数相减
*	乘法	10 * 3	30	两个数相乘
/	除法	10 / 3	3.3333	浮点数除法
DIV	整数除法	10 DIV 3	3	返回整数部分
% 或 MOD	取模	10 % 3	1	返回余数

比较运算符

基本比较运算

-- 1. 基本比较运算符
SELECT 
    name,
    age,
    salary,
    age = 30 AS is_thirty,              -- 等于
    age != 30 AS not_thirty,            -- 不等于
    age <> 30 AS not_equal_thirty,      -- 不等于（另一种写法）
    age > 30 AS older_than_thirty,      -- 大于
    age < 30 AS younger_than_thirty,    -- 小于
    age >= 30 AS thirty_or_older,       -- 大于等于
    age <= 30 AS thirty_or_younger,     -- 小于等于
    salary <=> NULL AS safe_equal_null  -- 安全等于（NULL安全）
FROM test_data;

-- 2. 字符串比较
SELECT 
    name,
    department,
    name = '张三' AS is_zhangsan,
    department != '技术部' AS not_tech_dept,
    name > '王' AS name_after_wang,
    department LIKE '%部' AS ends_with_bu,
    name REGEXP '^[张李王]' AS surname_zhang_li_wang
FROM test_data;

-- 3. 日期比较
SELECT 
    name,
    join_date,
    join_date = '2022-01-15' AS joined_specific_date,
    join_date > '2022-01-01' AS joined_after_2022,
    join_date BETWEEN '2022-01-01' AND '2022-12-31' AS joined_in_2022,
    YEAR(join_date) = 2023 AS joined_in_2023
FROM test_data;

-- 4. NULL值比较
SELECT 
    name,
    department,
    department IS NULL AS dept_is_null,
    department IS NOT NULL AS dept_not_null,
    department <=> NULL AS safe_equal_null,
    ISNULL(department) AS isnull_check
FROM test_data;
                

高级比较操作

-- 1. BETWEEN 范围比较
SELECT 
    name,
    age,
    salary,
    score,
    age BETWEEN 25 AND 30 AS age_in_range,
    salary BETWEEN 8000 AND 10000 AS salary_in_range,
    score NOT BETWEEN 80 AND 90 AS score_out_range,
    join_date BETWEEN '2022-01-01' AND '2022-12-31' AS joined_2022
FROM test_data;

-- 2. IN 集合比较
SELECT 
    name,
    department,
    age,
    status_code,
    department IN ('技术部', '销售部') AS is_core_dept,
    age IN (25, 30, 35) AS specific_ages,
    status_code NOT IN (1, 2) AS special_status,
    name IN ('张三', '李四', '王五') AS is_top_three
FROM test_data;

-- 3. LIKE 模式匹配
SELECT 
    name,
    department,
    name LIKE '张%' AS starts_with_zhang,
    name LIKE '%三' AS ends_with_san,
    name LIKE '_四' AS second_char_si,
    department LIKE '%技术%' AS contains_tech,
    name NOT LIKE '%五%' AS not_contains_wu
FROM test_data;

-- 4. 正则表达式比较
SELECT 
    name,
    department,
    name REGEXP '^[张李王]' AS common_surname,
    name REGEXP '[三四五]$' AS number_ending,
    department REGEXP '技术|销售' AS tech_or_sales,
    name NOT REGEXP '[六七八九十]' AS not_high_numbers
FROM test_data;
                

比较运算符总结

运算符	名称	示例	说明
=	等于	age = 30	值相等返回TRUE
!= 或 <>	不等于	age != 30	值不相等返回TRUE
>	大于	age > 30	左值大于右值
<	小于	age < 30	左值小于右值
>=	大于等于	age >= 30	左值大于或等于右值
<=	小于等于	age <= 30	左值小于或等于右值
<=>	安全等于	value <=> NULL	NULL安全的等于比较
IS NULL	为空	value IS NULL	检查是否为NULL
IS NOT NULL	非空	value IS NOT NULL	检查是否不为NULL
BETWEEN	范围内	age BETWEEN 20 AND 30	值在指定范围内
IN	在集合中	dept IN ('技术部', '销售部')	值在指定集合中
LIKE	模式匹配	name LIKE '张%'	字符串模式匹配

逻辑运算符

基本逻辑运算

-- 1. AND 逻辑与
SELECT 
    name,
    age,
    salary,
    department,
    (age > 25 AND salary > 8000) AS senior_high_paid,
    (department = '技术部' AND is_active = TRUE) AS active_tech,
    (age BETWEEN 25 AND 35 AND score > 80) AS prime_performer
FROM test_data;

-- 2. OR 逻辑或
SELECT 
    name,
    age,
    salary,
    department,
    (age < 25 OR salary > 10000) AS young_or_high_paid,
    (department = '技术部' OR department = '销售部') AS core_departments,
    (score > 90 OR salary > 11000) AS top_performer
FROM test_data;

-- 3. NOT 逻辑非
SELECT 
    name,
    age,
    department,
    is_active,
    NOT (age > 30) AS not_over_thirty,
    NOT (department = '技术部') AS not_tech_dept,
    NOT is_active AS inactive,
    !(score < 80) AS not_low_score
FROM test_data;

-- 4. 复杂逻辑组合
SELECT 
    name,
    age,
    salary,
    department,
    score,
    (
        (age BETWEEN 25 AND 35) AND 
        (salary > 8000) AND 
        (department IN ('技术部', '销售部')) AND 
        (score >= 80)
    ) AS ideal_candidate,
    (
        (age < 30 AND score > 85) OR 
        (age >= 30 AND salary > 10000)
    ) AS promotion_candidate
FROM test_data;
                

逻辑运算的实际应用

-- 1. 员工分类
SELECT 
    name,
    age,
    salary,
    department,
    score,
    CASE 
        WHEN (age < 30 AND score > 85 AND salary > 8000) THEN '潜力股'
        WHEN (age >= 30 AND salary > 10000 AND score > 80) THEN '骨干员工'
        WHEN (department = '技术部' AND score > 80) THEN '技术专家'
        WHEN (is_active = FALSE) THEN '待激活'
        ELSE '普通员工'
    END AS employee_category,
    (
        (score > 85) AND 
        (salary > (SELECT AVG(salary) FROM test_data)) AND
        (YEAR(CURDATE()) - YEAR(join_date) >= 1)
    ) AS eligible_for_bonus
FROM test_data;

-- 2. 薪资调整逻辑
SELECT 
    name,
    salary,
    department,
    score,
    age,
    CASE 
        WHEN (score >= 90 AND salary < 10000) THEN salary * 1.20  -- 高分低薪大幅调整
        WHEN (score >= 85 OR age >= 35) THEN salary * 1.15        -- 高分或资深适度调整
        WHEN (department = '技术部' AND score >= 80) THEN salary * 1.10  -- 技术部门小幅调整
        WHEN (is_active = TRUE AND score >= 75) THEN salary * 1.05      -- 在职及格小幅调整
        ELSE salary  -- 无调整
    END AS adjusted_salary,
    (
        (score < 70) OR 
        (is_active = FALSE AND YEAR(CURDATE()) - YEAR(join_date) < 1)
    ) AS needs_attention
FROM test_data;

-- 3. 查询条件组合
-- 查找符合特定条件的员工
SELECT 
    name,
    age,
    salary,
    department,
    score
FROM test_data
WHERE (
    -- 条件组1：年轻高潜力
    (age <= 28 AND score >= 85) OR
    -- 条件组2：资深高薪
    (age >= 32 AND salary >= 10000) OR
    -- 条件组3：技术部门优秀员工
    (department = '技术部' AND score >= 80 AND is_active = TRUE)
) AND (
    -- 排除条件：不要低分员工
    score >= 75
);
                

逻辑运算符总结

运算符	名称	说明	示例	结果
AND 或 &&	逻辑与	两个条件都为真时返回真	TRUE AND FALSE	FALSE
OR 或 \|\|	逻辑或	任一条件为真时返回真	TRUE OR FALSE	TRUE
NOT 或 !	逻辑非	条件取反	NOT TRUE	FALSE
XOR	逻辑异或	两个条件不同时返回真	TRUE XOR FALSE	TRUE

位运算符

基本位运算

-- 1. 基本位运算符
SELECT 
    binary_val,
    BIN(binary_val) AS binary_representation,
    binary_val & 8 AS bitwise_and,      -- 位与
    binary_val | 8 AS bitwise_or,       -- 位或
    binary_val ^ 8 AS bitwise_xor,      -- 位异或
    ~binary_val AS bitwise_not,         -- 位非
    binary_val << 1 AS left_shift,      -- 左移
    binary_val >> 1 AS right_shift      -- 右移
FROM numbers;

-- 2. 位运算的实际应用
-- 权限系统示例（使用位掩码）
SELECT 
    name,
    status_code,
    BIN(status_code) AS status_binary,
    status_code & 1 AS has_read_permission,     -- 检查读权限（第1位）
    status_code & 2 AS has_write_permission,    -- 检查写权限（第2位）
    status_code & 4 AS has_execute_permission,  -- 检查执行权限（第3位）
    status_code | 1 AS add_read_permission,     -- 添加读权限
    status_code | 2 AS add_write_permission,    -- 添加写权限
    status_code & ~1 AS remove_read_permission  -- 移除读权限
FROM test_data;

-- 3. 状态标志位操作
SELECT 
    id,
    status_code,
    BIN(status_code) AS current_status,
    CASE 
        WHEN status_code & 1 = 1 THEN '活跃'
        ELSE '非活跃'
    END AS activity_status,
    CASE 
        WHEN status_code & 2 = 2 THEN '已验证'
        ELSE '未验证'
    END AS verification_status,
    CASE 
        WHEN status_code & 4 = 4 THEN '管理员'
        ELSE '普通用户'
    END AS user_type
FROM test_data;

-- 4. 位运算在数据压缩中的应用
SELECT 
    value1,
    value2,
    (value1 << 8) | value2 AS packed_values,    -- 将两个8位值打包成16位
    ((value1 << 8) | value2) >> 8 AS unpacked_high,  -- 解包高8位
    ((value1 << 8) | value2) & 255 AS unpacked_low    -- 解包低8位
FROM numbers
WHERE value1 < 256 AND value2 < 256;  -- 确保是8位值
                

位运算符总结

运算符	名称	说明	示例	结果
&	位与	对应位都为1时结果为1	12 & 10 (1100 & 1010)	8 (1000)
\|	位或	对应位有一个为1时结果为1	12 \| 10 (1100 \| 1010)	14 (1110)
^	位异或	对应位不同时结果为1	12 ^ 10 (1100 ^ 1010)	6 (0110)
~	位非	按位取反	~12	-13
<<	左移	向左移动指定位数	12 << 1	24
>>	右移	向右移动指定位数	12 >> 1	6

赋值运算符

-- 1. 基本赋值运算符
-- 在变量中使用赋值运算符
SET @var1 = 10;
SET @var2 := 20;  -- := 也是赋值运算符

SELECT 
    @var1 AS variable1,
    @var2 AS variable2,
    @var3 := @var1 + @var2 AS sum_result,
    @var4 := @var1 * 2 AS double_var1;

-- 2. 在查询中使用赋值
SELECT 
    name,
    salary,
    @running_total := @running_total + salary AS running_total,
    @max_salary := GREATEST(@max_salary, salary) AS max_so_far,
    @row_num := @row_num + 1 AS row_number
FROM test_data, (SELECT @running_total := 0, @max_salary := 0, @row_num := 0) AS init
ORDER BY salary;

-- 3. UPDATE 语句中的赋值运算符
-- 创建临时表进行演示
CREATE TEMPORARY TABLE temp_salary AS SELECT * FROM test_data;

-- 使用复合赋值运算符（MySQL 8.0+）
UPDATE temp_salary SET salary = salary * 1.1 WHERE department = '技术部';
UPDATE temp_salary SET score = score + 5 WHERE score >= 85;

SELECT name, salary, score FROM temp_salary;

-- 4. 条件赋值
SELECT 
    name,
    age,
    salary,
    @bonus := CASE 
        WHEN age < 30 AND salary > 8000 THEN salary * 0.15
        WHEN age >= 30 AND salary > 10000 THEN salary * 0.20
        ELSE salary * 0.10
    END AS calculated_bonus,
    @total_compensation := salary + @bonus AS total_compensation
FROM test_data;
                

赋值运算符总结

运算符	名称	说明	示例
=	赋值	将右边的值赋给左边的变量	SET @var = 10
:=	赋值	赋值运算符，可在表达式中使用	@var := 10

运算符优先级

优先级运算符结合性说明 1 (最高) INTERVAL 左结合时间间隔 2 BINARY, COLLATE 左结合二进制转换，排序规则 3 ! 右结合逻辑非 4 - (一元), ~ (位非) 右结合一元减号，位非 5 ^ 左结合异或 6 *, /, DIV, %, MOD 左结合乘法，除法，取模 7 -, + 左结合减法，加法 8 <<, >> 左结合位移 9 & 左结合位与 10 | 左结合位或 11 =, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN 左结合比较运算符 12 BETWEEN, CASE, WHEN, THEN, ELSE 左结合范围，条件 13 NOT 右结合逻辑非 14 AND, && 左结合逻辑与 15 XOR 左结合逻辑异或 16 OR, || 左结合逻辑或 17 (最低) =, := 右结合赋值

-- 运算符优先级示例
SELECT 
    -- 算术运算优先级
    2 + 3 * 4 AS arithmetic1,        -- 结果：14 (不是20)
    (2 + 3) * 4 AS arithmetic2,      -- 结果：20
    
    -- 比较和逻辑运算优先级
    1 = 1 AND 2 = 3 OR 4 = 4 AS logic1,  -- 结果：1 (TRUE)
    1 = 1 AND (2 = 3 OR 4 = 4) AS logic2, -- 结果：1 (TRUE)
    (1 = 1 AND 2 = 3) OR 4 = 4 AS logic3, -- 结果：1 (TRUE)
    
    -- 位运算优先级
    12 | 3 & 5 AS bitwise1,          -- 结果：13
    (12 | 3) & 5 AS bitwise2,        -- 结果：5
    12 | (3 & 5) AS bitwise3;        -- 结果：13

-- 复杂表达式的优先级
SELECT 
    name,
    age,
    salary,
    -- 不使用括号（依赖优先级）
    age > 25 AND salary > 8000 OR department = '管理部' AS condition1,
    -- 使用括号明确优先级
    (age > 25 AND salary > 8000) OR department = '管理部' AS condition2,
    age > 25 AND (salary > 8000 OR department = '管理部') AS condition3
FROM test_data;
                

实际应用案例

复杂查询条件

-- 1. 员工筛选的复杂条件
SELECT 
    name,
    age,
    salary,
    department,
    score,
    join_date
FROM test_data
WHERE (
    -- 高潜力年轻员工
    (age <= 28 AND score >= 85 AND salary >= 7000) OR
    -- 资深高薪员工
    (age >= 32 AND salary >= 10000) OR
    -- 核心部门优秀员工
    (department IN ('技术部', '销售部') AND score >= 80 AND is_active = TRUE)
) AND (
    -- 排除试用期员工（入职不满3个月）
    DATEDIFF(CURDATE(), join_date) >= 90
) AND (
    -- 排除低分员工
    score >= 75
)
ORDER BY 
    CASE 
        WHEN department = '技术部' THEN 1
        WHEN department = '销售部' THEN 2
        ELSE 3
    END,
    score DESC,
    salary DESC;

-- 2. 薪资调整计算
SELECT 
    name,
    current_salary,
    department,
    performance_score,
    years_service,
    new_salary,
    salary_increase,
    increase_percentage
FROM (
    SELECT 
        name,
        salary AS current_salary,
        department,
        score AS performance_score,
        TIMESTAMPDIFF(YEAR, join_date, CURDATE()) AS years_service,
        CASE 
            -- 顶级表现者：20%增长
            WHEN score >= 95 AND salary < 15000 THEN salary * 1.20
            -- 优秀表现者：15%增长
            WHEN score >= 90 AND salary < 12000 THEN salary * 1.15
            -- 良好表现者：10%增长
            WHEN score >= 85 OR (department = '技术部' AND score >= 80) THEN salary * 1.10
            -- 合格表现者：5%增长
            WHEN score >= 75 AND is_active = TRUE THEN salary * 1.05
            -- 其他情况：无增长
            ELSE salary
        END AS new_salary
    FROM test_data
) AS salary_calc
CROSS JOIN (
    SELECT 
        new_salary - current_salary AS salary_increase,
        ROUND((new_salary - current_salary) / current_salary * 100, 2) AS increase_percentage
) AS calc
WHERE new_salary > current_salary
ORDER BY increase_percentage DESC;
                

数据分析和统计

-- 1. 员工分布分析
SELECT 
    department,
    COUNT(*) AS total_employees,
    SUM(CASE WHEN age < 30 THEN 1 ELSE 0 END) AS young_employees,
    SUM(CASE WHEN age BETWEEN 30 AND 40 THEN 1 ELSE 0 END) AS middle_age_employees,
    SUM(CASE WHEN age > 40 THEN 1 ELSE 0 END) AS senior_employees,
    ROUND(AVG(salary), 2) AS avg_salary,
    ROUND(AVG(score), 2) AS avg_score,
    SUM(CASE WHEN score >= 85 THEN 1 ELSE 0 END) AS high_performers,
    ROUND(
        SUM(CASE WHEN score >= 85 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 
        2
    ) AS high_performer_percentage
FROM test_data
GROUP BY department
HAVING COUNT(*) > 1  -- 只显示有多个员工的部门
ORDER BY avg_score DESC, avg_salary DESC;

-- 2. 绩效等级分布
SELECT 
    performance_grade,
    COUNT(*) AS employee_count,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM test_data), 2) AS percentage,
    ROUND(AVG(salary), 2) AS avg_salary,
    ROUND(AVG(age), 1) AS avg_age,
    GROUP_CONCAT(name ORDER BY score DESC SEPARATOR ', ') AS employees
FROM (
    SELECT 
        name,
        age,
        salary,
        score,
        CASE 
            WHEN score >= 95 THEN 'A+'
            WHEN score >= 90 THEN 'A'
            WHEN score >= 85 THEN 'B+'
            WHEN score >= 80 THEN 'B'
            WHEN score >= 75 THEN 'C+'
            WHEN score >= 70 THEN 'C'
            ELSE 'D'
        END AS performance_grade
    FROM test_data
) AS graded_employees
GROUP BY performance_grade
ORDER BY 
    CASE performance_grade
        WHEN 'A+' THEN 1
        WHEN 'A' THEN 2
        WHEN 'B+' THEN 3
        WHEN 'B' THEN 4
        WHEN 'C+' THEN 5
        WHEN 'C' THEN 6
        ELSE 7
    END;
                

最佳实践和注意事项

🎯 运算符使用建议

明确优先级：复杂表达式使用括号明确运算顺序
NULL值处理：注意NULL值在运算中的特殊行为
类型转换：避免隐式类型转换导致的性能问题
索引友好：WHERE子句中避免对列进行函数运算
可读性优先：复杂逻辑拆分为多个简单条件

⚠️ 常见陷阱

NULL比较：使用IS NULL而不是= NULL
字符串比较：注意字符集和排序规则的影响
浮点数比较：避免直接比较浮点数是否相等
除零错误：除法运算前检查除数是否为零
位运算范围：注意整数溢出和符号位的影响

                    📈 性能优化技巧
                    短路求值：利用AND和OR的短路特性优化条件顺序
索引利用：将能使用索引的条件放在WHERE子句前面
常量折叠：MySQL会自动优化常量表达式
谓词下推：复杂查询中合理使用子查询和连接

                

-- 性能优化示例
-- 1. 利用短路求值优化条件顺序
SELECT name, age, salary, department
FROM test_data
WHERE is_active = TRUE  -- 先检查简单条件
  AND department = '技术部'  -- 再检查索引条件
  AND salary > 8000  -- 最后检查范围条件
  AND score >= (SELECT AVG(score) FROM test_data);  -- 最后执行子查询

-- 2. 避免在WHERE子句中对列进行运算
-- 不好的写法
SELECT name FROM test_data WHERE YEAR(join_date) = 2022;
-- 好的写法
SELECT name FROM test_data WHERE join_date >= '2022-01-01' AND join_date < '2023-01-01';

-- 3. 合理使用CASE表达式
SELECT 
    name,
    salary,
    CASE 
        WHEN salary >= 10000 THEN '高薪'
        WHEN salary >= 8000 THEN '中等'
        ELSE '一般'
    END AS salary_level
FROM test_data;
                

总结

MySQL运算符是构建查询条件和表达式的基础工具。掌握各种运算符的使用方法和优先级规则，能够帮助我们编写更高效、更准确的SQL查询。

                    🎓 学习要点回顾
                    算术运算符：用于数值计算，注意除零和NULL值处理
比较运算符：用于条件判断，掌握各种比较方式
逻辑运算符：用于组合条件，理解短路求值机制
位运算符：用于位级操作，适用于权限和状态管理
运算符优先级：理解优先级规则，合理使用括号
性能考虑：编写索引友好的查询条件

                

🚀 进阶学习建议

深入学习MySQL的查询优化器工作原理
掌握EXPLAIN的使用，分析查询执行计划
学习窗口函数和CTE（公共表表达式）
了解MySQL 8.0的新特性和改进
实践复杂的数据分析和报表查询