PHẦN LÝ THUYẾT
1. LỢI ÍCH CỦA NGÔN NGỮ TRUY VẤN
- Dùng ngôn ngữ truy vấn, người dùng chỉ cần viết ra yêu cầu dưới dạng một câu truy vấn – muốn làm gì, chứ không phải nghĩ cách để thực hiện yêu cầu ấy. Mọi việc còn lại sẽ do hệ QTCSDL giải quyết: tiếp nhận yêu cầu ở dạng câu truy vấn rồi lấy ra kết quả theo đúng yêu cầu.
- Ngôn ngữ truy vấn phổ biến hiện nay là SQL. SQL đã trở thành ngữ truy vấn tiêu chuẩn mà hầu hết các hệ QTCSDL đều sử dụng.
- SQL có 3 thành phần là DDL (Data Definition Language – Ngôn ngữ định nghĩa dữ liệu), DML (Data Manibulation Language – Ngôn ngữ thao tác dữ liệu) và DCL (Data Control Language – Ngôn ngữ kiểm soát dữ liệu).
2. KHỞI TẠO CSDL
Thành phần DDL của SQL cung cấp các câu truy vấn khởi tạo CSDL, khởi tạo bảng, thiết lập các khóa, tóm tắt trong các bảng sau.
Bảng 14.1. Các câu truy vấn CSDL
| Câu truy vấn DDL | Ý nghĩa |
| CREATE DATABASE | Khởi tạo CSDL |
| CREATE TABLE | Khởi tạo bảng |
| ALTER TABLE | Thay đổi định nghĩa bảng |
| PRIMARY KEY | Khai báo khoá chính |
| FOREIGN KEY... REFERENCES... | Khai báo khoá ngoài |
Các kiểu dữ liệu được sử dụng cho các thuộc tính của các bảng trong SQL.
Bảng 14.2. Kiểu dữ liệu
| Kiểu dữ liệu | Ý nghĩa |
| CHAR (n) hay CHARACTER (n) | Xâu kí tự có độ dài cố định n kí tự, nếu xâu có ít hơn n kí tự, các kí tự trống được thêm vào phía bên phải |
| VARCHAR (n) | Xâu kí tự có độ dài thay đổi, không vượt quá n kí tự |
| BOOLEAN | Kiểu lôgic có giá trị Đúng (1) hay Sai (0) |
| INT hay INTEGER | Số nguyên |
| REAL | Số thực dấu phẩy động |
| DATE | Ngày tháng, dạng 'YYYY-MM-DD' |
| TIME | Thời gian, dạng 'HH:MM:SS' |
Ví dụ: Khởi tạo CSDL âm nhạc, đặt tên là music và khởi tạo các bảng Nhạc sĩ, Bản nhạc có tên tương ứng là nhacsi, bannhac.
2.1. Truy vấn con (Subquery)
Truy vấn con (Subquery) là một câu lệnh SELECT được bọc lại bên trong một câu lệnh SQL khác. Truy vấn con thường nằm trong dấu ngoặc tròn ( ) và được thực hiện trước truy vấn chính. Kết quả của truy vấn con sẽ được sử dụng làm dữ liệu đầu vào cho truy vấn bên ngoài.
Subquery giúp viết các truy vấn phức tạp một cách rõ ràng hơn, đặc biệt khi cần lấy dữ liệu dựa trên kết quả của một truy vấn khác.
| Thành phần | Cú pháp |
| Cấu trúc chung | SELECT tên_cột |
| FROM tên_bảng | |
| WHERE tên_cột toán_tử ( | |
| SELECT tên_cột | |
| FROM tên_bảng | |
| ); |
Trong đó:
• Câu lệnh SELECT bên trong ngoặc là truy vấn con.
• Câu lệnh SELECT bên ngoài là truy vấn chính.
• Truy vấn con được thực hiện trước và trả kết quả cho truy vấn chính sử dụng.
Ví dụ minh họa
Giả sử có bảng DIEM
| MaHS | Mon | Diem |
| HS01 | Tin | 9 |
| HS01 | Toan | 8 |
| HS02 | Tin | 7 |
| HS03 | Tin | 9 |
Ví dụ: Tìm học sinh có điểm lớn hơn điểm trung bình của lớp
SELECT MaHS, Mon, Diem FROM DIEM WHERE Diem > (SELECT AVG(Diem) FROM DIEM );
Trong câu lệnh trên:
1. Truy vấn con SELECT AVG(Diem) FROM DIEM được thực hiện trước để tính điểm trung bình.
2. Truy vấn chính sẽ lấy những bản ghi có điểm lớn hơn giá trị trung bình đó.
Một số cách sử dụng Subquery
| Cách dùng | Ví dụ |
| So sánh với một giá trị | WHERE Diem > (SELECT AVG(Diem) FROM DIEM) |
| Kiểm tra trong tập giá trị | WHERE MaHS IN (SELECT MaHS FROM DIEM WHERE Diem > 8) |
| Kiểm tra tồn tại dữ liệu | WHERE EXISTS (SELECT * FROM DIEM WHERE DIEM.MaHS = HOCSINH.MaHS) |
Subquery cho phép sử dụng kết quả của một truy vấn làm điều kiện cho truy vấn khác. Nhờ đó, người dùng có thể xây dựng các câu truy vấn linh hoạt và xử lí những yêu cầu dữ liệu phức tạp trong cơ sở dữ liệu.
2.2. Tại sao cần khai báo kiểu dữ liệu phù hợp, không thừa
Khi thiết kế bảng trong cơ sở dữ liệu, mỗi cột cần được khai báo kiểu dữ liệu phù hợp với loại thông tin mà nó lưu trữ. Việc lựa chọn đúng kiểu dữ liệu giúp hệ thống lưu trữ dữ liệu hiệu quả và đảm bảo tính chính xác của thông tin.
Nếu khai báo kiểu dữ liệu không phù hợp hoặc sử dụng kiểu dữ liệu quá lớn so với nhu cầu, cơ sở dữ liệu có thể tốn nhiều bộ nhớ hơn cần thiết và làm giảm hiệu suất xử lí.
3. CẬP NHẬT VÀ TRUY XUẤT DỮ LIỆU
Thành phần DML của SQL cung cấp các câu truy vấn cập nhật và truy xuất dữ liệu. Sau đây là một vài câu truy xuất dữ liệu để minh họa.
Bảng 14.3. Câu truy xuất dữ liệu
| Câu truy xuất dữ liệu | Ý nghĩa |
| SELECT <dữ liệu cần lấy> FROM <tên bảng> |
<dữ liệu cần lấy> có thể là danh sách các trường hay hàm nào đó với các biến là trường trong bảng |
| WHERE <điều kiện chọn> | Chỉ định chọn chỉ các dòng thoả mãn điều kiện xác định |
| ORDER BY <tên trường> | Sắp xếp các dòng kết quả theo thứ tự chỉ định |
| INNER JOIN | Liên kết các bảng theo điều kiện |
Một số câu truy vấn cập nhật dữ liệu cho trong bảng 14.4.
Bảng 14.4. Các câu truy vấn cập nhật dữ liệu
| Câu truy vấn cập nhật dữ liệu | Ý nghĩa |
| INSERT INTO <tên bảng> VALUES <danh sách giá trị> |
Thêm dữ liệu vào bảng <tên bảng> với giá trị lấy từ <danh sách giá trị> |
| DELETE FROM <tên bảng> WHERE <điều kiện> |
Xoá các dòng trong bảng <tên bảng> thoả mãn <điều kiện> |
| UPDATE <tên bảng> SET <tên trường> = <giá trị> |
Cập nhật <giá trị> cho trường có tên là <tên trường> trong bảng <tên bảng> |
3.1. Liên kết thông qua Trường khóa (Join)
Trong cơ sở dữ liệu quan hệ, dữ liệu thường được lưu trữ trong nhiều bảng khác nhau để giảm sự trùng lặp và giúp việc quản lí dữ liệu hiệu quả hơn. Để kết hợp thông tin từ nhiều bảng khi truy vấn dữ liệu, người ta sử dụng phép liên kết (Join).
Phép Join thường dựa trên trường khóa giữa các bảng, ví dụ khóa chính (Primary Key) của bảng này và khóa ngoại (Foreign Key) của bảng khác. Khi thực hiện liên kết, hệ quản trị cơ sở dữ liệu sẽ so sánh giá trị của các trường khóa giữa các bảng và ghép các bản ghi có liên quan lại với nhau để tạo ra bảng kết quả.
Ví dụ, giả sử có hai bảng dữ liệu:
Bảng HOCSINH
| MaHS | TenHS | Lop |
| HS01 | An | 11A |
| HS02 | Bình | 11A |
| HS03 | Chi | 11B |
Bảng DIEM
| MaHS | Mon | Diem |
| HS01 | Tin | 9 |
| HS01 | Toan | 8 |
| HS02 | Tin | 7 |
Trong hai bảng trên, trường MaHS được dùng làm trường khóa để liên kết.
Các loại phép liên kết phổ biến
| Loại liên kết | Cách sử dụng | Ví dụ |
| INNER JOIN (JOIN) | Kết hợp hai bảng và chỉ lấy những bản ghi có giá trị khớp nhau ở cả hai bảng theo trường khóa. | Truy vấn: SELECT * FROM HOCSINH INNER JOIN DIEM ON HOCSINH.MaHS = DIEM.MaHS → Kết quả chỉ gồm HS01 và HS02 vì hai học sinh này có dữ liệu điểm trong bảng DIEM. |
| LEFT JOIN (LEFT OUTER JOIN) | Lấy tất cả các bản ghi của bảng bên trái và các bản ghi tương ứng ở bảng bên phải. Nếu không có dữ liệu tương ứng thì giá trị sẽ là NULL. | Truy vấn: SELECT * FROM HOCSINH LEFT JOIN DIEM ON HOCSINH.MaHS = DIEM.MaHS → Kết quả gồm cả HS03 nhưng phần điểm sẽ là NULL vì học sinh này chưa có dữ liệu điểm. |
| RIGHT JOIN (RIGHT OUTER JOIN) | Lấy tất cả các bản ghi của bảng bên phải và các bản ghi tương ứng ở bảng bên trái. Nếu không khớp thì dữ liệu phía còn lại sẽ là NULL. | Truy vấn: SELECT * FROM HOCSINH RIGHT JOIN DIEM ON HOCSINH.MaHS = DIEM.MaHS → Tất cả dữ liệu trong bảng DIEM đều xuất hiện, kể cả khi một bản ghi điểm không có thông tin học sinh tương ứng. |
| FULL OUTER JOIN | Kết hợp toàn bộ dữ liệu của cả hai bảng. Các bản ghi khớp sẽ được ghép lại, còn bản ghi không khớp sẽ có phần dữ liệu thiếu là NULL. | Truy vấn: SELECT * FROM HOCSINH FULL OUTER JOIN DIEM ON HOCSINH.MaHS = DIEM.MaHS → Hiển thị tất cả học sinh và tất cả dữ liệu điểm, kể cả khi không khớp. |
| CROSS JOIN | Tạo ra tất cả các tổ hợp có thể giữa các bản ghi của hai bảng (tích Descartes). Không cần điều kiện liên kết. | Truy vấn: SELECT * FROM HOCSINH CROSS JOIN DIEM → Nếu bảng HOCSINH có 3 dòng và bảng DIEM có 3 dòng thì kết quả sẽ có 9 dòng. |
Qua những thông tin trên, ta thấy phép liên kết bảng giúp truy xuất và tổng hợp dữ liệu từ nhiều bảng khác nhau trong cơ sở dữ liệu. Tùy vào mục đích sử dụng, người dùng có thể lựa chọn loại Join phù hợp để đảm bảo kết quả truy vấn đầy đủ và chính xác. Các phép INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN và CROSS JOIN là những dạng liên kết phổ biến thường được sử dụng trong các hệ quản trị cơ sở dữ liệu.
| Thành phần | Cú pháp |
| Cấu trúc chung | SELECT tên_cột |
| FROM bảng1 | |
| [LOẠI_JOIN] bảng2 | |
| ON bảng1.trường_khóa = bảng2.trường_khóa | |
| [WHERE điều_kiện] | |
| [GROUP BY tên_cột] | |
| [HAVING điều_kiện] | |
| [ORDER BY tên_cột] |
Trong đó:
• SELECT: xác định các cột dữ liệu cần hiển thị trong kết quả truy vấn.
• FROM: chỉ ra bảng chính được sử dụng trong truy vấn.
• JOIN: xác định bảng thứ hai cần liên kết.
• ON: mô tả điều kiện liên kết giữa hai bảng, thường dựa trên trường khóa.
• WHERE: dùng để lọc các bản ghi thỏa mãn điều kiện.
• GROUP BY: nhóm các bản ghi có cùng giá trị ở một hoặc nhiều cột.
• HAVING: đặt điều kiện cho các nhóm dữ liệu sau khi đã nhóm.
• ORDER BY: sắp xếp kết quả truy vấn theo một hoặc nhiều cột.
Ví dụ, với hai bảng HOCSINH(MaHS, TenHS, Lop) và DIEM(MaHS, Mon, Diem), có thể viết câu lệnh JOIN như sau:
SELECT HOCSINH.MaHS, TenHS, Mon, Diem FROM HOCSINH INNER JOIN DIEM ON HOCSINH.MaHS = DIEM.MaHS;
Câu lệnh trên thực hiện việc liên kết bảng HOCSINH và bảng DIEM thông qua trường MaHS, sau đó hiển thị mã học sinh, tên học sinh, môn học và điểm của những học sinh có dữ liệu ở cả hai bảng.
Nhìn chung, việc sử dụng JOIN trong câu lệnh SELECT cho phép người dùng truy vấn dữ liệu từ nhiều bảng khác nhau trong cơ sở dữ liệu một cách linh hoạt và hiệu quả. Điều này đặc biệt hữu ích khi thông tin được tổ chức thành nhiều bảng có liên quan với nhau thông qua các trường khóa.
3.2. Lọc dữ liệu với WHERE
Trong SQL, mệnh đề WHERE được sử dụng để lọc các bản ghi thỏa mãn một điều kiện xác định. Khi thực hiện truy vấn, hệ quản trị cơ sở dữ liệu sẽ chỉ trả về những dòng dữ liệu đáp ứng điều kiện được đặt ra trong mệnh đề WHERE. Nhờ đó, người dùng có thể tìm kiếm và khai thác thông tin chính xác hơn từ bảng dữ liệu.
Cấu trúc cơ bản của câu lệnh SELECT có sử dụng WHERE:
| Thành phần | Cú pháp |
| Cấu trúc chung | SELECT tên_cột |
| FROM tên_bảng | |
| WHERE điều_kiện; |
Trong đó:
• SELECT: xác định các cột cần hiển thị.
• FROM: xác định bảng chứa dữ liệu.
• WHERE: đặt điều kiện để lọc dữ liệu.
Ví dụ:
SELECT TenHS, Lop FROM HOCSINH WHERE Lop = '11A';
Câu lệnh trên sẽ hiển thị tên và lớp của những học sinh thuộc lớp 11A.
| Cách lọc | Mô tả | Ví dụ |
| So sánh | Dùng các toán tử so sánh như =, >, <, >=, <=, <> để lọc dữ liệu | SELECT * FROM DIEM WHERE Diem >= 8; |
| Kết hợp điều kiện | Dùng các toán tử logic AND, OR, NOT để kết hợp nhiều điều kiện | SELECT * FROM HOCSINH WHERE Lop = '11A' AND TenHS = 'An'; |
| Tìm trong khoảng | Dùng BETWEEN để lọc dữ liệu trong một khoảng giá trị | SELECT * FROM DIEM WHERE Diem BETWEEN 7 AND 9; |
| Tìm trong danh sách | Dùng IN để kiểm tra giá trị thuộc một tập hợp | SELECT * FROM HOCSINH WHERE Lop IN ('11A','11B'); |
| Tìm theo mẫu | Dùng LIKE để tìm dữ liệu theo mẫu ký tự | SELECT * FROM HOCSINH WHERE TenHS LIKE 'A%'; |
| Kiểm tra giá trị rỗng | Dùng IS NULL hoặc IS NOT NULL | SELECT * FROM DIEM WHERE Diem IS NULL; |
3.3. Sử dụng WHERE kết hợp với các hàm tổng hợp
Ngoài việc lọc dữ liệu thông thường, SQL còn cung cấp các hàm tổng hợp (Aggregate Functions) để tính toán trên nhiều bản ghi. Một số hàm phổ biến gồm:
| Hàm | Chức năng | Ví dụ |
| COUNT() | Đếm số bản ghi | SELECT COUNT(*) FROM HOCSINH WHERE Lop = '11A'; |
| SUM() | Tính tổng giá trị của một cột số | SELECT SUM(Diem) FROM DIEM WHERE Mon = 'Tin'; |
| AVG() | Tính giá trị trung bình | SELECT AVG(Diem) FROM DIEM WHERE Mon = 'Tin'; |
| MAX() | Tìm giá trị lớn nhất | SELECT MAX(Diem) FROM DIEM; |
| MIN() | Tìm giá trị nhỏ nhất | SELECT MIN(Diem) FROM DIEM; |
Ví dụ:
SELECT AVG(Diem) FROM DIEM WHERE Mon = 'Tin';
Câu lệnh trên sẽ tính điểm trung bình môn Tin của các học sinh trong bảng DIEM.
Mệnh đề WHERE đóng vai trò quan trọng trong việc lọc và truy xuất dữ liệu theo điều kiện cụ thể. Khi kết hợp với các toán tử logic và hàm tổng hợp như COUNT, SUM, AVG, MAX, MIN, người dùng có thể khai thác thông tin từ cơ sở dữ liệu một cách linh hoạt và hiệu quả hơn.
3.4. Mệnh đề GROUP BY
Trong SQL, GROUP BY được sử dụng để nhóm các bản ghi có cùng giá trị trong một hoặc nhiều cột thành từng nhóm. Sau khi dữ liệu được nhóm lại, người dùng có thể áp dụng các hàm tổng hợp như COUNT(), SUM(), AVG(), MAX(), MIN() để thực hiện các phép tính trên từng nhóm dữ liệu.
Việc sử dụng GROUP BY giúp tổng hợp và phân tích dữ liệu trong bảng một cách hiệu quả, đặc biệt khi cần thống kê hoặc báo cáo.
| Thành phần | Cú pháp |
| Cấu trúc chung | SELECT tên_cột, hàm_tổng_hợp |
| FROM tên_bảng | |
| GROUP BY tên_cột; |
Trong đó:
• SELECT: xác định cột cần hiển thị hoặc kết quả tính toán.
• FROM: chỉ ra bảng chứa dữ liệu.
• GROUP BY: nhóm các bản ghi theo một hoặc nhiều cột.
Lưu ý: Những cột xuất hiện trong SELECT mà không nằm trong hàm tổng hợp thì phải được liệt kê trong GROUP BY.
Ví dụ minh họa
Giả sử có bảng DIEM
| MaHS | Mon | Diem |
| HS01 | Tin | 9 |
| HS01 | Toan | 8 |
| HS02 | Tin | 7 |
| HS02 | Toan | 6 |
| HS03 | Tin | 9 |
Ví dụ 1: Đếm số môn học của mỗi học sinh
SELECT MaHS, COUNT(Mon) FROM DIEM GROUP BY MaHS;
Kết quả sẽ hiển thị mỗi MaHS cùng với số môn học mà học sinh đó có trong bảng dữ liệu.
| MaHS | COUNT(Mon) |
| HS01 | 2 |
| HS02 | 2 |
| HS03 | 1 |
Ví dụ 2: Tính điểm trung bình của mỗi học sinh
SELECT MaHS, AVG(Diem) FROM DIEM GROUP BY MaHS;
Câu lệnh trên nhóm dữ liệu theo MaHS, sau đó tính điểm trung bình của từng học sinh.
| MaHS | AVG(Diem) |
| HS01 | 8.5 |
| HS02 | 6.5 |
| HS03 | 9 |
Ví dụ 3: Tính tổng điểm theo từng môn
SELECT Mon, SUM(Diem) FROM DIEM GROUP BY Mon;
Kết quả sẽ hiển thị tổng điểm của mỗi môn học trong bảng.
| Mon | SUM(Diem) |
| Tin | 25 |
| Toan | 14 |
GROUP BY có thể được sử dụng với nhiều cột để tạo ra các nhóm chi tiết hơn.
Ví dụ:
SELECT MaHS, Mon, AVG(Diem) FROM DIEM GROUP BY MaHS, Mon;
Câu lệnh này sẽ nhóm dữ liệu theo MaHS và Mon, sau đó tính điểm trung bình của từng học sinh cho từng môn học.
| MaHS | Mon | AVG(Diem) |
| HS01 | Tin | 9 |
| HS01 | Toan | 8 |
| HS02 | Tin | 7 |
| HS02 | Toan | 6 |
| HS03 | Tin | 9 |
3.5. Mệnh đề HAVING
Trong SQL, HAVING được sử dụng để đặt điều kiện cho các nhóm dữ liệu sau khi đã thực hiện phép nhóm bằng GROUP BY. Khác với WHERE, vốn dùng để lọc các bản ghi trước khi nhóm, HAVING được áp dụng sau khi dữ liệu đã được nhóm và các hàm tổng hợp đã được tính toán.
Mệnh đề HAVING thường được dùng cùng với các hàm tổng hợp như COUNT(), SUM(), AVG(), MAX(), MIN() để lọc các nhóm dữ liệu thỏa mãn điều kiện nhất định.
| Thành phần | Cú pháp |
| Cấu trúc chung | SELECT tên_cột, hàm_tổng_hợp |
| FROM tên_bảng | |
| GROUP BY tên_cột | |
| HAVING điều_kiện; |
Trong đó:
• SELECT: xác định các cột hoặc kết quả tính toán cần hiển thị.
• FROM: chỉ ra bảng chứa dữ liệu.
• GROUP BY: nhóm các bản ghi có cùng giá trị trong một hoặc nhiều cột.
• HAVING: đặt điều kiện để lọc các nhóm dữ liệu sau khi đã nhóm.
Ví dụ minh họa
Giả sử có bảng DIEM
| MaHS | Mon | Diem |
| HS01 | Tin | 9 |
| HS01 | Toan | 8 |
| HS02 | Tin | 7 |
| HS02 | Toan | 6 |
| HS03 | Tin | 9 |
Ví dụ 1: Tìm học sinh có số môn học lớn hơn 1
SELECT MaHS, COUNT(Mon) FROM DIEM GROUP BY MaHS HAVING COUNT(Mon) > 1;
Câu lệnh trên sẽ:
1. Nhóm dữ liệu theo MaHS.
2. Đếm số môn của mỗi học sinh.
3. Chỉ hiển thị những học sinh có hơn 1 môn học.
| MaHS | COUNT(Mon) |
| HS01 | 2 |
| HS02 | 2 |
Ví dụ 2: Tìm học sinh có điểm trung bình lớn hơn 7
SELECT MaHS, AVG(Diem) FROM DIEM GROUP BY MaHS HAVING AVG(Diem) > 7;
Câu lệnh trên nhóm dữ liệu theo MaHS, sau đó tính điểm trung bình và chỉ hiển thị những học sinh có điểm trung bình lớn hơn 7.
| MaHS | AVG(Diem) |
| HS01 | 8.5 |
| HS03 | 9 |
3.6. Kết hợp WHERE và HAVING
Trong nhiều trường hợp, WHERE và HAVING có thể được sử dụng cùng nhau trong một truy vấn.
Ví dụ:
SELECT Mon, ROUND(AVG(Diem), 2) FROM DIEM WHERE Diem >= 5 GROUP BY Mon HAVING AVG(Diem) > 7;
Ý nghĩa của truy vấn:
1. WHERE lọc các bản ghi có điểm ≥ 5.
2. GROUP BY nhóm dữ liệu theo môn học.
3. HAVING chỉ hiển thị những môn có điểm trung bình lớn hơn 7.
4. ROUND(nguồn_dữ_liệu, 2) làm tròn tới hàng thập phân phần trăm.
| Mon | AVG(Diem) |
| Tin | 8.33 |
3.7. Mệnh đề ORDER BY
Trong SQL, ORDER BY được sử dụng để sắp xếp kết quả truy vấn theo một hoặc nhiều cột. Việc sắp xếp có thể thực hiện theo thứ tự tăng dần hoặc giảm dần, giúp dữ liệu hiển thị rõ ràng và dễ theo dõi hơn.
Cấu trúc câu lệnh
| Thành phần | Cú pháp |
| Cấu trúc chung | SELECT tên_cột |
| FROM tên_bảng | |
| ORDER BY tên_cột [ASC/DESC] |
Trong đó:
• ASC (Ascending): sắp xếp tăng dần (mặc định).
• DESC (Descending): sắp xếp giảm dần.
Ví dụ
Ví dụ 1: Sắp xếp học sinh theo tên
SELECT TenHS, Lop FROM HOCSINH ORDER BY TenHS ASC;
Câu lệnh trên hiển thị danh sách học sinh và sắp xếp tên học sinh theo thứ tự tăng dần.
| TenHS | Lop |
| An | 11A |
| Bình | 11A |
| Chi | 11B |
Ví dụ 2: Sắp xếp điểm từ cao xuống thấp
SELECT MaHS, Mon, Diem FROM DIEM ORDER BY Diem DESC;
Câu lệnh này hiển thị dữ liệu điểm và sắp xếp theo điểm giảm dần.
| MaHS | Mon | Diem |
| HS01 | Tin | 9 |
| HS03 | Tin | 9 |
| HS01 | Toan | 8 |
| HS02 | Tin | 7 |
| HS02 | Toan | 6 |
Mệnh đề ORDER BY giúp sắp xếp kết quả truy vấn theo thứ tự mong muốn. Nó thường được sử dụng cùng với các mệnh đề khác như WHERE, GROUP BY hoặc HAVING để trình bày dữ liệu một cách rõ ràng và dễ phân tích hơn.
3.8. Mệnh đề CASCADE và ứng dụng với ON UPDATE / DELETE CASCADE
Trong cơ sở dữ liệu quan hệ, các bảng thường liên kết với nhau thông qua khóa ngoại (Foreign Key). Khi dữ liệu ở bảng cha thay đổi hoặc bị xóa, có thể phát sinh vấn đề mất tính toàn vẹn dữ liệu nếu các bảng liên quan vẫn giữ giá trị cũ.
Để giải quyết vấn đề này, SQL cung cấp mệnh đề CASCADE, cho phép các thay đổi ở bảng cha tự động lan truyền sang bảng con. CASCADE thường được sử dụng cùng với các tùy chọn ON UPDATE CASCADE và ON DELETE CASCADE khi định nghĩa khóa ngoại.
a) ON UPDATE CASCADE
ON UPDATE CASCADE được sử dụng để đảm bảo rằng khi giá trị khóa chính ở bảng cha bị thay đổi, các giá trị khóa ngoại tương ứng ở bảng con cũng tự động được cập nhật theo.
Ví dụ:
CREATE TABLE HOCSINH ( MaHS VARCHAR(10) PRIMARY KEY, TenHS VARCHAR(50) );
CREATE TABLE DIEM ( MaHS VARCHAR(10), Mon VARCHAR(20), Diem INT, FOREIGN KEY (MaHS) REFERENCES HOCSINH(MaHS) ON UPDATE CASCADE );
Trong trường hợp này:
• Nếu MaHS trong bảng HOCSINH được cập nhật (ví dụ từ HS01 thành HS10),
• Thì các giá trị MaHS tương ứng trong bảng DIEM cũng sẽ tự động cập nhật thành HS10.
Điều này giúp đảm bảo các bảng vẫn giữ được mối liên kết chính xác.
b) ON DELETE CASCADE
ON DELETE CASCADE được sử dụng khi muốn tự động xóa các bản ghi liên quan ở bảng con nếu bản ghi tương ứng ở bảng cha bị xóa.
Ví dụ:
CREATE TABLE DIEM ( MaHS VARCHAR(10), Mon VARCHAR(20), Diem INT, FOREIGN KEY (MaHS) REFERENCES HOCSINH(MaHS) ON DELETE CASCADE );
Trong trường hợp này:
• Nếu một học sinh bị xóa khỏi bảng HOCSINH,
• Tất cả các bản ghi điểm của học sinh đó trong bảng DIEM cũng sẽ tự động bị xóa.
Việc sử dụng CASCADE giúp duy trì tính toàn vẹn dữ liệu giữa các bảng trong cơ sở dữ liệu. Khi áp dụng ON UPDATE CASCADE và ON DELETE CASCADE, các thay đổi ở bảng cha sẽ được tự động cập nhật hoặc xóa ở bảng con, từ đó giảm sai sót và giúp quản lý dữ liệu hiệu quả hơn.
3.9. Những ứng dụng khác của CASCADE
Ngoài việc sử dụng với ON UPDATE CASCADE và ON DELETE CASCADE, mệnh đề CASCADE còn được áp dụng trong một số thao tác quản lí cơ sở dữ liệu khác nhằm tự động thực hiện các hành động liên quan đến các đối tượng phụ thuộc.
| Ứng dụng | Mô tả |
| DROP ... CASCADE | Khi xóa một bảng hoặc đối tượng trong cơ sở dữ liệu, các đối tượng phụ thuộc vào nó (như khóa ngoại, view, ràng buộc liên quan) cũng sẽ bị xóa theo. |
| TRUNCATE ... CASCADE | Khi xóa toàn bộ dữ liệu của một bảng, dữ liệu ở các bảng có quan hệ phụ thuộc cũng có thể bị xóa theo nếu sử dụng CASCADE. |
| ALTER TABLE ... DROP CASCADE | Khi xóa một ràng buộc (constraint) hoặc cột, các đối tượng phụ thuộc vào ràng buộc đó cũng được xóa tự động. |
Nhìn chung, CASCADE giúp hệ quản trị cơ sở dữ liệu tự động xử lý các đối tượng hoặc dữ liệu liên quan, từ đó giảm thao tác thủ công và đảm bảo tính nhất quán của hệ thống dữ liệu.
3.10. Mệnh đề AS
Trong SQL, AS được sử dụng để đặt bí danh (alias) cho tên cột hoặc tên bảng trong kết quả truy vấn. Việc đặt bí danh giúp tên cột trong bảng kết quả dễ hiểu hơn hoặc rút gọn tên bảng khi viết truy vấn.
Bí danh chỉ tồn tại trong kết quả truy vấn, không làm thay đổi tên cột hoặc tên bảng trong cơ sở dữ liệu.
Cấu trúc câu lệnh
| Thành phần | Cú pháp |
| Đặt bí danh cho cột | SELECT tên_cột AS bí_danh FROM tên_bảng; |
| Đặt bí danh cho bảng | SELECT ... FROM tên_bảng AS bí_danh; |
Trong đó:
• AS: từ khóa dùng để đặt bí danh.
• bí_danh: tên mới được hiển thị trong kết quả truy vấn.
Ví dụ minh họa
Giả sử có bảng DIEM
| MaHS | Mon | Diem |
| HS01 | Tin | 9 |
| HS01 | Toan | 8 |
| HS02 | Tin | 7 |
| HS02 | Toan | 6 |
| HS03 | Tin | 9 |
Ví dụ 1: Đặt bí danh cho cột
SELECT MaHS AS MaHocSinh, Diem AS DiemSo
FROM DIEM;
Câu lệnh trên đổi tên cột MaHS thành MaHocSinh và Diem thành DiemSo trong bảng kết quả.
Kết quả:
| MaHocSinh | DiemSo |
| HS01 | 9 |
| HS01 | 8 |
| HS02 | 7 |
| HS02 | 6 |
| HS03 | 9 |
Ví dụ 2: Đặt bí danh cho kết quả tính toán
SELECT MaHS, AVG(Diem) AS DiemTrungBinh
FROM DIEM
GROUP BY MaHS;
Câu lệnh trên tính điểm trung bình của mỗi học sinh và đặt tên cột kết quả là DiemTrungBinh.
Kết quả:
| MaHS | DiemTrungBinh |
| HS01 | 8.5 |
| HS02 | 6.5 |
| HS03 | 9 |
Ví dụ 3: Đặt bí danh cho bảng
SELECT D.MaHS, D.Mon, D.Diem
FROM DIEM AS D;
Trong câu lệnh này, bảng DIEM được đặt bí danh là D, giúp câu lệnh ngắn gọn và dễ viết hơn, đặc biệt khi truy vấn với nhiều bảng.
Mệnh đề AS giúp đặt tên thay thế cho cột hoặc bảng trong truy vấn SQL. Điều này giúp kết quả truy vấn dễ hiểu hơn và câu lệnh SQL gọn gàng hơn, đặc biệt khi làm việc với các phép tính hoặc truy vấn nhiều bảng.
4. KIỂM SOÁT QUYỀN TRUY CẬP
Thành phần DCL của SQL cung cấp các câu truy vấn kiểm soát quyền người dùng đối với CSDL, tóm tắt trong bảng 14.5.
Bảng 14.5. Câu truy vấn kiểm soát quyền người dùng
| Mẫu câu truy vấn | Ý nghĩa |
| GRANT | Cấp quyền cho người dùng |
| REVOKE | Thu hồi quyền đối với người dùng |
Ví dụ:
GRANT select
ON music.*
TO guest;
Cấp quyền dùng truy vấn SELECT đối với tất cả các bảng trong CSDL music cho người dùng guest.
REVOKE create, alter
ON music.bannhac
FROM mod;
Thu hồi quyền CREATE và ALTER cho bảng bannhac trong CSDL music đối với người dùng mod.
PHẦN BÀI TẬP
Bài 1: Cho các bảng sau:
Bảng student_data
| id | full_name | student_code | class_id | dob | gender | status |
| 1 | Mai Nguyễn Châu An | 110002 | 1 | active | ||
| 2 | Vũ Khánh An | 110006 | 1 | active | ||
| 3 | Đỗ Nguyễn Minh An | 110007 | 2 | active | ||
| 4 | Trần Nguyễn Nhật An | 110008 | 3 | active | ||
| 5 | Nguyễn Thị Phúc An | 110010 | 2 | active | ||
| 6 | Phạm Nguyễn Phúc An | 110011 | 4 | active | ||
| 7 | Trần Bảo Quốc An | 110014 | 5 | active | ||
| 8 | Nguyễn Ngọc Thuận An | 110016 | 6 | active | ||
| 9 | Nguyễn Ngọc Xuân An | 110017 | 7 | active | ||
| 10 | Nguyễn Hoàng Ân | 110018 | 8 | active | ||
| 11 | Nguyễn Trần Hà Anh | 110021 | 2 | active | ||
| 12 | Hoàng Lê Hoài Anh | 110023 | 9 | active | ||
| 13 | Nguyễn Lê Hồng Anh | 110026 | 10 | active | ||
| 14 | Nguyễn Ngọc Kim Anh | 110027 | 3 | active | ||
| 15 | Trần Thị Kim Anh | 110028 | 11 | active | ||
| 16 | Nguyễn Kỳ Lâm Anh | 110029 | 10 | active | ||
| 17 | Phạm Lưu Minh Anh | 110030 | 12 | active | ||
| 18 | Trần Nhật Nguyên Anh | 110032 | 13 | active | ||
| 19 | Nguyễn Nhật Anh | 110033 | 9 | active | ||
| 20 | Nguyễn Phương Anh | 110034 | 4 | active |
Biết rằng kiểu dữ liệu của trường id là INT (PRIMARY KEY), full_name là VARCHAR(128), student_code là CHAR(6), class_id là INT (FOREIGN KEY tới bảng Class_data), dob là VARCHAR(36), gender là BOOLEAN, status là VARCHAR(16)
Bảng Class_data
| id | name | school_id |
| 1 | 11A1 | 1 |
| 2 | 11A16 | 1 |
| 3 | 11A7 | 1 |
| 4 | 11A5 | 1 |
| 5 | 11A9 | 1 |
| 6 | 11A3 | 1 |
| 7 | 11A6 | 1 |
| 8 | 11A14 | 1 |
| 9 | 11A11 | 1 |
| 10 | 11A13 | 1 |
| 11 | 11A8 | 1 |
| 12 | 11A15 | 1 |
| 13 | 11A4 | 1 |
| 14 | 11A12 | 1 |
Biết rằng kiểu dữ liệu của trường id là INT (PRIMARY KEY), name là VARCHAR(18), school_id là INT
Hãy
a) Viết câu lệnh truy xuất id, full_name và tên lớp (name trong bảng Class_data) của các học sinh có class_id là 4
b) Viết câu lệnh truy xuất toàn bộ bảng student_data kèm tên lớp từ Class_data và sắp xếp theo thứ tự class_id tăng dần
c) Viết câu lệnh truy xuất danh sách học sinh (bao gồm id, full_name, student_code, tên lớp từ Class_data) có tên kết thúc bằng “An”
DÙNG CÁC NGỮ LIỆU SAU ĐỂ THỰC HÀNH BÀI 2 VÀ 3
Bối cảnh
Công ty TTKT muốn tăng cường bảo mật hệ thống quản lý nội bộ. Bộ phận IT yêu cầu phân tích dữ liệu đăng nhập của người dùng nhằm:
• Phát hiện đăng nhập bất thường
• Theo dõi IP đáng ngờ
• Kiểm soát thiết bị truy cập
• Thống kê hoạt động người dùng
Hệ thống lưu trữ dữ liệu đăng nhập trong bảng login_logs.
Cấu trúc dữ liệu
Công ty TTKT có bảng:
Bảng login_logs
Bảng login_logs lưu thông tin các lần đăng nhập vào hệ thống. Biết rằng:
• id là INT (PRIMARY KEY)
• user_id là INT (FOREIGN KEY tới bảng users)
• login_time là DATETIME
• ip_address là VARCHAR(45)
• user_agent là VARCHAR(255)
• source là VARCHAR(20) (ví dụ: 'web', 'mobile', 'unknown')
Bài 2: Hãy giúp administration thực hiện một số truy vấn cơ bản
a) Viết câu lệnh SQL lấy danh sách user_id, login_time, ip_address của các lần đăng nhập có source = 'web'.
b) Viết câu lệnh SQL lấy toàn bộ dữ liệu trong bảng login_logs và sắp xếp theo login_time giảm dần (mới nhất trước).
c) Viết câu lệnh SQL lấy danh sách các lần đăng nhập có địa chỉ IP là '113.182.131.218'.
Bài 3: Hãy giúp administration điều tra hệ thống có bất thường hay không
a) Viết câu lệnh SQL đếm số lần đăng nhập của từng user_id và trả về bảng gồm số lần và username (có sẵn trong users liên kết qua user_id).
b) Viết câu lệnh SQL tìm những user_id đăng nhập từ nhiều hơn 1 địa chỉ IP khác nhau và trả về bảng với chỉ các username (có sẵn trong users liên kết qua user_id).
c) Viết câu lệnh SQL tìm những địa chỉ IP được sử dụng bởi từ 2 người dùng trở lên và trả về bảng dạng gộp (dùng GROUP BY).
d) Viết câu lệnh SQL lấy danh sách các lần đăng nhập có source = 'unknown' và trả về đầy đủ các cột như bảng login_logs gốc.
ĐÁP ÁN PHẦN BÀI TẬP THỰC HÀNH SQL
BÀI 1
a) Viết câu lệnh truy xuất id, full_name và tên lớp (name trong bảng Class_data) của các học sinh có class_id là 4:
SELECT s.id, s.full_name, c.name AS class_name FROM student_data s INNER JOIN Class_data c ON s.class_id = c.id WHERE s.class_id = 4;
b) Viết câu lệnh truy xuất toàn bộ bảng student_data kèm tên lớp từ Class_data và sắp xếp theo thứ tự class_id tăng dần:
SELECT s.*, c.name AS class_name FROM student_data s LEFT JOIN Class_data c ON s.class_id = c.id ORDER BY s.class_id ASC;
c) Viết câu lệnh truy xuất danh sách học sinh (bao gồm id, full_name, student_, tên lớp từ Class_data) có tên kết thúc bằng “An”:
SELECT s.id, s.full_name, s.student_, c.name AS class_name FROM student_data s INNER JOIN Class_data c ON s.class_id = c.id WHERE s.full_name LIKE '%An';
BÀI 2
a) Viết câu lệnh SQL lấy danh sách user_id, login_time, ip_address của các lần đăng nhập có source = 'web':
SELECT user_id, login_time, ip_address FROM login_logs WHERE source = 'web';
b) Viết câu lệnh SQL lấy toàn bộ dữ liệu trong bảng login_logs và sắp xếp theo login_time giảm dần (mới nhất trước):
SELECT * FROM login_logs ORDER BY login_time DESC;
c) Viết câu lệnh SQL lấy danh sách các lần đăng nhập có địa chỉ IP là '113.182.131.218':
SELECT * FROM login_logs WHERE ip_address = '113.182.131.218';
BÀI 3
a) Viết câu lệnh SQL đếm số lần đăng nhập của từng user_id và trả về bảng gồm số lần và username:
SELECT u.username, COUNT(l.id) AS login_count FROM login_logs l INNER JOIN users u ON l.user_id = u.id GROUP BY l.user_id, u.username;
b) Viết câu lệnh SQL tìm những user_id đăng nhập từ nhiều hơn 1 địa chỉ IP khác nhau và trả về bảng với chỉ các username:
<>SELECT u.username FROM login_logs l INNER JOIN users u ON l.user_id = u.id GROUP BY l.user_id, u.username HAVING COUNT(DISTINCT l.ip_address) > 1;
c) Viết câu lệnh SQL tìm những địa chỉ IP được sử dụng bởi từ 2 người dùng trở lên và trả về bảng dạng gộp (dung GROUP BY):
SELECT ip_address, COUNT(DISTINCT user_id) AS total_users FROM login_logs GROUP BY ip_address HAVING COUNT(DISTINCT user_id) >= 2;
d) Viết câu lệnh SQL lấy danh sách các lần đăng nhập có source = 'unknown' và trả về đầy đủ các cột như bảng login_logs gốc:
SELECT * FROM login_logs WHERE source = 'unknown';
