內容目錄
SQL 基礎
SQL 是用來查詢結構化的資料集的語言,資料集有明確的規則。
資料庫是同時儲存一個或多個資料表的集合。
大多數的情況,SQL 是用來查詢部分資料表,以及資料表間聯結 (join) 的資料。
SELECT 與 FROM
SQL 有預先定義的語法,使用者用制式的語法向資料庫「問問題」。
其中最基礎的就是 SELECT 與 FROM。
SELECT是用來提取想要選取的欄位。FROM用來指定想要從哪 (幾) 張資料表中提取資料。
例如「從 example_table 資料表中,提取 USER 欄位的資料」。
SELECT USER FROM example_table
或者「從 example_table 資料表中,提取 USER 及 GENDER 欄位的資料」。
SELECT USER, GENDER FROM example_table
WHERE
WHERE 是用來篩選資料條件的語法,譬如:「從 example_table 資料表中,提取 GENDER 是 MALE 的 USER 資料」
SELECT USER FROM example_table WHERE SHIPPED='YES'
BigQuery 主控台
從導覽列中點選 [BigQuery],點擊 [新增資料] > [探索公開資料集],取得公開的資料。
Qwiklab 使用的範例是 London Bicycle Hires 這個資料集,搜尋後,點擊 [查看資料集 (View Dataset)]。此時編輯區會加入名為 bigquery-public-data 的新專案。
展開 bigquery-public-data,尋找 london-bicycles 資料集,選擇 cycle-hires 資料表。點擊編輯區的 [Preview],可以以表格的方式預覽資料。
接著到編輯器中,查詢自行車還車點的名稱。
SELECT end_station_name FROM `bigquery-public-data.london_bicycles.cycle_hire`;
總共有 2,400 多萬筆的資料。
利用 SELECT * 選取所有欄位,並篩選查詢「超過 20 分鐘行程」的借還紀錄。
SELECT * FROM `bigquery-public-data.london_bicycles.cycle_hire` WHERE duration>=1200;
查詢出來共有 730 多萬筆資料。換句話說,倫敦有 30% 以上的共享單車租用超過 20 分鐘。
GROUP BY
利用 GROUP BY 語法,可以將資料依據欄位的微一值進行分組。
SELECT start_station_name FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name;
上述的例子便能透過 start_station_name 找到倫敦總共有 880 個共享單車的起租點。
COUNT
COUNT() 函數功能可以用來回傳共通特性的計次結果,常與 GROUP BY 一起使用。
SELECT start_station_name, COUNT(*) FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name;
執行後可以看到起租點的租借次數。
AS
AS 語法是用來製作別名 (alias) 用的。換句話說,就是將欄位名稱重新命名為新的名稱。配合 COUNT() 函數可以這樣執行。
SELECT start_station_name, COUNT(*) AS num_starts FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name;
執行結果中,原本未定義的欄位名稱,變成 num_starts。
ORDER BY
用來排序查詢結果,有升序 (ASC)、降序 (DESC) 之分。
SELECT start_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name ORDER BY start_station_name; SELECT start_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name ORDER BY num; SELECT start_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name ORDER BY num DESC;
上述的指令會回傳以下結果:
- Return a table that contains the number of bikeshare rides that begin in each starting station, organized alphabetically by the starting station.
- Return a table that contains the number of bikeshare rides that begin in each starting station, organized numerically from lowest to highest.
- Return a table that contains the number of bikeshare rides that begin in each starting station, organized numerically from highest to lowest.
執行 Cloud SQL
先將最後執行的查詢結果儲存成 CSV 檔備用。接著執行下列指令:
SELECT end_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY end_station_name ORDER BY num DESC;
將查詢結果儲存成 CSV 檔並下載。
建立 Cloud Storage 貯體,並將下載的 CSV 檔個別命名為 start_station_name.csv 與 end_station_name.csv 後,上傳至貯體。
從導覽選單中,選擇 [SQL]。接著建立新的實體,選擇 [MySQL],輸入資料庫名稱與 root 的密碼後,建立新的實體。
利用 Cloud Shell 建立資料庫與資料表
gcloud sql connect qwiklabs-demo --user=root
輸入密碼後,便會進入 MySQL 的指令介面。建立資料庫 bike,接著用 USE 指令選擇資料庫,並 CREATE TABLE 建立新的資料表 london1,加入欄位 start_station_name 與 num。
CREATE DATABASE bike; USE bike; CREATE TABLE london1 (start_station_name VARCHAR(255), num INT);
接著建立資料表 london2,加入欄位 end_station_name 與 num。
USE bike; CREATE TABLE london2 (end_station_name VARCHAR(255), num INT);
可以嘗試查詢兩個資料表的結果,雖然應該會回傳 Empty。
SELECT * FROM london1; SELECT * FROM london2;
從主控台中,回到實體的 Overview,點選 IMPORT,從 Cloud Storage 貯體將資料匯入到指定的資料表中。
DELETE
利用 DELETE FROM 語法,將符合條件的資料列刪除。
DELETE FROM london1 WHERE num=0; DELETE FROM london2 WHERE num=0;
INSERT INTO
利用 INSERT INTO 語法,插入新的資料列。
INSERT INTO london1 (start_station_name, num) VALUES ("test destination", 1);UNION
利用 UNION 可以將兩個搜尋結果合併。
SELECT start_station_name AS top_stations, num FROM london1 WHERE num>100000 UNION SELECT end_station_name, num FROM london2 WHERE num>100000 ORDER BY top_stations DESC;