-- 创建数据库（如果不存在）
CREATE DATABASE IF NOT EXISTS wladmin
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

-- 使用数据库
USE wladmin;

-- 查看数据库字符集
SHOW CREATE DATABASE wladmin;

-- 修改数据库字符集（谨慎使用，会影响已存在的数据）
ALTER DATABASE wladmin CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 创建表（项目标准格式）
CREATE TABLE user (
    id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
    username VARCHAR(50) NOT NULL COMMENT '用户名',
    name varchar(100) NOT NULL DEFAULT '' COMMENT '姓名',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';

-- 修改表的字符集和排序规则
ALTER TABLE user CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 查看表的字符集
SHOW CREATE TABLE user;

-- 查看表的字符集信息
SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'wladmin'
  AND TABLE_NAME = 'user';

-- 主键ID（推荐使用无符号）
id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID'

-- 外键ID
user_id bigint(20) unsigned COMMENT '用户ID'
department_id bigint(20) unsigned COMMENT '部门ID'

-- 状态、类型、排序等
status int(11) NOT NULL DEFAULT '0' COMMENT '状态'
sort int(11) NOT NULL DEFAULT '1' COMMENT '排序号'
level int(11) NOT NULL DEFAULT '1' COMMENT '层级'

-- 布尔值、状态标记（常用）
is_delete tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否删除 1：已删除；0：未删除'
is_system TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否系统预置(0-否,1-是)'
status tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否启用 1：停用；0：启用'
gender TINYINT(1) DEFAULT 1 COMMENT '性别 1：男；0：女, 2: 其他'

-- 用户名、编码（短字符串）
username VARCHAR(50) NOT NULL COMMENT '用户名'
code VARCHAR(64) COMMENT '编码'

-- 姓名、标题（中等长度）
name varchar(100) NOT NULL DEFAULT '' COMMENT '姓名'
title VARCHAR(255) NOT NULL COMMENT '标题'

-- 地址、描述（较长字符串）
address VARCHAR(255) DEFAULT '' COMMENT '用户地址'
description VARCHAR(500) DEFAULT '' COMMENT '描述'

-- 路径、JSON字符串（超长字符串）
path VARCHAR(2500) DEFAULT '' COMMENT '树id的路径'

-- 固定长度字符串（较少使用）
status_code CHAR(2) COMMENT '状态码'

-- 内容、备注（大文本）
content MEDIUMTEXT NOT NULL COMMENT '内容'
remark VARCHAR(500) NULL COMMENT '备注信息'
change_summary text DEFAULT NULL COMMENT '变更摘要'

-- 创建时间、更新时间（推荐）
created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间'
operation_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间'

-- 生效时间、过期时间
effective_time DATETIME NULL COMMENT '生效时间'
expire_time DATETIME NULL COMMENT '过期时间'
published_at DATETIME NULL COMMENT '发布时间'

-- 生日、日期
birthday DATE DEFAULT NULL COMMENT '生日'

-- 较少使用，建议使用 DATETIME
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'

-- JSON 数据（MySQL 5.7.8+）
before_data json DEFAULT NULL COMMENT '操作前数据(JSON格式)'
after_data json DEFAULT NULL COMMENT '操作后数据(JSON格式)'
related_api_permissions JSON DEFAULT NULL COMMENT '关联的API权限编码(JSON数组)'

-- 枚举类型
token_type ENUM('access', 'refresh') NOT NULL COMMENT 'token类型'
client_type ENUM('web', 'app', 'wx') NOT NULL DEFAULT 'web' COMMENT '客户端类型'
category VARCHAR(20) NOT NULL COMMENT '通知公告类别（announcement:公告, notice:通知）'

-- 地理位置坐标（用于地图展示）
location_point GEOMETRY DEFAULT NULL COMMENT '地理位置坐标（用于地图展示）'

-- 精度要求高的数值（较少使用）
price DECIMAL(10, 2) COMMENT '价格'

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

-- 查看当前数据库
SELECT DATABASE();

-- 查看数据库字符集
SHOW CREATE DATABASE wladmin;

-- 查看数据库详细信息
SELECT
    SCHEMA_NAME AS '数据库名',
    DEFAULT_CHARACTER_SET_NAME AS '字符集',
    DEFAULT_COLLATION_NAME AS '排序规则'
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = 'wladmin';

-- 查看所有表
SHOW TABLES;

-- 查看表结构
DESC user;
DESCRIBE user;

-- 查看建表语句
SHOW CREATE TABLE user;

-- 查看表的详细信息
SELECT
    TABLE_NAME AS '表名',
    TABLE_COLLATION AS '排序规则',
    TABLE_COMMENT AS '表注释',
    ENGINE AS '存储引擎'
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'wladmin'
  AND TABLE_NAME = 'user';

-- 查看表的所有字段
SHOW COLUMNS FROM user;
SHOW FULL COLUMNS FROM user;  -- 包含更多信息（排序规则等）

-- 查看字段详细信息（推荐）
SELECT
    COLUMN_NAME AS '字段名',
    COLUMN_TYPE AS '字段类型',
    IS_NULLABLE AS '是否为空',
    COLUMN_DEFAULT AS '默认值',
    COLUMN_KEY AS '键类型',
    EXTRA AS '额外信息',
    COLUMN_COMMENT AS '字段注释',
    CHARACTER_SET_NAME AS '字符集',
    COLLATION_NAME AS '排序规则'
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'wladmin'
  AND TABLE_NAME = 'user'
ORDER BY ORDINAL_POSITION;

-- 查看特定字段
SELECT
    COLUMN_NAME,
    COLUMN_TYPE,
    COLUMN_COMMENT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'wladmin'
  AND TABLE_NAME = 'user'
  AND COLUMN_NAME IN ('id', 'username', 'name');

-- 查看表的所有索引
SHOW INDEX FROM user;
SHOW INDEXES FROM user;
SHOW KEYS FROM user;

-- 查看索引详细信息（推荐）
SELECT
    INDEX_NAME AS '索引名',
    COLUMN_NAME AS '字段名',
    SEQ_IN_INDEX AS '索引顺序',
    NON_UNIQUE AS '是否非唯一',
    INDEX_TYPE AS '索引类型',
    INDEX_COMMENT AS '索引注释'
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'wladmin'
  AND TABLE_NAME = 'user'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

-- 查看主键
SELECT
    COLUMN_NAME AS '主键字段'
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'wladmin'
  AND TABLE_NAME = 'user'
  AND CONSTRAINT_NAME = 'PRIMARY';

-- 查看表的外键
SELECT
    CONSTRAINT_NAME AS '外键名',
    TABLE_NAME AS '表名',
    COLUMN_NAME AS '字段名',
    REFERENCED_TABLE_NAME AS '引用表名',
    REFERENCED_COLUMN_NAME AS '引用字段名',
    UPDATE_RULE AS '更新规则',
    DELETE_RULE AS '删除规则'
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'wladmin'
  AND TABLE_NAME = 'user'
  AND REFERENCED_TABLE_NAME IS NOT NULL;

-- 查看所有外键约束（推荐）
SELECT
    CONSTRAINT_NAME AS '外键名',
    TABLE_NAME AS '表名',
    COLUMN_NAME AS '字段名',
    REFERENCED_TABLE_NAME AS '引用表名',
    REFERENCED_COLUMN_NAME AS '引用字段名'
FROM information_schema.REFERENTIAL_CONSTRAINTS rc
JOIN information_schema.KEY_COLUMN_USAGE kcu
    ON rc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
    AND rc.TABLE_SCHEMA = kcu.TABLE_SCHEMA
WHERE rc.CONSTRAINT_SCHEMA = 'wladmin'
  AND rc.TABLE_NAME = 'user';

-- 查看表的数据大小和行数
SELECT
    TABLE_NAME AS '表名',
    ROUND(DATA_LENGTH / 1024 / 1024, 2) AS '数据大小(MB)',
    ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS '索引大小(MB)',
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS '总大小(MB)',
    TABLE_ROWS AS '行数'
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'wladmin'
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;

-- 查看表的准确行数（较慢）
SELECT COUNT(*) FROM user;

-- 查看表的字符集和排序规则
SELECT
    TABLE_NAME AS '表名',
    TABLE_COLLATION AS '排序规则',
    TABLE_COMMENT AS '表注释'
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'wladmin'
  AND TABLE_NAME = 'user';

-- 查看字段的字符集和排序规则
SELECT
    COLUMN_NAME AS '字段名',
    CHARACTER_SET_NAME AS '字符集',
    COLLATION_NAME AS '排序规则'
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'wladmin'
  AND TABLE_NAME = 'user'
  AND CHARACTER_SET_NAME IS NOT NULL;

-- 查看所有表的注释
SELECT
    TABLE_NAME AS '表名',
    TABLE_COMMENT AS '表注释'
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'wladmin'
  AND TABLE_COMMENT != '';

-- 查看所有字段的注释
SELECT
    TABLE_NAME AS '表名',
    COLUMN_NAME AS '字段名',
    COLUMN_COMMENT AS '字段注释'
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'wladmin'
  AND COLUMN_COMMENT != ''
ORDER BY TABLE_NAME, ORDINAL_POSITION;

-- 查看自增ID当前值
SHOW TABLE STATUS LIKE 'user';

-- 或者
SELECT
    AUTO_INCREMENT AS '当前自增值'
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'wladmin'
  AND TABLE_NAME = 'user';

-- 查看所有表的字段（常用查询）
SELECT
    TABLE_NAME AS '表名',
    GROUP_CONCAT(COLUMN_NAME ORDER BY ORDINAL_POSITION SEPARATOR ', ') AS '字段列表'
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'wladmin'
GROUP BY TABLE_NAME
ORDER BY TABLE_NAME;

-- 查看所有表的字段详情
SELECT
    TABLE_NAME AS '表名',
    COLUMN_NAME AS '字段名',
    COLUMN_TYPE AS '字段类型',
    COLUMN_COMMENT AS '字段注释'
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'wladmin'
ORDER BY TABLE_NAME, ORDINAL_POSITION;

-- 查看表的所有约束（主键、唯一键、外键等）
SELECT
    CONSTRAINT_NAME AS '约束名',
    CONSTRAINT_TYPE AS '约束类型',
    TABLE_NAME AS '表名'
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'wladmin'
  AND TABLE_NAME = 'user';

-- 查看唯一约束
SELECT
    CONSTRAINT_NAME AS '唯一约束名',
    COLUMN_NAME AS '字段名'
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'wladmin'
  AND TABLE_NAME = 'user'
  AND CONSTRAINT_NAME != 'PRIMARY'
  AND CONSTRAINT_NAME IN (
    SELECT CONSTRAINT_NAME
    FROM information_schema.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_TYPE = 'UNIQUE'
    AND TABLE_SCHEMA = 'wladmin'
    AND TABLE_NAME = 'user'
  );

-- 查看表引用的其他表（通过外键）
SELECT
    CONSTRAINT_NAME AS '外键名',
    REFERENCED_TABLE_NAME AS '引用的表',
    REFERENCED_COLUMN_NAME AS '引用的字段'
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'wladmin'
  AND TABLE_NAME = 'user'
  AND REFERENCED_TABLE_NAME IS NOT NULL;

-- 查看哪些表引用了当前表
SELECT
    TABLE_NAME AS '引用表名',
    COLUMN_NAME AS '引用字段',
    CONSTRAINT_NAME AS '外键名'
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'wladmin'
  AND REFERENCED_TABLE_NAME = 'user';

-- 标准建表模板
CREATE TABLE table_name (
    -- 主键ID（必须）
    id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',

    -- 编码字段（可选，用于业务编码）
    code VARCHAR(64) COMMENT '编码',

    -- 基础字段
    name VARCHAR(100) NOT NULL DEFAULT '' COMMENT '名称',
    description VARCHAR(500) DEFAULT '' COMMENT '描述',

    -- 状态字段
    status tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否启用 1：停用；0：启用',
    is_delete tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否删除 1：已删除；0：未删除',
    is_system TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否系统预置(0-否,1-是)',
    is_protected TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否保护，1为不可删除',

    -- 关联字段（外键）
    parent_id bigint(20) unsigned COMMENT '父节点ID',
    create_by bigint(20) unsigned NULL COMMENT '创建人ID',
    update_by bigint(20) unsigned NULL COMMENT '更新人ID',

    -- 时间字段（必须）
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',

    -- 主键
    PRIMARY KEY (id),

    -- 唯一索引（软删除场景使用复合唯一索引）
    UNIQUE KEY uk_code_delete (code, is_delete),

    -- 普通索引
    KEY idx_parent_id (parent_id) USING BTREE,
    KEY idx_is_delete (is_delete) USING BTREE,
    KEY idx_name (name) USING BTREE,
    KEY idx_created_at (created_at) USING BTREE,

    -- 外键约束（可选）
    CONSTRAINT fk_table_parent FOREIGN KEY (parent_id) REFERENCES parent_table(id) ON DELETE CASCADE,
    CONSTRAINT fk_table_create_by FOREIGN KEY (create_by) REFERENCES user(id) ON DELETE SET NULL,
    CONSTRAINT fk_table_update_by FOREIGN KEY (update_by) REFERENCES user(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='表注释';

-- 主键必须使用 bigint(20) unsigned AUTO_INCREMENT
PRIMARY KEY (id)

-- 软删除场景：使用复合唯一索引（包含 is_delete）
UNIQUE KEY uk_code_delete (code, is_delete)
UNIQUE KEY uk_username_delete (username, is_delete)

-- 普通唯一索引（无软删除）
UNIQUE KEY uk_email (email)

-- 单字段索引
KEY idx_name (name) USING BTREE
KEY idx_mobile (mobile) USING BTREE
KEY idx_created_at (created_at) USING BTREE

-- 复合索引（根据查询场景设计）
KEY idx_is_delete_created_at (is_delete, created_at) USING BTREE
KEY idx_user_dept_action (user_id, dept_id, action_type) USING BTREE

-- ON DELETE CASCADE：删除父记录时，级联删除子记录
CONSTRAINT fk_department_parent FOREIGN KEY (parent_id) REFERENCES department(id) ON DELETE CASCADE

-- ON DELETE SET NULL：删除父记录时，子记录的外键设置为 NULL
CONSTRAINT fk_user_department FOREIGN KEY (department_id) REFERENCES department(id) ON DELETE SET NULL

-- ON DELETE RESTRICT：禁止删除父记录（默认行为）
CONSTRAINT fk_user_role_user FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE RESTRICT

-- 软删除字段
is_delete tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否删除 1：已删除；0：未删除'

-- 唯一索引需要考虑软删除
UNIQUE KEY uk_code_delete (code, is_delete)

-- 查询时过滤已删除记录
SELECT * FROM user WHERE is_delete = 0;

-- 创建时间：插入时自动设置
created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'

-- 更新时间：插入和更新时自动设置
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间'

-- 操作时间：仅插入时设置
operation_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间'

-- 创建表
CREATE TABLE table_name (...);

-- 删除表（谨慎使用）
DROP TABLE IF EXISTS table_name;

-- 重命名表
ALTER TABLE old_name RENAME TO new_name;

-- 修改表注释
ALTER TABLE table_name COMMENT '新的表注释';

-- 查看表是否存在
SELECT COUNT(*) FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'wladmin' AND TABLE_NAME = 'table_name';

-- 添加字段
ALTER TABLE table_name ADD COLUMN column_name VARCHAR(100) COMMENT '字段注释';

-- 修改字段类型
ALTER TABLE table_name MODIFY COLUMN column_name VARCHAR(200) COMMENT '新的注释';

-- 重命名字段
ALTER TABLE table_name CHANGE COLUMN old_name new_name VARCHAR(100) COMMENT '注释';

-- 删除字段（谨慎使用）
ALTER TABLE table_name DROP COLUMN column_name;

-- 修改字段位置
ALTER TABLE table_name MODIFY COLUMN column_name VARCHAR(100) AFTER other_column;

-- 添加索引
ALTER TABLE table_name ADD INDEX idx_name (column_name);

-- 添加唯一索引
ALTER TABLE table_name ADD UNIQUE INDEX uk_name (column_name);

-- 添加复合索引
ALTER TABLE table_name ADD INDEX idx_name (col1, col2);

-- 删除索引
ALTER TABLE table_name DROP INDEX idx_name;

-- 查看索引使用情况（MySQL 8.0+）
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'wladmin';

-- 添加外键
ALTER TABLE table_name
ADD CONSTRAINT fk_name FOREIGN KEY (column_name) REFERENCES ref_table(ref_column) ON DELETE CASCADE;

-- 删除外键
ALTER TABLE table_name DROP FOREIGN KEY fk_name;

-- 查看外键
SHOW CREATE TABLE table_name;

-- 添加约束（主键、唯一、外键等）
ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_type (column_name);

-- 删除约束
ALTER TABLE table_name DROP CONSTRAINT constraint_name;

-- 查看约束
SHOW CREATE TABLE table_name;

SELECT 列名
FROM 表1
INNER JOIN 表2
ON 表1.关联列 = 表2.关联列
WHERE 条件;

-- 查询用户及其所属部门
SELECT
    u.id,
    u.username,
    u.name,
    d.id AS dept_id,
    d.name AS dept_name
FROM user u
INNER JOIN department d ON u.dept_id = d.id
WHERE u.is_delete = 0 AND d.is_delete = 0;

-- 查询用户及其角色
SELECT
    u.id,
    u.username,
    r.id AS role_id,
    r.name AS role_name
FROM user u
INNER JOIN user_role ur ON u.id = ur.user_id
INNER JOIN role r ON ur.role_id = r.id
WHERE u.is_delete = 0 AND r.is_delete = 0;

SELECT 列名
FROM 表1
LEFT JOIN 表2
ON 表1.关联列 = 表2.关联列
WHERE 条件;

-- 查询所有用户及其所属部门
SELECT
    u.id,
    u.username,
    u.name,
    d.id AS dept_id,
    d.name AS dept_name
FROM user u
LEFT JOIN department d ON u.dept_id = d.id AND d.is_delete = 0
WHERE u.is_delete = 0;

-- 查询用户及其登录日志
SELECT
    u.id,
    u.username,
    a.login_time,
    a.ip_address
FROM user u
LEFT JOIN audit_logs a ON u.id = a.user_id AND a.action = 'login'
WHERE u.is_delete = 0
ORDER BY u.id, a.login_time DESC;

SELECT 列名
FROM 表1
RIGHT JOIN 表2
ON 表1.关联列 = 表2.关联列
WHERE 条件;

-- 查询所有部门及其用户
SELECT
    d.id AS dept_id,
    d.name AS dept_name,
    u.id,
    u.username,
    u.name
FROM user u
RIGHT JOIN department d ON u.dept_id = d.id AND u.is_delete = 0
WHERE d.is_delete = 0
ORDER BY d.id, u.id;

SELECT 列名
FROM 表1
LEFT JOIN 表2
ON 表1.关联列 = 表2.关联列
UNION
SELECT 列名
FROM 表1
RIGHT JOIN 表2
ON 表1.关联列 = 表2.关联列
WHERE 表1.关联列 IS NULL;

-- 查询所有用户和所有部门的关联关系
SELECT
    u.id,
    u.username,
    d.id AS dept_id,
    d.name AS dept_name
FROM user u
LEFT JOIN department d ON u.dept_id = d.id AND d.is_delete = 0
WHERE u.is_delete = 0
UNION
SELECT
    NULL,
    NULL,
    d.id AS dept_id,
    d.name AS dept_name
FROM department d
WHERE d.is_delete = 0 AND NOT EXISTS (
    SELECT 1 FROM user u WHERE u.dept_id = d.id AND u.is_delete = 0
);

SELECT 列名
FROM 表1
CROSS JOIN 表2
WHERE 条件;

-- 生成用户和角色的所有可能组合
SELECT
    u.id AS user_id,
    u.username,
    r.id AS role_id,
    r.name AS role_name
FROM user u
CROSS JOIN role r
WHERE u.is_delete = 0 AND r.is_delete = 0;

SELECT 列名
FROM 表1 t1
JOIN 表1 t2
ON t1.关联列 = t2.关联列
WHERE 条件;

-- 查询部门及其上级部门
SELECT
    d1.id AS dept_id,
    d1.name AS dept_name,
    d2.id AS parent_dept_id,
    d2.name AS parent_dept_name
FROM department d1
LEFT JOIN department d2 ON d1.parent_id = d2.id
WHERE d1.is_delete = 0;

-- 查询用户及其直接上级
SELECT
    u1.id AS user_id,
    u1.username AS user_name,
    u2.id AS leader_id,
    u2.username AS leader_name
FROM user u1
LEFT JOIN user u2 ON u1.leader_id = u2.id
WHERE u1.is_delete = 0;

-- 分析连接查询的执行计划
EXPLAIN SELECT
    u.id,
    u.username,
    d.name AS dept_name
FROM user u
JOIN department d ON u.dept_id = d.id
WHERE u.is_delete = 0 AND d.is_delete = 0;

-- 分析查询执行计划
EXPLAIN SELECT * FROM user WHERE username = 'admin';

-- 详细执行计划（MySQL 5.6+）
EXPLAIN FORMAT=JSON SELECT * FROM user WHERE username = 'admin';

-- 查看实际执行时间
EXPLAIN ANALYZE SELECT * FROM user WHERE username = 'admin';

-- 查看慢查询日志（需要开启）
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

-- 查看当前慢查询
SHOW FULL PROCESSLIST;

-- 查看正在执行的查询
SELECT * FROM information_schema.PROCESSLIST WHERE COMMAND != 'Sleep';

-- 更新表的统计信息（优化查询性能）
ANALYZE TABLE table_name;

-- 优化表（重建索引、整理碎片）
OPTIMIZE TABLE table_name;

-- 检查表
CHECK TABLE table_name;

-- 创建简单视图
CREATE VIEW view_user_list AS
SELECT id, username, name, email, status, created_at
FROM user
WHERE is_delete = 0;

-- 创建视图（如果不存在）
CREATE OR REPLACE VIEW view_user_list AS
SELECT id, username, name, email, status, created_at
FROM user
WHERE is_delete = 0;

-- 创建复杂视图（基于项目实际案例：监控数据联合视图）
CREATE VIEW monitor_combined_view AS
SELECT
    d.id,
    d.session_id,
    d.user_id,
    d.reporttype,
    d.type,
    d.category,
    d.message,
    d.page_url,
    d.timestamp,
    d.created_at,
    NULL as performance_data,
    NULL as behavior_data
FROM monitor_data d
UNION ALL
SELECT
    p.id,
    p.session_id,
    p.user_id,
    p.reporttype,
    p.type,
    p.category,
    CONCAT('Performance: ', COALESCE(p.metric, p.type)) as message,
    p.page_url,
    p.timestamp,
    p.created_at,
    JSON_OBJECT(
        'load_time', p.load_time,
        'fcp', p.fcp,
        'lcp', p.lcp
    ) as performance_data,
    NULL as behavior_data
FROM monitor_performance p;

-- 查看所有视图
SELECT TABLE_NAME AS '视图名', VIEW_DEFINITION AS '视图定义'
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = 'wladmin';

-- 查看视图定义
SHOW CREATE VIEW view_user_list;

-- 查看视图结构（同查看表结构）
DESC view_user_list;
SHOW COLUMNS FROM view_user_list;

-- 查看视图的所有字段
SELECT COLUMN_NAME, COLUMN_TYPE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'wladmin'
  AND TABLE_NAME = 'view_user_list';

-- 查询视图（同查询表）
SELECT * FROM view_user_list WHERE status = 0;

-- 视图可以用于 JOIN
SELECT v.*, d.name AS dept_name
FROM view_user_list v
LEFT JOIN department d ON v.dept_id = d.id;

-- 修改视图定义
ALTER VIEW view_user_list AS
SELECT id, username, name, email, status, created_at, department_id
FROM user
WHERE is_delete = 0;

-- 或者使用 CREATE OR REPLACE
CREATE OR REPLACE VIEW view_user_list AS
SELECT id, username, name, email, status, created_at, department_id
FROM user
WHERE is_delete = 0;

-- 删除视图
DROP VIEW IF EXISTS view_user_list;

-- 删除多个视图
DROP VIEW IF EXISTS view_user_list, view_role_list;

-- 创建触发器语法
DELIMITER //
CREATE TRIGGER trigger_name
BEFORE|AFTER INSERT|UPDATE|DELETE
ON table_name
FOR EACH ROW
BEGIN
    -- 触发器逻辑
END //
DELIMITER ;

-- 示例：用户创建时自动记录创建时间（虽然可用 DEFAULT CURRENT_TIMESTAMP，这里仅作示例）
DELIMITER //
CREATE TRIGGER trg_user_before_insert
BEFORE INSERT ON user
FOR EACH ROW
BEGIN
    IF NEW.created_at IS NULL THEN
        SET NEW.created_at = NOW();
    END IF;
END //
DELIMITER ;

-- 示例：删除用户时自动记录到审计表
DELIMITER //
CREATE TRIGGER trg_user_after_delete
AFTER DELETE ON user
FOR EACH ROW
BEGIN
    INSERT INTO audit_logs (
        user_id, username, action_type, module_code,
        operation_desc, before_data, operation_time
    ) VALUES (
        OLD.id, OLD.username, 'delete', 'user',
        CONCAT('删除用户：', OLD.username),
        JSON_OBJECT('id', OLD.id, 'username', OLD.username),
        NOW()
    );
END //
DELIMITER ;

-- 示例：更新部门时自动更新部门的 children_count
DELIMITER //
CREATE TRIGGER trg_department_after_insert
AFTER INSERT ON department
FOR EACH ROW
BEGIN
    IF NEW.parent_id IS NOT NULL THEN
        UPDATE department
        SET children_count = children_count + 1
        WHERE id = NEW.parent_id;
    END IF;
END //
DELIMITER ;

DELIMITER //
CREATE TRIGGER trg_department_after_delete
AFTER DELETE ON department
FOR EACH ROW
BEGIN
    IF OLD.parent_id IS NOT NULL THEN
        UPDATE department
        SET children_count = children_count - 1
        WHERE id = OLD.parent_id;
    END IF;
END //
DELIMITER ;

-- 查看所有触发器
SHOW TRIGGERS;

-- 查看指定数据库的触发器
SHOW TRIGGERS FROM wladmin;

-- 查看指定表的触发器
SHOW TRIGGERS FROM wladmin LIKE 'user';

-- 从 information_schema 查询触发器
SELECT
    TRIGGER_NAME AS '触发器名',
    EVENT_MANIPULATION AS '事件类型',
    EVENT_OBJECT_TABLE AS '表名',
    ACTION_TIMING AS '触发时机',
    ACTION_STATEMENT AS '触发器语句'
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'wladmin'
  AND EVENT_OBJECT_TABLE = 'user';

-- 删除触发器
DROP TRIGGER IF EXISTS trg_user_before_insert;

-- 基本语法
DELIMITER //
CREATE PROCEDURE procedure_name([IN|OUT|INOUT parameter_name data_type])
BEGIN
    -- 存储过程逻辑
END //
DELIMITER ;

-- 示例：清理过期监控数据（基于项目实际案例）
DELIMITER //
CREATE PROCEDURE clean_monitor_data(IN days_to_keep INT)
BEGIN
    DECLARE expire_time BIGINT;
    SET expire_time = UNIX_TIMESTAMP(NOW() - INTERVAL days_to_keep DAY) * 1000;

    -- 清理过期监控数据
    DELETE FROM monitor_data WHERE timestamp < expire_time;

    -- 清理过期性能数据
    DELETE FROM monitor_performance WHERE timestamp < expire_time;

    -- 清理过期行为事件
    DELETE FROM monitor_behavior_events WHERE timestamp < expire_time;

    -- 返回影响的行数
    SELECT
        ROW_COUNT() AS affected_rows,
        days_to_keep AS days_kept;
END //
DELIMITER ;

-- 示例：获取性能指标统计
DELIMITER //
CREATE PROCEDURE get_performance_summary(
    IN start_time BIGINT,
    IN end_time BIGINT
)
BEGIN
    SELECT
        'page_performance' as metric_group,
        AVG(load_time) as avg_load_time,
        AVG(ttfb) as avg_ttfb,
        AVG(fcp) as avg_fcp,
        AVG(lcp) as avg_lcp,
        COUNT(*) as sample_count
    FROM monitor_performance
    WHERE timestamp BETWEEN start_time AND end_time
    AND category = 'page_performance';
END //
DELIMITER ;

-- 示例：带输出参数的存储过程
DELIMITER //
CREATE PROCEDURE get_user_count(
    OUT total_count INT,
    OUT active_count INT
)
BEGIN
    SELECT COUNT(*) INTO total_count FROM user WHERE is_delete = 0;
    SELECT COUNT(*) INTO active_count FROM user WHERE is_delete = 0 AND status = 0;
END //
DELIMITER ;

-- 调用存储过程（无参数）
CALL clean_monitor_data(30);

-- 调用存储过程（带输入参数）
SET @start_time = UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 7 DAY)) * 1000;
SET @end_time = UNIX_TIMESTAMP(NOW()) * 1000;
CALL get_performance_summary(@start_time, @end_time);

-- 调用存储过程（带输出参数）
CALL get_user_count(@total, @active);
SELECT @total AS '总用户数', @active AS '活跃用户数';

-- 查看所有存储过程
SHOW PROCEDURE STATUS WHERE Db = 'wladmin';

-- 查看存储过程定义
SHOW CREATE PROCEDURE clean_monitor_data;

-- 从 information_schema 查询存储过程
SELECT
    ROUTINE_NAME AS '存储过程名',
    ROUTINE_TYPE AS '类型',
    CREATED AS '创建时间',
    LAST_ALTERED AS '修改时间'
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'wladmin'
  AND ROUTINE_TYPE = 'PROCEDURE';

-- 修改存储过程（使用 ALTER PROCEDURE，只能修改特性，不能修改逻辑）
ALTER PROCEDURE clean_monitor_data COMMENT '清理过期监控数据存储过程';

-- 修改存储过程逻辑（使用 DROP 和 CREATE）
DROP PROCEDURE IF EXISTS clean_monitor_data;
-- 然后重新创建

-- 删除存储过程
DROP PROCEDURE IF EXISTS clean_monitor_data;

-- 开始事务
START TRANSACTION;
-- 或
BEGIN;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 设置保存点
SAVEPOINT savepoint_name;

-- 回滚到保存点
ROLLBACK TO savepoint_name;

-- 释放保存点
RELEASE SAVEPOINT savepoint_name;

-- 示例：转账操作（原子性保证）
START TRANSACTION;

-- 从账户A扣款
UPDATE account SET balance = balance - 100 WHERE id = 1;

-- 向账户B加款
UPDATE account SET balance = balance + 100 WHERE id = 2;

-- 如果所有操作成功，提交事务
COMMIT;
-- 如果出错，回滚事务
-- ROLLBACK;

-- 示例：使用保存点
START TRANSACTION;

INSERT INTO user (username, name) VALUES ('user1', '用户1');
SAVEPOINT sp1;

INSERT INTO user (username, name) VALUES ('user2', '用户2');
SAVEPOINT sp2;

INSERT INTO user (username, name) VALUES ('user3', '用户3');

-- 回滚到保存点 sp2，user3 的插入会被撤销
ROLLBACK TO sp2;

-- 提交事务，user1 和 user2 会被保存
COMMIT;

-- 查看当前隔离级别
SELECT @@transaction_isolation;
-- MySQL 8.0+
SELECT @@transaction_isolation;

-- MySQL 5.7
SELECT @@tx_isolation;

-- 设置会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- 设置全局隔离级别（需要 SUPER 权限）
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 查看自动提交状态
SELECT @@autocommit;

-- 关闭自动提交（每个语句都会开启一个事务）
SET autocommit = 0;

-- 开启自动提交（默认状态）
SET autocommit = 1;

-- 在自动提交关闭的情况下，每个语句都需要手动提交或回滚
SET autocommit = 0;
INSERT INTO user (username, name) VALUES ('user1', '用户1');
COMMIT;  -- 必须手动提交

SET autocommit = 0;
INSERT INTO user (username, name) VALUES ('user2', '用户2');
ROLLBACK;  -- 回滚，user2 不会被保存

-- 恢复自动提交
SET autocommit = 1;

-- 创建临时表（基本语法）
CREATE TEMPORARY TABLE temp_table_name (
    column1 datatype,
    column2 datatype,
    ...
);

-- 创建临时表（完整语法，包含索引和约束）
CREATE TEMPORARY TABLE temp_user_summary (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    username VARCHAR(50) NOT NULL,
    action_count INT DEFAULT 0,
    last_action_time DATETIME,
    PRIMARY KEY (id),
    KEY idx_user_id (user_id),
    KEY idx_last_action_time (last_action_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 从查询结果创建临时表（最常用）
CREATE TEMPORARY TABLE temp_active_users AS
SELECT
    u.id,
    u.username,
    u.name,
    u.department_id,
    COUNT(a.id) AS action_count,
    MAX(a.operation_time) AS last_action_time
FROM user u
LEFT JOIN audit_logs a ON u.id = a.user_id
WHERE u.is_delete = 0
  AND u.status = 0
  AND a.operation_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY u.id, u.username, u.name, u.department_id;

-- 查看临时表数据
SELECT * FROM temp_active_users ORDER BY action_count DESC;

-- 创建临时表（结构类似现有表）
CREATE TEMPORARY TABLE temp_user_backup LIKE user;

-- 插入数据到临时表
INSERT INTO temp_user_backup
SELECT * FROM user WHERE is_delete = 0 AND status = 0;

-- 插入单条数据
INSERT INTO temp_user_summary (user_id, username, action_count, last_action_time)
VALUES (1, 'admin', 100, NOW());

-- 批量插入数据
INSERT INTO temp_user_summary (user_id, username, action_count, last_action_time)
SELECT
    u.id,
    u.username,
    COUNT(a.id) AS action_count,
    MAX(a.operation_time) AS last_action_time
FROM user u
LEFT JOIN audit_logs a ON u.id = a.user_id
WHERE u.is_delete = 0
GROUP BY u.id, u.username;

-- 查询临时表（与普通表查询方式相同）
SELECT * FROM temp_user_summary WHERE action_count > 10;

-- 临时表与其他表关联查询
SELECT
    t.*,
    d.name AS dept_name
FROM temp_user_summary t
LEFT JOIN department d ON t.department_id = d.id
WHERE d.is_delete = 0;

-- 更新临时表数据
UPDATE temp_user_summary
SET action_count = action_count + 1,
    last_action_time = NOW()
WHERE user_id = 1;

-- 删除临时表数据（不删除表结构）
DELETE FROM temp_user_summary WHERE action_count = 0;

-- 清空临时表
TRUNCATE TABLE temp_user_summary;

-- 手动删除临时表（会话结束时会自动删除）
DROP TEMPORARY TABLE IF EXISTS temp_user_summary;

-- 删除多个临时表
DROP TEMPORARY TABLE IF EXISTS temp_user_summary, temp_dept_summary;

-- 查看当前会话的所有临时表
SHOW TABLES LIKE 'temp_%';

-- 查看临时表结构
DESC temp_user_summary;
SHOW CREATE TABLE temp_user_summary;

-- 查看临时表的所有字段
SHOW COLUMNS FROM temp_user_summary;

-- 从 information_schema 查询临时表（MySQL 8.0+）
SELECT
    TABLE_NAME AS '临时表名',
    TABLE_ROWS AS '行数',
    ROUND(DATA_LENGTH / 1024, 2) AS '大小(KB)',
    ENGINE AS '存储引擎'
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME LIKE 'temp_%';

-- 创建 MEMORY 临时表（默认，速度快，但数据在内存中）
CREATE TEMPORARY TABLE temp_memory_table (
    id INT PRIMARY KEY,
    name VARCHAR(100)
) ENGINE=MEMORY;

-- 特点：
-- ✅ 速度快（数据在内存中）
-- ❌ 数据在服务器重启或表被删除时丢失
-- ❌ 不支持 TEXT/BLOB 类型
-- ❌ 不支持外键

-- 创建 InnoDB 临时表（支持事务、外键等）
CREATE TEMPORARY TABLE temp_innodb_table (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED,
    data JSON,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 特点：
-- ✅ 支持事务
-- ✅ 支持外键（虽然临时表很少用外键）
-- ✅ 支持 TEXT/BLOB/JSON 类型
-- ✅ 数据持久化到磁盘（会话期间）

-- 创建带索引的临时表
CREATE TEMPORARY TABLE temp_indexed_table (
    id INT PRIMARY KEY,
    user_id INT,
    action_time DATETIME,
    INDEX idx_user_id (user_id),
    INDEX idx_action_time (action_time),
    INDEX idx_user_action (user_id, action_time)
) ENGINE=InnoDB;

-- 为已存在的临时表添加索引
CREATE TEMPORARY TABLE temp_table (
    id INT,
    name VARCHAR(100)
);

ALTER TABLE temp_table ADD INDEX idx_name (name);
ALTER TABLE temp_table ADD PRIMARY KEY (id);

-- 场景：统计用户最近7天的操作次数和最后操作时间
-- 使用临时表存储中间结果，避免重复计算

-- 步骤1：创建临时表存储用户操作统计
CREATE TEMPORARY TABLE temp_user_stats AS
SELECT
    u.id AS user_id,
    u.username,
    COUNT(a.id) AS action_count,
    MAX(a.operation_time) AS last_action_time,
    MIN(a.operation_time) AS first_action_time
FROM user u
LEFT JOIN audit_logs a ON u.id = a.user_id
WHERE u.is_delete = 0
  AND a.operation_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY u.id, u.username;

-- 步骤2：基于临时表进行进一步查询
SELECT
    t.*,
    d.name AS dept_name,
    CASE
        WHEN t.action_count >= 100 THEN '活跃'
        WHEN t.action_count >= 50 THEN '一般'
        ELSE '不活跃'
    END AS activity_level
FROM temp_user_stats t
LEFT JOIN user u ON t.user_id = u.id
LEFT JOIN department d ON u.department_id = d.id
WHERE d.is_delete = 0
ORDER BY t.action_count DESC;

-- 步骤3：清理临时表（可选，会话结束会自动删除）
DROP TEMPORARY TABLE temp_user_stats;

-- 场景：批量更新用户状态，需要先验证数据

-- 步骤1：创建临时表存储待更新的用户ID
CREATE TEMPORARY TABLE temp_user_ids (
    user_id BIGINT UNSIGNED PRIMARY KEY,
    new_status TINYINT,
    reason VARCHAR(255)
);

-- 步骤2：插入待更新的用户ID和状态
INSERT INTO temp_user_ids (user_id, new_status, reason)
VALUES
    (1, 1, '长时间未登录'),
    (2, 1, '账户异常'),
    (3, 0, '恢复正常');

-- 步骤3：验证数据（检查用户是否存在）
SELECT
    t.user_id,
    t.new_status,
    t.reason,
    u.username,
    u.status AS current_status,
    CASE
        WHEN u.id IS NULL THEN '用户不存在'
        WHEN u.is_delete = 1 THEN '用户已删除'
        ELSE '可以更新'
    END AS validation_result
FROM temp_user_ids t
LEFT JOIN user u ON t.user_id = u.id;

-- 步骤4：执行批量更新（使用事务）
START TRANSACTION;

UPDATE user u
INNER JOIN temp_user_ids t ON u.id = t.user_id
SET u.status = t.new_status,
    u.updated_at = NOW()
WHERE u.is_delete = 0
  AND u.id IS NOT NULL;

-- 检查更新结果
SELECT ROW_COUNT() AS updated_rows;

COMMIT;

-- 步骤5：清理临时表
DROP TEMPORARY TABLE temp_user_ids;

-- 场景：清洗重复的审计日志数据

-- 步骤1：创建临时表存储去重后的数据
CREATE TEMPORARY TABLE temp_audit_clean AS
SELECT
    id,
    user_id,
    username,
    action_type,
    module_code,
    operation_time,
    ROW_NUMBER() OVER (
        PARTITION BY user_id, action_type, module_code, DATE(operation_time)
        ORDER BY operation_time DESC, id DESC
    ) AS rn
FROM audit_logs
WHERE operation_time >= DATE_SUB(NOW(), INTERVAL 30 DAY);

-- 步骤2：查看重复数据统计
SELECT
    COUNT(*) AS total_records,
    COUNT(CASE WHEN rn = 1 THEN 1 END) AS unique_records,
    COUNT(*) - COUNT(CASE WHEN rn = 1 THEN 1 END) AS duplicate_records
FROM temp_audit_clean;

-- 步骤3：提取去重后的数据
CREATE TEMPORARY TABLE temp_audit_unique AS
SELECT
    id,
    user_id,
    username,
    action_type,
    module_code,
    operation_time
FROM temp_audit_clean
WHERE rn = 1;

-- 步骤4：使用去重后的数据进行统计
SELECT
    action_type,
    COUNT(*) AS count,
    COUNT(DISTINCT user_id) AS unique_users
FROM temp_audit_unique
GROUP BY action_type
ORDER BY count DESC;

-- 清理临时表
DROP TEMPORARY TABLE temp_audit_clean;
DROP TEMPORARY TABLE temp_audit_unique;

-- 场景：批量生成用户报表数据

-- 步骤1：创建临时表存储报表数据
CREATE TEMPORARY TABLE temp_user_report (
    user_id BIGINT UNSIGNED PRIMARY KEY,
    username VARCHAR(50),
    dept_name VARCHAR(100),
    login_count INT DEFAULT 0,
    last_login_time DATETIME,
    action_count INT DEFAULT 0,
    last_action_time DATETIME
) ENGINE=InnoDB;

-- 步骤2：插入用户基本信息
INSERT INTO temp_user_report (user_id, username, dept_name)
SELECT
    u.id,
    u.username,
    d.name AS dept_name
FROM user u
LEFT JOIN department d ON u.department_id = d.id
WHERE u.is_delete = 0
  AND d.is_delete = 0;

-- 步骤3：更新登录统计
UPDATE temp_user_report t
INNER JOIN (
    SELECT
        user_id,
        COUNT(*) AS login_count,
        MAX(operation_time) AS last_login_time
    FROM audit_logs
    WHERE action_type = 'login'
      AND operation_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
    GROUP BY user_id
) a ON t.user_id = a.user_id
SET t.login_count = a.login_count,
    t.last_login_time = a.last_login_time;

-- 步骤4：更新操作统计
UPDATE temp_user_report t
INNER JOIN (
    SELECT
        user_id,
        COUNT(*) AS action_count,
        MAX(operation_time) AS last_action_time
    FROM audit_logs
    WHERE operation_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
    GROUP BY user_id
) a ON t.user_id = a.user_id
SET t.action_count = a.action_count,
    t.last_action_time = a.last_action_time;

-- 步骤5：查询报表数据
SELECT
    username,
    dept_name,
    login_count,
    last_login_time,
    action_count,
    last_action_time,
    CASE
        WHEN login_count >= 20 AND action_count >= 100 THEN '活跃'
        WHEN login_count >= 10 AND action_count >= 50 THEN '一般'
        ELSE '不活跃'
    END AS activity_level
FROM temp_user_report
ORDER BY action_count DESC;

-- 清理临时表
DROP TEMPORARY TABLE temp_user_report;

-- CTE：适合简单的中间结果，查询结束后自动消失
WITH latest_audit AS (
    SELECT
        a.*,
        ROW_NUMBER() OVER (PARTITION BY a.user_id ORDER BY a.operation_time DESC) AS rn
    FROM audit_logs a
    WHERE a.operation_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)
)
SELECT
    la.user_id,
    la.username,
    la.operation_time AS last_action_time
FROM latest_audit la
WHERE la.rn = 1;