Ngu ngơ học làm web (102) - mySQL - truy vấn có nhóm và thống kê

Tiếp theo của: Ngu ngơ học làm web (101) - mySQL – truy vấn đơn giản (tt)
-----

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
-----------
Xem thêm:
Tổng hợp các bài viết về Ngu ngơ học làm web