MySQL 命令完整版：從連接認證到備份還原的全棧操作手冊

一、連接與認證

# 基礎連接
mysql -h localhost -u root -p

# 遠程連接
mysql -h 110.110.110.110 -u root -p'password'

# 退出
exit

二、用戶與權限管理

-- 創建用戶（8.0+ 推薦）
CREATE USER 'test1'@'%' IDENTIFIED BY 'abc';
GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO 'test1'@'%';

-- 本地限制用戶
CREATE USER 'test2'@'localhost' IDENTIFIED BY 'abc';
GRANT SELECT,INSERT,UPDATE,DELETE ON mydb.* TO 'test2'@'localhost';

-- 刷新權限（舊版必需）
FLUSH PRIVILEGES;

三、數據庫高級操作

1. 創建與授權一體化

CREATE DATABASE xhkdb;
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON xhkdb.* TO 'xhkdb'@'localhost' IDENTIFIED BY 'pass';
SET PASSWORD FOR 'xhkdb'@'localhost' = PASSWORD('pass');

2. 刪除庫的安全模式

-- 確定存在
DROP DATABASE xhkdb;

-- 不確定存在（不報錯，產生警告）
DROP DATABASE IF EXISTS xhkdb;

3. 庫切換與跨庫查詢

USE db1;
SELECT COUNT(*) FROM mytable;  -- 查 db1.mytable

USE db2;
SELECT COUNT(*) FROM mytable;  -- 查 db2.mytable

-- 跨庫 JOIN
USE db1;
SELECT a.author_name, e.editor_name
FROM author a, db2.editor e
WHERE a.editor_id = e.editor_id;

4. SELECT 多功能用法

-- 版本/時間/計算
SELECT VERSION();
SELECT NOW();
SELECT DAYOFMONTH(CURRENT_DATE);
SELECT ((4*4)/10)+25;

-- 字符串拼接
SELECT CONCAT(f_name, ' ', l_name) AS Name
FROM employee_data
WHERE title = 'Marketing Executive';

四、表操作全集

1. 建表範例

CREATE TABLE MyClass (
    id INT(4) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name CHAR(20) NOT NULL,
    sex INT(4) NOT NULL DEFAULT '0',
    degree DOUBLE(16,2)
);

2. 結構查看與索引含義

DESC MyClass;
SHOW COLUMNS FROM MyClass;

Key 欄位解讀：

• 空：無索引或非前導列
• PRI：主鍵組成部分
• UNI：唯一索引前導列（非空）
• MUL：非唯一索引前導列或唯一復合索引部分列（可為空）

優先級：PRI > UNI > MUL

3. 刪表與數據操作

DROP TABLE MyClass;

INSERT INTO MyClass VALUES (1,'Tom',96.45),(2,'Joan',82.99);
SELECT * FROM MyClass;
SELECT * FROM MyClass ORDER BY id LIMIT 0,2;

DELETE FROM MyClass WHERE id=1;
UPDATE MyClass SET name='Mary' WHERE id=1;

4. 結構變更進階

-- 增加欄位/索引/主鍵/唯一約束
ALTER TABLE MyClass ADD passtest INT(4) DEFAULT '0';
ALTER TABLE employee ADD INDEX emp_name (name);
ALTER TABLE employee ADD PRIMARY KEY (id);
ALTER TABLE employee ADD UNIQUE emp_name2 (cardnumber);
ALTER TABLE employee DROP INDEX emp_name;

-- 修改欄位名/類型/刪除欄位
ALTER TABLE t1 CHANGE a b INTEGER;
ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;
ALTER TABLE t1 MODIFY b BIGINT NOT NULL;
ALTER TABLE t1 DROP COLUMN b;

-- 重命名表
RENAME TABLE MyClass TO YouClass;

五、備份與還原實戰

1. mysqldump 參數詳解

# 全庫
mysqldump -u root -p123456 db > out.sql

# 單表
mysqldump -u root -p db tbl > out.sql

# 僅結構（含 DROP TABLE）
mysqldump -u root -p -d --add-drop-table db > struct.sql

# 指定字符集導出
mysqldump -u root -p --default-character-set=latin1 --set-charset=gbk db > out.sql

2. 實戰備份腳本

cd /home/data/mysql
mysqldump -u root -p --opt aaa > back_aaa

六、完整建庫建表腳本範例

DROP DATABASE IF EXISTS school;
CREATE DATABASE school;
USE school;

CREATE TABLE teacher (
    id INT(3) AUTO_INCREMENT NOT NULL PRIMARY KEY,
    name CHAR(10) NOT NULL,
    address VARCHAR(50) DEFAULT '深圳',
    year DATE
);

INSERT INTO teacher VALUES (NULL,'allen','大連一中','1976-10-10');
INSERT INTO teacher VALUES (NULL,'jack','大連二中','1975-12-23');

執行方式

# 方式一：重定向
mysql -u root -p < c:\school.sql

# 方式二：source 命令
mysql> SOURCE c:\school.sql;

七、DROP TABLE 細節補充

• 帶分區表：永久刪除定義、分區、數據及 .par 文件
• IF EXISTS：防止不存在報錯，產生 Note
• RESTRICT/CASCADE：當前版本暫不生效，保留兼容性

本手冊涵蓋 MySQL 從連接到備份的完整命令鏈路。重點掌握：權限模型（用戶@主機）、字符集一致性、mysqldump 熱備份參數、ALTER TABLE 線上變更限制。實戰中根據版本差異（5.6/5.7/8.0）調整語法細節。