1. 連接與認證
# 本地連接
mysql -u root -p
# 遠程連接
mysql -h 192.168.1.100 -u root -p
# 指定端口/協議/壓縮
mysql -h host -P 3306 -u user -p --protocol=tcp --compress
# 退出
EXIT; \q Ctrl+D
2. 密碼管理
# 修改當前用戶密碼
SET PASSWORD = 'new_pass'; -- 5.7.6+
ALTER USER USER() IDENTIFIED BY 'new_pass';
# 管理員修改指定用戶
ALTER USER 'user'@'host' IDENTIFIED BY 'new_pass';
# mysqladmin 方式
mysqladmin -u root -p'old' password 'new'
3. 用戶與權限
# 創建用戶
CREATE USER 'app'@'%' IDENTIFIED BY 'pass';
# 授權（庫級）
GRANT SELECT, INSERT, UPDATE, DELETE ON db.* TO 'app'@'%';
# 授權（全局）
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'10.%' WITH GRANT OPTION;
# 查看權限
SHOW GRANTS FOR 'app'@'%';
# 回收權限
REVOKE DELETE ON db.* FROM 'app'@'%';
# 刪除用戶
DROP USER 'app'@'%';
# 刷新權限（直接修改 grant 表時需執行）
FLUSH PRIVILEGES;
4. 庫操作
CREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
SHOW DATABASES;
USE db_name;
DROP DATABASE db_name;
ALTER DATABASE db_name CHARACTER SET utf8mb4;
5. 表操作
-- 創建
CREATE TABLE t (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL DEFAULT '',
status TINYINT NOT NULL DEFAULT 0,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 查看結構
DESC t;
SHOW CREATE TABLE t\G
SHOW FULL COLUMNS FROM t;
-- 修改
ALTER TABLE t ADD COLUMN email VARCHAR(100) AFTER name;
ALTER TABLE t MODIFY COLUMN name VARCHAR(100);
ALTER TABLE t DROP COLUMN email;
ALTER TABLE t ADD INDEX idx_status (status);
ALTER TABLE t ADD PRIMARY KEY (id);
ALTER TABLE t ADD UNIQUE uk_email (email);
ALTER TABLE t DROP INDEX idx_status;
-- 重命名
RENAME TABLE t TO t_new;
-- 刪除
DROP TABLE t;
TRUNCATE TABLE t;
6. 數據操作 (DML)
-- 插入
INSERT INTO t (name, status) VALUES ('A', 1), ('B', 2);
INSERT INTO t (name) SELECT name FROM t_old;
-- 查詢
SELECT * FROM t WHERE status=1 ORDER BY id DESC LIMIT 10;
SELECT COUNT(*) FROM t;
SELECT status, COUNT(*) FROM t GROUP BY status;
-- 更新
UPDATE t SET status=2 WHERE id=100;
UPDATE t SET status=2 WHERE id IN (SELECT id FROM t WHERE status=1 LIMIT 100);
-- 刪除
DELETE FROM t WHERE status=0 LIMIT 1000;
TRUNCATE TABLE t;
7. 備份與恢複
# 導出整庫
mysqldump -u root -p db_name > dump.sql
# 導出單表
mysqldump -u root -p db_name table_name > table.sql
# 導出結構不導數據
mysqldump -u root -p -d db_name > schema.sql
# 導出帶觸發器/存儲過程
mysqldump -u root -p -R -E db_name > full.sql
# 壓縮導出
mysqldump -u root -p db_name | gzip > dump.sql.gz
# 恢複
mysql -u root -p db_name < dump.sql
zcat dump.sql.gz | mysql -u root -p db_name
# 導出 CSV
SELECT * INTO OUTFILE '/tmp/export.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n' FROM t;
8. 系統狀態與性能
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';
SET NAMES utf8mb4; -- 當前會話
-- 永久生效需改 my.cnf:
# [mysqld] character-set-server=utf8mb4
# [client] default-character-set=utf8mb4
10. 事務控制
START TRANSACTION;
SELECT * FROM accounts WHERE id=1 FOR UPDATE;
UPDATE accounts SET balance=balance-100 WHERE id=1;
UPDATE accounts SET balance=balance+100 WHERE id=2;
COMMIT; -- 或 ROLLBACK;
-- 隔離級別
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@transaction_isolation;
