萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 入門到進階學習筆記:基礎操作、數據類型、查詢優化與觸發器實戰

一、環境準備與連線

  • 安裝 WAMP / XAMPP / Docker MySQL
  • 連線命令:mysql -u root -p -h localhost

二、庫級操作

SHOW DATABASES;
USE db_name;
CREATE DATABASE db_name CHARSET utf8mb4;
DROP DATABASE db_name;

三、表級操作

3.1 基礎 DDL

SHOW TABLES;
DESC table_name;
SHOW CREATE TABLE table_name;

CREATE TABLE user (
    id INT AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL DEFAULT '',
    age TINYINT UNSIGNED NOT NULL DEFAULT 0,
    INDEX idx_id (id)
) ENGINE=InnoDB CHARSET=utf8mb4;

3.2 修改表結構 (ALTER TABLE)

-- 增加欄位
ALTER TABLE tb ADD col_name INT NOT NULL DEFAULT 0;

-- 修改欄位(含重命名)
ALTER TABLE tb CHANGE old_col new_col VARCHAR(50) NOT NULL DEFAULT '';

-- 刪除欄位
ALTER TABLE tb DROP col_name;

-- 主鍵操作
ALTER TABLE tb ADD PRIMARY KEY (id);
ALTER TABLE tb DROP PRIMARY KEY;

-- 索引操作
ALTER TABLE tb ADD UNIQUE INDEX uk_name (name);
ALTER TABLE tb ADD FULLTEXT INDEX ft_content (content);
ALTER TABLE tb DROP INDEX idx_name;

-- 清空表(重置自增 ID)
TRUNCATE TABLE tb;

四、數據類型詳解

類別類型存儲/範圍適用場景
整數TINYINT1B / -128~127狀態碼、布爾值
SMALLINT2B / -32K~32K較小計數
MEDIUMINT3B / -8M~8M中等計數
INT4B / -21E~21E主鍵、通用計數
BIGINT8B / -9E18~9E18大數據 ID、時間戳
浮點/定點FLOAT(M,D)4B / 7 位精度科學計算、非精確金額
DECIMAL(M,D)M+2 B / 精確金融金額、精確計算
字符串CHAR(M)固定長 MMD5、UUID、固定代碼
VARCHAR(M)實際長度+1/2B通用變長文本
TEXT/BLOB65KB~4GB長文本、二進制文件
日期時間DATE3B / 1000-01-01~9999-12-31生日、截止日
DATETIME8B / 秒級創建/更新時間
TIMESTAMP4B / 1970~2038自動更新時間戳
YEAR1B / 1901~2155年份存儲

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

-- 插入
INSERT INTO tb (col1, col2) VALUES (val1, val2);
INSERT INTO tb VALUES (val1, val2), (val3, val4); -- 批次

-- 更新
UPDATE tb SET col1=val1, col2=val2 WHERE condition;

-- 刪除
DELETE FROM tb WHERE condition;

-- 查詢完整結構
SELECT [DISTINCT] select_list
FROM table_list
[WHERE condition]
[GROUP BY group_list]
[HAVING having_condition]
[ORDER BY order_list]
[LIMIT offset, count];

六、條件查詢運算符

  • 比較:= != < > <= >=
  • 模糊:LIKE '%abc%'、NOT LIKE、_%(單字符)
  • 集合:IN (1,2,3)、NOT IN、BETWEEN 100 AND 500
  • 空值:IS NULL、IS NOT NULL

七、連接查詢 (JOIN)

類型關鍵字結果集特徵
內連接INNER JOIN兩表匹配行的交集
左連接LEFT JOIN左表全量 + 右表匹配行(無匹配為 NULL)
右連接RIGHT JOIN右表全量 + 左表匹配行
SELECT g.goods_name, c.cat_name, b.brand_name
FROM ecs_goods g
LEFT JOIN ecs_category c ON g.cat_id = c.cat_id
LEFT JOIN ecs_brand b ON g.brand_id = b.brand_id
WHERE g.cat_id = 4;

八、子查詢

WHERE 型(標量/列子查詢)

SELECT * FROM goods 
WHERE goods_id = (SELECT MAX(goods_id) FROM goods);

FROM 型(派生表)

SELECT * FROM (
    SELECT goods_id, cat_id, goods_name 
    FROM ecs_goods ORDER BY goods_id DESC
) AS t GROUP BY cat_id; -- 取每分類最新商品

九、字符集與存儲引擎

  • SET NAMES utf8mb4:一次性設定 client/connection/results 三個字符集變量。
  • MyISAM:讀快、全文索引、不支持事務、表鎖。
  • InnoDB:支持事務、行鎖、外鍵、崩潰恢復、聚簇索引(主鍵順序寫入最快)。

十、事務控制

START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT; -- 或 ROLLBACK;

十一、觸發器實戰

-- 下單後扣減庫存
CREATE TRIGGER tg_after_order_insert
AFTER INSERT ON `order`
FOR EACH ROW
UPDATE goods SET goods_number = goods_number - NEW.num WHERE id = NEW.gid;

-- 刪單後恢復庫存
CREATE TRIGGER tg_after_order_delete
AFTER DELETE ON `order`
FOR EACH ROW
UPDATE goods SET goods_number = goods_number + OLD.num WHERE id = OLD.gid;

-- 改單後調整庫存
CREATE TRIGGER tg_after_order_update
AFTER UPDATE ON `order`
FOR EACH ROW
UPDATE goods SET goods_number = goods_number + OLD.num - NEW.num WHERE id = OLD.gid;

十二、索引最佳實踐

  • 優先為 WHERE、JOIN ON、ORDER BY 欄位建索引。
  • 高選擇性(基數大)欄位索引效果最好(如 user_id、mobile)。
  • 聯合索引遵循「最左前綴原則」:INDEX idx_a_b_c (a,b,c) 可支持 a、a,b、a,b,c 查詢。
  • 索引不是越多越好:每次寫入需維護所有索引,評估讀寫比。

十三、綜合練習題(節選)

點擊展開:ECSHOP 商品表實戰查詢
-- 1.1 主鍵 32
SELECT goods_id, goods_name, shop_price FROM ecs_goods WHERE goods_id = 32;

-- 1.5 第 4 或 11 分類
SELECT goods_id, cat_id, goods_name, shop_price FROM ecs_goods WHERE cat_id IN (4,11);

-- 1.9 分類 3,價格<1000 或 >3000,點擊>5
SELECT goods_id, cat_id, goods_name, shop_price, click_count
FROM ecs_goods
WHERE cat_id = 3 AND (shop_price < 1000 OR shop_price > 3000) AND click_count > 5;

-- 2.8 每分類統計
SELECT cat_id,
       MAX(shop_price) AS max_price,
       MIN(shop_price) AS min_price,
       AVG(shop_price) AS avg_price,
       SUM(goods_number) AS total_stock,
       COUNT(*) AS goods_count
FROM ecs_goods GROUP BY cat_id;

-- 3.6 積壓貨款 > 20000 的分類
SELECT cat_id, SUM(goods_number * shop_price) AS stock_amount
FROM ecs_goods GROUP BY cat_id HAVING stock_amount > 20000;

-- 5.1 多表關聯
SELECT g.goods_name, c.cat_name, g.shop_price
FROM ecs_goods g
LEFT JOIN ecs_category c ON g.cat_id = c.cat_id;

十四、面試高頻題:分組統計「不及格門數 ≥ 2 的學生平均分」

-- 錯誤寫法:COUNT(score<60) 計算的是非空行數,非不及格數
SELECT name, COUNT(score<60) AS fail_cnt, AVG(score) 
FROM stu GROUP BY name HAVING fail_cnt >= 2;

-- 正確寫法:SUM(score<60) 利用 TRUE=1/FALSE=0 特性
SELECT name, SUM(score < 60) AS fail_cnt, AVG(score) AS avg_score
FROM stu GROUP BY name HAVING fail_cnt >= 2;

十五、UNION 合併結果集

-- 合併並去重
SELECT * FROM ta UNION SELECT * FROM tb;

-- 合併保留重複(性能更好)
SELECT * FROM ta UNION ALL SELECT * FROM tb;

-- 合併後分組求和
SELECT id, SUM(num) FROM (
    SELECT * FROM ta UNION ALL SELECT * FROM tb
) AS tmp GROUP BY id;

結語

本筆記涵蓋 MySQL 從安裝配置、基礎 DDL/DML、進階查詢、事務、觸發器、索引優化到實戰案例。建議配合官方文檔 MySQL Reference Manual 深入學習,並在測試環境大量練習,構建肌肉記憶。

copyright © 萬盛學電腦網 all rights reserved