SQL學習記錄（一）

跟着Udacity上的SQL課程學完有一段時間了，通過總結來加深一哈印象。（個人認為這個課程不錯，網址(https://classroom.udacity.com/courses/ud198)）

課程隻有七節課，會按照順序記錄

• Basic
• Join
• Aggregation
  • 常用
  • 日期
  • CASE
• Subqueries
• Data Cleaning
• Windows Functions
• SQL Advanced

1、Basic

SQL是結構化的查詢語句，它主要是對關系型的資料庫來說的。這個語句主要可以分為四類：

• DDL

  資料定義語言，主要是用來定義資料庫的對象，例如資料庫，表，列之類的。主要是對應資料庫和表的CRUD。（現在的用戶端操作友善，這方面很多都沒有去注意。）

• DML

  資料操作語言，主要是增删改。主要是

//增(一一對應，也可省略不寫，預設添加所有列)
INSERT INTO 表名(列名1,列名2,列名3,...) VALUES (值1,值2,值3,...) ;
//删
DELETE FROM 表名 WHERE 條件 ；

TRUNCATE TABLE 表名；//删除整張表的記錄的效率會更高
//改
UPDATE 表名 SET 列1=值1,列2=值2,列3=值3... WHERE 條件;
           

• DQL

  資料查詢語言，主要是查詢表，這也是課程主要講的内容。

  最基礎的查詢主要有

  

  這個寫（詳細點的後面會總結）

  

• DCL

  資料控制語言，主要是定義通路權限和安全級别。

SQL語言支援的是三級模式結構，如下圖所示

2、Join

這一塊的内容主要是針對于兩張表以上的查詢，這有利于我們從多張表裡取資料，主要分為：

1. 内連接配接

内連接配接分為隐式内連接配接和顯式内連接配接，二者隻是寫法不同，查詢的都是兩張表的交集部分

//顯示(條件中的字段名加單引号)
SELECT 字段名 FROM 表1 JOIN 表2 ON 條件;
//隐式(條件中的字段名加單引号)
SELECT 字段名 FROM 表1,表2 WHERE 條件;
           

為了書寫簡單，最好都起别名進行書寫。

2.外連接配接

外連接配接主要有 LEFT JOIN 、RIGHT JOIN 、FULL JOIN

寫法跟内連接配接顯示寫法相同，傳回的結果集不同。

3、Aggregation

SQL中提供得聚合函數有，COUNT、SUM、AVG、MAX、MIN。一般運用在select後面的對象中，其中需要注意的是在聚合函數遇到空值的時候，除了COUNT()外所有的函數皆跳過空值，隻處理非空值。（可以用IFNULL(列名,置為的值)*函數處理）。

聚合函數一般與GROUP BY和HAVING 一起使用，實作分組聚合

• GROUP BY 是對查詢結果按照某一列或者某幾列進行分組，值相等的分為一組。
• HAVING對分組的結果進行選擇，僅輸出滿足條件的組。（必須與GROUP BY配合使用）通常更多是用于對于聚合之後的元素進行選擇。例如

//有多少使用者有20個以上的訂單（COUNT的對象就是連接配接之後的虛拟表）
SELECT a.id, a.name, COUNT(*) num_orders
FROM accounts a
JOIN orders o
ON a.id = o.account_id
GROUP BY a.id, a.name
HAVING COUNT(*) > 20
ORDER BY num_orders;
           

特殊并常用的。對于日期的聚合，對于時間戳聚合顯然是不常用的。對此SQL引入了兩個函數

• DATE_TRUNC

//查詢2014年到2017年之間每個月銷售總數量
SELECT DATE_TRUNC('month', occurred_at) ord_month, SUM(total_amt_usd) total_spent
FROM orders
WHERE occurred_at BETWEEN '2014-01-01' AND '2017-01-01'
GROUP BY 1
ORDER BY 2 DESC; 
           

• DATE_PART

  對于這函數以及更多有關日期的函數的使用可以參看文檔（https://www.postgresql.org/docs/9.1/functions-datetime.html）

CASE情況也是聚合常用的。CASE必須包含以下元件:WHEN、THEN和END。ELSE是一個可選元件，用于捕獲不滿足任何其他先前案例條件的案例。通過一個例子可以了解

SELECT s.name, COUNT(*), SUM(o.total_amt_usd) total_spent, 
     CASE WHEN COUNT(*) > 200 OR SUM(o.total_amt_usd) > 750000 THEN 'top'
     WHEN COUNT(*) > 150 OR SUM(o.total_amt_usd) > 500000 THEN 'middle'
     ELSE 'low' END AS sales_rep_level
FROM orders o
JOIN accounts a
ON o.account_id = a.id 
JOIN sales_reps s
ON s.id = a.sales_rep_id
GROUP BY s.name
ORDER BY 3 DESC;
           

通過以下的結果集可以看到，實作的是一個分級的效果

4、Subqueries

這一節主要是講的子查詢和臨時表，其中需要注意的是可以用WITH來書寫來避免多級的嵌套帶來的了解上的困難

例如下面這個語句就是将t1、t2作為臨時表

WITH t1 AS (
   SELECT r.name region_name, SUM(o.total_amt_usd) total_amt
   FROM sales_reps s
   JOIN accounts a
   ON a.sales_rep_id = s.id
   JOIN orders o
   ON o.account_id = a.id
   JOIN region r
   ON r.id = s.region_id
   GROUP BY r.name), 
t2 AS (
   SELECT MAX(total_amt)
   FROM t1)
SELECT r.name, COUNT(o.total) total_orders
FROM sales_reps s
JOIN accounts a
ON a.sales_rep_id = s.id
JOIN orders o
ON o.account_id = a.id
JOIN region r
ON r.id = s.region_id
GROUP BY r.name
HAVING SUM(o.total_amt_usd) = (SELECT * FROM t2);
           

（這麼多JOIN，其實我覺得視圖可能更簡單）

另外，子查詢中不要使用ORDER BY

5、Data Cleaning

在這一章介紹了幾個常見的函數

用幾個例子來記錄一哈

WITH t1 AS (
//STRPOS傳回數字
 SELECT LEFT(primary_poc, STRPOS(primary_poc, ' ') -1 ) first_name,  RIGHT(primary_poc, LENGTH(primary_poc) - STRPOS(primary_poc, ' ')) last_name, 
 name
 FROM accounts)
 
SELECT first_name, last_name, 
//REPLACE此處是去掉空格
CONCAT(first_name, '.', last_name, '@', REPLACE(name, ' ', ''), '.com')
FROM  t1;
           

SELECT date orig_date, 
//::DATE是說拼接之後的作為日期CAST(date_column AS DATE) 和 date_column::DATE是一樣的
(SUBSTR(date, 7, 4) || '-' || LEFT(date, 2) || '-' || SUBSTR(date, 4, 2))::DATE new_date
FROM sf_crime_data;
           

6、Windows Functions

不想寫了，想起來再補