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

張巨集倫發表於2019-01-19

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

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

基本概念

MySQL中可以存在多個資料庫(Database),每個資料庫對應一個相對獨立的專案。一個資料庫中可以包含多個資料表(Table),不同的資料表用來儲存不同用途的資料。舉例來說,可以新建一個chat資料庫用於儲存和某一社交網站相關的資料,裡面有usermessage兩個表,分別用來儲存使用者基本資訊、使用者之間的聊天記錄。需要注意的是,資料庫名和資料表名最好都使用英文名稱。

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

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

命令列

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

mysql -u root -p複製程式碼

Web工具

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

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

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

首先讓我們來嘗試下如何新建資料庫。預設情況下會存在三個資料庫,information_schemamysqlperformance_schema,這三個資料庫是MySQL自帶的,不要去動它們。點選左側中的New,或者右側選單欄中的Databases,都可以在右側中看到當前已存在資料庫的一些基本資訊,以及進行資料庫新建操作。

輸入新建資料庫的名稱,推薦使用全英文。這裡我們輸入douban,因為需要將之前爬取的豆瓣電影資料存入資料庫中。選擇Collationutf8_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是一種長度可變的字串,使用時需要設定最大長度。如果需要儲存更長的文字,可以考慮TEXTMEDIUMTEXTLONGTEXT等。

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

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

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

在上圖所示的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版本的軟體介面如下圖所示。

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

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

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

新建連線後,在Navicat軟體介面的左側即可看到當前已存在的全部連線,雙擊連線名稱即可檢視連線下存在的資料庫,雙擊資料庫名稱即可建立資料連線,並檢視資料庫下存在的資料表。在連線名、資料庫名、資料表名上右鍵,都會出現一系列可執行的操作,例如在資料庫名和資料表名的右鍵選單裡,都有Execute SQL FileDump SQL File兩項,分別對應資料庫級別和資料表級別的匯入和匯出操作。

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

所以,我首先在本機上程式設計並將資料庫和資料表整理好,在本地Web環境上實現Web專案和資料庫的互動,然後將資料庫匯出並匯入到雲端伺服器上的MySQL,最後將Web專案也部署到伺服器上,即可完成一次資料專案的開發。

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

視訊連結:MySQL使用方法

如果覺得文章不錯,不妨點一下左下方的喜歡~

相關文章