MySQL常见命令

一、连接与登录

# 本地登录
mysql -uroot -p

# 远程登录
mysql -h192.168.1.10 -P3306 -uuser -p

# 指定库直接登录
mysql -uroot -p db_name

注意：-p后面不能有空格，否则空格也算密码的一部分。

二、数据库（database）操作

-- 查看所有库
SHOW DATABASES;

-- 创建库并指定字符集
CREATE DATABASE IF NOT EXISTS app_db
     DEFAULT CHARACTER SET utf8mb4
     COLLATE utf8mb4_unicode_ci;

-- 切换当前库
USE app_db;

-- 删除库（谨慎！）
DROP DATABASE IF EXISTS app_db;

三、表（table）操作

-- 查看当前库所有表
SHOW TABLES;

-- 查看表结构（三种方式）
DESC users;
SHOW CREATE TABLE users\G
EXPLAIN users;

-- 创建表
CREATE TABLE users (
  id        INT AUTO_INCREMENT PRIMARY KEY,
  username  VARCHAR(50) NOT NULL UNIQUE,
  email     VARCHAR(100),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 删除表
DROP TABLE IF EXISTS users;

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

-- 清空表（自增 ID 归零）
TRUNCATE TABLE users;

注意：DELETE FROM users 会逐行删，可回滚；TRUNCATE 直接删文件，不可回滚。

四、索引操作

-- 创建普通索引
CREATE INDEX idx_username ON users(username);

-- 创建联合索引
CREATE INDEX idx_user_email ON users(username, email);

-- 查看索引
SHOW INDEX FROM users;

-- 删除索引
DROP INDEX idx_username ON users;

-- 强制使用索引（调优时）
SELECT * FROM users FORCE INDEX(idx_username) WHERE username='bob';

五、数据增删改查（CRUD）

-- 插入
INSERT INTO users(username, email)
VALUES ('alice','alice@a.com'), ('bob','bob@b.com');

-- 批量插入忽略重复
INSERT IGNORE INTO users(username) VALUES ('alice'), ('alice');

-- 更新
UPDATE users SET email='new@a.com' WHERE id=1;

-- 删除
DELETE FROM users WHERE id=1;

-- 查询
SELECT id, username FROM users
WHERE email LIKE '%@a.com'
ORDER BY id DESC
LIMIT 10 OFFSET 20;

六、事务控制

START TRANSACTION;

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

COMMIT;   -- 提交
-- ROLLBACK;  -- 回滚

注意：MyISAM 不支持事务，确保表是 InnoDB。

七、用户与权限

-- 创建用户并授权
CREATE USER 'app_user'@'%' IDENTIFIED BY 'StrongPass!';
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_user'@'%';
FLUSH PRIVILEGES;

-- 查看权限
SHOW GRANTS FOR 'app_user'@'%';

-- 修改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPass!';

-- 回收权限
REVOKE DELETE ON app_db.* FROM 'app_user'@'%';

八、备份与恢复

# 逻辑备份（全库）
mysqldump -uroot -p --single-transaction --routines --triggers --events app_db > app_db.sql

# 仅备份结构
mysqldump -uroot -p -d app_db > app_db_schema.sql

# 恢复
mysql -uroot -p app_db < app_db.sql

注意：--single-transaction 只在 InnoDB 一致性快照有效；MyISAM 会锁表。

九、性能&状态

-- 查看服务器状态
SHOW GLOBAL STATUS LIKE 'Threads_connected';

-- 查看慢查询是否开启
SHOW VARIABLES LIKE 'slow_query_log%';

-- 查看当前运行线程
SHOW PROCESSLIST;

-- 分析 SQL 执行计划
EXPLAIN SELECT * FROM users WHERE username='alice'\G

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

十、变量与调优

-- 查看最大连接数
SHOW VARIABLES LIKE 'max_connections';

-- 临时调大（重启失效）
SET GLOBAL max_connections = 500;

-- 永久修改需写 /etc/my.cnf
[mysqld]
max_connections = 500

十一、元数据&搜索

-- 模糊搜索表名
SHOW TABLES LIKE '%order%';

-- 查看字段注释
SELECT column_name, column_comment
FROM information_schema.columns
WHERE table_schema='app_db' AND table_name='users';

-- 查看库大小
SELECT table_schema,
       ROUND(SUM(data_length+index_length)/1024/1024,2) AS 'MB'
FROM information_schema.tables
GROUP BY table_schema;

十二、实用单行命令

# 修改 root 密码免交互
mysqladmin -uroot -pOldPass password 'NewPass'

# 执行一条 SQL 并退出
mysql -uroot -p -e "SELECT NOW();"

# 把查询结果导出 CSV
mysql -uroot -p -e "SELECT * FROM users" app_db \
  --batch --silent \
  | sed 's/\t/","/g;s/^/"/;s/$/"/' > users.csv