-- 创建数据库
CREATE DATABASE IF NOT EXISTS myapp
    DEFAULT CHARACTER SET utf8mb4
    DEFAULT COLLATE utf8mb4_0900_ai_ci;

-- 查看所有数据库
SHOW DATABASES;

-- 切换数据库
USE myapp;

-- 删除数据库（慎用！）
DROP DATABASE IF EXISTS myapp;

-- ===================== 创建表 =====================
CREATE TABLE IF NOT EXISTS users (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
    username    VARCHAR(50)  NOT NULL UNIQUE COMMENT '用户名',
    email       VARCHAR(100) NOT NULL COMMENT '邮箱',
    phone       VARCHAR(20)  DEFAULT NULL COMMENT '手机号',
    password    VARCHAR(255) NOT NULL COMMENT '密码哈希',
    status      TINYINT      DEFAULT 1 COMMENT '状态：1=正常，0=禁用',
    balance     DECIMAL(12,2) DEFAULT 0.00 COMMENT '账户余额',
    birth_date  DATE         DEFAULT NULL COMMENT '出生日期',
    avatar_url  VARCHAR(500) DEFAULT NULL COMMENT '头像URL',
    bio         TEXT         DEFAULT NULL COMMENT '个人简介',
    created_at  DATETIME     DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at  DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    
    -- 索引
    INDEX idx_email (email),
    INDEX idx_phone (phone),
    INDEX idx_status_created (status, created_at)
) ENGINE=InnoDB 
  DEFAULT CHARSET=utf8mb4 
  COLLATE=utf8mb4_0900_ai_ci
  COMMENT='用户表';

-- ===================== 查看表结构 =====================
DESC users;
-- 或
SHOW COLUMNS FROM users;

-- ===================== 查看建表语句 =====================
SHOW CREATE TABLE users\G

-- ===================== 修改表结构 =====================
-- 添加列
ALTER TABLE users ADD COLUMN age TINYINT UNSIGNED DEFAULT 0 COMMENT '年龄' AFTER username;

-- 修改列类型
ALTER TABLE users MODIFY COLUMN email VARCHAR(200) NOT NULL;

-- 修改列名和类型
ALTER TABLE users CHANGE COLUMN bio introduction TEXT DEFAULT NULL COMMENT '个人简介';

-- 删除列
ALTER TABLE users DROP COLUMN age;

-- 添加索引
ALTER TABLE users ADD INDEX idx_birth_date (birth_date);

-- 添加唯一索引
ALTER TABLE users ADD UNIQUE INDEX uk_phone (phone);

-- 删除索引
ALTER TABLE users DROP INDEX idx_birth_date;

-- 重命名表
ALTER TABLE users RENAME TO members;
-- 或
RENAME TABLE members TO users;

-- ===================== 删除表 =====================
DROP TABLE IF EXISTS users;

-- ===================== 清空表（保留结构） =====================
TRUNCATE TABLE users;
-- TRUNCATE vs DELETE：
-- TRUNCATE：DDL 操作，重置 AUTO_INCREMENT，不触发触发器，不可回滚
-- DELETE：DML 操作，逐行删除，触发触发器，可回滚

-- 创建临时表（会话结束后自动删除）
CREATE TEMPORARY TABLE tmp_active_users AS
    SELECT * FROM users WHERE status = 1;

-- 查看临时表
SELECT * FROM tmp_active_users;

-- 删除临时表
DROP TEMPORARY TABLE IF EXISTS tmp_active_users;

-- ===================== 基础插入 =====================
-- 插入单行
INSERT INTO users (username, email, phone, password, balance)
VALUES ('zhangsan', '[email protected]', '13800000001', SHA2('mypassword', 256), 100.00);

-- 插入多行
INSERT INTO users (username, email, password) VALUES
    ('lisi',   '[email protected]',   SHA2('password1', 256)),
    ('wangwu', '[email protected]', SHA2('password2', 256)),
    ('zhaoliu','[email protected]',SHA2('password3', 256));

-- ===================== INSERT ... ON DUPLICATE KEY UPDATE =====================
-- 如果存在（根据唯一键/主键判断）则更新，否则插入
INSERT INTO users (username, email, password)
VALUES ('zhangsan', '[email protected]', SHA2('newpw', 256))
ON DUPLICATE KEY UPDATE 
    email = VALUES(email),
    updated_at = CURRENT_TIMESTAMP;

-- MySQL 8.0.19+ 推荐语法：
INSERT INTO users (username, email, password)
VALUES ('zhangsan', '[email protected]', SHA2('newpw', 256))
AS new_row
ON DUPLICATE KEY UPDATE 
    email = new_row.email,
    updated_at = CURRENT_TIMESTAMP;

-- ===================== INSERT IGNORE =====================
-- 忽略重复键错误
INSERT IGNORE INTO users (username, email, password)
VALUES ('zhangsan', '[email protected]', SHA2('pw', 256));

-- ===================== REPLACE INTO =====================
-- 如果存在则先删除再插入（慎用：会改变主键 ID）
REPLACE INTO users (username, email, password)
VALUES ('zhangsan', '[email protected]', SHA2('pw', 256));

-- ===================== INSERT ... SELECT =====================
-- 从查询结果插入
CREATE TABLE users_backup LIKE users;
INSERT INTO users_backup SELECT * FROM users;

-- ❌ 低效：逐行插入
INSERT INTO users (username, email, password) VALUES ('a', '[email protected]', 'pw');
INSERT INTO users (username, email, password) VALUES ('b', '[email protected]', 'pw');
INSERT INTO users (username, email, password) VALUES ('c', '[email protected]', 'pw');

-- ✅ 高效：批量插入（每批 500-1000 行）
INSERT INTO users (username, email, password) VALUES
    ('a', '[email protected]', 'pw'),
    ('b', '[email protected]', 'pw'),
    ('c', '[email protected]', 'pw');

-- ===================== 基础更新 =====================
UPDATE users 
SET balance = balance + 100.00 
WHERE username = 'zhangsan';

-- ===================== 多列更新 =====================
UPDATE users 
SET email = '[email protected]',
    phone = '13900000000',
    updated_at = CURRENT_TIMESTAMP
WHERE id = 1;

-- ===================== 带 LIMIT 的更新 =====================
UPDATE users 
SET status = 0 
WHERE created_at < '2024-01-01' 
LIMIT 100;  -- 分批处理

-- ===================== 使用 JOIN 更新 =====================
-- 订单表关联用户表更新
UPDATE orders o
JOIN users u ON o.user_id = u.id
SET o.user_name = u.username
WHERE o.user_name IS NULL;

-- ===================== CASE WHEN 条件更新 =====================
UPDATE users
SET status = CASE
    WHEN last_login < DATE_SUB(NOW(), INTERVAL 1 YEAR) THEN 0  -- 超过1年未登录=禁用
    WHEN last_login < DATE_SUB(NOW(), INTERVAL 6 MONTH) THEN 2 -- 超过6月=不活跃
    ELSE 1  -- 正常
END
WHERE status != 0;  -- 排除已禁用的

-- ❌ 危险：直接执行不确定范围的 UPDATE
UPDATE users SET status = 0 WHERE created_at < '2024-01-01';

-- ✅ 安全：先 SELECT 确认范围
SELECT id, username, created_at FROM users WHERE created_at < '2024-01-01';
-- 确认无误后再执行 UPDATE

-- ===================== 基础删除 =====================
DELETE FROM users WHERE id = 100;

-- ===================== 带条件的批量删除 =====================
DELETE FROM users WHERE status = 0 AND created_at < '2023-01-01';

-- ===================== 分批删除（避免长事务和锁表） =====================
DELETE FROM users 
WHERE status = 0 AND created_at < '2023-01-01'
LIMIT 1000;
-- 循环执行直到影响行数为 0

-- ===================== 使用 JOIN 删除 =====================
DELETE u FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL AND u.created_at < '2023-01-01';
-- 删除没有订单的老用户

-- ===================== 软删除（推荐生产使用） =====================
-- 不真正删除数据，而是标记为已删除
UPDATE users SET deleted_at = NOW() WHERE id = 100;
-- 查询时排除已删除的
SELECT * FROM users WHERE deleted_at IS NULL;

-- 查询所有列
SELECT * FROM users LIMIT 10;

-- 查询指定列
SELECT id, username, email, created_at 
FROM users 
WHERE status = 1;

-- 别名
SELECT 
    username AS '用户名',
    email AS '邮箱',
    created_at AS '注册时间'
FROM users;

-- 去重
SELECT DISTINCT status FROM users;

-- 条件查询
SELECT * FROM users WHERE status = 1 AND balance > 0;
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;
SELECT * FROM users WHERE username IN ('zhangsan', 'lisi', 'wangwu');
SELECT * FROM users WHERE username NOT IN ('admin');
SELECT * FROM users WHERE balance BETWEEN 0 AND 1000;
SELECT * FROM users WHERE username LIKE 'zhang%';      -- 以 zhang 开头
SELECT * FROM users WHERE email LIKE '%@gmail.com';    -- 以 @gmail.com 结尾
SELECT * FROM users WHERE username LIKE '%san%';       -- 包含 san

-- 排序
SELECT * FROM users ORDER BY created_at DESC;           -- 按时间倒序
SELECT * FROM users ORDER BY balance ASC, id DESC;     -- 余额升序，ID 降序

-- 分页（OFFSET 方式）
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 0;     -- 第 1 页
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10;    -- 第 2 页
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;    -- 第 3 页

-- 深分页问题（OFFSET 很大时性能差）
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 100000;  -- ❌ 慢

-- ✅ 基于游标的分页（Keyset Pagination）
SELECT * FROM users WHERE id > 100000 ORDER BY id LIMIT 10;

-- 子查询方式（延迟关联）
SELECT u.* FROM users u
INNER JOIN (SELECT id FROM users ORDER BY id LIMIT 10 OFFSET 100000) t
ON u.id = t.id;

-- 常用聚合函数
SELECT 
    COUNT(*)           AS total_users,         -- 总行数（包含 NULL）
    COUNT(phone)       AS users_with_phone,    -- phone 不为 NULL 的行数
    COUNT(DISTINCT status) AS status_count,    -- 去重后状态数
    SUM(balance)       AS total_balance,       -- 余额总和
    AVG(balance)       AS avg_balance,         -- 平均余额
    MAX(balance)       AS max_balance,         -- 最大余额
    MIN(balance)       AS min_balance,         -- 最小余额
    MAX(created_at)    AS latest_register,     -- 最新注册时间
    MIN(created_at)    AS earliest_register    -- 最早注册时间
FROM users
WHERE status = 1;

-- GROUP BY 分组统计
SELECT 
    status,
    COUNT(*)           AS user_count,
    AVG(balance)       AS avg_balance,
    SUM(balance)       AS total_balance
FROM users
GROUP BY status;

-- 多列分组
SELECT 
    status,
    DATE(created_at)   AS register_date,
    COUNT(*)           AS daily_count
FROM users
GROUP BY status, DATE(created_at)
ORDER BY register_date DESC;

-- HAVING 过滤分组结果
SELECT 
    status,
    COUNT(*)           AS user_count,
    AVG(balance)       AS avg_balance
FROM users
GROUP BY status
HAVING user_count > 10        -- 用户数 > 10
   AND avg_balance > 100;    -- 平均余额 > 100

-- ===================== 字符串函数 =====================
SELECT CONCAT('Hello', ' ', 'MySQL');          -- 拼接: 'Hello MySQL'
SELECT CONCAT_WS('-', '2026', '05', '10');     -- 带分隔符拼接: '2026-05-10'
SELECT LENGTH('Hello');                         -- 字节长度: 5
SELECT CHAR_LENGTH('你好');                      -- 字符长度: 2
SELECT UPPER('hello');                          -- 转大写: 'HELLO'
SELECT LOWER('HELLO');                          -- 转小写: 'hello'
SELECT TRIM('  hello  ');                       -- 去除首尾空格
SELECT SUBSTRING('Hello MySQL', 7);             -- 截取: 'MySQL'
SELECT SUBSTRING('Hello MySQL', 1, 5);          -- 截取: 'Hello'
SELECT REPLACE('Hello World', 'World', 'MySQL');-- 替换: 'Hello MySQL'
SELECT LEFT('Hello', 3);                        -- 左截取: 'Hel'
SELECT RIGHT('Hello', 3);                       -- 右截取: 'llo'

-- ===================== 数值函数 =====================
SELECT ABS(-100);           -- 绝对值: 100
SELECT CEIL(3.14);          -- 向上取整: 4
SELECT FLOOR(3.99);         -- 向下取整: 3
SELECT ROUND(3.1415, 2);    -- 四舍五入: 3.14
SELECT TRUNCATE(3.1415, 2); -- 截断: 3.14
SELECT MOD(10, 3);          -- 取余: 1
SELECT RAND();              -- 随机数 0~1
SELECT POWER(2, 10);        -- 幂运算: 1024

-- ===================== 日期时间函数 =====================
SELECT NOW();                           -- 当前时间: 2026-05-10 14:30:00
SELECT CURDATE();                       -- 当前日期: 2026-05-10
SELECT CURTIME();                       -- 当前时间: 14:30:00
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');  -- 格式化: '2026-05-10'
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 %H:%i:%s'); -- '2026年05月10日 14:30:00'
SELECT DATEDIFF('2026-12-31', '2026-01-01');       -- 日期差: 364
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);             -- 7天后
SELECT DATE_SUB(NOW(), INTERVAL 30 DAY);            -- 30天前
SELECT UNIX_TIMESTAMP(NOW());                       -- Unix 时间戳
SELECT FROM_UNIXTIME(1746929400);                   -- 时间戳转日期
SELECT YEAR(NOW());     -- 年
SELECT MONTH(NOW());    -- 月
SELECT DAY(NOW());      -- 日
SELECT HOUR(NOW());     -- 时
SELECT DAYOFWEEK(NOW());-- 星期几（1=周日，2=周一...）
SELECT WEEKDAY(NOW());  -- 星期几（0=周一，1=周二...）

-- ===================== 条件函数 =====================
SELECT IF(1 > 0, '是', '否');                    -- '是'
SELECT IFNULL(NULL, '默认值');                    -- '默认值'
SELECT NULLIF(10, 10);                           -- NULL（相等时返回 NULL）
SELECT COALESCE(NULL, NULL, '第三个值', '第四个值'); -- '第三个值'

-- CASE WHEN
SELECT 
    username,
    balance,
    CASE
        WHEN balance >= 10000 THEN 'VIP'
        WHEN balance >= 1000  THEN '高级'
        WHEN balance >= 100   THEN '普通'
        ELSE '低'
    END AS level
FROM users;

-- ===================== JSON 函数 =====================
SELECT JSON_OBJECT('name', 'zhangsan', 'age', 25);
-- {"name": "zhangsan", "age": 25}

SELECT JSON_EXTRACT('{"name":"zhangsan","age":25}', '$.name');
-- "zhangsan"

-- 简写语法
SELECT '{"name":"zhangsan","age":25}'->'$.name';
-- "zhangsan"

SELECT '{"name":"zhangsan","age":25}'->>'$.name';
-- zhangsan（去除引号）

-- ===================== 电商订单表完整示例 =====================
CREATE TABLE IF NOT EXISTS orders (
    -- 主键
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '订单ID',
    order_no        VARCHAR(32)    NOT NULL UNIQUE COMMENT '订单编号',
    
    -- 业务字段
    user_id         BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
    total_amount    DECIMAL(12,2)  NOT NULL DEFAULT 0.00 COMMENT '订单总金额',
    pay_amount      DECIMAL(12,2)  NOT NULL DEFAULT 0.00 COMMENT '实付金额',
    discount_amount DECIMAL(12,2)  DEFAULT 0.00 COMMENT '优惠金额',
    freight_amount  DECIMAL(10,2)  DEFAULT 0.00 COMMENT '运费',
    
    -- 状态字段
    status          TINYINT        NOT NULL DEFAULT 0 COMMENT '订单状态：0=待支付 1=已支付 2=已发货 3=已完成 4=已取消 5=已退款',
    pay_type        TINYINT        DEFAULT NULL COMMENT '支付方式：1=微信 2=支付宝 3=银行卡',
    
    -- 时间字段
    pay_time        DATETIME       DEFAULT NULL COMMENT '支付时间',
    ship_time       DATETIME       DEFAULT NULL COMMENT '发货时间',
    receive_time    DATETIME       DEFAULT NULL COMMENT '收货时间',
    cancel_time     DATETIME       DEFAULT NULL COMMENT '取消时间',
    
    -- 收货信息
    receiver_name   VARCHAR(50)    DEFAULT NULL COMMENT '收货人姓名',
    receiver_phone  VARCHAR(20)    DEFAULT NULL COMMENT '收货人电话',
    receiver_address VARCHAR(500)  DEFAULT NULL COMMENT '收货地址',
    
    -- 备注
    remark          VARCHAR(500)   DEFAULT NULL COMMENT '订单备注',
    internal_note   VARCHAR(500)   DEFAULT NULL COMMENT '内部备注',
    
    -- 审计字段
    created_at      DATETIME       DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at      DATETIME       DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    
    -- 索引
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at),
    INDEX idx_user_status (user_id, status)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_0900_ai_ci
  COMMENT='订单表';

-- 插入测试数据
INSERT INTO orders (order_no, user_id, total_amount, pay_amount, status, pay_type, pay_time, receiver_name, receiver_phone, receiver_address) VALUES
('ORD202605100001', 1, 299.00, 279.00, 3, 1, NOW(), '张三', '13800000001', '北京市朝阳区xxx'),
('ORD202605100002', 2, 59.90, 59.90, 1, 2, NOW(), '李四', '13800000002', '上海市浦东新区xxx'),
('ORD202605100003', 1, 1299.00, 1199.00, 0, NULL, NULL, '张三', '13800000001', '北京市朝阳区xxx');

-- 查询某用户的订单
SELECT order_no, total_amount, status, created_at
FROM orders
WHERE user_id = 1
ORDER BY created_at DESC;

-- 使用存储过程批量插入
DELIMITER //
CREATE PROCEDURE generate_test_users(IN num INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= num DO
        INSERT INTO users (username, email, password, balance)
        VALUES (
            CONCAT('user_', LPAD(i, 6, '0')),
            CONCAT('user_', i, '@test.com'),
            SHA2(CONCAT('password', i), 256),
            ROUND(RAND() * 10000, 2)
        );
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

-- 生成 1000 个测试用户
CALL generate_test_users(1000);

-- 创建历史表（结构相同）
CREATE TABLE orders_archive LIKE orders;

-- 分批迁移
INSERT INTO orders_archive
SELECT * FROM orders
WHERE status IN (3, 4)  -- 已完成或已取消
  AND created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR)
LIMIT 5000;

-- 确认迁移完成后删除原数据
DELETE FROM orders
WHERE status IN (3, 4)
  AND created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR)
LIMIT 5000;