Mục lục bài họcĐang ở d08-b2
Querying with SQL
The shape of a query
Every query in this syllabus follows the same pattern:
$$\textbf{SELECT } \text{fields} \ \textbf{ FROM } \text{table} \ \textbf{ WHERE } \text{condition} \ \textbf{ ORDER BY } \text{field}$$
| SELECT | which fields (columns) to show |
| FROM | which table to take them from |
| WHERE | which records (rows) to include — a condition |
| ORDER BY | which field to sort the results on |
The distinction that carries marks: SELECT chooses columns; WHERE chooses rows. They are doing different jobs on different axes of the table, and swapping them is a marked error.
$\textbf{SELECT} \ *$ means all fields.
A worked query
$\textbf{SELECT } \text{Surname, Mark}$
$\textbf{FROM } \text{Results}$
$\textbf{WHERE } \text{Mark} >= 50$
$\textbf{ORDER BY } \text{Mark } \textbf{DESC}$
Read it in the order the database works: take the table $\text{Results}$; keep only the rows where the mark is at least $50$; show only the two columns $\text{Surname}$ and $\text{Mark}$; sort them with the highest mark first.
$\textbf{ASC}$ sorts ascending (the default); $\textbf{DESC}$ sorts descending.
Conditions in WHERE
The comparison operators are the same as in programming: $=$, $<>$, $<$, $>$, $<=$, $>=$.
Conditions combine with AND and OR:
$\textbf{WHERE } \text{Mark} >= 50 \textbf{ AND } \text{Year} = 11$
Note that AND makes the result smaller (both must hold) while OR makes it larger (either will do). Students often reach for OR when they mean AND, and get far too many rows back.
Text values are put in quotation marks: $\textbf{WHERE } \text{Subject} = \text{"Physics"}$.
SUM and COUNT
- SUM adds up the values in a numeric field: $\textbf{SELECT SUM}(\text{Mark})$;
- COUNT counts how many records match: $\textbf{SELECT COUNT}(*)$.
The difference matters. $\textbf{SUM}(\text{Mark})$ on three marks of $40$, $50$ and $60$ gives $150$. $\textbf{COUNT}(*)$ on the same three records gives $3$.
So SUM answers "how much altogether?" and COUNT answers "how many?"
Why a database beats a set of separate files
- data is stored once, so there is no risk of two copies disagreeing;
- validation is set on the field, so every entry is checked;
- the data can be searched and sorted in any way, quickly;
- a primary key guarantees each record can be found reliably.
Hình dạng của một câu truy vấn. Mọi câu truy vấn trong chương trình này đều theo cùng một khuôn:
$$\textbf{SELECT } \text{các trường} \ \textbf{ FROM } \text{bảng} \ \textbf{ WHERE } \text{điều kiện} \ \textbf{ ORDER BY } \text{trường}$$
- SELECT — chọn những trường (cột) nào sẽ hiện ra;
- FROM — lấy từ bảng nào;
- WHERE — giữ lại những bản ghi (hàng) nào, bằng một điều kiện;
- ORDER BY — sắp xếp kết quả theo trường nào.
Khác biệt quyết định điểm: SELECT chọn cột; WHERE chọn hàng. Chúng làm hai việc khác nhau trên hai trục khác nhau của cái bảng, và đảo hai việc này là lỗi bị trừ điểm.
$\textbf{SELECT} \ *$ nghĩa là lấy tất cả các trường.
Một câu truy vấn mẫu.
$\textbf{SELECT } \text{HoTen, Diem}$
$\textbf{FROM } \text{KetQua}$
$\textbf{WHERE } \text{Diem} >= 50$
$\textbf{ORDER BY } \text{Diem } \textbf{DESC}$
Đọc nó theo đúng thứ tự cơ sở dữ liệu làm việc: lấy bảng $\text{KetQua}$; chỉ giữ những hàng có điểm từ $50$ trở lên; chỉ hiện hai cột là $\text{HoTen}$ và $\text{Diem}$; sắp xếp với điểm cao nhất lên trước.
$\textbf{ASC}$ sắp tăng dần (mặc định); $\textbf{DESC}$ sắp giảm dần.
Điều kiện trong WHERE. Các toán tử so sánh giống hệt trong lập trình: $=$, $<>$, $<$, $>$, $<=$, $>=$.
Các điều kiện ghép với nhau bằng AND và OR:
$\textbf{WHERE } \text{Diem} >= 50 \textbf{ AND } \text{Khoi} = 11$
Chú ý AND làm kết quả nhỏ đi (phải thoả cả hai), còn OR làm kết quả lớn ra (thoả một cái là được). Học sinh hay dùng OR trong khi ý là AND, và nhận về quá nhiều hàng.
Giá trị văn bản phải đặt trong dấu ngoặc kép: $\textbf{WHERE } \text{MonHoc} = \text{"Physics"}$.
SUM và COUNT.
- SUM cộng các giá trị trong một trường kiểu số: $\textbf{SELECT SUM}(\text{Diem})$;
- COUNT đếm xem có bao nhiêu bản ghi thoả điều kiện: $\textbf{SELECT COUNT}(*)$.
Khác biệt này quan trọng. $\textbf{SUM}(\text{Diem})$ trên ba điểm $40$, $50$ và $60$ cho $150$. $\textbf{COUNT}(*)$ trên đúng ba bản ghi đó cho $3$.
Vậy SUM trả lời câu “tổng cộng bao nhiêu?”, còn COUNT trả lời câu “có bao nhiêu cái?”
Vì sao cơ sở dữ liệu hơn một mớ tệp rời.
- dữ liệu được lưu một lần, nên không có chuyện hai bản sao mâu thuẫn nhau;
- kiểm tra hợp lệ đặt ngay trên trường, nên mọi lần nhập đều được kiểm;
- dữ liệu tìm và sắp xếp được theo bất kỳ cách nào, rất nhanh;
- khoá chính bảo đảm luôn tìm lại được đúng một bản ghi.
Bảng $\text{KetQua}$ có các trường $\text{HoTen}$, $\text{Diem}$, $\text{Khoi}$, $\text{MonHoc}$. (a) Viết câu truy vấn hiện họ tên và điểm của học sinh khối $11$ đạt từ $50$ trở lên, sắp theo điểm giảm dần. (b) Giải thích từng từ khoá làm gì. (c) Nêu khác biệt giữa SUM và COUNT.
(a) Câu truy vấn.
$\textbf{SELECT } \text{HoTen, Diem}$
$\textbf{FROM } \text{KetQua}$
$\textbf{WHERE } \text{Diem} >= 50 \textbf{ AND } \text{Khoi} = 11$
$\textbf{ORDER BY } \text{Diem } \textbf{DESC}$
Chú ý dùng AND chứ không phải OR: đề yêu cầu cả hai điều kiện cùng thoả.
Nếu viết OR, kết quả sẽ gồm mọi học sinh đạt từ $50$ ở bất kỳ khối nào, cộng thêm mọi học sinh khối $11$ kể cả điểm thấp — quá nhiều hàng so với yêu cầu.
(b) Từng từ khoá làm gì.
SELECT chọn những cột sẽ hiện ra. Ở đây là hai cột $\text{HoTen}$ và $\text{Diem}$ — các cột $\text{Khoi}$ và $\text{MonHoc}$ không hiện, dù $\text{Khoi}$ có được dùng trong điều kiện.
FROM chỉ ra lấy dữ liệu từ bảng nào — ở đây là $\text{KetQua}$.
WHERE chọn những hàng nào được giữ lại. Chỉ những bản ghi thoả cả hai điều kiện mới lọt vào kết quả.
ORDER BY sắp xếp kết quả. $\textbf{DESC}$ là giảm dần nên điểm cao nhất lên trước; $\textbf{ASC}$ là tăng dần và là mặc định nếu không ghi gì.
Điểm quan trọng nhất cần nắm: SELECT chọn cột, WHERE chọn hàng. Chúng làm hai việc khác nhau trên hai trục khác nhau của cùng một bảng.
(c) SUM và COUNT.
SUM cộng các giá trị trong một trường kiểu số. COUNT đếm số bản ghi thoả điều kiện.
Ví dụ cụ thể. Giả sử ba học sinh đạt $40$, $50$ và $60$ điểm.
$\textbf{SELECT SUM}(\text{Diem})$ cho $150$ — tổng của ba con số.
$\textbf{SELECT COUNT}(*)$ cho $3$ — số bản ghi.
Cách phân biệt gọn: SUM trả lời “tổng cộng bao nhiêu?”, còn COUNT trả lời “có bao nhiêu cái?”
Lưu ý SUM chỉ dùng được trên trường kiểu số; còn COUNT dùng được với mọi bảng vì nó chỉ đếm hàng.
Một câu lạc bộ đang giữ danh sách hội viên trong ba tệp riêng ở ba máy khác nhau. Giải thích bốn lợi ích khi chuyển sang một cơ sở dữ liệu.
Lợi ích một — dữ liệu được lưu một lần.
Vấn đề hiện tại. Cùng một hội viên xuất hiện trong cả ba tệp. Khi người đó chuyển nhà, nhân viên phải nhớ sửa cả ba chỗ.
Chỉ cần bỏ sót một tệp là câu lạc bộ có hai địa chỉ mâu thuẫn cho cùng một người — và không ai biết bản nào đúng.
Cơ sở dữ liệu giải quyết: dữ liệu nằm một chỗ duy nhất, nên sửa một lần là mọi nơi đều thấy bản mới.
Nó cũng tiết kiệm dung lượng, vì không lưu ba bản của cùng một thứ.
Lợi ích hai — kiểm tra hợp lệ đặt trên trường.
Trong một cơ sở dữ liệu, các phép kiểm — kiểm khoảng, độ dài, kiểu, có mặt, định dạng — được gắn vào chính cái trường.
Nghĩa là mọi lần nhập đều được kiểm, bất kể ai gõ và gõ ở máy nào. Với ba tệp rời, mỗi người có thể nhập theo một quy ước khác nhau và không có gì ngăn cản.
Lợi ích ba — tìm và sắp xếp theo bất kỳ cách nào.
Một câu truy vấn lấy ra được chính xác thứ cần: hội viên chưa đóng phí, hội viên ở một quận, hội viên sắp hết hạn thẻ — và sắp xếp theo trường bất kỳ.
Với các tệp rời, mỗi câu hỏi như vậy đều phải làm bằng tay: mở từng tệp, dò từng dòng, chép ra chỗ khác. Vừa chậm vừa dễ sót.
Lợi ích bốn — khoá chính bảo đảm tìm đúng bản ghi.
Khoá chính là một trường duy nhất cho mỗi hội viên, nên hệ thống luôn lấy ra được đúng một bản ghi.
Với các tệp rời không có mã hội viên, hai người trùng tên sẽ không phân biệt được — và cập nhật cho người này có thể ghi nhầm sang người kia.
Đọc xong rồi — làm thử ngay
Bài tập của chương Databases gồm 14 câu trắc nghiệm và 5 đề tự luận. Đáp án hiện ngay khi chọn, miễn phí.