連接與認證
# 本地連接（無密碼Root）
mysql -u root -p
# 遠程連接
mysql -h 192.168.1.100 -u root -p'password' db_name
# 退出
exit / quit / \q
賬號與密碼管理
# 修改當前用戶密碼
SET PASSWORD = PASSWORD('new_pwd');
# Root修改他人密碼
SET PASSWORD FOR 'user'@'host' = PASSWORD('new_pwd');
-- 或 5.7+
ALTER USER 'user'@'host' IDENTIFIED BY 'new_pwd';
# 創建用戶並授權（推薦分離創建與授權）
CREATE USER 'app'@'10.0.%' IDENTIFIED BY 'StrongPass!';
GRANT SELECT,INSERT,UPDATE,DELETE ON biz_db.* TO 'app'@'10.0.%';
數據庫與表操作
| 操作 | 命令 |
|---|
| 顯示所有庫 |
SHOW DATABASES; |
| 創建庫（指定字符集） |
CREATE DATABASE db_name CHARSET utf8mb4 COLLATE utf8mb4_general_ci; |
| 切換庫 |
USE db_name; |
| 顯示當前庫 |
SELECT DATABASE(); |
| 顯示所有表 |
SHOW TABLES; |
| 查看表結構 |
DESC tbl_name; / SHOW CREATE TABLE tbl_name; |
| 創建表 |
CREATE TABLE t (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50)) ENGINE=InnoDB; |
| 刪除庫/表 |
DROP DATABASE db_name; / DROP TABLE tbl_name; |
| 清空表（保留結構/AUTO_INCREMENT） |
TRUNCATE TABLE tbl_name; |
| 刪除表數據（保留AUTO_INCREMENT） |
DELETE FROM tbl_name WHERE 1; |
數據增刪改查 (CRUD)
-- 插入
INSERT INTO tbl (col1, col2) VALUES ('v1', 'v2'), ('v3', 'v4');
-- 查詢
SELECT col1, col2 FROM tbl WHERE col1='v1' ORDER BY col2 DESC LIMIT 10;
-- 更新
UPDATE tbl SET col2='new_v' WHERE col1='v1';
-- 刪除
DELETE FROM tbl WHERE col1='v1';
-- 分頁查詢 (Offset分頁，大偏移性能差)
SELECT * FROM tbl LIMIT 10 OFFSET 20;
-- 游標分頁 (大數據集推薦)
SELECT * FROM tbl WHERE id > 1000 ORDER BY id LIMIT 10;
數據導入導出
文本文件導入
# 字段Tab分隔，換行符\n，NULL用\N
LOAD DATA LOCAL INFILE '/path/data.txt' INTO TABLE tbl
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';
SQL腳本執行
# 客戶端內部
SOURCE /path/script.sql;
# 系統命令行
mysql -u root -p db_name < /path/script.sql
mysqldump 邏輯備份
# 全庫備份（含結構+數據）
mysqldump -u root -p --single-transaction --routines --triggers --events --databases db1 db2 > full_backup.sql
# 單表備份
mysqldump -u root -p db_name tbl_name > tbl_backup.sql
# 僅結構（不含數據）
mysqldump -u root -p -d --add-drop-table db_name > schema_only.sql
# 遠程備份壓縮傳輸
mysqldump -h remote_host -u root -p db_name | gzip > backup_$(date +%F).sql.gz
物理備份 (xtrabackup 推薦)
# 完全備份
xtrabackup --backup --target-dir=/backup/full_$(date +%F) --user=root --password
# 增量備份
xtrabackup --backup --target-dir=/backup/inc_$(date +%F) --incremental-basedir=/backup/full_20230101
實用技巧與快捷鍵
- 多行命令以分號結束，可跨行輸入
↑/↓ 歷史命令導航，Ctrl+R 歷史搜索\G 縱向顯示結果（適合寬表）\c 取消當前輸入tee /path/log.txt 記錄會話輸出，notee 停止system ls -lh 執行Shell命令不退出MySQL
完整建庫建表示例
DROP DATABASE IF EXISTS school;
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 '深圳',
hire_date DATE
) ENGINE=InnoDB;
INSERT INTO teacher (name, address, hire_date) VALUES
('Allen', '大連一中', '1976-10-10'),
('Jack', '大連二中', '1975-12-23');
-- 批量導入
mysql -u root -p school < teacher_data.sql
