MySQL 控制台操作大全：連接、授權、庫表結構、數據操作、備份恢復、導入導出

Windows 命令行下 MySQL 客戶端全流程操作速查，適合初學者離線學習。

1. 連接與退出

cd C:\MySQL5\bin
mysql -uroot -p          # 本地，提示輸入密碼
mysql -h10.0.0.1 -uroot -p'pwd' -P3306  # 遠程
exit | \q                # 退出

2. 修改密碼

mysqladmin -uroot -p'old' password 'new'
# 首次無密：mysqladmin -uroot password 'new'

3. 用戶授權

GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO 'app'@'%' IDENTIFIED BY 'pwd';
GRANT SELECT,INSERT,UPDATE,DELETE ON db.* TO 'app'@'localhost' IDENTIFIED BY 'pwd';
FLUSH PRIVILEGES;

4. 庫表結構操作

SHOW DATABASES;
CREATE DATABASE school CHARSET utf8mb4;
USE school;
CREATE TABLE teacher (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name CHAR(10) NOT NULL,
  address VARCHAR(50) DEFAULT '深圳',
  year DATE
);
DESC teacher;
SHOW CREATE TABLE teacher;
DROP TABLE teacher;
DROP DATABASE school;

5. 數據增刪改查

INSERT INTO teacher VALUES (NULL,'allen','大連一中','1976-10-10');
INSERT INTO teacher VALUES (NULL,'jack','大連二中','1975-12-23');
SELECT * FROM teacher;
UPDATE teacher SET address='北京' WHERE name='allen';
DELETE FROM teacher WHERE name='jack';
TRUNCATE TABLE teacher;

6. 文本導入導出

# 導出 CSV（字段用 TAB，NULL 用 \N）
SELECT * INTO OUTFILE 'C:/teacher.txt' FROM teacher;
# 導入
LOAD DATA LOCAL INFILE 'C:/teacher.txt' INTO TABLE teacher;
# 導入 SQL 腳本
SOURCE C:/school.sql;
# 或命令行
mysql -uroot -p school < C:/school.sql

7. 備份恢復

mysqldump -uroot -p school > school.sql
mysqldump -uroot -p school teacher > teacher.sql
mysqldump -uroot -p -d school > struct.sql
mysql -uroot -p school < school.sql

8. 批量執行技巧

• 忘記分號：另起一行輸入 ; 提交。
• 多行命令：\c 取消當前語句。
• 歷史命令：↑/↓ 翻閱。
• 幫助：help / help contents。