MySQL企业开发中高频使用语句

以下是企业级MySQL开发中高频使用的语句分类及示例，结合典型业务场景说明：

一、数据定义（DDL）

1. 表结构管理

   -- 创建用户表（含索引优化）
   CREATE TABLE user (
     id INT PRIMARY KEY AUTO_INCREMENT,
     name VARCHAR(50) NOT NULL COMMENT '用户名',
     email VARCHAR(100) UNIQUE COMMENT '邮箱',
     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
   

   用途：定义核心业务表结构，包含主键、约束和存储引擎选择

2. 动态修改表结构

   -- 添加索引与字段扩展 
   ALTER TABLE user 
     ADD INDEX idx_name (name),
     ADD COLUMN phone VARCHAR(20) AFTER email;
   

   场景：应对业务需求变更时的快速表结构调整

二、数据操作（DML）

1. 批量插入与更新

   -- 从临时表导入数据 
   INSERT INTO order_detail (product_id, quantity)
   SELECT product_id, SUM(qty) FROM temp_order GROUP BY product_id;
   
   -- 关联更新用户状态 
   UPDATE user u 
   JOIN user_status s ON u.id = s.user_id 
   SET u.is_active = 1 
   WHERE s.last_login > '2025-01-01';
   

   用途：数据迁移与批量业务状态更新

2. 条件删除

   -- 清理无效数据 
   DELETE FROM log 
   WHERE created_at < '2024-01-01' 
   LIMIT 1000;
   

   优化点：限制删除条数防止事务锁表

三、数据查询（DQL）

1. 多表关联与聚合

   -- 统计部门销售额 
   SELECT d.dept_name, SUM(o.amount) AS total_sales 
   FROM department d 
   LEFT JOIN orders o ON d.id = o.dept_id 
   GROUP BY d.dept_name 
   HAVING total_sales > 100000;
   

   场景：生成业务报表

2. 分页优化查询

   -- 使用覆盖索引加速分页 
   SELECT id, name 
   FROM user 
   WHERE created_at > '2025-01-01'
   ORDER BY id DESC 
   LIMIT 100000, 20;
   

   技巧：避免OFFSET过大导致性能问题

四、权限与安全管理

1. 用户权限控制

   -- 创建只读账户 
   CREATE USER 'report_user'@'%' IDENTIFIED BY 'SecurePass123!';
   GRANT SELECT ON sales_db.* TO 'report_user'@'%';
   FLUSH PRIVILEGES;
   

   安全实践：最小权限原则

2. 密码策略更新

   -- 修改用户密码并设置过期 
   ALTER USER 'admin'@'localhost' 
     IDENTIFIED BY 'NewPass!2025' 
     PASSWORD EXPIRE INTERVAL 90 DAY;
   

   合规要求：满足企业安全审计标准

五、高级处理

1. 数据聚合与格式转换

   -- 将多行合并为字符串（如标签系统）
   SELECT product_id, 
     GROUP_CONCAT(category_name SEPARATOR '|') AS categories 
   FROM product_category 
   GROUP BY product_id;
   

   应用场景：前端展示或数据导出

2. 事务控制

   START TRANSACTION;
   UPDATE account SET balance = balance - 500 WHERE user_id = 1001;
   UPDATE account SET balance = balance + 500 WHERE user_id = 1002;
   COMMIT;
   

   关键点：保证金融类业务原子性

高频优化技巧

• 索引分析：EXPLAIN SELECT ... 查看执行计划
• 慢查询日志：SET GLOBAL slow_query_log = ON;
• 连接池配置：SHOW STATUS LIKE 'Threads_connected';