您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

30 分鐘 MySQL 快速入門完全指南：安裝、SQL 基礎、資料型別、索引、視圖、存儲過程、觸發器、事務與鎖

編輯：MySQL 資料庫相關知識

MySQL 是全球最流行的開源關聯式資料庫。本文濃縮核心知識點，涵蓋安裝配置、SQL 四大語言分類、資料型別選擇、索引設計、視圖、存儲過程、觸發器、事務控制與鎖機制，配合實戰範例與最佳實務，適合快速上手與面試複習。

一、MySQL 安裝與客戶端工具

下載與版本選擇

官網：dev.mysql.com/downloads/mysql
版本：LTS 長期支援版 (8.0 LTS, 5.7 EOL 2023/10) → 生產首選 8.0 LTS
安裝類型：Custom 自定義 → 選擇功能、路徑、資料目錄、埠號

客戶端工具

MySQL Workbench：官方 GUI，含建模、管理、SQL 編輯器
命令列：mysql -u root -p
第三方：DBeaver (免費跨平台)、DataGrip (JetBrains)、Navicat (商業)

二、SQL 四大語言分類 (DDL/DML/DCL/TCL)

分類	全名	關鍵字	用途
DDL	Data Definition Language	CREATE, ALTER, DROP, TRUNCATE, RENAME	定義資料庫結構 (庫、表、索引、視圖)
DML	Data Manipulation Language	INSERT, UPDATE, DELETE, SELECT	操作資料列
DCL	Data Control Language	GRANT, REVOKE	權限控制
TCL	Transaction Control Language	COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION	交易控制

三、核心 DDL 操作

資料庫

SHOW DATABASES;
CREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE db_name;
DROP DATABASE db_name;

資料表

-- 建表
CREATE TABLE student (
    sid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    sno INT,
    sname VARCHAR(50) NOT NULL,
    age TINYINT UNSIGNED DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 查看定義
DESC student;
SHOW CREATE TABLE student;

-- 修改表
ALTER TABLE student MODIFY age SMALLINT UNSIGNED;     -- 修改型別
ALTER TABLE student ADD email VARCHAR(100);           -- 新增欄位
ALTER TABLE student DROP COLUMN email;                -- 刪除欄位
ALTER TABLE student CHANGE sname name VARCHAR(50);    -- 重命名+改型別
ALTER TABLE student RENAME TO users;                  -- 重命名表
ALTER TABLE student ADD address VARCHAR(200) FIRST;   -- 指定位置 FIRST/AFTER col

-- 刪除表
DROP TABLE student;

四、DML 核心操作

INSERT

-- 單筆
INSERT INTO student (sno, sname) VALUES (1001, '張三');
-- 多筆 (高效)
INSERT INTO student (sno, sname) VALUES (1002, '李四'), (1003, '王五');
-- 指定自增值
INSERT INTO student (sid, sno, sname) VALUES (100, 1004, '指定ID');

UPDATE (Workbench 預設安全模式需關閉)

UPDATE student SET age = 20 WHERE sid = 1;
-- Workbench 錯誤 1175: 關閉安全模態
-- Edit → Preferences → SQL Editor → 取消 "Safe Updates"

DELETE

DELETE FROM student WHERE sid = 1;

SELECT

SELECT * FROM student WHERE age > 18;

五、表連接 (JOIN)

-- 內連接
SELECT c.cno, c.cname, s.sname 
FROM student s INNER JOIN course c ON s.sno = c.cno;
-- 等價舊語法
SELECT c.cno, c.cname, s.sname FROM student s, course c WHERE s.sno = c.cno;

-- 左連接 (保留左表所有)
SELECT s.sname, c.cname FROM student s LEFT JOIN course c ON s.sno = c.cno;

-- 右連接 (保留右表所有)
SELECT s.sname, c.cname FROM student s RIGHT JOIN course c ON s.sno = c.cno;

六、子查詢與集合運算

-- 子查詢
SELECT * FROM student WHERE sid IN (SELECT sid FROM score WHERE score > 90);

-- UNION / UNION ALL
SELECT * FROM t1 UNION ALL SELECT * FROM t2; -- 直接合併
SELECT * FROM t1 UNION SELECT * FROM t2;     -- 去重 (隱含 DISTINCT)

七、MySQL 資料型選擇指南

類別	型別	儲存/範圍	選擇建議
數值	TINYINT	1 byte, -128~127 / 0~255	布林、狀態碼、小範圍計數
	SMALLINT	2 bytes	中等範圍
	INT / MEDIUMINT	4 / 3 bytes	通用整數、PK (INT 最佳平衡)
	BIGINT	8 bytes	大數值、分散式 ID
	DECIMAL(p,s)	p+2 bytes	金額、精確計算，避免 FLOAT/DOUBLE 誤差
日期時間	DATE	3 bytes, 1000-01-01 ~ 9999-12-31	純日期
	DATETIME	8 bytes, 同 DATE + 時間	日期時間，不受時區影響
	TIMESTAMP	4 bytes, 1970-2038	受時區影響，自動更新，首個 TIMESTAMP 預設 CURRENT_TIMESTAMP
字串	CHAR(n)	固定長度，右補空格	定長代碼 (MD5, UUID, 國籍代碼)
	VARCHAR(n)	變長，1-2 bytes 長度前綴	通用字串，InnoDB 無固定/變長效能差異
	TEXT / BLOB	64KB~4GB	大文本/二進位，TEXT 存字串，BLOB 存二進位 (圖片、檔案)

ZEROFILL 與顯示寬度

CREATE TABLE t (age INT(5) ZEROFILL);
INSERT INTO t VALUES (1); -- 顯示 00001，實際儲存 1
-- 顯示寬度不影響儲存範圍，僅客戶端顯示

八、運算子與常用函數

類別	運算子/函數
算術	+, -, *, /, DIV (整除), % (MOD)
比較	=, !=, <>, <, >, <=, >=, <=>, IS NULL, IS NOT NULL, BETWEEN, IN, LIKE, REGEXP
邏輯	AND, OR, NOT, XOR
位運算	&, \|, ^, ~, <<, >>
字串函數	CONCAT, SUBSTRING, TRIM, REPLACE, UPPER, LOWER, LENGTH, CHAR_LENGTH, LOCATE
數值函數	ROUND, CEIL, FLOOR, ABS, RAND, POW, SQRT
日期時間	NOW(), CURDATE(), CURTIME(), DATE_ADD, DATE_SUB, DATEDIFF, DATE_FORMAT, STR_TO_DATE
流程控制	IF, CASE, IFNULL, COALESCE, NULLIF

九、索引設計與使用

建立索引


CREATE TABLE idx_test (
    id INT AUTO_INCREMENT PRIMARY KEY,
    member_id INT NOT NULL,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    order_serial VARCHAR(50) NOT NULL
);
CREATE UNIQUE INDEX ix_order_serial ON idx_test (order_serial);
-- 或建表時
CREATE TABLE idx_test (
    ...
    UNIQUE KEY ix_order_serial (order_serial)
);

索引設計原則

WHERE/JOIN/ORDER BY 欄位優先，非 SELECT 清單欄位
高基數 (Cardinality) 欄位：值分布越分散越好 (如訂單號、身分證)，低基數 (性別、狀態) 索引效果差
避免過度索引：每個索引佔空間、降低寫入速度、增加優化器負擔
複合索引最左前綴原則：INDEX (a,b,c) 支援 a, a,b, a,b,c
覆蓋索引：查詢欄位全在索引中，避免回表

執行計畫分析

EXPLAIN SELECT * FROM idx_test WHERE order_serial = 'sz121213';
-- 關鍵欄位: type (ref/eq_ref/all), key (使用索引), rows (預估掃描行), Extra (Using index/where/filesort)

十、視圖


CREATE OR REPLACE VIEW v_idx AS SELECT * FROM idx_test;
SELECT * FROM v_idx;
SHOW TABLES; -- 視圖也會列出
DROP VIEW v_idx;

視圖優勢

簡化複雜查詢 (封裝 JOIN/WHERE/聚合)
安全性 (僅暴露特定欄位/列)
資料獨立性 (底表增欄不影響視圖)

十一、存儲過程與函數

建立與呼叫


DELIMITER $$
CREATE PROCEDURE sp_get_order(IN p_serial VARCHAR(50))
READS SQL DATA
BEGIN
    SELECT * FROM orders WHERE serial_number = p_serial;
END $$
DELIMITER ;

CALL sp_get_order('sz12234222');

DELIMITER $$ 變更結束符，避免過程內分號衝突
READS SQL DATA / MODIFIES SQL DATA / NO SQL 特性聲明

變量、條件處理、游標


DECLARE v_count INT DEFAULT 0;
SET v_count = 10;
SELECT COUNT(*) INTO v_count FROM orders;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET @done = 1;

DECLARE cur CURSOR FOR SELECT * FROM orders;
OPEN cur;
FETCH cur INTO @id, @serial;
CLOSE cur;

事件調度器


SET GLOBAL event_scheduler = ON;
CREATE EVENT e_insert_order
ON SCHEDULE EVERY 5 SECOND
DO INSERT INTO orders (member_id, serial_number, amount) VALUES (1, 'auto', 10);

ALTER EVENT e_insert_order DISABLE;
DROP EVENT e_insert_order;

十二、觸發器


DELIMITER $$
CREATE TRIGGER trg_order_log
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    INSERT INTO order_log (content) VALUES (NEW.serial_number);
END $$
DELIMITER ;

支援 BEFORE/AFTER × INSERT/UPDATE/DELETE
行級觸發 (FOR EACH ROW)，使用 OLD/NEW 存取舊/新值
不支援陳述式級觸發

十三、事務與鎖

表鎖 (MyISAM/InnoDB 皆支援)


LOCK TABLES orders READ;   -- 共享鎖，可讀不可寫
LOCK TABLES orders WRITE;  -- 排他鎖，獨佔
UNLOCK TABLES;

行鎖與事務 (InnoDB 核心)


START TRANSACTION;
UPDATE orders SET amount = 100 WHERE id = 1;
-- SELECT * FROM orders WHERE id = 1 FOR UPDATE; -- 排他鎖
-- SELECT * FROM orders WHERE id = 1 LOCK IN SHARE MODE; -- 共享鎖
COMMIT; -- 或 ROLLBACK;

隔離級別

級別	髒讀	不可重複讀	幻讀	MySQL 預設
READ UNCOMMITTED	Y	Y	Y
READ COMMITTED	N	Y	Y
REPEATABLE READ	N	N	Y (間隙鎖解決)	✅
SERIALIZABLE	N	N	N

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

十四、總結與避坑指南

坑點	正確做法
用 FLOAT/DOUBLE 存金額	用 DECIMAL(18,2) 或 BIGINT 存分
VARCHAR 當 PK	優先 INT/BIGINT AUTO_INCREMENT
SELECT * 生產環境	明確列出欄位，利用覆蓋索引
缺乏索引的大表 JOIN	建立適當索引、EXPLAIN 驗證
長事務未提交	最小化事務範圍、設定超時
不備份/不驗證備份	每日完整 + 定期記錄備份 + 季度還原演練
root 遠端登入、弱密碼	禁用 root 遠端、建立專用帳號、強密碼、最小權限

MySQL 入門易、精通難。掌握**架構原理 (InnoDB 架構、MVCC、鎖、紅黑樹/B+樹索引)**、**SQL 優化 (EXPLAIN、Profile、慢查詢分析)**、**高可用 (主從複寫、MGR、Orchestrator)**、**備份恢復 (xtrabackup、mydumper、binlog 還原)**，才是進階之路。建議從實戰專案、閱讀官方文檔、參與開源社群開始深入。