MySQL - SQL Basic 基礎

Basic

SQL命令：

• 不分大小寫（Case Insensitive）
• 關鍵字不可以分割成多行
• MySQL單雙引號皆可，但其它database只接受單引號
• 常數（Literal）：不變的數據，不儲存於數據庫內部

Ref:

• database design - Reason why oracle is case sensitive? - Stack Overflow
• Is SQL is case Sensitive or Insensitive? - Querychat

Data Queries

Queries

Order	SQL	Desc
5	SELECT	指定查詢項目 給定特定欄位而非全部查詢
1	FROM	數據來源
2	WHERE	原始過濾 不可以有群組函數
3	GROUP	數據分組 預設分組小到大
4	HAVING	分組過濾
6	ORDER BY	數值排序 預設小到大（ASC）
7	LIMIT	MySQL特有語法

查詢語法：

SELECT
  *
  [DISTINCT] column_name
  column_name, column_name, column_name...
  expression [alias]
FROM
  table_name AS [alias];

排除重複數據：

DESCRIBE table_name;

群組函數（Group / Aggregate Functions）

• COUNT(column)
• MAX(column)
• MIN(column)
• SUM(column)
• AVG(column)

Join

連結運算方式（Join Types）：

• 交叉連結 Cross Join
• 內部連結 Inner Join
  • 自然連結 Natural Join
  • 相等連結 Equal Join
  • 不相等連結 Non-Equal Join
• 外部連結 Outer Join
  • 左外部連結 Left Outer Join
  • 右外部連結 Right Outer Join
  • 完全外部連結 Full Outer Join
• 自我連結 Self Join

Cross Join

SELECT *
FROM emp e CROSS JOIN dept d;

Result: total e * d records

Inner Join

SELECT ...
FROM dept d JOIN emp e ON d.deptno = e.deptno;

SELECT ...
FROM t1 JOIN t2 ON t1.columnA = t2.columnA
        JOIN t3 ON t2.columnB = t3.columnB
        JOIN t4 ON t3.columnB = t4.columnB;

Natural Join

SELECT ...
FROM table_name NATURAL JOIN table_name;

• 使用表格內之同名欄位作為連結條件
• 若欄位名稱形態不同時，則產生錯誤訊息（盡量少用NATURAL JOIN）

Non-Equal Join

SELECT a.empno, a.ename, a.sal, b.grade
FROM emp a JOIN salgrade b ON
     (a.sal BETWEEN b.losal AND b.hisal);

• 若符合條件的數據超過一筆以上時，數據可能會不正確
• 不好寫，幾乎很少用（95%用不到）

Outer JOIN

SELECT a.deptno, a.dname, b.empno, b.ename
FROM dept a LEFT OUTER JOIN emp b ON a.deptno = b.deptno;

• 指定一數據表，當連結條件符合時則顯示，若與第二個數據表都不符合時則以null value顯示
• OUTER可以省略不寫

Self Join

SELECT ...
FROM table A1 JOIN table A2 ON A1.column1 = A2.column2;

• 使用同一個表格做連結運算
• 一定使用表格別名來建立二個相同數據表的連接運算

Inner Join vs Outer Join

Inner Join

Left Outer Join

Right Outer Join

Full Outer Join

Ref: Inner Join vs Outer Join - Difference and Comparison - Diffen

Sub-Queries

Transaction

在執行SQL語句的時候，某些業務要求一系列操作必須完整全部執行，而不能僅執行一部分。例如一個轉賬操作：

-- 
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- 
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

這兩條SQL語句必須全部執行，或是只要任一條語句失敗，就必須全部撤銷。

數據庫事務具有ACID四個特性：

• 原子性（Atomic）：將所有SQL作為原子工作單位執行，要麼全部執行，要麼全部不執行。
• 一致性（Consistent）：事務完成後，所有數據的狀態都是一致的，即A賬戶只要減去了100，B賬戶必定加上了100.
• 隔離性（Isolation）：如果有多個事務並發執行，每個事務作出的修改必須與其它的事務隔離。
• 持久性（Duration）：即事務完成後，對數據的修改被持久化存儲。

隱式事務 & 顯示事務

【隱式事務（Auto Commit）】：對於單條SQL語句，數據庫系統自動將其作為一個事務執行。

【顯式事務（User Commit）】：手動把多條SQL語句作為一個事務執行，使用BEGIN開啟一個事務，使用COMMIT提交一個事務。

BEGIN:
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

COMMIT & ROLLBACK

【COMMIT】：指提交SQL所有事務並持久性保存，如果執行失敗，整個事務也會失敗。

【ROLLBACK】：如果執行失敗，則取消或回復事務期間內所有的數據修改。

BEGIN:
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK;

隔離級別

對於兩個並發執行的事務，如果涉及到操作同一條記錄的時候，可能會發生問題。因為並發操作會帶來數據的不一致性，包括髒讀、不可重複讀、幻讀等。

數據庫系統提供了隔離級別是的可以有針對性地選擇事務的隔離級別，避免數據不一致的問題。

Isolation Level	髒讀 Dirty Read	不可重複讀 Non Repeatable Read	幻讀 Phantom Read
Read Uncommitted	Yes	Yes	Yes
Read Committed	-	Yes	Yes
Repeatable Read	-	-	Yes
Serializable	-	-	-

Ref: 事务 - 廖雪峰的官方网站

DML

Data Manipulation Language:

• INSERT
• UPDATE
• DELETE

常見的錯誤：

• 違反數據檢查條件（Constraint）
  • 鍵值重複：違反主鍵（primary key）或唯一鍵（unique）
  • 違反外鍵（foreign key）
  • 對非空值（not null）的欄位給定空值
• 數據長度過長
• 數據個數不夠
• 數據形態錯誤

	父	子
INSERT	X	V
UPDATE	V	V
DELETE	V	X

DDL

Data Definition Language:

• CREATE
• ALTER
• DROP

數據檢查條件（Database Constrain）

• 欄位預設值（Default）
• 不允許空值（NOT NULL）
• 主鍵（Primary Key）：不可重複、不可NULL、只能唯一
• 外鍵（Foreign Key）：可以重複、可以NULL
• 唯一鍵（Unique Key）：不可重複、可以NULL

Ref: sql - Can a foreign key be NULL and/or duplicate? - Stack Overflow

可在建立數據表時指定，或於數據表建立後加入

• 欄位階層（Column Level）
• 數據表階層（Table Level）

主鍵（Primary Key）

Column Level:

CREATE TABLE table_name
(
DEPTNO SMALLINT(4) PRIMARY KEY,
DNAME VARCHAR(14),
LOCAL VARCHAR(13)
);

Table Level:

CREATE TABLE table_name
(
DEPTNO SMALLINT(4) PRIMARY KEY,
DNAME VARCHAR(14),
LOCAL VARCHAR(13),
CONSTRAINT PK_DEPT122_DEPTNO PRIMARY KEY(DEPTNO)
);

• 命名原則：ConstraintType_TableName_ColumnName
• 不命名，系統預設給定。

外鍵（Foreign Key）

CREATE TABLE table_name
(
...,
fk_definition
[ON DELETE {RESTRICT | CASCADE | SET NULL}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL}]
);

• ON DELETE：數據刪除時
• ON UPDATE：數據更新時
• RESTRICT：限制（預設）
• CASCADE：相依性（依賴）
• SET NULL：設為空值

DELETE & TRUNCATE & DROP

Statement	DML vs DDL	COMMIT & ROLLBACK	Desc
WHERE	DML	can rollback	刪除整個行以及結構 可以使用WHERE條件
TRUNCATE	DDL	cannot rollback	只會刪除內容，而不是結構 無法使用WHERE條件
DROP	DDL	cannot rollback	從數據庫中刪除表 所有表的行，索引和權限也將被刪除

Ref: Difference between TRUNCATE, DELETE and DROP commands - Oracle FAQ

DCL

Data Control language（DCL）

Level:

• Lv5: God
• Lv4: Server
• Lv3: Database
• Lv2: Table
• Lv1: Column

Reference

學習路線：

• MySQL有什么推荐的学习书籍？ - 知乎
• MySQL 学习路线是怎样的？有哪些学习资料或网站推荐？ - 知乎

教程：

• MySQL优化系列（一）–库与表基本操作以及数据增删改_Jack__Frost的博客-CSDN博客

數據表設計：

• MySQL 数据库设计总结 - 云+社区 - 腾讯云

---