一、連線與基礎
# 連線本機
mysql -uroot -p
# 連線遠端
mysql -h 10.0.0.1 -uroot -p123
# 退出
exit
二、用戶與權限
# 建用戶並授權（全庫全權，任意主機）
GRANT ALL PRIVILEGES ON *.* TO 'user1'@'%' IDENTIFIED BY 'pwd123';
FLUSH PRIVILEGES;
# 僅本機、特定庫、讀寫權限
GRANT SELECT,INSERT,UPDATE,DELETE ON mydb.* TO 'user2'@'localhost' IDENTIFIED BY 'pwd123';
# 修改密碼
mysqladmin -uroot -poldpwd password newpwd
# 刪除用戶
DROP USER 'user1'@'%';
三、庫操作
SHOW DATABASES;
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE mydb;
DROP DATABASE mydb;
四、表操作
SHOW TABLES;
DESC users; -- 同 SHOW COLUMNS FROM users
CREATE TABLE users (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users MODIFY name VARCHAR(100);
ALTER TABLE users DROP COLUMN phone;
DROP TABLE users;
TRUNCATE TABLE users; -- 清空資料、重置自增
五、資料操作
INSERT INTO users (name, email) VALUES ('Tom', 'tom@example.com');
SELECT * FROM users WHERE id=1;
UPDATE users SET name='Jerry' WHERE id=1;
DELETE FROM users WHERE id=1;
六、備份與還原
mysqldump 邏輯備份
# 全庫
mysqldump -uroot -p --single-transaction --routines --triggers --events mydb > mydb_full.sql
# 單表
mysqldump -uroot -p mydb users > users.sql
# 僅結構
mysqldump -uroot -p -d mydb > mydb_schema.sql
# 壓縮備份
mysqldump -uroot -p mydb | gzip > mydb_$(date +%F).sql.gz
還原
mysql -uroot -p mydb < mydb_full.sql
# 或進入 mysql 後
SOURCE /path/to/mydb_full.sql;
七、資料匯入匯出
CSV 匯出
SELECT * FROM users INTO OUTFILE '/tmp/users.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
CSV 匯入
LOAD DATA INFILE '/tmp/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
IGNORE 1 LINES;
mysqlimport（命令列）
mysqlimport -uroot -p --fields-terminated-by=',' --fields-enclosed-by='"' --ignore-lines=1 mydb /tmp/users.csv
八、服務管理
net stop mysql
net start mysql
# Linux
systemctl stop mysqld
systemctl start mysqld
九、維護與修復
# 檢查/修復表
mysqlcheck -uroot -p --auto-repair --check --optimize mydb
# 或單表
mysqlcheck -uroot -p --repair mydb users
十、常用查詢技巧
# 當前連線
SHOW PROCESSLIST;
# 慢查詢
SHOW VARIABLES LIKE 'slow_query%';
# 表大小
SELECT TABLE_NAME, ROUND((DATA_LENGTH+INDEX_LENGTH)/1024/1024,2) AS MB
FROM information_schema.TABLES WHERE TABLE_SCHEMA='mydb' ORDER BY MB DESC;
# 找無主鍵表
SELECT TABLE_NAME FROM information_schema.TABLES
WHERE TABLE_SCHEMA='mydb' AND TABLE_TYPE='BASE TABLE'
AND TABLE_NAME NOT IN (SELECT TABLE_NAME FROM information_schema.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE='PRIMARY KEY' AND TABLE_SCHEMA='mydb');
