Open in app

Sign in

Write

Sign in

【CS50 學習筆記】week7 — SQL

Chengcheng Hsu
17 min readDec 27, 2023

--

Flat-File Database

Flat-File Database 就是一個以簡單表格形式來呈現的資料表，如 CSV 檔案，以純文字以及逗點來紀錄每一筆資料，使資料易於理解但可能缺少複雜資料庫系統的某些功能。

我們可以嘗試來用 python 打開 csv 檔，課程中的 favorites.csv 檔如下方的，是語言以及問題名稱，可以從 https://cdn.cs50.net/2021/fall/lectures/7/src7/favorites/ 這裡來下載

Timestamp,language,problem
10/24/2022 8:33:26 ,C ,Credit
10/24/2022 10:32:26 ,Python ,Runoff
10/24/2022 11:10:47 ,Python ,Mario
10/24/2022 11:22:35 ,Python ,Scratch
10/24/2022 11:39:06 ,Python ,Readability
10/24/2022 11:53:00 ,Scratch ,Scratch
10/24/2022 13:26:23 ,C ,Bulbs
...

我們想要印出每一列的 language ，一開始會這樣寫，透過 csv 這個套件提供的 reader 來打開檔案並存到 reader 這個變數，接著透過 next 這個語法來跳過 Timestamp,language,problem 這一列，對 reader 這個變數進行迴圈，row 會像是這樣的陣列 [‘10/24/2022 8:33:26’, ‘C’, ‘Credit’]，row[1] 則是印出所有的 language。

# Prints all favorites in CSV using csv.reader

import csv

# Open CSV file
with open("favorites.csv", "r") as file:

    # Create reader
    reader = csv.reader(file)

    # Skip header row
    next(reader)

    # Iterate over CSV file, printing each favorite    
    for row in reader:
        print(row[1])

然而今天因為檔案的 header 並沒有那麼多行，我們可以清楚的數出 language 是第幾行，但若我們想要指定印出的 key 時可以透過 DictReader 來讀取檔案，這時 row 就會像是 {‘Timestamp’: ‘10/24/2022 8:33:26’, ‘language’: ‘C’, ‘genres’: ‘Credit’} 這樣的物件，因此可以輕易的透過 row[“language”] 來印出每一列的 language，而不用去數。

# Prints all favorites in CSV using csv.DictReader

import csv

# Open CSV file
with open("favorites.csv", "r") as file:

    # Create DictReader
    reader = csv.DictReader(file)

    # Iterate over CSV file, printing each favorite
    for row in reader:
        print(row["language"])

如果我們想要數 csv 中有多少筆 Mario

# Counts favorites using variables

import csv

# Open CSV file
with open("favorites.csv", "r") as file:

    # Create DictReader
    reader = csv.DictReader(file)

    # Counts
    mario = 0

    # Iterate over CSV file, counting favorites
    for row in reader:
        favorite = row["language"]
        if favorite == "Mario":
            mario += 1

# Print mario
print(f"Mario: {mario}")

但今天若想統計所有問題分別有多少筆的話可以透過 counts 的陣列來統計，如果該問題有在該陣列裡，則加一，如果沒有的話就讓該問題的值為一。

# Counts favorites using dictionary

import csv

# Open CSV file
with open("favorites.csv", "r") as file:

    # Create DictReader
    reader = csv.DictReader(file)

    # Counts
    counts = {}

    # Iterate over CSV file, counting favorites
    for row in reader:
        favorite = row["language"]
        if favorite in counts:
            counts[favorite] += 1
        else:
            counts[favorite] = 1

# Print counts
for favorite in counts:
    print(f"{favorite}: {counts[favorite]}")

如果要排序問題的順序，從 A 列到 Z 可以透過 sorted 的函式來改寫

# Print counts
for favorite in sorted(counts):
    print(f"{favorite}: {counts[favorite]}")

如果要排序影集數量的話會需要給訂一個 key，告訴 python 說哪個項目是你要排序的，最後用 reverse 來控制從小排到大還是相反

def get_value(title):
    return counts[title]

# Print counts
for favorite in sorted(counts, key=get_value, reverse=True):
    print(f"{favorite}: {counts[favorite]}")

或是透過 python 給予的 lambda 來改寫，這樣省去定義函式想命名的過程


# Print counts
for favorite in sorted(counts, key=lambda title: counts[title], reverse=True):
    print(f"{favorite}: {counts[favorite]}")

Relational Databases

Google、Twitter 和 Meta 這些公司都是透過關聯式資料庫來大規模的儲存資料，關聯式資料庫就像是 csv 有行列來組成一整張（table）

有幾個步驟可以對剛剛的 csv 來操作

  1. 打上 sqlite3 favorites.db 來產生一個資料庫
  2. .schema 來確認欄位
  3. .mode csv 來更改成 csv 模式
  4. .import favorites.csv favorites 引入 csv
  5. SELECT * FROM favorites; 印出所有的行列，也就是所有的資料
  6. 或是指定某個欄位 SELECT [colume] FROM favorites;

另外 SQL 也提供了一些工具來做使用

AVG --計算一組數值的平均值。
COUNT --計算資料行的數量，或符合特定條件的資料行數。
DISTINCT --用於檢索唯一的值，刪除重複的值，使每個值只顯示一次。
LOWER --把文字轉換成小寫。
MAX --找出一組數值中的最大值。
MIN --找出一組數值中的最小值。
UPPER --把文字轉換成大寫。
WHERE --用於在查詢中添加布林表達式，以過濾資料。
LIKE --用於比較鬆散地過濾回應，通常與模式匹配一起使用。
ORDER BY --用於對回應進行排序，可以指定升序（ASC）或降序（DESC）。
LIMIT --用於限制回應的數量，僅檢索指定數量的結果。
GROUP BY --用於將回應分組在一起，通常與聚合函數（如SUM、AVG）一起使用。

最主要的還是要基於 SQL 提供的一些基礎語法來對 table 來做增刪改查，也就是以下 CURD 的範例

Create:

INSERT INTO table (column…) VALUES(value, …);

Update:

UPDATE table SET language = ‘A’ WHERE language = ‘B’;

Read:

SELECT * FROM favorites WHERE language = ‘C’ AND problem = ‘Mario’;

Delete:

DELETE FROM favorites WHERE problem = ‘A’;

IMDb

IMDb 提供了以下各個資料表用於存儲包含影集名稱、人、作者以及評價等等，一樣可以從這裡下載。

透過以上可以發現 shows 的表格有自己的 id，會稱為主鍵（Primary keys），而其他表格有 show_id 的會被稱為外鍵（Foreign keys）

將資料透過這種關聯式資料庫來儲存將會更有效率的被提取使用

而每一個項目如 id、title 等等都有屬於他的格式

BLOB       -- binary large objects that are groups of ones and zeros
INTEGER    -- an integer
NUMERIC    -- for numbers that are formatted specially like dates
REAL       -- like a float
TEXT       -- for strings and the like

也可以將其做一些限制，如是否可以是 null，或是表中的唯一值， id 就一定要是 UNIQUE

NOT NULL
UNIQUE

下載完剛剛的 db 後來執行 sqlite3 shows.db 和 .schema ，可以看到剛剛提到的格式

sqlite> .schema
CREATE TABLE genres (
    show_id INTEGER NOT NULL,
    genre TEXT NOT NULL,
    FOREIGN KEY(show_id) REFERENCES shows(id)
);
CREATE TABLE people (
    id INTEGER,
    name TEXT NOT NULL,
    birth NUMERIC,
    PRIMARY KEY(id)
);
CREATE TABLE ratings (
    show_id INTEGER NOT NULL,
    rating REAL NOT NULL,
    votes INTEGER NOT NULL,
    FOREIGN KEY(show_id) REFERENCES shows(id)
);
CREATE TABLE shows (
    id INTEGER,
    title TEXT NOT NULL,
    year NUMERIC,
    episodes INTEGER,
    PRIMARY KEY(id)
);
CREATE TABLE stars (
    show_id INTEGER NOT NULL,
    person_id INTEGER NOT NULL,
    FOREIGN KEY(show_id) REFERENCES shows(id),
    FOREIGN KEY(person_id) REFERENCES people(id)
);
CREATE TABLE writers (
    show_id INTEGER NOT NULL,
    person_id INTEGER NOT NULL,
    FOREIGN KEY(show_id) REFERENCES shows(id),
    FOREIGN KEY(person_id) REFERENCES people(id)
);

透過以下來列出 genres 資料表中所有 genre 是 Comedy 的 show_id

SELECT show_id FROM genres WHERE genre = 'Comedy';

接著可以透過合併的方式來查詢 shows 資料表中有 Comedy 的劇名

SELECT title
FROM shows
WHERE id IN (
    SELECT show_id
    FROM genres
    WHERE genre = 'Comedy'
)
LIMIT 10;

而我可以透過以下方式去找到 Steve Carell 演的戲，這樣稱作巢狀查詢（nested queries）的概念

SELECT title FROM shows WHERE id IN
 (SELECT show_id FROM stars WHERE person_id =
  (SELECT id FROM people WHERE name = 'Steve Carell')
 );

JOINs

如果我們想暫時的合併兩個資料表並呈現資料，則可以用 JOIN

SELECT * FROM shows
  JOIN ratings on shows.id = ratings.show_id
  WHERE title = 'The Office';

呈現結果：

所有稱作 ‘The Office’ 的劇，並帶上該劇的 rating

+---------+------------+------+----------+---------+--------+--------+
|   id    |   title    | year | episodes | show_id | rating | votes  |
+---------+------------+------+----------+---------+--------+--------+
| 112108  | The Office | 1995 | 6        | 112108  | 7.5    | 45     |
| 290978  | The Office | 2001 | 14       | 290978  | 8.5    | 112944 |
| 386676  | The Office | 2005 | 188      | 386676  | 9.0    | 585206 |
| 1791001 | The Office | 2010 | 30       | 1791001 | 4.7    | 56     |
| 2186395 | The Office | 2012 | 8        | 2186395 | 6.0    | 12     |
| 8305218 | The Office | 2019 | 28       | 8305218 | 5.7    | 5718   |
+---------+------------+------+----------+---------+--------+--------+

就剛剛提到用 nested queries 去找到 Steve Carell 演的戲，在這裡也能用 JOIN 來寫，就算 people 這個資料表沒有 title，但透過合併資料表就能形成一個包含 title 的大資料表，因此可以透過這樣來搜尋

SELECT title FROM people
  JOIN stars ON people.id = stars.person_id
  JOIN shows ON stars.show_id = shows.id
  WHERE name = 'Steve Carell';

或是這樣寫也是達到同樣的效果

SELECT title FROM people, stars, shows
WHERE people.id = stars.person_id
AND stars.show_id = shows.id
AND name = 'Steve Carell';

% 則可以用來找尋名稱是 Steve C 開頭的名字

SELECT * FROM people WHERE name LIKE 'Steve C%';

Indexes

透過執行 .timer on 可以來記錄時間，然後先執行以下會發現是 0.033 秒

sqlite> .timer on
sqlite> SELECT * FROM shows WHERE title = 'The Office';
+----------+------------+------+----------+
|    id    |   title    | year | episodes |
+----------+------------+------+----------+
| 112108   | The Office | 1995 | 6        |
| 290978   | The Office | 2001 | 14       |
| 386676   | The Office | 2005 | 188      |
| 1791001  | The Office | 2010 | 30       |
| 2186395  | The Office | 2012 | 8        |
| 8305218  | The Office | 2019 | 28       |
| 20877972 | The Office | 2022 | 20       |
+----------+------------+------+----------+
Run Time: real 0.033 user 0.028042 sys 0.004235

但如果我們為這張表加上 index 後再執行一次則會發現會花更少的時間，這是因為在底層建構了一個叫做 B Tree 的數據結構，但是這樣的方式會消耗更多的存儲空間，因此這是種權衡。

sqlite> CREATE INDEX title_index on shows (title);
Run Time: real 0.368 user 0.189052 sys 0.050368
sqlite> SELECT * FROM shows WHERE title = 'The Office';
+----------+------------+------+----------+
|    id    |   title    | year | episodes |
+----------+------------+------+----------+
| 112108   | The Office | 1995 | 6        |
| 290978   | The Office | 2001 | 14       |
| 386676   | The Office | 2005 | 188      |
| 1791001  | The Office | 2010 | 30       |
| 2186395  | The Office | 2012 | 8        |
| 8305218  | The Office | 2019 | 28       |
| 20877972 | The Office | 2022 | 20       |
+----------+------------+------+----------+
Run Time: real 0.000 user 0.000202 sys 0.000159

Using SQL in Python

回到最一開始 python 在 csv 找尋某個 problem 人喜歡的問題，就可以在 python 使用 SQL

# Searches database popularity of a problem

import csv

from cs50 import SQL

# Open database
db = SQL("sqlite:///favorites.db")

# Prompt user for favorite
favorite = input("Favorite: ")

# Search for title
rows = db.execute("SELECT COUNT(*) FROM favorites WHERE problem LIKE ?", "%" + favorite + "%")

# Get first (and only) row
row = rows[0]

# Print popularity
print(row["COUNT(*)"])

這邊順便提到我們不會知道使用者輸入了什麼 favorite，因此它可能會造成 SQL Injection Attacks ，因此使用 ? 的佔位符是很重要的

Race Conditions

在使用 SQL 時，有時可能會遇到競爭條件（Race Conditions）的問題。舉例來說，當多個使用者同時訪問同一個資料庫（例如，一個搶票系統），並且他們同時執行命令時，可能會出現一些問題。

這種情況可能導致程式碼被其他人的操作中斷，進而導致數據損失。為了避免這些競爭條件的問題，內建的 SQL 功能，如 BEGIN TRANSACTION、COMMIT 和 ROLLBACK。這些功能可以協助確保在執行複雜的 SQL 操作時，數據庫的一致性和完整性得到維護，從而減少競爭條件可能引起的問題。

Cs50

--

--

Chengcheng Hsu

Written by Chengcheng Hsu

50 Followers

Frontend developer | 前端新手村村長 | contact me: ychsu.wk@gmail.com

Help

Status

About

Careers

Press

Blog

Privacy

Terms

Text to speech

Teams