MySQL 速查手册

CREATE DATABASE mydb
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_unicode_ci;

DROP DATABASE IF EXISTS mydb;

SHOW DATABASES;

USE mydb;

SHOW CREATE DATABASE mydb;

CREATE TABLE users (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE,
  age INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DESC users;
-- 或
SHOW COLUMNS FROM users;

ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;

-- 修改类型
ALTER TABLE users MODIFY COLUMN name VARCHAR(200);
-- 改名
ALTER TABLE users CHANGE COLUMN name username VARCHAR(200);

ALTER TABLE users DROP COLUMN phone;

DROP TABLE IF EXISTS users;

TRUNCATE TABLE users;

RENAME TABLE users TO members;

INSERT INTO users (name, email, age)
VALUES ('张三', 'zhang@example.com', 25);

INSERT INTO users (name, email, age) VALUES
  ('张三', 'zhang@example.com', 25),
  ('李四', 'li@example.com', 30),
  ('王五', 'wang@example.com', 28);

SELECT id, name, email FROM users
WHERE age > 18
ORDER BY created_at DESC
LIMIT 10;

SELECT * FROM users;

UPDATE users
SET name = '张三丰', age = 30
WHERE id = 1;

DELETE FROM users WHERE id = 1;

INSERT INTO users (id, name, email)
VALUES (1, '张三', 'zhang@example.com')
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  email = VALUES(email);

-- 等于
WHERE name = '张三'
-- 不等于
WHERE age != 18
-- 范围
WHERE age BETWEEN 18 AND 30
-- 包含
WHERE id IN (1, 2, 3)
-- 模糊匹配
WHERE name LIKE '张%'
-- 空值判断
WHERE email IS NOT NULL

-- 升序（默认）
ORDER BY age ASC
-- 降序
ORDER BY created_at DESC
-- 多字段排序
ORDER BY age DESC, name ASC

-- 第1页（每页10条）
SELECT * FROM users LIMIT 10 OFFSET 0;
-- 第2页
SELECT * FROM users LIMIT 10 OFFSET 10;
-- 简写
SELECT * FROM users LIMIT 10, 10;

SELECT DISTINCT city FROM users;

SELECT COUNT(*) AS total FROM users;
SELECT COUNT(DISTINCT city) AS city_count FROM users;

SELECT SUM(amount) AS total, AVG(amount) AS avg_amount
FROM orders;

SELECT MAX(age) AS oldest, MIN(age) AS youngest
FROM users;

SELECT city, COUNT(*) AS user_count
FROM users
GROUP BY city
HAVING COUNT(*) > 10
ORDER BY user_count DESC;

SELECT u.name, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

SELECT * FROM users
WHERE id IN (
  SELECT user_id FROM orders
  WHERE amount > 1000
);

SELECT u.name, o.id, p.product_name
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN products p ON o.product_id = p.id
WHERE o.status = 'completed';

-- 普通索引
CREATE INDEX idx_name ON users(name);
-- 唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);
-- 联合索引
CREATE INDEX idx_name_age ON users(name, age);

SHOW INDEX FROM users;

DROP INDEX idx_name ON users;

ALTER TABLE orders
ADD CONSTRAINT fk_user
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE;

-- 创建不可见索引
ALTER TABLE users ALTER INDEX idx_name INVISIBLE;
-- 恢复可见
ALTER TABLE users ALTER INDEX idx_name VISIBLE;
-- 查看索引可见性
SELECT INDEX_NAME, IS_VISIBLE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_NAME = 'users';

CREATE TABLE products (
  id BIGINT PRIMARY KEY,
  name VARCHAR(100),
  price DECIMAL(10,2),
  CONSTRAINT chk_price CHECK (price > 0)
);

ALTER TABLE users
ADD CONSTRAINT chk_age CHECK (age >= 0 AND age <= 200);

SELECT
  CONCAT(first_name, ' ', last_name) AS full_name,
  UPPER(name) AS upper_name,
  LOWER(name) AS lower_name,
  LENGTH(name) AS name_len,
  SUBSTRING(name, 1, 3) AS short_name,
  REPLACE(email, '@', '[at]') AS safe_email,
  TRIM('  hello  ') AS trimmed
FROM users;

SELECT
  NOW() AS current_time,
  CURDATE() AS today,
  DATE_FORMAT(created_at, '%Y-%m-%d') AS formatted,
  DATEDIFF(NOW(), created_at) AS days_ago,
  DATE_ADD(NOW(), INTERVAL 7 DAY) AS next_week,
  YEAR(created_at) AS year,
  MONTH(created_at) AS month
FROM users;

SELECT name,
  IF(age >= 18, '成年', '未成年') AS status,
  CASE
    WHEN age < 18 THEN '少年'
    WHEN age < 40 THEN '青年'
    WHEN age < 60 THEN '中年'
    ELSE '老年'
  END AS age_group
FROM users;

SELECT
  CAST('123' AS SIGNED) AS int_val,
  CAST(price AS CHAR) AS str_val,
  CONVERT('2024-01-01', DATE) AS date_val;

-- 允许任意主机连接
CREATE USER 'appuser'@'%'
IDENTIFIED BY 'StrongPassword123!';
-- 仅允许本地连接
CREATE USER 'localuser'@'localhost'
IDENTIFIED BY 'Password456!';
-- 仅允许指定 IP
CREATE USER 'remote'@'192.168.1.%'
IDENTIFIED BY 'Password789!';

-- 查看用户列表
SELECT User, Host, authentication_string
FROM mysql.user;
-- 查看当前用户
SELECT CURRENT_USER();
-- 查看所有用户及最近登录
SELECT User, Host, account_locked, password_expired
FROM mysql.user;

-- 所有权限（增删改查+结构操作）
GRANT ALL PRIVILEGES ON mydb.* TO 'appuser'@'%';
-- 只给 CRUD 权限
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'appuser'@'%';
-- 给所有数据库的查询权限
GRANT SELECT ON *.* TO 'readonly'@'%';
-- 刷新权限
FLUSH PRIVILEGES;

-- 只授权某张表的查询
GRANT SELECT ON mydb.users TO 'appuser'@'%';
-- 多张表分别授权
GRANT SELECT, INSERT ON mydb.orders TO 'appuser'@'%';
GRANT SELECT ON mydb.products TO 'appuser'@'%';
-- 授权表的增删改查
GRANT SELECT, INSERT, UPDATE, DELETE
ON mydb.users TO 'appuser'@'%';

-- 只允许查看 name 和 email 列
GRANT SELECT (name, email) ON mydb.users TO 'appuser'@'%';
-- 只允许更新 email 列
GRANT UPDATE (email) ON mydb.users TO 'appuser'@'%';
-- 只允许插入指定列
GRANT INSERT (name, email) ON mydb.users TO 'appuser'@'%';

-- 数据操作权限
-- SELECT    查询数据
-- INSERT    插入数据
-- UPDATE    更新数据
-- DELETE    删除数据

-- 结构操作权限
-- CREATE    创建数据库/表
-- ALTER     修改表结构
-- DROP      删除数据库/表
-- INDEX     创建/删除索引
-- REFERENCES 创建外键

-- 管理权限
-- GRANT OPTION   可将自己的权限授予他人
-- PROCESS        查看所有进程
-- RELOAD         执行 FLUSH
-- SUPER          杀掉其他用户进程
-- CREATE USER    创建/删除用户
-- SHOW DATABASES 查看所有数据库

-- 撤销数据库级别权限
REVOKE ALL PRIVILEGES ON mydb.* FROM 'appuser'@'%';
-- 撤销特定表权限
REVOKE SELECT ON mydb.users FROM 'appuser'@'%';
-- 撤销全局权限
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'appuser'@'%';

-- 查看指定用户权限
SHOW GRANTS FOR 'appuser'@'%';
-- 查看当前用户权限
SHOW GRANTS;
-- 从系统表查看库级权限
SELECT * FROM mysql.db WHERE User = 'appuser';
-- 查看表级权限
SELECT * FROM mysql.tables_priv WHERE User = 'appuser';
-- 查看列级权限
SELECT * FROM mysql.columns_priv WHERE User = 'appuser';

-- 8.0 推荐方式
ALTER USER 'appuser'@'%'
IDENTIFIED BY 'NewPassword456!';
-- 修改当前用户密码
SET PASSWORD = 'NewPassword456!';
-- 密码过期，强制用户下次登录修改
ALTER USER 'appuser'@'%' PASSWORD EXPIRE;

-- 删除用户
DROP USER 'appuser'@'%';
DROP USER IF EXISTS 'tempuser'@'%';
-- 锁定账户（禁止登录）
ALTER USER 'appuser'@'%' ACCOUNT LOCK;
-- 解锁账户
ALTER USER 'appuser'@'%' ACCOUNT UNLOCK;

-- 创建角色
CREATE ROLE 'app_read', 'app_write';
-- 给角色授权
GRANT SELECT ON mydb.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON mydb.* TO 'app_write';
-- 将角色赋予用户
GRANT 'app_read', 'app_write' TO 'appuser'@'%';
-- 激活角色
SET DEFAULT ROLE ALL TO 'appuser'@'%';
-- 查看角色
SELECT * FROM mysql.role_edges;

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 一切正常则提交
COMMIT;
-- 出错则回滚
-- ROLLBACK;

START TRANSACTION;

INSERT INTO orders (user_id, amount) VALUES (1, 500);
SAVEPOINT sp_order;

INSERT INTO order_items (order_id, product_id) VALUES (100, 5);
-- 这一步出错，只回滚到保存点
ROLLBACK TO sp_order;

-- 保留 orders 的插入
COMMIT;

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

-- 设置会话级别
SET SESSION TRANSACTION ISOLATION LEVEL
  READ COMMITTED;

-- 四种隔离级别：
-- READ UNCOMMITTED  读未提交（最低）
-- READ COMMITTED    读已提交
-- REPEATABLE READ   可重复读（InnoDB默认）
-- SERIALIZABLE      串行化（最高）

-- 查看自动提交状态
SELECT @@autocommit;
-- 关闭自动提交（需要手动 COMMIT）
SET autocommit = 0;
-- 开启自动提交
SET autocommit = 1;

-- 查看最近的死锁信息
SHOW ENGINE INNODB STATUS;
-- 查看当前锁等待
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
-- 查看正在运行的事务
SELECT * FROM information_schema.INNODB_TRX;
-- 杀掉阻塞的进程
KILL <process_id>;

-- 表锁
LOCK TABLES users READ;
LOCK TABLES users WRITE;
UNLOCK TABLES;

-- 行锁（InnoDB，需在事务中）
SELECT * FROM users WHERE id = 1 FOR UPDATE;
SELECT * FROM users WHERE id = 1 FOR SHARE;

CREATE VIEW v_active_users AS
SELECT id, name, email, last_login
FROM users
WHERE status = 'active';

-- 像普通表一样查询
SELECT * FROM v_active_users WHERE name LIKE '张%';
-- 查看视图定义
SHOW CREATE VIEW v_active_users;
-- 查看所有视图
SELECT TABLE_NAME FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = 'mydb';
-- 修改视图
ALTER VIEW v_active_users AS
SELECT id, name FROM users WHERE status = 'active';
-- 删除视图
DROP VIEW IF EXISTS v_active_users;

DELIMITER //
CREATE PROCEDURE sp_get_user(IN uid BIGINT)
BEGIN
  SELECT * FROM users WHERE id = uid;
END //
DELIMITER ;

-- 带输出参数
DELIMITER //
CREATE PROCEDURE sp_count_users(
  IN city_name VARCHAR(50),
  OUT total INT
)
BEGIN
  SELECT COUNT(*) INTO total
  FROM users WHERE city = city_name;
END //
DELIMITER ;

-- 调用存储过程
CALL sp_get_user(1);
-- 带输出参数调用
CALL sp_count_users('北京', @cnt);
SELECT @cnt;
-- 查看存储过程
SHOW PROCEDURE STATUS WHERE Db = 'mydb';
-- 查看定义
SHOW CREATE PROCEDURE sp_get_user;
-- 删除
DROP PROCEDURE IF EXISTS sp_get_user;

DELIMITER //
CREATE FUNCTION fn_full_name(
  first_name VARCHAR(50),
  last_name VARCHAR(50)
) RETURNS VARCHAR(101)
DETERMINISTIC
BEGIN
  RETURN CONCAT(first_name, ' ', last_name);
END //
DELIMITER ;

-- 使用
SELECT fn_full_name('张', '三') AS name;
SELECT fn_full_name(first_name, last_name) FROM users;

DELIMITER //
CREATE PROCEDURE sp_classify_age(IN uid BIGINT)
BEGIN
  DECLARE user_age INT;
  DECLARE label VARCHAR(20);

  SELECT age INTO user_age FROM users WHERE id = uid;

  IF user_age < 18 THEN
    SET label = '未成年';
  ELSEIF user_age < 60 THEN
    SET label = '成年人';
  ELSE
    SET label = '老年人';
  END IF;

  SELECT uid, user_age, label;
END //
DELIMITER ;

# 备份单个数据库
mysqldump -u root -p mydb > mydb_backup.sql
# 备份指定表
mysqldump -u root -p mydb users orders > tables_backup.sql
# 备份所有数据库
mysqldump -u root -p --all-databases > all_backup.sql
# 只导出结构（不含数据）
mysqldump -u root -p --no-data mydb > schema.sql
# 只导出数据（不含结构）
mysqldump -u root -p --no-create-info mydb > data.sql

# 恢复数据库
mysql -u root -p mydb < mydb_backup.sql
# 恢复时创建数据库
mysql -u root -p < all_backup.sql
# 在 MySQL 内恢复
SOURCE /path/to/backup.sql;

-- 导出为 CSV（需要 FILE 权限）
SELECT * FROM users
INTO OUTFILE '/tmp/users.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

-- 从 CSV 导入
LOAD DATA INFILE '/tmp/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

-- 查看 binlog 是否开启
SHOW VARIABLES LIKE 'log_bin';
-- 查看所有 binlog 文件
SHOW BINARY LOGS;
-- 查看 binlog 内容
SHOW BINLOG EVENTS IN 'mysql-bin.000001' LIMIT 20;
-- 用 mysqlbinlog 工具查看
# mysqlbinlog mysql-bin.000001
-- 基于 binlog 做时间点恢复
# mysqlbinlog --start-datetime='2024-01-01 00:00:00' \
#   --stop-datetime='2024-01-01 12:00:00' \
#   mysql-bin.000001 | mysql -u root -p

-- 基本用法
EXPLAIN SELECT * FROM users WHERE name = '张三';
-- 详细格式
EXPLAIN FORMAT=JSON
SELECT * FROM users WHERE name = '张三';
-- 关键字段说明：
-- type: ALL(全表扫描) > index > range > ref > eq_ref > const
-- key:  实际使用的索引
-- rows: 预估扫描行数
-- Extra: Using index(覆盖索引), Using filesort(需要排序)

-- 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
-- 分析慢查询日志
# mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

-- 查看当前所有进程
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;
-- 杀掉某个长时间运行的查询
KILL <process_id>;
-- 查看最大连接数
SHOW VARIABLES LIKE 'max_connections';
-- 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';

-- 分析表（更新索引统计信息）
ANALYZE TABLE users;
-- 优化表（回收碎片空间）
OPTIMIZE TABLE users;
-- 检查表完整性
CHECK TABLE users;
-- 修复表
REPAIR TABLE users;
-- 查看表状态
SHOW TABLE STATUS LIKE 'users';

-- 查看 InnoDB 引擎状态
SHOW ENGINE INNODB STATUS;
-- 查看全局状态变量
SHOW GLOBAL STATUS;
-- 查看缓冲池命中率
SHOW STATUS LIKE 'Innodb_buffer_pool%';
-- 查看系统变量
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
-- 查看表空间使用
SELECT table_name,
  ROUND(data_length/1024/1024, 2) AS data_mb,
  ROUND(index_length/1024/1024, 2) AS index_mb
FROM information_schema.tables
WHERE table_schema = 'mydb'
ORDER BY data_length DESC;

SELECT
  name, department, salary,
  ROW_NUMBER() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS rn
FROM employees;

SELECT name, score,
  RANK() OVER (ORDER BY score DESC) AS rank_with_gap,
  DENSE_RANK() OVER (ORDER BY score DESC) AS rank_no_gap
FROM students;

SELECT
  month, revenue,
  LAG(revenue, 1) OVER (ORDER BY month) AS prev_month,
  revenue - LAG(revenue, 1) OVER (ORDER BY month) AS growth
FROM monthly_sales;

SELECT
  order_date, amount,
  SUM(amount) OVER (ORDER BY order_date) AS running_total,
  AVG(amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d
FROM orders;

SELECT name, salary,
  NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

WITH active_users AS (
  SELECT * FROM users
  WHERE status = 'active'
    AND last_login > DATE_SUB(NOW(), INTERVAL 30 DAY)
)
SELECT department, COUNT(*) AS cnt
FROM active_users
GROUP BY department;

WITH
  dept_stats AS (
    SELECT department_id, AVG(salary) AS avg_sal
    FROM employees GROUP BY department_id
  ),
  high_earners AS (
    SELECT e.*, d.avg_sal
    FROM employees e
    JOIN dept_stats d ON e.department_id = d.department_id
    WHERE e.salary > d.avg_sal * 1.5
  )
SELECT * FROM high_earners ORDER BY salary DESC;

WITH RECURSIVE org_tree AS (
  -- 锚定成员：找到根节点
  SELECT id, name, manager_id, 1 AS level
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  -- 递归成员：逐层展开
  SELECT e.id, e.name, e.manager_id, t.level + 1
  FROM employees e
  JOIN org_tree t ON e.manager_id = t.id
)
SELECT * FROM org_tree ORDER BY level, name;

CREATE TABLE events (
  id BIGINT PRIMARY KEY,
  data JSON NOT NULL
);

INSERT INTO events VALUES
  (1, '{"type": "click", "page": "/home"}');

-- 提取值
SELECT
  data->>'$.type' AS event_type,
  data->>'$.page' AS page
FROM events;

-- 提取
SELECT JSON_EXTRACT(data, '$.tags[0]') FROM events;
-- ->> 是 JSON_UNQUOTE(JSON_EXTRACT(...)) 的简写
SELECT data->>'$.name' FROM events;
-- 包含判断
SELECT * FROM events
WHERE JSON_CONTAINS(data, '"click"', '$.type');
-- 键是否存在
SELECT * FROM events
WHERE JSON_CONTAINS_PATH(data, 'one', '$.email');

-- 设置（存在则覆盖，不存在则新增）
UPDATE events SET data = JSON_SET(data, '$.status', 'done');
-- 插入（仅不存在时新增）
UPDATE events SET data = JSON_INSERT(data, '$.priority', 'high');
-- 删除
UPDATE events SET data = JSON_REMOVE(data, '$.temp');
-- 数组追加
UPDATE events SET data = JSON_ARRAY_APPEND(data, '$.tags', 'new');

SELECT jt.*
FROM events,
  JSON_TABLE(
    data, '$.items[*]' COLUMNS (
      item_id INT PATH '$.id',
      item_name VARCHAR(100) PATH '$.name',
      quantity INT PATH '$.qty' DEFAULT '0' ON EMPTY
    )
  ) AS jt
WHERE jt.quantity > 0;

ALTER TABLE events ADD CONSTRAINT chk_data
  CHECK (JSON_SCHEMA_VALID('{
    "type": "object",
    "required": ["type", "page"],
    "properties": {
      "type": {"type": "string"},
      "page": {"type": "string"}
    }
  }', data));