-----
Phần 102. mySQL – truy vấn có nhóm và thống kê
Để ý là mấy phần này mới dừng lại ở mức làm theo để quen với
cơ sở dữ liệu. Để có thể làm việc tốt với cơ sở dữ liệu cần phải đọc các tài
liệu lý thuyết bài bản về nó. Lúc đó sẽ hiểu tại sao lại phải tách ra thành nhiều
bảng, tại sao lại phải có khóa chính, khóa ngoại, tại sao phải chuẩn hóa cơ sở
dữ liệu, bản chất của các lệnh truy vấn, WHERE, GROUP BY là gì…v.v.
Xem và làm theo clip số 6 của thầy Nguyễn Anh Tuấn:
1. Thống kê tổng số Món ăn theo Loại, gồm các thông tin: Tên
Loại món, tổng số món ăn, sắp tăng theo tổng số sản phẩm.
Thống kê tổng số Món ăn theo Loại: hiểu nôm na là thống kê
xem trong cơ sở dữ liệu hiện tại, mỗi loại món ăn có bao nhiêu món ăn? Nghĩa là
đếm xem mỗi loại món ăn có bao nhiêu món?
Cách làm: dựa vào bảng mon_an, với mỗi loại món ăn sẽ đếm
món ăn dựa vào Mã món. Vì cần lấy Tên Loại món ăn nên cần thêm thông tin từ
bảng loai_mon_an.
Tìm hiểu thêm về kết bảng (inner join) tại đây: http://freetuts.net/inner-join-trong-mysql-341.html
Inner join là phép tích hai bảng dựa trên điều kiện.
Cú pháp của inner join:
SELECT column_list
FROM t1 INNER JOIN t2 ON join_condition
WHERE where_conditions;
Trong đó,
- t1 và t2 là hai bảng cần kết
- join_condition1 là điều kiện để kết, nếu điều kiện trả về
TRUE thì mẩu tin đó sẽ được chọn
Dùng alias để đặt tên mới (tạm thời) cho bảng và cột. Đọc
thêm tại đây: http://freetuts.net/su-dung-as-alias-trong-mysql-338.html
Lệnh sau để hiển thị tất cả ten_loai và ma_mon:
SELECT ten_loai, ma_mon
FROM loai_mon_an loai INNER JOIN mon_an mon ON loai.ma_loai =
mon.ma_loai
Thực hiện nhóm trên ten_loai và đếm theo ma_mon:
SELECT ten_loai, count(ma_mon)
FROM loai_mon_an loai INNER JOIN mon_an mon ON loai.ma_loai =
mon.ma_loai
GROUP BY ten_loai
Sử dụng alias cho cột count(ma_mon):
SELECT ten_loai, count(ma_mon) TongSoMon
FROM loai_mon_an loai INNER JOIN mon_an mon ON loai.ma_loai =
mon.ma_loai
GROUP BY ten_loai
Sắp tăng theo tổng số sản phẩm (TongSoMon):
SELECT ten_loai, count(ma_mon) TongSoMon
FROM loai_mon_an loai INNER JOIN mon_an mon ON loai.ma_loai =
mon.ma_loai
GROUP BY ten_loai
ORDER BY TongSoMon
Hoặc group by theo cột số 2,
SELECT ten_loai, count(ma_mon) TongSoMon
FROM loai_mon_an loai INNER JOIN mon_an mon ON loai.ma_loai =
mon.ma_loai
GROUP BY ten_loai
ORDER BY 2
2. Cho biết đơn giá trung bình của món ăn theo từng Loại món
ăn.
SELECT loai.ma_loai, ten_loai, ROUND(AVG(don_gia),1)
DonGiaTrungBinh
FROM loai_mon_an loai INNER JOIN mon_an mon ON loai.ma_loai =
mon.ma_loai
GROUP BY loai.ma_loai, ten_loai
Trong đó,
- AVG: hàm tính trung bình
- ROUND(x,y) hàm làm tròn số x, lấy y chữ số phần thập phân.
3. Cho biết món ăn có đơn giá thấp nhất theo từng loại món
ăn.
SELECT loai.ma_loai, ten_loai, MIN(don_gia) DonGiaThapNhat
FROM loai_mon_an loai INNER JOIN mon_an mon ON loai.ma_loai =
mon.ma_loai
GROUP BY loai.ma_loai, ten_loai
Đơn giá cao nhất:
SELECT loai.ma_loai, ten_loai, MAX(don_gia) DonGiaCaoNhat
FROM loai_mon_an loai INNER JOIN mon_an mon ON loai.ma_loai =
mon.ma_loai
GROUP BY loai.ma_loai, ten_loai
4. Cho biết tổng giá tiền và số món ăn của món ăn có đơn giá
trong khoảng 50.000 VNĐ đến 100.000 VNĐ theo từng loại món ăn.
SELECT loai.ma_loai, ten_loai, SUM(don_gia) TongTien,
COUNT(ma_mon) SoMon
FROM loai_mon_an loai INNER JOIN mon_an mon ON loai.ma_loai =
mon.ma_loai
WHERE don_gia BETWEEN 50000 AND 100000
GROUP BY loai.ma_loai, ten_loai
5. Thống kê hóa đơn gồm các thông tin sau: số hóa đơn, ngày
đặt, tổng số món ăn, tổng thành tiền.
Mối quan hệ giữa bảng hoa_don và chi_tiet_hoa_don là gì? Mỗi
lần mua hàng sẽ có một hóa đơn, như vậy, một khách hàng có thể có nhiều hóa
đơn, một hóa đơn có thể có nhiều món ăn. Để quản lý các thông tin chi tiết khác
của hóa đơn, sẽ tạo thêm bảng chi_tiet_hoa_don.
SELECT hd.ma_hoa_don, ngay_dat, COUNT(ma_mon) AS TongMon,
SUM(so_luong*don_gia) TongThanhTien
FROM `hoa_don` hd INNER JOIN chi_tiet_hoa_don cthd ON
hd.ma_hoa_don = cthd.ma_hoa_don
GROUP BY ma_hoa_don, ngay_dat
Làm thêm, chỉ xem các hóa đơn có số món từ 2 trở lên.
Chú ý: WHERE sẽ thực hiện lọc trước khi nhóm, HAVING sẽ thực
hiện lọc sau khi nhóm.
SELECT hd.ma_hoa_don, ngay_dat, COUNT(ma_mon) AS TongMon,
SUM(so_luong*don_gia) TongThanhTien
FROM `hoa_don` hd INNER JOIN chi_tiet_hoa_don cthd ON
hd.ma_hoa_don = cthd.ma_hoa_don
GROUP BY ma_hoa_don, ngay_dat
HAVING COUNT(ma_mon) >= 2
6. Cho biết đơn giá trung bình món ăn thuộc loại món ăn là
“bún, mì, miến, phở”.
Vì dữ liệu nhập hơi khác, nên sẽ thay “bún, mì, miến, phở”
bằng “cơm”.
SELECT ten_loai, ROUND(AVG(don_gia),1) AS ĐGTB
FROM loai_mon_an AS loai, mon_an AS mon
WHERE loai.ma_loai = mon.ma_loai AND ten_loai = "Cơm"
GROUP BY ten_loai
-----------
Cập nhật 6/2/2017
-----------