（大資料工程師學習路徑）第四步 SQL基礎課程----其他（基礎練習到此為止）

標籤：

一、準備

在正式開始本內容之前，需要先從github下載相關代碼，搭建好一個名為mysql_shiyan的資料庫(有三張表：department，employee，project)，並向其中插入資料。

具體操作如下,首先輸入命令進入tmp目錄：

cd /tmp

然後再輸入命令，下載代碼：

git clone http://git.shiyanlou.com/shiyanlou/SQL6

下載完成後，輸入“cd ~”（注意有空格）退回到原先目錄，然後輸入命令開啟MySQL服務並使用root使用者登入：

sudo service mysql start        #開啟MySQL服務mysql -u root                   #使用root使用者登入

剛才從github下載的SQL6目錄下，有個兩檔案“MySQL-06.sql”和“in.txt”，其中第一個檔案用於建立資料庫並向其中中插入資料，第二個檔案用於之後的實驗步驟。

（你可以進入/tmp/SQL6目錄用Gedit查看裡面兩個檔案的內容。）

輸入命令運行第一個檔案，搭建資料庫並插入資料：

source /tmp/SQL6/MySQL-06.sql

二、內容1、索引

索引是一種與表有關的結構，它的作用相當於書的目錄，可以根據目錄中的頁碼快速找到所需的內容。 當表中有大量記錄時，若要對錶進行查詢，沒有索引的情況是全表搜尋：將所有記錄一一取出，和查詢條件進行一一對比，然後返回滿足條件的記錄。這樣做會消耗大量資料庫系統時間，並造成大量磁碟I/O操作。 而如果在表中已建立索引，在索引中找到符合查詢條件的索引值，通過索引值就可以快速找到表中的資料，可以大大加快查詢速度。

對一張表中的某個列建立索引，有以下兩種語句格式：

ALTER TABLE 表名字 ADD INDEX 索引名 (列名);CREATE INDEX 索引名 ON 表名字 (列名);

我們用這兩種語句分別建立索引：

ALTER TABLE employee ADD INDEX idx_id (id);  #在employee表的id列上建立名為idx_id的索引CREATE INDEX idx_name ON employee (name);   #在employee表的name列上建立名為idx_name的索引

索引的效果是加快查詢速度，當表中資料不夠多的時候是感受不出它的效果的。這裡我們使用命令 SHOW INDEX FROM 表名字; 查看剛才建立的索引：

在使用SELECT語句查詢的時候，語句中WHERE裡面的條件，會自動判斷有沒有可用的索引。

2、視圖

視圖是從一個或多個表中匯出來的表，是一種虛擬存在的表。它就像一個視窗，通過這個視窗可以看到系統專門提供的資料，這樣，使用者可以不用看到整個資料庫中的資料，而只關心對自己有用的資料。

注意理解視圖是虛擬表：

• 資料庫中只存放了視圖的定義，而沒有存放視圖中的資料，這些資料存放在原來的表中；
• 使用視圖查詢資料時，資料庫系統會從原來的表中取出對應的資料；
• 視圖中的資料依賴於原來表中的資料，一旦表中資料發生改變，顯示在視圖中的資料也會發生改變；
• 在使用視圖的時候，可以把它當作一張表。

建立視圖的語句格式為：

CREATE VIEW 視圖名(列a,列b,列c) AS SELECT 列1,列2,列3 FROM 表名字;

可見建立視圖的語句，後半句是一個SELECT查詢語句，所以視圖也可以建立在多張表上，只需在SELECT語句中使用子查詢或串連查詢，這些在之前的實驗已經進行過。

現在我們建立一個簡單的視圖，名為 v_emp，包含v_name，v_age，v_phone三個列：

3、匯入

匯入操作，可以把一個檔案裡的資料儲存進一張表。匯入語句格式為：

LOAD DATA INFILE ‘檔案路徑‘ INTO TABLE 表名字;

現在 /tmp/SQL6 目錄下有一個名為 in.txt 的檔案，我們嘗試把這個檔案中的資料匯入資料庫 mysql_shiyan 的 employee 表中。

先按住 Ctrl+Z 退出MySQL，再使用命令 gedit /tmp/SQL6/in.txt 查看 test.txt 檔案中的內容：

再使用以下命令以root使用者登入資料庫，再串連 mysql_shiyan 資料庫：

mysql -u rootuse mysql_shiyan

查看一下沒有匯入資料之前，employee表中的資料：

現在執行匯入語句，檔案中的資料成功匯入employee表：

4、匯出

匯出與匯入是相反的過程，是把資料庫某個表中的資料儲存到一個檔案之中。匯出語句基本格式為：

SELECT 列1，列2 INTO OUTFILE ‘檔案路徑和檔案名稱‘ FROM 表名字;

注意：語句中 “檔案路徑” 之下不能已經有同名檔案。

現在我們把整個employee表的資料匯出到 /tmp 目錄下，匯出檔案命名為 out.txt 具體語句為：

SELECT * INTO OUTFILE ‘/tmp/out.txt‘ FROM employee;

用gedit可以查看匯出檔案 out.txt 的內容：

5、備份

資料庫中的資料或許十分重要，出於安全性考慮，在資料庫的使用中，應該注意使用備份功能。

備份與匯出的區別：匯出的檔案只是儲存資料庫中的資料；而備份，則是把資料庫的結構，包括資料、約束、索引、視圖等全部另存新檔一個檔案。

mysqldump是MySQL用於備份資料庫的公用程式。它主要產生一個SQL指令檔，其中包含從頭重新建立資料庫所必需的命令CREATE TABLE INSERT等。

使用mysqldump備份的語句：

mysqldump -u root 資料庫名>備份檔案名;   #備份整個資料庫mysqldump -u root 資料庫名 表名字>備份檔案名;  #備份整個表

我們嘗試備份整個資料庫 mysql_shiyan，將備份檔案命名為 bak.sql，先 Ctrl+Z 退出MySQL，再使用語句：

mysqldump -u root mysql_shiyan > bak.sql;

使用命令“ls”可見已經產生備份檔案bak.sql：

你可以用gedit查看備份檔案的內容，可以看見裡面不止儲存了資料，還有所備份的資料庫的其他資訊。

6、恢複

用備份檔案恢複資料庫，其實我們早就使用過了。在本次實驗的開始，我們使用過這樣一條命令：

source /tmp/SQL6/MySQL-06.sql

這就是一條恢複語句，它把MySQL-06.sql檔案中儲存的mysql_shiyan資料庫恢複。

還有另一種方式恢複資料庫，但是在這之前我們先使用命令建立一個空的資料庫 test：

mysql -u root          #因為在上一步已經退出了MySQL，現在需要重新登入CREATE DATABASE test;  #建立一個名為test的資料庫

再次 Ctrl+Z 退出MySQL，然後輸入語句進行恢複，把剛才備份的 bak.sql 恢複到test 資料庫：

mysql -u root test < bak.sql

我們輸入命令查看tset資料庫的表，便可驗證是否恢複成功：

mysql -u root          #因為在上一步已經退出了MySQL，現在需要重新登入use test               #串連資料庫testSHOW TABLES;           #查看test資料庫的表

可以看見原資料庫的4張表和1個視圖，現在已經恢複到test資料庫中：

再查看employee表的恢複情況：

（大資料工程師學習路徑）第四步 SQL基礎課程----其他（基礎練習到此為止）