-- 创建数据库
CREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 删除数据库
DROP DATABASE db_name;

-- 修改数据库字符集
ALTER DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 创建表
CREATE TABLE table_name (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_name (name)
) ENGINE=InnoDB;

-- 修改表结构
ALTER TABLE table_name ADD COLUMN new_column VARCHAR(255) AFTER existing_column;
ALTER TABLE table_name MODIFY COLUMN column_name VARCHAR(500);
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE table_name RENAME TO new_table_name;

-- 清空表
TRUNCATE TABLE table_name;

-- 插入数据
INSERT INTO table_name (col1, col2) VALUES (val1, val2), (val3, val4);

-- 更新数据
UPDATE table_name SET col1 = val1 WHERE condition;

-- 删除数据
DELETE FROM table_name WHERE condition;

-- 查询数据
SELECT * FROM table_name WHERE condition LIMIT 10 OFFSET 5;
SELECT col1, col2 FROM table_name ORDER BY col1 DESC;
SELECT COUNT(*) FROM table_name;
SELECT DISTINCT col1 FROM table_name;

-- 连接查询
SELECT a.*, b.* FROM table_a a JOIN table_b b ON a.id = b.a_id;

-- 子查询
SELECT * FROM table_name WHERE id IN (SELECT id FROM other_table);

-- 分组聚合
SELECT department, COUNT(*), AVG(salary) 
FROM employees 
GROUP BY department 
HAVING COUNT(*) > 5;

-- 窗口函数(MySQL 8.0+)
SELECT name, salary, 
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM employees;

-- 创建索引
CREATE INDEX idx_name ON table_name(column1, column2);
CREATE UNIQUE INDEX idx_unique ON table_name(column);

-- 删除索引
DROP INDEX idx_name ON table_name;

-- 查看索引使用情况
EXPLAIN SELECT * FROM table_name WHERE indexed_column = 'value';

-- 强制使用索引
SELECT * FROM table_name FORCE INDEX (idx_name) WHERE condition;

-- 开始事务
START TRANSACTION;

-- 提交/回滚
COMMIT;
ROLLBACK;

-- 设置隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 手动加锁
SELECT * FROM table_name WHERE id = 1 FOR UPDATE;  -- 排他锁
SELECT * FROM table_name WHERE id = 1 LOCK IN SHARE MODE;  -- 共享锁

-- 查看锁状态
SHOW OPEN TABLES WHERE In_use > 0;
SELECT * FROM performance_schema.data_locks;
SELECT * FROM sys.innodb_lock_waits;

-- 杀死进程释放锁
SHOW PROCESSLIST;
KILL process_id;

# 导出整个数据库
mysqldump -u username -p db_name > backup.sql

# 导出特定表
mysqldump -u username -p db_name table1 table2 > tables.sql

# 导出结构不导出数据
mysqldump -u username -p --no-data db_name > schema.sql

# 导出为CSV
mysql -u username -p -e "SELECT * FROM table_name" db_name > output.csv

# 导入SQL文件
mysql -u username -p db_name < backup.sql

# 导入CSV文件
mysqlimport -u username -p --fields-terminated-by=, --lines-terminated-by="\n" db_name file.csv

# 从SQL文件恢复单个表
mysql -u username -p db_name < table_dump.sql

-- 查看慢查询
SHOW VARIABLES LIKE 'slow_query_log%';
SELECT * FROM mysql.slow_log;

-- 查看当前连接
SHOW STATUS LIKE 'Threads%';

-- 查看缓存命中率
SHOW STATUS LIKE 'Qcache%';

-- 查看InnoDB状态
SHOW ENGINE INNODB STATUS\G

-- 分析表
ANALYZE TABLE table_name;

-- 优化表
OPTIMIZE TABLE table_name;

-- 重建表
ALTER TABLE table_name ENGINE=InnoDB;

-- 刷新权限
FLUSH PRIVILEGES;

-- 重置查询缓存
RESET QUERY CACHE;

-- 创建用户
CREATE USER 'username'@'host' IDENTIFIED BY 'password';

-- 修改密码
ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';

-- 授权
GRANT SELECT, INSERT ON db_name.* TO 'username'@'host';

-- 撤销权限
REVOKE INSERT ON db_name.* FROM 'username'@'host';

-- 查看权限
SHOW GRANTS FOR 'username'@'host';

-- 删除用户
DROP USER 'username'@'host';

DELIMITER //
CREATE PROCEDURE procedure_name(IN param INT)
BEGIN
    DECLARE var INT;
    SELECT COUNT(*) INTO var FROM table_name;
    IF var > param THEN
        INSERT INTO log_table VALUES ('Threshold exceeded');
    END IF;
END //
DELIMITER ;

-- 调用
CALL procedure_name(10);

CREATE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
    IF NEW.amount < 0 THEN
        SET NEW.amount = 0;
    END IF;
END;

-- 创建分区表
CREATE TABLE partitioned_table (
    id INT,
    created_at DATETIME
) PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p0 VALUES LESS THAN (2020),
    PARTITION p1 VALUES LESS THAN (2021),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- 添加分区
ALTER TABLE partitioned_table ADD PARTITION (PARTITION p2 VALUES LESS THAN (2022));

-- 查看变量
SHOW VARIABLES LIKE '%buffer%';

-- 设置全局变量
SET GLOBAL max_connections = 200;

-- 设置会话变量
SET SESSION sql_mode = 'STRICT_TRANS_TABLES';

-- 查看日志状态
SHOW VARIABLES LIKE '%log%';

-- 开启二进制日志
SET GLOBAL log_bin = ON;

-- 刷新日志
FLUSH LOGS;

-- 查看表信息
SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'db_name';

-- 查看列信息
SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'table_name';

-- 查看约束
SELECT * FROM information_schema.TABLE_CONSTRAINTS;