您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 命令大全：從入門到精通的完整參考手冊

編輯：MySQL 資料庫相關知識

一、連接與基礎操作

1.1 連接數據庫

# 本地連接
mysql -u root -p

# 遠程連接
mysql -h 192.168.1.100 -u root -p'password' -P 3306

# 退出
exit / quit / \q

1.2 修改密碼

# 5.7.6+ 推薦
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPass123!';

# 通用 (含舊版)
SET PASSWORD = PASSWORD('NewPass123!');
# 或
mysqladmin -u root -p'oldpass' password 'newpass'

二、用戶與權限管理

2.1 創建用戶

CREATE USER 'app_user'@'%' IDENTIFIED BY 'AppPass123!';
CREATE USER 'readonly'@'10.0.0.%' IDENTIFIED BY 'ReadOnly123!';

2.2 授權

# 所有權限、所有庫表
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;

# 特定庫讀寫
GRANT SELECT,INSERT,UPDATE,DELETE ON app_db.* TO 'app_user'@'%';

# 僅讀
GRANT SELECT ON *.* TO 'readonly'@'10.0.0.%';

# 刷新生效
FLUSH PRIVILEGES;

2.3 查看/撤銷權限

SHOW GRANTS FOR 'app_user'@'%';
REVOKE DELETE ON app_db.* FROM 'app_user'@'%';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'app_user'@'%';
DROP USER 'app_user'@'%';

三、數據庫操作 (DDL)

3.1 創建/顯示/刪除/使用


CREATE DATABASE IF NOT EXISTS app_db CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
SHOW DATABASES;
DROP DATABASE IF EXISTS app_db;
USE app_db;
SELECT DATABASE();  -- 當前庫

3.2 字符集處理


# 配置文件
[mysqld] character_set_server=utf8mb4
[client] default-character-set=utf8mb4

# 連接參數 (JDBC)
jdbc:mysql://host:3306/db?useUnicode=true&characterEncoding=utf8mb4

四、表操作 (DDL/DML)

4.1 創建表


CREATE TABLE users (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    password_hash CHAR(60) NOT NULL,
    status TINYINT DEFAULT 1,
    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;

4.2 修改表結構


# 添加列
ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;

# 修改列
ALTER TABLE users MODIFY COLUMN phone VARCHAR(30);
ALTER TABLE users CHANGE COLUMN phone mobile VARCHAR(20);

# 刪除列
ALTER TABLE users DROP COLUMN mobile;

# 添加/刪除索引
ALTER TABLE users ADD INDEX idx_email (email);
ALTER TABLE users DROP INDEX idx_email;

# 修改表名
RENAME TABLE users TO app_users;
ALTER TABLE users RENAME TO app_users;

4.3 數據增刪改查 (DML)


# 插入
INSERT INTO users (username, email, password_hash) VALUES ('zhangsan', 'zhang@example.com', 'hash123');
INSERT INTO users (username, email, password_hash) VALUES 
('lisi', 'li@example.com', 'hash456'),
('wangwu', 'wang@example.com', 'hash789');

# 查詢
SELECT id, username, email FROM users WHERE status=1 ORDER BY created_at DESC LIMIT 10;
SELECT * FROM users WHERE username LIKE 'zhang%';

# 更新
UPDATE users SET status=0 WHERE id=1;
UPDATE users SET updated_at=NOW() WHERE id=1;

# 刪除
DELETE FROM users WHERE id=1;
TRUNCATE TABLE users;  -- 清空表，重置自增ID

五、備份與恢復

5.1 mysqldump 邏輯備份


# 全庫備份
mysqldump -u root -p --single-transaction --routines --triggers --events --all-databases > full_backup.sql

# 單庫備份
mysqldump -u root -p --single-transaction app_db > app_db.sql

# 單表備份
mysqldump -u root -p app_db users > users.sql

# 只備份結構
mysqldump -u root -p --no-data app_db > schema.sql

# 壓縮備份
mysqldump -u root -p app_db | gzip > app_db_$(date +%F).sql.gz

5.2 恢復


# 全庫恢復
mysql -u root -p < full_backup.sql

# 指定庫恢復
mysql -u root -p app_db < app_db.sql

# 從全庫備份恢復單庫
mysql -u root -p app_db --one-database < full_backup.sql

5.3 定時備份腳本


#!/bin/bash
# /backup/backup_mysql.sh
BACKUP_DIR="/backup/mysql"
DATE=$(date +%F_%H-%M)
DB_USER="backup_user"
DB_PASS="BackupPass123!"

mkdir -p $BACKUP_DIR
mysqldump -u$DB_USER -p$DB_PASS --single-transaction --all-databases | gzip > $BACKUP_DIR/full_$DATE.sql.gz

# 保留 7 天
find $BACKUP_DIR -name "*.gz" -mtime +7 -delete


# crontab -e
0 2 * * * /backup/backup_mysql.sh > /dev/null 2>&1

六、進階功能

6.1 存儲過程


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

CALL GetUserById(1);

6.2 觸發器


DELIMITER //
CREATE TRIGGER trg_user_update
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
END //
DELIMITER ;

6.3 視圖


CREATE VIEW v_active_users AS
SELECT id, username, email FROM users WHERE status=1;

SELECT * FROM v_active_users;

6.4 事務控制


START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- 或 ROLLBACK;

6.5 鎖機制


# 表鎖 (MyISAM)
LOCK TABLES users READ;
UNLOCK TABLES;

# 行鎖 (InnoDB 自動)
SELECT * FROM users WHERE id=1 FOR UPDATE;  -- 排他鎖
SELECT * FROM users WHERE id=1 LOCK IN SHARE MODE;  -- 共享鎖

七、性能診斷命令


SHOW PROCESSLIST;                    -- 當前線程
SHOW ENGINE INNODB STATUS;           -- InnoDB 狀態
SHOW GLOBAL STATUS LIKE 'Slow%';     -- 慢查詢統計
SHOW VARIABLES LIKE 'slow_query%';   -- 慢查詢配置
EXPLAIN SELECT * FROM users WHERE id=1;  -- 執行計劃
ANALYZE TABLE users;                 -- 更新統計信息
OPTIMIZE TABLE users;                -- 整理碎片

八、常用系統函數


# 版本/時間
SELECT VERSION(), NOW(), CURRENT_DATE();

# 字符串
SELECT CONCAT('Hello', ' ', 'World'), UPPER('mysql'), SUBSTRING('MySQL', 1, 2);

# 數學
SELECT ROUND(3.14159, 2), POW(2, 10), RAND();

# 流程控制
SELECT IF(score>=60, 'Pass', 'Fail') FROM exam;
SELECT CASE WHEN score>=90 THEN 'A' WHEN score>=60 THEN 'B' ELSE 'C' END FROM exam;