【知識詳解】資料庫（秋招總結）

資料庫

目錄

• 資料庫
  • 1.資料庫概述
  • 2.MySQL
    • 2.1 DQL語言
      • 1 基礎查詢：
      • 2.條件查詢：
      • 3.排序查詢：
      • 4.分組查詢：
      • 5.連線查詢
      • 6.子查詢
      • 7.分頁查詢
    • 2.2 DML語言
      • 1.資料插入
      • 2.資料修改
      • 3.資料刪除
    • 2.3 DDL語言
      • 1.庫和表的管理
      • 2.資料型別
      • 3.常見約束
    • 2.4 TCL語言
      • 1.事務的概述
      • 問：事務的特性？
      • 2.事務的隔離級別
      • 問：四種隔離級別？
  • 3.資料庫基礎
    • 問：drop、truncate、delete的區別？
    • 問：關係型資料庫和非關係型資料庫？
    • 問：資料庫三大正規化？
    • 問：Mysql中的鎖機制？
    • 問：資料庫單表記錄數過大時，優化措施？
    • 問：一條SQL語句執行很慢的原因？
    • Redis
      • 問：Redis的資料型別？
      • 問：為什麼要用Redis和快取？
      • 問：Redis的持久化？
  • 4.常見sql題
    • 查詢每門課都大於80分的學生姓名
    • 刪除除了自動編號外不同，其他都相同的學生冗餘資訊

1.資料庫概述

使用資料庫的好處：

1.持久化資料到本地；
2.實現結構化查詢，方便管理；

相關概念

• DB:資料庫，儲存一組有組織的資料的容器；
• DBMS:資料庫管理系統，又叫資料庫軟體，用於管理DB中的資料；
• SQL:結構化查詢語言，用於和DBMS通訊的語言；

資料庫儲存的特點

• 資料放入表中，表再放入庫中；
• 表具有一些特性，定義了資料如何儲存，類似於Java中的類；
• 表由列(欄位)組成，類似於Java中的屬性；
• 表中資料按行儲存，類似於Java中的物件；

SQL語言分類

• DQL(Data Query Language):資料查詢語言； --> 查 select
• DML(Data Manipulate Language):資料操作語言； --> 增刪改； insert,update,delete;
• DDL(Data Define Language):資料定義語言； -->create,drop,alter;
• TCL(Transaction Control Language):事務控制語言； -->commit,rollback;

2.MySQL

2.1 DQL語言

1 基礎查詢：

SELECT firstname 姓 FROM students； #姓為別名；

2.條件查詢：

SELECT firstname, score FROM students WHERE score<=90 AND score>=60;  
SELECT * FROM students WHERE score BRTWEEN 90 AND 60;  #模糊查詢；
SELECT * FROM students WHERE firstname LIKE 'a%';  #like和萬用字元一起使用

3.排序查詢：

#查詢郵箱中含有e的員工資訊，先按郵箱位元組數降序，再按部門號升序；
SELECT *,LENGTH(email)  #如果是篩選時包括函式的注意要寫在select後面；
FROM employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC,department_id ASC;

4.分組查詢：

• 1.和分組函式一同查詢的欄位必須是group by後面出現的欄位；
• 2.分組前查詢：where：對原始表操作； 分組後查詢：having：對分組後的虛擬結果表操作；

# 分組前篩選：常見關鍵字：每個**；
# 查詢有獎金的每個領導手下員工的平均工資；   
SELECT AVG(salary),manager_id   #manager_id必須是groupby後面
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;

# 分組後篩選：
# 每個工種有獎金的員工的最高工資>12000的工種編號和最高工資；
SELECT job_id,MAX(salary)
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id     #先查詢有獎金的員工的最高工資；
HAVING MAX(salary)>12000   #分組完後再篩選；

5.連線查詢

#傳統模式：用where，其實就可以理解成在做一次篩選，完事後兩個表合成了一個新表；是在做兩個表的交集；
#查詢有獎金的員工名、部門名；  
SELECT lastname,department_name,commission_pct
FROM employees e, department d    #一般都起別名；
WHERE e.department_id = d.department_id   #等值連線；
AND e.commission_pct IS NOT NULL;   

#99模式：	
    select 查詢列表
	from 表1 別名 【連線型別】
	join 表2 別名 
	on 連線條件
#1.內連線：查詢有獎金的員工名、部門名；
SELECT lastname,department_name
FROM employees e   
(INNER) JOIN department d
ON e.department_id=d.department;   #on後是連線條件，where後是篩選條件，實現分離；
WHERE e.commission_pct IS NOT NULL; 
#2.外連線：外連線的查詢結果是主表中的所有記錄=內連線結果+主表中有而從表中沒有的，在連線條件上，如果從表上有匹配的，那就是匹配的值，如果沒有匹配的，那就是null；常用在查詢一個表中有，另一個表中沒有的場景；      
#查詢哪個部門沒有員工  
SELECT d.*,e.employee_id
FROM department d    #這個最後其實會連線很多，拿著d裡的每一行去匹配e表裡的所有，所有可能會有1個部門很多員工的，
#所以在外連線裡，並不一定查完之後表的大小和主表一模一樣，只是說主表中的查完之後一定有；
LEFT OUTER JOIN employees e
ON d.department_id=e.department_id
WHERE e.employee_id IS NULL;

• 左外連線：以左表為主表，可以查詢左表存在但右表為null的記錄。
• 右外連線：以右表為記錄，可以查詢右表存在但左表為null的記錄；
• 內連線：查詢左右表同時滿足條件的記錄，兩邊都不能為null；

6.子查詢

一條查詢語句中又巢狀了另一條完整的select語句

#查詢誰的工資比Abel高？
SELECT salary
FROM employees
WHERE salary>(
    SELECT salary       #先查出Able的工資；
    FROM employees
    WHERE lastname = 'able'
);

7.分頁查詢

當要查詢的資料，一頁顯示不全，需要分頁時；

#查詢第11條到第25條資料
SELECT * FROM employees LIMIT 10,15；  #10是起始，15是size；

2.2 DML語言

1.資料插入

INSERT INTO students(id, name, sex)
VALUES(12, '張三', '女')；   #可以不寫欄位，那就預設全部；

2.資料修改

#修改學生表中id為2的名稱為李四
UPDATE students SET name = '李四'
WHERE id=2;

3.資料刪除

#刪除手機號為9的學生資訊
DELETE FROM students WHERE phone LIKE '%9';
#刪除學生表
TRUNCATE TABLE students;

2.3 DDL語言

1.庫和表的管理

1.庫的管理

#建立學生庫
CREATE DATABASE IF NOT EXISTS students;
#更改學生庫為老師庫
ALTER DATABASE students TO teachers;
#刪除學生庫  
DROP DATABASE IF EXISTS students;

2.表的管理

#建立學生表
CREATE TABLE IF NOT EXISTS students(
    id INT,
    stuName VARCHAR(20),
    gender CHAR,
    born DATETIME
);            #欄位之間加逗號，最後一個不用加，括號外面要分號；
DESC students;   #檢視錶；
#修改表名
ALTER TABLE students RENAME studentInfo;
#刪除表
DROP TABLE IF EXISTS students;

#修改學生表  
#1.修改欄位名
ALTER TABLE student CHANGE COLUMN gender sex CHAR;
#2.修改列型別或約束
ALTER TABLE student MODIFY COLUMN born DATA;  
#3.新增欄位
ALTER TABLE student ADD COLUMN email VARCHAR(20);
#4.刪除欄位
ALTER TABLE student DROP COLUMN email;

庫和表通用的建立和刪除

DROP DATABASE/TABLE IF EXISTS       舊錶名/舊庫名
CREATE DATABASE/TABLE IF NOT EXISTS 新表名/新庫名

2.資料型別

整型：INT
小數：DOUBLE
字元型：CHAR(M), VARCHAR(M) M表示最大字串；
日期型：DATATIME;

3.常見約束

NOT NULL: 非空；保證欄位值不為空；
DEFAULT: 該欄位有預設值；
UNIQUE: 唯一，可以為空；比如座位號；
PRIMARY KEY: 主鍵，保證唯一且非空；比如編號；
FOREIGN KEY: 外來鍵，用於限制兩個表的關係；

CREATE TABLE student(
    id, INT PRIMARY KEY,
    student VARCHAR(20) NOT NULL UNIQUE, 
    seat INT UNIQUE,
    age INT DEFAULT 18,
    majorId INT
);

2.4 TCL語言

1.事務的概述

事務：一個或一組sql語句組成一個執行單元，要麼全部執行，要麼全部不執行；

問：事務的特性？

(ACID)
原子性：事務不可分割，要麼都執行，要麼都不執行；
一致性：保證事務的狀態在操作前和操作後保持一致；(主要是對約束而言，比如餘額的約束是大於0，比如90，要轉出去100，顯然不能成功，轉完之後就不滿足約束了，破壞了一致性；)
隔離性：一個事務的執行不受其他事務的干擾；
永續性：一個事務一旦提交，會永久改變資料庫的資料；

步驟：

1. 開啟事務：set autocommit=0;
2. 編寫一組邏輯單元； 增刪改查；
3. 結束事務：commit(提交事務)； rollback(回滾事務)

2.事務的隔離級別

當多個事務同時操作同一個資料庫的相同資料時，就會產生併發問題；

• 髒讀：T1讀取到了已經被T2更新但是還沒有提交的資料，那這之後，如果T2回滾，那T1讀的資料就是無效的；
• 不可重複讀：T1已經讀取了一個資料，然後T2更新並且提交了該資料，之後T1再次讀的時候值就不一樣了；
• 幻讀：T1讀取了一個表，之後T2在表裡插入了新的行，進行了更新，之後T1再次讀的時候，就會多出幾行；
• 不可重複讀多指的是修改某條記錄，而幻讀重點指的增加或刪除記錄；

問：四種隔離級別？

• READ UNCOMMITTED(讀未提交)：允許事務讀取還沒有被其他事務提交的資料，髒讀、不可重複讀、幻讀都會出現；
• REAT COMMITTED(讀已提交)：只允許事務讀取已經被其他事務提交的變更，可以避免髒讀，但不可重複讀和幻讀仍然會存在；
• REPEATABLE READ(可重複讀)：確保事務可以多次從一個欄位中讀取相同的值，在這個事務讀取的過程中，禁止其他事務對這個欄位進行更新，可以避免髒讀和不可重複讀，但是幻讀仍然存在；
• SERIALIZABLE(序列化)：確保一個事務可以從一個表中讀取相同的行，在這個事務持續期間，禁止其他事務對該表的插入更新刪除操作，併發問題都可以避免，但是效率很低；

3.資料庫基礎

問：drop、truncate、delete的區別？

• delete是DML，也就是資料操作語言，主要是用來刪除資料的，執行的時候，從表中刪除滿足條件的行，同時記錄該操作，為了以後進行回滾；
• truncate是DDL，資料定義語言，會直接隱式的提交，不能回滾，主要是用來運算元據表的；
• drop也是DDL資料定義語言，會隱式提交，不能回滾，會刪除表結構，並且把表所佔用的空間釋放；

在速度上，drop>truncate>delete; 在刪除部分資料的時候用delete，一般都要帶上where語句，if想刪除所有資料保留表，那可以用truncate，if想把整個表刪除，用drop；

問：關係型資料庫和非關係型資料庫？

• 關係型資料庫是採用了關係模型來組織資料的資料庫，是一種二維表格；很容易理解，而且儲存在磁碟上，掉電不丟失，缺點是當在高併發的時候，關係型資料庫的IO操作是很大的消耗；比如常見的Oracle、mysql
• 非關係型資料庫是非關係的，分散式的，不嚴格遵守ACID的原則，結構並不固定，常見的以鍵值對來儲存，比如redis；非關係型資料庫儲存在記憶體中，所以效率很高；

問：資料庫三大正規化？

• 第一正規化：表中的欄位具有原子性，不可再分割；
• 第二正規化：表中的每列都和主鍵有關，不能只和主鍵的一部分有關；
• 第三範數：表中每列都和主鍵直接相關，不是相互傳遞過來的相關。比如學號和學院號； 滿足第三正規化一定滿足第二正規化，滿足第二正規化一定滿足第一正規化；

問：Mysql中的鎖機制？

鎖的話就是計算機協調多個程式或者執行緒併發訪問某一資源的機制。在資料庫中，資料是需要很多使用者共享的資源，所以要保證資料在併發訪問的一致性，所以在mysql中需要用到鎖；

• 全域性鎖：也就是對整個資料庫進行加鎖，比如說需要對整個資料庫備份的時候；
• 表級鎖：開銷小，加鎖比較快，不會出現死鎖的問題，鎖定的是整個表，粒度大，發生鎖衝突的概率最高，併發度最低；比如MyISAM就是支援表級鎖，在Mysql裡有兩種：一種是表鎖，一種是後設資料鎖(meta data lock,MDL);

表鎖：lock tables … read/write；

例如lock tables t1 read, t2 write; 命令，則其他執行緒寫 t1、讀寫 t2   
的語句都會被阻塞。同時，執行緒 A 在執行 unlock tables 之前，也只能執行讀 t1、讀寫 t2   
的操作。連寫 t1 都不允許，自然也不能在unlock tables之前訪問其他表。

後設資料鎖：MDL 不需要顯式使用，在訪問一個表的時候會被自動加上，   
在 MySQL 5.5 版本中引入了 MDL，當對一個表做增刪改查操作的時候，   
加 MDL讀鎖；當要對錶做結構變更操作的時候，加 MDL 寫鎖。

• 行級鎖：開銷大，加鎖比較慢，會出現死鎖的問題，鎖定的是單行，粒度小，發生鎖衝突的概率最低，併發度也最高；只有InnoDB支援行級鎖；

行級鎖不是直接鎖的資料，而是鎖的索引，    
if是主鍵索引，則直接鎖定這條主鍵索引，    
if是非主鍵索引，那就先鎖定非主鍵索引，然後再鎖住主鍵索引；

• 頁面鎖：開銷和加鎖時間介於表鎖和行鎖之間，會出現死鎖，鎖定粒度在表鎖和行鎖之間，併發度一般；

問：資料庫單表記錄數過大時，優化措施？

• 限定資料的範圍：查詢的時候要有範圍：比如查最近1個月的訂單；
• 垂直分割槽：也就是拆分資料表，將資料列進行拆分，把一個表拆成多張表；
• 水平分割槽：按行拆分；

問：一條SQL語句執行很慢的原因？

這個問題其實要看具體情況，比如可能有兩種情況，一是大多數情況下是正常的，偶爾很慢，二是這條語句一直都很慢；

針對偶爾很慢的情況：

• 資料庫在重新整理髒頁，也就是將資料同步到磁碟中；

當往資料庫中插入或者是更新資料的時候，會在記憶體中將資料更新，但是並不會馬上同步到磁碟中   
而是把這些更新的記錄寫到redo log中去，等到空閒的時候，再通過redo log去把資料同步到磁碟中；    
if記憶體資料頁和磁碟資料頁不一樣的時候，就把這個頁稱為：髒頁；   
比如redo log一直在寫，最後存滿了，還沒把更新同步到磁碟中，那這時候就只能先暫停了； 
先停下來，得等人家把資料同步完再說，所以語句可能就執行的比較慢；

• 拿不到資料表的鎖，比如要訪問的資料表或者行別人也在用，並且加鎖了，這條語句拿不到鎖，就只能暫時等著別人釋放鎖；

針對一直很慢的情況：

• 沒有用上索引：比如要查詢的欄位在=或者>左邊進行了運算或者函式，是不會用到索引的；如 select * from t where c+1 = 1000; 這時候沒有用到索引，必須是 c=1000-1；

Redis

Redis是一種用C語言編寫的key-value儲存形式的資料庫，是非關係型的；
這裡面的value可以是多種型別，可以使用type進行檢視，比如字串、雜湊、列表、集合、有序集合；Redis的資料是存在記憶體中的，所以是一種記憶體資料庫，其讀寫速度很快，經常被用作快取。

問：Redis的資料型別？

• string
  概念：鍵是字串，值可以是字串（JSON，XML）、數字（整形、浮點數）、二進位制（圖片、音訊、視訊），最大不超過 512 MB。
  命令：set、get、setex、setnx、mset、mget、incr、decr。
  內部編碼：① int（< 8B）。② embstr（不大於 39 位元組）。③ raw（大於 39 位元組）。
  應用場景：
  ① 快取：Redis 作為快取，MySQL 作為儲存層，首先從 Redis 獲取資料，如果失敗就從 MySQL 獲取並將結果寫回 Redis 並新增過期時間。
  ② 計數：Redis可以實現快速計數功能，例如視訊每播放一次就用 incr 把播放數加 1。
  ③ 共享 Session：一個分散式 Web 服務將使用者的 Session 資訊儲存在各自伺服器，但會造成一個問題，出於負載均衡的考慮，分散式服務會將使用者的訪問負載到不同伺服器上，使用者重新整理一次可能會發現需要重新登陸。為解決該問題，可以使用 Redis 將使用者的 Session進行集中管理，每次使用者更新或查詢登入資訊都直接從 Redis 獲取。

• hash
  概念：鍵值本身又是一個鍵值對結構，雜湊型別中的對映關係叫 field-value， value 是指 field 對應的值而不是鍵對應的值。
  命令：hset、hget、hdel、hlen、hexists。
  內部編碼：① ziplist（field <= 512 且 value <= 64B）。② hashtable（field > 512 或 value > 64B）。

• list
  概念：儲存多個有序字串，每個字串稱為元素，一個列表最多可以儲存 2³²-1個元素。可以對列表兩端插入和彈出，還可以獲取指定範圍的元素列表、獲取指定索引的元素等。列表是一種比較靈活的資料結構，可以充當棧和佇列，在實際開發中有很多應用場景。
  list 有兩個特點：
  ①元素有序，可以通過索引獲取某個元素或某個範圍的元素。
  ② 元素可以重複。
  命令：lpush、rpop、lrange、lindex、llen。
  內部編碼：① ziplist（key <= 512 且 value <= 64B）。② linkedlist（key > 512 或 value > 64B）。③ quicklist。
  應用場景：lpush + lpop = 棧、lpush + rpop = 佇列、lpush + ltrim = 優先集合、lpush + brpop = 訊息佇列。

• set

概念：儲存多個字串元素，和 list 不同的是集合不允許有重複元素，並且集合中的元素是無序的，不能通過索引下標獲取元素。一個集合最多可以儲存 2³²-1 個元素。Redis 除了支援集合內的增刪改查，還支援多個集合取交集、並集、差集。
命令：sadd、sremove、scard、sismember、spop。
內部編碼包括：① intset（key <= 512 且 element 是整數）。② hashtable（key > 512 或 element 不是整數）。
應用場景：sadd = 標籤、spop = 生成隨機數，比如抽獎、sinter = 社交需求。

• zet

概念：有序集合保留了集合不能有重複成員的特性，不同的是可以排序。但是它和 list 使用索引下標作為排序依據不同的是，他給每個元素設定一個分數（score）作為排序的依據。有序集合提供了獲取指定分數和元素查詢範圍、計算成員排名等功能。
命令：zadd、zremove、zscore、zrank、zcount。
內部編碼：① ziplist（key <= 128 且 member <= 64B）。② skiplist（key > 128 或 member > 64B）。
應用場景：有序集合的典型使用場景就是排行榜系統，例如使用者上傳了一個視訊並獲得了贊，可以使用 zadd 和zincrby。如果需要將使用者從榜單刪除，可以使用zrem。如果要展示獲取贊數最多的十個使用者，可以使用 zrange。
具體5種型別使用可檢視此部落格

問：為什麼要用Redis和快取？

使用快取的主要目的是為了提升使用者體驗，能夠應對更多使用者；主要從高效能和高併發兩個方向進行考慮：

• 高效能：if使用者每次都是從磁碟中讀資料的話，這個過程比較慢，所以可以將經常訪問的高頻資料且不容易發生改變的存在快取裡，這樣就可以直接從快取中操作，速度更快；需要注意的點就是要保證資料庫和快取中的資料一致性；
• 高併發：直接操作快取能夠承受的資料庫請求數量遠遠大於直接訪問資料庫的；每秒的查詢次數，也就是QPS能夠大幅提高；

問：Redis的持久化？

redis是支援持久化的，提供了兩種持久化的方法，分別是RDB(Redis DataBase)快照持久化和AOF(Append Only File)追加檔案持久化;

• RDB意思就是說在不同的時間點，將redis儲存的資料生成快照並儲存在磁碟等介質中；if需要大規模資料的恢復，並且對資料完整性要求不高，RDB方式更加高效；但是if 對資料完整性非常敏感，就不太合適了，比如5分鐘持久化一次，ifredis故障了，仍然會有5分鐘的資料丟失；
• AOF就是把redis執行過的所有指令記錄下來，在下次redis重新啟動時把這些指令從前到後重復執行一遍，然後實現資料恢復；

4.常見sql題

查詢每門課都大於80分的學生姓名

select name
from student
group by name
having min(score)>80

刪除除了自動編號外不同，其他都相同的學生冗餘資訊

//先拿除了自動編號外的欄位進行分組，
//然後獲得裡面的最小編號，然後再進行子查詢
//也就是將不在這些編號裡的資料刪除
delete student 
where 自動編號 not in
(select min(自動編號) from student group by學號、姓名)