數(shù)據(jù)庫原理與SQL基礎(chǔ)

實體關(guān)系圖

實體關(guān)系圖 (entity relationship diagram狞膘，ERD) 是查看數(shù)據(jù)庫中數(shù)據(jù)的常用方式挽封。下面是我們將用于 Parch & Posey （虛擬公司）數(shù)據(jù)庫的 ERD辅愿。這些圖可幫助你可視化正在分析的數(shù)據(jù)点待，包括:

1. 表的名稱癞埠。
2. 每個表中的列苗踪。
3. 表配合工作的方式通铲。

你可以將下面的每個框看作一個電子表格颅夺。

image

在 Parch & Posey 數(shù)據(jù)庫中吧黄，共有五個表（基本上是 5 個電子表格）:

1. web_events
2. accounts
3. orders
4. sales_reps
5. region

你可以將每個表視為一個單獨的電子表格稚字。然后將每個電子表格中的列放在表名下面胆描。例如昌讲，region 表有兩列: id 和 name短绸，而 web_events 表有四列醋闭。

image.png

將這些表格連接在一起的 "crow's foot" 表示法顯示了一個表中的列與另一個表中的列之間的關(guān)聯(lián)乐埠。在第一課中丈咐，你將學(xué)習(xí)使用 SQL 與單個表進行交互的基礎(chǔ)知識棵逊。在下一節(jié)課中辆影，你將進一步了解這些連接對于使用 SQL 和關(guān)系數(shù)據(jù)庫的重要性秸歧。

關(guān)于 SQL 數(shù)據(jù)庫中所存儲數(shù)據(jù)的幾個要點：

1. 數(shù)據(jù)庫中的數(shù)據(jù)存儲在類似于 Excel 電子表格的表中。
   大多數(shù)情況下今布，可以將數(shù)據(jù)庫視為一堆 Excel 電子表格部默。每個電子表格都有行和列傅蹂。每行保存有關(guān)交易份蝴、個人婚夫、公司等的數(shù)據(jù)梦抢。而每列所保存的數(shù)據(jù)與你關(guān)心的某一特定行相關(guān)时捌，如名稱奢讨、位置禽笑、唯一身份等佳镜。

2. 同一列中的所有數(shù)據(jù)必須符合數(shù)據(jù)類型蟀伸。
   將整個列認為是定量離散的或是某種字符串蚀同。這說明如果特定列中有一行字符串，那么整個列可能會更改為文本數(shù)據(jù)類型啊掏。 如果想使用此列進行數(shù)學(xué)計算蠢络，這可能會非常糟糕！

3. 列類型一致是快速使用數(shù)據(jù)庫的主要原因之一迟蜜。
   數(shù)據(jù)庫通常會存儲海量數(shù)據(jù)刹孔。因此，知道這些列都是相同類型的數(shù)據(jù)意味著可快速從數(shù)據(jù)庫獲取數(shù)據(jù)髓霞。

數(shù)據(jù)庫類型

SQL 數(shù)據(jù)庫

數(shù)據(jù)庫的類型有很多，都用于不同的用途畦戒。在本課中方库，我們將使用 Postgres，這是一個流行的開源數(shù)據(jù)庫障斋，具有非常完整的分析函數(shù)庫纵潦。

一些最受歡迎的數(shù)據(jù)庫包括：:

1. MySQL
2. Access
3. Oracle
4. Microsoft SQL Server
5. Postgres

你也可以在其他編程框架中編寫 SQL，如 Python垃环、Scala 和 HaDoop邀层。

細微差別

這些 SQL 數(shù)據(jù)庫中的每一個可能在語法和可用函數(shù)上存在細微差異 -- 例如，MySQL 中沒有像 Postgres 中類似的可用于修改日期的函數(shù)晴裹。你在 Postgres 中看到的大部分直接適用于在其他框架中和數(shù)據(jù)庫環(huán)境中使用 SQL被济。如需了解存在的差異，可查看文檔涧团。大多數(shù) SQL 環(huán)境都有很好的在線文檔只磷，通過快速的 Google 搜索便可輕松訪問经磅。

這里 的文章比較了三種最常見的 SQL 類型：SQLite、PostgreSQL 和 MySQL钮追。盡管你在Lesson中使用的是 PostgreSQL预厌，但在項目中你要使用 SQLite。

一旦你學(xué)會了如何在一個環(huán)境中編寫 SQL元媚，這些基本大都是可以轉(zhuǎn)移到其他環(huán)境的轧叽。

語句

SQL 的關(guān)鍵是理解語句。SQL 語句是可以讀取和處理數(shù)據(jù)的代碼刊棕。但這不是一個真正的句子炭晒。 SQL 不區(qū)分大小寫 - 這意味著可以在代碼中的任何位置寫入大寫和小寫。另外甥角，還可以使用 分號 結(jié)束 SQL 語句网严，但某些 SQL 環(huán)境結(jié)尾時不需要分號。

常用語句包括：

1. CREATE TABLE 是一個在數(shù)據(jù)庫中創(chuàng)建新表的語句嗤无。
2. DROP TABLE 是刪除數(shù)據(jù)庫中表的語句震束。
3. SELECT 讀取并顯示數(shù)據(jù)。我們將這稱為查詢当犯。

SELECT 語句是分析師使用的通用語句垢村，被稱為查詢。 DROP 和 CREATE 語句實際上會更改數(shù)據(jù)庫中的數(shù)據(jù)嚎卫。在大多數(shù)公司嘉栓，分析師沒有權(quán)限使用這些類型的語句。這是一個不錯的規(guī)定 - 原因是實際上更改數(shù)據(jù)庫中的數(shù)據(jù)是一件需要很大權(quán)限的事情驰凛。通常僅數(shù)據(jù)庫管理員具有這個權(quán)限胸懈。

SELECT 用于提醒查詢要返回哪些列。

FROM 用于提醒查詢在哪個表中查詢恰响。注意，這個表中需要有列涌献。

SQL 語句 SELECT 和 FROM

Parch & Posey 數(shù)據(jù)庫中的表存儲在下面方框的后臺胚宦。你會注意到 SCHEMA 圖表 下的左側(cè)列表（需要先點擊刷新按鈕）下面，是之前在 ERD 中顯示的表列表燕垃。 與在大多數(shù)其他數(shù)據(jù)庫環(huán)境中一樣編寫查詢并運行枢劝，然后使用下表查看結(jié)果。在這節(jié)課中卜壕，我們一次只能訪問一個表您旁，但在后面的課程中，我們將增加表的數(shù)量并在各個表之間進行聚合轴捎。

image.png

開始時鹤盒，可以試著運行上一個視頻中看到的查詢蚕脏！ 可以在下面的環(huán)境中練習(xí)你自己的 SQL 代碼。在左側(cè)面板可以看到我們之前在 ERD 中看到的表侦锯。在右側(cè)面板可以編寫 SQL 代碼驼鞭，可以單擊 EVALUATE 按鈕就可以運行查詢。這可能需要一會兒才能運行尺碰。

HISTORY 菜單將顯示你以前運行的查詢挣棕。 可以通過 MENU 從左側(cè)面板刪除 SCHEMA 和重置數(shù)據(jù)庫。

SELECT *
FROM orders;

你會注意到 Derek 使用一個 demo 表（他在以后的課程中還會繼續(xù)采用這種方式）亲桥，但是你應(yīng)該使用完全如左側(cè)面板所示的表名來編寫自己的查詢洛心。 我們將來還會在 Derek 所教授的課程中看到這些（刪除了 demo）表格。

image.png

編寫的每個查詢至少要有兩個部分：SELECT 和 FROM题篷。 SELECT 語句用于放置要顯示數(shù)據(jù)的列皂甘。FROM 語句用于放置要從中提取數(shù)據(jù)的表。

規(guī)定查詢格式

大寫

你可能已經(jīng)注意到悼凑，我們大寫了 SELECT 和 FROM偿枕，而將表和列名稱小寫。這是一個常見的格式慣例户辫。大寫命令（SELECT渐夸、FROM），小寫查詢中的其他內(nèi)容是常見做法渔欢。這使得查詢更容易讀取墓塌，這在編寫更復(fù)雜的查詢時更為重要。準(zhǔn)備編寫查詢時奥额，這是一個很好的習(xí)慣苫幢。

表和變量名中不需要空格

通常在列名中使用下劃線，避免使用空格垫挨。 在 SQL 中使用空格有點麻煩韩肝。 在 Postgres 中，如果列或表名稱中有空格九榔，就需要使用雙引號括住這些列/表名稱（例如：FROM "Table Name"哀峻，而不是 FROM table_name）。在其他環(huán)境中哲泊，可能會使用方括號（例如：FROM [Table Name]）剩蟀。

在查詢中使用空格

SQL 查詢忽略空格，因此可以根據(jù)需要在代碼之間添加盡可能多的空格和空行切威，并且查詢結(jié)果是相同的育特。我們來看下面這個查詢

SELECT account_id FROM orders

等價于這個查詢:

SELECT account_id
FROM orders

SQL 不區(qū)分大小寫

如果你已經(jīng)使用過其他語言編程，那么可能會熟悉編程語言先朦，如果沒有區(qū)分大小寫鍵入正確的字符缰冤，那么會非常麻煩犬缨。 SQL 不區(qū)分大小寫。 我們來看看下面的查詢：

SELECT account_id
FROM orders

和這個相同：

select account_id
from orders

也和這個相同：

SeLeCt AcCoUnt_id
FrOm oRdErS

但是锋谐，我會再次提醒你遵循上面講述的完全大寫命令的慣例遍尺，而將其他代碼片段小寫。

分號

根據(jù) SQL 環(huán)境涮拗，查詢結(jié)尾可能需要一個執(zhí)行的分號乾戏。 這個"要求"在其他環(huán)境中比較靈活。我們認為在每個語句的末尾添加一個分號是最好的做法三热，如果環(huán)境能夠一次顯示多個結(jié)果鼓择，那么這樣做還可以一次運行多個命令。

最好的做法：

SELECT account_id
FROM orders;

因為就漾，我們這里的環(huán)境不需要分號呐能，你會看到?jīng)]有分號的解決方案：

SELECT account_id
FROM orders

SQL 語句 LIMIT

如果想要查看表的前幾行時，LIMIT 語句就能派上用場抑堡。這可比加載整個數(shù)據(jù)集要快得多摆出。

LIMIT 命令始終是查詢的最后一部分。下面的例子僅顯示 orders 表的前 10 行和所有列首妖，OFFSET 表示從第幾行開始：

SELECT *
FROM orders
LIMIT 10 OFFSET 5;

SQL 語句 ORDER BY

ORDER BY 語句可使我們按任意行排序表偎漫。如果熟悉 Excel，這與使用過濾器進行排序相似有缆。

ORDER BY 語句始終在 SELECT 和 FROM 語句之后象踊，但位于 LIMIT 語句之前。 學(xué)習(xí)其他命令時棚壁，這些語句的順序?qū)⒏鼮橹匾?如果使用 LIMIT 語句杯矩，它將始終顯示在最后。

提示

記住可以在ORDER BY 語句中的列之后添加 DESC袖外，然后按降序排序史隆，因為默認是按升序排序的。

示例

1. 編寫查詢在刺，返回 orders 表的先下單的前 10 個訂單逆害。包含id、occurred_at 和 total_amt_usd蚣驼。

SELECT id, occurred_at, total_amt_usd
FROM orders
ORDER BY occurred_at
LIMIT 10;

2. 編寫一個查詢，返回 orders 表里total_amt_usd 最高的5個 訂單相艇。包括id颖杏、account_id 和 total_amt_usd。

SELECT id, account_id, total_amt_usd
FROM orders
ORDER BY total_amt_usd DESC 
LIMIT 5;

3. 編寫一個查詢坛芽，基于 total 留储，返回orders 表里的前 20 個 訂單翼抠。包括id、account_id 和total

SELECT id, account_id, total
FROM orders
ORDER BY total
LIMIT 20;

我們可以一次 ORDER BY 多列获讳。這個語句可以按照從左至右列出的列進行排序阴颖。 我們也可以也可以使用 DESC 來翻轉(zhuǎn)排序。

示例

1. 查詢 orders表格丐膝，按照訂單日期的從新到舊降序排列量愧，同時每個日期下的訂單按照total_amt_usd降序排列，顯示前5行帅矗。

SELECT *
FROM orders
ORDER BY occurred_at DESC, total_amt_usd DESC
LIMIT 5;

2. 查詢orders表格偎肃，按照訂單日期的從舊到新升序排列，同時每個日期下的訂單按照total_amt_usd升序排列浑此，顯示前10行累颂。

SELECT *
FROM orders
ORDER BY occurred_at, total_amt_usd
LIMIT 10;

SQL 語句 WHERE

我們可以基于必須滿足的條件，使用 WHERE 語句來為表格創(chuàng)建子集凛俱。下面的視頻介紹了如何使用這個語句紊馏，在接下來的課程中，我們將學(xué)習(xí)一些與 WHERE 語句一起使用的常見運算符蒲犬。
WHERE 語句在 FROM 后朱监，ORDER BY 和 LIMIT 前。
WHERE 語句中使用的常用符號包括：

1. >（大于）
2. <（小于）
3. >=（大于或等于）
4. <=（小于或等于）
5. =（等于）
6. !=（不等于）

示例

1. 從 orders 表提取出gloss_amt_usd大于或等于 1000 美元的前五行數(shù)據(jù)（包含所有列）暖哨。

SELECT *
FROM orders
WHERE gloss_amt_usd >= 1000
LIMIT 5;

2. 從 orders 表提取出total_amt_usd小于 500 美元的前十行數(shù)據(jù)（包含所有列）赌朋。

SELECT *
FROM orders
WHERE total_amt_usd < 500
LIMIT 10;

你會注意到我們在使用這些 WHERE 語句時，不需要 ORDER BY篇裁，除非要實際整理數(shù)據(jù)沛慢。不必對數(shù)據(jù)進行排序，仍可繼續(xù)執(zhí)行條件达布。

WHERE 語句也可以與非數(shù)字數(shù)據(jù)一起使用团甲。我們可以使用 = 和 ！= 運算符黍聂。 還需要確保在文本數(shù)據(jù)中使用單引號躺苦，而不是雙引號（如果原始文本中有引號，就一定要注意）产还。

通常將 WHERE 與非數(shù)字數(shù)據(jù)字段一起使用時匹厘，我們會使用LIKE、NOT 或 IN 運算符脐区。

LIKE 如果操作數(shù)匹配某個模式愈诚，則值為TRUE。
LIKE 通常與 %等通配符配合使用，例如LOVING%可以表示LOVING炕柔、LOVING you酌泰、LOVING？
NOT 對其他布爾運算符的值取反匕累。
IN 如果操作數(shù)與一個表達式列表中的某個相等陵刹，則值為TRUE。

當(dāng)NOT 欢嘿、 AND衰琐、OR 出現(xiàn)在同一表達式中，優(yōu)先級為NOT 际插、 AND碘耳、OR。
例如

3>5 OR 6>3 AND NOT 6>4 = FALSE

1. NOT 6>4 = FALSE
2. 6>3 AND FALSE = FALSE
3. 3>5 OR FALSE = FALSE

示例

1. 從 accounts 表格中篩選出 Exxon Mobil 的 name（客戶名稱）框弛，同時包含website 和 primary point of contact (primary_poc) 等數(shù)據(jù)辛辨。

SELECT name, website, primary_poc
FROM accounts
WHERE name = 'Exxon Mobil';

SQL 語句 GROUP BY 和 HAVING

GROUP BY 通常與聚合函數(shù)如SUM、AVE等一起使用瑟枫，每一組如“男”/“女” GROUP BY Sex 各產(chǎn)生一個平均值

HAVING 子句只能與 SELECT 一起使用斗搞，且通常在 GROUP BY子句中使用，語法如下：

HAVING <searching condition>

示例

SELECT 課程名慷妙， COUNT(課程名) AS 重復(fù)數(shù)量 
FROM course
GROUP BY 課程名
HAVING COUNT(課程名)>1
ORDER BY 課程名

顯示結(jié)果

其他提示

盡管 SQL 不區(qū)分大小寫（它不在乎你將語句全部大寫還是小寫）僻焚，但我們討論了一些最佳實踐。關(guān)鍵詞的順序非常重要膝擂！ :

SELECT col1, col2
FROM table1
WHERE col3  > 5 AND col4 LIKE '%os%'
ORDER BY col5
LIMIT 10;

注意虑啤，你可以檢索不同于 ORDER BY 和 WHERE 語句中所使用列的列。假定這些列名均以這樣的方式（col1架馋、col2狞山、col3、col4叉寂、col5）存在于一個名為 table1 的表中萍启，此查詢便會很好地運行。

后續(xù)內(nèi)容

在后續(xù)的 SQL 課程中屏鳍，你將學(xué)習(xí) JOIN（連接）勘纯，聚合，子查詢和臨時表格钓瞭。

JOIN 語句

Select query with INNER JOIN on multiple tables

SELECT column, another_table_column, …
FROM mytable
INNER JOIN another_table 
    ON mytable.id = another_table.id
WHERE condition(s)
ORDER BY column, … ASC/DESC
LIMIT num_limit OFFSET num_offset;

Select query with LEFT/RIGHT/FULL JOINs on multiple tables
SELECT column, another_column, …
FROM mytable
INNER/LEFT/RIGHT/FULL JOIN another_table 
    ON mytable.id = another_table.matching_id
WHERE condition(s)
ORDER BY column, … ASC/DESC
LIMIT num_limit OFFSET num_offset;

When joining table A to table B, a LEFT JOIN simply includes rows from A regardless of whether a matching row is found in B. The RIGHT JOIN is the same, but reversed, keeping rows in B regardless of whether a match is found in A. Finally, a FULL JOIN simply means that rows from both tables are kept, regardless of whether a matching row exists in the other table.

Select query with constraints on NULL values
SELECT column, another_column, …
FROM mytable
WHERE column IS/IS NOT NULL
  AND/OR another_condition
  AND/OR …;

正確的順序

Complete SELECT query
SELECT DISTINCT column, AGG_FUNC(column_or_expression), …
FROM mytable
    JOIN another_table
      ON mytable.column = another_table.column
    WHERE constraint_expression
    GROUP BY column
    HAVING constraint_expression
    ORDER BY column ASC/DESC
    LIMIT count OFFSET COUNT;

INSERT INTO 語句

Insert statement with values for all columns

INSERT INTO mytable
(column, another_column, …)
VALUES (value_or_expr, another_value_or_expr, …),
       (value_or_expr_2, another_value_or_expr_2, …),
       …;

UPDATE 語句

用來更新 table 中的數(shù)據(jù)
Update statement with values

UPDATE mytable
SET column = value_or_expr, 
    other_column = another_value_or_expr, 
    …
WHERE condition;

一般 condition 里對行進行約束

刪除行

Delete statement with condition

DELETE FROM mytable
WHERE condition;

創(chuàng)建表格

CREATE TABLE IF NOT EXISTS mytable (
    column DataType TableConstraint DEFAULT default_value,
    another_column DataType TableConstraint DEFAULT default_value,
    …
);

Table data types

概括

命令