Cơ sở dữ liệu và SQL: từ ô dữ liệu đến câu truy vấn, bốn chặng từ tổ chức, liên kết đến truy vấn và bảo toàn | LUKATO AI
Cơ sở dữ liệu và SQL cho Tin học 11: bảng, khóa chính, khóa ngoại, câu truy vấn SELECT, WHERE, GROUP BY, JOIN, thao tác sửa dữ liệu và bảo toàn dữ liệu, với…
LUKATO AI · TIN HỌC 11 · CƠ SỞ DỮ LIỆU VÀ SQL
TỪ Ô DỮ LIỆU ĐẾN CÂU TRUY VẤN
Một cơ sở dữ liệu là những bảng được nối bằng khóa , hỏi bằng SQL và giữ cho đúng bằng ràng buộc
- Vì sao không nên ghi họ tên và lớp của một độc giả ở mỗi dòng mượn sách , và hai bảng được nối với nhau bằng gì?
- Câu SELECT … WHERE … ORDER BY được máy chạy theo thứ tự nào, và vì sao WHERE không dùng được hàm COUNT?
- Hai thủ thư cùng ghi một con số thì phần việc của người ghi trước chịu hậu quả gì, và những lớp nào giữ cho dữ liệu không bị đè, không bị mất ?
Chuyên đề này gom bảng, khóa, truy vấn và thao tác sửa dữ liệu vào một khung bốn chặng : tổ chức, định danh và liên kết, truy vấn, bảo toàn. Em sẽ kéo thả để phân loại, chọn khóa chính, nối khóa ngoại, xem máy chạy từng bước một câu SELECT, lắp câu lệnh, gom nhóm, nối bảng, bắt lỗi trong câu lệnh và xếp lịch hai người cùng ghi. Điều nào vượt ra ngoài nội dung cốt lõi được ghi là kiến thức bổ sung.
- ① TỔ CHỨC: ô, hàng, cột, bảng
- ② ĐỊNH DANH VÀ LIÊN KẾT: khóa chính, khóa ngoại
- ③ TRUY VẤN: SELECT, WHERE, GROUP BY, JOIN, ORDER BY
- ④ BẢO TOÀN: INSERT, UPDATE, DELETE, giao dịch, quyền, sao lưu
14
phòng lab: phân loại, kéo thả, lắp câu lệnh, chạy từng bước, bắt lỗi, trò chơi…
14 bẫy
mỗi bẫy gắn với một chặng và ít nhất một phòng lab
10 thẻ
thẻ ôn nhanh để lật và tự nói lại
12 bài
bài luyện chọn lọc và báo cáo học tập của riêng em
01
Bản đồ chuyên đề
Từ ô dữ liệu đến câu truy vấn: khung bốn chặng
Một thư viện ghi sổ mượn sách trên giấy: mỗi lần mượn viết một dòng, ghi tên độc giả, lớp, tên sách, giá sách. Sau ba tháng, độc giả đổi lớp mà các dòng cũ vẫn ghi lớp cũ, sách đổi giá mà mỗi dòng ghi một giá. Cơ sở dữ liệu chữa đúng căn bệnh ấy: mỗi thông tin chỉ ghi một chỗ , các chỗ nối bằng khóa, và có ngôn ngữ để hỏi và sửa. Mục này vẽ bản đồ cho cả chuyên đề.
Câu hỏi 1 · TỔ CHỨC
Thông tin này nằm ở bảng nào, cột nào?
Mỗi loại sự vật có một bảng; mỗi thuộc tính là một cột; mỗi sự vật cụ thể là một hàng. Gọi đúng tên ô, hàng, cột trước khi viết bất cứ câu lệnh nào.
Câu hỏi 2 · LIÊN KẾT
Hàng này được nhận ra bằng gì, và nối sang bảng khác bằng gì?
Khóa chính nhận ra từng hàng; khóa ngoại trỏ sang hàng của bảng khác. Hai khóa ấy là sợi chỉ nối các truy vấn nhiều bảng.
Câu hỏi 3 · TRUY VẤN VÀ BẢO TOÀN
Cần lấy gì ra, hay cần sửa gì vào, và điều gì giữ cho dữ liệu đúng?
SELECT lấy dữ liệu ra; INSERT, UPDATE, DELETE đưa dữ liệu vào, sửa và bỏ đi. Ràng buộc, giao dịch, quyền và sao lưu giữ cho dữ liệu đúng.
Một thư viện nhỏ làm ví dụ xuyên suốt
Cả chuyên đề dùng một thư viện trường học với bốn bảng: THELOAI (thể loại), SACH (sách), DOCGIA (độc giả) và MUON (lần mượn). Các số liệu đều là số liệu minh họa. Em hãy đọc kĩ bốn bảng này một lần, vì các mục sau đều quay lại chúng.
THELOAI
MaTL · TenTL
TL1 · Văn học
TL2 · Khoa học
TL3 · Lịch sử
TL4 · Truyện tranh
SACH (Gia tính bằng nghìn đồng, số liệu minh họa)
MaSach · TenSach · MaTL · Gia · SoLuong
S01 · Hạt giống mùa xuân · TL1 · 45 · 6
S02 · Dòng sông thầm lặng · TL1 · 62 · 3
S03 · Vì sao trời xanh · TL2 · 58 · 5
S04 · Bản đồ các vì sao · TL2 · 85 · 2
S05 · Máy tính đầu tiên · TL2 · 70 · 4
S06 · Người xây thành · TL3 · 90 · 3
S07 · Kí ức ngàn năm · TL3 · 55 · 5
S08 · Hành trình cá nhỏ · TL1 · 38 · 8
DOCGIA
MaDG · HoTen · Lop · SoThe
D01 · Nguyễn Lan · 10A1 · T-101
D02 · Trần Minh · 10A1 · NULL
D03 · Trần Minh · 11B2 · T-103
D04 · Lê Hoa · 11B2 · T-104
D05 · Phạm Nam · 10A1 · NULL
D06 · Vũ An · 12C1 · T-106
MUON
MaMuon · MaDG · MaSach · SoNgay
M01 · D01 · S01 · 7
M02 · D01 · S03 · 14
M03 · D02 · S03 · 10
M04 · D03 · S06 · 7
M05 · D03 · S01 · 12
M06 · D04 · S02 · 5
M07 · D04 · S05 · 9
M08 · D05 · S03 · 6
M09 · D01 · S05 · 8
Cột có chữ NULL xám là ô chưa có giá trị : độc giả D02 và D05 chưa được cấp số thẻ. Thể loại TL4 chưa có sách nào; độc giả D06 chưa mượn lần nào. Hai chi tiết ấy sẽ quay lại ở mục 4 và mục 6.
Bảng bốn chặng: bấm vào từng ô
Mỗi hàng của bảng là một đối tượng từ nhỏ đến lớn (ô, hàng, cột, bảng, nhiều bảng). Mỗi cột là một chặng nhìn đối tượng ấy: nó được tổ chức ra sao, được định danh và liên kết bằng gì, được truy vấn bằng mệnh đề nào, và được bảo toàn bằng cơ chế nào. Bảng này là bản đồ của cả chuyên đề: mục 2 đi qua chặng tổ chức, mục 3 qua chặng liên kết, mục 4 đến mục 6 qua chặng truy vấn, mục 7 qua chặng bảo toàn.
ĐỐI TƯỢNG
① TỔ CHỨC
② ĐỊNH DANH VÀ LIÊN KẾT
③ TRUY VẤN
④ BẢO TOÀN
Ô (giá trị)
Hàng (bản ghi)
Cột (thuộc tính)
Bảng
Nhiều bảng (CSDL)
↔ Vuốt ngang để xem đủ bốn chặng.
Bấm vào một ô để xem ô ấy nói gì, nằm ở bẫy nào và luyện ở phòng lab nào.
Sáu thuật ngữ nền
Mỗi thuật ngữ có gạch chấm bên dưới là một thẻ giải nghĩa: rê chuột hoặc chạm vào để đọc. Sáu từ sau đây là chỗ để làm quen.
cơ sở dữ liệu hệ quản trị CSDL bản ghi trường khóa truy vấn
Cùng một cuốn sách, ba cách gọi
Xét cuốn “Vì sao trời xanh” có mã S03. Cả hàng S03 là một bản ghi: nó mô tả đầy đủ một cuốn sách. Giá trị 58 ở cột Gia của hàng ấy là một ô . Cột Gia, gồm các giá trị 45, 62, 58, 85 và tiếp theo, là một thuộc tính chung cho các sách. Câu “lấy các sách có giá trên 60” nói về hàng được giữ lại, còn câu “hiện tên sách” nói về cột được chọn. Phòng lab 1 cho em tự xếp mười hai thẻ vào đúng chỗ.
Dự đoán trước khi mở lab: trong bảng SACH ở trên, “một bản ghi” là cái nào?
Phòng lab 1 · Ô, hàng, cột, bảng: xếp đúng chỗ DẠNG: KÉO THẢ PHÂN LOẠI
Dự đoán, thao tác, kiểm tra. Mười hai thẻ mô tả các phần của bảng SACH và DOCGIA. Kéo (hoặc bấm thẻ rồi bấm ô) để xếp thẻ vào đúng loại, rồi bấm Kiểm tra. Lab này gặp bẫy 1 .
Chỗ hay trượt ở chặng tổ chức
Bẫy 1: nhầm hàng, cột và ô. Điều kiện trong WHERE nói về hàng được giữ lại; danh sách sau SELECT nói về cột được hiện. Đảo hai vai này dễ dẫn tới câu lệnh sai ở các mục sau.
Gặp ở: Lab 1 · chưa có bài ở mục 10, luyện bằng lab
Bảng SACH có 8 hàng và 5 cột. Có bao nhiêu ô, và bảng mới thêm một sách thì thêm bao nhiêu ô? Hãy tự đoán trước, rồi mới mở các gợi ý theo thứ tự.
Gợi ý 1
Mỗi hàng có một ô cho mỗi cột. Hàng S01 có 5 ô (mã, tên, thể loại, giá, số lượng).
Gợi ý 2
Có 8 hàng, mỗi hàng 5 ô nên tổng số ô là 8\times5=40.
Kết luận
Bảng có 40 ô. Thêm một sách là thêm một hàng, tức thêm 5 ô; bảng có 9 hàng và 45 ô. Thêm một thuộc tính mới (ví dụ NamXuatBan) lại là thêm một cột, tức thêm 8 ô.
Vì sao một khung cho cả chuyên đề
Các bài về cơ sở dữ liệu nhìn bề ngoài rất khác nhau: chọn khóa chính, vẽ quan hệ, viết SELECT, gom nhóm, nối bảng, sửa dữ liệu, tránh mất dữ liệu. Nhưng bài nào cũng trả lời một trong bốn câu: dữ liệu được tổ chức ra sao, được nhận ra và nối bằng gì, được hỏi bằng mệnh đề nào, và được giữ đúng bằng cơ chế nào. Gặp một bài, em gạch chân đối tượng (ô, hàng, cột, bảng, nhiều bảng), xác định chặng, rồi chọn công cụ.
02
Chặng ① · Tổ chức
Bảng, hàng, cột: mỗi thông tin ghi ở một chỗ
Hãy tưởng tượng một tủ phiếu thư viện: mỗi tấm phiếu là một cuốn sách, trên phiếu có các ô để ghi tên, giá, thể loại. Cơ sở dữ liệu là tủ phiếu ấy được kỉ luật hóa: mỗi loại phiếu một ngăn (bảng), mỗi ô một giá trị, điều lặp lại được chuyển sang ngăn riêng.
Giải phẫu một bảng
Một bảng gồm các cột (còn gọi là trường hoặc thuộc tính) và các hàng (còn gọi là bản ghi ). Hàng tiêu đề ghi tên cột. Cột Gia và SoLuong chứa số nguyên, cột TenSach chứa chuỗi. Giao của một hàng và một cột là một ô , và mỗi ô chứa một giá trị thuộc kiểu của cột.
Mỗi cột còn có một kiểu dữ liệu .
Vuốt ngang để xem đủ hình.
Hình 2.1. Bảng SACH: hàng S02 tô xanh là một bản ghi, cột Gia tô xanh lục là một thuộc tính, ô vàng là giá trị 62 của sách S02. Dòng tím dưới bảng ghi kiểu dữ liệu của từng cột.
Cách ghi tên cũng có quy ước. Tên bảng nói về một loại sự vật (SACH, không phải “sách của thư viện tháng này”). Tên cột nói về một thuộc tính của sự vật ấy (TenSach, Gia). Tên không dấu, không khoảng trắng giúp câu lệnh gọn và ít lỗi gõ. Câu lệnh tạo bảng SACH có dạng:
CREATE TABLE SACH (
MaSach VARCHAR(5) PRIMARY KEY ,
TenSach VARCHAR(100) NOT NULL,
MaTL VARCHAR(5),
Gia INT,
SoLuong INT
);
Câu lệnh cho biết mỗi cột thuộc kiểu nào (INT là số nguyên, VARCHAR là chuỗi có độ dài tối đa) và có ràng buộc gì: PRIMARY KEY cho MaSach, NOT NULL cho TenSach nghĩa là mỗi sách bắt buộc có tên. Các ràng buộc này chính là chặng bảo toàn đã xuất hiện ngay từ lúc tạo bảng.
Ô rỗng (NULL) không phải số 0, cũng không phải chuỗi trống
Độc giả D02 chưa được cấp số thẻ. Ô SoThe của D02 vì thế chưa có giá trị ; SQL gọi trạng thái ấy là NULL . NULL khác số 0 (số lượng bằng 0 là đã biết, đó là “hết sách”) và khác chuỗi trống (đã biết, đó là chuỗi không có kí tự). Phân biệt ba trường hợp này giúp em hiểu vì sao ở mục 4 phép so sánh với NULL phải được viết riêng.
Vì sao phải tách bảng
Cuốn sổ mượn sách ở đầu chuyên đề có thể được chép nguyên thành một bảng phẳng: mỗi lần mượn một hàng, ghi kèm họ tên và lớp của độc giả. Với chín lần mượn của năm độc giả, họ tên và lớp bị chép lại nhiều lần. Việc ấy gây ra ba rắc rối, mà người làm cơ sở dữ liệu gọi là dị thường (kiến thức bổ sung):
- Dị thường khi sửa: một độc giả đổi lớp thì phải sửa ở từng dòng của người ấy; quên một dòng là dữ liệu mâu thuẫn nhau.
- Dị thường khi thêm: độc giả mới chưa mượn lần nào thì chưa có dòng để ghi, hoặc phải để trống nhiều ô.
- Dị thường khi xóa: xóa dòng mượn cuối cùng của một độc giả thì mất cả thông tin về người ấy.
Vuốt ngang để xem đủ hình.
Hình 2.2. Bên trái: bảng phẳng với năm lần mượn của hai độc giả, sáu ô họ tên và lớp bị chép lại (tô đỏ). Bên phải: hai bảng; họ tên và lớp ghi một lần ở DOCGIA, bảng MUON chỉ giữ mã độc giả MaDG.
Cách chữa là tách bảng , việc mà người làm cơ sở dữ liệu gọi là chuẩn hóa (kiến thức bổ sung): thông tin của độc giả đưa sang bảng DOCGIA, mỗi người một hàng; bảng MUON chỉ giữ mã độc giả để biết lần mượn thuộc về ai. Hai bảng gặp nhau nhờ cột MaDG, một chủ đề của mục 3. Nguyên tắc rút ra: mỗi sự kiện nhỏ một hàng; mỗi thông tin về một sự vật chỉ ghi một chỗ .
Bẫy ở chặng tổ chức khi thiết kế bảng
Bẫy 2: nhồi nhiều giá trị vào một ô cho “gọn”. Một ô giữ danh sách (hai lớp, hai địa chỉ thư điện tử, nhiều mã sách) thì không lọc, không đếm, không sửa từng phần được bằng câu lệnh đơn giản. Hãy tách sang bảng riêng và nối bằng khóa.
Gặp ở: Lab 2 · Bài 3 và 6
Dự đoán trước khi mở lab: cột Lop của một độc giả học thêm hai lớp được ghi “10A1; 10A2” trong một ô. Cách viết nào sau đây tìm đúng các độc giả học lớp 10A2?
Phòng lab 2 · Ba cách thiết kế sổ mượn DẠNG: KỊCH BẢN NHIỀU NHÁNH
Dự đoán, thao tác, kiểm tra. Chọn một cách thiết kế và một việc cần làm; lab đếm số ô phải sửa và cho biết dữ liệu có nguy cơ lệch không. Thử cả ba thiết kế với cả ba việc. Lab này gặp bẫy 2 .
Sổ mượn có 9 lần mượn của 5 độc giả. Trong bảng phẳng có bao nhiêu ô họ tên và lớp bị chép lặp, nếu mỗi độc giả chỉ cần ghi một lần?
Gợi ý 1
Mỗi độc giả cần đúng một dòng “gốc” để ghi họ tên và lớp. Số dòng gốc bằng số độc giả: 5.
Gợi ý 2
Số dòng chép lặp là 9-5=4. Mỗi dòng lặp chép lại hai ô (họ tên, lớp).
Kết luận
Số ô lặp là 4\times2=8. Khi tách bảng, 8 ô này biến mất: DOCGIA ghi họ tên và lớp một lần cho mỗi người, bảng MUON thay hai cột ấy bằng một cột mã MaDG ngắn.
Kiểm tra thiết kế bằng bốn câu hỏi
Trước khi chốt một bảng, em đối chiếu với danh sách dưới đây (bấm để đánh dấu; trình duyệt sẽ nhớ nếu được phép):
- Tên bảng là danh từ chỉ một loại sự vật (SACH, DOCGIA).
- Mỗi cột mô tả chính sự vật ấy, không mô tả một sự vật khác.
- Không ô nào chứa danh sách nhiều giá trị.
- Thông tin bị chép lặp ở nhiều hàng đã được chuyển sang một bảng riêng và nối bằng khóa.
Vì sao đây vẫn là chặng “tổ chức”
Tách bảng chưa phải là truy vấn, cũng chưa phải là bảo toàn: nó là cách đặt dữ liệu vào chỗ của mình. Nhưng nó quyết định các chặng sau dễ hay khó. Dữ liệu tổ chức đúng thì câu SELECT ngắn, câu UPDATE chỉ sửa một ô, và ràng buộc có chỗ để bám. Dữ liệu tổ chức sai thì các câu lệnh phía sau phải vá lại.
03
Chặng ② · Định danh và liên kết
Khóa chính và khóa ngoại: nhận ra hàng, nối các bảng
Thư viện có hai độc giả cùng tên Trần Minh, một người ở lớp 10A1, một người ở lớp 11B2. Hỏi “Trần Minh đã mượn sách nào?” thì thủ thư phải hỏi lại: Trần Minh nào? Cách chữa là cho mỗi hàng một tên riêng không trùng (khóa chính) và cho các hàng ở bảng khác một cách trỏ tới tên riêng ấy (khóa ngoại). Mục này nói về hai loại khóa.
Khóa chính: tên riêng của mỗi hàng
Khóa chính (primary key) là một cột, hoặc một nhóm cột, mà giá trị của nó phân biệt được từng bản ghi trong bảng. Để làm được việc ấy, khóa chính phải thỏa ba điều kiện. Hai điều đầu được hệ quản trị kiểm tra tự động khi em khai báo PRIMARY KEY; điều thứ ba là lời khuyên thiết kế: