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

MySQL 基礎知識點大全：從入門到進階完整匯總

編輯：MySQL 資料庫相關知識

1. SQL 基礎語法特點

字符串用單引號 '...'
無布林型，邏輯真/假用 1/0 或 TRUE/FALSE
不區分大小寫
關係運算符：> >= < <= = <> !=
邏輯運算符：NOT(!) AND(&&) OR(||)

2. 資料庫與表建立 (T-SQL 風格)

-- 建庫（含檔案組、日誌、自動增長）
CREATE DATABASE School ON PRIMARY (
  NAME='School_data', FILENAME='f:\project\School_data.mdf', SIZE=3MB, MAXSIZE=100MB, FILEGROWTH=10%
) FILEGROUP mygroup (
  NAME='School_data1', FILENAME='F:\qiyi\School_data1.ndf', SIZE=3MB, MAXSIZE=100MB, FILEGROWTH=10%
) LOG ON (
  NAME='School_log', FILENAME='f:\project\School_log.ldf', SIZE=3MB, FILEGROWTH=10%
), (
  NAME='School_log1', FILENAME='F:\qiyi\School_log1.ldf', SIZE=3MB, FILEGROWTH=10%
);

-- 建表
CREATE TABLE Teacher (
  Id INT IDENTITY(1,1),
  Name NVARCHAR(50) NOT NULL,
  ClassId INT NOT NULL,
  Gender BIT NOT NULL,
  Age INT,
  Salary MONEY,
  Birthday DATETIME
);

3. 四大完整性約束

類型	約束	關鍵字	說明
實體完整性	主鍵	PRIMARY KEY (PK)	非空 + 唯一，每表僅一個，可組合多欄
	標識列	IDENTITY	系統自動生成遞增值
	唯一鍵	UNIQUE (UQ)	唯一，可為 NULL（僅一次）
域完整性	類型/非空/預設/CHECK	CHECK (CK)	限制欄位取值範圍
引用完整性	外鍵	FOREIGN KEY (FK)	引用主表 PK/UQ，建立主從關係
自定義完整性	觸發器/存儲過程	TRIGGER/SP	複雜業務規則

4. 外鍵級聯行為

NO ACTION（預設）：報錯阻擋
CASCADE：級聯刪除/更新
SET NULL：置 NULL（欄位允許 NULL）
SET DEFAULT：置預設值（欄位有 DEFAULT）

5. 字符串類型對比

類型	空間特性	中文儲存	適用場景
CHAR(n)	固定長度，不足補空格	2 字節/字符	長度固定（如 MD5、UUID、身份證）
VARCHAR(n)	變長，實際佔用	2 字節/字符	長度波動大
NCHAR(n)	固定，Unicode，2 字節/字符	2 字節/字符	多語言固定長度
NVARCHAR(n)	變長，Unicode	2 字節/字符	多語言變長

6. 核心 DML 操作

-- 插入
INSERT INTO Teacher (Name,ClassId,Gender,Age,Salary,Birthday) VALUES ('張三',5,1,30,4000,'1984-9-11');
INSERT INTO Teacher (Name,ClassId,Gender,Age) VALUES ('李四',5,1,30);  -- Salary 用預設值

-- 刪除
DELETE FROM Teacher WHERE Age < 20;           -- 逐行刪除，寫日誌，觸發器生效
TRUNCATE TABLE Teacher;                        -- 一次性清空，極快，重置標識列，不觸發器

-- 更新
UPDATE Teacher SET Gender=0 WHERE Id=20;
UPDATE Teacher SET ClassId=4, Age+=5 WHERE Id=22;

7. 查詢進階

-- 別名、常量列
SELECT StudentNo AS 學號, StudentName 姓名, '中華人民共和國' AS 國籍 FROM Student;

-- TOP 與百分比
SELECT TOP 10 * FROM Student;
SELECT TOP 10 PERCENT * FROM Student;

-- 去重
SELECT DISTINCT Sex FROM Student;

-- 聚合函數（自動忽略 NULL）
SELECT COUNT(*), AVG(Salary), MAX(Age), MIN(Birthday), SUM(Score) FROM ...

-- 條件篩選
WHERE Sex='女' AND BornDate BETWEEN '1990-1-1' AND '1993-1-1'
WHERE ClassId IN (1,3,5,7)

-- 模糊查詢
WHERE StudentName LIKE '林%'      -- % 任意長度
WHERE StudentName LIKE '林_'      -- _ 單字符
WHERE StudentNo LIKE '[13579]'    -- 字符集合

-- NULL 處理
WHERE Email IS NULL / IS NOT NULL
SELECT ISNULL(Email, '未填寫') FROM Student

8. 分組與聚合

-- 分組統計
SELECT ClassId, Sex, COUNT(*) FROM Student WHERE Sex='男' GROUP BY ClassId, Sex;

-- HAVING 篩選分組結果
SELECT ClassId, COUNT(*) AS num FROM Student WHERE Email IS NOT NULL
GROUP BY ClassId HAVING COUNT(*) >= 2 ORDER BY num DESC;

-- 完整查詢執行順序
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ...

9. 常用函數速查

分類	函數	說明
類型轉換	CONVERT(target_type, expr, [style])	日期格式化需 style
類型轉換	CAST(expr AS target_type)	無格式參數
日期	GETDATE()	當前日期時間
	DATEADD(interval, number, date)	增減時間
	DATEDIFF(interval, start, end)	差值
	DATENAME/DATEPART	取名稱/數值部分
數學	RAND()	0-1 隨機數
	ABS/CEILING/FLOOR	絕對值/向上/向下取整
	POWER/ROUND/SIGN	冪/四捨五入/符號
	字符串	CHARINDEX(sub, str, [start])	子串位置
		LEN/UPPER/LOWER/LTRIM/RTRIM	長度/大小寫/去空格
LEFT/RIGHT/SUBSTRING		截取
REPLACE/STUFF		替換/填充替換

10. UNION 結果集合併

UNION：去重，列數/類型需相容
UNION ALL：不去重，效能更高

11. CASE 條件表達式

-- 簡單 CASE（等值判斷）
CASE ClassId WHEN 1 THEN '一班' WHEN 2 THEN '二班' ELSE '其他' END

-- 搜尋 CASE（範圍/複合判斷）
CASE 
  WHEN BornDate > '2000-1-1' THEN '小屁孩'
  WHEN BornDate > '1990-1-1' THEN '小青年'
  WHEN BornDate IS NULL THEN '出生不詳'
  ELSE '中年'
END

12. 流程控制

-- IF...ELSE
IF @avg >= 60 BEGIN PRINT '及格'; SELECT TOP 3 ... ORDER BY Score DESC END
ELSE BEGIN PRINT '不及格'; SELECT TOP 3 ... ORDER BY Score END

-- WHILE 迴圈
WHILE @unpassNum > @totalCount/2
BEGIN
  UPDATE Result SET Score += 2 WHERE ...;
  SET @unpassNum = (SELECT COUNT(*) ...);
END

13. 子查詢三大用法

條件子查詢：WHERE col = (SELECT ...) / IN (SELECT ...) / EXISTS (SELECT ...)
結果集子查詢：FROM (SELECT ...) AS t —— 分頁必備
標量子查詢：SELECT (SELECT Name FROM Student WHERE No=Result.No) FROM Result

14. 表連接

類型	關鍵字	結果特徵
內連接	INNER JOIN	雙方匹配行
左連接	LEFT JOIN	左表全保留，右表無匹配則 NULL
右連接	RIGHT JOIN	右表全保留
全連接	FULL JOIN	左右全保留

15. 事務 ACID

Atomicity：原子性，全做或全不做
Consistency：一致性，約束滿足
Isolation：隔離性，並發隔離
Durability：持久性，提交永久生效

BEGIN TRANSACTION;
UPDATE Bank SET Money=Money-500 WHERE Name='A';
UPDATE Bank SET Money=Money+500 WHERE Name='B';
IF @@ERROR <> 0 ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION;

16. 視圖

CREATE VIEW vw_StudentClass AS
SELECT s.StudentNo, s.StudentName, c.ClassName
FROM Student s INNER JOIN Class c ON s.ClassId=c.ClassId;

SELECT * FROM vw_StudentClass;  -- 像表一樣查詢

17. 觸發器

CREATE TRIGGER tr_Grade_Insert ON Grade AFTER INSERT AS
BEGIN
  SELECT * FROM inserted;   -- 新增後的資料
  SELECT * FROM deleted;    -- 刪除/更新前的資料（INSERT 時為空）
END;

inserted：操作後新資料
deleted：操作前舊資料

18. 存儲過程

CREATE PROCEDURE usp_GetStudentByClass
  @ClassName NVARCHAR(50), @Sex NCHAR(1)='男'
AS
BEGIN
  DECLARE @ClassId INT = (SELECT ClassId FROM Grade WHERE ClassName=@ClassName);
  SELECT * FROM Student WHERE ClassId=@ClassId AND Sex=@Sex;
END;

EXEC usp_GetStudentByClass '八期班', '女';

19. 分頁存儲過程

CREATE PROCEDURE usp_PageData
  @PageIndex INT=1, @PageSize INT=10, @TotalPage INT OUTPUT
AS
BEGIN
  SELECT * FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY StudentNo) AS RN, * FROM Student
  ) t WHERE RN BETWEEN (@PageIndex-1)*@PageSize+1 AND @PageIndex*@PageSize;
  SET @TotalPage = CEILING((SELECT COUNT(*) FROM Student)*1.0/@PageSize);
END;

20. 索引

CREATE CLUSTERED INDEX IX_Student_Name ON Student(StudentName);
CREATE NONCLUSTERED INDEX IX_Student_Class ON Student(ClassId);

聚集索引：資料物理順序 = 索引順序，每表僅一個（PK 預設）
非聚集索引：邏輯順序，含書籤查找

21. 臨時表與表變數

-- 局部臨時表（#）：僅當前會話可見
CREATE TABLE #Temp (Id INT, Name NVARCHAR(50));

-- 全局臨時表（##）：所有會話可見
CREATE TABLE ##GlobalTemp (Id INT, Name NVARCHAR(50));

-- 表變數（@）：記憶體優先，批次/存儲過程內部
DECLARE @TableVar TABLE (Id INT, Name NVARCHAR(50));

本文匯總 MySQL/SQL Server 通用核心知識點，涵蓋 DDL/DML/DQL、約束、函數、進階查詢、事務、程式化物件、索引、臨時物件，適合系統性複習與面試準備。