MySQL高阶混合指令大全：从复杂查询到事务控制的全场景实践

sql1SELECT c.name, o.order_date, p.product_name, SUM(oi.quantity * p.price) AS total_amount
2FROM customers c
3JOIN orders o ON c.id = o.customer_id
4JOIN order_items oi ON o.id = oi.order_id
5JOIN products p ON oi.product_id = p.id
6WHERE o.order_date BETWEEN '2025-01-01' AND '2025-12-31'
7  AND (p.category = 'Electronics' OR c.region = 'APAC')
8GROUP BY c.name, o.order_date, p.product_name
9HAVING total_amount > 1000
10ORDER BY total_amount DESC;

sql1CREATE VIEW high_value_orders AS
2SELECT c.name, o.order_id, SUM(oi.quantity * p.price) AS order_value
3FROM customers c
4JOIN orders o ON c.id = o.customer_id
5JOIN order_items oi ON o.id = oi.order_id
6JOIN products p ON oi.product_id = p.id
7GROUP BY c.name, o.order_id
8HAVING order_value > 5000;
9
10-- 查询视图
11SELECT * FROM high_value_orders WHERE name LIKE 'A%';

sql1DELIMITER //
2CREATE PROCEDURE transfer_funds(
3    IN from_account VARCHAR(32),
4    IN to_account VARCHAR(32),
5    IN amount DECIMAL(10,2)
6)
7BEGIN
8    DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
9    START TRANSACTION;
10    
11    -- 扣减转出账户余额
12    UPDATE accounts SET balance = balance - amount WHERE account_id = from_account;
13    
14    -- 增加转入账户余额
15    UPDATE accounts SET balance = balance + amount WHERE account_id = to_account;
16    
17    COMMIT;
18END //
19DELIMITER ;
20
21-- 调用存储过程
22CALL transfer_funds('ACC001', 'ACC002', 1000.00);

sql1START TRANSACTION;
2-- 批量更新库存
3UPDATE inventory SET stock = stock - 10 WHERE product_id = 'P001';
4-- 记录操作日志
5INSERT INTO operation_logs (product_id, change_type, quantity) 
6VALUES ('P001', 'OUT', 10);
7
8-- 检查库存是否充足
9SELECT stock FROM inventory WHERE product_id = 'P001';
10-- 若库存不足则回滚
11IF (SELECT stock FROM inventory WHERE product_id = 'P001') < 0 THEN
12    ROLLBACK;
13ELSE
14    COMMIT;
15END IF;

sql1CREATE INDEX idx_customer_region_order ON customers(region, last_order_date);
2-- 优化后的查询
3SELECT name, email FROM customers 
4WHERE region = 'APAC' AND last_order_date > '2025-01-01'
5ORDER BY last_order_date DESC;

sql1-- 创建旧版兼容用户
2CREATE USER 'legacy_app'@'%' IDENTIFIED WITH mysql_native_password BY 'LegacyPass123!';
3GRANT SELECT, INSERT ON legacy_db.* TO 'legacy_app'@'%';
4
5-- 创建新版安全用户
6CREATE USER 'modern_app'@'%' IDENTIFIED WITH caching_sha2_password BY 'ModernPass456!';
7GRANT ALL PRIVILEGES ON modern_db.* TO 'modern_app'@'%';
8
9-- 刷新权限
10FLUSH PRIVILEGES;

sql1UPDATE users 
2SET phone = TRIM(BOTH '-' FROM REPLACE(REPLACE(phone, '(', ''), ')', ''))
3WHERE phone LIKE '(%';
4
5-- 使用正则表达式提取区号
6SELECT 
7    phone,
8    REGEXP_SUBSTR(phone, '^\\d{3}') AS area_code,
9    SUBSTRING(phone, 4) AS local_number
10FROM users;

sql1SELECT 
2    product_id,
3    product_name,
4    sales,
5    RANK() OVER (ORDER BY sales DESC) AS sales_rank,
6    sales / SUM(sales) OVER () * 100 AS sales_percentage
7FROM products
8WHERE category = 'Electronics';