MySQL: MySQL运算符与表达式详解

-- 错误写法
SELECT * FROM orders 
WHERE order_date > '2026-06-03' AND amount > 100 AND status != 'cancelled';

-- 漏了：status 可能是 NULL

-- 正确写法：处理 NULL
SELECT * FROM orders 
WHERE order_date > DATE_SUB(CURDATE(), INTERVAL 30 DAY)
  AND amount > 100
  AND (status != 'cancelled' OR status IS NULL);

-- 计算订单总价
SELECT 
    product_name,
    price,
    quantity,
    price * quantity AS total
FROM order_items;

-- 计算折扣价
SELECT 
    product_name,
    original_price,
    discount_rate,
    original_price * (1 - discount_rate / 100) AS sale_price
FROM products;

+--------------+-------+----------+-------+
| product_name | price | quantity | total |
+--------------+-------+----------+-------+
| iPhone       | 999   |        2 | 1998  |
| MacBook      | 1999  |        1 | 1999  |
+--------------+-------+----------+-------+

-- 查询价格在 100 到 500 之间的商品
SELECT * FROM products WHERE price BETWEEN 100 AND 500;

-- 等价于
SELECT * FROM products WHERE price >= 100 AND price <= 500;

-- 查询某个日期范围内的订单
SELECT * FROM orders WHERE order_date BETWEEN '2026-01-01' AND '2026-06-30';

-- 查询特定状态的订单
SELECT * FROM orders WHERE status IN ('pending', 'paid', 'shipped');

-- 等价于
SELECT * FROM orders WHERE status = 'pending' OR status = 'paid' OR status = 'shipped';

-- 查询特定部门的员工
SELECT * FROM employees WHERE department_id IN (1, 3, 5, 7);

-- % 匹配任意多个字符
SELECT * FROM users WHERE name LIKE 'J%';       -- J 开头
SELECT * FROM users WHERE name LIKE '%son';     -- son 结尾
SELECT * FROM users WHERE name LIKE '%john%';   -- 包含 john

-- _ 匹配单个字符
SELECT * FROM users WHERE phone LIKE '138____1111'; -- 第4-7位任意

-- ESCAPE 转义
SELECT * FROM files WHERE name LIKE '%\%%' ESCAPE '\\'; -- 包含 %

-- NULL 不能用 = 比较
SELECT * FROM users WHERE email = NULL;    -- ❌ 错误，返回空
SELECT * FROM users WHERE email IS NULL;   -- ✅ 正确

-- NULL 不能用 != 比较
SELECT * FROM users WHERE email != NULL;   -- ❌ 错误
SELECT * FROM users WHERE email IS NOT NULL; -- ✅ 正确

-- 安全等于 <=>
SELECT NULL = NULL;    -- NULL（不确定）
SELECT NULL <=> NULL;  -- 1（确定相等）

-- AND：两个条件都满足
SELECT * FROM employees 
WHERE department = 'Engineering' AND salary > 8000;

-- OR：满足任一条件
SELECT * FROM employees 
WHERE department = 'Sales' OR department = 'Marketing';

-- 组合：注意优先级（AND > OR）
SELECT * FROM employees 
WHERE (department = 'Sales' OR department = 'Marketing')
  AND salary > 5000;

-- NOT IN
SELECT * FROM products WHERE category_id NOT IN (1, 2, 3);

-- NOT BETWEEN
SELECT * FROM orders WHERE amount NOT BETWEEN 100 AND 500;

-- NOT LIKE
SELECT * FROM users WHERE email NOT LIKE '%@test.com';

-- NOT EXISTS
SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- 权限系统（位掩码）
-- 读=1, 写=2, 执行=4
CREATE TABLE permissions (
    user_id INT,
    permission TINYINT
);

-- 用户有读+写权限 = 1|2 = 3
INSERT INTO permissions VALUES (1, 3);

-- 检查是否有写权限 (3 & 2 = 2, 非0表示有)
SELECT * FROM permissions WHERE permission & 2;