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

MySQL 基礎命令大全：連接、庫表操作、用戶權限、備份恢復與常用管理操作速查表

編輯：MySQL 綜合教學

MySQL 命令行核心操作速查手冊 (持續更新)

連接與基礎信息

# 連接本地
mysql -uroot -p'password'

# 連接遠程
mysql -h 192.168.1.100 -uroot -p'password' -P3306

# 查看版本/狀態
STATUS;
SELECT VERSION();

# 查看當前用戶/庫
SELECT USER(), DATABASE();

庫操作

SHOW DATABASES;
CREATE DATABASE db_name CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
USE db_name;
DROP DATABASE db_name;

表操作

SHOW TABLES;
DESC table_name;           -- 結構
SHOW CREATE TABLE table_name;  -- 建表語句

CREATE TABLE t (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  age TINYINT UNSIGNED DEFAULT 0,
  score DECIMAL(5,2),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB CHARSET=utf8mb4;

ALTER TABLE t ADD COLUMN email VARCHAR(100);
ALTER TABLE t MODIFY COLUMN name VARCHAR(100);
ALTER TABLE t DROP COLUMN email;
DROP TABLE t;

數據增刪改查 (DML)

-- 插入
INSERT INTO t(name, age, score) VALUES('張三', 25, 95.5);
INSERT INTO t(name, age, score) VALUES('李四', 30, 88.0), ('王五', 28, 92.5);

-- 查詢
SELECT * FROM t;
SELECT name, score FROM t WHERE age > 25 ORDER BY score DESC LIMIT 10;
SELECT COUNT(*) FROM t;
SELECT AVG(score) FROM t GROUP BY age HAVING AVG(score) > 90;

-- 更新
UPDATE t SET score = 100 WHERE name = '張三';

-- 刪除
DELETE FROM t WHERE id = 1;
TRUNCATE TABLE t;  -- 清空表 (DDL, 不可回滾, 速度快)

用戶與權限管理

-- 創建用戶
CREATE USER 'app'@'10.0.0.%' IDENTIFIED BY 'StrongPass123!';

-- 授權 (最小權限原則)
GRANT SELECT, INSERT, UPDATE, DELETE ON `mydb`.* TO 'app'@'10.0.0.%';
FLUSH PRIVILEGES;

-- 查看權限
SHOW GRANTS FOR 'app'@'10.0.0.%';

-- 修改密碼
ALTER USER 'app'@'10.0.0.%' IDENTIFIED BY 'NewPass456!';  -- 5.7+
SET PASSWORD FOR 'app'@'10.0.0.%' = PASSWORD('NewPass');  -- 5.6

-- 刪除用戶
DROP USER 'app'@'10.0.0.%';

-- 刪除權限 (保留用戶)
REVOKE ALL PRIVILEGES ON `mydb`.* FROM 'app'@'10.0.0.%';

備份與恢復

# 邏輯備份 (mysqldump)
mysqldump -uroot -p'pass' --single-transaction --routines --triggers --events \
  --master-data=2 db_name > backup_$(date +%F).sql

# 壓縮備份
mysqldump ... | gzip > backup_$(date +%F).sql.gz

# 恢復
mysql -uroot -p'pass' db_name < backup.sql
zcat backup.sql.gz | mysql -uroot -p'pass' db_name

# 只導出結構
mysqldump -d -uroot -p'pass' db_name > schema.sql

# 只導出數據
mysqldump -t -uroot -p'pass' db_name > data.sql

服務管理

# Systemd (CentOS 7+/Ubuntu 16+)
systemctl start|stop|restart|status mysqld
systemctl enable mysqld

# SysVinit (CentOS 6)
service mysql start|stop|restart|status
chkconfig mysql on

# 手動腳本
/etc/init.d/mysqld start|stop|restart

常用管理查詢

-- 進程列表
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;  -- 顯示完整 SQL

-- 殺死進程
KILL 12345;

-- 表大小統計
SELECT TABLE_NAME, 
       ROUND(DATA_LENGTH/1024/1024,2) AS data_mb,
       ROUND(INDEX_LENGTH/1024/1024,2) AS index_mb,
       TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'db_name'
ORDER BY (DATA_LENGTH+INDEX_LENGTH) DESC;

-- 索引碎片檢查
ANALYZE TABLE t;
OPTIMIZE TABLE t;  -- 整理碎片 (鎖表)

性能調優常用參數查看

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'max_connections';
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Slow_queries';
SHOW VARIABLES LIKE 'slow_query_log%';