複習寶典之Mysql資料庫

查看更多寶典，請點擊《金三銀四，你的專屬面試寶典》

第一章：mysql資料庫

1）mysql與mariaDb

MariaDB資料庫管理系統是MySQL的一個分支，主要由開源社區在維護，採用GPL授權許可 MariaDB的目的是完全相容MySQL，包括API和命令行，使之能輕鬆成為MySQL的代替品。在存儲引擎方面，使用XtraDB(英語:XtraDB)來代替MySQL的InnoDB。 MariaDB由MySQL的創始人Michael Widenius(英語:Michael Widenius)主導開發，他早前曾以10億美元的價格，將自己創建的公司MySQL AB賣給了SUN，此後，隨著SUN被甲骨文收購，MySQL的所有權也落入Oracle的手中。MariaDB名稱來自Michael Widenius的女兒Maria的名字。

MariaDB基於事務的Maria存儲引擎，替換了MySQL的MyISAM存儲引擎，它使用了Percona的 XtraDB，InnoDB的變體，分支的開發者希望提供訪問即將到來的MySQL 5.4 InnoDB性能。這個版本還包括了 PrimeBase XT (PBXT) 和 FederatedX存儲引擎。

2）如何獲取mysql版本

2.1 沒有連接到MySQL伺服器，就想查看MySQL的版本。打開cmd，切換至mysql的bin目錄，運行下麵的命令即可：

mysql -V 或 mysqladmin --version 或 mysql --help|find "Distrib"

2.2 如果已經連接到了MySQL伺服器，則運行下麵的命令：

select version(); 或 status 或 \s

2.3 在命令行連接上MySQL伺服器時，其實就已經顯示了MySQL的版本，如：

mysql -uroot -padmins

3）mysql基礎知識

3.1 mysql密碼

mysql初始密碼為空，預設埠3306，預設最大連接數為100；

修改密碼方式：

在DOS下進入目錄mysql\bin，然後鍵入以下命令：

mysqladmin -u用戶名 -p舊密碼 password 新密碼

如： mysqladmin -u root -p ab12 password djg345

3.2 命令行修改root密碼：

mysql> UPDATE mysql.user SET password=PASSWORD(’新密碼’) WHERE User=’root’;

mysql> FLUSH PRIVILEGES;

顯示當前的user：

mysql> SELECT USER();

3.4 命令行下資料庫的操作

使用 show databases; 展示所有資料庫；

使用 use+資料庫名稱 進入或改變當前使用的資料庫；

使用 show+資料庫名稱 展示該資料庫下的所有表；

3.5 查看表結構的方法：

登錄mysql，執行：

desc+表名 或 describe+表名 或 show columns from 表名 或 explain+表名;

使用mysql的工具mysqlshow.exe：

mysql+資料庫名稱+表名

3.6 如何導出與導入建表語句與數據

導出：mysqldump -uroot -padmins 資料庫名 表名 > database_dump.sql

導入：mysql -uroot -padmins 資料庫名 < database_dump.sql

3.7 如何備份所有資料庫

導出資料庫：mysqldump -uroot -padmins 資料庫名 > database.sql

導入資料庫：mysql -uroot -padmins 資料庫名 < database.sql

另外可以使用圖形化界面進行導出導入

3.8 Mysql中有一個with rollup是用來在分組統計數據的基礎上再進行統計彙總，即用來得到group by的彙總信息；

3.9 MySQL 臨時表在我們需要保存一些臨時數據時是非常有用的。臨時表只在當前連接可見，當關閉連接時，Mysql會自動刪除表並釋放所有空間。

通過mysql> CREATE TEMPORARY TABLE 表名 創建臨時表，

4）mysql資料庫引擎

資料庫引擎是用於存儲、處理和保護數據的核心服務。利用資料庫引擎可控制訪問許可權並快速處理事務，從而滿足企業內大多數需要處理大量數據的應用程式的要求。 使用資料庫引擎創建用於聯機事務處理或聯機分析處理數據的關係資料庫。這包括創建用於存儲數據的表和用於查看、管理和保護數據安全的資料庫對象（如索引、視圖和存儲過程）。

MYSQL支持三個引擎：ISAM、MYISAM和HEAP

另外兩種類型INNODB和BERKLEY（BDB），也常常可以使用。如果技術高超，還可以使用MySQL+API自己做一個引擎。

ISAM：ISAM是一個定義明確且歷經時間考驗的數據表格管理方法，它在設計之時就考慮到 資料庫被查詢的次數要遠大於更新的次數。因此，ISAM執行讀取操作的速度很快，而且不占用大量的記憶體和存儲資源。ISAM的兩個主要不足之處在於，它不 支持事務處理，也不能夠容錯：如果你的硬碟崩潰了，那麼數據文件就無法恢復了。如果你正在把ISAM用在關鍵任務應用程式里，那就必須經常備份你所有的實 時數據，通過其複製特性，MYSQL能夠支持這樣的備份應用程式。
MyISAM：MyISAM是MySQL的ISAM擴展格式和預設的資料庫引擎。除了提供ISAM里所沒有的索引和欄位管理的大量功能，MyISAM還使用一種表格鎖定的機制，來優化多個併發的讀寫操作，其代價是你需要經常運行OPTIMIZE TABLE命令，來恢復被更新機制所浪費的空間。MyISAM還有一些有用的擴展，例如用來修複資料庫文件的MyISAMCHK工具和用來恢復浪費空間的 MyISAMPACK工具。MYISAM強調了快速讀取操作，這可能就是為什麼MySQL受到了WEB開發如此青睞的主要原因：在WEB開發中你所進行的大量數據操作都是讀取操作。所以，大多數虛擬主機提供商和INTERNET平臺提供商只允許使用MYISAM格式。MyISAM格式的一個重要缺陷就是不能在表損壞後恢複數據。
HEAP：HEAP允許只駐留在記憶體里的臨時表格。駐留在記憶體里讓HEAP要比ISAM和MYISAM都快，但是它所管理的數據是不穩定的，而且如果在關機之前沒有進行保存，那麼所有的數據都會丟失。在數據行被刪除的時候，HEAP也不會浪費大量的空間。HEAP表格在你需要使用SELECT表達式來選擇和操控數據的時候非常有用。要記住，在用完表格之後就刪除表格。
InnoDB：InnoDB資料庫引擎都是造就MySQL靈活性的技術的直接產品，這項技術就是MYSQL+API。在使用MYSQL的時候，你所面對的每一個挑戰幾乎都源於ISAM和MyISAM資料庫引擎不支持事務處理（transaction process）也不支持外來鍵。儘管要比ISAM和 MyISAM引擎慢很多，但是InnoDB包括了對事務處理和外來鍵的支持，這兩點都是前兩個引擎所沒有的。如前所述，如果你的設計需要這些特性中的一者 或者兩者，那你就要被迫使用後兩個引擎中的一個了。
如果感覺自己的確技術高超，你還能夠使用MySQL+API來創建自己的資料庫引擎。這個API為你提供了操作欄位、記錄、表格、資料庫、連接、安全帳號的功能，以及建立諸如MySQL這樣DBMS所需要的所有其他無數功能。深入講解API已經超出了本文的範圍，但是你需要瞭解MySQL+API的存在及其可交換引擎背後的技術，這一點是很重要的。估計這個插件式資料庫引擎的模型甚至能夠被用來為MySQL創建本地的XML提供器（XML provider）。（任何讀到本文的MySQL+API開發人員可以把這一點當作是個要求。）

InnoDB和MyISAM是許多人在使用MySQL時最常用的兩個表類型，這兩個表類型各有優劣，視具體應用而定。基本的差別為：MyISAM類型不支持事務處理等高級處理，而InnoDB類型支持。MyISAM類型的表強調的是性能，其執行數度比InnoDB類型更快，但是不提供事務支持，而InnoDB提供事務支持已經外部鍵等高級資料庫功能。

一般來說，MyISAM適合：

(1)做很多count 的計算； (2)插入不頻繁，查詢非常頻繁； (3)沒有事務。

InnoDB適合：

(1)可靠性要求比較高，或者要求事務； (2)表更新和查詢都相當的頻繁，並且表鎖定的機會比較大的情況指定數據引擎的創建

如何查看MySQL的當前存儲引擎？

一般情況下，mysql會預設提供多種存儲引擎,你可以通過下麵的查看:

看你的mysql現在已提供什麼存儲引擎:

mysql> show engines;

看你的mysql當前預設的存儲引擎:

mysql> show variables like '%storage_engine%';

你要看某個表用了什麼引擎(在顯示結果里參數engine後面的就表示該表當前用的存儲引擎):

mysql> show create table 表名;

5）mysql基本工作流程

Mysql是由SQL介面，解析器，優化器，緩存，存儲引擎組成的。

資料庫通常不會被直接使用，而是由其他編程語言通過SQL語句調用mysql，由mysql處理並返回執行結果。那麼Mysql接受到SQL語句後，又是如何處理的呢？

首先程式的請求會通過mysql的connectors與其進行交互，請求到處後，會暫時存放在連接池（connection pool)中並由處理器（Management Serveices & Utilities）管理。當該請求從等待隊列進入到處理隊列，管理器會將該請求丟給SQL介面（SQL Interface）。SQL介面接收到請求後，它會將請求進行hash處理並與緩存中的結果進行對比，如果完全匹配則通過緩存直接返回處理結果；否則，需要完整的走一趟流程：

(1)由SQL介面丟給後面的解釋器（Parser），上面已經說到，解釋器會判斷SQL語句正確與否，若正確則將其轉化為數據結構。

(2)解釋器處理完，便來到後面的優化器（Optimizer），它會產生多種執行計劃,最終資料庫會選擇最優化的方案去執行,儘快返會結果。

(3)確定最優執行計劃後，SQL語句此時便可以交由存儲引擎（Engine）處理，存儲引擎將會到後端的存儲設備中取得相應的數據，並原路返回給程式。

6）DDL,DML,DQL,TCL

DDL：數據定義語言

包含：

1、``CREATE` `: 在資料庫中創建新的數據對象
2、``ALTER` `: 修改資料庫中對象的數據結構
3、``DROP` `: 刪除資料庫中的對象
4、DISABLE/ENABLE ``TRIGGER` `: 修改觸發器的狀態
5、``UPDATE` `STATISTIC : 更新表/視圖統計信息
6、``TRUNCATE` `TABLE` `: 清空表中數據
7、COMMENT : 給數據對象添加註釋
8、RENAME : 更改數據對象名稱

DML：數據操作語言

包含：

1、``INSERT` `：將數據插入到表或視圖
2、``DELETE` `：從表或視圖刪除數據
3、``SELECT` `：從表或視圖中獲取數據
4、``UPDATE` `：更新表或視圖中的數據
5、MERGE ： 對數據進行合併操作(插入/更新/刪除)

DCL：數據控制語言

包含：

1、``GRANT` `: 賦予用戶某種控制許可權
2、``REVOKE` `：取消用戶某種控制許可權

TCL：事務控制語言

包含：

1、``COMMIT` `: 保存已完成事務動作結果
2、SAVEPOINT : 保存事務相關數據和狀態用以可能的回滾操作
3、``ROLLBACK` `: 恢復事務相關數據至上一次``COMMIT``操作之後
4、``SET` `TRANSACTION` `: 設置事務選項

7）事務的基本概念

事務是併發控制的單位，是用戶定義的一個操作序列。這些操作要麼都做，要麼都不做，是一個不可分割的工作單位。

事務的ACID/四大特征：

原子性：表示事務內操作不可分割。要麼都成功、要麼都是失敗；

一致性：要麼都成功、要麼都是失敗.後面的失敗了要對前面的操作進行回滾；

隔離性：一個事務開始後，不能後其他事務干擾；

持久性/持續性：表示事務開始了，就不能終止。

8）事務的隔離級別

臟讀：是指在一個事務處理過程里讀取了另一個未提交的事務中的數據。

當一個事務正在多次修改某個數據，而在這個事務中這多次的修改都還未提交，這時一個併發的事務來訪問該數據，就會造成兩個事務得到的數據不一致。例如：用戶A向用戶B轉賬100元，對應SQL命令如下

    update account set money=money+100 where name=’B’;  (此時A通知B)
​
    update account set money=money - 100 where name=’A’;

　　當只執行第一條SQL時，A通知B查看賬戶，B發現確實錢已到賬（此時即發生了臟讀），而之後無論第二條SQL是否執行，只要該事務不提交，則所有操作都將回滾，那麼當B以後再次查看賬戶時就會發現錢其實並沒有轉。

不可重覆讀：是指在對於資料庫中的某個數據，一個事務範圍內多次查詢卻返回了不同的數據值，這是由於在查詢間隔，被另一個事務修改並提交了。

例如事務T1在讀取某一數據，而事務T2立馬修改了這個數據並且提交事務給資料庫，事務T1再次讀取該數據就得到了不同的結果，發送了不可重覆讀。

　　不可重覆讀和臟讀的區別是，臟讀是某一事務讀取了另一個事務未提交的臟數據，而不可重覆讀則是讀取了前一事務提交的數據。

　　在某些情況下，不可重覆讀並不是問題，比如我們多次查詢某個數據當然以最後查詢得到的結果為主。但在另一些情況下就有可能發生問題，例如對於同一個數據A和B依次查詢就可能不同，A和B就可能打起來了……

幻讀：是事務非獨立執行時發生的一種現象。

例如事務T1對一個表中所有的行的某個數據項做了從“1”修改為“2”的操作，這時事務T2又對這個表中插入了一行數據項，而這個數據項的數值還是為“1”並且提交給資料庫。而操作事務T1的用戶如果再查看剛剛修改的數據，會發現還有一行沒有修改，其實這行是從事務T2中添加的，就好像產生幻覺一樣，這就是發生了幻讀。

　　幻讀和不可重覆讀都是讀取了另一條已經提交的事務（這點就臟讀不同），所不同的是不可重覆讀查詢的都是同一個數據項，而幻讀針對的是一批數據整體（比如數據的個數）。

現在來看看MySQL資料庫為我們提供的四種隔離級別：

　　① Serializable (串列化)：可避免臟讀、不可重覆讀、幻讀的發生。

　　② Repeatable read (可重覆讀)：可避免臟讀、不可重覆讀的發生。

　　③ Read committed (讀已提交)：可避免臟讀的發生。

　　④ Read uncommitted (讀未提交)：最低級別，任何情況都無法保證。

　　以上四種隔離級別最高的是Serializable級別，最低的是Read uncommitted級別，當然級別越高，執行效率就越低。像Serializable這樣的級別，就是以鎖表的方式(類似於Java多線程中的鎖)使得其他的線程只能在鎖外等待，所以平時選用何種隔離級別應該根據實際情況。在MySQL資料庫中預設的隔離級別為Repeatable read (可重覆讀)。

　　在MySQL資料庫中，支持上面四種隔離級別，預設的為Repeatable read (可重覆讀)；而在Oracle資料庫中，只支持Serializable (串列化)級別和Read committed (讀已提交)這兩種級別，其中預設的為Read committed級別。

在MySQL資料庫中查看當前事務的隔離級別：

    select @@tx_isolation;

在MySQL資料庫中設置事務的隔離級別：

    set  [glogal | session]  transaction isolation level 隔離級別名稱;
​
    set tx_isolation=’隔離級別名稱;’

9）索引

索引用於快速找出在某個列中有一特定值的行，不使用索引，MySQL必須從第一條記錄開始讀完整個表，直到找出相關的行，表越大，查詢數據所花費的時間就越多，如果表中查詢的列有一個索引，MySQL能夠快速到達一個位置去搜索數據文件，而不必查看所有數據，那麼將會節省很大一部分時間。

索引我們分為四類來：

單列索引：一個索引只包含單個列，但一個表中可以有多個單列索引。

斷裂索引又分為：

普通索引：MySQL中基本索引類型，沒有什麼限制，允許在定義索引的列中插入重覆值和空值，純粹為了查詢數據更快一點。

唯一索引：索引列中的值必須是唯一的，但是允許為空值。

主鍵索引：是一種特殊的唯一索引，不允許有空值。

組合索引：在表中的多個欄位組合上創建的索引，只有在查詢條件中使用了這些欄位的左邊欄位時，索引才會被使用，使用組合索引時遵循最左首碼集合。

全文索引：全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT類型欄位上使用全文索引，介紹了要求，說說什麼是全文索引，就是在一堆文字中，通過其中的某個關鍵字等，就能找到該欄位所屬的記錄行，比如有"你是個大煞筆，二貨 ..." 通過大煞筆，可能就可以找到該條記錄。

空間索引：空間索引是對空間數據類型的欄位建立的索引，MySQL中的空間數據類型有四種，GEOMETRY、POINT、LINESTRING、POLYGON。

　　　　　　　　　　　　　　　空間索引是對空間數據類型的欄位建立的索引，MySQL中的空間數據類型有四種，GEOMETRY、POINT、LINESTRING、POLYGON。

在創建空間索引時，使用SPATIAL關鍵字。要求，引擎為MyISAM，創建空間索引的列，必須將其聲明為NOT NULL。

創建索引方法：

create index 索引名 on 表名（欄位名）;

alter table 表名 add index 索引名（欄位名）；

create table(id int not null,username varchar(10),index 索引名(username(length)));

刪除索引方法：

ALTER TABLE 表名 DROP INDEX 索引名；

DROP INDEX 索引名 ON 表名；

查看索引：

可以使用 SHOW INDEX 命令來列出表中的相關的索引信息。可以通過添加 \G 來格式化輸出信息。

如：mysql> SHOW INDEX FROM table_name; \G

10）B樹結構

1970年，R.Bayer和E.mccreight提出了一種適用於外查找的樹，它是一種平衡的多叉樹，稱為B樹（或B-樹、B_樹）。

資料庫索引底層常用就是用就是B樹或者是B+樹這種結構

• 先說說樹吧，其實樹就是從一個根節點出發，其可以有很多子節點，而子節點又可以有很多子節點，這樣就像我們現實生活中的樹一樣，不過我們這顆樹是倒立的！因為樹的分支太多且沒有規律所以很難控制，要想讓樹發揮他的作用就得在基本的樹結構上加上一些特性，讓有了特性的樹成為幫助我們解決問題的結構，最常用的就是二叉樹了，二叉樹聽名字就知道是一個節點至多只有兩個節點，這樣對數進行了一定的限制，整棵樹看起來就順眼多了。

• 二叉樹的拓展1：二叉搜索樹，二叉搜索樹的節點滿足一個規律，父節點的左孩子的鍵值小於父節點的鍵值，而右孩子的鍵值大於父節點的鍵值，這樣當我們在這顆數中查詢某個鍵值時就可以根據當前節點的鍵值和要尋找的鍵值的大小比較，確定該忘哪條路走下去。二叉搜索樹還有一個特點就是中序遍歷的時候其鍵值是按大小排序的。

• 二叉樹的拓展2：平衡二叉樹，由於我們要插入的數據可能是本身就排好序的，所以會導致插入數據時樹變成線性的結構，只有一條路。。於是我們需要保證二叉樹的平衡，當發現這棵樹要出現往一邊倒的情況時就要想某種方式讓其保持平衡（葉子節點的高度差最大為1），這就設計到一些節點的旋轉，變換了。

• 二叉樹的拓展3：紅黑樹，紅黑樹也是一種平衡二叉樹，不過加入了一些新的特性，聽名字就知道，在紅黑樹中節點的顏色要麼是紅色要麼是黑色的，當然還有其他的一些特性，當插入或者刪除數據破壞了紅黑樹的這些特性時，我們需要進行一些操作（一般是顏色改變和樹的旋轉）紅黑樹保持其原有的特性。

• 由於二叉樹是二叉的，所以當樹的節點不斷增加時就會導致樹的高度不斷的增加，所以查詢的效率就很低了，當我們面對海量數據（像資料庫中保存的數據）的時候這種結構是不行的，所以我們又衍生出了新的樹結構。

• 二叉樹的拓展4：B樹，B數一樣擁有自平衡的特性，最大的區別在於B樹不是二叉的，而是多叉的，具體有多少個叉要根據樹的階數來判斷。

• 二叉樹的拓展5：B+樹，和B樹相比，B+樹又增加了一些特性，B+樹主要是為了方便查詢一個區間的數據集合，因為我們使用B樹的時候要想查詢某個區間內的數據得使用中序遍歷將樹中的數據全部遍歷一遍，這樣的時間複雜度是O（n）,效率太低了。而B+樹只用葉子節點保存具體值的地址，非葉子節點只保存其子節點的指針，葉子節點之間通過指針鏈接起來，是有序的，所以在查找一個範圍內的數據是很有效的。其時間複雜度為O(logn+M)，M為要查找的數據個數。