MySQL常用指令

第1部分：数据定义与基础操作

1.1 表结构设计与创建

-- 核心玩家表结构（包含业务所需的所有基础字段）
CREATE TABLE `player` (
    `player_id` INT PRIMARY KEY AUTO_INCREMENT COMMENT '玩家唯一ID',
    `username` VARCHAR(50) UNIQUE NOT NULL COMMENT '登录账号',
    `password_hash` VARCHAR(100) NOT NULL COMMENT '加密密码',
    `nickname` VARCHAR(50) NOT NULL COMMENT '显示昵称',
    `email` VARCHAR(100) UNIQUE COMMENT '绑定邮箱',
    `level` INT DEFAULT 1 COMMENT '玩家等级',
    `experience` BIGINT DEFAULT 0 COMMENT '当前经验值',
    `vip_level` TINYINT DEFAULT 0 COMMENT 'VIP等级',
    `diamonds` INT DEFAULT 0 COMMENT '钻石（充值货币）',
    `gold_coins` BIGINT DEFAULT 1000 COMMENT '金币（游戏货币）',
    `attack_power` INT DEFAULT 10 COMMENT '攻击力',
    `defense_power` INT DEFAULT 5 COMMENT '防御力',
    `max_hp` INT DEFAULT 100 COMMENT '最大生命值',
    `current_hp` INT DEFAULT 100 COMMENT '当前生命值',
    `last_login_ip` VARCHAR(45) COMMENT '最后登录IP',
    `last_login_time` DATETIME COMMENT '最后登录时间',
    `created_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
    `is_banned` TINYINT(1) DEFAULT 0 COMMENT '是否封禁',
    `ban_reason` TEXT COMMENT '封禁原因',
    INDEX `idx_level` (`level`),
    INDEX `idx_vip` (`vip_level`),
    INDEX `idx_login_time` (`last_login_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='玩家主表';

1.2 基础数据操作（CRUD）

-- 1. 玩家注册：插入新玩家数据
INSERT INTO `player` (`username`, `password_hash`, `nickname`, `email`) 
VALUES ('player001', 'a1b2c3d4e5', '冒险者小明', 'xiaoming@example.com');

-- 2. 批量插入测试数据（用于开发环境）
INSERT INTO `player` (`username`, `password_hash`, `nickname`, `level`, `vip_level`, `gold_coins`) VALUES
('player002', 'hash002', '战士阿强', 15, 2, 50000),
('player003', 'hash003', '法师小美', 25, 5, 120000),
('player004', 'hash004', '游侠老张', 8, 0, 8000);

-- 3. 玩家登录验证：精确查询
SELECT `player_id`, `nickname`, `level`, `vip_level` 
FROM `player` 
WHERE `username` = 'player001' 
  AND `password_hash` = 'a1b2c3d4e5'
  AND `is_banned` = 0;

-- 4. 玩家信息更新
-- a. 等级提升
UPDATE `player` 
SET `level` = `level` + 1, 
    `experience` = `experience` - 1000,
    `attack_power` = `attack_power` + 5,
    `defense_power` = `defense_power` + 3
WHERE `player_id` = 1 
  AND `experience` >= 1000;

-- b. 货币变动（原子操作，避免并发问题）
UPDATE `player` 
SET `gold_coins` = `gold_coins` - 500,
    `diamonds` = `diamonds` + 50
WHERE `player_id` = 1 
  AND `gold_coins` >= 500;

-- 5. 账号封禁/解封
UPDATE `player` 
SET `is_banned` = 1, 
    `ban_reason` = '使用外挂程序'
WHERE `player_id` = 4;

-- 6. 删除测试数据（实际生产环境极少物理删除）
DELETE FROM `player` 
WHERE `player_id` = 4 
  AND `level` < 10 
  AND `last_login_time` < '2024-01-01';

第2部分：数据查询与分析

2.1 基础查询与筛选

-- 1. 精确查询：查看特定玩家完整信息
SELECT * FROM `player` WHERE `player_id` = 1;

-- 2. 条件组合：查找高等级VIP玩家
SELECT `player_id`, `nickname`, `level`, `vip_level`, `gold_coins`
FROM `player`
WHERE `level` >= 20 
  AND `vip_level` >= 3
  AND `is_banned` = 0
ORDER BY `level` DESC, `vip_level` DESC;

-- 3. 模糊查询：按昵称搜索玩家
SELECT `player_id`, `nickname`, `level`
FROM `player`
WHERE `nickname` LIKE '%冒险%'
  AND `is_banned` = 0
LIMIT 20;

-- 4. 范围查询：查找最近活跃玩家
SELECT `player_id`, `nickname`, `last_login_time`
FROM `player`
WHERE `last_login_time` >= DATE_SUB(NOW(), INTERVAL 7 DAY)
  AND `is_banned` = 0
ORDER BY `last_login_time` DESC;

-- 5. IN查询：批量查询特定玩家
SELECT `player_id`, `nickname`, `level`
FROM `player`
WHERE `player_id` IN (1, 3, 5, 7, 9)
ORDER BY FIELD(`player_id`, 1, 3, 5, 7, 9);

2.2 聚合分析与统计

-- 1. 基础统计：玩家总数、平均等级等
SELECT 
    COUNT(*) AS `total_players`,
    COUNT(CASE WHEN `is_banned` = 1 THEN 1 END) AS `banned_players`,
    AVG(`level`) AS `avg_level`,
    MAX(`level`) AS `max_level`,
    MIN(`level`) AS `min_level`,
    SUM(`gold_coins`) AS `total_gold`
FROM `player`;

-- 2. 分组统计：各等级段玩家分布
SELECT 
    CASE 
        WHEN `level` BETWEEN 1 AND 10 THEN '1-10级'
        WHEN `level` BETWEEN 11 AND 20 THEN '11-20级'
        WHEN `level` BETWEEN 21 AND 30 THEN '21-30级'
        ELSE '30级以上'
    END AS `level_range`,
    COUNT(*) AS `player_count`,
    AVG(`gold_coins`) AS `avg_gold`,
    SUM(`gold_coins`) AS `total_gold`
FROM `player`
WHERE `is_banned` = 0
GROUP BY `level_range`
ORDER BY MIN(`level`);

-- 3. VIP等级分析
SELECT 
    `vip_level`,
    COUNT(*) AS `player_count`,
    AVG(`level`) AS `avg_level`,
    AVG(`gold_coins`) AS `avg_gold`,
    SUM(`diamonds`) AS `total_diamonds`
FROM `player`
WHERE `is_banned` = 0
GROUP BY `vip_level`
HAVING `player_count` > 0  -- HAVING对分组结果筛选
ORDER BY `vip_level`;

-- 4. 活跃度分析（按最后登录时间分组）
SELECT 
    DATE(`last_login_time`) AS `login_date`,
    COUNT(*) AS `active_players`,
    AVG(`level`) AS `avg_level`
FROM `player`
WHERE `last_login_time` >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY DATE(`last_login_time`)
ORDER BY `login_date` DESC;

2.3 高级查询技巧

-- 1. 分页查询：玩家排行榜（效率优化版）
SELECT `player_id`, `nickname`, `level`, `vip_level`, `gold_coins`
FROM `player`
WHERE `is_banned` = 0
ORDER BY `level` DESC, `vip_level` DESC
LIMIT 20 OFFSET 0;  -- 第一页：OFFSET 0
-- 第二页：LIMIT 20 OFFSET 20

-- 2. 子查询：查找高于平均等级的玩家
SELECT `player_id`, `nickname`, `level`
FROM `player`
WHERE `level` > (SELECT AVG(`level`) FROM `player` WHERE `is_banned` = 0)
  AND `is_banned` = 0
ORDER BY `level` DESC;

-- 3. 自连接：查找相似等级的玩家（匹配系统）
SELECT 
    a.`player_id` AS `player_a_id`,
    a.`nickname` AS `player_a_name`,
    a.`level` AS `player_a_level`,
    b.`player_id` AS `player_b_id`,
    b.`nickname` AS `player_b_name`,
    b.`level` AS `player_b_level`
FROM `player` a
JOIN `player` b ON a.`player_id` < b.`player_id`  -- 避免重复配对
WHERE ABS(a.`level` - b.`level`) <= 3  -- 等级差不超过3级
  AND a.`is_banned` = 0
  AND b.`is_banned` = 0
LIMIT 10;

第3部分：多表关联实战

3.1 关联表设计与创建

-- 玩家背包表（与player表关联）
CREATE TABLE `player_inventory` (
    `inventory_id` INT PRIMARY KEY AUTO_INCREMENT,
    `player_id` INT NOT NULL,
    `item_id` INT NOT NULL,
    `item_type` VARCHAR(50) NOT NULL COMMENT '装备/消耗品/材料',
    `item_name` VARCHAR(100) NOT NULL,
    `quantity` INT DEFAULT 1,
    `equipped` TINYINT(1) DEFAULT 0 COMMENT '是否装备中',
    `acquired_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`player_id`) REFERENCES `player`(`player_id`) ON DELETE CASCADE,
    INDEX `idx_player_item` (`player_id`, `item_id`),
    INDEX `idx_player_equipped` (`player_id`, `equipped`)
) COMMENT='玩家背包物品表';

-- 玩家任务表
CREATE TABLE `player_quests` (
    `record_id` INT PRIMARY KEY AUTO_INCREMENT,
    `player_id` INT NOT NULL,
    `quest_id` INT NOT NULL,
    `quest_name` VARCHAR(100) NOT NULL,
    `status` ENUM('未开始','进行中','已完成','已奖励') DEFAULT '未开始',
    `progress` INT DEFAULT 0,
    `total_required` INT NOT NULL,
    `started_time` DATETIME,
    `completed_time` DATETIME,
    FOREIGN KEY (`player_id`) REFERENCES `player`(`player_id`) ON DELETE CASCADE,
    UNIQUE KEY `uq_player_quest` (`player_id`, `quest_id`),
    INDEX `idx_player_status` (`player_id`, `status`)
) COMMENT='玩家任务进度表';

3.2 关联查询实战

-- 1. INNER JOIN：查询玩家及其背包物品
SELECT 
    p.`nickname`,
    p.`level`,
    i.`item_name`,
    i.`quantity`,
    i.`equipped`
FROM `player` p
INNER JOIN `player_inventory` i ON p.`player_id` = i.`player_id`
WHERE p.`player_id` = 1
  AND p.`is_banned` = 0
ORDER BY i.`equipped` DESC, i.`acquired_time` DESC;

-- 2. LEFT JOIN：查询所有玩家（即使没有物品）
SELECT 
    p.`player_id`,
    p.`nickname`,
    COUNT(i.`inventory_id`) AS `item_count`,
    SUM(i.`quantity`) AS `total_items`
FROM `player` p
LEFT JOIN `player_inventory` i ON p.`player_id` = i.`player_id`
WHERE p.`is_banned` = 0
GROUP BY p.`player_id`, p.`nickname`
HAVING `item_count` > 0
ORDER BY `total_items` DESC;

-- 3. 多表JOIN：玩家+任务+背包物品
SELECT 
    p.`nickname`,
    q.`quest_name`,
    q.`status`,
    q.`progress`,
    q.`total_required`,
    i.`item_name` AS `required_item`,
    i.`quantity` AS `item_quantity`
FROM `player` p
INNER JOIN `player_quests` q ON p.`player_id` = q.`player_id`
LEFT JOIN `player_inventory` i ON p.`player_id` = i.`player_id` 
    AND i.`item_name` = q.`quest_name`  -- 假设任务需要特定物品
WHERE p.`player_id` = 1
  AND q.`status` = '进行中'
ORDER BY q.`started_time` DESC;

-- 4. 子查询作为关联条件：查找拥有特定稀有物品的玩家
SELECT 
    p.`player_id`,
    p.`nickname`,
    p.`level`,
    p.`vip_level`,
    (SELECT GROUP_CONCAT(`item_name`) 
     FROM `player_inventory` 
     WHERE `player_id` = p.`player_id` 
       AND `item_type` = '传奇装备'
    ) AS `legendary_items`
FROM `player` p
WHERE EXISTS (
    SELECT 1 
    FROM `player_inventory` i 
    WHERE i.`player_id` = p.`player_id` 
      AND i.`item_type` = '传奇装备'
      AND i.`equipped` = 1
)
AND p.`is_banned` = 0
ORDER BY p.`level` DESC;

3.3 联合查询与临时结果

-- 1. UNION：合并不同条件的查询结果
-- 查找高等级玩家 或 高VIP玩家（去重）
SELECT `player_id`, `nickname`, `level`, `vip_level`, '高等级' AS `category`
FROM `player`
WHERE `level` >= 30 AND `is_banned` = 0
UNION
SELECT `player_id`, `nickname`, `level`, `vip_level`, '高VIP' AS `category`
FROM `player`
WHERE `vip_level` >= 5 AND `is_banned` = 0
ORDER BY `level` DESC, `vip_level` DESC;

-- 2. 使用派生表（子查询作为临时表）
SELECT 
    t.`player_id`,
    t.`nickname`,
    t.`total_gold`,
    t.`item_count`,
    ROUND(t.`total_gold` / NULLIF(t.`item_count`, 0), 2) AS `gold_per_item`
FROM (
    SELECT 
        p.`player_id`,
        p.`nickname`,
        p.`gold_coins` AS `total_gold`,
        COUNT(i.`inventory_id`) AS `item_count`
    FROM `player` p
    LEFT JOIN `player_inventory` i ON p.`player_id` = i.`player_id`
    WHERE p.`is_banned` = 0
    GROUP BY p.`player_id`, p.`nickname`, p.`gold_coins`
) t
WHERE t.`item_count` > 0
ORDER BY `gold_per_item` DESC;

第4部分：数据维护与优化

4.1 表结构维护

-- 1. 增加新字段（如添加手机号字段）
ALTER TABLE `player`
ADD COLUMN `phone_number` VARCHAR(20) UNIQUE COMMENT '绑定手机号' AFTER `email`,
ADD INDEX `idx_phone` (`phone_number`);

-- 2. 修改字段属性
ALTER TABLE `player`
MODIFY COLUMN `gold_coins` BIGINT DEFAULT 0 COMMENT '游戏金币',
MODIFY COLUMN `email` VARCHAR(150) UNIQUE COMMENT '电子邮箱';

-- 3. 添加复合索引优化查询
ALTER TABLE `player`
ADD INDEX `idx_level_vip` (`level`, `vip_level`),
ADD INDEX `idx_login_status` (`last_login_time`, `is_banned`);

-- 4. 删除冗余索引
ALTER TABLE `player`
DROP INDEX `idx_level`,  -- 如果idx_level_vip已经够用
DROP INDEX `idx_vip`;

4.2 数据维护操作

-- 1. 批量更新：为所有活跃玩家发放奖励
UPDATE `player`
SET `gold_coins` = `gold_coins` + 1000,
    `diamonds` = `diamonds` + 50
WHERE `last_login_time` >= DATE_SUB(NOW(), INTERVAL 3 DAY)
  AND `is_banned` = 0;

-- 2. 数据归档：将久未登录玩家标记为不活跃
UPDATE `player`
SET `current_hp` = `max_hp`  -- 恢复满血状态
WHERE `last_login_time` < DATE_SUB(NOW(), INTERVAL 180 DAY)
  AND `is_banned` = 0
  AND `current_hp` < `max_hp`;

-- 3. 数据清理：删除测试账号
DELETE FROM `player`
WHERE `username` LIKE 'test%'
  AND `last_login_time` < DATE_SUB(NOW(), INTERVAL 30 DAY);

-- 4. 使用事务保证数据一致性（在Java中更重要）
START TRANSACTION;

UPDATE `player`
SET `gold_coins` = `gold_coins` - 1000
WHERE `player_id` = 1 AND `gold_coins` >= 1000;

INSERT INTO `player_inventory` (`player_id`, `item_id`, `item_name`, `quantity`)
VALUES (1, 1001, '传奇武器', 1);

COMMIT;  -- 如果任何一步失败，执行ROLLBACK

4.3 性能优化查询

-- 1. 使用EXPLAIN分析查询性能
EXPLAIN SELECT * FROM `player` WHERE `level` BETWEEN 20 AND 30;

-- 2. 强制使用特定索引
SELECT `player_id`, `nickname`, `level`
FROM `player` FORCE INDEX (`idx_level_vip`)
WHERE `level` >= 25
  AND `vip_level` >= 3
ORDER BY `level` DESC
LIMIT 100;

-- 3. 避免SELECT *，只选择需要的列
SELECT `player_id`, `nickname`, `level`, `vip_level`, `last_login_time`
FROM `player`
WHERE `is_banned` = 0
  AND `last_login_time` >= DATE_SUB(NOW(), INTERVAL 1 DAY);

-- 4. 分页优化：使用WHERE代替OFFSET（大数据量时）
-- 传统方式（大数据量时慢）：
SELECT * FROM `player` ORDER BY `player_id` LIMIT 20 OFFSET 100000;

-- 优化方式：
SELECT * FROM `player` 
WHERE `player_id` > 100000  -- 上次查询的最后一个ID
ORDER BY `player_id` 
LIMIT 20;