MySQL資料庫基礎——本地文件交互

從這一篇開始，大概會花四五篇的內容篇幅，歸納整理一下之前學過的SQL資料庫，一來可以為接下來數據分析工作提前鞏固基礎，二來把以前學的SQL內容系統化、結構化。

今天這一篇僅涉及MySQL與本地文本文件的導入導出操作，暫不涉及主要查詢語言以及MySQL與R語言和Python的交互。

平台使用Navicat Premium（當然你也可以使用MySQL自帶的workbench或者MySQL Conmand line）。

以下僅涉及MySQL中使用命令行語句導入/導出本地磁碟的文本文件（csvtxt文件）。

文件導入（csv）：

在導入本地文件之前，請確保你的MySQL設置有本地文件導入導出許可權。

在導入MySQL之前，需要在指定資料庫中先建立空表，以備之後導入。

USE db1;nCREATE TABLE subway (n ID INTEGER(5) NOT NULL AUTO_INCREMENT, -- ID是主鍵，格式為整數，非空值、自增列n address VARCHAR(10) NOT NULL,n lon FLOAT(10,6) NOT NULL, -- 浮點n lat FLOAT(10,6) NOT NULL, -- 浮點n Type VARCHAR(10) NOT NULL, -- 字元型（10位）n PRIMARY KEY (ID) -- 設定主鍵n );n

創建空表的語句格式如上：

CREATE TABLE 表名 (n column1 類型(字元位數) 是否允許為空值 自增列（可選） 默認值（可選）,n column2 類型(字元位數) 是否允許為空值 自增列（可選） 默認值（可選）,n PRIMARY KEY (column1)n );n

此時在MySQL中生成一個空表（僅有欄位名稱）。

以下是導入語句：

load data local infile D:/subway.txt ninto table db1.subway -- 導入本地文件語句ncharacter set gbk -- 設置導入文件編碼 nfields terminated by t -- 指定txt文件內的欄位分隔符noptionally enclosed by " -- 指定字元閉合符(可選參數，有些格式txt會設置字元使用雙引號/單引號包括等格式)nescaped by " -- 指定轉義符（字元內含符號與閉合符衝突，使用何種符號進行包括並轉義，使其保留原意）nlines terminated by rn -- 指定換行符nignore 1 lines -- 指定從文件第幾行開始導入（如果本地文件有行名，需要略過一行）n(address,lon,lat,Type); -- 最後一行指定要導入的列名(次內列名需與之前新建的空表列名嚴格匹配)n

主鍵可以設定為導入列中的某一列(保證無缺失值無重複值即可)，並不是必須設置的。

做簡單的表格信息概覽：

desc db1.subway n

文件導出（TXT）：

將剛才導入的subway文件導出到本地。

select "ID","address","lon","lat","Type" -- 為要導出的字典命名nunion select * from db1.subway -- 指定要從目標表中導出的欄位（與第一句指定的欄位嚴格對應）ninto outfile D:/SUBWAY.txt -- 導出目錄及文件名ncharacter set gbk -- 設置輸出編碼nFIELDS TERMINATED BY t -- 欄位分隔符n-- OPTIONALLY ENCLOSED BY " -- 文本包括符號(可選，這裡注釋掉了) n-- escaped by " -- 衝突轉義符（可選，這裡注釋掉了）nLINES TERMINATED BY rn; -- 換行符n

文件導入（csv）:

仍然是導入之前先新建空表：

CREATE TABLE President (nn ID INTEGER(5) NOT NULL AUTO_INCREMENT, -- ID是主鍵，格式為整數，非空值、自增列n STATE_NAME VARCHAR(15) NOT NULL, -- 字元型（15位）n STATE_ABBR VARCHAR(5) NOT NULL, -- 浮點n Count INTEGER(5) NOT NULL, -- 整數型（5位）n Clinton FLOAT(8,4) NOT NULL, -- 浮點型（8位，保留四位小數）n Trump FLOAT(8,4) NOT NULL,n Results VARCHAR(5) NOT NULL, PRIMARY KEY (ID) n );n

導入本地CSV文件：

load data local infile D:/President.csv ninto table db1.president -- 導入本地文件語句n-- character set gbk -- 設置導入文件編碼 (因為原始文件就是utf-8編碼的，這裡無需指定，如果不是需要單獨指定) nfields terminated by , -- 指定txt文件內的欄位分隔符noptionally enclosed by " -- 指定字元閉合符(可選參數，有些格式txt會設置字元使用雙引號/單引號包括等格式)nescaped by " -- 指定轉義符（字元內含符號與閉合符衝突，使用何種符號進行包括並轉義，使其保留原意）nlines terminated by rn -- 指定換行符nignore 1 lines -- 指定從文件第幾行開始導入（如果本地文件有行名，需要略過一行）n(STATE_NAME,STATE_ABBR,Count,Clinton,Trump,Results);n

DESC db1.president

文件導出（csv）:

將剛才導入的President文件導出到本地csv文件。

select "ID","STATE_NAME","STATE_ABBR","Count","Clinton","Trump","Results" -- 為要導出的字典命名nunion select * from db1.president -- 指定要從目標表中導出的欄位（與第一句指定的欄位嚴格對應）ninto outfile D:/President1.csv -- 導出目錄及文件名ncharacter set gbk -- 設置輸出編碼nFIELDS TERMINATED BY , -- 欄位分隔符nOPTIONALLY ENCLOSED BY " -- 文本包括符號(可選，這裡注釋掉了)nescaped by " -- 衝突轉義符（可選，這裡注釋掉了）nLINES TERMINATED BY rn; -- 換行符 n

除此之外，還有幾個基礎的增刪命令需要掌握：

插入命令：

INSERT INTO presidentn (LastName, Address) -- 插入列名稱nVALUES n (Wilson, Champs-Elysees); -- 具體值nselect count(*) from db1.presidentnINSERT INTO db1.president (STATE_NAME,STATE_ABBR,`Count`,Clinton,Trump,Results) nVALUES (Wilson, WL,10,0.4555,0.2344,T);nnselect count(*) from db1.president n

關於刪除表：

truncate db1.president; -- 刪除表內所有記錄（保留空表）ndrop table db1.president; -- 徹底刪除表（資料庫中該表將不存在）ndrop table db1.subway; -- 徹底刪除表（資料庫中該表將不存在）nselect count(*) from db1.president n

本文小結：

文件導入（txtcsv）

數據導出（TXTcsv）

表創建

表刪除

記錄插入

在線課程請點擊文末原文鏈接：

Hellobi Live | R語言可視化在商務場景中的應用

推薦閱讀：