您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL常用命令大全：連接、用戶管理、權限控制、庫表操作速查手冊

編輯：MySQL 綜合教學

MySQL日常運維開發高頻命令匯總，涵蓋連接認證、賬號體系、權限模型、DDL/DML核心操作，按場景分類整理，適合開發運維隨時查閱。

一、連接認證

標準連接

# 本地連接
mysql -u root -p

# 遠程連接
mysql -h 192.168.1.100 -u dev_user -p -P 3306

# 指定默認數據庫
mysql -u user -p db_name

# 非交互執行單條SQL
mysql -u user -p -e "SELECT VERSION();"

退出

mysql> EXIT;  -- 或 QUIT、\q

二、賬號與密碼管理

修改密碼（5.7.6+ / 8.0+）

# 方式1：ALTER USER（推薦）
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPass123!';

# 方式2：SET PASSWORD
SET PASSWORD FOR 'root'@'localhost' = 'NewPass123!';

# 方式3：mysqladmin（命令行非交互）
mysqladmin -u root -p'oldpass' password 'newpass'

密碼策略配置（validate_password插件）

SHOW VARIABLES LIKE 'validate_password%';
-- 長度、大小寫、數字、特殊字符、字典檢查

三、用戶創建與權限授權（8.0新語法）

創建用戶

CREATE USER 'app_user'@'%' IDENTIFIED BY 'StrongPass123!';
CREATE USER 'readonly'@'192.168.1.%' IDENTIFIED BY 'ReadOnly2024!';

授權模板

-- 核心業務賬號：增刪改查+DDL
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER 
ON mydb.* TO 'app_user'@'%';

-- 只讀賬號
GRANT SELECT ON mydb.* TO 'readonly'@'192.168.1.%';

-- 遷移/備份賬號
GRANT SELECT, LOCK TABLES, SHOW VIEW, PROCESS, REPLICATION CLIENT 
ON *.* TO 'backup_user'@'10.0.0.%';

-- 開發調試賬號（受限）
GRANT SELECT, EXPLAIN ON dev_db.* TO 'dev'@'192.168.%.%';

-- 刷新生效
FLUSH PRIVILEGES;

查看權限

SHOW GRANTS FOR 'app_user'@'%';
SHOW GRANTS; -- 當前用戶

回收權限與刪除用戶

REVOKE INSERT, UPDATE ON mydb.* FROM 'app_user'@'%';
DROP USER 'old_user'@'%';

四、庫表核心操作

數據庫級

CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SHOW DATABASES;
USE mydb;
DROP DATABASE old_db; -- 謹慎！

表級DDL

-- 創建表（InnoDB + utf8mb4 標準範本）
CREATE TABLE users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用戶名',
    email VARCHAR(100) NOT NULL UNIQUE COMMENT '郵箱',
    password_hash CHAR(60) NOT NULL COMMENT 'bcrypt哈希',
    status TINYINT DEFAULT 1 COMMENT '1正常 0禁用',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用戶表';

-- 查看表結構
DESC users;
SHOW CREATE TABLE users\G

-- 修改表
ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;
ALTER TABLE users MODIFY COLUMN status TINYINT DEFAULT 1;
ALTER TABLE users DROP INDEX idx_status;
ALTER TABLE users RENAME TO sys_users;

-- 刪除表
DROP TABLE IF EXISTS temp_table;
TRUNCATE TABLE log_table; -- 快速清空，重置自增ID

五、數據操作DML速查

-- 插入
INSERT INTO users (username, email, password_hash) VALUES ('zhangsan', 'zs@ex.com', '$2a$10$...');
INSERT INTO users (username, email) VALUES ('lisi', 'ls@ex.com'), ('wangwu', 'ww@ex.com'); -- 批量

-- 查詢
SELECT id, username FROM users WHERE status=1 ORDER BY created_at DESC LIMIT 20;
SELECT COUNT(*) FROM users WHERE created_at > '2024-01-01';

-- 更新
UPDATE users SET status=0 WHERE id=100;
UPDATE users SET last_login=NOW() WHERE username='zhangsan';

-- 刪除
DELETE FROM users WHERE status=0 AND created_at < '2023-01-01' LIMIT 1000; -- 分批刪除

六、運維檢查命令

-- 服務狀態
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL VARIABLES LIKE 'max_connections';

-- 進程管理
SHOW PROCESSLIST;
KILL 12345; -- 終止會話

-- 慢查詢
SHOW VARIABLES LIKE 'slow_query%';
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

-- 空間佔用
SELECT table_schema, ROUND(SUM(data_length+index_length)/1024/1024,2) AS size_mb
FROM information_schema.tables GROUP BY table_schema ORDER BY size_mb DESC;

-- 備份導出
mysqldump -u root -p --single-transaction --routines --triggers mydb > mydb_$(date +%F).sql