MySQL 命令列核心操作全攻略：從安裝到表結構管理實戰

MySQL 命令列核心操作全攻略：從安裝到表結構管理實戰

命令列是 MySQL 最高效、最通用的管理介面。本文系統整理從服務管理、帳號權限、庫表結構到資料增刪改查的完整操作指南，並針對常見陷阱（如外鍵約束、自增主鍵、註釋語法）提供最佳實踐，適合初學者建立知識體系、資深者作為速查手冊。

一、服務管理與連線認證

Windows 環境

# 註冊為系統服務（需管理員權限）
mysqld --install MySQL80
# 啟動/停止服務
net start MySQL80
net stop MySQL80

Linux 環境 (systemd)

systemctl start mysqld
systemctl enable mysqld  # 開機自啟
systemctl status mysqld

標準連線方式

# 交互式連線（推薦，密碼不顯示在歷史）
mysql -u root -p

# 非交互式執行單條 SQL
mysql -u root -p -e "SHOW DATABASES;"

# 指定協議/端口/Socket
mysql -h 192.168.1.10 -P 3306 -u root -p
mysql -S /tmp/mysql.sock -u root -p  # 本地 Socket 連線，繞過網路層

二、使用者與權限體系

查看現有使用者

USE mysql;
SELECT User, Host, plugin, authentication_string FROM user;
-- 關鍵欄位：Host 決定允許連線來源（% 為任意 IP，localhost 僅本地 Socket）

建立使用者（MySQL 5.7+ / 8.0 語法）

-- 8.0 推薦：建立 + 指定認證插件
CREATE USER 'zhangxiansen'@'%' IDENTIFIED WITH mysql_native_password BY 'StrongPass@123';
-- 或 5.7 相容語法
CREATE USER 'zhangxiansen'@'localhost' IDENTIFIED BY 'StrongPass@123';

權限授予模板

-- 全庫全表所有權限（生產慎用）
GRANT ALL PRIVILEGES ON *.* TO 'zhangxiansen'@'%' WITH GRANT OPTION;

-- 特定庫讀寫權限（應用帳號標準）
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON `app_db`.* TO 'app_user'@'%';

-- 僅讀權限（報表/分析帳號）
GRANT SELECT ON `app_db`.* TO 'readonly'@'%';

-- 刷新權限生效
FLUSH PRIVILEGES;

常見權限誤區

• GRANT ALL ON *.* 包含 SHUTDOWN、FILE 等高危權限，**生產嚴禁**
• Host 欄位區分 localhost（Socket）與 127.0.0.1（TCP），連線協議不匹配會拒絕
• 密碼策略：validate_password 插件強制長度/複雜度，建立前檢查 SHOW VARIABLES LIKE 'validate_password%'

三、資料庫與表結構管理

資料庫操作

CREATE DATABASE `test_data` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SHOW DATABASES;
USE `test_data`;
DROP DATABASE `test_data`;

建表最佳實踐範本

CREATE TABLE `stu` (
  `stuId` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '學生ID',
  `stuName` VARCHAR(15) NOT NULL COMMENT '姓名',
  `roomId` INT UNSIGNED NULL COMMENT '教室ID',
  `age` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年齡',
  PRIMARY KEY (`stuId`),
  KEY `idx_room` (`roomId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='學生表';

關鍵點：

• 明確指定 ENGINE=InnoDB、CHARSET=utf8mb4
• 數值型優先 UNSIGNED、NOT NULL、DEFAULT
• 註釋 COMMENT 覆蓋表/欄位，利於數據字典生成
• 最後一欄後**無逗號**，右括號後**加分號**

結構變更操作

四、約束管理：主鍵、外鍵、唯一索引

主鍵定義

-- 單欄主鍵（建表時）
CREATE TABLE t (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT);

-- 多欄聯合主鍵
CREATE TABLE rectangle (
  wide INT UNSIGNED NOT NULL,
  len INT UNSIGNED NOT NULL,
  PRIMARY KEY (wide, len)
);

外鍵約束（InnoDB 專屬）

-- 建表時定義
CREATE TABLE `desk` (
  `deskId` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  `roomId` INT UNSIGNED NOT NULL,
  CONSTRAINT `fk_desk_room` FOREIGN KEY (`roomId`) REFERENCES `classRoom`(`roomId`)
) ENGINE=InnoDB;

-- 事後添加
ALTER TABLE `stu` 
  ADD CONSTRAINT `fk_stu_room` FOREIGN KEY (`roomId`) REFERENCES `classRoom`(`roomId`)
  ON UPDATE CASCADE ON DELETE RESTRICT;

外鍵行為選項：

• CASCADE：級聯更新/刪除
• SET NULL：置空（需欄位允許 NULL）
• RESTRICT/NO ACTION：阻止父表操作（預設）

查看表結構與約束

DESC `stu`;                    -- 簡略結構
SHOW CREATE TABLE `stu`\G;     -- 完整 DDL（含約束、索引、引擎、編碼）
SHOW INDEX FROM `stu`;         -- 索引詳情

五、資料增刪改查 (CRUD) 核心語法

-- 插入（自增欄位可省略或指定 DEFAULT/NULL）
INSERT INTO classRoom (roomName) VALUES ('402'), ('403');

-- 插入外鍵關聯資料（父表必須存在對應值）
INSERT INTO desk (roomId) VALUES (1);

-- 查詢
SELECT * FROM classRoom WHERE roomId = 1;
SELECT roomName FROM classRoom WHERE roomId = 1;

-- 更新
UPDATE classRoom SET roomName = '403' WHERE roomId = 1;

-- 刪除（受外鍵約束影響）
DELETE FROM classRoom WHERE roomId = 1;  -- 若 desk 表有 roomId=1 且 FK=RESTRICT，將報錯

六、進階技巧與避坑清單

1. 安全刪除/更新：生產環境執行 DELETE/UPDATE 前，必先用 SELECT 驗證 WHERE 條件精準度
2. 批次操作：大量寫入使用 INSERT ... VALUES (...),(...),(...) 或 LOAD DATA INFILE
3. 事務控制：SET autocommit=0; BEGIN; ... COMMIT/ROLLBACK; 確保原子性
4. 編碼一致性：連線字符集 SET NAMES utf8mb4 必須與表/庫/應用層完全一致
5. 備份習慣：結構變更前執行 mysqldump -d db table > backup.sql

熟練掌握命令列操作不僅能提升效率，更能在無圖形介面的伺服器環境中從容應對各類運維場景。建立標準化的操作規範（命名慣例、註釋規範、變更流程），是團隊協作高效、資產可追溯的關鍵。

1. 上一篇文章： MySQL SQL_MODE 深度解析：從 ANSI 到 TRADITIONAL 的行為差異實測
2. 下一篇文章： MySQL DDL 語句完整手冊：庫表結構管理的核心語法與細節陷阱

MySQL 綜合教學

Navicat 遠程連接 MySQL 失敗？完整排查與解決方案（含權限、防火牆、綁定地址）

現象描述 本地 Navicat 連接遠程 MySQL 報錯：

測試 Kettle 增量更新是否支援級聯操作——不支援（基於 MySQL）

背景與目的 嘗試驗證 Pentaho Data Integr

Ubuntu 15.04 安裝 MySQL 與 MySQL Workbench 圖形化管理工具完整教學

Ubuntu 15.04 安裝 MySQL 與 MySQL

JDBC 實現寵物信息管理系統：分層架構、連接池、DAO 模式完整源碼

JDBC 實現寵物信息管理系統：分層架構、DBCP 連接池、