Bài 29. Hàm Kết Hợp và Gom Nhóm,
Chia sẻ bởi Trần Thị Như Trang |
Ngày 29/04/2019 |
57
Chia sẻ tài liệu: Bài 29. Hàm Kết Hợp và Gom Nhóm, thuộc Bài giảng khác
Nội dung tài liệu:
BÀI 29:
HÀM KẾT HỢP VÀ GOM NHÓM, MỘT SO TRUY VẤN KHÁC
29.1. Hàm kết hợp
29.1.1. Hàm count(), min(), max(), sum(), avg()
Cú pháp của hàm COUNT:
SELECT COUNT(tên_cột) FROM tên_bảng:
Hàm COUNT(*):
Hàm COUNT(*) trả về số lượng các dòng được chọn ở trong bảng.
Ví dụ ta có bảng Persons như sau:
29.1.1. Hàm count(), min(), max(), sum(), avg()
Câu lệnh sau sẽ trả về số lượng các dòng trong bảng:
SELECT COUNT(*) FROM Persons và kết quả trả về sẽ là : 3
Câu lệnh sau sẽ trả về số lượng những người lớn hơn 20 tuổi:
SELECT COUNT(*) FROM Persons WHERE Age > 20
kết quả trả về sẽ là: 2
Hàm COUNT(column):
Hàm COUNT(column) sẽ trả về số lượng các dòng có giá trị khác NULL ở cột được chỉ định.
29.1.1. Hàm count(), min(), max(), sum(), avg()
Ví dụ ta có bảng Persons như sau:
Câu lệnh sau sẽ trả về số lượng những người mà cột Age trong bảng không rỗng:
SELECT COUNT(Age) FROM Persons
và kết quả trả về sẽ là: 2
29.1.1. Hàm count(), min(), max(), sum(), avg()
Mệnh đề COUNT DISTINCT
Lưu ý: Các ví dụ dưới đây chỉ hoạt động với CSDL Oracle và MS SQL Server, không hoạt động trên MS Access (chưa thử nhiệm với các hệ CSDL khác!)
Từ khoá DISTINCT và COUNT có thể được dùng chung với nhau để đếm số lượng các kết quả không trùng nhau.
Cú pháp như sau:
SELECT COUNT(DISTINCT column(s)) FROM table
29.1.1. Hàm count(), min(), max(), sum(), avg()
Ví dụ ta có bảng Orders như sau:
Câu lệnh SQL sau:
SELECT COUNT(DISTINCT Company) FROM Orders
sẽ trả về kết quả là: 3
29.1.1. Hàm count(), min(), max(), sum(), avg()
SQL có sẵn khá nhiều hàm để thực hiện đếm và tính toán.
Cú pháp:
Cú pháp để gọi hàm trong câu lệnh SQL như sau:
SELECT function(tên_cột) FROM tên_bảng
29.1.1. Hàm count(), min(), max(), sum(), avg()
SQL có sẵn khá nhiều hàm để thực hiện đếm Bảng dữ liệu chúng ta sẽ dùng trong các ví sụ tiếp theo:
Câu lệnh SQL sau:
SELECT COUNT(DISTINCT Company) FROM Orders
Sẽ trả về kết quả là: 3
29.1.1. Hàm count(), min(), max(), sum(), avg()
Hàm AVG(column)
Hàm AVG trả về giá trị trung bình tính theo cột được chỉ định của các dòng được chọn. Các giá trị NULL sẽ không được xét đến khi tính giá trị trung bình.
Ví dụ:
Câu lệnh sau sẽ tính số tuổi trung bình của những người có tuổi trên 20:
SELECT AVG(Age) FROM Persons WHERE Age > 20
kết quả trả về sẽ là: 39.5
29.1.1. Hàm count(), min(), max(), sum(), avg()
Hàm MAX(column)
Hàm MAX trả về giá trị lớn nhất trong cột. Các giá trị NULL sẽ không được xét đến.
Ví dụ:
SELECT MAX(Age) FROM Persons
kết quả trả về: 45
29.1.1. Hàm count(), min(), max(), sum(), avg()
Hàm MIN(column)
Hàm MAX trả về giá trị nhỏ nhất trong cột. Các giá trị NULL sẽ không được xét đến.
Ví dụ:
SELECT MIN(Age) FROM Persons
kết quả trả về: 19
Lưu ý: Hàm MIN và MAX cũng có thể áp dụng cho các cột có dữ liệu là chuỗi văn bản. Dữ liệu trong cột sẽ được so sánh theo thứ tự tăng dần của từ điển
29.1.1. Hàm count(), min(), max(), sum(), avg()
Hàm SUM(column)
Hàm SUM trả về tổng giá trị của cột. Các giá trị NULL sẽ không được xét đến.
Ví dụ:
Tìm tổng số tuổi của tất cả những người có trong bảng:
SELECT SUM(Age) FROM Persons
kết quả trả về: 98
Ví dụ:
Tìm tổng số tuổi của tất cả những người có tuổi lớn hơn 20:
SELECT SUM(Age) FROM Persons WHERE Age > 20
kết quả trả về: 79
29.2. Hàm gom nhóm
29.2.1. Cú pháp
Các hàm tập hợp (ví dụ như SUM) thông thường cần thêm chức năng của mệnh đề GROUP BY.
GROUP BY...
Mệnh đề GROUP BY...được thêm vào SQL bởi vì các hàm tập hợp (như SUM) trả về một tập hợp của các giá trị trong cột mỗi khi chúng được gọi, và nếu không có GROUP BY ta không thể nào tính được tổng của các giá trị theo từng nhóm riêng lẻ trong cột.
Cú pháp của GROUP BY như sau:
SELECT tên_cột, SUM(tên_cột) FROM tên_bảng GROUP BY tên_cột
29.2.1. Cú pháp
Ví dụ sử dụng GROUP BY:
Giả sử ta có bảng Sales như sau:
Câu lệnh SQL sau:
SELECT Company, SUM(Amount) FROM Sales
sẽ trả về kết quả:
29.2.1. Cú pháp
Kết quả trả về ở trên đôi khi không phải là cái mà ta mong đợi. Ta thêm mệnh đề GROUP BY vào trong câu lệnh SQL:
SELECT Company, SUM(Amount) FROM Sales
GROUP BY Company
và kết quả trả về lần này sẽ là:
Kết quả này đúng là cái mà ta mong muốn
29.2.2. Điều kiện gom nhóm
HAVING...
Mệnh đề HAVING...được thêm vào SQL vì mệnh đề WHERE không áp dụng được đối với các hàm tập hợp (như SUM). Nếu không có HAVING, ta không thể nào kiểm tra được điều kiện với các hàm tập hợp.
Cú pháp của HAVING như sau:
SELECT tên_cột, SUM(tên_cột) FROM tên_bảng
GROUP BY tên_cột
HAVING SUM(tên_cột) điều_kiện giá_trị
Ta sử dụng lại bảng Sales ở trên. Câu lệnh SQL sau:
SELECT Company, SUM(Amount) FROM Sales
GROUP BY Company
HAVING SUM(Amount) > 10000
29.2.2. Điều kiện gom nhóm
Sẽ trả về kết quả:
Với SQL, bí danh có thể được sử dụng cho tên của cột và tên của bảng.
Bí danh cột:
Cú pháp bí danh cột như sau:
SELECT tên_cột AS bí_danh_cột FROM tên_bảng
Bí danh bảng:
Bí danh bảng có cú pháp như sau:
SELECT tên_cột FROM tên_bảng AS bí_danh_bảng
29.2.2. Điều kiện gom nhóm
Ví dụ sử dụng bí danh cột:
Ta có bảng Persons như sau:
Câu lệnh SQL sau:
SELECT LastName AS Họ, FirstName AS Tên
FROM Persons
Sẽ trả về kết quả:
29.3. Thứ tự thực hiện câu truy vấn có mệnh đề group by và having
Mệnh đề GROUP BY
Các thuộc tính trong mệnh đề SELECT (trừ những thuộc tính trong các hàm kết hợp) phải xuất hiện trong mệnh đề GROUP BY
Mệnh đề HAVING
Sử dụng các hàm kết hợp trong mệnh đề SELECT để kiểm tra một số điều kiện nào đó
Chỉ kiểm tra điều kiện trên nhóm, không là điều kiện lọc trên từng bộ
Sau khi gom nhóm điều kiện trên nhóm mới được thực hiện
29.3. Thứ tự thực hiện câu truy vấn có mệnh đề group by và having
Thứ tự thực hiện câu truy vấn có mệnh đề GROUP BY và HAVING
Chọn ra những dòng thỏa điều kiện trong mệnh đề WHERE
Những dòng này sẽ được gom thành nhiều nhóm tương ứng với mệnh đề GROUP BY
Áp dụng các hàm kết hợp cho mỗi nhóm
Bỏ qua những nhóm không thỏa điều kiện trong mệnh đề HAVING
Rút trích các giá trị của các cột và hàm kết hợp trong mệnh đề SELECT
29.4. Bài tập liên quan đến hàm kết hợp và gom nhóm
29.5. Một số truy vấn khác
29.5.1. Truy vấn con ở mệnh đề from
Kết quả trả về của một câu truy vấn phụ là một bảng
- Bảng trung gian trong quá trình truy vấn
- Không có lưu trữ thật sự
Cú pháp:
29.5.1. Truy vấn con ở mệnh đề from
Ví dụ:
Trong cơ sở dữ liệu quản lý vật tư, chúng ta muốn hiển thị tên những nhân viên đã xuất vật tư trong ngày ‘12/08/2008’, câu lệnh truy vấn sau sẽ thực hiện yêu cầu trên.
SELECT nv.ten_nv FROM nhan_vien As nv, (SELECT manv_xuat FROM phieu_xuat WHERE ngay_xuat = ‘08/12/2008’) As Temp
WHERE nv.ma_nv = Temp.manv_xuat
29.5.2. Điều kiện kết nối của mệnh đề from
29.5.2.1. kết nối bằng
INNER JOIN : trả về tất cả các hàng từ hai bảng khi điều kiện được so trùng. Nếu các hàng trong bảng R1 không so trùng trong bảng R2, hàng đó sẽ không được chọn ra.
29.5.2.2. kết nối Ngoài
LEFT JOIN : Trả về tất cả các hàng từ bảng thứ nhất R1, cho dù nó không được so trùng trong bảng thứ hai R2. Nếu các hàng trong bảng R1 không so trùng trong bảng R2, những hàng này cũng được liệt kê (thay thế bởi giá trị NULL).
RIGHT JOIN: Trả về tất cả các hàng từ bảng thứ nhất R2, cho dù nó không được so trùng trong bảng thứ hai R1. Nếu các hàng trong bảng R2 không so trùng trong bảng R1, những hàng này cũng được liệt kê (thay thế bởi giá trị NULL).
29.6. Các bài tập liên quan đến một số dạng truy vấn khác
HÀM KẾT HỢP VÀ GOM NHÓM, MỘT SO TRUY VẤN KHÁC
29.1. Hàm kết hợp
29.1.1. Hàm count(), min(), max(), sum(), avg()
Cú pháp của hàm COUNT:
SELECT COUNT(tên_cột) FROM tên_bảng:
Hàm COUNT(*):
Hàm COUNT(*) trả về số lượng các dòng được chọn ở trong bảng.
Ví dụ ta có bảng Persons như sau:
29.1.1. Hàm count(), min(), max(), sum(), avg()
Câu lệnh sau sẽ trả về số lượng các dòng trong bảng:
SELECT COUNT(*) FROM Persons và kết quả trả về sẽ là : 3
Câu lệnh sau sẽ trả về số lượng những người lớn hơn 20 tuổi:
SELECT COUNT(*) FROM Persons WHERE Age > 20
kết quả trả về sẽ là: 2
Hàm COUNT(column):
Hàm COUNT(column) sẽ trả về số lượng các dòng có giá trị khác NULL ở cột được chỉ định.
29.1.1. Hàm count(), min(), max(), sum(), avg()
Ví dụ ta có bảng Persons như sau:
Câu lệnh sau sẽ trả về số lượng những người mà cột Age trong bảng không rỗng:
SELECT COUNT(Age) FROM Persons
và kết quả trả về sẽ là: 2
29.1.1. Hàm count(), min(), max(), sum(), avg()
Mệnh đề COUNT DISTINCT
Lưu ý: Các ví dụ dưới đây chỉ hoạt động với CSDL Oracle và MS SQL Server, không hoạt động trên MS Access (chưa thử nhiệm với các hệ CSDL khác!)
Từ khoá DISTINCT và COUNT có thể được dùng chung với nhau để đếm số lượng các kết quả không trùng nhau.
Cú pháp như sau:
SELECT COUNT(DISTINCT column(s)) FROM table
29.1.1. Hàm count(), min(), max(), sum(), avg()
Ví dụ ta có bảng Orders như sau:
Câu lệnh SQL sau:
SELECT COUNT(DISTINCT Company) FROM Orders
sẽ trả về kết quả là: 3
29.1.1. Hàm count(), min(), max(), sum(), avg()
SQL có sẵn khá nhiều hàm để thực hiện đếm và tính toán.
Cú pháp:
Cú pháp để gọi hàm trong câu lệnh SQL như sau:
SELECT function(tên_cột) FROM tên_bảng
29.1.1. Hàm count(), min(), max(), sum(), avg()
SQL có sẵn khá nhiều hàm để thực hiện đếm Bảng dữ liệu chúng ta sẽ dùng trong các ví sụ tiếp theo:
Câu lệnh SQL sau:
SELECT COUNT(DISTINCT Company) FROM Orders
Sẽ trả về kết quả là: 3
29.1.1. Hàm count(), min(), max(), sum(), avg()
Hàm AVG(column)
Hàm AVG trả về giá trị trung bình tính theo cột được chỉ định của các dòng được chọn. Các giá trị NULL sẽ không được xét đến khi tính giá trị trung bình.
Ví dụ:
Câu lệnh sau sẽ tính số tuổi trung bình của những người có tuổi trên 20:
SELECT AVG(Age) FROM Persons WHERE Age > 20
kết quả trả về sẽ là: 39.5
29.1.1. Hàm count(), min(), max(), sum(), avg()
Hàm MAX(column)
Hàm MAX trả về giá trị lớn nhất trong cột. Các giá trị NULL sẽ không được xét đến.
Ví dụ:
SELECT MAX(Age) FROM Persons
kết quả trả về: 45
29.1.1. Hàm count(), min(), max(), sum(), avg()
Hàm MIN(column)
Hàm MAX trả về giá trị nhỏ nhất trong cột. Các giá trị NULL sẽ không được xét đến.
Ví dụ:
SELECT MIN(Age) FROM Persons
kết quả trả về: 19
Lưu ý: Hàm MIN và MAX cũng có thể áp dụng cho các cột có dữ liệu là chuỗi văn bản. Dữ liệu trong cột sẽ được so sánh theo thứ tự tăng dần của từ điển
29.1.1. Hàm count(), min(), max(), sum(), avg()
Hàm SUM(column)
Hàm SUM trả về tổng giá trị của cột. Các giá trị NULL sẽ không được xét đến.
Ví dụ:
Tìm tổng số tuổi của tất cả những người có trong bảng:
SELECT SUM(Age) FROM Persons
kết quả trả về: 98
Ví dụ:
Tìm tổng số tuổi của tất cả những người có tuổi lớn hơn 20:
SELECT SUM(Age) FROM Persons WHERE Age > 20
kết quả trả về: 79
29.2. Hàm gom nhóm
29.2.1. Cú pháp
Các hàm tập hợp (ví dụ như SUM) thông thường cần thêm chức năng của mệnh đề GROUP BY.
GROUP BY...
Mệnh đề GROUP BY...được thêm vào SQL bởi vì các hàm tập hợp (như SUM) trả về một tập hợp của các giá trị trong cột mỗi khi chúng được gọi, và nếu không có GROUP BY ta không thể nào tính được tổng của các giá trị theo từng nhóm riêng lẻ trong cột.
Cú pháp của GROUP BY như sau:
SELECT tên_cột, SUM(tên_cột) FROM tên_bảng GROUP BY tên_cột
29.2.1. Cú pháp
Ví dụ sử dụng GROUP BY:
Giả sử ta có bảng Sales như sau:
Câu lệnh SQL sau:
SELECT Company, SUM(Amount) FROM Sales
sẽ trả về kết quả:
29.2.1. Cú pháp
Kết quả trả về ở trên đôi khi không phải là cái mà ta mong đợi. Ta thêm mệnh đề GROUP BY vào trong câu lệnh SQL:
SELECT Company, SUM(Amount) FROM Sales
GROUP BY Company
và kết quả trả về lần này sẽ là:
Kết quả này đúng là cái mà ta mong muốn
29.2.2. Điều kiện gom nhóm
HAVING...
Mệnh đề HAVING...được thêm vào SQL vì mệnh đề WHERE không áp dụng được đối với các hàm tập hợp (như SUM). Nếu không có HAVING, ta không thể nào kiểm tra được điều kiện với các hàm tập hợp.
Cú pháp của HAVING như sau:
SELECT tên_cột, SUM(tên_cột) FROM tên_bảng
GROUP BY tên_cột
HAVING SUM(tên_cột) điều_kiện giá_trị
Ta sử dụng lại bảng Sales ở trên. Câu lệnh SQL sau:
SELECT Company, SUM(Amount) FROM Sales
GROUP BY Company
HAVING SUM(Amount) > 10000
29.2.2. Điều kiện gom nhóm
Sẽ trả về kết quả:
Với SQL, bí danh có thể được sử dụng cho tên của cột và tên của bảng.
Bí danh cột:
Cú pháp bí danh cột như sau:
SELECT tên_cột AS bí_danh_cột FROM tên_bảng
Bí danh bảng:
Bí danh bảng có cú pháp như sau:
SELECT tên_cột FROM tên_bảng AS bí_danh_bảng
29.2.2. Điều kiện gom nhóm
Ví dụ sử dụng bí danh cột:
Ta có bảng Persons như sau:
Câu lệnh SQL sau:
SELECT LastName AS Họ, FirstName AS Tên
FROM Persons
Sẽ trả về kết quả:
29.3. Thứ tự thực hiện câu truy vấn có mệnh đề group by và having
Mệnh đề GROUP BY
Các thuộc tính trong mệnh đề SELECT (trừ những thuộc tính trong các hàm kết hợp) phải xuất hiện trong mệnh đề GROUP BY
Mệnh đề HAVING
Sử dụng các hàm kết hợp trong mệnh đề SELECT để kiểm tra một số điều kiện nào đó
Chỉ kiểm tra điều kiện trên nhóm, không là điều kiện lọc trên từng bộ
Sau khi gom nhóm điều kiện trên nhóm mới được thực hiện
29.3. Thứ tự thực hiện câu truy vấn có mệnh đề group by và having
Thứ tự thực hiện câu truy vấn có mệnh đề GROUP BY và HAVING
Chọn ra những dòng thỏa điều kiện trong mệnh đề WHERE
Những dòng này sẽ được gom thành nhiều nhóm tương ứng với mệnh đề GROUP BY
Áp dụng các hàm kết hợp cho mỗi nhóm
Bỏ qua những nhóm không thỏa điều kiện trong mệnh đề HAVING
Rút trích các giá trị của các cột và hàm kết hợp trong mệnh đề SELECT
29.4. Bài tập liên quan đến hàm kết hợp và gom nhóm
29.5. Một số truy vấn khác
29.5.1. Truy vấn con ở mệnh đề from
Kết quả trả về của một câu truy vấn phụ là một bảng
- Bảng trung gian trong quá trình truy vấn
- Không có lưu trữ thật sự
Cú pháp:
29.5.1. Truy vấn con ở mệnh đề from
Ví dụ:
Trong cơ sở dữ liệu quản lý vật tư, chúng ta muốn hiển thị tên những nhân viên đã xuất vật tư trong ngày ‘12/08/2008’, câu lệnh truy vấn sau sẽ thực hiện yêu cầu trên.
SELECT nv.ten_nv FROM nhan_vien As nv, (SELECT manv_xuat FROM phieu_xuat WHERE ngay_xuat = ‘08/12/2008’) As Temp
WHERE nv.ma_nv = Temp.manv_xuat
29.5.2. Điều kiện kết nối của mệnh đề from
29.5.2.1. kết nối bằng
INNER JOIN : trả về tất cả các hàng từ hai bảng khi điều kiện được so trùng. Nếu các hàng trong bảng R1 không so trùng trong bảng R2, hàng đó sẽ không được chọn ra.
29.5.2.2. kết nối Ngoài
LEFT JOIN : Trả về tất cả các hàng từ bảng thứ nhất R1, cho dù nó không được so trùng trong bảng thứ hai R2. Nếu các hàng trong bảng R1 không so trùng trong bảng R2, những hàng này cũng được liệt kê (thay thế bởi giá trị NULL).
RIGHT JOIN: Trả về tất cả các hàng từ bảng thứ nhất R2, cho dù nó không được so trùng trong bảng thứ hai R1. Nếu các hàng trong bảng R2 không so trùng trong bảng R1, những hàng này cũng được liệt kê (thay thế bởi giá trị NULL).
29.6. Các bài tập liên quan đến một số dạng truy vấn khác
* Một số tài liệu cũ có thể bị lỗi font khi hiển thị do dùng bộ mã không phải Unikey ...
Người chia sẻ: Trần Thị Như Trang
Dung lượng: |
Lượt tài: 2
Loại file:
Nguồn : Chưa rõ
(Tài liệu chưa được thẩm định)