Week 7 SQL

Chào mừng các bạn!

  • Trong những tuần trước, chúng tôi đã giới thiệu tới các bạn Python, một ngôn ngữ lập trình cấp cao sử dụng các khối xây dựng tương tự như những gì chúng ta đã học trong C. Tuy nhiên, chúng tôi giới thiệu ngôn ngữ mới này không chỉ để học “thêm một ngôn ngữ khác”. Thay vào đó, chúng tôi làm vậy vì một số công cụ sẽ phù hợp hơn cho một vài công việc cụ thể và không quá tốt cho những công việc khác!

  • Tuần này, chúng ta sẽ tiếp tục tìm hiểu thêm các cú pháp liên quan đến Python.

  • Hơn nữa, chúng ta sẽ tích hợp kiến thức này với dữ liệu.

  • Cuối cùng, chúng ta sẽ thảo luận về SQL hay Structured Query Language (Ngôn ngữ Truy vấn Có cấu trúc), một cách thức chuyên biệt để chúng ta có thể tương tác và sửa đổi dữ liệu.

  • Nhìn chung, một trong những mục tiêu của khóa học này là học lập trình một cách tổng quát – không chỉ đơn thuần là cách lập trình trong các ngôn ngữ được mô tả trong khóa học này.

Cơ sở dữ liệu tệp phẳng (Flat-File Database)

  • Như các bạn có thể đã thấy trước đây, dữ liệu thường được mô tả theo các mẫu cột và hàng.

  • Các bảng tính như những bảng được tạo trong Microsoft Excel và Google Sheets có thể được xuất ra tệp csv hay comma-separated values (các giá trị phân tách bằng dấu phẩy).

  • Nếu bạn nhìn vào một tệp csv, bạn sẽ nhận thấy rằng tệp này là “phẳng” ở chỗ tất cả dữ liệu của chúng ta được lưu trữ trong một bảng duy nhất được biểu diễn bằng một tệp văn bản. Chúng ta gọi dạng dữ liệu này là cơ sở dữ liệu tệp phẳng.

  • Tất cả dữ liệu được lưu trữ theo từng hàng. Mỗi cột được phân tách bằng dấu phẩy hoặc một giá trị khác.

  • Python đi kèm với sự hỗ trợ mặc định cho các tệp csv.

  • Đầu tiên, hãy tải xuống favorites.csv và tải nó lên trình khám phá tệp bên trong cs50.dev. Thứ hai, kiểm tra dữ liệu này, hãy lưu ý rằng hàng đầu tiên rất đặc biệt vì nó xác định từng cột. Sau đó, mỗi bản ghi được lưu trữ theo từng hàng.

Trong cửa sổ terminal của bạn, gõ code favorites.py và viết mã như sau:

# 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])

Lưu ý rằng thư viện csv đã được nhập vào. Hơn nữa, chúng ta đã tạo một reader sẽ giữ kết quả của csv.reader(file). Hàm csv.reader đọc từng hàng từ tệp và trong mã của chúng ta, chúng ta lưu trữ kết quả trong reader. print(row[1]), do đó, sẽ in ra ngôn ngữ từ tệp favorites.csv.

Bạn có thể cải thiện mã của mình như sau:

# Stores favorite in a variable

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:
        favorite = row[1]
        print(favorite)

Lưu ý rằng favorite được lưu trữ và sau đó được in ra. Ngoài ra, hãy lưu ý rằng chúng ta sử dụng hàm next để bỏ qua hàng tiếp theo của reader.

  • Một trong những nhược điểm của cách tiếp cận trên là chúng ta đang tin tưởng rằng row[1] luôn là ngôn ngữ yêu thích. Tuy nhiên, điều gì sẽ xảy ra nếu các cột bị xáo trộn vị trí?

Chúng ta có thể khắc phục vấn đề tiềm ẩn này. Python cũng cho phép bạn lập chỉ mục bằng các khóa (keys) của một danh sách. Sửa đổi mã của bạn như sau:

# 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:
        favorite = row["language"]
        print(favorite)

Lưu ý rằng ví dụ này trực tiếp sử dụng khóa language trong câu lệnh in. favorite lập chỉ mục vào từ điển reader của row["language"].

Điều này có thể được đơn giản hóa hơn nữa thành:

# 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"])

Để đếm số lượng ngôn ngữ yêu thích được thể hiện trong tệp csv, chúng ta có thể làm như sau:

# Counts favorites using variables

import csv

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

    # Create DictReader
    reader = csv.DictReader(file)

    # Counts
    scratch, c, python = 0, 0, 0

    # Iterate over CSV file, counting favorites
    for row in reader:
        favorite = row["language"]
        if favorite == "Scratch":
            scratch += 1
        elif favorite == "C":
            c += 1
        elif favorite == "Python":
            python += 1

# Print counts
print(f"Scratch: {scratch}")
print(f"C: {c}")
print(f"Python: {python}")

Lưu ý rằng mỗi ngôn ngữ được đếm bằng cách sử dụng các câu lệnh if. Hơn nữa, hãy lưu ý các dấu bằng kép == trong các câu lệnh if đó.

Python cho phép chúng ta sử dụng một từ điển để đếm số lượng (counts) của mỗi ngôn ngữ. Hãy xem xét cải tiến sau cho mã của chúng ta:

# 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]}")

Lưu ý rằng giá trị trong counts với khóa favorite sẽ được tăng lên nếu nó đã tồn tại. Nếu nó chưa tồn tại, chúng ta định nghĩa counts[favorite] và đặt nó bằng 1. Hơn nữa, chuỗi định dạng (f-string) đã được cải tiến để trình bày counts[favorite].

Python cũng cho phép sắp xếp counts. Hãy cải thiện mã của bạn như sau:

# Sorts favorites by key

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 sorted(counts):
    print(f"{favorite}: {counts[favorite]}")

Lưu ý hàm sorted(counts) ở cuối mã.

Nếu bạn nhìn vào các tham số cho hàm sorted trong tài liệu Python, bạn sẽ thấy nó có nhiều tham số tích hợp sẵn. Bạn có thể tận dụng một số tham số tích hợp này như sau:

# Sorts favorites by value using .get

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 sorted(counts, key=counts.get, reverse=True):
    print(f"{favorite}: {counts[favorite]}")

Lưu ý các đối số được truyền vào sorted. Đối số key cho phép bạn chỉ định cho Python phương thức bạn muốn sử dụng để sắp xếp các mục. Trong trường hợp này, counts.get được sử dụng để sắp xếp theo các giá trị. reverse=True yêu cầu sorted sắp xếp từ lớn nhất đến nhỏ nhất.

Python có vô số thư viện mà chúng ta có thể sử dụng trong mã của mình. Một trong những thư viện này là collections, từ đó chúng ta có thể nhập Counter. Counter sẽ cho phép bạn truy cập số lượng của mỗi ngôn ngữ mà không gặp rắc rối với tất cả các câu lệnh if như trong mã trước đó của chúng ta. Bạn có thể triển khai như sau:

# Sorts favorites by value using .get

import csv

from collections import Counter

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

    # Create DictReader
    reader = csv.DictReader(file)

    # Counts
    counts = Counter()

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

# Print counts
for favorite, count in counts.most_common():
    print(f"{favorite}: {count}")

Lưu ý cách counts = Counter() cho phép sử dụng lớp Counter được nhập từ collections.

Cơ sở dữ liệu quan hệ (Relational Databases)

  • Google, X và Meta đều sử dụng cơ sở dữ liệu quan hệ để lưu trữ thông tin của họ ở quy mô lớn.

  • Cơ sở dữ liệu quan hệ lưu trữ dữ liệu theo hàng và cột trong các cấu trúc gọi là bảng (tables).

SQL cho phép thực hiện bốn loại lệnh:

  Create  (Tạo mới)
  Read    (Đọc)
  Update  (Cập nhật)
  Delete  (Xóa)

  • Bốn thao tác này được gọi một cách thân mật là CRUD.

  • Chúng ta có thể tạo một cơ sở dữ liệu với cú pháp SQL CREATE TABLE table (column type, ...);. Nhưng bạn chạy lệnh này ở đâu?

sqlite3 là một loại cơ sở dữ liệu SQL có các tính năng cốt lõi cần thiết cho khóa học này.

  • Chúng ta có thể tạo một cơ sở dữ liệu SQL tại terminal bằng cách gõ sqlite3 favorites.db. Khi được nhắc, chúng ta sẽ đồng ý tạo favorites.db bằng cách nhấn y.

  • Bạn sẽ nhận thấy một dấu nhắc khác vì hiện tại chúng ta đang sử dụng một chương trình có tên là sqlite.

  • Chúng ta có thể đặt sqlite vào chế độ csv bằng cách gõ .mode csv. Sau đó, chúng ta có thể nhập dữ liệu từ tệp csv của mình bằng cách gõ .import favorites.csv favorites. Có vẻ như không có gì xảy ra cả!

  • Chúng ta có thể gõ .schema để xem cấu trúc của cơ sở dữ liệu.

  • Bạn có thể đọc các mục từ một bảng bằng cú pháp SELECT columns FROM table.

  • Ví dụ: bạn có thể gõ SELECT * FROM favorites;, lệnh này sẽ in ra mọi hàng trong favorites.

  • Bạn có thể lấy một tập hợp con của dữ liệu bằng lệnh SELECT language FROM favorites;.

SQL hỗ trợ nhiều lệnh để truy cập dữ liệu, bao gồm:

  AVG      (Trung bình)
  COUNT    (Đếm)
  DISTINCT (Duy nhất)
  LOWER    (Chữ thường)
  MAX      (Lớn nhất)
  MIN      (Nhỏ nhất)
  UPPER    (Chữ hoa)
  • Ví dụ: bạn có thể gõ SELECT COUNT(*) FROM favorites;. Hơn nữa, bạn có thể gõ SELECT DISTINCT language FROM favorites; để lấy danh sách các ngôn ngữ riêng lẻ trong cơ sở dữ liệu. Bạn thậm chí có thể gõ SELECT COUNT(DISTINCT language) FROM favorites; để đếm số lượng ngôn ngữ đó.

SQL cung cấp các lệnh bổ sung mà chúng ta có thể sử dụng trong các truy vấn của mình:

  WHERE       -- thêm một biểu thức Boolean để lọc dữ liệu của chúng ta
  LIKE        -- lọc các phản hồi một cách linh hoạt hơn
  ORDER BY    -- sắp xếp các phản hồi
  LIMIT       -- giới hạn số lượng phản hồi
  GROUP BY    -- nhóm các phản hồi lại với nhau

Lưu ý rằng chúng ta sử dụng -- để viết bình luận trong SQL.

SELECT

  • Ví dụ: chúng ta có thể thực thi SELECT COUNT(*) FROM favorites WHERE language = 'C';. Một con số đếm sẽ được trình bày.

  • Hơn nữa, chúng ta có thể gõ SELECT COUNT(*) FROM favorites WHERE language = 'C' AND problem = 'Hello, World';. Lưu ý cách AND được sử dụng để thu hẹp kết quả của chúng ta.

  • Tương tự, chúng ta có thể thực thi SELECT language, COUNT(*) FROM favorites GROUP BY language;. Điều này sẽ cung cấp một bảng tạm thời hiển thị ngôn ngữ và số lượng tương ứng.

  • Chúng ta có thể cải thiện điều này bằng cách gõ SELECT language, COUNT(*) FROM favorites GROUP BY language ORDER BY COUNT(*);. Lệnh này sẽ sắp xếp bảng kết quả theo count.

  • Tương tự, chúng ta có thể thực thi SELECT COUNT(*) FROM favorites WHERE language = 'C' AND (problem = 'Hello, World' OR problem = 'Hello, It''s Me');. Lưu ý rằng có hai dấu '' để cho phép sử dụng dấu nháy đơn mà không làm SQL bị nhầm lẫn.

  • Hơn nữa, chúng ta có thể thực thi SELECT COUNT(*) FROM favorites WHERE language = 'C' AND problem LIKE 'Hello, %'; để tìm bất kỳ bài toán nào bắt đầu bằng Hello, (bao gồm cả dấu cách).

  • Chúng ta cũng có thể nhóm các giá trị của mỗi ngôn ngữ bằng cách thực thi SELECT language, COUNT(*) FROM favorites GROUP BY language;.

  • Chúng ta có thể sắp xếp đầu ra như sau: SELECT language, COUNT(*) FROM favorites GROUP BY language ORDER BY COUNT(*) DESC;.

  • Chúng ta thậm chí có thể tạo các bí danh (aliases), giống như các biến trong các truy vấn của mình: SELECT language, COUNT(*) AS n FROM favorites GROUP BY language ORDER BY n DESC;.

  • Cuối cùng, chúng ta có thể giới hạn đầu ra ở 1 hoặc nhiều giá trị: SELECT language, COUNT(*) AS n FROM favorites GROUP BY language ORDER BY n DESC LIMIT 1;.

INSERT

  • Chúng ta cũng có thể INSERT (chèn) vào cơ sở dữ liệu SQL bằng cách sử dụng dạng INSERT INTO table (column...) VALUES(value, ...);.

  • Chúng ta có thể thực thi INSERT INTO favorites (language, problem) VALUES ('SQL', 'Fiftyville');.

  • Bạn có thể xác minh việc thêm ngôn ngữ yêu thích này bằng cách thực thi SELECT * FROM favorites;.

DELETE

DELETE cho phép bạn xóa các phần dữ liệu của mình. Ví dụ: bạn có thể DELETE FROM favorites WHERE Timestamp IS NULL;. Lệnh này xóa bất kỳ bản ghi nào có Timestamp là NULL.

UPDATE

  • Chúng ta cũng có thể sử dụng lệnh UPDATE để cập nhật dữ liệu của mình.

  • Ví dụ: bạn có thể thực thi UPDATE favorites SET language = 'SQL', problem = 'Fiftyville';. Điều này sẽ dẫn đến việc ghi đè lên tất cả các câu lệnh trước đó nơi C và Scratch là ngôn ngữ lập trình yêu thích.

  • Lưu ý rằng những truy vấn này có sức mạnh rất lớn. Do đó, trong môi trường thực tế, bạn nên xem xét ai có quyền thực thi một số lệnh nhất định và liệu bạn có sẵn các bản sao lưu hay không!

IMDb

  • Chúng ta có thể hình dung một cơ sở dữ liệu mà chúng ta muốn tạo để liệt kê các chương trình truyền hình khác nhau. Chúng ta có thể tạo một bảng tính với các cột như title, star, star, star, star và nhiều ngôi sao khác. Một vấn đề với cách tiếp cận này là nó gây lãng phí rất nhiều không gian. Một số chương trình có thể chỉ có một ngôi sao, trong khi những chương trình khác có thể có hàng chục ngôi sao.

  • Chúng ta có thể tách cơ sở dữ liệu của mình thành nhiều trang tính (sheets). Chúng ta có thể có một bảng shows, một bảng stars và một bảng people. Trong bảng people, mỗi người có thể có một id duy nhất. Trong bảng shows, mỗi chương trình cũng có thể có một id duy nhất. Trên bảng thứ ba có tên stars, chúng ta có thể liên kết cách mỗi chương trình có những người tham gia bằng cách có show_id và person_id. Mặc dù đây là một cải tiến, nhưng đây vẫn chưa phải là một cơ sở dữ liệu lý tưởng.

IMDb cung cấp một cơ sở dữ liệu về con người, chương trình, biên kịch, diễn viên, thể loại và xếp hạng. Mỗi bảng này liên quan đến nhau như sau:

  • Sau khi tải xuống shows.db, bạn có thể thực thi sqlite3 shows.db trong cửa sổ terminal của mình.

Hãy tập trung vào mối quan hệ giữa hai bảng trong cơ sở dữ liệu có tên là shows và ratings. Mối quan hệ giữa hai bảng này có thể được minh họa như sau:

  • Để minh họa mối quan hệ giữa các bảng này, chúng ta có thể thực thi lệnh sau: SELECT * FROM ratings LIMIT 10;. Kiểm tra đầu ra, sau đó chúng ta có thể thực thi SELECT * FROM shows LIMIT 10;.

  • Kiểm tra shows và rating, chúng ta có thể thấy chúng có mối quan hệ một-một: Một chương trình có một mức xếp hạng.

  • Để hiểu về cơ sở dữ liệu, khi thực thi .schema, bạn sẽ không chỉ thấy từng bảng mà còn thấy các trường riêng lẻ bên trong mỗi trường này.

  • Cụ thể hơn, bạn có thể thực thi .schema shows để hiểu các trường bên trong shows. Bạn cũng có thể thực thi .schema ratings để xem các trường bên trong ratings.

  • Như bạn có thể thấy, show_id tồn tại trong tất cả các bảng. Trong bảng shows, nó chỉ đơn giản được gọi là id. Trường chung này giữa tất cả các trường được gọi là một khóa (key). Khóa chính (Primary keys) được sử dụng để xác định một bản ghi duy nhất trong một bảng. Khóa ngoại (Foreign keys) được sử dụng để xây dựng mối quan hệ giữa các bảng bằng cách trỏ đến khóa chính trong một bảng khác. Bạn có thể thấy trong lược đồ của ratings rằng show_id là một khóa ngoại tham chiếu đến id trong shows.

  • Bằng cách lưu trữ dữ liệu trong một cơ sở dữ liệu quan hệ như trên, dữ liệu có thể được lưu trữ hiệu quả hơn.

Trong sqlite, chúng ta có năm kiểu dữ liệu, bao gồm:

  BLOB       -- các đối tượng lớn nhị phân (binary large objects) là các nhóm số một và số không
  INTEGER    -- một số nguyên
  NUMERIC    -- cho các con số được định dạng đặc biệt như ngày tháng
  REAL       -- giống như một số thực (float)
  TEXT       -- cho các chuỗi ký tự và những thứ tương tự

Ngoài ra, các cột có thể được thiết lập để thêm các ràng buộc đặc biệt:

  NOT NULL
  UNIQUE

  • Chúng ta có thể tiếp tục thao tác với dữ liệu này để hiểu các mối quan hệ này. Thực thi SELECT * FROM ratings;. Có rất nhiều xếp hạng!

  • Chúng ta có thể giới hạn thêm dữ liệu này bằng cách thực thi SELECT show_id FROM ratings WHERE rating >= 6.0 LIMIT 10;. Từ truy vấn này, bạn có thể thấy có 10 chương trình được trình bày. Tuy nhiên, chúng ta không biết mỗi show_id đại diện cho chương trình nào.

  • Bạn có thể khám phá xem đó là những chương trình nào bằng cách thực thi SELECT * FROM shows WHERE id = 626124;

Chúng ta có thể thực hiện truy vấn hiệu quả hơn bằng cách thực thi:

SELECT title
FROM shows
WHERE id IN (
    SELECT show_id
    FROM ratings
    WHERE rating >= 6.0
    LIMIT 10
)

Lưu ý rằng truy vấn này lồng hai truy vấn lại với nhau. Một truy vấn bên trong được sử dụng bởi một truy vấn bên ngoài.

JOINs

  • Chúng ta đang lấy dữ liệu từ shows và ratings. Lưu ý rằng cả shows và ratings đều có chung trường id.

  • Làm thế nào chúng ta có thể kết hợp các bảng một cách tạm thời? Các bảng có thể được kết hợp với nhau bằng lệnh JOIN.

Thực thi lệnh sau:

SELECT * FROM shows
  JOIN ratings on shows.id = ratings.show_id
  WHERE rating >= 6.0
  LIMIT 10;

Lưu ý điều này tạo ra một bảng rộng hơn so với những gì chúng ta đã thấy trước đây.

Trong khi các truy vấn trước đó minh họa mối quan hệ một-một giữa các khóa này, hãy cùng kiểm tra một số mối quan hệ một-nhiều. Tập trung vào bảng genres, hãy thực thi lệnh sau:

SELECT * FROM genres
LIMIT 10;

Lưu ý cách điều này cung cấp cho chúng ta cái nhìn về dữ liệu thô. Bạn có thể nhận thấy rằng một chương trình có ba giá trị. Đây là mối quan hệ một-nhiều.

  • Chúng ta có thể tìm hiểu thêm về bảng genres bằng cách gõ .schema genres.

Thực thi lệnh sau để tìm hiểu thêm về các phim hài (comedies) khác nhau trong cơ sở dữ liệu:

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

Lưu ý cách lệnh này tạo ra một danh sách các phim hài, bao gồm cả Catweazle.

Để tìm hiểu thêm về Catweazle bằng cách kết hợp nhiều bảng thông qua lệnh join:

SELECT * FROM shows
JOIN genres
ON shows.id = genres.show_id
WHERE id = 63881;

Lưu ý rằng điều này tạo ra một bảng tạm thời. Việc có một bảng trùng lặp là hoàn toàn bình thường.

  • Trái ngược với các mối quan hệ một-một và một-nhiều, có thể có các mối quan hệ nhiều-nhiều.

Chúng ta có thể tìm hiểu thêm về chương trình The Office và các diễn viên trong chương trình đó bằng cách thực thi lệnh sau:

SELECT name FROM people WHERE id IN
    (SELECT person_id FROM stars WHERE show_id =
        (SELECT id FROM shows WHERE title = 'The Office' AND year = 2005));

Lưu ý rằng điều này dẫn đến một bảng bao gồm tên của các ngôi sao khác nhau thông qua các truy vấn lồng nhau.

Chúng ta tìm thấy tất cả các chương trình mà Steve Carell đã đóng vai chính:

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

Điều này tạo ra một danh sách các tiêu đề của các chương trình mà Steve Carell đã tham gia.

Điều này cũng có thể được diễn đạt theo cách này:

SELECT title FROM shows, stars, people
WHERE shows.id = stars.show_id
AND people.id = stars.person_id
AND name = 'Steve Carell';
  • Ký tự đại diện % có thể được sử dụng để tìm tất cả những người có tên bắt đầu bằng Steve C, người ta có thể sử dụng cú pháp SELECT * FROM people WHERE name LIKE 'Steve C%';.

Chỉ mục (Indexes)

  • Mặc dù cơ sở dữ liệu quan hệ có khả năng nhanh hơn và mạnh mẽ hơn so với việc sử dụng tệp CSV, dữ liệu vẫn có thể được tối ưu hóa trong một bảng bằng cách sử dụng chỉ mục (indexes).

  • Chỉ mục có thể được tận dụng để tăng tốc các truy vấn của chúng ta.

  • Chúng ta có thể theo dõi tốc độ truy vấn bằng cách thực thi .timer on trong sqlite3.

  • Để hiểu cách chỉ mục có thể tăng tốc truy vấn, hãy chạy lệnh sau: SELECT * FROM shows WHERE title = 'The Office'; Lưu ý thời gian hiển thị sau khi truy vấn thực thi.

  • Sau đó, chúng ta có thể tạo một chỉ mục với cú pháp CREATE INDEX title_index ON shows (title);. Lệnh này yêu cầu sqlite3 tạo một chỉ mục và thực hiện một số tối ưu hóa đặc biệt bên dưới liên quan đến cột title này.

Điều này sẽ tạo ra một cấu trúc dữ liệu gọi là B Tree, một cấu trúc dữ liệu trông tương tự như cây nhị phân (binary tree). Tuy nhiên, khác với cây nhị phân, có thể có nhiều hơn hai nút con.

Hơn nữa, chúng ta có thể tạo các chỉ mục như sau:

CREATE INDEX name_index ON people (name);
CREATE INDEX person_index ON stars (person_id);

Chạy truy vấn và bạn sẽ nhận thấy rằng truy vấn chạy nhanh hơn nhiều!

SELECT title FROM shows WHERE id IN
    (SELECT show_id FROM stars WHERE person_id =
        (SELECT id FROM people WHERE name = 'Steve Carell'));
  • Thật không may, việc lập chỉ mục cho tất cả các cột sẽ dẫn đến việc sử dụng nhiều không gian lưu trữ hơn. Do đó, có một sự đánh đổi để có được tốc độ nhanh hơn.

Sử dụng SQL trong Python

Để hỗ trợ làm việc với SQL trong khóa học này, Thư viện CS50 có thể được sử dụng như sau trong mã của bạn:

from cs50 import SQL

  • Tương tự như các lần sử dụng Thư viện CS50 trước đây, thư viện này sẽ hỗ trợ các bước phức tạp khi sử dụng SQL trong mã Python của bạn.

  • Bạn có thể đọc thêm về chức năng SQL của Thư viện CS50 trong tài liệu.

  • Sử dụng kiến thức mới về SQL, giờ đây chúng ta có thể tận dụng Python song song với nó.

Sửa đổi mã của bạn cho favorites.py như sau:

# Searches database popularity of a problem

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(*) AS n FROM favorites WHERE language = ?", favorite)

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

# Print popularity
print(row["n"])

Lưu ý rằng db = SQL("sqlite:///favorites.db") cung cấp cho Python vị trí của tệp cơ sở dữ liệu. Sau đó, dòng bắt đầu bằng rows thực thi các lệnh SQL bằng cách sử dụng db.execute. Thật vậy, lệnh này truyền cú pháp bên trong dấu ngoặc kép vào hàm db.execute. Chúng ta có thể đưa ra bất kỳ lệnh SQL nào bằng cú pháp này. Hơn nữa, hãy lưu ý rằng rows được trả về dưới dạng một danh sách các từ điển. Trong trường hợp này, chỉ có một kết quả, một hàng, được trả về danh sách rows dưới dạng một từ điển.

Tình trạng tranh đua (Race Conditions)

  • Việc sử dụng SQL đôi khi có thể dẫn đến một số vấn đề.

  • Bạn có thể hình dung một trường hợp có nhiều người dùng cùng truy cập vào một cơ sở dữ liệu và thực thi các lệnh cùng một lúc.

  • Điều này có thể dẫn đến các trục trặc khi mã bị gián đoạn bởi hành động của người khác. Điều này có thể dẫn đến mất mát dữ liệu.

  • Các tính năng SQL tích hợp như BEGIN TRANSACTION, COMMIT và ROLLBACK giúp tránh một số vấn đề về tình trạng tranh đua này.

Tấn công SQL Injection

  • Bây giờ, vẫn xem xét mã ở trên, bạn có thể thắc mắc các dấu hỏi ? ở trên làm gì. Một trong những vấn đề có thể phát sinh trong các ứng dụng thực tế của SQL là cái gọi là tấn công injection (injection attack). Tấn công injection là nơi một kẻ tấn công có thể nhập mã SQL độc hại.

Ví dụ, hãy xem xét một màn hình đăng nhập như sau:

Nếu không có các biện pháp bảo vệ thích hợp trong mã của chính chúng ta, một kẻ xấu có thể chạy mã độc hại. Hãy xem xét ví dụ sau:

rows = db.execute("SELECT COUNT(*) FROM users WHERE username = ? AND password = ?", username, password)

Lưu ý rằng vì dấu ? được đặt đúng chỗ, việc xác thực có thể được chạy trên favorite trước khi nó được chấp nhận một cách mù quáng bởi truy vấn.

  • Bạn không bao giờ nên sử dụng các chuỗi được định dạng (formatted strings) trong các truy vấn như trên hoặc tin tưởng một cách mù quáng vào thông tin đầu vào của người dùng.

  • Sử dụng Thư viện CS50, thư viện sẽ làm sạch (sanitize) và loại bỏ bất kỳ ký tự tiềm ẩn nguy hiểm nào.

Tổng kết

Trong bài học này, bạn đã tìm hiểu thêm các cú pháp liên quan đến Python. Hơn nữa, bạn đã học cách tích hợp kiến thức này với dữ liệu dưới dạng cơ sở dữ liệu tệp phẳng và cơ sở dữ liệu quan hệ. Cuối cùng, bạn đã học về SQL. Cụ thể, chúng ta đã thảo luận về…

  • Cơ sở dữ liệu tệp phẳng

  • Cơ sở dữ liệu quan hệ

  • Các lệnh SQL như SELECT, CREATE, INSERT, DELETE và UPDATE.

  • Khóa chính và khóa ngoại

  • JOINs

  • Chỉ mục (Indexes)

  • Sử dụng SQL trong Python

  • Tình trạng tranh đua (Race conditions)

  • Tấn công SQL injection

Hẹn gặp lại các bạn lần sau!