全棧 - 11 資料庫 MySQL使用方法

這是全棧數據工程師養成攻略系列教程的第十一期：11 資料庫 MySQL使用方法。

我們已經掌握了如何用MAMP或WAMP在個人電腦上搭建Web環境，其中包含了用於存儲數據的關係型資料庫MySQL，現在就讓我們來了解下如何使用MySQL。

基本概念

MySQL中可以存在多個資料庫（Database），每個資料庫對應一個相對獨立的項目。一個資料庫中可以包含多個數據表（Table），不同的數據表用來存儲不同用途的數據。舉例來說，可以新建一個chat資料庫用於存儲和某一社交網站相關的數據，裡面有user和message兩個表，分別用來存儲用戶基本信息、用戶之間的聊天記錄。需要注意的是，資料庫名和數據表名最好都使用英文名稱。

數據表和Excel中的表格很類似，既有行也有列，即我們之前提及的二維表結構。以user表為例，每一行記錄了一名用戶的基本信息，每一列即用戶信息的一個欄位，例如姓名、性別、職業等。

MySQL涉及的操作包括新建或刪除資料庫，以及在一個已有的資料庫中新建、清空或刪除數據表。除此之外，主要就是在一個已有的數據表中對數據進行CURD操作，即Create、Update、Read、Delete，分別對應插入數據、更新數據、讀取數據、刪除數據。接下來我們將了解，如何使用命令行、Web工具、本地軟體、Python代碼四種方法，來操作MySQL資料庫。

命令行

可以在命令行中輸入以下命令，按回車後再輸入MySQL資料庫的密碼，即可進入MySQL提供的交互命令行，類似Python的交互編程環境，每敲一行MySQL語句，按回車即可執行。這種方法僅適用於單獨安裝MySQL資料庫，不適用於MAMP或WAMP，而且對代碼能力要求較高，故不推薦使用。

mysql -u root -pn

Web工具

Web工具是指在MySQL內核之上，基於Web開發出的圖形化操作界面。只要在網站上點一點，即可完成對MySQL的各類操作。其中最為常用的一種即phpMyAdmin，基於PHP開發，簡單、輕量、好用，在MAMP或WAMP中也會自帶phpMyAdmin。

在MAMP的歡迎頁面上可以找到使用phpMyAdmin管理MySQL的鏈接，點擊後將看到以下界面，左側中所顯示的是當前已存在的資料庫，右側中則顯示了菜單欄和對應的內容。

首先讓我們來嘗試下如何新建資料庫。默認情況下會存在三個資料庫，information_schema、mysql和performance_schema，這三個資料庫是MySQL自帶的，不要去動它們。點擊左側中的New，或者右側菜單欄中的Databases，都可以在右側中看到當前已存在資料庫的一些基本信息，以及進行資料庫新建操作。

輸入新建資料庫的名稱，推薦使用全英文。這裡我們輸入douban，因為需要將之前爬取的豆瓣電影數據存入資料庫中。選擇Collation為utf8_general_ci，然後點擊Create即可。

新建好資料庫後，由於資料庫為空，會自動跳到新建數據表的頁面。給數據表取個英文名，這裡輸入movie，然後選擇表的列數，默認為4，點擊Go進入下一步。即使數據表最終不是4列也沒關係，多的列會自動忽略，列數不夠同樣可以繼續加，所以不用擔心。

接下來需要配置每一列的詳細內容，包括Name、Type、Length/Values、Default、Collation、Attributes、Null、Index、A_I、Comments。我們首先填寫第一個欄位，每個數據表都需要一個主鍵即id，不同的行具有唯一不同的id，用於進行彼此區分。

• Name：欄位的名稱，純英文，讓我們輸入id；
• Type：欄位的變數類型，id應當是正整數，所以使用默認的INT即可；
• Length/Values：欄位的長度，不填的話會使用默認值，即INT類型的默認長度；
• Default：插入數據時如果不提供值，欄位的默認值，這裡可以先不管；
• Collation：不填的話則使用資料庫的Collation，否則覆蓋，這裡可以先不管；
• Attributes：欄位的屬性，這裡可以先不管；
• Null：欄位是否默認為空值，默認不勾選；
• Index：欄位使用何種索引，這裡選擇PRIMARY，即主鍵，如果有彈窗則點擊Go即可；
• A_I：是否自增，Auto Increasement，即在插入數據時如果不提供值，會自動增加，第一條記錄為1，第二條記錄為2，以此類推。這裡需要勾上，因為我們希望id是自增的，從而不同記錄使用不同的id；
• Comments：欄位的備註信息，可以留空不管。

接下來再填一下電影的標題欄位，Name輸入title，Type選為VARCHAR，Length/Values輸入255，因為255個字元對於標題而言足夠了，其他選項不用管。Type的可選值包括數值類Numeric、日期時間類Date and time、文本類String、空間類Spatial四大類，以上所使用的VARCHAR是一種長度可變的字元串，使用時需要設置最大長度。如果需要存儲更長的文本，可以考慮TEXT、MEDIUMTEXT、LONGTEXT等。

同理，繼續填寫電影的鏈接欄位，填入url、VARCHAR和255；對於電影的簡介欄位，由於電影簡介可能會很長，所以Name輸入summary，Type選擇TEXT；至於電影的評分欄位，由於評分可以是小數，所以Name輸入score，Type選擇Numeric類中的FLOAT。如果需要繼續增加欄位，輸入需要增加的列數並點擊Go，然後根據欄位特徵完善Name、Type和Length/Values即可。

欄位信息全部填寫完畢後，點擊右下方的Save即可完成數據表的新建，左側中會選中剛才新建的資料庫和數據表，右側中會出現新的菜單欄。點擊Browse可以查看數據表內的數據記錄，點擊Structure可以查看數據表的結構，即各項欄位的配置內容，點擊SQL可以在當前數據表上執行SQL命令，點擊Insert可以向當前數據表中插入數據記錄，點擊Export和Import分別可以導出和導入數據表，點擊Operations可以進一步執行清空數據表和刪除數據表等操作。

在上圖所示的Structure標籤頁中，可以查看數據表的結構，即各項欄位的詳細配置，並對某個欄位執行修改Change、刪除Drop等操作。除此之外，還可以向數據表中添加若干列，以及查看數據表的空間存儲情況。

如果在左側點擊某一資料庫，則右側的菜單欄相應地會變成資料庫級別的操作，如查看資料庫的結構，即各個數據表的基本信息，在當前資料庫上執行SQL命令，導出和導入資料庫，執行刪除資料庫等操作。

細心的話可以發現，每次在phpMyAdmin上執行相關操作之後，右側菜單欄下都會出現相應的SQL命令，說明phpMyAdmin都是將我們的點擊操作轉換成了相應的SQL命令，然後交與MySQL執行。

讓我們嘗試下向movie表中插入數據。在左側中點擊douban資料庫中的movie表，選擇右側菜單欄中的Insert，依次在title、url、summary、score的Value中填寫對應的值，id留空。如果需要同時插入兩條數據，則取消勾選ignore並填寫第二條數據的對應值。如果需要同時插入更多數據，則在底部的Continue insertion with後選擇相應的行數，並依次填寫相關數據。數據全部填寫完畢後，點擊頁面下方的Go，即可進行數據插入操作，在菜單欄下也可以看到對應INSERT命令。之後再點擊Browse，即可查看剛插入的數據。

總而言之，phpMyAdmin是一款簡單輕量好用的Web工具，它提供的功能雖然有限，但都是管理MySQL所需的最為核心的功能，因此更容易上手，不會因為功能太多反而導致眼花繚亂。

本地軟體

相對於phpMyAdmin等Web工具，本地數據管理軟體連接更穩定、功能更強大，例如Navicat Premium，Mac OS版本的軟體界面如下圖所示。

Navicat支持MySQL、Oracle、PostgreSQL、SQLite、SQL Server、MariaDB等多種資料庫，在資料庫和數據表之上還有數據連接（Connection）這一概念，因為Navicat可以記錄並連接多個主機上的資料庫，而phpMyAdmin作為一種Web工具僅能連接本地資料庫。

讓我們來嘗試新建一個連接。點擊左上角的Connection，選擇MySQL，在彈出的對話框中依次填入連接名稱、主機地址、埠、用戶名、密碼等信息，其中基於MAMP安裝的MySQL主機地址即為localhost，點擊OK即可新建一個連接。當然，新建連接時還可以涉及到更多配置內容，這裡就不展開講了。

新建連接後，在Navicat軟體界面的左側即可看到當前已存在的全部連接，雙擊連接名稱即可查看連接下存在的資料庫，雙擊資料庫名稱即可建立數據連接，並查看資料庫下存在的數據表。在連接名、資料庫名、數據表名上右鍵，都會出現一系列可執行的操作，例如在資料庫名和數據表名的右鍵菜單里，都有Execute SQL File和Dump SQL File兩項，分別對應資料庫級別和數據表級別的導入和導出操作。

總的來說，Navicat功能更強大，使用門檻也更高，畢竟可點擊的按鈕、可配置的選項、可執行的操作都遠遠更多。我個人的習慣是，使用phpMyAdmin完成新建資料庫、新建數據表、定義表欄位等操作，因為phpMyAdmin簡單輕量，但又足以完成這些任務；使用Python代碼對數據表進行CURD操作，因為項目中涉及的數據記錄可能非常多，所以用Python代碼處理自然是最高效和靈活的選擇；當需要導入導出大量數據，或者導入導出需要對涉及欄位進行靈活配置時，則使用Navicat完成複雜的導入導出任務。

所以，我首先在本機上編程並將資料庫和數據表整理好，在本地Web環境上實現Web項目和資料庫的交互，然後將資料庫導出並導入到雲端伺服器上的MySQL，最後將Web項目也部署到伺服器上，即可完成一次數據項目的開發。

至於如何使用Python來操縱MySQL資料庫，將在下一節中進行講解。

視頻鏈接：MySQL使用方法