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(最高)

資料模型

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

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

資料型別

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

場景：要經常根據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隔離級別下，所有事務按照次序依次執行，因此，臟讀、不可重復讀、幻讀都不會出現，

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

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

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