-----
Phần 103. mySQL – truy vấn con (sub query)
Xem và làm theo clip số 7 của thầy Nguyễn Anh Tuấn:
Truy vấn con là các truy vấn lồng nhau, chỉ liệt kê được một
cột (field).
Cách làm: tạo câu lệnh truy vấn con > tạo câu lệnh truy
vấn cha > lồng câu lệnh truy vấn con vào câu lệnh truy vấn cha.
Ví dụ,
1. Liệt kê loại món ăn chưa có món ăn nào
Sử dụng từ khóa distinct để loại bỏ các dòng tin trùng lặp
trong kết quả truy vấn.
Câu truy vấn con: liệt kê tất cả các loại món ăn có xuất
hiện trong bảng mon_an:
SELECT DISTINCT ma_loai
FROM mon_an
Câu truy vấn cha:
SELECT *
FROM loai_mon_an
WHERE ma_loai NOT IN (Câu truy vấn con)
Lồng câu truy vấn con vào câu truy vấn cha:
SELECT *
FROM loai_mon_an
WHERE ma_loai NOT IN (SELECT DISTINCT ma_loai FROM mon_an)
2. Liệt kê món ăn chưa có khách hàng nào mua
Tìm các món ăn đã có khách hàng mua.
Dựa vào bảng chi_tiet_mon_an.
Câu truy vấn con:
SELECT DISTINCT ma_mon FROM chi_tiet_hoa_don
Câu truy vấn cha:
SELECT ma_mon, ten_mon FROM mon_an
WHERE ma_mon NOT IN (câu truy vấn con)
Câu truy vấn kết quả:
SELECT ma_mon, ten_mon FROM mon_an
WHERE ma_mon NOT IN (SELECT DISTINCT ma_mon FROM chi_tiet_hoa_don)
3. Liệt kê món ăn có doanh thu cao nhất
Câu truy vấn con:
SELECT SUM(so_luong * don_gia) AS DoanhThu
FROM chi_tiet_hoa_don
GROUP BY ma_mon
ORDER BY 1 DESC
LIMIT 0, 1
Câu truy vấn kết quả:
SELECT m.ma_mon, ten_mon, SUM(so_luong * ct.don_gia) AS DoanhThu
FROM mon_an m INNER JOIN chi_tiet_hoa_don ct
ON m.ma_mon = ct.ma_mon
GROUP BY m.ma_mon, ten_mon
HAVING SUM(so_luong * ct.don_gia) = (SELECT SUM(so_luong *
don_gia) AS DoanhThu
FROM chi_tiet_hoa_don
GROUP BY ma_mon
ORDER BY 1 DESC
LIMIT 0, 1
)
4. Liệt kê thực đơn chưa có món ăn nào
Do trong cơ sở dữ liệu chưa có bảng thuc_don_mon_an, nên sẽ
tạo thêm bảng này.
thuc_don_mon_an
#
|
Name
|
Type
|
Collation
|
Attributes
|
Null
|
Default
|
Extra
|
1
|
ma_thuc_don
|
int(11)
|
No
|
None
|
|||
2
|
ma_mon
|
int(11)
|
No
|
None
|
|||
3
|
so_luong
|
int(11)
|
No
|
None
|
Lưu ý: ma_thuc_don và ma_mon là khóa ngoại, nên cần cấu hình
ràng buộc khóa ngoại cho hai thuộc tính này.
Cách làm: đầu tiên thiết lập thuộc tính INDEX cho hai cột
ma_thuc_don và ma_mon ở bảng thuc_don_mon_an.
Sau đó, chọn tab Structure, chọn tab Relation view, cấu hình
trong mục Foreign key constraints.
Nhập một số thông tin cho bảng thuc_don_mon_an.
Câu lệnh truy vấn:
SELECT * FROM thuc_don
WHERE ma_thuc_don NOT IN (SELECT DISTINCT ma_thuc_don FROM
thuc_don_mon_an)
5. Liệt kê các thực đơn chưa có khách hàng mua
SELECT * FROM thuc_don
WHERE ma_thuc_don NOT IN (
SELECT DISTINCT ma_thuc_don FROM thuc_don_mon_an
WHERE ma_mon IN (SELECT DISTINCT ma_mon FROM chi_tiet_hoa_don)
ORDER BY 1)
6. Liệt kê khách hàng chưa đặt món
SELECT * FROM khach_hang
WHERE ma_khach_hang NOT IN (SELECT ma_khach_hang FROM hoa_don)
7. Liệt kê danh sách món ăn có cùng loại món ăn với món ăn
có mã món ăn là 4
SELECT ma_loai, ma_mon, ten_mon FROM mon_an
WHERE ma_loai = (SELECT ma_loai FROM mon_an WHERE ma_mon=4)
8. Liệt kê các món ăn có đơn giá cao nhất theo từng loại món
ăn
SELECT ma_loai, ten_mon, ma_mon, don_gia FROM `mon_an` as m
WHERE don_gia = (SELECT max(don_gia) FROM mon_an WHERE ma_loai =
m.ma_loai)
ORDER BY 1
-----------
Cập nhật 7/2/2017
-----------