SQL(Structured Query Language)

由於最近心血來潮，就把SQL重新過了一遍，順便總結了一份小寶典，應該算是挺全面的哈~~

導語：SQL之路–SQL大全

作者：變優秀的小白

愛好：美式一定加冰！

話不多說，展翅(shi) ！

• 目錄
  • 資料模型
  • 資料型別
  • SQL基本能力
  • 執行SQL
  • 關係模型
    • 主鍵
      • 常見的可作為id欄位的型別
      • 聯合主鍵
    • 外來鍵
    • 幾種關係
    • 索引
      • 概念
      • 目的
      • 使用效率
      • 優缺點
      • 例子ex
      • 唯一索引
    • 查詢資料
      • 基本查詢
      • 條件查詢
      • 投影查詢
      • 排序
      • 分頁查詢
      • 聚合查詢
      • 多表查詢
      • 連線查詢
        • INNER JOIN 內連線
        • OUTER JOIN 外連線
          • LEFT OUTER JOIN 左外連線
          • RIGHT OUTER JOIN 右外連線
          • FULL OUTER JOIN
    • 修改資料
      • INSERT 插入資料
      • UPDATE 更新資料
      • DELETE
  • mysql
    • 管理mysql
      • 資料庫
      • 表
      • 退出mysql
  • 實用SQL
    • 插入或替換
    • 插入或更新
    • 插入或忽略
    • 快速複製
    • 強制索引
  • 事務
    • 隱式事務
    • 顯式事務
  • 隔離級別
    • Read Uncommitted(最低)
    • Read Committed(較低)
    • Repeatable Read(較高)
    • Serializable(最高)

資料模型

資料庫按照資料結構來組織、儲存和管理資料

• 層次模型
• 網狀模型
• 關係模型

資料型別

名稱	型別	說明
INT	整型	4位元組
BIGINT	長整型	8位元組
REAL	浮點型	4位元組
DOUBLE	浮點型	8位元組
DECIMAL	高精度小數	DECIMAL(20,10)表示一共20位，其中小數10位，通常用於財務計算
CHAR	定長字串	儲存指定長度的字串，例如，CHAR(100)總是儲存100個字元的字串
VARCHAR	變長字串	儲存可變長度的字串
BOOLEAN	布林型別	True或者False
DATE	日期型別	日期
TIME	時間型別	時間
DATETIME	日期和時間型別	儲存日期+時間

SQL基本能力

1. DDL：Data Definition Language
   DDL允許使用者定義資料，也就是建立表、刪除表、修改表結構這些操作。通常，DDL由資料庫管理員執行
2. DML：Data Manipulation Language
   DML為使用者提供新增、刪除、更新資料的能力，這些是應用程式對資料庫的日常操作
3. DQL：Data Query Language
   DQL允許使用者查詢資料，這也是通常最頻繁的資料庫日常操作

執行SQL

若是windows版本，百度上有很多安裝教學，

# 若是使用brew安裝的(系統為macOS)
# 需要通過brew啟動mysql
$ brew services start mysql
$ mysql -u root -p

關係模型

• 關聯式資料庫是建立在關係模型上的。而關係模型本質上就是若干個儲存資料的二維表，可以把它們看作很多Excel表
• 表的每一行稱為記錄（Record），記錄是一個邏輯意義上的資料
• 表的每一列稱為欄位（Column），同一個表的每一行記錄都擁有相同的若干欄位

主鍵

• 最關鍵的一點：記錄一旦插入到表中，主鍵最好不要再修改，因為主鍵是用來唯一定位記錄的，修改了主鍵，會造成一系列的影響

常見的可作為id欄位的型別

1. 自增整數型別
   • BIGINT NOT NULL AUTO_INCREMENT(滿足大部分場景)
   • INT：一張表的記錄數超過2147483647（約21億）時，會達到上限而出錯
   • BIGINT：922億
2. 全域性唯一GUID型別

聯合主鍵

沒有必要的情況下，我們儘量不使用聯合主鍵，因為它給關係錶帶來了複雜度的上升

• 定義：通過多個欄位唯一標識記錄，即兩個或更多的欄位都設定為主鍵

外來鍵

• 外來鍵是通過定義外來鍵約束實現的
• 外來鍵約束會降低資料庫的效能，一般不設定

增加一個外來鍵約束

ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes (id);

• CONSTRAINT: 外來鍵約束的名稱可自定義
• FOREIGN KEY: class_id作為外來鍵
• REFERENCES 關聯classes表的id列

刪除一個外來鍵約束

ALTER TABLE students
DROP FOREIGN KEY fk_class_id;

幾種關係

均為表之間的對應關係

• 一對一(q:問題，a:回答，ex: 例子)
  • q: 既然是一對一，為啥不把另一個表(只有1、2列)的資料放入表的一個列中
  • a: 如果業務執行是可以的，但是一個大表拆成兩個一對一的表，目的是把經常讀取和不經常讀取的欄位分開，以獲得更高的效能
  • ex: 把一個大的使用者表分拆為使用者基本資訊表user_info和使用者詳細資訊表user_profiles，大部分時候，只需要查詢user_info表，並不需要查詢user_profiles表，這樣就提高了查詢速度
• 一對多
• 多對多
  • ex: 兩個一對多

索引

概念

關聯式資料庫中對某一列或多個列的值進行預排序的資料結構

目的

可以讓資料庫系統不必掃描整個表，而是直接定位到符合條件的記錄，這樣就大大加快了查詢速度

使用效率

• 取決於索引列的值是否雜湊，即該列的值如果越互不相同，那麼索引效率越高
• 若存在大量資料具有相同值，索引就沒有意義
• 對於主鍵，關聯式資料庫會自動對其建立主鍵索引。使用主鍵索引的效率是最高的，因為主鍵會保證絕對唯一

優缺點

• 優點：提高查詢效率
• 缺點：插入、更新和刪除記錄時，需要同時修改索引，因此，索引越多，插入、更新和刪除記錄的速度就越慢

例子ex

id	class_id	name	gender	score
1	1	小明	M	90
2	1	小紅	F	95
3	1	小軍	M	88

場景：要經常根據score列進行查詢
解決：對score列建立索引

ALTER TABLE students
ADD INDEX idx_score (score);

則建立了一個名稱未idx_score的索引

場景：索引名稱是任意的，索引如果有多列
解決：括號內增加列名稱

ALTER TABLE students
ADD INDEX idx_name_score (name, score);

唯一索引

1. 運用場景：

   • 看上去唯一的列，例如身份證號、郵箱地址等，因為他們具有業務含義，因此不宜作為主鍵
   • 這些列根據業務要求，又具有唯一性約束（即不能出現兩條記錄儲存了同一個身份證號）

2. 解決：該列新增一個唯一索引

3. ex例子：

新增一個唯一索引

ALTER TABLE students
ADD UNIQUE INDEX uni_name (name);

新增一個唯一約束而不建立唯一索引(具有唯一性保證)

ALTER TABLE students
ADD CONSTRAINT uni_name UNIQUE (name);

查詢資料

基本查詢

ex. *

SELECT *
FROM students;

條件查詢

ex. where & and & or & ()

SELECT *
FROM students
WHERE (score < 80 OR score > 90)
AND gender = 'M';

投影查詢

ex. 列1、列2…

SELECT id, score, name
FROM students;

排序

ex. ORDER BY & DESC & ASC

SELECT id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC;

分頁查詢

ex. LIMIT M OFFSET N

• LIMIT M OFFSET N 可簡寫為 LIMIT N, M
• LIMIT總是設定為pageSize
• OFFSET = pageSize * (pageIndex - 1)

-- 查詢第一頁
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0;
-- 查詢第二頁，需要 "跳過" 前三條記錄
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 3;
-- 查詢第四頁，類似
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 9;
-- 若查詢頁沒有資料，不報錯，得到空結果集
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 20;

聚合查詢

注意點：如果聚合查詢的WHERE條件沒有匹配到任何行，COUNT()會返回0，而SUM()、AVG()、MAX()和MIN()會返回NULL

• ex. COUNT()統計記錄數量

-- 使用聚合查詢
SELECT COUNT(*) FROM students;
-- 使用聚合查詢並設定結果集的列名為num
SELECT COUNT(*) num FROM students;
-- 使用聚合查詢並設定WHERE條件
SELECT COUNT(*) boys FROM students WHERE gender = 'M';
-- 分組聚合
SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;
-- 聚合查詢的列中，只能放入分組的列
SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;

• ex. AVG()平均值，該列必須為數值型別
• SUM()合計值，類似
• MAX()最大值，類似
• MIN()最小值，類似

-- 使用聚合查詢計算男生平均成績
-- average為自定義名稱
SELECT AVG(score) average FROM students WHERE gender = 'M';
-- 每頁3條記錄，如何通過聚合查詢獲得總頁數
SELECT CEILING(COUNT(*)/3) FROM student;

多表查詢

• 語法：SELECT * FROM <表1> <表2>
• 笛卡爾乘積，資料量巨大M*N

-- 兩個表的乘積
SELECT * FROM students, classes;
-- 可設定列的別名區分
SELECT
    students.id sid,
    students.name,
    students.gender,
    students.score,
    classes.id cid,
    classes.name cname
FROM students, classes;
-- sql可給表設定別名，相對於上面會簡潔些
SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c;
-- 加上where條件判斷
SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c
WHERE s.gender = 'M' AND c.id = 1;

連線查詢

INNER JOIN 內連線

• INNER JOIN
  1. 確定主表，FROM 表1
  2. 確定需要連線的表，INNER JOIN 表2
  3. 確定連線條件，ON 條件
  4. 可選：WHERE Or ORDER BY等

-- 內連線，運用別名，id對應取出
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
INNER JOIN classes c
ON s.class_id = c.id;

OUTER JOIN 外連線

LEFT OUTER JOIN 左外連線

• 返回左表都存在的行，若僅左表存在會返回NULL

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
LEFT OUTER JOIN classes c
ON s.class_id = c.id;

RIGHT OUTER JOIN 右外連線

• 返回右表都存在的行，若僅右表存在會返回NULL

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
RIGHT OUTER JOIN classes c
ON s.class_id = c.id;

FULL OUTER JOIN

• 所有記錄全部選擇出來，把對方不存在的列填充為NULL：

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
FULL OUTER JOIN classes c
ON s.class_id = c.id;

修改資料

INSERT 插入資料

• 語法：INSERT INTO <表名> (欄位1, 欄位2, ...) VALUES (值1, 值2, ...);
• 插入資料的欄位順序不需要一致，但欄位和值要對應
• 自增主鍵和預設值可不填

INSERT INTO students (class_id, name, gender, score) VALUES
  (1, '張三', 'M', 87),
  (2, '李四', 'M', 81);
-- 查詢更新結果
SELECT * FROM students;

UPDATE 更新資料

• 語法：UPDATE <表名> SET 欄位1=值1, 欄位2=值2, ... WHERE ...;
• 需十分注意使用！

-- 更新多條記錄
UPDATE students SET name='小牛', score=77 WHERE id>=5 AND id<=7;
-- 使用表示式，將所有80下同學成績加10分
UPDATE students SET score=score+10 WHERE score<80;
-- 查詢並觀察結果:
SELECT * FROM students;

DELETE

• 語法：DELETE FROM <表名> WHERE …;
• 與UPDATE語法類似，但需十分注意使用！

-- 刪除id=1的記錄
DELETE FROM students WHERE id=1;
-- 刪除id=5,6,7的記錄
DELETE FROM students WHERE id>=5 AND id<=7;
-- 查詢並觀察結果:
SELECT * FROM students;

mysql

管理mysql

• 幾個系統庫
  • imformation_schema
  • mysql
  • performance_shema
  • sys

資料庫

-- 建立一個新的DB
mysql> CREATE DATABASE test
Query OK, 1 row affected (0.01 sec)
-- 刪除一個DB
mysql> DROP DATABASE test
Query OK, 0 rows affected (0.01 sec)
-- 切換DB
mysql> USE test;
Database changed

表

-- 檢視DB的所有表
mysql> SHOW TABLES;
-- 檢視一個表的結構
mysql> DESC students;
-- 檢視建立表的SQL語句
mysql> SHOW CREATE TABLE students;
-- 刪除表
mysql> DROP TABLE students;
Query OK, 0 rows affected (0.01 sec)
-- 修改表，如新增一列
ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL;
-- 修改表，列名改為birthday，型別改為VARCHAR(20)
ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;
-- 修改表，刪除列
ALTER TABLE students DROP COLUMN birthday;

退出mysql

mysql> exit
Bye

實用SQL

插入或替換

-- 若記錄已經存在，刪除原記錄，再插入新記錄
REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

插入或更新

-- 語法：INSERT INTO ... ON DUPLICATE KEY UPDATE ...
-- 如果記錄已經存在，就更新該記錄
INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;

插入或忽略

-- 如果記錄已經存在，就啥事也不幹直接忽略
INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

快速複製

-- 對class_id=1的記錄進行快照，並儲存為新表students_of_class1:
CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1;

強制索引

-- FORCE INDEX
SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;

事務

某些業務要求必須完成一系列的操作，而不是隻執行一部分

可見，資料庫事務有ACID 4個特性

• A：Atomic，原子性，將所有SQL作為原子工作單元執行，要麼全部執行，要麼全部不執行
• C：Consistent，一致性，事務完成後，所有資料的狀態都是一致的，即A賬戶只要減去了100，B賬戶則必定加上了100
• I：Isolation，隔離性，如果有多個事務並行執行，每個事務作出的修改必須與其他事務隔離
• D：Duration，永續性，即事務完成後，對資料庫資料的修改被持久化儲存

隱式事務

解釋：單條SQL語句，資料庫系統自動將其作為一個事務執行

顯式事務

解釋：多條SQL語句作為一個事務執行，使用BEGIN開啟一個事務，使用COMMIT提交一個事務

-- 顯式事務例子
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- 若希望它主動失敗，ROLLBACK回滾事務就很棒
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK;

隔離級別

目的：對於兩個並行執行的事務，如果涉及到操作同一條記錄的時候，可能會發生問題

Read Uncommitted(最低)

解釋：是隔離級別最低的一種事務級別。在這種隔離級別下，一個事務會讀到另一個事務更新後但未提交的資料，如果另一個事務回滾，那麼當前事務讀到的資料就是髒資料，這就是髒讀（Dirty Read）

Read Committed(較低)

解釋：不可重複讀是指，在一個事務內，多次讀同一資料，在這個事務還沒有結束時，如果另一個事務恰好修改了這個資料，那麼，在第一個事務中，兩次讀取的資料就可能不一致

Repeatable Read(較高)

• mysql中InnoDB模式級別

解釋：一個事務可能會遇到幻讀（Phantom Read）的問題。

幻讀是指，在一個事務中，第一次查詢某條記錄，發現沒有，但是，當試圖更新這條不存在的記錄時，竟然能成功，並且，再次讀取同一條記錄，它就神奇地出現了。

Serializable(最高)

解釋：在Serializable隔離級別下，所有事務按照次序依次執行，因此，髒讀、不可重複讀、幻讀都不會出現。

• 缺點：由於事務是序列執行，所以效率會大大下降，應用程式的效能會急劇降低

結束語：大家如果遇到什麼疑問或者建議的地方，可直接留言評論！本人會一一回復！

如果小白的部落格有建議或批評的，下方留言即可！如果覺得小白還不錯的，留下你的點贊👍，關注❤️和收藏🤘哦！謝謝謝！